Double-entry ledger

CREATE TYPE account_type AS ENUM ('asset', 'liability', 'equity', 'revenue', 'expense');

CREATE TABLE currencies (
    code char(3) PRIMARY KEY,
    name text NOT NULL,
    minor_units smallint DEFAULT 2 NOT NULL
);

CREATE TABLE accounts (
    id serial PRIMARY KEY,
    parent_id integer,
    code text NOT NULL UNIQUE,
    name text NOT NULL,
    type account_type NOT NULL,
    currency_code char(3) NOT NULL
);

CREATE TABLE journal_entries (
    id bigserial PRIMARY KEY,
    posted_at timestamptz DEFAULT now() NOT NULL,
    memo text,
    reversal_of bigint
);

CREATE TABLE postings (
    id bigserial PRIMARY KEY,
    entry_id bigint NOT NULL,
    account_id integer NOT NULL,
    amount_minor bigint NOT NULL,
    CHECK (amount_minor <> 0)
);
CREATE INDEX postings_account_idx ON postings (account_id);
CREATE INDEX postings_entry_idx ON postings (entry_id);

CREATE TABLE exchange_rates (
    base_code char(3) NOT NULL,
    quote_code char(3) NOT NULL,
    effective_on date NOT NULL,
    rate numeric(18,8) NOT NULL,
    PRIMARY KEY (base_code, quote_code, effective_on),
    CHECK (rate > 0)
);

ALTER TABLE accounts ADD FOREIGN KEY (parent_id) REFERENCES accounts (id);

ALTER TABLE accounts ADD FOREIGN KEY (currency_code) REFERENCES currencies (code);

ALTER TABLE journal_entries ADD FOREIGN KEY (reversal_of) REFERENCES journal_entries (id);

ALTER TABLE postings ADD FOREIGN KEY (entry_id) REFERENCES journal_entries (id) ON DELETE CASCADE;

ALTER TABLE postings ADD FOREIGN KEY (account_id) REFERENCES accounts (id);

ALTER TABLE exchange_rates ADD FOREIGN KEY (base_code) REFERENCES currencies (code);

ALTER TABLE exchange_rates ADD FOREIGN KEY (quote_code) REFERENCES currencies (code);