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 commented Mar 14, 2017

bingwallpaper-2015-12-16

@staalung

This comment has been minimized.

Copy link

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 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 commented Jan 16, 2019

absolutely fantastic. what i've been looking for

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.