Tighten v1 schema keys, weekday checks and ai_runs references

- ai_run_id in suggestion_batches and week_plans now uses the default
  refusing action instead of ON DELETE SET NULL; the ai_runs purge must
  clear references explicitly. Tested as a refused parent delete.
- Every TEXT primary key (including notification_prefs.event and
  sessions.id_hash) is NOT NULL; tested that NULL keys are rejected.
- Weekday columns use CHECK (col IN (0, 1, 2, 3, 4, 5, 6)) so fractional
  values are refused; tested with 5.5, 0.5 and 3.5.
- Composite UNIQUE tests add positive partial-overlap cases (same date
  under different plans, same plan_date for different dinners, same
  event_key on different channels, same period_key for different
  schedules).

Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>

.dockerignore

@@ -0,0 +1,26 @@
+# Keep the build context small and free of secrets and local state.
+.git
+.gitignore
+.dockerignore
+Dockerfile
+.remember/
+.claude/
+design/
+**/.DS_Store
+node_modules
+web/node_modules
+web/dist
+# Generated embed output; the Go stage takes the PWA from the web stage.
+internal/frontend/dist/
+bin/
+*.db
+*.db-*
+.env*
+**/.env*
+deploy/secrets/
+**/*.pem
+**/*.key
+**/*.p12
+**/*.pfx
+**/*.jks
+**/*.keystore

.gitea/workflows/ci.yml

@@ -0,0 +1,32 @@
+# Runs on every push and pull request (no branch filter) so feature branches,
+# their pull requests, and dev/main are all checked. Runs directly on the
+# self-hosted act_runner; no container: key.
+#
+# The Makefile is the single build definition: CI runs `make test` and
+# `make build` so it fails whenever either target would, and the binary
+# carries the Makefile's -trimpath and version-stamped -ldflags (the
+# `make build` log shows the -X main.version=... value).
+name: CI
+
+on: [push, pull_request]
+
+jobs:
+  ci:
+    runs-on: ubuntu-latest
+    steps:
+      - uses: actions/checkout@v4
+
+      - uses: actions/setup-go@v5
+        with:
+          go-version-file: go.mod
+          cache: false
+
+      - uses: actions/setup-node@v4
+        with:
+          node-version: "22"
+
+      - name: make test
+        run: make test
+
+      - name: make build
+        run: make build

.gitignore

@@ -8,6 +8,11 @@ deploy/secrets/
 # Build artifacts and dependencies
 node_modules
 dist
+bin/
+# Placeholder so the Go embed compiles on a fresh clone; built assets stay ignored.
+!internal/frontend/dist/
+internal/frontend/dist/*
+!internal/frontend/dist/index.html
 
 # Local databases
 *.db

AGENTS.md

@@ -0,0 +1,143 @@
+# AGENTS.md — MealQuest agent guide
+
+This is the authoritative guide for every agent (and human) working in this
+repository. `CLAUDE.md` only imports this file. Where this guide and a general
+habit disagree, this guide wins.
+
+MealQuest is a single Go service with an embedded React PWA for one
+household. The architecture of record is [docs/ARCHITECTURE.md](docs/ARCHITECTURE.md);
+product requirements are in [design/MealQuest Requirements.md](design/MealQuest%20Requirements.md).
+
+## House rules
+
+1. **Fail fast.** Invalid configuration, input or state is an error reported
+   immediately and clearly — the service refuses to start on bad config.
+2. **No silent fallbacks.** Never substitute a default, retry quietly, or
+   swallow an error to "keep going". If something is missing, say so.
+3. **One way to do a thing.** No parallel code paths, alternative settings or
+   "just in case" options. Pick one approach and use it everywhere.
+4. **No new third-party dependency without owner approval.** Use only the
+   approved list below; anything else needs the owner's explicit yes first.
+5. **Never use `tea`.** The Gitea CLI on this host is authenticated as the
+   human owner. Agents act only through their own Gitea identities.
+
+## Approved dependencies (from CTXD)
+
+Backend:
+
+- Go 1.25, built with `CGO_ENABLED=0`.
+- `github.com/go-chi/chi/v5` — HTTP router.
+- `modernc.org/sqlite` — CGO-free SQLite driver.
+- `sqlc` — query code generation (dev tool only).
+- `github.com/coreos/go-oidc/v3` — OIDC (Authentik).
+- `golang.org/x/oauth2` and `golang.org/x/oauth2/google`.
+- `github.com/SherClockHolmes/webpush-go` — Web Push.
+- `golang.org/x/image` — photo processing.
+- `github.com/oklog/ulid/v2` — IDs.
+- Standard library `log/slog` and `net/http`.
+- **Not allowed:** LLM vendor SDKs, Google API SDKs (call the HTTP APIs
+  directly), goose, golang-migrate.
+
+Frontend:
+
+- React 19, TypeScript, Vite.
+- React Router 7, TanStack Query 5.
+- `vite-plugin-pwa`.
+- `lucide-react` icons at stroke width 2.75.
+- CSS Modules over the design tokens in
+  [tokens.css](design/MealQuest%20UI%20Designs/design_handoff_mealquest/tokens.css).
+  **No Tailwind, no Framer Motion, no hard-coded hex colours.**
+- Self-hosted fonts: Bagel Fat One and Figtree.
+- Tests: Vitest, Testing Library, jsdom.
+
+## Quality floor
+
+Every change must pass all of these before review (CI in
+[.gitea/workflows/ci.yml](.gitea/workflows/ci.yml) enforces them):
+
+```sh
+gofmt -l .            # must print nothing
+go vet ./...
+go test -race ./...
+cd web && npm run typecheck && npm test && npm run build
+```
+
+And the build entry points in the [Makefile](Makefile) and [Dockerfile](Dockerfile):
+
+```sh
+make test
+make build
+docker build .
+```
+
+Lint proves code parses, not that it works: where a change has runtime
+behaviour, exercise it (hit the endpoint, render the page).
+
+## Repository layout
+
+| Path | Purpose |
+| --- | --- |
+| `cmd/server` | Entry point. Wiring only — no business logic. |
+| `internal/config` | `MEALQUEST_*` environment config; secrets from `*_FILE`. |
+| `internal/server` | HTTP server, routes, `/health`. |
+| `internal/frontend` | `go:embed` of the built PWA with SPA fallback. |
+| `internal/*` (planned) | `auth`, `db`, `store`, `secretbox`, `household`, `dinners`, `recipes`, `ai/llm`, `ai/harness`, `ai/skills`, `suggest`, `plan`, `shopping`, `ideas`, `gcal`, `notify`, `push`, `chat`, `jobs`, `scheduler`. |
+| `migrations/` | SQL migrations (planned). |
+| `web/` | React PWA source. |
+| `deploy/` | Deployment files (planned). |
+| `docs/` | Project documentation, including [ARCHITECTURE.md](docs/ARCHITECTURE.md). |
+| `design/` | Requirements and UI handoff. **Read-only reference** — do not edit. |
+| `Dockerfile` | Multi-stage image build, at the repository root. |
+| `Makefile` | `dev`, `test`, `build`, `docker`. |
+| `.gitea/workflows/ci.yml` | CI quality floor. |
+
+Design references:
+
+- [design/MealQuest Requirements.md](design/MealQuest%20Requirements.md)
+- [design/MealQuest UI Designs/design_handoff_mealquest/README.md](design/MealQuest%20UI%20Designs/design_handoff_mealquest/README.md)
+- [design/MealQuest UI Designs/design_handoff_mealquest/tokens.css](design/MealQuest%20UI%20Designs/design_handoff_mealquest/tokens.css)
+
+## Branches, pull requests and merges
+
+- Integration branch is `dev`. `main` is the release branch.
+- **Feature branches:** `feature/<feature-uuid>-<slug>`, branched from `dev`.
+- **Task branches:** `task/<slug>_<task-uuid>`, branched from the feature branch.
+- Task pull requests **squash-merge into the feature branch**.
+- The feature pull request merges into `dev`.
+- **Only `overseer` merges `dev` → `main`** (a release).
+- Never push directly to `dev` or `main`.
+- Authors never review, approve, merge or close their own work.
+- Never store credentials in the repository; environment or ignored files only.
+
+## Where context lives and where work is tracked
+
+- **Context:** CTXD project `mealquest` — client guide at
+  <https://ctxd.cubecraftcreations.com>. Project documents: `CONTEXT.MD`
+  (architecture), `DECISIONS.MD` (decision log, e.g. D-021), `RUNBOOKS.MD`,
+  `PROMPTS.MD`, `GLOSSARY.MD`.
+- **Work tracking:** Maestro at <https://maestro.cubecraftlabs.com>,
+  workspace **Maniacs-Mansion** → project **MealQuest**. Every change has a
+  task with a verifiable done-condition.
+
+## Vocabulary (GLOSSARY)
+
+Use these product terms in code names, UI copy, docs and commit messages. Do
+not invent synonyms.
+
+| Term | Meaning |
+| --- | --- |
+| **Regulars** | Dinners the household makes routinely. |
+| **Side quest** | An AI-suggested new dinner to try. |
+| **Quest log** | The to-try pool of accepted Side quests, scheduled later. |
+| **Try it / Pass** | Accept (swipe right) / decline (swipe left) a suggestion. |
+| **Rotation check** / **"Keep it in rotation?"** | The prompt after a Quest-log dinner is tried, asking whether it becomes a Regular. Promotion is always the household's choice, never automatic. |
+| **Supply run** | The current week's shopping list, grouped by dinner. |
+| **Supply archive** | The blog-style history of past shopping lists (by week, month, year). |
+| **Draft board** | The mobile weekly plan layout the AI drafts for the household to adjust. |
+| **Rebuild week** | Ask the AI to redraft the week's plan. |
+| **Push to Calendar** | Push the plan one-way to Google Calendar. |
+| **Quick dinner** | Cooked in a crock pot **or** 30 minutes or less from prep to table. |
+| **Cook from what I have** | Ideas from a list of ingredients on hand. |
+| **Kitchen reading mode** | Tablet recipe view: large text, ingredient checkboxes, step-by-step. |
+| **"Why this?"** | The reason shown for a suggestion, referencing the household's Regulars. |
+| **Our kitchen / Shared household** | The single shared household identity; no per-person profiles. |

CLAUDE.md

@@ -0,0 +1 @@
+@AGENTS.md

Dockerfile

@@ -0,0 +1,42 @@
+# syntax=docker/dockerfile:1
+# Base images are pinned by digest (multi-arch index); tags are for readability.
+
+FROM node:22-alpine@sha256:0a7108bf6c7bf5de370ffb1a3ed6be93d405b43ff159f681a8d18c0e2bc2e402 AS web
+WORKDIR /src/web
+COPY web/package.json web/package-lock.json ./
+RUN npm ci
+COPY web/ ./
+RUN npm run build
+
+FROM golang:1.25-alpine@sha256:1ae0735f00daffa3aaf1363a5184c0d2dc55c78e3db4ec70241cdac97bf84b59 AS server
+ARG VERSION=dev
+WORKDIR /src
+COPY go.mod go.sum ./
+RUN go mod download
+COPY . .
+# Replace the placeholder with the built PWA; recreate the directory so no
+# stale file survives into the embed.
+RUN rm -rf internal/frontend/dist
+COPY --from=web /src/web/dist/ internal/frontend/dist/
+RUN CGO_ENABLED=0 go build -trimpath -ldflags "-s -w -X main.version=${VERSION}" \
+    -o /out/mealquest ./cmd/server
+
+# Alpine ships the CA bundle and busybox wget for the health check.
+FROM alpine:3.22@sha256:5291449c3df73caf6ed85e649dec1b9e818b39a5d8c871e97afc13e9cd5e8fa8
+RUN addgroup -S -g 10001 mealquest \
+    && adduser -S -D -H -u 10001 -G mealquest mealquest \
+    && mkdir /data && chown mealquest:mealquest /data
+COPY --from=server /out/mealquest /usr/local/bin/mealquest
+ENV MEALQUEST_HTTP_ADDR=:8080 \
+    MEALQUEST_DATA_DIR=/data
+VOLUME /data
+EXPOSE 8080
+USER 10001:10001
+# Probe the listener from MEALQUEST_HTTP_ADDR: host is everything before the
+# last colon (IPv6 keeps its brackets). Wildcard hosts map to loopback, as
+# does localhost: Go listens on its IPv4 address but wget would try ::1.
+HEALTHCHECK --interval=30s --timeout=3s --start-period=5s --retries=3 \
+    CMD host="${MEALQUEST_HTTP_ADDR%:*}"; \
+    case "$host" in ""|0.0.0.0|"[::]"|localhost) host=127.0.0.1 ;; esac; \
+    wget -q -O /dev/null "http://${host}:${MEALQUEST_HTTP_ADDR##*:}/health" || exit 1
+ENTRYPOINT ["/usr/local/bin/mealquest"]

Makefile

@@ -0,0 +1,60 @@
+# MealQuest build entry points.
+#
+#   make dev     Go server on :8080 plus Vite dev server on :5173 (proxies
+#                /api, /auth, /media to :8080). Needs the MEALQUEST_* env set.
+#   make test    gofmt check, go vet, go test -race, npm typecheck and test.
+#   make build   Build the PWA, embed it, build the static binary bin/mealquest.
+#   make docker  Build the container image from ./Dockerfile.
+
+FRONTEND_DIST := internal/frontend/dist
+# Dirty means a tracked change outside the generated embed output, which
+# `make embed` rewrites; plain `--dirty` would flag every rebuild.
+GIT_DIRTY := $(shell git status --porcelain --untracked-files=no -- . ':(exclude)$(FRONTEND_DIST)' 2>/dev/null)
+VERSION ?= $(shell git describe --tags --always 2>/dev/null || echo dev)$(if $(GIT_DIRTY),-dirty)
+IMAGE ?= mealquest:$(VERSION)
+LDFLAGS := -s -w -X main.version=$(VERSION)
+
+.PHONY: dev test go-test web-test build web embed check-embed-clean docker
+
+# Run both servers; Ctrl-C (or either exiting) stops both. The binary is built
+# first and run directly because `go run` would orphan its child on kill.
+dev: SHELL := /bin/bash
+dev:
+	go build -ldflags "$(LDFLAGS)" -o bin/mealquest-dev ./cmd/server
+	@trap 'kill $$(jobs -p) 2>/dev/null; wait' EXIT; \
+	bin/mealquest-dev & \
+	(cd web && exec npm run dev) & \
+	wait -n
+
+test: go-test web-test
+
+go-test:
+	@unformatted="$$(gofmt -l .)"; \
+	if [ -n "$$unformatted" ]; then echo "gofmt needed:"; echo "$$unformatted"; exit 1; fi
+	go vet ./...
+	go test -race ./...
+
+web-test:
+	cd web && npm ci && npm run typecheck && npm test
+
+build: embed
+	CGO_ENABLED=0 go build -trimpath -ldflags "$(LDFLAGS)" -o bin/mealquest ./cmd/server
+
+web:
+	cd web && npm ci && npm run build
+
+# Replace the committed placeholder with the built PWA so go:embed picks it up.
+# Recreate the whole directory: a glob would skip dotfiles, which all:dist embeds.
+embed: web
+	rm -rf $(FRONTEND_DIST)
+	mkdir -p $(FRONTEND_DIST)
+	cp -R web/dist/. $(FRONTEND_DIST)/
+
+# Regression check: a stale hidden file must not survive an embed.
+check-embed-clean:
+	touch $(FRONTEND_DIST)/.stale-embed-check
+	$(MAKE) embed
+	test ! -e $(FRONTEND_DIST)/.stale-embed-check
+
+docker:
+	docker build --build-arg VERSION=$(VERSION) -t $(IMAGE) .

README.md

@@ -0,0 +1,56 @@
+# MealQuest
+
+MealQuest is a mobile-first Progressive Web App for weekly dinner planning for
+one household. The household lists the dinners it makes regularly, an AI
+assistant suggests new dinners to try (swipe "Try it" or "Pass"), and the app
+builds a weekly plan, pushes it to Google Calendar, and generates a shopping
+list grouped by dinner, kept as a browsable weekly history. It ships as a
+single Go service with the React PWA embedded in the binary.
+
+> Status: this repository is the Feature 01 scaffold (Go service with
+> `/health`, embedded PWA skeleton, Makefile, Dockerfile, CI). The planned
+> application is described in [docs/ARCHITECTURE.md](docs/ARCHITECTURE.md).
+
+## Quick start
+
+Requirements: Go 1.25 and Node.js `^22.22.2 || ^24.15.0 || >=26.0.0` — that
+is, 22.22.2 or later on the 22 line, 24.15.0 or later on the 24 line, or 26.0.0
+or later. Node.js 23, 24.0–24.14 and 25 are not supported: this is the
+`engines` range of jsdom in the committed lockfile, the strictest of all the
+web dependencies.
+
+```sh
+(cd web && npm ci)
+mkdir -p ~/.mealquest-dev   # outside the repository: it holds a secret
+chmod 700 ~/.mealquest-dev
+head -c 32 /dev/urandom | base64 > ~/.mealquest-dev/secret_key
+chmod 600 ~/.mealquest-dev/secret_key
+export MEALQUEST_HTTP_ADDR=:8080
+export MEALQUEST_PUBLIC_URL=http://localhost:5173
+export MEALQUEST_DATA_DIR="$HOME/.mealquest-dev"
+export MEALQUEST_SHUTDOWN_TIMEOUT=10s
+export MEALQUEST_SECRET_KEY_FILE="$HOME/.mealquest-dev/secret_key"
+make dev
+```
+
+`make dev` runs the Go server on `:8080` and the Vite dev server on `:5173`
+(which proxies `/api`, `/auth` and `/media` to `:8080`). Open
+http://localhost:5173. Configuration is environment-only and fails fast:
+every variable above is required, and secrets are read only from files named
+by `*_FILE` variables.
+
+## Common commands
+
+| Command        | What it does                                                    |
+| -------------- | --------------------------------------------------------------- |
+| `make dev`     | Go server plus Vite dev server.                                 |
+| `make test`    | `gofmt` check, `go vet`, `go test -race`, web typecheck and tests. |
+| `make build`   | Build the PWA, embed it, build the static binary `bin/mealquest`. |
+| `make docker`  | Build the container image from the root `Dockerfile`.           |
+
+## Where to read next
+
+- [AGENTS.md](AGENTS.md) — house rules, approved dependencies, conventions.
+- [docs/ARCHITECTURE.md](docs/ARCHITECTURE.md) — architecture of record.
+- [design/MealQuest Requirements.md](design/MealQuest%20Requirements.md) — product requirements.
+- [design/MealQuest UI Designs/design_handoff_mealquest/README.md](design/MealQuest%20UI%20Designs/design_handoff_mealquest/README.md) — UI handoff.

cmd/server/main.go

@@ -13,7 +13,10 @@ import (
 	"time"
 
 	"code.cubecraftlabs.com/Maniacs_Mansion/Meal-Quest/internal/config"
+	"code.cubecraftlabs.com/Maniacs_Mansion/Meal-Quest/internal/frontend"
 	"code.cubecraftlabs.com/Maniacs_Mansion/Meal-Quest/internal/server"
+	"code.cubecraftlabs.com/Maniacs_Mansion/Meal-Quest/internal/store"
+	"code.cubecraftlabs.com/Maniacs_Mansion/Meal-Quest/migrations"
 )
 
 // version is set at build time with -ldflags "-X main.version=<version>".
@@ -37,9 +40,26 @@ func run(cfg config.Config, logger *slog.Logger) error {
 	ctx, stop := signal.NotifyContext(context.Background(), os.Interrupt, syscall.SIGTERM)
 	defer stop()
 
+	db, err := store.Open(ctx, cfg.DataDir)
+	if err != nil {
+		return err
+	}
+	defer func() {
+		if err := db.Close(); err != nil {
+			logger.Error("close database", "error", err)
+		}
+	}()
+	if err := store.Migrate(ctx, db.Writer, migrations.FS); err != nil {
+		return err
+	}
+
+	router, err := server.NewRouter(logger, version, frontend.Assets())
+	if err != nil {
+		return err
+	}
 	srv := &http.Server{
 		Addr:              cfg.HTTPAddr,
-		Handler:           server.NewRouter(logger, version),
+		Handler:           router,
 		ReadHeaderTimeout: 10 * time.Second,
 		ErrorLog:          slog.NewLogLogger(logger.Handler(), slog.LevelError),
 	}

docs/ARCHITECTURE.md

@@ -0,0 +1,235 @@
+# MealQuest architecture
+
+This mirrors the architecture section of `CONTEXT.MD` in the CTXD project
+`mealquest`, which is the source of record; if the two disagree, CTXD wins and
+this file should be updated. It describes the **planned** application. The
+repository today is the Feature 01 scaffold: `cmd/server`, `internal/config`,
+`internal/server` (`/health`), `internal/frontend` (embedded PWA with SPA
+fallback), the `web/` skeleton, `Makefile`, `Dockerfile` and CI.
+
+Decision numbers (D-NNN) refer to `DECISIONS.MD` in CTXD.
+
+## Topology
+
+```
+PWA (phone / tablet / desktop)
+   │ HTTPS
+   ▼
+Traefik v3.6 — 10.60.1.33
+   file route: mealquest-ccl.yml
+   │
+   ▼
+docker compose project "mealquest" — 10.60.1.14
+   ├── mealquest   Go service + embedded PWA, host 8090 → container 8080
+   ├── searxng     sidecar for recipe search; internal compose network
+   │               only, no published port
+   └── Litestream  SQLite replication → Synology NAS 10.60.1.50
+```
+
+One Go service serves the API, the auth flow, media and the PWA. Its SQLite
+database is replicated by Litestream to the NAS.
+
+## Repository layout
+
+| Path | Purpose |
+| --- | --- |
+| `cmd/server` | Entry point; wiring only. |
+| `internal/config` | Env-only `MEALQUEST_*` config; secrets from `*_FILE`. |
+| `internal/server` | HTTP server, router, middleware. |
+| `internal/frontend` | Embedded PWA, SPA fallback. |
+| `internal/auth` | Authentik OIDC, sessions, CSRF. |
+| `internal/db`, `internal/store` | SQLite connections, sqlc queries. |
+| `internal/secretbox` | AES-256-GCM encryption for `_ct` columns. |
+| `internal/household`, `internal/dinners`, `internal/recipes` | Domain. |
+| `internal/ai/llm`, `internal/ai/harness`, `internal/ai/skills` | AI provider adapters, skill harness, skills. |
+| `internal/suggest`, `internal/plan`, `internal/shopping`, `internal/ideas` | Feature services. |
+| `internal/gcal`, `internal/notify`, `internal/push`, `internal/chat` | Integrations. |
+| `internal/jobs`, `internal/scheduler` | Background jobs and schedules. |
+| `migrations/` | SQL migrations (no goose or golang-migrate). |
+| `web/` | React PWA. |
+| `deploy/` | Deployment files. |
+| `docs/` | Documentation. |
+| `design/` | Requirements and UI handoff (read-only reference). |
+
+## Backend stack and conventions
+
+- Go 1.25, `CGO_ENABLED=0`, a single static binary.
+- Router: `go-chi/chi/v5`. Logging: `log/slog` JSON.
+- SQLite via `modernc.org/sqlite`, with `journal_mode=WAL`,
+  `foreign_keys=ON` and `busy_timeout`. **One writer connection plus a read
+  pool.** Queries generated by `sqlc`.
+- IDs are ULIDs (`oklog/ulid/v2`).
+- Timestamps are stored in UTC; local calendar dates are `YYYY-MM-DD` strings
+  in the household timezone.
+- Configuration is environment-only (`MEALQUEST_*`). Secrets come only from
+  files named by `*_FILE` variables. Missing or invalid config stops startup.
+- Error responses always have the body
+  `{"error":{"code":"…","message":"…","retryable":false}}`.
+
+## Authentication
+
+- Authentik OIDC; the backend is a **confidential client**
+  (`coreos/go-oidc/v3`, `golang.org/x/oauth2`).
+- Session cookie `__Host-mealquest_session` (HttpOnly, Secure,
+  SameSite=Lax). Sessions expire after 30 days idle or 180 days absolute.
+- Non-GET requests require the CSRF header `X-MealQuest-CSRF`.
+- Access requires membership of the Authentik group `mealquest-admin`.
+  There is a **single role**: one shared household, no per-person profiles.
+
+## Data model
+
+Columns ending in `_ct` hold AES-256-GCM ciphertext (via `secretbox`).
+
+| Table | Holds |
+| --- | --- |
+| `household_settings` | One row: timezone, dinner_time (17:30), reminder_time (16:30), review_time (09:00), plan_draft day/time (Fri 09:00), suggestions day/time (Sun 08:00), chat_autosend day/time (Sat 08:00), onboarding_completed_at, updates_seen_at. |
+| `recipes` | A real recipe page: source_url (unique), site_name, title, total/prep/cook minutes, yield, raw_jsonld, fetched_at. |
+| `recipe_ingredients` | recipe_id, position, raw_text, name, qty, unit, note. |
+| `recipe_steps` | recipe_id, position, text. |
+| `photos` | source_url, local_path, width, height, sha256, fetched_at. |
+| `dinners` | The meal record and its lifecycle: name, normalized_name, status (`suggested` / `quest_log` / `tried` / `regular` / `archived`), origin, method, minutes_total, difficulty, cuisine, is_favorite, recipe_id, photo_id, grounding_status, updated_by. |
+| `suggestion_batches` | trigger, status, ai_run_id. |
+| `suggestions` | dinner_id, batch_id, reason, cited_regular_ids (JSON), status, decided_at. |
+| `week_plans` | week_start (unique), status (`draft` / `synced` / `changed`), ai_note, ai_run_id, calendar_synced_at. |
+| `plan_days` | plan_id, date (unique per plan), dinner_id, is_new, calendar_event_id. |
+| `rotation_reviews` | dinner_id + plan_date (unique), status, decision (`keep` / `drop` / `not_made`), decided_at, updated_by. |
+| `shopping_lists` | week_start (unique), plan_id, title, status (`current` / `archived`), sent_to_chat_at, frozen_at. |
+| `shopping_groups` / `shopping_items` | One group per dinner/day; item name, qty, needed (bool), position. |
+| `ai_settings` | One row: provider, base_url, model, temperature, system_prompt, api_key_ct, api_key_last4, key_verified_at. |
+| `recipe_search_settings` | One row: provider (`searxng` / `brave`), base_url, api_key_ct. |
+| `integrations_google` | One row: refresh_token_ct, calendar_id, calendar_name, connected_at, last_sync_error. |
+| `chat_settings` | One row: webhook_url_ct, space_label, last_test_at, last_test_status. |
+| `notification_prefs` | event (`plan_ready` / `list_ready` / `day_of_reminder` / `new_suggestions` / `rotation_review`), push, chat. |
+| `push_subscriptions` | endpoint (unique), p256dh, auth, user_agent, failure_count, last_success_at. |
+| `notification_outbox` | event_key + channel (unique), payload, status, attempts, next_attempt_at, last_error. |
+| `jobs` | kind, status, payload, result (progressive), error, timestamps. |
+| `schedule_runs` | schedule, period_key (unique). |
+| `ai_runs` | skill, skill_version, provider, model, temperature, status, latency_ms, tokens_in/out, error, redacted request/response (30-day retention). |
+| `sessions` | id_hash, subject, email, name, groups, csrf_token, created/last_seen/expires. |
+
+`recipes` holds a fetched recipe page; `dinners` holds the household's meal
+record and its lifecycle, optionally linked to a recipe and a photo.
+
+## Dinner lifecycle
+
+```
+suggested (deck) ──Try it──▶ quest_log ──planned date passes──▶ tried
+    │                            ▲                                │
+    │                            └──────── explicit decision ─────┤
+    │                                                             ├──▶ regular
+    │                                                             └──▶ archived
+    └──Pass──▶ archived (name kept for dedupe)
+```
+
+From `tried`, an explicit decision moves the dinner to `regular`, to
+`archived`, or back to `quest_log`. Promotion to `regular` is **never
+automatic** (D-021).
+
+## API surface
+
+- JSON API under `/api/v1`, using the error body above for every failure.
+  It requires the session cookie and, for non-GET requests, the
+  `X-MealQuest-CSRF` header.
+- `/auth/*` — the OIDC flow.
+- `/media/*` — cached photos.
+- `/health` — liveness.
+- Everything else serves the embedded PWA with SPA fallback.
+
+## AI layer
+
+- **Provider interface** (`ai/llm`) with adapters for OpenAI-compatible,
+  Anthropic and Ollama endpoints, called over plain HTTP — no vendor SDKs.
+- **Skill harness** (`ai/harness`): prompt layering, validation of the
+  structured output, and **one repair retry**; if the repair also fails the
+  run fails — no silent fallback. Every call is recorded in `ai_runs`.
+
+## Recipe grounding
+
+- Dinners are grounded in real recipe pages found through recipe search
+  (`searxng` or `brave`, per `recipe_search_settings`).
+- Fetching is **SSRF-safe**.
+- Recipe data is parsed from schema.org `Recipe` JSON-LD.
+- Photos are cached locally (`photos`) and processed with
+  `golang.org/x/image`.
+
+## Scheduler
+
+Times are in the household timezone. The times of `day_of_reminder`
+(`reminder_time`) and `rotation_check` (`review_time`), and the day and time
+of `suggestions_weekly`, `plan_draft` and `shopping_chat_autosend`, are
+household settings in `household_settings` (`dinner_time` is also stored
+there); the table shows their defaults. `week_rollover` and the `ai_runs`
+purge are fixed and not configurable.
+
+| Schedule | When | Does |
+| --- | --- | --- |
+| `suggestions_weekly` | Sunday 08:00 | New batch of Side quests. |
+| `plan_draft` | Friday 09:00 | AI drafts next week's plan. |
+| `shopping_chat_autosend` | Saturday 08:00 | Sends the Supply run to Google Chat. |
+| `day_of_reminder` | Daily 16:30 | Tonight's dinner reminder. |
+| `rotation_check` | Daily 09:00 | Asks "Keep it in rotation?" for tried dinners. |
+| `week_rollover` | Monday 00:05 (fixed) | Freezes last week's Supply run into the Supply archive. |
+| `ai_runs` purge | Daily (fixed) | Deletes `ai_runs` rows past 30-day retention. |
+
+Runs are recorded in `schedule_runs` and executed through `jobs`.
+
+## Notifications
+
+- Events: `plan_ready`, `list_ready`, `day_of_reminder`, `new_suggestions`,
+  `rotation_review`; each can go to Web Push and/or Google Chat per
+  `notification_prefs`.
+- Delivery goes through `notification_outbox`.
+- Web Push uses VAPID (`webpush-go`). Google Chat uses the stored incoming
+  webhook.
+
+## Google Calendar
+
+- One-way push: the app is the source of truth; edits in Google Calendar do
+  not come back.
+- Connected once with Google OAuth in Settings; one event per planned
+  dinner.
+- **Deterministic event ids**; each plan day records its
+  `calendar_event_id`.
+
+## Frontend
+
+- React 19, TypeScript, Vite, React Router 7, TanStack Query 5,
+  `vite-plugin-pwa`, `lucide-react` (stroke 2.75).
+- CSS Modules over `tokens.css`; no Tailwind, no Framer Motion, no
+  hard-coded hex. Self-hosted Bagel Fat One and Figtree.
+- Tests: Vitest, Testing Library, jsdom.
+- Offline cache for the current plan, shopping list and recipes.
+
+| Route | Screen |
+| --- | --- |
+| `/` | Home |
+| `/onboarding` | Add regular dinners |
+| `/plan` | Weekly plan (Draft board) |
+| `/plan/shopping` | Shopping list (Supply run) |
+| `/plan/shopping/archive` | Supply archive |
+| `/discover` | Swipe suggestions (Side quests) |
+| `/quick` | Quick dinners |
+| `/cook-from-pantry` | Cook from what I have |
+| `/dinner/:id` | Recipe / dinner detail (Kitchen reading mode on tablet) |
+| `/settings` | Settings home |
+| `/settings/household` | Household settings |
+| `/settings/regulars` | Regular dinners |
+| `/settings/calendar` | Google Calendar connection |
+| `/settings/notifications` | Notifications and Google Chat |
+| `/settings/ai` | Admin · AI assistant |
+
+`/settings/household`, `/settings/regulars` and `/settings/calendar` come from
+CTXD; the other routes are defined in the UI handoff.
+
+## Boundaries and gotchas
+
+- **Secrets never reach the client** — API keys, OAuth tokens and the Chat
+  webhook stay server-side, encrypted at rest.
+- **The app is the source of truth for the plan**; Google Calendar is a
+  one-way mirror.
+- **Never auto-promote to Regular** (D-021).
+- **Quick** means cooked in a crock pot **or** ≤ 30 minutes prep to table.
+- **Weeks run Monday–Sunday** in the household timezone.
+- **No silent fallbacks** — fail fast and report.
+- **iOS Web Push requires the PWA to be installed to the Home Screen**; show
+  an install hint.

go.mod

@@ -1,5 +1,21 @@
 module code.cubecraftlabs.com/Maniacs_Mansion/Meal-Quest
 
-go 1.25
+go 1.25.0
 
-require github.com/go-chi/chi/v5 v5.3.1
+require (
+	github.com/go-chi/chi/v5 v5.3.1
+	github.com/oklog/ulid/v2 v2.1.2
+	modernc.org/sqlite v1.59.0
+)
+
+require (
+	github.com/dustin/go-humanize v1.0.1 // indirect
+	github.com/google/uuid v1.6.0 // indirect
+	github.com/mattn/go-isatty v0.0.24 // indirect
+	github.com/ncruces/go-strftime v1.0.0 // indirect
+	github.com/remyoudompheng/bigfft v0.0.0-20230129092748-24d4a6f8daec // indirect
+	golang.org/x/sys v0.47.0 // indirect
+	modernc.org/libc v1.75.7 // indirect
+	modernc.org/mathutil v1.7.1 // indirect
+	modernc.org/memory v1.12.1 // indirect
+)

go.sum

@@ -1,2 +1,55 @@
+github.com/dustin/go-humanize v1.0.1 h1:GzkhY7T5VNhEkwH0PVJgjz+fX1rhBrR7pRT3mDkpeCY=
+github.com/dustin/go-humanize v1.0.1/go.mod h1:Mu1zIs6XwVuF/gI1OepvI0qD18qycQx+mFykh5fBlto=
 github.com/go-chi/chi/v5 v5.3.1 h1:3j4HZLGZQ3JpMCrPJF/Jl3mYJfWLKBfNJ6quurUGCf8=
 github.com/go-chi/chi/v5 v5.3.1/go.mod h1:R+tYY2hNuVUUjxoPtqUdgBqevM9s9njzkTLutVsOCto=
+github.com/google/pprof v0.0.0-20260802141513-ef3492d7dac3 h1:LMLX+LgTNWpfvCBdFebv6EsYotImrt/Ppc5cXIriCSo=
+github.com/google/pprof v0.0.0-20260802141513-ef3492d7dac3/go.mod h1:jl5iWTm0/hd5PjEYEOuwAJ57L/CibdZfrqZ5XA5GrCk=
+github.com/google/uuid v1.6.0 h1:NIvaJDMOsjHA8n1jAhLSgzrAzy1Hgr+hNrb57e+94F0=
+github.com/google/uuid v1.6.0/go.mod h1:TIyPZe4MgqvfeYDBFedMoGGpEw/LqOeaOT+nhxU+yHo=
+github.com/hashicorp/golang-lru/v2 v2.0.7 h1:a+bsQ5rvGLjzHuww6tVxozPZFVghXaHOwFs4luLUK2k=
+github.com/hashicorp/golang-lru/v2 v2.0.7/go.mod h1:QeFd9opnmA6QUJc5vARoKUSoFhyfM2/ZepoAG6RGpeM=
+github.com/mattn/go-isatty v0.0.24 h1:tGZZoVgT/KiqK1c8ocVLeDS8BSWMRd47J3Lbz7vsReI=
+github.com/mattn/go-isatty v0.0.24/go.mod h1:nMCL3Zebbrt45jsMDgnfIwz6ydEQApk5oEI3HqDio6A=
+github.com/ncruces/go-strftime v1.0.0 h1:HMFp8mLCTPp341M/ZnA4qaf7ZlsbTc+miZjCLOFAw7w=
+github.com/ncruces/go-strftime v1.0.0/go.mod h1:Fwc5htZGVVkseilnfgOVb9mKy6w1naJmn9CehxcKcls=
+github.com/oklog/ulid/v2 v2.1.2 h1:IEclFb9JNvzYA6MW2SCxbLzcHTVsfqm3PrqGQJH5zec=
+github.com/oklog/ulid/v2 v2.1.2/go.mod h1:rcEKHmBBKfef9DhnvX7y1HZBYxjXb0cP5ExxNsTT1QQ=
+github.com/pborman/getopt v0.0.0-20170112200414-7148bc3a4c30/go.mod h1:85jBQOZwpVEaDAr341tbn15RS4fCAsIst0qp7i8ex1o=
+github.com/remyoudompheng/bigfft v0.0.0-20230129092748-24d4a6f8daec h1:W09IVJc94icq4NjY3clb7Lk8O1qJ8BdBEF8z0ibU0rE=
+github.com/remyoudompheng/bigfft v0.0.0-20230129092748-24d4a6f8daec/go.mod h1:qqbHyh8v60DhA7CoWK5oRCqLrMHRGoxYCSS9EjAz6Eo=
+golang.org/x/mod v0.38.0 h1:MECBjubtXD7yj4HrhIUcywNaGeNVUdfVnxmPajOk4yk=
+golang.org/x/mod v0.38.0/go.mod h1:V6Xz0pq8TQ3dGqVQ1FVHuelZpAL0uNhSkk9ogYP3c40=
+golang.org/x/sync v0.22.0 h1:SZjpbeLmrCk4xhRSZFNZW5gFUeCeFgjekvI/+gfScek=
+golang.org/x/sync v0.22.0/go.mod h1:9xrNwdLfx4jkKbNva9FpL6vEN7evnE43NNNJQ2LF3+0=
+golang.org/x/sys v0.47.0 h1:o7XGOvZQCADBQQ4Y7VNq2dRWQR7JmOUW8Kxx4ZsNgWs=
+golang.org/x/sys v0.47.0/go.mod h1:4GL1E5IUh+htKOUEOaiffhrAeqysfVGipDYzABqnCmw=
+golang.org/x/tools v0.48.0 h1:3+hClM1aLL5mjMKm5ovokw9epgRXPuu2tILgismM6RE=
+golang.org/x/tools v0.48.0/go.mod h1:08xX0orndb/F7jJxGDicx061tyd5pcMto75YMAXr6lk=
+modernc.org/cc/v4 v4.29.2 h1:h6+9ciCnPKutf4I03CvheAvDLX7+IHlqR6Iy6J+cgd8=
+modernc.org/cc/v4 v4.29.2/go.mod h1:OnovgIhbbMXMu1aISnJ0wvVD1KnW+cAUJkIrAWh+kVI=
+modernc.org/ccgo/v4 v4.35.0 h1:F+TUsmw09QxLzmi3aeYYGxjAXarmZaKgj3mKQHNaA8w=
+modernc.org/ccgo/v4 v4.35.0/go.mod h1:qrVGs9S3Sr2Ztcg9ve+kTAYMp5a3YvWjo+SoN06kJ5I=
+modernc.org/fileutil v1.4.0 h1:j6ZzNTftVS054gi281TyLjHPp6CPHr2KCxEXjEbD6SM=
+modernc.org/fileutil v1.4.0/go.mod h1:EqdKFDxiByqxLk8ozOxObDSfcVOv/54xDs/DUHdvCUU=
+modernc.org/gc/v2 v2.6.5 h1:nyqdV8q46KvTpZlsw66kWqwXRHdjIlJOhG6kxiV/9xI=
+modernc.org/gc/v2 v2.6.5/go.mod h1:YgIahr1ypgfe7chRuJi2gD7DBQiKSLMPgBQe9oIiito=
+modernc.org/gc/v3 v3.1.5 h1:21ldfPfRYE31Tb7B3mwAK8gy1AxP4+dKjrOQPfqakoc=
+modernc.org/gc/v3 v3.1.5/go.mod h1:HFK/6AGESC7Ex+EZJhJ2Gni6cTaYpSMmU/cT9RmlfYY=
+modernc.org/goabi0 v0.2.0 h1:HvEowk7LxcPd0eq6mVOAEMai46V+i7Jrj13t4AzuNks=
+modernc.org/goabi0 v0.2.0/go.mod h1:CEFRnnJhKvWT1c1JTI3Avm+tgOWbkOu5oPA8eH8LnMI=
+modernc.org/libc v1.75.7 h1:o3DTP9/0p9pKmY2WCKQaySW6wIiZhNM7wc2lUoyhfew=
+modernc.org/libc v1.75.7/go.mod h1:bO5o2ztHxBb2rjz0PgdHN0sSMw57CgxGFLZ3Qd/QpVQ=
+modernc.org/mathutil v1.7.1 h1:GCZVGXdaN8gTqB1Mf/usp1Y/hSqgI2vAGGP4jZMCxOU=
+modernc.org/mathutil v1.7.1/go.mod h1:4p5IwJITfppl0G4sUEDtCr4DthTaT47/N3aT6MhfgJg=
+modernc.org/memory v1.12.1 h1:nFMiWrpStgZczNl6XI9GnIk/rWhYIyHGUaR04pGbp9g=
+modernc.org/memory v1.12.1/go.mod h1:/JP4VbVC+K5sU2wZi9bHoq2MAkCnrt2r98UGeSK7Mjw=
+modernc.org/opt v0.2.0 h1:tGyef5ApycA7FSEOMraay9SaTk5zmbx7Tu+cJs4QKZg=
+modernc.org/opt v0.2.0/go.mod h1:03fq9lsNfvkYSfxrfUhZCWPk1lm4cq4N+Bh//bEtgns=
+modernc.org/sortutil v1.2.1 h1:+xyoGf15mM3NMlPDnFqrteY07klSFxLElE2PVuWIJ7w=
+modernc.org/sortutil v1.2.1/go.mod h1:7ZI3a3REbai7gzCLcotuw9AC4VZVpYMjDzETGsSMqJE=
+modernc.org/sqlite v1.59.0 h1:X1es1GpqBlS/5T+vbM4HLUdaa8OtQx468DF2vrx+38A=
+modernc.org/sqlite v1.59.0/go.mod h1:+paeT2A3iPRHkQDwG7oA6Tk0zQd5woMEI8q7orfry8k=
+modernc.org/strutil v1.2.1 h1:UneZBkQA+DX2Rp35KcM69cSsNES9ly8mQWD71HKlOA0=
+modernc.org/strutil v1.2.1/go.mod h1:EHkiggD70koQxjVdSBM3JKM7k6L0FbGE5eymy9i3B9A=
+modernc.org/token v1.1.0 h1:Xl7Ap9dKaEs5kLoOQeQmPWevfnk/DM5qcLcYlA8ys6Y=
+modernc.org/token v1.1.0/go.mod h1:UGzOrNV1mAFSEB63lOFHIpNRUVMvYTc6yu1SMY/XTDM=

internal/frontend/dist/index.html

@@ -0,0 +1,11 @@
+<!doctype html>
+<html lang="en">
+  <head>
+    <meta charset="UTF-8" />
+    <title>MealQuest</title>
+  </head>
+  <body>
+    <!-- Placeholder so the Go embed compiles. The build replaces this directory with web/dist. -->
+    <p>MealQuest web assets have not been built.</p>
+  </body>
+</html>

internal/frontend/frontend.go

@@ -0,0 +1,127 @@
+// Package frontend serves the embedded PWA build with an SPA fallback.
+package frontend
+
+import (
+	"embed"
+	"errors"
+	"fmt"
+	"io"
+	"io/fs"
+	"log/slog"
+	"net/http"
+	"path"
+	"strings"
+	"time"
+)
+
+// embedded holds the web build. The build copies web/dist over the committed
+// placeholder index.html.
+//
+//go:embed all:dist
+var embedded embed.FS
+
+const (
+	indexFile = "index.html"
+
+	cacheImmutable = "public, max-age=31536000, immutable"
+	cacheNoCache   = "no-cache"
+)
+
+// noCacheFiles must be revalidated on every load: they name the hashed assets
+// (or, for the service worker, control their caching), so a stale copy pins an
+// old release.
+var noCacheFiles = map[string]bool{
+	indexFile:              true,
+	"sw.js":                true,
+	"manifest.webmanifest": true,
+}
+
+// Assets returns the embedded web build rooted at its dist directory.
+func Assets() fs.FS {
+	sub, err := fs.Sub(embedded, "dist")
+	if err != nil {
+		panic(err) // "dist" is a valid path, so Sub cannot fail
+	}
+	return sub
+}
+
+// NewHandler returns a handler that serves files from fsys. A GET or HEAD for a
+// path that is not a file in fsys is answered with index.html so client-side
+// routes load the app. Paths equal to or under a reserved prefix (for example
+// "/api") are never answered with index.html, whether before or after the path
+// is cleaned; they are passed to notFound.
+// fsys must contain index.html.
+func NewHandler(fsys fs.FS, logger *slog.Logger, notFound http.Handler, reserved ...string) (http.Handler, error) {
+	if _, err := fs.Stat(fsys, indexFile); err != nil {
+		return nil, fmt.Errorf("frontend assets: %w", err)
+	}
+	return &handler{fsys: fsys, logger: logger, notFound: notFound, reserved: reserved}, nil
+}
+
+type handler struct {
+	fsys     fs.FS
+	logger   *slog.Logger
+	notFound http.Handler
+	reserved []string
+}
+
+func (h *handler) ServeHTTP(w http.ResponseWriter, r *http.Request) {
+	urlPath := path.Clean("/" + r.URL.Path)
+	// Check the uncleaned path too, so dot segments cannot escape a reserved
+	// prefix into the fallback ("/auth/../quests/42" cleans to "/quests/42").
+	if h.isReserved(r.URL.Path) || h.isReserved(urlPath) {
+		h.notFound.ServeHTTP(w, r)
+		return
+	}
+
+	name := strings.TrimPrefix(urlPath, "/")
+	if name == "" || !h.isFile(name) {
+		name = indexFile
+	}
+	h.serveFile(w, r, name)
+}
+
+func (h *handler) isReserved(urlPath string) bool {
+	for _, prefix := range h.reserved {
+		if urlPath == prefix || strings.HasPrefix(urlPath, prefix+"/") {
+			return true
+		}
+	}
+	return false
+}
+
+func (h *handler) isFile(name string) bool {
+	info, err := fs.Stat(h.fsys, name)
+	return err == nil && info.Mode().IsRegular()
+}
+
+func (h *handler) serveFile(w http.ResponseWriter, r *http.Request, name string) {
+	f, err := h.fsys.Open(name)
+	if err != nil {
+		h.fail(w, r, name, err)
+		return
+	}
+	defer f.Close()
+	content, ok := f.(io.ReadSeeker)
+	if !ok {
+		h.fail(w, r, name, errors.New("file does not implement io.Seeker"))
+		return
+	}
+
+	if noCacheFiles[name] {
+		w.Header().Set("Cache-Control", cacheNoCache)
+	} else {
+		w.Header().Set("Cache-Control", cacheImmutable)
+	}
+	if path.Ext(name) == ".webmanifest" {
+		w.Header().Set("Content-Type", "application/manifest+json")
+	}
+	// Embedded files carry no modification time; ServeContent omits
+	// Last-Modified for the zero time.
+	http.ServeContent(w, r, name, time.Time{}, content)
+}
+
+func (h *handler) fail(w http.ResponseWriter, r *http.Request, name string, err error) {
+	h.logger.ErrorContext(r.Context(), "serve frontend asset", "file", name, "error", err)
+	http.Error(w, http.StatusText(http.StatusInternalServerError), http.StatusInternalServerError)
+}

internal/frontend/frontend_test.go

@@ -0,0 +1,137 @@
+package frontend
+
+import (
+	"io"
+	"log/slog"
+	"net/http"
+	"net/http/httptest"
+	"testing"
+	"testing/fstest"
+)
+
+const (
+	indexBody    = "<!doctype html><title>fixture index</title>"
+	assetBody    = "console.log('fixture asset')"
+	robotsBody   = "User-agent: *"
+	swBody       = "self.addEventListener('fetch', () => {})"
+	manifestBody = `{"name":"MealQuest"}`
+	notFoundBody = "reserved not found"
+)
+
+func fixture() fstest.MapFS {
+	return fstest.MapFS{
+		"index.html":             {Data: []byte(indexBody)},
+		"sw.js":                  {Data: []byte(swBody)},
+		"manifest.webmanifest":   {Data: []byte(manifestBody)},
+		"assets/index-AbC123.js": {Data: []byte(assetBody)},
+		"robots.txt":             {Data: []byte(robotsBody)},
+	}
+}
+
+func newTestHandler(t *testing.T) http.Handler {
+	t.Helper()
+	logger := slog.New(slog.NewTextHandler(io.Discard, nil))
+	notFound := http.HandlerFunc(func(w http.ResponseWriter, r *http.Request) {
+		http.Error(w, notFoundBody, http.StatusNotFound)
+	})
+	h, err := NewHandler(fixture(), logger, notFound, "/api", "/auth", "/media", "/health")
+	if err != nil {
+		t.Fatal(err)
+	}
+	return h
+}
+
+func TestServe(t *testing.T) {
+	t.Parallel()
+
+	tests := []struct {
+		name      string
+		method    string
+		path      string
+		wantBody  string
+		wantCache string
+	}{
+		{"hashed asset", http.MethodGet, "/assets/index-AbC123.js", assetBody, cacheImmutable},
+		{"unhashed asset", http.MethodGet, "/robots.txt", robotsBody, cacheImmutable},
+		{"root", http.MethodGet, "/", indexBody, cacheNoCache},
+		{"index.html", http.MethodGet, "/index.html", indexBody, cacheNoCache},
+		{"service worker", http.MethodGet, "/sw.js", swBody, cacheNoCache},
+		{"manifest", http.MethodGet, "/manifest.webmanifest", manifestBody, cacheNoCache},
+		{"client route", http.MethodGet, "/quests/42", indexBody, cacheNoCache},
+		{"missing asset", http.MethodGet, "/assets/missing.js", indexBody, cacheNoCache},
+		{"directory", http.MethodGet, "/assets", indexBody, cacheNoCache},
+		{"dot segments", http.MethodGet, "/x/../../sw.js", swBody, cacheNoCache},
+		{"prefix lookalike", http.MethodGet, "/apiary", indexBody, cacheNoCache},
+		{"head", http.MethodHead, "/assets/index-AbC123.js", "", cacheImmutable},
+	}
+	h := newTestHandler(t)
+	for _, tt := range tests {
+		t.Run(tt.name, func(t *testing.T) {
+			t.Parallel()
+
+			rec := httptest.NewRecorder()
+			h.ServeHTTP(rec, httptest.NewRequest(tt.method, tt.path, nil))
+
+			if rec.Code != http.StatusOK {
+				t.Errorf("status = %d, want 200", rec.Code)
+			}
+			if got := rec.Body.String(); got != tt.wantBody {
+				t.Errorf("body = %q, want %q", got, tt.wantBody)
+			}
+			if got := rec.Header().Get("Cache-Control"); got != tt.wantCache {
+				t.Errorf("Cache-Control = %q, want %q", got, tt.wantCache)
+			}
+		})
+	}
+}
+
+func TestManifestContentType(t *testing.T) {
+	t.Parallel()
+
+	rec := httptest.NewRecorder()
+	newTestHandler(t).ServeHTTP(rec, httptest.NewRequest(http.MethodGet, "/manifest.webmanifest", nil))
+
+	if got := rec.Header().Get("Content-Type"); got != "application/manifest+json" {
+		t.Errorf("Content-Type = %q, want application/manifest+json", got)
+	}
+}
+
+func TestReservedPrefixesNotServed(t *testing.T) {
+	t.Parallel()
+
+	h := newTestHandler(t)
+	for _, path := range []string{"/api", "/api/unknown", "/auth/login", "/media/x.jpg", "/health", "/health/deep", "/x/../api/unknown", "/auth/../quests/42", "/api%2F..%2Fquests/42"} {
+		t.Run(path, func(t *testing.T) {
+			t.Parallel()
+
+			rec := httptest.NewRecorder()
+			h.ServeHTTP(rec, httptest.NewRequest(http.MethodGet, path, nil))
+
+			if rec.Code != http.StatusNotFound {
+				t.Errorf("status = %d, want 404", rec.Code)
+			}
+			if got := rec.Body.String(); got != notFoundBody+"\n" {
+				t.Errorf("body = %q, want the notFound handler's body", got)
+			}
+		})
+	}
+}
+
+func TestNewHandlerRequiresIndex(t *testing.T) {
+	t.Parallel()
+
+	logger := slog.New(slog.NewTextHandler(io.Discard, nil))
+	fsys := fstest.MapFS{"sw.js": {Data: []byte(swBody)}}
+	if _, err := NewHandler(fsys, logger, http.NotFoundHandler()); err == nil {
+		t.Fatal("NewHandler without index.html: want error, got nil")
+	}
+}
+
+func TestAssetsContainsIndex(t *testing.T) {
+	t.Parallel()
+
+	logger := slog.New(slog.NewTextHandler(io.Discard, nil))
+	if _, err := NewHandler(Assets(), logger, http.NotFoundHandler()); err != nil {
+		t.Fatalf("embedded assets: %v", err)
+	}
+}

internal/server/server.go

@@ -3,20 +3,44 @@ package server
 
 import (
 	"encoding/json"
+	"io/fs"
 	"log/slog"
 	"net/http"
 
 	"github.com/go-chi/chi/v5"
+
+	"code.cubecraftlabs.com/Maniacs_Mansion/Meal-Quest/internal/frontend"
 )
 
+// reservedPrefixes are owned by the API router and are never answered with the
+// SPA's index.html.
+var reservedPrefixes = []string{"/api", "/auth", "/media", "/health"}
+
 // NewRouter returns the HTTP handler for the service. version is reported by
-// GET /health.
-func NewRouter(logger *slog.Logger, version string) http.Handler {
+// GET /health; assets is the web build served for every other GET.
+func NewRouter(logger *slog.Logger, version string, assets fs.FS) (http.Handler, error) {
+	notFound := apiErrorHandler(logger, http.StatusNotFound, "not_found", "not found")
+	methodNotAllowed := apiErrorHandler(logger, http.StatusMethodNotAllowed, "method_not_allowed", "method not allowed")
+
+	web, err := frontend.NewHandler(assets, logger, notFound, reservedPrefixes...)
+	if err != nil {
+		return nil, err
+	}
+
 	r := chi.NewRouter()
+	r.MethodNotAllowed(methodNotAllowed)
+	r.NotFound(notFound)
+
 	r.Get("/health", health(logger, version))
-	r.MethodNotAllowed(apiErrorHandler(logger, http.StatusMethodNotAllowed, "method_not_allowed", "method not allowed"))
-	r.NotFound(apiErrorHandler(logger, http.StatusNotFound, "not_found", "not found"))
-	return r
+	r.Route("/api", func(r chi.Router) {
+		r.MethodNotAllowed(methodNotAllowed)
+		r.NotFound(notFound)
+	})
+	// Catch-all for the PWA. /auth and /media have no routes yet; the frontend
+	// handler passes them to notFound rather than the SPA fallback.
+	r.Method(http.MethodGet, "/*", web)
+	r.Method(http.MethodHead, "/*", web)
+	return r, nil
 }
 
 type errorBody struct {

internal/server/server_test.go

@@ -7,14 +7,35 @@ import (
 	"maps"
 	"net/http"
 	"net/http/httptest"
+	"strings"
 	"testing"
+	"testing/fstest"
 )
 
+const (
+	fixtureIndex = "<!doctype html><title>fixture index</title>"
+	fixtureAsset = "console.log('fixture asset')"
+	notFoundJSON = `{"error":{"code":"not_found","message":"not found","retryable":false}}`
+)
+
+func newTestRouter(t *testing.T, version string) http.Handler {
+	t.Helper()
+	logger := slog.New(slog.NewTextHandler(io.Discard, nil))
+	assets := fstest.MapFS{
+		"index.html":             {Data: []byte(fixtureIndex)},
+		"assets/index-AbC123.js": {Data: []byte(fixtureAsset)},
+	}
+	r, err := NewRouter(logger, version, assets)
+	if err != nil {
+		t.Fatal(err)
+	}
+	return r
+}
+
 func TestHealth(t *testing.T) {
 	t.Parallel()
 
-	logger := slog.New(slog.NewTextHandler(io.Discard, nil))
-	srv := httptest.NewServer(NewRouter(logger, "1.2.3"))
+	srv := httptest.NewServer(newTestRouter(t, "1.2.3"))
 	t.Cleanup(srv.Close)
 
 	resp, err := http.Get(srv.URL + "/health")
@@ -42,9 +63,8 @@ func TestHealth(t *testing.T) {
 func TestHealthRejectsOtherMethods(t *testing.T) {
 	t.Parallel()
 
-	logger := slog.New(slog.NewTextHandler(io.Discard, nil))
 	rec := httptest.NewRecorder()
-	NewRouter(logger, "dev").ServeHTTP(rec, httptest.NewRequest(http.MethodPost, "/health", nil))
+	newTestRouter(t, "dev").ServeHTTP(rec, httptest.NewRequest(http.MethodPost, "/health", nil))
 
 	if rec.Code != http.StatusMethodNotAllowed {
 		t.Errorf("status = %d, want 405", rec.Code)
@@ -68,21 +88,98 @@ func TestHealthRejectsOtherMethods(t *testing.T) {
 	}
 }
 
-func TestUnknownPathNotFound(t *testing.T) {
+func TestReservedPathsNotFound(t *testing.T) {
 	t.Parallel()
 
-	logger := slog.New(slog.NewTextHandler(io.Discard, nil))
-	rec := httptest.NewRecorder()
-	NewRouter(logger, "dev").ServeHTTP(rec, httptest.NewRequest(http.MethodGet, "/unknown", nil))
+	router := newTestRouter(t, "dev")
+	tests := []struct {
+		method string
+		path   string
+	}{
+		{http.MethodGet, "/api"},
+		{http.MethodGet, "/api/unknown"},
+		{http.MethodPost, "/api/unknown"},
+		{http.MethodGet, "/auth/callback"},
+		{http.MethodGet, "/media/photo.jpg"},
+		{http.MethodGet, "/health/deep"},
+		{http.MethodGet, "/auth/../quests/42"},
+		{http.MethodGet, "/api%2F..%2Fquests/42"},
+	}
+	for _, tt := range tests {
+		t.Run(tt.method+" "+tt.path, func(t *testing.T) {
+			t.Parallel()
 
-	if rec.Code != http.StatusNotFound {
-		t.Errorf("status = %d, want 404", rec.Code)
+			rec := httptest.NewRecorder()
+			router.ServeHTTP(rec, httptest.NewRequest(tt.method, tt.path, nil))
+
+			if rec.Code != http.StatusNotFound {
+				t.Errorf("status = %d, want 404", rec.Code)
+			}
+			if ct := rec.Header().Get("Content-Type"); ct != "application/json" {
+				t.Errorf("Content-Type = %q, want application/json", ct)
+			}
+			if got := rec.Body.String(); got != notFoundJSON {
+				t.Errorf("body = %s, want %s", got, notFoundJSON)
+			}
+		})
 	}
-	if ct := rec.Header().Get("Content-Type"); ct != "application/json" {
-		t.Errorf("Content-Type = %q, want application/json", ct)
+}
+
+func TestFrontend(t *testing.T) {
+	t.Parallel()
+
+	router := newTestRouter(t, "dev")
+	tests := []struct {
+		path      string
+		wantBody  string
+		wantCache string
+	}{
+		{"/", fixtureIndex, "no-cache"},
+		{"/assets/index-AbC123.js", fixtureAsset, "public, max-age=31536000, immutable"},
+		{"/quests/42", fixtureIndex, "no-cache"},
+		{"/quests/42/edit", fixtureIndex, "no-cache"},
 	}
-	const want = `{"error":{"code":"not_found","message":"not found","retryable":false}}`
-	if got := rec.Body.String(); got != want {
-		t.Errorf("body = %s, want %s", got, want)
+	for _, tt := range tests {
+		t.Run(tt.path, func(t *testing.T) {
+			t.Parallel()
+
+			rec := httptest.NewRecorder()
+			router.ServeHTTP(rec, httptest.NewRequest(http.MethodGet, tt.path, nil))
+
+			if rec.Code != http.StatusOK {
+				t.Errorf("status = %d, want 200", rec.Code)
+			}
+			if got := rec.Body.String(); got != tt.wantBody {
+				t.Errorf("body = %q, want %q", got, tt.wantBody)
+			}
+			if got := rec.Header().Get("Cache-Control"); got != tt.wantCache {
+				t.Errorf("Cache-Control = %q, want %q", got, tt.wantCache)
+			}
+		})
+	}
+}
+
+func TestHealthNotShadowed(t *testing.T) {
+	t.Parallel()
+
+	rec := httptest.NewRecorder()
+	newTestRouter(t, "dev").ServeHTTP(rec, httptest.NewRequest(http.MethodGet, "/health", nil))
+
+	if body := rec.Body.String(); rec.Code != http.StatusOK || strings.Contains(body, fixtureIndex) {
+		t.Errorf("GET /health = %d %q, want 200 health JSON", rec.Code, body)
+	}
+}
+
+func TestFrontendRejectsOtherMethods(t *testing.T) {
+	t.Parallel()
+
+	rec := httptest.NewRecorder()
+	newTestRouter(t, "dev").ServeHTTP(rec, httptest.NewRequest(http.MethodPost, "/quests/42", nil))
+
+	if rec.Code != http.StatusMethodNotAllowed {
+		t.Errorf("status = %d, want 405", rec.Code)
+	}
+	if strings.Contains(rec.Body.String(), fixtureIndex) {
+		t.Errorf("body = %q, want no index.html", rec.Body.String())
 	}
 }

internal/store/id.go

@@ -0,0 +1,27 @@
+package store
+
+import (
+	"crypto/rand"
+	"sync"
+	"time"
+
+	"github.com/oklog/ulid/v2"
+)
+
+var (
+	idMu      sync.Mutex
+	idEntropy = ulid.Monotonic(rand.Reader, 0)
+	idLastMS  uint64
+)
+
+// NewID returns a new ULID as its canonical 26-character string. IDs from
+// this process sort strictly in creation order, including within one
+// millisecond and across a backwards wall-clock step. It panics if the
+// monotonic entropy overflows within a millisecond.
+func NewID() string {
+	idMu.Lock()
+	defer idMu.Unlock()
+	ms := max(ulid.Timestamp(time.Now()), idLastMS)
+	idLastMS = ms
+	return ulid.MustNew(ms, idEntropy).String()
+}

internal/store/id_test.go

@@ -0,0 +1,25 @@
+package store
+
+import (
+	"testing"
+
+	"github.com/oklog/ulid/v2"
+)
+
+func TestNewIDStrictlyIncreasing(t *testing.T) {
+	const n = 10000
+	prev := NewID()
+	for range n {
+		id := NewID()
+		if len(id) != ulid.EncodedSize {
+			t.Fatalf("NewID() = %q, want %d characters", id, ulid.EncodedSize)
+		}
+		if _, err := ulid.ParseStrict(id); err != nil {
+			t.Fatalf("NewID() = %q: %v", id, err)
+		}
+		if id <= prev {
+			t.Fatalf("NewID() = %q, not after previous %q", id, prev)
+		}
+		prev = id
+	}
+}

internal/store/migrate.go

@@ -0,0 +1,105 @@
+package store
+
+import (
+	"context"
+	"database/sql"
+	"fmt"
+	"io/fs"
+	"strings"
+	"time"
+)
+
+const createSchemaMigrations = `CREATE TABLE IF NOT EXISTS schema_migrations (
+	version    TEXT PRIMARY KEY,
+	applied_at TEXT NOT NULL
+)`
+
+// Migrate applies every *.sql file at the root of migrations that is not yet
+// recorded in schema_migrations, in filename order. Each migration runs in its
+// own transaction together with the insert that records it, so a failing
+// migration leaves the database at the previous version. A version is the
+// file name without its .sql extension.
+//
+// The database must not hold a version unknown to migrations: that means it
+// was migrated by a newer build, and Migrate refuses to continue.
+func Migrate(ctx context.Context, db *sql.DB, migrations fs.FS) error {
+	files, err := fs.Glob(migrations, "*.sql")
+	if err != nil {
+		return fmt.Errorf("migrate: list migrations: %w", err)
+	}
+	if len(files) == 0 {
+		return fmt.Errorf("migrate: no migrations found")
+	}
+
+	if _, err := db.ExecContext(ctx, createSchemaMigrations); err != nil {
+		return fmt.Errorf("migrate: create schema_migrations: %w", err)
+	}
+	applied, err := appliedVersions(ctx, db)
+	if err != nil {
+		return err
+	}
+
+	known := make(map[string]bool, len(files))
+	for _, name := range files {
+		known[strings.TrimSuffix(name, ".sql")] = true
+	}
+	for version := range applied {
+		if !known[version] {
+			return fmt.Errorf("migrate: database has version %q, which this build does not know", version)
+		}
+	}
+
+	for _, name := range files { // fs.Glob returns names in lexical order
+		version := strings.TrimSuffix(name, ".sql")
+		if applied[version] {
+			continue
+		}
+		body, err := fs.ReadFile(migrations, name)
+		if err != nil {
+			return fmt.Errorf("migrate: read %s: %w", name, err)
+		}
+		if err := apply(ctx, db, version, string(body)); err != nil {
+			return fmt.Errorf("migrate: apply %s: %w", name, err)
+		}
+	}
+	return nil
+}
+
+func appliedVersions(ctx context.Context, db *sql.DB) (map[string]bool, error) {
+	rows, err := db.QueryContext(ctx, `SELECT version FROM schema_migrations`)
+	if err != nil {
+		return nil, fmt.Errorf("migrate: read schema_migrations: %w", err)
+	}
+	defer rows.Close()
+	applied := make(map[string]bool)
+	for rows.Next() {
+		var v string
+		if err := rows.Scan(&v); err != nil {
+			return nil, fmt.Errorf("migrate: read schema_migrations: %w", err)
+		}
+		applied[v] = true
+	}
+	if err := rows.Err(); err != nil {
+		return nil, fmt.Errorf("migrate: read schema_migrations: %w", err)
+	}
+	return applied, nil
+}
+
+func apply(ctx context.Context, db *sql.DB, version, body string) error {
+	tx, err := db.BeginTx(ctx, nil)
+	if err != nil {
+		return err
+	}
+	defer tx.Rollback() // no-op once committed
+
+	if _, err := tx.ExecContext(ctx, body); err != nil {
+		return err
+	}
+	if _, err := tx.ExecContext(ctx,
+		`INSERT INTO schema_migrations (version, applied_at) VALUES (?, ?)`,
+		version, time.Now().UTC().Format(time.RFC3339),
+	); err != nil {
+		return err
+	}
+	return tx.Commit()
+}

internal/store/migrate_test.go

@@ -0,0 +1,203 @@
+package store
+
+import (
+	"context"
+	"io/fs"
+	"strings"
+	"testing"
+	"testing/fstest"
+	"time"
+
+	"code.cubecraftlabs.com/Maniacs_Mansion/Meal-Quest/migrations"
+)
+
+var testMigrations = fstest.MapFS{
+	"0001_first.sql":  {Data: []byte("CREATE TABLE first (id INTEGER PRIMARY KEY);")},
+	"0002_second.sql": {Data: []byte("CREATE TABLE second (id INTEGER PRIMARY KEY);\nCREATE TABLE third (id INTEGER PRIMARY KEY);")},
+	"README.md":       {Data: []byte("not a migration")},
+}
+
+type migrationRow struct {
+	version   string
+	appliedAt string
+}
+
+func migrationRows(t *testing.T, s *Store) []migrationRow {
+	t.Helper()
+	rows, err := s.Reader.Query("SELECT version, applied_at FROM schema_migrations ORDER BY version")
+	if err != nil {
+		t.Fatal(err)
+	}
+	defer rows.Close()
+	var out []migrationRow
+	for rows.Next() {
+		var r migrationRow
+		if err := rows.Scan(&r.version, &r.appliedAt); err != nil {
+			t.Fatal(err)
+		}
+		out = append(out, r)
+	}
+	if err := rows.Err(); err != nil {
+		t.Fatal(err)
+	}
+	return out
+}
+
+func versions(rows []migrationRow) string {
+	vs := make([]string, len(rows))
+	for i, r := range rows {
+		vs[i] = r.version
+	}
+	return strings.Join(vs, ",")
+}
+
+func tableExists(t *testing.T, s *Store, name string) bool {
+	t.Helper()
+	var n int
+	if err := s.Reader.QueryRow("SELECT count(*) FROM sqlite_schema WHERE type = 'table' AND name = ?", name).Scan(&n); err != nil {
+		t.Fatal(err)
+	}
+	return n == 1
+}
+
+func TestMigrateFreshDatabaseReachesLatest(t *testing.T) {
+	s := openTestStore(t)
+	if err := Migrate(context.Background(), s.Writer, testMigrations); err != nil {
+		t.Fatal(err)
+	}
+	rows := migrationRows(t, s)
+	if got := versions(rows); got != "0001_first,0002_second" {
+		t.Fatalf("versions = %q", got)
+	}
+	for _, r := range rows {
+		if _, err := time.Parse(time.RFC3339, r.appliedAt); err != nil || !strings.HasSuffix(r.appliedAt, "Z") {
+			t.Errorf("applied_at %q is not UTC RFC 3339", r.appliedAt)
+		}
+	}
+	for _, table := range []string{"first", "second", "third"} {
+		if !tableExists(t, s, table) {
+			t.Errorf("table %s missing", table)
+		}
+	}
+}
+
+func TestMigrateSecondRunIsNoOp(t *testing.T) {
+	ctx := context.Background()
+	s := openTestStore(t)
+	if err := Migrate(ctx, s.Writer, testMigrations); err != nil {
+		t.Fatal(err)
+	}
+	before := migrationRows(t, s)
+	if err := Migrate(ctx, s.Writer, testMigrations); err != nil {
+		t.Fatalf("second run: %v", err)
+	}
+	after := migrationRows(t, s)
+	if len(after) != len(before) {
+		t.Fatalf("rows after second run = %v, want %v", after, before)
+	}
+	for i := range before {
+		if after[i] != before[i] {
+			t.Errorf("row %d changed: %v -> %v", i, before[i], after[i])
+		}
+	}
+}
+
+func TestMigrateBrokenMigrationRollsBack(t *testing.T) {
+	ctx := context.Background()
+	s := openTestStore(t)
+	if err := Migrate(ctx, s.Writer, testMigrations); err != nil {
+		t.Fatal(err)
+	}
+
+	broken := fstest.MapFS{"0003_broken.sql": {Data: []byte(
+		// The first statement succeeds; the second is invalid, so the
+		// table created by the first must be rolled back with it.
+		"CREATE TABLE fourth (id INTEGER PRIMARY KEY);\nCREATE TABLE oops (;",
+	)}}
+	for name, f := range testMigrations {
+		broken[name] = f
+	}
+	err := Migrate(ctx, s.Writer, broken)
+	if err == nil {
+		t.Fatal("broken migration returned no error")
+	}
+	if !strings.Contains(err.Error(), "0003_broken.sql") {
+		t.Errorf("error %q does not name the failing migration", err)
+	}
+	if got := versions(migrationRows(t, s)); got != "0001_first,0002_second" {
+		t.Errorf("versions after failure = %q, want previous version intact", got)
+	}
+	if tableExists(t, s, "fourth") {
+		t.Error("table from the failed migration was not rolled back")
+	}
+	for _, table := range []string{"first", "second", "third"} {
+		if !tableExists(t, s, table) {
+			t.Errorf("previous table %s missing after failed migration", table)
+		}
+	}
+}
+
+func TestMigrateAppliesInFilenameOrder(t *testing.T) {
+	s := openTestStore(t)
+	// 0002 depends on 0001; MapFS iteration order is random, so this only
+	// passes if migrations are sorted by name.
+	ordered := fstest.MapFS{
+		"0002_child.sql":  {Data: []byte("CREATE TABLE child (parent_id INTEGER REFERENCES parent(id)); INSERT INTO child VALUES (1);")},
+		"0001_parent.sql": {Data: []byte("CREATE TABLE parent (id INTEGER PRIMARY KEY); INSERT INTO parent VALUES (1);")},
+	}
+	if err := Migrate(context.Background(), s.Writer, ordered); err != nil {
+		t.Fatal(err)
+	}
+}
+
+func TestMigrateUnknownAppliedVersionFails(t *testing.T) {
+	ctx := context.Background()
+	s := openTestStore(t)
+	if err := Migrate(ctx, s.Writer, testMigrations); err != nil {
+		t.Fatal(err)
+	}
+	older := fstest.MapFS{"0001_first.sql": testMigrations["0001_first.sql"]}
+	if err := Migrate(ctx, s.Writer, older); err == nil {
+		t.Fatal("migrating a database newer than the build returned no error")
+	}
+}
+
+func TestMigrateNoMigrationsFails(t *testing.T) {
+	s := openTestStore(t)
+	if err := Migrate(context.Background(), s.Writer, fstest.MapFS{}); err == nil {
+		t.Fatal("empty migrations returned no error")
+	}
+}
+
+func TestMigrateEmbeddedMigrations(t *testing.T) {
+	s := openTestStore(t)
+	if err := Migrate(context.Background(), s.Writer, migrations.FS); err != nil {
+		t.Fatal(err)
+	}
+	if got := versions(migrationRows(t, s)); got != "0000_bootstrap,0001_init" {
+		t.Errorf("versions = %q, want 0000_bootstrap,0001_init", got)
+	}
+}
+
+func TestMigrateBootstrapThenEmbeddedInitApplies(t *testing.T) {
+	ctx := context.Background()
+	s := openTestStore(t)
+	bootstrap, err := fs.ReadFile(migrations.FS, "0000_bootstrap.sql")
+	if err != nil {
+		t.Fatal(err)
+	}
+	// A database migrated by the bootstrap-only build upgrades to the
+	// embedded set, which adds 0001_init.sql alongside the unchanged bootstrap.
+	if err := Migrate(ctx, s.Writer, fstest.MapFS{"0000_bootstrap.sql": {Data: bootstrap}}); err != nil {
+		t.Fatal(err)
+	}
+	if err := Migrate(ctx, s.Writer, migrations.FS); err != nil {
+		t.Fatal(err)
+	}
+	if got := versions(migrationRows(t, s)); got != "0000_bootstrap,0001_init" {
+		t.Errorf("versions = %q, want 0000_bootstrap,0001_init", got)
+	}
+	if !tableExists(t, s, "dinners") {
+		t.Error("table from 0001_init was not created")
+	}
+}

internal/store/schema_test.go

@@ -0,0 +1,556 @@
+package store
+
+import (
+	"context"
+	"database/sql"
+	"fmt"
+	"slices"
+	"strings"
+	"testing"
+
+	"code.cubecraftlabs.com/Maniacs_Mansion/Meal-Quest/migrations"
+)
+
+// schemaTables is every table in the v1 data model, in insert order: parents
+// before the children that reference them.
+var schemaTables = []string{
+	"ai_runs",
+	"recipes",
+	"recipe_ingredients",
+	"recipe_steps",
+	"photos",
+	"dinners",
+	"suggestion_batches",
+	"suggestions",
+	"week_plans",
+	"plan_days",
+	"rotation_reviews",
+	"shopping_lists",
+	"shopping_groups",
+	"shopping_items",
+	"notification_prefs",
+	"push_subscriptions",
+	"notification_outbox",
+	"jobs",
+	"schedule_runs",
+	"sessions",
+	// Single-row settings tables; row is always id 1.
+	"household_settings",
+	"ai_settings",
+	"recipe_search_settings",
+	"integrations_google",
+	"chat_settings",
+}
+
+var singleRowTables = []string{
+	"household_settings",
+	"ai_settings",
+	"recipe_search_settings",
+	"integrations_google",
+	"chat_settings",
+}
+
+const testTime = "2026-10-03T12:00:00Z"
+
+// rowID is the id of the i-th test row of table. The schema stores ids as
+// opaque TEXT, so deterministic ids keep the fixtures readable.
+func rowID(table string, i int) string { return fmt.Sprintf("%s-%d", table, i) }
+
+// testRow returns a valid row for table. Rows with different i differ in
+// every unique column; references point at row 0 of the parent table.
+func testRow(table string, i int) map[string]any {
+	ts := map[string]any{"created_at": testTime, "updated_at": testTime}
+	r := map[string]any{"id": rowID(table, i)}
+	switch table {
+	case "household_settings":
+		r = map[string]any{"id": 1, "timezone": "America/New_York"}
+	case "ai_runs":
+		r["skill"] = "suggest"
+		r["skill_version"] = "1"
+		r["provider"] = "anthropic"
+		r["model"] = "claude"
+		r["temperature"] = 0.7
+		r["status"] = "ok"
+		r["request_redacted"] = "{}"
+	case "recipes":
+		r["source_url"] = fmt.Sprintf("https://example.com/recipe/%d", i)
+		r["title"] = "Chili"
+		r["raw_jsonld"] = "{}"
+		r["fetched_at"] = testTime
+	case "recipe_ingredients":
+		r["recipe_id"] = rowID("recipes", 0)
+		r["position"] = i
+		r["raw_text"] = "1 onion"
+	case "recipe_steps":
+		r["recipe_id"] = rowID("recipes", 0)
+		r["position"] = i
+		r["text"] = "Chop the onion."
+	case "photos":
+		r["source_url"] = "https://example.com/chili.jpg"
+		r["local_path"] = "photos/chili.jpg"
+		r["width"] = 800
+		r["height"] = 600
+		r["sha256"] = "abc"
+		r["fetched_at"] = testTime
+	case "dinners":
+		r["name"] = "Chili"
+		r["normalized_name"] = "chili"
+		r["status"] = "regular"
+		r["origin"] = "regular"
+		r["method"] = "crockpot"
+		r["difficulty"] = "easy"
+		r["is_favorite"] = 0
+		r["recipe_id"] = rowID("recipes", 0)
+		r["photo_id"] = rowID("photos", 0)
+		r["grounding_status"] = "grounded"
+	case "suggestion_batches":
+		r["trigger"] = "scheduled"
+		r["status"] = "done"
+		r["ai_run_id"] = rowID("ai_runs", 0)
+	case "suggestions":
+		r["dinner_id"] = rowID("dinners", 0)
+		r["batch_id"] = rowID("suggestion_batches", 0)
+		r["reason"] = "Like your chili."
+		r["cited_regular_ids"] = "[]"
+		r["status"] = "pending"
+	case "week_plans":
+		r["week_start"] = fmt.Sprintf("2026-%02d-05", i+1)
+		r["status"] = "draft"
+		r["ai_run_id"] = rowID("ai_runs", 0)
+	case "plan_days":
+		r["plan_id"] = rowID("week_plans", 0)
+		r["date"] = fmt.Sprintf("2026-01-%02d", i+5)
+		r["dinner_id"] = rowID("dinners", 0)
+		r["is_new"] = 0
+	case "rotation_reviews":
+		r["dinner_id"] = rowID("dinners", 0)
+		r["plan_date"] = fmt.Sprintf("2026-01-%02d", i+5)
+		r["status"] = "decided"
+		r["decision"] = "keep"
+	case "shopping_lists":
+		r["week_start"] = fmt.Sprintf("2026-%02d-05", i+1)
+		r["plan_id"] = rowID("week_plans", 0)
+		r["title"] = "Supply run"
+		r["status"] = "current"
+	case "shopping_groups":
+		r["list_id"] = rowID("shopping_lists", 0)
+		r["dinner_id"] = rowID("dinners", 0)
+		r["position"] = i
+	case "shopping_items":
+		r["group_id"] = rowID("shopping_groups", 0)
+		r["name"] = "Onion"
+		r["needed"] = 1
+		r["position"] = i
+	case "ai_settings":
+		r = map[string]any{"id": 1, "provider": "ollama", "base_url": "http://ollama:11434", "model": "llama", "temperature": 0.7}
+	case "recipe_search_settings":
+		r = map[string]any{"id": 1, "provider": "searxng", "base_url": "http://searxng:8080"}
+	case "integrations_google":
+		r = map[string]any{"id": 1, "refresh_token_ct": []byte{1}, "connected_at": testTime}
+	case "chat_settings":
+		r = map[string]any{"id": 1, "webhook_url_ct": []byte{1}, "last_test_status": "ok"}
+	case "notification_prefs":
+		events := []string{"plan_ready", "list_ready", "day_of_reminder", "new_suggestions", "rotation_review"}
+		r = map[string]any{"event": events[i], "push": 1, "chat": 0}
+	case "push_subscriptions":
+		r["endpoint"] = fmt.Sprintf("https://push.example.com/%d", i)
+		r["p256dh"] = "key"
+		r["auth"] = "auth"
+		r["failure_count"] = 0
+	case "notification_outbox":
+		r["event_key"] = fmt.Sprintf("plan_ready:2026-W%02d", i+1)
+		r["channel"] = "push"
+		r["payload"] = "{}"
+		r["status"] = "pending"
+		r["attempts"] = 0
+	case "jobs":
+		r["kind"] = "suggest"
+		r["status"] = "pending"
+		r["payload"] = "{}"
+	case "schedule_runs":
+		r["schedule"] = "plan_draft"
+		r["period_key"] = fmt.Sprintf("2026-W%02d", i+1)
+		r["created_at"] = testTime
+		return r
+	case "sessions":
+		return map[string]any{
+			"id_hash":      fmt.Sprintf("hash-%d", i),
+			"subject":      "sub",
+			"email":        "kitchen@example.com",
+			"name":         "Our kitchen",
+			"groups":       `["mealquest-admin"]`,
+			"csrf_token":   "csrf",
+			"created_at":   testTime,
+			"last_seen_at": testTime,
+			"expires_at":   testTime,
+		}
+	default:
+		panic("testRow: unknown table " + table)
+	}
+	for k, v := range ts {
+		r[k] = v
+	}
+	return r
+}
+
+type execer interface {
+	ExecContext(ctx context.Context, query string, args ...any) (sql.Result, error)
+}
+
+func insertSchemaRow(db execer, table string, row map[string]any) error {
+	cols := make([]string, 0, len(row))
+	for c := range row {
+		cols = append(cols, c)
+	}
+	slices.Sort(cols)
+	quoted := make([]string, len(cols))
+	args := make([]any, len(cols))
+	for i, c := range cols {
+		quoted[i] = `"` + c + `"`
+		args[i] = row[c]
+	}
+	q := fmt.Sprintf(`INSERT INTO "%s" (%s) VALUES (%s)`,
+		table, strings.Join(quoted, ", "), strings.TrimSuffix(strings.Repeat("?, ", len(cols)), ", "))
+	_, err := db.ExecContext(context.Background(), q, args...)
+	return err
+}
+
+// migratedStore opens a temp store migrated with the embedded migrations.
+func migratedStore(t *testing.T) *Store {
+	t.Helper()
+	s := openTestStore(t)
+	if err := Migrate(context.Background(), s.Writer, migrations.FS); err != nil {
+		t.Fatal(err)
+	}
+	return s
+}
+
+// seededStore is a migrated store holding row 0 of every multi-row table.
+// Single-row tables are left empty so tests can insert their only row.
+func seededStore(t *testing.T) *Store {
+	t.Helper()
+	s := migratedStore(t)
+	for _, table := range schemaTables {
+		if slices.Contains(singleRowTables, table) {
+			continue
+		}
+		if err := insertSchemaRow(s.Writer, table, testRow(table, 0)); err != nil {
+			t.Fatalf("seed %s: %v", table, err)
+		}
+	}
+	return s
+}
+
+// inRolledBackTx runs fn in a writer transaction that is always rolled back,
+// so each case starts from the seeded state.
+func inRolledBackTx(t *testing.T, s *Store, fn func(tx *sql.Tx)) {
+	t.Helper()
+	tx, err := s.Writer.BeginTx(context.Background(), nil)
+	if err != nil {
+		t.Fatal(err)
+	}
+	defer func() {
+		if err := tx.Rollback(); err != nil {
+			t.Fatal(err)
+		}
+	}()
+	fn(tx)
+}
+
+func wantConstraintError(t *testing.T, err error, kind, what string) {
+	t.Helper()
+	if err == nil {
+		t.Fatalf("%s: accepted, want %s constraint failure", what, kind)
+	}
+	if !strings.Contains(err.Error(), kind+" constraint failed") {
+		t.Fatalf("%s: error %q, want %s constraint failure", what, err, kind)
+	}
+}
+
+func TestSchemaTablesExist(t *testing.T) {
+	s := migratedStore(t)
+	if len(schemaTables) != 25 {
+		t.Fatalf("schemaTables lists %d tables, want 25", len(schemaTables))
+	}
+	for _, table := range schemaTables {
+		if !tableExists(t, s, table) {
+			t.Errorf("table %s missing", table)
+		}
+	}
+}
+
+func TestSchemaForeignKeysAreValid(t *testing.T) {
+	s := seededStore(t)
+	rows, err := s.Writer.Query("PRAGMA foreign_key_check")
+	if err != nil {
+		t.Fatal(err)
+	}
+	defer rows.Close()
+	if rows.Next() {
+		t.Error("foreign_key_check reported violations in the seeded data")
+	}
+	if err := rows.Err(); err != nil {
+		t.Fatal(err)
+	}
+}
+
+func TestSchemaEnumChecksRejectInvalidValues(t *testing.T) {
+	cases := []struct {
+		table, column string
+		bad           any
+	}{
+		{"household_settings", "plan_draft_dow", 7},
+		{"household_settings", "suggestions_dow", -1},
+		{"household_settings", "chat_autosend_dow", 7},
+		{"household_settings", "plan_draft_dow", 5.5},
+		{"household_settings", "suggestions_dow", 0.5},
+		{"household_settings", "chat_autosend_dow", 3.5},
+		{"ai_runs", "provider", "openai"},
+		{"ai_runs", "status", "done"},
+		{"dinners", "status", "deck"},
+		{"dinners", "origin", "ai"},
+		{"dinners", "method", "microwave"},
+		{"dinners", "difficulty", "extreme"},
+		{"dinners", "is_favorite", 2},
+		{"dinners", "grounding_status", "unknown"},
+		{"suggestion_batches", "trigger", "cron"},
+		{"suggestion_batches", "status", "ok"},
+		{"suggestions", "status", "maybe"},
+		{"week_plans", "status", "final"},
+		{"plan_days", "is_new", 2},
+		{"rotation_reviews", "status", "open"},
+		{"rotation_reviews", "decision", "promote"},
+		{"shopping_lists", "status", "frozen"},
+		{"shopping_items", "needed", 2},
+		{"ai_settings", "provider", "openai"},
+		{"recipe_search_settings", "provider", "google"},
+		{"chat_settings", "last_test_status", "pending"},
+		{"notification_prefs", "event", "weekly_digest"},
+		{"notification_prefs", "push", 2},
+		{"notification_prefs", "chat", 2},
+		{"notification_outbox", "channel", "email"},
+		{"notification_outbox", "status", "queued"},
+		{"jobs", "status", "queued"},
+		{"schedule_runs", "schedule", "hourly"},
+	}
+	s := seededStore(t)
+	for _, c := range cases {
+		t.Run(fmt.Sprintf("%s.%s=%v", c.table, c.column, c.bad), func(t *testing.T) {
+			row := testRow(c.table, 1)
+			if slices.Contains(singleRowTables, c.table) {
+				row = testRow(c.table, 0)
+			}
+			inRolledBackTx(t, s, func(tx *sql.Tx) {
+				if err := insertSchemaRow(tx, c.table, row); err != nil {
+					t.Fatalf("valid row rejected: %v", err)
+				}
+			})
+			row[c.column] = c.bad
+			inRolledBackTx(t, s, func(tx *sql.Tx) {
+				wantConstraintError(t, insertSchemaRow(tx, c.table, row), "CHECK", fmt.Sprintf("%s.%s = %v", c.table, c.column, c.bad))
+			})
+		})
+	}
+}
+
+func TestSchemaRotationReviewEventAllowed(t *testing.T) {
+	s := migratedStore(t)
+	if err := insertSchemaRow(s.Writer, "notification_prefs", testRow("notification_prefs", 4)); err != nil {
+		t.Fatalf("rotation_review event rejected: %v", err)
+	}
+}
+
+func TestSchemaDinnerStatuses(t *testing.T) {
+	s := seededStore(t)
+	for _, status := range []string{"suggested", "quest_log", "tried", "regular", "archived"} {
+		inRolledBackTx(t, s, func(tx *sql.Tx) {
+			row := testRow("dinners", 1)
+			row["status"] = status
+			if err := insertSchemaRow(tx, "dinners", row); err != nil {
+				t.Errorf("status %s rejected: %v", status, err)
+			}
+		})
+	}
+}
+
+func TestSchemaUniqueConstraintsRejectDuplicates(t *testing.T) {
+	cases := []struct {
+		table   string
+		columns []string
+	}{
+		{"recipes", []string{"source_url"}},
+		{"week_plans", []string{"week_start"}},
+		{"plan_days", []string{"plan_id", "date"}},
+		{"rotation_reviews", []string{"dinner_id", "plan_date"}},
+		{"shopping_lists", []string{"week_start"}},
+		{"push_subscriptions", []string{"endpoint"}},
+		{"notification_outbox", []string{"event_key", "channel"}},
+		{"schedule_runs", []string{"schedule", "period_key"}},
+		{"notification_prefs", []string{"event"}},
+	}
+	s := seededStore(t)
+	for _, c := range cases {
+		t.Run(c.table+"("+strings.Join(c.columns, ",")+")", func(t *testing.T) {
+			row := testRow(c.table, 1)
+			inRolledBackTx(t, s, func(tx *sql.Tx) {
+				if err := insertSchemaRow(tx, c.table, row); err != nil {
+					t.Fatalf("distinct row rejected: %v", err)
+				}
+			})
+			seeded := testRow(c.table, 0)
+			for _, col := range c.columns {
+				row[col] = seeded[col]
+			}
+			inRolledBackTx(t, s, func(tx *sql.Tx) {
+				wantConstraintError(t, insertSchemaRow(tx, c.table, row), "UNIQUE", "duplicate "+c.table)
+			})
+		})
+	}
+}
+
+// TestSchemaCompositeUniqueAllowsPartialOverlap shows each composite UNIQUE
+// spans all its columns: a row sharing only some of them with the seeded row
+// is accepted. (Sharing only the other columns is covered by the distinct row
+// in TestSchemaUniqueConstraintsRejectDuplicates.)
+func TestSchemaCompositeUniqueAllowsPartialOverlap(t *testing.T) {
+	cases := []struct {
+		name   string
+		table  string
+		shared string         // column copied from the seeded row
+		parent string         // row 1 of this table is inserted first, if set
+		set    map[string]any // columns that differ from the seeded row
+	}{
+		{"same date, different plan", "plan_days", "date", "week_plans", map[string]any{"plan_id": rowID("week_plans", 1)}},
+		{"same plan_date, different dinner", "rotation_reviews", "plan_date", "dinners", map[string]any{"dinner_id": rowID("dinners", 1)}},
+		{"same event_key, different channel", "notification_outbox", "event_key", "", map[string]any{"channel": "chat"}},
+		{"same period_key, different schedule", "schedule_runs", "period_key", "", map[string]any{"schedule": "week_rollover"}},
+	}
+	s := seededStore(t)
+	for _, c := range cases {
+		t.Run(c.table+": "+c.name, func(t *testing.T) {
+			row := testRow(c.table, 1)
+			row[c.shared] = testRow(c.table, 0)[c.shared]
+			for k, v := range c.set {
+				row[k] = v
+			}
+			inRolledBackTx(t, s, func(tx *sql.Tx) {
+				if c.parent != "" {
+					if err := insertSchemaRow(tx, c.parent, testRow(c.parent, 1)); err != nil {
+						t.Fatalf("insert parent: %v", err)
+					}
+				}
+				if err := insertSchemaRow(tx, c.table, row); err != nil {
+					t.Fatalf("row rejected: %v", err)
+				}
+			})
+		})
+	}
+}
+
+// TestSchemaTextPrimaryKeysRejectNull guards against SQLite's legacy
+// acceptance of NULL in non-INTEGER primary keys.
+func TestSchemaTextPrimaryKeysRejectNull(t *testing.T) {
+	s := seededStore(t)
+	ctx := context.Background()
+	for _, table := range schemaTables {
+		if slices.Contains(singleRowTables, table) {
+			continue
+		}
+		key := "id"
+		switch table {
+		case "notification_prefs":
+			key = "event"
+		case "sessions":
+			key = "id_hash"
+		}
+		t.Run(table+"."+key, func(t *testing.T) {
+			inRolledBackTx(t, s, func(tx *sql.Tx) {
+				// Two NULL-key rows: neither may be accepted, so NULL can
+				// never stand in for a key, once or repeatedly.
+				for i := 1; i <= 2; i++ {
+					row := testRow(table, i)
+					row[key] = nil
+					wantConstraintError(t, insertSchemaRow(tx, table, row), "NOT NULL", fmt.Sprintf("%s.%s = NULL (row %d)", table, key, i))
+				}
+				var n int
+				if err := tx.QueryRowContext(ctx, fmt.Sprintf(`SELECT count(*) FROM "%s" WHERE "%s" IS NULL`, table, key)).Scan(&n); err != nil {
+					t.Fatal(err)
+				}
+				if n != 0 {
+					t.Errorf("%d rows with NULL %s", n, key)
+				}
+			})
+		})
+	}
+}
+
+func TestSchemaSingleRowTablesRejectSecondRow(t *testing.T) {
+	s := migratedStore(t)
+	for _, table := range singleRowTables {
+		if err := insertSchemaRow(s.Writer, table, testRow(table, 0)); err != nil {
+			t.Fatalf("%s: %v", table, err)
+		}
+		row := testRow(table, 0)
+		row["id"] = 2
+		wantConstraintError(t, insertSchemaRow(s.Writer, table, row), "CHECK", table+" second row")
+	}
+}
+
+func TestSchemaDeleteParentWithChildrenRefused(t *testing.T) {
+	cases := []struct{ parent, child, column string }{
+		{"recipes", "recipe_ingredients", "recipe_id"},
+		{"recipes", "recipe_steps", "recipe_id"},
+		{"recipes", "dinners", "recipe_id"},
+		{"photos", "dinners", "photo_id"},
+		{"dinners", "suggestions", "dinner_id"},
+		{"suggestion_batches", "suggestions", "batch_id"},
+		{"week_plans", "plan_days", "plan_id"},
+		{"dinners", "plan_days", "dinner_id"},
+		{"dinners", "rotation_reviews", "dinner_id"},
+		{"week_plans", "shopping_lists", "plan_id"},
+		{"shopping_lists", "shopping_groups", "list_id"},
+		{"dinners", "shopping_groups", "dinner_id"},
+		{"shopping_groups", "shopping_items", "group_id"},
+		// ai_runs too: the purge must clear references before deleting.
+		{"ai_runs", "suggestion_batches", "ai_run_id"},
+		{"ai_runs", "week_plans", "ai_run_id"},
+	}
+	s := seededStore(t)
+	for _, c := range cases {
+		t.Run(c.parent+"<-"+c.child+"."+c.column, func(t *testing.T) {
+			// A fresh parent with exactly one child, through this column only.
+			parentID := rowID(c.parent, 1)
+			child := testRow(c.child, 1)
+			child[c.column] = parentID
+			del := fmt.Sprintf(`DELETE FROM "%s" WHERE id = ?`, c.parent)
+			ctx := context.Background()
+
+			inRolledBackTx(t, s, func(tx *sql.Tx) {
+				if err := insertSchemaRow(tx, c.parent, testRow(c.parent, 1)); err != nil {
+					t.Fatalf("insert parent: %v", err)
+				}
+				if err := insertSchemaRow(tx, c.child, child); err != nil {
+					t.Fatalf("insert child: %v", err)
+				}
+				_, err := tx.ExecContext(ctx, del, parentID)
+				wantConstraintError(t, err, "FOREIGN KEY", "delete "+c.parent)
+				var n int
+				if err := tx.QueryRowContext(ctx, fmt.Sprintf(`SELECT count(*) FROM "%s" WHERE id = ?`, c.parent), parentID).Scan(&n); err != nil {
+					t.Fatal(err)
+				}
+				if n != 1 {
+					t.Errorf("parent row gone after refused delete")
+				}
+			})
+			// Control: without the child the same delete succeeds.
+			inRolledBackTx(t, s, func(tx *sql.Tx) {
+				if err := insertSchemaRow(tx, c.parent, testRow(c.parent, 1)); err != nil {
+					t.Fatalf("insert parent: %v", err)
+				}
+				if _, err := tx.ExecContext(ctx, del, parentID); err != nil {
+					t.Fatalf("delete childless parent: %v", err)
+				}
+			})
+		})
+	}
+}

internal/store/store.go

@@ -0,0 +1,79 @@
+// Package store owns the MealQuest SQLite database: one writer connection,
+// a read pool, and the embedded schema migrations.
+package store
+
+import (
+	"context"
+	"database/sql"
+	"errors"
+	"fmt"
+	"net/url"
+	"path/filepath"
+
+	_ "modernc.org/sqlite" // registers the "sqlite" database/sql driver
+)
+
+// FileName is the database file inside the data directory.
+const FileName = "mealquest.db"
+
+// readPoolSize is the maximum number of open read connections.
+const readPoolSize = 4
+
+// connPragmas are applied by the driver to every new connection.
+var connPragmas = []string{
+	"busy_timeout(5000)",
+	"journal_mode(WAL)",
+	"foreign_keys(1)",
+}
+
+// Store holds the database handles. All writes go through Writer, which has
+// exactly one connection; Reader is a pool of read-only connections.
+type Store struct {
+	Writer *sql.DB
+	Reader *sql.DB
+}
+
+// Open opens <dataDir>/mealquest.db, creating it if absent, and verifies both
+// handles can connect. It does not apply migrations; call Migrate for that.
+func Open(ctx context.Context, dataDir string) (*Store, error) {
+	path, err := filepath.Abs(filepath.Join(dataDir, FileName))
+	if err != nil {
+		return nil, fmt.Errorf("store: resolve database path: %w", err)
+	}
+
+	// The writer is opened and pinged first so the file exists and is in WAL
+	// mode before any read-only connection touches it.
+	writer, err := openDB(ctx, path, 1, url.Values{"_txlock": {"immediate"}})
+	if err != nil {
+		return nil, fmt.Errorf("store: open writer: %w", err)
+	}
+	reader, err := openDB(ctx, path, readPoolSize, url.Values{"_pragma": {"query_only(1)"}})
+	if err != nil {
+		return nil, errors.Join(fmt.Errorf("store: open reader: %w", err), writer.Close())
+	}
+	return &Store{Writer: writer, Reader: reader}, nil
+}
+
+// Close closes both handles.
+func (s *Store) Close() error {
+	return errors.Join(s.Reader.Close(), s.Writer.Close())
+}
+
+func openDB(ctx context.Context, path string, maxConns int, extra url.Values) (*sql.DB, error) {
+	q := url.Values{"_pragma": connPragmas}
+	for k, vs := range extra {
+		q[k] = append(q[k], vs...)
+	}
+	dsn := (&url.URL{Scheme: "file", Path: path, RawQuery: q.Encode()}).String()
+
+	db, err := sql.Open("sqlite", dsn)
+	if err != nil {
+		return nil, err
+	}
+	db.SetMaxOpenConns(maxConns)
+	db.SetMaxIdleConns(maxConns)
+	if err := db.PingContext(ctx); err != nil {
+		return nil, errors.Join(err, db.Close())
+	}
+	return db, nil
+}

internal/store/store_test.go

@@ -0,0 +1,116 @@
+package store
+
+import (
+	"context"
+	"database/sql"
+	"os"
+	"path/filepath"
+	"testing"
+)
+
+func openTestStore(t *testing.T) *Store {
+	t.Helper()
+	s, err := Open(context.Background(), t.TempDir())
+	if err != nil {
+		t.Fatal(err)
+	}
+	t.Cleanup(func() {
+		if err := s.Close(); err != nil {
+			t.Error(err)
+		}
+	})
+	return s
+}
+
+func TestOpenCreatesDatabaseFile(t *testing.T) {
+	dir := t.TempDir()
+	s, err := Open(context.Background(), dir)
+	if err != nil {
+		t.Fatal(err)
+	}
+	defer s.Close()
+	if _, err := os.Stat(filepath.Join(dir, FileName)); err != nil {
+		t.Fatalf("database file: %v", err)
+	}
+}
+
+func TestOpenMissingDirFails(t *testing.T) {
+	_, err := Open(context.Background(), filepath.Join(t.TempDir(), "missing"))
+	if err == nil {
+		t.Fatal("Open succeeded on a missing data directory")
+	}
+}
+
+func TestPoolSizes(t *testing.T) {
+	s := openTestStore(t)
+	if got := s.Writer.Stats().MaxOpenConnections; got != 1 {
+		t.Errorf("writer max open connections = %d, want 1", got)
+	}
+	if got := s.Reader.Stats().MaxOpenConnections; got != readPoolSize {
+		t.Errorf("reader max open connections = %d, want %d", got, readPoolSize)
+	}
+}
+
+// TestPragmasOnEveryConnection holds every reader connection open at once so
+// each one is a distinct connection, then checks the pragmas on each and on
+// the writer.
+func TestPragmasOnEveryConnection(t *testing.T) {
+	ctx := context.Background()
+	s := openTestStore(t)
+
+	conns := []*sql.Conn{}
+	for range readPoolSize {
+		c, err := s.Reader.Conn(ctx)
+		if err != nil {
+			t.Fatal(err)
+		}
+		defer c.Close()
+		conns = append(conns, c)
+	}
+	w, err := s.Writer.Conn(ctx)
+	if err != nil {
+		t.Fatal(err)
+	}
+	defer w.Close()
+	conns = append(conns, w)
+
+	for i, c := range conns {
+		var fk, busy int
+		var journal string
+		if err := c.QueryRowContext(ctx, "PRAGMA foreign_keys").Scan(&fk); err != nil {
+			t.Fatal(err)
+		}
+		if err := c.QueryRowContext(ctx, "PRAGMA busy_timeout").Scan(&busy); err != nil {
+			t.Fatal(err)
+		}
+		if err := c.QueryRowContext(ctx, "PRAGMA journal_mode").Scan(&journal); err != nil {
+			t.Fatal(err)
+		}
+		if fk != 1 || busy != 5000 || journal != "wal" {
+			t.Errorf("connection %d: foreign_keys=%d busy_timeout=%d journal_mode=%q, want 1, 5000, wal", i, fk, busy, journal)
+		}
+	}
+}
+
+func TestReaderIsReadOnly(t *testing.T) {
+	s := openTestStore(t)
+	if _, err := s.Reader.Exec("CREATE TABLE t (id INTEGER)"); err == nil {
+		t.Fatal("write through the reader succeeded")
+	}
+	if _, err := s.Writer.Exec("CREATE TABLE t (id INTEGER)"); err != nil {
+		t.Fatalf("write through the writer: %v", err)
+	}
+}
+
+func TestForeignKeysEnforced(t *testing.T) {
+	s := openTestStore(t)
+	if _, err := s.Writer.Exec(`
+		CREATE TABLE parent (id INTEGER PRIMARY KEY);
+		CREATE TABLE child (parent_id INTEGER NOT NULL REFERENCES parent(id));
+	`); err != nil {
+		t.Fatal(err)
+	}
+	if _, err := s.Writer.Exec("INSERT INTO child (parent_id) VALUES (42)"); err == nil {
+		t.Fatal("insert violating a foreign key succeeded")
+	}
+}

internal/store/time.go

@@ -0,0 +1,119 @@
+package store
+
+import (
+	"database/sql/driver"
+	"errors"
+	"fmt"
+	"time"
+)
+
+// timestampLayout is RFC 3339 in UTC with fixed millisecond precision, so
+// stored timestamps sort lexicographically in time order.
+const timestampLayout = "2006-01-02T15:04:05.000Z"
+
+// Timestamp is an instant stored as UTC RFC 3339 TEXT with millisecond
+// precision. Sub-millisecond precision is dropped on write. Use
+// sql.Null[Timestamp] for a nullable column.
+type Timestamp struct {
+	time.Time
+}
+
+// Now returns the current instant as a Timestamp, in UTC at millisecond
+// precision, so it equals its stored form.
+func Now() Timestamp {
+	return Timestamp{time.Now().UTC().Truncate(time.Millisecond)}
+}
+
+// String returns the stored TEXT form.
+func (t Timestamp) String() string {
+	return t.UTC().Format(timestampLayout)
+}
+
+// Value implements driver.Valuer. The zero Timestamp is an error, as is a UTC
+// year outside 0000–9999, which the four-digit stored form cannot represent.
+func (t Timestamp) Value() (driver.Value, error) {
+	if t.IsZero() {
+		return nil, errors.New("store: zero Timestamp")
+	}
+	if y := t.UTC().Year(); y < 0 || y > 9999 {
+		return nil, fmt.Errorf("store: Timestamp year %d outside 0000-9999", y)
+	}
+	return t.String(), nil
+}
+
+// Scan implements sql.Scanner. It accepts only TEXT in the stored form, and
+// rejects the zero instant, which Value refuses to write.
+func (t *Timestamp) Scan(src any) error {
+	s, ok := src.(string)
+	if !ok {
+		return fmt.Errorf("store: scan Timestamp: want TEXT, got %T", src)
+	}
+	parsed, err := time.Parse(timestampLayout, s)
+	if err != nil {
+		return fmt.Errorf("store: scan Timestamp %q: %w", s, err)
+	}
+	if parsed.Format(timestampLayout) != s {
+		return fmt.Errorf("store: scan Timestamp %q: want %s", s, timestampLayout)
+	}
+	if parsed.IsZero() {
+		return fmt.Errorf("store: scan Timestamp %q: zero Timestamp", s)
+	}
+	t.Time = parsed
+	return nil
+}
+
+// localDateLayout is the household-local calendar date form.
+const localDateLayout = "2006-01-02"
+
+// LocalDate is a household-local calendar date with no time or zone, stored
+// as YYYY-MM-DD TEXT. The zero LocalDate is invalid and cannot be stored.
+type LocalDate struct {
+	year  int
+	month time.Month
+	day   int
+}
+
+// ParseLocalDate parses a YYYY-MM-DD date, rejecting any other form and any
+// date that does not exist on the calendar.
+func ParseLocalDate(s string) (LocalDate, error) {
+	t, err := time.Parse(localDateLayout, s)
+	if err != nil {
+		return LocalDate{}, fmt.Errorf("store: invalid date %q: %w", s, err)
+	}
+	if t.Format(localDateLayout) != s {
+		return LocalDate{}, fmt.Errorf("store: invalid date %q: want YYYY-MM-DD", s)
+	}
+	return LocalDate{t.Year(), t.Month(), t.Day()}, nil
+}
+
+// String returns the YYYY-MM-DD form.
+func (d LocalDate) String() string {
+	return fmt.Sprintf("%04d-%02d-%02d", d.year, int(d.month), d.day)
+}
+
+// IsZero reports whether d is the zero LocalDate.
+func (d LocalDate) IsZero() bool {
+	return d == LocalDate{}
+}
+
+// Value implements driver.Valuer. The zero LocalDate is an error.
+func (d LocalDate) Value() (driver.Value, error) {
+	if d.IsZero() {
+		return nil, errors.New("store: zero LocalDate")
+	}
+	return d.String(), nil
+}
+
+// Scan implements sql.Scanner. It accepts only YYYY-MM-DD TEXT.
+func (d *LocalDate) Scan(src any) error {
+	s, ok := src.(string)
+	if !ok {
+		return fmt.Errorf("store: scan LocalDate: want TEXT, got %T", src)
+	}
+	parsed, err := ParseLocalDate(s)
+	if err != nil {
+		return fmt.Errorf("store: scan LocalDate: %w", err)
+	}
+	*d = parsed
+	return nil
+}

internal/store/time_test.go

@@ -0,0 +1,126 @@
+package store
+
+import (
+	"context"
+	"testing"
+	"time"
+)
+
+func openTypesTestStore(t *testing.T) *Store {
+	t.Helper()
+	s := openTestStore(t)
+	if _, err := s.Writer.Exec("CREATE TABLE v (val TEXT)"); err != nil {
+		t.Fatal(err)
+	}
+	return s
+}
+
+// roundTrip writes in through the writer, then reads it back into out and
+// returns the stored SQLite type and raw text.
+func roundTrip(t *testing.T, s *Store, in, out any) (typ, raw string) {
+	t.Helper()
+	ctx := context.Background()
+	if _, err := s.Writer.ExecContext(ctx, "DELETE FROM v"); err != nil {
+		t.Fatal(err)
+	}
+	if _, err := s.Writer.ExecContext(ctx, "INSERT INTO v (val) VALUES (?)", in); err != nil {
+		t.Fatal(err)
+	}
+	if err := s.Reader.QueryRowContext(ctx, "SELECT typeof(val), val, val FROM v").Scan(&typ, &raw, out); err != nil {
+		t.Fatal(err)
+	}
+	return typ, raw
+}
+
+func TestTimestampRoundTrip(t *testing.T) {
+	s := openTypesTestStore(t)
+	zone := time.FixedZone("EDT", -4*60*60)
+	in := Timestamp{time.Date(2026, 10, 3, 8, 37, 5, 123456789, zone)}
+
+	var out Timestamp
+	typ, raw := roundTrip(t, s, in, &out)
+	if typ != "text" || raw != "2026-10-03T12:37:05.123Z" {
+		t.Fatalf("stored %s %q, want text %q", typ, raw, "2026-10-03T12:37:05.123Z")
+	}
+	if want := in.Truncate(time.Millisecond); !out.Equal(want) {
+		t.Fatalf("read back %v, want %v", out, want)
+	}
+	if out.Location() != time.UTC {
+		t.Fatalf("read back location %v, want UTC", out.Location())
+	}
+
+	now := Now()
+	roundTrip(t, s, now, &out)
+	if out != now {
+		t.Fatalf("Now() round trip = %#v, want %#v", out, now)
+	}
+}
+
+func TestTimestampRejects(t *testing.T) {
+	if _, err := (Timestamp{}).Value(); err == nil {
+		t.Error("zero Timestamp Value succeeded")
+	}
+	for _, tm := range []time.Time{
+		time.Date(10000, 1, 1, 0, 0, 0, 0, time.UTC),
+		time.Date(-1, 12, 31, 0, 0, 0, 0, time.UTC),
+		time.Date(9999, 12, 31, 23, 0, 0, 0, time.FixedZone("west", -2*3600)),
+	} {
+		if v, err := (Timestamp{tm}).Value(); err == nil {
+			t.Errorf("Timestamp{%v}.Value() = %v, want error", tm, v)
+		}
+	}
+	for _, src := range []any{
+		nil, int64(0), []byte("2026-10-03T12:37:05.123Z"), "2026-10-03T12:37:05Z",
+		"2026-10-03T08:37:05.123-04:00", "2026-10-03T12:37:05,123Z", "garbage",
+		"0001-01-01T00:00:00.000Z",
+	} {
+		var ts Timestamp
+		if err := ts.Scan(src); err == nil {
+			t.Errorf("Scan(%#v) succeeded", src)
+		}
+	}
+}
+
+func TestLocalDateRoundTrip(t *testing.T) {
+	s := openTypesTestStore(t)
+	for _, str := range []string{"2026-10-03", "2028-02-29", "0001-01-01", "9999-12-31"} {
+		in, err := ParseLocalDate(str)
+		if err != nil {
+			t.Fatalf("ParseLocalDate(%q): %v", str, err)
+		}
+		if in.String() != str {
+			t.Fatalf("String() = %q, want %q", in.String(), str)
+		}
+		var out LocalDate
+		typ, raw := roundTrip(t, s, in, &out)
+		if typ != "text" || raw != str {
+			t.Fatalf("stored %s %q, want text %q", typ, raw, str)
+		}
+		if out != in {
+			t.Fatalf("read back %v, want %v", out, in)
+		}
+	}
+}
+
+func TestLocalDateRejectsInvalid(t *testing.T) {
+	for _, str := range []string{
+		"", "2026-02-30", "2026-02-29", "2026-13-01", "2026-00-10", "2026-10-00",
+		"2026-10-32", "2026-1-03", "2026-10-3", "26-10-03", "2026/10/03",
+		"2026-10-03T00:00:00Z", " 2026-10-03", "20261003", "not-a-date",
+	} {
+		if d, err := ParseLocalDate(str); err == nil {
+			t.Errorf("ParseLocalDate(%q) = %v, want error", str, d)
+		}
+		var d LocalDate
+		if err := d.Scan(str); err == nil {
+			t.Errorf("Scan(%q) succeeded", str)
+		}
+	}
+	if _, err := (LocalDate{}).Value(); err == nil {
+		t.Error("zero LocalDate Value succeeded")
+	}
+	var d LocalDate
+	if err := d.Scan(nil); err == nil {
+		t.Error("Scan(nil) succeeded")
+	}
+}

internal/store/tx.go

@@ -0,0 +1,37 @@
+package store
+
+import (
+	"context"
+	"database/sql"
+	"errors"
+	"fmt"
+)
+
+// WithTx runs fn in a transaction on the writer. It commits when fn returns
+// nil and rolls back when fn returns an error, panics or exits the goroutine.
+// A panic is not recovered: it propagates unchanged after the rollback.
+func (s *Store) WithTx(ctx context.Context, fn func(tx *sql.Tx) error) error {
+	tx, err := s.Writer.BeginTx(ctx, nil)
+	if err != nil {
+		return fmt.Errorf("store: begin transaction: %w", err)
+	}
+	finished := false
+	defer func() {
+		if !finished {
+			_ = tx.Rollback() // fn panicked; the panic carries on regardless
+		}
+	}()
+
+	if err := fn(tx); err != nil {
+		finished = true
+		if rbErr := tx.Rollback(); rbErr != nil {
+			return errors.Join(err, fmt.Errorf("store: roll back transaction: %w", rbErr))
+		}
+		return err
+	}
+	finished = true
+	if err := tx.Commit(); err != nil {
+		return fmt.Errorf("store: commit transaction: %w", err)
+	}
+	return nil
+}

internal/store/tx_test.go

@@ -0,0 +1,84 @@
+package store
+
+import (
+	"context"
+	"database/sql"
+	"errors"
+	"testing"
+)
+
+func openTxTestStore(t *testing.T) *Store {
+	t.Helper()
+	s := openTestStore(t)
+	if _, err := s.Writer.Exec("CREATE TABLE t (id INTEGER PRIMARY KEY)"); err != nil {
+		t.Fatal(err)
+	}
+	return s
+}
+
+func countRows(t *testing.T, s *Store) int {
+	t.Helper()
+	var n int
+	if err := s.Reader.QueryRow("SELECT count(*) FROM t").Scan(&n); err != nil {
+		t.Fatal(err)
+	}
+	return n
+}
+
+func insertRow(tx *sql.Tx) error {
+	_, err := tx.Exec("INSERT INTO t (id) VALUES (1)")
+	return err
+}
+
+func TestWithTxCommitsOnNil(t *testing.T) {
+	s := openTxTestStore(t)
+	if err := s.WithTx(context.Background(), insertRow); err != nil {
+		t.Fatal(err)
+	}
+	if n := countRows(t, s); n != 1 {
+		t.Fatalf("rows after commit = %d, want 1", n)
+	}
+}
+
+func TestWithTxRollsBackOnError(t *testing.T) {
+	s := openTxTestStore(t)
+	errBoom := errors.New("boom")
+	err := s.WithTx(context.Background(), func(tx *sql.Tx) error {
+		if err := insertRow(tx); err != nil {
+			t.Fatal(err)
+		}
+		return errBoom
+	})
+	if !errors.Is(err, errBoom) {
+		t.Fatalf("WithTx error = %v, want %v", err, errBoom)
+	}
+	if n := countRows(t, s); n != 0 {
+		t.Fatalf("rows after rollback = %d, want 0", n)
+	}
+}
+
+func TestWithTxRollsBackOnPanic(t *testing.T) {
+	s := openTxTestStore(t)
+	const value = "kaboom"
+	func() {
+		defer func() {
+			if p := recover(); p != value {
+				t.Fatalf("recovered %v, want panic %q to propagate", p, value)
+			}
+		}()
+		_ = s.WithTx(context.Background(), func(tx *sql.Tx) error {
+			if err := insertRow(tx); err != nil {
+				t.Fatal(err)
+			}
+			panic(value)
+		})
+		t.Fatal("WithTx returned instead of panicking")
+	}()
+	if n := countRows(t, s); n != 0 {
+		t.Fatalf("rows after panic = %d, want 0", n)
+	}
+	// The single writer connection must have been released.
+	if err := s.WithTx(context.Background(), insertRow); err != nil {
+		t.Fatalf("WithTx after panic: %v", err)
+	}
+}

migrations/0000_bootstrap.sql

@@ -0,0 +1,4 @@
+-- 0000_bootstrap: immutable no-op bootstrap migration. It lets the migrations
+-- embed compile and proves the runner end to end. Never edit or remove it:
+-- applied versions are recorded by filename and never re-run. The initial v1
+-- schema arrives as a new migration, 0001_init.sql (Feature 02 task 3).

migrations/0001_init.sql

@@ -0,0 +1,333 @@
+-- 0001_init: the initial v1 schema (CTXD CONTEXT.MD data model).
+--
+-- Conventions:
+--   * Row ids are ULIDs stored as TEXT NOT NULL PRIMARY KEY (SQLite would
+--     otherwise accept NULL in a TEXT primary key). Single-row settings tables
+--     use id INTEGER PRIMARY KEY CHECK (id = 1) so a second row is refused.
+--   * Timestamps are UTC RFC 3339 TEXT. Household-local dates (week_start,
+--     plan day) are YYYY-MM-DD TEXT; household-local times are HH:MM TEXT.
+--   * Columns ending in _ct only ever hold secretbox ciphertext.
+--   * Every enum column has a CHECK. Booleans are INTEGER CHECK (x IN (0, 1)).
+--     Days of the week are INTEGER IN (0, 1, 2, 3, 4, 5, 6), Sunday = 0 (Go
+--     time.Weekday); IN rather than BETWEEN so fractional values are refused.
+--   * Foreign keys use the default NO ACTION, so deleting a parent that still
+--     has children is refused. This includes ai_run_id: the 30-day ai_runs
+--     purge must clear those references explicitly before deleting.
+--   * Defaults exist only where CTXD names one (household_settings times and
+--     shopping_items.needed).
+--
+-- Enum value sets not spelled out in CONTEXT.MD, chosen here:
+--   ai_settings.provider, ai_runs.provider: openai_compatible, anthropic, ollama
+--   ai_runs.status: running, ok, error
+--   chat_settings.last_test_status: ok, failed
+--   jobs.status, suggestion_batches.status: pending, running, done, failed
+--   notification_outbox.status: pending, sent, failed
+--   schedule_runs.schedule: suggestions_weekly, plan_draft,
+--     shopping_chat_autosend, day_of_reminder, rotation_check, week_rollover,
+--     ai_runs_purge
+-- jobs.kind and ai_runs.skill are open identifiers that grow with features,
+-- not enums, so they carry no CHECK.
+
+CREATE TABLE household_settings (
+	id                      INTEGER PRIMARY KEY CHECK (id = 1),
+	timezone                TEXT    NOT NULL,
+	dinner_time             TEXT    NOT NULL DEFAULT '17:30',
+	reminder_time           TEXT    NOT NULL DEFAULT '16:30',
+	review_time             TEXT    NOT NULL DEFAULT '09:00',
+	plan_draft_dow          INTEGER NOT NULL DEFAULT 5 CHECK (plan_draft_dow IN (0, 1, 2, 3, 4, 5, 6)),
+	plan_draft_time         TEXT    NOT NULL DEFAULT '09:00',
+	suggestions_dow         INTEGER NOT NULL DEFAULT 0 CHECK (suggestions_dow IN (0, 1, 2, 3, 4, 5, 6)),
+	suggestions_time        TEXT    NOT NULL DEFAULT '08:00',
+	chat_autosend_dow       INTEGER NOT NULL DEFAULT 6 CHECK (chat_autosend_dow IN (0, 1, 2, 3, 4, 5, 6)),
+	chat_autosend_time      TEXT    NOT NULL DEFAULT '08:00',
+	onboarding_completed_at TEXT,
+	updates_seen_at         TEXT,
+	created_at              TEXT    NOT NULL,
+	updated_at              TEXT    NOT NULL
+);
+
+CREATE TABLE ai_runs (
+	id                 TEXT    NOT NULL PRIMARY KEY,
+	skill              TEXT    NOT NULL,
+	skill_version      TEXT    NOT NULL,
+	provider           TEXT    NOT NULL CHECK (provider IN ('openai_compatible', 'anthropic', 'ollama')),
+	model              TEXT    NOT NULL,
+	temperature        REAL    NOT NULL,
+	status             TEXT    NOT NULL CHECK (status IN ('running', 'ok', 'error')),
+	latency_ms         INTEGER,
+	tokens_in          INTEGER,
+	tokens_out         INTEGER,
+	error              TEXT,
+	request_redacted   TEXT    NOT NULL,
+	response_redacted  TEXT,
+	created_at         TEXT    NOT NULL,
+	updated_at         TEXT    NOT NULL
+);
+
+CREATE TABLE recipes (
+	id            TEXT    NOT NULL PRIMARY KEY,
+	source_url    TEXT    NOT NULL UNIQUE,
+	site_name     TEXT,
+	title         TEXT    NOT NULL,
+	total_minutes INTEGER,
+	prep_minutes  INTEGER,
+	cook_minutes  INTEGER,
+	yield         TEXT,
+	raw_jsonld    TEXT    NOT NULL,
+	fetched_at    TEXT    NOT NULL,
+	created_at    TEXT    NOT NULL,
+	updated_at    TEXT    NOT NULL
+);
+
+CREATE TABLE recipe_ingredients (
+	id         TEXT    NOT NULL PRIMARY KEY,
+	recipe_id  TEXT    NOT NULL REFERENCES recipes (id),
+	position   INTEGER NOT NULL,
+	raw_text   TEXT    NOT NULL,
+	name       TEXT,
+	qty        TEXT,
+	unit       TEXT,
+	note       TEXT,
+	created_at TEXT    NOT NULL,
+	updated_at TEXT    NOT NULL
+);
+
+CREATE TABLE recipe_steps (
+	id         TEXT    NOT NULL PRIMARY KEY,
+	recipe_id  TEXT    NOT NULL REFERENCES recipes (id),
+	position   INTEGER NOT NULL,
+	text       TEXT    NOT NULL,
+	created_at TEXT    NOT NULL,
+	updated_at TEXT    NOT NULL
+);
+
+CREATE TABLE photos (
+	id         TEXT    NOT NULL PRIMARY KEY,
+	source_url TEXT    NOT NULL,
+	local_path TEXT    NOT NULL,
+	width      INTEGER NOT NULL,
+	height     INTEGER NOT NULL,
+	sha256     TEXT    NOT NULL,
+	fetched_at TEXT    NOT NULL,
+	created_at TEXT    NOT NULL,
+	updated_at TEXT    NOT NULL
+);
+
+CREATE TABLE dinners (
+	id               TEXT    NOT NULL PRIMARY KEY,
+	name             TEXT    NOT NULL,
+	normalized_name  TEXT    NOT NULL,
+	status           TEXT    NOT NULL CHECK (status IN ('suggested', 'quest_log', 'tried', 'regular', 'archived')),
+	origin           TEXT    NOT NULL CHECK (origin IN ('regular', 'side_quest', 'ingredients', 'quick', 'manual')),
+	method           TEXT    CHECK (method IN ('stovetop', 'oven', 'crockpot', 'grill', 'other')),
+	minutes_total    INTEGER,
+	difficulty       TEXT    CHECK (difficulty IN ('easy', 'medium', 'hard')),
+	cuisine          TEXT,
+	is_favorite      INTEGER NOT NULL CHECK (is_favorite IN (0, 1)),
+	recipe_id        TEXT    REFERENCES recipes (id),
+	photo_id         TEXT    REFERENCES photos (id),
+	grounding_status TEXT    NOT NULL CHECK (grounding_status IN ('pending', 'grounded', 'failed', 'manual')),
+	updated_by       TEXT,
+	created_at       TEXT    NOT NULL,
+	updated_at       TEXT    NOT NULL
+);
+
+CREATE TABLE suggestion_batches (
+	id         TEXT NOT NULL PRIMARY KEY,
+	trigger    TEXT NOT NULL CHECK (trigger IN ('scheduled', 'manual', 'replenish')),
+	status     TEXT NOT NULL CHECK (status IN ('pending', 'running', 'done', 'failed')),
+	ai_run_id  TEXT REFERENCES ai_runs (id),
+	created_at TEXT NOT NULL,
+	updated_at TEXT NOT NULL
+);
+
+CREATE TABLE suggestions (
+	id                TEXT NOT NULL PRIMARY KEY,
+	dinner_id         TEXT NOT NULL REFERENCES dinners (id),
+	batch_id          TEXT NOT NULL REFERENCES suggestion_batches (id),
+	reason            TEXT NOT NULL,
+	cited_regular_ids TEXT NOT NULL, -- JSON array of dinner ids
+	status            TEXT NOT NULL CHECK (status IN ('pending', 'accepted', 'declined')),
+	decided_at        TEXT,
+	created_at        TEXT NOT NULL,
+	updated_at        TEXT NOT NULL
+);
+
+CREATE TABLE week_plans (
+	id                 TEXT NOT NULL PRIMARY KEY,
+	week_start         TEXT NOT NULL UNIQUE,
+	status             TEXT NOT NULL CHECK (status IN ('draft', 'synced', 'changed')),
+	ai_note            TEXT,
+	ai_run_id          TEXT REFERENCES ai_runs (id),
+	calendar_synced_at TEXT,
+	created_at         TEXT NOT NULL,
+	updated_at         TEXT NOT NULL
+);
+
+CREATE TABLE plan_days (
+	id                TEXT    NOT NULL PRIMARY KEY,
+	plan_id           TEXT    NOT NULL REFERENCES week_plans (id),
+	date              TEXT    NOT NULL,
+	dinner_id         TEXT    REFERENCES dinners (id),
+	is_new            INTEGER NOT NULL CHECK (is_new IN (0, 1)),
+	calendar_event_id TEXT,
+	created_at        TEXT    NOT NULL,
+	updated_at        TEXT    NOT NULL,
+	UNIQUE (plan_id, date)
+);
+
+CREATE TABLE rotation_reviews (
+	id         TEXT NOT NULL PRIMARY KEY,
+	dinner_id  TEXT NOT NULL REFERENCES dinners (id),
+	plan_date  TEXT NOT NULL,
+	status     TEXT NOT NULL CHECK (status IN ('pending', 'decided')),
+	decision   TEXT CHECK (decision IN ('keep', 'drop', 'not_made')),
+	decided_at TEXT,
+	updated_by TEXT,
+	created_at TEXT NOT NULL,
+	updated_at TEXT NOT NULL,
+	UNIQUE (dinner_id, plan_date)
+);
+
+CREATE TABLE shopping_lists (
+	id              TEXT NOT NULL PRIMARY KEY,
+	week_start      TEXT NOT NULL UNIQUE,
+	plan_id         TEXT NOT NULL REFERENCES week_plans (id),
+	title           TEXT NOT NULL,
+	status          TEXT NOT NULL CHECK (status IN ('current', 'archived')),
+	sent_to_chat_at TEXT,
+	frozen_at       TEXT,
+	created_at      TEXT NOT NULL,
+	updated_at      TEXT NOT NULL
+);
+
+CREATE TABLE shopping_groups (
+	id         TEXT    NOT NULL PRIMARY KEY,
+	list_id    TEXT    NOT NULL REFERENCES shopping_lists (id),
+	dinner_id  TEXT    NOT NULL REFERENCES dinners (id),
+	position   INTEGER NOT NULL,
+	created_at TEXT    NOT NULL,
+	updated_at TEXT    NOT NULL
+);
+
+CREATE TABLE shopping_items (
+	id         TEXT    NOT NULL PRIMARY KEY,
+	group_id   TEXT    NOT NULL REFERENCES shopping_groups (id),
+	name       TEXT    NOT NULL,
+	qty        TEXT,
+	needed     INTEGER NOT NULL DEFAULT 1 CHECK (needed IN (0, 1)),
+	position   INTEGER NOT NULL,
+	created_at TEXT    NOT NULL,
+	updated_at TEXT    NOT NULL
+);
+
+CREATE TABLE ai_settings (
+	id              INTEGER PRIMARY KEY CHECK (id = 1),
+	provider        TEXT    NOT NULL CHECK (provider IN ('openai_compatible', 'anthropic', 'ollama')),
+	base_url        TEXT    NOT NULL,
+	model           TEXT    NOT NULL,
+	temperature     REAL    NOT NULL,
+	system_prompt   TEXT,
+	api_key_ct      BLOB,
+	api_key_last4   TEXT,
+	key_verified_at TEXT,
+	created_at      TEXT    NOT NULL,
+	updated_at      TEXT    NOT NULL
+);
+
+CREATE TABLE recipe_search_settings (
+	id         INTEGER PRIMARY KEY CHECK (id = 1),
+	provider   TEXT    NOT NULL CHECK (provider IN ('searxng', 'brave')),
+	base_url   TEXT,
+	api_key_ct BLOB,
+	created_at TEXT    NOT NULL,
+	updated_at TEXT    NOT NULL
+);
+
+CREATE TABLE integrations_google (
+	id               INTEGER PRIMARY KEY CHECK (id = 1),
+	refresh_token_ct BLOB    NOT NULL,
+	calendar_id      TEXT,
+	calendar_name    TEXT,
+	connected_at     TEXT    NOT NULL,
+	last_sync_error  TEXT,
+	created_at       TEXT    NOT NULL,
+	updated_at       TEXT    NOT NULL
+);
+
+CREATE TABLE chat_settings (
+	id               INTEGER PRIMARY KEY CHECK (id = 1),
+	webhook_url_ct   BLOB    NOT NULL,
+	space_label      TEXT,
+	last_test_at     TEXT,
+	last_test_status TEXT    CHECK (last_test_status IN ('ok', 'failed')),
+	created_at       TEXT    NOT NULL,
+	updated_at       TEXT    NOT NULL
+);
+
+CREATE TABLE notification_prefs (
+	event      TEXT    NOT NULL PRIMARY KEY CHECK (event IN ('plan_ready', 'list_ready', 'day_of_reminder', 'new_suggestions', 'rotation_review')),
+	push       INTEGER NOT NULL CHECK (push IN (0, 1)),
+	chat       INTEGER NOT NULL CHECK (chat IN (0, 1)),
+	created_at TEXT    NOT NULL,
+	updated_at TEXT    NOT NULL
+);
+
+CREATE TABLE push_subscriptions (
+	id              TEXT    NOT NULL PRIMARY KEY,
+	endpoint        TEXT    NOT NULL UNIQUE,
+	p256dh          TEXT    NOT NULL,
+	auth            TEXT    NOT NULL,
+	user_agent      TEXT,
+	failure_count   INTEGER NOT NULL,
+	last_success_at TEXT,
+	created_at      TEXT    NOT NULL,
+	updated_at      TEXT    NOT NULL
+);
+
+CREATE TABLE notification_outbox (
+	id              TEXT    NOT NULL PRIMARY KEY,
+	event_key       TEXT    NOT NULL,
+	channel         TEXT    NOT NULL CHECK (channel IN ('push', 'chat')),
+	payload         TEXT    NOT NULL, -- JSON
+	status          TEXT    NOT NULL CHECK (status IN ('pending', 'sent', 'failed')),
+	attempts        INTEGER NOT NULL,
+	next_attempt_at TEXT,
+	last_error      TEXT,
+	created_at      TEXT    NOT NULL,
+	updated_at      TEXT    NOT NULL,
+	UNIQUE (event_key, channel)
+);
+
+CREATE TABLE jobs (
+	id          TEXT NOT NULL PRIMARY KEY,
+	kind        TEXT NOT NULL,
+	status      TEXT NOT NULL CHECK (status IN ('pending', 'running', 'done', 'failed')),
+	payload     TEXT NOT NULL, -- JSON
+	result      TEXT,          -- JSON, written progressively
+	error       TEXT,
+	started_at  TEXT,
+	finished_at TEXT,
+	created_at  TEXT NOT NULL,
+	updated_at  TEXT NOT NULL
+);
+
+CREATE TABLE schedule_runs (
+	id         TEXT NOT NULL PRIMARY KEY,
+	schedule   TEXT NOT NULL CHECK (schedule IN ('suggestions_weekly', 'plan_draft', 'shopping_chat_autosend', 'day_of_reminder', 'rotation_check', 'week_rollover', 'ai_runs_purge')),
+	period_key TEXT NOT NULL,
+	created_at TEXT NOT NULL,
+	UNIQUE (schedule, period_key)
+);
+
+CREATE TABLE sessions (
+	id_hash      TEXT NOT NULL PRIMARY KEY,
+	subject      TEXT NOT NULL,
+	email        TEXT NOT NULL,
+	name         TEXT NOT NULL,
+	groups       TEXT NOT NULL, -- JSON array
+	csrf_token   TEXT NOT NULL,
+	created_at   TEXT NOT NULL,
+	last_seen_at TEXT NOT NULL,
+	expires_at   TEXT NOT NULL
+);

migrations/embed.go

@@ -0,0 +1,10 @@
+// Package migrations embeds the SQL schema migrations applied at boot by
+// internal/store. Files are named NNNN_slug.sql and applied in filename order.
+package migrations
+
+import "embed"
+
+// FS holds every *.sql migration in this directory.
+//
+//go:embed *.sql
+var FS embed.FS

web/index.html

@@ -0,0 +1,12 @@
+<!doctype html>
+<html lang="en">
+  <head>
+    <meta charset="UTF-8" />
+    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
+    <title>MealQuest</title>
+  </head>
+  <body>
+    <div id="root"></div>
+    <script type="module" src="/src/main.tsx"></script>
+  </body>
+</html>

web/package.json

@@ -0,0 +1,31 @@
+{
+  "name": "mealquest-web",
+  "private": true,
+  "version": "0.0.0",
+  "type": "module",
+  "scripts": {
+    "dev": "vite",
+    "build": "tsc --noEmit && vite build",
+    "preview": "vite preview",
+    "typecheck": "tsc --noEmit",
+    "test": "vitest run"
+  },
+  "dependencies": {
+    "@tanstack/react-query": "^5.104.1",
+    "react": "^19.3.0",
+    "react-dom": "^19.3.0",
+    "react-router": "^7.18.4"
+  },
+  "devDependencies": {
+    "@testing-library/dom": "^10.4.2",
+    "@testing-library/jest-dom": "^7.0.1",
+    "@testing-library/react": "^16.3.3",
+    "@types/react": "^19.3.0",
+    "@types/react-dom": "^19.3.0",
+    "@vitejs/plugin-react": "^6.1.1",
+    "jsdom": "^30.1.1",
+    "typescript": "^7.0.2",
+    "vite": "^8.3.2",
+    "vitest": "^5.0.3"
+  }
+}

web/src/App.test.tsx

@@ -0,0 +1,15 @@
+import { render, screen } from '@testing-library/react'
+import { MemoryRouter } from 'react-router'
+import { describe, expect, it } from 'vitest'
+import App from './App.tsx'
+
+describe('App', () => {
+  it('renders the MealQuest shell', () => {
+    render(
+      <MemoryRouter>
+        <App />
+      </MemoryRouter>,
+    )
+    expect(screen.getByRole('heading', { name: 'MealQuest' })).toBeInTheDocument()
+  })
+})

web/src/App.tsx

@@ -0,0 +1,25 @@
+import { useState } from 'react'
+import { QueryClient, QueryClientProvider } from '@tanstack/react-query'
+import { Route, Routes } from 'react-router'
+
+// Placeholder shell only: no product UI, auth or API calls yet.
+function Shell() {
+  return (
+    <main>
+      <h1>MealQuest</h1>
+    </main>
+  )
+}
+
+// App expects to be rendered inside a router (BrowserRouter in main.tsx,
+// MemoryRouter in tests).
+export default function App() {
+  const [queryClient] = useState(() => new QueryClient())
+  return (
+    <QueryClientProvider client={queryClient}>
+      <Routes>
+        <Route path="*" element={<Shell />} />
+      </Routes>
+    </QueryClientProvider>
+  )
+}

web/src/main.tsx

@@ -0,0 +1,17 @@
+import { StrictMode } from 'react'
+import { createRoot } from 'react-dom/client'
+import { BrowserRouter } from 'react-router'
+import App from './App.tsx'
+
+const root = document.getElementById('root')
+if (!root) {
+  throw new Error('MealQuest: #root element missing from index.html')
+}
+
+createRoot(root).render(
+  <StrictMode>
+    <BrowserRouter>
+      <App />
+    </BrowserRouter>
+  </StrictMode>,
+)

web/src/test/setup.ts

@@ -0,0 +1,7 @@
+import '@testing-library/jest-dom/vitest'
+import { cleanup } from '@testing-library/react'
+import { afterEach } from 'vitest'
+
+afterEach(() => {
+  cleanup()
+})

web/tsconfig.json

@@ -0,0 +1,21 @@
+{
+  "compilerOptions": {
+    "target": "ES2022",
+    "lib": ["ES2022", "DOM", "DOM.Iterable"],
+    "module": "ESNext",
+    "moduleResolution": "bundler",
+    "jsx": "react-jsx",
+    "types": ["vite/client"],
+    "strict": true,
+    "noUnusedLocals": true,
+    "noUnusedParameters": true,
+    "noFallthroughCasesInSwitch": true,
+    "noUncheckedIndexedAccess": true,
+    "verbatimModuleSyntax": true,
+    "isolatedModules": true,
+    "allowImportingTsExtensions": true,
+    "skipLibCheck": true,
+    "noEmit": true
+  },
+  "include": ["src", "vite.config.ts"]
+}

web/vite.config.ts

@@ -0,0 +1,19 @@
+import { defineConfig } from 'vitest/config'
+import react from '@vitejs/plugin-react'
+
+const backend = 'http://localhost:8080'
+
+export default defineConfig({
+  plugins: [react()],
+  server: {
+    proxy: {
+      '/api': backend,
+      '/auth': backend,
+      '/media': backend,
+    },
+  },
+  test: {
+    environment: 'jsdom',
+    setupFiles: ['./src/test/setup.ts'],
+  },
+})