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