My Postgres AI automation stopped acting haunted when I added 7 boring database guardrails

I knew we had a real problem when an agent “successfully” updated a customer record that was not, in any useful sense, the customer it was supposed to update.

Nothing crashed.

That was the unsettling part.

The JSON was valid. The schema matched. OpenAI Structured Outputs had done exactly what it promised. Every field existed. Types were correct. Enums were legal.

If you only looked at the model response, you’d say: looks good, ship it.

Then we looked at Postgres.

We had:

• a duplicate row in one table
• a stale status in another
• a note attached to the wrong account

The agent had made a very human mistake: it guessed.

And because we had wrapped the whole thing in “good prompting,” there was nothing deterministic around that guess.

That flipped the whole problem for me.

A lot of what people call flaky agents is just missing database boundaries.

The model was fine. Our write path was not.

If your agent is doing CRUD-heavy work in:

• n8n
• Make
• Zapier
• OpenClaw
• LangGraph
• custom workers

...you probably do not have an AI reliability problem first.

You have a database discipline problem.

People keep trying to solve this by:

• tweaking the GPT-5 prompt again
• switching to Claude Opus 4.6
• adding another validation pass with Qwen or Llama
• asking for stricter structured outputs

I’m not anti-prompting. Better prompts help. Structured outputs help.

But they solve the wrong layer.

OpenAI Structured Outputs is good at making sure the model returns JSON that matches a schema.

That helps with:

• malformed JSON
• missing required fields
• invalid enum values
• retry loops caused by formatting errors

It does not stop an agent from:

• updating the wrong row
• inserting a duplicate record
• violating a business rule
• writing data in the wrong order
• racing another worker and clobbering fresh data

A valid JSON object can still do a very dumb thing.

Once you accept that, the fix gets much less glamorous and much more effective.

The first shift: the agent should not write directly

The biggest improvement came from one mindset change:

Treat the LLM as an untrusted planner.

It can:

• classify
• summarize
• draft arguments
• suggest an action

But Postgres decides what actually gets committed.

That means the model can propose a write, but it does not get to freestyle side effects.

1. Put hard boundaries around every multi-step write

This is the boring fix that solved most of the pain.

Use:

• transactions
• SAVEPOINTs
• UPSERTs with ON CONFLICT
• advisory locks when multiple workers might touch the same entity

This is not exotic architecture. This is just grown-up SQL.

Basic transaction pattern

BEGIN;

SAVEPOINT before_agent_write;

-- validated insert/update here

-- if a downstream check fails:
ROLLBACK TO SAVEPOINT before_agent_write;

COMMIT;

That pattern matters more than another week of prompt tweaking.

Example: safe update with a freshness check

BEGIN;

UPDATE customers
SET status = 'active', updated_at = now()
WHERE id = $1
  AND updated_at = $2;

-- if row_count = 0, someone else changed it first
-- abort or retry upstream

COMMIT;

That one WHERE updated_at = $2 check prevents a lot of silent clobbering.

Example: idempotent retry with UPSERT

INSERT INTO customer_notes (customer_id, external_id, body)
VALUES ($1, $2, $3)
ON CONFLICT (external_id)
DO UPDATE SET
  body = EXCLUDED.body,
  updated_at = now();

If your workflow retries, this keeps retries from spraying duplicates everywhere.

2. If you use n8n, use transaction mode for CRUD-heavy jobs

This one is almost too easy to miss.

In n8n’s Postgres node, query batching can run as:

• Single Query
• Independently
• Transaction

For CRUD-heavy automation, Transaction is the adult option.

If one step fails, Postgres rolls everything back.

That is dramatically better than half-applying a batch and then trying to repair the damage later.

3. Durable state is not optional if the workflow can restart

The next bug looked like model inconsistency.

The agent would:

• enrich a CRM record
• get interrupted
• restart
• behave like it had never touched the record

Sometimes it repeated work.
Sometimes it skipped work.
Sometimes it did both in the same afternoon.

That was not a prompting issue.

That was a state persistence issue.

If you are using LangGraph, this distinction matters:

• checkpointers store thread-scoped execution state
• stores handle longer-term data

For production, persistent backends matter. PostgresSaver matters.

If you are still using MemorySaver or InMemorySaver for a workflow that can restart, you do not have durable execution.

You have optimism.

Example: switch from in-memory to Postgres-backed state

from langgraph.checkpoint.postgres import PostgresSaver

DB_URI = "postgresql://app:secret@localhost:5432/agents"

with PostgresSaver.from_conn_string(DB_URI) as checkpointer:
    graph = builder.compile(checkpointer=checkpointer)
    result = graph.invoke(
        {"messages": [{"role": "user", "content": "sync this account"}]},
        config={"configurable": {"thread_id": "acct_123"}}
    )

One practical detail that is easy to miss: keep thread_id values short. Using a UUID is usually safer than stuffing huge composite IDs into it.

4. Durable state and safe writes are different problems

This is where a lot of teams get sloppy.

PostgresSaver helps an agent resume.

It does not guarantee row-level correctness.

You still need constraints, transactions, and deterministic write logic.

Here’s the mental model I wish we had earlier:

Approach	What it actually solves
OpenAI Structured Outputs	Ensures model output matches a supplied JSON schema; helps with type safety and malformed arguments; does not enforce business rules
PostgreSQL transactions + ON CONFLICT + advisory locks	Creates deterministic write boundaries; prevents duplicate or competing writes; handles rollback correctly
LangGraph persistence with PostgresSaver	Preserves agent execution state across restarts; helps workflows resume cleanly; does not guarantee data integrity by itself

You usually need all three.

5. The safest pattern is also the least sexy: queue the side effects

If the automation touches anything that finance, support, or ops cares about, do not let the LLM chain writes directly.

Queue the work first.

A Postgres-backed queue like pgmq is a strong pattern here.

The model can propose:

• update invoice 183
• add note to account 92
• sync status to HubSpot

Fine.

Put those intents on a queue.

Then let a deterministic worker:

1. validate current state
2. enforce business rules
3. apply the mutation in a transaction
4. archive or retry cleanly

Example: enqueue work instead of writing immediately

SELECT * FROM pgmq.send(
  queue_name => 'agent_writes',
  msg => jsonb_build_object(
    'action', 'update_customer_note',
    'customer_id', 92,
    'external_id', 'hs_12345',
    'body', 'Customer requested invoice copy'
  )
);

Then your worker can process it safely.

Example worker shape

import psycopg
import json

conn = psycopg.connect("postgresql://app:secret@localhost:5432/app")

with conn, conn.cursor() as cur:
    cur.execute("SELECT * FROM pgmq.read('agent_writes', 30, 1)")
    rows = cur.fetchall()

    for row in rows:
        msg = row[3]  # depends on pgmq result shape/version

        with conn:
            with conn.cursor() as tx:
                tx.execute("""
                    INSERT INTO customer_notes (customer_id, external_id, body)
                    VALUES (%s, %s, %s)
                    ON CONFLICT (external_id)
                    DO UPDATE SET body = EXCLUDED.body, updated_at = now()
                """, (msg["customer_id"], msg["external_id"], msg["body"]))

                tx.execute("SELECT pgmq.archive('agent_writes', %s)", (row[0],))

That is a lot safer than “let the agent call updateCustomerNote directly and hope for the best.”

6. Use Row Level Security if you’re on Supabase

If you are using Supabase, Row Level Security is one of the best guardrails you can add.

RLS policies run inside Postgres. They act like an implicit WHERE clause on table access.

So even if an agent in n8n, OpenClaw, or a custom worker issues a bad query, Postgres can still limit what rows are readable or writable.

That is exactly where security and correctness belong: in the database, not in a prompt.

Example RLS policy

ALTER TABLE customer_notes ENABLE ROW LEVEL SECURITY;

CREATE POLICY notes_update_policy
ON customer_notes
FOR UPDATE
USING (account_id = current_setting('app.account_id')::uuid);

That will not fix every logic bug.

But it absolutely reduces blast radius.

And blast radius matters a lot when agents are running unattended.

7. Add deterministic validation before commit

Before committing any agent-proposed write, validate things the model should never be trusted to infer.

For example:

• uniqueness
• ownership
• allowed state transitions
• record freshness
• foreign key existence
• authorization

Example: enforce a legal state transition

UPDATE invoices
SET status = 'paid', updated_at = now()
WHERE id = $1
  AND status IN ('sent', 'overdue');

If the invoice is already void or draft, that update simply does not happen.

That is much better than asking the model to remember your billing rules.

Quick local test setup

If you want to pressure-test this pattern locally, spin up Postgres and try a few failure cases.

docker run --name agent-pg \
  -e POSTGRES_PASSWORD=postgres \
  -e POSTGRES_DB=app \
  -p 5432:5432 \
  -d postgres:16

Create a table with a uniqueness constraint:

CREATE TABLE customer_notes (
  id bigserial PRIMARY KEY,
  customer_id bigint NOT NULL,
  external_id text NOT NULL UNIQUE,
  body text NOT NULL,
  updated_at timestamptz NOT NULL DEFAULT now()
);

Then intentionally retry the same write and verify that ON CONFLICT keeps the table clean.

The checklist I wish we started with

If your Postgres AI automation keeps acting haunted, start here:

1. Make every multi-step write transactional.
2. Use ON CONFLICT for idempotent retries.
3. Add SAVEPOINTs for partial undo.
4. Persist agent state outside the prompt.
5. Queue side effects before applying them.
6. Enforce authorization in Postgres.
7. Validate business rules before commit.

That list is not exciting.

It will also do more for reliability than switching frontier models every Friday.

Where Standard Compute fits

One side effect of doing this right: your agents usually make more calls than you expected.

Not because they are worse.

Because production-safe automation adds:

• retries
• validation passes
• classification steps
• queue workers
• reconciliation jobs
• long-running background flows

That is exactly where per-token pricing starts to get annoying.

If you are running agents in n8n, Make, Zapier, OpenClaw, or custom workflows, Standard Compute is useful for the boring reason that matters most: predictable cost.

It is a drop-in OpenAI-compatible API that gives you unlimited AI compute for a flat monthly price, with routing across models like GPT-5.4, Claude Opus 4.6, and Grok 4.20.

That means you can add the extra validation and workflow steps your automation actually needs without watching a token meter the whole time.

For agent systems, that changes how willing you are to build the safe version instead of the cheap-looking demo version.

Final take

The weirdest part of fixing our “AI reliability” issue was that the real fix barely felt like AI work.

It felt like old-school database engineering:

• transactions
• constraints
• queues
• rollback paths
• durable state

Which is exactly what you want when an agent is touching real records.

If your agent keeps mangling rows, duplicating entries, or losing its place mid-workflow, stop asking for a more obedient prompt.

Ask a harder question instead:

What is the strongest thing Postgres can guarantee even when the model is wrong?

Start there.

That is where the haunting usually ends.