module-spec · supabase_json

KnowledgeVault AI module-spec M-06

Planning Surface

Use this to decide what happens next.

Status

approved

Phase

M-06

open questions

id	owner	blocks	question	resolved	resolution
OQ-M-06-01	justin	D-06-02 (push notification requires push_token in experts table) and D-06-05 (EarningsDashboard assumes token is registered before payment is released)	Does the expert mobile app register the Expo push token on login via an existing PATCH /api/experts/:id endpoint, or does M-06 need to add a dedicated POST /api/experts/:id/push-token route?	true	Add dedicated POST /api/experts/:id/push-token route in M-06 (Priya default).
OQ-M-06-02	justin	D-06-02 (Resend send call fails if the from-domain is not verified in the Resend account)	Is noreply@knowledgevault.ai verified as a Resend sender domain, or should a different from-address be used for the payment_hold admin alert email?	true	Resend confirmed. Use noreply@knowledgevault.ai — domain must be verified in Resend before deploy.

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-06-01: id: string, owner: string, blocks: string, question: string, resolved: boolean, resolution: string
OQ-M-06-02: id: string, owner: string, blocks: string, question: string, resolved: boolean, resolution: string

Structured Payload

Machine-readable source fields

summary

M-06 delivers the expert earnings pipeline on top of the payment schema established in M-00 and the payment records created in M-05. It adds a DB trigger that fires immediately when the M-00 pg_cron job releases a payment, calling an internal API route that initiates a Stripe Connect transfer to the expert's bank account and sends a push notification. A Stripe webhook handler finalises payment status to 'paid' when the transfer settles and keeps stripe_payouts_enabled current when Stripe fires account.updated. A payment_hold trigger emails the admin within 60s whenever a payment enters hold status. The earnings API gives each expert a JWT-scoped summary of total earned, pending balance, and per-session breakdown. The Expert Earnings Dashboard (S-05-a) surfaces all of this on mobile with skeleton loading, hold countdowns, and a Stripe onboarding CTA for experts who have not yet connected a bank account.

module id

M-06

api routes

auth	path	errors	method	request	response
Expert JWT required — Authorization: Bearer <token>; JWT must have role=expert and sub must equal :id	/api/experts/:id/earnings	- No or invalid JWT -> 401 {error:'Unauthorized', code:'auth/missing-token', status:401} - JWT role != expert -> 401 {error:'Unauthorized', code:'auth/wrong-role', status:401} - JWT sub != :id -> 403 {error:'Forbidden', code:'auth/forbidden', status:403} - Expert UUID not found -> 404 {error:'Expert not found', code:'expert/not-found', status:404} - DB error -> 500 {error:'Internal server error', code:'server/db-error', status:500}	GET	Path param: id (uuid). No query params. No request body.	{total_earned: number, pending: number, sessions: [{session_id: uuid, product_name: string, sku: string, base_amount: number, surge_bonus: number, rarity_bonus: number, completion_bonus: number, quality_multiplier: number, total_amount: number, status: string, composite_score: number\|null, hold_until: string\|null, captured_at: string}]} ordered by captured_at DESC
Stripe-Signature header validated via stripe.webhooks.constructEvent() — no JWT	/api/webhooks/stripe	- Missing or invalid stripe-signature -> 400 {error:'Webhook signature verification failed', code:'webhook/invalid-signature', status:400} - DB write error -> 500 {error:'Internal server error', code:'server/db-error', status:500}	POST	Raw Stripe event body (Buffer, not parsed JSON). stripe-signature header required.	{received: true}
Authorization: Bearer <INTERNAL_API_SECRET> — internal use only, never called by client apps	/api/internal/payments/release	- Missing or invalid bearer -> 401 {error:'Unauthorized', code:'auth/missing-token', status:401} - payment_id not found -> 404 {error:'Payment not found', code:'payment/not-found', status:404} - Unhandled exception -> 500 {error:'Internal server error', code:'server/unhandled', status:500}	POST	{payment_id: uuid, expert_id: uuid}	{success: true} \| {success: true, deferred: 'no_stripe_account'} \| {success: false, error: string}
Authorization: Bearer <INTERNAL_API_SECRET> — internal use only	/api/internal/notifications/payment-hold	- Missing or invalid bearer -> 401 {error:'Unauthorized', code:'auth/missing-token', status:401} - Resend API failure -> 500 {error:'Notification failed', code:'notify/email-failed', status:500}	POST	{payment_id: uuid, expert_id: uuid}	{success: true}

data model

notes

No new tables are created in M-06. The on_payment_released() trigger fires synchronously within the same transaction as the M-00 pg_cron UPDATE. pg_net.http_post() is non-blocking — it enqueues the HTTP call and returns immediately so the pg_cron transaction is not held open. The on_payment_hold() trigger guards against re-firing: IF TG_OP='UPDATE' AND OLD.status='payment_hold' THEN RETURN NEW END IF — prevents infinite loop when D-06-02 sets status back to payment_hold after 3 Stripe failures (the first payment_hold trigger already fired). transfer_attempt_count is never reset — once a payment reaches count=3 it enters payment_hold for manual admin resolution. The M-00 cron job 'release-held-payments' is NOT modified by M-06; the trigger pattern cleanly separates the state machine (M-00 cron) from the side effects (M-06 triggers + D-06-02 service).

tables

rls	name	indexes	purpose	key columns
Expert JWT can SELECT own rows (expert_id=auth.uid()). No INSERT/UPDATE/DELETE for expert JWT — all writes via service-key routes and PL/pgSQL trigger functions. Service role has full access.	payments	- btree on expert_id — existing M-00, used by GET /api/experts/:id/earnings - btree UNIQUE on session_id — existing M-00 - btree on (status, hold_until) WHERE status='pending_release' — existing M-00, used by pg_cron release job	Expert payment records — one per capture session; M-00 defines base schema; M-06 adds transfer_attempt_count for Stripe retry tracking	- id uuid NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY (existing M-00) - expert_id uuid NOT NULL REFERENCES experts(id) (existing M-00) - session_id uuid NOT NULL REFERENCES capture_sessions(id) UNIQUE (existing M-00) - status text NOT NULL DEFAULT 'pending_release' CHECK (status IN ('pending_release','released','paid','payment_hold','disputed')) (existing M-00) - hold_until timestamptz NULL — set by M-05 using EVALUATION_HOLD_HOURS env var (existing M-00) - base_amount numeric(10,2) NOT NULL (existing M-00) - surge_bonus numeric(10,2) NOT NULL DEFAULT 0.00 (existing M-00) - rarity_bonus num
Expert JWT can SELECT and UPDATE own row (id=auth.uid()) — includes push_token and stripe_payouts_enabled. Service role full access.	experts	- btree UNIQUE on email — existing M-00	Expert profiles — M-00 defines base schema; M-06 adds push_token for Expo push notifications and stripe_payouts_enabled to track Stripe Connect onboarding completion	- id uuid NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY (existing M-00) - name text NOT NULL (existing M-00) - email text NOT NULL UNIQUE (existing M-00) - stripe_account_id text NULL (existing M-00, written in M-01) - created_at timestamptz NOT NULL DEFAULT now() (existing M-00) - push_token text NULL — NEW in M-06 D-06-01; Expo push token written by expert app on login via PATCH /api/experts/:id - stripe_payouts_enabled boolean NOT NULL DEFAULT false — NEW in M-06 D-06-01; set true by Stripe account.updated webhook when event.data.object.payouts_enabled=true

module name

Payment & Earnings

deliverables

id	name	type	inputs	outputs	description
D-06-01	supabase/migrations/006_payment_release_triggers.sql	migration	- M-00 through M-05 migrations applied — payments, experts, capture_sessions, products all exist - pg_net extension available in Supabase project tier - app.internal_api_base_url and app.internal_api_secret set in DB via ALTER DATABASE before migration runs	- pg_net extension active - payments.transfer_attempt_count column (integer not null default 0) - experts.push_token column (text nullable) - experts.stripe_payouts_enabled column (boolean not null default false) - on_payment_released() PL/pgSQL function - payment_released_trigger AFTER UPDATE trigger on payments - on_payment_hold() PL/pgSQL function - payment_hold_trigger AFTER INSERT OR UPDATE trigger on payments	Enables pg_net extension (CREATE EXTENSION IF NOT EXISTS pg_net). Adds three new columns: ALTER TABLE payments ADD COLUMN IF NOT EXISTS transfer_attempt_count integer NOT NULL DEFAULT 0; ALTER TABLE experts ADD COLUMN IF NOT EXISTS push_token text NULL; ALTER TABLE experts ADD COLUMN IF NOT EXISTS stripe_payouts_enabled boolean NOT NULL DEFAULT false. Creates PL/pgSQL function on_payment_released(): reads app.internal_api_base_url and app.internal_api_secret from current_setting(); calls pg_net.http_post(url := base_url \|\| '/api/internal/payments/release', body := json_build_object('payment_id
D-06-02	Internal Release Handler: POST /api/internal/payments/release + POST /api/internal/notifications/payment-hold	service	- {payment_id: uuid, expert_id: uuid} in POST body - INTERNAL_API_SECRET for bearer validation - STRIPE_SECRET_KEY for Stripe SDK initialisation - EXPO_ACCESS_TOKEN for Expo push (if token-gated by Expo plan tier) - RESEND_API_KEY for Resend email client	- Stripe Connect transfer created — transfer.id written to payments.stripe_transfer_id - payments.transfer_attempt_count incremented by 1 on each Stripe failure - payments reverted to status='pending_release' when expert lacks stripe_account_id or payouts not enabled - Expo push notification delivered to expert on successful Stripe transfer initiation (if push_token set) - payments escalated to status='payment_hold' when transfer_attempt_count reaches 3 - Admin email sent to justin@b2bea.org within 60s of payment_hold trigger via Resend - HTTP 200 {success:true} on Stripe success or deliberate	Two internal API routes authenticated by INTERNAL_API_SECRET bearer — called only by pg_net DB triggers, never by client apps. Route 1 — POST /api/internal/payments/release: validates Authorization: Bearer <INTERNAL_API_SECRET> (401 on mismatch). Accepts {payment_id: uuid, expert_id: uuid}. Fetches expert (stripe_account_id, stripe_payouts_enabled, push_token, name) and payment (total_amount, session_id). Fetches product name via capture_sessions JOIN products. If stripe_account_id IS NULL OR stripe_payouts_enabled=false: reverts payment to pending_release (UPDATE payments SET status='pending_
D-06-03	GET /api/experts/:id/earnings	api_route	- Authorization: Bearer <expert-jwt> — sub=expert uuid, role=expert - :id path parameter (uuid)	- {total_earned: number, pending: number, sessions: [{session_id: uuid, product_name: string, sku: string, base_amount: number, surge_bonus: number, rarity_bonus: number, completion_bonus: number, quality_multiplier: number, total_amount: number, status: string, composite_score: number\|null, hold_until: string\|null, captured_at: string}]} — sessions ordered by captured_at DESC	Expert-scoped earnings summary endpoint. Validates JWT: must be present and valid (401 on missing/invalid); must have role=expert (401); sub must exactly equal :id path parameter (403 on mismatch). Queries payments p JOIN capture_sessions cs ON cs.id=p.session_id JOIN products pr ON pr.id=cs.product_id WHERE p.expert_id=:id ORDER BY p.created_at DESC. Extracts composite_score as (cs.score_object->>'composite_score')::float (null when score_object is null). Computes totals server-side: total_earned = SUM(p.total_amount) FILTER (WHERE p.status IN ('released','paid')); pending = SUM(p.total_amoun
D-06-04	POST /api/webhooks/stripe	api_route	- Raw HTTP request body as Buffer - stripe-signature header from Stripe - STRIPE_WEBHOOK_SECRET environment variable	- 200 {received: true} on all events with valid signatures - payments.status='paid' and released_at set on transfer.paid event - experts.stripe_payouts_enabled updated to true or false on account.updated event	Stripe webhook endpoint. Raw request body must NOT be parsed by body-parsing middleware before this handler — Stripe signature verification requires the raw buffer. Validates via stripe.webhooks.constructEvent(rawBody, request.headers['stripe-signature'], STRIPE_WEBHOOK_SECRET); returns 400 on signature failure. Handles two event types: (1) event.type='transfer.paid': extracts stripe_transfer_id=event.data.object.id; executes UPDATE payments SET status='paid', released_at=COALESCE(released_at, now()) WHERE stripe_transfer_id=stripe_transfer_id. (2) event.type='account.updated': extracts accoun
D-06-05	EarningsDashboard.tsx + EarningsSummaryCard.tsx + SessionEarningsRow.tsx (screen S-05-a)	component	- Expert JWT and expert id from auth context - GET /api/experts/:id/earnings JSON response - expert.stripe_payouts_enabled from auth context or GET /api/experts/:id profile (used to determine no-stripe state)	- Rendered EarningsDashboard screen with live earnings data or appropriate empty/error/skeleton state	React Native screen mounted at the bottom nav Earnings tab and navigable from the Score Results CTA. On mount, fires GET /api/experts/:id/earnings using the JWT from auth context. Immediately renders skeleton: tall card placeholder matching EarningsSummaryCard dimensions plus 3 row placeholders matching SessionEarningsRow height. If no response within 10s, dismisses skeleton and shows error state: body text 'Something went wrong' + 'Try again' CTA that re-fires the request. On success, renders EarningsSummaryCard (C-05-a-1) and SessionEarningsRow list (C-05-a-2). EarningsSummaryCard: two stat

integrations

calls	purpose	service
- stripe.transfers.create({amount: Math.round(total_amount*100), currency:'usd', destination: expert.stripe_account_id, transfer_group: payment_id, metadata:{payment_id, expert_id}}) — called from POST /api/internal/payments/release on each eligible released payment - stripe.webhooks.constructEvent(rawBody, sig, STRIPE_WEBHOOK_SECRET) — called from POST /api/webhooks/stripe for every inbound Stripe event	Initiate Connect Express payouts to expert bank accounts on payment release; receive transfer.paid and account.updated webhooks to finalise payment status and track onboarding completion	Stripe
- POST https://exp.host/--/api/v2/push/send with {to: expert.push_token, title:'Payment Released', body:'$<total_amount> from <product_name> is on its way', data:{screen:'earnings'}} — called from POST /api/internal/payments/release on successful Stripe transfer creation when expert.push_token IS NOT NULL	Deliver in-app push notification to expert's mobile device when their payment release Stripe transfer is initiated	Expo Push Notifications
- resend.emails.send({from:'noreply@knowledgevault.ai', to:'justin@b2bea.org', subject:'[KV] Payment Hold Alert — '+payment_id, html:'<p>Payment <strong>'+payment_id+'</strong> is in payment_hold status.</p><p>Expert: '+expert_id+'</p><p>Amount: $'+total_amount+'</p>'}) — called from POST /api/internal/notifications/payment-hold	Send admin alert email to justin@b2bea.org within 60s when a payment enters payment_hold status (either from M-05 evaluation or from 3 consecutive Stripe transfer failures)	Resend
- pg_net.http_post(url, body, headers) from on_payment_released() trigger — fires to /api/internal/payments/release for each payment updated from pending_release to released by the M-00 pg_cron job - pg_net.http_post(url, body, headers) from on_payment_hold() trigger — fires to /api/internal/notifications/payment-hold for each payment entering payment_hold status	Enable non-blocking HTTP calls from PL/pgSQL trigger functions to internal API routes, allowing DB triggers to fire side effects (Stripe transfer, admin email) without holding the calling transaction open	pg_net

prerequisites

payments table exists per M-00 schema: id uuid pk, expert_id uuid fk experts.id, session_id uuid fk capture_sessions.id UNIQUE, status text check(pending_release|released|paid|payment_hold|disputed) default pending_release, hold_until timestamptz null, base_amount numeric(10,2) not null, surge_bonus numeric(10,2) default 0, rarity_bonus numeric(10,2) default 0, completion_bonus numeric(10,2) default 0, quality_multiplier float default 1.0, total_amount numeric(10,2) not null, stripe_transfer_id text null, released_at timestamptz null, created_at timestamptz not null
pg_cron job 'release-held-payments' exists from M-00 D-00-04: UPDATE payments SET status='released', released_at=now() WHERE status='pending_release' AND hold_until<=now() — runs hourly at :00
experts table exists per M-00 schema: id uuid pk, name text not null, email text not null unique, stripe_account_id text null, created_at timestamptz not null
capture_sessions table exists per M-00 schema: id uuid pk, expert_id uuid, product_id uuid, status text, score_object jsonb null, completed_at timestamptz null
products table exists per M-00 schema: id uuid pk, name text not null, sku text not null unique
M-05 creates payment records with status='pending_release' (hold_until = payment.created_at + interval per EVALUATION_HOLD_HOURS) or status='payment_hold' when evaluation flags manual review
STRIPE_SECRET_KEY populated in API runtime environment
STRIPE_WEBHOOK_SECRET populated in API runtime environment
INTERNAL_API_SECRET populated in both API runtime and DB (via ALTER DATABASE SET app.internal_api_secret='...')
INTERNAL_API_BASE_URL populated in DB via ALTER DATABASE SET app.internal_api_base_url='https://app.knowledgevault.ai' before migration runs
EXPO_ACCESS_TOKEN populated in API runtime for Expo push notification API
RESEND_API_KEY populated in API runtime for transactional email
pg_cron extension already enabled in Supabase project (from M-00)
pg_net extension available in Supabase project tier (Pro plan or above)

open questions

id	owner	blocks	question	resolved	resolution
OQ-M-06-01	justin	D-06-02 (push notification requires push_token in experts table) and D-06-05 (EarningsDashboard assumes token is registered before payment is released)	Does the expert mobile app register the Expo push token on login via an existing PATCH /api/experts/:id endpoint, or does M-06 need to add a dedicated POST /api/experts/:id/push-token route?	true	Add dedicated POST /api/experts/:id/push-token route in M-06 (Priya default).
OQ-M-06-02	justin	D-06-02 (Resend send call fails if the from-domain is not verified in the Resend account)	Is noreply@knowledgevault.ai verified as a Resend sender domain, or should a different from-address be used for the payment_hold admin alert email?	true	Resend confirmed. Use noreply@knowledgevault.ai — domain must be verified in Resend before deploy.

schema version

1.0