Database Schema¶

Audience: Developers writing queries, migrations, or admin tooling.

Scope: The Kysely Database interface, key tables, indexes, and migration workflow. Source of truth is src/db/types.ts and the migrations under src/db/migrations/.

Engines¶

Production: PostgreSQL.
Local development & tests: SQLite via better-sqlite3. Use DATA_DIR=./data/test for isolated end-to-end test state.
Dialect picker: src/db/client.ts — driven by DATABASE_URL (sqlite:./... vs postgres://...).

Kysely is not an ORM. There is no schema codegen — the handwritten Database interface is updated alongside every migration in the same commit.

Tables (V1)¶

Table	Purpose
`tenants`	Tenant identity, status (`active`/`suspended`/`archived`), feature flags JSON, custom domain.
`users`	Auth identity. `tenant_id` nullable only for `super_admin`. `role ∈ { student, course_admin, tenant_admin, super_admin }`.
`oauth_accounts`	OAuth provider links (Google). Composite key `(provider, provider_account_id)`.
`course_admin_assignments`	Per-course scope for the `course_admin` role. Unique on `(user_id, tenant_id, course_slug)`.
`course_settings`	Per-course LMS flags (`feature_flags_json`), keyed by `(tenant_id, course_slug)`. See `reference/course-configuration.md`.
`invitations`	Email-based invitation flow with `token_hash`, `expires_at`, optional `course_slug` for course-admin invites.
`enrollments`	`status ∈ { pending, active, completed, cancelled }`. Unique on `(tenant_id, user_id, course_slug)`.
`entry_progress`	Per-entry completion + `last_viewed_at` for resume. Unique on `(user_id, tenant_id, course_slug, entry_slug)`.
`quiz_attempts`	Per-entry attempts with sequential `attempt_number`, server-graded `answers_json` and `feedback_json`.
`verification_tokens`	Single-use tokens for email verify, password reset, magic-link, invitation acceptance, super-admin impersonation. Hashed at rest.
`email_events`	Reserved email event/audit shape. The table exists, but no application reads or writes it yet.
`audit_log`	Audit trail for impersonation, role changes, tenant mutations, enrollment transitions, course-admin assignments.

There are no sessions or accounts tables — Auth.js uses the JWT strategy.

Indexes (V1)¶

Table	Index
`users`	unique `(tenant_id, email)`
`enrollments`	unique `(tenant_id, user_id, course_slug)`; covering `(tenant_id, course_slug, status)` for rosters
`entry_progress`	unique `(user_id, tenant_id, course_slug, entry_slug)`
`quiz_attempts`	`(user_id, tenant_id, course_slug, entry_slug, attempt_number DESC)`
`course_admin_assignments`	unique `(user_id, tenant_id, course_slug)`
`verification_tokens`	unique on `token_hash`; index on `(user_id, type)`
`email_events`	unique `(tenant_id, user_id, event, subject_id)`; currently unused by application code
`audit_log`	index on `(tenant_id, created_at DESC)` and `(actor_user_id, created_at DESC)`

users.preferences stores LMS notification settings as flat JSON with courseActivity and marketing booleans. Both default to true. The shared canSend utility exists, but no optional notification sends enforce these preferences yet. See Email System.

Migrations¶

Migrations are numbered TypeScript files in src/db/migrations/. Each migration implements both up and down. Run with:

npm run db:migrate            # apply pending migrations
npm run db:migrate:make       # scaffold a new migration
npm run db:seed               # seed dev data (tenant, admin, course)

Whenever you change a table:

Add a migration under src/db/migrations/.
Update src/db/types.ts in the same commit.
Update query helpers in src/db/queries/ and any affected Zod schemas in src/schemas/.
Update reference docs (this page, reference/user-management.md, reference/tenant-configuration.md, reference/course-enrollment-system.md, reference/quiz-system.md, reference/entry-pagination.md, or reference/analytics.md) when behaviour changes.

Migrations must run portably against both PostgreSQL and SQLite. Helper utilities live in src/db/migrations/_helpers.ts.

Development seed data¶

Run npm run db:seed after npm run db:migrate on a fresh local or staging database. Seeding is manual — it does not run on npm run dev or db:migrate. Use it for development and staging refreshes only; do not treat the default password as a production secret.

Implementation lives in scripts/db-seed.ts. Upserts are idempotent (matched on email + tenant_id). Every seeded user gets email_verified_at set so credentials sign-in works immediately.

Seeded tenant¶

Slug	Name	Status
`demo`	Demo Tenant	`active`

Seeded users¶

Shared password (all accounts, dev only): set SEED_DEMO_PASSWORD in .env.local.

Account fixture	Display name	Role	Tenant slug	Sign-in host (`APP_BASE_DOMAIN=lvh.me:3001`)
Platform admin	Super Admin	`super_admin`	— (`tenant_id` null)	Apex: `http://lvh.me:3001`
Demo tenant admin	Demo Tenant Admin	`tenant_admin`	`demo`	`http://demo.lvh.me:3001`
Demo course admin	Demo Course Admin	`course_admin`	`demo`	`http://demo.lvh.me:3001`
Demo student one	Student One	`student`	`demo`	`http://demo.lvh.me:3001`
Demo student two	Student Two	`student`	`demo`	`http://demo.lvh.me:3001`

Concrete seeded email identifiers are intentionally omitted from this document. Use scripts/db-seed.ts for the current fixture values.

Tenant-scoped sign-in resolves the tenant from the request host, not the form. Set APP_BASE_DOMAIN in .env.local.template (default lvh.me:3001) so <tenant>.lvh.me subdomains work without /etc/hosts edits. See Multi-Tenant Administration for host resolution.

If you sign in with a platform admin email on a tenant subdomain such as demo.lvh.me, the sign-in form shows a dedicated message with a link to the apex URL (http://<APP_BASE_DOMAIN>/learn/sign-in) when the password is correct but the host is wrong. A wrong password still shows the generic invalid-credentials message.

Also seeded¶

Table	Details
`course_admin_assignments`	The demo course-admin fixture is assigned to course slug `demo-course` (assigned by the demo tenant admin).

Not seeded¶

enrollments — learners must enrol through the app (or admin roster actions) after seeding.
CMS course content — a published demo-course in the CMS is still required for catalogue and entry player flows.

Conceptual RBAC personas (P1–P5) are documented in reference/user-management.md; this section lists concrete dev fixtures only.

Authorization¶

There is no declarative DB rule engine. Authorization is enforced in application code:

Resolve the current session with auth() (re-exported from src/auth) before any sensitive query.
Use the helpers in src/auth/guards.ts (requireSession, requireRole, requireCourseAccess, withTenantOverride).
Validate every input with a Zod schema at the boundary, then pass the parsed values into Kysely.
Scope every multi-tenant query by tenantId and every per-user query by userId explicitly. Never rely on the caller to filter.