Basic double-entry bookkeeping system, for PostgreSQL.

accounting.sql

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

@ak4zh can you share your gist?

@ameenpv My sql has grown quite complicated now as I was trying to build a full fledged Tally alternative.
I will see if I can copy the relevant parts and add as a gist.

@ak4zh that'll be cool, thanks in advance.

@ameenpv Here’s the gist https://gist.github.com/ak4zh/3884def2a1d746093bfbc8298589bc78
Just copied the relevant parts not sure if I missed something, you will have to wipe out the parts related to organisations (my app was a multi tenant app).

It may not work directly and some changes will be required but you get the idea.
It does not re-calculate all balances so is quite light weight.

@ak4zh alright, that was super helpful. thanks

Thanks for the gist

@ak4zh by chance do you have the version that does the "update only the balance account for the accounts that have been updated ?"

I'm thinking about adding a column "balance" in account, and to have instead the trigger do UPDATE SELECT instead

Thanks for the gist

@ak4zh by chance do you have the version that does the "update only the balance account for the accounts that have been updated ?"

I'm thinking about adding a column "balance" in account, and to have instead the trigger do UPDATE SELECT instead

Here you go: https://gist.github.com/masonforest/a0b595b18e728301db7feda9f5aa725b.

The nice thing about @ak4zh's solution is account_balances is read-only.

Calculating all balances after each insert does feel like it could be a performance issue though if you're inserting a lot of entries.

@masonforest thanks a lot !

Thanks for sharing everyone! Here's my $0.02 schema for a multi-tenant, multi-currency implementation based on the code in this thread:

https://gist.github.com/sundbry/80edb76658f72b7386cca13dd116d235