Basic double-entry bookkeeping system, for PostgreSQL.

accounting_ledger.sql

-- Defines a schema for a multi-tenant, multi-currency, double-entry accounting ledger.

-- Trigger design derived from the gist discussion at:
-- https://gist.github.com/NYKevin/9433376
-- Data model inspired by:
-- https://medium.com/@RobertKhou/double-entry-accounting-in-a-relational-database-2b7838a5d7f8
-- https://developer.squareup.com/blog/books-an-immutable-double-entry-accounting-database-service/

BEGIN;

  CREATE SCHEMA accounting;

  CREATE TABLE accounting.ledger (
    ledger_id bigint NOT NULL,
    name text NOT NULL,
    PRIMARY KEY (ledger_id)
  );

  CREATE TYPE accounting.book_type_enum AS enum ('asset', 'liability', 'income', 'expense', 'equity');

  CREATE TABLE accounting.book (
    book_id bigint NOT NULL,
    ledger_id bigint NOT NULL REFERENCES accounting.ledger (ledger_id) ON DELETE RESTRICT,
    book_type accounting.book_type_enum NOT NULL,
    name text NOT NULL,
    asset_code text NOT NULL,
    balance BIGINT NOT NULL,
    PRIMARY KEY (ledger_id, book_id)
  );

  CREATE INDEX billing_ledger_idx
  ON accounting.book
  USING hash (ledger_id);

  CREATE TABLE accounting.journal (
    journal_id bigint NOT NULL,
    ledger_id bigint NOT NULL REFERENCES accounting.ledger (ledger_id) ON DELETE RESTRICT,
    created_at timestamptz NOT NULL,
    PRIMARY KEY (ledger_id, journal_id)
  );

  CREATE TYPE accounting.posting_type_enum AS enum ('credit', 'debit');

  CREATE TABLE accounting.posting (
    posting_id bigint NOT NULL,
    ledger_id bigint NOT NULL REFERENCES accounting.ledger (ledger_id) ON DELETE RESTRICT,
    journal_id bigint NOT NULL,
    book_id bigint NOT NULL,
    memo text NOT NULL,
    asset_code text NOT NULL,
    amount bigint NOT NULL CHECK (amount > 0),
    posting_type accounting.posting_type_enum NOT NULL,
    PRIMARY KEY (ledger_id, posting_id),
    FOREIGN KEY (ledger_id, journal_id) REFERENCES accounting.journal (ledger_id, journal_id) ON DELETE RESTRICT,
    FOREIGN KEY (ledger_id, book_id) REFERENCES accounting.book (ledger_id, book_id) ON DELETE RESTRICT
  );

  CREATE INDEX posting_journal_idx
  ON accounting.posting (ledger_id, journal_id);

  CREATE OR REPLACE FUNCTION journal_postings_imbalance(p_ledger_id bigint, p_journal_id bigint)
  RETURNS TABLE(asset_code text) AS $$
  BEGIN
      RETURN QUERY
      SELECT
          p.asset_code
      FROM
          accounting.posting p
      WHERE
          p.ledger_id = p_ledger_id
          AND p.journal_id = p_journal_id
      GROUP BY
          p.asset_code
      HAVING
          SUM(CASE WHEN p.posting_type = 'debit' THEN p.amount ELSE 0 END) != SUM(CASE WHEN p.posting_type = 'credit' THEN p.amount ELSE 0 END);
  END;
  $$ LANGUAGE plpgsql;


  CREATE OR REPLACE FUNCTION accounting.validate_journal_balance()
  RETURNS TRIGGER AS $$
  DECLARE
    imbalance_record RECORD;
  BEGIN
      -- Check all postings in the journal balance
      FOR imbalance_record IN
          SELECT asset_code FROM accounting.journal_postings_imbalance(NEW.ledger_id, NEW.journal_id)
      LOOP
          RAISE EXCEPTION 'Journal is not balanced. Imbalanced asset code: %', imbalance_record.asset_code;
      END LOOP;

      RETURN NEW;
  END;
  $$ LANGUAGE plpgsql;

  CREATE CONSTRAINT TRIGGER validate_journal_insert_trg
  AFTER INSERT ON accounting.journal
  INITIALLY DEFERRED
  FOR EACH ROW
  EXECUTE FUNCTION accounting.validate_journal_balance();

  CREATE OR REPLACE FUNCTION accounting.validate_posting_journal()
  RETURNS TRIGGER AS $$
  DECLARE
    journal_record RECORD;
  BEGIN
      -- Postings in a journal are sealed when the journal is created
      FOR journal_record IN (
          SELECT created_at
          FROM accounting.journal j
          WHERE
            j.ledger_id = NEW.ledger_id
            AND j.journal_id = NEW.journal_id
            AND j.created_at <> CURRENT_TIMESTAMP
        )
      LOOP
          RAISE EXCEPTION 'Journal is sealed.';
      END LOOP;

      RETURN NEW;
  END;
  $$ LANGUAGE plpgsql;

  CREATE CONSTRAINT TRIGGER validate_posting_insert_trg
  AFTER INSERT ON accounting.posting
  INITIALLY DEFERRED
  FOR EACH ROW
  EXECUTE FUNCTION accounting.validate_posting_journal();

  CREATE OR REPLACE FUNCTION accounting.update_posting_book_balance()
  RETURNS TRIGGER AS $$
  DECLARE
      book_record RECORD;
  BEGIN
      -- Fetch the book type
      SELECT b.book_type
      INTO book_record
      FROM accounting.book b
      WHERE b.ledger_id = NEW.ledger_id AND b.book_id = NEW.book_id AND b.asset_code = NEW.asset_code;

      -- Check if the book record was not found
      IF NOT FOUND
      THEN
         RAISE EXCEPTION 'Invalid asset for posting book: %', NEW.asset_code;
      END IF;

      -- Update the balance based on book type
      IF NEW.posting_type = 'debit'
      THEN
          IF (book_record.book_type IN ('asset', 'expense'))
          THEN
              UPDATE accounting.book
              SET balance = balance + NEW.amount
              WHERE ledger_id = NEW.ledger_id AND book_id = NEW.book_id;
          ELSE
              UPDATE accounting.book
              SET balance = balance - NEW.amount
              WHERE ledger_id = NEW.ledger_id AND book_id = NEW.book_id;
          END IF;
      ELSIF NEW.posting_type = 'credit'
      THEN
          IF (book_record.book_type IN ('asset', 'expense'))
          THEN
              UPDATE accounting.book
              SET balance = balance - NEW.amount
              WHERE ledger_id = NEW.ledger_id AND book_id = NEW.book_id;
          ELSE
              UPDATE accounting.book
              SET balance = balance + NEW.amount
              WHERE ledger_id = NEW.ledger_id AND book_id = NEW.book_id;
          END IF;
      END IF;

      RETURN NEW;
  END;
  $$ LANGUAGE plpgsql;

  CREATE TRIGGER update_balances_after_insert_trg
  AFTER INSERT ON accounting.posting
  FOR EACH ROW
  EXECUTE FUNCTION accounting.update_posting_book_balance();

COMMIT;