Prompt Versioning (managed AI)

Prompt Versioning runbook (SUR-316)

The three managed-AI system prompts live in the service-role-only prompts table and are loaded per call by the anthropic-proxy Edge Function. This runbook covers editing a prompt, activating a new version, rolling back, and what to watch.

Model

One active row per name (transcribe · discover_canon · discover_with_custom), enforced by the prompts_one_active_per_name partial unique index.
Versions are immutable. A change is a new (name, version) row — never an edit of an existing row. That is what keeps prompt_version a stable telemetry key.
The Edge Function caches the active set in-memory for ~5 minutes per isolate, and fails open to the code constants (prompts.ts) on any DB read error — those calls record prompt_version = 0.
body, model, and max_tokens all come from the row. discover_with_custom’s body is a template with a {{customList}} placeholder filled at call time.

⚠️ A prompts write changes live AI behaviour and cost for all users within the cache TTL, with no deploy. Treat every prompts write like a production deploy — it is a spine change (GATING.md): founder sign-off + migration review.

Activate a new prompt version

Do the deactivate + activate in one transaction so the partial unique index never sees two active rows (and there is never a window with zero active rows):

begin;
  -- 1. Insert the new version (inactive).
  insert into public.prompts (name, version, body, model, max_tokens, is_active, notes)
  values ('transcribe', 2, $body$<new prompt text>$body$, 'claude-sonnet-4-6', 500, false,
          'why v2 exists');

  -- 2. Flip active in one step.
  update public.prompts set is_active = false where name = 'transcribe' and is_active;
  update public.prompts set is_active = true  where name = 'transcribe' and version = 2;
commit;

Propagation is up to the cache TTL (~5 min) and per-isolate. For discover_with_custom, keep the {{customList}} placeholder in body — the call site substitutes it.

Roll back

Re-activate the prior version (same one-transaction flip):

begin;
  update public.prompts set is_active = false where name = 'transcribe' and is_active;
  update public.prompts set is_active = true  where name = 'transcribe' and version = 1;
commit;

Because versions are immutable, rollback is always available as long as the old row exists.

Monitoring

prompt_version = 0 spike (in ai_usage_events or the managed_ai_call_succeeded PostHog event) → the prompts table is unreachable and the loader is failing open to code constants. This is an availability signal, not a quality regression. Check Supabase health; the cache retries on the next request (it is not poisoned on error).
ai_usage_events vs ai_usage_daily.request_count divergence → dropped best-effort event writes (DB pressure). Expected gap is ~0.
Quality by version: slice managed_ai_call_succeeded by prompt_version (token means, error/parse-failure rate). This is the foundation for the A/B follow-up.

Seed integrity (v1)

The seeded v1 rows (0028) are byte-identical to the prompts.ts constants; the supabase/test/prompts.test.js DB test diff-checks them against the __tests__/fixtures/prompts/*.txt snapshots. 0028 is generated by scripts/gen_0028_seed_prompts.ts — never hand-edit the seeded bodies; ship a new version row instead.

Grants

prompts, ai_usage_events, and insert_ai_usage_event are service-role-only (explicit REVOKE … FROM public, anon, authenticated; secdef functions search_path-pinned). Note: querying these tables from the SQL editor requires the service_role (or postgres) role.