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, required triggers present, RLS helper owners able to bypass table policies, and no invalid public indexes.

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

The recursion-breaking users-policy helpers require a function owner that bypasses RLS on users and activations: a superuser/BYPASSRLS role, or membership in both owning roles when FORCE RLS is disabled. Apply migration 0027 through that deployment role. db:verify checks the actual function owners, including ownership drift after deployment.

An interrupted concurrent index build can leave an invalid index whose name causes IF NOT EXISTS to skip rebuilding. If db:verify reports one, inspect pg_index.indisvalid, drop that invalid index with DROP INDEX CONCURRENTLY, and rerun its original concurrent-index SQL. Do not edit applied migration files or their tracking hashes to bypass the check.