Database Package

@activation-sys/database — Drizzle ORM schemas, database connection, and PII encryption.

Installation

Already included in the monorepo workspace.

import { db, users, operators, packages, ... } from '@activation-sys/database';

Exports

// Schema tables
export * from './schema/index.js';

// Database connection
export { createDb, getDb, db } from './utils/connection.js';

// Encryption utilities
export { encrypt, decrypt } from './utils/encryption.js';

Tables

Table	File	Key Columns
users	auth.ts	id, phone (encrypted), email (encrypted), name (encrypted), idNumber (encrypted), identityType, appRole
sessions	auth.ts	id, userId, tokenHash, ipAddress, userAgent
operators	catalog.ts	id, slug (stc/mobily/zain), nameEn, nameAr, serviceType, paymentMode, isNameArFallback
packages	catalog.ts	id, operatorId, dataAmountMb, voiceMinutes, priceSar, priceSarB2b, isActive, isActiveCustomized, isNameCustomized, isPriceCustomized, source_*
orders	activation.ts	id, userId, companyId, status, totalAmount, currency
order_items	activation.ts	id, orderId, packageId, iccid, quantity
activations	activation.ts	id, orderItemId, userId, operatorId, iccid, status, identityStatus
identity_verifications	activation.ts	id, activationId, transId, randomCode, status
payments	payment.ts	id, orderId, userId, amount, currency, method, status, idempotencyKey
invoices	payment.ts	id, companyId, orderId, amountSar, zatcaInvoiceId
fx_rates	fx.ts	currency (PK), ratePerUsd, fetchedAt, source — deny-all RLS
tickets	support.ts	id, userId, subject, status, priority
ticket_comments	support.ts	id, ticketId, authorId, content, isInternal
companies	b2b.ts	id, crNumber, name, contactName, contactEmail, contactPhone (encrypted)
company_users	b2b.ts	id, companyId, userId, role
bulk_orders	b2b.ts	id, companyId, orderId, totalSims, status, csvData
audit_logs	audit.ts	id, actorId, action, entityType, entityId, oldValues, newValues
consent_records	audit.ts	id, userId, consentType, version, givenAt, revokedAt

PII Encryption

Uses AES-256-GCM (authenticated encryption) via a custom Drizzle type:

// Encrypted columns are transparent — read/write as plaintext
const [user] = await db.insert(users).values({
  phone: '0512345678',  // Auto-encrypted on write
  email: 'user@example.com',
  name: 'Ahmed',
  identityType: 'citizen',
  appRole: 'end_user',
}).returning();
// user.phone returns '0512345678' (auto-decrypted on read)

Key rotation: ENCRYPTION_KEY is read at call time, not at startup. During a rotation, set ENCRYPTION_KEY_PREVIOUS to the old key — decryption tries the current key first and falls back to the previous one (the GCM auth tag identifies the right key), while new writes always use the current key. Unset it once the old ciphertexts are gone — either by re-encrypting the existing rows, or by waiting for the retention window to drain them. There is no re-encryption command in the repo today, so a rewrite is a one-off task against the affected tables.

RLS Policies

Each domain exports SQL strings for Row Level Security:

import { authRlsPolicies, catalogRlsPolicies, activationRlsPolicies } from '@activation-sys/database';