Database Schema

Drop Database Schema

Source: src/shared/db/schema.ts (Drizzle ORM schema — single source of truth for all environments)

Overview

Drop uses PostgreSQL 16 as the sole database engine in all environments (development, CI, staging, production). Database access is via Drizzle ORM. There is no SQLite dependency and no dual-driver abstraction.

Local dev: PostgreSQL 16 in Docker (docker compose up -d), port 5433
CI: PostgreSQL 16 service container in GitHub Actions
Production: PostgreSQL 16 on AWS RDS (db.t3.small)
Schema definition: src/shared/db/schema.ts (Drizzle schema, TypeScript, PostgreSQL-native)
Migrations: managed by drizzle-kit

See ADR-014 for the full rationale.

Total tables: 19 (12 core + 7 compliance)

Tables

users

Primary user accounts.

Column	Type	Constraints	Default
id	TEXT	PRIMARY KEY	-
email	TEXT	UNIQUE NOT NULL	-
password_hash	TEXT	NOT NULL	-
first_name	TEXT	NOT NULL	-
last_name	TEXT	NOT NULL	-
phone	TEXT	-	NULL
date_of_birth	TEXT	-	NULL
kyc_status	TEXT	CHECK('pending','approved','rejected')	'pending'
role	TEXT	CHECK('user','merchant')	'user'
created_at	TEXT	-	CURRENT_TIMESTAMP

ID format: usr_ + 16 hex chars (generated by randomId("usr") in utils-server.ts:4)

Password hashing: bcrypt with 12 rounds (utils-server.ts:8-11)

recipients

Saved remittance recipients per user.

Column	Type	Constraints	Default
id	TEXT	PRIMARY KEY	-
user_id	TEXT	NOT NULL, FK → users(id)	-
name	TEXT	NOT NULL	-
country	TEXT	NOT NULL	-
currency	TEXT	NOT NULL	-
bank_account	TEXT	NOT NULL	-
bank_name	TEXT	-	NULL
created_at	TEXT	-	CURRENT_TIMESTAMP

Supported countries: RS, BA, PL, PK, TR (enforced at API level, not DB level)

Index: idx_recipients_user on user_id

merchants

Registered merchant profiles for QR payments.

Column	Type	Constraints	Default
id	TEXT	PRIMARY KEY	-
user_id	TEXT	NOT NULL, FK → users(id)	-
business_name	TEXT	NOT NULL	-
org_number	TEXT	UNIQUE NOT NULL	-
address	TEXT	-	NULL
bank_account	TEXT	NOT NULL	-
fee_rate	REAL	-	0.01
status	TEXT	-	'active'
created_at	TEXT	-	CURRENT_TIMESTAMP

Index: idx_merchants_org on org_number

transactions

All financial transactions (remittances and QR payments).

Column	Type	Constraints	Default
id	TEXT	PRIMARY KEY	-
user_id	TEXT	NOT NULL, FK → users(id)	-
type	TEXT	NOT NULL, CHECK('remittance','qr_payment')	-
status	TEXT	CHECK('processing','completed','failed')	'processing'
amount	REAL	NOT NULL	-
currency	TEXT	-	'NOK'
fee	REAL	-	0
recipient_id	TEXT	FK → recipients(id)	NULL
merchant_id	TEXT	FK → merchants(id)	NULL
send_amount	REAL	-	NULL
send_currency	TEXT	-	NULL
receive_amount	REAL	-	NULL
receive_currency	TEXT	-	NULL
exchange_rate	REAL	-	NULL
created_at	TEXT	-	CURRENT_TIMESTAMP
completed_at	TEXT	-	NULL

Indexes: idx_transactions_user on user_id, idx_transactions_merchant on merchant_id

Notes:

Remittances have recipient_id set, merchant_id NULL
QR payments have merchant_id set, recipient_id NULL
send_* / receive_* / exchange_rate fields are populated for remittances only

exchange_rates

Currency exchange rates (from NOK).

Column	Type	Constraints	Default
id	SERIAL	PRIMARY KEY	AUTOINCREMENT
from_currency	TEXT	-	'NOK'
to_currency	TEXT	NOT NULL	-
rate	REAL	NOT NULL	-
updated_at	TEXT	-	CURRENT_TIMESTAMP

Seed data (db.ts:531-545):

Corridor	Rate
NOK → RSD	11.7
NOK → BAM	1.04
NOK → PLN	0.41
NOK → PKR	26.8
NOK → TRY	3.45
NOK → EUR	0.089

bank_accounts

Linked bank accounts (AISP — Open Banking read in production, mock balances in dev).

Pass-through model: Drop NEVER holds customer money. The balance column stores the last AISP-read balance from the user's real bank account — it is a cached read-only value, not a Drop-held balance. In dev/demo mode, mock balances are seeded for testing.

Column	Type	Constraints	Default
id	TEXT	PRIMARY KEY	-
user_id	TEXT	NOT NULL, FK → users(id)	-
bank_name	TEXT	NOT NULL	-
account_number	TEXT	NOT NULL	-
iban	TEXT	-	NULL
balance	REAL	-	0
currency	TEXT	-	'NOK'
is_primary	INTEGER	-	0
connected_at	TEXT	-	CURRENT_TIMESTAMP

Index: idx_bank_accounts_user on user_id

cards (FUTURE — feature-flagged)

Note: Cards are a FUTURE feature, gated behind feature flags (all default to false). This table exists in the schema but is not actively used until a card issuing partner is integrated.

Virtual and physical payment cards.

Column	Type	Constraints	Default
id	TEXT	PRIMARY KEY	-
user_id	TEXT	NOT NULL, FK → users(id)	-
type	TEXT	CHECK('virtual','physical')	'virtual'
last_four	TEXT	NOT NULL	-
token_ref	TEXT	-	NULL
expiry	TEXT	NOT NULL	-
status	TEXT	CHECK('active','frozen','cancelled')	'active'
shipping_address	TEXT	-	NULL
created_at	TEXT	-	CURRENT_TIMESTAMP
pin_hash	TEXT	-	NULL (added via runtime migration in `cards/[id]/pin/route.ts:51-53`)

Index: idx_cards_user on user_id

sessions

JWT session tracking for revocation support.

Column	Type	Constraints	Default
id	TEXT	PRIMARY KEY	-
user_id	TEXT	NOT NULL, FK → users(id)	-
token_hash	TEXT	NOT NULL	-
created_at	TEXT	-	CURRENT_TIMESTAMP
expires_at	TEXT	NOT NULL	-
revoked	INTEGER	-	0

Indexes: idx_sessions_user on user_id, idx_sessions_token on token_hash

Token hash: SHA-256 of the JWT string (auth.ts:59)

notifications

In-app notifications.

Column	Type	Constraints	Default
id	TEXT	PRIMARY KEY	-
user_id	TEXT	NOT NULL, FK → users(id)	-
type	TEXT	NOT NULL	-
title	TEXT	NOT NULL	-
body	TEXT	NOT NULL	-
read	INTEGER	-	0
created_at	TEXT	-	CURRENT_TIMESTAMP

Index: idx_notifications_user on user_id

settings

Per-user preferences.

Column	Type	Constraints	Default
user_id	TEXT	PRIMARY KEY, FK → users(id)	-
currency	TEXT	-	'NOK'
language	TEXT	-	'nb'
push_enabled	INTEGER	-	1
email_enabled	INTEGER	-	1
updated_at	TEXT	-	CURRENT_TIMESTAMP

spending_limits (FUTURE — feature-flagged)

Note: Tied to the cards feature. Only active when card feature flags are enabled.

Card spending limits.

Column	Type	Constraints	Default
id	TEXT	PRIMARY KEY	-
user_id	TEXT	NOT NULL, FK → users(id)	-
card_id	TEXT	FK → cards(id)	NULL
limit_type	TEXT	NOT NULL	-
amount	REAL	NOT NULL	-
currency	TEXT	-	'NOK'
created_at	TEXT	-	CURRENT_TIMESTAMP

Indexes: idx_spending_limits_user on user_id, idx_spending_limits_card on card_id

Limit types (API-enforced): daily, weekly, monthly, transaction

rate_limits

Persistent rate limiting store.

Column	Type	Constraints	Default
key	TEXT	PRIMARY KEY	-
count	INTEGER	NOT NULL	-
reset_at	INTEGER	NOT NULL	-

Used by middleware.ts:rateLimit() for IP-based rate limiting. Expired entries are cleaned on each call (middleware.ts:11).

Added: 2026-02-16 (compliance infrastructure)

These tables support Drop's compliance requirements for Norwegian financial services regulation, GDPR, and AML/KYC requirements per hvitvaskingsloven.

audit_log

User action audit trail for compliance and security monitoring.

Column	Type	Constraints	Default
id	TEXT	PRIMARY KEY	-
timestamp	TEXT	-	CURRENT_TIMESTAMP
user_id	TEXT	FK → users(id)	NULL
action	TEXT	NOT NULL	-
resource_type	TEXT	-	NULL
resource_id	TEXT	-	NULL
details	TEXT	-	NULL
ip_address	TEXT	-	NULL
user_agent	TEXT	-	NULL

Indexes: idx_audit_log_user on user_id, idx_audit_log_timestamp on timestamp, idx_audit_log_action on action

Purpose: Tracks all significant user actions (login, transaction, settings change, etc.) for audit purposes.

aml_alerts

AML (Anti-Money Laundering) transaction monitoring alerts.

Column	Type	Constraints	Default
id	TEXT	PRIMARY KEY	-
user_id	TEXT	NOT NULL, FK → users(id)	-
alert_type	TEXT	NOT NULL	-
severity	TEXT	NOT NULL, CHECK('low','medium','high','critical')	-
transaction_id	TEXT	FK → transactions(id)	NULL
details	TEXT	-	NULL
status	TEXT	CHECK('open','investigating','resolved','escalated','filed')	'open'
reviewed_by	TEXT	-	NULL
reviewed_at	TEXT	-	NULL
created_at	TEXT	-	CURRENT_TIMESTAMP

Indexes: idx_aml_alerts_user on user_id, idx_aml_alerts_status on status

Purpose: Records suspicious transaction patterns flagged by AML monitoring rules (e.g., structuring, velocity, high-risk corridors).

str_reports

STR (Suspicious Transaction Reports) filed with financial authorities.

Column	Type	Constraints	Default
id	TEXT	PRIMARY KEY	-
user_id	TEXT	NOT NULL, FK → users(id)	-
alert_id	TEXT	FK → aml_alerts(id)	NULL
report_type	TEXT	NOT NULL	-
status	TEXT	CHECK('draft','submitted','acknowledged')	'draft'
filed_at	TEXT	-	NULL
reference_number	TEXT	-	NULL
details	TEXT	-	NULL
created_at	TEXT	-	CURRENT_TIMESTAMP

Purpose: Tracks STRs filed with Økokrim/EFE (Norwegian financial intelligence unit) per hvitvaskingsloven requirements.

screening_results

Results from sanctions/PEP (Politically Exposed Persons) screening.

Column	Type	Constraints	Default
id	TEXT	PRIMARY KEY	-
user_id	TEXT	NOT NULL, FK → users(id)	-
screening_type	TEXT	NOT NULL, CHECK('pep','sanctions','adverse_media')	-
provider	TEXT	-	NULL
result	TEXT	NOT NULL, CHECK('clear','match','potential_match','error')	-
match_details	TEXT	-	NULL
screened_at	TEXT	-	CURRENT_TIMESTAMP

Indexes: idx_screening_user on user_id

Purpose: Stores results from automated screening against PEP lists, sanctions lists (OFAC, UN, EU), and adverse media databases.

consents

Column	Type	Constraints	Default
id	TEXT	PRIMARY KEY	-
user_id	TEXT	NOT NULL, FK → users(id)	-
consent_type	TEXT	NOT NULL	-
granted	INTEGER	NOT NULL	1
granted_at	TEXT	-	CURRENT_TIMESTAMP
withdrawn_at	TEXT	-	NULL
ip_address	TEXT	-	NULL

Indexes: idx_consents_user on user_id

Consent Types (API-enforced): terms, privacy, marketing, cookies_analytics, cookies_marketing

Purpose: Tracks when users grant or withdraw consent for different types of data processing, with IP address as proof of consent action.

data_access_requests

Column	Type	Constraints	Default
id	TEXT	PRIMARY KEY	-
user_id	TEXT	NOT NULL, FK → users(id)	-
request_type	TEXT	NOT NULL, CHECK('export','erasure','rectification','restriction')	-
status	TEXT	CHECK('pending','processing','completed','rejected')	'pending'
requested_at	TEXT	-	CURRENT_TIMESTAMP
completed_at	TEXT	-	NULL
download_url	TEXT	-	NULL
notes	TEXT	-	NULL

Indexes: idx_data_requests_user on user_id

Purpose: Tracks GDPR data subject access requests. export requests generate full data export, erasure triggers account deletion.

complaints

Customer complaints per Finansavtaleloven §3-53 (15-day response requirement).

Column	Type	Constraints	Default
id	TEXT	PRIMARY KEY	-
user_id	TEXT	NOT NULL, FK → users(id)	-
category	TEXT	NOT NULL	-
subject	TEXT	NOT NULL	-
description	TEXT	NOT NULL	-
status	TEXT	CHECK('received','investigating','resolved','escalated')	'received'
resolution	TEXT	-	NULL
created_at	TEXT	-	CURRENT_TIMESTAMP
resolved_at	TEXT	-	NULL

Indexes: idx_complaints_user on user_id, idx_complaints_status on status

Categories (API-enforced): transaction, service, fees, privacy, technical, other

Purpose: Formal complaint logging system to ensure compliance with Norwegian financial services law requiring 15 business day response time.

Database Access Layer

Source: db.ts

Data Access Layer

The database access layer is Drizzle ORM (src/shared/db/schema.ts). The old db.ts dual-driver abstraction has been removed (see ADR-014).

Use Drizzle query builder or the sql template tag for raw queries:

import { db } from '@drop/shared/db';
import { users } from '@drop/shared/db/schema';
import { eq } from 'drizzle-orm';

// Type-safe query
const user = await db.select().from(users).where(eq(users.id, userId)).limit(1);

// Raw SQL escape hatch (PostgreSQL syntax, $1 params not needed — Drizzle handles binding)
import { sql } from 'drizzle-orm';
const result = await db.execute(sql`SELECT id FROM users WHERE email = ${email}`);

Migrations are managed by drizzle-kit:

cd src/shared && npx drizzle-kit generate  # Generate migration file
cd src/shared && npx drizzle-kit push      # Push schema to dev database
make db-push                               # Shortcut (from repo root)

Seed Data

When exchange_rates table is empty, seedData() (db.ts:530) populates:

6 exchange rate corridors (NOK → RSD, BAM, PLN, PKR, TRY, EUR)
Demo data (when NODE_ENV !== "production" or SEED_DEMO=true):
- 1 demo user (usr_demo1, [email protected], role: merchant)
- 3 recipients (Serbia, Bosnia, Turkey)
- 1 merchant (Ahmetov Kebab)
- 3 transactions (2 remittances, 1 QR payment)
- 2 bank accounts (DNB primary with 45,230 NOK, SpareBank 1 with 12,800 NOK)