Skip to content

Instantly share code, notes, and snippets.

@ak4zh
Created September 22, 2022 08:48
Show Gist options
  • Save ak4zh/3884def2a1d746093bfbc8298589bc78 to your computer and use it in GitHub Desktop.
Save ak4zh/3884def2a1d746093bfbc8298589bc78 to your computer and use it in GitHub Desktop.
Double Entry Book Keeping with Journals and Voucher
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
);
@kennethjor
Copy link

I don't know if it's relevant to your application, but one optimisation I've done in mine is rather than having is_credit, just use positive and negative numbers. That way, you can easily sum up without if-statements.

I also like that you can have multiple entries per transaction. That was a big limitation of the previous one.

@ak4zh
Copy link
Author

ak4zh commented Sep 22, 2022

@kennethjor I originally thought about the negative values but for some reason I had to drop it, I can’t remember the reason right now but I will go through the sql and update here the reason behind that.

@staalung
Copy link

The reason was probably; "despite the use of a minus sign, debits and credits do not correspond directly to positive and negative numbers."

https://en.wikipedia.org/wiki/Debits_and_credits

@ak4zh
Copy link
Author

ak4zh commented Sep 28, 2022

@staalung Yes.
Yes based on the type of ledger a credit can result in both increase or decrease to the ledger balance.
Also in case on journal transactions somestimes both ledger balance increase.

cash received against sales.
So Turnover increase (+) (credit) as well as bank (+) (debit)

@kennethjor
Copy link

@staalung That's true. Whether something is positive or negative depends on the context of the account type. However, in the database schema, you can just define which is which. Since credits and debits will always be opposites within the same account type, being able to use a simple SUM() is nice, IMHO. As long as you don't mix accounts.

@ak4zh
Copy link
Author

ak4zh commented Sep 28, 2022

@kennethjor I have thought this in very detail considering various extreme use case and decided to go with the current structure checking their parent account group to decide how it should be treated.
If you have a cleaner solution, I would love to update the gist.

Just remember, a ledger can be direct child of an Account Group as well as a deep chain of parents.
Sundry Debtors > Type A Debtors > Sub Type A Debtors > XYZ

When a transaction gets entered effecting XYZ you need to make sure if it should increase or decrease for that particular transaction.
Not sure how you will implement SUM for this.

@kennethjor
Copy link

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

@ak4zh
Copy link
Author

ak4zh commented Sep 28, 2022

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment