Database Design

Database Design

Version: 1.1 Date: 2026-03-04 Status: Approved Owner: Database Architect

Last Updated: 2026-03-04 — Corrected to match schema.ts. Fixed table count (19→20), data types (TEXT→TIMESTAMPTZ for timestamps, REAL→INTEGER for monetary amounts), added missing tables: otp_codes, refresh_tokens, user_preferences, withdrawal_requests, ob_consents. Removed deprecated rate_limits table.

---

Design Philosophy

Drop's database schema is designed around three principles:

1. Simplicity over abstraction. 23 tables for a well-scoped fintech app. No generic "entities" table, no EAV patterns. Each table maps to a clear domain concept.
2. Compliance by design. 7 of 23 tables exist solely for regulatory requirements (GDPR, AML, PSD2). They were added as a compliance infrastructure layer, not retrofitted.
3. PostgreSQL-native. Schema is defined in Drizzle ORM (src/shared/db/schema.ts) targeting PostgreSQL 16. PostgreSQL-native features (JSONB, FOR UPDATE, RETURNING, arrays) are available and used where beneficial. See ADR-014.

Why 23 Tables

The table count reflects the actual domain:

• 16 core tables cover the business logic: users, bank accounts, recipients, merchants, transactions, exchange rates, cards, sessions, OTP codes, refresh tokens, notifications, settings, user preferences, spending limits, Open Banking consents, and withdrawal requests.
• 7 compliance tables were added as a single compliance infrastructure layer: audit logging, AML alerts, STR reports, sanctions screening, GDPR consent tracking, data access requests, and complaints.

Removed: rate_limits table was eliminated in the C3 refactor — replaced by an in-memory rate limiter. It no longer exists in schema.ts.

No table is redundant. No table combines unrelated concerns.

---

Complete Schema ERD

erDiagram
    users ||--|| settings : "1:1 notification prefs"
    users ||--|| user_preferences : "1:1 extended prefs"
    users ||--o{ bank_accounts : "1:N linked accounts"
    users ||--o{ cards : "1:N payment cards"
    users ||--o{ recipients : "1:N saved recipients"
    users ||--o{ transactions : "1:N financial ops"
    users ||--o{ sessions : "1:N auth sessions"
    users ||--o{ refresh_tokens : "1:N refresh tokens"
    users ||--o{ otp_codes : "1:N OTP verifications"
    users ||--o{ notifications : "1:N alerts"
    users ||--o{ spending_limits : "1:N limits"
    users ||--o{ merchants : "1:N merchant profiles"
    users ||--o{ ob_consents : "1:N OB consents"
    users ||--o{ withdrawal_requests : "1:N closure requests"
    users ||--o{ audit_log : "1:N audit entries"
    users ||--o{ aml_alerts : "1:N AML flags"
    users ||--o{ str_reports : "1:N STR filings"
    users ||--o{ screening_results : "1:N screenings"
    users ||--o{ consents : "1:N GDPR consents"
    users ||--o{ data_access_requests : "1:N DSARs"
    users ||--o{ complaints : "1:N complaints"

    transactions }o--o| recipients : "remittance target"
    transactions }o--o| merchants : "QR payment target"
    transactions ||--o{ aml_alerts : "triggers alert"
    aml_alerts ||--o{ str_reports : "escalates to STR"
    cards ||--o{ spending_limits : "card-level limits"
    bank_accounts ||--o{ ob_consents : "consent per account"

    users {
        text id PK "usr_ + 16 hex"
        text email UK "NOT NULL"
        text password_hash "NOT NULL, default EIDONLY"
        text auth_provider "default bankid"
        text first_name "NOT NULL"
        text last_name "NOT NULL"
        text phone "nullable"
        text date_of_birth "nullable"
        text kyc_status "default pending"
        text role "default user"
        text risk_level "default low"
        text pep_status "default not_checked"
        integer sanctions_cleared "default 0"
        text kyc_method "nullable"
        timestamptz kyc_verified_at "nullable"
        text national_id_hash "nullable, indexed"
        text national_id_encrypted "AES-256-GCM fødselsnummer"
        integer phone_verified "default 0"
        timestamptz onboarded_at "nullable"
        timestamptz deleted_at "nullable, soft delete"
        timestamptz created_at "default NOW()"
    }

    transactions {
        text id PK
        text user_id FK "NOT NULL"
        text type "remittance|qr_payment"
        text status "processing|completed|failed"
        integer amount "NOT NULL, øre"
        text currency "default NOK"
        integer fee "default 0, øre"
        text recipient_id FK "nullable"
        text merchant_id FK "nullable"
        integer send_amount "nullable, øre"
        text send_currency "nullable"
        integer receive_amount "nullable, øre"
        text receive_currency "nullable"
        real exchange_rate "nullable, ratio"
        text purpose_code "nullable"
        text idempotency_key "UNIQUE per user"
        timestamptz created_at "default NOW()"
        timestamptz completed_at "nullable"
    }

    bank_accounts {
        text id PK
        text user_id FK "NOT NULL"
        text bank_name "NOT NULL"
        text account_number "NOT NULL"
        text iban "nullable"
        integer balance "default 0, cached AISP, øre"
        timestamptz balance_synced_at "nullable"
        text currency "default NOK"
        integer is_primary "default 0"
        timestamptz connected_at "default NOW()"
    }

    merchants {
        text id PK
        text user_id FK "NOT NULL"
        text business_name "NOT NULL"
        text org_number "UNIQUE NOT NULL"
        text address "nullable"
        text bank_account "NOT NULL"
        real fee_rate "default 0.01, ratio"
        text status "default active"
        text qr_hmac_key "NOT NULL, random 32 bytes"
        timestamptz created_at "default NOW()"
    }

    recipients {
        text id PK
        text user_id FK "NOT NULL"
        text name "NOT NULL"
        text country "NOT NULL"
        text currency "NOT NULL"
        text bank_account "NOT NULL"
        text bank_name "nullable"
        timestamptz created_at "default NOW()"
    }

---

Table-by-Table Design Rationale

Core Tables

users

Normalization: 3NF. All columns are functionally dependent on the primary key.

Design decisions:

• id uses usr_ prefix + 16 hex chars for readability and collision avoidance across distributed systems.
• password_hash defaults to 'EIDONLY' sentinel value — BankID-only users have no password. This avoids nullable password fields that complicate auth logic.
• auth_provider tracks how the user registered (bankid). Supports future Vipps Login without schema changes.
• national_id_hash stores SHA-256 of Norwegian fødselsnummer. Enables user deduplication across auth providers without storing the raw national ID.
• national_id_encrypted stores AES-256-GCM encrypted fødselsnummer (format: v1:<iv_hex>:<tag_hex>:<ciphertext_hex>). Required for GDPR data export (Art. 20) and Finanstilsynet regulatory identity requests.
• phone_verified (0/1 integer) tracks phone verification status via OTP.
• deleted_at enables soft delete for GDPR erasure while retaining records for AML legal obligations (5-year retention).
• risk_level, pep_status, sanctions_cleared are denormalized onto the user for fast access during transaction authorization — these are checked on every financial operation.
• KYC fields (kyc_status, kyc_method, kyc_verified_at) are on the user table rather than a separate KYC table because there is a 1:1 relationship and the fields are accessed on every authenticated request.

transactions

Normalization: 3NF with intentional denormalization.

Design decisions:

• amount, fee, send_amount, receive_amount are stored as integers in øre (100 øre = 1 NOK) to avoid floating-point precision issues. exchange_rate is a ratio, stored as REAL.
• Polymorphic reference: recipient_id is set for remittances, merchant_id for QR payments. Never both. This avoids a separate join table for a simple either/or relationship.
• exchange_rate is denormalized (snapshot at transaction time) because rates change. The rate at execution time must be preserved for audit and dispute resolution.
• idempotency_key with a UNIQUE index on (user_id, idempotency_key) prevents duplicate transaction submission without requiring every transaction to have a key.
• purpose_code supports remittance regulatory requirements (some corridors require a transfer purpose).
• completed_at is separate from created_at to track processing duration.

bank_accounts

Normalization: 3NF.

Design decisions:

• balance is a cached read-only value from AISP, stored as integer øre — not a Drop-held balance. This is the most important design detail in the entire schema.
• balance_synced_at tracks when the balance was last refreshed from the bank via Open Banking.
• is_primary flag determines which account is used for transactions by default (1 = primary, 0 = secondary).
• No unique constraint on account_number because the same bank account could theoretically appear under different user records (shared accounts).

recipients

Normalization: 3NF.

Design decisions:

• Scoped to user (user_id FK) — recipients are private, not shared.
• country and currency stored as free text validated at the API layer (not as FK to a countries table). This avoids over-engineering for 5-6 supported corridors.
• bank_account stores the full foreign account number. Format varies by country (IBAN for EU, local format for others).

merchants

Normalization: 3NF.

Design decisions:

• org_number is UNIQUE — one merchant registration per Norwegian organization number (9 digits).
• qr_hmac_key is generated server-side (encode(gen_random_bytes(32), 'hex')) for QR code integrity verification. Each merchant gets a unique key.
• fee_rate defaults to 0.01 (1%). Stored as REAL per merchant (it is a ratio, not money) to allow variable pricing in the future.
• user_id FK links the merchant to the user who registered it. A user's role is upgraded to merchant upon registration.

exchange_rates

Normalization: 3NF.

Design decisions:

• id uses SERIAL PRIMARY KEY — the only auto-increment ID in the schema. Exchange rates are system-managed, not user-created, so prefixed IDs are unnecessary.
• rate is stored as REAL because it is a ratio, not a monetary value.
• Only stores NOK-to-X rates (6 corridors). Inverse rates are calculated at runtime.
• No historical rate tracking in this table. Transaction records snapshot the rate at execution time.

sessions

Normalization: 3NF.

Design decisions:

• token_hash stores SHA-256 of the JWT, not the JWT itself. This prevents session hijacking even if the database is compromised.
• revoked flag (0/1) enables server-side session invalidation without waiting for JWT expiry.
• last_activity_at tracks the most recent activity timestamp for session idle-timeout enforcement.
• Multiple active sessions per user are allowed (different devices).

otp_codes

Normalization: 3NF.

Design decisions:

• Scoped to phone (not user_id) because OTPs are issued before user authentication.
• code_hash stores hashed OTP — never the raw code.
• attempts counter enables lockout after N failed attempts without a separate table.
• used flag (0/1) prevents OTP reuse.
• Index on expires_at supports efficient cleanup of expired codes.

refresh_tokens

Normalization: 3NF.

Design decisions:

• Separate from sessions to support long-lived refresh / short-lived access token pattern.
• token_hash stores hashed refresh token — never the raw token.
• revoked flag enables immediate invalidation (e.g., on suspicious activity, logout-all).

settings

Normalization: 3NF. 1:1 with users.

Design decisions:

• user_id as PRIMARY KEY enforces the 1:1 relationship at the database level.
• Created lazily on first GET /api/settings (INSERT default if not exists).
• Defaults: currency='NOK', language='nb', push_enabled=1, email_enabled=1.

user_preferences

Normalization: 3NF. 1:1 with users.

Design decisions:

• Extends settings with granular notification controls and biometrics flag.
• user_id as PRIMARY KEY enforces the 1:1 relationship.
• Notification fields split by category (transactions, security, marketing) to comply with GDPR consent granularity requirements.
• biometrics_enabled (0/1) stores user opt-in for biometric auth on the mobile app.

notifications

Normalization: 3NF.

Design decisions:

• read flag (0/1) for marking notifications as read in batch.
• No foreign key to the triggering entity (transaction, system event) — type field categorizes the notification source.
• Composite index on (user_id, created_at) supports efficient pagination of the notification feed.

cards (FUTURE)

Normalization: 3NF.

Design decisions:

• Feature-flagged. Table exists in schema but endpoints return 404 when disabled.
• Only stores last_four and token_ref — never full card number or CVV (PCI-DSS compliance).
• pin_hash in schema for PIN support.
• status supports freeze/unfreeze without deletion (active -> frozen -> active).

spending_limits (FUTURE)

Normalization: 3NF.

Design decisions:

• card_id is nullable — supports user-level limits (no specific card) or card-level limits.
• limit_type values (daily, weekly, monthly, transaction) enforced at API level.
• amount stored as integer øre (consistent with all other monetary values).
• Limits are replaced, not accumulated (PUT semantics per limit type per card).

ob_consents

Normalization: 3NF.

Design decisions:

• Tracks PSD2 Open Banking consent lifecycle per user per provider.
• bank_account_id FK is nullable — consent may exist before a specific bank account is linked.
• scope distinguishes read-only (AISP) from payment initiation (PISP) or combined access.
• access_count_today + last_access_date implement daily access quota enforcement without a separate rate-limit table.
• external_consent_id stores the consent ID issued by the Open Banking provider for correlation.

withdrawal_requests

Normalization: 3NF.

Design decisions:

• Separates the intent to close an account from the actual deleted_at soft-delete on users.
• reason and comment capture user feedback for product analytics and regulatory reporting.
• status workflow: pending -> processing -> completed|rejected.

Compliance Tables

audit_log

• user_id is nullable because some audit events occur before authentication (e.g., failed login attempts).
• resource_type + resource_id enable generic resource tracking without polymorphic FKs.
• details is a TEXT field (JSON string) for flexible event-specific data.
• request_id for correlating multiple audit entries from a single API request.
• severity (default 'INFO') supports tiered alerting and log filtering.
• session_id correlates audit events to a specific session.
• Five indexes: user_id, timestamp, action, (user_id, timestamp), (action, timestamp) — covering all primary query patterns.

aml_alerts

• severity (low/medium/high/critical) determines investigation priority and escalation timelines.
• status workflow: open -> investigating -> resolved|escalated|filed.
• reviewed_by and reviewed_at (TIMESTAMPTZ) track the compliance officer's review.
• transaction_id FK links to the specific transaction that triggered the alert.

str_reports

• Filed with Okokrim/EFE (Norwegian financial intelligence unit).
• reference_number stores the authority-assigned reference after submission.
• alert_id links back to the originating AML alert.
• Immutable after status = 'submitted' — regulatory requirement.

screening_results

• screening_type (pep/sanctions/adverse_media) supports multiple screening categories.
• provider tracks which screening service was used (future: Sumsub, Refinitiv, etc.).
• match_details stores full match information as TEXT (JSON) for review.
• Multiple results per user (periodic rescreening).

consents

• consent_type values: terms, privacy, marketing, cookies_analytics, cookies_marketing.
• granted (0/1) with separate granted_at/withdrawn_at timestamps (TIMESTAMPTZ) for full consent lifecycle.
• ip_address stored as proof of consent action per GDPR requirements.

data_access_requests

• request_type covers GDPR data subject rights: export (Art. 15), erasure (Art. 17), rectification (Art. 16), restriction (Art. 18).
• download_url for data export files (temporary signed URLs).
• status workflow: pending -> processing -> completed|rejected.

complaints

• Required by Finansavtaleloven section 3-53 (15 business day response requirement).
• category (transaction/service/fees/privacy/technical/other) for routing and reporting.
• resolution text field filled when complaint is resolved.
• resolved_at (TIMESTAMPTZ) for SLA compliance tracking.

---

Constraint Inventory

Table	Constraint Type	Column(s)	Value
users	PRIMARY KEY	id	-
users	UNIQUE	email	-
users	NOT NULL	email, password_hash, first_name, last_name	-
transactions	PRIMARY KEY	id	-
transactions	NOT NULL	user_id, type, amount	-
transactions	FK	user_id	users(id)
transactions	FK	recipient_id	recipients(id)
transactions	FK	merchant_id	merchants(id)
transactions	UNIQUE	(user_id, idempotency_key)	via idx_tx_idempotency
merchants	PRIMARY KEY	id	-
merchants	UNIQUE	org_number	-
merchants	NOT NULL	user_id, business_name, org_number, bank_account, qr_hmac_key	-
merchants	FK	user_id	users(id)
bank_accounts	PRIMARY KEY	id	-
bank_accounts	NOT NULL	user_id, bank_name, account_number	-
bank_accounts	FK	user_id	users(id)
recipients	PRIMARY KEY	id	-
recipients	NOT NULL	user_id, name, country, currency, bank_account	-
recipients	FK	user_id	users(id)
sessions	PRIMARY KEY	id	-
sessions	NOT NULL	user_id, token_hash, expires_at	-
sessions	FK	user_id	users(id)
otp_codes	PRIMARY KEY	id	-
otp_codes	NOT NULL	phone, code_hash, expires_at	-
refresh_tokens	PRIMARY KEY	id	-
refresh_tokens	NOT NULL	user_id, token_hash, expires_at	-
refresh_tokens	FK	user_id	users(id)
cards	PRIMARY KEY	id	-
cards	NOT NULL	user_id, last_four, expiry	-
cards	FK	user_id	users(id)
settings	PRIMARY KEY	user_id	1:1 with users
settings	FK	user_id	users(id)
user_preferences	PRIMARY KEY	user_id	1:1 with users
user_preferences	FK	user_id	users(id)
exchange_rates	PRIMARY KEY	id	SERIAL AUTOINCREMENT
exchange_rates	NOT NULL	to_currency, rate	-
notifications	PRIMARY KEY	id	-
notifications	NOT NULL	user_id, type, title, body	-
notifications	FK	user_id	users(id)
spending_limits	PRIMARY KEY	id	-
spending_limits	NOT NULL	user_id, limit_type, amount	-
spending_limits	FK	user_id, card_id	users(id), cards(id)
ob_consents	PRIMARY KEY	id	-
ob_consents	NOT NULL	user_id, provider, expires_at	-
ob_consents	FK	user_id, bank_account_id	users(id), bank_accounts(id)
withdrawal_requests	PRIMARY KEY	id	-
withdrawal_requests	NOT NULL	user_id	-
withdrawal_requests	FK	user_id	users(id)
audit_log	PRIMARY KEY	id	-
audit_log	NOT NULL	action	-
audit_log	FK	user_id	users(id) (nullable)
aml_alerts	PRIMARY KEY	id	-
aml_alerts	NOT NULL	user_id, alert_type, severity	-
aml_alerts	FK	user_id, transaction_id	users(id), transactions(id)
str_reports	PRIMARY KEY	id	-
str_reports	NOT NULL	user_id, report_type	-
str_reports	FK	user_id, alert_id	users(id), aml_alerts(id)
screening_results	PRIMARY KEY	id	-
screening_results	NOT NULL	user_id, screening_type, result	-
screening_results	FK	user_id	users(id)
consents	PRIMARY KEY	id	-
consents	NOT NULL	user_id, consent_type, granted	-
consents	FK	user_id	users(id)
data_access_requests	PRIMARY KEY	id	-
data_access_requests	NOT NULL	user_id, request_type	-
data_access_requests	FK	user_id	users(id)
complaints	PRIMARY KEY	id	-
complaints	NOT NULL	user_id, category, subject, description	-
complaints	FK	user_id	users(id)

---

Naming Conventions

Convention	Rule	Examples
Table names	Lowercase, plural, snake_case	users, bank_accounts, aml_alerts
Column names	Lowercase, snake_case	user_id, first_name, created_at
Primary keys	id (or entity-name for 1:1 tables like settings.user_id)	users.id, settings.user_id
Foreign keys	{referenced_table_singular}_id	user_id, recipient_id, card_id
Timestamps	{action}_at suffix, TIMESTAMPTZ type	created_at, completed_at, withdrawn_at
Boolean flags	Descriptive name, INTEGER 0/1	revoked, read, is_primary, granted
Monetary amounts	INTEGER in øre	amount, fee, send_amount, balance
Ratios / rates	REAL	exchange_rate, fee_rate, rate
Status columns	status with documented values	status (values enforced at API layer)
Indexes	idx_{table}_{column} (exception: idx_tx_idempotency uses abbreviation)	idx_transactions_user, idx_sessions_token, idx_tx_idempotency
ID prefixes	3-letter prefix + underscore + 16 hex chars	usr_, tx_, ba_, mer_, rec_, ses_, con_, cmp_

---

Normalization Analysis

All tables are in Third Normal Form (3NF) with documented exceptions:

Table	NF Level	Deviation	Justification
users	3NF	risk_level, pep_status, sanctions_cleared could be in a separate risk_profile table	Accessed on every transaction check. Separate table would add a JOIN to the critical path. 1:1 relationship makes a separate table pointless.
transactions	3NF	exchange_rate, send_amount, receive_amount denormalized from exchange_rates	Rate at execution time must be preserved immutably. The exchange_rates table changes; the transaction record must not.
transactions	3NF	Polymorphic FK (recipient_id OR merchant_id)	Simple either/or. A join table or STI would add complexity for no benefit at this scale.
bank_accounts	3NF	balance denormalized from external bank (AISP)	This is a cache, not authoritative data. Drop cannot modify the real balance.
audit_log	3NF	details is unstructured TEXT (JSON)	Audit events have variable structure. A normalized schema would require dozens of event-specific tables.
ob_consents	3NF	access_count_today + last_access_date denormalized onto consent	Avoids a separate daily-quota table for a simple daily counter reset pattern.

No table violates 2NF (no partial key dependencies) because all tables use single-column primary keys.

---

Cross-References

• Full schema: DATABASE-SCHEMA.md
• Data architecture overview: data-architecture.md
• Migration strategy: migration-strategy.md
• Schema source of truth: src/shared/db/schema.ts