How should a subscription tracker model free trials?
Free trials as a status, not a flag: why trials stay out of spend totals, get their own reminder type and a partial index. From a production schema.
Answer
Model a trial as a subscription status rather than a boolean flag, with the conversion date stored as the next billing date. Exclude trials from spend totals by the same rule that excludes paused subscriptions, give trial-end warnings their own reminder type so they deduplicate separately from renewals, and index trials partially, because they are a small fraction of rows that users filter on constantly.
Leutrim Miftaraj
Founder, SubTracker · Updated September 29, 2026
The short answer
Model a trial as a subscription status rather than a boolean flag, with the conversion date stored as the next billing date. Exclude trials from spend totals by the same rule that excludes paused subscriptions, give trial-end warnings their own reminder type so they deduplicate separately from renewals, and index trials partially, because they are a small fraction of rows that users filter on constantly.
A Status, Not a Flag
The tempting design is an `is_trial` boolean on the subscription. It creates a second axis that every query has to remember: is this row active and a trial, paused and a trial, cancelled and a trial? Most of those combinations are meaningless.
SubTracker added `trial` as a value of the existing subscription status enum instead. A trial is a state the subscription is in; when it converts, the status changes to `active`. One column, one set of transitions, and nothing to keep in sync.
Keeping Trials Out of Spend Totals
A trial costs nothing yet, so it must not inflate the monthly total. The rule that achieves this already existed: only rows with status `active` count towards spend. Paused subscriptions were excluded by it before trials existed, and trials inherit the behaviour without a new branch in the cost calculation.
That matters because the generated cost columns switch on billing interval, not on status. Adding a status therefore could not introduce a missing case or a NULL into the maths — a property worth preserving deliberately, and one asserted in tests.
A Separate Reminder Type
Trial-end warnings are a different reminder type from renewal warnings. Two reasons. Users want to turn them on and off independently — many keep trial warnings on and renewal warnings off. And the reminders job deduplicates on user, subscription, reminder type and billing cycle; if trials shared the renewal type, a trial ending on the same date as a renewal reminder would collide with it and one of the two would be dropped.
A Partial Index
"Show me my trials" is the main reason the status exists, and trials are a small share of all rows. A partial index on workspace and next billing date, restricted to status `trial` and not deleted, serves that query without adding weight to every write on the main table.
Scope: Someone Else's Trial
The trial status describes a third-party trial — a streaming or software trial the user is tracking. It has nothing to do with the tracker's own pricing. Keeping that distinction explicit in the schema comments avoids a whole class of product confusion later.
Frequently asked questions
Why not use a boolean is_trial column?+
Because it adds a second state axis that every query must consider, producing meaningless combinations like a cancelled trial. A status value keeps one set of transitions.
How do you stop trials counting towards monthly spend?+
Count only subscriptions with status active. Paused subscriptions are excluded by the same rule, so trials need no special case in the cost calculation.
Why do trial reminders need their own type?+
So users can toggle them independently of renewal reminders, and so deduplication keyed on subscription, type and cycle never merges a trial warning with a renewal warning.
What happens when a trial converts?+
The status changes from trial to active, the next billing date becomes the first paid charge, and the subscription starts counting towards spend.