DEV Community

Cover image for A reader found a hole in our AI inbox. Postgres closes it now
Viacheslav Bobok
Viacheslav Bobok

Posted on Edited on AI-assisted

A reader found a hole in our AI inbox. Postgres closes it now

Community spot for a subtle race condition fix

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" }
Enter fullscreen mode Exit fullscreen mode

Here is the sequence that broke it:

  1. The milestone is at 55%. Claude reads a site report and proposes 65%.
  2. The proposal waits in the inbox over lunch.
  3. Meanwhile the site manager walks the floor and records 70% from Telegram.
  4. The project manager opens the inbox, sees "55% to 65%", which looks reasonable, and clicks Approve.
  5. apply_pending_change writes 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();
Enter fullscreen mode Exit fullscreen mode

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 fires BEFORE triggers in name order, and our a_guard_closed trigger, 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;
Enter fullscreen mode Exit fullscreen mode

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_locks until it sees the approval waiting. Then the fact commits, and the approval must fail with proposal_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.

Top comments (21)

Collapse
 
izgorodin profile image
Edward Izgorodin •

The base you stamp covers what the database knew when the proposal was filed. There is a second base the trigger cannot see: when the evidence behind the proposal was measured. Claude proposed 65 from the site report photos. If those photos were taken before the 70 that is already in the journal, the count and the progress both match at approval time and the check passes, yet the proposal is older than the fact it would overwrite. The reviewer sees 70 to 65 and can read it as a correction.

Facts already carry reported_at, so the proposal could carry one too, taken from the source it was made from, and apply_pending_change could refuse when it is older than the newest fact's reported_at. That is the back-dating case you used to rule out the latest-row base, seen from the proposal's side.

Picked as gem
Collapse
 
appwebseo profile image
Viacheslav Bobok •

You’re right, and it’s the better point of the two bases. The proposal already carries the evidence date: propose_milestone_update takes observed_on and stores it as reported_at, and the approval writes it into the journal. But nothing compares it with the journal. Worse, the sync trigger moves milestones.progress on every insert, whatever its reported_at, so approving an older measurement overwrites a newer one.

Today one thing softens your scenario. If the 70 is already in the journal when Claude proposes 65, the tool sees the drop and requires a note, so it can’t arrive silently. That only helps if the note is honest, though. The fix is what you describe: refuse in apply_pending_change when the proposal’s reported_at is older than the newest fact’s reported_at. Same rule as the stale check: such a proposal can only be rejected.

Collapse
 
izgorodin profile image
Edward Izgorodin •

The refusal in apply_pending_change closes the path through the inbox, and the trigger is where the same rule protects every other path. As long as milestones.progress follows the newest inserted row, a manual insert or an import of an older measurement still moves it backwards. If the trigger set progress from the row with the latest reported_at instead of the latest insert, the journal would keep the late arrival as history and the milestone would keep showing the newest measurement, whichever way the row came in.

Thread Thread
 
appwebseo profile image
Viacheslav Bobok •

You’re right, and it’s the better fix. Recent migration 069_outdated_evidence guards one door; the trigger is the room. Right now apply_milestone_update copies new.progress from whichever row was inserted last. A site manager who enters last week’s measurement today, which the office form and Telegram both allow, moves the milestone backwards just like a stale proposal would.

Taking progress from the row with the latest reported_at (ties broken by insertion order) makes the journal the source of truth. A late arrival becomes history instead of the current value. Start and finish dates need the same treatment: they’re derived from the same row today, so an out of order 100% can set an actual finish date that a newer measurement contradicts.

It changes what the inbox check means, too. Once the trigger ignores older evidence, approving it no longer overwrites anything; it only adds history. I’d still keep the 069 refusal, because a reviewer approving a change that has no effect is its own kind of confusing. Writing it as the next migration, together with a lock timeout another commenter asked for. Thanks for following the rule down to where it actually lives.

Collapse
 
mike-johnstone profile image
Mike Johnstone •

Thanks for the write-up and the credit! One migration edge: backfilling a waiting proposal with the current state can make an old proposal look fresh. The 55→65 proposal could get a base of 70 without being regenerated. Did those proposals also get a fresh review during the backfill?

Collapse
 
appwebseo profile image
Viacheslav Bobok •

You’re right, and they did not. The backfill stamped the state at migration time, so a proposal filed before 064 inherited a base it never saw. Your 55→65 case passes the check and writes 65.
The exposure is narrow: only milestone proposals that were still pending when 064 ran. But “narrow” is the same argument the original bug hid behind. Thanks for reading the migration this closely.

Collapse
 
mike-johnstone profile image
Mike Johnstone •

For those pending pre-064 milestone proposals, I'd require a new proposal based on current data before approval. Linking it to the old, rejected proposal would preserve the audit trail and make the reason for regeneration visible.

Thread Thread
 
appwebseo profile image
Viacheslav Bobok •

Agreed. Rejecting with a reason already keeps the old proposal in the history, with who decided and why. A link from the new proposal to the one it replaces would make that visible from either side. I’ll fold it into the follow-up.

Thread Thread
 
appwebseo profile image
Viacheslav Bobok •

Agreed, and that’s how it works now. Migration 068 cleared the base on every milestone proposal that was still waiting from before 064. A null base never matches, so those can only be rejected; the inbox says so and disables Approve. Rejecting with a reason keeps the old one in the history. Linking the new proposal back to it is a good idea, and I’ve noted it for later.

Collapse
 
kami_butt_69b922ade8de012 profile image
Agentic Affairs •

The count-plus-value base is the elegant bit — the 70→55→70 case is exactly the trap a value-only check walks into, and the count catches it. The READ COMMITTED snapshot detail is the part most people would get wrong: locking first and reading in a new statement is a subtle requirement. Do you ever see legitimate proposals go stale often enough that reviewers complain, or is the inbox quiet enough that it rarely fires?

Collapse
 
appwebseo profile image
Viacheslav Bobok •

Too early to say. We have one live site, and I don’t have numbers I’d trust yet. By construction it only fires when someone enters a fact on the same milestone between Claude’s proposal and the decision, so it should be rare. When it does fire, rejecting is the right outcome, not friction: the alternative is approving a value nobody measured.

Collapse
 
kami_butt_69b922ade8de012 profile image
Agentic Affairs •

That's the right framing — rejecting a proposal nobody measured isn't friction, it's the control doing its job. The rarity-by-construction point is worth stating plainly, since readers will assume it fires constantly. When it does fire once, will you log it as a case study, or keep it quiet until you have a few?

Collapse
 
x1y1co profile image
X1Y1 •

Good call stamping the base in a trigger instead of letting Claude send it. Otherwise the proposer chooses the value the stale check compares against. What happens on Claude's side after a proposal is rejected as stale? If it never finds out, the next site report that mentions the same milestone will likely produce another proposal from the same old number. Do you send the rejection reason back over MCP, or does it re-read the milestone before every proposal?

Collapse
 
appwebseo profile image
Viacheslav Bobok •

It re-reads, and it doesn’t hear back. propose_milestone_update reads the milestone’s current progress every time. The summary is built from that value, and a drop needs a note, so a site report that still says 65 when the journal says 70 can’t become a silent proposal. Claude has to explain the drop, and the reviewer sees “70% → 65%” with that explanation. The tool also warns when another proposal for the same milestone is still open. What it doesn’t do is return rejection reasons: there’s no MCP tool that reads decisions. That would be a nice addition for a model that learns within a conversation, though I’d keep it read only.

Collapse
 
jsbry profile image
Joshua Bryan •

Re-reading every time is the safer half, and it's what our runs would recommend too. If you do add a decisions read, the wording of the reason matters. In our conflict-error study (1,688 agent runs, exploratory, almost all on one small model), an error that listed the record's state unquoted, with one label spelled like the version, led the agent to resend its old values under the new version when told not to re-read: the other writer's change was lost in 38 of 48 runs. Quoting the values: 0 of 48. None of the agents' final messages after a loss mentioned it. So I'd return the current value as a quoted field, not a sentence.
https://isokron.ai/research/mcp-conflict-errors-ai-agent?utm_source=devto&utm_medium=social&utm_campaign=mws-launch&utm_content=comment

Collapse
 
useaifix profile image
AIFix •

Counting journal rows plus the progress value is a neat way to catch the 70 → 55 → 70 case that a value-only check misses. One honest question: if the proposal sits in the inbox for days, do you show the reviewer how many new facts came in since it was stamped, so they know how far off the diff is?

Collapse
 
appwebseo profile image
Viacheslav Bobok •

Partly. The card shows the current value next to the proposed one (“now 70% → proposed 65%”) and says that a fact was entered after the proposal was filed. It doesn’t show how many. Since a stale proposal can only be rejected, the distance matters less than it would in a merge. Still, “3 facts since” is cheap to add, because the base count is already in the payload, and it tells the reviewer whether to look at the journal first. I’ll add it.

Collapse
 
vietautomation profile image
Viet Automation •

Choosing the base marker was the interesting part, and the 70 -> 55 -> 70 case is a good argument against comparing only the value.

Two things I'd look at next:

  • count(*) works, but it scans that milestone's whole journal on every proposal and every approval, and it's the reason the delete + insert gap needs the progress comparison too. A per-milestone fact_seq column, bumped by the same trigger that already moves milestones.progress, gives you a number that only goes up. A delete can't bring it back to an old value, and the check becomes a single-row read under the lock you already take.
  • Your lock-order test shows the approval waiting on an open fact transaction. In real use that wait has no upper bound: someone's Telegram insert stuck in an idle-in-transaction session leaves the approver's request hanging. A SET LOCAL lock_timeout inside apply_pending_change, mapped to a "busy, try again" error next to proposal_stale, keeps the inbox responsive.

Small UX point: since a stale proposal can now only be rejected, a "re-propose against current data" action (re-run the proposal with the new base and the original source attached) saves the reviewer from rejecting and then asking for the same thing again.

Collapse
 
appwebseo profile image
Viacheslav Bobok •

fact_seq: agreed. The count runs on an index (milestone_id, reported_at), so it’s cheap at our journal sizes, but a counter that only goes up is the cleaner marker. As you say, a delete can’t move it back, which removes the reason we also compare progress. It also turns the check into a single row read under the lock we already hold. I’ll move the base to it.

lock_timeout: you’re right, and it’s a real gap, not a hypothetical. Nothing in apply_pending_change or in the app’s connection setup limits the wait, so a fact insert stuck idle in a transaction would leave the approver’s request hanging. A SET LOCAL lock_timeout in the function, mapped to “busy, try again” next to proposal_stale, is the fix.

Re-propose: a good idea. The awkward part is that a proposal from Claude comes from a conversation, not from a stored source we can replay. “Re-propose” would mean asking the source again with the current value attached. For Telegram it’s simpler. I’ll start with the first two.

Collapse
 
ascendra_ventures_92c21f2 profile image
Ascendra Ventures •

Mike's point that a proposal can go stale while it waits in the inbox, and the fix of stamping the base into every proposal and refusing the approval inside the database, keeps "a person approved it" from meaning "a person approved an old picture". Edward's second base, when the evidence itself was measured, looks like the natural next layer.

The question you are answering is the one we wrote about at Ascendra Ventures when we launched CleanSync, our tool that keeps Google Drive and Gmail on a server you own: what is the system allowed to do without a human? Our answer was that every sync is rehearsed first and stops to show you the list if it would delete more files than your limit, anything replaced or deleted goes to a Recovery folder first, and mail rules written in plain words suggest first, with every change able to be undone. Like your inbox, the model proposes and something deterministic decides.

Can a reviewer in Reperline undo a change after it has been applied, or is the stale check the last line of defence?

Collapse
 
appwebseo profile image
Viacheslav Bobok •

Not as one click, but nothing is lost either. Progress lives in an append only journal: a wrong value is corrected by entering a new fact, a drop needs a note, and both rows stay. Transactions are never edited or erased in the UI; the owner voids one with a reason, the row stays struck through, and the views skip it. Every change to a main table is in the audit log with who, when, before and after. So the stale check isn’t the last line of defence, but undo is a new, visible entry rather than a rewind. That’s deliberate: on a building site, “what we thought on Tuesday” is evidence too.