Mood Tracker

A document store, because the schema is the user's choice

The form is user-defined, so the shape of an entry isn't known until runtime and can't be expressed as typed columns. Everything lives in one SQLite table used as a document store, with real columns only for what has to be queried.

CREATE TABLE docs (
  collection TEXT NOT NULL,   -- 'form' or 'entry'
  id         TEXT NOT NULL,
  created_at TEXT NOT NULL,   -- ISO-8601 UTC
  updated_at TEXT NOT NULL,
  json       TEXT NOT NULL,   -- the document itself
  PRIMARY KEY (collection, id)
);
CREATE INDEX idx_docs_created ON docs (collection, created_at DESC);

A FormField is a discriminated union on kind, so adding a sixth kind someday is additive, not a migration:

Field ids are permanent and answers are stored against the id, never the label — renaming a field updates it everywhere, including entries saved years earlier. Deleting a field archives it by default; erasing the data it recorded is a second, explicitly confirmed action, because without a field's definition a stored value can't be interpreted at all.

Migrating a journal someone already relies on

An earlier build stored tracked fields as fixed columns on a logs table. Moving existing users onto the document model meant migrating real, already-written entries without a single one going missing or duplicating — on a background job that has to survive the app being killed mid-run.

Two properties made that safe: deterministic ids (row 7 of the old table always becomes entry legacy-7, so re-running the migration replaces rather than duplicates), and verify before stamping (the "already migrated" marker is only written once the count of migrated documents matches the count of source rows — an interrupted run just resumes on the next launch instead of silently half-finishing).

The old logs table is deliberately left in place afterward rather than dropped — it costs almost nothing, and it's the only fallback copy if a mapping bug ever surfaces. Erasing everything from Privacy & data removes it too.

Reminders: working around a silent iOS ceiling

iOS caps pending local notifications at 64 per app and drops the rest with no error and no callback — a repeating daily trigger would never hit the cap but couldn't vary its wording day to day, and wording that never changes is exactly what gets swiped away unread after a week.

The trade-off is explicit: if the app goes unopened for twenty days, the queue empties and reminders stop until it's reopened. Rotating, time-of-day-aware copy was worth that cost. Every scheduled notification carries the times it was built from in its own payload, so a rebuild is skipped only when the queue is both full and still matches the configured times — meaning repeated launches do no redundant work, but re-timing a reminder always takes effect immediately.

Import, export, and the problem of merging a backup

With no account and no server, a lost phone is a lost journal, so backup isn't a nice-to-have — it's the only thing standing between a user and losing years of entries. Export writes a versioned, human-readable JSON file to the cache directory and hands it to the OS share sheet; the app never learns or controls where it ends up next.

Import offers two modes: replace everything, the real-restore path, and add missing entries, which keeps the current form and only appends entries whose ids aren't already present. If the backup references a field the current form doesn't have, it's appended archived rather than dropped — the imported answers stay readable in History instead of silently vanishing because their field no longer exists.

The form is the product; the app is just a renderer.

Once fields became data instead of hardcoded screens, the hardest product question, "what should people track?", stopped being a decision I had to get right for every user. Mood tracking needs differ wildly between people; a user-defined form sidesteps the need to guess.

A document store beats a normalized schema when the shape is the user's choice.

I don't control what fields exist, so I can't design columns for them. One JSON-payload table with a couple of queryable columns gave me arbitrary structure without a single migration for anything the user configures — only genuinely new storage needs (a new collection, a new index) ever touch the schema.

Untrusted input isn't just the network — it's your own old code.

Every document on disk came from some previous version of this exact app, or from a backup file. Writing the normalizer to assume disagreement, rather than assume its own shape, is what let later changes (new field kinds, renamed options) ship without a forced migration step.

Notification limits are a silent failure mode, not a documented error.

iOS caps pending local notifications at 64 and just drops the rest — no error, no callback. The fix wasn't cleverness, it was arithmetic: schedule enough dated notifications to cover a real runway (60, ~20 days at 3/day) and refill on every launch rather than trying to maintain an indefinite repeating schedule that can't vary its wording.

Privacy as architecture beats privacy as policy.

The meaningful privacy claim isn't a sentence in a privacy policy — it's that the app has no server to send data to in the first place. No account and no analytics weren't a feature cut for later; they were the actual product decision that made people comfortable logging sensitive entries honestly.

Deletion needs two verbs, not one.

"Remove this field" and "delete this data" feel like the same user intent and are not. Archiving solves the first without foreclosing the second, and keeping them as separate, explicit actions means an accidental tap never destroys years of entries.

The native release pipeline is its own project.

Certificates, provisioning profiles, build-number bumps, DerivedData cache invalidation, and where EAS's remote version source disagrees with a local app.json are all failure points that have nothing to do with the app's code and everything to do with shipping it. None of that complexity is optional once the target is an actual device in someone's pocket.