Give repair scripts a preview before they write

Article · Interrupted execution

“This script only updates the missing accounts.” That sentence is reassuring until a filter is wrong or the data changes after the operator checks it.

A repair tool deserves a preview. The preview should show scope and proposed changes, not just the number of rows it found.

For a tool that changes customer data, I prefer a plan-and-apply flow with explicit record versions. It gives the operator something concrete to inspect and gives the code a way to reject stale assumptions.

Plan the exact changes

The plan can include the tenant, record identity, current version, proposed operation, and the reason for it. Bound the selection and require an explicit choice for a wider scope.

A Rust model might be:

pub struct PlannedChange {
    pub tenant_id: String,
    pub record_id: i64,
    pub expected_version: i64,
    pub replacement_status: String,
}

The model is intentionally small. A real plan also needs its schema version, creation context, policy limits, and an identity that connects review to application. Protect stored plans because they may contain sensitive business information.

Show meaningful differences to the operator. “Will update 300 records” is not enough when 299 belong to another tenant. Include the selected scope and a safe sample or complete bounded list appropriate to the tool.

The reconciliation article explains how to find drift. The repair interface should make the resulting decisions inspectable before performing them.

Check versions again when applying

The database may change while somebody reads the preview. Use a conditional write rather than trusting the old snapshot:

let changed = sqlx::query(
    "UPDATE repair_accounts SET status=$1,version=version+1
     WHERE tenant_id=$2 AND id=$3 AND version=$4",
)
.bind(&change.replacement_status)
.bind(&change.tenant_id)
.bind(change.record_id)
.bind(change.expected_version)
.execute(&mut *tx)
.await?
.rows_affected();

Zero changed rows means the expected record version was not updated. Treat that as a conflict or absence according to the contract. Do not quietly remove the condition and retry the old write.

PostgreSQL's transaction-isolation documentation explains concurrent update behaviour. The application still chooses whether conflicts stop the whole plan or become explicit per-record outcomes.

Keep application and authorization together

Recheck the operator's current authority at application time. A previously generated plan is not a permanent grant. Bind it to the relevant environment and refuse a production apply using a staging plan.

Record applied, skipped, conflicted, and failed outcomes. A repair that changes some records before failing needs a resumable contract. Repeating it should not undo newer legitimate changes or duplicate external effects.

For external writes, a database version check is only part of the design. Use provider preconditions or documented idempotency where available, and expose uncertainty where not.

Test the stale preview

Generate a plan, modify one selected record through another connection, then apply. The changed record should produce the documented conflict. An unchanged record should follow the chosen atomic or per-record policy.

Also test the wrong tenant and environment, an oversized scope, and a repeated apply. A preview is useful only if the apply path respects what it showed. Otherwise it is a nice report beside an uncontrolled script.

A small command with a server-saved plan

The complete command has two modes: preview a tenant, or apply a reviewed plan file. It connects using a trusted database URL from deployment configuration. The operator identity comes from PostgreSQL's current user, not an actor name inside the plan.

Preview checks that identity against an operator-scope table and reads the environment from the database. It selects at most ten eligible rows, with one extra row used to detect an oversized scope. It refuses that scope instead of silently changing the first ten. The exact JSON bytes are saved in a plan table under their SHA-256 before being returned for inspection.

Apply bounds the file to eight kilobytes, checks its schema version, operator, environment, and current tenant permission, and requires those exact saved bytes. Changing the replacement status in a local file does not produce a newly authorized plan. This hash is a plan identity, not a substitute for protecting the database tables and operator credential.

The integration test previews two rows, changes one through another connection, then applies. The stale row returns conflict. The unchanged row becomes ready and advances its version. Both outcomes are saved in the same transaction as their changes. Repeating apply returns those saved outcomes without advancing the version again.

The test also rejects a foreign tenant, an oversized selection, a changed plan, an environment mismatch, and a revoked operator. The library and tests keep these checks beside the conditional writes.

This is a bounded local-database repair, not a distributed transaction or a finished enterprise approval service. It uses fixture authorization tables and does not promise immediate revocation halfway through an already running transaction. Protect plan creation and operator grants in deployment, choose plan expiry where needed, and keep external effects out until their own recovery contract is defined.