Feature 04 task 2: Household week and local-time utilities (#18)

Co-authored-by: dev_agent <dev_agent@cubecraftlabs.com>
Co-committed-by: dev_agent <dev_agent@cubecraftlabs.com>

.gitea/workflows/ci.yml

@@ -5,7 +5,9 @@
 # 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).
+# `make build` log shows the -X main.version=... value). `make sqlc-check`
+# fails when the committed internal/db code is stale against sqlc.yaml,
+# migrations/ and migrations/queries/.
 name: CI
 
 on: [push, pull_request]
@@ -28,5 +30,10 @@ jobs:
       - name: make test
         run: make test
 
+      - name: make sqlc-check
+        run: |
+          make sqlc-install
+          make sqlc-check SQLC="$(go env GOPATH)/bin/sqlc"
+
       - name: make build
         run: make build

Makefile

@@ -5,6 +5,9 @@
 #   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.
+#   make sqlc    Regenerate internal/db from sqlc.yaml with the pinned sqlc.
+#   make sqlc-check    Regenerate; fail if internal/db differs from the commit.
+#   make sqlc-install  Install the pinned sqlc into the Go bin directory.
 
 FRONTEND_DIST := internal/frontend/dist
 # Dirty means a tracked change outside the generated embed output, which
@@ -14,7 +17,13 @@ VERSION ?= $(shell git describe --tags --always 2>/dev/null || echo dev)$(if $(G
 IMAGE ?= mealquest:$(VERSION)
 LDFLAGS := -s -w -X main.version=$(VERSION)
 
-.PHONY: dev test go-test web-test build web embed check-embed-clean docker
+# sqlc is a dev tool only, never a go.mod dependency. Generated code records
+# the version that wrote it, so every run must use exactly this one.
+SQLC_VERSION := v1.31.1
+SQLC ?= sqlc
+SQLC_OUT := internal/db
+
+.PHONY: dev test go-test web-test build web embed check-embed-clean docker sqlc sqlc-check sqlc-install
 
 # 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.
@@ -58,3 +67,22 @@ check-embed-clean:
 
 docker:
 	docker build --build-arg VERSION=$(VERSION) -t $(IMAGE) .
+
+# sqlc $(SQLC_VERSION) needs Go 1.26 to build; GOTOOLCHAIN=auto lets go fetch
+# it for this install only, even where the environment pins the local Go.
+sqlc-install:
+	GOTOOLCHAIN=auto go install github.com/sqlc-dev/sqlc/cmd/sqlc@$(SQLC_VERSION)
+
+# Previously generated files are removed first so a dropped query file does
+# not leave its stale output behind; hand-written files are kept.
+sqlc:
+	@v="$$($(SQLC) version)"; \
+	if [ "$$v" != "$(SQLC_VERSION)" ]; then echo "sqlc $(SQLC_VERSION) required, $(SQLC) is $$v"; exit 1; fi
+	find $(SQLC_OUT) -maxdepth 1 -name '*.go' -exec grep -l -x '// Code generated by sqlc. DO NOT EDIT.' {} + | xargs -r rm --
+	$(SQLC) generate
+
+# Fails on any difference from the committed generated code, including new
+# or deleted files.
+sqlc-check: sqlc
+	@drift="$$(git status --porcelain --untracked-files=all -- $(SQLC_OUT))"; \
+	if [ -n "$$drift" ]; then echo "internal/db is stale; run make sqlc and commit:"; echo "$$drift"; git diff -- $(SQLC_OUT); exit 1; fi

cmd/server/boot_test.go

@@ -0,0 +1,195 @@
+package main
+
+import (
+	"bytes"
+	"context"
+	"crypto/rand"
+	"encoding/base64"
+	"errors"
+	"io/fs"
+	"log/slog"
+	"os"
+	"path/filepath"
+	"strings"
+	"testing"
+	"testing/fstest"
+	"time"
+
+	"code.cubecraftlabs.com/Maniacs_Mansion/Meal-Quest/internal/config"
+	"code.cubecraftlabs.com/Maniacs_Mansion/Meal-Quest/internal/secretbox"
+	"code.cubecraftlabs.com/Maniacs_Mansion/Meal-Quest/internal/store"
+	"code.cubecraftlabs.com/Maniacs_Mansion/Meal-Quest/migrations"
+)
+
+// useBootMigrations swaps bootMigrations for the test and restores it after.
+// Tests that call it must not run in parallel.
+func useBootMigrations(t *testing.T, m fstest.MapFS) {
+	t.Helper()
+	saved := bootMigrations
+	bootMigrations = m
+	t.Cleanup(func() { bootMigrations = saved })
+}
+
+// loadConfig loads a configuration through config.Load whose secret key file
+// holds keyFile. Tests that call it must not run in parallel.
+func loadConfig(t *testing.T, keyFile string) config.Config {
+	t.Helper()
+	keyPath := filepath.Join(t.TempDir(), "secret_key")
+	if err := os.WriteFile(keyPath, []byte(keyFile), 0o600); err != nil {
+		t.Fatal(err)
+	}
+	t.Setenv(config.EnvHTTPAddr, "127.0.0.1:0")
+	t.Setenv(config.EnvPublicURL, "http://localhost")
+	t.Setenv(config.EnvDataDir, t.TempDir())
+	t.Setenv(config.EnvShutdownTimeout, "1s")
+	t.Setenv(config.EnvSecretKeyFile, keyPath)
+	cfg, err := config.Load()
+	if err != nil {
+		t.Fatal(err)
+	}
+	return cfg
+}
+
+// validKeyFile returns secret key file contents as written by the README:
+// base64 of 32 random bytes followed by a newline.
+func validKeyFile(t *testing.T) string {
+	t.Helper()
+	key := make([]byte, secretbox.KeySize)
+	if _, err := rand.Read(key); err != nil {
+		t.Fatal(err)
+	}
+	return base64.StdEncoding.EncodeToString(key) + "\n"
+}
+
+var brokenMigrations = fstest.MapFS{
+	"0000_good.sql":   {Data: []byte("CREATE TABLE good (id INTEGER PRIMARY KEY);")},
+	"0001_broken.sql": {Data: []byte("CREATE TABLE broken (id INTEGER PRIMARY KEY;")},
+}
+
+func TestOpenStoreMigrationFailureStopsBoot(t *testing.T) {
+	useBootMigrations(t, brokenMigrations)
+
+	db, err := openStore(context.Background(), t.TempDir())
+	if err == nil {
+		db.Close()
+		t.Fatal("openStore succeeded with a broken migration")
+	}
+	if db != nil {
+		t.Error("openStore returned a store alongside the error")
+	}
+	if !strings.Contains(err.Error(), "0001_broken.sql") {
+		t.Errorf("error does not name the broken migration: %v", err)
+	}
+}
+
+func TestOpenStoreAppliesEmbeddedMigrations(t *testing.T) {
+	ctx := context.Background()
+	db, err := openStore(ctx, t.TempDir())
+	if err != nil {
+		t.Fatal(err)
+	}
+	t.Cleanup(func() {
+		if err := db.Close(); err != nil {
+			t.Error(err)
+		}
+	})
+	var n int
+	if err := db.Reader.QueryRowContext(ctx, "SELECT count(*) FROM schema_migrations").Scan(&n); err != nil {
+		t.Fatal(err)
+	}
+	files, err := fs.Glob(migrations.FS, "*.sql")
+	if err != nil {
+		t.Fatal(err)
+	}
+	if n != len(files) {
+		t.Errorf("schema_migrations has %d rows, want %d", n, len(files))
+	}
+}
+
+func TestRunMigrationFailureReturnsBeforeListening(t *testing.T) {
+	useBootMigrations(t, brokenMigrations)
+
+	var logs bytes.Buffer
+	cfg := loadConfig(t, validKeyFile(t))
+	errc := make(chan error, 1)
+	go func() { errc <- run(cfg, slog.New(slog.NewJSONHandler(&logs, nil))) }()
+
+	select {
+	case err := <-errc:
+		if err == nil || !strings.Contains(err.Error(), "0001_broken.sql") {
+			t.Fatalf("run error = %v, want the broken migration's error", err)
+		}
+	case <-time.After(10 * time.Second):
+		t.Fatal("run kept going after a migration failure")
+	}
+	if strings.Contains(logs.String(), `"listening"`) {
+		t.Errorf("run started listening despite the migration failure: %s", logs.String())
+	}
+}
+
+func TestOpenSecretBoxAcceptsReadmeKey(t *testing.T) {
+	cfg := loadConfig(t, validKeyFile(t))
+	box, err := openSecretBox(cfg.SecretKey)
+	if err != nil {
+		t.Fatal(err)
+	}
+	token, err := box.Encrypt([]byte("x"))
+	if err != nil {
+		t.Fatal(err)
+	}
+	if _, err := box.Decrypt(token); err != nil {
+		t.Fatal(err)
+	}
+}
+
+func TestRunBadSecretKeyStopsBoot(t *testing.T) {
+	short := base64.StdEncoding.EncodeToString(make([]byte, 31))
+	tests := []struct {
+		name    string
+		keyFile string
+		want    error
+	}{
+		{"not base64", "this is not a base64 key!\n", secretbox.ErrKeyEncoding},
+		{"wrong length", short + "\n", secretbox.ErrKeyLength},
+	}
+	for _, tt := range tests {
+		t.Run(tt.name, func(t *testing.T) {
+			cfg := loadConfig(t, tt.keyFile)
+			var logs bytes.Buffer
+			errc := make(chan error, 1)
+			go func() { errc <- run(cfg, slog.New(slog.NewJSONHandler(&logs, nil))) }()
+
+			var err error
+			select {
+			case err = <-errc:
+			case <-time.After(10 * time.Second):
+				t.Fatal("run kept going with a bad secret key")
+			}
+			if !errors.Is(err, tt.want) {
+				t.Fatalf("run error = %v, want %v", err, tt.want)
+			}
+			if !strings.Contains(err.Error(), config.EnvSecretKeyFile) {
+				t.Errorf("error does not name %s: %v", config.EnvSecretKeyFile, err)
+			}
+			if key := strings.TrimSpace(tt.keyFile); strings.Contains(err.Error(), key) {
+				t.Errorf("error leaks the key: %v", err)
+			}
+			if strings.Contains(logs.String(), `"listening"`) {
+				t.Errorf("run started listening despite the bad key: %s", logs.String())
+			}
+			if _, statErr := os.Stat(filepath.Join(cfg.DataDir, store.FileName)); !errors.Is(statErr, fs.ErrNotExist) {
+				t.Errorf("run opened the database before rejecting the key (stat: %v)", statErr)
+			}
+		})
+	}
+}
+
+func TestLoadMissingSecretKeyFileStopsBoot(t *testing.T) {
+	loadConfig(t, validKeyFile(t)) // sets every other variable
+	t.Setenv(config.EnvSecretKeyFile, filepath.Join(t.TempDir(), "missing"))
+	_, err := config.Load()
+	var cfgErr *config.Error
+	if !errors.As(err, &cfgErr) || !strings.Contains(err.Error(), config.EnvSecretKeyFile) {
+		t.Fatalf("Load error = %v, want a config error naming %s", err, config.EnvSecretKeyFile)
+	}
+}

cmd/server/main.go

@@ -5,6 +5,7 @@ import (
 	"context"
 	"errors"
 	"fmt"
+	"io/fs"
 	"log/slog"
 	"net/http"
 	"os"
@@ -14,6 +15,7 @@ import (
 
 	"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/secretbox"
 	"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"
@@ -40,7 +42,11 @@ 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 := openSecretBox(cfg.SecretKey); err != nil {
+		return err
+	}
+
+	db, err := openStore(ctx, cfg.DataDir)
 	if err != nil {
 		return err
 	}
@@ -49,9 +55,6 @@ func run(cfg config.Config, logger *slog.Logger) error {
 			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 {
@@ -88,6 +91,33 @@ func run(cfg config.Config, logger *slog.Logger) error {
 	return nil
 }
 
+// openSecretBox builds the secret box from the configured key, naming the
+// variable whose file holds a bad key. The key itself is never reported.
+func openSecretBox(key config.Secret) (*secretbox.Box, error) {
+	box, err := secretbox.New(key.Reveal())
+	if err != nil {
+		return nil, fmt.Errorf("%s: %w", config.EnvSecretKeyFile, err)
+	}
+	return box, nil
+}
+
+// bootMigrations is the migration set applied at boot. Tests replace it to
+// prove a failing migration stops the boot.
+var bootMigrations fs.FS = migrations.FS
+
+// openStore opens the database in dataDir and applies bootMigrations. On a
+// migration failure it closes the database and returns the error.
+func openStore(ctx context.Context, dataDir string) (*store.Store, error) {
+	db, err := store.Open(ctx, dataDir)
+	if err != nil {
+		return nil, err
+	}
+	if err := store.Migrate(ctx, db.Writer, bootMigrations); err != nil {
+		return nil, errors.Join(err, db.Close())
+	}
+	return db, nil
+}
+
 // logListening logs the startup line. Only the public URL's origin is logged
 // so no credential-bearing URL component can reach the log.
 func logListening(logger *slog.Logger, cfg config.Config) {

internal/db/db.go

@@ -0,0 +1,31 @@
+// Code generated by sqlc. DO NOT EDIT.
+// versions:
+//   sqlc v1.31.1
+
+package db
+
+import (
+	"context"
+	"database/sql"
+)
+
+type DBTX interface {
+	ExecContext(context.Context, string, ...interface{}) (sql.Result, error)
+	PrepareContext(context.Context, string) (*sql.Stmt, error)
+	QueryContext(context.Context, string, ...interface{}) (*sql.Rows, error)
+	QueryRowContext(context.Context, string, ...interface{}) *sql.Row
+}
+
+func New(db DBTX) *Queries {
+	return &Queries{db: db}
+}
+
+type Queries struct {
+	db DBTX
+}
+
+func (q *Queries) WithTx(tx *sql.Tx) *Queries {
+	return &Queries{
+		db: tx,
+	}
+}

internal/db/db_test.go

@@ -0,0 +1,564 @@
+package db_test
+
+import (
+	"context"
+	"database/sql"
+	"errors"
+	"reflect"
+	"testing"
+
+	"code.cubecraftlabs.com/Maniacs_Mansion/Meal-Quest/internal/db"
+	"code.cubecraftlabs.com/Maniacs_Mansion/Meal-Quest/internal/store"
+	"code.cubecraftlabs.com/Maniacs_Mansion/Meal-Quest/migrations"
+)
+
+const (
+	created = "2026-10-03T12:00:00.000Z"
+	updated = "2026-10-04T12:00:00.000Z"
+)
+
+// openQueries returns queries on the writer of a freshly migrated database
+// in a temp directory.
+func openQueries(t *testing.T) (*store.Store, *db.Queries) {
+	t.Helper()
+	ctx := context.Background()
+	s, err := store.Open(ctx, t.TempDir())
+	if err != nil {
+		t.Fatalf("Open: %v", err)
+	}
+	t.Cleanup(func() {
+		if err := s.Close(); err != nil {
+			t.Errorf("Close: %v", err)
+		}
+	})
+	if err := store.Migrate(ctx, s.Writer, migrations.FS); err != nil {
+		t.Fatalf("Migrate: %v", err)
+	}
+	return s, db.New(s.Writer)
+}
+
+func str(s string) sql.NullString { return sql.NullString{String: s, Valid: true} }
+func num(n int64) sql.NullInt64   { return sql.NullInt64{Int64: n, Valid: true} }
+
+func mustNoRows(t *testing.T, what string, err error) {
+	t.Helper()
+	if !errors.Is(err, sql.ErrNoRows) {
+		t.Fatalf("%s: got %v, want sql.ErrNoRows", what, err)
+	}
+}
+
+func createPhoto(t *testing.T, q *db.Queries, sha string) db.Photo {
+	t.Helper()
+	p, err := q.CreatePhoto(context.Background(), db.CreatePhotoParams{
+		ID:        store.NewID(),
+		SourceUrl: "https://example.com/" + sha + ".jpg",
+		LocalPath: "photos/" + sha + ".jpg",
+		Width:     800,
+		Height:    600,
+		Sha256:    sha,
+		FetchedAt: created,
+		CreatedAt: created,
+		UpdatedAt: created,
+	})
+	if err != nil {
+		t.Fatalf("CreatePhoto: %v", err)
+	}
+	return p
+}
+
+func upsertRecipe(t *testing.T, q *db.Queries, id, url, title, at string) db.Recipe {
+	t.Helper()
+	r, err := q.UpsertRecipe(context.Background(), db.UpsertRecipeParams{
+		ID:           id,
+		SourceUrl:    url,
+		SiteName:     str("Example"),
+		Title:        title,
+		TotalMinutes: num(30),
+		PrepMinutes:  num(10),
+		CookMinutes:  num(20),
+		Yield:        str("4 servings"),
+		RawJsonld:    `{"@type":"Recipe"}`,
+		FetchedAt:    at,
+		CreatedAt:    at,
+		UpdatedAt:    at,
+	})
+	if err != nil {
+		t.Fatalf("UpsertRecipe: %v", err)
+	}
+	return r
+}
+
+func TestDinners(t *testing.T) {
+	ctx := context.Background()
+	_, q := openQueries(t)
+	recipe := upsertRecipe(t, q, store.NewID(), "https://example.com/tacos", "Tacos", created)
+	photo := createPhoto(t, q, "aa11")
+
+	create := db.CreateDinnerParams{
+		ID:              store.NewID(),
+		Name:            "Taco Tuesday",
+		NormalizedName:  "taco tuesday",
+		Status:          "regular",
+		Origin:          "regular",
+		Method:          str("stovetop"),
+		MinutesTotal:    num(30),
+		Difficulty:      str("easy"),
+		Cuisine:         str("Mexican"),
+		IsFavorite:      0,
+		RecipeID:        str(recipe.ID),
+		PhotoID:         str(photo.ID),
+		GroundingStatus: "grounded",
+		UpdatedBy:       str("alex@example.com"),
+		CreatedAt:       created,
+		UpdatedAt:       created,
+	}
+	want := db.Dinner(create)
+	got, err := q.CreateDinner(ctx, create)
+	if err != nil {
+		t.Fatalf("CreateDinner: %v", err)
+	}
+	if got != want {
+		t.Fatalf("CreateDinner returned %+v, want %+v", got, want)
+	}
+	if got, err = q.GetDinner(ctx, create.ID); err != nil || got != want {
+		t.Fatalf("GetDinner = %+v, %v; want %+v", got, err, want)
+	}
+	_, err = q.GetDinner(ctx, "missing")
+	mustNoRows(t, "GetDinner missing", err)
+
+	// Second dinner sorts before the first by normalized name, in a
+	// different status so the status filter is exercised.
+	other, err := q.CreateDinner(ctx, db.CreateDinnerParams{
+		ID:              store.NewID(),
+		Name:            "Apple Pork Chops",
+		NormalizedName:  "apple pork chops",
+		Status:          "quest_log",
+		Origin:          "side_quest",
+		GroundingStatus: "pending",
+		CreatedAt:       created,
+		UpdatedAt:       created,
+	})
+	if err != nil {
+		t.Fatalf("CreateDinner other: %v", err)
+	}
+	third, err := q.CreateDinner(ctx, db.CreateDinnerParams{
+		ID:              store.NewID(),
+		Name:            "Beef Stew",
+		NormalizedName:  "beef stew",
+		Status:          "regular",
+		Origin:          "manual",
+		GroundingStatus: "manual",
+		CreatedAt:       created,
+		UpdatedAt:       created,
+	})
+	if err != nil {
+		t.Fatalf("CreateDinner third: %v", err)
+	}
+	listIDs := func(status string) []string {
+		t.Helper()
+		ds, err := q.ListDinnersByStatus(ctx, status)
+		if err != nil {
+			t.Fatalf("ListDinnersByStatus(%s): %v", status, err)
+		}
+		ids := []string{}
+		for _, d := range ds {
+			ids = append(ids, d.ID)
+		}
+		return ids
+	}
+	assertIDs(t, "regular", listIDs("regular"), third.ID, create.ID)
+	assertIDs(t, "quest_log", listIDs("quest_log"), other.ID)
+	assertIDs(t, "tried", listIDs("tried"))
+
+	update := db.UpdateDinnerParams{
+		ID:              create.ID,
+		Name:            "Fish Tacos",
+		NormalizedName:  "fish tacos",
+		Status:          "tried",
+		Origin:          "side_quest",
+		Method:          str("grill"),
+		MinutesTotal:    num(25),
+		Difficulty:      str("medium"),
+		Cuisine:         sql.NullString{},
+		RecipeID:        sql.NullString{},
+		PhotoID:         str(photo.ID),
+		GroundingStatus: "failed",
+		UpdatedBy:       str("sam@example.com"),
+		UpdatedAt:       updated,
+	}
+	got, err = q.UpdateDinner(ctx, update)
+	if err != nil {
+		t.Fatalf("UpdateDinner: %v", err)
+	}
+	want = db.Dinner{
+		ID: create.ID, Name: update.Name, NormalizedName: update.NormalizedName,
+		Status: update.Status, Origin: update.Origin, Method: update.Method,
+		MinutesTotal: update.MinutesTotal, Difficulty: update.Difficulty,
+		Cuisine: update.Cuisine, IsFavorite: 0, RecipeID: update.RecipeID,
+		PhotoID: update.PhotoID, GroundingStatus: update.GroundingStatus,
+		UpdatedBy: update.UpdatedBy, CreatedAt: created, UpdatedAt: updated,
+	}
+	if got != want {
+		t.Fatalf("UpdateDinner returned %+v, want %+v", got, want)
+	}
+	if got, err = q.GetDinner(ctx, create.ID); err != nil || got != want {
+		t.Fatalf("GetDinner after update = %+v, %v; want %+v", got, err, want)
+	}
+	update.ID = "missing"
+	_, err = q.UpdateDinner(ctx, update)
+	mustNoRows(t, "UpdateDinner missing", err)
+
+	got, err = q.SetDinnerFavorite(ctx, db.SetDinnerFavoriteParams{
+		ID: create.ID, IsFavorite: 1, UpdatedBy: str("alex@example.com"), UpdatedAt: updated,
+	})
+	if err != nil {
+		t.Fatalf("SetDinnerFavorite: %v", err)
+	}
+	want.IsFavorite, want.UpdatedBy = 1, str("alex@example.com")
+	if got != want {
+		t.Fatalf("SetDinnerFavorite returned %+v, want %+v", got, want)
+	}
+	got, err = q.SetDinnerFavorite(ctx, db.SetDinnerFavoriteParams{
+		ID: create.ID, IsFavorite: 0, UpdatedBy: str("sam@example.com"), UpdatedAt: updated,
+	})
+	if err != nil || got.IsFavorite != 0 || got.UpdatedBy != str("sam@example.com") {
+		t.Fatalf("SetDinnerFavorite off = %+v, %v", got, err)
+	}
+	_, err = q.SetDinnerFavorite(ctx, db.SetDinnerFavoriteParams{ID: "missing", IsFavorite: 1, UpdatedAt: updated})
+	mustNoRows(t, "SetDinnerFavorite missing", err)
+
+	const archivedAt = "2026-10-05T12:00:00.000Z"
+	got, err = q.ArchiveDinner(ctx, db.ArchiveDinnerParams{
+		ID: create.ID, UpdatedBy: str("alex@example.com"), UpdatedAt: archivedAt,
+	})
+	if err != nil {
+		t.Fatalf("ArchiveDinner: %v", err)
+	}
+	want.IsFavorite, want.Status, want.UpdatedBy, want.UpdatedAt = 0, "archived", str("alex@example.com"), archivedAt
+	if got != want {
+		t.Fatalf("ArchiveDinner returned %+v, want %+v", got, want)
+	}
+	assertIDs(t, "archived", listIDs("archived"), create.ID)
+	assertIDs(t, "tried after archive", listIDs("tried"))
+	_, err = q.ArchiveDinner(ctx, db.ArchiveDinnerParams{ID: "missing", UpdatedAt: archivedAt})
+	mustNoRows(t, "ArchiveDinner missing", err)
+}
+
+func assertIDs(t *testing.T, what string, got []string, want ...string) {
+	t.Helper()
+	if len(got) != len(want) {
+		t.Fatalf("%s: got ids %v, want %v", what, got, want)
+	}
+	for i := range got {
+		if got[i] != want[i] {
+			t.Fatalf("%s: got ids %v, want %v", what, got, want)
+		}
+	}
+}
+
+func TestRecipeUpsertWithIngredientsAndSteps(t *testing.T) {
+	ctx := context.Background()
+	s, q := openQueries(t)
+	const url = "https://example.com/chili"
+
+	_, err := q.GetRecipeBySourceURL(ctx, url)
+	mustNoRows(t, "GetRecipeBySourceURL before insert", err)
+
+	// saveRecipe is how a caller stores a fetched recipe: upsert by
+	// source_url, then replace the children, all in one transaction.
+	saveRecipe := func(title, at string, ingredients, steps []string) db.Recipe {
+		t.Helper()
+		var r db.Recipe
+		err := s.WithTx(ctx, func(tx *sql.Tx) error {
+			qtx := q.WithTx(tx)
+			var err error
+			r, err = qtx.UpsertRecipe(ctx, db.UpsertRecipeParams{
+				ID: store.NewID(), SourceUrl: url, SiteName: str("Example"), Title: title,
+				TotalMinutes: num(60), RawJsonld: `{"name":"` + title + `"}`,
+				FetchedAt: at, CreatedAt: at, UpdatedAt: at,
+			})
+			if err != nil {
+				return err
+			}
+			if err := qtx.DeleteRecipeIngredients(ctx, r.ID); err != nil {
+				return err
+			}
+			if err := qtx.DeleteRecipeSteps(ctx, r.ID); err != nil {
+				return err
+			}
+			for i, text := range ingredients {
+				if err := qtx.CreateRecipeIngredient(ctx, db.CreateRecipeIngredientParams{
+					ID: store.NewID(), RecipeID: r.ID, Position: int64(i), RawText: text,
+					Name: str(text), Qty: str("1"), Unit: str("cup"), Note: sql.NullString{},
+					CreatedAt: at, UpdatedAt: at,
+				}); err != nil {
+					return err
+				}
+			}
+			for i, text := range steps {
+				if err := qtx.CreateRecipeStep(ctx, db.CreateRecipeStepParams{
+					ID: store.NewID(), RecipeID: r.ID, Position: int64(i), Text: text,
+					CreatedAt: at, UpdatedAt: at,
+				}); err != nil {
+					return err
+				}
+			}
+			return nil
+		})
+		if err != nil {
+			t.Fatalf("save recipe %q: %v", title, err)
+		}
+		return r
+	}
+	assertRecipe := func(id, title, createdAt, updatedAt string, ingredients, steps []string) {
+		t.Helper()
+		r, err := q.GetRecipe(ctx, id)
+		if err != nil {
+			t.Fatalf("GetRecipe: %v", err)
+		}
+		if r.Title != title || r.SourceUrl != url || r.CreatedAt != createdAt || r.UpdatedAt != updatedAt || r.FetchedAt != updatedAt {
+			t.Fatalf("GetRecipe = %+v; want title %q, created %s, updated %s", r, title, createdAt, updatedAt)
+		}
+		byURL, err := q.GetRecipeBySourceURL(ctx, url)
+		if err != nil || byURL != r {
+			t.Fatalf("GetRecipeBySourceURL = %+v, %v; want %+v", byURL, err, r)
+		}
+		ings, err := q.ListRecipeIngredients(ctx, id)
+		if err != nil {
+			t.Fatalf("ListRecipeIngredients: %v", err)
+		}
+		gotIngs := []string{}
+		for i, ing := range ings {
+			if ing.RecipeID != id || ing.Position != int64(i) || ing.Name != str(ing.RawText) {
+				t.Fatalf("ingredient %d = %+v", i, ing)
+			}
+			gotIngs = append(gotIngs, ing.RawText)
+		}
+		assertIDs(t, "ingredients", gotIngs, ingredients...)
+		sts, err := q.ListRecipeSteps(ctx, id)
+		if err != nil {
+			t.Fatalf("ListRecipeSteps: %v", err)
+		}
+		gotSteps := []string{}
+		for i, st := range sts {
+			if st.RecipeID != id || st.Position != int64(i) {
+				t.Fatalf("step %d = %+v", i, st)
+			}
+			gotSteps = append(gotSteps, st.Text)
+		}
+		assertIDs(t, "steps", gotSteps, steps...)
+	}
+
+	first := saveRecipe("Chili", created, []string{"beans", "beef", "tomatoes"}, []string{"Brown the beef.", "Simmer everything."})
+	assertRecipe(first.ID, "Chili", created, created, []string{"beans", "beef", "tomatoes"}, []string{"Brown the beef.", "Simmer everything."})
+
+	// Re-fetching the same URL keeps the id and created_at and replaces the
+	// fields and children.
+	second := saveRecipe("White Chili", updated, []string{"chicken", "white beans"}, []string{"Simmer."})
+	if second.ID != first.ID {
+		t.Fatalf("upsert changed id: %s -> %s", first.ID, second.ID)
+	}
+	assertRecipe(first.ID, "White Chili", created, updated, []string{"chicken", "white beans"}, []string{"Simmer."})
+
+	// A different recipe's children are not listed.
+	upsertRecipe(t, q, store.NewID(), "https://example.com/other", "Other", created)
+	var n int
+	if err := s.Reader.QueryRowContext(ctx, "SELECT count(*) FROM recipes").Scan(&n); err != nil || n != 2 {
+		t.Fatalf("recipes count = %d, %v; want 2", n, err)
+	}
+	_, err = q.GetRecipe(ctx, "missing")
+	mustNoRows(t, "GetRecipe missing", err)
+}
+
+func TestPhotos(t *testing.T) {
+	ctx := context.Background()
+	_, q := openQueries(t)
+
+	_, err := q.GetPhotoBySHA256(ctx, "bb22")
+	mustNoRows(t, "GetPhotoBySHA256 before insert", err)
+
+	first := createPhoto(t, q, "bb22")
+	createPhoto(t, q, "cc33")
+	if first.Sha256 != "bb22" || first.Width != 800 || first.Height != 600 || first.CreatedAt != created {
+		t.Fatalf("CreatePhoto returned %+v", first)
+	}
+	got, err := q.GetPhotoBySHA256(ctx, "bb22")
+	if err != nil || got != first {
+		t.Fatalf("GetPhotoBySHA256 = %+v, %v; want %+v", got, err, first)
+	}
+
+	// sha256 is not unique in the schema; the earliest photo is returned.
+	createPhoto(t, q, "bb22")
+	if got, err = q.GetPhotoBySHA256(ctx, "bb22"); err != nil || got != first {
+		t.Fatalf("GetPhotoBySHA256 with duplicate = %+v, %v; want %+v", got, err, first)
+	}
+}
+
+func TestHouseholdSettings(t *testing.T) {
+	ctx := context.Background()
+	_, q := openQueries(t)
+	_, err := q.GetHouseholdSettings(ctx)
+	mustNoRows(t, "GetHouseholdSettings before upsert", err)
+
+	p := db.UpsertHouseholdSettingsParams{
+		Timezone: "America/New_York", DinnerTime: "17:30", ReminderTime: "16:30",
+		ReviewTime: "09:00", PlanDraftDow: 5, PlanDraftTime: "09:00",
+		SuggestionsDow: 0, SuggestionsTime: "08:00", ChatAutosendDow: 6,
+		ChatAutosendTime: "08:00", CreatedAt: created, UpdatedAt: created,
+	}
+	want := db.HouseholdSetting{
+		ID: 1, Timezone: p.Timezone, DinnerTime: p.DinnerTime, ReminderTime: p.ReminderTime,
+		ReviewTime: p.ReviewTime, PlanDraftDow: p.PlanDraftDow, PlanDraftTime: p.PlanDraftTime,
+		SuggestionsDow: p.SuggestionsDow, SuggestionsTime: p.SuggestionsTime,
+		ChatAutosendDow: p.ChatAutosendDow, ChatAutosendTime: p.ChatAutosendTime,
+		CreatedAt: created, UpdatedAt: created,
+	}
+	assertSettings(t, "insert", q.UpsertHouseholdSettings, q.GetHouseholdSettings, p, want)
+
+	p = db.UpsertHouseholdSettingsParams{
+		Timezone: "America/Chicago", DinnerTime: "18:00", ReminderTime: "17:00",
+		ReviewTime: "10:00", PlanDraftDow: 4, PlanDraftTime: "20:00",
+		SuggestionsDow: 1, SuggestionsTime: "07:00", ChatAutosendDow: 0,
+		ChatAutosendTime: "09:30", OnboardingCompletedAt: str(updated),
+		UpdatesSeenAt: str(updated), CreatedAt: updated, UpdatedAt: updated,
+	}
+	want = db.HouseholdSetting{
+		ID: 1, Timezone: p.Timezone, DinnerTime: p.DinnerTime, ReminderTime: p.ReminderTime,
+		ReviewTime: p.ReviewTime, PlanDraftDow: p.PlanDraftDow, PlanDraftTime: p.PlanDraftTime,
+		SuggestionsDow: p.SuggestionsDow, SuggestionsTime: p.SuggestionsTime,
+		ChatAutosendDow: p.ChatAutosendDow, ChatAutosendTime: p.ChatAutosendTime,
+		OnboardingCompletedAt: p.OnboardingCompletedAt, UpdatesSeenAt: p.UpdatesSeenAt,
+		CreatedAt: created, UpdatedAt: updated,
+	}
+	assertSettings(t, "update", q.UpsertHouseholdSettings, q.GetHouseholdSettings, p, want)
+}
+
+func TestAISettings(t *testing.T) {
+	ctx := context.Background()
+	_, q := openQueries(t)
+	_, err := q.GetAISettings(ctx)
+	mustNoRows(t, "GetAISettings before upsert", err)
+
+	p := db.UpsertAISettingsParams{
+		Provider: "ollama", BaseUrl: "http://ollama:11434", Model: "llama3",
+		Temperature: 0.7, CreatedAt: created, UpdatedAt: created,
+	}
+	want := db.AiSetting{
+		ID: 1, Provider: p.Provider, BaseUrl: p.BaseUrl, Model: p.Model,
+		Temperature: p.Temperature, CreatedAt: created, UpdatedAt: created,
+	}
+	assertSettings(t, "insert", q.UpsertAISettings, q.GetAISettings, p, want)
+
+	p = db.UpsertAISettingsParams{
+		Provider: "anthropic", BaseUrl: "https://api.anthropic.com", Model: "claude",
+		Temperature: 0.2, SystemPrompt: str("Be brief."), ApiKeyCt: []byte{1, 2, 3},
+		ApiKeyLast4: str("wxyz"), KeyVerifiedAt: str(updated), CreatedAt: updated, UpdatedAt: updated,
+	}
+	want = db.AiSetting{
+		ID: 1, Provider: p.Provider, BaseUrl: p.BaseUrl, Model: p.Model,
+		Temperature: p.Temperature, SystemPrompt: p.SystemPrompt, ApiKeyCt: p.ApiKeyCt,
+		ApiKeyLast4: p.ApiKeyLast4, KeyVerifiedAt: p.KeyVerifiedAt,
+		CreatedAt: created, UpdatedAt: updated,
+	}
+	assertSettings(t, "update", q.UpsertAISettings, q.GetAISettings, p, want)
+}
+
+func TestRecipeSearchSettings(t *testing.T) {
+	ctx := context.Background()
+	_, q := openQueries(t)
+	_, err := q.GetRecipeSearchSettings(ctx)
+	mustNoRows(t, "GetRecipeSearchSettings before upsert", err)
+
+	p := db.UpsertRecipeSearchSettingsParams{
+		Provider: "searxng", BaseUrl: str("http://searxng:8080"), CreatedAt: created, UpdatedAt: created,
+	}
+	want := db.RecipeSearchSetting{
+		ID: 1, Provider: p.Provider, BaseUrl: p.BaseUrl, CreatedAt: created, UpdatedAt: created,
+	}
+	assertSettings(t, "insert", q.UpsertRecipeSearchSettings, q.GetRecipeSearchSettings, p, want)
+
+	p = db.UpsertRecipeSearchSettingsParams{
+		Provider: "brave", ApiKeyCt: []byte{9, 8, 7}, CreatedAt: updated, UpdatedAt: updated,
+	}
+	want = db.RecipeSearchSetting{
+		ID: 1, Provider: p.Provider, ApiKeyCt: p.ApiKeyCt, CreatedAt: created, UpdatedAt: updated,
+	}
+	assertSettings(t, "update", q.UpsertRecipeSearchSettings, q.GetRecipeSearchSettings, p, want)
+}
+
+func TestIntegrationsGoogle(t *testing.T) {
+	ctx := context.Background()
+	_, q := openQueries(t)
+	_, err := q.GetIntegrationsGoogle(ctx)
+	mustNoRows(t, "GetIntegrationsGoogle before upsert", err)
+
+	p := db.UpsertIntegrationsGoogleParams{
+		RefreshTokenCt: []byte{1}, ConnectedAt: created, CreatedAt: created, UpdatedAt: created,
+	}
+	want := db.IntegrationsGoogle{
+		ID: 1, RefreshTokenCt: p.RefreshTokenCt, ConnectedAt: created, CreatedAt: created, UpdatedAt: created,
+	}
+	assertSettings(t, "insert", q.UpsertIntegrationsGoogle, q.GetIntegrationsGoogle, p, want)
+
+	p = db.UpsertIntegrationsGoogleParams{
+		RefreshTokenCt: []byte{2, 3}, CalendarID: str("family@group.calendar.google.com"),
+		CalendarName: str("Family"), ConnectedAt: updated, LastSyncError: str("quota exceeded"),
+		CreatedAt: updated, UpdatedAt: updated,
+	}
+	want = db.IntegrationsGoogle{
+		ID: 1, RefreshTokenCt: p.RefreshTokenCt, CalendarID: p.CalendarID,
+		CalendarName: p.CalendarName, ConnectedAt: updated, LastSyncError: p.LastSyncError,
+		CreatedAt: created, UpdatedAt: updated,
+	}
+	assertSettings(t, "update", q.UpsertIntegrationsGoogle, q.GetIntegrationsGoogle, p, want)
+}
+
+func TestChatSettings(t *testing.T) {
+	ctx := context.Background()
+	_, q := openQueries(t)
+	_, err := q.GetChatSettings(ctx)
+	mustNoRows(t, "GetChatSettings before upsert", err)
+
+	p := db.UpsertChatSettingsParams{
+		WebhookUrlCt: []byte{4, 5}, CreatedAt: created, UpdatedAt: created,
+	}
+	want := db.ChatSetting{
+		ID: 1, WebhookUrlCt: p.WebhookUrlCt, CreatedAt: created, UpdatedAt: created,
+	}
+	assertSettings(t, "insert", q.UpsertChatSettings, q.GetChatSettings, p, want)
+
+	p = db.UpsertChatSettingsParams{
+		WebhookUrlCt: []byte{6}, SpaceLabel: str("Kitchen"), LastTestAt: str(updated),
+		LastTestStatus: str("ok"), CreatedAt: updated, UpdatedAt: updated,
+	}
+	want = db.ChatSetting{
+		ID: 1, WebhookUrlCt: p.WebhookUrlCt, SpaceLabel: p.SpaceLabel, LastTestAt: p.LastTestAt,
+		LastTestStatus: p.LastTestStatus, CreatedAt: created, UpdatedAt: updated,
+	}
+	assertSettings(t, "update", q.UpsertChatSettings, q.GetChatSettings, p, want)
+}
+
+// assertSettings upserts p and checks that both the returned row and a
+// fresh Get equal want. On update, want keeps the original created_at.
+func assertSettings[P, R any](
+	t *testing.T, what string,
+	upsert func(context.Context, P) (R, error), get func(context.Context) (R, error),
+	p P, want R,
+) {
+	t.Helper()
+	ctx := context.Background()
+	got, err := upsert(ctx, p)
+	if err != nil {
+		t.Fatalf("%s upsert: %v", what, err)
+	}
+	if !reflect.DeepEqual(got, want) {
+		t.Fatalf("%s upsert returned %+v, want %+v", what, got, want)
+	}
+	got, err = get(ctx)
+	if err != nil {
+		t.Fatalf("%s get: %v", what, err)
+	}
+	if !reflect.DeepEqual(got, want) {
+		t.Fatalf("%s get = %+v, want %+v", what, got, want)
+	}
+}

internal/db/dinners.sql.go

@@ -0,0 +1,321 @@
+// Code generated by sqlc. DO NOT EDIT.
+// versions:
+//   sqlc v1.31.1
+// source: dinners.sql
+
+package db
+
+import (
+	"context"
+	"database/sql"
+)
+
+const archiveDinner = `-- name: ArchiveDinner :one
+UPDATE dinners
+SET status = 'archived',
+	updated_by = ?,
+	updated_at = ?
+WHERE id = ?
+RETURNING id, name, normalized_name, status, origin, method, minutes_total, difficulty, cuisine, is_favorite, recipe_id, photo_id, grounding_status, updated_by, created_at, updated_at
+`
+
+type ArchiveDinnerParams struct {
+	UpdatedBy sql.NullString
+	UpdatedAt string
+	ID        string
+}
+
+// An unknown id returns sql.ErrNoRows.
+func (q *Queries) ArchiveDinner(ctx context.Context, arg ArchiveDinnerParams) (Dinner, error) {
+	row := q.db.QueryRowContext(ctx, archiveDinner, arg.UpdatedBy, arg.UpdatedAt, arg.ID)
+	var i Dinner
+	err := row.Scan(
+		&i.ID,
+		&i.Name,
+		&i.NormalizedName,
+		&i.Status,
+		&i.Origin,
+		&i.Method,
+		&i.MinutesTotal,
+		&i.Difficulty,
+		&i.Cuisine,
+		&i.IsFavorite,
+		&i.RecipeID,
+		&i.PhotoID,
+		&i.GroundingStatus,
+		&i.UpdatedBy,
+		&i.CreatedAt,
+		&i.UpdatedAt,
+	)
+	return i, err
+}
+
+const createDinner = `-- name: CreateDinner :one
+INSERT INTO dinners (
+	id, name, normalized_name, status, origin, method, minutes_total,
+	difficulty, cuisine, is_favorite, recipe_id, photo_id, grounding_status,
+	updated_by, created_at, updated_at
+) VALUES (
+	?, ?, ?, ?, ?, ?, ?,
+	?, ?, ?, ?, ?, ?,
+	?, ?, ?
+)
+RETURNING id, name, normalized_name, status, origin, method, minutes_total, difficulty, cuisine, is_favorite, recipe_id, photo_id, grounding_status, updated_by, created_at, updated_at
+`
+
+type CreateDinnerParams struct {
+	ID              string
+	Name            string
+	NormalizedName  string
+	Status          string
+	Origin          string
+	Method          sql.NullString
+	MinutesTotal    sql.NullInt64
+	Difficulty      sql.NullString
+	Cuisine         sql.NullString
+	IsFavorite      int64
+	RecipeID        sql.NullString
+	PhotoID         sql.NullString
+	GroundingStatus string
+	UpdatedBy       sql.NullString
+	CreatedAt       string
+	UpdatedAt       string
+}
+
+func (q *Queries) CreateDinner(ctx context.Context, arg CreateDinnerParams) (Dinner, error) {
+	row := q.db.QueryRowContext(ctx, createDinner,
+		arg.ID,
+		arg.Name,
+		arg.NormalizedName,
+		arg.Status,
+		arg.Origin,
+		arg.Method,
+		arg.MinutesTotal,
+		arg.Difficulty,
+		arg.Cuisine,
+		arg.IsFavorite,
+		arg.RecipeID,
+		arg.PhotoID,
+		arg.GroundingStatus,
+		arg.UpdatedBy,
+		arg.CreatedAt,
+		arg.UpdatedAt,
+	)
+	var i Dinner
+	err := row.Scan(
+		&i.ID,
+		&i.Name,
+		&i.NormalizedName,
+		&i.Status,
+		&i.Origin,
+		&i.Method,
+		&i.MinutesTotal,
+		&i.Difficulty,
+		&i.Cuisine,
+		&i.IsFavorite,
+		&i.RecipeID,
+		&i.PhotoID,
+		&i.GroundingStatus,
+		&i.UpdatedBy,
+		&i.CreatedAt,
+		&i.UpdatedAt,
+	)
+	return i, err
+}
+
+const getDinner = `-- name: GetDinner :one
+SELECT id, name, normalized_name, status, origin, method, minutes_total, difficulty, cuisine, is_favorite, recipe_id, photo_id, grounding_status, updated_by, created_at, updated_at FROM dinners
+WHERE id = ?
+`
+
+func (q *Queries) GetDinner(ctx context.Context, id string) (Dinner, error) {
+	row := q.db.QueryRowContext(ctx, getDinner, id)
+	var i Dinner
+	err := row.Scan(
+		&i.ID,
+		&i.Name,
+		&i.NormalizedName,
+		&i.Status,
+		&i.Origin,
+		&i.Method,
+		&i.MinutesTotal,
+		&i.Difficulty,
+		&i.Cuisine,
+		&i.IsFavorite,
+		&i.RecipeID,
+		&i.PhotoID,
+		&i.GroundingStatus,
+		&i.UpdatedBy,
+		&i.CreatedAt,
+		&i.UpdatedAt,
+	)
+	return i, err
+}
+
+const listDinnersByStatus = `-- name: ListDinnersByStatus :many
+SELECT id, name, normalized_name, status, origin, method, minutes_total, difficulty, cuisine, is_favorite, recipe_id, photo_id, grounding_status, updated_by, created_at, updated_at FROM dinners
+WHERE status = ?
+ORDER BY normalized_name, id
+`
+
+func (q *Queries) ListDinnersByStatus(ctx context.Context, status string) ([]Dinner, error) {
+	rows, err := q.db.QueryContext(ctx, listDinnersByStatus, status)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+	var items []Dinner
+	for rows.Next() {
+		var i Dinner
+		if err := rows.Scan(
+			&i.ID,
+			&i.Name,
+			&i.NormalizedName,
+			&i.Status,
+			&i.Origin,
+			&i.Method,
+			&i.MinutesTotal,
+			&i.Difficulty,
+			&i.Cuisine,
+			&i.IsFavorite,
+			&i.RecipeID,
+			&i.PhotoID,
+			&i.GroundingStatus,
+			&i.UpdatedBy,
+			&i.CreatedAt,
+			&i.UpdatedAt,
+		); err != nil {
+			return nil, err
+		}
+		items = append(items, i)
+	}
+	if err := rows.Close(); err != nil {
+		return nil, err
+	}
+	if err := rows.Err(); err != nil {
+		return nil, err
+	}
+	return items, nil
+}
+
+const setDinnerFavorite = `-- name: SetDinnerFavorite :one
+UPDATE dinners
+SET is_favorite = ?,
+	updated_by = ?,
+	updated_at = ?
+WHERE id = ?
+RETURNING id, name, normalized_name, status, origin, method, minutes_total, difficulty, cuisine, is_favorite, recipe_id, photo_id, grounding_status, updated_by, created_at, updated_at
+`
+
+type SetDinnerFavoriteParams struct {
+	IsFavorite int64
+	UpdatedBy  sql.NullString
+	UpdatedAt  string
+	ID         string
+}
+
+// An unknown id returns sql.ErrNoRows.
+func (q *Queries) SetDinnerFavorite(ctx context.Context, arg SetDinnerFavoriteParams) (Dinner, error) {
+	row := q.db.QueryRowContext(ctx, setDinnerFavorite,
+		arg.IsFavorite,
+		arg.UpdatedBy,
+		arg.UpdatedAt,
+		arg.ID,
+	)
+	var i Dinner
+	err := row.Scan(
+		&i.ID,
+		&i.Name,
+		&i.NormalizedName,
+		&i.Status,
+		&i.Origin,
+		&i.Method,
+		&i.MinutesTotal,
+		&i.Difficulty,
+		&i.Cuisine,
+		&i.IsFavorite,
+		&i.RecipeID,
+		&i.PhotoID,
+		&i.GroundingStatus,
+		&i.UpdatedBy,
+		&i.CreatedAt,
+		&i.UpdatedAt,
+	)
+	return i, err
+}
+
+const updateDinner = `-- name: UpdateDinner :one
+UPDATE dinners
+SET name = ?,
+	normalized_name = ?,
+	status = ?,
+	origin = ?,
+	method = ?,
+	minutes_total = ?,
+	difficulty = ?,
+	cuisine = ?,
+	recipe_id = ?,
+	photo_id = ?,
+	grounding_status = ?,
+	updated_by = ?,
+	updated_at = ?
+WHERE id = ?
+RETURNING id, name, normalized_name, status, origin, method, minutes_total, difficulty, cuisine, is_favorite, recipe_id, photo_id, grounding_status, updated_by, created_at, updated_at
+`
+
+type UpdateDinnerParams struct {
+	Name            string
+	NormalizedName  string
+	Status          string
+	Origin          string
+	Method          sql.NullString
+	MinutesTotal    sql.NullInt64
+	Difficulty      sql.NullString
+	Cuisine         sql.NullString
+	RecipeID        sql.NullString
+	PhotoID         sql.NullString
+	GroundingStatus string
+	UpdatedBy       sql.NullString
+	UpdatedAt       string
+	ID              string
+}
+
+// An unknown id returns sql.ErrNoRows.
+func (q *Queries) UpdateDinner(ctx context.Context, arg UpdateDinnerParams) (Dinner, error) {
+	row := q.db.QueryRowContext(ctx, updateDinner,
+		arg.Name,
+		arg.NormalizedName,
+		arg.Status,
+		arg.Origin,
+		arg.Method,
+		arg.MinutesTotal,
+		arg.Difficulty,
+		arg.Cuisine,
+		arg.RecipeID,
+		arg.PhotoID,
+		arg.GroundingStatus,
+		arg.UpdatedBy,
+		arg.UpdatedAt,
+		arg.ID,
+	)
+	var i Dinner
+	err := row.Scan(
+		&i.ID,
+		&i.Name,
+		&i.NormalizedName,
+		&i.Status,
+		&i.Origin,
+		&i.Method,
+		&i.MinutesTotal,
+		&i.Difficulty,
+		&i.Cuisine,
+		&i.IsFavorite,
+		&i.RecipeID,
+		&i.PhotoID,
+		&i.GroundingStatus,
+		&i.UpdatedBy,
+		&i.CreatedAt,
+		&i.UpdatedAt,
+	)
+	return i, err
+}

internal/db/models.go

@@ -0,0 +1,309 @@
+// Code generated by sqlc. DO NOT EDIT.
+// versions:
+//   sqlc v1.31.1
+
+package db
+
+import (
+	"database/sql"
+)
+
+type AiRun struct {
+	ID               string
+	Skill            string
+	SkillVersion     string
+	Provider         string
+	Model            string
+	Temperature      float64
+	Status           string
+	LatencyMs        sql.NullInt64
+	TokensIn         sql.NullInt64
+	TokensOut        sql.NullInt64
+	Error            sql.NullString
+	RequestRedacted  string
+	ResponseRedacted sql.NullString
+	CreatedAt        string
+	UpdatedAt        string
+}
+
+type AiSetting struct {
+	ID            int64
+	Provider      string
+	BaseUrl       string
+	Model         string
+	Temperature   float64
+	SystemPrompt  sql.NullString
+	ApiKeyCt      []byte
+	ApiKeyLast4   sql.NullString
+	KeyVerifiedAt sql.NullString
+	CreatedAt     string
+	UpdatedAt     string
+}
+
+type ChatSetting struct {
+	ID             int64
+	WebhookUrlCt   []byte
+	SpaceLabel     sql.NullString
+	LastTestAt     sql.NullString
+	LastTestStatus sql.NullString
+	CreatedAt      string
+	UpdatedAt      string
+}
+
+type Dinner struct {
+	ID              string
+	Name            string
+	NormalizedName  string
+	Status          string
+	Origin          string
+	Method          sql.NullString
+	MinutesTotal    sql.NullInt64
+	Difficulty      sql.NullString
+	Cuisine         sql.NullString
+	IsFavorite      int64
+	RecipeID        sql.NullString
+	PhotoID         sql.NullString
+	GroundingStatus string
+	UpdatedBy       sql.NullString
+	CreatedAt       string
+	UpdatedAt       string
+}
+
+type HouseholdSetting struct {
+	ID                    int64
+	Timezone              string
+	DinnerTime            string
+	ReminderTime          string
+	ReviewTime            string
+	PlanDraftDow          int64
+	PlanDraftTime         string
+	SuggestionsDow        int64
+	SuggestionsTime       string
+	ChatAutosendDow       int64
+	ChatAutosendTime      string
+	OnboardingCompletedAt sql.NullString
+	UpdatesSeenAt         sql.NullString
+	CreatedAt             string
+	UpdatedAt             string
+}
+
+type IntegrationsGoogle struct {
+	ID             int64
+	RefreshTokenCt []byte
+	CalendarID     sql.NullString
+	CalendarName   sql.NullString
+	ConnectedAt    string
+	LastSyncError  sql.NullString
+	CreatedAt      string
+	UpdatedAt      string
+}
+
+type Job struct {
+	ID         string
+	Kind       string
+	Status     string
+	Payload    string
+	Result     sql.NullString
+	Error      sql.NullString
+	StartedAt  sql.NullString
+	FinishedAt sql.NullString
+	CreatedAt  string
+	UpdatedAt  string
+}
+
+type NotificationOutbox struct {
+	ID            string
+	EventKey      string
+	Channel       string
+	Payload       string
+	Status        string
+	Attempts      int64
+	NextAttemptAt sql.NullString
+	LastError     sql.NullString
+	CreatedAt     string
+	UpdatedAt     string
+}
+
+type NotificationPref struct {
+	Event     string
+	Push      int64
+	Chat      int64
+	CreatedAt string
+	UpdatedAt string
+}
+
+type Photo struct {
+	ID        string
+	SourceUrl string
+	LocalPath string
+	Width     int64
+	Height    int64
+	Sha256    string
+	FetchedAt string
+	CreatedAt string
+	UpdatedAt string
+}
+
+type PlanDay struct {
+	ID              string
+	PlanID          string
+	Date            string
+	DinnerID        sql.NullString
+	IsNew           int64
+	CalendarEventID sql.NullString
+	CreatedAt       string
+	UpdatedAt       string
+}
+
+type PushSubscription struct {
+	ID            string
+	Endpoint      string
+	P256dh        string
+	Auth          string
+	UserAgent     sql.NullString
+	FailureCount  int64
+	LastSuccessAt sql.NullString
+	CreatedAt     string
+	UpdatedAt     string
+}
+
+type Recipe struct {
+	ID           string
+	SourceUrl    string
+	SiteName     sql.NullString
+	Title        string
+	TotalMinutes sql.NullInt64
+	PrepMinutes  sql.NullInt64
+	CookMinutes  sql.NullInt64
+	Yield        sql.NullString
+	RawJsonld    string
+	FetchedAt    string
+	CreatedAt    string
+	UpdatedAt    string
+}
+
+type RecipeIngredient struct {
+	ID        string
+	RecipeID  string
+	Position  int64
+	RawText   string
+	Name      sql.NullString
+	Qty       sql.NullString
+	Unit      sql.NullString
+	Note      sql.NullString
+	CreatedAt string
+	UpdatedAt string
+}
+
+type RecipeSearchSetting struct {
+	ID        int64
+	Provider  string
+	BaseUrl   sql.NullString
+	ApiKeyCt  []byte
+	CreatedAt string
+	UpdatedAt string
+}
+
+type RecipeStep struct {
+	ID        string
+	RecipeID  string
+	Position  int64
+	Text      string
+	CreatedAt string
+	UpdatedAt string
+}
+
+type RotationReview struct {
+	ID        string
+	DinnerID  string
+	PlanDate  string
+	Status    string
+	Decision  sql.NullString
+	DecidedAt sql.NullString
+	UpdatedBy sql.NullString
+	CreatedAt string
+	UpdatedAt string
+}
+
+type ScheduleRun struct {
+	ID        string
+	Schedule  string
+	PeriodKey string
+	CreatedAt string
+}
+
+type Session struct {
+	IDHash     string
+	Subject    string
+	Email      string
+	Name       string
+	Groups     string
+	CsrfToken  string
+	CreatedAt  string
+	LastSeenAt string
+	ExpiresAt  string
+}
+
+type ShoppingGroup struct {
+	ID        string
+	ListID    string
+	DinnerID  string
+	Position  int64
+	CreatedAt string
+	UpdatedAt string
+}
+
+type ShoppingItem struct {
+	ID        string
+	GroupID   string
+	Name      string
+	Qty       sql.NullString
+	Needed    int64
+	Position  int64
+	CreatedAt string
+	UpdatedAt string
+}
+
+type ShoppingList struct {
+	ID           string
+	WeekStart    string
+	PlanID       string
+	Title        string
+	Status       string
+	SentToChatAt sql.NullString
+	FrozenAt     sql.NullString
+	CreatedAt    string
+	UpdatedAt    string
+}
+
+type Suggestion struct {
+	ID              string
+	DinnerID        string
+	BatchID         string
+	Reason          string
+	CitedRegularIds string
+	Status          string
+	DecidedAt       sql.NullString
+	CreatedAt       string
+	UpdatedAt       string
+}
+
+type SuggestionBatch struct {
+	ID        string
+	Trigger   string
+	Status    string
+	AiRunID   sql.NullString
+	CreatedAt string
+	UpdatedAt string
+}
+
+type WeekPlan struct {
+	ID               string
+	WeekStart        string
+	Status           string
+	AiNote           sql.NullString
+	AiRunID          sql.NullString
+	CalendarSyncedAt sql.NullString
+	CreatedAt        string
+	UpdatedAt        string
+}

internal/db/photos.sql.go

@@ -0,0 +1,86 @@
+// Code generated by sqlc. DO NOT EDIT.
+// versions:
+//   sqlc v1.31.1
+// source: photos.sql
+
+package db
+
+import (
+	"context"
+)
+
+const createPhoto = `-- name: CreatePhoto :one
+INSERT INTO photos (
+	id, source_url, local_path, width, height, sha256, fetched_at,
+	created_at, updated_at
+) VALUES (
+	?, ?, ?, ?, ?, ?, ?,
+	?, ?
+)
+RETURNING id, source_url, local_path, width, height, sha256, fetched_at, created_at, updated_at
+`
+
+type CreatePhotoParams struct {
+	ID        string
+	SourceUrl string
+	LocalPath string
+	Width     int64
+	Height    int64
+	Sha256    string
+	FetchedAt string
+	CreatedAt string
+	UpdatedAt string
+}
+
+func (q *Queries) CreatePhoto(ctx context.Context, arg CreatePhotoParams) (Photo, error) {
+	row := q.db.QueryRowContext(ctx, createPhoto,
+		arg.ID,
+		arg.SourceUrl,
+		arg.LocalPath,
+		arg.Width,
+		arg.Height,
+		arg.Sha256,
+		arg.FetchedAt,
+		arg.CreatedAt,
+		arg.UpdatedAt,
+	)
+	var i Photo
+	err := row.Scan(
+		&i.ID,
+		&i.SourceUrl,
+		&i.LocalPath,
+		&i.Width,
+		&i.Height,
+		&i.Sha256,
+		&i.FetchedAt,
+		&i.CreatedAt,
+		&i.UpdatedAt,
+	)
+	return i, err
+}
+
+const getPhotoBySHA256 = `-- name: GetPhotoBySHA256 :one
+SELECT id, source_url, local_path, width, height, sha256, fetched_at, created_at, updated_at FROM photos
+WHERE sha256 = ?
+ORDER BY id
+LIMIT 1
+`
+
+// Finds stored image bytes by content hash. The schema does not make sha256
+// unique, so the earliest-created (lowest id) match is returned.
+func (q *Queries) GetPhotoBySHA256(ctx context.Context, sha256 string) (Photo, error) {
+	row := q.db.QueryRowContext(ctx, getPhotoBySHA256, sha256)
+	var i Photo
+	err := row.Scan(
+		&i.ID,
+		&i.SourceUrl,
+		&i.LocalPath,
+		&i.Width,
+		&i.Height,
+		&i.Sha256,
+		&i.FetchedAt,
+		&i.CreatedAt,
+		&i.UpdatedAt,
+	)
+	return i, err
+}

internal/db/querier.go

@@ -0,0 +1,65 @@
+// Code generated by sqlc. DO NOT EDIT.
+// versions:
+//   sqlc v1.31.1
+
+package db
+
+import (
+	"context"
+)
+
+type Querier interface {
+	// An unknown id returns sql.ErrNoRows.
+	ArchiveDinner(ctx context.Context, arg ArchiveDinnerParams) (Dinner, error)
+	CreateDinner(ctx context.Context, arg CreateDinnerParams) (Dinner, error)
+	CreatePhoto(ctx context.Context, arg CreatePhotoParams) (Photo, error)
+	CreateRecipeIngredient(ctx context.Context, arg CreateRecipeIngredientParams) error
+	CreateRecipeStep(ctx context.Context, arg CreateRecipeStepParams) error
+	DeleteRecipeIngredients(ctx context.Context, recipeID string) error
+	DeleteRecipeSteps(ctx context.Context, recipeID string) error
+	// Returns sql.ErrNoRows until the row exists.
+	GetAISettings(ctx context.Context) (AiSetting, error)
+	// Returns sql.ErrNoRows until the row exists.
+	GetChatSettings(ctx context.Context) (ChatSetting, error)
+	GetDinner(ctx context.Context, id string) (Dinner, error)
+	// Returns sql.ErrNoRows until the row exists.
+	GetHouseholdSettings(ctx context.Context) (HouseholdSetting, error)
+	// Returns sql.ErrNoRows until the row exists.
+	GetIntegrationsGoogle(ctx context.Context) (IntegrationsGoogle, error)
+	// Finds stored image bytes by content hash. The schema does not make sha256
+	// unique, so the earliest-created (lowest id) match is returned.
+	GetPhotoBySHA256(ctx context.Context, sha256 string) (Photo, error)
+	GetRecipe(ctx context.Context, id string) (Recipe, error)
+	GetRecipeBySourceURL(ctx context.Context, sourceUrl string) (Recipe, error)
+	// Returns sql.ErrNoRows until the row exists.
+	GetRecipeSearchSettings(ctx context.Context) (RecipeSearchSetting, error)
+	ListDinnersByStatus(ctx context.Context, status string) ([]Dinner, error)
+	ListRecipeIngredients(ctx context.Context, recipeID string) ([]RecipeIngredient, error)
+	ListRecipeSteps(ctx context.Context, recipeID string) ([]RecipeStep, error)
+	// An unknown id returns sql.ErrNoRows.
+	SetDinnerFavorite(ctx context.Context, arg SetDinnerFavoriteParams) (Dinner, error)
+	// An unknown id returns sql.ErrNoRows.
+	UpdateDinner(ctx context.Context, arg UpdateDinnerParams) (Dinner, error)
+	// Creates the single row (id 1) or replaces every column except id and
+	// created_at.
+	UpsertAISettings(ctx context.Context, arg UpsertAISettingsParams) (AiSetting, error)
+	// Creates the single row (id 1) or replaces every column except id and
+	// created_at.
+	UpsertChatSettings(ctx context.Context, arg UpsertChatSettingsParams) (ChatSetting, error)
+	// Creates the single row (id 1) or replaces every column except id and
+	// created_at.
+	UpsertHouseholdSettings(ctx context.Context, arg UpsertHouseholdSettingsParams) (HouseholdSetting, error)
+	// Creates the single row (id 1) or replaces every column except id and
+	// created_at.
+	UpsertIntegrationsGoogle(ctx context.Context, arg UpsertIntegrationsGoogleParams) (IntegrationsGoogle, error)
+	// A recipe is keyed by source_url: re-fetching the same URL updates the
+	// existing row and keeps its id and created_at, which the returned row
+	// carries. Replace its ingredients and steps in the same transaction with
+	// DeleteRecipeIngredients/DeleteRecipeSteps then the Create queries.
+	UpsertRecipe(ctx context.Context, arg UpsertRecipeParams) (Recipe, error)
+	// Creates the single row (id 1) or replaces every column except id and
+	// created_at.
+	UpsertRecipeSearchSettings(ctx context.Context, arg UpsertRecipeSearchSettingsParams) (RecipeSearchSetting, error)
+}
+
+var _ Querier = (*Queries)(nil)

internal/db/recipes.sql.go

@@ -0,0 +1,298 @@
+// Code generated by sqlc. DO NOT EDIT.
+// versions:
+//   sqlc v1.31.1
+// source: recipes.sql
+
+package db
+
+import (
+	"context"
+	"database/sql"
+)
+
+const createRecipeIngredient = `-- name: CreateRecipeIngredient :exec
+INSERT INTO recipe_ingredients (
+	id, recipe_id, position, raw_text, name, qty, unit, note,
+	created_at, updated_at
+) VALUES (
+	?, ?, ?, ?, ?, ?, ?, ?,
+	?, ?
+)
+`
+
+type CreateRecipeIngredientParams struct {
+	ID        string
+	RecipeID  string
+	Position  int64
+	RawText   string
+	Name      sql.NullString
+	Qty       sql.NullString
+	Unit      sql.NullString
+	Note      sql.NullString
+	CreatedAt string
+	UpdatedAt string
+}
+
+func (q *Queries) CreateRecipeIngredient(ctx context.Context, arg CreateRecipeIngredientParams) error {
+	_, err := q.db.ExecContext(ctx, createRecipeIngredient,
+		arg.ID,
+		arg.RecipeID,
+		arg.Position,
+		arg.RawText,
+		arg.Name,
+		arg.Qty,
+		arg.Unit,
+		arg.Note,
+		arg.CreatedAt,
+		arg.UpdatedAt,
+	)
+	return err
+}
+
+const createRecipeStep = `-- name: CreateRecipeStep :exec
+INSERT INTO recipe_steps (
+	id, recipe_id, position, text, created_at, updated_at
+) VALUES (
+	?, ?, ?, ?, ?, ?
+)
+`
+
+type CreateRecipeStepParams struct {
+	ID        string
+	RecipeID  string
+	Position  int64
+	Text      string
+	CreatedAt string
+	UpdatedAt string
+}
+
+func (q *Queries) CreateRecipeStep(ctx context.Context, arg CreateRecipeStepParams) error {
+	_, err := q.db.ExecContext(ctx, createRecipeStep,
+		arg.ID,
+		arg.RecipeID,
+		arg.Position,
+		arg.Text,
+		arg.CreatedAt,
+		arg.UpdatedAt,
+	)
+	return err
+}
+
+const deleteRecipeIngredients = `-- name: DeleteRecipeIngredients :exec
+DELETE FROM recipe_ingredients
+WHERE recipe_id = ?
+`
+
+func (q *Queries) DeleteRecipeIngredients(ctx context.Context, recipeID string) error {
+	_, err := q.db.ExecContext(ctx, deleteRecipeIngredients, recipeID)
+	return err
+}
+
+const deleteRecipeSteps = `-- name: DeleteRecipeSteps :exec
+DELETE FROM recipe_steps
+WHERE recipe_id = ?
+`
+
+func (q *Queries) DeleteRecipeSteps(ctx context.Context, recipeID string) error {
+	_, err := q.db.ExecContext(ctx, deleteRecipeSteps, recipeID)
+	return err
+}
+
+const getRecipe = `-- name: GetRecipe :one
+SELECT id, source_url, site_name, title, total_minutes, prep_minutes, cook_minutes, yield, raw_jsonld, fetched_at, created_at, updated_at FROM recipes
+WHERE id = ?
+`
+
+func (q *Queries) GetRecipe(ctx context.Context, id string) (Recipe, error) {
+	row := q.db.QueryRowContext(ctx, getRecipe, id)
+	var i Recipe
+	err := row.Scan(
+		&i.ID,
+		&i.SourceUrl,
+		&i.SiteName,
+		&i.Title,
+		&i.TotalMinutes,
+		&i.PrepMinutes,
+		&i.CookMinutes,
+		&i.Yield,
+		&i.RawJsonld,
+		&i.FetchedAt,
+		&i.CreatedAt,
+		&i.UpdatedAt,
+	)
+	return i, err
+}
+
+const getRecipeBySourceURL = `-- name: GetRecipeBySourceURL :one
+SELECT id, source_url, site_name, title, total_minutes, prep_minutes, cook_minutes, yield, raw_jsonld, fetched_at, created_at, updated_at FROM recipes
+WHERE source_url = ?
+`
+
+func (q *Queries) GetRecipeBySourceURL(ctx context.Context, sourceUrl string) (Recipe, error) {
+	row := q.db.QueryRowContext(ctx, getRecipeBySourceURL, sourceUrl)
+	var i Recipe
+	err := row.Scan(
+		&i.ID,
+		&i.SourceUrl,
+		&i.SiteName,
+		&i.Title,
+		&i.TotalMinutes,
+		&i.PrepMinutes,
+		&i.CookMinutes,
+		&i.Yield,
+		&i.RawJsonld,
+		&i.FetchedAt,
+		&i.CreatedAt,
+		&i.UpdatedAt,
+	)
+	return i, err
+}
+
+const listRecipeIngredients = `-- name: ListRecipeIngredients :many
+SELECT id, recipe_id, position, raw_text, name, qty, unit, note, created_at, updated_at FROM recipe_ingredients
+WHERE recipe_id = ?
+ORDER BY position, id
+`
+
+func (q *Queries) ListRecipeIngredients(ctx context.Context, recipeID string) ([]RecipeIngredient, error) {
+	rows, err := q.db.QueryContext(ctx, listRecipeIngredients, recipeID)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+	var items []RecipeIngredient
+	for rows.Next() {
+		var i RecipeIngredient
+		if err := rows.Scan(
+			&i.ID,
+			&i.RecipeID,
+			&i.Position,
+			&i.RawText,
+			&i.Name,
+			&i.Qty,
+			&i.Unit,
+			&i.Note,
+			&i.CreatedAt,
+			&i.UpdatedAt,
+		); err != nil {
+			return nil, err
+		}
+		items = append(items, i)
+	}
+	if err := rows.Close(); err != nil {
+		return nil, err
+	}
+	if err := rows.Err(); err != nil {
+		return nil, err
+	}
+	return items, nil
+}
+
+const listRecipeSteps = `-- name: ListRecipeSteps :many
+SELECT id, recipe_id, position, text, created_at, updated_at FROM recipe_steps
+WHERE recipe_id = ?
+ORDER BY position, id
+`
+
+func (q *Queries) ListRecipeSteps(ctx context.Context, recipeID string) ([]RecipeStep, error) {
+	rows, err := q.db.QueryContext(ctx, listRecipeSteps, recipeID)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+	var items []RecipeStep
+	for rows.Next() {
+		var i RecipeStep
+		if err := rows.Scan(
+			&i.ID,
+			&i.RecipeID,
+			&i.Position,
+			&i.Text,
+			&i.CreatedAt,
+			&i.UpdatedAt,
+		); err != nil {
+			return nil, err
+		}
+		items = append(items, i)
+	}
+	if err := rows.Close(); err != nil {
+		return nil, err
+	}
+	if err := rows.Err(); err != nil {
+		return nil, err
+	}
+	return items, nil
+}
+
+const upsertRecipe = `-- name: UpsertRecipe :one
+INSERT INTO recipes (
+	id, source_url, site_name, title, total_minutes, prep_minutes,
+	cook_minutes, yield, raw_jsonld, fetched_at, created_at, updated_at
+) VALUES (
+	?, ?, ?, ?, ?, ?,
+	?, ?, ?, ?, ?, ?
+)
+ON CONFLICT (source_url) DO UPDATE SET
+	site_name = excluded.site_name,
+	title = excluded.title,
+	total_minutes = excluded.total_minutes,
+	prep_minutes = excluded.prep_minutes,
+	cook_minutes = excluded.cook_minutes,
+	yield = excluded.yield,
+	raw_jsonld = excluded.raw_jsonld,
+	fetched_at = excluded.fetched_at,
+	updated_at = excluded.updated_at
+RETURNING id, source_url, site_name, title, total_minutes, prep_minutes, cook_minutes, yield, raw_jsonld, fetched_at, created_at, updated_at
+`
+
+type UpsertRecipeParams struct {
+	ID           string
+	SourceUrl    string
+	SiteName     sql.NullString
+	Title        string
+	TotalMinutes sql.NullInt64
+	PrepMinutes  sql.NullInt64
+	CookMinutes  sql.NullInt64
+	Yield        sql.NullString
+	RawJsonld    string
+	FetchedAt    string
+	CreatedAt    string
+	UpdatedAt    string
+}
+
+// A recipe is keyed by source_url: re-fetching the same URL updates the
+// existing row and keeps its id and created_at, which the returned row
+// carries. Replace its ingredients and steps in the same transaction with
+// DeleteRecipeIngredients/DeleteRecipeSteps then the Create queries.
+func (q *Queries) UpsertRecipe(ctx context.Context, arg UpsertRecipeParams) (Recipe, error) {
+	row := q.db.QueryRowContext(ctx, upsertRecipe,
+		arg.ID,
+		arg.SourceUrl,
+		arg.SiteName,
+		arg.Title,
+		arg.TotalMinutes,
+		arg.PrepMinutes,
+		arg.CookMinutes,
+		arg.Yield,
+		arg.RawJsonld,
+		arg.FetchedAt,
+		arg.CreatedAt,
+		arg.UpdatedAt,
+	)
+	var i Recipe
+	err := row.Scan(
+		&i.ID,
+		&i.SourceUrl,
+		&i.SiteName,
+		&i.Title,
+		&i.TotalMinutes,
+		&i.PrepMinutes,
+		&i.CookMinutes,
+		&i.Yield,
+		&i.RawJsonld,
+		&i.FetchedAt,
+		&i.CreatedAt,
+		&i.UpdatedAt,
+	)
+	return i, err
+}

internal/db/settings.sql.go

@@ -0,0 +1,428 @@
+// Code generated by sqlc. DO NOT EDIT.
+// versions:
+//   sqlc v1.31.1
+// source: settings.sql
+
+package db
+
+import (
+	"context"
+	"database/sql"
+)
+
+const getAISettings = `-- name: GetAISettings :one
+SELECT id, provider, base_url, model, temperature, system_prompt, api_key_ct, api_key_last4, key_verified_at, created_at, updated_at FROM ai_settings
+WHERE id = 1
+`
+
+// Returns sql.ErrNoRows until the row exists.
+func (q *Queries) GetAISettings(ctx context.Context) (AiSetting, error) {
+	row := q.db.QueryRowContext(ctx, getAISettings)
+	var i AiSetting
+	err := row.Scan(
+		&i.ID,
+		&i.Provider,
+		&i.BaseUrl,
+		&i.Model,
+		&i.Temperature,
+		&i.SystemPrompt,
+		&i.ApiKeyCt,
+		&i.ApiKeyLast4,
+		&i.KeyVerifiedAt,
+		&i.CreatedAt,
+		&i.UpdatedAt,
+	)
+	return i, err
+}
+
+const getChatSettings = `-- name: GetChatSettings :one
+SELECT id, webhook_url_ct, space_label, last_test_at, last_test_status, created_at, updated_at FROM chat_settings
+WHERE id = 1
+`
+
+// Returns sql.ErrNoRows until the row exists.
+func (q *Queries) GetChatSettings(ctx context.Context) (ChatSetting, error) {
+	row := q.db.QueryRowContext(ctx, getChatSettings)
+	var i ChatSetting
+	err := row.Scan(
+		&i.ID,
+		&i.WebhookUrlCt,
+		&i.SpaceLabel,
+		&i.LastTestAt,
+		&i.LastTestStatus,
+		&i.CreatedAt,
+		&i.UpdatedAt,
+	)
+	return i, err
+}
+
+const getHouseholdSettings = `-- name: GetHouseholdSettings :one
+SELECT id, timezone, dinner_time, reminder_time, review_time, plan_draft_dow, plan_draft_time, suggestions_dow, suggestions_time, chat_autosend_dow, chat_autosend_time, onboarding_completed_at, updates_seen_at, created_at, updated_at FROM household_settings
+WHERE id = 1
+`
+
+// Returns sql.ErrNoRows until the row exists.
+func (q *Queries) GetHouseholdSettings(ctx context.Context) (HouseholdSetting, error) {
+	row := q.db.QueryRowContext(ctx, getHouseholdSettings)
+	var i HouseholdSetting
+	err := row.Scan(
+		&i.ID,
+		&i.Timezone,
+		&i.DinnerTime,
+		&i.ReminderTime,
+		&i.ReviewTime,
+		&i.PlanDraftDow,
+		&i.PlanDraftTime,
+		&i.SuggestionsDow,
+		&i.SuggestionsTime,
+		&i.ChatAutosendDow,
+		&i.ChatAutosendTime,
+		&i.OnboardingCompletedAt,
+		&i.UpdatesSeenAt,
+		&i.CreatedAt,
+		&i.UpdatedAt,
+	)
+	return i, err
+}
+
+const getIntegrationsGoogle = `-- name: GetIntegrationsGoogle :one
+SELECT id, refresh_token_ct, calendar_id, calendar_name, connected_at, last_sync_error, created_at, updated_at FROM integrations_google
+WHERE id = 1
+`
+
+// Returns sql.ErrNoRows until the row exists.
+func (q *Queries) GetIntegrationsGoogle(ctx context.Context) (IntegrationsGoogle, error) {
+	row := q.db.QueryRowContext(ctx, getIntegrationsGoogle)
+	var i IntegrationsGoogle
+	err := row.Scan(
+		&i.ID,
+		&i.RefreshTokenCt,
+		&i.CalendarID,
+		&i.CalendarName,
+		&i.ConnectedAt,
+		&i.LastSyncError,
+		&i.CreatedAt,
+		&i.UpdatedAt,
+	)
+	return i, err
+}
+
+const getRecipeSearchSettings = `-- name: GetRecipeSearchSettings :one
+SELECT id, provider, base_url, api_key_ct, created_at, updated_at FROM recipe_search_settings
+WHERE id = 1
+`
+
+// Returns sql.ErrNoRows until the row exists.
+func (q *Queries) GetRecipeSearchSettings(ctx context.Context) (RecipeSearchSetting, error) {
+	row := q.db.QueryRowContext(ctx, getRecipeSearchSettings)
+	var i RecipeSearchSetting
+	err := row.Scan(
+		&i.ID,
+		&i.Provider,
+		&i.BaseUrl,
+		&i.ApiKeyCt,
+		&i.CreatedAt,
+		&i.UpdatedAt,
+	)
+	return i, err
+}
+
+const upsertAISettings = `-- name: UpsertAISettings :one
+INSERT INTO ai_settings (
+	id, provider, base_url, model, temperature, system_prompt, api_key_ct,
+	api_key_last4, key_verified_at, created_at, updated_at
+) VALUES (
+	1, ?, ?, ?, ?, ?, ?,
+	?, ?, ?, ?
+)
+ON CONFLICT (id) DO UPDATE SET
+	provider = excluded.provider,
+	base_url = excluded.base_url,
+	model = excluded.model,
+	temperature = excluded.temperature,
+	system_prompt = excluded.system_prompt,
+	api_key_ct = excluded.api_key_ct,
+	api_key_last4 = excluded.api_key_last4,
+	key_verified_at = excluded.key_verified_at,
+	updated_at = excluded.updated_at
+RETURNING id, provider, base_url, model, temperature, system_prompt, api_key_ct, api_key_last4, key_verified_at, created_at, updated_at
+`
+
+type UpsertAISettingsParams struct {
+	Provider      string
+	BaseUrl       string
+	Model         string
+	Temperature   float64
+	SystemPrompt  sql.NullString
+	ApiKeyCt      []byte
+	ApiKeyLast4   sql.NullString
+	KeyVerifiedAt sql.NullString
+	CreatedAt     string
+	UpdatedAt     string
+}
+
+// Creates the single row (id 1) or replaces every column except id and
+// created_at.
+func (q *Queries) UpsertAISettings(ctx context.Context, arg UpsertAISettingsParams) (AiSetting, error) {
+	row := q.db.QueryRowContext(ctx, upsertAISettings,
+		arg.Provider,
+		arg.BaseUrl,
+		arg.Model,
+		arg.Temperature,
+		arg.SystemPrompt,
+		arg.ApiKeyCt,
+		arg.ApiKeyLast4,
+		arg.KeyVerifiedAt,
+		arg.CreatedAt,
+		arg.UpdatedAt,
+	)
+	var i AiSetting
+	err := row.Scan(
+		&i.ID,
+		&i.Provider,
+		&i.BaseUrl,
+		&i.Model,
+		&i.Temperature,
+		&i.SystemPrompt,
+		&i.ApiKeyCt,
+		&i.ApiKeyLast4,
+		&i.KeyVerifiedAt,
+		&i.CreatedAt,
+		&i.UpdatedAt,
+	)
+	return i, err
+}
+
+const upsertChatSettings = `-- name: UpsertChatSettings :one
+INSERT INTO chat_settings (
+	id, webhook_url_ct, space_label, last_test_at, last_test_status,
+	created_at, updated_at
+) VALUES (
+	1, ?, ?, ?, ?,
+	?, ?
+)
+ON CONFLICT (id) DO UPDATE SET
+	webhook_url_ct = excluded.webhook_url_ct,
+	space_label = excluded.space_label,
+	last_test_at = excluded.last_test_at,
+	last_test_status = excluded.last_test_status,
+	updated_at = excluded.updated_at
+RETURNING id, webhook_url_ct, space_label, last_test_at, last_test_status, created_at, updated_at
+`
+
+type UpsertChatSettingsParams struct {
+	WebhookUrlCt   []byte
+	SpaceLabel     sql.NullString
+	LastTestAt     sql.NullString
+	LastTestStatus sql.NullString
+	CreatedAt      string
+	UpdatedAt      string
+}
+
+// Creates the single row (id 1) or replaces every column except id and
+// created_at.
+func (q *Queries) UpsertChatSettings(ctx context.Context, arg UpsertChatSettingsParams) (ChatSetting, error) {
+	row := q.db.QueryRowContext(ctx, upsertChatSettings,
+		arg.WebhookUrlCt,
+		arg.SpaceLabel,
+		arg.LastTestAt,
+		arg.LastTestStatus,
+		arg.CreatedAt,
+		arg.UpdatedAt,
+	)
+	var i ChatSetting
+	err := row.Scan(
+		&i.ID,
+		&i.WebhookUrlCt,
+		&i.SpaceLabel,
+		&i.LastTestAt,
+		&i.LastTestStatus,
+		&i.CreatedAt,
+		&i.UpdatedAt,
+	)
+	return i, err
+}
+
+const upsertHouseholdSettings = `-- name: UpsertHouseholdSettings :one
+INSERT INTO household_settings (
+	id, timezone, dinner_time, reminder_time, review_time, plan_draft_dow,
+	plan_draft_time, suggestions_dow, suggestions_time, chat_autosend_dow,
+	chat_autosend_time, onboarding_completed_at, updates_seen_at,
+	created_at, updated_at
+) VALUES (
+	1, ?, ?, ?, ?, ?,
+	?, ?, ?, ?,
+	?, ?, ?,
+	?, ?
+)
+ON CONFLICT (id) DO UPDATE SET
+	timezone = excluded.timezone,
+	dinner_time = excluded.dinner_time,
+	reminder_time = excluded.reminder_time,
+	review_time = excluded.review_time,
+	plan_draft_dow = excluded.plan_draft_dow,
+	plan_draft_time = excluded.plan_draft_time,
+	suggestions_dow = excluded.suggestions_dow,
+	suggestions_time = excluded.suggestions_time,
+	chat_autosend_dow = excluded.chat_autosend_dow,
+	chat_autosend_time = excluded.chat_autosend_time,
+	onboarding_completed_at = excluded.onboarding_completed_at,
+	updates_seen_at = excluded.updates_seen_at,
+	updated_at = excluded.updated_at
+RETURNING id, timezone, dinner_time, reminder_time, review_time, plan_draft_dow, plan_draft_time, suggestions_dow, suggestions_time, chat_autosend_dow, chat_autosend_time, onboarding_completed_at, updates_seen_at, created_at, updated_at
+`
+
+type UpsertHouseholdSettingsParams struct {
+	Timezone              string
+	DinnerTime            string
+	ReminderTime          string
+	ReviewTime            string
+	PlanDraftDow          int64
+	PlanDraftTime         string
+	SuggestionsDow        int64
+	SuggestionsTime       string
+	ChatAutosendDow       int64
+	ChatAutosendTime      string
+	OnboardingCompletedAt sql.NullString
+	UpdatesSeenAt         sql.NullString
+	CreatedAt             string
+	UpdatedAt             string
+}
+
+// Creates the single row (id 1) or replaces every column except id and
+// created_at.
+func (q *Queries) UpsertHouseholdSettings(ctx context.Context, arg UpsertHouseholdSettingsParams) (HouseholdSetting, error) {
+	row := q.db.QueryRowContext(ctx, upsertHouseholdSettings,
+		arg.Timezone,
+		arg.DinnerTime,
+		arg.ReminderTime,
+		arg.ReviewTime,
+		arg.PlanDraftDow,
+		arg.PlanDraftTime,
+		arg.SuggestionsDow,
+		arg.SuggestionsTime,
+		arg.ChatAutosendDow,
+		arg.ChatAutosendTime,
+		arg.OnboardingCompletedAt,
+		arg.UpdatesSeenAt,
+		arg.CreatedAt,
+		arg.UpdatedAt,
+	)
+	var i HouseholdSetting
+	err := row.Scan(
+		&i.ID,
+		&i.Timezone,
+		&i.DinnerTime,
+		&i.ReminderTime,
+		&i.ReviewTime,
+		&i.PlanDraftDow,
+		&i.PlanDraftTime,
+		&i.SuggestionsDow,
+		&i.SuggestionsTime,
+		&i.ChatAutosendDow,
+		&i.ChatAutosendTime,
+		&i.OnboardingCompletedAt,
+		&i.UpdatesSeenAt,
+		&i.CreatedAt,
+		&i.UpdatedAt,
+	)
+	return i, err
+}
+
+const upsertIntegrationsGoogle = `-- name: UpsertIntegrationsGoogle :one
+INSERT INTO integrations_google (
+	id, refresh_token_ct, calendar_id, calendar_name, connected_at,
+	last_sync_error, created_at, updated_at
+) VALUES (
+	1, ?, ?, ?, ?,
+	?, ?, ?
+)
+ON CONFLICT (id) DO UPDATE SET
+	refresh_token_ct = excluded.refresh_token_ct,
+	calendar_id = excluded.calendar_id,
+	calendar_name = excluded.calendar_name,
+	connected_at = excluded.connected_at,
+	last_sync_error = excluded.last_sync_error,
+	updated_at = excluded.updated_at
+RETURNING id, refresh_token_ct, calendar_id, calendar_name, connected_at, last_sync_error, created_at, updated_at
+`
+
+type UpsertIntegrationsGoogleParams struct {
+	RefreshTokenCt []byte
+	CalendarID     sql.NullString
+	CalendarName   sql.NullString
+	ConnectedAt    string
+	LastSyncError  sql.NullString
+	CreatedAt      string
+	UpdatedAt      string
+}
+
+// Creates the single row (id 1) or replaces every column except id and
+// created_at.
+func (q *Queries) UpsertIntegrationsGoogle(ctx context.Context, arg UpsertIntegrationsGoogleParams) (IntegrationsGoogle, error) {
+	row := q.db.QueryRowContext(ctx, upsertIntegrationsGoogle,
+		arg.RefreshTokenCt,
+		arg.CalendarID,
+		arg.CalendarName,
+		arg.ConnectedAt,
+		arg.LastSyncError,
+		arg.CreatedAt,
+		arg.UpdatedAt,
+	)
+	var i IntegrationsGoogle
+	err := row.Scan(
+		&i.ID,
+		&i.RefreshTokenCt,
+		&i.CalendarID,
+		&i.CalendarName,
+		&i.ConnectedAt,
+		&i.LastSyncError,
+		&i.CreatedAt,
+		&i.UpdatedAt,
+	)
+	return i, err
+}
+
+const upsertRecipeSearchSettings = `-- name: UpsertRecipeSearchSettings :one
+INSERT INTO recipe_search_settings (
+	id, provider, base_url, api_key_ct, created_at, updated_at
+) VALUES (
+	1, ?, ?, ?, ?, ?
+)
+ON CONFLICT (id) DO UPDATE SET
+	provider = excluded.provider,
+	base_url = excluded.base_url,
+	api_key_ct = excluded.api_key_ct,
+	updated_at = excluded.updated_at
+RETURNING id, provider, base_url, api_key_ct, created_at, updated_at
+`
+
+type UpsertRecipeSearchSettingsParams struct {
+	Provider  string
+	BaseUrl   sql.NullString
+	ApiKeyCt  []byte
+	CreatedAt string
+	UpdatedAt string
+}
+
+// Creates the single row (id 1) or replaces every column except id and
+// created_at.
+func (q *Queries) UpsertRecipeSearchSettings(ctx context.Context, arg UpsertRecipeSearchSettingsParams) (RecipeSearchSetting, error) {
+	row := q.db.QueryRowContext(ctx, upsertRecipeSearchSettings,
+		arg.Provider,
+		arg.BaseUrl,
+		arg.ApiKeyCt,
+		arg.CreatedAt,
+		arg.UpdatedAt,
+	)
+	var i RecipeSearchSetting
+	err := row.Scan(
+		&i.ID,
+		&i.Provider,
+		&i.BaseUrl,
+		&i.ApiKeyCt,
+		&i.CreatedAt,
+		&i.UpdatedAt,
+	)
+	return i, err
+}

internal/household/week.go

@@ -0,0 +1,130 @@
+// Package household owns household-local calendar and clock logic. Every
+// function works in the household's IANA zone, passed explicitly, and never
+// in the process zone (time.Local).
+//
+// Weeks are ISO weeks: Monday to Sunday, numbered by ISO 8601, so the period
+// key of 2026-12-28 is "2026-W53" and of 2027-01-04 is "2027-W01".
+//
+// Calendar arithmetic is done on UTC midnights, which have no DST, so a date
+// plus seven days is always the same weekday a week later.
+package household
+
+import (
+	"errors"
+	"fmt"
+	"time"
+
+	"code.cubecraftlabs.com/Maniacs_Mansion/Meal-Quest/internal/store"
+)
+
+// Today returns the household-local date at instant now in zone loc.
+func Today(now time.Time, loc *time.Location) store.LocalDate {
+	return store.LocalDateOf(now.In(loc))
+}
+
+// WeekStart returns the ISO Monday of the week containing d. It panics if d is
+// the zero LocalDate.
+func WeekStart(d store.LocalDate) store.LocalDate {
+	t := midnightUTC(d)
+	// Weekday counts from Sunday = 0; shift so Monday = 0 and Sunday = 6.
+	sinceMonday := (int(t.Weekday()) + 6) % 7
+	return store.LocalDateOf(t.AddDate(0, 0, -sinceMonday))
+}
+
+// WeekDays returns the seven dates Monday to Sunday of the week starting on
+// weekStart. It is an error if weekStart is not a Monday.
+func WeekDays(weekStart store.LocalDate) ([7]store.LocalDate, error) {
+	t, err := monday(weekStart)
+	if err != nil {
+		return [7]store.LocalDate{}, err
+	}
+	var days [7]store.LocalDate
+	for i := range days {
+		days[i] = store.LocalDateOf(t.AddDate(0, 0, i))
+	}
+	return days, nil
+}
+
+// WeekLabel returns the display label of the week starting on weekStart, such
+// as "Week 41 · Oct 5 – 11", or "Week 40 · Sep 28 – Oct 4" when the week
+// spans two months. It is an error if weekStart is not a Monday.
+func WeekLabel(weekStart store.LocalDate) (string, error) {
+	start, err := monday(weekStart)
+	if err != nil {
+		return "", err
+	}
+	_, week := start.ISOWeek()
+	end := start.AddDate(0, 0, 6)
+	if end.Month() == start.Month() {
+		return fmt.Sprintf("Week %d · %s – %d", week, start.Format("Jan 2"), end.Day()), nil
+	}
+	return fmt.Sprintf("Week %d · %s – %s", week, start.Format("Jan 2"), end.Format("Jan 2")), nil
+}
+
+// PeriodKey returns the ISO week key of the week containing d, such as
+// "2026-W41". The year is the ISO week-numbering year, which differs from the
+// calendar year around New Year. It panics if d is the zero LocalDate.
+func PeriodKey(d store.LocalDate) string {
+	year, week := midnightUTC(d).ISOWeek()
+	return fmt.Sprintf("%04d-W%02d", year, week)
+}
+
+// LocalTime returns the instant at which household clocks in zone loc read
+// hhmm (24-hour "HH:MM") on date d.
+//
+// DST rule: a wall time that does not exist (spring forward) or occurs twice
+// (fall back) is read with the UTC offset in effect before the transition. A
+// skipped time therefore moves forward by the length of the gap (02:30 on a
+// day clocks jump 02:00→03:00 is 03:30), and a repeated time is its first
+// occurrence. The rule assumes at most one offset change in the 48 hours
+// around the wall time, which holds for every real zone.
+func LocalTime(loc *time.Location, d store.LocalDate, hhmm string) (time.Time, error) {
+	if d.IsZero() {
+		return time.Time{}, errors.New("household: zero date")
+	}
+	clock, err := time.Parse("15:04", hhmm)
+	if err != nil || clock.Format("15:04") != hhmm {
+		return time.Time{}, fmt.Errorf("household: invalid time %q: want HH:MM", hhmm)
+	}
+	y, m, day := d.Date()
+	// wall is the wall-clock reading expressed as if it were UTC; the real
+	// instant is wall minus the zone's UTC offset at that instant.
+	wall := time.Date(y, m, day, clock.Hour(), clock.Minute(), 0, 0, time.UTC)
+	before := offset(wall.Add(-24*time.Hour), loc)
+	after := offset(wall.Add(24*time.Hour), loc)
+	early := wall.Add(-before)
+	late := wall.Add(-after)
+	// Use the post-transition offset only when the pre-transition reading is
+	// not a real wall time and the post-transition one is.
+	if offset(early, loc) != before && offset(late, loc) == after {
+		return late.In(loc), nil
+	}
+	return early.In(loc), nil
+}
+
+// offset returns the UTC offset of zone loc at instant t.
+func offset(t time.Time, loc *time.Location) time.Duration {
+	_, secs := t.In(loc).Zone()
+	return time.Duration(secs) * time.Second
+}
+
+// midnightUTC returns d as midnight UTC. It panics if d is the zero LocalDate.
+func midnightUTC(d store.LocalDate) time.Time {
+	if d.IsZero() {
+		panic("household: zero date")
+	}
+	y, m, day := d.Date()
+	return time.Date(y, m, day, 0, 0, 0, 0, time.UTC)
+}
+
+// monday returns weekStart as midnight UTC, or an error if it is not a Monday.
+func monday(weekStart store.LocalDate) (time.Time, error) {
+	if weekStart.IsZero() {
+		return time.Time{}, errors.New("household: zero week start")
+	}
+	t := midnightUTC(weekStart)
+	if t.Weekday() != time.Monday {
+		return time.Time{}, fmt.Errorf("household: week start %s is a %s, want Monday", weekStart, t.Weekday())
+	}
+	return t, nil
+}

internal/household/week_test.go

@@ -0,0 +1,311 @@
+package household
+
+import (
+	"strings"
+	"testing"
+	"time"
+
+	"code.cubecraftlabs.com/Maniacs_Mansion/Meal-Quest/internal/store"
+)
+
+// TestMain runs the package with the process zone set to UTC, so any use of
+// time.Local in place of the household zone shows up as a failure.
+func TestMain(m *testing.M) {
+	time.Local = time.UTC
+	m.Run()
+}
+
+func loadZone(t *testing.T, name string) *time.Location {
+	t.Helper()
+	loc, err := time.LoadLocation(name)
+	if err != nil {
+		t.Fatalf("LoadLocation(%q): %v", name, err)
+	}
+	return loc
+}
+
+func date(t *testing.T, s string) store.LocalDate {
+	t.Helper()
+	d, err := store.ParseLocalDate(s)
+	if err != nil {
+		t.Fatal(err)
+	}
+	return d
+}
+
+func utc(t *testing.T, s string) time.Time {
+	t.Helper()
+	ts, err := time.Parse(time.RFC3339, s)
+	if err != nil {
+		t.Fatal(err)
+	}
+	return ts
+}
+
+func TestTodayUsesHouseholdZone(t *testing.T) {
+	chicago := loadZone(t, "America/Chicago")
+	auckland := loadZone(t, "Pacific/Auckland")
+	for _, tc := range []struct {
+		now  string
+		loc  *time.Location
+		want string
+	}{
+		// 2026-10-05T04:30Z is Sunday 23:30 in Chicago (CDT, UTC−5) and
+		// Monday 17:30 in Auckland (NZDT, UTC+13).
+		{"2026-10-05T04:30:00Z", chicago, "2026-10-04"},
+		{"2026-10-05T04:30:00Z", auckland, "2026-10-05"},
+		// 2026-10-04T11:30Z is Sunday 06:30 in Chicago and already Monday
+		// 00:30 in Auckland.
+		{"2026-10-04T11:30:00Z", chicago, "2026-10-04"},
+		{"2026-10-04T11:30:00Z", auckland, "2026-10-05"},
+	} {
+		if got := Today(utc(t, tc.now), tc.loc).String(); got != tc.want {
+			t.Errorf("Today(%s, %s) = %s, want %s", tc.now, tc.loc, got, tc.want)
+		}
+	}
+}
+
+func TestSundayNightMondayMorningWeekAssignment(t *testing.T) {
+	for _, tc := range []struct {
+		zone, now     string
+		wantDay       string
+		wantWeekStart string
+		wantPeriodKey string
+		wantWeekday   time.Weekday
+	}{
+		// Sunday 23:59 local belongs to the week that started the previous
+		// Monday; one minute later, Monday 00:00 starts a new week.
+		{"America/Chicago", "2026-10-12T04:59:00Z", "2026-10-11", "2026-10-05", "2026-W41", time.Sunday},
+		{"America/Chicago", "2026-10-12T05:00:00Z", "2026-10-12", "2026-10-12", "2026-W42", time.Monday},
+		{"Pacific/Auckland", "2026-10-11T10:59:00Z", "2026-10-11", "2026-10-05", "2026-W41", time.Sunday},
+		{"Pacific/Auckland", "2026-10-11T11:00:00Z", "2026-10-12", "2026-10-12", "2026-W42", time.Monday},
+	} {
+		loc := loadZone(t, tc.zone)
+		now := utc(t, tc.now)
+		if wd := now.In(loc).Weekday(); wd != tc.wantWeekday {
+			t.Fatalf("%s in %s is %s, want %s", tc.now, tc.zone, wd, tc.wantWeekday)
+		}
+		today := Today(now, loc)
+		if today.String() != tc.wantDay {
+			t.Errorf("Today(%s, %s) = %s, want %s", tc.now, tc.zone, today, tc.wantDay)
+		}
+		if got := WeekStart(today).String(); got != tc.wantWeekStart {
+			t.Errorf("WeekStart(%s) = %s, want %s", today, got, tc.wantWeekStart)
+		}
+		if got := PeriodKey(today); got != tc.wantPeriodKey {
+			t.Errorf("PeriodKey(%s) = %s, want %s", today, got, tc.wantPeriodKey)
+		}
+	}
+}
+
+func TestWeekStartEveryWeekday(t *testing.T) {
+	for _, d := range []string{
+		"2026-10-05", "2026-10-06", "2026-10-07", "2026-10-08",
+		"2026-10-09", "2026-10-10", "2026-10-11",
+	} {
+		if got := WeekStart(date(t, d)).String(); got != "2026-10-05" {
+			t.Errorf("WeekStart(%s) = %s, want 2026-10-05", d, got)
+		}
+	}
+	if got := WeekStart(date(t, "2026-10-12")).String(); got != "2026-10-12" {
+		t.Errorf("WeekStart(2026-10-12) = %s, want 2026-10-12", got)
+	}
+}
+
+func TestWeekDays(t *testing.T) {
+	days, err := WeekDays(date(t, "2026-09-28"))
+	if err != nil {
+		t.Fatal(err)
+	}
+	want := []string{
+		"2026-09-28", "2026-09-29", "2026-09-30", "2026-10-01",
+		"2026-10-02", "2026-10-03", "2026-10-04",
+	}
+	for i, d := range days {
+		if d.String() != want[i] {
+			t.Errorf("WeekDays[%d] = %s, want %s", i, d, want[i])
+		}
+	}
+}
+
+func TestWeekDaysAcrossDSTChange(t *testing.T) {
+	// Chicago falls back on Sunday 2026-11-01 and Auckland springs forward on
+	// Sunday 2026-09-27; neither shifts the dates of those weeks.
+	for _, tc := range []struct{ start, last string }{
+		{"2026-10-26", "2026-11-01"},
+		{"2026-09-21", "2026-09-27"},
+		{"2026-03-02", "2026-03-08"},
+	} {
+		days, err := WeekDays(date(t, tc.start))
+		if err != nil {
+			t.Fatal(err)
+		}
+		if days[0].String() != tc.start || days[6].String() != tc.last {
+			t.Errorf("WeekDays(%s) = %s … %s, want %s … %s", tc.start, days[0], days[6], tc.start, tc.last)
+		}
+	}
+}
+
+func TestISOWeekYearBoundary(t *testing.T) {
+	for _, tc := range []struct{ day, weekStart, key string }{
+		{"2026-12-27", "2026-12-21", "2026-W52"},
+		{"2026-12-28", "2026-12-28", "2026-W53"},
+		{"2026-12-31", "2026-12-28", "2026-W53"},
+		{"2027-01-01", "2026-12-28", "2026-W53"},
+		{"2027-01-03", "2026-12-28", "2026-W53"},
+		{"2027-01-04", "2027-01-04", "2027-W01"},
+		{"2027-01-10", "2027-01-04", "2027-W01"},
+		{"2026-01-01", "2025-12-29", "2026-W01"},
+		{"2026-10-07", "2026-10-05", "2026-W41"},
+	} {
+		d := date(t, tc.day)
+		if got := WeekStart(d).String(); got != tc.weekStart {
+			t.Errorf("WeekStart(%s) = %s, want %s", tc.day, got, tc.weekStart)
+		}
+		if got := PeriodKey(d); got != tc.key {
+			t.Errorf("PeriodKey(%s) = %s, want %s", tc.day, got, tc.key)
+		}
+	}
+	days, err := WeekDays(date(t, "2026-12-28"))
+	if err != nil {
+		t.Fatal(err)
+	}
+	if days[6].String() != "2027-01-03" {
+		t.Errorf("W53 Sunday = %s, want 2027-01-03", days[6])
+	}
+}
+
+func TestWeekLabel(t *testing.T) {
+	for _, tc := range []struct{ start, want string }{
+		{"2026-10-05", "Week 41 · Oct 5 – 11"},
+		{"2026-09-28", "Week 40 · Sep 28 – Oct 4"},
+		{"2026-12-28", "Week 53 · Dec 28 – Jan 3"},
+		{"2027-01-04", "Week 1 · Jan 4 – 10"},
+	} {
+		got, err := WeekLabel(date(t, tc.start))
+		if err != nil {
+			t.Fatal(err)
+		}
+		if got != tc.want {
+			t.Errorf("WeekLabel(%s) = %q, want %q", tc.start, got, tc.want)
+		}
+	}
+}
+
+func TestWeekStartRequired(t *testing.T) {
+	for _, d := range []store.LocalDate{date(t, "2026-10-07"), date(t, "2026-10-11"), {}} {
+		if _, err := WeekDays(d); err == nil {
+			t.Errorf("WeekDays(%s) succeeded, want error", d)
+		}
+		if _, err := WeekLabel(d); err == nil {
+			t.Errorf("WeekLabel(%s) succeeded, want error", d)
+		}
+	}
+}
+
+func TestZeroDatePanics(t *testing.T) {
+	for name, fn := range map[string]func(){
+		"WeekStart": func() { WeekStart(store.LocalDate{}) },
+		"PeriodKey": func() { PeriodKey(store.LocalDate{}) },
+	} {
+		func() {
+			defer func() {
+				if recover() == nil {
+					t.Errorf("%s(zero) did not panic", name)
+				}
+			}()
+			fn()
+		}()
+	}
+}
+
+func TestLocalTime(t *testing.T) {
+	for _, tc := range []struct {
+		zone, day, hhmm string
+		want            string // UTC instant
+		wantWall        string // local wall clock of the result
+	}{
+		// Ordinary days.
+		{"America/Chicago", "2026-10-05", "18:30", "2026-10-05T23:30:00Z", "2026-10-05 18:30 CDT"},
+		{"America/Chicago", "2026-12-28", "00:00", "2026-12-28T06:00:00Z", "2026-12-28 00:00 CST"},
+		{"Pacific/Auckland", "2026-10-05", "18:30", "2026-10-05T05:30:00Z", "2026-10-05 18:30 NZDT"},
+		{"Pacific/Auckland", "2026-07-01", "23:59", "2026-07-01T11:59:00Z", "2026-07-01 23:59 NZST"},
+
+		// Chicago spring forward, Sunday 2026-03-08: 02:00 CST → 03:00 CDT.
+		{"America/Chicago", "2026-03-08", "01:59", "2026-03-08T07:59:00Z", "2026-03-08 01:59 CST"},
+		{"America/Chicago", "2026-03-08", "02:00", "2026-03-08T08:00:00Z", "2026-03-08 03:00 CDT"},
+		{"America/Chicago", "2026-03-08", "02:30", "2026-03-08T08:30:00Z", "2026-03-08 03:30 CDT"},
+		{"America/Chicago", "2026-03-08", "03:00", "2026-03-08T08:00:00Z", "2026-03-08 03:00 CDT"},
+		{"America/Chicago", "2026-03-08", "03:30", "2026-03-08T08:30:00Z", "2026-03-08 03:30 CDT"},
+
+		// Chicago fall back, Sunday 2026-11-01: 02:00 CDT → 01:00 CST, so
+		// 01:00–01:59 happens twice; the first (CDT) occurrence is chosen.
+		{"America/Chicago", "2026-11-01", "00:59", "2026-11-01T05:59:00Z", "2026-11-01 00:59 CDT"},
+		{"America/Chicago", "2026-11-01", "01:00", "2026-11-01T06:00:00Z", "2026-11-01 01:00 CDT"},
+		{"America/Chicago", "2026-11-01", "01:30", "2026-11-01T06:30:00Z", "2026-11-01 01:30 CDT"},
+		{"America/Chicago", "2026-11-01", "02:00", "2026-11-01T08:00:00Z", "2026-11-01 02:00 CST"},
+
+		// Auckland spring forward, Sunday 2026-09-27: 02:00 NZST → 03:00 NZDT.
+		{"Pacific/Auckland", "2026-09-27", "01:59", "2026-09-26T13:59:00Z", "2026-09-27 01:59 NZST"},
+		{"Pacific/Auckland", "2026-09-27", "02:30", "2026-09-26T14:30:00Z", "2026-09-27 03:30 NZDT"},
+		{"Pacific/Auckland", "2026-09-27", "03:00", "2026-09-26T14:00:00Z", "2026-09-27 03:00 NZDT"},
+
+		// Auckland fall back, Sunday 2026-04-05: 03:00 NZDT → 02:00 NZST, so
+		// 02:00–02:59 happens twice; the first (NZDT) occurrence is chosen.
+		{"Pacific/Auckland", "2026-04-05", "01:59", "2026-04-04T12:59:00Z", "2026-04-05 01:59 NZDT"},
+		{"Pacific/Auckland", "2026-04-05", "02:30", "2026-04-04T13:30:00Z", "2026-04-05 02:30 NZDT"},
+		{"Pacific/Auckland", "2026-04-05", "03:00", "2026-04-04T15:00:00Z", "2026-04-05 03:00 NZST"},
+	} {
+		loc := loadZone(t, tc.zone)
+		got, err := LocalTime(loc, date(t, tc.day), tc.hhmm)
+		if err != nil {
+			t.Fatalf("LocalTime(%s, %s, %s): %v", tc.zone, tc.day, tc.hhmm, err)
+		}
+		if !got.Equal(utc(t, tc.want)) {
+			t.Errorf("LocalTime(%s, %s, %s) = %s, want %s", tc.zone, tc.day, tc.hhmm, got.UTC().Format(time.RFC3339), tc.want)
+		}
+		if wall := got.Format("2006-01-02 15:04 MST"); wall != tc.wantWall {
+			t.Errorf("LocalTime(%s, %s, %s) wall = %s, want %s", tc.zone, tc.day, tc.hhmm, wall, tc.wantWall)
+		}
+		if got.Location() != loc {
+			t.Errorf("LocalTime(%s, …) location = %s, want %s", tc.zone, got.Location(), loc)
+		}
+	}
+}
+
+func TestLocalTimeDeterministic(t *testing.T) {
+	// The same ambiguous or skipped input always resolves to the same instant.
+	chicago := loadZone(t, "America/Chicago")
+	for _, tc := range []struct{ day, hhmm string }{{"2026-03-08", "02:30"}, {"2026-11-01", "01:30"}} {
+		first, err := LocalTime(chicago, date(t, tc.day), tc.hhmm)
+		if err != nil {
+			t.Fatal(err)
+		}
+		for range 100 {
+			again, err := LocalTime(chicago, date(t, tc.day), tc.hhmm)
+			if err != nil {
+				t.Fatal(err)
+			}
+			if !again.Equal(first) {
+				t.Fatalf("LocalTime(%s, %s) = %s then %s", tc.day, tc.hhmm, first, again)
+			}
+		}
+	}
+}
+
+func TestLocalTimeRejectsInvalid(t *testing.T) {
+	chicago := loadZone(t, "America/Chicago")
+	d := date(t, "2026-10-05")
+	for _, hhmm := range []string{
+		"", "9:30", "09:3", "24:00", "23:60", "09:30:00", "0930", " 09:30", "09:30 ", "9am", "-1:00",
+	} {
+		if got, err := LocalTime(chicago, d, hhmm); err == nil {
+			t.Errorf("LocalTime(%q) = %s, want error", hhmm, got)
+		} else if !strings.Contains(err.Error(), "HH:MM") {
+			t.Errorf("LocalTime(%q) error %q does not name the HH:MM form", hhmm, err)
+		}
+	}
+	if _, err := LocalTime(chicago, store.LocalDate{}, "09:30"); err == nil {
+		t.Error("LocalTime(zero date) succeeded")
+	}
+}

internal/secretbox/secretbox.go

@@ -0,0 +1,81 @@
+// Package secretbox encrypts secrets at rest with AES-256-GCM. It is the only
+// owner of secret encryption: every _ct column holds a token produced by
+// Box.Encrypt, laid out as nonce ‖ ciphertext.
+//
+// Errors from this package never contain plaintext, ciphertext or key
+// material.
+package secretbox
+
+import (
+	"crypto/aes"
+	"crypto/cipher"
+	"crypto/rand"
+	"encoding/base64"
+	"errors"
+	"fmt"
+)
+
+// KeySize is the decoded key length in bytes (AES-256).
+const KeySize = 32
+
+var (
+	// ErrKeyEncoding means the key is not valid standard base64.
+	ErrKeyEncoding = errors.New("secretbox: key is not valid standard base64")
+	// ErrKeyLength means the decoded key is not exactly KeySize bytes.
+	ErrKeyLength = errors.New("secretbox: key must decode to exactly 32 bytes")
+	// ErrMalformed means a token is too short to hold a nonce and tag.
+	ErrMalformed = errors.New("secretbox: token is too short")
+	// ErrDecrypt means a token failed authentication: it was corrupted or
+	// sealed under a different key.
+	ErrDecrypt = errors.New("secretbox: token failed authentication")
+)
+
+// Box seals and opens secrets under one key. It is safe for concurrent use.
+type Box struct {
+	aead cipher.AEAD
+}
+
+// New returns a Box for encodedKey, the standard-base64 encoding of a
+// KeySize-byte key.
+func New(encodedKey string) (*Box, error) {
+	key, err := base64.StdEncoding.Strict().DecodeString(encodedKey)
+	if err != nil {
+		// The decoder's error is not wrapped: it describes the key's content.
+		return nil, ErrKeyEncoding
+	}
+	if len(key) != KeySize {
+		return nil, fmt.Errorf("%w, got %d", ErrKeyLength, len(key))
+	}
+	block, err := aes.NewCipher(key)
+	if err != nil {
+		return nil, fmt.Errorf("secretbox: %w", err)
+	}
+	aead, err := cipher.NewGCM(block)
+	if err != nil {
+		return nil, fmt.Errorf("secretbox: %w", err)
+	}
+	return &Box{aead: aead}, nil
+}
+
+// Encrypt seals plaintext under a fresh random nonce and returns
+// nonce ‖ ciphertext.
+func (b *Box) Encrypt(plaintext []byte) ([]byte, error) {
+	nonce := make([]byte, b.aead.NonceSize(), b.aead.NonceSize()+len(plaintext)+b.aead.Overhead())
+	if _, err := rand.Read(nonce); err != nil {
+		return nil, fmt.Errorf("secretbox: generate nonce: %w", err)
+	}
+	return b.aead.Seal(nonce, nonce, plaintext, nil), nil
+}
+
+// Decrypt authenticates and opens a token produced by Encrypt.
+func (b *Box) Decrypt(token []byte) ([]byte, error) {
+	n := b.aead.NonceSize()
+	if len(token) < n+b.aead.Overhead() {
+		return nil, ErrMalformed
+	}
+	plaintext, err := b.aead.Open(nil, token[:n], token[n:], nil)
+	if err != nil {
+		return nil, ErrDecrypt
+	}
+	return plaintext, nil
+}

internal/secretbox/secretbox_test.go

@@ -0,0 +1,200 @@
+package secretbox
+
+import (
+	"bytes"
+	"crypto/rand"
+	"encoding/base64"
+	"errors"
+	"strings"
+	"testing"
+)
+
+// newKey returns a fresh random key and its standard-base64 encoding.
+func newKey(t *testing.T) ([]byte, string) {
+	t.Helper()
+	key := make([]byte, KeySize)
+	if _, err := rand.Read(key); err != nil {
+		t.Fatal(err)
+	}
+	return key, base64.StdEncoding.EncodeToString(key)
+}
+
+func newBox(t *testing.T) (*Box, []byte, string) {
+	t.Helper()
+	key, encoded := newKey(t)
+	box, err := New(encoded)
+	if err != nil {
+		t.Fatal(err)
+	}
+	return box, key, encoded
+}
+
+// assertNoLeak fails if err's message contains any of the secrets, either
+// raw or base64-encoded.
+func assertNoLeak(t *testing.T, err error, secrets ...[]byte) {
+	t.Helper()
+	msg := err.Error()
+	for _, s := range secrets {
+		if len(s) == 0 {
+			continue
+		}
+		for _, form := range []string{string(s), base64.StdEncoding.EncodeToString(s), base64.RawStdEncoding.EncodeToString(s)} {
+			if strings.Contains(msg, form) {
+				t.Errorf("error %q leaks secret material %q", msg, form)
+			}
+		}
+	}
+}
+
+func TestRoundTrip(t *testing.T) {
+	box, _, _ := newBox(t)
+	for _, plaintext := range [][]byte{
+		[]byte("ya29.oauth-refresh-token"),
+		{},
+		bytes.Repeat([]byte{0x00, 0xff}, 4096),
+	} {
+		token, err := box.Encrypt(plaintext)
+		if err != nil {
+			t.Fatal(err)
+		}
+		if len(plaintext) > 0 && bytes.Contains(token, plaintext) {
+			t.Errorf("token contains the plaintext")
+		}
+		got, err := box.Decrypt(token)
+		if err != nil {
+			t.Fatal(err)
+		}
+		if !bytes.Equal(got, plaintext) {
+			t.Errorf("Decrypt = %q, want %q", got, plaintext)
+		}
+	}
+}
+
+func TestTokenLayout(t *testing.T) {
+	box, _, _ := newBox(t)
+	plaintext := []byte("secret")
+	token, err := box.Encrypt(plaintext)
+	if err != nil {
+		t.Fatal(err)
+	}
+	// nonce (12) ‖ ciphertext (len(plaintext)) ‖ GCM tag (16).
+	if want := 12 + len(plaintext) + 16; len(token) != want {
+		t.Errorf("token length = %d, want %d", len(token), want)
+	}
+}
+
+func TestEncryptUsesFreshNonce(t *testing.T) {
+	box, _, _ := newBox(t)
+	plaintext := []byte("same plaintext")
+	a, err := box.Encrypt(plaintext)
+	if err != nil {
+		t.Fatal(err)
+	}
+	b, err := box.Encrypt(plaintext)
+	if err != nil {
+		t.Fatal(err)
+	}
+	if bytes.Equal(a, b) {
+		t.Fatal("two encryptions of the same plaintext are identical")
+	}
+	if bytes.Equal(a[:12], b[:12]) {
+		t.Error("two encryptions reused the same nonce")
+	}
+}
+
+func TestDecryptRejectsFlippedByte(t *testing.T) {
+	box, key, encoded := newBox(t)
+	plaintext := []byte("do-not-leak-me")
+	token, err := box.Encrypt(plaintext)
+	if err != nil {
+		t.Fatal(err)
+	}
+	// Flip one bit in every position: nonce, ciphertext and tag.
+	for i := range token {
+		tampered := bytes.Clone(token)
+		tampered[i] ^= 0x01
+		got, err := box.Decrypt(tampered)
+		if !errors.Is(err, ErrDecrypt) {
+			t.Fatalf("byte %d flipped: Decrypt = %q, %v; want ErrDecrypt", i, got, err)
+		}
+		if got != nil {
+			t.Errorf("byte %d flipped: Decrypt returned plaintext alongside the error", i)
+		}
+		assertNoLeak(t, err, plaintext, key, []byte(encoded), tampered)
+	}
+}
+
+func TestDecryptRejectsWrongKey(t *testing.T) {
+	box, key, encoded := newBox(t)
+	other, otherKey, otherEncoded := newBox(t)
+	plaintext := []byte("do-not-leak-me")
+	token, err := box.Encrypt(plaintext)
+	if err != nil {
+		t.Fatal(err)
+	}
+	got, err := other.Decrypt(token)
+	if !errors.Is(err, ErrDecrypt) {
+		t.Fatalf("Decrypt with wrong key = %q, %v; want ErrDecrypt", got, err)
+	}
+	assertNoLeak(t, err, plaintext, key, []byte(encoded), otherKey, []byte(otherEncoded), token)
+}
+
+func TestDecryptRejectsShortToken(t *testing.T) {
+	box, _, _ := newBox(t)
+	for _, n := range []int{0, 1, 12, 12 + 15} {
+		token := bytes.Repeat([]byte{0xab}, n)
+		if _, err := box.Decrypt(token); !errors.Is(err, ErrMalformed) {
+			t.Errorf("Decrypt(%d bytes) error = %v, want ErrMalformed", n, err)
+		}
+	}
+}
+
+func TestNewRejectsBadKey(t *testing.T) {
+	key, _ := newKey(t)
+	tests := []struct {
+		name    string
+		encoded string
+		want    error
+	}{
+		{"empty", "", ErrKeyLength},
+		{"not base64", "not*base64!" + strings.Repeat("A", 33), ErrKeyEncoding},
+		{"url-safe alphabet", strings.Repeat("_-", 22), ErrKeyEncoding},
+		{"unpadded", base64.RawStdEncoding.EncodeToString(key), ErrKeyEncoding},
+		{"raw key bytes", string(key), ErrKeyEncoding},
+		{"too short", base64.StdEncoding.EncodeToString(key[:31]), ErrKeyLength},
+		{"too long", base64.StdEncoding.EncodeToString(append(bytes.Clone(key), 0x00)), ErrKeyLength},
+		{"AES-128 length", base64.StdEncoding.EncodeToString(key[:16]), ErrKeyLength},
+	}
+	for _, tt := range tests {
+		t.Run(tt.name, func(t *testing.T) {
+			box, err := New(tt.encoded)
+			if !errors.Is(err, tt.want) {
+				t.Fatalf("New error = %v, want %v", err, tt.want)
+			}
+			if box != nil {
+				t.Error("New returned a Box alongside the error")
+			}
+			assertNoLeak(t, err, key, []byte(tt.encoded))
+		})
+	}
+}
+
+func TestNewAcceptsBase64Key(t *testing.T) {
+	key, encoded := newKey(t)
+	box, err := New(encoded)
+	if err != nil {
+		t.Fatal(err)
+	}
+	// A Box built from the same key opens tokens sealed by the first.
+	token, err := box.Encrypt([]byte("x"))
+	if err != nil {
+		t.Fatal(err)
+	}
+	again, err := New(base64.StdEncoding.EncodeToString(key))
+	if err != nil {
+		t.Fatal(err)
+	}
+	if _, err := again.Decrypt(token); err != nil {
+		t.Errorf("same key failed to decrypt: %v", err)
+	}
+}

internal/store/migrate_test.go

@@ -3,6 +3,7 @@ package store
 import (
 	"context"
 	"io/fs"
+	"slices"
 	"strings"
 	"testing"
 	"testing/fstest"
@@ -174,33 +175,89 @@ func TestMigrateEmbeddedMigrations(t *testing.T) {
 	if err := Migrate(context.Background(), s.Writer, migrations.FS); err != nil {
 		t.Fatal(err)
 	}
-	if got := versions(migrationRows(t, s)); got != "0000_bootstrap" {
-		t.Errorf("versions = %q, want 0000_bootstrap", got)
+	if got := versions(migrationRows(t, s)); got != "0000_bootstrap,0001_init" {
+		t.Errorf("versions = %q, want 0000_bootstrap,0001_init", got)
 	}
 }
 
-func TestMigrateEmbeddedThenNewMigrationApplies(t *testing.T) {
+func TestMigrateBootstrapThenEmbeddedInitApplies(t *testing.T) {
 	ctx := context.Background()
 	s := openTestStore(t)
-	if err := Migrate(ctx, s.Writer, migrations.FS); err != nil {
-		t.Fatal(err)
-	}
 	bootstrap, err := fs.ReadFile(migrations.FS, "0000_bootstrap.sql")
 	if err != nil {
 		t.Fatal(err)
 	}
-	// A later build adds 0001_init.sql alongside the unchanged bootstrap.
-	upgraded := fstest.MapFS{
-		"0000_bootstrap.sql": {Data: bootstrap},
-		"0001_init.sql":      {Data: []byte("CREATE TABLE later (id INTEGER PRIMARY KEY);")},
+	// 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, upgraded); err != nil {
+	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, "later") {
+	if !tableExists(t, s, "dinners") {
 		t.Error("table from 0001_init was not created")
 	}
 }
+
+// schemaSQL returns every schema object's definition, so a test can prove a
+// later Migrate created or altered nothing.
+func schemaSQL(t *testing.T, s *Store) []string {
+	t.Helper()
+	rows, err := s.Reader.Query("SELECT type || ' ' || name || ': ' || coalesce(sql, '') FROM sqlite_schema ORDER BY type, name")
+	if err != nil {
+		t.Fatal(err)
+	}
+	defer rows.Close()
+	var out []string
+	for rows.Next() {
+		var def string
+		if err := rows.Scan(&def); err != nil {
+			t.Fatal(err)
+		}
+		out = append(out, def)
+	}
+	if err := rows.Err(); err != nil {
+		t.Fatal(err)
+	}
+	return out
+}
+
+func TestMigrateReopenSameDataDirIsNoOp(t *testing.T) {
+	ctx := context.Background()
+	dir := t.TempDir()
+
+	first, err := Open(ctx, dir)
+	if err != nil {
+		t.Fatal(err)
+	}
+	if err := Migrate(ctx, first.Writer, migrations.FS); err != nil {
+		t.Fatal(err)
+	}
+	before, beforeSchema := migrationRows(t, first), schemaSQL(t, first)
+	if err := first.Close(); err != nil {
+		t.Fatal(err)
+	}
+
+	second, err := Open(ctx, dir)
+	if err != nil {
+		t.Fatalf("reopen: %v", err)
+	}
+	t.Cleanup(func() {
+		if err := second.Close(); err != nil {
+			t.Error(err)
+		}
+	})
+	if err := Migrate(ctx, second.Writer, migrations.FS); err != nil {
+		t.Fatalf("migrate after reopen: %v", err)
+	}
+	if after := migrationRows(t, second); !slices.Equal(after, before) {
+		t.Errorf("schema_migrations changed on reopen:\n got %v\nwant %v", after, before)
+	}
+	if afterSchema := schemaSQL(t, second); !slices.Equal(afterSchema, beforeSchema) {
+		t.Errorf("schema changed on reopen:\n got %v\nwant %v", afterSchema, beforeSchema)
+	}
+}

internal/store/repository_test.go

@@ -0,0 +1,177 @@
+package store
+
+import (
+	"context"
+	"fmt"
+	"maps"
+	"reflect"
+	"slices"
+	"strings"
+	"testing"
+)
+
+// The tables below have no public sqlc queries yet (they arrive with the
+// planning, suggestion and Supply run features), so these tests drive them
+// with raw SQL to prove the schema stores, returns and updates their rows.
+
+const repoTime = "2026-10-03T12:00:00Z"
+
+type repoRow struct {
+	table string
+	cols  map[string]any
+}
+
+// repoParents are the rows the tables under test reference, in insert order.
+var repoParents = map[string]repoRow{
+	"dinners": {"dinners", map[string]any{
+		"id": "dinner-1", "name": "Chili", "normalized_name": "chili",
+		"status": "regular", "origin": "regular", "is_favorite": int64(0),
+		"grounding_status": "manual", "created_at": repoTime, "updated_at": repoTime,
+	}},
+	"suggestion_batches": {"suggestion_batches", map[string]any{
+		"id": "batch-1", "trigger": "manual", "status": "done",
+		"created_at": repoTime, "updated_at": repoTime,
+	}},
+	"week_plans": {"week_plans", map[string]any{
+		"id": "plan-1", "week_start": "2026-10-05", "status": "draft",
+		"created_at": repoTime, "updated_at": repoTime,
+	}},
+	"shopping_lists": {"shopping_lists", map[string]any{
+		"id": "list-1", "week_start": "2026-10-05", "plan_id": "plan-1",
+		"title": "Supply run", "status": "current",
+		"created_at": repoTime, "updated_at": repoTime,
+	}},
+	"shopping_groups": {"shopping_groups", map[string]any{
+		"id": "group-1", "list_id": "list-1", "dinner_id": "dinner-1",
+		"position": int64(0), "created_at": repoTime, "updated_at": repoTime,
+	}},
+}
+
+// readRepoRow reads cols of the row with id through the reader.
+func readRepoRow(t *testing.T, s *Store, table, id string, cols []string) map[string]any {
+	t.Helper()
+	quoted := make([]string, len(cols))
+	vals := make([]any, len(cols))
+	ptrs := make([]any, len(cols))
+	for i, c := range cols {
+		quoted[i] = `"` + c + `"`
+		ptrs[i] = &vals[i]
+	}
+	q := fmt.Sprintf(`SELECT %s FROM "%s" WHERE id = ?`, strings.Join(quoted, ", "), table)
+	if err := s.Reader.QueryRowContext(context.Background(), q, id).Scan(ptrs...); err != nil {
+		t.Fatalf("read %s %s: %v", table, id, err)
+	}
+	got := make(map[string]any, len(cols))
+	for i, c := range cols {
+		got[c] = vals[i]
+	}
+	return got
+}
+
+func TestRepositoryInsertReadUpdate(t *testing.T) {
+	cases := []struct {
+		parents []string
+		row     repoRow
+		update  map[string]any
+	}{
+		{
+			row: repoRow{"week_plans", map[string]any{
+				"id": "plan-2", "week_start": "2026-10-12", "status": "draft",
+				"ai_note": "Two Side quests this week.", "ai_run_id": nil, "calendar_synced_at": nil,
+				"created_at": repoTime, "updated_at": repoTime,
+			}},
+			update: map[string]any{"status": "synced", "calendar_synced_at": "2026-10-04T09:00:00Z"},
+		},
+		{
+			parents: []string{"dinners", "week_plans"},
+			row: repoRow{"plan_days", map[string]any{
+				"id": "day-1", "plan_id": "plan-1", "date": "2026-10-06", "dinner_id": "dinner-1",
+				"is_new": int64(0), "calendar_event_id": nil,
+				"created_at": repoTime, "updated_at": repoTime,
+			}},
+			update: map[string]any{"dinner_id": nil, "calendar_event_id": "evt-123"},
+		},
+		{
+			parents: []string{"dinners", "suggestion_batches"},
+			row: repoRow{"suggestions", map[string]any{
+				"id": "sugg-1", "dinner_id": "dinner-1", "batch_id": "batch-1",
+				"reason": "You make chili often.", "cited_regular_ids": `["dinner-1"]`,
+				"status": "pending", "decided_at": nil,
+				"created_at": repoTime, "updated_at": repoTime,
+			}},
+			update: map[string]any{"status": "accepted", "decided_at": "2026-10-04T10:00:00Z"},
+		},
+		{
+			parents: []string{"week_plans"},
+			row: repoRow{"shopping_lists", map[string]any{
+				"id": "list-2", "week_start": "2026-10-05", "plan_id": "plan-1",
+				"title": "Supply run", "status": "current", "sent_to_chat_at": nil, "frozen_at": nil,
+				"created_at": repoTime, "updated_at": repoTime,
+			}},
+			update: map[string]any{"status": "archived", "frozen_at": "2026-10-12T00:00:00Z"},
+		},
+		{
+			parents: []string{"dinners", "week_plans", "shopping_lists"},
+			row: repoRow{"shopping_groups", map[string]any{
+				"id": "group-2", "list_id": "list-1", "dinner_id": "dinner-1", "position": int64(0),
+				"created_at": repoTime, "updated_at": repoTime,
+			}},
+			update: map[string]any{"position": int64(3)},
+		},
+		{
+			parents: []string{"dinners", "week_plans", "shopping_lists", "shopping_groups"},
+			row: repoRow{"shopping_items", map[string]any{
+				"id": "item-1", "group_id": "group-1", "name": "Onion", "qty": "2",
+				"needed": int64(1), "position": int64(0),
+				"created_at": repoTime, "updated_at": repoTime,
+			}},
+			update: map[string]any{"needed": int64(0), "qty": "3"},
+		},
+	}
+
+	for _, tc := range cases {
+		t.Run(tc.row.table, func(t *testing.T) {
+			ctx := context.Background()
+			s := migratedStore(t)
+			for _, p := range tc.parents {
+				parent := repoParents[p]
+				if err := insertSchemaRow(s.Writer, parent.table, parent.cols); err != nil {
+					t.Fatalf("seed %s: %v", p, err)
+				}
+			}
+
+			table, id := tc.row.table, tc.row.cols["id"].(string)
+			cols := slices.Sorted(maps.Keys(tc.row.cols))
+
+			if err := insertSchemaRow(s.Writer, table, tc.row.cols); err != nil {
+				t.Fatalf("insert: %v", err)
+			}
+			if got := readRepoRow(t, s, table, id, cols); !reflect.DeepEqual(got, tc.row.cols) {
+				t.Fatalf("read after insert:\n got %v\nwant %v", got, tc.row.cols)
+			}
+
+			want := maps.Clone(tc.row.cols)
+			maps.Copy(want, tc.update)
+			want["updated_at"] = "2026-10-04T12:00:00Z"
+			setCols := slices.Sorted(maps.Keys(tc.update))
+			sets := make([]string, 0, len(setCols)+1)
+			args := make([]any, 0, len(setCols)+2)
+			for _, c := range append(setCols, "updated_at") {
+				sets = append(sets, `"`+c+`" = ?`)
+				args = append(args, want[c])
+			}
+			args = append(args, id)
+			q := fmt.Sprintf(`UPDATE "%s" SET %s WHERE id = ?`, table, strings.Join(sets, ", "))
+			res, err := s.Writer.ExecContext(ctx, q, args...)
+			if err != nil {
+				t.Fatalf("update: %v", err)
+			}
+			if n, err := res.RowsAffected(); err != nil || n != 1 {
+				t.Fatalf("update affected %d rows (err %v), want 1", n, err)
+			}
+			if got := readRepoRow(t, s, table, id, cols); !reflect.DeepEqual(got, want) {
+				t.Fatalf("read after update:\n got %v\nwant %v", got, want)
+			}
+		})
+	}
+}

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/time.go

@@ -86,6 +86,17 @@ func ParseLocalDate(s string) (LocalDate, error) {
 	return LocalDate{t.Year(), t.Month(), t.Day()}, nil
 }
 
+// LocalDateOf returns the calendar date of t in t's own location.
+func LocalDateOf(t time.Time) LocalDate {
+	y, m, d := t.Date()
+	return LocalDate{y, m, d}
+}
+
+// Date returns the year, month and day of d.
+func (d LocalDate) Date() (year int, month time.Month, day int) {
+	return d.year, d.month, d.day
+}
+
 // 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)
@@ -96,11 +107,15 @@ func (d LocalDate) IsZero() bool {
 	return d == LocalDate{}
 }
 
-// Value implements driver.Valuer. The zero LocalDate is an error.
+// Value implements driver.Valuer. The zero LocalDate is an error, as is a
+// year outside 0000–9999, which the four-digit stored form cannot represent.
 func (d LocalDate) Value() (driver.Value, error) {
 	if d.IsZero() {
 		return nil, errors.New("store: zero LocalDate")
 	}
+	if d.year < 0 || d.year > 9999 {
+		return nil, fmt.Errorf("store: LocalDate year %d outside 0000-9999", d.year)
+	}
 	return d.String(), nil
 }
 

internal/store/time_test.go

@@ -102,6 +102,25 @@ func TestLocalDateRoundTrip(t *testing.T) {
 	}
 }
 
+func TestLocalDateOf(t *testing.T) {
+	auckland, err := time.LoadLocation("Pacific/Auckland")
+	if err != nil {
+		t.Fatal(err)
+	}
+	// 2026-10-03T20:00Z is already 2026-10-04 in Auckland (UTC+13).
+	instant := time.Date(2026, time.October, 3, 20, 0, 0, 0, time.UTC)
+	if got := LocalDateOf(instant).String(); got != "2026-10-03" {
+		t.Errorf("LocalDateOf(UTC) = %s, want 2026-10-03", got)
+	}
+	d := LocalDateOf(instant.In(auckland))
+	if got := d.String(); got != "2026-10-04" {
+		t.Errorf("LocalDateOf(Auckland) = %s, want 2026-10-04", got)
+	}
+	if y, m, day := d.Date(); y != 2026 || m != time.October || day != 4 {
+		t.Errorf("Date() = %d-%d-%d, want 2026-10-4", y, m, day)
+	}
+}
+
 func TestLocalDateRejectsInvalid(t *testing.T) {
 	for _, str := range []string{
 		"", "2026-02-30", "2026-02-29", "2026-13-01", "2026-00-10", "2026-10-00",
@@ -119,6 +138,12 @@ func TestLocalDateRejectsInvalid(t *testing.T) {
 	if _, err := (LocalDate{}).Value(); err == nil {
 		t.Error("zero LocalDate Value succeeded")
 	}
+	for _, y := range []int{-1, 10000} {
+		d := LocalDateOf(time.Date(y, time.January, 1, 0, 0, 0, 0, time.UTC))
+		if _, err := d.Value(); err == nil {
+			t.Errorf("LocalDate year %d Value succeeded", y)
+		}
+	}
 	var d LocalDate
 	if err := d.Scan(nil); err == nil {
 		t.Error("Scan(nil) succeeded")

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/queries/dinners.sql

@@ -0,0 +1,57 @@
+-- name: CreateDinner :one
+INSERT INTO dinners (
+	id, name, normalized_name, status, origin, method, minutes_total,
+	difficulty, cuisine, is_favorite, recipe_id, photo_id, grounding_status,
+	updated_by, created_at, updated_at
+) VALUES (
+	?, ?, ?, ?, ?, ?, ?,
+	?, ?, ?, ?, ?, ?,
+	?, ?, ?
+)
+RETURNING *;
+
+-- name: GetDinner :one
+SELECT * FROM dinners
+WHERE id = ?;
+
+-- name: ListDinnersByStatus :many
+SELECT * FROM dinners
+WHERE status = ?
+ORDER BY normalized_name, id;
+
+-- name: UpdateDinner :one
+-- An unknown id returns sql.ErrNoRows.
+UPDATE dinners
+SET name = ?,
+	normalized_name = ?,
+	status = ?,
+	origin = ?,
+	method = ?,
+	minutes_total = ?,
+	difficulty = ?,
+	cuisine = ?,
+	recipe_id = ?,
+	photo_id = ?,
+	grounding_status = ?,
+	updated_by = ?,
+	updated_at = ?
+WHERE id = ?
+RETURNING *;
+
+-- name: ArchiveDinner :one
+-- An unknown id returns sql.ErrNoRows.
+UPDATE dinners
+SET status = 'archived',
+	updated_by = ?,
+	updated_at = ?
+WHERE id = ?
+RETURNING *;
+
+-- name: SetDinnerFavorite :one
+-- An unknown id returns sql.ErrNoRows.
+UPDATE dinners
+SET is_favorite = ?,
+	updated_by = ?,
+	updated_at = ?
+WHERE id = ?
+RETURNING *;

migrations/queries/photos.sql

@@ -0,0 +1,17 @@
+-- name: CreatePhoto :one
+INSERT INTO photos (
+	id, source_url, local_path, width, height, sha256, fetched_at,
+	created_at, updated_at
+) VALUES (
+	?, ?, ?, ?, ?, ?, ?,
+	?, ?
+)
+RETURNING *;
+
+-- name: GetPhotoBySHA256 :one
+-- Finds stored image bytes by content hash. The schema does not make sha256
+-- unique, so the earliest-created (lowest id) match is returned.
+SELECT * FROM photos
+WHERE sha256 = ?
+ORDER BY id
+LIMIT 1;

migrations/queries/recipes.sql

@@ -0,0 +1,65 @@
+-- name: UpsertRecipe :one
+-- A recipe is keyed by source_url: re-fetching the same URL updates the
+-- existing row and keeps its id and created_at, which the returned row
+-- carries. Replace its ingredients and steps in the same transaction with
+-- DeleteRecipeIngredients/DeleteRecipeSteps then the Create queries.
+INSERT INTO recipes (
+	id, source_url, site_name, title, total_minutes, prep_minutes,
+	cook_minutes, yield, raw_jsonld, fetched_at, created_at, updated_at
+) VALUES (
+	?, ?, ?, ?, ?, ?,
+	?, ?, ?, ?, ?, ?
+)
+ON CONFLICT (source_url) DO UPDATE SET
+	site_name = excluded.site_name,
+	title = excluded.title,
+	total_minutes = excluded.total_minutes,
+	prep_minutes = excluded.prep_minutes,
+	cook_minutes = excluded.cook_minutes,
+	yield = excluded.yield,
+	raw_jsonld = excluded.raw_jsonld,
+	fetched_at = excluded.fetched_at,
+	updated_at = excluded.updated_at
+RETURNING *;
+
+-- name: GetRecipe :one
+SELECT * FROM recipes
+WHERE id = ?;
+
+-- name: GetRecipeBySourceURL :one
+SELECT * FROM recipes
+WHERE source_url = ?;
+
+-- name: CreateRecipeIngredient :exec
+INSERT INTO recipe_ingredients (
+	id, recipe_id, position, raw_text, name, qty, unit, note,
+	created_at, updated_at
+) VALUES (
+	?, ?, ?, ?, ?, ?, ?, ?,
+	?, ?
+);
+
+-- name: ListRecipeIngredients :many
+SELECT * FROM recipe_ingredients
+WHERE recipe_id = ?
+ORDER BY position, id;
+
+-- name: DeleteRecipeIngredients :exec
+DELETE FROM recipe_ingredients
+WHERE recipe_id = ?;
+
+-- name: CreateRecipeStep :exec
+INSERT INTO recipe_steps (
+	id, recipe_id, position, text, created_at, updated_at
+) VALUES (
+	?, ?, ?, ?, ?, ?
+);
+
+-- name: ListRecipeSteps :many
+SELECT * FROM recipe_steps
+WHERE recipe_id = ?
+ORDER BY position, id;
+
+-- name: DeleteRecipeSteps :exec
+DELETE FROM recipe_steps
+WHERE recipe_id = ?;

migrations/queries/settings.sql

@@ -0,0 +1,128 @@
+-- name: GetHouseholdSettings :one
+-- Returns sql.ErrNoRows until the row exists.
+SELECT * FROM household_settings
+WHERE id = 1;
+
+-- name: UpsertHouseholdSettings :one
+-- Creates the single row (id 1) or replaces every column except id and
+-- created_at.
+INSERT INTO household_settings (
+	id, timezone, dinner_time, reminder_time, review_time, plan_draft_dow,
+	plan_draft_time, suggestions_dow, suggestions_time, chat_autosend_dow,
+	chat_autosend_time, onboarding_completed_at, updates_seen_at,
+	created_at, updated_at
+) VALUES (
+	1, ?, ?, ?, ?, ?,
+	?, ?, ?, ?,
+	?, ?, ?,
+	?, ?
+)
+ON CONFLICT (id) DO UPDATE SET
+	timezone = excluded.timezone,
+	dinner_time = excluded.dinner_time,
+	reminder_time = excluded.reminder_time,
+	review_time = excluded.review_time,
+	plan_draft_dow = excluded.plan_draft_dow,
+	plan_draft_time = excluded.plan_draft_time,
+	suggestions_dow = excluded.suggestions_dow,
+	suggestions_time = excluded.suggestions_time,
+	chat_autosend_dow = excluded.chat_autosend_dow,
+	chat_autosend_time = excluded.chat_autosend_time,
+	onboarding_completed_at = excluded.onboarding_completed_at,
+	updates_seen_at = excluded.updates_seen_at,
+	updated_at = excluded.updated_at
+RETURNING *;
+
+-- name: GetAISettings :one
+-- Returns sql.ErrNoRows until the row exists.
+SELECT * FROM ai_settings
+WHERE id = 1;
+
+-- name: UpsertAISettings :one
+-- Creates the single row (id 1) or replaces every column except id and
+-- created_at.
+INSERT INTO ai_settings (
+	id, provider, base_url, model, temperature, system_prompt, api_key_ct,
+	api_key_last4, key_verified_at, created_at, updated_at
+) VALUES (
+	1, ?, ?, ?, ?, ?, ?,
+	?, ?, ?, ?
+)
+ON CONFLICT (id) DO UPDATE SET
+	provider = excluded.provider,
+	base_url = excluded.base_url,
+	model = excluded.model,
+	temperature = excluded.temperature,
+	system_prompt = excluded.system_prompt,
+	api_key_ct = excluded.api_key_ct,
+	api_key_last4 = excluded.api_key_last4,
+	key_verified_at = excluded.key_verified_at,
+	updated_at = excluded.updated_at
+RETURNING *;
+
+-- name: GetRecipeSearchSettings :one
+-- Returns sql.ErrNoRows until the row exists.
+SELECT * FROM recipe_search_settings
+WHERE id = 1;
+
+-- name: UpsertRecipeSearchSettings :one
+-- Creates the single row (id 1) or replaces every column except id and
+-- created_at.
+INSERT INTO recipe_search_settings (
+	id, provider, base_url, api_key_ct, created_at, updated_at
+) VALUES (
+	1, ?, ?, ?, ?, ?
+)
+ON CONFLICT (id) DO UPDATE SET
+	provider = excluded.provider,
+	base_url = excluded.base_url,
+	api_key_ct = excluded.api_key_ct,
+	updated_at = excluded.updated_at
+RETURNING *;
+
+-- name: GetIntegrationsGoogle :one
+-- Returns sql.ErrNoRows until the row exists.
+SELECT * FROM integrations_google
+WHERE id = 1;
+
+-- name: UpsertIntegrationsGoogle :one
+-- Creates the single row (id 1) or replaces every column except id and
+-- created_at.
+INSERT INTO integrations_google (
+	id, refresh_token_ct, calendar_id, calendar_name, connected_at,
+	last_sync_error, created_at, updated_at
+) VALUES (
+	1, ?, ?, ?, ?,
+	?, ?, ?
+)
+ON CONFLICT (id) DO UPDATE SET
+	refresh_token_ct = excluded.refresh_token_ct,
+	calendar_id = excluded.calendar_id,
+	calendar_name = excluded.calendar_name,
+	connected_at = excluded.connected_at,
+	last_sync_error = excluded.last_sync_error,
+	updated_at = excluded.updated_at
+RETURNING *;
+
+-- name: GetChatSettings :one
+-- Returns sql.ErrNoRows until the row exists.
+SELECT * FROM chat_settings
+WHERE id = 1;
+
+-- name: UpsertChatSettings :one
+-- Creates the single row (id 1) or replaces every column except id and
+-- created_at.
+INSERT INTO chat_settings (
+	id, webhook_url_ct, space_label, last_test_at, last_test_status,
+	created_at, updated_at
+) VALUES (
+	1, ?, ?, ?, ?,
+	?, ?
+)
+ON CONFLICT (id) DO UPDATE SET
+	webhook_url_ct = excluded.webhook_url_ct,
+	space_label = excluded.space_label,
+	last_test_at = excluded.last_test_at,
+	last_test_status = excluded.last_test_status,
+	updated_at = excluded.updated_at
+RETURNING *;

sqlc.yaml

@@ -0,0 +1,15 @@
+# sqlc code generation. Regenerate with `make sqlc` (pinned sqlc version in the
+# Makefile); `make sqlc-check` fails when internal/db is stale. sqlc reads only
+# the top-level *.sql files of the schema directory, so migrations/queries/ is
+# not treated as schema.
+version: "2"
+sql:
+  - engine: sqlite
+    schema: migrations
+    queries: migrations/queries
+    gen:
+      go:
+        package: db
+        out: internal/db
+        sql_package: database/sql
+        emit_interface: true