Elijah/Study
1
0
Fork 0
Code Issues Pull requests Projects Releases Packages 1 Wiki Activity Actions
Study/audit-results/EXECUTIVE_SUMMARY.md

7.8 KiB
Raw Permalink Blame History

Study Desk — Overnight Read-Only Audit: Executive Summary

Status: COMPLETE (2026-08-06)

Scope and method

Comprehensive read-only audit of the Study repository (Next.js 16 / React 19 / Prisma 7 / SQLite, self-hosted single-user study app). Ten specialist subagents audited: (1) Prisma schema/migrations/transactions/cascades/drift, (2) quiz scoring/attempts/retakes/progress, (3) flashcards/SRS/progress/ordering, (4) auth/protected routes/API authorization/sharing, (5) imports/exports/generation/Zod validation, (6) material groups/library/drag-drop/deletion, (7) frontend state/races/localStorage/boundaries, (8) test quality, (9) Docker/env/production/recovery, (10) adversarial review challenging every Critical/High finding and hunting missed issues. The parent agent independently re-verified all Critical/High claims (code tracing, git history, temp-DB migration chain, DB introspection, Prisma-shaped SQL probes). The Arcade feature set was excluded per scope unless it affects the main app, shared dependencies, database integrity, build, or deployment.

Constraints honored

  • No source code, tests, migrations, config, package files, lockfiles, docs, or databases modified. Final git status --short: only untracked .reasonix/ and audit-results/; git diff --check clean; src/generated/prisma/ byte-identical before/after all prisma commands (sha256).
  • All reports and diagnostic artifacts live under audit-results/ (reports + tmp/: audit-migration.db, inspect-db.cjs, reproduce-drift.cjs, validate-findings.cjs, generated-before.sha256).
  • Existing databases: none found in the workspace; the migration-chain test used a brand-new temp DB.

Baseline verification

Check Result
npm test ✅ PASS — 30 tests / 6 files (20 arcade out of scope, 10 spacedRepetition)
npm run lint ⚠️ 28 problems (9 errors, 19 warnings) — all pre-existing baseline
npm run build ✅ PASS — full route table incl. "ƒ Proxy (Middleware)"
npx prisma validate ✅ PASS
Migration chain on fresh temp DB ✅ PASS — 4/4 migrations apply, but the resulting DB is missing MaterialGroup + groupId columns (drift, see below)

Findings (53 total: 2 Critical, 5 High, 18 Medium, 25 Low, 3 Observation)

Full detail in FINDINGS.json (validated) and CONFIRMED_FINDINGS.md.

Critical

  1. FIN-01 — Schema/migration drift (Confirmed, runtime-verified). prisma/schema.prisma defines MaterialGroup and Deck/QuizSet/ShareLink.groupId (added by commit 7af0935, which also rewrote a committed dev.db via prisma db push) but no migration creates them. Every DB built by prisma migrate deploy — the predev hook and the Docker entrypoint — lacks these objects; the generated client then fails every Deck/QuizSet/ShareLink/MaterialGroup query (no such column: Deck.groupId, no such table: MaterialGroup — reproduced against a temp DB). Any fresh install/deploy is non-functional. The app only works on the developer's db-push-synced local database.
  2. FIN-02 — Hardcoded session-secret fallback (High-confidence inference). src/lib/auth.ts:15 and src/proxy.ts:8-9 fall back to the public constant "dev-session-secret-change-in-production-must-be-32-chars" whenever SESSION_SECRET is unset — and the shipped docker-compose.yml passes ${SESSION_SECRET}, which is empty by default. Anyone with the source can forge an iron-session cookie ({isAuthenticated:true, sessionGeneration:1}) and fully bypass authentication on such deployments.

High

  1. FIN-03 — "Retake Missed" persists as a full attempt with a wrong lower score (in-viewer retake never sets the retakeIds prop; server scores all unanswered questions as 0) and pollutes SEQUENTIAL progress.
  2. FIN-04 — Deleting a card mid-session bricks the flashcard study session (filterAndClampOrder is dead code; resume never filters stale ids; no skip UI).
  3. FIN-05 — Production compose is unreachable: container listens on 3726 (ENV PORT=3726), compose publishes 3000:3000.
  4. FIN-06 — No .dockerignore: Windows-host builds inject win32 native modules (better-sqlite3, argon2) into the Linux image.
  5. FIN-07 — docker-compose.override.yml auto-merges: plain docker compose up silently runs dev mode in production.

Medium (selected)

First-login provisioning takeover + dead ADMIN_PASSWORD_HASH (FIN-08); proxy auth bypass for dot-containing paths (FIN-09); password-reset abuse (FIN-10); unvalidated attempt answersJson (FIN-11); finish double-submit duplicates attempts (FIN-12); progress PATCH/DELETE race (FIN-13); unsequenced progress autosaves (FIN-14); completed session resumes on last card (FIN-15); SRS review endpoint lacks due/new-card-limit enforcement (FIN-16); unbounded DB scans (FIN-17); library optimistic mutations without rollback (FIN-18); unvalidated groupId/sortOrder incl. cross-class group membership (FIN-19); shared-quiz reshuffle on re-render (FIN-20); unvalidated /api/progress + unguarded JSON.parse crashes (FIN-21); entrypoint migrate fragility (FIN-22); non-reproducible installs (FIN-23); no healthcheck/backup (FIN-24); CI ships without tests/lint/prisma/smoke gates (FIN-25).

Low / Observation

25 Low (e.g. SATA div-by-zero NaN, historical score recompute, card-edit endpoints without Zod, no import size caps, sortOrder duplicates after group deletion, keyboard cross-group moves impossible, restart DELETE race, P2002 → 500, git history containing dev.db, .gitignore gaps, dashboard fetch crash, empty slug from slugify, shared-viewer localStorage key collisions) and 3 Observations (group sortOrder desc convention, collapse flash, shared quizzes ship the answer key by design).

Verification highlights

  • FIN-01 disproved attempts: checked whether the client avoids selecting groupId (it doesn't — generated Deck.ts includes it in every payload), whether pages swallow errors (they don't), and whether any migration creates the objects (grep = 0 hits). Confirmed via live probes instead: see audit-results/tmp/reproduce-drift.cjs output in VERIFICATION_LOG.md.
  • Adversarial review refuted the frontend auditor's "no auth on API routes" claim (proxy is compiled middleware — verified in .next artifacts), weakened the entrypoint crash-loop framing (fresh-volume migrate deploy succeeds), and downgraded the optimistic-reorder severity. It independently confirmed all other Critical/High findings and added 10 missed issues, merged into the final list.

Repository areas NOT fully audited

  1. Arcade feature internals (out of scope; shared-DB/build/deploy impact only — verified in migrations).
  2. Real browser interaction (no browser available): drag-drop, resume flows, share pages, keyboard a11y, themes — code-verified; flagged for runtime confirmation in UNVERIFIED_RISKS.md.
  3. Container build/run (docker CLI unavailable): FIN-06, FIN-22, FIN-24 need a container run.
  4. Deep git-history diff of every commit; full content audit of the committed dev.db blobs (no sqlite3 CLI; no credentials found in the checked blobs).
  5. Runtime performance on a populated database (FIN-17 not measured).
  6. CI/remote execution (Forgejo pipeline analyzed statically).

Recommended priority (when fixes are authorized)

  1. FIN-01 — generate and commit the missing migration (prisma migrate dev --name add_material_groups); add a CI drift gate. Everything else depends on a working fresh deployment.
  2. FIN-02 / FIN-05 / FIN-06 / FIN-07 — harden the deployment path: require SESSION_SECRET, fix the port mapping, add .dockerignore, rename the dev override, and smoke-test the image in CI.
  3. FIN-03 / FIN-04 — the two most user-visible application bugs (wrong retake scores; stuck flashcard sessions).
  4. Then the Medium cluster (validation, races, SRS enforcement, progress handling) and finally Low items + TEST_GAPS.md.