plan-module · supabase_json
KnowledgeVault AI plan-module M-03
plan-module artifact · for KnowledgeVault AI · phase M-03 · status draft
tasks
| id | role | brief | title | completion | depends on |
|---|---|---|---|---|---|
| 1 | developer | Create apps/knowledgevault/supabase/functions/pre-seed-pipeline/index.ts — Supabase Edge Function (Deno TypeScript) that calls Gemini 2.5 Pro to generate product question sets and caches results in product_pre_seeds.
Auth: Service-role only — Authorization: Bearer SUPABASE_SERVICE_ROLE_KEY. Expert and company JWTs are rejected with 401.
Request: POST body { product_id: string (uuid) }
Steps:
1. Validate product_id is present and a valid UUID format → 400 { error: 'product_id is required and must be a valid UUID', code: 'MISSING_PARAM', status: 400 } if invalid
2. Verif | Build pre-seed-pipeline Edge Function | - type: file - evaluate: Deno TypeScript Edge Function validates auth (401), UUID format (400), product existence (404), builds multimodal Gemini 2.5 Pro request with correct responseSchema enums, returns 422 on schema mismatch and 502 on Gemini error, upserts idempotently to product_pre_seeds ON CONFLICT(product_id), returns 200 with pre_seed_id; no secrets hardcoded (POL-SEC-004). - min length: 400 | |
| 2 | developer | Create apps/knowledgevault/supabase/migrations/20260430000001_preseed_triggers.sql — SQL migration installing pg_net and two Postgres trigger functions that asynchronously call the pre-seed-pipeline Edge Function when products are inserted or spec-updated.
Steps in migration:
1. CREATE EXTENSION IF NOT EXISTS pg_net;
2. ALTER DATABASE postgres SET app.preseed_edge_fn_url = ''; — Quinn fills the actual Edge Function URL before applying
3. ALTER DATABASE postgres SET app.preseed_service_role_key = ''; — Quinn fills the actual service-role key before applying
4. `CREATE OR REPLACE FU | Create preseed_triggers database migration | - type: file - evaluate: SQL file enables pg_net extension, sets app.preseed_edge_fn_url and app.preseed_service_role_key database settings with empty string defaults, creates preseed_on_product_insert and preseed_on_product_spec_updated trigger functions using pg_net.http_post, creates AFTER INSERT trigger on products, creates AFTER UPDATE trigger scoped to spec_sheet_url and description columns only with WHEN condition; no existing migration files modified (POL-ARCH-001). - min length: 200 | |
| 3 | developer | Create apps/knowledgevault/supabase/migrations/20260430000002_preseed_cron.sql — SQL migration registering a pg_cron job for daily TTL-based cache refresh of product_pre_seeds.
Prerequisites: pg_cron extension (enabled in M-00), pg_net extension (enabled in D-03-02 migration). Apply D-03-02 BEFORE this migration.
Steps in migration:
1. ALTER DATABASE postgres SET app.preseed_cache_ttl_days = '30'; — configures TTL, overridable without code deploy
2. Create preseed_ttl_refresh() PL/pgSQL function:
```sql
CREATE OR REPLACE FUNCTION preseed_ttl_refresh() RETURNS void LANGUAG | Create preseed_cron database migration | - type: file - evaluate: SQL file sets app.preseed_cache_ttl_days='30', creates preseed_ttl_refresh() PL/pgSQL function that uses LEFT JOIN to find missing or stale product_pre_seeds rows and calls pg_net.http_post for each, registers pg_cron job 'preseed-ttl-refresh' at schedule '0 2 * * *'; no existing migration files modified (POL-ARCH-001). - min length: 150 | - 2 |
| 4 | developer | Create apps/knowledgevault/scripts/seed-pre-seeds.ts — Node.js TypeScript script (run via npx tsx scripts/seed-pre-seeds.ts) that batch-seeds all demo products idempotently before presentation day.
Required env vars: SUPABASE_URL, SUPABASE_SERVICE_ROLE_KEY. Optional: PRESEED_CACHE_TTL_DAYS (default 30).
Logic:
1. Init Supabase client with SUPABASE_URL + SUPABASE_SERVICE_ROLE_KEY
2. Read PRESEED_CACHE_TTL_DAYS from env (default 30)
3. SELECT id, sku FROM products
4. SELECT product_id, generated_at FROM product_pre_seeds
5. For each product:
- If fresh pre_seed row exi | Build demo seed script seed-pre-seeds.ts | - type: file - evaluate: TypeScript script queries products and product_pre_seeds via Supabase client, loops with SKIP/SEED/FAIL logic per product including TTL check, awaits 2000ms between POSTs, prints 'Complete — Seeded: X, Skipped: Y, Failed: Z', exits 1 if any failed; no secrets logged or hardcoded (POL-SEC-004); idempotent on re-run. - min length: 150 | - 1 - 2 |
Agent Handoff
Start Here
| id | role | brief | title | completion | depends on |
|---|---|---|---|---|---|
| 1 | developer | Create apps/knowledgevault/supabase/functions/pre-seed-pipeline/index.ts — Supabase Edge Function (Deno TypeScript) that calls Gemini 2.5 Pro to generate product question sets and caches results in product_pre_seeds.
Auth: Service-role only — Authorization: Bearer SUPABASE_SERVICE_ROLE_KEY. Expert and company JWTs are rejected with 401.
Request: POST body { product_id: string (uuid) }
Steps:
1. Validate product_id is present and a valid UUID format → 400 { error: 'product_id is required and must be a valid UUID', code: 'MISSING_PARAM', status: 400 } if invalid
2. Verif | Build pre-seed-pipeline Edge Function | - type: file - evaluate: Deno TypeScript Edge Function validates auth (401), UUID format (400), product existence (404), builds multimodal Gemini 2.5 Pro request with correct responseSchema enums, returns 422 on schema mismatch and 502 on Gemini error, upserts idempotently to product_pre_seeds ON CONFLICT(product_id), returns 200 with pre_seed_id; no secrets hardcoded (POL-SEC-004). - min length: 400 | |
| 2 | developer | Create apps/knowledgevault/supabase/migrations/20260430000001_preseed_triggers.sql — SQL migration installing pg_net and two Postgres trigger functions that asynchronously call the pre-seed-pipeline Edge Function when products are inserted or spec-updated.
Steps in migration:
1. CREATE EXTENSION IF NOT EXISTS pg_net;
2. ALTER DATABASE postgres SET app.preseed_edge_fn_url = ''; — Quinn fills the actual Edge Function URL before applying
3. ALTER DATABASE postgres SET app.preseed_service_role_key = ''; — Quinn fills the actual service-role key before applying
4. `CREATE OR REPLACE FU | Create preseed_triggers database migration | - type: file - evaluate: SQL file enables pg_net extension, sets app.preseed_edge_fn_url and app.preseed_service_role_key database settings with empty string defaults, creates preseed_on_product_insert and preseed_on_product_spec_updated trigger functions using pg_net.http_post, creates AFTER INSERT trigger on products, creates AFTER UPDATE trigger scoped to spec_sheet_url and description columns only with WHEN condition; no existing migration files modified (POL-ARCH-001). - min length: 200 | |
| 3 | developer | Create apps/knowledgevault/supabase/migrations/20260430000002_preseed_cron.sql — SQL migration registering a pg_cron job for daily TTL-based cache refresh of product_pre_seeds.
Prerequisites: pg_cron extension (enabled in M-00), pg_net extension (enabled in D-03-02 migration). Apply D-03-02 BEFORE this migration.
Steps in migration:
1. ALTER DATABASE postgres SET app.preseed_cache_ttl_days = '30'; — configures TTL, overridable without code deploy
2. Create preseed_ttl_refresh() PL/pgSQL function:
```sql
CREATE OR REPLACE FUNCTION preseed_ttl_refresh() RETURNS void LANGUAG | Create preseed_cron database migration | - type: file - evaluate: SQL file sets app.preseed_cache_ttl_days='30', creates preseed_ttl_refresh() PL/pgSQL function that uses LEFT JOIN to find missing or stale product_pre_seeds rows and calls pg_net.http_post for each, registers pg_cron job 'preseed-ttl-refresh' at schedule '0 2 * * *'; no existing migration files modified (POL-ARCH-001). - min length: 150 | - 2 |
| 4 | developer | Create apps/knowledgevault/scripts/seed-pre-seeds.ts — Node.js TypeScript script (run via npx tsx scripts/seed-pre-seeds.ts) that batch-seeds all demo products idempotently before presentation day.
Required env vars: SUPABASE_URL, SUPABASE_SERVICE_ROLE_KEY. Optional: PRESEED_CACHE_TTL_DAYS (default 30).
Logic:
1. Init Supabase client with SUPABASE_URL + SUPABASE_SERVICE_ROLE_KEY
2. Read PRESEED_CACHE_TTL_DAYS from env (default 30)
3. SELECT id, sku FROM products
4. SELECT product_id, generated_at FROM product_pre_seeds
5. For each product:
- If fresh pre_seed row exi | Build demo seed script seed-pre-seeds.ts | - type: file - evaluate: TypeScript script queries products and product_pre_seeds via Supabase client, loops with SKIP/SEED/FAIL logic per product including TTL check, awaits 2000ms between POSTs, prints 'Complete — Seeded: X, Skipped: Y, Failed: Z', exits 1 if any failed; no secrets logged or hardcoded (POL-SEC-004); idempotent on re-run. - min length: 150 | - 1 - 2 |
Completion Evidence
No explicit evidence field yet. Require tests, screenshots, linked PRs, or reviewed outputs before marking complete.
Artifact Shape
- tasks: 4 items
- module id: M-03
- module name: Pre-Seed Intelligence Pipeline
- schema version: 1.0
- depends on modules: 1 item
Structured Payload
Machine-readable source fields
tasks
| id | role | brief | title | completion | depends on |
|---|---|---|---|---|---|
| 1 | developer | Create apps/knowledgevault/supabase/functions/pre-seed-pipeline/index.ts — Supabase Edge Function (Deno TypeScript) that calls Gemini 2.5 Pro to generate product question sets and caches results in product_pre_seeds.
Auth: Service-role only — Authorization: Bearer SUPABASE_SERVICE_ROLE_KEY. Expert and company JWTs are rejected with 401.
Request: POST body { product_id: string (uuid) }
Steps:
1. Validate product_id is present and a valid UUID format → 400 { error: 'product_id is required and must be a valid UUID', code: 'MISSING_PARAM', status: 400 } if invalid
2. Verif | Build pre-seed-pipeline Edge Function | - type: file - evaluate: Deno TypeScript Edge Function validates auth (401), UUID format (400), product existence (404), builds multimodal Gemini 2.5 Pro request with correct responseSchema enums, returns 422 on schema mismatch and 502 on Gemini error, upserts idempotently to product_pre_seeds ON CONFLICT(product_id), returns 200 with pre_seed_id; no secrets hardcoded (POL-SEC-004). - min length: 400 | |
| 2 | developer | Create apps/knowledgevault/supabase/migrations/20260430000001_preseed_triggers.sql — SQL migration installing pg_net and two Postgres trigger functions that asynchronously call the pre-seed-pipeline Edge Function when products are inserted or spec-updated.
Steps in migration:
1. CREATE EXTENSION IF NOT EXISTS pg_net;
2. ALTER DATABASE postgres SET app.preseed_edge_fn_url = ''; — Quinn fills the actual Edge Function URL before applying
3. ALTER DATABASE postgres SET app.preseed_service_role_key = ''; — Quinn fills the actual service-role key before applying
4. `CREATE OR REPLACE FU | Create preseed_triggers database migration | - type: file - evaluate: SQL file enables pg_net extension, sets app.preseed_edge_fn_url and app.preseed_service_role_key database settings with empty string defaults, creates preseed_on_product_insert and preseed_on_product_spec_updated trigger functions using pg_net.http_post, creates AFTER INSERT trigger on products, creates AFTER UPDATE trigger scoped to spec_sheet_url and description columns only with WHEN condition; no existing migration files modified (POL-ARCH-001). - min length: 200 | |
| 3 | developer | Create apps/knowledgevault/supabase/migrations/20260430000002_preseed_cron.sql — SQL migration registering a pg_cron job for daily TTL-based cache refresh of product_pre_seeds.
Prerequisites: pg_cron extension (enabled in M-00), pg_net extension (enabled in D-03-02 migration). Apply D-03-02 BEFORE this migration.
Steps in migration:
1. ALTER DATABASE postgres SET app.preseed_cache_ttl_days = '30'; — configures TTL, overridable without code deploy
2. Create preseed_ttl_refresh() PL/pgSQL function:
```sql
CREATE OR REPLACE FUNCTION preseed_ttl_refresh() RETURNS void LANGUAG | Create preseed_cron database migration | - type: file - evaluate: SQL file sets app.preseed_cache_ttl_days='30', creates preseed_ttl_refresh() PL/pgSQL function that uses LEFT JOIN to find missing or stale product_pre_seeds rows and calls pg_net.http_post for each, registers pg_cron job 'preseed-ttl-refresh' at schedule '0 2 * * *'; no existing migration files modified (POL-ARCH-001). - min length: 150 | - 2 |
| 4 | developer | Create apps/knowledgevault/scripts/seed-pre-seeds.ts — Node.js TypeScript script (run via npx tsx scripts/seed-pre-seeds.ts) that batch-seeds all demo products idempotently before presentation day.
Required env vars: SUPABASE_URL, SUPABASE_SERVICE_ROLE_KEY. Optional: PRESEED_CACHE_TTL_DAYS (default 30).
Logic:
1. Init Supabase client with SUPABASE_URL + SUPABASE_SERVICE_ROLE_KEY
2. Read PRESEED_CACHE_TTL_DAYS from env (default 30)
3. SELECT id, sku FROM products
4. SELECT product_id, generated_at FROM product_pre_seeds
5. For each product:
- If fresh pre_seed row exi | Build demo seed script seed-pre-seeds.ts | - type: file - evaluate: TypeScript script queries products and product_pre_seeds via Supabase client, loops with SKIP/SEED/FAIL logic per product including TTL check, awaits 2000ms between POSTs, prints 'Complete — Seeded: X, Skipped: Y, Failed: Z', exits 1 if any failed; no secrets logged or hardcoded (POL-SEC-004); idempotent on re-run. - min length: 150 | - 1 - 2 |
module id
M-03
module name
Pre-Seed Intelligence Pipeline
schema version
1.0
depends on modules
- M-02