spec · supabase_json
KnowledgeVault AI spec M-09
spec artifact · for KnowledgeVault AI · phase M-09 · status draft
Agent Handoff
Start Here
| id | role | brief | title | completion | depends on |
|---|---|---|---|---|---|
| 1 | developer | Create the Postgres migration at apps/knowledgevault/supabase/migrations/020_company_dashboard_mv.sql. Use CREATE MATERIALIZED VIEW IF NOT EXISTS company_dashboard_mv (idempotent per POL-ARCH-001). One row per company.
Required columns:
- company_id uuid NOT NULL — from companies c
- company_name text NOT NULL — c.name
- coverage_grid jsonb NOT NULL DEFAULT '[]' — built via lateral join products p JOIN company_knowledge_access cka ON cka.product_id = p.id AND cka.company_id = c.id. Each element: `json_build_object('sku', p.sku, 'name', p.name, 'coverage_pct', p.coverage_pct, ' | Create company_dashboard_mv materialized view migration | - type: file - evaluate: SQL file creates company_dashboard_mv with all 7 columns (company_id, company_name, coverage_grid, top_queries, slang_cloud, stats, refreshed_at), correct red/yellow/green thresholds (red<0.40, yellow 0.40–<0.80, green>=0.80), UNIQUE INDEX on company_id, and ends with REFRESH MATERIALIZED VIEW; file is idempotent (IF NOT EXISTS) per POL-ARCH-001. - min length: 600 | |
| 2 | developer | Create the Postgres migration at apps/knowledgevault/supabase/migrations/021_dashboard_cron_and_demo_seed.sql. Migration 020 (task 1) must be applied first — company_dashboard_mv must exist. Use INSERT ... ON CONFLICT DO NOTHING throughout for idempotency.
Part 1 — pg_cron job: SELECT cron.schedule('refresh_company_dashboard_mv', '0 * * * *', 'REFRESH MATERIALIZED VIEW CONCURRENTLY company_dashboard_mv'); — refreshes every hour on the hour. pg_cron extension is already enabled in the Supabase project.
Part 2 — Demo seed data. DEMO_COMPANY_UUID = 'a1b2c3d4-0000-0000-0000-000000000001'.
| Create pg_cron refresh job and demo seed migration | - type: file - evaluate: SQL file contains pg_cron.schedule call with '0 * * * *' schedule, inserts demo company with id='a1b2c3d4-0000-0000-0000-000000000001', 5 products with correct coverage_pct values, 10 field_queries (6 with ground_truth_verified:true, 4 with false), 20 capture_sessions, ends with REFRESH MATERIALIZED VIEW; all inserts use ON CONFLICT DO NOTHING per POL-ARCH-001. - min length: 800 | - 1 |
| 3 | developer | Create the Next.js App Router route handler at apps/knowledgevault/app/api/dashboard/company/[id]/route.ts implementing GET /api/dashboard/company/:id.
Import pattern (match existing codebase): use NextRequest, NextResponse from 'next/server'. Use Supabase server client from @/lib/supabase/server (createClient). Company JWT middleware importable from lib/middleware/companyAuth (ships with M-07).
Route logic:
1. Extract :id from params.id.
2. Demo mode: if params.id === 'demo' (exact case-sensitive), skip JWT. Set resolvedUuid = 'a1b2c3d4-0000-0000-0000-000000000001', `pow | Implement dashboard company GET API route | - type: file - evaluate: TypeScript file exports a GET handler that checks auth before DB access (POL-SEC-002), returns 200 with all 8 response fields (coverage_grid, top_queries, slang_cloud, stats, company_name, powered_by_badge, last_updated, stale), correctly bypasses auth for id==='demo', and returns typed 401/403/404/500 error envelopes; SUPABASE_SERVICE_ROLE_KEY is server-side only per POL-ARCH-003. - min length: 400 | - 1 |
| 4 | developer | Build the React page at apps/knowledgevault/app/dashboard/[company_id]/page.tsx — the Company Coverage Dashboard (S-08-a). This is a Next.js App Router client component ('use client') fetching GET /api/dashboard/company/:company_id.
On mount: fetch the API. Render full skeleton layout immediately (≤200ms) — no spinner-only state visible in Chrome DevTools Performance trace.
On data load, render 4 sections:
1. Sticky header (position: sticky, top: 0): company_name from API + 'Powered by KnowledgeVault' badge (aria-label='Powered by KnowledgeVault', background: color.badge.powered_by). | Build company coverage dashboard React page | - type: file - evaluate: TSX file renders skeleton on mount, all 4 sections (sticky header with badge, stats row with 3 cards, coverage grid with correct aria-labels and status badge colors, right sidebar with slang cloud and top queries), staleness banner, error state with Retry CTA, and empty states for all 3 array sections; all click handlers navigate to correct /search routes; /dashboard/demo requires no auth. - min length: 800 | - 3 |
| 5 | developer | Write two test files for M-09 under apps/knowledgevault/tests/m09/.
File 1 (primary output): apps/knowledgevault/tests/m09/api.test.ts — unit tests using Vitest or Jest + supertest (match project test stack). Test the dashboard API route (GET /api/dashboard/company/:id). Write exactly 6 test cases:
1. GET /api/dashboard/company/demo with no Authorization header → 200. Assert exact response shape: `{coverage_grid: Array, top_queries: Array, slang_cloud: Array, stats: {hours_preserved: Number, support_call_reduction_pct: Number, roi_gauge: Number}, company_name: String, powered_by_badge: t | Write M-09 API unit tests and k6 load test | - type: file - evaluate: Test file contains exactly 6 test cases covering demo 200, authenticated 200, 403 company_id mismatch, 401 no-auth, 404 unknown UUID, and stale=true staleness check; companion tests/m09/load.js exists with 50 VU / 30s config and p95<2000ms threshold; all 6 unit tests pass on npm test / vitest run. - min length: 600 | - 2 - 3 |
Completion Evidence
No explicit evidence field yet. Require tests, screenshots, linked PRs, or reviewed outputs before marking complete.
Artifact Shape
- notes: API route output path adapted from spec's 'Cloudflare Pages Function at functions/api/dashboard/company/[id].ts' to Next.js App Router convention 'app/api/dashboard/company/[id]/route.ts' — codebase uses Next.js (apps/kv-app/app/api/ pattern with route.ts files). Dashboard page uses app/dashboard/[company_id]/page.tsx (no (dashboard) route group) consistent with existing app/dashboard/ structure. All OQs resolved in spec: OQ-M-09-01 stats formulas approved, OQ-M-09-02 JSONB path confirmed, OQ-M-09-03 middleware import path confirmed, OQ-M-09-04 session_duration_s column confirmed.
- tasks: 5 items
- module id: M-09
- module name: Coverage Dashboard
- artifact type: plan-module
- schema version: 1.0
- depends on modules: 2 items
Structured Payload
Machine-readable source fields
notes
API route output path adapted from spec's 'Cloudflare Pages Function at functions/api/dashboard/company/[id].ts' to Next.js App Router convention 'app/api/dashboard/company/[id]/route.ts' — codebase uses Next.js (apps/kv-app/app/api/ pattern with route.ts files). Dashboard page uses app/dashboard/[company_id]/page.tsx (no (dashboard) route group) consistent with existing app/dashboard/ structure. All OQs resolved in spec: OQ-M-09-01 stats formulas approved, OQ-M-09-02 JSONB path confirmed, OQ-M-09-03 middleware import path confirmed, OQ-M-09-04 session_duration_s column confirmed.
tasks
| id | role | brief | title | completion | depends on |
|---|---|---|---|---|---|
| 1 | developer | Create the Postgres migration at apps/knowledgevault/supabase/migrations/020_company_dashboard_mv.sql. Use CREATE MATERIALIZED VIEW IF NOT EXISTS company_dashboard_mv (idempotent per POL-ARCH-001). One row per company.
Required columns:
- company_id uuid NOT NULL — from companies c
- company_name text NOT NULL — c.name
- coverage_grid jsonb NOT NULL DEFAULT '[]' — built via lateral join products p JOIN company_knowledge_access cka ON cka.product_id = p.id AND cka.company_id = c.id. Each element: `json_build_object('sku', p.sku, 'name', p.name, 'coverage_pct', p.coverage_pct, ' | Create company_dashboard_mv materialized view migration | - type: file - evaluate: SQL file creates company_dashboard_mv with all 7 columns (company_id, company_name, coverage_grid, top_queries, slang_cloud, stats, refreshed_at), correct red/yellow/green thresholds (red<0.40, yellow 0.40–<0.80, green>=0.80), UNIQUE INDEX on company_id, and ends with REFRESH MATERIALIZED VIEW; file is idempotent (IF NOT EXISTS) per POL-ARCH-001. - min length: 600 | |
| 2 | developer | Create the Postgres migration at apps/knowledgevault/supabase/migrations/021_dashboard_cron_and_demo_seed.sql. Migration 020 (task 1) must be applied first — company_dashboard_mv must exist. Use INSERT ... ON CONFLICT DO NOTHING throughout for idempotency.
Part 1 — pg_cron job: SELECT cron.schedule('refresh_company_dashboard_mv', '0 * * * *', 'REFRESH MATERIALIZED VIEW CONCURRENTLY company_dashboard_mv'); — refreshes every hour on the hour. pg_cron extension is already enabled in the Supabase project.
Part 2 — Demo seed data. DEMO_COMPANY_UUID = 'a1b2c3d4-0000-0000-0000-000000000001'.
| Create pg_cron refresh job and demo seed migration | - type: file - evaluate: SQL file contains pg_cron.schedule call with '0 * * * *' schedule, inserts demo company with id='a1b2c3d4-0000-0000-0000-000000000001', 5 products with correct coverage_pct values, 10 field_queries (6 with ground_truth_verified:true, 4 with false), 20 capture_sessions, ends with REFRESH MATERIALIZED VIEW; all inserts use ON CONFLICT DO NOTHING per POL-ARCH-001. - min length: 800 | - 1 |
| 3 | developer | Create the Next.js App Router route handler at apps/knowledgevault/app/api/dashboard/company/[id]/route.ts implementing GET /api/dashboard/company/:id.
Import pattern (match existing codebase): use NextRequest, NextResponse from 'next/server'. Use Supabase server client from @/lib/supabase/server (createClient). Company JWT middleware importable from lib/middleware/companyAuth (ships with M-07).
Route logic:
1. Extract :id from params.id.
2. Demo mode: if params.id === 'demo' (exact case-sensitive), skip JWT. Set resolvedUuid = 'a1b2c3d4-0000-0000-0000-000000000001', `pow | Implement dashboard company GET API route | - type: file - evaluate: TypeScript file exports a GET handler that checks auth before DB access (POL-SEC-002), returns 200 with all 8 response fields (coverage_grid, top_queries, slang_cloud, stats, company_name, powered_by_badge, last_updated, stale), correctly bypasses auth for id==='demo', and returns typed 401/403/404/500 error envelopes; SUPABASE_SERVICE_ROLE_KEY is server-side only per POL-ARCH-003. - min length: 400 | - 1 |
| 4 | developer | Build the React page at apps/knowledgevault/app/dashboard/[company_id]/page.tsx — the Company Coverage Dashboard (S-08-a). This is a Next.js App Router client component ('use client') fetching GET /api/dashboard/company/:company_id.
On mount: fetch the API. Render full skeleton layout immediately (≤200ms) — no spinner-only state visible in Chrome DevTools Performance trace.
On data load, render 4 sections:
1. Sticky header (position: sticky, top: 0): company_name from API + 'Powered by KnowledgeVault' badge (aria-label='Powered by KnowledgeVault', background: color.badge.powered_by). | Build company coverage dashboard React page | - type: file - evaluate: TSX file renders skeleton on mount, all 4 sections (sticky header with badge, stats row with 3 cards, coverage grid with correct aria-labels and status badge colors, right sidebar with slang cloud and top queries), staleness banner, error state with Retry CTA, and empty states for all 3 array sections; all click handlers navigate to correct /search routes; /dashboard/demo requires no auth. - min length: 800 | - 3 |
| 5 | developer | Write two test files for M-09 under apps/knowledgevault/tests/m09/.
File 1 (primary output): apps/knowledgevault/tests/m09/api.test.ts — unit tests using Vitest or Jest + supertest (match project test stack). Test the dashboard API route (GET /api/dashboard/company/:id). Write exactly 6 test cases:
1. GET /api/dashboard/company/demo with no Authorization header → 200. Assert exact response shape: `{coverage_grid: Array, top_queries: Array, slang_cloud: Array, stats: {hours_preserved: Number, support_call_reduction_pct: Number, roi_gauge: Number}, company_name: String, powered_by_badge: t | Write M-09 API unit tests and k6 load test | - type: file - evaluate: Test file contains exactly 6 test cases covering demo 200, authenticated 200, 403 company_id mismatch, 401 no-auth, 404 unknown UUID, and stale=true staleness check; companion tests/m09/load.js exists with 50 VU / 30s config and p95<2000ms threshold; all 6 unit tests pass on npm test / vitest run. - min length: 600 | - 2 - 3 |
module id
M-09
module name
Coverage Dashboard
artifact type
plan-module
schema version
1.0
depends on modules
- M-05
- M-07