| name | neon-prod-data-backfill |
| description | Safely run an ad-hoc data backfill/UPDATE against the Index production database (Neon project "Protocol", database protocol_prod) via the Neon MCP tools. Use when reclassifying rows, fixing bad statuses, or any one-off prod data mutation outside Drizzle migrations. Covers the mandatory sequence — validate the predicate with a control group, dry-run the UPDATE on the dev branch, snapshot a backup branch, execute in a transaction, verify exact counts — plus the Neon MCP gotchas (default branch/database resolve to empty neondb; always pass branchId + databaseName=protocol_prod). |
neon-prod-data-backfill
Ad-hoc prod data mutations (not schema migrations — those go through Drizzle) run
through the Neon MCP tools. This skill encodes the safe sequence proven in the
2026-07-06 stalled-opportunities backfill (299 rows reclassified, zero surprises).
Topology (also in CLAUDE.md → Neon Database Topology)
| Thing | Value |
|---|
| Prod project | Protocol → shiny-cloud-34341469 |
| Prod branch | production → br-fragrant-brook-ahexgsek (protected) |
| Dev branch (Railway dev env) | dev → br-late-tooth-ahlsfgdb — periodically reset from prod, realistic data |
| Database name | protocol_prod on both branches |
| Local-dev project | Protocol-dev-europe → patient-pine-89907813 (not for this) |
Gotcha: omitting branchId/databaseName resolves to the default neondb, which is
empty — you get relation "opportunities" does not exist. Always pass all three:
projectId, branchId, databaseName: "protocol_prod".
The sequence (do not skip steps)
- Size the problem + validate the discriminator with a control group (read-only,
prod branch). A predicate that matches ~100% of the target population means nothing
until you show it matches ~0% of a healthy control population. Example: stalled rows
matched "actor premise lapse within ±10 min of
updated_at" 299/299 while pending
rows matched 20/1398 (1.4% base rate) — that gap is the proof.
- Dry-run on the dev branch (
br-late-tooth-ahlsfgdb): run the sizing SELECT,
then the actual UPDATE, then verify counts moved exactly (before + delta = after).
- Snapshot a backup branch from production right before the write:
neon_create_branch with a dated name like backup-pre-<what>-YYYY-MM-DD.
(Prod also has 7-day PITR, but a named branch is an instant, explicit restore point.)
- Execute on prod with
neon_run_sql_transaction, bundling the UPDATE and the
verification SELECTs in one call. Keep the WHERE predicate in the UPDATE even if it
currently matches everything — it's the guard against racing writes.
- Verify exact arithmetic: target count → 0 (or expected), destination count ==
before + moved. Any off-by-N means stop and investigate via the backup branch.
- Preserve
updated_at unless the product needs it bumped — it's your audit trail for
provenance (and often the discriminator itself).
Tool call shape
neon_run_sql_transaction {
projectId: "shiny-cloud-34341469",
branchId: "br-fragrant-brook-ahexgsek", // or br-late-tooth-ahlsfgdb for the dev dry-run
databaseName: "protocol_prod",
sqlStatements: ["UPDATE ...", "SELECT status, count(*) ... GROUP BY status"]
}
Useful JSONB patterns for this schema: jsonb_array_elements(o.actors) a +
a->>'userId'; timing correlation via
abs(extract(epoch FROM (o.updated_at - COALESCE(p.retracted_at, p.updated_at)))) < 600.
Guardrails
- Prod writes need explicit user confirmation in the session; read-only sizing does not.
- Clean up the backup branch after a few days of confidence (
neon_delete_branch).
- If the mutation is repeatable/ongoing, promote it to a CLI under
services/api/src/cli/ (see expire-opportunities.ts) instead of re-running raw SQL.
See also
release-prod-safety — for schema-migration-level prod risk (frozen lockfile, destructive Drizzle migrations); this skill is for data fixes.railway-mcp-edge-city — Railway-side log correlation when diagnosing what wrote the bad data.
