plan-module · supabase_json
KnowledgeVault AI plan-module M-10
plan-module artifact · for KnowledgeVault AI · phase M-10 · status draft
tasks
| id | role | brief | title | completion | depends on |
|---|---|---|---|---|---|
| 1 | developer | Create apps/knowledgevault/supabase/migrations/002_platform_analytics_mv.sql. This migration creates (1) the platform_analytics_mv PostgreSQL MATERIALIZED VIEW WITH DATA, and (2) a companion PL/pgSQL refresh function. The view must aggregate a single row from capture_sessions and knowledge_chunks. Include row_id integer NOT NULL DEFAULT 1 as a dummy PK column — required to enable REFRESH MATERIALIZED VIEW CONCURRENTLY (which needs a unique index). Columns and SQL expressions — wrap all aggregates in COALESCE so they return 0, never NULL, when tables are empty: - hours_preserved numeric NOT | Migration: platform_analytics_mv materialized view | - type: file - evaluate: SQL file creates platform_analytics_mv materialized view with row_id plus 8 aggregate columns all wrapped in COALESCE (returning 0 not null), includes CREATE UNIQUE INDEX platform_analytics_mv_singleton, includes refresh_platform_analytics_mv() PL/pgSQL function, file is named 002_platform_analytics_mv.sql per sequential migration convention POL-ARCH-001, and all cost aggregates exclude NULL cost_breakdown rows. - min length: 500 | |
| 2 | developer | Create apps/knowledgevault/src/app/api/analytics/platform/route.ts — a Next.js 14 App Router route handler for the admin-only platform analytics endpoint. Auth pattern (cookie-based SSR, matching app/api/auth/me/route.ts — no Authorization header): 1. Import createClient from @/lib/supabase/server 2. Call supabase.auth.getUser(). If error or no user: return 401 JSON { error: 'Unauthorized', code: 'auth_required', status: 401 } 3. Check user.app_metadata.role — if not exactly 'admin': return 403 JSON { error: 'Forbidden', code: 'admin_required', status: 403 } Data query (service-role client — | API route: GET /api/analytics/platform | - type: file - evaluate: Route handler exports GET function; calls supabase.auth.getUser() before any DB access (POL-SEC-002 hard rule); returns 401 for no/invalid session, 403 for non-admin role, 200 with all 8 numeric fields for valid admin session; SUPABASE_SERVICE_ROLE_KEY used only in this server-side file (POL-ARCH-003 hard rule); file passes TypeScript strict mode with no bare any (POL-STYLE-001 hard rule). - min length: 300 | - 1 |
| 3 | developer | Create apps/knowledgevault/src/components/analytics/PlatformAnalyticsPanel.tsx — a React client component ('use client') implementing surface S-09-a. Fetch: useEffect on mount — GET /api/analytics/platform with AbortController timeout of 10000ms. On timeout or network error → error state. Three UI states: - loading (initial, before fetch resolves): 6 animated placeholder cards (animate-pulse class), 3 per row, matching the 2-row layout below - loaded: 2-row analytics panel (see layout below) - error: <div>Failed to load analytics</div> + <button>Retry</button>. Clicking Retry resets to loadi | Component: PlatformAnalyticsPanel admin display | - type: file - evaluate: Component file has 'use client' directive; renders 2-row panel with 6 stat cards (3 impact + 3 cost); shows animate-pulse skeleton (6 cards) on mount before fetch resolves; error state renders element with text 'Failed to load analytics' and button with text 'Retry'; powered-by badge has aria-label='Powered by KnowledgeVault'; impact section has role='region' aria-label='Impact statistics'; cost section has role='region' aria-label='Cost statistics'; stat value elements have className containing kv-stat-hero; tailwind.config.ts extended with analytics and badge colors; | - 2 |
| 4 | developer | Create apps/knowledgevault/src/__tests__/analytics/analytics.test.ts — a Vitest + React Testing Library suite covering the M-10 API route and PlatformAnalyticsPanel component. Setup: 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 (configure jsdom environment). Group A — API Route tests (import GET from src/app/api/analytics/platform/route.ts; mock @/lib/supabase/server createClient and service-role createClient via vi.mock; mock supabase.from('platform_analytics_mv').select('*').singl | Test suite: analytics API and component | - type: file - evaluate: Test file contains ≥14 named test cases; vitest run exits code 0 (all tests pass); Group A covers no-session 401, non-admin 403, admin 200 with 8 numeric keys, zero-state, hours math, and cost math (≥90% GET handler branch coverage); Group B covers skeleton on mount, loaded 6 stat cards, ROI gauge aria-label, error state after 10s abort, Retry re-triggers fetch, USD formatting of cost_per_session, and powered-by badge aria-label. - min length: 800 | - 2 - 3 |
Agent Handoff
Start Here
| id | role | brief | title | completion | depends on |
|---|---|---|---|---|---|
| 1 | developer | Create apps/knowledgevault/supabase/migrations/002_platform_analytics_mv.sql. This migration creates (1) the platform_analytics_mv PostgreSQL MATERIALIZED VIEW WITH DATA, and (2) a companion PL/pgSQL refresh function. The view must aggregate a single row from capture_sessions and knowledge_chunks. Include row_id integer NOT NULL DEFAULT 1 as a dummy PK column — required to enable REFRESH MATERIALIZED VIEW CONCURRENTLY (which needs a unique index). Columns and SQL expressions — wrap all aggregates in COALESCE so they return 0, never NULL, when tables are empty: - hours_preserved numeric NOT | Migration: platform_analytics_mv materialized view | - type: file - evaluate: SQL file creates platform_analytics_mv materialized view with row_id plus 8 aggregate columns all wrapped in COALESCE (returning 0 not null), includes CREATE UNIQUE INDEX platform_analytics_mv_singleton, includes refresh_platform_analytics_mv() PL/pgSQL function, file is named 002_platform_analytics_mv.sql per sequential migration convention POL-ARCH-001, and all cost aggregates exclude NULL cost_breakdown rows. - min length: 500 | |
| 2 | developer | Create apps/knowledgevault/src/app/api/analytics/platform/route.ts — a Next.js 14 App Router route handler for the admin-only platform analytics endpoint. Auth pattern (cookie-based SSR, matching app/api/auth/me/route.ts — no Authorization header): 1. Import createClient from @/lib/supabase/server 2. Call supabase.auth.getUser(). If error or no user: return 401 JSON { error: 'Unauthorized', code: 'auth_required', status: 401 } 3. Check user.app_metadata.role — if not exactly 'admin': return 403 JSON { error: 'Forbidden', code: 'admin_required', status: 403 } Data query (service-role client — | API route: GET /api/analytics/platform | - type: file - evaluate: Route handler exports GET function; calls supabase.auth.getUser() before any DB access (POL-SEC-002 hard rule); returns 401 for no/invalid session, 403 for non-admin role, 200 with all 8 numeric fields for valid admin session; SUPABASE_SERVICE_ROLE_KEY used only in this server-side file (POL-ARCH-003 hard rule); file passes TypeScript strict mode with no bare any (POL-STYLE-001 hard rule). - min length: 300 | - 1 |
| 3 | developer | Create apps/knowledgevault/src/components/analytics/PlatformAnalyticsPanel.tsx — a React client component ('use client') implementing surface S-09-a. Fetch: useEffect on mount — GET /api/analytics/platform with AbortController timeout of 10000ms. On timeout or network error → error state. Three UI states: - loading (initial, before fetch resolves): 6 animated placeholder cards (animate-pulse class), 3 per row, matching the 2-row layout below - loaded: 2-row analytics panel (see layout below) - error: <div>Failed to load analytics</div> + <button>Retry</button>. Clicking Retry resets to loadi | Component: PlatformAnalyticsPanel admin display | - type: file - evaluate: Component file has 'use client' directive; renders 2-row panel with 6 stat cards (3 impact + 3 cost); shows animate-pulse skeleton (6 cards) on mount before fetch resolves; error state renders element with text 'Failed to load analytics' and button with text 'Retry'; powered-by badge has aria-label='Powered by KnowledgeVault'; impact section has role='region' aria-label='Impact statistics'; cost section has role='region' aria-label='Cost statistics'; stat value elements have className containing kv-stat-hero; tailwind.config.ts extended with analytics and badge colors; | - 2 |
| 4 | developer | Create apps/knowledgevault/src/__tests__/analytics/analytics.test.ts — a Vitest + React Testing Library suite covering the M-10 API route and PlatformAnalyticsPanel component. Setup: 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 (configure jsdom environment). Group A — API Route tests (import GET from src/app/api/analytics/platform/route.ts; mock @/lib/supabase/server createClient and service-role createClient via vi.mock; mock supabase.from('platform_analytics_mv').select('*').singl | Test suite: analytics API and component | - type: file - evaluate: Test file contains ≥14 named test cases; vitest run exits code 0 (all tests pass); Group A covers no-session 401, non-admin 403, admin 200 with 8 numeric keys, zero-state, hours math, and cost math (≥90% GET handler branch coverage); Group B covers skeleton on mount, loaded 6 stat cards, ROI gauge aria-label, error state after 10s abort, Retry re-triggers fetch, USD formatting of cost_per_session, and powered-by badge aria-label. - min length: 800 | - 2 - 3 |
Completion Evidence
No explicit evidence field yet. Require tests, screenshots, linked PRs, or reviewed outputs before marking complete.
Artifact Shape
- tasks: 4 items
- module id: M-10
- module name: Platform Analytics
- schema version: 1.0
- depends on modules: 1 item
Structured Payload
Machine-readable source fields
tasks
| id | role | brief | title | completion | depends on |
|---|---|---|---|---|---|
| 1 | developer | Create apps/knowledgevault/supabase/migrations/002_platform_analytics_mv.sql. This migration creates (1) the platform_analytics_mv PostgreSQL MATERIALIZED VIEW WITH DATA, and (2) a companion PL/pgSQL refresh function. The view must aggregate a single row from capture_sessions and knowledge_chunks. Include row_id integer NOT NULL DEFAULT 1 as a dummy PK column — required to enable REFRESH MATERIALIZED VIEW CONCURRENTLY (which needs a unique index). Columns and SQL expressions — wrap all aggregates in COALESCE so they return 0, never NULL, when tables are empty: - hours_preserved numeric NOT | Migration: platform_analytics_mv materialized view | - type: file - evaluate: SQL file creates platform_analytics_mv materialized view with row_id plus 8 aggregate columns all wrapped in COALESCE (returning 0 not null), includes CREATE UNIQUE INDEX platform_analytics_mv_singleton, includes refresh_platform_analytics_mv() PL/pgSQL function, file is named 002_platform_analytics_mv.sql per sequential migration convention POL-ARCH-001, and all cost aggregates exclude NULL cost_breakdown rows. - min length: 500 | |
| 2 | developer | Create apps/knowledgevault/src/app/api/analytics/platform/route.ts — a Next.js 14 App Router route handler for the admin-only platform analytics endpoint. Auth pattern (cookie-based SSR, matching app/api/auth/me/route.ts — no Authorization header): 1. Import createClient from @/lib/supabase/server 2. Call supabase.auth.getUser(). If error or no user: return 401 JSON { error: 'Unauthorized', code: 'auth_required', status: 401 } 3. Check user.app_metadata.role — if not exactly 'admin': return 403 JSON { error: 'Forbidden', code: 'admin_required', status: 403 } Data query (service-role client — | API route: GET /api/analytics/platform | - type: file - evaluate: Route handler exports GET function; calls supabase.auth.getUser() before any DB access (POL-SEC-002 hard rule); returns 401 for no/invalid session, 403 for non-admin role, 200 with all 8 numeric fields for valid admin session; SUPABASE_SERVICE_ROLE_KEY used only in this server-side file (POL-ARCH-003 hard rule); file passes TypeScript strict mode with no bare any (POL-STYLE-001 hard rule). - min length: 300 | - 1 |
| 3 | developer | Create apps/knowledgevault/src/components/analytics/PlatformAnalyticsPanel.tsx — a React client component ('use client') implementing surface S-09-a. Fetch: useEffect on mount — GET /api/analytics/platform with AbortController timeout of 10000ms. On timeout or network error → error state. Three UI states: - loading (initial, before fetch resolves): 6 animated placeholder cards (animate-pulse class), 3 per row, matching the 2-row layout below - loaded: 2-row analytics panel (see layout below) - error: <div>Failed to load analytics</div> + <button>Retry</button>. Clicking Retry resets to loadi | Component: PlatformAnalyticsPanel admin display | - type: file - evaluate: Component file has 'use client' directive; renders 2-row panel with 6 stat cards (3 impact + 3 cost); shows animate-pulse skeleton (6 cards) on mount before fetch resolves; error state renders element with text 'Failed to load analytics' and button with text 'Retry'; powered-by badge has aria-label='Powered by KnowledgeVault'; impact section has role='region' aria-label='Impact statistics'; cost section has role='region' aria-label='Cost statistics'; stat value elements have className containing kv-stat-hero; tailwind.config.ts extended with analytics and badge colors; | - 2 |
| 4 | developer | Create apps/knowledgevault/src/__tests__/analytics/analytics.test.ts — a Vitest + React Testing Library suite covering the M-10 API route and PlatformAnalyticsPanel component. Setup: 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 (configure jsdom environment). Group A — API Route tests (import GET from src/app/api/analytics/platform/route.ts; mock @/lib/supabase/server createClient and service-role createClient via vi.mock; mock supabase.from('platform_analytics_mv').select('*').singl | Test suite: analytics API and component | - type: file - evaluate: Test file contains ≥14 named test cases; vitest run exits code 0 (all tests pass); Group A covers no-session 401, non-admin 403, admin 200 with 8 numeric keys, zero-state, hours math, and cost math (≥90% GET handler branch coverage); Group B covers skeleton on mount, loaded 6 stat cards, ROI gauge aria-label, error state after 10s abort, Retry re-triggers fetch, USD formatting of cost_per_session, and powered-by badge aria-label. - min length: 800 | - 2 - 3 |
module id
M-10
module name
Platform Analytics
schema version
1.0
depends on modules
- M-05