Embedded Work Planner and Python Learning Hub

BenchHub — Embedded Work Planner and Python Learning Hub: Product Specification #

Version: Final · 2026-10-21

BenchHub is a private, self-hosted web app for one person who does hardware circuit design and embedded software programming. It has two halves that share one calendar of time. The first half is a work planner: tasks with priorities, due dates and tags such as schematic, firmware or testing; a Today view that orders what to work on; and reminders shown inside the app. The second half is a beginner Python course: 42 short lessons written for someone who already thinks in C, with coding exercises that run in the browser, quizzes, and progress tracking with a streak. Learning sessions are placed automatically into the owner's spare-time windows and appear beside work tasks. An optional AI helper explains code in lessons and suggests a daily priority order.

The app works on desktop and phone browsers. It has exactly one account per installation, no team features and no payments.

This document is written to be executed cold by an AI coding agent or a developer. Every behavior, limit, data field, endpoint, screen and default is decided here. Where a choice could go more than one way, the chosen answer is stated as a decision. Each concern has one owning section, and other sections refer to it by number. Section 19 explains how to run the build; Section 18 gives the milestone order.

How to read this document: Sections 1–4 set the frame (choices you may change, goals, stack, conventions). Sections 5–6 are the data and API contracts. Sections 7–14 specify each feature and the interface. Sections 15–17 cover security, operations and testing. Sections 18–19 are the execution plan.

Table of Contents #

1. Before You Start
2. Project Overview & Vision
3. Technology Stack & Architecture
4. Conventions & Best Practices
5. Data Model & Database Schema
6. API Design
7. Account & Authentication
8. Work Task Planner
9. Today View & In-App Reminders
10. Python Curriculum & Lesson Content
11. In-Browser Exercises & Quizzes
12. Learning Progress, Streaks & Spare-Time Scheduler
13. AI Helper
14. User Interface, Responsive Layout & Accessibility
15. Security & Privacy
16. Configuration, Deployment & Operations
17. Testing Strategy & Quality Gates
18. Milestones & Execution Plan
19. Executor Instructions

1. Before You Start #

This section lists the choices an owner most often wants to change before BenchHub is built. Each row gives the default that gets built if nobody answers.

Rule: if nobody answers, build with the defaults. Nothing in this section blocks the build. The executor never waits for answers. If an answer arrives after the build has started, apply it where the "Where it is configured" column says and record the change in the repository's DECISIONS.md (Section 4.12).

There are three kinds of configuration:

• Environment variables are set at deploy time in .env and take effect on restart. Section 16.2 is the only complete list.
• Settings fields are columns of user_settings (Section 5). The owner can change them at any time in the app under /settings (Section 14). The defaults below are what a new account starts with.
• Built-in defaults and content live in code or content files and need a rebuild to change: seeded tags, default availability windows and the curriculum.

Fixed rules are not on this list on purpose. These include the password policy, session lifetimes, rate limits, the quiz pass mark, exercise timeouts and the streak definition. They are owned by Sections 7, 11, 12 and 15, and changing them is a product change, not a customization.

2. Project Overview & Vision #

2.1 Problem #

The owner designs circuits and writes embedded C for microcontrollers. Their workday mixes schematic reviews, PCB layout iterations, firmware changes, lab bring-up, testing, documentation and parts procurement. Their tasks are spread across sticky notes, chat messages and memory, so each morning starts with rebuilding a picture of what matters today.

They also want to learn Python, for test automation, parsing serial logs and sensor data, and scripting lab instruments. This plan keeps failing for three reasons:

1. Generic courses ignore what they already know. They explain "what is a variable" at length and never connect Python to C, registers, bit masks, byte buffers or units.
2. Spare time evaporates. Without a fixed slot, study happens "when there is time", which in practice means rarely. Evenings and weekends are also when they rest.
3. Practice needs setup. Installing Python, picking an editor and managing environments on a home phone or a locked-down work machine is enough friction to stop a beginner.

2.2 Vision #

BenchHub is one private place that answers two questions every day:

• "What should I work on today?" A Today view puts overdue, due-today, scheduled-today and in-progress work tasks in a sensible default order. The owner can pin their own order or ask the AI helper to suggest one. In-app reminders surface what is about to start.
• "When and what do I study next?" A beginner Python course written for an embedded C engineer is delivered in short lessons with exercises that run in the browser. An automatic scheduler places short learning sessions in the owner's free windows, and those sessions appear next to the day's work tasks so studying is planned, not hoped for.

The product runs on the owner's own server, holds data for exactly one person, sends no email and has no social features. Without an AI key it is fully useful; with one, the AI helper explains code and suggests a daily order.

2.3 Persona #

There is exactly one persona. The single account on an instance always belongs to this person, called "the owner" throughout this document.

2.4 Primary user journeys #

Each journey runs end to end. Endpoints are listed in Section 6. Screens and microcopy are defined in Section 14.

J1. First run: claim the instance and get a ready-made week

1. The owner deploys BenchHub with Docker Compose. In the server's .env they set APP_DOMAIN, POSTGRES_PASSWORD and SETUP_TOKEN; Compose derives APP_URL and DATABASE_URL from them (Section 16.2).
2. They open https://<APP_DOMAIN>. No account exists yet, so every page redirects to /setup (GET /api/v1/setup/status returns needsSetup: true).
3. They enter the setup token, email, display name and a password (12–128 characters, Section 7). The timezone field is pre-filled from the browser's IANA zone. They submit (POST /api/v1/setup). The server checks the setup token before it validates any other field (Section 7.4).
4. In one transaction the server creates the account, the default user_settings row, the 7 default tags and the default availability windows (Section 1, rows 9–10). After the commit it runs the scheduler, which places learning sessions in today plus the next 6 days according to the weekly target (default 4 per calendar week) and the default windows (Section 12).
5. The one-time recovery code (XXXXX-XXXXX-XXXXX-XXXXX) is shown once. The owner must confirm they saved it before continuing (Section 7).
6. They land on /today, which shows the planned learning sessions and an empty-state prompt to add the first task. From now on /setup returns 404.

J2. Morning at work: decide the day's order (desktop)

1. The owner opens /today (/ redirects there). The page shows open tasks that are overdue, due today, scheduled to start today or in progress, in the default order (Section 9.2). Today's learning sessions appear as separate "Learning" cards.
2. They reorder by dragging, or by using the keyboard "Move up"/"Move down" controls. The order is saved as the pinned order for today (PUT /api/v1/today/order, source: "manual").
3. Alternatively, when the AI helper is available, they choose Suggest my priorities. The AI helper returns a proposed order with one-line reasons (POST /api/v1/ai/priorities, Section 13.6). Nothing is saved until they choose Apply order, which saves the order with source: "ai"; Dismiss discards it. With AI off or not set up, the button is not shown.
4. Reset to smart order deletes the pin (DELETE /api/v1/today/order).
5. Completing a task from a Today card removes it from Today and dismisses its pending and fired reminders (Section 9.5).

J3. Capture and plan work (desktop or phone)

1. From /tasks or Today, the owner types a title and presses Enter. The task is created with status todo and priority 3 Normal (POST /api/v1/tasks).
2. They open the task (/tasks/[id]) and set tags (at most 5), a priority (1 Critical to 4 Low), a due date, a scheduled start, an estimate (5–960 minutes) and Markdown notes.
3. Once both a scheduled start and an estimate are set, the task occupies time. If it overlaps a planned automatic learning session (including the 5-minute buffer), the scheduler, requested after the task change commits, moves that session to another free slot (Section 12.6).
4. They add a reminder with a preset: "At scheduled start", "15 min before scheduled start", "09:00 on due date" or "Custom" (Section 9.5).
5. Later they search and filter the list by text, status, priority, tags and due-date range. They can also act on up to 100 selected tasks at once: complete, reopen, delete, restore, set priority, add tag, remove tag (POST /api/v1/tasks/bulk). Ids that cannot take the action are skipped one by one with a reason, and the rest are applied (Section 8.7).
6. Deleted tasks go to Trash. They can be restored, and they are purged automatically after TRASH_RETENTION_DAYS (default 30) by the hourly job in Section 3.6.

J4. A reminder fires during the day

1. While a BenchHub tab is visible, the client polls GET /api/v1/reminders/due every 60 s and immediately when the tab regains focus or becomes visible.
2. At or after remind_at, the server marks the reminder fired. A toast appears (at most 3 stacked) and the bell badge count goes up. With several tabs or devices open, the toast appears on exactly one of them, and every tab's bell shows the reminder within one poll interval (Section 9.5).
3. The owner chooses Open (go to the task or session), Snooze (10 min, 1 hour, or tomorrow 09:00 local; only a reminder that has fired can be snoozed) or Dismiss.
4. A reminder that is first delivered more than 24 h late appears in the bell list as "Missed" without a toast.

J5. Evening learning session (phone)

1. Ten minutes before a planned session (the default lead time), an automatic reminder fires in any open BenchHub tab. The session's card on Today shows the planned or recommended lesson.
2. The owner taps Start (POST /api/v1/learning-sessions/:id/start). They are taken to /learn/[slug]. Every mount of that page calls POST /api/v1/lessons/:slug/start, which sets started_at once and always updates last_viewed_at (Section 12.1). Because the lesson has Python blocks, the Python runtime starts loading as soon as the page mounts (Section 11.1.2).
3. They read the 10–20-minute lesson, which is full of C comparisons (for example, 0x0F & reg in C next to the same expression in Python).
4. They solve an exercise in the in-browser editor. Run executes their code in the Pyodide worker with a 5-second limit and shows the output; Check runs the tests and records the result (POST /api/v1/exercises/:exerciseId/attempts). They may reveal up to 3 hints one at a time, or view the reference solution (without a confirmation after 3 failed checks, with one before). The code is autosaved (Section 11.5).
5. If AI is enabled, they select a confusing line and choose Ask AI. The AI helper streams an explanation that never contains the full solution to the current exercise (Section 13). They can ask follow-up questions.
6. They take the lesson quiz (3–6 questions) and choose Submit answers; it is graded on the server and needs ≥ 70% to pass. When the last exercise is solved or the quiz is passed with everything else done, the lesson completes automatically (the response carries lessonCompletedNow: true, Section 12.1). Mark lesson complete exists only on lessons with no exercises and no quiz.
7. They tap Finish session (POST /api/v1/learning-sessions/:id/complete). Today counts as a learning day and the streak goes up (Section 12).

J6. Life happens: skip, miss and reschedule

1. A board bring-up runs late. The owner taps Skip on tonight's session (POST /api/v1/learning-sessions/:id/skip). The scheduler re-plans the rest of the 7-day horizon to keep the weekly target if the windows allow it.
2. On another day they never open the app. After the session's end time passes, the session becomes missed, either when Today, the schedule or the progress page is next read or within an hour through the background job (Section 3.6). A session that was started but abandoned is closed automatically 3 hours after its end time (Section 12.7). The next run of the scheduler fills the gap if capacity allows.
3. They change their availability (for example, adding Tuesday 06:30–07:15) under Settings → Learning → Available time, reached from Edit availability on /schedule. Times are picked in 15-minute steps. The scheduler immediately re-places future automatic sessions. Manual sessions, and sessions that were started, completed or skipped, are never moved.

J7. Weekly look back

1. On Sunday the owner opens /progress. They see the current and longest streak, a 12-week activity heatmap, lessons completed per module, exercises solved (with and without help), quiz best scores and sessions completed against the weekly target for the current calendar week (GET /api/v1/progress, Section 12.4).
2. "Recommended next" points to the first lesson in curriculum order that is not completed.

J8. Account safety and data ownership

1. The owner forgets their password. On /recover they enter their email, the recovery code and a new password (POST /api/v1/auth/recover). All existing sessions are revoked, they are signed in, and a new recovery code is shown once.
2. In Settings → Sessions they review signed-in devices and revoke a lost phone's session (DELETE /api/v1/me/sessions/:id). The current device's row offers Sign out instead, because revoking the current session through that endpoint is refused (Section 6.7.2).
3. In Settings → Data they download a JSON export of all their data (GET /api/v1/me/export, file benchhub-export-YYYY-MM-DD.json, Section 5.12). They can also delete the account and all data with password and typed confirmation (DELETE /api/v1/me, 204), which returns the instance to the setup state.
4. If the recovery code is lost too, the server operator resets the password from the command line (Section 7.8): in the Docker deployment docker compose exec -it app node dist/reset-password.mjs, in a source checkout pnpm account:reset-password.

2.5 Goals and success criteria #

Each goal has a measurable criterion. Section 17 defines how each one is verified. "Reference deployment" means the Docker Compose stack of Section 16 on a host with 2 vCPU, 4 GB RAM and SSD storage. "Full dataset" means 5,000 non-deleted tasks, 50 tags, 21 availability windows and 500 learning sessions.

2.6 Non-goals #

The following are out of scope. They must not be built, stubbed or prepared for (no hidden flags, no placeholder tables or endpoints):

1. Team or multi-user features, sharing and collaboration. One deployment serves one person with one account. There are no invitations, roles, shared tasks, comments or public links.
2. Native mobile or desktop apps. BenchHub is a responsive web app only.
3. Circuit design, schematic or PCB tools, firmware editors or simulators. Tags such as schematic and firmware only label work tasks. The product never edits or simulates hardware or firmware.
4. Movie, music or media playback, or media recommendations. The owner's interest in movies and listening is background only.
5. Lesson languages other than Python. The course teaches Python only. There are no C, Rust or MicroPython-on-device lessons.
6. Calendar sync with outside services. There is no Google Calendar, Outlook or iCal import or export, and no CalDAV.
7. Payments or subscriptions. The product handles no money.
8. Social login. There is no OAuth, SSO or "Sign in with …". Only email and password.
9. Email or push notifications of any kind. Reminders are in-app only. No email is ever sent, including for password reset (the recovery code replaces it).
10. Browser Notification API. No system notifications and no permission prompts.
11. Offline mode and PWA install. No service worker, no web app manifest install prompt, no offline data.
12. Internationalization. The UI is in English only. There is no translation framework. Dates and times use the account timezone with the formats in Section 14.

2.7 Feature map #

Each of the 11 product features has one owning section that defines it completely.

Cross-cutting concerns have their own owners. Data model: Section 5. API: Section 6. Security: Section 15. Configuration and operations: Section 16. Testing: Section 17.

2.8 Glossary #

3. Technology Stack & Architecture #

3.1 Architecture overview #

BenchHub is a single Next.js application running as one Node.js process. That process serves the UI, the HTTP API and the hourly background jobs. It talks to one PostgreSQL database and, only when AI is configured and enabled, to the Anthropic API. User Python code never runs on the server: it runs in the owner's browser inside a dedicated Web Worker that hosts Pyodide. Database backups run in a separate backup container next to the database (Section 16.7).

┌──────────────────────────── Browser (desktop or phone) ────────────────────────────────────┐
│                                                                                            │
│  Next.js client (React)                             Python runner (dedicated Web Worker)   │
│  ├─ RSC-rendered pages + client components          ├─ Pyodide (CPython on WebAssembly)    │
│  ├─ TanStack Query → fetch /api/v1/**  ◄─postMessage─►├─ assets from /pyodide/<version>/   │
│  ├─ reminder poll: GET /api/v1/reminders/due (60 s) ├─ run time limit → terminate+respawn  │
│  └─ SSE reader for POST /api/v1/ai/explain          └─ 64 KB output cap                    │
│                                                                                            │
└───────────────────────────────────────┬────────────────────────────────────────────────────┘
                                        │ HTTPS (TLS terminated by Caddy, reverse proxy)
┌───────────────────────────────────────▼──── Next.js server: ONE Node.js process ───────────┐
│  src/proxy.ts ─ request id, CSP nonce, security headers, Sec-Fetch-Site + Origin check,    │
│                 Content-Length pre-check                                                   │
│        │                                                                                   │
│  ┌─────▼──────────────────┐    ┌──────────────────────────────┐   ┌──────────────────┐     │
│  │ RSC pages (initial     │    │ Route handlers /api/v1/**    │   │ In-process jobs  │     │
│  │ reads; every page      │    │ via defineRoute(): limits →  │   │ (hourly, guarded │     │
│  │ calls requireUser())   │    │ setup gate → session → Zod   │   │ by advisory lock)│     │
│  └─────┬──────────────────┘    └──────────────┬───────────────┘   └───────┬──────────┘     │
│        └──────────────► Services: src/server/services/<domain>.ts ◄───────┘                │
│                          │                         │                                       │
│                    Drizzle ORM + pg pool      AiProvider interface ─ Anthropic adapter ────┼──► Anthropic API
│                          │                                                                 │    (HTTPS, server-side only)
└──────────────────────────┼─────────────────────────────────────────────────────────────────┘
                           ▼
              PostgreSQL (`db` container, named volume; extensions citext, pg_trgm)
                           ▲
              `backup` container: daily pg_dump to the host (Section 16.7)

Versions of every component in the diagram: Section 3.2.

Data leaving the instance: only AI requests to the Anthropic API, server-side, when ANTHROPIC_API_KEY is set and user_settings.ai_enabled is true. Section 13 defines exactly which fields are sent.

3.2 Versions (the only version table) #

Every dependency is listed here once, by major line. No other section restates a version; they say "see Section 3.2".

These version lines are a known-good floor, not a lockfile. At build time, install the current stable release of each dependency (npm install <pkg>@latest, or your ecosystem's equivalent), confirm the major line still matches, and let the lockfile record the exact resolved versions.

Decision: when a major line does not match. If, at build time, the current stable release of a package is on a newer major line than listed, install the newest release of the listed line. Move to the newer major only when the listed line is deprecated or incompatible with another listed dependency, and record the move in DECISIONS.md (Section 4.12). Pre-1.0 packages (0.x) follow the same rule at the minor level stated (for example, Drizzle ORM 0.45.x), except that a stable 1.x of Drizzle ORM is used if it exists. TypeScript has its own fallback rule in the table.

Runtime, platform and infrastructure

Application (runtime dependencies)

Build-time content pipeline (dev dependencies)

Quality tooling (dev dependencies; how they are used is in Section 17)

No other dependency may be added without a DECISIONS.md entry giving its purpose and why an existing dependency is not enough.

3.3 Key architectural decisions #

3.4 Repository layout #

This is the complete layout. Every file the executor creates goes into one of these folders, and every path named anywhere in this specification appears here. A new top-level folder needs a DECISIONS.md entry. Route handler files exist for exactly the endpoints in Section 6, plus the catch-all that returns the JSON 404 for unknown /api/v1 paths. Static segments (tasks/trash/empty, tasks/bulk, reminders/due, learning-sessions/reschedule) sit next to dynamic [id] segments, and Next.js gives static segments priority, so there are no conflicts.

benchhub/
├── .github/workflows/ci.yml          # CI pipeline: stages, Python matrix 3.12 + 3.14, gates (Section 17.13)
├── content/
│   └── curriculum/
│       ├── modules.yaml              # module + lesson order (Section 10)
│       ├── content.lock.json         # id immutability lock; created when all 42 lessons exist (Section 10.7.2)
│       └── lessons/<slug>.md         # one lesson per file: frontmatter + body + exercise/quiz blocks (Section 10)
├── ops/                              # deployment files (Section 16.4)
│   ├── docker/entrypoint.sh          # runs node dist/migrate.mjs, then exec node server.js (Section 16.6)
│   ├── caddy/Caddyfile               # reverse proxy + automatic HTTPS for APP_DOMAIN (Section 16.4)
│   ├── backup/benchhub-backup.sh     # backup service script: daily pg_dump, retention, restore check (Section 16.7)
│   └── backup/offsite.sh             # host cron job: age-encrypt the newest dump for offsite storage (Section 16.7.5)
├── drizzle/                          # generated SQL migrations + drizzle-kit meta; never edited by hand after being applied
├── public/
│   ├── pyodide/<version>/            # GITIGNORED; copied from node_modules/pyodide at build (Sections 3.7, 11.1)
│   └── favicon.svg, icon-*.png       # static icons
├── scripts/                          # run with tsx (Section 3.2)
│   ├── build-content.ts              # validates + verifies content, renders HTML, writes src/generated/* (Sections 3.7, 10.2)
│   ├── content-harness.py            # thin CPython driver around src/workers/harness.py for the content build (Section 10.2.6)
│   ├── copy-pyodide.ts               # copies the Section 11.1 files into public/pyodide/<version>/, writes python-runtime.json
│   ├── check-client-bundle.ts        # fails the build if server-only content leaks into .next/static (Section 3.7)
│   ├── check-bundle-budget.ts        # per-route JavaScript budget check (Section 17.11)
│   ├── check-test-guards.ts          # no focused/skipped tests; ignore-comment budget (Section 17.3)
│   ├── test-db.ts                    # recreates and migrates the test database (Section 17.5)
│   ├── migrate.ts                    # runMigrations() + MIGRATION_LOCK_ID 727001; bundled to dist/migrate.mjs and run by
│   │                                 # ops/docker/entrypoint.sh (Section 16.6); `pnpm db:migrate` in development
│   ├── reset-password.ts             # CLI password reset; bundled to dist/reset-password.mjs (Section 7.8)
│   └── dev-reset.ts                  # wipes the local dev database back to the setup state (Section 16.3.4)
├── dist/                             # GITIGNORED; migrate.mjs and reset-password.mjs from `pnpm build:scripts`
├── src/
│   ├── proxy.ts                      # Next.js proxy (the file formerly named "middleware"): request id, CSP nonce, headers,
│   │                                 # Sec-Fetch-Site + Origin checks (Sections 3.5, 15.5, 15.6)
│   ├── instrumentation.ts            # register(): validate env, log startup, start jobs (once per process; never migrates)
│   ├── app/
│   │   ├── layout.tsx                # root HTML shell: theme attribute on <html> from user_settings.theme (no theme script),
│   │   │                             # fonts, skip link
│   │   ├── globals.css               # Tailwind entry + design tokens as CSS variables (Section 14)
│   │   ├── not-found.tsx, error.tsx, global-error.tsx  # 404 and error boundaries
│   │   ├── page.tsx                  # "/" → redirect to /today (or /setup, /login)
│   │   ├── (auth)/                   # unauthenticated screens, centered card layout, theme always "system"
│   │   │   ├── layout.tsx
│   │   │   ├── setup/page.tsx        # account creation; 404 once an account exists
│   │   │   ├── login/page.tsx
│   │   │   └── recover/page.tsx
│   │   ├── (app)/                    # authenticated shell: sidebar ≥1024 px, top bar + bottom tabs <1024 px
│   │   │   ├── layout.tsx            # requireUser() for the redirect UX; reminder poller; toasts; bell; session banner
│   │   │   ├── today/page.tsx        # every page.tsx below also calls requireUser() itself (Section 3.5)
│   │   │   ├── tasks/page.tsx
│   │   │   ├── tasks/[id]/page.tsx
│   │   │   ├── learn/page.tsx
│   │   │   ├── learn/[slug]/page.tsx
│   │   │   ├── schedule/page.tsx
│   │   │   ├── progress/page.tsx
│   │   │   └── settings/page.tsx     # tabs: Account, Preferences, Learning, AI, Sessions, Data
│   │   └── api/
│   │       ├── health/route.ts       # GET /api/health (unauthenticated, outside v1)
│   │       └── v1/
│   │           ├── [...path]/route.ts  # JSON 404 NOT_FOUND for unknown /api/v1 paths, every method (Section 6.1)
│   │           ├── setup/route.ts, setup/status/route.ts
│   │           ├── auth/login/route.ts, auth/logout/route.ts, auth/recover/route.ts
│   │           ├── me/route.ts, me/password/route.ts, me/recovery-code/route.ts, me/export/route.ts
│   │           ├── me/sessions/route.ts, me/sessions/[id]/route.ts
│   │           ├── settings/route.ts
│   │           ├── tags/route.ts, tags/[id]/route.ts
│   │           ├── tasks/route.ts, tasks/bulk/route.ts, tasks/trash/empty/route.ts
│   │           ├── tasks/[id]/route.ts, tasks/[id]/restore/route.ts, tasks/[id]/permanent/route.ts
│   │           ├── today/route.ts, today/order/route.ts
│   │           ├── reminders/route.ts, reminders/due/route.ts, reminders/[id]/route.ts
│   │           ├── reminders/[id]/snooze/route.ts, reminders/[id]/dismiss/route.ts
│   │           ├── curriculum/route.ts, progress/route.ts
│   │           ├── lessons/[slug]/route.ts, lessons/[slug]/start/route.ts, lessons/[slug]/complete/route.ts
│   │           ├── exercises/[exerciseId]/draft/route.ts, exercises/[exerciseId]/attempts/route.ts
│   │           ├── exercises/[exerciseId]/solution/route.ts
│   │           ├── quizzes/[quizId]/attempts/route.ts
│   │           ├── availability/route.ts
│   │           ├── learning-sessions/route.ts, learning-sessions/reschedule/route.ts
│   │           ├── learning-sessions/[id]/route.ts, learning-sessions/[id]/start/route.ts
│   │           ├── learning-sessions/[id]/complete/route.ts, learning-sessions/[id]/skip/route.ts
│   │           └── ai/status/route.ts, ai/explain/route.ts, ai/priorities/route.ts
│   ├── server/                       # SERVER-ONLY: every module starts with `import "server-only"` (exceptions: Section 4.3)
│   │   ├── env-schema.ts             # pure Zod env schema, parseEnv(), loadEnv(), warnings (Section 16.2.4); no server-only
│   │   ├── env.ts                    # getEnv(): the validated, frozen env object for application code (variables: Section 16.2)
│   │   ├── logger.ts                 # createLogger() + root logger with redaction config (Section 4.7); no server-only
│   │   ├── db/
│   │   │   ├── schema.ts             # Drizzle schema: every table, enum, index (Section 5)
│   │   │   └── client.ts             # pg Pool (timezone=UTC) + drizzle instance + `Db` / `Tx` types
│   │   ├── http/
│   │   │   ├── route.ts              # defineRoute(): the route-handler wrapper (Section 3.5)
│   │   │   ├── envelope.ts           # ok(), list(), noContent() response helpers (shapes: Section 6)
│   │   │   ├── errors.ts             # AppError, ErrorCode, HTTP status map, helpers (Section 4.6)
│   │   │   ├── cursor.ts             # encode/decode opaque base64url cursors (Section 6)
│   │   │   └── sse.ts                # Server-Sent Events stream helper for AI explain (Section 6.8)
│   │   ├── security/                 # Section 15
│   │   │   ├── headers.ts            # SECURITY_HEADERS + CSP builders, imported by next.config.ts and src/proxy.ts; no server-only
│   │   │   ├── origin.ts             # checkRequestOrigin(): Sec-Fetch-Site + Origin rules (Section 15.6); no server-only
│   │   │   ├── rate-limit.ts         # in-memory token-bucket limiter and RULES (Section 15.8.3)
│   │   │   ├── redact.ts             # REDACT_PATHS for pino (Section 15.16)
│   │   │   └── client-ip.ts          # clientIp() and ipPrefix() (/24, /48), honoring TRUST_PROXY (Section 15.8.4)
│   │   ├── auth/
│   │   │   ├── password.ts           # Argon2id hash/verify, password policy checks
│   │   │   ├── common-passwords.txt  # bundled list of the 10,000 most common passwords
│   │   │   ├── recovery-code.ts      # generate/format/normalize/hash recovery codes
│   │   │   ├── session.ts            # create/validate/touch/revoke sessions, cookie options
│   │   │   ├── lockout.ts            # failed-login counting and lock windows
│   │   │   └── current-user.ts       # getCurrentUser()/requireUser() for RSC pages, memoized per request with React cache
│   │   ├── services/                 # one file per domain; all business logic (Section 4.4)
│   │   │   ├── context.ts            # ServiceContext { db, clock, log } + defaultContext
│   │   │   ├── setup.ts, setup-defaults.ts   # account creation; DEFAULT_TAGS, DEFAULT_AVAILABILITY_WINDOWS
│   │   │   ├── account.ts            # profile, password change, recovery code, sessions list, delete account
│   │   │   ├── export.ts             # full JSON export (format: Section 5.12)
│   │   │   ├── settings.ts
│   │   │   ├── tags.ts
│   │   │   ├── tasks.ts              # CRUD, list/search/filter, bulk, trash
│   │   │   ├── today.ts              # Today composition, default order, pinned order (Section 9)
│   │   │   ├── reminders.ts          # reminder lifecycle, presets, due delivery (Section 9)
│   │   │   ├── curriculum.ts         # outline + progress merge, recommended next
│   │   │   ├── lessons.ts            # lesson read/start/complete
│   │   │   ├── exercises.ts          # code autosave, attempts, solution reveal
│   │   │   ├── quizzes.ts            # server-side grading
│   │   │   ├── progress.ts           # streaks and progress summary (Section 12)
│   │   │   ├── activity.ts           # activity_days upserts (Section 12.2)
│   │   │   ├── streak-core.ts        # pure streak math (Section 12.3)
│   │   │   ├── learning-constants.ts # shared learning constants (Section 12)
│   │   │   ├── availability.ts
│   │   │   ├── learning-sessions.ts  # session CRUD, state transitions, status refresh (Section 12.7)
│   │   │   ├── scheduler.ts          # requestRun(userId, reason), per-user advisory lock, persistence (Section 12.6)
│   │   │   └── scheduler-core.ts     # pure placement algorithm (Section 12.6)
│   │   ├── ai/
│   │   │   ├── provider.ts           # AiProvider interface + types
│   │   │   ├── anthropic.ts          # Anthropic adapter (@anthropic-ai/sdk)
│   │   │   ├── fake.ts               # deterministic fake provider used by unit and integration tests
│   │   │   ├── prompts.ts            # system prompts and prompt builders (Section 13)
│   │   │   ├── context.ts            # message assembly for explain/follow-up (Section 13.5.4)
│   │   │   ├── limits.ts             # daily cap via ai_usage
│   │   │   └── ai-service.ts         # explain/follow-up/suggest orchestration + output validation
│   │   ├── content/
│   │   │   ├── curriculum.ts         # loads src/generated/curriculum.json; lookup by slug/exercise id
│   │   │   └── answer-keys.ts        # loads src/generated/quiz-keys.json (server-only)
│   │   └── jobs/                     # the only code allowed to act across users (Section 3.6)
│   │       ├── runner.ts             # startJobs(): hourly setTimeout chain + advisory lock
│   │       ├── refresh-session-statuses.ts
│   │       ├── purge-trash.ts
│   │       ├── prune-login-events.ts
│   │       ├── prune-sessions.ts
│   │       ├── prune-ai-usage.ts
│   │       ├── prune-dismissed-reminders.ts
│   │       └── prune-daily-plans.ts
│   ├── lib/                          # ISOMORPHIC: safe in browser and server; no Node APIs, no DB
│   │   ├── schemas/                  # Zod schemas per domain + common primitives (Section 4.5)
│   │   │   ├── enums.ts              # every enum value tuple (TASK_STATUS_VALUES, …), single source (Sections 4.1, 5.6)
│   │   │   ├── common.ts, auth.ts, account.ts, settings.ts, tags.ts, tasks.ts, today.ts, reminders.ts
│   │   │   └── curriculum.ts, exercises.ts, quizzes.ts, progress.ts, availability.ts, learning-sessions.ts, ai.ts, content.ts
│   │   ├── time/                     # ALL date/time logic (Section 4.8)
│   │   │   ├── types.ts, clock.ts, zone.ts, local-date.ts, convert.ts, week.ts, format.ts, index.ts
│   │   │   └── windows.ts            # availability-window overlap checks, shared by client and server (Section 12.5)
│   │   ├── api-client/
│   │   │   ├── fetch.ts              # apiFetch(): JSON, credentials, envelope unwrap, ApiError
│   │   │   └── query-keys.ts         # TanStack Query key factory
│   │   ├── python/                   # main-thread side of the Python runner (Section 11)
│   │   │   ├── protocol.ts           # message types shared by worker and client
│   │   │   ├── runtime.ts            # pythonRuntime controller: load, run, timeout, terminate, respawn
│   │   │   ├── attempt-queue.ts      # queued attempt submissions with retry (Section 11.8)
│   │   │   └── friendly-errors.ts    # Python error → plain-language explanation
│   │   ├── markdown/
│   │   │   ├── render-notes.tsx      # renderNotes() for task notes and AI answers, loaded lazily (Section 15.7)
│   │   │   └── notes-schema.ts       # rehype-sanitize allow-list (Section 15.7.2)
│   │   ├── ai/code-window.ts         # code window sent with explain requests (Section 13.5.3)
│   │   ├── tasks/default-order.ts    # pure default-order comparator (Section 9.2)
│   │   ├── tasks/due-label.ts        # due-date labels (Section 8)
│   │   ├── safe-next.ts              # safeNext(): post-login redirect validation (Section 7.5)
│   │   ├── quiz/normalize-output.ts  # normalizeOutput() shared by quiz grader, client and content build (Section 11.7.4)
│   │   ├── today/capacity.ts         # Today capacity summary (Section 9)
│   │   ├── reminders/presets.ts      # reminder preset → remind_at computation (Section 9.5)
│   │   ├── quick-add.ts              # quick-add parsing (Section 8)
│   │   ├── user-agent.ts             # user agent → "Safari on iOS" device label (Section 7.9)
│   │   ├── sse.ts                    # client SSE parser for AI explain (Section 6.8)
│   │   ├── browser-storage.ts        # the storage keys of Section 15.14.2 + clearing them at sign-out
│   │   ├── copy.ts                   # every user-visible string (Section 14.7)
│   │   ├── constants.ts              # product limits (max tasks, tags, windows, …) shared by client and server
│   │   └── utils.ts                  # tiny pure helpers (cn() class merge, assertNever)
│   ├── components/
│   │   ├── ui/                       # design-system wrappers over Radix (Button, Dialog, Field, Toast, …)
│   │   ├── layout/                   # AppShell, Sidebar, TopBar, BottomTabs, SkipLink, SessionBanner
│   │   ├── code-editor/              # CodeMirror wrapper and extensions.ts (Section 11.5)
│   │   ├── tasks/, today/, reminders/, learn/, exercise/, quiz/, schedule/, progress/, settings/, auth/, ai/
│   │   └── providers.tsx             # QueryClientProvider, toast provider
│   ├── hooks/                        # client hooks: use-reminder-poll.ts, use-today-reorder.ts (dnd-kit), use-visibility.ts, …
│   ├── workers/
│   │   ├── python-runner.worker.ts   # dedicated Web Worker hosting Pyodide (Section 11)
│   │   └── harness.py                # the one grading harness, used by the worker and by the content build (Section 11.4.1)
│   └── generated/                    # GITIGNORED; written by the build (Section 3.7)
│       ├── curriculum.json           # client-safe: outline, lesson blocks, exercises (prompt, starter, hints, tests), quizzes without answers
│       ├── quiz-keys.json            # server-only: quiz answer keys + explanations, exercise reference solutions
│       ├── code-theme.css            # highlighter class rules mapped to the code tokens of Section 14.2
│       └── python-runtime.json       # Pyodide version, indexURL, harness source; bundled into the worker (Section 11.1)
├── tests/                            # structure refined in Section 17.2
│   ├── unit/                         # pure logic (time, ordering, scheduler, streaks, grading, schemas); unit/components/ for components
│   ├── content/                      # content suite against the real lesson files (Section 17.7)
│   ├── integration/                  # services against the test database of the Compose `db` service (Section 17.5)
│   ├── contract/                     # HTTP tests against the production build (Section 17.6)
│   ├── e2e/                          # Playwright: journeys/ (J1–J12), worker/, a11y/, fakes/anthropic-server.ts
│   ├── perf/                         # Lighthouse CI config (Section 17.11)
│   ├── factories/                    # test data factories (Section 17.14)
│   ├── support/                      # prng, clock helpers, db reset, network guard
│   └── fixtures/content/             # deliberately broken lesson files for build-script tests
├── .env.example                      # local development variables (Section 16.2.5)
├── .env.test                         # committed test DB connection settings, no secrets (Section 17.5)
├── deploy.env.example                # production .env template (Section 16.2)
├── .gitignore, .dockerignore
├── Dockerfile                        # multi-stage build with `pnpm build:image` → standalone runtime image (Section 16.4)
├── docker-compose.yml                # services: app, db (postgres), caddy, backup (Section 16.4)
├── docker-compose.dev.yml            # local development database (Section 16.3.5)
├── docker-compose.ci.yml             # CI override for the container smoke test (Section 16.4)
├── drizzle.config.ts                 # drizzle-kit config (schema path, out: ./drizzle, dialect postgresql)
├── next.config.ts                    # output "standalone", typedRoutes, security headers (Section 15.5), /pyodide cache headers (Section 11.1)
├── postcss.config.mjs                # @tailwindcss/postcss
├── eslint.config.mjs                 # flat config incl. the restrictions in Section 4
├── prettier.config.mjs
├── vitest.config.ts, playwright.config.ts
├── tsconfig.json                     # strict settings (Section 4.1), path alias @/* → src/*
├── package.json, pnpm-lock.yaml      # scripts: Section 3.7
├── DECISIONS.md                      # executor's record of decisions and deviations (format: Section 19.4)
└── README.md                         # setup, development, deployment, backup/restore summary (Section 16)

3.5 Request lifecycle #

This is the one canonical pipeline order for every /api/v1 request; Sections 6.1.1 and 15.8.3 cite it. Stages 1–4 run in src/proxy.ts; stages 5–15 run in the defineRoute() wrapper (src/server/http/route.ts). Route files contain only a defineRoute(...) call per exported HTTP method. The first failing stage ends the request.

GET /api/health is outside /api/v1 and skips stages 3–14.

Pages follow a shorter path: proxy stages 1–2, then the page. Every page.tsx under (app) calls const user = await requireUser() itself; the call in (app)/layout.tsx exists only for the redirect experience, because layouts are not re-run on client-side navigation between sibling pages. requireUser() lives in src/server/auth/current-user.ts, is memoized per request with React cache, redirects to /setup when no account exists and to /login?next=… when the session is invalid. The page then calls services directly with user.id.

// src/server/http/route.ts (shape; implementation details are the executor's)
import "server-only";
import type { NextRequest } from "next/server";
import type { z } from "zod";

export type RateLimitBucket = "login" | "recover" | "setup" | "anon" | "api" | "ai";

export interface RouteConfig<P extends z.ZodType, Q extends z.ZodType, B extends z.ZodType> {
  name: string;                       // stable operation name for logs, e.g. "tasks.update"
  auth: "required" | "public";
  rateLimit?: "login" | "recover" | "setup" | "ai"; // "anon" and "api" are applied automatically (stages 5, 7, 8)
  dailyHook?: true;                   // stage 9; only GET /today, GET /learning-sessions, GET /progress
  bodyLimitBytes?: 32_768 | 65_536;   // default 32_768
  beforeValidate?: (rawBody: unknown) => void; // stage 12; only POST /setup (throws AppError FORBIDDEN)
  params?: P;
  query?: Q;
  body?: B;
}

export interface RouteContext<P, Q, B> {
  req: NextRequest;
  requestId: string;
  userId: string;                     // "" for public routes; never read in public handlers
  sessionId: string;                  // "" for public routes
  ipPrefix: string;
  params: P;
  query: Q;
  body: B;
  log: import("pino").Logger;         // child logger bound to reqId, route name, userId
}

export function defineRoute<P extends z.ZodType, Q extends z.ZodType, B extends z.ZodType>(
  config: RouteConfig<P, Q, B>,
  handler: (ctx: RouteContext<z.infer<P>, z.infer<Q>, z.infer<B>>) => Promise<Response>,
): (req: NextRequest, segment: { params: Promise<Record<string, string>> }) => Promise<Response>;

// src/app/api/v1/tasks/[id]/route.ts: a complete route file
import { defineRoute } from "@/server/http/route";
import { ok } from "@/server/http/envelope";
import { taskIdParams, updateTaskBody } from "@/lib/schemas/tasks";
import * as tasks from "@/server/services/tasks";

export const PATCH = defineRoute(
  { name: "tasks.update", auth: "required", params: taskIdParams, body: updateTaskBody },
  async ({ userId, params, body }) => ok(await tasks.updateTask(userId, params.id, body)),
);

3.6 In-process background jobs #

Seven maintenance jobs run inside the app process. This runner is the only background-job mechanism: no separate worker, OS cron or queue exists (AD-3), and every retention purge defined in Section 5.10 runs here.

Jobs are the only code allowed to act across all users by predicate instead of taking a userId (Section 4.4). With one account the effect is the same, but the exception is explicit and limited to src/server/jobs/. When no users row exists (setup state, or after DELETE /api/v1/me), the per-user jobs 1 and 7 do nothing and log rows: 0.

Start on boot. src/instrumentation.ts exports register(). Next.js calls it once per server process before serving requests. It validates the environment, logs the startup line (both defined in Section 16.2.4) and then starts the job runner:

// src/instrumentation.ts
export async function register(): Promise<void> {
  if (process.env.NEXT_RUNTIME !== "nodejs") return;
  if (process.env.NEXT_PHASE === "phase-production-build") return;
  const { loadEnv } = await import("./server/env-schema");
  const env = loadEnv();                                        // exits the process on invalid configuration
  // config warnings + "BenchHub starting" log line: Section 16.2.4
  const { startJobs } = await import("./server/jobs/runner");
  startJobs({ trashRetentionDays: env.TRASH_RETENTION_DAYS });
}

Migrations are not run here. They are applied by the container entrypoint (ops/docker/entrypoint.sh runs node dist/migrate.mjs, advisory lock key 727001) before the server starts (Section 16.6), and by pnpm db:migrate in development. register() never migrates.

Runner rules (src/server/jobs/runner.ts).

1. Once per process. startJobs() sets globalThis.__benchhubJobsStarted = true and returns immediately if it is already set. This guards against development hot reload calling register() again.
2. Timing. The first tick runs 60 s after boot, so startup finishes first. Each later tick is scheduled with setTimeout(tick, 3_600_000) after the previous tick finishes, so ticks can never overlap within the process. Timers are unref()'d so they never keep the process alive during shutdown.
3. Cross-process exclusion with an advisory lock. Normally one process runs. During a container replacement, an old and a new process can briefly overlap. Each tick therefore checks out a dedicated pool client and runs SELECT pg_try_advisory_lock(hashtext('benchhub:jobs')). If the result is false, another process is running the jobs: log jobs.skipped_locked at debug and return. Otherwise run the seven jobs sequentially in the order of the table above, each in its own transaction, then SELECT pg_advisory_unlock(hashtext('benchhub:jobs')) in a finally block and release the client. Because the lock is session-level, a crashed process's connection drops and the lock releases automatically. The scheduler runs that job 1 requests take the scheduler's own per-user lock (Section 12.6.10).
4. Failure isolation. Each job is wrapped in try/catch. A failure is logged at error with job and err (Section 4.7), and the remaining jobs still run. The next tick runs normally. A job never crashes the process.
5. Logging. Each job logs one info line: { job, rows, durationMs }.
6. Shutdown. On SIGTERM the runner clears its pending timer. A tick in progress is allowed to finish, and Docker's default 10-second stop grace period is enough for these statements on the full dataset.
7. Testability. Each job module exports run(db, now: Date, opts), which returns the affected row count. Integration tests call it directly with a fixed now.

3.7 Build pipeline #

pnpm build runs these steps in order. Any failing step fails the build. pnpm build:image, which the Dockerfile runs (Section 16.4), is identical except that step 1 passes --no-verify, because the image has no Python and CI has already verified the same files.

Details:

1. Content build internals. Parse modules.yaml and each lesson's frontmatter with yaml. Validate both with Zod schemas from src/lib/schemas/content.ts. Split exercise, quiz and runnable blocks out of the body (format: Section 10.2). Render the body with unified → remark-parse → remark-gfm → remark-rehype → rehype-sanitize (allow-list of Section 10.3.3) → Shiki highlighting with the CSS-variables theme and transformerStyleToClass, which emits classes, never style attributes → rehype-slug → rehype-stringify. The exact order and the post-highlighting assertions are owned by Section 10.3.3. Emit JSON in stable key order with 2-space indent, so diffs and builds are reproducible. Answer keys, quiz explanations and reference solutions go only into quiz-keys.json.
2. Content verification is part of the content build. Unless --no-verify is passed, step 1 runs every reference solution and every runnable block under CPython (python3 3.12 or newer on PATH) through scripts/content-harness.py, a thin driver around the one grading harness src/workers/harness.py (Sections 10.2.6, 11.4.1). Separately, pnpm content:test runs the content test suite, and the Playwright worker project runs every reference solution in the real Pyodide worker (Section 17.7). CI runs the content build and pnpm content:test under both CPython 3.12 and 3.14 (Section 17.13). There is no separate content verification script.
3. Who reads the generated files. Only src/server/content/curriculum.ts imports curriculum.json, and only src/server/content/answer-keys.ts imports quiz-keys.json. Both modules import server-only. Client components receive lesson data through RSC props or GET /api/v1/lessons/:slug. The only generated files client code imports are python-runtime.json (bundled into the worker, Section 11.1.1) and code-theme.css (lesson styles). The client never imports the lesson JSON directly, which keeps the 42 lessons out of the JavaScript bundle.
4. No server-only data in rendered pages either. The client bundle check covers JavaScript chunks only. A contract test (Section 17.6) additionally asserts, for every lesson, that the body of GET /api/v1/lessons/:slug and the rendered /learn/[slug] HTML (including the RSC payload) contain neither the sentinel __BENCHHUB_SERVER_ONLY__ nor any reference-solution or quiz-explanation string from quiz-keys.json.
5. Generated files are gitignored (src/generated/, public/pyodide/, dist/). The predev script runs steps 1 and 2 before next dev. After editing content during development, run pnpm content:build again; the dev server picks up the changed JSON.
6. Static asset caching. /pyodide/* is served from the versioned path /pyodide/<version>/ with Cache-Control: public, max-age=31536000, immutable (Sections 11.1.1, 15.5.4); upgrading Pyodide changes every URL. Next.js's own hashed assets under /_next/static/ keep their default immutable caching.
7. Standalone packaging. The Docker build stage (Section 16.4) runs pnpm install --frozen-lockfile and pnpm build:image. It then copies .next/standalone/ plus .next/static/ (to .next/standalone/.next/static/), public/ (to .next/standalone/public/), drizzle/ and dist/ into the runtime image. The image starts with ops/docker/entrypoint.sh, which runs node dist/migrate.mjs and then exec node server.js (Section 16.6).

package.json scripts. This is the only complete list; Sections 16.3.3 and 17.3 explain the scripts they use and refer to these names. Every TypeScript script runs with tsx.

{
  "scripts": {
    "predev": "pnpm content:build && pnpm pyodide:copy",
    "dev": "next dev",
    "build": "pnpm content:build && pnpm pyodide:copy && next build && tsx scripts/check-client-bundle.ts && pnpm build:scripts",
    "build:image": "pnpm content:build --no-verify && pnpm pyodide:copy && next build && tsx scripts/check-client-bundle.ts && pnpm build:scripts",
    "build:scripts": "esbuild scripts/migrate.ts scripts/reset-password.ts --bundle --platform=node --target=node24 --format=esm --outdir=dist --out-extension:.js=.mjs --external:@node-rs/argon2 --external:pg-native --banner:js=\"import { createRequire } from 'node:module'; const require = createRequire(import.meta.url);\"",
    "start": "node .next/standalone/server.js",
    "content:build": "tsx scripts/build-content.ts",
    "content:test": "vitest run --project content",
    "pyodide:copy": "tsx scripts/copy-pyodide.ts",
    "db:up": "docker compose -f docker-compose.dev.yml up -d --wait db",
    "db:down": "docker compose -f docker-compose.dev.yml down",
    "db:generate": "drizzle-kit generate",
    "db:migrate": "tsx scripts/migrate.ts",
    "dev:reset": "tsx scripts/dev-reset.ts",
    "account:reset-password": "tsx scripts/reset-password.ts",
    "lint": "eslint . --max-warnings 0",
    "format": "prettier --write .",
    "format:check": "prettier --check .",
    "typecheck": "tsc --noEmit",
    "test:guard": "tsx scripts/check-test-guards.ts",
    "test:unit": "vitest run --project unit --project component",
    "test:db:prepare": "tsx scripts/test-db.ts",
    "test:integration": "vitest run --project integration",
    "test:coverage": "vitest run --project unit --project component --project integration --coverage",
    "test:contract": "vitest run --project contract",
    "test:e2e": "playwright test --project chromium-desktop --project chromium-mobile --project firefox-desktop --project firefox-mobile --project webkit-desktop --project webkit-mobile --project worker-chromium --project worker-firefox",
    "test:a11y": "playwright test --project a11y",
    "test:bundle": "tsx scripts/check-bundle-budget.ts",
    "test:perf": "lhci autorun --config=tests/perf/lighthouserc.cjs",
    "verify": "pnpm test:guard && pnpm lint && pnpm format:check && pnpm typecheck && pnpm content:build && pnpm content:test && pnpm test:db:prepare && pnpm test:coverage",
    "verify:full": "pnpm verify && pnpm build && pnpm test:contract && pnpm test:e2e && pnpm test:a11y && pnpm test:bundle && pnpm test:perf"
  }
}

Script rules:

1. From Milestone M9 on, content:build is tsx scripts/build-content.ts --require-complete (Section 18). build:image inherits the flag because it calls pnpm content:build, and pnpm appends --no-verify to that command.
2. pnpm verify is the local gate of every milestone; pnpm verify:full is what CI runs (Section 17.3, 17.13).
3. drizzle-kit push is never used; every schema change goes through a committed migration.

4. Conventions & Best Practices #

These rules apply to all code in the repository. ESLint, Prettier and TypeScript enforce them wherever a rule can be checked mechanically (configured in eslint.config.mjs, prettier.config.mjs and tsconfig.json). The remaining rules are checked in review against the definition of done (Section 4.13).

4.1 TypeScript rules #

1. tsconfig.json compiler options: strict: true, noUncheckedIndexedAccess: true, noImplicitOverride: true, noFallthroughCasesInSwitch: true, useUnknownInCatchVariables: true (implied by strict), verbatimModuleSyntax: true, isolatedModules: true, module: "esnext", moduleResolution: "bundler", target: "ES2023", path alias "@/*": ["./src/*"].
2. No any (@typescript-eslint/no-explicit-any: error). Use unknown and narrow it. No non-null assertions (!) outside tests/. No @ts-ignore. @ts-expect-error is allowed only in tests and must have a comment.
3. No TypeScript enum. Domain enums are as const tuples in src/lib/schemas/enums.ts. Each tuple is the single source for the Drizzle pgEnum, the Zod z.enum and the TS union (Section 5.6). The Zod schema is built from the tuple, never from the Drizzle enum, so src/lib never imports server code:
   Copy

   // src/lib/schemas/enums.ts
   export const TASK_STATUS_VALUES = ["todo", "in_progress", "blocked", "done"] as const;
   export type TaskStatus = (typeof TASK_STATUS_VALUES)[number];
   // src/lib/schemas/tasks.ts
   export const taskStatus = z.enum(TASK_STATUS_VALUES);
   // src/server/db/schema.ts
   export const taskStatusEnum = pgEnum("task_status", TASK_STATUS_VALUES);

4. Exhaustiveness. A switch over a union ends with default: return assertNever(x) (from src/lib/utils.ts), so adding a member is a compile error everywhere it matters.
5. Types vs interfaces. Use type for data shapes and unions. Use interface only for contracts with multiple implementations (for example, AiProvider, Clock).
6. Types come from schemas. Request and response types are z.infer<> of the Zod schemas in src/lib/schemas/. Never write a parallel hand-written type for the same shape. Database row types come from Drizzle (typeof tasks.$inferSelect) and never leave src/server/.
7. Named exports only, except where Next.js requires a default export (page.tsx, layout.tsx, error.tsx, not-found.tsx, config files). Route handlers export named HTTP methods.
8. No barrel files (index.ts that re-exports a folder) except src/lib/time/index.ts, which is the public surface of the time module.
9. Async. Every promise is awaited, returned or explicitly void-ed (@typescript-eslint/no-floating-promises: error). No .then() chains in application code.
10. Immutability. Prefer const, readonly arrays in public signatures, and Object.freeze for module-level configuration objects.
11. No console.* in src/ (no-console: error). Server code uses the logger (Section 4.7). Client code does not log; errors are shown in the UI.
12. Comments explain why, not what. Every exported service function has a one-line JSDoc stating its contract, including which AppError codes it can throw.
13. No unfinished-work markers (to-do or fix-me comments) and no commented-out code in committed code. Unfinished work is not committed; deviations go to DECISIONS.md (Section 4.12). The check is grep -rnE "T[O]DO|F[I]XME|X[X]X|console\.log" src/ scripts/ (the bracketed letters keep the pattern from matching itself) and must print nothing.

4.2 Naming #

4.3 Folder and import rules #

The repository layout is fixed in Section 3.4. These dependency rules are enforced with ESLint no-restricted-imports per folder:

Additional rules:

1. Every module in src/server/** starts with import "server-only";. Exceptions are the modules that next.config.ts, src/proxy.ts, src/instrumentation.ts or scripts/*.ts import, because server-only throws outside the React server environment: src/server/env-schema.ts, src/server/logger.ts, src/server/security/headers.ts and src/server/security/origin.ts. These four must not import any module that imports server-only.
2. Every client component file starts with "use client";. Keep the client boundary as low in the tree as possible: pages and layouts are Server Components.
3. Domain components live in src/components/<domain>/. Generic, design-system components live in src/components/ui/ and never import domain code.
4. Tests mirror the source path below src/: src/lib/time/convert.ts → tests/unit/lib/time/convert.test.ts; src/server/services/scheduler-core.ts → tests/unit/server/services/scheduler-core.test.ts (pure logic); src/server/services/scheduler.ts → tests/integration/server/services/scheduler.test.ts (database); endpoint behavior → tests/contract/learning-sessions.test.ts.
5. Product limits (max tasks, tags, windows, reminders per task, bulk size, text lengths) are constants in src/lib/constants.ts. Schemas, services and UI all use the constants; no magic numbers elsewhere.

4.4 Service-layer pattern #

1. Route handlers are thin. A handler body is one expression: call one service function and wrap the result (example in Section 3.5). It never contains conditionals on business state, SQL or more than one service call.
2. Services own all logic. Each domain has one file in src/server/services/. It exports async functions that implement the rules of the owning section: validation beyond schema shape (for example, "status done requires …"), state transitions, limits, ordering and side effects.
3. Every service function takes userId: string as its first parameter, and every query filters by it (eq(table.userId, userId)). That includes queries by primary key: WHERE id = $1 AND user_id = $2. A row owned by someone else is indistinguishable from a missing row and yields NOT_FOUND, never FORBIDDEN. The only exceptions are setup (createAccount, which has no user yet), login and recovery lookups by email, session lookup by token hash, and the jobs in Section 3.6.
4. Signature shape:
   Copy

   export async function updateTask(
     userId: string,
     taskId: string,
     input: UpdateTaskBody,
     ctx: ServiceContext = defaultContext,
   ): Promise<TaskDto>;
   
   // src/server/services/context.ts
   export type ServiceContext = {
     db: Db | Tx;      // pass a transaction to compose services atomically
     clock: Clock;     // src/lib/time/clock.ts; tests inject a fixed clock
     log: Logger;      // child logger from the request
   };

5. Transactions. A public service function that performs more than one write opens a transaction (db.transaction) unless it was given a Tx in ctx.db, in which case it joins that transaction. Cross-domain effects (for example, a task completed → reminders.dismissForTask(userId, …, ctx); a session moved → reminders.syncSessionReminders(userId, …, ctx)) are explicit function calls inside the same transaction. Exception: the scheduler. A mutation that triggers a scheduler run (for example, a task's time block changed) calls scheduler.requestRun(userId, reason) after its transaction commits, with a reason id from Section 12.6.10 (setup, settings_changed, availability_changed, task_time_changed, session_created, session_edited, session_skipped, session_deleted, manual_reschedule, new_local_day, status_refresh). The scheduler takes its own per-user advisory lock; a scheduler failure is logged at error and never fails the request that triggered it. There are no event emitters, ORM hooks or database triggers for business logic.
6. Services return DTOs, never Drizzle rows. A DTO is the exact API shape: camelCase, instants as ISO strings ending in Z, local dates as YYYY-MM-DD. Each domain maps rows with a private toXxxDto(row) function in its service file. RSC pages receive the same DTOs, so server-rendered and client-fetched data are identical.
7. Services never read HTTP state. No Request, cookies or headers. Whatever they need (userId, ipPrefix, userAgent) is passed in as parameters.
8. Time. Services get "now" only from ctx.clock.now(), never new Date() or Date.now() (Section 4.8).
9. Limits are checked inside the transaction that would exceed them, so concurrent requests cannot both pass the check. Every count-capped insert and every whole-set replace (the list is in Section 5.9; for example POST /api/v1/tasks, PUT /api/v1/availability, PUT /api/v1/today/order) first runs SELECT 1 FROM users WHERE id = $1 FOR UPDATE in its transaction, then counts or replaces.
10. Pure logic is separate. Algorithms (default order, scheduler placement, streak math, quiz grading, reminder preset computation) are pure functions with no database access, exported from the service file or a sibling <name>-core.ts file (for example scheduler-core.ts, streak-core.ts). Pure logic that the client also needs (default order, due labels, capacity, reminder presets, quick-add parsing) lives in src/lib/<domain>/ instead, so it is isomorphic (Section 3.4). Pure logic is unit-tested exhaustively; the service function loads data, calls the pure function and persists the result.

4.5 Zod schema conventions #

1. Location. src/lib/schemas/<domain>.ts, shared by client forms and server validation. Common primitives are in src/lib/schemas/common.ts:
   Copy

   export const uuid = z.uuid();
   export const localDate = z.string().regex(/^\d{4}-\d{2}-\d{2}$/, "Use the format YYYY-MM-DD.")
     .refine(isValidLocalDate, "This date does not exist.");            // real calendar date, 2000-01-01..2100-12-31
   export const isoInstant = z.iso.datetime({ offset: false, error: "Use a UTC time ending in Z." }) // offsets rejected
     .refine(isWithinSupportedRange, "Date is out of range.");          // 2000-01-01T00:00:00Z ≤ t < 2101-01-01T00:00:00Z
   export const userInstant = isoInstant                                // scheduledStart, remindAt, startsAt
     .transform(truncateToMinute);                                      // seconds and ms set to 0 (Section 4.8.1), never rejected
   export const minuteOfDay = z.number().int().min(0).max(1440);
   export const limit = z.coerce.number().int().min(1).max(200).default(50);
   export const cursor = z.string().regex(/^[A-Za-z0-9_-]{1,512}$/).optional();
   export const ianaTimeZone = z.string().max(64).refine(isValidTimeZone, "Choose a valid time zone."); // Section 4.8.2

2. Strict objects. Request bodies use z.strictObject({...}), so unknown keys fail with VALIDATION_FAILED. Query schemas use z.object (unknown query parameters are ignored).
3. Strings are trimmed first. z.string().trim().min(1, "Title is required.").max(200, "Title must be 200 characters or fewer."). Passwords are never trimmed. Emails are trimmed and lowercased with .trim().toLowerCase() before z.email() and a 254-character maximum.
4. Absent vs null in PATCH bodies. An absent key means "leave unchanged"; null means "clear the value" (allowed only for nullable columns). PATCH schemas are .partial() objects. An empty PATCH body {} is valid: it changes nothing and returns the unchanged resource with 200 (Section 6.6.2).
5. Query coercion. Numbers use z.coerce.number(). Booleans use z.enum(["true","false"]).transform(v => v === "true"), never z.coerce.boolean() (which treats "false" as true). Repeatable keys (tag) are arrays.
6. Messages are user-facing sentences that end with a period and match the microcopy style in Section 14. They never contain the submitted value.
7. Naming: <verb><Entity>Body, <verb><Entity>Query, <entity>IdParams, <entity>Dto. Each schema is exported along with its inferred type: export type CreateTaskBody = z.infer<typeof createTaskBody>.
8. Mapping issues to the error envelope. fields keys are issue paths joined with . (windows.2.startMinute). Only the first issue per path is kept. Issues with an empty path (object-level refinements) use the key _form. The envelope itself is defined in Section 6.3.
9. Output validation. DTO schemas are used in tests to assert that responses match, and in the AI layer to validate model output (Section 13). They are not re-run on every production response.

4.6 Error handling #

All expected failures are AppErrors. The code → HTTP mapping below follows the error catalog in Section 6.3, which owns codes, messages and the envelope.

// src/server/http/errors.ts
import "server-only";

export const ERROR_HTTP_STATUS = {
  UNAUTHENTICATED: 401,
  FORBIDDEN: 403,
  NOT_FOUND: 404,
  CONFLICT: 409,
  SETUP_ALREADY_DONE: 409,
  SETUP_REQUIRED: 409,
  PAYLOAD_TOO_LARGE: 413,
  VALIDATION_FAILED: 422,
  ACCOUNT_LOCKED: 423,
  RATE_LIMITED: 429,
  AI_LIMIT_REACHED: 429,
  INTERNAL_ERROR: 500,
  AI_UNAVAILABLE: 503,
} as const;
export type ErrorCode = keyof typeof ERROR_HTTP_STATUS;

export class AppError extends Error {
  readonly code: ErrorCode;
  readonly status: number;
  readonly fields?: Readonly<Record<string, string>>;
  readonly retryAfterSeconds?: number;
  constructor(
    code: ErrorCode,
    message: string,
    opts: { fields?: Record<string, string>; retryAfterSeconds?: number; cause?: unknown } = {},
  ) {
    super(message, { cause: opts.cause });
    this.name = "AppError";
    this.code = code;
    this.status = ERROR_HTTP_STATUS[code];
    this.fields = opts.fields;
    this.retryAfterSeconds = opts.retryAfterSeconds;
  }
}

export const notFound = (what: string) => new AppError("NOT_FOUND", `${what} was not found.`);
export const conflict = (message: string) => new AppError("CONFLICT", message);
export const validationFailed = (fields: Record<string, string>, message = "Fix the highlighted fields and try again.") =>
  new AppError("VALIDATION_FAILED", message, { fields });

Rules:

1. Services throw; they do not return error unions. Throw only AppError for expected failures. Any other thrown value is an unexpected bug.
2. Business-rule violations of a single field (for example, a tag name already in use → 422 with fields.name, or an end before a start) use VALIDATION_FAILED with fields. State conflicts (for example, completing a lesson whose rule is not met, permanently deleting a task that is not in Trash, a learning session that has already started, a reminder for a done or trashed task, lesson content that changed under an open page) use CONFLICT with a sentence explaining the state. Section 6 lists the code each endpoint uses.
3. PostgreSQL errors are translated in the service that expects them, never globally: unique violation 23505 on a known constraint → VALIDATION_FAILED with the matching field for user-facing duplicates (for example tags_user_name_uq → fields.name); during setup, users_singleton_uq / users_email_uq → SETUP_ALREADY_DONE; foreign-key violation 23503 → NOT_FOUND for the referenced entity; check violation 23514 → VALIDATION_FAILED with the field derived from the constraint name, logged at warn because it means a Zod rule is missing. Any other database error propagates as unexpected (INTERNAL_ERROR).
4. Unexpected errors become 500 INTERNAL_ERROR with the fixed message "Something went wrong. Please try again." and the response header X-Request-Id. The full error with its stack is logged at error (Section 4.7). Internal details never reach the client.
5. Retry-After is set from retryAfterSeconds for RATE_LIMITED, AI_LIMIT_REACHED and ACCOUNT_LOCKED.
6. Client side. apiFetch() (src/lib/api-client/fetch.ts) unwraps { data }, and for error envelopes throws ApiError { code, message, fields, status, requestId }. Network failures throw ApiError with code NETWORK_ERROR, a client-only code that is never sent by the server. Forms map fields onto inputs; other errors show the message from the envelope, or Section 14's microcopy for client-only cases. A 401 from any endpoint except POST /api/v1/auth/login and POST /api/v1/auth/recover (whose 401 is a form error shown inline) means the session has ended: the client clears the TanStack Query cache, removes every browser-storage key listed in Section 15.14.2, and redirects to /login?next=<current path>; the sign-in page shows "Your session has ended. Sign in again." (Section 7.5.3). A 423 ACCOUNT_LOCKED from a re-authentication form never triggers this redirect; it is shown on the form.
7. React error boundaries. src/app/error.tsx and global-error.tsx show a recoverable error screen with "Try again" and never render error details. The Python runner's errors are not application errors: they are results shown in the output panel (Section 11).

4.7 Logging rules #

1. Library and output. pino (see Section 3.2) writes JSON lines to stdout; Docker collects them (Section 16). LOG_LEVEL sets the minimum level (default info). In development, output may be piped through pino-pretty. That is a developer convenience only; pino-pretty is not a dependency of the built app.

2. Base fields on every line: time (ISO), level, msg, app ("benchhub"), env (NODE_ENV). Request-scoped lines add reqId, route (the defineRoute name, for example tasks.update) and userId once known.

3. One access line per API request at info (or debug for /api/health and /api/v1/reminders/due, which are polled), sent on response: { reqId, route, method, status, durationMs, errorCode? }. The URL path and query string are not logged, because queries carry search text.

4. Levels.

   Level	Use
   fatal	The process cannot continue (invalid env, migration failure). The process exits.
   error	Unexpected exceptions (500s), job failures, AI provider errors after retry. Always includes err.
   warn	Security-relevant expected events: account lockout, repeated Origin failures, rate-limit trips on login, recover or setup; AI output failed validation (first attempt).
   info	Access lines, job summaries, boot summary ({ version, node, aiConfigured: boolean }), login success and logout ({ event } only), AI usage ({ kind, status, model, inputTokens, outputTokens, latencyMs }).
   debug	Polling endpoints, scheduler decision summaries as counts only ({ created, deleted, kept }, Section 12.6.11), job skips.

5. Never log any of the following, at any level, in any field, including inside error messages and stack contexts:

   • passwords (current, new, attempted) or password hashes;
   • recovery codes or their hashes, SETUP_TOKEN, ANTHROPIC_API_KEY, or any other secret or env var value;
   • session tokens, the bh_session cookie, Cookie/Set-Cookie/Authorization headers, or session ids (the id is a token hash and works as a lookup key);
   • request or response bodies, in whole or in part;
   • code: exercise code, autosaved code, attempt code, reference solutions;
   • task notes and task titles, tag names, search queries;
   • AI prompts, AI responses, conversation history, selected code or error output sent to AI;
   • email addresses (log userId instead); full IP addresses (only ip_prefix may be logged); user-agent strings.

6. Redaction as a safety net. The root logger configures pino redact with censor: "[REDACTED]" for at least these paths: req.headers.cookie, req.headers.authorization, res.headers["set-cookie"], *.password, *.currentPassword, *.newPassword, *.recoveryCode, *.setupToken, *.token, *.apiKey, *.draftCode, *.solution, *.notes, *.title, *.email, *.prompt, *.messages, *.body, req.body. The key code is not redacted, because it is reserved for error codes (AppError codes and PostgreSQL codes such as 23505, which rule 7 requires); exercise code is never placed in a log object (rule 5). Redaction backs up rule 5; it does not replace it.

7. Error serialization. Log errors as { err } with pino's standard serializer, but for PostgreSQL errors log only { code, constraint, table, routine }. The detail and message of database errors can contain row values and are dropped by a custom serializer in src/server/logger.ts.

8. Security events live in the database (login_events, Section 7), not only in logs. Logs repeat only the event name and userId.

9. Client: no logging, no telemetry, no error-reporting services (goal G8, Section 2.5).

4.8 Time and timezone rules #

Time handling is the most error-prone part of this product. These rules are complete and binding; Sections 9 and 12 build their behavior on them.

4.8.1 Storage and wire formats #

Rules:

1. Database session timezone is UTC. The pg pool is created with options: "-c timezone=UTC". The container sets TZ=UTC (Section 16). Code never depends on either: every conversion passes an explicit zone.
2. Inputs. Instants must end in Z; offsets such as +02:00, and strings without a zone, are rejected with 422 VALIDATION_FAILED so clients always convert explicitly. Fractional seconds are accepted. User-entered instants (scheduledStart, remindAt, learning session startsAt) are truncated to the whole minute server-side before any further validation and storage (the userInstant schema of Section 4.5); a value with seconds is never rejected for that reason. Supported range: 2000-01-01 to 2100-12-31 for local dates; instants from 2000-01-01T00:00:00Z up to but excluding 2101-01-01T00:00:00Z.
3. Outputs. Always toISOString(), which gives milliseconds and Z. Local dates are never converted to instants on the wire.
4. In SQL, local-date logic uses a bound zone parameter, e.g. (starts_at AT TIME ZONE $tz)::date. current_date, now()::date, localtimestamp and timezone-less timestamp columns are forbidden.

4.8.2 The account timezone #

1. The account timezone is user_settings.timezone, an IANA name. At setup it is pre-filled from the browser with Intl.DateTimeFormat().resolvedOptions().timeZone. If that is unavailable or invalid, it falls back to UTC.
2. Validation: a zone is valid if new Intl.DateTimeFormat("en-US", { timeZone: zone }) does not throw. This is the only server-side validity rule (setup, settings and every other input use it); membership in Intl.supportedValuesOf("timeZone") is never required, because engines list different IDs for the same zone (for example Asia/Calcutta in V8 and Asia/Kolkata in Firefox). Store new Intl.DateTimeFormat("en-US", { timeZone: zone }).resolvedOptions().timeZone, the case-normalized ID; aliases such as US/Eastern are accepted and may be stored as given, which is correct because they resolve to the same rules. UTC is always valid. The validation message is "Choose a valid time zone." The time-zone picker lists Intl.supportedValuesOf("timeZone") plus UTC, plus the detected zone if it is missing from the list.
3. All local interpretation uses the account timezone, never the browser's. That covers "today", due dates, availability windows, reminder presets and display. The client receives the account timezone with the settings in its initial data and passes it to every src/lib/time call. If the browser's zone differs, the UI shows the notice defined in Section 14 offering to switch; it never switches automatically.
4. Changing the timezone. Stored instants do not change. Local-date fields (tasks.due_date, daily_plans.local_date, activity_days.local_date) keep their stored dates and are never recomputed. Availability windows keep their wall-clock values and so shift in absolute time. The settings change triggers a reschedule (Section 12). Relative reminder presets are recomputed per Section 9.
5. Timezone rules come from Node.js's bundled ICU data. Upgrading Node.js within its major line updates the rules. No separate tz database package is used.

4.8.3 The time module (src/lib/time/) #

All date and time logic lives here. Elsewhere, the following are banned through ESLint no-restricted-properties / no-restricted-syntax: getHours, getMinutes, getDate, getDay, getMonth, getFullYear, setHours, setDate and related local-time Date methods; toLocaleDateString, toLocaleTimeString and toLocaleString without a timeZone option; new Date(<string>) for anything except parsing an ISO instant ending in Z; and Date.now() / new Date() in src/server/services/** (use ctx.clock). The module may use Date.UTC, getUTC*, Intl.DateTimeFormat with an explicit timeZone, date-fns with the in: tz(zone) context, and tzOffset from @date-fns/tz.

// src/lib/time/types.ts
export type LocalDate = string & { readonly __brand: "LocalDate" };   // "YYYY-MM-DD"
export type MinuteOfDay = number;                                       // 0..1440 (1440 only as exclusive end)
export type Weekday = 0 | 1 | 2 | 3 | 4 | 5 | 6;                        // 0 = Sunday
export type IanaZone = string & { readonly __brand: "IanaZone" };
export type InstantRange = { start: Date; end: Date };                  // half-open [start, end)

// src/lib/time/clock.ts
export interface Clock { now(): Date }
export const systemClock: Clock = { now: () => new Date() };
export const fixedClock = (iso: string): Clock => ({ now: () => new Date(iso) });

// Public API (src/lib/time/index.ts)
export function parseLocalDate(s: string): LocalDate;                   // throws on invalid; validates real calendar date
export function isValidLocalDate(s: string): boolean;
export function isValidTimeZone(zone: string): boolean;
export function canonicalZone(zone: string): IanaZone;
export function tzOffsetMinutes(zone: IanaZone, at: Date): number;      // e.g. -240 for EDT
export function toLocalDate(at: Date, zone: IanaZone): LocalDate;       // local calendar date of an instant
export function toMinuteOfDay(at: Date, zone: IanaZone): MinuteOfDay;   // local wall-clock minute of an instant
export function todayLocal(zone: IanaZone, clock: Clock): LocalDate;
export function addLocalDays(date: LocalDate, days: number): LocalDate; // pure calendar arithmetic, no zone
export function diffLocalDays(a: LocalDate, b: LocalDate): number;      // a − b in calendar days
export function weekdayOf(date: LocalDate): Weekday;                    // pure calendar, via Date.UTC(...).getUTCDay()
export function localDateTimeToInstant(date: LocalDate, minute: MinuteOfDay, zone: IanaZone): Date;
export function startOfLocalDay(date: LocalDate, zone: IanaZone): Date; // = localDateTimeToInstant(date, 0, zone)
export function localDayRange(date: LocalDate, zone: IanaZone): InstantRange; // [start(date), start(date+1))
export function weekStart(date: LocalDate, weekStartsOn: Weekday): LocalDate;
export function weekDates(date: LocalDate, weekStartsOn: Weekday): LocalDate[];  // 7 dates
export function windowToInstantRange(date: LocalDate, startMinute: MinuteOfDay, endMinute: MinuteOfDay, zone: IanaZone): InstantRange | null;
export function formatInstant(at: Date, zone: IanaZone, style: InstantFormatStyle): string;  // styles: Section 14
export function formatLocalDate(date: LocalDate, style: DateFormatStyle): string;            // styles: Section 14

The module is isomorphic: it runs the same in the browser and on the server, so client-side previews ("Reminder at Tue 09:00") always match what the server stores.

4.8.4 Converting a local wall-clock time to an instant (DST rules) #

localDateTimeToInstant(date, minute, zone) is the only way to turn a local date and time into an instant. It handles the two DST edge cases deterministically, with the same semantics as the "compatible" disambiguation of the TC39 Temporal proposal:

• Nonexistent local time (spring-forward gap): the time is shifted forward by the length of the gap. Example: America/New_York, 2026-03-08, 02:30 does not exist (clocks jump from 02:00 EST to 03:00 EDT). It resolves to 03:30 EDT = 2026-03-08T07:30:00.000Z.
• Repeated local time (fall-back overlap): the earlier occurrence is used. Example: America/New_York, 2026-11-01, 01:30 occurs twice. It resolves to 01:30 EDT = 2026-11-01T05:30:00.000Z, not 01:30 EST (06:30Z).
• Minute 1440 means 00:00 of the next local date.

Reference algorithm (it assumes at most one offset transition within ±24 h, which holds for every zone in the IANA database):

export function localDateTimeToInstant(date: LocalDate, minute: MinuteOfDay, zone: IanaZone): Date {
  const [y, m, d] = date.split("-").map(Number) as [number, number, number];
  const wallMs = Date.UTC(y, m - 1, d, 0, minute);          // wall clock read as if it were UTC
  const offBefore = tzOffsetMinutes(zone, new Date(wallMs - 86_400_000));
  const offAfter = tzOffsetMinutes(zone, new Date(wallMs + 86_400_000));
  const candidates = [...new Set([wallMs - offBefore * 60_000, wallMs - offAfter * 60_000])]
    .filter((t) => wallClockMs(new Date(t), zone) === wallMs); // instants whose local wall clock equals the input
  if (candidates.length > 0) return new Date(Math.min(...candidates)); // normal (1) or repeated (2) → earliest
  return new Date(wallMs - offBefore * 60_000);              // gap → pre-transition offset = shift forward by gap
}
// wallClockMs(t, zone) = t.getTime() + tzOffsetMinutes(zone, t) * 60_000

Consequences used elsewhere:

1. Local days are not always 24 hours. A local day is the half-open range [startOfLocalDay(d), startOfLocalDay(d + 1)), which can be 23, 23.5, 24, 24.5 or 25 hours long. Never compute "tomorrow" by adding 86,400,000 ms. Use addLocalDays and then convert.
2. Durations are real elapsed time. A 20-minute learning session is startsAt + 20 × 60,000 ms, even across a transition.
3. Availability windows on transition days. windowToInstantRange converts both ends with localDateTimeToInstant. If the resulting range is shorter than 15 minutes, or end ≤ start (a window entirely inside a gap), the function returns null and the window is skipped for that date. A window spanning a fall-back overlap is one hour longer in real time on that date, and the scheduler may use that time (Section 12).
4. Displayed results. When a user-entered local time falls in a gap, the stored instant displays as the shifted time (02:30 → 03:30). The UI shows the saved value after the round trip, so no extra warning is needed.

4.8.5 "Today", day membership and weeks #

1. Today = todayLocal(user_settings.timezone, clock), computed on the server for every request that needs it. The client never sends its own idea of today, except as an explicit date parameter (for example GET /api/v1/today?date=), which the server validates as a localDate.
2. An instant belongs to local date d iff it is in localDayRange(d, zone). "Scheduled start today" and "today's learning sessions" use this range test, never a date cast of the UTC value.
3. A learning session belongs to the local date of its starts_at, even if it ends after midnight.
4. Overdue / due today compare local-date strings: due_date < today and due_date === today. YYYY-MM-DD strings compare correctly as text, so no conversion is needed.
5. Fixed local times come from localDateTimeToInstant: "09:00 on due date" = (dueDate, 540); "tomorrow 09:00" snooze = (addLocalDays(today, 1), 540).
6. Weeks. The week containing d starts at weekStart(d, weekStartsOn) = addLocalDays(d, -((weekdayOf(d) - weekStartsOn + 7) % 7)) and consists of 7 consecutive local dates. Example: with week_starts_on = 1 (Monday), the week of Wednesday 2026-10-21 is 2026-10-19 … 2026-10-25. With 0 (Sunday) it is 2026-10-18 … 2026-10-24. Weekly targets (Section 12) count sessions whose starts_at local date is in the week. A change to week_starts_on applies to all calculations from the next request on; past data is not rewritten.
7. Activity days use the local date of the qualifying event in the timezone that applied when the event happened. Rows are never moved after a timezone change.
8. New local day. A change of todayLocal between requests is detectable on the server. Section 12 defines what is triggered by it.
9. Client clocks are not trusted for anything stored. The client uses its clock only for display (relative labels such as "in 12 min", countdowns) and for deciding when to poll.

4.8.6 Mandatory time test cases #

tests/unit/lib/time/*.test.ts must cover at least the following (Section 17 sets the coverage gate):

4.9 Money #

BenchHub handles no money. There are no prices, currencies, payments or subscriptions (Section 2.6), and no monetary fields anywhere. Token counts in ai_usage are stored as integers, never converted to currency.

4.10 Accessibility-first component rules #

The target is WCAG 2.2 AA (Section 14 owns tokens and screens; Section 17 owns automated checks). Every component follows these rules:

1. Native elements first. Actions are <button>, navigation is <a href> (Next.js Link), inputs are real <input>, <select> or <textarea>. A div with onClick is forbidden (jsx-a11y rules set to error).
2. Radix primitives (see Section 3.2) for every Dialog, AlertDialog (destructive confirmations), DropdownMenu, Popover, Tabs, Tooltip, Select, Switch, Checkbox, RadioGroup and Toast. Wrap them in src/components/ui/ and keep their focus management and ARIA intact. Do not hand-roll these widgets.
3. Names. Every interactive element has an accessible name. Icon-only buttons have aria-label (for example, "Delete task 'Order 0402 resistors'") and a Tooltip with the same text. Icons that accompany text are aria-hidden="true".
4. Never color alone. Priority shows its label (Critical/High/Normal/Low) or an icon with a text alternative alongside the color. Tag chips show their name. Status, overdue and error states have text or an icon, not just a color.
5. Forms. Every input has a visible <label>. Help text and errors are linked through aria-describedby, and invalid inputs set aria-invalid="true". On a failed submit, focus moves to the first invalid field, and forms with 4 or more fields also show an error summary at the top. Required fields are marked with text ("required"), not just an asterisk.
6. Focus. Every focusable element has a visible focus indicator (focus ring token from Section 14, at least 2 px, contrast ≥ 3:1). Focus is never removed with outline: none without a replacement. After client-side navigation, focus moves to the page's <h1>. A "Skip to main content" link is the first focusable element. Closing a dialog returns focus to its trigger.
7. Live regions. Toasts, including reminders, use Radix Toast (role="status", polite). Form-level errors use role="alert". The exercise output panel is not a live region: each exercise or runnable block has one visually hidden aria-live="polite" element that announces only the one-line result summary (for example "3 of 4 tests passed"), never the output itself (Section 11.5). The AI answer stream is not announced token by token; when the stream ends, screen readers get "Answer ready" (Section 13.5). Live-region elements are present in the DOM before their content changes.
8. Keyboard. Everything works without a pointer. Drag-to-reorder always has "Move up"/"Move down" buttons as well as the dnd-kit keyboard sensor. In the CodeMirror editor, Tab indents. Pressing Escape then Tab moves focus out of the editor, and the instruction is shown next to the editor and exposed through aria-describedby.
9. Touch and size. Below 1024 px, touch targets are at least 44 × 44 px. Form controls use at least a 16 px font size so iOS Safari does not auto-zoom. Pinch-zoom is never disabled. Layouts reflow at 320 px and 400% zoom without horizontal scrolling, except for code blocks and the editor, which scroll inside their own region.
10. Motion and themes. Animations respect prefers-reduced-motion: reduce (they become instant). Both themes meet contrast: text ≥ 4.5:1, large text and UI components ≥ 3:1.
11. Semantics. One <h1> per page, headings without skipped levels, landmarks (header, nav, main), lists as <ul>/<ol>. Dates and times render inside <time dateTime="…"> with the ISO value.
12. Loading and empty states are announced: skeletons have aria-busy="true" on their container, and empty states are real text, never just an image.

4.11 Commits and branches #

1. Branches. main is always releasable and every commit on it passes the gates of Section 17. Work happens on short-lived branches named <type>/<milestone>-<slug>, e.g. feat/m3-today-pinned-order, where m3 is a milestone from Section 18. Each branch is merged with a fast-forward or squash; no merge commits on main.
2. Commit messages follow Conventional Commits: <type>(<scope>): <subject>.
   • type ∈ feat, fix, refactor, test, docs, build, ci, chore, perf, style.
   • scope is a domain: auth, tasks, tags, today, reminders, curriculum, content, exercises, quizzes, progress, scheduler, ai, ui, db, api, ops, deps.
   • The subject is imperative, lowercase, has no trailing period and is at most 72 characters. The body (wrapped at 100 columns) explains why and references the spec section, e.g. Spec: Section 12.
   • Breaking API changes are not allowed after milestone 1 without a DECISIONS.md entry.
3. Atomic commits. One logical change per commit, including its tests. Schema changes ship with their generated migration in the same commit.
4. Migrations. Never edit or delete a migration that has been applied anywhere. Fix forward with a new migration. Migration files keep the drizzle-kit generated names.
5. Never commit .env, generated folders (src/generated/, public/pyodide/, .next/, dist/), secrets, database dumps or real user data. .env.example and deploy.env.example hold placeholders only.
6. Dependency changes use deps scope commits that include the lockfile change and, for majors or new packages, a DECISIONS.md entry (Section 3.2).

4.12 DECISIONS.md #

DECISIONS.md at the repository root is the executor's own log of decisions this specification did not fix and of every deviation from it. Nothing is deviated from silently.

Write an entry when:

1. A dependency's major line differs from Section 3.2, a dependency is added, or the TypeScript 6.x fallback is used.
2. The spec is ambiguous or silent on something the executor had to decide, and the decision is visible in behavior, API, data or UI.
3. Something in the spec could not be implemented as written (for example, a library limitation), and an alternative was used.
4. An owner's answer to a Section 1 question changed a default.
5. A new top-level folder, new environment variable or new endpoint was added. New environment variables are also added to .env.example and the README.

Format: Section 19.4 (one table row per decision, append-only, numbered D-001, D-002, …; superseding a decision adds a new row that references the old ID, and old rows are never edited). Commits that implement a decision mention its ID in the body (Decision: D-007).

4.13 Definition of done #

A task, whether a feature, endpoint, screen or fix, is done only when all of these hold:

1. Behavior matches the owning section's rules, limits, states and error codes exactly. Every acceptance criterion stated there passes.
2. API: endpoints exist with the exact paths, methods, schemas, status codes and envelope of Section 6. Every endpoint runs through the pipeline of Section 3.5 (Sec-Fetch-Site and Origin checks, rate limits, setup gate).
3. Data: schema changes are in src/server/db/schema.ts with a generated migration committed. Every query filters by user_id (Section 4.4).
4. Tests: unit tests for pure logic, integration tests for each endpoint touched (success, validation failure, not-found or other-user, and each documented error code), and end-to-end coverage for any affected E2E journey J1–J12 (Section 17.9.2), all meeting the gates of Section 17.
5. UI states: loading, empty, error, success and disabled states (including AI unavailable/disabled where relevant) are implemented with the microcopy of Section 14.
6. Accessibility: the rules in Section 4.10 hold. There are no serious or critical axe violations, and the flow has been checked with keyboard only.
7. Responsive: verified at 320 px, 768 px and 1280 px, in light and dark themes, with no horizontal page scroll.
8. Time: all date and time logic goes through src/lib/time. Behavior is correct for a non-UTC account timezone and on DST transition days (Section 4.8).
9. Security and privacy: no secret or forbidden data in logs (Section 4.7), no new third-party origins, CSP unchanged unless Section 15 says otherwise, and server-only modules guarded.
10. Quality gates: pnpm verify passes locally (it includes the content build and pnpm content:test), and pnpm verify:full passes in CI (scripts: Section 3.7; gates: Section 17.3).
11. Hygiene: no unfinished-work markers (to-do or fix-me comments), no console.*, no dead code, no unused dependencies (check command in Section 4.1 rule 13). DECISIONS.md is updated for any decision or deviation (Section 4.12). The README is updated if setup, env vars or operations changed.
12. Committed and pushed following Section 4.11, with the branch merged into main after CI passes.

5. Data Model & Database Schema #

This section is the single authoritative definition of every table, column, enum, index, constraint, seed row, retention rule and migration rule. Other sections refer to tables and columns by the exact names used here. Database: PostgreSQL; ORM Drizzle with drizzle-kit migrations (versions: see Section 3.2).

5.1 Conventions #

1. Table names are plural snake_case; column names are snake_case. Drizzle exposes them as camelCase properties (user_id → userId). Every column is given an explicit SQL name in the Drizzle schema; the Drizzle casing option is not used.
2. Primary keys are uuid with default uuidv7() (built into the PostgreSQL line of Section 3.2, time-ordered) unless the table states a natural or composite key.
3. Every table has created_at timestamptz NOT NULL DEFAULT now(). Every mutable table also has updated_at timestamptz NOT NULL DEFAULT now(). The application sets updated_at = now() on every UPDATE (Drizzle $onUpdate). Raw SQL updates written by hand (purge jobs, bulk operations) must also set it.
4. All instants are timestamptz and are always written and read in UTC. Local calendar days are date values computed in user_settings.timezone (time rules: Section 4). Minutes-of-day are smallint 0–1440.
5. Enums are PostgreSQL enums created by migration. Their values are fixed and never user-configurable. Adding a value requires a new migration (ALTER TYPE … ADD VALUE); values are never removed or renamed.
6. Every user-owned table carries user_id uuid NOT NULL referencing users(id) ON DELETE CASCADE, and every query filters by user_id even though an instance has one account (defense in depth). The only exceptions: user_settings and daily_plans/activity_days (where user_id is part of the primary key, still FK-cascaded), task_tags (ownership inherited from tasks and tags, both of which carry user_id), and login_events (nullable user_id, see 5.5.4).
7. Text length limits are enforced three times: in the Zod schema (Section 6 returns VALIDATION_FAILED), in the column type (varchar(n)) where the type allows, and in a CHECK constraint. Lengths are counted in Unicode code points (char_length in SQL; [...str].length in TypeScript — not str.length, which counts UTF-16 units).
8. Text that must not carry surrounding whitespace (titles, names, emails) is trimmed by the application before insert; the CHECK (col = btrim(col)) constraint guarantees it.
9. Empty optional text (notes = "") is normalized to NULL by the application.
10. Lesson, exercise and quiz content is not stored in the database. It lives in repository files (Section 10), compiled at build time to src/generated/curriculum.json (client-safe) and src/generated/quiz-keys.json (server-only). Rows reference content by stable string IDs: lesson slug m{MM}-l{LL}-{kebab-title} (e.g. m02-l04-bitwise-operators), exercise id {lesson-slug}--ex{N}, quiz id {lesson-slug}--quiz. The database does not validate that an ID exists in the content; the service layer does, against the compiled curriculum, and returns NOT_FOUND (Section 6) for unknown IDs.
11. Decision: exercise and quiz IDs are stored in varchar(80) columns and are derived by appending --ex{N} (N ≤ 99) or --quiz to the slug. Therefore a lesson slug may be at most 74 characters in practice so that every derived ID fits in 80; the content build (Section 10) rejects slugs longer than 74 characters. Slug columns remain varchar(80).
12. Constraint names follow {table}_{column-or-purpose}_{kind} with kinds check, uq, idx, fk (foreign keys use the drizzle-kit default form {table}_{column}_{reftable}_{refcolumn}_fk). Names are set explicitly in the Drizzle schema so the generated SQL matches 5.8 exactly.

5.2 Extensions #

uuidv7() is built into the PostgreSQL line of Section 3.2 and needs no extension. Both extensions ship with the official PostgreSQL image used in Section 16. The database role used by the app must own the database (the Compose setup in Section 16 does this) so CREATE EXTENSION succeeds.

5.3 Entity-relationship overview #

                                  ┌───────────────────────────┐
                                  │ users  (0 or 1 row ever)  │
                                  └─────────────┬─────────────┘
                                                │ every child: user_id → users.id ON DELETE CASCADE
   ┌────────────────┬──────────────┬────────────┼──────────────┬──────────────────┬──────────────────┐
   │1:1             │1:N           │1:N         │1:N           │1:N               │1:N               │
┌──▼───────────┐ ┌──▼─────┐ ┌──────▼──────┐ ┌───▼──┐      ┌────▼────┐      ┌──────▼───────────┐ ┌────▼──────────────┐
│user_settings │ │sessions│ │login_events │ │ tags │      │  tasks  │      │availability_     │ │learning_sessions  │
└──────────────┘ └────────┘ │(user_id may │ └───┬──┘      └─┬───┬───┘      │windows           │ └────┬──────────────┘
                            │ be NULL)    │     │1:N     1:N  │   │1:N     └──────────────────┘      │1:N
                            └─────────────┘     │   ┌─────────┘   │                                  │
                                              ┌─▼───▼────┐        │        ┌─────────────────────────┘
                                              │task_tags │        │        │
                                              │(task,tag)│      ┌─▼────────▼───────────────────────────┐
                                              └──────────┘      │ reminders                            │
                                                                │ exactly one of task_id /             │
                                                                │ learning_session_id (both CASCADE)   │
                                                                └──────────────────────────────────────┘
   users 1:N (no FKs to content; content referenced by string IDs):
   ┌────────────────┐ ┌──────────────────┐ ┌──────────────────┐ ┌──────────────┐ ┌──────────────┐ ┌──────────┐ ┌────────────┐
   │lesson_progress │ │exercise_progress │ │exercise_attempts │ │quiz_attempts │ │activity_days │ │ ai_usage │ │daily_plans │
   │(user, slug) uq │ │(user, ex_id) uq  │ │≤50 per exercise  │ │              │ │pk(user,date) │ │          │ │pk(user,    │
   └────────────────┘ └──────────────────┘ └──────────────────┘ └──────────────┘ └──────────────┘ └──────────┘ │ local_date)│
                                                                                                              │ uuid[] of  │
                                                                                                              │ task ids   │
                                                                                                              └────────────┘

daily_plans.ordered_task_ids is an array of task IDs without a foreign key (arrays cannot carry FKs); IDs that no longer refer to an eligible task are ignored when the Today view is built (Section 9).

5.4 Enums #

Meaning of reminder_preset values (computation owned by Section 9): at_start = the task's scheduled_start; before_start_15 = scheduled_start − 15 min; due_morning = 09:00 local on the task's due_date; custom = an absolute time chosen by the user, never recomputed; session_lead = a learning session's starts_at − user_settings.session_reminder_lead_minutes, created and kept in sync automatically. Relative presets (at_start, before_start_15, due_morning, session_lead) are recomputed when their source time changes.

5.5 Tables #

Column-table legend: Null = yes if the column accepts NULL. Default = SQL default (— = none, value must be supplied). All CHECK constraints are listed under each table.

5.5.1 users #

The one account of the instance. At most one row can ever exist (singleton index).

• Primary key: id.
• Foreign keys: none.
• Indexes: users_email_uq UNIQUE (email); users_singleton_uq UNIQUE on the constant expression ((true)) — a second INSERT fails with unique violation, which the setup service maps to SETUP_ALREADY_DONE (Section 6.3). This makes concurrent POST /api/v1/setup requests safe.
• Checks: users_email_check: char_length(email::text) BETWEEN 3 AND 254 AND email::text = lower(btrim(email::text)); users_display_name_check: char_length(display_name) BETWEEN 1 AND 60 AND display_name = btrim(display_name); users_failed_login_count_check: failed_login_count >= 0.

5.5.2 user_settings #

• Primary key: user_id. FK user_settings_user_id_users_id_fk → users(id) ON DELETE CASCADE.
• Indexes: PK only.
• Checks: user_settings_timezone_check: char_length(timezone) BETWEEN 1 AND 64; user_settings_week_starts_on_check: week_starts_on BETWEEN 0 AND 6; user_settings_session_minutes_check: learning_session_minutes BETWEEN 10 AND 60 AND learning_session_minutes % 5 = 0; user_settings_weekly_target_check: weekly_session_target BETWEEN 0 AND 14; user_settings_max_per_day_check: max_sessions_per_day BETWEEN 1 AND 3; user_settings_reminder_lead_check: session_reminder_lead_minutes BETWEEN 0 AND 120 AND session_reminder_lead_minutes % 5 = 0.

5.5.3 sessions #

Login sessions (not learning sessions). The raw 32-byte token exists only in the bh_session cookie; the row stores its SHA-256 hex digest (Section 7).

• Primary key: id. FK sessions_user_id_users_id_fk → users(id) ON DELETE CASCADE.
• Indexes: sessions_user_id_idx (user_id); sessions_idle_expires_at_idx (idle_expires_at) for the purge job.
• Checks: sessions_id_check: id ~ '^[0-9a-f]{64}$'; sessions_expiry_check: idle_expires_at <= absolute_expires_at.
• A session is valid iff a row exists with idle_expires_at > now() (which implies absolute_expires_at > now()).

5.5.4 login_events #

Security audit trail. No endpoint lists it; it is included in the data export (5.12).

• Primary key: id. FK login_events_user_id_users_id_fk → users(id) ON DELETE CASCADE.
• Indexes: login_events_created_at_idx (created_at) for purge; login_events_user_created_idx (user_id, created_at).
• Checks: none.
• Retention: 90 days (5.10). Account deletion also deletes rows with user_id IS NULL (5.10.3).

5.5.5 tags #

• Primary key: id. FK tags_user_id_users_id_fk → users(id) ON DELETE CASCADE.
• Indexes: tags_user_name_uq UNIQUE (user_id, lower(name)).
• Checks: tags_name_check: char_length(name) BETWEEN 1 AND 32 AND name = btrim(name).
• Limit: 50 tags per user (5.9).

5.5.6 tasks #

• Primary key: id. FK tasks_user_id_users_id_fk → users(id) ON DELETE CASCADE.
• Indexes:
  • tasks_user_status_idx (user_id, status) WHERE deleted_at IS NULL
  • tasks_user_due_date_idx (user_id, due_date)
  • tasks_user_scheduled_start_idx (user_id, scheduled_start)
  • tasks_user_trash_idx (user_id, deleted_at) WHERE deleted_at IS NOT NULL — Trash view and purge job
  • tasks_title_trgm_idx GIN (title gin_trgm_ops) — search
• Checks: tasks_title_check: char_length(title) BETWEEN 1 AND 200 AND title = btrim(title); tasks_notes_check: notes IS NULL OR char_length(notes) <= 10000; tasks_priority_check: priority BETWEEN 1 AND 4; tasks_due_date_check: due_date IS NULL OR due_date BETWEEN DATE '2000-01-01' AND DATE '2100-12-31'; tasks_scheduled_start_check: scheduled_start IS NULL OR (scheduled_start >= TIMESTAMPTZ '2000-01-01 00:00:00+00' AND scheduled_start < TIMESTAMPTZ '2101-01-01 00:00:00+00'); tasks_estimate_check: estimate_minutes IS NULL OR (estimate_minutes BETWEEN 5 AND 960 AND estimate_minutes % 5 = 0); tasks_completed_at_check: (status = 'done') = (completed_at IS NOT NULL).
• Derived rule: a task occupies time (blocks the scheduler, Section 12) iff scheduled_start IS NOT NULL AND estimate_minutes IS NOT NULL AND status <> 'done' AND deleted_at IS NULL. Its busy interval is [scheduled_start, scheduled_start + estimate_minutes).
• Limit: 5,000 tasks with deleted_at IS NULL per user (5.9). Trashed tasks do not count.

5.5.7 task_tags #

• Primary key: (task_id, tag_id).
• FKs: task_tags_task_id_tasks_id_fk → tasks(id) ON DELETE CASCADE; task_tags_tag_id_tags_id_fk → tags(id) ON DELETE CASCADE.
• Indexes: task_tags_tag_id_idx (tag_id).
• Checks: none. The service verifies that task and tag belong to the same user before insert.
• Limit: 5 tags per task (5.9).

5.5.8 daily_plans #

A pinned order for the Today view on one local date.

• Primary key: (user_id, local_date). FK daily_plans_user_id_users_id_fk → users(id) ON DELETE CASCADE.
• Indexes: PK only.
• Checks: daily_plans_ids_check: cardinality(ordered_task_ids) <= 500.

5.5.9 reminders #

• Primary key: id.
• FKs: reminders_user_id_users_id_fk → users(id) CASCADE; reminders_task_id_tasks_id_fk → tasks(id) CASCADE; reminders_learning_session_id_learning_sessions_id_fk → learning_sessions(id) CASCADE.
• Indexes:
  • reminders_user_status_remind_idx (user_id, status, remind_at) — due polling and bell list
  • reminders_task_id_idx (task_id) WHERE task_id IS NOT NULL
  • reminders_learning_session_id_idx (learning_session_id) WHERE learning_session_id IS NOT NULL
  • reminders_session_auto_uq UNIQUE (learning_session_id) WHERE origin = 'auto_session' — exactly one automatic reminder row per learning session at most, counting rows of every status. Automatic sync updates (and, when the session moves to a new future start, resets to pending) the existing row instead of inserting a second one (Section 9.5.7).
• Checks:
  • reminders_target_check: num_nonnulls(task_id, learning_session_id) = 1
  • reminders_fired_check: status <> 'fired' OR fired_at IS NOT NULL
  • reminders_dismissed_check: status <> 'dismissed' OR dismissed_at IS NOT NULL
  • reminders_auto_origin_check: origin <> 'auto_session' OR learning_session_id IS NOT NULL
  • reminders_task_preset_check: preset NOT IN ('at_start','before_start_15','due_morning') OR task_id IS NOT NULL
  • reminders_session_lead_check: preset <> 'session_lead' OR origin = 'auto_session'
  • reminders_snooze_count_check: snooze_count BETWEEN 0 AND 1000
• Derived rule: a fired reminder is missed iff fired_at − remind_at > 24 hours; missed reminders are listed in the bell without a toast (Section 9).
• Limit: 10 reminders with status = 'pending' per target (task or learning session), counting both origins; enforced only on POST /api/v1/reminders; snoozing, editing and automatic sync are never blocked by it (Sections 5.9, 9.5.3, 9.5.7).
• Origin conversion: a successful PATCH /api/v1/reminders/:id on an auto_session reminder sets origin = 'user' and preset = 'custom' (Section 9.5.7); both CHECK constraints above stay satisfied.
• Completing or trashing a task sets every pending and fired reminder of that task to dismissed (Section 9.5.1).

5.5.10 availability_windows #

Recurring weekly available-time windows on a 15-minute grid. Replaced as a whole set by PUT /api/v1/availability; rows are never updated in place (no updated_at).

• Primary key: id. FK availability_windows_user_id_users_id_fk → users(id) ON DELETE CASCADE.
• Indexes: availability_windows_user_day_start_uq UNIQUE (user_id, weekday, start_minute).
• Checks: availability_windows_weekday_check: weekday BETWEEN 0 AND 6; availability_windows_start_check: start_minute BETWEEN 0 AND 1425 AND start_minute % 15 = 0; availability_windows_end_check: end_minute BETWEEN 15 AND 1440 AND end_minute % 15 = 0; availability_windows_length_check: end_minute - start_minute >= 15.
• 15-minute grid: the API rejects other values with 422 "Start and end times must be on a 15-minute mark." (Sections 6.7.10, 12.5); the CHECKs above are the database guard for the same rule.
• Overlap rule (application-validated, Section 12.5): two windows on the same weekday overlap iff a.start_minute < b.end_minute AND b.start_minute < a.end_minute. Touching windows (a.end_minute = b.start_minute) are allowed.
• Limit: 21 windows per user (5.9).

5.5.11 learning_sessions #

• Primary key: id. FK learning_sessions_user_id_users_id_fk → users(id) ON DELETE CASCADE.
• Indexes: learning_sessions_user_starts_idx (user_id, starts_at); learning_sessions_open_idx (user_id, ends_at) WHERE status IN ('planned','in_progress') — session status refresh (planned → missed, stale in_progress auto-close after 3 hours; Section 12.7.3).
• Checks: learning_sessions_duration_check: ends_at >= starts_at + interval '10 minutes' AND ends_at <= starts_at + interval '60 minutes'; learning_sessions_started_check: status NOT IN ('in_progress','completed') OR started_at IS NOT NULL; learning_sessions_completed_check: (status = 'completed') = (completed_at IS NOT NULL).
• Limit: 6 sessions (any source, any status except skipped and missed) starting on the same local date (5.9).

5.5.12 lesson_progress #

• Primary key: id. FK lesson_progress_user_id_users_id_fk → users(id) ON DELETE CASCADE.
• Indexes: lesson_progress_user_slug_uq UNIQUE (user_id, lesson_slug).
• Checks: lesson_progress_completed_check: (status = 'completed') = (completed_at IS NOT NULL).
• A completed lesson never returns to in_progress (content changes do not un-complete it; Section 10).

5.5.13 exercise_progress #

• Primary key: id. FK exercise_progress_user_id_users_id_fk → users(id) ON DELETE CASCADE.
• Indexes: exercise_progress_user_exercise_uq UNIQUE (user_id, exercise_id); exercise_progress_user_lesson_idx (user_id, lesson_slug).
• Checks: exercise_progress_draft_check: draft_code IS NULL OR char_length(draft_code) <= 20000; exercise_progress_attempts_check: attempts_count >= 0; exercise_progress_solved_check: (status IN ('solved','solved_with_help')) = (first_solved_at IS NOT NULL).
• Status rule: on the first passing attempt, status becomes solved_with_help if solution_viewed_at IS NOT NULL, else solved. Once solved, the status never changes (viewing the solution later keeps solved).

5.5.14 exercise_attempts #

• Primary key: id. FK exercise_attempts_user_id_users_id_fk → users(id) ON DELETE CASCADE.
• Indexes: exercise_attempts_user_exercise_created_idx (user_id, exercise_id, created_at DESC).
• Checks: exercise_attempts_code_check: char_length(code) <= 20000; exercise_attempts_tests_check: tests_total BETWEEN 1 AND 50 AND tests_passed BETWEEN 0 AND tests_total; exercise_attempts_passed_check: (passed AND tests_passed = tests_total AND error_kind IS NULL) OR (NOT passed AND error_kind IS NOT NULL); exercise_attempts_duration_check: duration_ms BETWEEN 0 AND 60000.
• Pruning: after each insert, in the same transaction, delete rows for the same (user_id, exercise_id) beyond the newest 50 by (created_at DESC, id DESC).

5.5.15 quiz_attempts #

• Primary key: id. FK quiz_attempts_user_id_users_id_fk → users(id) ON DELETE CASCADE.
• Indexes: quiz_attempts_user_quiz_created_idx (user_id, quiz_id, created_at DESC).
• Checks: quiz_attempts_counts_check: total_count BETWEEN 1 AND 20 AND correct_count BETWEEN 0 AND total_count; quiz_attempts_score_check: score_percent BETWEEN 0 AND 100; quiz_attempts_passed_check: passed = (score_percent >= 70).
• Retention: all attempts kept (unlimited retakes; best score = max(score_percent)).

5.5.16 activity_days #

One row per local date with learning activity. Upserted with INSERT … ON CONFLICT (user_id, local_date) DO UPDATE SET <counter> = activity_days.<counter> + 1, updated_at = now(). Which events increment which counter, and idempotency: Section 12.

• Primary key: (user_id, local_date). FK activity_days_user_id_users_id_fk → users(id) ON DELETE CASCADE.
• Indexes: PK only.
• Checks: activity_days_counters_check: lessons_completed >= 0 AND exercises_solved >= 0 AND quizzes_passed >= 0 AND sessions_completed >= 0.
• A row whose four counters are all 0 is never written. A date is a learning day iff its row exists.

5.5.17 ai_usage #

Accounting only. No prompt or response text is ever stored.

• Primary key: id. FK ai_usage_user_id_users_id_fk → users(id) ON DELETE CASCADE.
• Indexes: ai_usage_user_created_idx (user_id, created_at). "Used today" counts rows with created_at in [local midnight today, local midnight tomorrow) converted to UTC.
• Checks: ai_usage_tokens_check: input_tokens >= 0 AND output_tokens >= 0; ai_usage_latency_check: latency_ms IS NULL OR latency_ms >= 0.

5.6 Drizzle schema conventions #

1. The whole schema lives in src/server/db/schema.ts (single file; layout: Section 3.4). The connection pool (pg.Pool, max 10 connections) and the drizzle() instance live in src/server/db/client.ts.
2. Every enum's values are defined exactly once, as an as const tuple in src/lib/schemas/enums.ts (for example TASK_STATUS_VALUES), together with its TypeScript union type (export type TaskStatus = (typeof TASK_STATUS_VALUES)[number]). src/server/db/schema.ts passes the tuple to pgEnum and names the result with an Enum suffix (taskStatusEnum); the Zod schemas in src/lib/schemas/<domain>.ts pass the same tuple to z.enum (taskStatus = z.enum(TASK_STATUS_VALUES) in src/lib/schemas/tasks.ts). Because src/lib/schemas/ is shared with the client, it never imports from src/server/ (Section 4.3); there is never a second hand-written list of values.
3. timestamptz columns use timestamp(name, { withTimezone: true, mode: "date" }) (JavaScript Date, always UTC). date columns use date(name, { mode: "string" }) so local dates stay "YYYY-MM-DD" strings and never pass through a Date (which would shift across timezones).
4. citext is declared with customType<{ data: string }>({ dataType: () => "citext" }).
5. Partial-index WHERE clauses and CHECK expressions are written as raw sql strings with unqualified column names (e.g. sql`"deleted_at" IS NULL` ) so drizzle-kit emits exactly the SQL in 5.8.
6. Every constraint and index has the explicit name listed in 5.5.
7. Shared column helpers createdAt() and updatedAt() are defined once at the top of schema.ts.

Example (the tasks table, complete):

// src/server/db/schema.ts (excerpt)
import { sql } from "drizzle-orm";
import {
  pgEnum, pgTable, uuid, varchar, text, smallint, date, timestamp, index, check,
} from "drizzle-orm/pg-core";
import { TASK_STATUS_VALUES } from "@/lib/schemas/enums"; // ["todo","in_progress","blocked","done"] as const

export const taskStatusEnum = pgEnum("task_status", TASK_STATUS_VALUES);
// The union type TaskStatus is exported from src/lib/schemas/enums.ts, not from here.

const tsz = (name: string) => timestamp(name, { withTimezone: true, mode: "date" });
const createdAt = () => tsz("created_at").notNull().defaultNow();
const updatedAt = () => tsz("updated_at").notNull().defaultNow().$onUpdate(() => new Date());

export const tasks = pgTable(
  "tasks",
  {
    id: uuid("id").primaryKey().default(sql`uuidv7()`),
    userId: uuid("user_id").notNull().references(() => users.id, { onDelete: "cascade" }),
    title: varchar("title", { length: 200 }).notNull(),
    notes: text("notes"),
    status: taskStatusEnum("status").notNull().default("todo"),
    priority: smallint("priority").notNull().default(3),
    dueDate: date("due_date", { mode: "string" }),
    scheduledStart: tsz("scheduled_start"),
    estimateMinutes: smallint("estimate_minutes"),
    completedAt: tsz("completed_at"),
    deletedAt: tsz("deleted_at"),
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (t) => [
    index("tasks_user_status_idx").on(t.userId, t.status).where(sql`"deleted_at" IS NULL`),
    index("tasks_user_due_date_idx").on(t.userId, t.dueDate),
    index("tasks_user_scheduled_start_idx").on(t.userId, t.scheduledStart),
    index("tasks_user_trash_idx").on(t.userId, t.deletedAt).where(sql`"deleted_at" IS NOT NULL`),
    index("tasks_title_trgm_idx").using("gin", t.title.op("gin_trgm_ops")),
    check("tasks_title_check", sql`char_length("title") BETWEEN 1 AND 200 AND "title" = btrim("title")`),
    check("tasks_notes_check", sql`"notes" IS NULL OR char_length("notes") <= 10000`),
    check("tasks_priority_check", sql`"priority" BETWEEN 1 AND 4`),
    check("tasks_due_date_check",
      sql`"due_date" IS NULL OR "due_date" BETWEEN DATE '2000-01-01' AND DATE '2100-12-31'`),
    check("tasks_scheduled_start_check",
      sql`"scheduled_start" IS NULL OR ("scheduled_start" >= TIMESTAMPTZ '2000-01-01 00:00:00+00' AND "scheduled_start" < TIMESTAMPTZ '2101-01-01 00:00:00+00')`),
    check("tasks_estimate_check",
      sql`"estimate_minutes" IS NULL OR ("estimate_minutes" BETWEEN 5 AND 960 AND "estimate_minutes" % 5 = 0)`),
    check("tasks_completed_at_check", sql`("status" = 'done') = ("completed_at" IS NOT NULL)`),
  ],
);

export type TaskRow = typeof tasks.$inferSelect;
export type NewTaskRow = typeof tasks.$inferInsert;

drizzle.config.ts (repository root):

import { defineConfig } from "drizzle-kit";
export default defineConfig({
  dialect: "postgresql",
  schema: "./src/server/db/schema.ts",
  out: "./drizzle",
  dbCredentials: { url: process.env.DATABASE_URL! },
  strict: true,
  verbose: true,
});

5.7 Seed and setup data #

There is no seed script for production. All initial rows are inserted by POST /api/v1/setup (Section 6.7.1; flow: Section 7) inside one transaction; any failure rolls back everything and the instance stays in setup state.

After the transaction commits, the setup service calls scheduler.requestRun(userId, 'setup') (trigger setup, Section 12.6.10), which takes the per-user advisory lock and creates auto learning_sessions for today + 6 days and their session_lead reminders. A scheduler failure is logged at error and does not fail setup; the next trigger retries.

Seeded tags (ordinary tags: renamable, recolorable, deletable):

Seeded availability windows — Monday–Friday 19:30–21:00 is five windows, plus Saturday and Sunday 10:00–12:00, for seven windows in total:

Seed SQL equivalent (the service uses Drizzle inserts; this shows the exact values):

INSERT INTO tags (user_id, name, color) VALUES
  ($1,'schematic','blue'), ($1,'pcb-layout','violet'), ($1,'firmware','green'),
  ($1,'testing','amber'), ($1,'bring-up','red'), ($1,'documentation','slate'),
  ($1,'procurement','teal');

INSERT INTO availability_windows (user_id, weekday, start_minute, end_minute) VALUES
  ($1,1,1170,1260), ($1,2,1170,1260), ($1,3,1170,1260), ($1,4,1170,1260), ($1,5,1170,1260),
  ($1,6,600,720), ($1,0,600,720);

Development and test fixtures (factories) are defined in Section 17; they never run in production.

5.8 Initial migration DDL #

Two migration files make up the initial schema. 0000_extensions.sql is a custom migration (pnpm drizzle-kit generate --custom --name extensions, then filled in by hand); 0001_initial.sql is generated from schema.ts (pnpm drizzle-kit generate --name initial). drizzle-kit inserts --> statement-breakpoint lines between statements and may order statements differently or quote identifiers; that is acceptable. What must match exactly is the resulting schema: every name, type, nullability, default, constraint and index below. Section 17 includes an integration test that compares information_schema/pg_catalog against this list.

-- ===================== drizzle/0000_extensions.sql =====================
CREATE EXTENSION IF NOT EXISTS citext;
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- ===================== drizzle/0001_initial.sql ========================
-- Enums
CREATE TYPE theme_pref        AS ENUM ('system', 'light', 'dark');
CREATE TYPE login_event_type  AS ENUM ('login_success', 'login_failure', 'logout', 'lockout',
                                       'password_changed', 'recovery_used', 'recovery_regenerated',
                                       'session_revoked');
CREATE TYPE tag_color         AS ENUM ('slate', 'red', 'orange', 'amber', 'green', 'teal', 'blue', 'violet');
CREATE TYPE task_status       AS ENUM ('todo', 'in_progress', 'blocked', 'done');
CREATE TYPE plan_source       AS ENUM ('manual', 'ai');
CREATE TYPE reminder_status   AS ENUM ('pending', 'fired', 'dismissed');
CREATE TYPE reminder_origin   AS ENUM ('user', 'auto_session');
CREATE TYPE reminder_preset   AS ENUM ('at_start', 'before_start_15', 'due_morning', 'custom', 'session_lead');
CREATE TYPE session_status    AS ENUM ('planned', 'in_progress', 'completed', 'skipped', 'missed');
CREATE TYPE session_source    AS ENUM ('auto', 'manual');
CREATE TYPE lesson_status     AS ENUM ('in_progress', 'completed');
CREATE TYPE exercise_status   AS ENUM ('attempted', 'solved', 'solved_with_help');
CREATE TYPE run_error_kind    AS ENUM ('syntax', 'runtime', 'timeout', 'output_limit', 'assertion', 'worker_crash');
CREATE TYPE ai_request_kind   AS ENUM ('explain_code', 'follow_up', 'suggest_priorities');
CREATE TYPE ai_request_status AS ENUM ('ok', 'error', 'refused_limit');

-- users
CREATE TABLE users (
  id                  uuid        NOT NULL DEFAULT uuidv7(),
  email               citext      NOT NULL,
  password_hash       text        NOT NULL,
  display_name        varchar(60) NOT NULL,
  recovery_code_hash  text        NOT NULL,
  failed_login_count  integer     NOT NULL DEFAULT 0,
  locked_until        timestamptz,
  password_changed_at timestamptz NOT NULL DEFAULT now(),
  created_at          timestamptz NOT NULL DEFAULT now(),
  updated_at          timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT users_pkey PRIMARY KEY (id),
  CONSTRAINT users_email_uq UNIQUE (email),
  CONSTRAINT users_email_check CHECK (char_length(email::text) BETWEEN 3 AND 254
                                      AND email::text = lower(btrim(email::text))),
  CONSTRAINT users_display_name_check CHECK (char_length(display_name) BETWEEN 1 AND 60
                                             AND display_name = btrim(display_name)),
  CONSTRAINT users_failed_login_count_check CHECK (failed_login_count >= 0)
);
CREATE UNIQUE INDEX users_singleton_uq ON users ((true));

-- user_settings
CREATE TABLE user_settings (
  user_id                       uuid        NOT NULL,
  timezone                      text        NOT NULL DEFAULT 'UTC',
  week_starts_on                smallint    NOT NULL DEFAULT 1,
  theme                         theme_pref  NOT NULL DEFAULT 'system',
  learning_session_minutes      smallint    NOT NULL DEFAULT 20,
  weekly_session_target         smallint    NOT NULL DEFAULT 4,
  max_sessions_per_day          smallint    NOT NULL DEFAULT 1,
  auto_schedule                 boolean     NOT NULL DEFAULT true,
  session_reminder_lead_minutes smallint    NOT NULL DEFAULT 10,
  ai_enabled                    boolean     NOT NULL DEFAULT true,
  ai_include_task_titles        boolean     NOT NULL DEFAULT true,
  created_at                    timestamptz NOT NULL DEFAULT now(),
  updated_at                    timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT user_settings_pkey PRIMARY KEY (user_id),
  CONSTRAINT user_settings_user_id_users_id_fk FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,
  CONSTRAINT user_settings_timezone_check CHECK (char_length(timezone) BETWEEN 1 AND 64),
  CONSTRAINT user_settings_week_starts_on_check CHECK (week_starts_on BETWEEN 0 AND 6),
  CONSTRAINT user_settings_session_minutes_check CHECK (learning_session_minutes BETWEEN 10 AND 60
                                                        AND learning_session_minutes % 5 = 0),
  CONSTRAINT user_settings_weekly_target_check CHECK (weekly_session_target BETWEEN 0 AND 14),
  CONSTRAINT user_settings_max_per_day_check CHECK (max_sessions_per_day BETWEEN 1 AND 3),
  CONSTRAINT user_settings_reminder_lead_check CHECK (session_reminder_lead_minutes BETWEEN 0 AND 120
                                                      AND session_reminder_lead_minutes % 5 = 0)
);

-- sessions (login sessions)
CREATE TABLE sessions (
  id                  text         NOT NULL,
  user_id             uuid         NOT NULL,
  persistent          boolean      NOT NULL DEFAULT false,
  created_at          timestamptz  NOT NULL DEFAULT now(),
  last_seen_at        timestamptz  NOT NULL DEFAULT now(),
  idle_expires_at     timestamptz  NOT NULL,
  absolute_expires_at timestamptz  NOT NULL,
  user_agent          varchar(400),
  ip_prefix           varchar(64),
  CONSTRAINT sessions_pkey PRIMARY KEY (id),
  CONSTRAINT sessions_user_id_users_id_fk FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,
  CONSTRAINT sessions_id_check CHECK (id ~ '^[0-9a-f]{64}$'),
  CONSTRAINT sessions_expiry_check CHECK (idle_expires_at <= absolute_expires_at)
);
CREATE INDEX sessions_user_id_idx ON sessions (user_id);
CREATE INDEX sessions_idle_expires_at_idx ON sessions (idle_expires_at);

-- login_events
CREATE TABLE login_events (
  id         uuid             NOT NULL DEFAULT uuidv7(),
  user_id    uuid,
  event      login_event_type NOT NULL,
  ip_prefix  varchar(64),
  user_agent varchar(400),
  created_at timestamptz      NOT NULL DEFAULT now(),
  CONSTRAINT login_events_pkey PRIMARY KEY (id),
  CONSTRAINT login_events_user_id_users_id_fk FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
);
CREATE INDEX login_events_created_at_idx ON login_events (created_at);
CREATE INDEX login_events_user_created_idx ON login_events (user_id, created_at);

-- tags
CREATE TABLE tags (
  id         uuid        NOT NULL DEFAULT uuidv7(),
  user_id    uuid        NOT NULL,
  name       varchar(32) NOT NULL,
  color      tag_color   NOT NULL DEFAULT 'slate',
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT tags_pkey PRIMARY KEY (id),
  CONSTRAINT tags_user_id_users_id_fk FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,
  CONSTRAINT tags_name_check CHECK (char_length(name) BETWEEN 1 AND 32 AND name = btrim(name))
);
CREATE UNIQUE INDEX tags_user_name_uq ON tags (user_id, lower(name));

-- tasks
CREATE TABLE tasks (
  id               uuid         NOT NULL DEFAULT uuidv7(),
  user_id          uuid         NOT NULL,
  title            varchar(200) NOT NULL,
  notes            text,
  status           task_status  NOT NULL DEFAULT 'todo',
  priority         smallint     NOT NULL DEFAULT 3,
  due_date         date,
  scheduled_start  timestamptz,
  estimate_minutes smallint,
  completed_at     timestamptz,
  deleted_at       timestamptz,
  created_at       timestamptz  NOT NULL DEFAULT now(),
  updated_at       timestamptz  NOT NULL DEFAULT now(),
  CONSTRAINT tasks_pkey PRIMARY KEY (id),
  CONSTRAINT tasks_user_id_users_id_fk FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,
  CONSTRAINT tasks_title_check CHECK (char_length(title) BETWEEN 1 AND 200 AND title = btrim(title)),
  CONSTRAINT tasks_notes_check CHECK (notes IS NULL OR char_length(notes) <= 10000),
  CONSTRAINT tasks_priority_check CHECK (priority BETWEEN 1 AND 4),
  CONSTRAINT tasks_due_date_check CHECK (due_date IS NULL
                                         OR due_date BETWEEN DATE '2000-01-01' AND DATE '2100-12-31'),
  CONSTRAINT tasks_scheduled_start_check CHECK (scheduled_start IS NULL
                                                OR (scheduled_start >= TIMESTAMPTZ '2000-01-01 00:00:00+00'
                                                    AND scheduled_start < TIMESTAMPTZ '2101-01-01 00:00:00+00')),
  CONSTRAINT tasks_estimate_check CHECK (estimate_minutes IS NULL
                                         OR (estimate_minutes BETWEEN 5 AND 960 AND estimate_minutes % 5 = 0)),
  CONSTRAINT tasks_completed_at_check CHECK ((status = 'done') = (completed_at IS NOT NULL))
);
CREATE INDEX tasks_user_status_idx ON tasks (user_id, status) WHERE deleted_at IS NULL;
CREATE INDEX tasks_user_due_date_idx ON tasks (user_id, due_date);
CREATE INDEX tasks_user_scheduled_start_idx ON tasks (user_id, scheduled_start);
CREATE INDEX tasks_user_trash_idx ON tasks (user_id, deleted_at) WHERE deleted_at IS NOT NULL;
CREATE INDEX tasks_title_trgm_idx ON tasks USING gin (title gin_trgm_ops);

-- task_tags
CREATE TABLE task_tags (
  task_id    uuid        NOT NULL,
  tag_id     uuid        NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT task_tags_pkey PRIMARY KEY (task_id, tag_id),
  CONSTRAINT task_tags_task_id_tasks_id_fk FOREIGN KEY (task_id) REFERENCES tasks (id) ON DELETE CASCADE,
  CONSTRAINT task_tags_tag_id_tags_id_fk FOREIGN KEY (tag_id) REFERENCES tags (id) ON DELETE CASCADE
);
CREATE INDEX task_tags_tag_id_idx ON task_tags (tag_id);

-- daily_plans
CREATE TABLE daily_plans (
  user_id          uuid        NOT NULL,
  local_date       date        NOT NULL,
  ordered_task_ids uuid[]      NOT NULL DEFAULT '{}',
  source           plan_source NOT NULL DEFAULT 'manual',
  created_at       timestamptz NOT NULL DEFAULT now(),
  updated_at       timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT daily_plans_pkey PRIMARY KEY (user_id, local_date),
  CONSTRAINT daily_plans_user_id_users_id_fk FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,
  CONSTRAINT daily_plans_ids_check CHECK (cardinality(ordered_task_ids) <= 500)
);

-- availability_windows
CREATE TABLE availability_windows (
  id           uuid        NOT NULL DEFAULT uuidv7(),
  user_id      uuid        NOT NULL,
  weekday      smallint    NOT NULL,
  start_minute smallint    NOT NULL,
  end_minute   smallint    NOT NULL,
  created_at   timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT availability_windows_pkey PRIMARY KEY (id),
  CONSTRAINT availability_windows_user_id_users_id_fk FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,
  CONSTRAINT availability_windows_weekday_check CHECK (weekday BETWEEN 0 AND 6),
  CONSTRAINT availability_windows_start_check CHECK (start_minute BETWEEN 0 AND 1425 AND start_minute % 15 = 0),
  CONSTRAINT availability_windows_end_check CHECK (end_minute BETWEEN 15 AND 1440 AND end_minute % 15 = 0),
  CONSTRAINT availability_windows_length_check CHECK (end_minute - start_minute >= 15)
);
CREATE UNIQUE INDEX availability_windows_user_day_start_uq ON availability_windows (user_id, weekday, start_minute);

-- learning_sessions
CREATE TABLE learning_sessions (
  id           uuid           NOT NULL DEFAULT uuidv7(),
  user_id      uuid           NOT NULL,
  starts_at    timestamptz    NOT NULL,
  ends_at      timestamptz    NOT NULL,
  status       session_status NOT NULL DEFAULT 'planned',
  source       session_source NOT NULL,
  lesson_slug  varchar(80),
  started_at   timestamptz,
  completed_at timestamptz,
  created_at   timestamptz    NOT NULL DEFAULT now(),
  updated_at   timestamptz    NOT NULL DEFAULT now(),
  CONSTRAINT learning_sessions_pkey PRIMARY KEY (id),
  CONSTRAINT learning_sessions_user_id_users_id_fk FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,
  CONSTRAINT learning_sessions_duration_check CHECK (ends_at >= starts_at + interval '10 minutes'
                                                     AND ends_at <= starts_at + interval '60 minutes'),
  CONSTRAINT learning_sessions_started_check CHECK (status NOT IN ('in_progress', 'completed')
                                                    OR started_at IS NOT NULL),
  CONSTRAINT learning_sessions_completed_check CHECK ((status = 'completed') = (completed_at IS NOT NULL))
);
CREATE INDEX learning_sessions_user_starts_idx ON learning_sessions (user_id, starts_at);
CREATE INDEX learning_sessions_open_idx ON learning_sessions (user_id, ends_at)
  WHERE status IN ('planned', 'in_progress');

-- reminders (after tasks and learning_sessions because of FKs)
CREATE TABLE reminders (
  id                  uuid            NOT NULL DEFAULT uuidv7(),
  user_id             uuid            NOT NULL,
  task_id             uuid,
  learning_session_id uuid,
  remind_at           timestamptz     NOT NULL,
  status              reminder_status NOT NULL DEFAULT 'pending',
  origin              reminder_origin NOT NULL DEFAULT 'user',
  preset              reminder_preset NOT NULL DEFAULT 'custom',
  fired_at            timestamptz,
  dismissed_at        timestamptz,
  snooze_count        smallint        NOT NULL DEFAULT 0,
  created_at          timestamptz     NOT NULL DEFAULT now(),
  updated_at          timestamptz     NOT NULL DEFAULT now(),
  CONSTRAINT reminders_pkey PRIMARY KEY (id),
  CONSTRAINT reminders_user_id_users_id_fk FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,
  CONSTRAINT reminders_task_id_tasks_id_fk FOREIGN KEY (task_id) REFERENCES tasks (id) ON DELETE CASCADE,
  CONSTRAINT reminders_learning_session_id_learning_sessions_id_fk FOREIGN KEY (learning_session_id)
    REFERENCES learning_sessions (id) ON DELETE CASCADE,
  CONSTRAINT reminders_target_check CHECK (num_nonnulls(task_id, learning_session_id) = 1),
  CONSTRAINT reminders_fired_check CHECK (status <> 'fired' OR fired_at IS NOT NULL),
  CONSTRAINT reminders_dismissed_check CHECK (status <> 'dismissed' OR dismissed_at IS NOT NULL),
  CONSTRAINT reminders_auto_origin_check CHECK (origin <> 'auto_session' OR learning_session_id IS NOT NULL),
  CONSTRAINT reminders_task_preset_check CHECK (preset NOT IN ('at_start', 'before_start_15', 'due_morning')
                                                OR task_id IS NOT NULL),
  CONSTRAINT reminders_session_lead_check CHECK (preset <> 'session_lead' OR origin = 'auto_session'),
  CONSTRAINT reminders_snooze_count_check CHECK (snooze_count BETWEEN 0 AND 1000)
);
CREATE INDEX reminders_user_status_remind_idx ON reminders (user_id, status, remind_at);
CREATE INDEX reminders_task_id_idx ON reminders (task_id) WHERE task_id IS NOT NULL;
CREATE INDEX reminders_learning_session_id_idx ON reminders (learning_session_id)
  WHERE learning_session_id IS NOT NULL;
CREATE UNIQUE INDEX reminders_session_auto_uq ON reminders (learning_session_id)
  WHERE origin = 'auto_session';

-- lesson_progress
CREATE TABLE lesson_progress (
  id             uuid          NOT NULL DEFAULT uuidv7(),
  user_id        uuid          NOT NULL,
  lesson_slug    varchar(80)   NOT NULL,
  status         lesson_status NOT NULL DEFAULT 'in_progress',
  started_at     timestamptz   NOT NULL DEFAULT now(),
  completed_at   timestamptz,
  last_viewed_at timestamptz   NOT NULL DEFAULT now(),
  created_at     timestamptz   NOT NULL DEFAULT now(),
  updated_at     timestamptz   NOT NULL DEFAULT now(),
  CONSTRAINT lesson_progress_pkey PRIMARY KEY (id),
  CONSTRAINT lesson_progress_user_id_users_id_fk FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,
  CONSTRAINT lesson_progress_completed_check CHECK ((status = 'completed') = (completed_at IS NOT NULL))
);
CREATE UNIQUE INDEX lesson_progress_user_slug_uq ON lesson_progress (user_id, lesson_slug);

-- exercise_progress
CREATE TABLE exercise_progress (
  id                 uuid            NOT NULL DEFAULT uuidv7(),
  user_id            uuid            NOT NULL,
  exercise_id        varchar(80)     NOT NULL,
  lesson_slug        varchar(80)     NOT NULL,
  status             exercise_status NOT NULL DEFAULT 'attempted',
  draft_code         text,
  attempts_count     integer         NOT NULL DEFAULT 0,
  first_solved_at    timestamptz,
  solution_viewed_at timestamptz,
  created_at         timestamptz     NOT NULL DEFAULT now(),
  updated_at         timestamptz     NOT NULL DEFAULT now(),
  CONSTRAINT exercise_progress_pkey PRIMARY KEY (id),
  CONSTRAINT exercise_progress_user_id_users_id_fk FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,
  CONSTRAINT exercise_progress_draft_check CHECK (draft_code IS NULL OR char_length(draft_code) <= 20000),
  CONSTRAINT exercise_progress_attempts_check CHECK (attempts_count >= 0),
  CONSTRAINT exercise_progress_solved_check CHECK ((status IN ('solved', 'solved_with_help'))
                                                   = (first_solved_at IS NOT NULL))
);
CREATE UNIQUE INDEX exercise_progress_user_exercise_uq ON exercise_progress (user_id, exercise_id);
CREATE INDEX exercise_progress_user_lesson_idx ON exercise_progress (user_id, lesson_slug);

-- exercise_attempts
CREATE TABLE exercise_attempts (
  id           uuid           NOT NULL DEFAULT uuidv7(),
  user_id      uuid           NOT NULL,
  exercise_id  varchar(80)    NOT NULL,
  lesson_slug  varchar(80)    NOT NULL,
  code         text           NOT NULL,
  passed       boolean        NOT NULL,
  tests_passed smallint       NOT NULL,
  tests_total  smallint       NOT NULL,
  error_kind   run_error_kind,
  duration_ms  integer        NOT NULL,
  created_at   timestamptz    NOT NULL DEFAULT now(),
  CONSTRAINT exercise_attempts_pkey PRIMARY KEY (id),
  CONSTRAINT exercise_attempts_user_id_users_id_fk FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,
  CONSTRAINT exercise_attempts_code_check CHECK (char_length(code) <= 20000),
  CONSTRAINT exercise_attempts_tests_check CHECK (tests_total BETWEEN 1 AND 50
                                                  AND tests_passed BETWEEN 0 AND tests_total),
  CONSTRAINT exercise_attempts_passed_check CHECK ((passed AND tests_passed = tests_total AND error_kind IS NULL)
                                                   OR (NOT passed AND error_kind IS NOT NULL)),
  CONSTRAINT exercise_attempts_duration_check CHECK (duration_ms BETWEEN 0 AND 60000)
);
CREATE INDEX exercise_attempts_user_exercise_created_idx
  ON exercise_attempts (user_id, exercise_id, created_at DESC);

-- quiz_attempts
CREATE TABLE quiz_attempts (
  id            uuid        NOT NULL DEFAULT uuidv7(),
  user_id       uuid        NOT NULL,
  quiz_id       varchar(80) NOT NULL,
  lesson_slug   varchar(80) NOT NULL,
  answers       jsonb       NOT NULL,
  correct_count smallint    NOT NULL,
  total_count   smallint    NOT NULL,
  score_percent smallint    NOT NULL,
  passed        boolean     NOT NULL,
  created_at    timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT quiz_attempts_pkey PRIMARY KEY (id),
  CONSTRAINT quiz_attempts_user_id_users_id_fk FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,
  CONSTRAINT quiz_attempts_counts_check CHECK (total_count BETWEEN 1 AND 20
                                               AND correct_count BETWEEN 0 AND total_count),
  CONSTRAINT quiz_attempts_score_check CHECK (score_percent BETWEEN 0 AND 100),
  CONSTRAINT quiz_attempts_passed_check CHECK (passed = (score_percent >= 70))
);
CREATE INDEX quiz_attempts_user_quiz_created_idx ON quiz_attempts (user_id, quiz_id, created_at DESC);

-- activity_days
CREATE TABLE activity_days (
  user_id            uuid        NOT NULL,
  local_date         date        NOT NULL,
  lessons_completed  smallint    NOT NULL DEFAULT 0,
  exercises_solved   smallint    NOT NULL DEFAULT 0,
  quizzes_passed     smallint    NOT NULL DEFAULT 0,
  sessions_completed smallint    NOT NULL DEFAULT 0,
  created_at         timestamptz NOT NULL DEFAULT now(),
  updated_at         timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT activity_days_pkey PRIMARY KEY (user_id, local_date),
  CONSTRAINT activity_days_user_id_users_id_fk FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,
  CONSTRAINT activity_days_counters_check CHECK (lessons_completed >= 0 AND exercises_solved >= 0
                                                 AND quizzes_passed >= 0 AND sessions_completed >= 0)
);

-- ai_usage
CREATE TABLE ai_usage (
  id            uuid              NOT NULL DEFAULT uuidv7(),
  user_id       uuid              NOT NULL,
  kind          ai_request_kind   NOT NULL,
  status        ai_request_status NOT NULL,
  input_tokens  integer           NOT NULL DEFAULT 0,
  output_tokens integer           NOT NULL DEFAULT 0,
  latency_ms    integer,
  created_at    timestamptz       NOT NULL DEFAULT now(),
  CONSTRAINT ai_usage_pkey PRIMARY KEY (id),
  CONSTRAINT ai_usage_user_id_users_id_fk FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,
  CONSTRAINT ai_usage_tokens_check CHECK (input_tokens >= 0 AND output_tokens >= 0),
  CONSTRAINT ai_usage_latency_check CHECK (latency_ms IS NULL OR latency_ms >= 0)
);
CREATE INDEX ai_usage_user_created_idx ON ai_usage (user_id, created_at);