Database Schema Management¶

This project uses Drizzle ORM for type-safe database migrations and schema management.

Architecture¶

backend/
├── src/
│   └── db/
│       ├── index.ts              # Database connection (Drizzle + pg)
│       ├── schema/
│       │   ├── index.ts          # Re-exports all tables
│       │   ├── auth.ts           # Better-auth tables (user, session, account)
│       │   ├── profiles.ts       # Extended user profiles
│       │   └── organizations.ts  # Organization and membership tables
├── scripts/
│   ├── migrate.ts                # Runtime migration runner (drizzle-orm migrator)
│   └── seed.ts                   # Environment-aware seed script
└── drizzle/
    ├── meta/                 # Drizzle journal
    └── migrations/           # SQL migration files

Commands¶

Command	Description
`pnpm db:generate`	Generate a new SQL migration from schema changes
`pnpm db:migrate`	Apply all pending migrations (local/dev)
`pnpm db:migrate:run`	Apply migrations via runtime migrator script
`pnpm db:studio`	Open Drizzle Studio (visual DB browser)
`pnpm db:seed`	Seed the database with dev/test data

For Dokku deployments, run migrations in-container:

ssh dokku@fenrir run praxia-coaching-app-backend-staging node backend/dist/scripts/migrate.js

Workflow¶

1. Modify the schema¶

Edit or add a table in backend/src/db/schema/:

// backend/src/db/schema/profiles.ts
import { pgTable, text, timestamp, uuid } from 'drizzle-orm/pg-core';
import { user } from './auth';
import { organizations } from './organizations';

export const profile = pgTable('profile', {
  id: uuid('id').defaultRandom().primaryKey(),
  userId: text('user_id')
    .notNull()
    .references(() => user.id, { onDelete: 'cascade' }),
  role: text('role').notNull().default('teacher'),
  organizationId: uuid('organization_id').references(() => organizations.id),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
});

2. Generate the migration¶

cd backend
pnpm db:generate

This creates a timestamped SQL file in drizzle/migrations/.

3. Review the migration¶

Always inspect the generated SQL before applying:

-- drizzle/migrations/0003_add_profile_table.sql
CREATE TABLE IF NOT EXISTS "profile" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  "user_id" text NOT NULL REFERENCES "user"("id") ON DELETE CASCADE,
  "role" text NOT NULL DEFAULT 'teacher',
  "organization_id" uuid REFERENCES "organization"("id"),
  "created_at" timestamp DEFAULT now() NOT NULL,
  "updated_at" timestamp DEFAULT now() NOT NULL
);

4. Apply the migration¶

pnpm db:migrate

Core Tables¶

`user` (Better-auth managed)¶

Column	Type	Description
`id`	text PK	Better-auth user ID
`email`	text unique	User email
`name`	text	Display name
`emailVerified`	boolean	Email verification status
`createdAt`	timestamp	Creation time

`profile`¶

Column	Type	Description
`id`	uuid PK	Profile ID
`userId`	text FK	References `user.id`
`role`	text	`platform_admin`, `organization_admin`, `manager`, `coach`, `teacher`
`organizationId`	uuid FK	References `organization.id`

`organization`¶

Column	Type	Description
`id`	uuid PK	Organization ID
`name`	text	Organization name
`slug`	text unique	URL-safe identifier
`createdAt`	timestamp	Creation time

`organization_user`¶

Column	Type	Description
`id`	uuid PK	Membership ID
`organizationId`	uuid FK	References `organization.id`
`userId`	text FK	References `user.id`
`role`	text	Role within the organization

Rules¶

Never modify the database schema manually in production.
Always create a migration for schema changes — commit the SQL file with your PR.
Run migrations as part of deployment (node backend/dist/scripts/migrate.js inside Dokku container).
Never edit a generated migration file after it has been applied to any environment.

Seeding¶

The seed script is environment-aware:

NODE_ENV=development seeds full test fixture data.
NODE_ENV=staging or NODE_ENV=production refuses to run fixture seed data.

cd backend
pnpm db:seed

For staging/production bootstrap, create the first platform admin via POST /api/setup/first-run.

Seed files live in backend/scripts/.