Basic double-entry bookkeeping system, for PostgreSQL.

accounting.sql

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();