A Two-Step Apply for Agent Writes: Dry Run, Then Commit

Before either step runs, the queue record needs to hold more than the payload. Store four things:

1. The proposal itself: the operation, its target and its parameters.
2. The expected effect: the row counts, the before and after values, and any side effects the reviewer acknowledged.
3. A proposal hash over the payload, so a changed payload cannot be replayed under an old approval.
4. An approval record with who approved, when, and when the approval expires.

Binding approval to the specific proposal matters because a changed payload, destination or scope needs the review that applies to it in this change preview playbook. The expected effect is the part most teams skip, and it is the part that makes the commit check possible.

A worked example. An agent proposes to reassign 412 open leads from owner Alex to owner Sam. The queue stores: operation update leads set owner_id where owner_id = 'alex' and status = 'open', expected counts {updated: 412, skipped: 17, excluded_manual_exception: 3}, and the before/after owner for each record. Eighteen minutes later a rep reassigns 40 of those leads by hand and three new open leads arrive from a campaign. The live dry run at commit time now returns {updated: 375, ...} and the commit refuses.

The dry run takes the stored proposal, opens a transaction, executes the statement, reads the affected rows, and rolls back. Nothing is written. What comes back is the reviewer's contract:

• A diff of rows inserted, updated and deleted, with before and after values for the columns that change.
• Row counts per category, including records deliberately skipped, so the reviewer can tell "excluded by rule" from "not found" in this change preview playbook.
• A snapshot token that identifies the state the measurement was taken against.
• Any constraint errors, privilege failures or validation problems the statement raised.

The rollback approach is not exotic. API platforms use a dry run mode for operations with complex validation rules, returning the normal error codes while the task the request would complete does not take place in the Apiture dry run documentation. The same documentation is honest about the limit: a successful dry run does not guarantee the real request will succeed, because other users may have changed application state between the two calls in the Apiture dry run documentation. That caveat is exactly why the commit needs its own check rather than trusting the earlier preview.

Do not sprinkle if dry_run branches through your write code. Split planning from execution so that immediate-apply is simply execute(plan()) in this discussion of dry run race conditions. That keeps one code path for the mutation and one for the measurement.

with conn.transaction(isolation="repeatable_read") as tx:
    snapshot = tx.snapshot_token()          # identifies the state measured
    before = tx.fetch_all(select_sql)       # rows the statement will touch
    result = tx.execute(proposal.sql, proposal.params)
    after = tx.fetch_all(select_sql)
    diff = build_diff(before, after)
    tx.rollback()                           # nothing is written

return {
    "diff": diff,
    "counts": {"inserted": diff.inserted, "updated": diff.updated, "deleted": diff.deleted},
    "snapshot_token": snapshot,
    "errors": tx.collected_errors,          # constraint, privilege, validation
}

The read set matters. If you select the rows after the mutation inside the transaction, you see the post-state; select them before and after to get a true before/after without ever committing.

The commit takes the proposal, the stored expected effect and the snapshot token. It re-runs the measurement, compares, and either writes or returns a mismatch report.

The comparison rule should be strict on what the reviewer saw and tolerant only where you can defend it:

If everything matches, write inside a single transaction using the same snapshot, and record the diff that was actually applied. If anything mismatches, do not partially apply. Return a mismatch report: the stored expected counts, the fresh counts, the list of records that changed, and the records that appeared or disappeared. The reviewer sees the same before-and-after format they approved, now with the drift highlighted in this change preview playbook.

Concurrency control choices matter here. Optimistic concurrency works by validating at commit time and aborting when the data changed since the read, which is exactly the shape of a two-step apply; pessimistic locking holds rows and avoids the abort but pays in contention and blocking in the Spanner concurrency control documentation. For agent writes that sit behind a human approval queue, optimistic checking plus a re-measure is usually the right default, because approvals can wait minutes or hours and you do not want a lock held across a human decision.

One more safeguard: every write tool should accept an idempotency key and store it with the outcome, so a retried commit returns the original result instead of doing the work twice in this idempotent tool design guide. Retries are where "create once" becomes "create twice" in this analysis of tool calling costs.

For a SQL update, the snapshot token can be a transaction snapshot plus a table-level change stamp. In PostgreSQL, a repeatable read transaction sees a consistent snapshot for its duration, so the before and after reads are comparable; in Spanner terms this is comparable to a repeatable read or serializable read that the commit later validates against in the Spanner concurrency control documentation.

A simple, portable snapshot token is a hash over SELECT max(updated_at), count(*) for the target table plus the maximum transaction id you can see. It is cheap and catches most drift. It is not perfect: a delete followed by an insert can leave both values unchanged. If your table has no reliable change column, add one, or fall back to a hash of the primary keys and version columns in the affected set.

Privileges belong in the design, not in an afterthought. The agent's database role should hold only what the write needs. PostgreSQL's grant system follows an allow list model, so a role gets no access to objects except those explicitly granted, and least privilege means separate read and write roles per use case in the Prisma privileges guide. The AWS guidance is the same: create roles with specific permission sets and assign them to users rather than granting directly, and keep the master user out of application paths in the AWS roles blog. A dry run executed under a read-only role is a useful safety net: if the statement needs a privilege the role lacks, the dry run reports the error before a human wastes time approving it in this Stack Overflow answer on read-only users.

Auditing closes the loop. Enable statement-level logging for the write role so the commit is recorded independently of your application logs; tools that extend native logging with more detail exist for exactly this purpose in the AWS pgAudit guide.

For an API mutation, you usually cannot roll back inside a transaction you control. Two mechanisms replace the rollback: a provider dry run mode, and a precondition on the commit.

Where the provider offers ?dryRun=true, use it for validation and to learn the error codes the mutation would raise in the Apiture dry run documentation. Where it does not, model the expected effect yourself: fetch the resource, compute the field-level diff, and store that as the expected effect.

The commit then sends the mutation with a precondition. Common forms:

• An ETag or If-Match header carrying the version the reviewer saw.
• A version or updated_at field in the request body that the server validates.
• A compare-and-set parameter, such as updating only where the current value equals the expected value.

If the precondition fails, the server returns a conflict status rather than applying the change. Treat that as your mismatch report and re-present the diff. The dry run documentation is explicit that state can change between the two calls and clients should be prepared to handle the error responses even after a passing dry run in the Apiture dry run documentation.

Two practical rules for the API case. First, the dry run must call the same validation code as the commit, or it is theater. Second, the expected effect should be expressed in terms the reviewer can verify: which record, which field, old value, new value, plus any known side effects such as a notification or a downstream automation in this change preview playbook. Side effects you cannot suppress belong in the approval, not in the error log.

File writes are the easiest place to get this right because the snapshot token can be the file content itself. Compute a hash of the current file, produce the diff the write would create, store both with the proposal, and at commit time recompute the hash. If it changed, reject and show both diffs.

Git gives you a worked model. git add -n shows what would be staged without staging anything, which is useful for checking ignore rules and wildcards before a recursive add in this writeup on git dry run. Git's commit path also offers a dry run that summarizes what would be included, with a porcelain output format for programmatic use in this writeup on git dry run. The lesson for agent file writes is that the preview should be machine-readable as well as human-readable, because the commit step is going to compare against it.

For a file write, the expected effect is the line diff plus the pre-image hash. The commit applies with an atomic replace (write to a temporary file, fsync, rename) and records the post-image hash. If the pre-image hash no longer matches, the tool returns the mismatch report with the reviewer's diff and the current diff side by side. This also gives you rollback for free: the pre-image is the rollback artifact.

One caution taken from the git discussion: -n does not mean dry run for every subcommand, and a flag that looks like a preview can mean something else entirely in this writeup on git dry run. Name your endpoints dry_run and commit explicitly rather than overloading an existing parameter.

Use a decision rule to pick the check strength. The blast radius and reversibility of the action determine how strict the commit comparison should be in this approval workflow guide.

When the commit rejects, do not silently re-propose. Return the mismatch report to the queue, mark the proposal stale, and require a fresh approval against the new diff. The reviewer's decision applied to the old state, not the new one.

Watch for the approval expiring as well. Store an expiry with the approval so that a proposal approved on Friday cannot commit on Monday against a changed dataset without re-review.

Finally, instrument the ratio of commits that reject. A high mismatch rate usually means the proposal is being written against a stale read at creation time, and the fix is earlier: have the agent draft from a fresh read, and keep the queue latency short. The cost model research on agentic query execution makes the same point in a different setting, that where a workflow places its expensive or stateful steps changes both cost and correctness, and that choice is often only revealed at runtime in this cost-aware optimization paper.

If you are building agents that write across many systems, keep the dry run and commit logic in one shared tool layer rather than in each agent. A skill library is a practical way to package that layer so each coding agent installs the same reviewed write path: Quanta Skills lets you install a skill into your coding agent or put an expert on the job.