Reverse SQL synthesis: deterministic undo for AI agents

When an autonomous AI agent executes an unintended database mutation, human operators face an immediate crisis. A script intended to update a single user record inadvertently wipes an entire column, or a query with an inverted WHERE clause changes account balances across thousands of rows.

In many modern developer frameworks, the initial response to this scenario is conversational. The developer writes a follow-up prompt: “You made an error on the last query; please reverse what you just changed.”

Relying on a large language model to write its own undo queries is an operational disaster. When an agent hallucinates syntax or misunderstood schema state during its forward execution, asking that same agent to hallucinate backward does not restore clean data. Instead, it triggers secondary update cascades, overwrites concurrent human writes, and destroys the evidentiary trail required for forensic recovery.

Under voluntary standards such as the NIST AI Risk Management Framework (AI RMF 1.0)¹, managing AI risk requires concrete mechanisms to govern, map, measure, and manage system actions. In database operations, managing risk requires deterministic undo: mathematical, catalog-accurate reverse SQL generated directly from storage engine before-images, verified and countersigned before execution.

The fatal flaw of conversational rollback prompts

Why do conversational rollback prompts fail so reliably? Large language models possess no direct sensory connection to physical storage. They operate over token sequences and probabilistic associations. When an agent issues a SQL mutation, it does not hold the previous database contents in memory.

Consider an agent executing an update against an enterprise inventory database:

Code A database command in SQL. The text around it says what it does.

UPDATE inventory SET reorder_point = 50 WHERE category_id = 12;

If the developer realizes category 12 was the incorrect target and prompts the agent to undo the change, the agent cannot know what the prior reorder_point values were for each affected item. Some items had a reorder point of 20, others 100, and several were set to NULL.

Lacking the historical row images, the agent is forced to guess:

Code A database command in SQL. The text around it says what it does.

-- Hallucinated rollback attempt
UPDATE inventory SET reorder_point = 0 WHERE category_id = 12;

The database state has not been restored. It has been corrupted twice. The original values are permanently erased from the active table pages.

Furthermore, if the initial mutation fired database triggers that deleted related supplier records, the agent remains completely unaware of the orphaned records. Prompting an LLM to reverse its actions compounds corruption rather than resolving it.

Anatomy of a WAL row tuple

Deterministic rollback requires historical data that is independent of LLM memory or agent honesty. PostgreSQL preserves this data within its Write-Ahead Log (WAL).

When logical decoding is enabled with REPLICA IDENTITY FULL, PostgreSQL writes both the old tuple (OLD) and the new tuple (NEW) into the replication stream for every modified row.

A logical decoding message representing an UPDATE contains structured fields:

1. Relation Metadata: Namespace (schema), relation name, and relation ID.
2. Tuple Type: An indicator specifying whether the record represents an INSERT (N), UPDATE (K or O followed by N), or DELETE (O or K).
3. Old Tuple Image: Complete binary or textual representations of every column value prior to the transaction commit.
4. New Tuple Image: Complete representations of every column value as written by the transaction.

By capturing these tuple streams via PostgreSQL logical replication protocol (pglogrepl), an external supervisor obtains the exact byte-level state of every row before the agent touched it.

Synthesizing inverted SQL statements

Once the before-and-after tuple pairs are captured from the WAL, generating an undo operation becomes a deterministic compilation problem rather than an AI prompt exercise.

The synthesis engine maps forward operations to their exact inverse:

1. Inverting an INSERT operation

When an agent executes an unauthorized INSERT, the WAL stream records a NEW tuple containing the inserted row. To invert this mutation, the synthesis engine generates a targeted DELETE statement targeting the primary key:

Code A database command in SQL. The text around it says what it does.

-- Forward action: INSERT INTO customers (id, name, tier) VALUES (501, 'Acme Corp', 'gold');
-- Synthesized reverse SQL:
DELETE FROM customers WHERE id = 501;

2. Inverting a DELETE operation

When an agent erroneously deletes rows, the WAL stream with REPLICA IDENTITY FULL records the complete OLD tuple for every purged row. The synthesis engine reconstructs the exact original state as an INSERT:

Code A database command in SQL. The text around it says what it does.

-- Forward action: DELETE FROM customers WHERE status = 'dormant';
-- Synthesized reverse SQL:
INSERT INTO customers (id, name, tier, balance, created_at)
VALUES (501, 'Acme Corp', 'gold', 1420.50, '2024-03-12 09:30:00+00');

Every column is populated with its exact historical value, restoring the record without data loss.

3. Inverting an UPDATE operation

When an agent modifies rows, the synthesis engine compares the OLD tuple with the NEW tuple. It identifies which columns changed and produces an UPDATE statement that restores the old values while binding to the primary key:

Code A database command in SQL. The text around it says what it does.

-- Forward action: UPDATE accounts SET balance = 0 WHERE id = 88;
-- Synthesized reverse SQL:
UPDATE accounts SET balance = 45200.00 WHERE id = 88;

Because this query is synthesized mathematically from the storage engine own log, there is zero possibility of hallucination. The syntax is catalog-accurate, type-safe, and precise.

Handling NULLs, foreign keys, and TOAST pointers

Real-world PostgreSQL databases contain structural complexities that break simplistic reverse engineering scripts. A production-ready reverse SQL engine must correctly navigate three critical edge cases:

NULL values in predicates and assignments

In SQL, NULL represents an unknown value. The expression column = NULL always evaluates to UNKNOWN, which causes WHERE clauses to fail silently. When synthesizing reverse statements for rows where columns were previously NULL, the engine must emit column IS NULL in predicates and column = NULL in assignment clauses.

Foreign key dependency ordering

When an agent deletes a parent record and triggers cascading deletions of child records, restoring the data requires strict topological ordering. The parent record must be re-inserted before the child records can reference it. Conversely, when undoing an erroneous insertion across related tables, child records must be deleted before the parent record can be removed.

Deedglass inspects the PostgreSQL system catalog (pg_constraint and pg_class) to construct a directed acyclic graph (DAG) of table dependencies, ensuring synthesized rollback statements execute in mathematically valid dependency order.

TOAST (The Oversized-Attribute Storage Technique) pointers

PostgreSQL stores large column values (such as lengthy text blocks or JSON payloads) out-of-line in TOAST tables. In standard UPDATE operations, unchanged TOAST values are not duplicated in the WAL stream; PostgreSQL emits an unchanged TOAST pointer indicator.

The reverse SQL synthesizer must detect TOAST pointers and verify whether the out-of-line value was modified. If the value remained untouched, the reverse UPDATE omits that column, preventing unnecessary storage bloat and avoiding foreign pointer corruption.

Human-in-the-loop countersigning and audit sealing

Even when reverse SQL is generated with mathematical precision, executing recovery commands on a production database must never happen autonomously without human oversight. An automated loop that undoes actions without human review risks fighting with concurrent application logic.

Deedglass implements an explicit authorization boundary. If your application keeps its data in your own PostgreSQL instance, with logical decoding enabled, Deedglass checks each agent write it runs against the database change log and can undo the rows after a human operator countersigns².

The workflow is deliberate and transparent:

1. Detection: Deedglass detects a row-count mismatch or policy violation in the agent tool call.
2. Synthesis: The engine immediately decodes the WAL tuples and synthesizes the exact reverse SQL script.
3. Staging: The reverse SQL is presented to a designated engineer or DBA in the Deedglass console, Slack, or terminal.
4. Countersignature: A named human operator reviews the proposed statements, inspects the affected primary keys, and signs the authorization.
5. Execution and Sealing: The rollback executes within an explicit transaction. Deedglass seals a signed record of the recovery action that can be checked offline³.

This design satisfies the govern and manage criteria established in frameworks like the NIST AI Risk Management Framework¹. Autonomous agents are granted the freedom to operate quickly, but the organization maintains an unbending safety net anchored in storage reality.

Crucially, this entire infrastructure operates locally within your private network. Deedglass runs on your own servers with your own PostgreSQL and sends nothing to external servers by default⁴. Your database rows, table names, and reverse SQL payloads never traverse third-party APIs.

Auditing your agent rollback readiness

Before deploying autonomous agents with database access, teams must verify whether their current database configuration supports deterministic rollback.

You can test this capability in your browser right now at watch an agent get caught. In the interactive playground, you can trigger an agent mutation that alters 301 rows and watch the engine synthesize and execute the exact reverse SQL to restore the table.

Next, evaluate your production codebase and agent client configurations by running our open-source scanner:

Code Commands typed in a terminal. The text around it says what happens.

npx deedglass scan

The scanner checks your local configurations, detects whether agents possess uncontained write permissions, and assigns a blast radius score from A to F. Learn how each factor is calculated by reviewing our comprehensive scoring guide at what the scan grade means.

By replacing conversational rollback prompts with catalog-accurate reverse SQL synthesized from PostgreSQL Write-Ahead Log, engineering teams eliminate the danger of secondary corruption and establish verifiable operational control over autonomous AI agents.

Written with AI assistance and checked against our claims register.