module-spec · supabase_json

KnowledgeVault AI module-spec M-00

Planning Surface

Use this to decide what happens next.

Status

approved

Phase

M-00

open questions

id	owner	blocks	question	resolved	resolution
OQ-M-00-01	justin	D-00-02 schema for session_context table — removal simplifies migrations 002 and 003	The blueprint defines session_context as a JSONB column on capture_sessions only. The module brief lists session_context as a separate table. This spec implements both: capture_sessions.session_context JSONB for the final frozen context, and a separate session_context table for per-answer incremental snapshots (crash recovery). If the separate table is not required, remove it from D-00-02 and its RLS policy from D-00-03.	true	Keep separate session_context table — yes. crash recovery design is correct.
OQ-M-00-02	justin	D-00-01 and D-00-02 FK design for field_queries and company_knowledge_access	companies is not in the 12-table list but is required as a FK target for field_queries.company_id and company_knowledge_access.company_id. This spec creates companies in M-00 migration 001. If companies is intended for a later module, then field_queries and company_knowledge_access cannot have FK constraints in M-00 and must use nullable text columns for company_id — weakening schema integrity. Confirm companies belongs in M-00.	true	companies belongs in M-00 — yes. FK integrity requires it as the 13th foundational table.
OQ-M-00-03	justin	D-00-02 schema for approved_artifacts table	approved_artifacts purpose is not defined in the blueprint or feature-ux-spec. This spec defines it as an admin review record for knowledge_chunks in payment_hold status (the manual review path). If this table serves a different purpose — e.g. distilled knowledge products, compiled installation guides, or exported expert knowledge packs — D-00-02 schema must be revised before Quinn builds migration 002.	true	approved_artifacts = admin review queue for payment_hold chunks — yes.

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

Structured Payload

Machine-readable source fields

summary

M-00 establishes the complete Supabase PostgreSQL 15 schema for KnowledgeVault AI. It delivers four numbered migration files: enabling pgvector and pg_cron extensions; creating all foundational tables (12 specified plus companies required for FK integrity); establishing all performance indexes including an IVFFlat vector index on knowledge_chunks.embedding for semantic search; enabling row-level security with role-specific policies for expert JWT, company JWT, and service-role access patterns; creating the product_coverage materialized view consumed by the company dashboard endpoint; and registering two pg_cron jobs for hourly payment hold release and view refresh. No other module can run a migration or start API/UI development until M-00 migrations are applied and reviewed on the target Supabase project.

module id

M-00

data model

notes

companies is a 13th foundational table created in M-00 beyond the 12 listed in the module brief — required because field_queries.company_id and company_knowledge_access.company_id are FK references that must resolve at migration time; see OQ-M-00-02. capture_sessions.session_context JSONB stores the final frozen SharedExpertContext on session close; the separate session_context table stores incremental per-answer snapshots for crash recovery — both coexist by design, see OQ-M-00-01. The IVFFlat index on knowledge_chunks.embedding uses lists=100 — effective for datasets up to ~1M rows; for the demo (~800 chunks), probes default of 10 is sufficient. The product_coverage materialized view must be initially populated with a non-CONCURRENTLY REFRESH at end of migration 004 before the unique index can be created; subsequent pg_cron refreshes use CONCURRENTLY. All status columns use text + CHECK constraints rather than PostgreSQL enum types to allow future value additions without ALTER TYPE migrations.

tables

rls	name	indexes	purpose	key columns
Company JWT user can SELECT own row (auth.jwt()->>'company_id' = id::text). Service role full access.	companies	- btree on contact_email for admin lookup	Distributor companies — AD member distributors with purchased access to KnowledgeVault. Created in M-00 as a 13th foundational table because it is a FK dependency for field_queries and company_knowledge_access.	- id uuid NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY - name text NOT NULL - ad_member_id text NULL - contact_email text NULL - plan text NULL CHECK (plan IN ('starter','growth','enterprise')) - created_at timestamptz NOT NULL DEFAULT now()
Expert JWT can SELECT and UPDATE own row (auth.uid() = id). Service role full access.	experts	- btree UNIQUE on email - GIN on specialties for array @> overlap queries	Human knowledge contributors — retiring and retired tradespeople who participate in voice capture sessions and receive payments.	- id uuid NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY - name text NOT NULL - email text NOT NULL UNIQUE - phone text NULL - role_type text NOT NULL - specialties text[] NOT NULL DEFAULT '{}' - years_experience integer NULL - bio text NULL - stripe_account_id text NULL - created_at timestamptz NOT NULL DEFAULT now()
All authenticated users (expert JWT or company JWT) can SELECT. Service role can INSERT/UPDATE/DELETE.	products	- btree UNIQUE on sku - btree on (has_surge_badge, is_fully_captured) for expert queue ordering	Catalog of SKUs available for expert knowledge capture, with coverage state and surge/completion badge flags.	- id uuid NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY - sku text NOT NULL UNIQUE - name text NOT NULL - manufacturer text NOT NULL - category text NULL - description text NULL - spec_sheet_url text NULL - image_url text NULL - has_surge_badge boolean NOT NULL DEFAULT false - is_fully_captured boolean NOT NULL DEFAULT false - target_session_count integer NOT NULL DEFAULT 5 - created_at timestamptz NOT NULL DEFAULT now()
Expert JWT can SELECT (needed at POST /api/sessions/start). Service role can INSERT/UPDATE.	product_pre_seeds	- btree UNIQUE on product_id - btree on generated_at for staleness check (generated_at < now() - INTERVAL '30 days')	Cached Gemini 2.5 Pro output per SKU — one row per product, loaded at session start by GPT-4o interview conductor.	- id uuid NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY - product_id uuid NOT NULL REFERENCES products(id) ON DELETE CASCADE UNIQUE - question_set jsonb NOT NULL - context_summary jsonb NOT NULL - key_vectors jsonb NULL - model_version text NOT NULL - generated_at timestamptz NOT NULL DEFAULT now()
Company JWT can SELECT rows where is_published=true. Expert JWT can SELECT and UPDATE own virtual expert (expert_id = auth.uid()). Service role full access.	virtual_experts	- btree on expert_id - btree on is_published for company dashboard queries	AI personas derived from capture sessions with a real expert — queryable by name by field technicians via the Virtual Expert interface.	- id uuid NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY - expert_id uuid NOT NULL REFERENCES experts(id) ON DELETE RESTRICT - name text NOT NULL - description text NULL - session_count integer NOT NULL DEFAULT 0 - knowledge_chunk_count integer NOT NULL DEFAULT 0 - avg_accuracy_score float NULL - is_published boolean NOT NULL DEFAULT false - created_at timestamptz NOT NULL DEFAULT now()
Expert JWT can SELECT/INSERT/UPDATE own sessions (expert_id = auth.uid()). Service role full access.	capture_sessions	- btree on expert_id - btree on product_id - btree on status - btree on (expert_id, started_at DESC) for earnings history	One expert's full knowledge capture session for one product — tracks lifecycle status, stores the frozen SharedExpertContext, and receives o3 score object after evaluation.	- id uuid NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY - expert_id uuid NOT NULL REFERENCES experts(id) - product_id uuid NOT NULL REFERENCES products(id) - virtual_expert_id uuid NULL REFERENCES virtual_experts(id) - status text NOT NULL DEFAULT 'in_progress' CHECK (status IN ('in_progress','complete','abandoned','evaluating','payment_hold','paid')) - transcript text NULL - session_context jsonb NOT NULL DEFAULT '{}' - score_object jsonb NULL - session_duration_s integer NULL - cost_breakdown jsonb NULL - started_at timestamptz NOT NULL DEFAULT now() - completed_at timestamptz NULL - evalua
Expert JWT can SELECT/INSERT own session contexts via session_id FK (expert_id = auth.uid() on the parent capture_session). Service role full access.	session_context	- btree UNIQUE on (session_id, sequence_number) - btree on (session_id, sequence_number DESC) for latest-snapshot lookup	Incremental session context snapshots written after each answered question — enables crash recovery by storing full SharedExpertContext per answer without locking the capture_sessions row.	- id uuid NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY - session_id uuid NOT NULL REFERENCES capture_sessions(id) ON DELETE CASCADE - sequence_number integer NOT NULL - context_snapshot jsonb NOT NULL - created_at timestamptz NOT NULL DEFAULT now() - UNIQUE (session_id, sequence_number)
Company JWT can SELECT where is_published=true. Expert JWT can SELECT own chunks (expert_id = auth.uid()). Service role full access.	knowledge_chunks	- btree on expert_id - btree on product_id - btree on sku for SKU-scoped search - btree on (is_published, ground_truth_verified) for search query filtering - IVFFlat on embedding vector_cosine_ops WITH (lists=100) WHERE embedding IS NOT NULL	Atomic structured micro-objects extracted from capture sessions — the searchable unit of expert knowledge, embedded as 1536-dimension vectors for semantic retrieval.	- id uuid NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY - session_id uuid NOT NULL REFERENCES capture_sessions(id) - expert_id uuid NOT NULL REFERENCES experts(id) - product_id uuid NOT NULL REFERENCES products(id) - sku text NOT NULL - 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) NULL - ground_truth_verified boolean NOT NULL DEFAULT false - novelty_score float NULL - is_published boolean NOT NULL DEFAULT false - created_at timest
Expert JWT can SELECT own payments (expert_id = auth.uid()). Service role full access.	payments	- btree on expert_id for earnings history - btree UNIQUE on session_id - btree on (status, hold_until) for pg_cron: WHERE status='pending_release' AND hold_until <= now()	Expert payment records — one per capture session, tracks base amount, bonus components, quality multiplier, 24-hour hold period, and Stripe Connect transfer state.	- id uuid NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY - expert_id uuid NOT NULL REFERENCES experts(id) - session_id uuid NOT NULL REFERENCES capture_sessions(id) UNIQUE - base_amount numeric(10,2) NOT NULL - quality_multiplier float NOT NULL DEFAULT 1.0 - rarity_bonus numeric(10,2) NOT NULL DEFAULT 0.00 - surge_bonus numeric(10,2) NOT NULL DEFAULT 0.00 - completion_bonus numeric(10,2) NOT NULL DEFAULT 0.00 - total_amount numeric(10,2) NOT NULL - status text NOT NULL DEFAULT 'pending_release' CHECK (status IN ('pending_release','released','paid','payment_hold','disputed')) - hold_until times
Company JWT can INSERT own rows (company_id = JWT claim company_id) and SELECT own rows. Service role full access.	field_queries	- btree on company_id - btree on created_at for time-windowed analytics - IVFFlat on query_embedding vector_cosine_ops WITH (lists=50) WHERE query_embedding IS NOT NULL	Log of field technician queries — captured for analytics, top-queries ranking, and slang cloud in the company coverage dashboard.	- id uuid NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY - company_id uuid NULL REFERENCES companies(id) - virtual_expert_id uuid NULL REFERENCES virtual_experts(id) - product_id uuid NULL REFERENCES products(id) - query_text text NOT NULL - query_embedding vector(1536) NULL - results jsonb NULL - created_at timestamptz NOT NULL DEFAULT now()
Company JWT can SELECT own rows (company_id = JWT claim company_id). Service role full access.	company_knowledge_access	- btree on company_id - btree on (company_id, virtual_expert_id) for access-check queries - btree on (company_id, product_id) for product access-check queries	Access control rows — records which companies can query which virtual experts and products.	- id uuid NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY - company_id uuid NOT NULL REFERENCES companies(id) - virtual_expert_id uuid NULL REFERENCES virtual_experts(id) - product_id uuid NULL REFERENCES products(id) - granted_at timestamptz NOT NULL DEFAULT now() - CHECK (virtual_expert_id IS NOT NULL OR product_id IS NOT NULL)
Service role only — all non-service-role requests denied. RLS ENABLED with no permissive policies for expert or company JWT roles.	agent_tasks	- btree on (status, created_at ASC) for queue polling WHERE status=pending ORDER BY created_at ASC - btree on entity_id for task lookup by session_id or product_id	Async evaluation task queue — dispatches post-session o3 scoring, knowledge chunk embedding, pre-seed generation, and coverage view refresh jobs to Supabase Edge Function consumers.	- id uuid NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY - task_type text NOT NULL CHECK (task_type IN ('evaluate_session','embed_chunks','preseed_product','refresh_coverage')) - entity_id uuid NOT NULL - status text NOT NULL DEFAULT 'pending' CHECK (status IN ('pending','processing','complete','failed')) - payload jsonb NOT NULL DEFAULT '{}' - retries integer NOT NULL DEFAULT 0 - max_retries integer NOT NULL DEFAULT 3 - error_message text NULL - created_at timestamptz NOT NULL DEFAULT now() - claimed_at timestamptz NULL - completed_at timestamptz NULL
Expert JWT can SELECT own rows (expert_id = auth.uid()). Service role full access.	approved_artifacts	- btree on chunk_id - btree on review_status for admin review queue - btree on expert_id for expert-facing review history	Admin manual review records for knowledge_chunks in payment_hold status — tracks review outcome and unblocks chunk publication and payment release after manual review.	- id uuid NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY - chunk_id uuid NOT NULL REFERENCES knowledge_chunks(id) ON DELETE CASCADE - session_id uuid NOT NULL REFERENCES capture_sessions(id) - expert_id uuid NOT NULL REFERENCES experts(id) - product_id uuid NOT NULL REFERENCES products(id) - review_status text NOT NULL DEFAULT 'pending' CHECK (review_status IN ('pending','approved','rejected')) - reviewed_by text NULL - review_notes text NULL - created_at timestamptz NOT NULL DEFAULT now() - reviewed_at timestamptz NULL

module name

Data Model & Migrations

deliverables

id	name	type	inputs	outputs	description
D-00-01	supabase/migrations/001_extensions_companies.sql	migration	- Empty Supabase PostgreSQL 15 database - pg_cron extension pre-enabled in Supabase dashboard	- pgvector extension active in pg_extension - pg_cron extension active in pg_extension - companies table created with all columns and constraints	Enables pgvector (CREATE EXTENSION IF NOT EXISTS vector) and pg_cron (CREATE EXTENSION IF NOT EXISTS pg_cron SCHEMA cron). Creates the companies table — this must precede all other tables because field_queries.company_id and company_knowledge_access.company_id are FK references to companies. Columns: id uuid PK gen_random_uuid(), name text NOT NULL, ad_member_id text NULL, contact_email text NULL, plan text NULL CHECK (plan IN (starter,growth,enterprise)), created_at timestamptz NOT NULL DEFAULT now().
D-00-02	supabase/migrations/002_core_schema.sql	migration	- 001_extensions_companies.sql applied — pgvector, pg_cron, companies all exist	- All 12 application tables created with correct column types, NOT NULL constraints, DEFAULT values, CHECK constraints, and FK references	Creates all 12 application tables in FK dependency order: experts, products, product_pre_seeds, virtual_experts, capture_sessions, session_context, knowledge_chunks, payments, field_queries, company_knowledge_access, agent_tasks, approved_artifacts. All PKs use gen_random_uuid(). Status columns use text + CHECK constraints (not enum types) to allow future value additions without ALTER TYPE. capture_sessions includes session_context JSONB for the final frozen SharedExpertContext; the separate session_context table stores incremental per-answer snapshots for crash recovery. See data_model for fu
D-00-03	supabase/migrations/003_indexes_rls.sql	migration	- 002_core_schema.sql applied — all 13 tables exist	- All btree, GIN, and IVFFlat indexes created - RLS enabled on all 13 tables - All named RLS policies created	Creates all performance indexes and enables RLS on all 13 tables. Indexes: UNIQUE btrees on experts.email and products.sku; btrees on all FK columns; GIN on experts.specialties for array overlap (@>) queries; btree on (is_published, ground_truth_verified) on knowledge_chunks; btree on (status, hold_until) on payments for pg_cron; IVFFlat on knowledge_chunks.embedding vector_cosine_ops WITH (lists=100) WHERE embedding IS NOT NULL; IVFFlat on field_queries.query_embedding vector_cosine_ops WITH (lists=50) WHERE query_embedding IS NOT NULL. RLS policies: experts (expert reads/updates own row via
D-00-04	supabase/migrations/004_views_cron.sql	migration	- 001, 002, 003 migrations applied — all tables, indexes, and extensions exist	- product_coverage materialized view created and initially refreshed - Unique index on product_coverage(product_id) created - Two cron jobs registered in cron.job table	Creates the product_coverage materialized view and registers two pg_cron jobs. Materialized view columns: product_id uuid, sku text, name text, manufacturer text, category text, has_surge_badge boolean, is_fully_captured boolean, target_session_count integer, completed_session_count bigint (COUNT capture_sessions WHERE status='complete'), coverage_pct float (completed_session_count::float / NULLIF(target_session_count,0) * 100), published_chunk_count bigint (COUNT knowledge_chunks WHERE is_published=true). Unique index on product_coverage(product_id). pg_cron job 1: cron.schedule('release-held

prerequisites

Supabase PostgreSQL 15 project provisioned — SUPABASE_URL and project reference available
pg_cron extension enabled in the Supabase dashboard (Database > Extensions > pg_cron) before migration 001 runs — available on Pro plans and above
SUPABASE_URL and SUPABASE_SERVICE_ROLE_KEY environment variables set in the migration runner environment
Supabase CLI installed and linked to the project reference (supabase link --project-ref <ref>)

open questions

id	owner	blocks	question	resolved	resolution
OQ-M-00-01	justin	D-00-02 schema for session_context table — removal simplifies migrations 002 and 003	The blueprint defines session_context as a JSONB column on capture_sessions only. The module brief lists session_context as a separate table. This spec implements both: capture_sessions.session_context JSONB for the final frozen context, and a separate session_context table for per-answer incremental snapshots (crash recovery). If the separate table is not required, remove it from D-00-02 and its RLS policy from D-00-03.	true	Keep separate session_context table — yes. crash recovery design is correct.
OQ-M-00-02	justin	D-00-01 and D-00-02 FK design for field_queries and company_knowledge_access	companies is not in the 12-table list but is required as a FK target for field_queries.company_id and company_knowledge_access.company_id. This spec creates companies in M-00 migration 001. If companies is intended for a later module, then field_queries and company_knowledge_access cannot have FK constraints in M-00 and must use nullable text columns for company_id — weakening schema integrity. Confirm companies belongs in M-00.	true	companies belongs in M-00 — yes. FK integrity requires it as the 13th foundational table.
OQ-M-00-03	justin	D-00-02 schema for approved_artifacts table	approved_artifacts purpose is not defined in the blueprint or feature-ux-spec. This spec defines it as an admin review record for knowledge_chunks in payment_hold status (the manual review path). If this table serves a different purpose — e.g. distilled knowledge products, compiled installation guides, or exported expert knowledge packs — D-00-02 schema must be revised before Quinn builds migration 002.	true	approved_artifacts = admin review queue for payment_hold chunks — yes.

schema version

1.0