Database Schema

All schemas live in backend/db/schemas/. They are the source of truth for all TypeScript types — types are always derived with $inferSelect / $inferInsert, never written by hand.

The database client singleton is in backend/db/client.ts (getDb()). It pings the database on startup with 5-attempt exponential backoff.

Tables

users

Stores all application users. Role controls access via RBAC middleware.

Column	Type	Constraints	Notes
id	uuid	PK, defaultRandom()	UUID v4
email	varchar(255)	NOT NULL, UNIQUE	Normalised to lowercase
password	varchar(255)	NOT NULL	Argon2 / bcrypt hash via Bun.password
name	varchar(255)	NOT NULL	Display name
role	user_role enum	NOT NULL, default 'user'	'admin' | 'user'
created_at	timestamp	NOT NULL, defaultNow()	Set on INSERT
updated_at	timestamp	NOT NULL, defaultNow()	Set on INSERT; updated manually via dayjs().toISOString()

Enum: user_role — values: admin, user

Schema file: backend/db/schemas/users.ts

Derived types:

import { users } from '@backend/db/schemas/users';
 
type UserRow = typeof users.$inferSelect; // full row including password
type NewUser = typeof users.$inferInsert; // insert shape (id optional)

The service layer exposes a User type that omits password:

// backend/types/user.ts
export type User = Omit<NewUser, 'password'>;

---

refresh_tokens

Stores hashed refresh tokens for session management. One row per active session. Foreign key to users with ON DELETE CASCADE — revoking a user cascades to all their tokens.

Column	Type	Constraints	Notes
id	uuid	PK, defaultRandom()	UUID v4
user_id	uuid	NOT NULL, FK → users.id	ON DELETE CASCADE
token_hash	varchar(64)	NOT NULL, UNIQUE	SHA-256 hex of the raw random token
expires_at	timestamp	NOT NULL	Set to now + 7 days on INSERT
created_at	timestamp	NOT NULL, defaultNow()	Set on INSERT

Schema file: backend/db/schemas/refreshTokens.ts

Derived type:

import { refreshTokens } from '@backend/db/schemas/refreshTokens';
 
type RefreshToken = typeof refreshTokens.$inferSelect;

TTL config: backend/utils/auth/refreshTokenConfig.ts

export const refreshTokenConfig = {
  cookieName: 'refresh_token',
  ttlSeconds: 7 * 24 * 60 * 60, // used for cookie Max-Age
  ttlDays: 7, // used for DB expires_at
} as const;

---

Relationships

users (1) ──────────── (many) refresh_tokens
           user_id FK
           ON DELETE CASCADE

---

Migration Workflow

1. Edit or create a schema file in backend/db/schemas/
2. bun run db:generate — Drizzle Kit compares schema to the last snapshot and generates a new SQL file in backend/db/migrations/
3. Review the generated SQL before applying
4. bun run db:migrate — applies all pending migrations

Never edit migration files manually. If a migration needs changing, delete it and regenerate.

Migration files follow Drizzle Kit’s naming convention: NNNN_<slug>.sql.

Current migrations:

File	Description
0000_friendly_lady_bullseye.sql	Initial users table
0001_young_aqueduct.sql	refresh_tokens table
0002_lyrical_toad_men.sql	Schema refinements

---

Seed

backend/db/seed.ts creates the initial admin user. It is idempotent — safe to run multiple times without duplicating data.

bun run db:seed

The admin credentials come from environment variables:

SEED_ADMIN_EMAIL
SEED_ADMIN_PASSWORD
SEED_ADMIN_NAME

---

Repository Layer

Each table has a dedicated repository that contains only Drizzle queries — no business logic.

Repository	File
userRepository	backend/repositories/userRepository.ts
refreshTokenRepository	backend/repositories/refreshTokenRepository.ts

Repositories are plain objects (not classes). All methods are async and return Drizzle-inferred types. See ADR-003 for the factory pattern used when injecting repositories into services.

---

Drizzle Studio

bun run db:studio

Opens a visual browser GUI for the database.