Pinpointing Agent State Corruption with Postgres xmin and LSN

Every autonomous agent eventually corrupts its own state. Not because the model is bad, but because state is written incrementally, across many turns, and no single write is obviously wrong. The corruption shows up later, as a cascading failure: a retrieval query returns nonsense, a planning step loops, a tool call targets the wrong resource. By then, the original mistake is buried under hundreds of subsequent writes.

You need a way to rewind. Not to a checkpoint, but to the exact commit that introduced the poison. If your agent's memory lives in Postgres, you already have the tools: xmin and the write-ahead log (WAL) LSN. This article shows a forensic method for pinpointing the exact commit that corrupted agent state, using these Postgres internals.

The Problem: State Corruption Is a Write-Level Event

An agent's memory is not a single blob. It's a graph of facts, embeddings, and relationships, spread across tables. A typical setup: a memories table for semantic chunks, a relations table for links, and a sources table for provenance. Each turn, the agent writes new memories, updates old ones, and prunes others. The writes are transactional, but the agent's reasoning is not. It might write a fact that contradicts a previous one, or overwrite a critical relation with a hallucinated alternative.

When things go wrong, you need to answer three questions:

1. Which write was the first one that introduced the bad data?
2. What was the agent doing at that moment?
3. Can I roll back to just before that write without losing everything else?

Restoring from a full backup is too blunt. You'd lose all subsequent, valid state. You need surgical rollback.

Why xmin and LSN?

Postgres provides two hidden columns on every table: xmin and xmax. xmin is the transaction ID (XID) that inserted the row version. xmax is the transaction ID that deleted or updated it (if any). These are per-row, and they update on every change.

The LSN (Log Sequence Number) is a pointer into the WAL. Every write operation records the LSN at which it was written. You can capture that with pg_current_wal_lsn() or txid_current() in a trigger.

Together, they give you a timeline. xmin tells you which transaction created a row. The LSN tells you the exact position in the WAL where that transaction's changes were recorded. If you log the LSN at each write, you can map a corrupt row back to the exact transaction that created it, and then to the agent's context at that time.

The Forensic Method: Step by Step

1. Add Provenance Columns

First, you need to capture the write context. Add columns to your memory tables:

ALTER TABLE memories ADD COLUMN xmin_snapshot xid;
ALTER TABLE memories ADD COLUMN lsn_snapshot pg_lsn;
ALTER TABLE memories ADD COLUMN agent_turn uuid;
ALTER TABLE memories ADD COLUMN agent_context jsonb;

xmin_snapshot is redundant with the system column xmin, but storing it explicitly makes queries easier. lsn_snapshot is the LSN at write time. agent_turn and agent_context are your own metadata: the agent's turn ID and a JSON blob of what the agent was doing.

2. Create a Trigger to Populate Them

Use a trigger to fill these columns on insert and update:

CREATE OR REPLACE FUNCTION set_memory_provenance()
RETURNS TRIGGER AS $$
BEGIN
    NEW.xmin_snapshot := txid_current();
    NEW.lsn_snapshot := pg_current_wal_lsn();
    NEW.agent_turn := current_setting('app.agent_turn', true)::uuid;
    NEW.agent_context := current_setting('app.agent_context', true)::jsonb;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_memories_provenance
BEFORE INSERT OR UPDATE ON memories
FOR EACH ROW EXECUTE FUNCTION set_memory_provenance();

The current_setting calls read custom GUCs that you set per transaction from your application. This way, the provenance is captured atomically with the write.

3. Detect the Corruption

When you notice the agent misbehaving, you need to find the corrupt rows. This is often done by comparing against a known-good state, or by running validation queries. For example, if you have a uniqueness constraint on a fact, you might find duplicates:

SELECT fact, count(*)
FROM memories
GROUP BY fact
HAVING count(*) > 1;

Or you might notice that a relation points to a non-existent memory. The key is to identify a set of rows that are 'bad'.

4. Trace Back to the Poison Commit

Once you have a suspect row, query its provenance:

SELECT xmin_snapshot, lsn_snapshot, agent_turn, agent_context
FROM memories
WHERE id = 'the-bad-row-id';

That gives you the transaction ID and LSN. Now, look at all writes that happened in that transaction. Since a single agent turn might write multiple rows, you can find all rows with the same agent_turn:

SELECT * FROM memories WHERE agent_turn = 'the-turn-id';

But the poison might be a single write within a multi-write turn. To narrow down, use the LSN. The LSN is monotonically increasing. You can compare LSNs to see the order of writes within the turn.

5. Reconstruct the Agent's Context

Now, you need to know what the agent was doing at that exact moment. The agent_context JSON should capture the agent's current task, the last few prompts, the tool calls, and the model's output. If you didn't store this, you can reconstruct from your application logs, but it's much easier if you have it in the row.

For example, the context might look like:

{
  "task": "update user profile",
  "turn": 42,
  "prompt": "...",
  "model_output": "...",
  "tool_calls": [
    {"name": "memory_write", "args": {"fact": "user likes coffee"}}
  ]
}

With this, you can see exactly what the agent was doing when it wrote the bad row.

Rolling Back to the Poison Point

Once you've identified the poison commit, you have a few options for rollback.

Option 1: Delete the Bad Rows

If the corruption is limited to a few rows, you can simply delete them and re-run the agent from that point, or manually correct the data.

DELETE FROM memories WHERE id IN ('bad1', 'bad2');

But this doesn't undo any cascading effects. If other rows were updated based on the bad data, they remain corrupted.

Option 2: Use Point-in-Time Recovery (PITR)

If you have WAL archiving enabled, you can restore the entire database to just before the poison commit. This is the nuclear option, but it loses all subsequent valid state. However, you can use the LSN to pinpoint the exact recovery point.

# Restore to a specific LSN
pg_restore --recovery-target-lsn='the-lsn'

This is useful if the corruption is widespread and you can't surgically fix it.

Option 3: Transaction-Level Replay with a Staging Table

A more surgical approach: copy the database to a staging environment, replay all transactions up to the poison point, and then apply the remaining transactions with the bad ones filtered out. This is complex but preserves valid state.

A simpler version: use the provenance columns to identify all rows written after the poison commit, and then revert them to their previous state using the WAL. This requires a logical decoding setup.

Practical Considerations

Overhead

Adding a trigger and extra columns adds overhead to every write. In practice, the overhead is small, but you should test under load. If you're writing thousands of memories per second, the trigger might be a bottleneck. You can mitigate by only capturing provenance on critical tables, or by batching writes.

Retention

The xmin and LSN values are only valid as long as the tuples are not vacuumed. Postgres will reuse transaction IDs and recycle WAL segments. To keep provenance useful, you need to ensure that you capture the info before it's lost. Since you're storing it in the row, it persists until the row is deleted or updated. But if the row is updated, the old version becomes dead and is eventually vacuumed. So, if you need to trace back to a specific version, you need to either:

• Disable vacuuming on those tables (not recommended), or
• Use a separate audit table that records every version.

Audit Table Alternative

Instead of relying on xmin and LSN in the row, you can create an audit table that logs every write:

CREATE TABLE memory_audit (
    id bigserial PRIMARY KEY,
    memory_id uuid,
    old_row jsonb,
    new_row jsonb,
    xmin_snapshot xid,
    lsn_snapshot pg_lsn,
    agent_turn uuid,
    agent_context jsonb,
    created_at timestamptz DEFAULT now()
);

Then, in the trigger, insert a record into the audit table. This gives you a full history of every change, which is more robust than relying on the current row's provenance.

Real-World Pattern: The Poison Turn

Let's walk through a scenario. An agent is managing a knowledge graph. On turn 42, it writes a memory: "user prefers dark roast coffee." But the agent had previously learned that the user is allergic to caffeine. The new memory contradicts the old one, but the agent doesn't check for contradictions. Later, a retrieval query returns both facts, and the agent gets confused, leading to a wrong recommendation.

With provenance, you can find the bad memory:

SELECT * FROM memories WHERE fact = 'user prefers dark roast coffee';

You see agent_turn = 'turn-42'. You query the audit table for that turn:

SELECT * FROM memory_audit WHERE agent_turn = 'turn-42';

You see the exact write, the LSN, and the context. The context shows the agent was processing a user message: "I love strong coffee." The model hallucinated the preference without checking allergies.

Now you can roll back by deleting that memory and any consequences. You might also want to add a validation step to the agent's pipeline to prevent similar issues.

Conclusion

The ability to pinpoint the exact commit that corrupted agent state is invaluable. Postgres's xmin and LSN provide the forensic hooks you need, and with a bit of setup, you can trace any bad write back to the agent's context. This method turns a mysterious failure into a debuggable event, and gives you the power to roll back precisely.

Implementing this is straightforward: add provenance columns, a trigger, and maybe an audit table. The overhead is minimal, and the payoff is huge when you're debugging autonomous systems. The next time your agent goes off the rails, you'll know exactly where it happened.