Files

28 lines
1.7 KiB
PL/PgSQL
Raw Permalink Normal View History

2026-11-22 18:02:23 +01:00
-- Months that start on payday (profile.month = "payday", src/lib/paymonths.ts): one row per month with the day it starts.
-- The server writes them (lib/data.ts payPeriods) whenever they change; with no rows, months are calendar months.
create table if not exists public.pay_periods (
user_id uuid not null references auth.users on delete cascade,
month date not null, -- the calendar month it's named after, first day
start_date date not null,
primary key (user_id, month)
);
create index if not exists pay_periods_start on public.pay_periods (user_id, start_date);
alter table public.pay_periods enable row level security;
drop policy if exists "own" on public.pay_periods;
create policy "own" on public.pay_periods for select to authenticated using (user_id = (select auth.uid()));
grant select on public.pay_periods to authenticated;
-- By month and category, the month being the pay month a day falls in (the latest start on or before it), else the
-- calendar month. n counts every own row in the group, counted or not (pages use it for "months of data").
create or replace function public.money_by_month(start_date date, end_date date default null)
returns table (month date, category_id uuid, spend numeric, income numeric, n bigint)
language sql stable set search_path = '' as $$
with x as (
select coalesce(
(select p.month from public.pay_periods p where p.user_id = (select auth.uid()) and p.start_date <= r.date order by p.start_date desc limit 1),
date_trunc('month', r.date)::date) as month,
r.category_id, r.spend, r.income
from public.money_rows(start_date, end_date) r
)
select x.month, x.category_id, sum(x.spend), sum(x.income), count(*) from x group by 1, 2
$$;