plan-module · supabase_json
KnowledgeVault AI plan-module M-00
plan-module artifact · for KnowledgeVault AI · phase M-00 · status draft
tasks
| id | role | brief | title | completion | depends on |
|---|---|---|---|---|---|
| 1 | developer | Write the Supabase PostgreSQL 15 migration file at apps/knowledgevault/supabase/migrations/001_extensions_companies.sql. This is the ground-zero migration — it must run before any other. Do NOT modify any existing migration file; create this file fresh.
The file must do three things in order:
1. Enable the pgvector extension: CREATE EXTENSION IF NOT EXISTS vector;
2. Enable the pg_cron extension: CREATE EXTENSION IF NOT EXISTS pg_cron SCHEMA cron;
3. Create the companies table with exactly these columns:
- id uuid NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY
- name text NOT NULL
| Write migration 001: extensions and companies | - type: file - evaluate: SQL file creates pgvector and pg_cron extensions with IF NOT EXISTS, creates companies table with all 6 specified columns and CHECK constraint on plan, creates btree index on contact_email, and all DDL uses IF NOT EXISTS per POL-ARCH-001 - min length: 400 | |
| 2 | developer | Write the Supabase PostgreSQL 15 migration file at apps/knowledgevault/supabase/migrations/002_core_schema.sql. Prerequisite: migration 001 has been applied — pgvector extension and companies table already exist. Create 12 application tables in strict FK dependency order using CREATE TABLE IF NOT EXISTS. All PKs use gen_random_uuid(). Status columns use text + CHECK (NOT PostgreSQL enum types — text allows future value additions without ALTER TYPE). Table creation order and required columns: 1. experts: id uuid PK, name text NOT NULL, email text NOT NULL UNIQUE, phone text NULL, role_type t | Write migration 002: twelve core application tables | - type: file - evaluate: SQL file creates all 12 application tables (experts, products, product_pre_seeds, virtual_experts, capture_sessions, session_context, knowledge_chunks, payments, field_queries, company_knowledge_access, agent_tasks, approved_artifacts) with correct FK references, CHECK constraints using text (not enum), UNIQUE constraint on session_context(session_id,sequence_number) and payments.session_id, all boolean columns NOT NULL, and all DDL uses IF NOT EXISTS per POL-ARCH-001 - min length: 2500 | - 1 |
| 3 | developer | Write the Supabase PostgreSQL 15 migration file at apps/knowledgevault/supabase/migrations/003_indexes_rls.sql. Prerequisite: migrations 001 and 002 applied — all 13 tables exist. PART A — Create performance indexes (use CREATE INDEX IF NOT EXISTS throughout): - experts: CREATE UNIQUE INDEX IF NOT EXISTS experts_email_idx ON experts(email); CREATE INDEX IF NOT EXISTS experts_specialties_gin_idx ON experts USING GIN(specialties); - products: CREATE UNIQUE INDEX IF NOT EXISTS products_sku_idx ON products(sku); CREATE INDEX IF NOT EXISTS products_badge_captured_idx ON products(has_surge_badge, i | Write migration 003: indexes and row-level security | - type: file - evaluate: SQL file creates IVFFlat indexes on knowledge_chunks.embedding (lists=100) and field_queries.query_embedding (lists=50), enables RLS on all 13 tables, creates at least one named policy per table except agent_tasks (which has RLS enabled with no permissive policies, effectively denying all non-service-role access), and all index DDL uses IF NOT EXISTS per POL-ARCH-001 and POL-SEC-001 - min length: 3000 | - 1 - 2 |
| 4 | developer | Write the Supabase PostgreSQL 15 migration file at apps/knowledgevault/supabase/migrations/004_views_cron.sql. Prerequisite: migrations 001, 002, and 003 applied — all 13 tables, all indexes, and all RLS policies exist. pg_cron extension is active. Step 1 — Create the product_coverage materialized view: CREATE MATERIALIZED VIEW IF NOT EXISTS product_coverage AS SELECT p.id AS product_id, p.sku, p.name, p.manufacturer, p.category, p.has_surge_badge, p.is_fully_captured, p.target_session_count, COUNT(DISTINCT cs.id) FILTER (WHERE cs.status = 'complete') AS completed_session_co | Write migration 004: materialized view and cron jobs | - type: file - evaluate: SQL file creates product_coverage materialized view with correct columns (product_id, sku, name, manufacturer, category, has_surge_badge, is_fully_captured, target_session_count, completed_session_count, coverage_pct, published_chunk_count), performs an initial non-CONCURRENT REFRESH before creating the unique index on product_id, and registers both pg_cron jobs ('release-held-payments' on '0 * * * *' and 'refresh-product-coverage' on '15 * * * *') with idempotent scheduling per POL-ARCH-001 - min length: 800 | - 1 - 2 - 3 |
| 5 | reviewer | Review all four KnowledgeVault AI M-00 migration files for correctness, completeness, and policy compliance. Read each file in sequence and produce a written review report. Files to review: - apps/knowledgevault/supabase/migrations/001_extensions_companies.sql - apps/knowledgevault/supabase/migrations/002_core_schema.sql - apps/knowledgevault/supabase/migrations/003_indexes_rls.sql - apps/knowledgevault/supabase/migrations/004_views_cron.sql Check each file against these criteria (cite file and line for any violation): 1. POL-ARCH-001: Files named 001–004 in sequence. All CREATE statements | Review all M-00 migration files | - type: file - evaluate: Markdown review report covering all 10 checklist items with explicit PASS/FAIL per item, lists any specific violations with file reference, and ends with a clear overall PASS or FAIL verdict - min length: 600 | - 1 - 2 - 3 - 4 |
Agent Handoff
Start Here
| id | role | brief | title | completion | depends on |
|---|---|---|---|---|---|
| 1 | developer | Write the Supabase PostgreSQL 15 migration file at apps/knowledgevault/supabase/migrations/001_extensions_companies.sql. This is the ground-zero migration — it must run before any other. Do NOT modify any existing migration file; create this file fresh.
The file must do three things in order:
1. Enable the pgvector extension: CREATE EXTENSION IF NOT EXISTS vector;
2. Enable the pg_cron extension: CREATE EXTENSION IF NOT EXISTS pg_cron SCHEMA cron;
3. Create the companies table with exactly these columns:
- id uuid NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY
- name text NOT NULL
| Write migration 001: extensions and companies | - type: file - evaluate: SQL file creates pgvector and pg_cron extensions with IF NOT EXISTS, creates companies table with all 6 specified columns and CHECK constraint on plan, creates btree index on contact_email, and all DDL uses IF NOT EXISTS per POL-ARCH-001 - min length: 400 | |
| 2 | developer | Write the Supabase PostgreSQL 15 migration file at apps/knowledgevault/supabase/migrations/002_core_schema.sql. Prerequisite: migration 001 has been applied — pgvector extension and companies table already exist. Create 12 application tables in strict FK dependency order using CREATE TABLE IF NOT EXISTS. All PKs use gen_random_uuid(). Status columns use text + CHECK (NOT PostgreSQL enum types — text allows future value additions without ALTER TYPE). Table creation order and required columns: 1. experts: id uuid PK, name text NOT NULL, email text NOT NULL UNIQUE, phone text NULL, role_type t | Write migration 002: twelve core application tables | - type: file - evaluate: SQL file creates all 12 application tables (experts, products, product_pre_seeds, virtual_experts, capture_sessions, session_context, knowledge_chunks, payments, field_queries, company_knowledge_access, agent_tasks, approved_artifacts) with correct FK references, CHECK constraints using text (not enum), UNIQUE constraint on session_context(session_id,sequence_number) and payments.session_id, all boolean columns NOT NULL, and all DDL uses IF NOT EXISTS per POL-ARCH-001 - min length: 2500 | - 1 |
| 3 | developer | Write the Supabase PostgreSQL 15 migration file at apps/knowledgevault/supabase/migrations/003_indexes_rls.sql. Prerequisite: migrations 001 and 002 applied — all 13 tables exist. PART A — Create performance indexes (use CREATE INDEX IF NOT EXISTS throughout): - experts: CREATE UNIQUE INDEX IF NOT EXISTS experts_email_idx ON experts(email); CREATE INDEX IF NOT EXISTS experts_specialties_gin_idx ON experts USING GIN(specialties); - products: CREATE UNIQUE INDEX IF NOT EXISTS products_sku_idx ON products(sku); CREATE INDEX IF NOT EXISTS products_badge_captured_idx ON products(has_surge_badge, i | Write migration 003: indexes and row-level security | - type: file - evaluate: SQL file creates IVFFlat indexes on knowledge_chunks.embedding (lists=100) and field_queries.query_embedding (lists=50), enables RLS on all 13 tables, creates at least one named policy per table except agent_tasks (which has RLS enabled with no permissive policies, effectively denying all non-service-role access), and all index DDL uses IF NOT EXISTS per POL-ARCH-001 and POL-SEC-001 - min length: 3000 | - 1 - 2 |
| 4 | developer | Write the Supabase PostgreSQL 15 migration file at apps/knowledgevault/supabase/migrations/004_views_cron.sql. Prerequisite: migrations 001, 002, and 003 applied — all 13 tables, all indexes, and all RLS policies exist. pg_cron extension is active. Step 1 — Create the product_coverage materialized view: CREATE MATERIALIZED VIEW IF NOT EXISTS product_coverage AS SELECT p.id AS product_id, p.sku, p.name, p.manufacturer, p.category, p.has_surge_badge, p.is_fully_captured, p.target_session_count, COUNT(DISTINCT cs.id) FILTER (WHERE cs.status = 'complete') AS completed_session_co | Write migration 004: materialized view and cron jobs | - type: file - evaluate: SQL file creates product_coverage materialized view with correct columns (product_id, sku, name, manufacturer, category, has_surge_badge, is_fully_captured, target_session_count, completed_session_count, coverage_pct, published_chunk_count), performs an initial non-CONCURRENT REFRESH before creating the unique index on product_id, and registers both pg_cron jobs ('release-held-payments' on '0 * * * *' and 'refresh-product-coverage' on '15 * * * *') with idempotent scheduling per POL-ARCH-001 - min length: 800 | - 1 - 2 - 3 |
| 5 | reviewer | Review all four KnowledgeVault AI M-00 migration files for correctness, completeness, and policy compliance. Read each file in sequence and produce a written review report. Files to review: - apps/knowledgevault/supabase/migrations/001_extensions_companies.sql - apps/knowledgevault/supabase/migrations/002_core_schema.sql - apps/knowledgevault/supabase/migrations/003_indexes_rls.sql - apps/knowledgevault/supabase/migrations/004_views_cron.sql Check each file against these criteria (cite file and line for any violation): 1. POL-ARCH-001: Files named 001–004 in sequence. All CREATE statements | Review all M-00 migration files | - type: file - evaluate: Markdown review report covering all 10 checklist items with explicit PASS/FAIL per item, lists any specific violations with file reference, and ends with a clear overall PASS or FAIL verdict - min length: 600 | - 1 - 2 - 3 - 4 |
Completion Evidence
No explicit evidence field yet. Require tests, screenshots, linked PRs, or reviewed outputs before marking complete.
Artifact Shape
- notes: All 4 deliverables map 1:1 to migration files. A fifth reviewer task is added because the module spec explicitly states it must be 'complete and reviewed before any other module starts.' Migrations must execute in strict sequence: 001 → 002 → 003 → 004 → review.
- tasks: 5 items
- module id: M-00
- module name: Data Model & Migrations
- schema version: 1.0
- depends on modules: 0 items
Structured Payload
Machine-readable source fields
notes
All 4 deliverables map 1:1 to migration files. A fifth reviewer task is added because the module spec explicitly states it must be 'complete and reviewed before any other module starts.' Migrations must execute in strict sequence: 001 → 002 → 003 → 004 → review.
tasks
| id | role | brief | title | completion | depends on |
|---|---|---|---|---|---|
| 1 | developer | Write the Supabase PostgreSQL 15 migration file at apps/knowledgevault/supabase/migrations/001_extensions_companies.sql. This is the ground-zero migration — it must run before any other. Do NOT modify any existing migration file; create this file fresh.
The file must do three things in order:
1. Enable the pgvector extension: CREATE EXTENSION IF NOT EXISTS vector;
2. Enable the pg_cron extension: CREATE EXTENSION IF NOT EXISTS pg_cron SCHEMA cron;
3. Create the companies table with exactly these columns:
- id uuid NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY
- name text NOT NULL
| Write migration 001: extensions and companies | - type: file - evaluate: SQL file creates pgvector and pg_cron extensions with IF NOT EXISTS, creates companies table with all 6 specified columns and CHECK constraint on plan, creates btree index on contact_email, and all DDL uses IF NOT EXISTS per POL-ARCH-001 - min length: 400 | |
| 2 | developer | Write the Supabase PostgreSQL 15 migration file at apps/knowledgevault/supabase/migrations/002_core_schema.sql. Prerequisite: migration 001 has been applied — pgvector extension and companies table already exist. Create 12 application tables in strict FK dependency order using CREATE TABLE IF NOT EXISTS. All PKs use gen_random_uuid(). Status columns use text + CHECK (NOT PostgreSQL enum types — text allows future value additions without ALTER TYPE). Table creation order and required columns: 1. experts: id uuid PK, name text NOT NULL, email text NOT NULL UNIQUE, phone text NULL, role_type t | Write migration 002: twelve core application tables | - type: file - evaluate: SQL file creates all 12 application tables (experts, products, product_pre_seeds, virtual_experts, capture_sessions, session_context, knowledge_chunks, payments, field_queries, company_knowledge_access, agent_tasks, approved_artifacts) with correct FK references, CHECK constraints using text (not enum), UNIQUE constraint on session_context(session_id,sequence_number) and payments.session_id, all boolean columns NOT NULL, and all DDL uses IF NOT EXISTS per POL-ARCH-001 - min length: 2500 | - 1 |
| 3 | developer | Write the Supabase PostgreSQL 15 migration file at apps/knowledgevault/supabase/migrations/003_indexes_rls.sql. Prerequisite: migrations 001 and 002 applied — all 13 tables exist. PART A — Create performance indexes (use CREATE INDEX IF NOT EXISTS throughout): - experts: CREATE UNIQUE INDEX IF NOT EXISTS experts_email_idx ON experts(email); CREATE INDEX IF NOT EXISTS experts_specialties_gin_idx ON experts USING GIN(specialties); - products: CREATE UNIQUE INDEX IF NOT EXISTS products_sku_idx ON products(sku); CREATE INDEX IF NOT EXISTS products_badge_captured_idx ON products(has_surge_badge, i | Write migration 003: indexes and row-level security | - type: file - evaluate: SQL file creates IVFFlat indexes on knowledge_chunks.embedding (lists=100) and field_queries.query_embedding (lists=50), enables RLS on all 13 tables, creates at least one named policy per table except agent_tasks (which has RLS enabled with no permissive policies, effectively denying all non-service-role access), and all index DDL uses IF NOT EXISTS per POL-ARCH-001 and POL-SEC-001 - min length: 3000 | - 1 - 2 |
| 4 | developer | Write the Supabase PostgreSQL 15 migration file at apps/knowledgevault/supabase/migrations/004_views_cron.sql. Prerequisite: migrations 001, 002, and 003 applied — all 13 tables, all indexes, and all RLS policies exist. pg_cron extension is active. Step 1 — Create the product_coverage materialized view: CREATE MATERIALIZED VIEW IF NOT EXISTS product_coverage AS SELECT p.id AS product_id, p.sku, p.name, p.manufacturer, p.category, p.has_surge_badge, p.is_fully_captured, p.target_session_count, COUNT(DISTINCT cs.id) FILTER (WHERE cs.status = 'complete') AS completed_session_co | Write migration 004: materialized view and cron jobs | - type: file - evaluate: SQL file creates product_coverage materialized view with correct columns (product_id, sku, name, manufacturer, category, has_surge_badge, is_fully_captured, target_session_count, completed_session_count, coverage_pct, published_chunk_count), performs an initial non-CONCURRENT REFRESH before creating the unique index on product_id, and registers both pg_cron jobs ('release-held-payments' on '0 * * * *' and 'refresh-product-coverage' on '15 * * * *') with idempotent scheduling per POL-ARCH-001 - min length: 800 | - 1 - 2 - 3 |
| 5 | reviewer | Review all four KnowledgeVault AI M-00 migration files for correctness, completeness, and policy compliance. Read each file in sequence and produce a written review report. Files to review: - apps/knowledgevault/supabase/migrations/001_extensions_companies.sql - apps/knowledgevault/supabase/migrations/002_core_schema.sql - apps/knowledgevault/supabase/migrations/003_indexes_rls.sql - apps/knowledgevault/supabase/migrations/004_views_cron.sql Check each file against these criteria (cite file and line for any violation): 1. POL-ARCH-001: Files named 001–004 in sequence. All CREATE statements | Review all M-00 migration files | - type: file - evaluate: Markdown review report covering all 10 checklist items with explicit PASS/FAIL per item, lists any specific violations with file reference, and ends with a clear overall PASS or FAIL verdict - min length: 600 | - 1 - 2 - 3 - 4 |
module id
M-00
module name
Data Model & Migrations
schema version
1.0
depends on modules
No items captured.