Personal Budget (PostgreSQL)

create table budget_category
(
  id   bigserial,
  name text not null,
  constraint pk_budget_category primary key (id)
);

create table financial_source
(
  id                    bigserial,
  budget_category_id    bigint not null,
  financial_source_type text   not null,
  name                  text   not null,
  constraint pk_financial_source    primary key (id),
  constraint uq_financial_source_1  unique (name),
  constraint chk_financial_source_1 check  (financial_source_type in ('CREDIT', 'DEBIT'))
);

create table adhoc_transaction
(
  id                  bigserial,
  financial_source_id bigint  not null,
  transaction_date    date    not null,
  description         text,
  credit              decimal,
  debit               decimal,
  constraint pk_adhoc_transaction    primary key (id),
  constraint fk_adhoc_transaction_1  foreign key (financial_source_id) references financial_source (id),
  constraint chk_adhoc_transaction_1 check       (credit is not null and debit is null or credit is null and debit is not null)
);

create table recurring_transaction
(
  id bigserial,
  financial_source_id bigint   not null,
  start_date          date     not null,
  end_date            date,
  description         text,
  frequency           interval not null,
  credit              decimal,
  debit               decimal,
  constraint pk_recurring_transaction    primary key (id),
  constraint fk_recurring_transaction_1  foreign key (financial_source_id) references financial_source (id),
  constraint chk_recurring_transaction_1 check       (credit is not null and debit is null or credit is null and debit is not null)
);

create function generate_budget(start_date date, end_date date, opening_balance decimal) returns table
(
  date        date,
  category    text,
  source      text,
  description text,
  credit      decimal,
  debit       decimal,
  balance     decimal
) as $$
  with a as (
    select generate_series(start_date, coalesce(end_date, $2), frequency)::date date,
           budget_category.name                                                 category,
           financial_source.name                                                source,
           description,
           credit,
           debit
    from recurring_transaction
    join financial_source on recurring_transaction.financial_source_id = financial_source.id
    join budget_category  on financial_source.budget_category_id       = budget_category.id
  ), b as (
    select transaction_date      date,
           budget_category.name  category,
           financial_source.name source,
           description,
           credit,
           debit
    from adhoc_transaction
    join financial_source on adhoc_transaction.financial_source_id = financial_source.id
    join budget_category  on financial_source.budget_category_id   = budget_category.id
  ), c as (
    select * from a
    union all
    select * from b
    order by date
  )
  select *, coalesce($3, 0) + sum(coalesce(credit, 0) - coalesce(debit, 0)) over (order by date, source) balance
  from c
  where date between $1 and $2
$$ language sql;

create function generate_budget(start_date date, end_date date) returns table
(
  date        date,
  category    text,
  source      text,
  description text,
  credit      decimal,
  debit       decimal,
  balance     decimal
) as $$
  select * from generate_budget($1, $2, 0)
$$ language sql;

create function generate_budget_category_summary(start_date date, end_date date, opening_balance decimal) returns table
(
  category text,
  credit   decimal,
  debit    decimal
) as $$
  select category, sum(credit), sum(debit)
  from   generate_budget($1, $2, $3)
  group by category
  order by greatest(sum(credit), sum(debit)) desc, category
$$ language sql;

create function generate_budget_category_summary(start_date date, end_date date) returns table
(
  category text,
  credit   decimal,
  debit    decimal
) as $$
  select * from generate_budget_category_summary($1, $2, 0)
$$ language sql;

create function generate_budget_source_summary(start_date date, end_date date, opening_balance decimal) returns table
(
  category text,
  source   text,
  credit   decimal,
  debit    decimal
) as $$
  select category, source, sum(credit), sum(debit)
  from   generate_budget($1, $2, $3)
  group by category, source
  order by greatest(sum(credit), sum(debit)) desc, category, source
$$ language sql;

create function generate_budget_source_summary(start_date date, end_date date) returns table
(
  category text,
  source   text,
  credit   decimal,
  debit    decimal
) as $$
  select * from generate_budget_source_summary($1, $2, 0)
$$ language sql;

create function generate_budget_report(start_date date, end_date date, opening_balance decimal) returns table
(
  credits decimal,
  debits  decimal,
  surplus decimal,
  deficit decimal
) as $$
  with a as (
    select sum(credit) + coalesce(opening_balance, 0) credits, sum(debit) debits
    from   generate_budget($1, $2, $3)
  )
  select credits,
         debits,
         case when credits > debits then credits - debits else 0 end,
         case when debits > credits then debits - credits else 0 end
  from   a
$$ language sql;

create function generate_budget_report(start_date date, end_date date) returns table
(
  credits decimal,
  debits  decimal,
  surplus decimal,
  deficit decimal
) as $$
  select * from generate_budget_report($1, $2, 0)
$$ language sql;