Restrict dev secret permissions and state exact Node.js ranges in README

The quick start now sets the dev data directory to 700 and the secret key
file to 600, so other local users cannot read the key. The prerequisites
state the exact supported Node.js ranges from the lockfile's jsdom engine
(^22.22.2 || ^24.15.0 || >=26.0.0) instead of "22.22.2 or newer".

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

.gitea/workflows/ci.yml

@@ -1,11 +1,6 @@
 # Runs on every push and pull request (no branch filter) so feature branches,
 # their pull requests, and dev/main are all checked. Runs directly on the
 # self-hosted act_runner; no container: key.
-#
-# The Makefile is the single build definition: CI runs `make test` and
-# `make build` so it fails whenever either target would, and the binary
-# carries the Makefile's -trimpath and version-stamped -ldflags (the
-# `make build` log shows the -X main.version=... value).
 name: CI
 
 on: [push, pull_request]
@@ -21,12 +16,45 @@ jobs:
           go-version-file: go.mod
           cache: false
 
+      - name: gofmt
+        run: |
+          unformatted="$(gofmt -l .)"
+          if [ -n "$unformatted" ]; then
+            echo "gofmt needed:"
+            echo "$unformatted"
+            exit 1
+          fi
+
+      - name: go vet
+        run: go vet ./...
+
+      - name: go test
+        run: go test -race ./...
+
       - uses: actions/setup-node@v4
         with:
           node-version: "22"
 
-      - name: make test
-        run: make test
+      - name: npm ci
+        working-directory: web
+        run: npm ci
 
-      - name: make build
-        run: make build
+      - name: npm run typecheck
+        working-directory: web
+        run: npm run typecheck
+
+      - name: npm test
+        working-directory: web
+        run: npm test
+
+      - name: npm run build
+        working-directory: web
+        run: npm run build
+
+      # Embed the real PWA, as `make embed` does, then build the static binary.
+      - name: go build (embedded PWA)
+        run: |
+          rm -rf internal/frontend/dist
+          mkdir -p internal/frontend/dist
+          cp -R web/dist/. internal/frontend/dist/
+          CGO_ENABLED=0 go build -o bin/mealquest ./cmd/server

cmd/server/main.go

@@ -15,8 +15,6 @@ 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/server"
-	"code.cubecraftlabs.com/Maniacs_Mansion/Meal-Quest/internal/store"
-	"code.cubecraftlabs.com/Maniacs_Mansion/Meal-Quest/migrations"
 )
 
 // version is set at build time with -ldflags "-X main.version=<version>".
@@ -40,19 +38,6 @@ func run(cfg config.Config, logger *slog.Logger) error {
 	ctx, stop := signal.NotifyContext(context.Background(), os.Interrupt, syscall.SIGTERM)
 	defer stop()
 
-	db, err := store.Open(ctx, cfg.DataDir)
-	if err != nil {
-		return err
-	}
-	defer func() {
-		if err := db.Close(); err != nil {
-			logger.Error("close database", "error", err)
-		}
-	}()
-	if err := store.Migrate(ctx, db.Writer, migrations.FS); err != nil {
-		return err
-	}
-
 	router, err := server.NewRouter(logger, version, frontend.Assets())
 	if err != nil {
 		return err

go.mod

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

go.sum

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

internal/store/id.go

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

internal/store/id_test.go

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

internal/store/migrate.go

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

internal/store/migrate_test.go

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

internal/store/schema_test.go

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

internal/store/store.go

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

internal/store/store_test.go

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

internal/store/time.go

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

internal/store/time_test.go

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

internal/store/tx.go

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

internal/store/tx_test.go

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

migrations/0000_bootstrap.sql

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

migrations/0001_init.sql

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

migrations/embed.go

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