Introduction

coldchain-ops is a synthetic fresh-fruit cold-chain distribution platform, built end to end as a portfolio piece: a messy accounting-system Excel export gets modeled, cleaned, loaded into a proper star schema, and then handed to an LLM — but only through a governed, read-only, tool-scoped door, because in a real deployment you don't hand a model your database key.

Why This Exists

Most "LLM + database" portfolio projects stop at the demo: paste a schema into a frontier model, let it write SQL, ship a chatbot. That's the easy 80% — any current frontier model can already do it, so it's not what makes an engineer worth hiring.

This project is about the other 20%: what it actually takes to let a language model touch a real business's data without that being a liability. Concretely, that means a dedicated Postgres role (llm_reader) that can only SELECT from three read-only analytics views and nothing else — so the safety mechanism is a database privilege, not a prompt asking the model to behave. It means an append-only audit log of every question asked, every SQL statement generated, and every error, written by a role that can INSERT and nothing else. It means an eval harness that catches regressions automatically instead of "it looked right when I read it." And it means being honest that this doesn't remove the model's core failure mode — a wrong answer that looks exactly like a right one, where every safety layer in the system behaves exactly as designed and the program still confidently states the wrong answer.

Every apparent limitation in this system — read-only access, a small scoped role, a narrator forbidden from computing its own numbers — is a decision, not a shortcoming.

Architecture

What's Demonstrated, Phase by Phase

PROGRESS.md in the repo is the full running log (row counts, verification steps, decisions) behind every line in that table.

The Demo

A basic question, answered correctly regardless of backend:

A harder question — asking for a count and the underlying names in one query — is where the two backends diverge. This is the clearest demonstration in the project of why the model choice behind the SQL-generation step matters, and why that step is kept narrowly scoped and audited rather than trusted outright.

LLM_BACKEND=ollama (qwen2.5-coder:7b, running locally) drops the DISTINCT, turning a 2-row answer into one row per matching order line:

LLM_BACKEND=gemini (gemini-2.5-flash-lite, the deployed default) gets the same question right:

Every one of these runs — right or wrong — is written to query_audit. But that's a narrower guarantee than it sounds like, which is worth looking at directly.

The Audit Trail's Blind Spot

Both rows say failed = f. v_query_audit.failed is defined as error IS NOT NULL — it flips to true only when something threw: a DB error, an LLM timeout, a validate_sql rejection. The Ollama run above isn't any of those. It's a syntactically valid, successfully executed SELECT that happens to answer a different question than the one asked (every matching order line instead of every distinct product). Nothing in the pipeline throws on that, so nothing marks it.

That's the honest limit of this audit trail: it catches the system breaking, not the system being wrong while working exactly as designed. Run once and read by a human, the bug is obvious. Run a thousand times unattended, it's a failed = f row indistinguishable from every correct one — the kind of gap that would need something outcome-aware (an eval-style correctness check, not an error check) to close, and the project's eval pipeline in its current form checks known fixed cases, not open-ended production traffic. Worth stating plainly rather than implying the audit log is a complete safety net.

A Bug Neither Backend Avoids

Not every failure is model-specific. Ask for two numbers that come from different tables at different grains — total margin (v_sales_margin, one row per order line) and total storage cost (v_storage_cost, one row per product per day) — grouped by category:

Both backends join the two views directly on product/category. Since neither view is unique per product, the join pairs every matching order-line row with every matching storage-day row instead of lining the two lists up 1:1 — a category with 2,928 sales rows and 1,039 storage rows doesn't produce 2,928 + 1,039 combined rows, it produces up to 2,928 × 1,039. Every real margin figure then gets summed once per storage row it happened to pair with, and vice versa. The true numbers, computed independently with two single-view SUM ... GROUP BY queries and no join at all:

Both backends were off by roughly 500-1500x, identically. Two things make this different from the DISTINCT bug above: the correct answer is reachable within the pipeline's existing constraints (a single SELECT that pre-aggregates each view in a subquery before joining passes validate_sql and returns the exact numbers above), and the information needed to avoid the mistake is already in the prompt — the view schema documents each view's grain inline (-- one row per order line / -- one row per product per day held in storage). Both models had that information available and neither used it.

That leaves two honest ways to close this gap, and this project deliberately stops here rather than picking one by default: (1) keep the same backend and iterate on the prompt until it reliably makes the model act on the grain information it's already given, or (2) accept that as an open-ended, no-fixed-endpoint effort not worth chasing, and treat "upgrade to a more capable backend" as the fix instead — plausible given the correct query is well within reach of a more careful model, but unverified here, since this pipeline only wires up Gemini and a local Ollama model. Recording the trade-off honestly seemed more valuable than picking a side and prompt-engineering until this one case happened to pass.

Key Engineering Decisions

• Role-scoped access, not prompt-level trust. llm_reader can SELECT from three views and nothing else at the database layer — no amount of prompt injection changes what the role is physically permitted to run.
• Quarantine over silent-drop. Rows that fail ETL validation (bad quantities, unparseable dates) go to a rejects CSV instead of vanishing — the pipeline's data-quality decisions are auditable, not hidden.
• The export's real IDs are preserved, not regenerated, using OVERRIDING SYSTEM VALUE — so loaded orders stay traceable back to the original messy source file.
• An eval harness caught a real bug: Gemini was still sampling at nonzero temperature and "percentage" was ambiguous (fraction vs. ×100) in the prompt. Both were invisible from reading the code and only surfaced once answers were checked against known-correct values across repeated runs.
• The narrator is shown the executed SQL, not just the result rows, so it can say "no rows matched this filter" honestly instead of asserting nothing exists — the difference between a dangerous wrong answer and a system that flags its own uncertainty.

Stack

Postgres 17 + pgvector · goose (migrations) · pandas · SQLAlchemy / psycopg3 · sentence-transformers · Gemini (gemini-2.5-flash-lite, default) with a swappable local Ollama backend (qwen2.5-coder:7b) behind the same interface · uv for dependency management.