Skip to content

Instantly share code, notes, and snippets.

@maierru
Forked from RichardKnop/accounting.sql
Created February 6, 2018 09:20
Show Gist options
  • Save maierru/6ecb4817b6bc581dfbaeb92abad6f0b1 to your computer and use it in GitHub Desktop.
Save maierru/6ecb4817b6bc581dfbaeb92abad6f0b1 to your computer and use it in GitHub Desktop.
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();
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment