KnowledgeVault AI module-spec M-10
M-10 delivers an admin-only analytics panel that surfaces aggregate platform impact (hours of expert knowledge preserved, support-call reduction estimate) and AI cost efficiency metrics (cost per session, cost per knowledge chunk, total AI spend). A pre-aggregated PostgreSQL materialized view — platform_analytics_mv — ensures sub-2-second API response time regardless of dataset size. Access is enforced via Supabase Auth admin-role check on every request; unauthenticated callers receive 401 and n
No items captured.
M-10 delivers an admin-only analytics panel that surfaces aggregate platform impact (hours of expert knowledge preserved, support-call reduction estimate) and AI cost efficiency metrics (cost per session, cost per knowledge chunk, total AI spend). A pre-aggregated PostgreSQL materialized view — platform_analytics_mv — ensures sub-2-second API response time regardless of dataset size. Access is enforced via Supabase Auth admin-role check on every request; unauthenticated callers receive 401 and n
No explicit evidence field yet. Require tests, screenshots, linked PRs, or reviewed outputs before marking complete.
No section body captured.
Machine-readable source fields
M-10 delivers an admin-only analytics panel that surfaces aggregate platform impact (hours of expert knowledge preserved, support-call reduction estimate) and AI cost efficiency metrics (cost per session, cost per knowledge chunk, total AI spend). A pre-aggregated PostgreSQL materialized view — platform_analytics_mv — ensures sub-2-second API response time regardless of dataset size. Access is enforced via Supabase Auth admin-role check on every request; unauthenticated callers receive 401 and non-admin callers receive 403. The panel closes the executive demo loop by giving stakeholders a single-screen view of KnowledgeVault's preserved knowledge value and operating cost.
M-10
| auth | path | errors | method | request | response |
|---|---|---|---|---|---|
| Admin session required — Supabase Auth cookie session where supabase.auth.getUser() returns a user with app_metadata.role = 'admin'. Role is set via Supabase Admin API on user creation and embedded in the JWT. No Authorization header — this is SSR cookie-based auth matching the pattern in app/api/auth/me/route.ts. | /api/analytics/platform | - No session or invalid session cookie → 401 { "error": "Unauthorized", "code": "auth_required", "status": 401 } - Valid session with app_metadata.role != 'admin' → 403 { "error": "Forbidden", "code": "admin_required", "status": 403 } - Supabase query error → 500 { "error": "Internal server error", "code": "db_error", "status": 500 } — error logged via console.error before returning | GET | No request body. No query parameters. Authentication is implicit via Supabase session cookie set by prior login. | HTTP 200 Content-Type: application/json. Body: { "hours_preserved": 42.5, "expert_count": 12, "chunk_count": 480, "verified_chunk_count": 320, "support_reduction_pct": 66.7, "cost_per_session": 0.08, "cost_per_chunk": 0.0017, "total_session_cost": 1.60 }. All values are JSON numbers, never null. |
platform_analytics_mv is a PostgreSQL MATERIALIZED VIEW created WITH DATA. Must be created AFTER knowledge_chunks table exists (added by an earlier module migration). cost_breakdown JSONB structure written by M-04 and M-05: { "deepgram": <float>, "gpt4o": <float>, "o3": <float>, "embedding": <float>, "total": <float> } — total is the authoritative sum written by M-05. Sessions with null cost_breakdown are excluded from ALL cost aggregates. COLUMN NAME NOTE: migration 001 defines capture_sessions.duration_seconds numeric(8,2); the brief references session_duration_s. The view SQL in D-10-01 uses duration_seconds (the actual column). If M-04 adds a separate session_duration_s integer column, the view SQL must be updated to use that column name.
| rls | name | indexes | purpose | key columns |
|---|---|---|---|---|
| Not applicable — materialized views do not support RLS. Access is controlled at the API layer: route handler validates admin role before any Supabase query. The view is queried using the service-role client which bypasses RLS. | platform_analytics_mv | - CREATE UNIQUE INDEX platform_analytics_mv_singleton ON platform_analytics_mv (row_id) — required for REFRESH CONCURRENTLY | Single-row materialized view pre-aggregating all platform impact and AI cost metrics; refreshed on demand to guarantee sub-2s API response at any dataset size. | - row_id integer NOT NULL DEFAULT 1 — dummy PK enabling REFRESH MATERIALIZED VIEW CONCURRENTLY - hours_preserved numeric NOT NULL DEFAULT 0 — COALESCE(SUM(duration_seconds) FILTER (WHERE status='completed'), 0) / 3600.0 - expert_count bigint NOT NULL DEFAULT 0 — COUNT(DISTINCT expert_id) FILTER (WHERE status='completed') from capture_sessions - chunk_count bigint NOT NULL DEFAULT 0 — COUNT(*) from knowledge_chunks - verified_chunk_count bigint NOT NULL DEFAULT 0 — COUNT(*) FILTER (WHERE ground_truth_verified = true) from knowledge_chunks - support_reduction_pct numeric NOT NULL DEFAULT 0 — LEA |
Platform Analytics
| id | name | type | inputs | outputs | description |
|---|---|---|---|---|---|
| D-10-01 | 002_platform_analytics_mv.sql | migration | - capture_sessions(id, expert_id, status, duration_seconds, cost_breakdown jsonb) - knowledge_chunks(id, ground_truth_verified boolean) | - Materialized view platform_analytics_mv with columns: row_id integer, hours_preserved numeric, expert_count bigint, chunk_count bigint, verified_chunk_count bigint, support_reduction_pct numeric, cost_per_session numeric, cost_per_chunk numeric, total_session_cost numeric - Unique index platform_analytics_mv_singleton ON platform_analytics_mv (row_id) — enables REFRESH CONCURRENTLY - PL/pgSQL function refresh_platform_analytics_mv() returns void | Creates the platform_analytics_mv PostgreSQL materialized view and a companion refresh function. The view aggregates a single row of platform-wide stats from capture_sessions and knowledge_chunks. A dummy integer column (row_id = 1) is included solely to enable REFRESH MATERIALIZED VIEW CONCURRENTLY, which requires a unique index. All aggregate columns are wrapped in COALESCE so the row always returns 0 (not null) even when the underlying tables are empty. support_reduction_pct = LEAST(ROUND((verified_chunk_count::numeric / NULLIF(chunk_count, 0) * 100), 1), 100) — percentage of ground-truth-v |
| D-10-02 | GET /api/analytics/platform (app/api/analytics/platform/route.ts) | api_route | - HTTP GET request — no body, no query params - Authentication: Supabase session cookie from prior /api/auth/login - Supabase user.app_metadata.role claim | - HTTP 200 JSON: { "hours_preserved": number, "expert_count": number, "chunk_count": number, "verified_chunk_count": number, "support_reduction_pct": number, "cost_per_session": number, "cost_per_chunk": number, "total_session_cost": number } — all fields JSON numbers, never null - HTTP 401 JSON: { "error": "Unauthorized", "code": "auth_required", "status": 401 } - HTTP 403 JSON: { "error": "Forbidden", "code": "admin_required", "status": 403 } - HTTP 500 JSON: { "error": "Internal server error", "code": "db_error", "status": 500 } | Next.js 14 App Router Route Handler. Uses createClient() from @/lib/supabase/server (cookie-based SSR session — no Authorization header). Auth check: call supabase.auth.getUser() — if error or no user, return 401 error envelope. Inspect user.app_metadata.role; if not equal to 'admin', return 403 error envelope. On valid admin session, query platform_analytics_mv using a service-role client (SUPABASE_SERVICE_ROLE_KEY env var) via supabase.from('platform_analytics_mv').select('*').single(). Call supabase.rpc('refresh_platform_analytics_mv') fire-and-forget (do not await — do not block response). |
| D-10-03 | PlatformAnalyticsPanel (components/analytics/PlatformAnalyticsPanel.tsx) | component | - No props — component is self-contained and fetches from /api/analytics/platform - Implicit: valid admin session cookie in browser (page-level auth guard ensures only admins reach this component) | - Rendered analytics panel: 2-row stat grid + powered-by badge - Skeleton state: 6 animated placeholder cards (shown on initial mount) - Error state: error message + Retry button (shown after 10s timeout or fetch failure) | React client component ('use client') implementing surface S-09-a. Full-width 2-row layout. Row 1 (Impact Stats): HTML element with role='region' aria-label='Impact statistics'. Three stat cards: (1) hours_preserved — label 'Hours of retiree knowledge preserved', value to 1dp; (2) support_reduction_pct — label 'Predicted tech-support call reduction', value as percentage; (3) ROI gauge — visual arc/radial gauge driven by support_reduction_pct (0–100%), label 'ROI gauge', aria-label='ROI: {value}%'. Row 2 (Cost Stats): HTML element with role='region' aria-label='Cost statistics'. Three stat card |
| D-10-04 | analytics.test.ts (apps/kv-app/__tests__/analytics.test.ts) | test_suite | - app/api/analytics/platform/route.ts (imported directly) - components/analytics/PlatformAnalyticsPanel.tsx - Mocked Supabase clients via vi.mock | - apps/kv-app/__tests__/analytics.test.ts with all tests passing | Vitest + React Testing Library test suite. Add vitest, @testing-library/react, @testing-library/jest-dom, jsdom as devDependencies if not present; add vitest.config.ts at project root if not present. Two groups: (A) API Route tests — import the GET handler, mock @/lib/supabase/server createClient and the service-role createClient; test all auth branches and data paths. (B) Component tests — render PlatformAnalyticsPanel with global fetch mocked via vi.fn(). Group A tests: no-session returns 401, expert-role returns 403, admin-role returns 200 with all 8 numeric keys, zero-state returns 200 wit |
| calls | purpose | service |
|---|---|---|
| - supabase.auth.getUser() — verifies cookie session, returns user object including app_metadata | Verify session cookie and extract app_metadata.role claim to enforce admin-only access gate | Supabase Auth |
| - supabase.from('platform_analytics_mv').select('*').single() — retrieves the single aggregate row (service-role, bypasses RLS) - supabase.rpc('refresh_platform_analytics_mv') — fire-and-forget async refresh; not awaited | Query platform_analytics_mv materialized view for aggregate stats; call refresh function | Supabase PostgreSQL (service-role client) |
- capture_sessions table exists with duration_seconds numeric(8,2) column and status text column with value 'completed' for finished sessions (from migration 001 — already present)
- capture_sessions.cost_breakdown JSONB column added by M-04, containing keys: deepgram (float), gpt4o (float) at minimum; M-05 appends o3 (float) and embedding (float) and sets total (float) = sum of all model costs
- knowledge_chunks table exists with columns: id uuid, expert_id uuid, ground_truth_verified boolean (from M-05)
- users table exists with id uuid column used as expert_id in capture_sessions (migration 001 — already present)
- Supabase Auth configured with admin role support: at least one test user has app_metadata.role = 'admin' set via Supabase service-role Admin API; role claim is included in the issued JWT
- NOTE: The brief refers to the duration column as session_duration_s; the actual column in migration 001 is duration_seconds numeric(8,2). The materialized view uses duration_seconds. If M-04 adds a separate session_duration_s integer column, Quinn must update the view SQL to use that column name instead.
No items captured.
1.0