Database Architecture

Overview

The API uses Drizzle ORM with PostgreSQL through the postgres.js driver. Drizzle provides fully type-safe queries derived from schema definitions — no code generation step, no runtime overhead.

Key characteristics:

• Driver: postgres (postgres.js) for connection pooling and query execution
• ORM layer: drizzle-orm/postgres-js for type-safe query building
• Schema toolkit: drizzle-kit for migrations and schema introspection
• TypeScript integration: Schema definitions produce TypeScript types automatically

All database code lives in apps/api/src/database/.

Provider Pattern

Database access is wired through NestJS dependency injection using Symbol tokens (not string tokens):

// drizzle.provider.ts
export const DRIZZLE = Symbol('DRIZZLE')
export const POSTGRES_CLIENT = Symbol('POSTGRES_CLIENT')

Two providers are registered:

1. POSTGRES_CLIENT — creates the raw postgres connection. Reads DATABASE_URL from ConfigService. Connection pool settings: max: 10, idle_timeout: 20, connect_timeout: 10.

2. DRIZZLE — wraps the postgres client in Drizzle ORM with the full schema. This is the token you inject in services. Prefer DATABASE_APP_URL over DATABASE_URL for application queries: DATABASE_APP_URL connects as the app_user role (RLS enforced), while DATABASE_URL connects as the schema owner (RLS bypassed). When RLS enforcement is required, SET LOCAL ROLE app_user must be called before set_config() in any transaction.

Graceful degradation: If DATABASE_URL is not set:

• In production: throws immediately (hard failure)
• In development: returns null and logs a warning — the app starts without database features

The DatabaseModule is registered as @Global() so every module can inject DRIZZLE without importing it:

@Inject(DRIZZLE) private readonly db: DrizzleDB | null

The module implements OnModuleInit and OnModuleDestroy:

• onModuleInit: Pings the database (SELECT 1) to verify the connection, then checks for pending migrations by comparing drizzle/migrations/meta/_journal.json against the drizzle.__drizzle_migrations table. Logs a warning if migrations are pending. After the migration check, it also calls checkCoreTables() to verify that Better Auth core tables exist in the database.
• onModuleDestroy: Closes the postgres connection on shutdown.

Schema Conventions

Shared helpers

database/schema/timestamps.ts defines reusable timestamp columns, re-exported from database/schema/base.ts:

// Timestamps with timezone and auto-update
export const timestamps = {
  createdAt: timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
  updatedAt: timestamp('updated_at', { withTimezone: true })
    .defaultNow()
    .notNull()
    .$onUpdateFn(() => new Date()),
}

// Tenant column for Row-Level Security
export const tenantColumn = {
  tenantId: text('tenant_id')
    .notNull()
    .references(() => organizations.id, { onDelete: 'cascade' }),
}

Spread timestamps into any new table to get consistent created_at / updated_at columns with timezone support and automatic update-on-write.

Naming rules

Barrel export

All schema files re-export through database/schema/index.ts:

export * from './auth.schema.js'
export * from './base.js'
export * from './rbac.schema.js'

The Drizzle provider imports * as schema from this index.

Auth Schema

Better Auth manages its own database tables via the Drizzle adapter (usePlural: true). These are defined in database/schema/auth.schema.ts:

These tables use text primary keys with UUID generation (advanced.database.generateId: 'uuid' in Better Auth config). All auth tables now use the shared ...timestamps spread (with timezone and $onUpdateFn), except invitations which only has expiresAt and lacks createdAt/updatedAt.

RBAC Schema

RBAC tables are defined in database/schema/rbac.schema.ts and manage per-tenant roles and permissions. The schema follows the existing conventions (text PKs with UUID, snake_case columns, timestamps).

Tables

members table extension: A role_id FK column was added to the existing members table, referencing roles.id with ON DELETE SET NULL. This column is the source of truth for RBAC; the original role text column is kept for Better Auth compatibility.

Constraints

• permissions: unique on (resource, action) — seeded via migration, not editable by tenants
• roles: unique on (tenant_id, slug) — default roles (owner, admin, member, viewer) seeded per org on creation
• role_permissions: composite PK on (role_id, permission_id), cascades on delete
• roles has RLS via create_tenant_rls_policy('roles'). permissions is global (no RLS). role_permissions inherits isolation through the FK to roles.

Default roles

Each organization gets four default roles seeded automatically via RbacListener on the ORGANIZATION_CREATED event:

Drizzle Kit Configuration

apps/api/drizzle.config.ts:

export default defineConfig({
  schema: './src/database/schema/index.ts',
  out: './drizzle/migrations',
  dialect: 'postgresql',
  dbCredentials: {
    url: process.env.DATABASE_URL ?? '',
  },
  verbose: true,
  strict: true,
})

• schema — points to the barrel export of all schema files
• out — migration SQL files are written to apps/api/drizzle/migrations/
• strict: true — prompts for confirmation before destructive changes
• verbose: true — prints detailed migration output

Migration Workflow

Run from the repository root (recommended) — the dispatcher loads .env automatically:

# 1. Generate a migration from schema changes
bun db:generate

# 2. Apply pending migrations to the database
bun db:migrate

Or from apps/api/ directly (requires DATABASE_URL to be set):

bunx drizzle-kit generate
bunx drizzle-kit migrate
bunx drizzle-kit studio   # Visual inspection (no root wrapper)

Workflow:

1. Edit or create schema files in src/database/schema/
2. Run bunx drizzle-kit generate — Drizzle compares the schema to the last migration snapshot and generates SQL
3. Review the generated SQL in drizzle/migrations/
4. Run bunx drizzle-kit migrate to apply
5. Commit both the schema changes and the migration files

Adding a New Table

1. Create the schema file:

   // src/database/schema/products.ts
   import { pgTable, text } from 'drizzle-orm/pg-core'
   import { timestamps } from './base.js'
   
   export const products = pgTable('products', {
     id: text('id').primaryKey(),
     name: text('name').notNull(),
     ...timestamps,
   })

2. Re-export from the barrel:

   // src/database/schema/index.ts
   export * from './auth.schema.js'
   export * from './base.js'
   export * from './products.js'

3. Generate the migration:

   cd apps/api
   bunx drizzle-kit generate

4. Review and apply:

   # Check the generated SQL
   cat drizzle/migrations/XXXX_*.sql
   
   # Apply to database
   bunx drizzle-kit migrate

5. Use in a service:

   import { Inject, Injectable } from '@nestjs/common'
   import { DRIZZLE, type DrizzleDB } from '../database/drizzle.provider.js'
   import { products } from '../database/schema/index.js'
   
   @Injectable()
   export class ProductService {
     constructor(@Inject(DRIZZLE) private readonly db: DrizzleDB) {}
   
     async findAll() {
       return this.db.select().from(products)
     }
   }