Idempotent Database Transactions for Safe Payroll Posts

The Bug That Could Corrupt the Books Mid-Run

I found this one during an adversarial review of my own systems, where I go looking for the failure that hasn't happened yet. The candidate was a payroll-style run, the kind of process that has to be right every single time because it touches real money.

Five separate writes, one fragile sequence

The run wrote five distinct things, in order. A pay-run row. The individual paychecks. The paycheck lines (the breakdown of every earning and deduction). The year-to-date snapshots. And finally the journal entry that posts the whole thing to the general ledger.

Five writes. Five separate steps. Each one its own trip to the database.

That sequence looks fine when everything works. It is a time bomb when something doesn't.

Why a crash leaves you worse than nothing

Picture the process dying between step 3 and step 4. A deploy, a timeout, a dropped connection, anything.

The partial-write failure across five sequential database writes

Now you have paychecks and paycheck lines in the database, but no YTD snapshot and no journal entry. The payroll exists but never posted to the ledger. Or it dies a step later and the YTD updated but the journal entry never landed, so nothing reconciles.

The books are now in a state that is both wrong and un-rerunnable. You can't just run it again, because a naive retry writes the first three steps a second time. So you're stuck cleaning it up by hand, in a financial system, under time pressure.

Here's the part that makes this dangerous. A partial write looks done. The paychecks are sitting right there. Nobody knows the journal entry is missing until reconciliation fails days later.

In a financial system, a partial write is worse than no write at all. No write tells you clearly that nothing happened. A partial write lies to you.

So the honest buyer question is: what happens when an AI-built financial process crashes mid-run? If the answer is "we hope it doesn't," that's not an answer.

Why "Just Retry It" Is the Wrong Instinct

The first instinct when one of these dies is to re-run it. Of course it is. The run failed, so run it again.

Double-posting is the default failure mode

Without guardrails, that quietly makes everything worse.

Re-running writes a second pay-run row. A second set of paychecks. A second journal entry posting the same money to the ledger again. Now you haven't fixed the partial write, you've double-posted real dollars on top of it.

That is the default failure mode of any quickly built financial feature: the retry doesn't recognize that part of the work already happened, so it just does the work again. Double-posting isn't an edge case here. It's what naturally happens.

The difference between atomic and idempotent

You actually need two properties, and they're different things.

Atomic vs Idempotent, two different properties you need

Atomic means all-or-nothing. The entire run commits, or none of it does. There is no in-between state where three of five writes landed. The crash between step 3 and step 4 simply becomes impossible.

Idempotent means running it twice produces the same result as running it once. The second run sees that this period already posted and does nothing instead of posting again.

You need both. Atomic stops the partial write. Idempotent stops the double-post. Most quick-built financial features have neither, because the developer treated each table write as its own independent step instead of one indivisible operation.

This is also why money-moving logic should be deterministic, not improvised. I covered the broader version of this in let the code do the math. The AI can decide what to run. The code has to run it the same way every time.

Move the Whole Run Into One Postgres Transaction

The fix is structural, and it's simpler than the workarounds people reach for.

One RPC, five writes, one commit

I collapsed all five writes into a single Postgres function, an RPC the application calls once. Inside that function, every write happens within one transaction. The pay-run, the paychecks, the lines, the YTD snapshots, the journal entry. All of it.

Either every row commits, or Postgres rolls all of it back automatically the moment anything errors.

There is no partial state to land in. If the journal entry insert fails, the paychecks that were written milliseconds earlier get rolled back too. The application doesn't have to detect the failure and clean up. It doesn't have to write compensating logic to undo the first three steps. Postgres handles it.

Postgres gives you all-or-nothing for free

Compare that to the before state. Five round-trips from the application, each one committing independently. Five separate moments where a crash leaves you stranded.

Before and after: five independent commits vs one Postgres transaction

The reason doing this at the database layer beats coordinating it in application code is that the database already guarantees atomicity within a transaction. That's a core thing it does. The moment you try to coordinate five independent commits from your app, you're re-implementing a guarantee Postgres hands you for free, and doing it worse.

So you stop fighting it. One function, one transaction, one commit. I went deeper on the mechanics in atomic financial writes in one transaction, but the shape is the whole point: the unit of work is the entire run, not each table.

This single change eliminated the partial-write problem completely. Not reduced it. Eliminated it. The crash between step 3 and step 4 can no longer happen, because there is no step 3 and step 4 from the database's perspective. There's one operation.

Dry-Run That Tests the Exact Real Path

A dry-run that runs different code than the real run tests nothing useful. It validates a fantasy version of the process and misses the bug that only the real path triggers.

The deliberate error that rolls everything back

Here's the pattern I use. The RPC takes a commit flag.

When commit is true, it does the real posting and commits. When commit is false, it executes the entire real posting path, every insert, every constraint check, every validation, and then deliberately raises a DRY_RUN_OK exception at the very end.

That exception forces Postgres to roll the whole transaction back.

So the dry-run runs the actual code. It hits the same unique indexes. It triggers the same foreign key checks. It builds the same journal entry. And then it throws everything away.

Zero residue, full confidence

The result is a dry-run that exercises the exact same code path as a real commit but leaves zero rows behind. You're not simulating the run. You're running it for real and then undoing it.

Dry-run rollback pattern using the real code path

This matters more than it sounds. Most dry-runs are a separate validation branch, a different code path that checks "would this be valid?" That branch drifts from reality over time. Someone adds a constraint to the real path and forgets the validation branch. Now your dry-run passes and your real run fails, which is the worst possible outcome because you trusted the dry-run.

My version can't drift, because it is the real path. The only difference is the exception at the end.

This is the dry run rollback pattern, and once you've used it you stop trusting any dry-run that works differently from the real thing. If the test doesn't run the production code, it isn't testing production.

Three Locks That Make Re-Running a No-Op

Atomicity stops partial writes. It does not stop double-posting. If someone runs the same period twice, both runs are individually atomic and you still post the money twice.

Idempotency is a separate problem, and I solve it with three layers. Because money is involved.

A partial-unique index on posted runs

First, a partial-unique index that allows exactly one posted run per period.

It's partial on purpose. You may want multiple draft rows and multiple voided rows for the same period, that's normal. But only one row can be in the posted state for a given period at a time. The database enforces it. If a second posted run tries to land, the insert fails outright.

A deterministic period key as the external reference

Second, a deterministic external reference keyed to the period. The same period always produces the same key. There's nothing random or timestamp-based about it.

That gives the system a stable way to recognize "I have already posted this exact period." The key for March payroll is the key for March payroll, every time, so the system can match against prior work instead of guessing.

An in-RPC pre-check before any write

Third, an explicit pre-check inside the RPC. Before it writes a single row, the function looks for an existing posted run for this period. If it finds one, it returns a no-op and exits. No writes attempted.

Three independent idempotency locks that make re-running a no-op

Belt, suspenders, and a second belt.

Here's how the three combine. A network blip causes the app to retry, the pre-check catches it and returns the existing run. A user double-clicks the post button, same thing. A cron job overlaps with a manual run, the partial-unique index refuses the second post even if the pre-check somehow raced past it.

There is no sequence of events, no race condition, no double-click, no overlapping job that produces a second posted run. The pre-check handles the common case cleanly. The unique index is the hard floor that physically cannot be crossed. The deterministic key is what lets both of them know what "already posted" means.

Three independent locks, any one of which is enough on its own.

Undoing a Posted Run Without Breaking History

Sometimes you legitimately need to reverse a run. Wrong numbers got posted. A correction is needed. That's a real workflow, and it has to be safe too.

Void with a reversing entry, never a delete

You never delete financial rows. You reverse them.

A separate void RPC posts a reversing journal entry, equal and opposite to the original, and backs the YTD snapshot out cleanly. The original post stays in the database. The reversal sits next to it. You can see both: what was posted, and that it was reversed, with timestamps and reasons.

Delete is the wrong tool, and not by a little. A financial system exists to preserve history. Deleting a posted run destroys the audit trail that justifies its existence. It also desyncs everything downstream that already saw the original entry, because now they're referencing a row that no longer exists.

Backing out YTD cleanly

The YTD snapshot gets reversed the same way, backed out by the exact amounts the original added, so the running totals land back where they were before the post.

And the void is itself atomic, in the same single-transaction style. The reversing journal entry and the YTD back-out commit together or not at all. You can't void halfway any more than you can post halfway.

That's the whole theme in one operation. Every state change is recorded, reversible, and re-runnable safely. The system can always tell you exactly what happened and exactly how it was undone.

What This Means When You Hand Money Logic to Software

Back to the doubt I set up at the start. What happens when an AI-built financial process crashes mid-run?

The honest answer to "what if it crashes?"

With this transaction design, nothing happens. It rolls back clean. The retry is safe. You can re-run it and it either completes the work it didn't finish or recognizes it already finished and does nothing.

That's the honest answer, and it's a good one. But it is not luck. It is a design choice somebody made on purpose, before the crash, because they assumed the crash would come.

The speed of AI-assisted building is real. I've shipped financial features in days that would have taken a team weeks. But that speed is only an asset if someone insists on this exact discipline on the parts that move money. Fast and wrong is not a deal in a payroll system.

Where I draw the line on automation

This is also where I'm careful about what I let run on its own. I wrote about where I draw the line on autonomy, and money-moving operations sit firmly on the cautious side of that line. The AI can build the pipeline, prepare the run, surface the numbers. The posting logic underneath it has to be deterministic, atomic, and idempotent, every time, no exceptions.

This is the kind of review and rebuild I do when I take on a system. I go looking for the partial-write bug, the missing idempotency lock, the dry-run that tests a fantasy. Then I fix it before it costs you a reconciliation nightmare.

If you're a CEO and you're quietly unsure what's actually holding your financial automation together, that's a conversation worth having.

Ready to bring AI leadership into your company?

I work with a small number of companies at a time. If you're serious about AI, apply to work together and I'll review your application personally.

Apply to Work Together