Agent failure labs / Guide 58
AI Database Update Safety: Stop a Stale Agent Overwriting a Human
Reproduce a stale AI database record update that overwrites a human correction, then require tenant scope, exact field approval, and atomic version preconditions.
Illustrative engineering guide. Examples are not client deployments, production-ready integrations, or evidence of measured results. Validate the design with your domain and security owners.
“Correct Acme’s billing city to Pune.” In this hypothetical workflow, an assistant reads customer record version seven and prepares a patch. Before approval completes, an operator changes the city to Mumbai after speaking with the customer, producing version eight. The assistant then writes its older interpretation over the newer human correction. Both actions used the right record identifier; the lost update came from ignoring time.
This failure lab focuses on a single business-record patch, not schema migration or free-form SQL generation. A correctly parameterized query can still overwrite newer information. The missing control is a precondition binding the intended field change to the record version that the requester actually reviewed.
Reproduce the Lost Update
The unsafe mock copies the current row and applies the old patch without checking its expected version. The guarded function checks tenant, record, version, editable fields, value type, and an exact approval tuple. Its output is either a conflict or a new in-memory row. It never connects to a database.
This is a deterministic synthetic simulation of a possible failure, not an actual LLM experiment or a real database concurrency test. Both implementations receive the same stale proposal and current record. All customer names, places, and identifiers are fixture data. There are no network calls, file writes, destructive queries, or production mutations.
from copy import deepcopy
current = {"tenant": "t-a", "id": "customer-17", "version": 8,
"city": "Mumbai", "credit_limit": 5000}
proposal = {"tenant": "t-a", "id": "customer-17", "expected_version": 7,
"patch": {"city": "Pune"}}
approval = ("t-a", "customer-17", 7, (("city", "Pune"),))
def naive(row, request):
result = deepcopy(row)
result.update(request["patch"])
return result
def guarded(row, request, approved):
if (request["tenant"], request["id"]) != (row["tenant"], row["id"]):
return "not_authorized"
if request["expected_version"] != row["version"]:
return "version_conflict"
patch = request["patch"]
if set(patch) != {"city"} or not isinstance(patch["city"], str) or not patch["city"].strip():
return "invalid_patch"
binding = (row["tenant"], row["id"], row["version"], tuple(sorted(patch.items())))
if binding != approved:
return "approval_mismatch"
return dict(row, **patch, version=row["version"] + 1)
assert naive(current, proposal)["city"] == "Pune"
assert guarded(current, proposal, approval) == "version_conflict"
assert current["city"] == "Mumbai"
fresh = dict(proposal, expected_version=8)
fresh_approval = ("t-a", "customer-17", 8, (("city", "Pune"),))
assert guarded(current, fresh, fresh_approval)["version"] == 9
assert guarded(current, dict(fresh, tenant="t-b"), fresh_approval) == "not_authorized"
assert guarded(current, dict(fresh, patch={"credit_limit": 99999}), fresh_approval) == "invalid_patch"
assert guarded(current, fresh, approval) == "approval_mismatch"
print("Stale update blocked; fresh, exact patch can progress.")The fixed rerun does not choose between Pune and Mumbai. It recognizes that the earlier decision is stale and returns a conflict. Only a fresh proposal with new approval produces version nine. That distinction matters: optimistic concurrency detects incompatible timing, but it does not decide whose business information is correct.
Do Not Ask the Model to Remember Freshness
A prompt saying “check the latest data before writing” can trigger another read, but another read followed by an unconditional write still contains a race. The database must enforce the expected version as part of the mutation. A model’s statement that it checked version eight is not a substitute for an atomic predicate at the storage boundary.
PostgreSQL transaction isolation documentation explains snapshot behavior and re-evaluation of update conditions after concurrent updates. A production implementation can use an update constrained by tenant, primary key, and expected version, incrementing the version in that same statement. Require exactly one affected row; zero rows means no authorized matching precondition, not successful completion.
Do not use the Python copy-and-return function as a concurrency primitive. Two workers could independently validate the same row and return identical next versions. Its purpose is to teach the condition. Integration tests must exercise the actual database statement, transaction boundaries, and any alternate writers that might forget to increment the version.
Constrain the Patch Surface
The model should propose a structured patch for a permitted business operation, not arbitrary SQL or a complete replacement row. Server-side field allowlists prevent a city correction from modifying credit limits, tenant ownership, or administrative flags. Type validation alone is insufficient: a valid integer can still be an unauthorized change to a sensitive field.
OWASP authorization guidance recommends object-specific checks and server-side enforcement. Derive tenant scope and actor rights from the authenticated session. The fixture’s tenant equality is only a simulated binding; production must not trust a caller to assert both sides of that comparison. Record visibility and permission to change billing data are separate decisions.
Store the approved patch, expected version, actor, purpose, and expiry. OWASP transaction authorization guidance recommends preventing changes to significant data after authorization. If the assistant adds a second field or changes the city after review, reject the operation rather than reusing approval for the general intention to “fix the record.”
Customer notes and imported spreadsheets are untrusted evidence. Text saying “admin override approved” cannot add editable fields or suppress version checks. One orchestrator, a typed business API, and a database repository are sufficient for this lab. A specialist entity-resolution agent may help distinguish two Acme companies, but selection still needs confirmation before any patch is considered.
Conflict Handling Is a Product Behavior
When a conflict occurs, fetch the current authorized record and show a compact before-and-after comparison: the assistant reviewed Pune as a proposed change, while the operator has now recorded Mumbai. Ask whether the new evidence changes the request. Do not automatically rebase the stale patch onto the latest version, because that would convert a conflict detector into a delayed overwrite.
For approved writes, persist an operation identifier and payload binding with the mutation in one transaction. If the client times out after commit, the outcome is unknown until the authoritative operation record is retrieved. Retrying the same operation should return the recorded result; a changed payload under the same identifier should fail rather than create a second mutation.
A returned status: success must come from the configured database-facing service and match the record, operation, applied fields, and resulting version. Reading the desired city later is not enough to prove this operation succeeded, because another actor may have independently entered the same value. Preserve causal operation evidence instead of guessing from current state alone.
The snippet omits the authentication source, database transactions, persistent approvals, state persistence, provider semantics, actual writes, and audit retention. It assumes correct versions and one editable field. Deterministic checks cannot protect against unversioned external writers, incomplete business rules, or an authorized person approving incorrect customer information.
Acceptance Tests
- A version-seven proposal against version eight must produce no update and an explicit conflict.
- A fresh approved city-only patch must increment the version exactly once and preserve other fields.
- A patch containing a credit-limit or tenant field must fail even if its JSON types are valid.
- A cross-tenant identifier must fail without revealing the protected row’s values or existence.
- Two simultaneous updates with the same expected version must produce at most one committed mutation.
- A timeout after commit must reconcile the original operation without applying the same patch a second time.
Measure Overwrites and Resolution Work
Establish a baseline with controlled concurrent edits and human-labeled expected outcomes. Measure stale patches committed over all stale patch attempts. Separately measure legitimate updates blocked over all reviewed legitimate attempts, and report conflict resolution time. A high conflict rate may indicate an overly broad version scope rather than careless users.
Begin with read-only patch previews, then pilot one low-impact field behind explicit approval. Stop the pilot on any unapproved field change or duplicate committed operation. Those are proposed guardrails, not achieved production results. Review whether field-level versions are justified only after row-level conflicts create measured friction; premature merging rules can hide real business disagreement.
The database migration guide handles schema evolution, while the file deletion dry-run lab binds approval to object versions. Backend and API engineering can implement the conditional write. Start with an expected-version predicate and the two-writer test, not a longer reminder in the system prompt.