Skip to content

Instantly share code, notes, and snippets.

@revskill10
Forked from 001101/accounting.sql
Last active April 15, 2022 02:31
Show Gist options
  • Save revskill10/4a90542c102e034ae72c7f254f94734c to your computer and use it in GitHub Desktop.
Save revskill10/4a90542c102e034ae72c7f254f94734c 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,
parent_id INTEGER REFERENCES section,
parent_path LTREE
);
CREATE INDEX account_parent_path_idx ON accounts USING GIST (parent_path);
CREATE INDEX account_parent_id_idx ON accounts (parent_id);
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();
create extension ltree;
CREATE OR REPLACE FUNCTION update_account_parent_path() RETURNS TRIGGER AS $$
DECLARE
path ltree;
BEGIN
IF NEW.parent_id IS NULL THEN
NEW.parent_path = 'root'::ltree;
ELSEIF TG_OP = 'INSERT' OR OLD.parent_id IS NULL OR OLD.parent_id != NEW.parent_id THEN
SELECT parent_path || id::text FROM accounts WHERE id = NEW.parent_id INTO path;
IF path IS NULL THEN
RAISE EXCEPTION 'Invalid parent_id %', NEW.parent_id;
END IF;
NEW.parent_path = path;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER parent_path_tgr
BEFORE INSERT OR UPDATE ON accounts
FOR EACH ROW EXECUTE PROCEDURE update_account_parent_path();
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment