Database
Convex tables, typed end-to-end
Overview
Eighteen tables defined in `convex/schema.ts`: users, missions, checkpoints, tasks, friends, activity, reactions, notifications, userVault, clubs, clubMemberships, missionSupporters, feedback, mineralLedger — plus a few auxiliary tables for synthetic agents and insights. Every table has explicit indexes on the access patterns we use, and searchIndexes where we need full-text.
How it works
Schemas are defined once in `schema.ts`. Convex's TypeScript codegen produces `Doc<'tasks'>` and similar types that propagate through the entire app — including the client React components.
Queries return reactive results — a `useQuery(api.missions.list)` call subscribes to the server, and whenever any of the rows the query touched gets written, the client re-renders. No manual refetch wiring.
Mutations are atomic. A goal completion mutation that grants Olivium, writes an activity row, and creates a notification all commits or fails together.
Indexes are named explicitly (`by_user_status`, `by_mission_order`) and used by reading them in queries via `q.withIndex('by_user_status', q => q.eq('userId', userId).eq('status', 'in-progress'))`.
Schema migrations: we add fields as optional first, backfill via idempotent mount-time mutations (see the missions tab's `backfillScheduledDateISOForUser`), and then promote to required in a later release.
Key decisions
Convex over Supabase / Firebase
Convex's reactivity model fits the app's shape — every screen is a few queries and the UI just listens. We get TypeScript end-to-end (schema → query result → React prop) without writing zero codegen plumbing. The trade-off is vendor specificity, but the developer velocity wins.
Indexes named for access, not data shape
Indexes are named after how they're queried, not what they index. `by_user_status` is a compound index on (userId, status) — but its name reflects 'I want this user's tasks in this status'. Reading the query code feels like reading English.
Idempotent mount-time migrations
Instead of versioned migration scripts, structural backfills (like `scheduledDateISO` for tasks) are written as idempotent mutations the client fires on the relevant screen's mount. The data heals as users use the app, and the migration is collocated with the code that needs the new shape.