A dry run should leave the changes that were reviewed

The preview and the command can select different work

The preview shows two products, the person approves them, and the apply command finds three. Nobody changed the query; the data changed underneath it. Product three has nevertheless arrived in a decision it was never part of. If approval is meant to authorise the work shown on screen, execution needs the saved membership of that proposal rather than another run of the discovery query.

A useful dry run should therefore leave a concrete change plan: identified records, expected old state, proposed new state and the reason for each change. The plan is a product of the preview, not a promise to repeat discovery later. It allows the operator to answer what was proposed even after the source has changed, and gives execution something precise to test. A log saying would update two rows leaves most of that information behind.

The worked example has plan P7 with products 12 and 13, each expected in category A at version 1, each proposed to move to B. Before application, product 13 changes to C at version 2 and product 14 is added in category A. The resource executes a disposable SQLite model of this situation, with independent row application and an alternative all-or-nothing comparison. No production data or real approval service is involved. The case distinguishes membership drift from stale-state drift because they require different protections. The plan itself needs validation before review: unique record identities, supported operation types, valid proposed values and a readable summary of any exclusions. Duplicate plan rows can otherwise make an outcome ledger look ambiguous even when the database behaved consistently. In this two-row fixture those properties are supplied and inspectable; a production plan generator should enforce them. The person approving should see the same validated contents that the executor later accepts.

Save the membership and the conditions

P7 keeps products 12 and 13 even after 14 becomes a match for category A. Each saved row includes old category, proposed category and expected version. The version matters because a record can go from A to C and back to A while its visible category ends where it started. Detecting that intervening change depends on every relevant writer incrementing the version. The example assumes that convention; a working service must make it true across the paths allowed to change the product.

The script identifies P7’s exact contents by hashing a specified JSON encoding: sorted keys, compact separators and UTF-8 bytes with SHA-256. Python documents those serialization options.[1] That makes this encoding reproducible, without making it universal cross-language canonical JSON or an authorised signature. Review still needs a trusted link between those contents and the person permitted to approve them. A plan and hash from the same untrusted sender can agree perfectly without telling us that anybody with authority saw either.

Product 13’s current C/version-two state no longer meets the reviewed A-to-B proposal. The version marks the stale observation, while the category explains the disagreement to the operator. For this artificial category edit, those are the relevant conditions. A price, access or routing change may also depend on another field, relationship or policy version. Include that dependency or use a resource version covering it. Comparing only convenient columns would let a technically precise plan proceed after the very fact that would change the reviewer’s decision had moved.

Creating the plan should also have a clear observation boundary. If discovery reads a large changing dataset page by page, a row count is not proof that it captured the intended population. Establish that selection contract before treating the saved plan as complete. Once generated, retain its contents and expiry or review policy. The plan solves execution membership drift; it does not retroactively fix an incomplete discovery or make an old operational decision appropriate forever.

Apply a row only when its expected state still holds

The conditional UPDATE asks for one product with the expected category and version. SQLite can execute that statement successfully while changing zero rows, because no row met its WHERE condition.[2] The caller checks the affected-row result provided by Python’s SQLite interface.[3] Otherwise the application can announce applied after the database correctly declined every requested change. SQL success answers whether the statement ran; the row count tells this caller whether its precondition allowed the proposed update.

Product 12 satisfies the condition and becomes B at version 2. Product 13 no longer satisfies it and is recorded as conflict, remaining C. Product 14 stays A because execution iterates the saved plan instead of rediscovering matches. Those are the executed results. The table places the unapproved third product beside the approved rows so the central boundary remains visible. It should be possible to inspect not only what changed, but why a tempting additional change did not occur.

For independent changes, update the product and record its outcome in one local transaction. The ledger is keyed by plan and product, so a repeated P7 sees 12 already applied and 13 already conflicted. It does not apply 12 twice or turn the recorded conflict into fresh authority. That boundary works because both writes share SQLite. If execution instead calls another system, the receiver needs its own duplicate and outcome contract; the local ledger cannot decide whether a remote operation succeeded when its response was lost.

A conflict on 13 calls for current evidence and another decision. Automatically changing expected version one to two would remove the condition the reviewer approved, rather than resolve the disagreement. Keep P7’s original outcome even if the category later becomes A again. A revised plan may then be appropriate, but it must say what is being approved now instead of making the first proposal appear to have remained valid all along.

UPDATE products
SET category = ?, version = version + 1
WHERE id = ? AND category = ? AND version = ?;
-- Require exactly one affected row for this planned change.

Choose partial completion or a single batch deliberately

The independent-row policy leaves 12 applied and 13 conflicted. That can suit unrelated category corrections, provided the outcome ledger makes partial completion explicit. The alternative comparison puts the same candidate changes inside one transaction and rolls everything back when 13 conflicts. In that executed case, 12 remains A, 13 remains C and 14 remains A. The difference is not a technical preference: it changes what an operator can expect when one precondition fails. The commit policy belongs in the reviewed plan as well as its rows.

An atomic batch can preserve a meaningful group decision, but long-running transactions may create operational costs and one late conflict can undo substantial work. Independent rows can make progress and expose more complex partial outcomes. If the changes must be coherent as a group, saving per-row successes is not equivalent. If they are genuinely independent, insisting on total rollback can unnecessarily postpone valid corrections. Ask which mixed state the business can accept before selecting the transaction boundary. The review screen should say which policy approval selects; an apply-time default would otherwise make that group decision without the reviewer.

Reversal needs a fresh set of preconditions too. Recording A before B does not establish that writing A back tomorrow is safe. Somebody may have made a legitimate later change, and external effects may already depend on B. A reversal plan should identify the current expected state, the proposed compensation and what cannot be undone. The toy changes are simple data fields, but that simplicity should not become a general undo guarantee attached to every dry-run system.

A bounded script and small JSON plan may be enough for a one-off correction; the mechanism need not grow into an approval platform. It still needs stable identities, a trusted connection to review, checked conditions and recorded outcomes. P7 leaves a useful account: 12 applied, 13 conflicted, 14 never belonged to the plan. That last result is as deliberate as the first two. It is how the apply step keeps the promise made by the preview instead of quietly enlarging the reviewer’s decision.