PostgreSQL Development Inside an AI Workflow That Has to Be Right

PostgreSQL development is not interesting on its own. What makes it interesting is the context it sits inside, and the context I kept returning to was this: an AI workflow where a wrong answer does not just look bad, it costs someone money, a visa application, or a job opportunity. That constraint changed every decision I made at the database layer.

By 2026 the industry consensus had already moved away from single-prompt LLM calls. Compound AI systems, agentic workflows, stateful multi-agent execution with tools like LangGraph and CrewAI. The model is one node in a graph. The database is another. The problem is that the model node is probabilistic and the database node has to be deterministic. Getting those two things to cooperate without letting the model's uncertainty bleed into the persistence layer is the actual engineering problem.

The constraint that shaped every schema decision

On Fursa, a visa route eligibility tool covering 170+ destination countries, the model produced structured eligibility assessments. Those assessments had to be stored, versioned, and auditable. A user needed to be able to come back six months later and see exactly what the model said at the time, under exactly which rule set, with exactly which source documents cited. That is not a caching problem. That is a schema design problem.

I modelled the eligibility result as an immutable record. No updates. Every re-assessment wrote a new row, with a foreign key back to the rule version and the source snapshot that was active at query time. The model output was stored as validated JSONB, not raw text, not a blob. Structured outputs from the API were validated against a Pydantic schema before they ever touched the database. If validation failed, the workflow halted. The record was never written.

That single decision, validate before persist, eliminated an entire category of corruption that would have been invisible until an auditor asked for it.

Structured outputs are only as good as the schema enforcing them

Frontier model APIs now guarantee structured JSON outputs natively. That is useful. It is not sufficient. Native JSON from the model still needs to be validated against your domain schema, because the model does not know your domain schema. It knows what you told it in the system prompt, and system prompts drift.

The pattern I settled on across multiple builds:

• —Define the canonical shape as a Pydantic model, not in the prompt
• —Pass the JSON schema derived from that model to the API's structured output parameter
• —Validate the response against the same Pydantic model on receipt
• —Write to PostgreSQL only after validation passes
• —Log validation failures with the raw model response attached, for debugging

This gave me two things. First, the database stayed clean regardless of what the model produced. Second, validation failures became a signal. A spike in failures meant the model's behaviour had drifted, or the prompt had changed, or a new model version had been deployed upstream. The database was not just storage. It was a canary.

Concurrency and the agentic workflow problem

On Job Hunter, a daily job board and outreach engine crawling 185+ career pages, multiple agents ran in parallel. One agent scraped and normalised job listings. Another scored them against a candidate profile. A third drafted outreach messages. All three could be writing to the same candidate record at the same time.

PostgreSQL's row-level locking handled the obvious case. The subtler problem was idempotency. If the scoring agent crashed halfway through and restarted, it needed to know which records it had already scored and which it had not. I used a status column with a constrained enum, combined with a FOR UPDATE SKIP LOCKED query pattern. The agent selected the next unprocessed batch, locked those rows, processed them, and updated the status in the same transaction. If the agent died, the lock released and another instance picked up the work. No double-processing. No gaps.

This is a well-known pattern in queue-based systems. What made it interesting here was that the "work" being done was a model call, which is slow and expensive. I did not want to re-run a model call I had already paid for. So I also stored the model's raw output alongside the processed result. If a downstream step failed, I could replay from the stored output without hitting the API again. That reduced both cost and latency on retries.

Human approval gates and what they require from the database

Some workflows should not be fully automated. On OptimalTax, an automated tax return tool with 99% calculation accuracy, the model generated a draft return. A human reviewed it before submission. That review step is not a UX detail. It is a compliance requirement, and it has to be represented in the database.

I added an approval table with a foreign key to the draft, a reviewer identifier, a timestamp, and a decision column. The submission process checked for an approved record before it would proceed. No approved record, no submission, enforced at the application layer and at the database layer with a check constraint. Two enforcement points because one is not enough when the stakes are a tax filing.

The audit trail this created was not optional. It was the feature. Any regulator asking who approved what and when got a precise answer from a single query. The model was one part of the workflow. The human was another. The database recorded both.

What prompt caching changed about the data strategy

Prompt caching, now supported natively by Anthropic and OpenAI, reduces input token costs by up to 50% for context-heavy applications. That sounds like an API concern. It changed my data strategy.

When you cache a large system prompt or a document corpus, you stop sending that content on every call. But you still need to know which cached context a given model response was generated against. If the cached context changes, old responses may no longer be valid. I started treating the cache key as a first-class piece of metadata, stored alongside every model response in the database. That made it possible to invalidate or flag responses when the underlying context was updated, without re-running every historical record.

This is the kind of decision that looks unnecessary until the context changes and you need to know which records to re-evaluate. Then it looks obvious.

Where to look if you want to see the full engineering picture

The patterns above are not theoretical. They ran in production across several AI products, each with a different failure mode that the database layer had to absorb. The engineering lens on this work goes into the architecture, stack choices, and trade-offs in more detail. If you are assessing whether the approach is sound before an engagement, that is the right place to start.

If you already have a specific build in mind, the Technical Due Diligence service is scoped for exactly that situation: two to three weeks, a fixed scope, and a clear answer on whether the architecture will hold.