neon-prod-data-backfill

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)

1. 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.
2. 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).
3. 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.)
4. 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.
5. Verify exact arithmetic: target count → 0 (or expected), destination count == before + moved. Any off-by-N means stop and investigate via the backup branch.
6. 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.