Build a Double-Entry Accounting System on Postgres

Why I Couldn't Use QuickBooks for These Books

I needed to build a double-entry accounting system from scratch, and I want to be clear up front that I didn't want to. Building your own ledger is the kind of project most sane people avoid. But I had a situation where every off-the-shelf option failed, and not on features. They failed on trust.

Here's the setup. An e-commerce operation was under heavy financial scrutiny. The kind where a third party (think trustee, auditor, opposing counsel) needs to defend the books line by line. Not "the report looks clean." Defend. Prove that no posted transaction was quietly edited after the fact.

I looked at everything.

QuickBooks carried inherited errors from years of sloppy bookkeeping, and worse, there's no way to prove a posted entry was never touched. You're trusting that nobody opened a transaction and changed a number.

Bigcapital is open source, which sounds great until you read the license. Viral copyleft terms that would have created a legal liability for the business. Hard pass.

Xero was the closest fit until I read their API terms, which ban AI processing of the data. The entire workflow I needed depended on AI categorizing and reconciling messy imports. That clause alone killed it.

The problem wasn't that these tools lacked features. They have plenty. The problem is that every single one relies on you trusting that nobody edited a posted transaction. The integrity lives in a promise.

So the real question, the one any skeptical CEO should ask: can custom software actually replace mission-critical accounting software, or is that reckless?

My answer is yes. But only under one condition. The integrity has to be enforced by the database itself, not by a promise, not by application code, and definitely not by good intentions. Let me show you how.

The Core Idea: Integrity You Can't Forget to Check

Trust is a feature you forget

Quick refresher for anyone who hasn't lived in accounting. Double-entry bookkeeping means every transaction has two sides: debits and credits. They must equal each other. Buy $500 of inventory, you debit Inventory $500 and credit Cash $500. The books always balance because every entry balances.

Simple rule. The question is who enforces it.

In QuickBooks, Xero, and basically every double-entry bookkeeping software out there, that balance rule is checked by application code. Some function in the software runs, adds up the debits, adds up the credits, and confirms they match before saving.

You're trusting that code runs correctly every time. You're trusting nobody found a side door. You're trusting that an update path six months from now didn't skip the check. Trust is a feature you forget to verify until the day it matters.

Enforcement happens at the database layer

So I moved the rules down into Postgres itself, using database triggers.

App-layer vs database-layer enforcement

A trigger is a piece of logic that fires automatically inside the database whenever you insert or update a row. It doesn't care what application is talking to it. It doesn't care if an AI is involved. It doesn't care if someone connects directly and pokes the tables by hand.

If the entry is wrong, the database physically refuses to commit it. The transaction rolls back. There is no path around it because the wall isn't in the app, it's in the engine that stores the data.

This is the same principle I follow everywhere I build: make the math deterministic, not AI. The integrity isn't something a human remembers to check or an AI promises to respect. It's a wall. You can't forget to check a wall.

The Five Triggers That Make Bad Data Impossible

Here's the meat. Five enforcement triggers, each with a specific accounting reason for existing.

The five enforcement triggers

Balanced debits and credits at post time

When a journal entry is posted, a trigger sums all the debit lines and all the credit lines. If they don't match to the cent, the entire transaction rolls back.

You cannot post an unbalanced entry. Not by accident, not on purpose, not through any app or import. The fundamental rule of double-entry accounting is enforced by the database, so the books literally cannot go out of balance.

Posted-entry immutability

Once a journal entry is marked posted, a trigger blocks any update or delete on that entry. Full stop.

A draft entry can be edited all day. But the moment it's posted, it's frozen. This is the heart of a defensible ledger. A posted transaction is a historical fact, and historical facts don't change. If you need to fix something, you post a correcting entry, which leaves a trail. You never quietly rewrite history.

Posted-line immutability

Same protection, one level deeper. The individual line items inside a posted entry are also locked.

Why both? Because someone clever might leave the entry header alone and try to sneak an amount change on a single line. The line-level trigger blocks that too. There's no granularity small enough to slip through.

Control-account routing

Some accounts should never be written to by hand. Accounts Receivable, Accounts Payable, and Inventory are control accounts. Their balances must come from sub-ledgers (the actual invoices, bills, and stock movements), not from someone typing a number into a manual journal entry.

A trigger rejects any direct manual write to a control account. If your AR balance is wrong, you fix the underlying invoices, and the control account follows. You can't paper over a reconciliation problem by jamming a number into AR. The Postgres general ledger forces the discipline.

The append-only audit log

Every change to the system captures its full before-and-after state as JSONB and writes it to an append-only log. Who changed what, when, from what value to what value.

The log only grows. You can't edit it, you can't delete from it. Combined with the immutability triggers, this means every legitimate change (posting an entry, creating a correction) leaves a permanent, queryable record.

And closed accounting periods get locked from mutation entirely. Once a month is closed, nothing in it moves. That's how real books work, and now the database guarantees it.

Proving It Works: Smoke Tests That Try to Break It

A skeptical CEO is not going to take "it's secure" on faith. Nor should they. I wouldn't.

Adversarial smoke tests rejecting bad input

So I didn't just build the triggers and call it done. I wrote smoke tests whose entire job is to attack the system. Each test deliberately feeds the database bad input and asserts that the database rejects it.

Five attacks:

• Post an unbalanced entry where debits don't equal credits. Must be rejected.
• Edit a posted transaction. Must be rejected.
• Delete a posted line item. Must be rejected.
• Write a manual entry directly to a control account. Must be rejected.
• Mutate a record inside a closed period. Must be rejected.

When all five rejections fire, you don't have a promise. You have proof.

This is the difference that matters. "We believe the books are right" versus "the books cannot be wrong by construction." The first is an opinion. The second is a property of the system that anyone can verify by running the tests themselves.

This is exactly what a trustee, auditor, or court actually needs. Not a report that looks clean (anyone can produce a clean-looking report). They need a system where tampering is physically impossible and every legitimate change is logged. The smoke tests are the evidence. Run them, watch the database refuse every attack, and the integrity stops being a claim and becomes a demonstrated fact.

Where AI Belongs in This System (And Where It Doesn't)

Now let's address the reckless-AI fear head on, because it's the right fear to have.

AI judges, code computes split

AI did not touch the integrity layer. Not one trigger, not the balance math, not the period locks, not the audit log. All of that is deterministic Postgres. There's no model in the loop deciding whether an entry balances, because that's not a judgment call. It's arithmetic, and arithmetic belongs to code.

So where did AI help? The fuzzy front-end work where judgment actually adds value.

Categorizing transactions from messy bank and payment imports. Drafting an initial chart of accounts. Reconciling imports where the source data was inconsistent, vendor names spelled three different ways, dates in two formats. That's the kind of pattern-matching work AI is genuinely good at, and it saved hours of manual cleanup.

But the moment a number heads for the ledger, it passes through rules no model can override. AI can suggest that a transaction is "office supplies, debit Expenses, credit Cash." It cannot post an unbalanced entry. It cannot edit a posted line. It cannot write to a control account. The triggers don't care that the request came from an AI.

This is the architectural principle I come back to constantly: let the model judge, let the code compute. Let AI handle the interpretation, the categorization, the messy human-language stuff. Let the database enforce the hard rules that have one correct answer.

That split is the whole thing. It's what makes custom accounting software defensible instead of reckless. AI never gets to be the reason the books are wrong, because the books physically can't be wrong.

Build vs. Buy for Mission-Critical Software

Let me zoom out, because if you're a CEO reading this, you're probably wondering whether any of this applies to you.

Build vs buy decision when vendors fail hard requirements

This project was never about saving a subscription fee. QuickBooks is cheap. The point was owning a system that met requirements no vendor would meet: no viral open-source licensing, no terms banning AI on my own data, no inherited errors baked into years of history, and full, provable auditability.

When every vendor fails a hard requirement, "buy" isn't the safe choice. It's the impossible one.

I'm not anti-SaaS. I deleted a pile of subscriptions this year, and I wrote about the SaaS tools I deleted and why. But deleting a project management tool is different from rebuilding your ledger. I want to be honest about the limits here.

This took real engineering. It is not a weekend toy. You do not rebuild your accounting system on a whim, and if a consultant tells you it's easy, run.

But here's the reframe. When the off-the-shelf options actively fail your requirements, a focused custom build on boring, proven infrastructure is the responsible choice, not the risky one. Postgres has run banks and financial systems for decades. There's nothing experimental about it. The experimental, risky move was trusting tools that couldn't prove their own integrity, that asked me to take their word that no posted transaction had ever been quietly edited.

Build versus buy isn't about cost. It's about which option you can actually defend when someone is examining your books line by line.

What This Means for Your Books

Most companies don't need a court-grade ledger. If QuickBooks works for you and nobody's challenging your numbers, keep it. I'm not telling you to go rebuild your accounting.

But here's the part that does transfer.

Almost every company has one or two systems where "the vendor says it's fine" isn't actually good enough. Financial data. Compliance records. Customer PII. The systems where being wrong, or being tampered with, creates real legal or business consequences.

For those systems, the lesson holds regardless of industry. When integrity actually matters, enforce it at the layer that can't be bypassed, and prove it with tests that try to break it. Don't enforce critical rules in application code that you have to trust is running. Push them down to where they can't be skipped, then attack your own system until you're sure it holds.

That's the real takeaway from building a double-entry accounting system this way. The triggers and audit logs are specific to ledgers, but the principle (deterministic enforcement plus adversarial proof) applies anywhere the stakes are high.

If you've got a mission-critical system you can't fully trust and can't fully replace, that's exactly the kind of problem I build for. Not because custom is always better, but because sometimes the off-the-shelf options genuinely can't meet the bar, and somebody has to figure out what does. Happy to talk through what you'd build and whether it's even worth building.

Thinking about AI for your business?

If this resonated, let's have a conversation. I do free 30-minute discovery calls where we look at your operations and find the places AI could actually move the needle, not the hype version, the real version. Book a Discovery Call, or if you'd rather start with the bigger picture, let's talk about what you'd build.