KnowledgeVault AI module-spec M-03
M-03 delivers the offline intelligence pipeline that pre-computes product knowledge scaffolding before any expert session begins. For each SKU in the products catalog, a Supabase Edge Function invokes Gemini 2.5 Pro with the product image, catalog description, and spec sheet to generate a calibrated ordered question set (3-5 questions), a product context summary, and key knowledge vectors. Results are cached in product_pre_seeds — one row per product — and are re-generated automatically when spe
No items captured.
M-03 delivers the offline intelligence pipeline that pre-computes product knowledge scaffolding before any expert session begins. For each SKU in the products catalog, a Supabase Edge Function invokes Gemini 2.5 Pro with the product image, catalog description, and spec sheet to generate a calibrated ordered question set (3-5 questions), a product context summary, and key knowledge vectors. Results are cached in product_pre_seeds — one row per product — and are re-generated automatically when spe
No explicit evidence field yet. Require tests, screenshots, linked PRs, or reviewed outputs before marking complete.
No section body captured.
Machine-readable source fields
M-03 delivers the offline intelligence pipeline that pre-computes product knowledge scaffolding before any expert session begins. For each SKU in the products catalog, a Supabase Edge Function invokes Gemini 2.5 Pro with the product image, catalog description, and spec sheet to generate a calibrated ordered question set (3-5 questions), a product context summary, and key knowledge vectors. Results are cached in product_pre_seeds — one row per product — and are re-generated automatically when spec_sheet_url or description changes (via Postgres trigger + pg_net webhook), or when the cached row is older than PRESEED_CACHE_TTL_DAYS days (via pg_cron daily refresh). M-03 is the direct prerequisite for M-04: POST /api/sessions/start requires a valid product_pre_seeds row, and F-03 (first_question delivery at session open) depends entirely on the cached question_set being present. A demo seed script batch-processes all 80 demo SKUs idempotently before presentation day.
M-03
| auth | path | errors | method | request | response |
|---|---|---|---|---|---|
| service-key only — Authorization: Bearer SUPABASE_SERVICE_ROLE_KEY. Expert JWT and company JWT are rejected with 401. Internal endpoint: called only by pg_net triggers, pg_cron jobs, and the demo seed script. | /functions/v1/pre-seed-pipeline | - Missing or non-UUID product_id => 400 { error: 'product_id is required and must be a valid UUID', code: 'MISSING_PARAM', status: 400 } - No or invalid Authorization header => 401 { error: 'Unauthorized', code: 'UNAUTHORIZED', status: 401 } - product_id not found in products => 404 { error: 'Product not found', code: 'PRODUCT_NOT_FOUND', status: 404 } - Gemini response schema mismatch (wrong fields, question_set length < 3 or > 5, invalid enum) => 422 { error: 'Gemini response schema validation failed', code: 'PARSE_ERROR', status: 422, detail: string } - Gemini API non-200 status => 502 { er | POST | { product_id: string (uuid) } | HTTP 200: { status: 'ok', pre_seed_id: string (uuid), product_id: string (uuid) } |
M-03 does not create any new tables — all schema was established in M-00. M-03 adds pg_net extension (D-03-02) and three new database objects: preseed_on_product_insert() trigger function, preseed_on_product_spec_updated() trigger function, and preseed_ttl_refresh() PL/pgSQL function. The question_set JSONB internal structure — including depth_target and knowledge_dimension enums — is enforced by Gemini responseSchema at generation time and validated by the Edge Function before upsert; there is no Postgres CHECK constraint on JSONB internals. M-04 reads product_pre_seeds via SELECT WHERE product_id = product_id at session start — all demo SKUs must have a valid row (run D-03-04 seed script) before M-04 can be tested end-to-end. D-03-02 must be applied before D-03-03 (pg_net dependency).
| rls | name | indexes | purpose | key columns |
|---|---|---|---|---|
| Expert JWT can SELECT (required at session start). Service role can INSERT/UPDATE. No company JWT access. Configured in M-00 — M-03 does not modify RLS. | product_pre_seeds | - UNIQUE btree on product_id (from M-00 migration 003_indexes_rls.sql) - btree on generated_at for staleness check (from M-00 migration 003_indexes_rls.sql) | Cached Gemini 2.5 Pro output per SKU — one row per product, loaded at session start by GPT-4o interview conductor. Created in M-00; M-03 writes to it. | - 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 — array of 3-5 objects: { question_id: string, question_text: string, depth_target: surface|deep|expert, knowledge_dimension: failure_mode|symptom|fix|psi_range|safety_warning|field_tip|tool_required|part_number_reference } - context_summary jsonb NOT NULL — { product_overview: string, common_failure_patterns: string[], safety_considerations: string[], technical_complexity: low|medium|high } - key_vectors jsonb NULL — { primary_topic |
Pre-Seed Intelligence Pipeline
| id | name | type | inputs | outputs | description |
|---|---|---|---|---|---|
| D-03-01 | supabase/functions/pre-seed-pipeline/index.ts | service | - POST body: { product_id: string (uuid) } - Authorization header: Bearer SUPABASE_SERVICE_ROLE_KEY - Env secret: GEMINI_API_KEY — Google AI Studio API key with Gemini 2.5 Pro access - Auto-injected: SUPABASE_URL — project URL - Auto-injected: SUPABASE_SERVICE_ROLE_KEY — service key for DB reads and upsert | - HTTP 200: { status: 'ok', pre_seed_id: uuid, product_id: uuid } - HTTP 400: { error: 'product_id is required and must be a valid UUID', code: 'MISSING_PARAM', status: 400 } - HTTP 401: { error: 'Unauthorized', code: 'UNAUTHORIZED', status: 401 } - HTTP 404: { error: 'Product not found', code: 'PRODUCT_NOT_FOUND', status: 404 } - HTTP 422: { error: 'Gemini response schema validation failed', code: 'PARSE_ERROR', status: 422, detail: string } - HTTP 502: { error: 'Gemini API call failed', code: 'GEMINI_ERROR', status: 502, detail: string } - Upserted row in product_pre_seeds with all JSONB fie | Supabase Edge Function (Deno TypeScript). Accepts POST with body { product_id: string } and Authorization: Bearer SUPABASE_SERVICE_ROLE_KEY. Steps: (1) Validate product_id is present and a valid UUID format — return 400 if not. (2) Fetch products row for product_id — return 404 if not found. (3) Build Gemini 2.5 Pro request: system instruction sets HVAC/plumbing domain context; user content includes product.name, product.manufacturer, product.description (up to 3000 chars, truncated with notice if longer); if product.image_url non-null, download and include as base64 inlineData (mimeType image |
| D-03-02 | supabase/migrations/[timestamp]_preseed_triggers.sql (generate via: supabase migration new preseed_triggers) | migration | - products table (from M-00) - pg_net extension availability (built-in on Supabase PostgreSQL 15) - app.preseed_edge_fn_url value: full Edge Function URL - app.preseed_service_role_key value: Supabase service-role key | - pg_net extension enabled - Trigger function preseed_on_product_insert installed - Trigger function preseed_on_product_spec_updated installed - AFTER INSERT trigger preseed_product_insert active on products table - AFTER UPDATE trigger preseed_product_spec_update active on products table (spec_sheet_url and description columns only) - app.preseed_edge_fn_url and app.preseed_service_role_key database settings configured | SQL migration installing two PostgreSQL trigger functions using pg_net to call the pre-seed-pipeline Edge Function asynchronously. Steps: (1) CREATE EXTENSION IF NOT EXISTS pg_net. (2) ALTER DATABASE postgres SET app.preseed_edge_fn_url = '' — Quinn fills actual Edge Function URL before applying. (3) ALTER DATABASE postgres SET app.preseed_service_role_key = '' — Quinn fills actual service-role key before applying. (4) CREATE OR REPLACE FUNCTION preseed_on_product_insert() RETURNS trigger: calls pg_net.http_post(url => current_setting('app.preseed_edge_fn_url'), body => jsonb_build_object('pro |
| D-03-03 | supabase/migrations/[timestamp]_preseed_cron.sql (generate via: supabase migration new preseed_cron) | migration | - pg_cron extension (enabled in M-00) - pg_net extension (enabled in D-03-02) - products and product_pre_seeds tables (from M-00) - app.preseed_edge_fn_url and app.preseed_service_role_key settings (from D-03-02) | - app.preseed_cache_ttl_days database setting set to '30' - preseed_ttl_refresh() PL/pgSQL function installed - pg_cron job 'preseed-ttl-refresh' registered: schedule '0 2 * * *' | SQL migration registering a pg_cron job for daily TTL-based cache refresh. Steps: (1) ALTER DATABASE postgres SET app.preseed_cache_ttl_days = '30'. (2) CREATE OR REPLACE FUNCTION preseed_ttl_refresh() RETURNS void: SELECT product.id from products LEFT JOIN product_pre_seeds WHERE product_pre_seeds.id IS NULL OR generated_at < now() - make_interval(days => COALESCE(current_setting('app.preseed_cache_ttl_days', true), '30')::int); for each stale or missing product_id call pg_net.http_post with same URL, body { product_id }, and headers as D-03-02. (3) SELECT cron.schedule('preseed-ttl-refresh', |
| D-03-04 | scripts/seed-pre-seeds.ts | service | - Env: SUPABASE_URL (required) - Env: SUPABASE_SERVICE_ROLE_KEY (required) - Env: PRESEED_CACHE_TTL_DAYS (optional, default 30) - products table populated with 80 demo SKUs - pre-seed-pipeline Edge Function deployed and reachable (D-03-01) | - product_pre_seeds rows upserted for all products without a fresh cache entry - Per-product console lines: 'SEED [sku]', 'SKIP [sku]', or 'FAIL [sku] HTTP STATUS BODY' - Final summary: 'Complete — Seeded: X, Skipped: Y, Failed: Z' - Exit code 0 if failed === 0; exit code 1 if failed > 0 | Node.js TypeScript script (run via: npx tsx scripts/seed-pre-seeds.ts). Batch-seeds all demo products idempotently before presentation day. 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 exists (generated_at within TTL_DAYS days of now), log 'SKIP [sku]' increment skipped; else POST to /functions/v1/pre-seed-pipeline with { product_id } and Bearer header; HTTP 200: log 'SE |
| calls | purpose | service |
|---|---|---|
| - POST https://generativelanguage.googleapis.com/v1beta/models/gemini-2.5-pro:generateContent?key=GEMINI_API_KEY — multimodal request; contents array includes text parts (name, description) and optional inlineData parts (image/jpeg or application/pdf for spec sheet); generationConfig.responseMimeType = 'application/json'; generationConfig.responseSchema enforces question_set array (3-5 items), context_summary object, key_vectors object | Offline per-SKU intelligence generation — reads product catalog data and produces calibrated question set, product context summary, and key knowledge vectors. Never in the real-time session path. | Gemini 2.5 Pro (Google Generative AI — generativelanguage.googleapis.com) |
| - pg_net.http_post(url, body, headers, timeout_milliseconds) — called from preseed_on_product_insert() trigger on products INSERT - pg_net.http_post(url, body, headers, timeout_milliseconds) — called from preseed_on_product_spec_updated() trigger on products UPDATE (spec_sheet_url or description changed) - pg_net.http_post(url, body, headers, timeout_milliseconds) — called from preseed_ttl_refresh() invoked by daily pg_cron job | Enables non-blocking async HTTP calls from Postgres trigger functions and pg_cron jobs to the pre-seed-pipeline Edge Function without blocking the caller transaction. | Supabase pg_net |
| - supabase.from('products').select('id, sku, name, manufacturer, description, image_url, spec_sheet_url').eq('id', product_id).single() — fetch product data - supabase.from('product_pre_seeds').upsert({ product_id, question_set, context_summary, key_vectors, model_version: 'gemini-2.5-pro', generated_at: new Date().toISOString() }, { onConflict: 'product_id' }) — cache Gemini result | Hosts the pre-seed-pipeline worker — provides isolated Deno runtime with GEMINI_API_KEY secret and auto-injected SUPABASE_SERVICE_ROLE_KEY for authenticated Postgres reads and upserts. | Supabase Edge Functions (Deno runtime) |
- products table exists with columns: id uuid PK, sku text UNIQUE, name text, description text NULL, image_url text NULL, spec_sheet_url text NULL (created in M-00 migration 002_core_schema.sql)
- product_pre_seeds table exists with columns: id uuid PK, product_id uuid 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() (created in M-00 migration 002_core_schema.sql)
- pg_cron extension enabled on Supabase project (enabled in M-00 migration 001_extensions_companies.sql)
- GEMINI_API_KEY secret set on Supabase Edge Functions environment (Dashboard > Settings > Edge Functions > Secrets) — Google AI Studio key with Gemini 2.5 Pro access
- PRESEED_EDGE_FN_URL configured via ALTER DATABASE postgres SET app.preseed_edge_fn_url = '...' — full URL of the deployed pre-seed-pipeline Edge Function
- PRESEED_SERVICE_ROLE_KEY configured via ALTER DATABASE postgres SET app.preseed_service_role_key = '...' — Supabase service-role key used by pg_net trigger calls
No items captured.
1.0