Postgresql basic accounting

accounting.sql

create table public.account
(
    id               bigint                                 not null primary key generated always as identity,
    created_at       timestamp with time zone default now() not null,
    display_name     text,
    picture          text,
);

create table currency
(
    id       text not null primary key,
    template text not null
);

create table public.accounting_entry
(
    id          bigint                                 not null primary key generated always as identity,
    created_at  timestamp with time zone default now() not null,
    account_id  bigint                                 not null
        references account
            on update cascade on delete cascade,
    currency_id text                                   not null
        references currency
            on update cascade on delete restrict,
    amount      numeric                                not null
        constraint not_zero_amount check ( amount <> 0 )
);

create table public.account_balance
(
    account_id  bigint  not null
        references account
            on update cascade on delete cascade,
    currency_id text    not null
        references currency
            on update cascade on delete restrict,
    amount      numeric not null check ( amount >= 0 ),
    primary key (account_id, currency_id)
);

create or replace function public.accounting_entry_process() returns trigger as
$$
declare
    ae public.accounting_entry%ROWTYPE;
begin
    ae := NEW;
    insert into public.account_balance (account_id, currency_id, amount)
    values (ae.account_id, ae.currency_id, ae.amount)
    on conflict (account_id, currency_id) do update set amount = EXCLUDED.amount + ae.amount;
    return ae;
end;
$$
    language plpgsql;

create trigger process_insert
    before insert
    on public.accounting_entry
    for each row
execute procedure public.accounting_entry_process();

create view public.account_balance_view as
select a.id as account_id, c.id as currency_id, coalesce(ab.amount, 0) as amount
from public.account as a
         cross join public.currency c
         left join account_balance ab on a.id = ab.account_id and c.id = ab.currency_id;