Database Schema

The Sema Link API uses PostgreSQL via Drizzle ORM. All tables use UUID primary keys. Timestamps are stored in UTC.

Migration workflow

Two modes are used depending on the change:

1. Generated migrations — run drizzle-kit generate to produce a timestamped SQL file from the Drizzle schema. Run drizzle-kit migrate to apply it. Use this for new tables and straightforward column additions.

2. Manual SQL migrations — for changes Drizzle cannot express (self-referential foreign keys, backfill queries, seed data), write a raw .sql file directly in src/db/migrations/ and register it in src/db/migrations/meta/_journal.json. The journal entry must be added atomically so the migration tracker stays consistent.

Why Drizzle ORM?

We chose Drizzle over Prisma after evaluating both for this project:

• Type safety — Drizzle's inference is tighter; query results are typed without a separate code-generation step
• Raw SQL escape hatch — Drizzle lets you drop into sql\`` without losing the connection pool or type context
• Lightweight — no engine binary, no Rust sidecar; just a thin TypeScript layer over pg
• Schema-as-code — the schema file is the source of truth; no .prisma DSL to learn

The tradeoff is that Drizzle's migration workflow is less forgiving: always run db:generate to produce a migration before db:migrate. Manually authored SQL files are silently ignored.

---

Entity-Relationship Overview

---

Enums

account_type

Value	Meaning
direct	Self-billing customer — pays Sema Link directly
agent	Reseller — manages their own sub-customers at a custom margin
agent_customer	Customer of an agent; billing flows through the agent

account_status

Value	Meaning
pending	Newly registered, awaiting first activation
active	Fully operational
suspended	Access restricted by the Sema Link team

user_role

Value	Capabilities
owner	Full account control — invite, role changes, member removal
admin	Can invite new members; cannot change roles or remove others
member	Read/write own profile only

token_type

Used by the auth_tokens table to identify single-use token purpose.

Value	Purpose	Expiry
email_verification	Sent after registration to confirm the email address	24 hours
password_reset	Sent via forgot-password flow	1 hour
magic_link	Passwordless login link sent by request	15 minutes

oauth_provider

Value	Notes
google	Google OAuth 2.0
microsoft	Microsoft Identity Platform (Azure AD)
github	GitHub OAuth App
apple	Sign in with Apple (JWT-based)

channel_type

Value	Meaning
sms	Standard SMS (default)
whatsapp	WhatsApp Business (future)
email	Email channel (future)

sender_id_status

Value	Meaning
pending	Submitted; awaiting review by the Sema Link team
approved	Approved for use in messages and campaigns
rejected	Not approved — see rejection notes
suspended	Previously approved but temporarily blocked

contact_status

Value	Meaning
pending_validation	Awaiting MNO prefix lookup
valid	Phone prefix matched a known operator
invalid	Prefix unmatched — see invalid_reason (e.g. unknown_network)

message_status

Value	Meaning
submitted	Accepted by the API
queued	Published to sms.priority or sms.normal RabbitMQ queue
sent	Submitted to Celcom Africa aggregator
delivered	Delivery report (DLR) received confirming delivery to handset
failed	Send or delivery failed; reserved credits refunded
expired	Message expired before delivery (aggregator TTL)

campaign_status

Value	Meaning
pending_content_check	Awaiting SMS Content Worker review
approved	Content approved; ready to schedule or send
rejected	Content rejected — see rejection_reason
scheduled	Approved with a future send_at time; sitting in PostgreSQL
queued	Due campaigns being dispatched by the scheduler
sending	Messages actively being published to RabbitMQ
completed	All messages dispatched
cancelled	Cancelled before sending; credits refunded

credit_tx_type

Value	Meaning
topup	Credit added (M-Pesa, manual)
reservation	Credits held for an in-flight send
finalisation	Reservation confirmed after successful DLR
refund	Credits returned on failed send
adjustment	Manual admin correction

---

Tables

accounts

One row per organisation. All other records belong to an account.

Column	Type	Notes
id	uuid PK
name	text	Company or account name
type	account_type	Default: direct
status	account_status	Default: pending
billing_account_id	uuid	Self-referential: equals own id for direct; agent's id for agent_customer
parent_agent_id	uuid nullable	Set only for agent_customer accounts
timezone	text	Default: Africa/Nairobi
country	text	Default: KE
created_at	timestamp
updated_at	timestamp

---

account_members

Junction table that links users to accounts. A single user can belong to multiple organisations. This replaced the original direct account_id foreign key on users.

Column	Type	Notes
id	uuid PK
user_id	uuid FK → users	Cascades delete
account_id	uuid FK → accounts	Cascades delete
role	user_role	Role within this specific org. Default: member
is_active	boolean	Default: true. Set false to revoke access without deleting the record
created_at	timestamp

Unique constraint: (user_id, account_id) — a user can only have one membership per org.

Why multi-org? Agencies and resellers need to manage multiple brands under a single login. A user creates their personal account on signup, then creates or is invited into additional orgs. The JWT's accountId and role claims are derived from the active membership row chosen at login or via POST /api/v1/accounts/switch.

---

users

Team members. password_hash is nullable because OAuth-only users do not have a password. Users no longer carry a direct account_id — org affiliation is entirely through account_members.

Column	Type	Notes
id	uuid PK
account_id	uuid FK → accounts	Cascades delete
first_name	text
last_name	text
email	text unique
password_hash	text nullable	bcrypt cost 12. Null for OAuth-only accounts
role	user_role	Default: member
is_verified	boolean	Default: false. Set true after email verification
is_active	boolean	Default: true. Deactivated users cannot log in
phone	text nullable	Optional phone number for the user profile
profile_photo_url	text nullable	Full HTTPS URL of photo stored in Cloudflare R2
last_login_at	timestamp nullable	Updated on every successful login
created_at	timestamp
updated_at	timestamp

Design note: password_hash was made nullable (not added as a separate column) to keep the user record as a single source of truth regardless of auth method. If a user registers via OAuth and later adds a password, the field is simply populated — no record duplication.

---

refresh_tokens

Tracks issued refresh tokens. Only the SHA-256 hash is stored — the raw token is returned to the client once and never persisted.

Column	Type	Notes
id	uuid PK
user_id	uuid FK → users	Cascades delete
token_hash	text unique	SHA-256 of the 128-char hex raw token
expires_at	timestamp	7 days from issuance
revoked_at	timestamp nullable	Set on logout or token rotation
created_at	timestamp

Security note: Storing only the hash means a database breach does not expose usable tokens. Rotation on use (each refresh token is single-use) limits replay window.

---

auth_tokens

Single-use tokens for email verification, password reset, and magic link flows. Separate from refresh_tokens because these are short-lived and tied to an action rather than a session.

Column	Type	Notes
id	uuid PK
user_id	uuid FK → users	Cascades delete
email	text	Target email address
token_hash	text unique	SHA-256 of the raw token sent in the email link
type	token_type	email_verification, password_reset, magic_link
expires_at	timestamp	Varies by type (see enum table above)
used_at	timestamp nullable	Set when redeemed; prevents replay
created_at	timestamp

Why a shared table instead of three separate tables? The logic is identical across all three types — hash, check expiry, mark used. One table with a type discriminator keeps the service clean. If the flows diverge significantly in future, they can be split.

---

oauth_accounts

Links a user account to an OAuth provider identity. One user can link multiple providers.

Column	Type	Notes
id	uuid PK
user_id	uuid FK → users	Cascades delete
provider	oauth_provider	google, microsoft, github, apple
provider_id	text	Provider's unique user ID (sub claim)
provider_email	text nullable	Email returned by the provider
created_at	timestamp

Unique index: (provider, provider_id) — prevents the same provider account being linked to two different Sema Link users.

---

api_keys

Machine-to-machine keys. Used by external integrations and the REST API. The raw key is shown once on creation and never stored.

Column	Type	Notes
id	uuid PK
account_id	uuid FK → accounts
key_hash	text unique	Hash of the raw API key
label	text nullable	Human-readable label
last_used_at	timestamp nullable
revoked_at	timestamp nullable
created_at	timestamp

---

webhook_endpoints

Customer-configured URLs for DLR (delivery report) callbacks.

Column	Type	Notes
id	uuid PK
account_id	uuid FK → accounts
url	text	HTTPS URL to receive POST callbacks
is_active	boolean	Default: true
created_at	timestamp
updated_at	timestamp

---

contact_lists

Named groups of contacts. Contacts can belong to multiple lists via the junction table.

Column	Type	Notes
id	uuid PK
account_id	uuid FK → accounts	Cascades delete
name	text
description	text nullable	Optional description shown in the UI
total_count	integer	Denormalised count — maintained by service layer
valid_count	integer	Contacts that passed phone validation
invalid_count	integer	Contacts that failed validation
created_at	timestamp
updated_at	timestamp

---

contacts

Individual contacts. Soft-deleted via deleted_at — trashed contacts remain in the database for 90 days before hard deletion.

Column	Type	Notes
id	uuid PK
account_id	uuid indexed FK → accounts	Cascades delete
first_name	text nullable
last_name	text nullable
phone	text	Stored in international format (e.g. +254712345678)
email	text nullable
status	contact_status	Default: pending_validation
invalid_reason	text nullable	Set by Phone Validation Worker on failure
tags	text[]	Array of freeform tag strings. Default: []
notes	text nullable	Internal notes about the contact
is_opted_out	boolean	Default: false. Opted-out contacts are excluded from campaigns
consent_source	text nullable	How consent was obtained (e.g. "opt-in form", "CSV import")
consent_at	timestamp nullable	When consent was recorded
consent_note	text nullable	Free-form note for auditors (POPIA/KICA/NCC compliance)
deleted_at	timestamp nullable	Set when moved to trash; null for active contacts
created_at	timestamp
updated_at	timestamp

Why soft delete? Customer support frequently needs to restore accidentally deleted contacts. Hard-deleting immediately makes that impossible. Trash with a 90-day window matches user expectations (mirrors Gmail behaviour) while keeping the database tidy long-term.

---

contact_list_memberships

Junction table implementing the many-to-many relationship between contacts and lists. This replaced an earlier list_id foreign key on the contacts table.

Column	Type	Notes
id	uuid PK
contact_id	uuid indexed FK → contacts	Cascades delete
list_id	uuid indexed FK → contact_lists	Cascades delete
created_at	timestamp

Unique constraint: (contact_id, list_id) — a contact cannot appear in the same list twice.

Why the migration from list_id to a junction table? The original design put a single list_id FK on the contacts table. This worked for the first phase but failed the real-world use case: a contact built for one campaign often needs to be added to another campaign's list without duplication. Migrating to a junction table makes this native. The service layer maintains total_count on contact_lists rather than querying COUNT(*) on each request.

---

mno_prefixes

Lookup table mapping phone number prefixes to mobile network operators. Used for synchronous validation at import and contact-create time.

Column	Type	Notes
id	uuid PK
prefix	text unique	Digits only, no +. E.g. 254722 (Safaricom KE), 234 (Nigeria catch-all)
mno	text	Operator name (e.g. Safaricom, Airtel Kenya, Multiple)
country	text	ISO 3166-1 alpha-2 (e.g. KE, NG)
created_at	timestamp

Two-tier prefix strategy:

Tier	Prefix length	Coverage
1 — Country catch-all	2–3 digits	53 African countries; mno = 'Multiple'
2 — Operator-level	5–6 digits	KE, NG, UG, TZ, RW, GH, ZA, ET

151 rows total, seeded via migration 0011_seed_mno_prefixes.sql. The lookup algorithm loads all rows once per import job (sorted longest-prefix-first) and walks them greedily — operator-level rows win over country catch-alls. A match returns the mno and country; no match means the number is not attributable to any African network.

---

messages

Individual SMS messages. Each campaign generates one row per recipient.

Column	Type	Notes
id	uuid PK
account_id	uuid indexed
campaign_id	uuid indexed nullable	Present for bulk sends
channel	channel_type	Default: sms
to	text	Recipient phone number
from	text	Sender ID string used
body	text	Message content
status	message_status	Default: submitted
aggregator_message_id	text nullable	Celcom Africa's reference ID for DLR matching
credit_cost	numeric(14, 4) nullable	Finalised on DLR receipt
failure_reason	text nullable
sent_at	timestamp nullable
delivered_at	timestamp nullable
created_at	timestamp indexed
updated_at	timestamp

---

campaigns

Bulk SMS campaigns targeting a contact list.

Column	Type	Notes
id	uuid PK
account_id	uuid indexed
name	text	Campaign name
channel	channel_type	Default: sms
message	text	SMS body
sender_id	uuid FK → sender_ids
contact_list_id	uuid FK → contact_lists
status	campaign_status indexed
rejection_reason	text nullable	Set by SMS Content Worker on rejection
total_recipients	integer
sent_count	integer
delivered_count	integer
failed_count	integer
estimated_cost	numeric(14, 4) nullable	Calculated after content check
send_at	timestamp nullable indexed	Null = send immediately
started_at	timestamp nullable
completed_at	timestamp nullable
created_at	timestamp
updated_at	timestamp

---

sender_ids

Alphanumeric sender IDs registered for an account.

Column	Type	Notes
id	uuid PK
account_id	uuid FK → accounts
name	text	Alphanumeric sender string (e.g. AcmeCorp, max 11 chars for GSM)
status	sender_id_status	Default: pending. Only approved IDs can be used in sends
country	text nullable	ISO 3166-1 alpha-2 country the sender ID is registered for
is_default	boolean	Default: false — used when no sender ID is specified
created_at	timestamp
updated_at	timestamp

---

credits

One row per account. Tracks the real-time balance. Denormalised from credit_transactions for fast reads.

Column	Type	Notes
id	uuid PK
account_id	uuid unique FK → accounts
balance	numeric(14, 4)	Available credit
reserved_balance	numeric(14, 4)	Held for in-flight sends
currency	text	ISO 4217 code. Default: KES
updated_at	timestamp

---

credit_transactions

Immutable ledger of all credit movements. Never updated — only inserted.

Column	Type	Notes
id	uuid PK
account_id	uuid indexed
type	credit_tx_type
amount	numeric(14, 4)	Positive or negative
balance_after	numeric(14, 4)	Account balance snapshot after this transaction
description	text nullable	Human-readable reason
reference_id	uuid nullable	Links to messages.id or campaigns.id
created_at	timestamp indexed

---

pricing_rates

Per-operator SMS rates. A null account_id is the global default applied to all accounts without a custom rate.

Column	Type	Notes
id	uuid PK
account_id	uuid nullable	Null = platform default
mno	text	Mobile operator (e.g. Safaricom, Airtel)
country	text nullable	ISO 3166-1 alpha-2. Set for country-specific rate overrides
rate_per_sms	numeric(10, 6)	Cost per SMS segment in platform credits
created_at	timestamp
updated_at	timestamp

---

Migration History

Migrations live in src/db/migrations/. Generated ones are produced by drizzle-kit generate; manual ones are hand-authored SQL files with a corresponding meta/_journal.json entry.

File	Description
0000_initial_schema.sql	Base tables: accounts, users, contacts, messages, campaigns, credits, pricing_rates, sender_ids, api_keys, webhook_endpoints
0001_auth_tokens.sql	auth_tokens table — email verification, password reset, magic link
0002_refresh_tokens.sql	refresh_tokens table — session management with SHA-256 hash storage
0003_oauth_accounts.sql	oauth_accounts table — Google, Microsoft, GitHub, Apple provider links
0004_contact_lists.sql	contact_lists + contact_list_memberships junction table
0005_credit_transactions.sql	credit_transactions immutable ledger table
0006_mno_prefixes.sql	mno_prefixes lookup table schema
0007_account_members.sql	account_members junction table — multi-org support
0008_account_address.sql	address, city, state, zip_code columns on accounts
0009_valid_count.sql	valid_count + invalid_count columns on contact_lists
0010_schema_audit_fixes.sql	is_active on account_members; user_id NOT NULL on auth_tokens; channel on messages/campaigns; sender_id_status enum + columns; consent fields on contacts; currency on credits; country on pricing_rates; self-referential FKs on accounts
0011_seed_mno_prefixes.sql	Seed data: 151 MNO prefix rows across 53 African countries