Script a Production Data Fix with Rollback and Sign-Off

Tier: Core · Workflow area: Schema & Change Management · ~30 minutes

Production data fixes — the mispriced orders, the double-applied credits, the status stuck by an app bug — are where careers meet WHERE clauses. This playbook is the approval-gate discipline in miniature: the same pattern that later governs ERP agents (pb-51) applied to its original home, the manual data correction. AI removes the excuse that the safe way takes too long.

When to use

• Every write to production data outside the applicationthat is the whole rule. If a human is issuing DML against prod, it travels in this package.
• Bug-cleanup backfillsapp defect fixed, damaged rows remain; the fix plus its audit trail is exactly this shape.
• Data corrections requested by business teamsa finance-requested adjustment without a ticket-linked package is how audit findings are born.

When NOT to use

• When the application can do ita fix expressible as normal app operations (even tediously) inherits the app's validation, hooks and audit logging. Prefer that path for small counts.
• Schema changes disguised as data fixesif the "fix" involves ALTER, it is pb-10 with those gates.
• Truly massive correctionsrewriting 100M rows is a migration with batching and replica-lag management (pb-10's machinery), even though the sign-off discipline here still applies.

The recipe

1. 1.

   Establish the blast radius first — the count is the contract. Before any fix script exists, a SELECT proves what will change:

   Copy

   -- The damage: orders double-credited by the 2026-07-30 app bug
   SELECT count(*), sum(credit_amount)
   FROM order_credits
   WHERE created_at >= '2026-07-30 14:00+05:30'
     AND created_at <  '2026-07-30 18:30+05:30'
     AND source = 'auto_credit'
     AND credit_amount > 0
     AND order_id IN (SELECT order_id FROM order_credits
                      GROUP BY order_id, source HAVING count(*) > 1);
   -- -> 1,412 rows, ₹8,20,344.00
   

   That number — 1,412 — is now the contract every later step must match.

2. 2.

   Have AI draft the package around your SELECT.

   Copy

   Draft a production data-fix package for PostgreSQL 16. The defect:
   duplicate auto-credits (SELECT below identifies them — 1,412 rows).
   Fix: zero out the LATER duplicate of each pair, preserving the first.
   
   Produce:
   1. BACKUP step — copy affected rows to a timestamped side table.
   2. FIX script — single transaction, updates ONLY rows identified by
      the same predicate as my SELECT, with an affected-row count check
      that ROLLS BACK automatically if the count <> 1412.
   3. INVERSE script — restores previous values from the side table.
   4. AFFECTED-ROW REPORT query — before/after values per row, for
      the ticket.
   Use ctid/PK capture, not predicate re-evaluation, for the inverse.
   

   The count-guard is the package's soul. The fix core should look like:

   Copy

   BEGIN;
   CREATE TABLE fix_20260806_credits_backup AS
   SELECT * FROM order_credits WHERE id IN (/* duplicate-later ids */);
   
   WITH fixed AS (
     UPDATE order_credits
     SET credit_amount = 0,
         fix_ref = 'TICKET-4821'
     WHERE id IN (SELECT id FROM fix_20260806_credits_backup)
     RETURNING id
   )
   SELECT count(*) FROM fixed;
   -- Human reads: must print 1412. If not:
   -- ROLLBACK; investigate. Only on exact match: COMMIT;
   

   Note the shape: the transaction stays open across the count check, and a human (or a strict DO block asserting the count) decides commit vs rollback.

3. 3.

   Review the package like an adversary. Three questions, in order: Does the fix predicate exactly match the damage SELECT (not a paraphrase — the same text)? Does the inverse restore from captured rows rather than recomputing (recomputed predicates drift when data changes between fix and revert)? Is there a uniqueness guarantee that rows are counted once (a JOIN in the fix that can fan out invalidates the count contract)?

4. 4.

   Get the sign-off — named human, in the ticket, before execution. The ticket carries: the damage description, the SELECT and its count, the four scripts, the rehearsal result (next step), and an explicit approval from the data owner — for financial data, someone in finance, not just the DBA. "Approved in chat" is not sign-off; the approval lives where the audit will look.

5. 5.

   Rehearse on a copy. Restore or snapshot, run the package end-to-end: fix (count matches), report (values as expected), inverse (rows byte-identical to backup), re-fix. Five minutes that converts the package from plausible to proven.

6. 6.

   Execute once, attach evidence, keep the backup. Run in production at the agreed time, paste the actual count and the affected-row report into the ticket, and retain the side table for the agreed period (30–90 days per your policy) before dropping it — with the drop itself as a dated follow-up task, not a memory.

Make it yours

• SQL Serversame package with OUTPUT deleted.* INTO dbo.fix_20260806_backup capturing the before-image inside the UPDATE itself, @@ROWCOUNT as the count guard, and XACT_ABORT ON; wrap in an explicit transaction and have the runbook state the exact COMMIT/ROLLBACK decision rule.
• MySQLcapture the backup with CREATE TABLE ... AS SELECT first (no OUTPUT clause), use ROW_COUNT() for the guard, and be explicit about autocommit (SET autocommit = 0) so the check-then-commit pattern actually holds the transaction open.
• OracleRETURNING ... BULK COLLECT or a flashback-based safety net (AS OF TIMESTAMP verification of the before-image) strengthens the inverse; on 19c+, note the flashback retention window in the ticket as your extra rollback path.

Quality check

• The count guard is exact, not approximate"about 1,400" is not a contract. Predicate drift between damage-SELECT and fix script is the number-one package defect; diff them textually.
• The inverse was executed in rehearsalan inverse script that has never run is a hope. It ran, and restored bytes matched the backup.
• Sign-off precedes execution and lives in the ticketapproval after the fact is documentation of a violation, not a control.
• The fix is idempotent or guarded against re-runsomeone will double-click someday. Running the package twice must be a no-op (the fix predicate excludes already-fixed rows) or blocked by the count guard.