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

First of all thanks for this amazing gist, it gave us a lot of direction,

However, this solution works in case you want to know account end_balance,
In our case, we want to generate a general ledger in the way that we would need end_balance after every entry.

⬇️ Similar to this example (referance)

I used this gist to have a MATERIALIZED VIEW which calculates for each entry, not for each account, and currently, with our DB it takes
9 seconds (~300000 rows), but soon this number will be way greater. And entries are happening quite often. So we can not effort to refresh MATERIALIZED VIEW every time something is changing in the journal.

Did anyone had similar issue ?

@Vostan The above gist refreshes everything account balance for every single entry, in my use case I have modified it to update the balance of only the account that is affected by that particular entry and update the closing balance in the account table itself.
Completely eliminated the materialized view, so no joins required as well when you need to get all account balances for reporting.

@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 !