How do you model subscription spending history by category and month?

Store Charges, Derive Rollups

Two designs present themselves, and the wrong one is the one that looks more efficient.

Stored rollup. A `monthly_spend` table with user, month, category and total, written by a job. Reads are trivial. The problem is that a manual tracker's history is not append-only: a user realises they entered the wrong amount in March, or recategorises six subscriptions, or marks something as having been cancelled two months earlier than they first said. Every one of those invalidates a stored rollup, and the reconciliation logic to fix it is more code than the aggregation you were avoiding.

Derived from charges. A `charges` table records what was actually billed. Rollups are a query. Edits to history are correct immediately with no invalidation step.

At personal-tracker scale — hundreds of rows per user, not millions — the aggregation is not a performance concern. Derive it. Revisit only if a query plan tells you to.

create table charges (
  id              uuid primary key default gen_random_uuid(),
  subscription_id uuid not null references subscriptions(id) on delete cascade,
  user_id         uuid not null,
  amount_cents    bigint not null,
  currency        char(3) not null,
  category_id     uuid,
  charged_at      date not null,
  source          text not null default 'projected'
                    check (source in ('projected','confirmed','imported')),
  created_at      timestamptz not null default now()
);

create index on charges (user_id, charged_at);
create index on charges (user_id, category_id, charged_at);

The `source` column is the one people leave out and then want. A charge the system projected from a renewal date is a different kind of fact from one the user confirmed happened, and a report that mixes them without distinction will eventually be challenged by a user comparing it against a statement.

Denormalise category_id onto the charge. It looks redundant against the subscription's own category. It is not: if the user recategorises a subscription today, last March's spend should stay in the category it was in at the time. Reading the category through the subscription silently rewrites history.

The Aggregation Query

Spend by category by month, with months that had no spend still present — which matters, because a chart with missing months implies a gap rather than a zero.

with months as (
  select generate_series(
    date_trunc('month', now()) - interval '11 months',
    date_trunc('month', now()),
    interval '1 month'
  )::date as month
)
select
  m.month,
  c.name                              as category,
  coalesce(sum(ch.amount_cents), 0)   as total_cents
from months m
cross join categories c
left join charges ch
  on  date_trunc('month', ch.charged_at)::date = m.month
  and ch.category_id = c.id
  and ch.user_id     = $1
where c.user_id = $1
group by m.month, c.name
order by m.month, total_cents desc;

The cross join against categories is what produces the zero rows. Without it, a category with no spend in April simply vanishes from April, and the client has to reconstruct the grid — which it will do slightly differently from the next client you write.

For a single total per month, drop the category join. For a rolling twelve-month figure per category, wrap it in a window function rather than issuing twelve queries.

Normalising Annual Plans

The question every spend report has to answer: does a $120 annual subscription appear as $120 in January, or as $10 in every month?

Both are correct answers to different questions, and a report that does not say which it is using is not trustworthy.

Cash view — $120 in January. What actually left the account. Matches the bank statement, which is what a user reconciling against reality needs.

Normalised view — $10 per month. What the subscription costs to hold. Matches the intuition of "what am I spending on subscriptions", and makes an annual plan comparable to a monthly one.

Support both, label them, and default to normalised for the headline figure and cash for the history chart. The generated `monthly_cents` column from the schema page gives you the normalised figure for free; the charges table gives you cash.

Where this goes wrong in practice: a report that silently uses cash produces a January spike that users read as a bug, and one that silently uses normalised produces a total that does not match their statement. Both generate the same support message — "your numbers are wrong" — and both are avoided by a one-line label on the chart.

Multiple Currencies in One Total

A user with subscriptions in USD, EUR and CHF wants one number. Producing it requires a rate, and a rate has a date.

Store the amount in its original currency on the charge. Always. This is the fact.

Convert at read time, and record which rate was used. Either attach a `rate_used` and `rate_date` to the response, or accept that the historical total changes as rates move and say so in the UI.

Do not backfill converted amounts into the charges table. The moment you do, last year's total starts changing when this year's rate moves, and no user will accept that as correct behaviour even though it is arguably more accurate.

The pragmatic default: convert everything to the user's display currency at the current rate, label the figure as approximate, and offer a per-currency breakdown for anyone who cares. Most people have one dominant currency and a couple of outliers, so the approximation is small and the breakdown answers the objection.

Generating the Charges

Where the rows come from, in a manual tracker with no transaction feed.

Projection. For each active subscription, walk `next_billing_date` forward and write a projected charge for each occurrence. Run it on a schedule, and make it idempotent — a unique constraint on `(subscription_id, charged_at)` is the cheapest way to guarantee a retried job does not double-write.

Backfill on creation. When a user adds a subscription with a `started_at` in the past, generate the charges between then and now. This is what makes a report useful on day one instead of in three months, and it is a small piece of code with a disproportionate effect on whether the product feels worth using.

Confirmation. Optionally let the user mark a projected charge as confirmed, or correct its amount. That is where the `source` column earns its place, and where a price change gets detected without any bank access at all.

Keep the projection horizon short — a few months forward is plenty. Generating five years of projected charges makes the table large, the aggregations slower, and the numbers wrong the moment the subscription is cancelled.

The Honest Build-Versus-Buy Line

The schema is the easy part. It is also the part that feels like progress, which is why people stop enjoying the project immediately after finishing it.

What actually takes the time, in the order it bites:

Scheduled reminders. Not the sending — a cron job and an SMTP call is an afternoon. The problems are idempotency (a retried run must not email twice), timezone handling (a reminder "7 days before" for a user in Auckland is not the same instant as for one in Lisbon), and the failure mode where the job dies halfway through the list and the second half silently never runs. Every one of these has bitten us in production.

Recurrence maths. "Monthly" is not a fixed interval. The 31st of January plus one month is a decision, not a calculation, and every library makes a different one. Add quarterly, annual, every-two-months and every-18-months and you have a small state machine.

Currency. Storing an amount without its currency works until the first user has two. Converting at entry time is the intuitive fix and it is wrong — the stored figure drifts as rates move, and the user's original agreement is lost.

Nothing to import from. Manual entry of twenty subscriptions is genuinely tedious. Building CSV import means writing a column mapper, and a column mapper that silently mishandles a column is worse than no import at all.

None of that is hard in the sense of being intellectually difficult. It is hard in the sense of being four weekends, and the fourth weekend is the one where the project dies.

If the goal is to learn, build it — the schema below is a real one and it works. If the goal is to stop losing money to forgotten subscriptions, the build is a detour.

What SubTracker Does With This

For context, since the decisions above come from a running system rather than from a design exercise.

SubTracker is a Next.js and PostgreSQL application (Supabase) that tracks subscriptions manually — there is no bank connection, so every row originates from a form or a CSV import. That constraint shapes the schema: there is no "detected" state, no confidence score, no merchant-matching table. Every row is an assertion the user made.

The free plan tracks unlimited subscriptions and includes CSV import and a live calendar feed. Reminders and price-hike alerts are Plus, at $5.90/month. Family is $9.90/month and adds a shared workspace for up to ten people, which is where the ownership columns below stop being theoretical.