A reader found a hole in our AI inbox. Postgres closes it now
In my last post I wrote about how our AI assistant can write to exactly one table. Claude, connected to Reperline over MCP, can only insert rows into pending_changes. A person approves or rejects each one in an inbox, an
In my last post I wrote about how our AI assistant can write to exactly one table. Claude, connected to Reperline over MCP, can only insert rows into pending_changes. A person approves or rejects each one in an inbox, and apply_pending_change writes the real tables with the approver's own rights.
I ended that post with "the inbox is where the real control happens". In the comments, Mike Johnstone pointed out that the inbox had a hole of its own:
A proposal can go stale in the inbox too. If someone changes a milestone before approval, the reviewer may be looking at a diff against an old value.
He was right, and the bug was worse than a confusing screen. This post is the fix, migration 064.
How the bug looked on a real site
A milestone proposal carries an absolute value, not a delta:
{ "milestone_id": "...", "progress": 65, "note": "from the site report photos" }
Here is the sequence that broke it:
- The milestone is at 55%. Claude reads a site report and proposes 65%.
- The proposal waits in the inbox over lunch.
- Meanwhile the site manager walks the floor and records 70% from Telegram.
- The project manager opens the inbox, sees "55% to 65%", which looks reasonable, and clicks Approve.
-
apply_pending_changewrites 65%.
Progress just went down by five points. Our normal entry path refuses to lower progress without a note explaining why, but that rule lived in the application function that records a fact from the UI and Telegram. The approval path wrote the journal directly, so the drop went in silently. Nobody did anything wrong, and the database accepted a value that was already false when it was approved.
The grants from the first post did their job. Claude still wrote nothing by itself. A person approved a stale diff, and the approval was the hole.
Option one: compare in the UI
The quick fix is to show the current value next to the proposal and hope the reviewer notices. We do show it now, but a check that lives only on the screen is the same kind of promise the first post was about avoiding. Another client, a script or a second tab can still approve. The check had to be inside apply_pending_change, in the same transaction as the write.
What exactly is "the base"?
Mike suggested storing the base row version with the proposal. The idea is right; choosing which version took most of the work.
A row version such as xmin. It changes on any update of the milestone row, including a renamed milestone or a corrected planned date. Every unrelated edit would strand every open proposal.
The current progress value alone. It misses the case 70 β 55 β 70: someone records a drop, then measures again and records 70. The value is back where it was, but two facts were entered that the proposal never saw. The reviewer should look again.
The id or timestamp of the latest journal row. Facts can be back dated (reported_at is when the work was measured, not when it was typed), so "latest by reported_at" misses a late entry of an older measurement. created_at is the transaction start time, so rows inserted in one transaction tie, and a transaction that started earlier can commit later.
What we used: the number of journal rows plus the progress value. Every fact for a milestone is a new row in milestone_updates, and a trigger moves milestones.progress on every insert. So the count changes exactly when progress may have changed, regardless of dates. Comparing progress as well covers the remaining blind spot (a delete plus an insert keeps the count). Our app never deletes a fact, but the role technically still holds DELETE, so we did not want to rely on that.
Stamping the base: the client does not get a say
If Claude sent the base, a prompt could make it send whatever value makes the check pass. So the base is stamped by a BEFORE INSERT trigger, and anything the client put in those keys is overwritten:
create function stamp_proposal_base() returns trigger language plpgsql as $$
declare v_progress int; v_facts bigint;
begin
if new.kind <> 'milestone_update' or jsonb_typeof(new.payload) <> 'object' then return new; end if;
-- one statement, one snapshot: progress and count describe the same moment
select m.progress,
(select count(*) from milestone_updates u where u.milestone_id = m.id and u.org_id = m.org_id)
into v_progress, v_facts
from milestones m
where m.id = nullif(new.payload->>'milestone_id', '')::uuid and m.org_id = new.org_id;
new.payload := new.payload || jsonb_build_object('base_progress', v_progress, 'base_facts', v_facts);
return new;
end $$;
create trigger b_stamp_base before insert on pending_changes
for each row execute function stamp_proposal_base();
Two details:
- The function is
SECURITY INVOKER, so it reads with the proposer's row level security. If the proposer cannot see the milestone, the base is stamped as null, and a null base never matches, so such a proposal can never be approved. - The trigger name starts with
b_on purpose. Postgres firesBEFOREtriggers in name order, and oura_guard_closedtrigger, which refuses changes to a closed phase, has to run first.
Checking it: lock first, then read
In apply_pending_change the milestone branch now does this before writing:
-- lock first, then read in a new statement
perform 1 from milestones
where id = (pl->>'milestone_id')::uuid and org_id = pc.org_id
for update;
select m.progress,
(select count(*) from milestone_updates u where u.milestone_id = m.id and u.org_id = m.org_id)
into cur_progress, cur_facts
from milestones m
where m.id = (pl->>'milestone_id')::uuid and m.org_id = pc.org_id;
if (cur_facts, cur_progress) is distinct from
((pl->>'base_facts')::bigint, (pl->>'base_progress')::int) then
raise exception 'proposal_stale';
end if;
The split into two statements matters. Under READ COMMITTED, each statement inside a PL/pgSQL function gets a fresh snapshot. If the count ran in the same statement as the lock, it could use a snapshot taken before the lock was granted. A fact that committed while we were waiting would be missing from the count, and we would approve over it.
The lock also closes the window from the other side. Inserting a fact takes a KEY SHARE lock on the milestone (the foreign key) and the trigger then updates the row, so no fact can land between our check and our own journal insert.
is distinct from instead of <> matters too: it treats null as a value, so a null base never compares equal to anything.
Rejecting is never checked. A stale proposal can always be thrown away; it just cannot be approved.
The inbox
The UI shows the same thing the database enforces. A stale proposal gets a red notice ("It was 55% when proposed. A fact was entered since, so the proposal can only be rejected.") and a disabled Approve button. That is a display hint only. The function refuses on its own, and the error key proposal_stale is translated for anyone who reaches it some other way.
Proposals that were already waiting when the migration ran were backfilled with the current state, so the existing inbox was not stranded by the upgrade.
The test that made me trust it
Each case runs against a real Postgres, with Claude's role filing the proposal:
- nothing changed since the proposal: approval works;
- a fact after the proposal: approval refused, and the site fact stands;
- 70 β 55 β 70: refused, because the count moved;
- rejecting a stale proposal still works;
- Claude sends
base_progress: 10, base_facts: 99: both are overwritten by the trigger; - two open proposals for one milestone: approving one makes the other stale;
-
lock order: a second connection inserts a fact and holds the transaction open. The approval starts and the test polls
pg_locksuntil it sees the approval waiting. Then the fact commits, and the approval must fail withproposal_stale.
That last one is the case that is easiest to get wrong and hardest to see in review, so it is the one I would not ship without.
What I take from this
The first post was about making sure the AI cannot write. This one is about the next layer: a person approving is only meaningful if they approve against the data as it is now. Optimistic concurrency is an old idea; the work was in choosing a version marker that changes when it should and only then, and in making the stamp something the proposer cannot fake.
And thanks to Mike. A comment section that finds a real bug in a week is worth more than any launch post.
If you are building an approval inbox for an AI agent: store what the proposal was based on, stamp it on the server, and check it under a lock in the same transaction that writes.
Originally published by Dev.to AI. Aggregated on AIWithGhost for educational purposes β full credit and traffic to the original publisher.