Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Basic double-entry bookkeeping system, for PostgreSQL.
CREATE TABLE accounts(
id serial PRIMARY KEY,
name VARCHAR(256) NOT NULL
);
CREATE TABLE entries(
id serial PRIMARY KEY,
description VARCHAR(1024) NOT NULL,
amount NUMERIC(20, 2) NOT NULL CHECK (amount > 0.0),
-- Every entry is a credit to one account...
credit INTEGER NOT NULL REFERENCES accounts(id) ON DELETE RESTRICT,
-- And a debit to another
debit INTEGER NOT NULL REFERENCES accounts(id) ON DELETE RESTRICT
-- In a paper ledger, the entry would be recorded once in each account, but
-- that would be silly in a relational database
-- Deletes are restricted because deleting an account with outstanding
-- entries just doesn't make sense. If the account's balance is nonzero,
-- it would make assets or liabilities vanish, and even if it is zero,
-- the account is still responsible for the nonzero balances of other
-- accounts, so deleting it would lose important information.
);
CREATE INDEX ON entries(credit);
CREATE INDEX ON entries(debit);
CREATE VIEW account_ledgers(
account_id,
entry_id,
amount
) AS
SELECT
entries.credit,
entries.id,
entries.amount
FROM
entries
UNION ALL
SELECT
entries.debit,
entries.id,
(0.0 - entries.amount)
FROM
entries;
CREATE MATERIALIZED VIEW account_balances(
-- Materialized so financial reports run fast.
-- Modification of accounts and entries will require a
-- REFRESH MATERIALIZED VIEW, which we can trigger
-- automatically.
id, -- INTEGER REFERENCES accounts(id) NOT NULL UNIQUE
balance -- NUMERIC NOT NULL
) AS
SELECT
accounts.id,
COALESCE(sum(account_ledgers.amount), 0.0)
FROM
accounts
LEFT OUTER JOIN account_ledgers
ON accounts.id = account_ledgers.account_id
GROUP BY accounts.id;
CREATE UNIQUE INDEX ON account_balances(id);
CREATE FUNCTION update_balances() RETURNS TRIGGER AS $$
BEGIN
REFRESH MATERIALIZED VIEW account_balances;
RETURN NULL;
END
$$ LANGUAGE plpgsql;
CREATE TRIGGER trigger_fix_balance_entries
AFTER INSERT
OR UPDATE OF amount, credit, debit
OR DELETE OR TRUNCATE
ON entries
FOR EACH STATEMENT
EXECUTE PROCEDURE update_balances();
CREATE TRIGGER trigger_fix_balance_accounts
AFTER INSERT
OR UPDATE OF id
OR DELETE OR TRUNCATE
ON accounts
FOR EACH STATEMENT
EXECUTE PROCEDURE update_balances();
@LongPerson

This comment has been minimized.

Copy link

@LongPerson LongPerson commented Mar 14, 2017

bingwallpaper-2015-12-16

@staalung

This comment has been minimized.

Copy link

@staalung staalung commented Jul 25, 2018

How would you handle SPLIT entries like f.ex. Petty Cash, Sale and Tax in double-entry bookkeeping system like this?

@amrfaisal

This comment has been minimized.

Copy link

@amrfaisal amrfaisal commented Sep 30, 2018

@staalung You can use the description field, or add a new column with name like entry_type, or maybe as a reference to another table called entry_types where you define available transaction types for your system.

@phalbert

This comment has been minimized.

Copy link

@phalbert phalbert commented Jan 16, 2019

absolutely fantastic. what i've been looking for

@jordan-dimov

This comment has been minimized.

Copy link

@jordan-dimov jordan-dimov commented Jun 13, 2020

@staalung did you figure out how to have split journal entries? I'm not sure how @amrfaisal's answer helps with this.

@staalung

This comment has been minimized.

Copy link

@staalung staalung commented Jun 25, 2020

@jordan-dimov No, Sorry I did not figure it out.

@amrfaisal

This comment has been minimized.

Copy link

@amrfaisal amrfaisal commented Jul 9, 2020

@jordan-dimov and @staalung You can have another table (Journal) with one to many relationship to entries table. So multiple SPLIT entries can be related to a single source/transaction for example.

@alanjds

This comment has been minimized.

Copy link

@alanjds alanjds commented Sep 4, 2020

Is there a way to constraint balances to go negative? I mean, other than refreshing the materialized view on every entry INSERT.

@kennethjor

This comment has been minimized.

Copy link

@kennethjor kennethjor commented Apr 11, 2021

@staalung @jordan-dimov I'd suggest having a look at this article instead: https://medium.com/@RobertKhou/double-entry-accounting-in-a-relational-database-2b7838a5d7f8

Specifically, this gist implies that each debit is countered by a single equal and opposite credit. This means you can't do split transactions. One of the powerful features of double-entry bookkeeping is that you can have multiple debits and credits for each entry and they don't have to be matched individually. The method outlined in the article above shows how that can be done.

@tomidotomicode

This comment has been minimized.

Copy link

@tomidotomicode tomidotomicode commented Jul 9, 2021

@staalung @jordan-dimov I'd suggest having a look at this article instead: https://medium.com/@RobertKhou/double-entry-accounting-in-a-relational-database-2b7838a5d7f8

Specifically, this gist implies that each debit is countered by a single equal and opposite credit. This means you can't do split transactions. One of the powerful features of double-entry bookkeeping is that you can have multiple debits and credits for each entry and they don't have to be matched individually. The method outlined in the article above shows how that can be done.

Then again, it is relatively simple matter to have two or more entries per transaction (just add transaction table).

@kennethjor

This comment has been minimized.

Copy link

@kennethjor kennethjor commented Jul 15, 2021

Then again, it is relatively simple matter to have two or more entries per transaction (just add transaction table).

Yeah, that's pretty much what the article I linked suggests. Have a journal entry table and a separate journal posting table containing the individual credits and debits.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment