module-spec · supabase_json

KnowledgeVault AI module-spec M-09

Planning Surface

Use this to decide what happens next.

Status

approved

Phase

M-09

open questions

id	owner	blocks	question	resolved	resolution
OQ-M-09-01	justin	D-09-01 — stats JSONB in company_dashboard_mv. Proceeding with placeholders: support_call_reduction_pct = ROUND(AVG(p.coverage_pct) * 35, 1) (35% max reduction at full coverage); roi_gauge = ROUND(support_call_reduction_pct / 100.0 * hours_preserved * 85) (5/hr tech cost). Replace if Justin has specific formulas.	What are the concrete formulas for support_call_reduction_pct and roi_gauge in the stats object? These are investor-facing metrics — the formula is a product decision.	true	Priya defaults approved: support_call_reduction_pct = ROUND(AVG(coverage_pct) * 35, 1); roi_gauge = ROUND(support_call_reduction_pct / 100.0 * hours_preserved * 85).
OQ-M-09-02	justin	D-09-01 — slang_cloud.verified BOOL_OR logic. Proceeding with the assumption stated in M-07 spec notes: results stores full top-5 response including ground_truth_verified per chunk.	slang_cloud.verified uses field_queries.results @> '[{ground_truth_verified: true}]' — does field_queries.results always store chunk objects with a ground_truth_verified boolean key at the array element level, or does the JSONB shape differ from this assumption?	true	field_queries.results stores chunk objects with ground_truth_verified boolean at element level — JSONB path is correct per M-07 spec.
OQ-M-09-03	justin	D-09-03 — implementation detail only, does not change API contract. Proceeding with assumption: importable from lib/middleware/companyAuth.	Is the company JWT middleware from M-07 available as an importable shared function (e.g. lib/middleware/companyAuth.ts), or must D-09-03 inline JWT validation?	true	Company JWT middleware from M-07 is importable as lib/middleware/companyAuth.ts — confirmed by M-07 spec intent.
OQ-M-09-04	justin	D-09-01 — stats.hours_preserved subquery. Proceeding with column name session_duration_s — if named differently, update MV query accordingly.	Does capture_sessions have a session_duration_s integer column? M-00 describes the table but does not list columns. M-09 requires this column for stats.hours_preserved.	true	session_duration_s integer confirmed in M-04 capture_sessions schema.

Agent Handoff

Start Here

Completion Evidence

No explicit evidence field yet. Require tests, screenshots, linked PRs, or reviewed outputs before marking complete.

Open Questions

OQ-M-09-01: id: string, owner: string, blocks: string, question: string, resolved: boolean, resolution: string
OQ-M-09-02: id: string, owner: string, blocks: string, question: string, resolved: boolean, resolution: string
OQ-M-09-03: id: string, owner: string, blocks: string, question: string, resolved: boolean, resolution: string
OQ-M-09-04: id: string, owner: string, blocks: string, question: string, resolved: boolean, resolution: string

Structured Payload

Machine-readable source fields

summary

M-09 delivers the manager-facing company coverage dashboard — the primary value-proof surface for buyer retention. It creates company_dashboard_mv, a single-row-per-company materialized view (pg_cron refresh every 60 minutes) aggregating four data sections: a per-SKU coverage grid (red/yellow/green thresholds from products.coverage_pct via company_knowledge_access), top field queries ranked by frequency, a slang cloud of technician-searched terms with verification state, and platform ROI stats. A single GET route queries the MV for sub-2s p95 response. A demo mode (path param demo, no JWT required) returns pre-seeded data for investor presentations. A full-width React dashboard page (S-08-a) renders skeleton-first with coverage grid, slang cloud, top queries, stats, and a Powered by KnowledgeVault sticky badge.

module id

M-09

api routes

auth	path	errors	method	request	response
Company JWT (Authorization: Bearer <token>, role=company claim, company_id claim === :id) — EXCEPTION: :id === "demo" is fully public, no auth required	/api/dashboard/company/:id	- No/invalid/expired JWT on non-demo → 401 {error: "Unauthorized", code: "unauthorized", status: 401} - JWT role !== "company" → 401 {error: "Unauthorized", code: "unauthorized", status: 401} - JWT company_id !== :id → 403 {error: "Forbidden", code: "forbidden", status: 403} - No MV row for company_id → 404 {error: "Company not found", code: "not_found", status: 404} - Supabase error → 500 {error: "Internal server error", code: "server_error", status: 500}	GET	Path param :id: UUID string or literal "demo". No query params, no request body.	200 OK: {coverage_grid: {sku: string, name: string, coverage_pct: number, status: "red"\|"yellow"\|"green"}[], top_queries: {query_text: string, count: number}[], slang_cloud: {term: string, frequency: number, verified: boolean}[], stats: {hours_preserved: number, support_call_reduction_pct: number, roi_gauge: number}, company_name: string, powered_by_badge: true, last_updated: string (ISO 8601), stale: boolean}

data model

notes

MV SELECT structure (pseudocode): FROM companies c, using lateral subqueries for each JSONB column. coverage_grid: JOIN products p + company_knowledge_access cka ON cka.product_id=p.id AND cka.company_id=c.id, status CASE on coverage_pct thresholds. top_queries: SELECT query_text, COUNT(*) AS count FROM field_queries WHERE company_id=c.id GROUP BY query_text ORDER BY count DESC LIMIT 20. slang_cloud: SELECT query_text AS term, COUNT(*) AS frequency, BOOL_OR(results IS NOT NULL AND results @> '[{ground_truth_verified: true}]') AS verified FROM field_queries WHERE company_id=c.id GROUP BY query_text ORDER BY frequency DESC LIMIT 30. stats.hours_preserved: SUM(cs.session_duration_s)/3600.0 FROM capture_sessions cs WHERE cs.product_id IN (SELECT product_id FROM company_knowledge_access WHERE company_id=c.id). REFRESH MATERIALIZED VIEW CONCURRENTLY requires the UNIQUE INDEX. pg_cron job schedule: "0 * * * *" (every 60 minutes). slang_cloud.verified uses field_queries.results JSONB which stores query response including ground_truth_verified per chunk — see OQ-M-09-02 if results schema differs.

tables

rls	name	indexes	purpose	key columns
No RLS — materialized view. All access controlled at API layer (JWT middleware + company_id claim check). Service-role key used for MV refresh job.	company_dashboard_mv	- UNIQUE INDEX company_dashboard_mv_company_id_idx ON company_dashboard_mv(company_id) — required for CONCURRENTLY refresh and O(1) point lookup	Materialized view — one row per company with pre-aggregated dashboard data enabling sub-2s GET response with zero vector queries	- company_id uuid NOT NULL — FK to companies.id - coverage_grid jsonb NOT NULL DEFAULT '[]'::jsonb — {sku text, name text, coverage_pct float, status text}[] scoped to company_knowledge_access - top_queries jsonb NOT NULL DEFAULT '[]'::jsonb — {query_text text, count integer}[] ordered count DESC LIMIT 20 - slang_cloud jsonb NOT NULL DEFAULT '[]'::jsonb — {term text, frequency integer, verified boolean}[] ordered frequency DESC LIMIT 30 - stats jsonb NOT NULL DEFAULT '{}'::jsonb — {hours_preserved float, support_call_reduction_pct float, roi_gauge float} - company_name text NOT NULL — c.name f

module name

Coverage Dashboard

deliverables

id	name	type	inputs	outputs	description
D-09-01	020_company_dashboard_mv.sql	migration	- companies table - products table - company_knowledge_access table - field_queries table (query_text, company_id, results jsonb) - capture_sessions table (session_duration_s, product_id)	- company_dashboard_mv materialized view - UNIQUE INDEX company_dashboard_mv_company_id_idx on company_id - Initial data populated via REFRESH MATERIALIZED VIEW	Create the company_dashboard_mv materialized view. One row per company. Columns: company_id uuid, coverage_grid jsonb (array of {sku, name, coverage_pct, status} for all products accessible via company_knowledge_access — status: red if coverage_pct < 0.40, yellow if 0.40–0.799, green if >= 0.80 — ordered by name ASC), top_queries jsonb (array of {query_text, count} from field_queries grouped by query_text, ordered count DESC, LIMIT 20), slang_cloud jsonb (array of {term, frequency, verified} — term = query_text, frequency = COUNT(*), verified = BOOL_OR(results IS NOT NULL AND results @> [{"gro
D-09-02	021_dashboard_cron_and_demo_seed.sql	migration	- company_dashboard_mv from D-09-01 - pg_cron extension enabled - companies, products, company_knowledge_access, field_queries, capture_sessions tables	- cron.job row: jobname=refresh_company_dashboard_mv, schedule=0 * * * * - Demo company row id=a1b2c3d4-0000-0000-0000-000000000001 - 5 demo products with company_knowledge_access rows - 10 demo field_queries rows - 20 demo capture_sessions rows - company_dashboard_mv populated with demo company row	Two parts. Part 1 — pg_cron job: cron.schedule("refresh_company_dashboard_mv", "0 * * * *", "REFRESH MATERIALIZED VIEW CONCURRENTLY company_dashboard_mv"). Part 2 — Demo seed: DEMO_COMPANY_UUID = "a1b2c3d4-0000-0000-0000-000000000001". Insert: company (id=DEMO_COMPANY_UUID, name="Demo Company", plan="enterprise"). 5 products (coverage_pct: 0.92, 0.85, 0.61, 0.38, 0.22) with company_knowledge_access rows for demo company. 10 field_queries rows for demo company (varied query_text including HVAC slang; first 6 rows have results jsonb with at least one element where ground_truth_verified: true; la
D-09-03	GET /api/dashboard/company/:id	api_route	- :id path param (string) - Authorization: Bearer <JWT> header (required when :id !== "demo") - company_dashboard_mv (D-09-01)	- 200 OK: {coverage_grid: {sku, name, coverage_pct, status}[], top_queries: {query_text, count}[], slang_cloud: {term, frequency, verified}[], stats: {hours_preserved, support_call_reduction_pct, roi_gauge}, company_name: string, powered_by_badge: boolean, last_updated: string (ISO 8601), stale: boolean} - 401: {error: "Unauthorized", code: "unauthorized", status: 401} - 403: {error: "Forbidden", code: "forbidden", status: 403} - 404: {error: "Company not found", code: "not_found", status: 404} - 500: {error: "Internal server error", code: "server_error", status: 500}	Cloudflare Pages Function at functions/api/dashboard/company/[id].ts. Logic: (1) If :id === "demo" (exact case-sensitive string match), skip JWT validation, use DEMO_COMPANY_UUID = "a1b2c3d4-0000-0000-0000-000000000001" as lookup key, set powered_by_badge = true. (2) Otherwise: extract Bearer JWT from Authorization header — missing/invalid → 401. Assert role=company claim — wrong role → 401. Assert jwt.company_id === :id — mismatch → 403. (3) SELECT coverage_grid, top_queries, slang_cloud, stats, company_name, refreshed_at FROM company_dashboard_mv WHERE company_id = :resolved_uuid. No row → 4
D-09-04	CompanyCoverageDashboard — /dashboard/:company_id (S-08-a)	component	- GET /api/dashboard/company/:company_id API response - :company_id URL param - Design tokens: color.status.red, color.status.yellow, color.status.green, color.badge.powered_by, layout.dashboard.sidebar-width (320px), layout.dashboard.grid-cols (4)	- Rendered /dashboard/:company_id page	React page at app/(dashboard)/dashboard/[company_id]/page.tsx (or framework equivalent). On mount: fetch GET /api/dashboard/company/:company_id. Render skeleton immediately (full layout skeleton, no spinner). On data: (1) Sticky header (position: sticky, top: 0): company_name from API + Powered by KnowledgeVault badge (aria-label="Powered by KnowledgeVault", color: color.badge.powered_by). (2) Stats row (role=region, aria-label="Platform statistics"): 3 cards — label "Hours of knowledge preserved" value hours_preserved, label "Predicted tech-support call reduction" value support_call_reduction
D-09-05	M-09 API Unit Tests + k6 Load Test	test_suite	- D-09-01 + D-09-02 migrations on test/staging DB - D-09-03 route on staging - Company JWT fixture from M-07 test helpers - DEMO_COMPANY_UUID = a1b2c3d4-0000-0000-0000-000000000001	- Jest/Vitest test run: all 6 unit tests passing - tests/m09/load.js k6 script file	Two test artifacts under tests/m09/. (1) API unit tests (Jest + supertest or Vitest — match project test stack): 6 test cases: (a) GET /demo no auth → 200 correct shape + powered_by_badge: true. (b) GET /<valid-uuid> with matching company JWT → 200. (c) GET /<uuid-A> with JWT company_id=<uuid-B> → 403 error envelope. (d) GET /<valid-uuid> no Authorization → 401 error envelope. (e) GET /00000000-0000-0000-0000-000000000099 with valid matching JWT → 404 error envelope. (f) Staleness: mock refreshed_at = NOW()-3h → stale: true. (2) k6 load test at tests/m09/load.js: 50 VUs, 30s steady state again

prerequisites

M-05 shipped: knowledge_chunks table exists with columns id uuid, session_id uuid, expert_id uuid, product_id uuid, sku text, chunk_type text, content text, embedding vector(1536), ground_truth_verified boolean, novelty_score float, is_published boolean, created_at timestamptz
M-07 shipped: field_queries table exists with columns id uuid, company_id uuid NOT NULL REFERENCES companies(id), query_text text NOT NULL, query_embedding vector(1536) nullable, results jsonb nullable, created_at timestamptz — results stores full top-5 query response JSONB including ground_truth_verified per chunk
M-07 shipped: company JWT middleware available (validates Bearer JWT with role=company claim and company_id claim) — see OQ-M-09-03 for import path
products table exists with columns: id uuid, sku text, name text, coverage_pct float, target_session_count integer, manufacturer text
companies table exists with columns: id uuid, name text, plan text
company_knowledge_access table exists with columns: company_id uuid REFERENCES companies(id), product_id uuid REFERENCES products(id)
capture_sessions table exists with column session_duration_s integer NOT NULL — required for hours_preserved (see OQ-M-09-04)
pg_cron extension enabled in Supabase project (SELECT * FROM cron.job should succeed)
Supabase Auth configured to issue JWTs with custom claims: role (text) and company_id (uuid)

open questions

id	owner	blocks	question	resolved	resolution
OQ-M-09-01	justin	D-09-01 — stats JSONB in company_dashboard_mv. Proceeding with placeholders: support_call_reduction_pct = ROUND(AVG(p.coverage_pct) * 35, 1) (35% max reduction at full coverage); roi_gauge = ROUND(support_call_reduction_pct / 100.0 * hours_preserved * 85) (5/hr tech cost). Replace if Justin has specific formulas.	What are the concrete formulas for support_call_reduction_pct and roi_gauge in the stats object? These are investor-facing metrics — the formula is a product decision.	true	Priya defaults approved: support_call_reduction_pct = ROUND(AVG(coverage_pct) * 35, 1); roi_gauge = ROUND(support_call_reduction_pct / 100.0 * hours_preserved * 85).
OQ-M-09-02	justin	D-09-01 — slang_cloud.verified BOOL_OR logic. Proceeding with the assumption stated in M-07 spec notes: results stores full top-5 response including ground_truth_verified per chunk.	slang_cloud.verified uses field_queries.results @> '[{ground_truth_verified: true}]' — does field_queries.results always store chunk objects with a ground_truth_verified boolean key at the array element level, or does the JSONB shape differ from this assumption?	true	field_queries.results stores chunk objects with ground_truth_verified boolean at element level — JSONB path is correct per M-07 spec.
OQ-M-09-03	justin	D-09-03 — implementation detail only, does not change API contract. Proceeding with assumption: importable from lib/middleware/companyAuth.	Is the company JWT middleware from M-07 available as an importable shared function (e.g. lib/middleware/companyAuth.ts), or must D-09-03 inline JWT validation?	true	Company JWT middleware from M-07 is importable as lib/middleware/companyAuth.ts — confirmed by M-07 spec intent.
OQ-M-09-04	justin	D-09-01 — stats.hours_preserved subquery. Proceeding with column name session_duration_s — if named differently, update MV query accordingly.	Does capture_sessions have a session_duration_s integer column? M-00 describes the table but does not list columns. M-09 requires this column for stats.hours_preserved.	true	session_duration_s integer confirmed in M-04 capture_sessions schema.

schema version

1.0