module-spec · supabase_json
KnowledgeVault AI module-spec
Delivers all Supabase SQL migrations for KnowledgeVault AI v1: 12 tables, pgvector 1536-dimension extension, full row-level security policies, two materialized views (company coverage dashboard and platform analytics), and three pg_cron jobs (payment hold release, view refresh). Ground-zero: no expert signup, session capture, knowledge retrieval, payment, or dashboard can start until these four migration files are applied in order against the target Supabase PostgreSQL 15 project. The schema is
open questions
| id | owner | blocks | question |
|---|---|---|---|
| OQ-M-00-01 | quinn | Migration file naming in D-00-01 through D-00-04. Does not block any feature module. | apps/kv-app/supabase/migrations/001_kv_phase1_foundation.sql exists and defines tables (users, expert_profiles, skus, session_answers, follow_up_questions, known_terms) incompatible with the M-00 schema. Options: (a) treat 001 as a superseded draft and write M-00 files as 001-004, replacing it — recommended if no production data exists; or (b) keep 001 and write M-00 files as 002-005. This spec uses 002-005 as working filenames. Quinn resolves on implementation start. |
Agent Handoff
Start Here
Delivers all Supabase SQL migrations for KnowledgeVault AI v1: 12 tables, pgvector 1536-dimension extension, full row-level security policies, two materialized views (company coverage dashboard and platform analytics), and three pg_cron jobs (payment hold release, view refresh). Ground-zero: no expert signup, session capture, knowledge retrieval, payment, or dashboard can start until these four migration files are applied in order against the target Supabase PostgreSQL 15 project. The schema is
Completion Evidence
No explicit evidence field yet. Require tests, screenshots, linked PRs, or reviewed outputs before marking complete.
Open Questions
- OQ-M-00-01: id: string, owner: string, blocks: string, question: string
Structured Payload
Machine-readable source fields
phase
id
M-00
summary
Delivers all Supabase SQL migrations for KnowledgeVault AI v1: 12 tables, pgvector 1536-dimension extension, full row-level security policies, two materialized views (company coverage dashboard and platform analytics), and three pg_cron jobs (payment hold release, view refresh). Ground-zero: no expert signup, session capture, knowledge retrieval, payment, or dashboard can start until these four migration files are applied in order against the target Supabase PostgreSQL 15 project. The schema is designed for strict module dependency ordering — M-01 through M-10 each reference tables created here without requiring further structural migrations.
module id
M-00
data model
notes
Migration execution order is strict: D-00-01 then D-00-02 then D-00-03 then D-00-04. Each must complete error-free before the next starts. Within D-00-01, creation order: (1) experts, (2) products, (3) virtual_experts, (4) company_knowledge_access — virtual_experts must precede capture_sessions in D-00-02 which has a nullable FK to virtual_experts. Coverage_pct is NOT stored on products — computed in company_dashboard_mv and inline in M-02 GET /api/products/queue as completed_session_count/target_session_count*100; M-02 stubs coverage_pct=0 until M-04 ships. companies table: the blueprint references a companies table but it is NOT in the M-00 12-table scope because v1 uses JWT text claims for company identity. A companies table is V2 scope — company_id on field_queries and company_knowledge_access is TEXT in v1. Payment hold: hold_until = now() + make_interval(hours => COALESCE(current_setting('app.evaluation_hold_hours', true)::int, 24)); configure via Supabase project settings. HNSW parameters (m=16, ef_construction=64) are blueprint-specified values for 1536-dimension cosine similarity — do not change without validating recall accuracy.
tables
| rls | name | indexes | purpose | key columns |
|---|---|---|---|---|
| SELECT/UPDATE own row via auth_user_id=auth.uid(). INSERT: service_role only (M-01 API). DELETE: none. Anon: blocked. | experts | - UNIQUE INDEX on email - UNIQUE INDEX on auth_user_id - INDEX on role_type - GIN INDEX on specialties (array overlap @> for M-02 specialty filtering) | Human knowledge contributors — retiring and retired tradespeople who are the source of all VirtualExpert personas and knowledge chunks. | - id uuid PRIMARY KEY DEFAULT gen_random_uuid() - auth_user_id uuid UNIQUE — Supabase auth.users.id; set during M-01 signup; RLS predicate - name text NOT NULL - email text UNIQUE NOT NULL - phone text - role_type text NOT NULL — e.g. plumber, electrician, HVAC tech, pipefitter - specialties text[] NOT NULL DEFAULT '{}' — product categories for M-02 queue filtering - years_experience integer - bio text — nullable; auto-generated post-first-session in M-05 - stripe_account_id text — nullable if Stripe deferred at signup - stripe_onboarding_complete boolean NOT NULL DEFAULT false - created_at ti |
| SELECT: any authenticated JWT. INSERT/UPDATE: service_role only. DELETE: none. | products | - UNIQUE INDEX on sku - INDEX on category - INDEX on has_surge_badge - INDEX on is_fully_captured | Catalog of SKUs for expert knowledge capture. target_session_count is the coverage denominator. | - id uuid PRIMARY KEY DEFAULT gen_random_uuid() - sku text UNIQUE NOT NULL - name text NOT NULL - manufacturer text NOT NULL - category text — expert specialty filtering in M-02 - description text - spec_sheet_url text — read by Gemini (M-03) and o3 ground-truth gate (M-05) - image_url text - has_surge_badge boolean NOT NULL DEFAULT false — +$2.00 bonus - is_fully_captured boolean NOT NULL DEFAULT false — set true by M-05 when coverage >= 90% - target_session_count integer NOT NULL DEFAULT 5 — coverage_pct denominator - created_at timestamptz NOT NULL DEFAULT now() |
| SELECT is_published=true for authenticated; expert JWT also sees own unpublished. INSERT/UPDATE: service_role only. DELETE: none. | virtual_experts | - INDEX on expert_id - INDEX on is_published | AI personas derived from expert sessions. Published after >= 3 sessions with avg_accuracy_score >= 70. | - id uuid PRIMARY KEY DEFAULT gen_random_uuid() - expert_id uuid NOT NULL REFERENCES experts(id) ON DELETE RESTRICT - name text NOT NULL — e.g. 'Bob — Plumbing Specialist, 38 years' - description text - session_count integer NOT NULL DEFAULT 0 — incremented by M-05 - knowledge_chunk_count integer NOT NULL DEFAULT 0 — incremented by M-05 - avg_accuracy_score float — rolling average of composite_score_0_100; updated by M-05 - is_published boolean NOT NULL DEFAULT false — set true by M-05 when session_count>=3 AND avg_accuracy_score>=70 - created_at timestamptz NOT NULL DEFAULT now() |
| service_role only. No user access in v1. | company_knowledge_access | - INDEX on company_id | V2 access control stub. company_id is TEXT (JWT claim) in v1. Table created in M-00 to prevent breaking migrations when V2 ships. | - id uuid PRIMARY KEY DEFAULT gen_random_uuid() - company_id text NOT NULL — JWT claim; no FK to companies table in v1 - virtual_expert_id uuid REFERENCES virtual_experts(id) ON DELETE CASCADE — null = all virtual experts - product_id uuid REFERENCES products(id) ON DELETE CASCADE — null = all products - granted_at timestamptz NOT NULL DEFAULT now() |
| expert SELECT/INSERT own rows via expert_id->experts.auth_user_id=auth.uid(). service_role UPDATE/DELETE. No direct user UPDATE. | capture_sessions | - INDEX on expert_id - INDEX on product_id - INDEX on status - INDEX on started_at | One expert's complete knowledge capture session for one product. Stores full SharedExpertContext JSONB (written after each answered question for crash recovery), lifecycle status, and AI cost breakdown. | - id uuid PRIMARY KEY DEFAULT gen_random_uuid() - expert_id uuid NOT NULL REFERENCES experts(id) ON DELETE RESTRICT - product_id uuid NOT NULL REFERENCES products(id) ON DELETE RESTRICT - virtual_expert_id uuid REFERENCES virtual_experts(id) ON DELETE SET NULL — linked after VirtualExpert created in M-05 - status text NOT NULL DEFAULT 'in_progress' CHECK (status IN ('in_progress','complete','abandoned','evaluating','payment_hold','paid')) - transcript text — full Deepgram STT transcript - session_context jsonb NOT NULL DEFAULT '{}' — full SharedExpertContext; fields: {session_id, expert_id, sk |
| SELECT: any authenticated JWT. INSERT/UPDATE/DELETE: service_role only. | product_pre_seeds | - UNIQUE INDEX on product_id - INDEX on generated_at | Cached Gemini 2.5 Pro pre-processing output per SKU. One row per product. Generated by M-03 with 30-day TTL. Consumed by M-04 at session start. | - id uuid PRIMARY KEY DEFAULT gen_random_uuid() - product_id uuid NOT NULL REFERENCES products(id) ON DELETE CASCADE UNIQUE - question_set jsonb NOT NULL — [{question_id uuid, question_text text, depth_target text, knowledge_dimension text}] - context_summary jsonb NOT NULL — {manufacturer, category, key_specs, known_failure_modes_from_spec, part_number_aliases, applicable_codes} - key_vectors jsonb — {failure_modes[], install_sequence[], safety_critical_points[]} - model_version text NOT NULL — e.g. 'gemini-2.5-pro-2026-04' - generated_at timestamptz NOT NULL DEFAULT now() — TTL check via PRE |
| company JWT SELECT WHERE is_published=true. expert JWT SELECT own rows. service_role INSERT/UPDATE. DELETE: none. | knowledge_chunks | - INDEX on session_id - INDEX on expert_id - INDEX on product_id - INDEX on sku - INDEX on chunk_type - INDEX on is_published - INDEX on ground_truth_verified - HNSW INDEX on embedding USING hnsw (embedding vector_cosine_ops) WITH (m=16,ef_construction=64) WHERE embedding IS NOT NULL | Atomic structured micro-objects extracted from sessions. The searchable unit of expert knowledge. Type-tagged, 1536d pgvector embedded for semantic retrieval in M-07/M-08. | - id uuid PRIMARY KEY DEFAULT gen_random_uuid() - session_id uuid NOT NULL REFERENCES capture_sessions(id) ON DELETE RESTRICT - expert_id uuid NOT NULL REFERENCES experts(id) ON DELETE RESTRICT - product_id uuid NOT NULL REFERENCES products(id) ON DELETE RESTRICT - sku text NOT NULL — denormalized from products.sku for query performance - chunk_type text NOT NULL CHECK (chunk_type IN ('failure_mode','symptom','fix','psi_range','safety_warning','field_tip','tool_required','part_number_reference')) - content text NOT NULL - embedding vector(1536) — populated by text-embedding-3-small in M-05; nu |
| expert JWT SELECT own rows. service_role INSERT/UPDATE. DELETE: none. | payments | - INDEX on expert_id - UNIQUE INDEX on session_id - INDEX on status - PARTIAL INDEX on hold_until WHERE status='pending_release' | Expert payment records — one per capture session (UNIQUE on session_id). Created by M-05 after o3 evaluation. Released by pg_cron after hold_until. | - id uuid PRIMARY KEY DEFAULT gen_random_uuid() - expert_id uuid NOT NULL REFERENCES experts(id) ON DELETE RESTRICT - session_id uuid NOT NULL REFERENCES capture_sessions(id) ON DELETE RESTRICT UNIQUE - status text NOT NULL DEFAULT 'pending_release' CHECK (status IN ('pending_release','released','paid','payment_hold','disputed')) - base_amount numeric(10,2) NOT NULL DEFAULT 0.00 — $2.00 x questions_answered - quality_multiplier float NOT NULL DEFAULT 1.0 — composite_score_0_100/100 - surge_bonus numeric(10,2) NOT NULL DEFAULT 0.00 — $2.00 if has_surge_badge and first capture - rarity_bonus num |
| company JWT SELECT own rows WHERE company_id=auth.jwt()->>'company_id'. service_role INSERT. DELETE: none. | field_queries | - INDEX on company_id - INDEX on created_at - HNSW INDEX on query_embedding USING hnsw (query_embedding vector_cosine_ops) WITH (m=16,ef_construction=64) WHERE query_embedding IS NOT NULL | Append-only log of company field technician search queries. Source data for M-09 top_queries and slang_cloud analytics. | - id uuid PRIMARY KEY DEFAULT gen_random_uuid() - company_id text NOT NULL — JWT claim; text not UUID FK in v1 - query_text text NOT NULL - query_embedding vector(1536) — embedded by text-embedding-3-small at query time in M-07 - results jsonb NOT NULL DEFAULT '[]' — [{chunk_id uuid, content text, chunk_type text, sku text, relevance_score float}] - created_at timestamptz NOT NULL DEFAULT now() |
| expert JWT SELECT own via session_id->capture_sessions join. service_role INSERT. DELETE: none (append-only). | session_context | - INDEX on session_id - INDEX on (session_id, checkpoint_at DESC) — fast latest-checkpoint retrieval | Append-only crash-recovery checkpoint log. Written after every answered question in M-04. Latest row by checkpoint_at is the recovery point for crashed sessions. | - id uuid PRIMARY KEY DEFAULT gen_random_uuid() - session_id uuid NOT NULL REFERENCES capture_sessions(id) ON DELETE CASCADE - context_snapshot jsonb NOT NULL — full SharedExpertContext at this checkpoint - question_index integer NOT NULL DEFAULT 0 — 1-indexed; question just answered - checkpoint_at timestamptz NOT NULL DEFAULT now() |
| service_role only. No user-facing access. | agent_tasks | - INDEX on session_id - INDEX on status - INDEX on task_type - PARTIAL INDEX on (status,created_at) WHERE status='pending' | Async evaluation pipeline task queue. M-04 creates 'evaluate_session' tasks on session close. M-05 Edge Function/pg_cron polls and processes. Max 3 attempts. | - id uuid PRIMARY KEY DEFAULT gen_random_uuid() - session_id uuid REFERENCES capture_sessions(id) ON DELETE CASCADE — nullable for preseed_generate tasks - task_type text NOT NULL CHECK (task_type IN ('evaluate_session','embed_chunks','preseed_generate','payment_release')) - status text NOT NULL DEFAULT 'pending' CHECK (status IN ('pending','processing','complete','failed')) - payload jsonb NOT NULL DEFAULT '{}' — evaluate_session: {session_id}; preseed_generate: {product_id} - result jsonb - error_message text - attempts integer NOT NULL DEFAULT 0 — max 3; permanent failure after 3rd attempt |
| expert JWT SELECT own via session_id join. company JWT SELECT all. service_role INSERT. DELETE: none. | approved_artifacts | - INDEX on knowledge_chunk_id - INDEX on session_id | Ground-truth verification records from o3 evaluation. One row per knowledge_chunk that entered the ground-truth gate. Stores outcome and authoritative source references. | - id uuid PRIMARY KEY DEFAULT gen_random_uuid() - knowledge_chunk_id uuid NOT NULL REFERENCES knowledge_chunks(id) ON DELETE CASCADE - session_id uuid NOT NULL REFERENCES capture_sessions(id) ON DELETE CASCADE - verified_by text NOT NULL DEFAULT 'o3' — frozen at payment go-live - verification_result jsonb NOT NULL — {passed bool, confidence float, notes text} - source_references jsonb NOT NULL DEFAULT '[]' — [{source_type: 'spec_sheet'|'trade_code'|'erp', reference text, matched_claim text}] - approved_at timestamptz NOT NULL DEFAULT now() |
module name
Data Model & Migrations
deliverables
| id | name | type | inputs | outputs | description |
|---|---|---|---|---|---|
| D-00-01 | 002_extensions_and_foundation.sql | migration | - Target Supabase PostgreSQL 15 project with no prior M-00 tables applied - Postgres role with CREATE EXTENSION privilege (superuser or postgres role) | - CREATE EXTENSION IF NOT EXISTS vector — pgvector enabled - experts table: id uuid PK DEFAULT gen_random_uuid(), auth_user_id uuid UNIQUE (links to auth.users.id for RLS), name text NOT NULL, email text UNIQUE NOT NULL, phone text, role_type text NOT NULL, specialties text[] NOT NULL DEFAULT '{}', years_experience integer, bio text, stripe_account_id text, stripe_onboarding_complete boolean NOT NULL DEFAULT false, created_at timestamptz NOT NULL DEFAULT now() - products table: id uuid PK DEFAULT gen_random_uuid(), sku text UNIQUE NOT NULL, name text NOT NULL, manufacturer text NOT NULL, categ | Enables the pgvector extension and creates the four foundation tables that have no inter-table FK dependencies within M-00 scope: experts, products, virtual_experts, and company_knowledge_access. Adds all non-vector indexes on these tables. experts gains auth_user_id for RLS; products gains target_session_count for coverage calculation; virtual_experts is created before capture_sessions to resolve the nullable FK dependency. |
| D-00-02 | 003_operational_tables.sql | migration | - D-00-01 applied — experts, products, virtual_experts, company_knowledge_access tables must exist - pgvector extension enabled | - capture_sessions table: id uuid PK DEFAULT gen_random_uuid(), expert_id uuid NOT NULL REFERENCES experts(id) ON DELETE RESTRICT, product_id uuid NOT NULL REFERENCES products(id) ON DELETE RESTRICT, virtual_expert_id uuid REFERENCES virtual_experts(id) ON DELETE SET NULL, status text NOT NULL DEFAULT 'in_progress' CHECK (status IN ('in_progress','complete','abandoned','evaluating','payment_hold','paid')), transcript text, session_context jsonb NOT NULL DEFAULT '{}' (full SharedExpertContext; fields: session_id, expert_id, sku, product_context_summary, question_sequence, transcript, extracted_ | Creates the eight operational tables that depend on D-00-01 foundation tables: capture_sessions, product_pre_seeds, knowledge_chunks (vector(1536) + HNSW index), payments, field_queries (vector(1536) + HNSW index), session_context, agent_tasks, and approved_artifacts. Vector HNSW indexes use m=16, ef_construction=64, cosine ops as specified in the blueprint for 1536-dimension semantic search. |
| D-00-03 | 004_rls_policies.sql | migration | - D-00-01 and D-00-02 applied — all 12 tables must exist | - ALTER TABLE <all 12 tables> ENABLE ROW LEVEL SECURITY - experts: expert_select WHERE auth_user_id=auth.uid(); expert_update WHERE auth_user_id=auth.uid(); service_insert USING (auth.role()='service_role') - products: authenticated_select USING (auth.role() IN ('authenticated','service_role')); service_write INSERT/UPDATE USING (auth.role()='service_role') - virtual_experts: published_select WHERE is_published=true for authenticated; expert_own_select WHERE expert_id IN (SELECT id FROM experts WHERE auth_user_id=auth.uid()); service_write INSERT/UPDATE USING (auth.role()='service_role') - com | Enables Row Level Security on all 12 tables and creates policies for three JWT roles: expert (role=expert, auth.uid() matches experts.auth_user_id), company (role=company with company_id text claim), and admin (role=admin). Service_role key bypasses RLS for all server-side writes. Unauthenticated requests are blocked on all tables. |
| D-00-04 | 005_views_and_cron.sql | migration | - D-00-01, D-00-02, D-00-03 applied — all 12 tables with RLS must exist - pg_cron extension enabled in Supabase project (enabled by default on all Supabase projects) | - kv_release_due_payments() PL/pgSQL SECURITY DEFINER function: UPDATE payments SET status='released', released_at=now() WHERE status='pending_release' AND hold_until<=now(); returns integer count of rows released - company_dashboard_mv materialized view: product_id uuid, sku text, product_name text, manufacturer text, target_session_count integer, completed_session_count bigint (COUNT of capture_sessions WHERE status='complete'), coverage_pct numeric(5,1) = ROUND(completed_session_count::numeric/NULLIF(target_session_count,0)*100,1) defaulting to 0, coverage_status text ('red' < 40%, 'yellow' | Creates two materialized views (company_dashboard_mv for M-09 coverage dashboard; platform_analytics_mv for M-10 analytics panel) with UNIQUE indexes to support REFRESH CONCURRENTLY. Registers three pg_cron jobs: hourly payment hold release, 15-minute company dashboard refresh, 16-minute platform analytics refresh (offset by 1 minute to stagger I/O). Payment release is a PL/pgSQL SECURITY DEFINER function. |
prerequisites
- None — this is ground zero. Supabase project must exist with postgres superuser access to run CREATE EXTENSION.
- IMPORTANT: apps/kv-app/supabase/migrations/001_kv_phase1_foundation.sql already exists and defines a different schema (users, expert_profiles, skus, session_answers, follow_up_questions, known_terms). Quinn must resolve numbering before writing M-00 files — see open_questions OQ-M-00-01. This spec uses filenames 002–005 as working names; adjust on resolution.
open questions
| id | owner | blocks | question |
|---|---|---|---|
| OQ-M-00-01 | quinn | Migration file naming in D-00-01 through D-00-04. Does not block any feature module. | apps/kv-app/supabase/migrations/001_kv_phase1_foundation.sql exists and defines tables (users, expert_profiles, skus, session_answers, follow_up_questions, known_terms) incompatible with the M-00 schema. Options: (a) treat 001 as a superseded draft and write M-00 files as 001-004, replacing it — recommended if no production data exists; or (b) keep 001 and write M-00 files as 002-005. This spec uses 002-005 as working filenames. Quinn resolves on implementation start. |
schema version
1.0