Building a subscription tracker

What database schema should a personal subscription tracker use?

A single subscriptions table carries most of it: name, amount, currency, billing_cycle as an enum, billing_every as an integer multiplier, next_billing_date, status, category_id, and timestamps. Three columns are commonly wrong — storing amounts as floats, storing an interval without a multiplier, and storing a converted amount instead of the original currency. Each fails only after the data matters.

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

Derive it, do not store it. Keep a charges table of what was actually billed — subscription_id, amount_cents, currency, charged_at, category_id — and aggregate on read. A stored monthly rollup goes stale the moment a user corrects a past entry, and correcting past entries is common in a tracker where every row is typed by hand.

How do you build a subscription tracker in a spreadsheet?

Eight columns: Name, Amount, Currency, Cycle, Every, Next Renewal, Status, Category. Derive monthly cost with a formula rather than typing it, and derive the next renewal with EDATE rather than maintaining it by hand. The limit is structural — a spreadsheet cannot notify you, so the renewal you needed warning about arrives unannounced.

How do you build reliable subscription renewal reminders?

Make the job idempotent through stored state, not through code discipline. Write last_sent_at per subscription per lead time, and check it before sending. Then handle three more failure modes: local-time evaluation for timezones, a resumable cursor for runs that exhaust their budget, and recording the delivery provider's response so a failed send is visible rather than assumed successful.

What CSV format should a subscription tracker import?

Eight columns: name, amount, currency, billing_cycle, billing_every, next_billing_date, status, category. Export and import must use the same set so a round-trip is lossless. The failure mode that matters is not a rejected file — it is a file that imports with four columns silently ignored and reports success.

What features does a subscription tracker actually need?

Nine: subscription CRUD with unit-plus-multiplier intervals, per-row currency, categories, status including trial, per-subscription reminder lead times, a price history table, spend aggregation, CSV import with undo, and a calendar feed. Three that look optional and are not: price history, import undo, and one-time payments. Everything else can wait.

How do you calculate the next billing date for a recurring subscription?

Store the original billing day separately from the next billing date and derive forward from it, rather than repeatedly adding an interval to the last computed date. Otherwise a subscription anchored on the 31st drifts permanently to the 28th after one February, and no library will warn you — they simply clamp and move on.

How should a subscription tracker store money in multiple currencies?

Store integer minor units with the currency code on the same row, keep a per-currency exponent table because JPY and KRW have zero decimal places, and convert only at read time with the rate date attached to the response. Never write a converted amount back — doing so makes last year's total change when today's rate moves.

How do you generate an ICS calendar feed for recurring subscriptions?

Map the interval to an RRULE, give each subscription a stable UID that never changes, and serve the feed from a per-user token URL. The constraint nobody expects: calendar clients refresh on their own schedule, often only every few hours to a day, so a live feed is not live and the product copy should not claim it is.

How do you make payment provider webhooks reliable?

Verify the signature, insert the event id into a processed-events table before doing anything, and read payload fields through a helper that tolerates more than one API version shape. The failure that cost us most was none of those: a handler that threw, caught its own error, and returned 200 — so the provider never retried.

Why does a Supabase query silently return only 1000 rows?

PostgREST applies a default maximum row count — commonly 1000 — to unbounded selects and returns success. Your query gets the first 1000 rows and no error. In a cron job this means the tail of the table is never processed, and because the ordering is stable it is always the same tail, so the same users stop being served.

How do you know whether a transactional email was actually delivered?

A 200 means the request was accepted, not that the message arrived. Record the provider message id and any error body against the send, surface failures in the interface rather than reporting success, and if you run a fallback provider, store which one delivered. Rate limiting is the case with no log entry at all on the sender side.

How do you model shared workspaces with row level security?

Put workspace_id on every user-owned row from the start, even if sharing ships much later, and create a personal workspace at signup so there is no null case. Policies check membership through a security-definer function rather than a subquery on the membership table, which is what causes the recursive policy errors people hit.

How should a subscription tracker API be designed?

Four resources — subscriptions, categories, reminders, events — with API keys rather than OAuth, since the caller is almost always the user automating their own account. Support an idempotency key on creates, paginate with an opaque cursor rather than an offset, and emit webhooks, because polling is what integrations do when you do not.

Should you build a subscription tracker in Notion or Airtable?

Airtable if you want the reminder to actually fire, because its scheduled automations can send email on a date condition. Notion if the list lives alongside your other notes and you will open it anyway. Both struggle with the same thing: recalculating the next renewal date after each cycle, which is a formula you have to maintain.