Double Entry Book Keeping with Journals and Voucher

migration.sql

CREATE TABLE public.vouchers (
		id serial PRIMARY KEY,
		name text NOT NULL
);

INSERT INTO vouchers  
	(name)
VALUES
	('Sales'),
	('Purchase'),
	('Payment'),
	('Receipt'),
	('Contra'),
	('Journal'),
	('Credit Note'),
	('Debit Note');

CREATE TABLE transactions (
		id BIGSERIAL PRIMARY KEY,
		organization_id uuid NOT NULL REFERENCES organizations(id),
		voucher_id bigint NOT NULL REFERENCES vouchers(id),
		created_at timestamp without time zone DEFAULT now(),
		date date not null default current_date,
		description text COLLATE pg_catalog."default" NOT NULL,
		data jsonb
);

CREATE TABLE journals(
		id BIGSERIAL PRIMARY KEY,
		organization_id uuid NOT NULL REFERENCES organizations(id),
		ledger_id bigint NOT NULL REFERENCES ledgers(id),
		transaction_id bigint NOT NULL REFERENCES transactions(id) ON DELETE CASCADE,
		amount NUMERIC(20, 2) NOT NULL,
		is_credit boolean NOT NULL
);

CREATE VIEW normalized_journals as
select 
	journals.*, 
	CASE 
		WHEN (ledgers.account_group_id IN (3,4,7,11,12,13) OR account_groups.parent_id IN (3,4,7,11,12,13)) THEN
			CASE
				WHEN journals.is_credit THEN
					journals.amount
				ELSE
					0.0 - journals.amount
			END
		ELSE
			CASE
				WHEN NOT journals.is_credit THEN
					journals.amount
				ELSE
					0.0 - journals.amount
			END
	END AS normalized_amount
from journals
JOIN ledgers ON ledgers.id = journals.ledger_id
JOIN account_groups ON ledgers.account_group_id = account_groups.id;

CREATE OR REPLACE FUNCTION public.update_closing_balance_for_change_of_ledgers()
		RETURNS trigger
		LANGUAGE 'plpgsql'
		COST 100
		VOLATILE NOT LEAKPROOF SECURITY DEFINER
AS $BODY$
	DECLARE 
		new_balance numeric;
BEGIN
	SELECT
		COALESCE(SUM(normalized_journals.normalized_amount), 0.0) into new_balance
	FROM
		normalized_journals
	JOIN ledgers ON ledgers.id = normalized_journals.ledger_id
	GROUP BY normalized_journals.ledger_id, ledgers.id
	HAVING normalized_journals.ledger_id = NEW.id;
	UPDATE public.ledgers
		SET closing_balance = opening_balance + COALESCE(new_balance, 0.0)
	WHERE ledgers.id = NEW.id;
	RETURN NULL;
END
$BODY$;

CREATE OR REPLACE FUNCTION public.update_closing_balance_for_change_of_journals()
		RETURNS trigger
		LANGUAGE 'plpgsql'
		COST 100
		VOLATILE NOT LEAKPROOF SECURITY DEFINER
AS $BODY$
	DECLARE 
		new_balance numeric;
BEGIN
	SELECT
		COALESCE(SUM(normalized_journals.normalized_amount), 0.0) into new_balance
	FROM
		normalized_journals
	JOIN ledgers ON ledgers.id = normalized_journals.ledger_id
	GROUP BY normalized_journals.ledger_id, ledgers.id
	HAVING normalized_journals.ledger_id = NEW.ledger_id;
	UPDATE public.ledgers
		SET closing_balance = opening_balance + COALESCE(new_balance, 0.0)
	WHERE ledgers.id = NEW.ledger_id;
	RETURN NULL;
END
$BODY$;

CREATE TRIGGER trigger_update_closing_balance_for_change_of_ledgers
	AFTER INSERT OR UPDATE OF opening_balance
	ON public.ledgers
	FOR EACH ROW
	EXECUTE PROCEDURE update_closing_balance_for_change_of_ledgers();

CREATE TRIGGER trigger_update_closing_balance_for_change_of_journals
	AFTER INSERT 
	OR UPDATE OF amount
	OR DELETE
	ON public.journals
	FOR EACH ROW
	EXECUTE PROCEDURE update_closing_balance_for_change_of_journals();

CREATE OR REPLACE FUNCTION insert_transaction(organization_id uuid, transaction_record json, journal_records json[])
RETURNS setof public.transactions AS $$
DECLARE
	new_transaction_id int;
	x json;
	new_journal_ids bigint[];
	newest_journal_id int;
	credit_total numeric;
	debit_total numeric;
BEGIN
	credit_total = 0;
	debit_total = 0;
	FOREACH x IN ARRAY journal_records LOOP
		IF (x->>'is_credit')::boolean is true
		THEN credit_total = credit_total + (x->>'amount')::numeric;
		ELSE debit_total = debit_total + (x->>'amount')::numeric;
		END IF;
	END LOOP;

	IF 
		credit_total != debit_total 
	THEN 
		RAISE EXCEPTION 'Credit amount must match debit amount';
	END IF;

	-- this check ensures to not enter a transaction if:
	-- the debit or credit is 0 or negative
	-- debit or credit entries are not specified for the transaction
	IF 
		credit_total < 1
	THEN 
		RAISE EXCEPTION 'Credit and Debit amount must be greater than 0';
	END IF;

	INSERT INTO public.transactions (organization_id, date, data, description, voucher_id) VALUES(
		organization_id,
		(transaction_record->>'date')::date,
		(transaction_record->>'data')::jsonb,
		(transaction_record->>'description')::text,
		(transaction_record->>'voucher_id')::bigint
	) RETURNING transactions.id INTO new_transaction_id;
	FOREACH x IN ARRAY journal_records LOOP

		INSERT INTO public.journals (organization_id, transaction_id, ledger_id, amount, is_credit) VALUES(
			organization_id,
			new_transaction_id,
			(x->>'ledger_id')::bigint,
			(x->>'amount')::numeric,
			(x->>'is_credit')::boolean
		) RETURNING journals.id INTO newest_journal_id;
		new_journal_ids := new_journal_ids || newest_journal_id;
	END LOOP;
	RETURN QUERY
	SELECT 
		*
	FROM 
		public.transactions
	WHERE 
		transactions.id = new_transaction_id;
END
$$ LANGUAGE plpgsql;


INSERT INTO account_groups
		(name, parent_id, icon)
VALUES
		('Branch/Division', null, 'mdi:source-branch'),
		('Current Assets', null, 'mdi:laptop'),
		('Indirect Income', null, 'mdi:cash'),
		('Capital Accounts', null, 'mdi:account-cash-outline'),
		('Fixed Assets', null, 'mdi:home-city'),
		('Misc. Expenses', null, 'mdi:folder'),
		('Loans (Liability)', null, 'mdi:cash-lock'),
		('Investments', null, 'mdi:coffee-maker'),
		('Purchase Accounts', null, 'mdi:cart-arrow-down'),
		('Suspense Account', null, 'mdi:alert-circle-outline'),
		('Sales Accounts', null, 'mdi:cart-arrow-up'),
		('Direct Income', null, 'mdi:cash-plus'),
		('Current Liabilities', null, 'mdi:cash-lock-open'),
		('Indirect Expenses', null, 'mdi:cash-refund'),
		('Direct Expenses', null, 'mdi:cash-minus'),
		('Sundry Creditors', 13, 'mdi:cash-lock-open'),
		('Secured Loans', 7, 'mdi:cash-lock'),
		('Bank Accounts', 2, 'mdi:bank'),
		('Stock in Hand', 2, 'mdi:bookshelf'),
		('Deposits', 2, 'mdi:cash-fast'),
		('Cash in Hand', 2, 'mdi:cash'),
		('Duties & Taxes', 13, 'mdi:cash-register'),
		('Banks OD Accounts', 7, 'mdi:bank-transfer-out'),
		('Loan & Advances (Assets)', 2, 'mdi:cash-100'),
		('Unsecured Loans', 7, 'mdi:cash-lock-open'),
		('Provisions', 13, 'mdi:cash-lock-open'),
		('Reserves & Surplus', 4, 'mdi:cash-lock-open'),
		('Sundry Debtors', 2, 'mdi:account-cash-outline'),
		('Parties', 2, 'mdi:account-tie'),
		('Lorry Suppliers', 13, 'mdi:card-account-details-outline'),
		('Drivers', 13, 'mdi:steering');

CREATE TABLE IF NOT EXISTS public.ledgers
(
		id BIGSERIAL PRIMARY KEY,
		created_at timestamp without time zone DEFAULT now(),
		maintain_balance_bill_by_bill boolean,
		name text COLLATE pg_catalog."default" NOT NULL,
		email email,
		organization_id uuid NOT NULL REFERENCES organizations(id),
		account_group_id bigint NOT NULL REFERENCES account_groups(id),
		opening_balance numeric(20, 2) NOT NULL DEFAULT 0.0,
		closing_balance numeric(20, 2),
		additional_fields jsonb,
		data jsonb,
		allow_transaction_refrence boolean
);

@ak4zh You have to separate the data from the representation. If you define the data from the standpoint of an asset account and say a debit is a positive number and a credit is a negative number.

Let me show you an example. Suppose you have an asset account with the following entries:

• Dr 2,000
• Cr 500
• Final balance: Dr 1,500

Replace that with raw numbers and you have 2,000 + (-500) = 1,500. This is a positive number, thus it's a debit.

Suppose you have a liability account with similar entries:

• Cr 2,000
• Dr 500
• Final balance: Cr 1,500

Again replace that with raw numbers and you say (-2,000) + 500 = -1,500. This is a negative number, thus it's a credit.

Of course, you'd want to display a credit as a positive number on the liability account, so you invert it in your presentation layer. This allows you to use SUM() directly in your SQL and get the correct result.

@kennethjor I am sure I had considered using positive and negative values as well.
But hit some roadblocks and then moved to use the is_credit boolean.

At the moment I am unable to remember the issues I faced.
Lazy me, I should have added the issues as comments.

But yeah this looks like a simpler approach.
I will try to dig more and see if every use case can be covered with positive or negative values instead.

I think the problem was to detect the account type on the front-end.
If you look at this comment

It was a hassle to detect if XYZ should increase or decrease for a particular entry.
So instead of writing all the logic in my app code I moved the logic to the database.