# Data that syncs and works offline

## Shape
- D1 (SQLite) behind the Worker. The browser never talks to the database directly.
- Every table has `id TEXT` (made on the phone, so offline entries need no server round trip), `household_id`/`user_id`, `created_at`, `updated_at`, `deleted INTEGER DEFAULT 0`.
- Soft delete (`deleted = 1`) so other phones learn about deletions when they sync.

## The Worker checks everything
One `clean…()` function per kind of record: allow-list the fields, check types, ranges and lengths, reject the rest. Dates within sensible bounds (not the future, not before the app existed). Return 400 with a plain message; the app shows it.

## Sync with a cursor
- `GET /api/sync?since=<updated_at>` returns rows changed after the cursor, oldest first, in pages (e.g. 5,000) with a `more` flag. The phone stores the new cursor only after saving the page.
- Writes go in batches: `POST /api/events` with `[{op:"put", …}, {op:"del", id}]`. Upserts with `ON CONFLICT … DO UPDATE`, so retries are safe.
- Last write wins per row is fine for small groups. Shared "now" state (a running timer) lives on the household row and is replaced whole.

## Offline queue (outbox)
- Every change goes into an outbox in `localStorage` first, then the UI updates immediately from local state plus the outbox.
- Flush on a short debounce, on `online`, on `visibilitychange`. Keep items until the server confirms. Drop items the server rejects as invalid (400), or the queue can jam forever.
- Show a small "offline, will save when back" pill while items wait.
- `localStorage` holds about 5 MB: fine for years of small entries; move to IndexedDB before it gets close.

## Migrations
- Numbered files: `migrations/0001_init.sql`, `0002_…`. Apply locally (`wrangler d1 migrations apply DB --local`), test, then remotely **before** deploying code that needs them. A `deploy` script that runs `migrations apply --remote && wrangler deploy` makes the order automatic.
- Additive only while old phones are out there: `ALTER TABLE … ADD COLUMN x INTEGER` (nullable). Rename or drop later, in a separate release, once every phone has updated.
- Insert statements list columns explicitly; count the placeholders after adding one.

## Backups
- D1 Time Travel restores to any minute in the last 7 days (free plan) or 30 days (Workers Paid): `wrangler d1 time-travel restore DB --timestamp=<unix>`. It overwrites in place.
- For longer, a scheduled Worker writes a nightly export to R2. Write a restore script and test it on a copy, counting rows, without touching live data.
- A test that fails when a new table isn't in the backup list.

## Logic shared by phone and server
Put calculations (totals, streaks, "is this a joined feed") in one `logic.js` that works in both the browser and the Worker (UMD or ES module), with unit tests. Then the phone and the server never disagree.
