Skip to main content

Database Design

Database Design

Version: 1.10 Date: 2026-03-0402-21 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. 2319 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 2319 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 2319 Tables

The table count reflects the actual domain:

  • 1612 core tables cover the business logic: users, their bank accounts, recipients, merchants, transactions, exchange rates, cards, sessions, OTP codes, refresh tokens, notifications, settings, user preferences, spending limits, Open Banking consents, and withdrawalrate requests.limits.
  • 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"preferences"
    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 "defaultCHECK pending"pending|approved|rejected"
        text role "defaultCHECK user"user|merchant"
        text risk_level "defaultCHECK low"low|medium|high"
        text pep_status "defaultCHECK not_checked"not_checked|clear|match|pending_review"
        integer sanctions_cleared "default 0"
        text kyc_method "nullable"CHECK timestamptzbankid|document|simplified"
        text kyc_verified_at "nullable"
        text national_id_hash "nullable, indexed"indexed WHERE NOT NULL"
        text national_id_encrypted "AES-256-GCM fødselsnummer"
        integer phone_verified "default 0"
        timestamptz onboarded_at "nullable"
        timestamptz deleted_at "nullable, soft delete"
        timestamptztext created_at "default NOW()"datetime now"
    }

    transactions {
        text id PK
        text user_id FK "NOT NULL"
        text type "CHECK remittance|qr_payment"
        text status "CHECK processing|completed|failed"
        integer amount "NOT NULL, øre"in minor units"
        text currency "default NOK"
        integer fee "default 0, øre"0"
        text recipient_id FK "nullable"
        text merchant_id FK "nullable"
        integer send_amount "nullable, øre"nullable"
        text send_currency "nullable"
        integer receive_amount "nullable, øre"nullable"
        text receive_currency "nullable"
        real exchange_rate "nullable, ratio"nullable"
        text purpose_code "nullable"
        text idempotency_key "UNIQUE perWHERE user"NOT timestamptzNULL"
        text created_at "default NOW()"datetime timestamptznow"
        text 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,AISP"
        øre"
        timestamptztext balance_synced_at "nullable"
        text currency "default NOK"
        integer is_primary "default 0"
        timestamptztext connected_at "default NOW()"datetime 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"01"
        text status "default active"
        text qr_hmac_key "NOT NULL, random 32 bytes"
        timestamptztext created_at "default NOW()"datetime 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"
        timestamptztext created_at "default NOW()"datetime 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.fodselsnummer. 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 øreminor units (100ore ørefor =NOK, 1para NOK)for RSD, etc.) 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 UNIQUEunique partial index on(WHERE (user_id,idempotency_key idempotency_key)IS NOT NULL) 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(hex(randomblob(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 SERIALINTEGER PRIMARY KEY AUTOINCREMENT —-- 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.
  • CompositeDesigned indexfor onhigh volume with eventual cleanup (user_id,no created_at)retention supportspolicy efficient pagination of the notification feed.yet).

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 inadded schemavia runtime migration for PINbackward support.compatibility.
  • 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_consentsrate_limits

Normalization: 3NF.3NF (trivial -- 3 columns).

Design decisions:

  • Trackskey PSD2is Openthe BankingIP consentaddress lifecycle(TEXT perPK). userSimple perkey-value provider.store.
  • bank_account_idreset_at FK is nullable — consent may exist before a specificUnix banktimestamp. accountExpired isentries linked.
    • are
  • scopecleaned distinguishesevery read-only100 (AISP)rate fromlimit paymentchecks initiation (PISP) or combined access.
  • access_count_today +in last_access_date implement daily access quota enforcement without a separate middleware/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 userslimit.ts.
  • reasonNot anda comment"real" capturedomain user feedback for product analytics and regulatory reporting.
  • status workflow: pendingtable ->- processingit ->is completed|rejected.infrastructure. Could be replaced by Redis in production but works fine in SQLite/PostgreSQL.

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.
  • FiveTwo indexes: user_id, timestamp,and action, (user_id,for timestamp), (action, timestamp) — covering allthe primary query patterns. (Note: no timestamp index exists in the implementation — only idx_audit_log_user and idx_audit_log_action are created in db.ts.)

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

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)timestamp for SLA compliance tracking.

Constraint Inventory

IP
Table Constraint Type Column(s) Value
users PRIMARY KEY id -
users UNIQUE email -
users NOT NULL email, password_hash, first_name, last_name -
usersCHECKkyc_statusIN ('pending','approved','rejected')
usersCHECKroleIN ('user','merchant')
usersCHECKrisk_levelIN ('low','medium','high')
usersCHECKpep_statusIN ('not_checked','clear','match','pending_review')
usersCHECKkyc_methodIN ('bankid','document','simplified')
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 UNIQUECHECK (user_id, idempotency_key)type viaIN ('remittance','qr_payment')
idx_tx_idempotencytransactionsCHECKstatusIN ('processing','completed','failed')
transactionsUNIQUE (partial)idempotency_keyWHERE idempotency_key IS NOT NULL
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_codesPRIMARY KEYid-
otp_codesNOT NULLphone, code_hash, expires_at-
refresh_tokensPRIMARY KEYid-
refresh_tokensNOT NULLuser_id, token_hash, expires_at-
refresh_tokensFKuser_idusers(id)
cards PRIMARY KEY id -
cards NOT NULL user_id, last_four, expiry -
cards FK user_id users(id)
cardsCHECKtypeIN ('virtual','physical')
cardsCHECKstatusIN ('active','frozen','cancelled')
settings PRIMARY KEY user_id 1:1 with users
settings FK user_id users(id)
user_preferencesPRIMARY KEYuser_id1:1 with users
user_preferencesFKuser_idusers(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_consentsrate_limits PRIMARY KEY idkey -
ob_consentsNOT NULLuser_id, provider, expires_at-
ob_consentsFKuser_id, bank_account_idusers(id), bank_accounts(id)
withdrawal_requestsPRIMARY KEYid-
withdrawal_requestsNOT NULLuser_id-
withdrawal_requestsFKuser_idusers(id)address
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)
aml_alertsCHECKseverityIN ('low','medium','high','critical')
aml_alertsCHECKstatusIN ('open','investigating','resolved','escalated','filed')
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)
str_reportsCHECKstatusIN ('draft','submitted','acknowledged')
screening_results PRIMARY KEY id -
screening_results NOT NULL user_id, screening_type, result -
screening_results FK user_id users(id)
screening_resultsCHECKscreening_typeIN ('pep','sanctions','adverse_media')
screening_resultsCHECKresultIN ('clear','match','potential_match','error')
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)
data_access_requestsCHECKrequest_typeIN ('export','erasure','rectification','restriction')
data_access_requestsCHECKstatusIN ('pending','processing','completed','rejected')
complaints PRIMARY KEY id -
complaints NOT NULL user_id, category, subject, description -
complaints FK user_id users(id)
complaintsCHECKstatusIN ('received','investigating','resolved','escalated')

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 typesuffix created_at, completed_at, withdrawn_at
Boolean flags Descriptive name, INTEGER 0/1 revoked, read, is_primary, granted
Monetary amountsINTEGER in øreamount, fee, send_amount, balance
Ratios / ratesREALexchange_rate, fee_rate, rate
Status columns status with documentedCHECK valuesconstraint status CHECK(... IN (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_consents3NFaccess_count_today + last_access_date denormalized onto consentAvoids 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

Back to top