plan-module · supabase_json
KnowledgeVault AI plan-module M-06
plan-module artifact · for KnowledgeVault AI · phase M-06 · status draft
tasks
| id | role | brief | title | completion | depends on |
|---|---|---|---|---|---|
| 1 | developer | Create apps/knowledgevault/supabase/migrations/006_payment_release_triggers.sql. The database already has M-00 through M-05 applied (payments, experts, capture_sessions, products, pg_cron all exist). Do NOT modify the pg_cron job 'release-held-payments' — it belongs to M-00.
Migration steps (all idempotent):
1. CREATE EXTENSION IF NOT EXISTS pg_net;
2. ALTER TABLE payments ADD COLUMN IF NOT EXISTS transfer_attempt_count integer NOT NULL DEFAULT 0;
3. `ALTER TABLE experts ADD COLUMN IF NOT EXISTS push_token text NULL; -- nullable: set by expert app on login via POST /api/experts/:id/push | DB migration: payment release triggers and columns | - type: file - evaluate: SQL file applies without error on M-00..M-05 database: pg_net extension enabled, transfer_attempt_count/push_token/stripe_payouts_enabled columns created, both trigger functions and both triggers exist per information_schema checks in D-06-01 acceptance criteria; migration is idempotent and does not modify existing migrations (POL-ARCH-001). - min length: 800 | |
| 2 | developer | Create two internal Next.js 15 API route files. These routes are called only by pg_net DB triggers — never by client apps.
File 1: apps/knowledgevault/apps/web/src/app/api/internal/payments/release/route.ts
POST handler. Validate Authorization: Bearer <INTERNAL_API_SECRET> (process.env.INTERNAL_API_SECRET) — 401 {error:'Unauthorized',code:'auth/missing-token',status:401} on mismatch. Accept body {payment_id: uuid, expert_id: uuid}. Fetch expert (stripe_account_id, stripe_payouts_enabled, push_token, name) and payment (total_amount, session_id) from DB. Fetch product name: payments | Internal payment release and hold notification routes | - type: file - evaluate: Both internal routes enforce INTERNAL_API_SECRET bearer auth (401 on mismatch), handle the stripe_account_id/payouts_enabled deferral case, correctly increment transfer_attempt_count and escalate to payment_hold at count=3, send Expo push when push_token is set, and send Resend admin email on payment_hold — all per D-06-02 acceptance criteria; TypeScript strict mode (POL-STYLE-001); no secrets in source (POL-SEC-004). - min length: 600 | - 1 |
| 3 | developer | Create apps/knowledgevault/apps/web/src/app/api/experts/[id]/earnings/route.ts in a Next.js 15 app.
GET handler. Auth validation:
1. Call supabase.auth.getUser() — 401 {error:'Unauthorized',code:'auth/missing-token',status:401} on missing/invalid JWT.
2. If user.user_metadata.role !== 'expert' — 401 {error:'Unauthorized',code:'auth/wrong-role',status:401}.
3. If user.id !== params.id — 403 {error:'Forbidden',code:'auth/forbidden',status:403}.
4. SELECT id FROM experts WHERE id=params.id — 404 {error:'Expert not found',code:'expert/not-found',status:404} if no row.
Query (usin | GET /api/experts/:id/earnings endpoint | - type: file - evaluate: GET /api/experts/:id/earnings enforces JWT auth with getUser(), returns correct total_earned/pending totals and sessions array per D-06-03 acceptance criteria (including empty sessions array not null, and 401/403/404 error shapes); TypeScript strict mode (POL-STYLE-001); getUser() call present (POL-SEC-002). - min length: 400 | - 1 |
| 4 | developer | Create apps/knowledgevault/apps/web/src/app/api/webhooks/stripe/route.ts in a Next.js 15 app.
CRITICAL: Raw request body must NOT be parsed by Next.js body middleware before this handler. In Next.js 15 App Router, read raw body via const rawBody = await req.text(). Stripe signature verification requires the exact raw bytes — parsed JSON body will fail verification.
POST handler:
1. Read stripe-signature header: request.headers.get('stripe-signature').
2. Verify: stripe.webhooks.constructEvent(rawBody, stripeSignature, process.env.STRIPE_WEBHOOK_SECRET) — return 400 `{error:'Webhook | POST /api/webhooks/stripe handler | - type: file - evaluate: POST /api/webhooks/stripe verifies stripe-signature via constructEvent() before any DB writes, handles transfer.paid and account.updated events correctly, is idempotent, returns 200 {received:true} for all event types including unhandled, and returns 400 on signature failure — per D-06-04 acceptance criteria (POL-PAY-002); no secrets in source (POL-SEC-004). - min length: 300 | - 1 |
| 5 | developer | Create three React Native (Expo) files:
apps/knowledgevault/apps/mobile/src/screens/EarningsDashboard.tsx (screen S-05-a):
- On mount, call GET /api/experts/:id/earnings with Authorization: Bearer <expertJwt> from auth context. Start 10s timeout on mount.
- Immediately render skeleton: tall card placeholder + 3 row placeholders (use animated shimmer or ActivityIndicator-based skeleton matching final component dimensions).
- If no response within 10s: dismiss skeleton, show Text 'Something went wrong' + Button 'Try again' that retries the request.
- On API success with sessions.lengt | EarningsDashboard screen and earnings components | - type: file - evaluate: EarningsDashboard renders skeleton on mount, EarningsSummaryCard shows correct totals and Stripe CTA logic, SessionEarningsRow renders correct badge text/colors per status and correct accessibilityLabel, empty state renders wallet icon and both text strings with no button, error state shows after 10s timeout — per all D-06-05 acceptance criteria; TypeScript strict mode (POL-STYLE-001). - min length: 400 | |
| 6 | developer | Create apps/knowledgevault/apps/web/src/app/api/experts/[id]/push-token/route.ts in a Next.js 15 App Router project.
Purpose: Expert mobile app calls this on login to register their Expo push notification device token. Resolves OQ-M-06-01 (dedicated push-token route, not an existing PATCH endpoint). Token is stored in experts.push_token (text NULL column added by migration 006_payment_release_triggers.sql in Task 1 of this module).
POST handler:
1. JWT auth: call supabase.auth.getUser() — 401 {error:'Unauthorized',code:'auth/missing-token',status:401} on missing/invalid JWT.
2. Role | POST /api/experts/:id/push-token registration route | - type: file - evaluate: POST /api/experts/:id/push-token validates JWT via getUser(), enforces ownership check, rejects missing/empty push_token with 400, writes push_token to experts table, returns correct 401/403/404/200 status codes; TypeScript strict (POL-STYLE-001); getUser() call present (POL-SEC-002). - min length: 200 | - 1 |
Agent Handoff
Start Here
| id | role | brief | title | completion | depends on |
|---|---|---|---|---|---|
| 1 | developer | Create apps/knowledgevault/supabase/migrations/006_payment_release_triggers.sql. The database already has M-00 through M-05 applied (payments, experts, capture_sessions, products, pg_cron all exist). Do NOT modify the pg_cron job 'release-held-payments' — it belongs to M-00.
Migration steps (all idempotent):
1. CREATE EXTENSION IF NOT EXISTS pg_net;
2. ALTER TABLE payments ADD COLUMN IF NOT EXISTS transfer_attempt_count integer NOT NULL DEFAULT 0;
3. `ALTER TABLE experts ADD COLUMN IF NOT EXISTS push_token text NULL; -- nullable: set by expert app on login via POST /api/experts/:id/push | DB migration: payment release triggers and columns | - type: file - evaluate: SQL file applies without error on M-00..M-05 database: pg_net extension enabled, transfer_attempt_count/push_token/stripe_payouts_enabled columns created, both trigger functions and both triggers exist per information_schema checks in D-06-01 acceptance criteria; migration is idempotent and does not modify existing migrations (POL-ARCH-001). - min length: 800 | |
| 2 | developer | Create two internal Next.js 15 API route files. These routes are called only by pg_net DB triggers — never by client apps.
File 1: apps/knowledgevault/apps/web/src/app/api/internal/payments/release/route.ts
POST handler. Validate Authorization: Bearer <INTERNAL_API_SECRET> (process.env.INTERNAL_API_SECRET) — 401 {error:'Unauthorized',code:'auth/missing-token',status:401} on mismatch. Accept body {payment_id: uuid, expert_id: uuid}. Fetch expert (stripe_account_id, stripe_payouts_enabled, push_token, name) and payment (total_amount, session_id) from DB. Fetch product name: payments | Internal payment release and hold notification routes | - type: file - evaluate: Both internal routes enforce INTERNAL_API_SECRET bearer auth (401 on mismatch), handle the stripe_account_id/payouts_enabled deferral case, correctly increment transfer_attempt_count and escalate to payment_hold at count=3, send Expo push when push_token is set, and send Resend admin email on payment_hold — all per D-06-02 acceptance criteria; TypeScript strict mode (POL-STYLE-001); no secrets in source (POL-SEC-004). - min length: 600 | - 1 |
| 3 | developer | Create apps/knowledgevault/apps/web/src/app/api/experts/[id]/earnings/route.ts in a Next.js 15 app.
GET handler. Auth validation:
1. Call supabase.auth.getUser() — 401 {error:'Unauthorized',code:'auth/missing-token',status:401} on missing/invalid JWT.
2. If user.user_metadata.role !== 'expert' — 401 {error:'Unauthorized',code:'auth/wrong-role',status:401}.
3. If user.id !== params.id — 403 {error:'Forbidden',code:'auth/forbidden',status:403}.
4. SELECT id FROM experts WHERE id=params.id — 404 {error:'Expert not found',code:'expert/not-found',status:404} if no row.
Query (usin | GET /api/experts/:id/earnings endpoint | - type: file - evaluate: GET /api/experts/:id/earnings enforces JWT auth with getUser(), returns correct total_earned/pending totals and sessions array per D-06-03 acceptance criteria (including empty sessions array not null, and 401/403/404 error shapes); TypeScript strict mode (POL-STYLE-001); getUser() call present (POL-SEC-002). - min length: 400 | - 1 |
| 4 | developer | Create apps/knowledgevault/apps/web/src/app/api/webhooks/stripe/route.ts in a Next.js 15 app.
CRITICAL: Raw request body must NOT be parsed by Next.js body middleware before this handler. In Next.js 15 App Router, read raw body via const rawBody = await req.text(). Stripe signature verification requires the exact raw bytes — parsed JSON body will fail verification.
POST handler:
1. Read stripe-signature header: request.headers.get('stripe-signature').
2. Verify: stripe.webhooks.constructEvent(rawBody, stripeSignature, process.env.STRIPE_WEBHOOK_SECRET) — return 400 `{error:'Webhook | POST /api/webhooks/stripe handler | - type: file - evaluate: POST /api/webhooks/stripe verifies stripe-signature via constructEvent() before any DB writes, handles transfer.paid and account.updated events correctly, is idempotent, returns 200 {received:true} for all event types including unhandled, and returns 400 on signature failure — per D-06-04 acceptance criteria (POL-PAY-002); no secrets in source (POL-SEC-004). - min length: 300 | - 1 |
| 5 | developer | Create three React Native (Expo) files:
apps/knowledgevault/apps/mobile/src/screens/EarningsDashboard.tsx (screen S-05-a):
- On mount, call GET /api/experts/:id/earnings with Authorization: Bearer <expertJwt> from auth context. Start 10s timeout on mount.
- Immediately render skeleton: tall card placeholder + 3 row placeholders (use animated shimmer or ActivityIndicator-based skeleton matching final component dimensions).
- If no response within 10s: dismiss skeleton, show Text 'Something went wrong' + Button 'Try again' that retries the request.
- On API success with sessions.lengt | EarningsDashboard screen and earnings components | - type: file - evaluate: EarningsDashboard renders skeleton on mount, EarningsSummaryCard shows correct totals and Stripe CTA logic, SessionEarningsRow renders correct badge text/colors per status and correct accessibilityLabel, empty state renders wallet icon and both text strings with no button, error state shows after 10s timeout — per all D-06-05 acceptance criteria; TypeScript strict mode (POL-STYLE-001). - min length: 400 | |
| 6 | developer | Create apps/knowledgevault/apps/web/src/app/api/experts/[id]/push-token/route.ts in a Next.js 15 App Router project.
Purpose: Expert mobile app calls this on login to register their Expo push notification device token. Resolves OQ-M-06-01 (dedicated push-token route, not an existing PATCH endpoint). Token is stored in experts.push_token (text NULL column added by migration 006_payment_release_triggers.sql in Task 1 of this module).
POST handler:
1. JWT auth: call supabase.auth.getUser() — 401 {error:'Unauthorized',code:'auth/missing-token',status:401} on missing/invalid JWT.
2. Role | POST /api/experts/:id/push-token registration route | - type: file - evaluate: POST /api/experts/:id/push-token validates JWT via getUser(), enforces ownership check, rejects missing/empty push_token with 400, writes push_token to experts table, returns correct 401/403/404/200 status codes; TypeScript strict (POL-STYLE-001); getUser() call present (POL-SEC-002). - min length: 200 | - 1 |
Completion Evidence
No explicit evidence field yet. Require tests, screenshots, linked PRs, or reviewed outputs before marking complete.
Artifact Shape
- notes: OQ-M-06-01 resolved: added Task 6 for POST /api/experts/:id/push-token (dedicated route to register Expo push token). OQ-M-06-02 resolved: noreply@knowledgevault.ai confirmed as Resend sender domain. Tasks 2–6 all depend on Task 1 (migration must be applied before any route writes to new columns). Tasks 2, 3, 4, 6 can run in parallel once Task 1 ships. Task 5 (UI) can run in parallel with all others — API contract is fully specified in brief.
- tasks: 6 items
- module id: M-06
- module name: Payment & Earnings
- schema version: 1.0
- depends on modules: 1 item
Structured Payload
Machine-readable source fields
notes
OQ-M-06-01 resolved: added Task 6 for POST /api/experts/:id/push-token (dedicated route to register Expo push token). OQ-M-06-02 resolved: noreply@knowledgevault.ai confirmed as Resend sender domain. Tasks 2–6 all depend on Task 1 (migration must be applied before any route writes to new columns). Tasks 2, 3, 4, 6 can run in parallel once Task 1 ships. Task 5 (UI) can run in parallel with all others — API contract is fully specified in brief.
tasks
| id | role | brief | title | completion | depends on |
|---|---|---|---|---|---|
| 1 | developer | Create apps/knowledgevault/supabase/migrations/006_payment_release_triggers.sql. The database already has M-00 through M-05 applied (payments, experts, capture_sessions, products, pg_cron all exist). Do NOT modify the pg_cron job 'release-held-payments' — it belongs to M-00.
Migration steps (all idempotent):
1. CREATE EXTENSION IF NOT EXISTS pg_net;
2. ALTER TABLE payments ADD COLUMN IF NOT EXISTS transfer_attempt_count integer NOT NULL DEFAULT 0;
3. `ALTER TABLE experts ADD COLUMN IF NOT EXISTS push_token text NULL; -- nullable: set by expert app on login via POST /api/experts/:id/push | DB migration: payment release triggers and columns | - type: file - evaluate: SQL file applies without error on M-00..M-05 database: pg_net extension enabled, transfer_attempt_count/push_token/stripe_payouts_enabled columns created, both trigger functions and both triggers exist per information_schema checks in D-06-01 acceptance criteria; migration is idempotent and does not modify existing migrations (POL-ARCH-001). - min length: 800 | |
| 2 | developer | Create two internal Next.js 15 API route files. These routes are called only by pg_net DB triggers — never by client apps.
File 1: apps/knowledgevault/apps/web/src/app/api/internal/payments/release/route.ts
POST handler. Validate Authorization: Bearer <INTERNAL_API_SECRET> (process.env.INTERNAL_API_SECRET) — 401 {error:'Unauthorized',code:'auth/missing-token',status:401} on mismatch. Accept body {payment_id: uuid, expert_id: uuid}. Fetch expert (stripe_account_id, stripe_payouts_enabled, push_token, name) and payment (total_amount, session_id) from DB. Fetch product name: payments | Internal payment release and hold notification routes | - type: file - evaluate: Both internal routes enforce INTERNAL_API_SECRET bearer auth (401 on mismatch), handle the stripe_account_id/payouts_enabled deferral case, correctly increment transfer_attempt_count and escalate to payment_hold at count=3, send Expo push when push_token is set, and send Resend admin email on payment_hold — all per D-06-02 acceptance criteria; TypeScript strict mode (POL-STYLE-001); no secrets in source (POL-SEC-004). - min length: 600 | - 1 |
| 3 | developer | Create apps/knowledgevault/apps/web/src/app/api/experts/[id]/earnings/route.ts in a Next.js 15 app.
GET handler. Auth validation:
1. Call supabase.auth.getUser() — 401 {error:'Unauthorized',code:'auth/missing-token',status:401} on missing/invalid JWT.
2. If user.user_metadata.role !== 'expert' — 401 {error:'Unauthorized',code:'auth/wrong-role',status:401}.
3. If user.id !== params.id — 403 {error:'Forbidden',code:'auth/forbidden',status:403}.
4. SELECT id FROM experts WHERE id=params.id — 404 {error:'Expert not found',code:'expert/not-found',status:404} if no row.
Query (usin | GET /api/experts/:id/earnings endpoint | - type: file - evaluate: GET /api/experts/:id/earnings enforces JWT auth with getUser(), returns correct total_earned/pending totals and sessions array per D-06-03 acceptance criteria (including empty sessions array not null, and 401/403/404 error shapes); TypeScript strict mode (POL-STYLE-001); getUser() call present (POL-SEC-002). - min length: 400 | - 1 |
| 4 | developer | Create apps/knowledgevault/apps/web/src/app/api/webhooks/stripe/route.ts in a Next.js 15 app.
CRITICAL: Raw request body must NOT be parsed by Next.js body middleware before this handler. In Next.js 15 App Router, read raw body via const rawBody = await req.text(). Stripe signature verification requires the exact raw bytes — parsed JSON body will fail verification.
POST handler:
1. Read stripe-signature header: request.headers.get('stripe-signature').
2. Verify: stripe.webhooks.constructEvent(rawBody, stripeSignature, process.env.STRIPE_WEBHOOK_SECRET) — return 400 `{error:'Webhook | POST /api/webhooks/stripe handler | - type: file - evaluate: POST /api/webhooks/stripe verifies stripe-signature via constructEvent() before any DB writes, handles transfer.paid and account.updated events correctly, is idempotent, returns 200 {received:true} for all event types including unhandled, and returns 400 on signature failure — per D-06-04 acceptance criteria (POL-PAY-002); no secrets in source (POL-SEC-004). - min length: 300 | - 1 |
| 5 | developer | Create three React Native (Expo) files:
apps/knowledgevault/apps/mobile/src/screens/EarningsDashboard.tsx (screen S-05-a):
- On mount, call GET /api/experts/:id/earnings with Authorization: Bearer <expertJwt> from auth context. Start 10s timeout on mount.
- Immediately render skeleton: tall card placeholder + 3 row placeholders (use animated shimmer or ActivityIndicator-based skeleton matching final component dimensions).
- If no response within 10s: dismiss skeleton, show Text 'Something went wrong' + Button 'Try again' that retries the request.
- On API success with sessions.lengt | EarningsDashboard screen and earnings components | - type: file - evaluate: EarningsDashboard renders skeleton on mount, EarningsSummaryCard shows correct totals and Stripe CTA logic, SessionEarningsRow renders correct badge text/colors per status and correct accessibilityLabel, empty state renders wallet icon and both text strings with no button, error state shows after 10s timeout — per all D-06-05 acceptance criteria; TypeScript strict mode (POL-STYLE-001). - min length: 400 | |
| 6 | developer | Create apps/knowledgevault/apps/web/src/app/api/experts/[id]/push-token/route.ts in a Next.js 15 App Router project.
Purpose: Expert mobile app calls this on login to register their Expo push notification device token. Resolves OQ-M-06-01 (dedicated push-token route, not an existing PATCH endpoint). Token is stored in experts.push_token (text NULL column added by migration 006_payment_release_triggers.sql in Task 1 of this module).
POST handler:
1. JWT auth: call supabase.auth.getUser() — 401 {error:'Unauthorized',code:'auth/missing-token',status:401} on missing/invalid JWT.
2. Role | POST /api/experts/:id/push-token registration route | - type: file - evaluate: POST /api/experts/:id/push-token validates JWT via getUser(), enforces ownership check, rejects missing/empty push_token with 400, writes push_token to experts table, returns correct 401/403/404/200 status codes; TypeScript strict (POL-STYLE-001); getUser() call present (POL-SEC-002). - min length: 200 | - 1 |
module id
M-06
module name
Payment & Earnings
schema version
1.0
depends on modules
- M-05