Database

Overview

We use Drizzle ORM with PostgreSQL (Supabase). All schemas are in packages/database/src/schema/.

Schema Organization

File	Tables	Domain
auth.ts	users, sessions	Authentication
catalog.ts	operators, packages	Telecom catalog
activation.ts	orders, orderItems, activations, identityVerifications	SIM lifecycle
payment.ts	payments, invoices	Payment processing
support.ts	tickets, ticketComments	Customer support
b2b.ts	companies, companyUsers, bulkOrders	B2B portal
audit.ts	auditLogs, consentRecords	Compliance (immutable)

PII Encryption

Sensitive fields use AES-256-GCM encryption transparently via Drizzle custom types:

// Encrypted fields are automatically encrypted on write and decrypted on read
const user = await db.select().from(users); // phone, email, name, idNumber auto-decrypted

Encrypted columns: phone, email, name, idNumber, contactPhone.

The encryption key is read at call time (not startup) to support key rotation.

Row Level Security (RLS)

Each domain has RLS policies documented in schema files and applied through custom SQL migrations:

import { authRlsPolicies } from '@activation-sys/database';
// The executable SQL lives in packages/database/drizzle/custom/0004_apply_rls_policies.sql

After applying migrations, run pnpm db:verify to confirm the live Supabase database has RLS enabled, expected policies installed, custom migration hashes matching, Realtime publication configured, and required triggers present.

Common Commands

# Generate migration from schema changes
pnpm db:generate

# Run pending migrations
pnpm db:migrate

# Verify the live Supabase database state
pnpm db:verify

# Local throwaway schema push only; blocked unless explicitly confirmed
ALLOW_DB_PUSH=1 pnpm db:push:unsafe

Entity Relationships

users ──┬── sessions
        ├── orders ──── order_items
        ├── activations ──── identity_verifications
        ├── payments ──── invoices
        ├── tickets ──── ticket_comments
        └── company_users ──── companies

operators ──── packages
companies ──── company_users
                └── bulk_orders