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. 2519 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 the original 19 tables exist solely for regulatory requirements (GDPR, AML, PSD2). 6 additional operational and compliance tablesThey were added toas addressa reconciliation,compliance circuitinfrastructure breakerlayer, state,not webhook processing, and dispute handling requirements.retrofitted.
3. PostgreSQL-native.Dual-driver portability. Schema targetsuses the intersection of SQLite and PostgreSQL 16capabilities. exclusivelyNo (ADR-014 supersedes ADR-006/010). PostgreSQL-database-specific features (e.g., PostgreSQL arrays, JSONB, partial indexes, TIMESTAMPTZ) are available where beneficial. No SQLite compatibilityJSON1) constraints.in the schema definition.

Why 2519 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