Database

Code: app.abhyas.database.dev. Data: Supabase Postgres, one project per environment (dev wvwcibiijcwvtcjbxjvt, prd pqncgrgssiopykgctjge). app.abhyas.database.prd only deploys releases.

Layout

Path	What it is
`prisma/schema.prisma`	The tables, columns, keys and relations, with notes
`prisma/migrations/`	Every change, in order. `0_init` is the database as it was when migrations began
`prisma/shadow-supabase.sql`	Stands in for what Supabase provides (`auth.users`, `auth.uid()`, roles) in local and CI databases
`prisma.config.ts`	Prisma's settings: `auth.users` belongs to Supabase, so Prisma never changes it
`scripts/local-db.mjs`	A local PostgreSQL 17 for trying migrations (`npm run db:local`)
`deploy/prd/`	Copies of database.prd's workflows

Making a change

You need Node 22.12 or newer. Once: npm ci, cp .env.example .env, then npm run db:local in its own terminal.

Edit prisma/schema.prisma.
npm run migrate:new -- <name> writes the migration without applying it. Read it.
Add by hand what Prisma can't express: row-level security, functions, triggers, views, grants. Follow 0_init.
npm run migrate:dev applies it to the local Postgres.
npm run check must pass: the migrations produce exactly schema.prisma.
Commit the schema and migration together and open a pull request. Merging applies it to dev.

Never edit a migration that has been applied; write a new one. Migrations only move forward. Other commands: npm run status, npm run format. Delete .local/ to reset the local database.

Tables

disciplines → chapters → topics, each with a uuid and a readable code (mathematics, 04, 04-13). Chapters and topics carry lesson_latex (the Concept tab). Sources and tags belong to a discipline. Each discipline has its own question bank, made by create_question_bank(slug):

Table or view	What it holds
`<slug>_questions`	Each question: main topic, source and folder, `question_latex`, `official_answer_latex` and its status
`<slug>_question_topics`	Topics a question is in besides its main one
`<slug>_question_tags`	Its tags
`<slug>_question_topic_links` (view)	Every question and topic pair
`<slug>_practice_questions` (view)	What practice loads
`question_progress`	Each user's answers, files, ratings and notes
`records`	Each user's saved papers and results

To add a discipline's bank: a migration with select public.create_question_bank('physics');, copies of the three Mathematics… models in schema.prisma renamed, then npm run check.

Access

Sign-in is invite-only. Turn off Allow new users to sign up in each Supabase project; invite from Authentication → Users → Add user → Send invitation.
is_member() lets in existing, non-banned users.
app_owner can edit the catalogue; members can read shared content.
Row-level security keeps each user's records and question_progress to themselves.

Workflows

Workflow	Repository	What it does
🚀 Deploy (`migrations.yml`)	database.dev	On pull requests: check. On merge: migrate the dev database, make a release with `bundle.zip`
Sync from the book	database.dev	Daily: opens a PR with a migration of the book's changes
🚀 Deploy	database.prd	By hand: applies a release to the prd database
Export the question banks	database.prd	Nightly: commits `exports/<slug>.sql` to the `exports` branch

SUPABASE_DB_URL is the Session pooler connection string (port 5432). The direct connection is IPv6 only, which GitHub's runners can't reach. A database with no migration history is set up by the deploy job itself on its first deploy.