Clarity

Text-to-SQL demos in an afternoon

Point a decent model at a schema and you'll have something working before lunch. That part really is easy now.

Day two is the problem. It answers from a table that died a year ago. It quotes a number without running a query. It says your export is ready when nothing was ever written. Each one beats a stack trace for damage, because nobody can tell it happened.

So I stopped reading feature requests and read transcripts instead. The gap was never capability. It was trust.

No vector store. Anywhere.

The default move is embeddings. Chunk the schema, load it into a vector database, retrieve per question. I'd push back on anyone reaching for that first: a schema isn't an unbounded corpus, it's a few hundred tables you can simply describe.

A nightly job compiles a knowledge document per tenant. Business summary per table, a glossary, SQL recipes known to work. It goes into every conversation as cached context, byte-identical so the provider can actually cache it.

The model turns up already knowing the estate. Live discovery stays as a fallback, so a table added this morning works this morning. This is what it gets handed:

## Schema Directory
Every table you can query is listed below with a one-line description.
This is the complete set.
 
### operational
- `work_order` — jobs raised against a site, with status and due date
- `site` — every physical location, with region and commissioning date
- `asset` — equipment installed at a site, keyed to its site
- `site_daily_rollup` (no new data since 2025-03) — legacy daily aggregates
- `alarm_event_archive` (no rows) — superseded, retained for audit
 
### telemetry
- `reading` — time-series sensor readings, one row per probe per interval
- `probe` — sensor metadata: type, unit, the asset it monitors
 
## Glossary
- **overdue**: a work order past its due date and not yet closed
- **estate**: every site belonging to one tenant
 
## Verified Query Recipes
- **open work by site** — how much outstanding work does each site have?
```sql
SELECT s.name, count(*) AS open_jobs
FROM work_order w JOIN site s ON s.id = w.site_id
WHERE w.closed_at IS NULL GROUP BY s.name ORDER BY 2 DESC
```

The bug that changed the design

Someone asked which sites were running hottest. Clarity said there was no data. There was loads of data. It had found a promisingly named table, dead for months, while the live telemetry sat somewhere less obvious.

A wrong "no data" is the nastiest failure in the set. Nobody escalates it. They decide the tool is useless and stop asking, and no metric ever tells you.

Now the compile probes each table for its newest timestamp and marks the directory: (no rows), (no new data since …). The model routes around dead tables because it can see they're dead. Freshness stays out of the change-detection hash, or a timestamp moving nightly would re-summarise the whole estate nightly to tell us nothing changed.

Try to get something past it

Generated SQL is untrusted input that happens to be executable. The system prompt says read-only, which is worth nothing on its own, so the enforcement lives in the database.

Queries run as a dedicated read-only role, provisioned on every tenant database at startup. It fails closed: no pool, no query. It never falls back to the admin connection, which is the sort of helpfulness that ends up in an incident report.

Validation lexes SQL to canonical tokens before checking anything. String matching loses to quoting and comments, so FROM/**/pg_tables would walk straight past it. Failures return as data, not exceptions, so the model reads its own error and corrects itself.

I won't use a model to grade a model

LLM-as-judge is the obvious approach and I rejected it. A grader that hallucinates can't certify a system whose defining failure is hallucination. The bad case isn't a wrong score, it's two models agreeing confidently in the same wrong direction with a green dashboard on top.

Canned questions replay after every deploy, asserted against the audit record rather than the prose. Answer contains a number, SQL must have run. Fabricated-names list must be empty. Export claimed, report row must have completed. Nothing to argue with at 2am.

Two calls worth explaining

Before either of them: every answer carries the query that produced it, verbatim. It costs screen space and turns an oracle into a tool. These people can read SQL, and letting them check beats any amount of confident phrasing.

Where it stands

Straight about status: the trust layer is built and running, and the proof it's driven fabrication to zero isn't in yet. The transcript analysis that started this hasn't been re-run against the current build. I'd rather say that than put up a number I can't back.