Basic double-entry bookkeeping system, for PostgreSQL.

accounting.sql

CREATE TABLE account_groups
(
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
    name text COLLATE pg_catalog."default" NOT NULL,
    account_group_id bigint,
    CONSTRAINT account_types_pkey PRIMARY KEY (id),
    CONSTRAINT account_groups_account_group_id_fkey FOREIGN KEY (account_group_id)
        REFERENCES public.account_groups (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT account_types_organization_id_fkey FOREIGN KEY (organization_id)
        REFERENCES public.organizations (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
);

INSERT INTO account_groups
    (name, account_group_id)
VALUES
    ('Branch/Division', null),
    ('Current Assets', null),
    ('Indirect Income', null),
    ('Capital Accounts', null),
    ('Fixed Assets', null),
    ('Misc. Expenses', null),
    ('Loans (Liability)', null),
    ('Investments', null),
    ('Purchase Accounts', null),
    ('Suspense Account', null),
    ('Sales Accounts', null),
    ('Direct Income', null),
    ('Current Liabilities', null),
    ('Indirect Expenses', null),
    ('Direct Expenses', null),
    ('Sundry Creditors', 13),
    ('Secured Loans', 7),
    ('Bank Accounts', 2),
    ('Stock in Hand', 2),
    ('Deposits', 2),
    ('Cash in Hand', 2),
    ('Duties & Taxes', 13),
    ('Banks OD Accounts', 7),
    ('Loan & Advances (Assets)', 2),
    ('Unsecured Loans', 7),
    ('Provisions', 13),
    ('Reserves & Surplus', 4),
    ('Sundry Debtors', 2);

CREATE TABLE accounts(
	id serial PRIMARY KEY,
    	account_group_id bigint NOT NULL,
	name VARCHAR(256) NOT NULL,
	opening_balance bigint NOT NULL DEFAULT '0'::bigint,
	CONSTRAINT accounts_account_group_id_fkey FOREIGN KEY (account_group_id)
		REFERENCES public.account_groups (id) MATCH SIMPLE
		ON UPDATE NO ACTION
		ON DELETE NO ACTION,
);

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,
		CASE
			-- if account group is of type which increases on credit then positive else negative
			WHEN cacc.account_group_id IN (3,4,7,11,12,13) THEN entries.amount
			-- if account group is a sub group of account group type which increases on credit, then positive
			WHEN cag.account_group_id IN (3,4,7,11,12,13) THEN entries.amount
			ELSE (0.0 - entries.amount)
		END
	FROM
		entries
	JOIN accounts cacc ON cacc.id = entries.credit
	JOIN account_groups cag ON cacc.account_group_id = cag.id
	UNION ALL
	SELECT
		entries.debit,
		entries.id,
		CASE
			-- Negative, if account group is of type which increases on credit
			WHEN dacc.account_group_id IN (3,4,7,11,12,13) THEN (0.0 - entries.amount)
			-- Negative, if account group is a sub group of account group type which increases on credit
			WHEN dag.account_group_id IN (3,4,7,11,12,13) THEN (0.0 - entries.amount)
			ELSE entries.amount
		END
	FROM
		entries
	JOIN accounts dacc ON dacc.id = entries.credit
	JOIN account_groups dag ON dacc.account_group_id = dag.id;

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(accounts.opening_balance, 0.0) + 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();