Data Change Script

Data change scripts

A deploy can be rolled back. A script that rewrote ten thousand rows cannot, unless you built the ability in before you ran it.

1. Dry run is the default; applying needs a flag

node scripts/fix-orphans.js              # reports what it WOULD do, changes nothing
node scripts/fix-orphans.js --execute    # does it

Never the other way round, and never --dry-run as the opt-in. The half-second of thought needed to add --execute is the entire safety mechanism.

The dry run must exercise the real read path and the real decision logic — same query, same filter, same transformation — and stop at the write. A dry run that only prints "would update 412 rows" without having computed the 412 new values has tested nothing.

2. Print the plan, not a summary

The dry run's output is the artifact that gets reviewed. Make it reviewable:

target: STAGING
scanned:   12,480 rows
matched:       412
  UPDATE item#a1b2  status: 'draft' -> 'published'
  UPDATE item#c3d4  status: 'draft' -> 'published'
  SKIP   item#e5f6  already published
  ...
unchanged: 12,068

Show old and new values, identify rows by id, and show the skips — the skip count is what tells you the filter is right. If the plan is too long to read, write it to a file and report the path; do not truncate it silently.

3. Idempotent, always

Running it twice must be indistinguishable from running it once. Scripts get interrupted, half-applied, and re-run by someone who does not know it already ran.

• Match on the pre state, so already-fixed rows fall out of the filter naturally.
• Never blind-append to a list, increment a counter, or add a tag without checking.
• Make the second run's output the proof: matched: 0 is the completion signal.

4. Bound the blast radius

• Put the filter in the query, not in a post-fetch if. A scan with no limit and client-side filtering will one day be shipped with the filter removed.
• Support --limit N and use it for the first real run. Apply to ten, check those ten, then run the rest.
• Support --id <id> for a single-row rehearsal.
• Refuse to run if the match count exceeds a sanity threshold unless the operator passes the expected number explicitly. A script that matches 100% of the table when you expected 3% has a bug, and the threshold is what catches it.

5. Be able to undo it

Before the first --execute:

• Take a backup, or write the pre-state of every touched row to a timestamped file next to the script's output. That file is the rollback.
• Prefer soft changes — set a flag, move to an archive collection — over hard deletes. Hard-deleting is a decision you cannot revisit.
• Note the reverse operation in the script's header comment. If you cannot describe it, you are not ready to run.

6. Never print secrets, never widen access

• Log ids and field names. Do not log tokens, password hashes, keys, full personal records, or anything you would not paste into a ticket.
• Run with the narrowest credential that works. A cleanup script does not need admin.
• If the script needs a secret, read it from the environment or a secret store at run time. A secret written into the script is in git forever, including after you delete the line.

7. Leave a record

Commit the script even though it runs once. It is the only description of what happened to that data.

The header comment carries: what it changes, why, the date it was run, which environment, the match count, and the rollback path. Six months later, when a row looks strange, this file is the answer to "what touched it?"

8. Reviewing someone else's before running it

Read it in this order, and stop at the first one that fails:

1. Which environment does it resolve with no flags? If the answer is production, stop.
2. What is the filter? Read it literally. Does it match what the description claims?
3. Is there a write outside the --execute branch? Including a log written to a table, a cache invalidation, or an event emitted.
4. Is it idempotent? Trace a second run.
5. What is the rollback?

Then dry-run it yourself and read the plan. Do not accept someone else's dry-run output as evidence — it was produced against a different environment than the one you are about to touch, often enough to matter.

Checklist

• Dry run is the default; --execute applies
• Dry run computes real values and prints old to new by id, with skips
• Idempotent; second run matches nothing
• Filter in the query; --limit and --id supported; sanity threshold
• Pre-state captured or backup taken before the first apply
• Soft change preferred over hard delete; reverse operation written down
• No secrets printed; least-privilege credential
• Script committed with a header recording what was run, where, and when