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

How would you handle SPLIT entries like f.ex. Petty Cash, Sale and Tax in double-entry bookkeeping system like this?

@staalung You can use the description field, or add a new column with name like entry_type, or maybe as a reference to another table called entry_types where you define available transaction types for your system.

absolutely fantastic. what i've been looking for

@staalung did you figure out how to have split journal entries? I'm not sure how @amrfaisal's answer helps with this.

@jordan-dimov No, Sorry I did not figure it out.

@jordan-dimov and @staalung You can have another table (Journal) with one to many relationship to entries table. So multiple SPLIT entries can be related to a single source/transaction for example.

Is there a way to constraint balances to go negative? I mean, other than refreshing the materialized view on every entry INSERT.

@staalung @jordan-dimov I'd suggest having a look at this article instead: https://medium.com/@RobertKhou/double-entry-accounting-in-a-relational-database-2b7838a5d7f8

Specifically, this gist implies that each debit is countered by a single equal and opposite credit. This means you can't do split transactions. One of the powerful features of double-entry bookkeeping is that you can have multiple debits and credits for each entry and they don't have to be matched individually. The method outlined in the article above shows how that can be done.

@staalung @jordan-dimov I'd suggest having a look at this article instead: https://medium.com/@RobertKhou/double-entry-accounting-in-a-relational-database-2b7838a5d7f8

Specifically, this gist implies that each debit is countered by a single equal and opposite credit. This means you can't do split transactions. One of the powerful features of double-entry bookkeeping is that you can have multiple debits and credits for each entry and they don't have to be matched individually. The method outlined in the article above shows how that can be done.

Then again, it is relatively simple matter to have two or more entries per transaction (just add transaction table).

Then again, it is relatively simple matter to have two or more entries per transaction (just add transaction table).

Yeah, that's pretty much what the article I linked suggests. Have a journal entry table and a separate journal posting table containing the individual credits and debits.

Btw, is it more efficient from the performance point of view to use materialized view for getting account balances instead of just using a corresponding column in accounts table and updating it at each insert on entry table by trigger?

How will you handle the asset and liability account types in above schema?

Asset balance increase when you credit it.
Liability balance increase when you debit it.

@ak4zh You don't. You simply invert the number in your GUI when you display asset or liability accounts.

Yes this is a play accounting system. In a real accounting program you'd have transactions with multiple entries, none of that debit/credit nonsense we inherited from paper ledgers, and all of it governed by the simple condition that the sum of the amounts associated with each and every transaction must be zero. In a real system you'd have the ability to freeze transactions (but NOT to unfreeze them) so that you can balance your books and be sure that nothing can get changed afterwards. In a real accounting program … and so on and so forth.

@smurfix I see this is a play accounting system but quite good for one of my project where I needed simple book keeping.

I have forked and added the following:

• Table account_groups to hold some common types of accounts
• Modified accounts to add columns opening_balance and account_group_id
• account_ledgers checks the account_group_id to ascertain if account's balance should increase or decrease based on the type of entry (debit / credit)
• account_balances now considers the opening balance to calculate the correct final balance of the ledger

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 !