DEV Community

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

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

Viacheslav Bobok on November 17, 2026

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 in...
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.