ADR-0016 · Reminders domain model

Status: 🟢 Accepted — adopted 2026-07-27 · Depends on: ADR-0007 (quota is entitlement-driven), ADR-0009 (reminders are cross-archetype), ADR-0012 (schemas → domain → data), ADR-0014 (TO authenticated on every policy) · Related: QRS-209, QRS-210, QRS-211

The one-line thesis

Store the recurrence rule, compute occurrences on read, and persist an occurrence row only when its state diverges from the rule. The legacy tables are archived, not dropped — because the pre-flight that was supposed to confirm they were empty found 11 rows on Prod.

Context

Reminders is one of the features the design project itself identifies as "largely identical across all business domains" — it is framework, not vertical. A schema for it already existed in the baseline squash (20260710134136_baseline_schema_from_prod.sql), inherited verbatim from production and never designed against the standards program.

The first draft of the implementation plan chose to adopt that schema, on the reasoning that a table which already exists is cheaper than a migration. A Principal-Architect review overturned that. This ADR records why, because the reasoning generalises: "the table already exists" is an argument about cost, and it is only decisive when the existing shape can express the requirement.

Decision 1 — the legacy schema is replaced, not adopted

The decisive finding: it cannot represent a recurring reminder at all

The legacy public.reminders carries is_recurring, recurrence_pattern and recurrence_end_date alongside a single due_date, a single is_completed, and a single notification_sent.

A recurring series has N occurrences, each with its own completion and notification state. In that shape, completing one occurrence must either complete the entire series or nothing at all — there is no third option available. This is not a missing constraint that could be tightened; it is a modelling error, and recurring reminders ("file GST monthly", "reorder stock weekly", "renew the trade licence annually") are the highest-value case for every archetype we ship.

The live data confirms the incoherence rather than merely implying it. All 11 Prod rows carry is_recurring = false with recurrence_pattern = 'daily' — the column default leaking into every row, because nothing constrains the two fields to agree. A reader cannot distinguish "not recurring" from "recurring daily, flag not set".

Three further flaws, each independently sufficient

Flaw	Why it is disqualifying
category varchar(50) free-text and a reminder_categories table, with no FK between them	Two competing sources of truth. Already drifted in production: the column default is lowercase 'general' while every stored row holds 'General', 'Work' or 'Personal'
reminder_categories.color stores raw hex (DEFAULT '#3B82F6')	A hard-coded colour baked into the database, against the zero-hard-coded-colour invariant. Tokens cannot theme it, so it is wrong in dark mode by construction, and no amount of frontend work fixes a value the DB supplies
No workspace_id	ADR-0001 has every domain entity carrying one. Adding it later is a backfill; adding it now, nullable, costs nothing

Consequence for profiles

profiles.reminder_count is a cached counter with no maintainer anywhere in the tree. Measured on Prod: 0 for all 11 profiles while reminders held 11 rows — already 100% wrong. It is dropped rather than repaired with a trigger, because nothing reads it and the new model derives counts from rows (QRS-211).

reminder_notifications_enabled and reminder_notification_time are kept. The implementation plan contained a contradiction here — it directed dropping "the three profiles reminder columns" while its own scheduler specification "honours reminder_notifications_enabled". Resolved in favour of the scheduler: these two are well-shaped preferences and are now actually read.

Decision 2 — archive the legacy tables; do not drop them

This decision was forced by evidence, and is the more instructive half of this ADR.

The plan's first blocking pre-flight gate read: "Prove both projects are empty. A destructive migration on unverified data is not acceptable regardless of expectation." Running it, per project ref rather than by connection label (the QRS-179 trap):

Project	Ref	reminders	reminder_categories
qr-setu-prod	ygmqxyrbnemhwkiyoboc	11 rows, 4 users	0
qr-setu-dev	dyhjofjjuazhyqcvlrkx	0	0

The premise the plan was built on — "Prod confirmed to hold no reminder data" — was false. No amount of code inspection would have revealed this: the only consumers of these tables live in legacy/**, which is neither built nor linted, so a search for live callers returns clean and tells you nothing whatsoever about stored rows.

So the migration moves both tables into a legacy schema instead of dropping them:

• Rows are preserved, and remain queryable by an operator.
• PostgREST reach is removed — Supabase exposes only configured schemas, and legacy is not one.
• Privileges are revoked as well, so reachability does not depend on a single config setting holding. Belt and braces, deliberately: "not in the exposed schema list" is a good control but a poor only control.
• The canonical public.reminders name is freed for the new model, which is the only thing the drop was actually needed for.

The real DROP becomes a separate one-line contract migration, gated on the data owner confirming the 11 rows are disposable.

This is what the standard already required

CLAUDE.md mandates expand-contract: "add-nullable → backfill → switch → drop; never a breaking change in one step." A one-shot DROP TABLE was never compliant. The pre-flight did not force a workaround — it forced us onto the process the standard already specified. The safe path and the compliant path turned out to be the same path.

Decision 3 — rule + sparse exception rows

Store the rule. Compute occurrences on read. Persist an occurrence row only when its state diverges.

reminders                         -- the RULE (one row per reminder, recurring or not)
  id, user_id → auth.users on delete cascade
  workspace_id uuid NULL                                 -- forward-compat (ADR-0001)
  title (<=200), description
  category_id → reminder_categories on delete set null    -- single source of truth
  priority   CHECK (low|medium|high|urgent)
  starts_at  timestamptz                                  -- the first occurrence, an instant
  timezone   text                                         -- IANA zone; local-time correctness
  recurrence jsonb NULL                                   -- NULL = one-off; RRULE subset
  recurrence_until timestamptz NULL
  status     CHECK (active|archived)
  created_at, updated_at

reminder_occurrences              -- sparse EXCEPTIONS only; absence means "as the rule says"
  id, reminder_id → cascade, user_id                      -- denormalised for RLS + index
  due_at timestamptz
  state  CHECK (completed|skipped)
  completed_at, notified_at
  UNIQUE (reminder_id, due_at)                            -- one exception per occurrence

reminder_categories
  id, user_id, name
  color_token text                                        -- a TOKEN KEY, never hex
  icon, UNIQUE (user_id, name)

Alternatives considered

Option	Why not
Full materialisation — expand every occurrence into rows up front	Unbounded recurrence has no natural horizon, so it needs an arbitrary cutoff plus a cron job to extend it. Row count grows without bound for a feature whose entire read pattern is "the next few". Editing a rule means rewriting N rows
Rule only, no occurrence rows	Cannot represent "I completed Tuesday's but not Wednesday's" — the same defect that disqualified the legacy schema
Legacy shape, constraints tightened	Disqualified above: one is_completed for N occurrences is not fixable by constraint

The trade-off, stated

A future server-side digest worker cannot answer "which users have something overdue right now" without scanning and expanding every rule, because overdue-ness is computed, not stored. That is a real cost and it is accepted for now: all consumption in R1 is per-user and client-bounded. The migration path is additive — a materialised projection can be built later without changing this model, which is why this is a deferrable cost rather than a trap.

Why CHECK and not Postgres ENUM

Extending a CHECK constraint is expand-contract friendly: add the new constraint, migrate, drop the old. ALTER TYPE ... ADD VALUE cannot run in a transaction block and cannot be reversed, which makes an enum the harder thing to change later — the opposite of what a growing priority/state vocabulary needs.

Why the timezone lives on the reminder, not just the profile

timestamptz is correct for an instant, but "every day at 09:00 local" is not an instant — it is a rule that resolves to a different instant depending on zone and DST. Storing the IANA zone per reminder (defaulted from the device at creation) handles the case a profile-level column cannot: a merchant who travels keeps "09:00 Asia/Kolkata" anchored where they set it, rather than silently shifting to wherever they opened the app.

Decision 4 — idempotency is required, not recommended

manage-reminder requires a client-supplied idempotency_key, persisted under a UNIQUE constraint so a replay returns the original result instead of creating a second row.

This is not defensive speculation. It has already happened three times on Prod, and the duplicate rows are still there: three pairs with identical title and due_date, same user, created 0.7 s, 1.1 s and 7.7 s apart — 6 of the 11 rows are duplicates of each other (QRS-210). The legacy write path was a direct supabase.from().insert(), so no layer existed that could have deduped; routing writes through an Edge Function is what makes the fix possible.

Decision 5 — occurrence computation is pure, and bounded by iOS

Recurrence expansion, DST-safe local resolution, overdue bucketing and next-N selection live in packages/domain/src/reminders/ as pure functions with no I/O, per ADR-0012's schemas → domain → data DAG.

This is not merely tidiness. iOS hard-caps pending local notifications at 64 per app, silently dropping the overflow. A scheduler that "reconciles every reminder" therefore fails at scale in the least visible way possible: the alerts a merchant most needs are the ones dropped. The correct design is a rolling horizon — select the soonest N occurrences within a budget below the cap, re-arm on launch and foreground — and "select the soonest N across all rules" is exactly a pure function over rules and exceptions. Making it pure makes the cap unit-testable rather than something discovered on a device with 80 reminders.

The platform constraint therefore shapes the model, which is why it belongs in an ADR and not only in a scheduler file.

Consequences

Positive

• Recurring reminders are representable, per-occurrence, which was the point.
• No cron job, no row explosion, no arbitrary expansion horizon.
• Category colours become token keys, so dark mode works and the DB stops carrying design decisions.
• The iOS 64-cap becomes an L1-testable property of a pure function.
• 11 rows of production data survive a schema replacement that was planned to destroy them.

Negative / accepted

• Server-side "who is overdue" needs materialisation that does not exist yet (additive later).
• Occurrence computation runs on every client rather than once on a server — bounded, memoised, and cheap at R1 scale.
• Two schemas (public + legacy) until the contract migration lands.
• workspace_id is present but unenforced until multi-tenancy in R2; a nullable column that nothing validates is a mild honesty cost, accepted because the backfill it avoids is worse.

Compliance

Standard	How
RLS on every table, TO authenticated on every policy (ADR-0014)	All three new tables, from the first line — the legacy tables' 8 unqualified policies are QRS-211
Expand-contract (CLAUDE.md)	Archive now, drop in a separate gated migration
Idempotent mutations (CLAUDE.md)	idempotency_key + UNIQUE, with a replay test
Zero hard-coded colours	color_token, never hex
Entitlement-gated limits (ADR-0007)	Per-user quota enforced in the EF, resolved from the tier
schemas → domain → data (ADR-0012)	Pure recurrence logic in packages/domain, no I/O
Reminder text is private	Never in EF logs, Sentry breadcrumbs, or the default lock-screen preview