Prevent Stale AI Updates With a Version Check in SQLite

An AI assistant reads a support ticket and proposes raising its priority. Before that proposal is approved, a colleague changes the ticket. The proposed value may be valid, yet applying it now would overwrite a newer decision.

Add a version check to the write itself. This tutorial builds an offline Python fixture that rejects an outdated proposal, accepts a newly reviewed one, and rejects a replay. It uses Python 3.10 or later and the standard-library sqlite3 module; no model, account, or persistent database is required.

1. Bind approval to the state that was read

Store an integer version alongside each ticket. The application captures the ticket ID and version when reading the record, then retains them with the proposal. The model may suggest a priority; it must not choose a newer version to bypass a conflict.

Every writer, including human-facing tools, must increment the version whenever relevant state changes. Keep IDs stable and never reset versions or reuse an old ID/version combination after deletion.

This guard complements a field allowlist for AI edits. It does not replace authorization or strict validation of model-produced JSON.

2. Put the comparison inside the UPDATE

Save the following as guard_stale_update.py. The SQL is application-authored, not generated by the model. Bound parameters carry the values. isolation_level=None leaves SQLite in autocommit mode, so each standalone update completes without a separate commit in this example. See Python’s transaction-control documentation.

The human edit is deliberately placed between the initial read and the AI write. For the fresh attempt, assume the changed record has been reviewed and the proposal approved again; fetching the latest version alone is not approval.

import sqlite3


db = sqlite3.connect(":memory:", isolation_level=None)
db.execute(
    "CREATE TABLE tickets (id INTEGER PRIMARY KEY, "
    "priority TEXT NOT NULL CHECK(priority IN ('low','normal','high')), "
    "version INTEGER NOT NULL CHECK(version > 0))"
)
db.execute("INSERT INTO tickets VALUES (7, 'normal', 1)")


def state():
    return db.execute(
        "SELECT priority, version FROM tickets WHERE id = 7"
    ).fetchone()


def apply_priority(ticket_id, expected_version, priority):
    if type(expected_version) is not int or expected_version < 1:
        raise ValueError("invalid version")
    if type(priority) is not str or priority not in {"low", "normal", "high"}:
        raise ValueError("invalid priority")
    cursor = db.execute(
        "UPDATE tickets SET priority = ?, version = version + 1 "
        "WHERE id = ? AND version = ?",
        (priority, ticket_id, expected_version),
    )
    return cursor.rowcount == 1


snapshot_version = state()[1]
db.execute(
    "UPDATE tickets SET priority = 'low', version = version + 1 WHERE id = ?",
    (7,),
)

stale = apply_priority(7, snapshot_version, "high")
assert stale is False and state() == ("low", 2)
print("stale:", "APPLY" if stale else "REJECT", state())

reviewed_version = state()[1]
fresh = apply_priority(7, reviewed_version, "high")
assert fresh is True and state() == ("high", 3)
print("fresh:", "APPLY" if fresh else "REJECT", state())

replay = apply_priority(7, reviewed_version, "high")
assert replay is False and state() == ("high", 3)
print("replay:", "APPLY" if replay else "REJECT", state())

missing = apply_priority(99, 1, "high")
assert missing is False and state() == ("high", 3)
print("missing:", "APPLY" if missing else "REJECT")
db.close()

The version comparison and increment occur in one statement. A separate read-check followed by an unconditional write would leave a gap where another writer could intervene.

3. Run and validate the fixtures

From the script’s directory, run:

python3 guard_stale_update.py

Expected standard output:

stale: REJECT ('low', 2)
fresh: APPLY ('high', 3)
replay: REJECT ('high', 3)
missing: REJECT

This exact example was checked locally with Python 3.11.4: exit status 0 and empty standard error. The assertions verify that the stale attempt preserves the colleague’s low priority at version 2. The approved update advances to version 3; replaying version 2 changes nothing. A missing ticket also changes nothing. This is an ordered-interference fixture, not a concurrent-load test or a measurement of model accuracy.

SQLite treats an update matching no rows as a normal outcome, not an SQL error. SQLite’s UPDATE documentation explains this behavior; Python exposes the affected-row count through Cursor.rowcount. Here, only one affected row means success.

4. Handle conflicts without discarding the guard

• No exception, but no update: zero rows can mean an outdated version or a missing record. Return a conflict/not-applied outcome; do not claim success.
• Changed records still accept old proposals: audit all writers for missing version increments. The guard depends on that shared rule.
• Repeated conflicts: reload the record, show the difference, and request a new decision. Do not silently attach the current version to an old approval.
• Database lock or constraint error: treat it as an execution error, not a successful write or an ordinary version conflict. Report it separately.

This gate protects one row. It does not make an email, payment, or multi-record operation atomic. A fresh version also does not prove that the proposed priority is sensible. Keep permission checks and human-review rules outside the model.

For an HTTP service that supports conditional updates, the corresponding mechanism is a strong ETag with If-Match. RFC 9110 defines it to prevent lost updates; do not assume an arbitrary API implements it.

Practical takeaway

Approve a change against a particular version, then enforce that version where the write happens. Combine this with a reviewable, read-only preview. When the underlying state changes, stop and review again rather than letting a valid-looking AI proposal overwrite newer work.