Postgresql basic accounting

accounting.sql

CREATE SCHEMA IF NOT EXISTS wallet;

CREATE TABLE wallet.segment
(
    id   smallint NOT NULL,
    code text     NOT NULL,
    PRIMARY KEY (id) INCLUDE (code),
    UNIQUE (code) INCLUDE (id)
);

INSERT INTO wallet.segment (id, code)
VALUES (1, 'main'),
       (2, 'affiliate');

CREATE TABLE wallet.currency
(
    id   smallint NOT NULL,
    code text     NOT NULL,
    PRIMARY KEY (id) INCLUDE (code),
    UNIQUE (code) INCLUDE (id)
);

INSERT INTO wallet.currency (id, code)
VALUES (1, 'BTC'),
       (2, 'USDT');

CREATE TABLE wallet.category
(
    id          smallint GENERATED BY DEFAULT AS IDENTITY NOT NULL,
    origin      text                                      NOT NULL,
    type        text                                      NOT NULL,
    description text,
    PRIMARY KEY (id) INCLUDE (origin, type),
    UNIQUE (origin, type)
);

CREATE TABLE wallet.tx
(
    id          uuid        DEFAULT gen_random_uuid_v7() PRIMARY KEY,
    account_id  uuid REFERENCES public.account (id)      NOT NULL,
    currency_id smallint REFERENCES wallet.currency (code) NOT NULL,
    category_id smallint REFERENCES wallet.category (id) NOT NULL,
    segment_id  smallint REFERENCES wallet.segment (id)  NOT NULL,
    amount      numeric(36, 18) CHECK ( amount <> 0 )    NOT NULL,
    created_at  timestamptz DEFAULT NOW()                NOT NULL
);

CREATE TABLE wallet.account
(
    created_at  timestamptz     NOT NULL DEFAULT NOW(),
    account_id  uuid            NOT NULL REFERENCES public.account (id),
    segment_id  smallint        NOT NULL REFERENCES wallet.segment (id),
    currency_id smallint        NOT NULL REFERENCES wallet.currency (code),
    balance     numeric(36, 18) NOT NULL,
    CONSTRAINT wallet_pk PRIMARY KEY (account_id, segment_id, currency_id)
);

CREATE VIEW wallet.account$ AS
SELECT a.id                               AS account_id,
       c.code                             AS currency,
       s.code                             AS segment,
       COALESCE(wa.balance, (0)::numeric) AS balance
FROM ((public.account a
    CROSS JOIN wallet.currency c
    CROSS JOIN wallet.segment s)
    INNER JOIN wallet.account wa ON (((a.id = wa.account_id) AND (c.id = wa.currency_id))));


CREATE OR REPLACE FUNCTION wallet.provide_tx(
    p_account_id uuid,
    p_currency text,
    p_amount numeric,
    p_origin text,
    p_type text,
    p_allow_overdraft boolean DEFAULT FALSE,
    p_segment text DEFAULT 'main',
    OUT id uuid,
    OUT balance numeric,
    OUT amount numeric,
    OUT currency text,
    OUT segment text,
    OUT currency_id smallint,
    OUT segment_id smallint,
    OUT category_id smallint
)
    LANGUAGE plpgsql
AS
$$
BEGIN
    SELECT c.id, c.code INTO currency_id, currency FROM wallet.currency c WHERE c.code = p_currency;
    IF NOT FOUND THEN
        RAISE EXCEPTION 'Currency "%" does not exist', p_currency
            USING DETAIL = 'WALLET_PROVIDE_TX_UNKNOWN_CURRENCY';
    END IF;

    SELECT s.id INTO segment_id FROM wallet.segment s WHERE s.code = p_segment;
    IF NOT FOUND THEN
        RAISE EXCEPTION 'Segment "%" does not exist', p_segment
            USING DETAIL = 'WALLET_PROVIDE_TX_UNKNOWN_SEGMENT';
    END IF;

    SELECT c.id INTO category_id FROM wallet.category c WHERE origin = p_origin AND type = p_type;
    IF NOT FOUND THEN
        INSERT INTO wallet.category (origin, type)
        VALUES (p_origin, p_type)
        RETURNING category.id INTO category_id;
    END IF;


    INSERT INTO wallet.account(account_id, currency_id, segment_id, balance)
    VALUES (p_account_id, currency_id, segment_id, p_amount)
    ON CONFLICT (account_id, segment_id, currency_id) DO UPDATE SET balance = account.balance + p_amount
    RETURNING account.balance INTO balance;

    IF balance < 0 AND p_amount < 0 AND NOT p_allow_overdraft THEN
        RAISE EXCEPTION 'Insufficient funds: balance is % after transaction of % %', balance, p_amount, p_currency
            USING DETAIL = 'WALLET_PROVIDE_TX_INSUFFICIENT_BALANCE';
    END IF;

    INSERT INTO wallet.tx (account_id, segment_id, currency_id, category_id, amount)
    VALUES (p_account_id, segment_id, currency_id, category_id, p_amount)
    RETURNING tx.id, tx.amount INTO id, amount;
END;
$$;