What CSV format should a subscription tracker import?

The Column Set

name,amount,currency,billing_cycle,billing_every,next_billing_date,status,category
Netflix,15.49,USD,month,1,2026-10-04,active,Streaming
Adobe CC,59.99,USD,year,1,2027-01-18,active,Software
Gym,89.00,CHF,month,3,2026-11-01,active,Fitness
Domain,14.00,USD,year,2,2028-03-09,active,Software

Eight columns, and each one earns its place.

`billing_every` separate from `billing_cycle`. Row three is billed quarterly and row four every two years. A format with a single `frequency` column cannot express either without inventing tokens, and inventing tokens means every importer has to know your vocabulary.

Dates as ISO 8601. `2026-10-04`. Nothing else. `04/10/2026` is ambiguous between two large markets and the ambiguity is undetectable — both readings produce a valid date, so there is no error to raise, just a wrong one silently accepted.

Amount as a plain decimal, no symbol, no thousands separator. Currency lives in its own column. `$1,299.00` in an amount field is three parsing decisions you do not want to make.

Lowercase enum values. `month`, not `Monthly`. Case-normalise on read anyway, because exports from elsewhere will not comply.

Mapping Foreign Headers

Most imports come from somewhere else, and nobody's export matches yours. Map by synonym, then show the mapping and stop.

const SYNONYMS: Record<string, string[]> = {
  name:              ['name','service','provider','subscription','merchant','description'],
  amount:            ['amount','cost','price','charge','value','monthly cost'],
  currency:          ['currency','ccy','cur'],
  billing_cycle:     ['billing_cycle','cycle','frequency','interval','period','recurrence'],
  billing_every:     ['billing_every','every','multiplier','interval_count'],
  next_billing_date: ['next_billing_date','next payment','renewal','renews','due date','next charge'],
  status:            ['status','state','active'],
  category:          ['category','type','group','tag'],
}

Then — and this is the part that gets skipped — render the mapping and require confirmation before writing anything. Column A → name, column D → amount, column F unmapped. A user glancing at that catches a wrong guess in two seconds. An import that proceeds on a confident-but-wrong guess produces a database full of plausible garbage.

Report unmapped columns explicitly. "4 of 6 columns imported" is a useful sentence. Importing four and saying "success" is the failure this whole page is about: we shipped exactly that, and four of six columns were silently dropped while the interface reported a clean import. Nobody could have noticed from the outside, because the rows that did import looked correct.

Parsing the Awkward Values

Frequency text. Normalise into the two-column form:

Anything unmatched goes to the review screen. Do not default it to monthly — a guessed interval produces wrong dates and wrong totals, and the user has no way to know which rows were guessed.

Dates. Accept ISO without argument. For anything else, detect the format across the whole column rather than row by row: if any value has a first component above 12, the column is unambiguous and the rest follows. If the column is genuinely ambiguous — every value has both components at 12 or below — ask. One question is much cheaper than a silently transposed date set.

Amounts. Strip currency symbols and spaces. Handle both decimal conventions: `1.299,00` and `1,299.00` are the same number, and the rule that disambiguates them is which separator appears last.

Currency. If absent, do not assume USD — ask once and apply to the file. A defaulted currency is invisible and wrong.

The Round-Trip Test

The single test that catches most import bugs, and the one most often missing:

Export a known set. Import the export into an empty account. Compare field by field.

Not row counts — field by field. Our CSV import passed every column-level test it had and still failed this, because the export wrote a header the import did not recognise. Both halves were individually correct and the pair was broken, which is exactly the class of defect that unit tests on each half cannot see.

Make it a permanent test case rather than a one-off check. It is the only test that asserts the two halves agree, and they drift apart every time either is touched.

Undo, and Why It Is Not Optional

An import writes many rows at once. When it goes wrong — wrong column mapping, wrong file, duplicate of a previous import — the user faces deleting forty rows by hand, and that is the point at which they stop trusting the feature.

Stamp every row with a batch id:

alter table subscriptions add column import_batch_id uuid;
create index on subscriptions (user_id, import_batch_id);

Then undo is one delete, and an import history screen showing batch, timestamp, row count and an undo button turns a frightening operation into a reversible one. That change in perception is worth more than the code costs: people import willingly when they know they can undo.

Detect probable duplicates at preview time as well — same name and same amount as an existing active row — and let the user choose to skip or add. Importing the same file twice is common and produces a list nobody wants to clean up by hand.

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.