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:

Simplicity over abstraction. 1925 tables for a well-scoped fintech app. No generic "entities" table, no EAV patterns. Each table maps to a clear domain concept.
Compliance by design. 7 of the original 19 tables exist solely for regulatory requirements (GDPR, AML, PSD2). ~~They~~6 additional operational and compliance tables were added asto aaddress ~~compliance~~reconciliation, ~~infrastructure~~circuit ~~layer,~~breaker ~~not~~state, ~~retrofitted.~~webhook processing, and dispute handling requirements.
PostgreSQL-native. Schema targets PostgreSQL 16 exclusively (ADR-014 supersedes ADR-006/010). PostgreSQL-specific features (arrays, JSONB, partial indexes, TIMESTAMPTZ) are available where beneficial. No SQLite compatibility constraints.

Why 1925 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.

6 operational tables were added to address critical operational requirements (FR-073 through FR-077): reconciliation reporting, discrepancy tracking, circuit breaker state persistence, webhook event logging, dead-letter queue management, and payment dispute handling.

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"

    reconciliation_reports ||--o{ reconciliation_discrepancies : "has discrepancies"
    transactions }o--o| reconciliation_discrepancies : "referenced in discrepancy"
    transactions ||--o{ webhook_events : "status updates"
    webhook_events ||--o{ webhook_dlq : "dead-lettered"
    transactions ||--o{ disputes : "disputed by user"
    users ||--o{ disputes : "submits dispute"
    transactions }o--o| disputes : "refund transaction"

    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`

consent_type values: terms, privacy, marketing, cookies_analytics, cookies_marketing.
granted (0/1) with separate granted_at/withdrawn_at timestamps 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 timestamp for SLA compliance tracking.

Operational Tables

`reconciliation_reports`

Source: FR-073 (Daily Transaction Reconciliation).

Design decisions:

One record per reconciliation run. The report_date (DATE) identifies which day's transactions were compared, not when the job ran. This is the natural primary query key for the admin dashboard.

status follows the lifecycle: pending (job started) → matched (clean run, no discrepancies) → discrepancies_found (at least one discrepancy detected). A failed variant should be handled at the application layer by leaving status as pending and recording error details in report_data.

total_drop_transactions and total_partner_transactions are separate counts — their difference is a quick audit signal that one side is missing records entirely.

matched_count and discrepancy_count should sum to total_drop_transactions when all Drop records are accounted for.

report_data (JSONB) stores the full machine-readable reconciliation output — all matched pairs, all discrepancy details — for audit trail purposes. This is retained for the 5-year AML record-keeping obligation (Hvitvaskingsloven § 30). Querying individual discrepancies uses the reconciliation_discrepancies table; the JSONB is the archival copy.

completed_at is nullable — it is null while the job is running and set when the job finishes.

Column	Type	Constraint	Description
`id`	UUID	PRIMARY KEY	Internal report ID
`report_date`	DATE	NOT NULL	The transaction date being reconciled (T-1)
`status`	TEXT	CHECK `IN ('pending','matched','discrepancies_found')`	Job lifecycle status
`total_drop_transactions`	INTEGER	NOT NULL, DEFAULT 0	Count of Drop transactions for this date
`total_partner_transactions`	INTEGER	NOT NULL, DEFAULT 0	Count of ZTL transactions for this date
`matched_count`	INTEGER	NOT NULL, DEFAULT 0	Count of matched (clean) transactions
`discrepancy_count`	INTEGER	NOT NULL, DEFAULT 0	Count of discrepancies found
`report_data`	JSONB	NULLABLE	Full reconciliation detail — all records, matches, discrepancies
`created_at`	TIMESTAMPTZ	DEFAULT now()	When the job started
`completed_at`	TIMESTAMPTZ	NULLABLE	When the job completed

`reconciliation_discrepancies`

Source: FR-073 (Daily Transaction Reconciliation).

Design decisions:

One record per discrepancy found in a reconciliation run. Linked to the parent reconciliation_reports record via report_id.

transaction_id is nullable — a missing_in_drop discrepancy, by definition, has no corresponding Drop transaction record. In this case, partner_reference is the only identifier.

partner_reference stores the ZTL settlement record's reference. For discrepancies where both sides exist, both transaction_id and partner_reference are populated, enabling cross-referencing.

discrepancy_type is a closed enum: missing_in_partner, missing_in_drop, amount_mismatch, status_mismatch. No catch-all "other" — every discrepancy must be categorised.

drop_amount and partner_amount are stored in minor units (ore), matching the transactions table convention. Nullable because the relevant amount is absent when a record is missing on one side.

resolution_status workflow: open → investigating → resolved (discrepancy explained and accepted) or accepted (discrepancy accepted as a known tolerable difference, e.g., fee rounding).

resolved_by and resolved_at track which compliance officer resolved the discrepancy. notes captures the explanation for audit purposes.

Column	Type	Constraint	Description
`id`	UUID	PRIMARY KEY	Internal discrepancy ID
`report_id`	UUID	FK → `reconciliation_reports(id)`, NOT NULL	Parent reconciliation report
`transaction_id`	UUID	FK → `transactions(id)`, NULLABLE	Drop transaction (null for missing_in_drop)
`partner_reference`	TEXT	NULLABLE	ZTL settlement record reference
`discrepancy_type`	TEXT	CHECK `IN ('missing_in_partner','missing_in_drop','amount_mismatch','status_mismatch')`, NOT NULL	Category of discrepancy
`drop_amount`	BIGINT	NULLABLE	Amount in Drop's records (minor units)
`partner_amount`	BIGINT	NULLABLE	Amount in ZTL's records (minor units)
`drop_status`	TEXT	NULLABLE	Transaction status in Drop's records
`partner_status`	TEXT	NULLABLE	Transaction status in ZTL's records
`resolution_status`	TEXT	CHECK `IN ('open','investigating','resolved','accepted')`, DEFAULT `'open'`	Workflow status
`resolved_by`	TEXT	NULLABLE	Admin identifier who resolved
`resolved_at`	TIMESTAMPTZ	NULLABLE	Resolution timestamp
`notes`	TEXT	NULLABLE	Free-text explanation of resolution
`created_at`	TIMESTAMPTZ	DEFAULT now()	When the discrepancy was logged

`circuit_breaker_state`

Source: FR-075 (Circuit Breaker and Fallback Strategy).

Design decisions:

One record per external dependency. The dependency_name column has a UNIQUE constraint — it is the natural primary lookup key. The auto-increment id exists for FK references and ORM compatibility.

dependency_name values: bankid, ztl, fx_provider, compliance, push. These are the 5 external systems identified in FR-075. No other values are valid; enforced at the application layer.

state values: closed (normal operation), open (short-circuiting, fallback active), half_open (recovery probe in flight). Lowercase matches the circuit breaker state machine terminology in FR-075.

failure_count tracks consecutive failures in CLOSED state. Reset to 0 on any success or on transition to CLOSED from HALF-OPEN.

last_failure_at and last_success_at are maintained for the error-rate calculation window and for the operations dashboard.

opened_at records when the circuit last opened — used to calculate downtime duration in the recovery Slack alert (FR-075.6).

half_open_at records when the circuit entered HALF-OPEN — used to determine if the 30-second recovery window has elapsed.

updated_at is a general last-modified timestamp, updated on every state change or counter update. Required because multiple App Runner instances share this state via database polling.

This table is the single source of truth for circuit state across all application instances (FR-075.5). No in-memory circuit state survives a restart.

Column	Type	Constraint	Description
`id`	INTEGER	PRIMARY KEY AUTOINCREMENT	Internal ID
`dependency_name`	TEXT	UNIQUE, NOT NULL	External dependency identifier (`bankid`, `ztl`, `fx_provider`, `compliance`, `push`)
`state`	TEXT	CHECK `IN ('closed','open','half_open')`, NOT NULL, DEFAULT `'closed'`	Current circuit state
`failure_count`	INTEGER	NOT NULL, DEFAULT 0	Consecutive failure count (reset on success)
`last_failure_at`	TIMESTAMPTZ	NULLABLE	Timestamp of most recent failure
`last_success_at`	TIMESTAMPTZ	NULLABLE	Timestamp of most recent success
`opened_at`	TIMESTAMPTZ	NULLABLE	When the circuit last transitioned to OPEN
`half_open_at`	TIMESTAMPTZ	NULLABLE	When the circuit last transitioned to HALF-OPEN
`updated_at`	TIMESTAMPTZ	NOT NULL, DEFAULT now()	Last modified timestamp (any state or counter change)

`webhook_events`

Source: FR-076 (Webhook Handling Specification).

Design decisions:

Every webhook received is logged here, regardless of processing outcome. This is an append-only audit log — records are never updated after the final processing_status is set.

webhook_id carries a UNIQUE constraint — this is the database-level deduplication guard for duplicate webhook delivery (FR-076.4). The application checks this column before processing; the constraint prevents race conditions.

source is always banking_partner in the current schema (ZTL is the only webhook source). This field is included for forward compatibility with additional webhook sources (e.g., a future push notification provider).

payload (JSONB) stores the raw validated webhook payload. Storing the raw payload is essential for debugging, DLQ reprocessing, and dispute investigation (a dispute may reference what the banking partner sent).

signature stores the X-ZTL-Signature header value. Retained for audit — allows post-incident verification of which webhooks were accepted as authentic.

processing_status lifecycle: received (persisted, acknowledgement sent) → processing (background worker started) → completed (state machine updated, notifications sent) or failed (processing error, retry eligible) or dlq (moved to dead-letter queue after 3 failures).

processing_attempts tracks retry count. Combined with last_attempt_at, this determines retry eligibility.

processing_latency_ms measures from received_at to final HTTP response — the FR-076.8 requirement is ≤5 seconds. This enables alerting on SLA violations.

transaction_id is nullable — rejected webhooks (invalid signature, unknown source) do not have a resolvable transaction ID.

error_message is null on success, populated on failure.

Column	Type	Constraint	Description
`id`	UUID	PRIMARY KEY	Internal event ID
`webhook_id`	UUID	UNIQUE, NOT NULL	`webhook_id` from payload — deduplication key
`source`	TEXT	NOT NULL, DEFAULT `'banking_partner'`	Webhook origin identifier
`event_type`	TEXT	NOT NULL	Webhook event type (e.g., `payment.completed`, `payment.failed`)
`payload`	JSONB	NOT NULL	Raw validated webhook payload
`signature`	TEXT	NULLABLE	`X-ZTL-Signature` header value as received
`received_at`	TIMESTAMPTZ	NOT NULL, DEFAULT now()	When the webhook arrived at the endpoint
`processing_status`	TEXT	CHECK `IN ('received','processing','completed','failed','dlq')`, NOT NULL, DEFAULT `'received'`	Processing lifecycle status
`processing_attempts`	INTEGER	NOT NULL, DEFAULT 0	Number of processing attempts made
`last_attempt_at`	TIMESTAMPTZ	NULLABLE	Timestamp of most recent processing attempt
`processing_latency_ms`	INTEGER	NULLABLE	Time from receipt to final response (milliseconds)
`transaction_id`	UUID	FK → `transactions(id)`, NULLABLE	Referenced transaction (null for rejected/unknown webhooks)
`error_message`	TEXT	NULLABLE	Error detail on failure; null on success
`created_at`	TIMESTAMPTZ	NOT NULL, DEFAULT now()	Record creation timestamp

`webhook_dlq`

Source: FR-076 (Webhook Handling Specification — Dead Letter Queue).

Design decisions:

Separate table from webhook_events by design. The DLQ is for human review; it must be queryable independently without scanning the full event log.

webhook_event_id FK links back to the original webhook_events record — all payload, signature, and retry history is accessible via the parent record.

reason records the final error message that caused the 3rd retry failure. This is the primary information for manual triage.

moved_at is when the event was moved to DLQ — distinct from webhook_events.created_at (original receipt time).

reviewed_by and reviewed_at track operator review. An unreviewed DLQ entry has reviewed_by = NULL.

resolution workflow: pending (awaiting review) → reprocessed (operator triggered reprocessing and it succeeded) → discarded (operator determined the webhook is invalid and should not be reprocessed).

notes captures the operator's explanation — required for audit trail when a payment-related webhook is discarded.

Column	Type	Constraint	Description
`id`	UUID	PRIMARY KEY	Internal DLQ entry ID
`webhook_event_id`	UUID	FK → `webhook_events(id)`, NOT NULL	Parent webhook event
`reason`	TEXT	NOT NULL	Final error message that caused DLQ promotion
`moved_at`	TIMESTAMPTZ	NOT NULL, DEFAULT now()	When the event was moved to DLQ
`reviewed_by`	TEXT	NULLABLE	Operator identifier who reviewed
`reviewed_at`	TIMESTAMPTZ	NULLABLE	When the operator reviewed
`resolution`	TEXT	CHECK `IN ('pending','reprocessed','discarded')`, NOT NULL, DEFAULT `'pending'`	Operator resolution status
`notes`	TEXT	NULLABLE	Operator explanation for resolution

`disputes`

Source: FR-077 (Dispute and Refund Process).

Design decisions:

One record per dispute. A single transaction can theoretically have multiple disputes (e.g., first dispute denied, user files a new report with additional evidence) — no unique constraint on transaction_id.

dispute_reference is a human-readable reference generated at submission time (e.g., DISP-20260226-00001). UNIQUE — used in user communications, Finansklagenemnda referrals, and operator lookup. The format must be deterministic and sortable.

status follows the dispute lifecycle state machine from FR-077: submitted → acknowledged → investigating → decided_refund / decided_partial / decided_denied → refund_processing (for refund decisions) → closed.

description is the user's free-text description (20-1000 chars per FR-077.2). Stored as-is — not sanitised for search; operators read it directly.

date_noticed is the date the user states they noticed the issue. DATE type (not TIMESTAMPTZ) — the user selects a calendar date.

evidence_urls (JSONB array) stores S3 URLs for uploaded evidence files (max 3 per FR-077.2). JSONB array chosen over a separate dispute_evidence table because evidence is always queried with the dispute and is bounded (max 3 items).

assigned_to stores the compliance officer identifier for the current investigator. Nullable — not assigned until after auto-acknowledgement.

investigation_notes and decision_justification are TEXT fields for free-form officer notes. These are legally required documentation under Betalingstjenesteloven § 4-21 (decision must be substantiated).

refund_amount is the amount actually refunded in minor units. May differ from the transaction amount (partial refund, liability cap of 450 NOK per FR-077.7).

liability_amount is the portion charged to the user (0 for full refund, up to 450 NOK for negligence cases).

refund_transaction_id is an FK to the transactions table for the reverse PISP transaction created for the refund. Nullable — only populated after a refund is initiated.

finansklagenemnda_referral_sent (BOOLEAN) tracks whether the user was provided the Finansklagenemnda information in the denial notification. Required for compliance with Betalingstjenesteloven § 4-32.

Timestamps follow the dispute lifecycle: submitted_at (form submitted), acknowledged_at (auto-acknowledgement sent ≤1h), decided_at (officer decision recorded), closed_at (case fully resolved).

do_not_delete (BOOLEAN, DEFAULT TRUE) is set on all dispute records. Data retention jobs MUST check this flag before deletion. Disputes are retained 5 years from closed_at per Betalingstjenesteloven § 3-15 and FR-077.10.

Column	Type	Constraint	Description
`id`	UUID	PRIMARY KEY	Internal dispute ID
`transaction_id`	UUID	FK → `transactions(id)`, NOT NULL	The disputed transaction
`user_id`	UUID	FK → `users(id)`, NOT NULL	The user who filed the dispute
`dispute_reference`	TEXT	UNIQUE, NOT NULL	Human-readable reference (e.g., `DISP-20260226-00001`)
`status`	TEXT	CHECK `IN ('submitted','acknowledged','investigating','decided_refund','decided_partial','decided_denied','refund_processing','closed')`, NOT NULL, DEFAULT `'submitted'`	Lifecycle status
`description`	TEXT	NOT NULL	User's description of the issue (20–1000 chars)
`date_noticed`	DATE	NOT NULL	Date user first noticed the unauthorized transaction
`evidence_urls`	JSONB	NULLABLE	Array of S3 URLs for uploaded evidence files (max 3)
`assigned_to`	TEXT	NULLABLE	Compliance officer identifier
`investigation_notes`	TEXT	NULLABLE	Officer's investigation notes
`decision_justification`	TEXT	NULLABLE	Officer's written justification for decision (required on close)
`refund_amount`	BIGINT	NULLABLE	Amount refunded in minor units
`liability_amount`	BIGINT	NULLABLE	Amount charged to user in minor units (0 for full refund, ≤45000 ore for negligence)
`refund_transaction_id`	UUID	FK → `transactions(id)`, NULLABLE	FK to the reverse PISP transaction for the refund
`finansklagenemnda_referral_sent`	BOOLEAN	NOT NULL, DEFAULT FALSE	Whether the Finansklagenemnda referral was included in denial notification
`submitted_at`	TIMESTAMPTZ	NOT NULL, DEFAULT now()	When the dispute form was submitted
`acknowledged_at`	TIMESTAMPTZ	NULLABLE	When the auto-acknowledgement was sent
`decided_at`	TIMESTAMPTZ	NULLABLE	When the officer recorded their decision
`closed_at`	TIMESTAMPTZ	NULLABLE	When the case was fully resolved
`created_at`	TIMESTAMPTZ	NOT NULL, DEFAULT now()	Record creation timestamp
`updated_at`	TIMESTAMPTZ	NOT NULL, DEFAULT now()	Last modified timestamp
`do_not_delete`	BOOLEAN	NOT NULL, DEFAULT TRUE	Retention guard — data purge jobs must not delete records with this flag

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')`
`reconciliation_reports`	PRIMARY KEY	`id`	-
`reconciliation_reports`	NOT NULL	`report_date`, `status`, `total_drop_transactions`, `total_partner_transactions`, `matched_count`, `discrepancy_count`	-
`reconciliation_reports`	CHECK	`status`	`IN ('pending','matched','discrepancies_found')`
`reconciliation_discrepancies`	PRIMARY KEY	`id`	-
`reconciliation_discrepancies`	NOT NULL	`report_id`, `discrepancy_type`	-
`reconciliation_discrepancies`	FK	`report_id`	`reconciliation_reports(id)`
`reconciliation_discrepancies`	FK	`transaction_id`	`transactions(id)` (nullable)
`reconciliation_discrepancies`	CHECK	`discrepancy_type`	`IN ('missing_in_partner','missing_in_drop','amount_mismatch','status_mismatch')`
`reconciliation_discrepancies`	CHECK	`resolution_status`	`IN ('open','investigating','resolved','accepted')`
`circuit_breaker_state`	PRIMARY KEY	`id`	AUTOINCREMENT
`circuit_breaker_state`	UNIQUE	`dependency_name`	-
`circuit_breaker_state`	NOT NULL	`dependency_name`, `state`, `failure_count`, `updated_at`	-
`circuit_breaker_state`	CHECK	`state`	`IN ('closed','open','half_open')`
`webhook_events`	PRIMARY KEY	`id`	-
`webhook_events`	UNIQUE	`webhook_id`	-
`webhook_events`	NOT NULL	`webhook_id`, `source`, `event_type`, `payload`, `received_at`, `processing_status`, `processing_attempts`, `created_at`	-
`webhook_events`	FK	`transaction_id`	`transactions(id)` (nullable)
`webhook_events`	CHECK	`processing_status`	`IN ('received','processing','completed','failed','dlq')`
`webhook_dlq`	PRIMARY KEY	`id`	-
`webhook_dlq`	NOT NULL	`webhook_event_id`, `reason`, `moved_at`, `resolution`	-
`webhook_dlq`	FK	`webhook_event_id`	`webhook_events(id)`
`webhook_dlq`	CHECK	`resolution`	`IN ('pending','reprocessed','discarded')`
`disputes`	PRIMARY KEY	`id`	-
`disputes`	UNIQUE	`dispute_reference`	-
`disputes`	NOT NULL	`transaction_id`, `user_id`, `dispute_reference`, `status`, `description`, `date_noticed`, `submitted_at`, `created_at`, `updated_at`, `do_not_delete`, `finansklagenemnda_referral_sent`	-
`disputes`	FK	`transaction_id`	`transactions(id)`
`disputes`	FK	`user_id`	`users(id)`
`disputes`	FK	`refund_transaction_id`	`transactions(id)` (nullable)
`disputes`	CHECK	`status`	`IN ('submitted','acknowledged','investigating','decided_refund','decided_partial','decided_denied','refund_processing','closed')`
`disputes`	DEFAULT	`do_not_delete`	`TRUE`

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

Full schema: DATABASE-SCHEMA.md
Data architecture overview: data-architecture.md
Migration strategy: migration-strategy.md
Dual-driver implementation: src/drop-api/src/lib/db.ts

Database Design

Database Design

Design Philosophy

Why 1925 Tables

Complete Schema ERD

Table-by-Table Design Rationale

Core Tables

users

transactions

bank_accounts

recipients

merchants

exchange_rates

sessions

settings

notifications

cards (FUTURE)

spending_limits (FUTURE)

rate_limits

Compliance Tables

audit_log

aml_alerts

str_reports

screening_results

consents

data_access_requests

complaints

Operational Tables

reconciliation_reports

reconciliation_discrepancies

circuit_breaker_state

webhook_events

webhook_dlq

disputes

Constraint Inventory

Naming Conventions

Normalization Analysis

Cross-References

`users`

`transactions`

`bank_accounts`

`recipients`

`merchants`

`exchange_rates`

`sessions`

`settings`

`notifications`

`cards` (FUTURE)

`spending_limits` (FUTURE)

`rate_limits`

`audit_log`

`aml_alerts`

`str_reports`

`screening_results`

`consents`

`data_access_requests`

`complaints`

`reconciliation_reports`

`reconciliation_discrepancies`

`circuit_breaker_state`

`webhook_events`

`webhook_dlq`

`disputes`