My postgres ai automation stopped acting haunted when I quit trusting the prompt

The boring fix for flaky postgres ai automation is usually not a smarter prompt. It’s putting hard boundaries around writes with PostgreSQL transactions, ON CONFLICT, SAVEPOINTs, durable state in PostgresSaver, and queue-based execution like pgmq, so one bad agent step doesn’t corrupt 500 records before you notice.

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

Nothing crashed. That was the creepy part.

The JSON was valid. OpenAI Structured Outputs had done its job. The fields were all there, the types were correct, and the enum values were legal. If you only looked at the LLM response, you’d say: nice, ship it.

Then we checked Postgres.

We had a duplicate row in one table, a stale status in another, and a note attached to the wrong account because the agent made a very human mistake: it guessed. And because we’d wrapped the whole thing in “good prompting,” there was nothing deterministic around the guess.

That was the moment the whole "flaky agent" story flipped for me. A lot of what people call agent unreliability is just missing database boundaries.

The model wasn’t broken — our write path was

If your agent is doing CRUD-heavy internal work in n8n, Make, Zapier, OpenClaw, or a custom LangGraph flow, you don’t actually have an “AI problem” most of the time.

You have a database discipline problem.

People keep reaching for a better GPT-5 prompt, or switching to Claude Opus, or adding another validation pass with Qwen or Llama. I’m not against any of that. Better prompting matters. Structured outputs matter.

But they solve the wrong layer.

OpenAI Structured Outputs is great at one thing: making sure the response matches a supplied JSON Schema. That removes a lot of retry pain and formatting nonsense. It helps with bad enums, missing fields, and malformed arguments.

It does not stop an agent from:

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

A perfectly valid JSON object can still do a perfectly stupid thing.

And once you really accept that, the fix gets much less glamorous and much more effective.

What if the agent never gets to write directly?

This was the first big shift.

Instead of asking the agent to “be careful” while calling side-effecting CRUD actions, we started treating the LLM like an untrusted planner sitting outside the vault. It can suggest. It can classify. It can draft arguments. But Postgres decides what actually lands.

That changes everything.

The minimum guardrails that stopped the bleeding

PostgreSQL already gives you the boundaries the model cannot improvise around:

• Transactions so a multi-step write either fully happens or fully doesn’t
• SAVEPOINTs so you can partially undo a bad step without throwing away the whole job
• UPSERT with INSERT ... ON CONFLICT so retries don’t create duplicates
• Advisory locks for application-level mutual exclusion when two workers might touch the same record

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

And n8n makes the tradeoff almost embarrassingly explicit in its Postgres node. Query Batching can run as Single Query, Independently, or Transaction. For CRUD-heavy automations, Transaction is the adult choice because if one step fails, Postgres rolls back all changes.

That one setting is more valuable than a week of prompt tweaking.

BEGIN;
SAVEPOINT before_agent_write;
-- perform validated insert/update here
-- if a downstream check fails:
ROLLBACK TO SAVEPOINT before_agent_write;
COMMIT;

That pattern is the heart of ai agent rollback. Not vibes. Not retries. Actual rollback.

But write safety was only half the mess.

Why did the agent keep “forgetting” what it already did?

This one drove me nuts because it looked like model inconsistency.

The agent would enrich a CRM record, get interrupted, restart, and then act like it had never touched the record. Sometimes it would repeat work. Sometimes it would skip a step because the prompt implied the step had already happened. Sometimes it would do both in the same afternoon.

That is not a prompting issue. That is a state persistence issue.

LangGraph’s docs are refreshingly clear here: durable execution needs persistence outside the prompt. They split it into checkpointers for thread-scoped graph state and stores for long-term data. And for production, they recommend persistent backends like PostgresSaver because in-memory savers lose checkpoints on restart.

That warning should be tattooed on half the agent demos on the internet.

If you’re still using MemorySaver or InMemorySaver in a workflow that can restart, you don’t have durable state. You have hope.

One practical detail from the LangGraph docs that’s easy to miss: keep thread_id values under 255 characters, or use a UUID or hash, otherwise you can hit database errors. Small detail, huge difference when you’re debugging “random” failures at 2 a.m.

Durable state is not the same as safe writes

This is where people get sloppy.

PostgresSaver helps your agent resume. It does not, by itself, guarantee row-level correctness. You still need constraints, transactions, and explicit write logic.

That’s why I think the cleanest mental model is this:

You need all three layers if the workflow matters.

And then there’s the part almost nobody wants to build.

The safest pattern is also the least sexy

If the automation touches invoices, support tickets, CRM records, or anything finance or ops will yell about, don’t let the LLM chain writes directly.

Put the work on a queue first.

Supabase’s pgmq is a really good example of why Postgres can be both your system of record and your workflow control plane. pgmq gives you a Postgres-backed queue with exactly-once delivery to a consumer within a visibility timeout, explicit removal semantics, archive and replay, batch send, and delayed visibility. The send delay parameter is specified in seconds and defaults to 0.

That’s boring. I mean that as praise.

An agent can propose: “update invoice 183, add note to account 92, sync status to HubSpot.” Fine. Put those intents onto pgmq. Then a deterministic worker validates current state, applies the mutation in a transaction, and archives or retries cleanly.

That pattern is so much safer for back-office jobs like:

• invoice updates
• CRM enrichment
• support-ticket syncing
• customer note ingestion
• reconciliation after third-party API failures

Here’s the kind of UPSERT that saves you from retry-created garbage:

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();

You can retry that all day without spraying duplicates across your database.

That’s not just reliability. That’s ai agent quality assurance at the only layer that really counts: the place where the data lives.

Can’t Row Level Security catch bad agent behavior too?

Yes, and if you’re using Supabase, you should take this very seriously.

Supabase’s Row Level Security docs make the key point better than most AI orchestration docs do: policies run inside Postgres on every table access and behave like an implicit WHERE clause. So even if an agent in n8n or OpenClaw issues a bad query, RLS can still constrain which rows are readable or writable.

That’s a huge deal.

Because security and correctness do not belong in a prompt. They belong in the database.

There’s also an ugly footgun here. Supabase notes that on some existing projects, new tables in public may start with select, insert, update, and delete granted to anon, authenticated, and service_role by default unless you revoke them.

If you’re building internal automations and assuming “the agent would never touch that table,” that assumption is doing a lot of unpaid labor.

RLS won’t solve every logic bug. But it absolutely reduces blast radius when an agent or workflow step goes off-script.

So should you stop caring about prompts?

No. That would be the wrong lesson.

If GPT-5, Claude Opus 4.6, Grok 4.20, Qwen, or Llama can’t reliably emit the right fields or tool arguments, your UX will still be awful. Structured outputs still matter. Good prompting still matters. Clear tool descriptions still matter.

But those are input quality improvements.

They are not permission to let an LLM freestyle writes into Postgres.

And yes, Postgres-first design adds overhead. Transactions, RLS policies, queues, reconciliation workers, and deterministic checks are more work than letting an agent call CRUD tools directly. If you’re building a toy internal helper for five people, that overhead may not pay off.

If the workflow has real side effects or runs unattended at scale, it pays off fast.

Especially once the first silent corruption bug lands.

The checklist I wish we’d started with

If your postgres ai automation keeps acting haunted, this is where I’d start:

1. Make every multi-step write transactional. In n8n, choose Query Batching: Transaction for CRUD-heavy batches.
2. Use ON CONFLICT for idempotent retries. Assume retries will happen.
3. Add SAVEPOINTs for partial undo. Not every failure should nuke the whole job.
4. Persist agent state outside the prompt. In LangGraph, use PostgresSaver, not in-memory savers, for production.
5. Queue side effects before applying them. pgmq is a strong pattern for durable, replayable work.
6. Enforce authorization in Postgres. Use Supabase RLS or equivalent database-side policies.
7. Add deterministic validation before commit. Check uniqueness, ownership, allowed state transitions, and freshness.

That list is not exciting.

It will also do more for reliability than switching from one frontier model to another every Friday.

The weirdest surprise in all of this is that the “AI” fix barely felt like AI work at all. It felt like old-school database engineering. Transactions. Constraints. Queues. Rollback paths. Boring stuff.

Which, honestly, 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: what is the strongest thing Postgres can guarantee even when the model is wrong?

Start there. That’s where the haunting usually ends.