What database schema should a personal subscription tracker use?

The Core Table

Start here and add nothing until something forces you to.

create table subscriptions (
  id              uuid primary key default gen_random_uuid(),
  user_id         uuid not null references auth.users(id) on delete cascade,

  name            text not null,
  amount_cents    bigint not null check (amount_cents >= 0),
  currency        char(3) not null,

  billing_cycle   text not null
                    check (billing_cycle in ('day','week','month','year','once')),
  billing_every   smallint not null default 1
                    check (billing_every between 1 and 60),

  next_billing_date date,
  started_at        date,
  status          text not null default 'active'
                    check (status in ('active','paused','cancelled','trial')),

  category_id     uuid references categories(id) on delete set null,
  notes           text,

  created_at      timestamptz not null default now(),
  updated_at      timestamptz not null default now()
);

create index on subscriptions (user_id, status);
create index on subscriptions (user_id, next_billing_date)
  where status = 'active';

That is enough to build a working tracker. Everything below is an explanation of why each column is the shape it is, and three of those explanations are the reason this page exists.

Amount: Integer Minor Units, Not a Float

`amount_cents bigint`, not `amount numeric` and definitely not `amount float`.

Floating point cannot represent 0.1 exactly. Sum a hundred monthly amounts and the total is off by a fraction of a cent, which is invisible until a user compares your reported annual figure to their own arithmetic and finds a discrepancy they cannot explain. They will not report it as a rounding artefact; they will report it as the app being wrong, and they are right.

`numeric` is correct and slower, and it invites a second problem: developers write `amount * 12` somewhere and the type quietly promotes. Integers of minor units make the unit explicit at every call site — you cannot accidentally treat 1290 as dollars.

The wrinkle is that not every currency has two decimal places. JPY has none, so ¥1,290 is 1290 minor units, not 129000. Store the exponent per currency and format at the edge:

const EXPONENT: Record<string, number> = { JPY: 0, KRW: 0 } // default 2
export function format(cents: number, currency: string) {
  const e = EXPONENT[currency] ?? 2
  return new Intl.NumberFormat(undefined, { style: 'currency', currency })
    .format(cents / 10 ** e)
}

Getting this wrong makes every Japanese amount a hundred times too large, which is the kind of bug that reaches production because nobody on the team has a JPY subscription.

Billing Interval: A Unit Plus a Multiplier

This is the column people get wrong, and it is the one that is genuinely painful to change later.

The instinct is an enum: `monthly | quarterly | yearly`. It works for about a month. Then a user has a subscription billed every two months, or every 18 months, or a domain renewed every three years, and the enum has no slot for it. The workarounds are all bad: adding `bimonthly` and `biennial` as enum values pushes the problem out by one iteration, and storing "every 2 months" as monthly-with-a-note breaks every projection you have written.

Unit plus multiplier handles all of it with two columns:

The advance function becomes one expression instead of a switch:

next_billing_date + (billing_every || ' ' || billing_cycle)::interval

We shipped the enum first and migrated to the multiplier later. The migration touched the cost maths, the calendar projection, the cron advancement loop, the ICS RRULE generator, the subscription form, the display labels, the CSV import and export, and the public API. Every one of those places had encoded the assumption that an interval was a single token. It is two columns; add them on day one.

Normalised monthly cost is worth generating rather than computing in application code, because it will otherwise be computed slightly differently in four places:

alter table subscriptions add column monthly_cents bigint
  generated always as (
    case billing_cycle
      when 'day'   then amount_cents * 30 / billing_every
      when 'week'  then amount_cents * 52 / (12 * billing_every)
      when 'month' then amount_cents / billing_every
      when 'year'  then amount_cents / (12 * billing_every)
      else 0
    end
  ) stored;

Note `once` normalising to zero. A one-time payment is not recurring spend, and including it in a monthly figure produces a number the user cannot reconcile with anything.

Currency: Per Row, Never Converted at Entry

Store the currency the user is actually charged, on every row.

The tempting design is a single currency on the user profile with amounts converted on entry. It fails on first contact with a real list: someone in Switzerland pays CHF for local services, USD for American SaaS and EUR for a German newspaper. Converting at entry destroys the original figure, so when the exchange rate moves your stored amount is wrong and there is nothing to recompute from.

Convert at display time, with the rate and its date attached to the presentation rather than to the row. Users understand "≈ $71 at today's rate"; they do not understand a stored figure that changed on its own.

Do not add an `exchange_rate` column to the subscriptions table. The rate is a property of a moment, not of a subscription, and putting it here means either updating every row on a schedule or serving stale figures.

Status, Trials and the Soft-Delete Question

`status` carries more weight than it looks like it does, because it determines what every query means.

`active` — counts towards spend, gets reminders. `paused` — user intends to resume; excluded from spend, no reminders. Gyms and meal kits make this real rather than theoretical. `cancelled` — kept for history. Do not delete. Users want to see what they cancelled and what it was costing, and that record is one of the few places a tracker demonstrates its own value. `trial` — converts to active on a known date, and needs a reminder before it does.

Trials deserve a note. Modelling them as a separate boolean plus a `trial_ends_at` column is workable, but treating trial as a status with `next_billing_date` set to the conversion date means the reminder machinery works unchanged, which is worth more than the semantic tidiness.

Do not hard-delete. Add `deleted_at timestamptz` and filter it out. Users delete a subscription and then want it back, and the alternative is a support conversation you cannot resolve.

Partial indexes matter here. The reminder job asks one question: which active subscriptions renew in the next N days. An index on `(user_id, next_billing_date) where status = 'active'` answers it without reading the cancelled history, which grows without bound.

The Tables You Add Second

categories — `id, user_id, name, colour`. A text column on the subscription works and stops working the first time someone types "Streaming" and "streaming". A table with a foreign key is barely more code.

price_history — `subscription_id, amount_cents, currency, changed_at`. Write a row whenever the amount changes. This is what makes "your Netflix went up" possible, and it cannot be reconstructed later because the old figure is gone the moment it is overwritten.

reminders — `subscription_id, days_before, channel, last_sent_at`. Per-subscription lead times, not one global setting. Seven days for monthly and thirty for annual are different requirements, and `last_sent_at` is what makes the job idempotent when the cron retries.

workspaces / workspace_members — only if the household case matters to you. Retrofitting shared ownership onto a schema keyed on `user_id` means touching every query and every policy. If it is on the roadmap at all, put `workspace_id` on the subscription now and default it to a personal workspace created at signup.

Three Things That Bit Us in Production

Not hypotheticals.

The client library truncated results silently. PostgREST returns a maximum of 1,000 rows by default and reports success. A query over a large table returned 1,000 rows, the code summed them, and the total was wrong with no error anywhere. Any unbounded select needs explicit pagination, and the guard belongs in a shared helper rather than in each call site.

A payment provider moved a field between API versions. Stripe's 2025-03-31 release relocated `current_period_end` off the Subscription object. Our webhook threw a RangeError constructing a date from undefined, the handler returned 200 anyway, and plan updates silently stopped applying. Version-pin the API and read period fields through one helper that tolerates both shapes.

Email failures returned success. The transactional provider accepted the request, failed to deliver, and the application reported success to the user because it only checked the HTTP status. If a send matters, surface the failure to the UI and log the provider's own response.

The pattern in all three is the same and it is the thing worth taking from this page: the dangerous failures are the ones that return success while doing nothing. Design the schema so that "did this actually happen" is answerable from stored state — `last_sent_at`, a delivery outcome column, a row count — rather than inferred from the absence of an exception.

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.