I have been building a thing that lets a language model propose an UPDATE
, then
executes it for real inside a transaction, measures the actual before and after
values, and always rolls back. A human reads the measurement and decides. Only
then does anything commit.
The pitch is one sentence: what you approve is not the model's description of its SQL, it is what the database did when the SQL ran.
Last week I found that the thing showing you that measurement was showing you a
subset of it, and had been since the first release.
Real output, from @hyuga/llm-safe-sql@0.4.0
installed from npm. One row:
name = 'Tanaka'
, postcode = '00100'
.
UPDATE customers SET name='Sato', postcode='00100' WHERE id=1
What this touches
customers — Customer records. The postcode is used for billing address and delivery.
1 row would change, across 1 column: name
Measured by running the statement and rolling it back
id = 1
name: 'Tanaka' -> 'Sato'
One row, one column. postcode
is not mentioned, and that is correct — it is
being assigned the value it already holds, so nothing about it changes. The card
is describing the diff accurately.
Approve it. Then, before it is applied, somebody else notices the postcode is
wrong and fixes it:
UPDATE customers SET postcode='90210' WHERE id=1;
Now apply the approved plan:
Applied: UPDATE on customers, 1 row(s), at 2026-08-10T09:49:12.049Z.
DB now: [{"name":"Sato","postcode":"00100"}]
The fix is gone. Zero warnings. The word postcode
never appeared on the
approval card, never appeared in the audit record, and never appeared in the
comparison the tool makes before it commits.
The diff was built like this:
const changed: string[] = [];
for (const c of Object.keys(before)) {
if (same(before[c], after[c])) continue; // drop what did not move
if (auto.has(lower(c))) continue; // drop what the DB maintains itself
changed.push(c);
}
That is a correct answer to "what should the card show". Showing postcode:
would be noise, and worse than noise — it would be the card
'00100' -> '00100'
claiming a change where there is none.
The problem is that changed
was also the list the apply iterated when it
re-checked that nothing had moved since approval:
for (const c of plan.changed) {
if (!same(live[c], plan.before[c])) throw new ApplyRefused('ROW_CHANGED', …);
}
postcode
is not in changed
, so it is not checked. And the statement writes it
on every execution. A column that is written and never verified.
"The set of columns that change" and "the set of columns the statement writes"
are different sets, and I had used one name for both. SET x = <the value it
is not exotic SQL — it is zero-padded codes, defensive
already has>status
assignments, SET updated_by = 'batch'
. Every one of those is this shape.
There is a second version of the same hole, one level up.
When a WHERE
matches several rows and one of them already holds the target
value, the card says:
1 row would change, across 1 column: status
(1 more match the condition but are already correct.)
That row's changed
is empty, so the verification loop runs zero times — before
the write and after it. Nothing about that row's contents was ever checked.
MySQL was saved by an accident of its protocol. It reports "rows matched" and
"rows changed" separately, so a plan that measured one changed row and an apply
that changed two is a detectable disagreement. PostgreSQL and SQLite rewrite a
row even when the new values equal the old ones, so those two numbers are the
same and the comparison says nothing. The reconciliation code knew this:
if (plan.op === 'UPDATE' && plan.rowsChangedIsMeaningful && res.rowsChanged !== plan.rowsChanged) {
rowsChangedIsMeaningful
is true on MySQL and false on the other two. A guard that worked on one of three supported engines was the only thing standing there.
The fix was to stop conflating the two sets: PlanRow
now carries covered
—
every column the statement assigns — snapshotted before and after even when the
value does not move, included in the tamper digest, and checked at both ends of
the apply. changed
still drives the display. Same sequence on 0.4.2:
Refused (ROW_CHANGED): Row id=1 no longer holds the value you approved:
`postcode` was '00100' when the plan was made and is '90210' now.
Nothing was applied — make a new plan against the current values.
I found this because of how the previous audit failed.
I had run an adversarial review over the codebase — several independent passes by
dimension, each finding verified by separate sceptics whose job was to refute it.
Twenty-one findings survived. Adapters, engine, parser, policy. It felt like a
good day's work.
Then I asked a different question: not "what did you find" but "what did this review structurally not look at?" The first line of the answer:
src/apply.ts
(494 lines at the time) produced zero findings. That should be read as
"nobody opened it", not as "it was clean".
apply.ts
is the only code in the library that writes to production. Dry runs
always roll back. Approval only writes a record. Committing happens in exactly
one place, and the review had not been there.
Scoped to that one file, the same process returned twenty-three findings —
more than the first pass found in the whole rest of the codebase. Everything
above came out of it.
The number 21 had felt like progress. It was a record of where I had looked.
This week I wrote worked examples: the four database accounts the design assumes,
with the exact grants, for MySQL and Postgres. I decided to run every line against
a real server rather than write what I knew to be true.
The server disagreed twice.
A database-wide grant cannot be narrowed. I had written the obvious thing —
grant DML on the whole schema, then take it back on the two tables that hold the
approval records:
ERROR 1147 (42000): There is no such grant defined for user 'llm_plan'
on host '%' on table 'llm_safe_sql_plans'
MySQL will not revoke a table-level subset of a database-level grant. So
GRANT ... ON shop.*
hands the dry-run account write access to the table that
records approvals, permanently. A dry run could forge its own approval. The
examples name each table instead.
And check did not check. The tool has a command whose entire job is to say
ready
and exited 0 — and the omission surfaced on the first plan
, asThat is the likeliest mistake anyone makes on day one, and it was invisible to the
command that exists to catch exactly that class of thing. Fixed in 0.4.2: it
reports the missing table and exits non-zero, and a missing store table is a
refusal on every path rather than a stack trace.
There was a third, smaller one. check
now asks the catalogue whether the tables
exist rather than issuing SELECT 1 FROM audit WHERE 1 = 0
— because the store
account the examples recommend holds INSERT
on the audit table and nothing else,
so the select probe reports the table missing exactly when the credential is as narrow as it is supposed to be. Writing the recommended configuration is what
And then CI failed, because CI runs the README's own quick start verbatim, and my
fix had made the documented order wrong. The instructions said check
then
migrate
; check
now exits non-zero before migrate
has run. Something noticed
before a reader did.
Count what you have not looked at. A list of findings looks like evidence of
thoroughness and is evidence of coverage. The file that should obviously have been
at the top of a risk-ordered list did not appear on the list at all, and absence
looked exactly like safety.
Writing the explanation is a test. Twice now, the act of documenting this
thing has found defects in it that reading the code did not. Not because prose is
magic — because writing an example means running it, and running it means the
server gets a vote. Two privilege lists that were obviously right were wrong.
Watch for one name doing two jobs. The bug here was not a missing check. It
was a display set and a verification set sharing a variable, so narrowing the
display for good reasons silently narrowed the check. If you have something that
both shows a human what will happen and decides whether it may happen, those are
two lists, and they should have two names.
A guard that only works on one backend is not a guard. It is a coincidence
with good timing, and it will be silently absent on the day you switch.
If you are building anything that measures before it asks a human to agree, the
question I would now ask it is: what is the interface not showing me, and how would I know? The answers worth having are specific — which columns does it
None of the five layers people usually reach for would have caught this. Not a
role without write privileges, not a proxy, not an approval dialog, not a stricter
prompt. Every one of these was a legitimate, authorised UPDATE
on an allowlisted
table by a credential entitled to run it. Nothing about what was permitted was
violated. What was wrong was what the human was told before they agreed.
Code, and the full list of what changed in each version, at
github.com/hyuga611/llm-safe-sql.
The examples/
directory is the part I would read first — it is the only
documentation I have written where every line was executed against a real server
before it was committed.