Skip to main content

Database Design

Database Design

Version: 1.0 Date: 2026-02-21 Status: Approved Owner: Database Architect

Design Philosophy

Drop's database schema is designed around three principles:

  1. Simplicity over abstraction. 19 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 19 tables exist solely for regulatory requirements (GDPR, AML, PSD2). They were added as a compliance infrastructure layer, not retrofitted.
  3. Dual-driver portability. Schema uses the intersection of SQLite and PostgreSQL capabilities. No database-specific features (e.g., PostgreSQL arrays, SQLite JSON1) in the schema definition.

Why 19 Tables

The table count reflects the actual domain:

  • 12 core tables cover the business logic: users, their bank accounts, recipients, merchants, transactions, exchange rates, cards, sessions, notifications, settings, spending limits, and rate limits.
  • 7 compliance tables were added as a single compliance infrastructure layer: audit logging, AML alerts, STR reports, sanctions screening, consent tracking, data access requests, and complaints.

No table is redundant. No table combines unrelated concerns.

Complete Schema ERD

erDiagram
    users ||--|| settings : "1:1 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{ notifications : "1:N alerts"
    users ||--o{ spending_limits : "1:N limits"
    users ||--o{ merchants : "1:N merchant profiles"
    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 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"

    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 "CHECK pending|approved|rejected"
        text role "CHECK user|merchant"
        text risk_level "CHECK low|medium|high"
        text pep_status "CHECK not_checked|clear|match|pending_review"
        integer sanctions_cleared "default 0"
        text kyc_method "CHECK bankid|document|simplified"
        text kyc_verified_at "nullable"
        text national_id_hash "nullable, indexed WHERE NOT NULL"
        text deleted_at "nullable, soft delete"
        text created_at "default 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, in minor units"
        text currency "default NOK"
        integer fee "default 0"
        text recipient_id FK "nullable"
        text merchant_id FK "nullable"
        integer send_amount "nullable"
        text send_currency "nullable"
        integer receive_amount "nullable"
        text receive_currency "nullable"
        real exchange_rate "nullable"
        text purpose_code "nullable"
        text idempotency_key "UNIQUE WHERE NOT NULL"
        text created_at "default datetime now"
        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"
        text balance_synced_at "nullable"
        text currency "default NOK"
        integer is_primary "default 0"
        text connected_at "default 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"
        text status "default active"
        text qr_hmac_key "NOT NULL, random 32 bytes"
        text created_at "default 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"
        text created_at "default 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 fodselsnummer. Enables user deduplication across auth providers without storing the raw national ID.
  • 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 minor units (ore for NOK, para for RSD, etc.) to avoid floating-point precision issues.
  • 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 partial index (WHERE 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, 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 (hex(randomblob(32))) for QR code integrity verification. Each merchant gets a unique key.
  • fee_rate defaults to 0.01 (1%). Stored per merchant 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 INTEGER PRIMARY KEY AUTOINCREMENT -- the only auto-increment ID in the schema. Exchange rates are system-managed, not user-created, so prefixed IDs are unnecessary.
  • 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.
  • Multiple active sessions per user are allowed (different devices).

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.

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.
  • Designed for high volume with eventual cleanup (no retention policy 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 added via runtime migration for backward 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.
  • Limits are replaced, not accumulated (PUT semantics per limit type per card).

rate_limits

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

Design decisions:

  • key is the IP address (TEXT PK). Simple key-value store.
  • reset_at is a Unix timestamp. Expired entries are cleaned every 100 rate limit checks in middleware/rate-limit.ts.
  • Not a "real" domain table -- it is 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.
  • Two indexes: user_id and action for the 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 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 timestamp 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 -
users CHECK kyc_status IN ('pending','approved','rejected')
users CHECK role IN ('user','merchant')
users CHECK risk_level IN ('low','medium','high')
users CHECK pep_status IN ('not_checked','clear','match','pending_review')
users CHECK kyc_method IN ('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 CHECK type IN ('remittance','qr_payment')
transactions CHECK status IN ('processing','completed','failed')
transactions UNIQUE (partial) idempotency_key WHERE 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)
cards PRIMARY KEY id -
cards NOT NULL user_id, last_four, expiry -
cards FK user_id users(id)
cards CHECK type IN ('virtual','physical')
cards CHECK status IN ('active','frozen','cancelled')
settings PRIMARY KEY user_id 1:1 with users
settings FK user_id users(id)
exchange_rates PRIMARY KEY id 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)
rate_limits PRIMARY KEY key IP 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_alerts CHECK severity IN ('low','medium','high','critical')
aml_alerts CHECK status IN ('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_reports CHECK status IN ('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_results CHECK screening_type IN ('pep','sanctions','adverse_media')
screening_results CHECK result IN ('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_requests CHECK request_type IN ('export','erasure','rectification','restriction')
data_access_requests CHECK status IN ('pending','processing','completed','rejected')
complaints PRIMARY KEY id -
complaints NOT NULL user_id, category, subject, description -
complaints FK user_id users(id)
complaints CHECK status IN ('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 created_at, completed_at, withdrawn_at
Boolean flags Descriptive name, INTEGER 0/1 revoked, read, is_primary, granted
Status columns status with CHECK constraint status CHECK(... IN (...))
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.

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

Cross-References

Back to top