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, assignedOfficerId, assignedAt, completionAttemptedAt, failedReason, errorCode
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
store_order_status_events	store.ts	id, storeOrderId (FK, cascade), oldState, newState, source, errorCode, note, createdAt — deny-all RLS
tickets	support.ts	id, userId, reference, subject, status, priority, type, source, openedBy, assignedTo, activationId, storeOrderId, dueAt, firstResponseAt, resolvedAt, closedAt, refundReason, decision, decidedBy, decisionReason, decidedAt
ticket_comments	support.ts	id, ticketId, authorId, content, isInternal, createdAt
companies	b2b.ts	id, crNumber, name, contactName, contactEmail (nullable), contactPhone (encrypted), status, archivedFrom (set only while archived, CHECK-enforced)
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
notifications	notification.ts	id, userId, category, eventKey, templateKey, params, severity, batchId, dedupeKey, readAt, seenAt, archivedAt, expiresAt, retainUntil, deletionRequestedAt, deletedAt, createdBy, createdAt
notification_deliveries	notification.ts	id, notificationId, batchId, channel, outcome, provider, providerMessageId, attempts, errorCode, errorText, suppressionReason, createdAt, updatedAt, queuedAt, sentAt, deliveredAt, failedAt
notification_channel_optouts	notification.ts	userId + channel (PK), createdAt
notification_batches	notification.ts	id, createdBy, request, category, status, idempotencyKey, requestFingerprint, replayExpiresAt, scheduledAt, startedAt, finishedAt, cancelledAt, recipientsTotal, createdAt
notification_provider_webhook_events	notification.ts	id, channel, provider, externalEventId, eventType, providerMessageId, normalisedStatus, reasonCode, providerTimestamp, receivedAt, enqueuedAt, processedAt

Notification Storage

All five tables are exported from the package root.

Table Export	Constraints and Storage
notifications	One row per recipient and occurrence. (userId, dedupeKey) is unique. params is JSON with a default of {}. Custom copy and its optional actionUrl and imageUrl live in params. Recipient addresses and credentials are loaded at render time. Category and nullable severity have checks against the shared vocabulary. Feed ordering uses (userId, createdAt DESC, id DESC). A separate (userId, readAt) index supports unread queries, and a batchId index supports batch reference lookups.
notificationDeliveries	(notificationId, channel) is unique. Channel, outcome and nullable suppression reason have checks against the shared vocabulary. Retry failures update attempts, errorCode, errorText and failedAt without moving the outcome backwards. updatedAt fences send claims. queuedAt tracks queue handoffs and the recovery cooldown. Indexes cover creation-time rate queries, outcome recovery, batch aggregation and provider message correlation.
notificationChannelOptouts	The primary key is (userId, channel). Only email, sms and push are allowed. in_app cannot be opted out of. An absent row means no opt-out for that channel.
notificationBatches	Stores the validated request and cached acceptance response. (createdBy, idempotencyKey) is unique when the key is not null. requestFingerprint detects conflicting reuse. replayExpiresAt bounds response replay. Status is scheduled, running, finished or cancelled. Category has a shared-vocabulary check. recipientsTotal counts recipients, not deliveries. Outcome counts are computed from delivery rows rather than stored here. List ordering uses (createdAt DESC, id DESC).
notificationProviderWebhookEvents	Stores parsed callback fields, not raw bodies. (channel, provider, externalEventId) is unique. Only email and sms are allowed. enqueuedAt stays null until the queue accepts the callback job. processedAt marks completed normalization. There is no delivery foreign key. Correlation uses channel, provider and provider message ID.

Deleting a recipient cascades to their notifications and opt-outs. Deleting a notification cascades to its deliveries. Deleting a batch clears batchId on notifications and deliveries. Deleting a creator clears createdBy on notifications and batches.

The exported relations are notificationsRelations, notificationDeliveriesRelations, notificationChannelOptoutsRelations, notificationBatchesRelations and usersNotificationRelations. They connect recipients, creators, batches and deliveries. Callback events have no relation export.

Notification Retention and Export

Expiry and archiving hide inbox entries without deleting them. A profile deletion request sets deletionRequestedAt and retainUntil on the user's notifications. The retention sweep requires a deletion request, an elapsed retention deadline and a null deletedAt. It replaces params with {} and sets deletedAt. It keeps the notification row, dedupe key and deliveries. An old occurrence cannot become sendable again merely because its content was scrubbed.

Independently of recipient deletion and its 365-day retention window, this sweep replaces batch request with channels-only metadata when the batch has been finished or cancelled for at least 24 hours and replayExpiresAt has expired or is null. It preserves the batch row, response snapshot, fingerprint, and dedupe history. It does not scrub opt-outs or callback events. Queue job retention is separate from stored notification retention.

Profile export includes each notification's id, category, eventKey, templateKey, params, severity, createdAt, readAt, seenAt, archivedAt and expiresAt. It includes archived, expired and scrubbed rows. Stored params are returned without rendering, so scrubbed rows retain {} in the export.

Each exported notification includes its deliveries with channel, outcome, provider, attempts, sentAt, deliveredAt, failedAt and suppressionReason. Opt-outs include channel and createdAt. Export does not include batch requests, callback events, provider message IDs, error text, dedupe keys or idempotency keys.

Contract Readiness Episodes

contracts.notificationReadinessEpisode maps to notification_readiness_episode, an integer with NOT NULL DEFAULT 0. Readiness producers atomically increment it on a false-to-true transition inside the company advisory-lock transaction and use the captured value for postcommit notification dispatch. Notification-history counts do not allocate episodes.

Migration 0026_contract_readiness_episode.sql backfills the counter above the largest matching legacy numeric dedupe-key suffix per contract, including gaps and duplicate recipients. Contracts without history stay at zero; their first allocation is 1. Apply the migration before the new API code; see deployment prerequisites and rollback.

Support and Officer Storage

tickets.reference is a stored generated column containing TKT- plus the last 12 UUID hex characters, with a unique index. Ticket type and source have checks; decisions are approved or rejected; refund reasons are service_not_delivered, activation_failed, duplicate_charge, customer_request, or other. A refund link check permits no more than one of activation_id and store_order_id.

Ticket indexes are idx_tickets_user_id, idx_tickets_status, idx_tickets_status_due_at on (status, due_at), idx_tickets_assigned_to_status on (assigned_to, status), idx_tickets_type_created on (type, created_at), and idx_ticket_comments_ticket_id. Partial unique indexes uq_tickets_refund_activation and uq_tickets_refund_store_order allow one live refund request per activation or store order; a rejected request does not occupy the unique slot.

ticket_comments.author_id is nullable and set to null when the author's user is deleted, so the comment outlives the account.

Migration 0030_empty_ben_grimm.sql adds ticket type/source/link/timing/decision columns, activations.assigned_at, and the ticket indexes above. The officer assignment foreign key and the two activations indexes are built online by custom migrations 0023 to 0026: the foreign key is added NOT VALID and then validated, and idx_activations_assigned_officer_id and idx_activations_physical_iccid use CREATE INDEX CONCURRENTLY, one statement per file, so writes to activations are not blocked. The custom policy migration drops the earlier broad ticket policies and replaces them with tickets_end_user_own, tickets_support_read, tickets_officer_assigned, tickets_super_admin_all, ticket_comments_end_user_visible, and ticket_comments_staff_read. Customers see only their customer requests and non-internal comments; support reads all tickets; officers read assigned tickets plus refunds they opened; super admins read all tickets. Writes remain through the API.

activations.assigned_at records when the current fallout claim was taken and is paired with idx_activations_assigned_officer_id on assigned_officer_id.

The serial-hold helpers are lockPhysicalSerial, findPhysicalSerialHolder, and findPhysicalSerialClaimant in src/activation/serial-hold.ts. They use a transaction advisory lock keyed by the physical ICCID. Holder lookup includes paid pending/in-progress/activated rows and claimed failures. Cancelled rows, other unpaid rows, unclaimed failures, and officer-verified failures release the serial.

applyIdentityOutcome in src/activation/lifecycle.ts records one identity check outcome and moves the activation accordingly; the queue poll, the customer status read, and the recovery scan all call it.

transitionStoreOrder in src/store/lifecycle.ts is how the store worker writes issuance_state, error_code and failed_reason on esim_store_orders. It takes a database handle or an open transaction (joined as a savepoint), the store order id, the column patch, an optional guard condition, a source and a note. In one transaction it reads the row FOR NO KEY UPDATE, runs the update with id = ? AND <guard>, and, when the update matched and either the state or the error code changed, inserts one store_order_status_events row with the state read under the lock, the new state, the new error code, the source and the note. A write that only rewords failed_reason under the same state and code adds no event. It returns whether the guarded update applied, with the old and new state and code, so callers keep branching on a lost race. Called with the global handle it commits before returning, which keeps the store worker's pre-call stamp durable before the provider call.

store_order_status_events is the store order timeline. Generated migration 0033_uneven_johnny_blaze.sql creates it with its cascading foreign key to esim_store_orders and idx_store_order_status_events_store_order_created on (store_order_id, created_at, id), and changes no other table. Source is system for every worker write (STORE_TRANSITION_SOURCES in the shared package). Nothing is backfilled, so an order's history starts at its first change after the table exists.

hasIdentityTypeLockingCheck in src/activation/identity-type-lock.ts tells whether an identity check holds a customer's identity type in place: any completed check, for good, or a waiting check that is the current attempt of a pending, non-deleted activation. A waiting row left behind by a cancelled, failed, deleted or superseded activation does not count. The API's identity type rule for the profile update, the customer identity check, and the officer route reads it.

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)

Notification params and batch request use plain JSON columns, not the encrypted column type. Recipient addresses and credentials stay in their source records and are loaded for rendering.

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

Some domains export SQL strings for Row Level Security:

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

store_order_status_events gets the same deny-all treatment as esim_store_orders in packages/database/drizzle/custom/0028_store_order_status_events_rls.sql: RLS enabled, table privileges revoked from PUBLIC, anon and authenticated, and four store_order_status_events_no_client_* policies with USING (false) / WITH CHECK (false). It is not in the realtime publication. Staff read it through the API.

Notification policies are defined in packages/database/drizzle/custom/0016_notifications_rls.sql. There is no notificationRlsPolicies export. All five notification tables enable RLS and revoke table privileges from PUBLIC, plus anon and authenticated when those roles exist. Clients use the API for reads and writes. No recipient or administrator client policy grants direct access.

The SQL defines all twenty notification policies explicitly:

Table	Policy	Operation	Predicate
notifications	notifications_no_client_select	SELECT	USING (false)
notifications	notifications_no_client_insert	INSERT	WITH CHECK (false)
notifications	notifications_no_client_update	UPDATE	USING (false) WITH CHECK (false)
notifications	notifications_no_client_delete	DELETE	USING (false)
notification_deliveries	notification_deliveries_no_client_select	SELECT	USING (false)
notification_deliveries	notification_deliveries_no_client_insert	INSERT	WITH CHECK (false)
notification_deliveries	notification_deliveries_no_client_update	UPDATE	USING (false) WITH CHECK (false)
notification_deliveries	notification_deliveries_no_client_delete	DELETE	USING (false)
notification_channel_optouts	notification_channel_optouts_no_client_select	SELECT	USING (false)
notification_channel_optouts	notification_channel_optouts_no_client_insert	INSERT	WITH CHECK (false)
notification_channel_optouts	notification_channel_optouts_no_client_update	UPDATE	USING (false) WITH CHECK (false)
notification_channel_optouts	notification_channel_optouts_no_client_delete	DELETE	USING (false)
notification_batches	notification_batches_no_client_select	SELECT	USING (false)
notification_batches	notification_batches_no_client_insert	INSERT	WITH CHECK (false)
notification_batches	notification_batches_no_client_update	UPDATE	USING (false) WITH CHECK (false)
notification_batches	notification_batches_no_client_delete	DELETE	USING (false)
notification_provider_webhook_events	notification_provider_webhook_events_no_client_select	SELECT	USING (false)
notification_provider_webhook_events	notification_provider_webhook_events_no_client_insert	INSERT	WITH CHECK (false)
notification_provider_webhook_events	notification_provider_webhook_events_no_client_update	UPDATE	USING (false) WITH CHECK (false)
notification_provider_webhook_events	notification_provider_webhook_events_no_client_delete	DELETE	USING (false)