Runbook — pullmd provenance migration push (ID-42)

Status: ready to push (out-of-band, manual). Owner: Liam (holds the DB / prod credentials). Scope: apply the source-controlled migration supabase/migrations/20260526074944_id42_pullmd_provenance.sql to staging, verify, then prod; regenerate and commit the TypeScript types.

This is a manual, out-of-band procedure. The Executor authored the migration, the controlled-vocabulary update (docs/ontology/16-extraction-method.md), and this runbook; the Executor holds no DB or prod credentials and does not run supabase db push or supabase gen types. Per the OQ-42.4-B (S265) decision, Liam runs the push steps below.

This file is separate from docs/runbooks/pullmd-deploy.md (ID-42.6, the application deploy runbook). Do not merge the two — this one covers only the schema migration push.

What the migration does

Pure DDL, function-free, idempotent (ADD COLUMN IF NOT EXISTS, CREATE INDEX IF NOT EXISTS, DROP CONSTRAINT IF EXISTS before re-add), file-level SET search_path = public, extensions. Mirrors the op_id precedent (20260521203414_t8_op_id_propagation.sql). It:

Adds source_documents.pullmd_share_id text NULL + a partial index (idx_source_documents_pullmd_share_id WHERE pullmd_share_id IS NOT NULL) + a column comment. Carries the pullmd X-Share-Id permalink (8-hex; GET /s/:id round-trips).
Adds source_documents.extraction_method text NULL + a CHECK constraint (source_documents_extraction_method_check) admitting NULL or one of the 11 values (rss_content, fetch, jina_reader, firecrawl, summary_fallback, pullmd_readability, pullmd_playwright, pullmd_cloudflare, pullmd_reddit, pullmd_trafilatura, docling) + a column comment.
Extends the existing feed_articles_extraction_method_check constraint to admit the same 11 values. This is additive — the legacy 5 (rss_content, fetch, jina_reader, firecrawl, summary_fallback) stay valid; firecrawl is not pruned (Ratified-S237 lockstep — the prune stays T7-gated and DB-driven, out of ID-42 scope). Pre-existing extraction_method='firecrawl' rows therefore survive unchanged.

Because the CHECK redefinition only widens the admitted set, applying it to a populated table cannot reject any existing row. No data migration is required.

Pre-flight

Confirm the linked project before any push. supabase db push operates against the linked project recorded in supabase/.temp/project-ref, which is independent of .env.local. Always check it first and relink if it has drifted:
Terminal window
```
1
cat supabase/.temp/project-ref
2
# If it is NOT the project you intend to push to, relink:
3
# supabase link --project-ref <correct-ref>
```
Canonical refs: staging branch turayklvaunphgbgscat, production rovrymhhffssilaftdwd.
DDL via CLI only. Apply this migration with supabase db push only. Never via MCP execute_sql / apply_migration, and never by hand-running the SQL against the live DB. (CLAUDE.md “DDL via CLI only”.)
Confirm the migration is the latest local file and has not already been applied:
Terminal window
```
1
supabase migration list   # 20260526074944_id42_pullmd_provenance should be local-only
```

Step 1 — Push to staging FIRST

1
# Relink to staging if the pre-flight showed a different ref.
2
supabase link --project-ref turayklvaunphgbgscat
3
cat supabase/.temp/project-ref   # expect: turayklvaunphgbgscat
4

5
supabase db push

Then run the verification queries in the Verification section below against staging (via the SQL editor or psql). Do not proceed to prod until staging verifies clean.

Step 2 — Push to prod (after staging verifies)

Production pushes are an explicit opt-in (the --env=prod posture — prod is never the safe default). Relink to prod deliberately, confirm the ref, then push:

1
supabase link --project-ref rovrymhhffssilaftdwd
2
cat supabase/.temp/project-ref   # expect: rovrymhhffssilaftdwd
3

4
supabase db push

Re-run the verification queries against prod.

Step 3 — Regenerate and commit types

After the prod push succeeds, regenerate the TypeScript types from the live prod schema and commit the regenerated file (the Executor deliberately did not regenerate types — they must reflect the post-push reality):

1
/opt/homebrew/bin/supabase gen types typescript \
2
  --project-id rovrymhhffssilaftdwd --schema public \
3
  > supabase/types/database.types.ts
4

5
git add supabase/types/database.types.ts
6
git commit -m "chore(db): regenerate types after id42 pullmd provenance migration"

After regeneration, source_documents should declare pullmd_share_id: string | null and extraction_method: string | null in its Row/Insert/Update blocks.

Verification

Run against each target (staging, then prod):

source_documents shape — two new columns, the partial index, and the CHECK:
```
1
\d source_documents
```
Expect pullmd_share_id text + extraction_method text, the idx_source_documents_pullmd_share_id partial index, and the source_documents_extraction_method_check CHECK constraint.

feed_articles CHECK admits the new values + NULL:

1
-- Should succeed (rolled back so no test rows persist):
2
BEGIN;
3
INSERT INTO feed_articles (extraction_method /*, …required cols… */) VALUES ('docling');
4
INSERT INTO feed_articles (extraction_method /*, …required cols… */) VALUES ('pullmd_readability');
5
INSERT INTO feed_articles (extraction_method /*, …required cols… */) VALUES (NULL);
6
ROLLBACK;

(Populate the other NOT NULL columns as needed for the insert to reach the CHECK; the point is the CHECK does not reject docling / pullmd_* / NULL.)

Legacy firecrawl rows survive — count unchanged across the migration:
```
1
SELECT count(*) FROM feed_articles WHERE extraction_method = 'firecrawl';
```
Record the count before the push (the documented baseline at decision time was 27 rows) and confirm it is unchanged after.
Schema parity prod ↔ staging green. After both pushes, trigger the manual schema-parity.yml workflow (GitHub Actions → “schema-parity”, run workflow). It must report “Production and staging schemas match” for public, extensions, auth, and storage. A non-empty diff means the two environments drifted — resolve before considering the migration landed.

Rollback

The migration is additive and non-destructive, so a forward fix is preferred over a rollback. If a reversal is genuinely required, author a new migration (never edit the applied file) that drops the added columns / index and restores the original feed_articles_extraction_method_check (legacy 5 values) — and push it through the same staging-first-then-prod sequence.

References

Migration: supabase/migrations/20260526074944_id42_pullmd_provenance.sql
Spec: docs/specs/id-42-pullmd-deploy/TECH.md §WP-D (Inv-8 / Inv-9 / Inv-17)
Controlled vocabulary: docs/ontology/16-extraction-method.md
op_id precedent: supabase/migrations/20260521203414_t8_op_id_propagation.sql
Env / linking guidance: docs/runbooks/local-development.md
Schema parity gate: .github/workflows/schema-parity.yml