Basic double-entry bookkeeping system, for PostgreSQL.

accounting.sql

CREATE TABLE accounts(
	id serial PRIMARY KEY,
	name VARCHAR(256) NOT NULL,
	balance NUMERIC(20, 2) NOT NULL DEFAULT '0'
);

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 OR REPLACE FUNCTION validate_entry()
RETURNS TRIGGER AS $$
BEGIN
    -- Check if the debit account has enough balance
    IF (SELECT balance FROM accounts WHERE id = NEW.debit) < NEW.amount THEN
        RAISE EXCEPTION 'Insufficient funds in the debit account.';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER validate_before_insert
BEFORE INSERT ON entries
FOR EACH ROW
EXECUTE FUNCTION validate_entry();

CREATE OR REPLACE FUNCTION update_account_balances()
RETURNS TRIGGER AS $$
BEGIN
    -- Decrease the balance from the debit account
    UPDATE accounts SET balance = balance - NEW.amount
    WHERE accounts.id = NEW.debit;

    -- Increase the balance in the credit account
    UPDATE accounts SET balance = balance + NEW.amount
    WHERE accounts.id = NEW.credit;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_balances_after_insert
AFTER INSERT ON entries
FOR EACH ROW
EXECUTE FUNCTION update_account_balances();

i think it would help greatly by introducing a fiscal period column to the entries table. Each year needs to be closed and by adding a fiscal period id, you can update account balances only in that period. For ex by looking up the currently active fiscal period before you update credit and debit, and adding a NEW.fiscal_period_id = active_fiscal_period_id, because no closed fiscal periods should ever update balances anyway.

However, account balances shouldn't just be a number in the accounts table. This doesn't make any sense from an accounting perspective. The account balance needs to be the first entry in the entries table for an asset account like a bank account. Debiting the asset account and crediting another relevant account (where it came from). This way you wont need this update account balance shenanigan. Also i dont think the validate_entry will work since the other entry that increased the account will then need to always be inserted before the entry that decreases it. But i may have misunderstood that part.