Database Schema Document

Database Schema Document

Project: Drop Database: drop_db (PostgreSQL prod) / drop.db (SQLite dev) Version: 1.0 Date: 2026-02-23 Author: Petter Graff, Senior Enterprise Architect Status: In Review Reviewers: Alem Bašić (CEO), John (AI Director)

Document History

Version	Date	Author	Changes
0.1	2026-02-23	Petter Graff	Initial draft — extracted from src/drop-app/src/lib/db.ts

---

1. Database Technology & Version

Property	Value
Technology (Production)	PostgreSQL 16
Technology (Development)	SQLite (via better-sqlite3)
Hosting (Production)	AWS RDS (PLANNED — region: eu-north-1 Stockholm)
Instance type	TBD — requires AWS sizing exercise
Storage	TBD — auto-scaling: YES (RDS storage autoscaling)
Read replicas	0 in Phase 1; evaluate at > 10K concurrent users
Connection pooling	None in Phase 1 (direct connection from App Runner) — evaluate PgBouncer at scale
Encoding	UTF-8
Timezone	UTC (all timestamps in UTC)
Migration tool	Custom inline schema (initDb() in db.ts) — no Flyway/Liquibase yet
Driver selection	DATABASE_URL env var presence: set → PostgreSQL, unset → SQLite
Dev DB file	./data/drop.db (host) / /app/data/drop.db (Docker)
SQLite mode	WAL journal mode + foreign keys enabled (db.ts:37-38)

Dual-driver abstraction: The db.ts access layer transparently converts SQLite SQL to PostgreSQL equivalents at runtime:

• INSERT OR IGNORE INTO → ON CONFLICT DO NOTHING
• INSERT OR REPLACE INTO → ON CONFLICT ... DO UPDATE SET
• datetime('now') → CURRENT_TIMESTAMP
• ? placeholders → $1, $2, ... positional params

---

2. ER Diagram

erDiagram
    USERS {
        text id PK
        text email UK
        text password_hash
        text first_name
        text last_name
        text phone
        text date_of_birth
        text kyc_status
        text role
        text created_at
    }

    RECIPIENTS {
        text id PK
        text user_id FK
        text name
        text country
        text currency
        text bank_account
        text bank_name
        text created_at
    }

    MERCHANTS {
        text id PK
        text user_id FK
        text business_name
        text org_number UK
        text address
        text bank_account
        real fee_rate
        text status
        text created_at
    }

    TRANSACTIONS {
        text id PK
        text user_id FK
        text type
        text status
        real amount
        text currency
        real fee
        text recipient_id FK
        text merchant_id FK
        real send_amount
        text send_currency
        real receive_amount
        text receive_currency
        real exchange_rate
        text created_at
        text completed_at
    }

    EXCHANGE_RATES {
        integer id PK
        text from_currency
        text to_currency
        real rate
        text updated_at
    }

    BANK_ACCOUNTS {
        text id PK
        text user_id FK
        text bank_name
        text account_number
        text iban
        real balance
        text currency
        integer is_primary
        text connected_at
    }

    CARDS {
        text id PK
        text user_id FK
        text type
        text last_four
        text token_ref
        text expiry
        text status
        text shipping_address
        text created_at
        text pin_hash
    }

    SESSIONS {
        text id PK
        text user_id FK
        text token_hash
        text created_at
        text expires_at
        integer revoked
    }

    NOTIFICATIONS {
        text id PK
        text user_id FK
        text type
        text title
        text body
        integer read
        text created_at
    }

    SETTINGS {
        text user_id PK
        text currency
        text language
        integer push_enabled
        integer email_enabled
        text updated_at
    }

    SPENDING_LIMITS {
        text id PK
        text user_id FK
        text card_id FK
        text limit_type
        real amount
        text currency
        text created_at
    }

    RATE_LIMITS {
        text key PK
        integer count
        integer reset_at
    }

    AUDIT_LOG {
        text id PK
        text timestamp
        text user_id FK
        text action
        text resource_type
        text resource_id
        text details
        text ip_address
        text user_agent
    }

    AML_ALERTS {
        text id PK
        text user_id FK
        text alert_type
        text severity
        text transaction_id FK
        text details
        text status
        text reviewed_by
        text reviewed_at
        text created_at
    }

    STR_REPORTS {
        text id PK
        text user_id FK
        text alert_id FK
        text report_type
        text status
        text filed_at
        text reference_number
        text details
        text created_at
    }

    SCREENING_RESULTS {
        text id PK
        text user_id FK
        text screening_type
        text provider
        text result
        text match_details
        text screened_at
    }

    CONSENTS {
        text id PK
        text user_id FK
        text consent_type
        integer granted
        text granted_at
        text withdrawn_at
        text ip_address
    }

    DATA_ACCESS_REQUESTS {
        text id PK
        text user_id FK
        text request_type
        text status
        text requested_at
        text completed_at
        text download_url
        text notes
    }

    COMPLAINTS {
        text id PK
        text user_id FK
        text category
        text subject
        text description
        text status
        text resolution
        text created_at
        text resolved_at
    }

    USERS ||--o{ RECIPIENTS : "has"
    USERS ||--o{ TRANSACTIONS : "initiates"
    USERS ||--o{ BANK_ACCOUNTS : "links"
    USERS ||--o{ CARDS : "holds"
    USERS ||--o{ SESSIONS : "has"
    USERS ||--o{ NOTIFICATIONS : "receives"
    USERS ||--o{ SETTINGS : "configures"
    USERS ||--o{ SPENDING_LIMITS : "sets"
    USERS ||--o{ AUDIT_LOG : "generates"
    USERS ||--o{ AML_ALERTS : "triggers"
    USERS ||--o{ STR_REPORTS : "subject of"
    USERS ||--o{ SCREENING_RESULTS : "screened in"
    USERS ||--o{ CONSENTS : "grants"
    USERS ||--o{ DATA_ACCESS_REQUESTS : "submits"
    USERS ||--o{ COMPLAINTS : "files"
    USERS ||--o{ MERCHANTS : "registers as"
    TRANSACTIONS ||--o{ RECIPIENTS : "targets"
    TRANSACTIONS ||--o{ MERCHANTS : "pays"
    TRANSACTIONS ||--o{ AML_ALERTS : "triggers"
    AML_ALERTS ||--o{ STR_REPORTS : "escalates to"
    CARDS ||--o{ SPENDING_LIMITS : "governed by"

---

3. Schema Conventions

3.1 Naming Conventions

Element	Convention	Example
Tables	snake_case, plural	bank_accounts, audit_log
Columns	snake_case	created_at, user_id
Primary keys	id (TEXT in Drop — custom prefixed IDs)	id TEXT PRIMARY KEY
Foreign keys	{referenced_table_singular}_id	user_id, recipient_id
Indexes	idx_{table}_{column}	idx_transactions_user
Unique indexes	uq_{table}_{column} (convention; SQLite uses UNIQUE inline)	uq_users_email

3.2 ID Convention (Drop-specific)

Drop uses custom-prefixed hex IDs instead of UUIDs. Generated by randomId(prefix) in utils-server.ts:

Format: {prefix}_{16 hex chars}
Examples:
  usr_a1b2c3d4e5f67890   (users)
  tx_a1b2c3d4e5f67890    (transactions)
  rec_a1b2c3d4e5f67890   (recipients)
  ses_a1b2c3d4e5f67890   (sessions)
  ba_a1b2c3d4e5f67890    (bank_accounts)
  mer_a1b2c3d4e5f67890   (merchants)
  noti_a1b2c3d4e5f67890  (notifications)
  con_a1b2c3d4e5f67890   (consents)
  cmp_a1b2c3d4e5f67890   (complaints)

3.3 Data Type Standards

Drop targets SQLite/PostgreSQL dual compatibility. Column types use SQLite-compatible declarations; the access layer handles PostgreSQL mapping.

Data	SQLite Type	PostgreSQL Equivalent	Notes
IDs (custom)	TEXT	TEXT	Format: {prefix}_{hex16}
Strings	TEXT	TEXT	No length limit in SQLite; add constraints at app layer
Money / currency	REAL	NUMERIC(18, 2) (recommended for PG)	Current: REAL — consider migration to NUMERIC for prod
Booleans	INTEGER	BOOLEAN	0 = false, 1 = true in SQLite
Timestamps	TEXT	TIMESTAMPTZ	SQLite: ISO-8601 string; PG: native timestamp
Auto-increment	INTEGER AUTOINCREMENT	SERIAL	Used only for exchange_rates.id
JSON	TEXT	JSONB	Currently no JSON columns — compliance fields use TEXT details

3.4 Standard Columns

Column	Present In	Description
id	All tables	Custom-prefixed unique identifier
user_id	Most tables	FK to users(id) — user ownership
created_at	Most tables	ISO-8601 creation timestamp (SQLite default: datetime('now'))

---

4. Tables by Domain

4.1 User Domain

Table: users

Purpose: Primary user accounts. Authentication identity. KYC status gate for transactions.

Pass-through model note: Drop users are Norwegian residents (age >= 18) authenticated via BankID. Email/password exists but BankID is the primary auth path.

Column	Type	Nullable	Default	Constraints	Description
id	TEXT	NO	—	PRIMARY KEY	Format: usr_{hex16}
email	TEXT	NO	—	UNIQUE NOT NULL	Normalized; used for legacy login
password_hash	TEXT	NO	—	NOT NULL	bcrypt with 12 rounds (utils-server.ts:8-11)
first_name	TEXT	NO	—	NOT NULL	Extracted from BankID id_token.name split
last_name	TEXT	NO	—	NOT NULL	Extracted from BankID id_token.name split
phone	TEXT	YES	NULL	—	International format +XXXXXXXXXXXX
date_of_birth	TEXT	YES	NULL	—	ISO date string; must be >= 18 (validated on registration)
kyc_status	TEXT	NO	'pending'	CHECK IN ('pending','approved','rejected')	Updated by Sumsub webhook
role	TEXT	NO	'user'	CHECK IN ('user','merchant')	Elevated to merchant on POST /api/merchants/register
created_at	TEXT	NO	datetime('now')	—	Registration timestamp

-- SQLite
CREATE TABLE users (
    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,
    date_of_birth TEXT,
    kyc_status TEXT DEFAULT 'pending' CHECK(kyc_status IN ('pending','approved','rejected')),
    role TEXT DEFAULT 'user' CHECK(role IN ('user','merchant')),
    created_at TEXT DEFAULT (datetime('now'))
);

-- PostgreSQL equivalent
CREATE TABLE users (
    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,
    date_of_birth TEXT,
    kyc_status TEXT NOT NULL DEFAULT 'pending' CHECK(kyc_status IN ('pending','approved','rejected')),
    role TEXT NOT NULL DEFAULT 'user' CHECK(role IN ('user','merchant')),
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

---

Table: sessions

Purpose: JWT session tracking for revocation support. Session records are validated on every authenticated request.

Column	Type	Nullable	Default	Constraints	Description
id	TEXT	NO	—	PRIMARY KEY	Format: ses_{hex16}
user_id	TEXT	NO	—	FK → users(id)	Session owner
token_hash	TEXT	NO	—	NOT NULL	SHA-256 of the JWT string (auth.ts:59)
created_at	TEXT	NO	datetime('now')	—	Session start
expires_at	TEXT	NO	—	NOT NULL	Web: +24h; Mobile: +7d
revoked	INTEGER	NO	0	—	1 = revoked (logout or account deletion)

CREATE TABLE sessions (
    id TEXT PRIMARY KEY,
    user_id TEXT NOT NULL REFERENCES users(id),
    token_hash TEXT NOT NULL,
    created_at TEXT DEFAULT (datetime('now')),
    expires_at TEXT NOT NULL,
    revoked INTEGER DEFAULT 0
);

CREATE INDEX idx_sessions_user ON sessions(user_id);
CREATE INDEX idx_sessions_token ON sessions(token_hash);

---

Table: settings

Purpose: Per-user application preferences. One row per user (user_id is PK). Created with defaults on first access.

Column	Type	Nullable	Default	Constraints	Description
user_id	TEXT	NO	—	PRIMARY KEY, FK → users(id)	One row per user
currency	TEXT	NO	'NOK'	—	Display currency preference
language	TEXT	NO	'nb'	—	App language (nb, en, bs, sq)
push_enabled	INTEGER	NO	1	—	Push notification opt-in
email_enabled	INTEGER	NO	1	—	Email notification opt-in
updated_at	TEXT	NO	datetime('now')	—	Last settings update

CREATE TABLE settings (
    user_id TEXT PRIMARY KEY REFERENCES users(id),
    currency TEXT DEFAULT 'NOK',
    language TEXT DEFAULT 'nb',
    push_enabled INTEGER DEFAULT 1,
    email_enabled INTEGER DEFAULT 1,
    updated_at TEXT DEFAULT (datetime('now'))
);

---

4.2 Financial Domain

Table: bank_accounts

Purpose: Linked bank accounts from Open Banking AISP reads. Stores cached balance — NOT Drop-held funds. In dev/mock mode, seeded with demo data.

Pass-through model note: balance is a cached read from the user's real bank account (via AISP consent). Drop never stores or controls actual funds.

Column	Type	Nullable	Default	Constraints	Description
id	TEXT	NO	—	PRIMARY KEY	Format: ba_{hex16}
user_id	TEXT	NO	—	FK → users(id)	Account owner
bank_name	TEXT	NO	—	NOT NULL	e.g., DNB, SpareBank 1, Nordea
account_number	TEXT	NO	—	NOT NULL	Norwegian account number format
iban	TEXT	YES	NULL	—	IBAN (populated in Phase 2 AISP integration)
balance	REAL	YES	0	—	Last AISP-read balance (cached)
currency	TEXT	YES	'NOK'	—	Account currency
is_primary	INTEGER	YES	0	—	1 = primary account for PISP debit
connected_at	TEXT	NO	datetime('now')	—	When AISP consent was established

CREATE TABLE bank_accounts (
    id TEXT PRIMARY KEY,
    user_id TEXT NOT NULL REFERENCES users(id),
    bank_name TEXT NOT NULL,
    account_number TEXT NOT NULL,
    iban TEXT,
    balance REAL DEFAULT 0,
    currency TEXT DEFAULT 'NOK',
    is_primary INTEGER DEFAULT 0,
    connected_at TEXT DEFAULT (datetime('now'))
);

CREATE INDEX idx_bank_accounts_user ON bank_accounts(user_id);

---

Table: recipients

Purpose: Saved international remittance recipients per user. Stores destination bank details for recurring transfers.

Column	Type	Nullable	Default	Constraints	Description
id	TEXT	NO	—	PRIMARY KEY	Format: rec_{hex16}
user_id	TEXT	NO	—	FK → users(id)	Recipient owner
name	TEXT	NO	—	NOT NULL	Recipient full name (validated)
country	TEXT	NO	—	NOT NULL	ISO 2-letter: RS, BA, PL, PK, TR
currency	TEXT	NO	—	NOT NULL	Target currency: RSD, BAM, PLN, PKR, TRY
bank_account	TEXT	NO	—	NOT NULL	Foreign bank account number (masked on read)
bank_name	TEXT	YES	NULL	—	Destination bank name (max 200 chars)
created_at	TEXT	NO	datetime('now')	—

CREATE TABLE recipients (
    id TEXT PRIMARY KEY,
    user_id TEXT NOT NULL REFERENCES users(id),
    name TEXT NOT NULL,
    country TEXT NOT NULL,
    currency TEXT NOT NULL,
    bank_account TEXT NOT NULL,
    bank_name TEXT,
    created_at TEXT DEFAULT (datetime('now'))
);

CREATE INDEX idx_recipients_user ON recipients(user_id);

---

Table: merchants

Purpose: Registered merchant profiles. Merchants can accept QR payments from Drop users. fee_rate controls the percentage taken per transaction.

Column	Type	Nullable	Default	Constraints	Description
id	TEXT	NO	—	PRIMARY KEY	Format: mer_{hex16}
user_id	TEXT	NO	—	FK → users(id)	Merchant owner (must be role=merchant)
business_name	TEXT	NO	—	NOT NULL	Validated business name
org_number	TEXT	NO	—	UNIQUE NOT NULL	Norwegian org number (9 digits)
address	TEXT	YES	NULL	—	Business address (max 300 chars)
bank_account	TEXT	NO	—	NOT NULL	Merchant payout account
fee_rate	REAL	YES	0.01	—	Transaction fee rate (default 1% = 0.01)
status	TEXT	YES	'active'	—	active or suspended
created_at	TEXT	NO	datetime('now')	—

CREATE TABLE merchants (
    id TEXT PRIMARY KEY,
    user_id TEXT NOT NULL REFERENCES users(id),
    business_name TEXT NOT NULL,
    org_number TEXT UNIQUE NOT NULL,
    address TEXT,
    bank_account TEXT NOT NULL,
    fee_rate REAL DEFAULT 0.01,
    status TEXT DEFAULT 'active',
    created_at TEXT DEFAULT (datetime('now'))
);

CREATE INDEX idx_merchants_org ON merchants(org_number);

---

Table: transactions

Purpose: All financial transactions — both remittances and QR payments. Central financial record for the system.

Remittances: recipient_id set, merchant_id NULL. send_* / receive_* / exchange_rate populated. QR Payments: merchant_id set, recipient_id NULL. send_* / receive_* / exchange_rate NULL.

Column	Type	Nullable	Default	Constraints	Description
id	TEXT	NO	—	PRIMARY KEY	Format: tx_{hex16}
user_id	TEXT	NO	—	FK → users(id)	Sender
type	TEXT	NO	—	NOT NULL, CHECK IN ('remittance','qr_payment')	Transaction type
status	TEXT	YES	'processing'	CHECK IN ('processing','completed','failed')	Current status
amount	REAL	NO	—	NOT NULL	NOK amount deducted from bank account
currency	TEXT	YES	'NOK'	—	Source currency (always NOK in Phase 1)
fee	REAL	YES	0	—	Fee charged (remittance: 0.5%; QR: 1%)
recipient_id	TEXT	YES	NULL	FK → recipients(id)	Remittance target (NULL for QR)
merchant_id	TEXT	YES	NULL	FK → merchants(id)	QR payment target (NULL for remittance)
send_amount	REAL	YES	NULL	—	Amount sent in source currency (remittance)
send_currency	TEXT	YES	NULL	—	Source currency (remittance)
receive_amount	REAL	YES	NULL	—	Amount received in target currency (remittance)
receive_currency	TEXT	YES	NULL	—	Target currency code (remittance)
exchange_rate	REAL	YES	NULL	—	Rate applied at time of transaction (remittance)
created_at	TEXT	NO	datetime('now')	—	Transaction initiation
completed_at	TEXT	YES	NULL	—	Transaction completion (NULL while processing)

CREATE TABLE transactions (
    id TEXT PRIMARY KEY,
    user_id TEXT NOT NULL REFERENCES users(id),
    type TEXT NOT NULL CHECK(type IN ('remittance', 'qr_payment')),
    status TEXT DEFAULT 'processing' CHECK(status IN ('processing', 'completed', 'failed')),
    amount REAL NOT NULL,
    currency TEXT DEFAULT 'NOK',
    fee REAL DEFAULT 0,
    recipient_id TEXT REFERENCES recipients(id),
    merchant_id TEXT REFERENCES merchants(id),
    send_amount REAL,
    send_currency TEXT,
    receive_amount REAL,
    receive_currency TEXT,
    exchange_rate REAL,
    created_at TEXT DEFAULT (datetime('now')),
    completed_at TEXT
);

CREATE INDEX idx_transactions_user ON transactions(user_id);
CREATE INDEX idx_transactions_merchant ON transactions(merchant_id);

---

Table: exchange_rates

Purpose: Currency exchange rates from NOK. Seeded with 6 corridors. Updated manually or via external rate feed in production.

Column	Type	Nullable	Default	Constraints	Description
id	INTEGER (SQLite) / SERIAL (PG)	NO	AUTOINCREMENT	PRIMARY KEY	Numeric auto-increment
from_currency	TEXT	YES	'NOK'	—	Source currency (always NOK in Phase 1)
to_currency	TEXT	NO	—	NOT NULL	Target currency code
rate	REAL	NO	—	NOT NULL	Rate: 1 NOK = rate target currency
updated_at	TEXT	YES	datetime('now')	—	Last rate update

Seeded corridors (db.ts:531-545):

from_currency	to_currency	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

-- SQLite
CREATE TABLE exchange_rates (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    from_currency TEXT DEFAULT 'NOK',
    to_currency TEXT NOT NULL,
    rate REAL NOT NULL,
    updated_at TEXT DEFAULT (datetime('now'))
);

-- PostgreSQL
CREATE TABLE exchange_rates (
    id SERIAL PRIMARY KEY,
    from_currency TEXT NOT NULL DEFAULT 'NOK',
    to_currency TEXT NOT NULL,
    rate REAL NOT NULL,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

---

Table: notifications

Purpose: In-app notifications for transaction events, KYC updates, and system alerts.

Column	Type	Nullable	Default	Constraints	Description
id	TEXT	NO	—	PRIMARY KEY	Format: noti_{hex16}
user_id	TEXT	NO	—	FK → users(id)	Notification recipient
type	TEXT	NO	—	NOT NULL	e.g., transaction_completed, kyc_approved
title	TEXT	NO	—	NOT NULL	Short notification title (Norwegian)
body	TEXT	NO	—	NOT NULL	Full notification body
read	INTEGER	YES	0	—	0 = unread, 1 = read
created_at	TEXT	NO	datetime('now')	—

CREATE TABLE notifications (
    id TEXT PRIMARY KEY,
    user_id TEXT NOT NULL REFERENCES users(id),
    type TEXT NOT NULL,
    title TEXT NOT NULL,
    body TEXT NOT NULL,
    read INTEGER DEFAULT 0,
    created_at TEXT DEFAULT (datetime('now'))
);

CREATE INDEX idx_notifications_user ON notifications(user_id);

---

Table: cards (FUTURE — feature-flagged)

Note: Cards are a future feature gated behind feature flags (all flags default false). Table exists in schema but is not actively used until a card issuing partner (e.g., Stripe Issuing) is integrated.

Column	Type	Nullable	Default	Constraints	Description
id	TEXT	NO	—	PRIMARY KEY	Format: card_{hex16}
user_id	TEXT	NO	—	FK → users(id)	Card owner
type	TEXT	YES	'virtual'	CHECK IN ('virtual','physical')	Card type
last_four	TEXT	NO	—	NOT NULL	Last 4 digits (PCI-DSS: never store full PAN)
token_ref	TEXT	YES	NULL	—	External card issuer token reference
expiry	TEXT	NO	—	NOT NULL	Format: MM/YY
status	TEXT	YES	'active'	CHECK IN ('active','frozen','cancelled')	Card state
shipping_address	TEXT	YES	NULL	—	Physical card delivery address
created_at	TEXT	NO	datetime('now')	—
pin_hash	TEXT	YES	NULL	—	bcrypt-hashed PIN (added via runtime migration in cards/[id]/pin/route.ts:51-53)

CREATE TABLE cards (
    id TEXT PRIMARY KEY,
    user_id TEXT NOT NULL REFERENCES users(id),
    type TEXT DEFAULT 'virtual' CHECK(type IN ('virtual', 'physical')),
    last_four TEXT NOT NULL,
    token_ref TEXT,
    expiry TEXT NOT NULL,
    status TEXT DEFAULT 'active' CHECK(status IN ('active', 'frozen', 'cancelled')),
    shipping_address TEXT,
    created_at TEXT DEFAULT (datetime('now')),
    pin_hash TEXT
);

CREATE INDEX idx_cards_user ON cards(user_id);

---

Table: spending_limits (FUTURE — feature-flagged)

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

Column	Type	Nullable	Default	Constraints	Description
id	TEXT	NO	—	PRIMARY KEY
user_id	TEXT	NO	—	FK → users(id)
card_id	TEXT	YES	NULL	FK → cards(id)	Which card this limit applies to
limit_type	TEXT	NO	—	NOT NULL	daily, weekly, monthly, or transaction
amount	REAL	NO	—	NOT NULL	Limit amount in NOK
currency	TEXT	YES	'NOK'	—
created_at	TEXT	NO	datetime('now')	—

CREATE TABLE spending_limits (
    id TEXT PRIMARY KEY,
    user_id TEXT NOT NULL REFERENCES users(id),
    card_id TEXT REFERENCES cards(id),
    limit_type TEXT NOT NULL,
    amount REAL NOT NULL,
    currency TEXT DEFAULT 'NOK',
    created_at TEXT DEFAULT (datetime('now'))
);

CREATE INDEX idx_spending_limits_user ON spending_limits(user_id);
CREATE INDEX idx_spending_limits_card ON spending_limits(card_id);

---

4.3 System Domain

Table: rate_limits

Purpose: Persistent rate limiting store. Used by middleware.ts:rateLimit() for IP-based rate limiting. Expired entries pruned on each call.

Column	Type	Nullable	Default	Constraints	Description
key	TEXT	NO	—	PRIMARY KEY	Format: {endpoint}:{ip_address}
count	INTEGER	NO	—	NOT NULL	Request count in current window
reset_at	INTEGER	NO	—	NOT NULL	Unix timestamp when window resets

CREATE TABLE rate_limits (
    key TEXT PRIMARY KEY,
    count INTEGER NOT NULL,
    reset_at INTEGER NOT NULL
);

---

4.4 Compliance & GDPR Domain

Added 2026-02-16. All 7 tables below support Norwegian financial services compliance (PSD2, GDPR, AML/KYC, Finansavtaleloven).

---

Table: audit_log

Purpose: Tamper-evident audit trail of all significant user actions. Required for AML and PSD2 compliance.

Column	Type	Nullable	Default	Constraints	Description
id	TEXT	NO	—	PRIMARY KEY
timestamp	TEXT	NO	datetime('now')	—	Event timestamp (UTC)
user_id	TEXT	YES	NULL	FK → users(id)	NULL for anonymous/system events
action	TEXT	NO	—	NOT NULL	e.g., login, transaction_created, kyc_approved
resource_type	TEXT	YES	NULL	—	e.g., transaction, user, recipient
resource_id	TEXT	YES	NULL	—	ID of the affected resource
details	TEXT	YES	NULL	—	JSON-serialized event details
ip_address	TEXT	YES	NULL	—	Client IP address
user_agent	TEXT	YES	NULL	—	Client User-Agent string

CREATE TABLE audit_log (
    id TEXT PRIMARY KEY,
    timestamp TEXT DEFAULT (datetime('now')),
    user_id TEXT REFERENCES users(id),
    action TEXT NOT NULL,
    resource_type TEXT,
    resource_id TEXT,
    details TEXT,
    ip_address TEXT,
    user_agent TEXT
);

CREATE INDEX idx_audit_log_user ON audit_log(user_id);
CREATE INDEX idx_audit_log_timestamp ON audit_log(timestamp);
CREATE INDEX idx_audit_log_action ON audit_log(action);

---

Table: aml_alerts

Purpose: AML transaction monitoring alerts. Records suspicious patterns flagged by automated monitoring rules (structuring, velocity, high-risk corridors). Reviewed by compliance team.

Column	Type	Nullable	Default	Constraints	Description
id	TEXT	NO	—	PRIMARY KEY
user_id	TEXT	NO	—	FK → users(id)	Flagged user
alert_type	TEXT	NO	—	NOT NULL	e.g., structuring, velocity, high_risk_corridor
severity	TEXT	NO	—	NOT NULL, CHECK IN ('low','medium','high','critical')	Alert severity
transaction_id	TEXT	YES	NULL	FK → transactions(id)	Triggering transaction (if applicable)
details	TEXT	YES	NULL	—	JSON-serialized alert details
status	TEXT	YES	'open'	CHECK IN ('open','investigating','resolved','escalated','filed')	Review status
reviewed_by	TEXT	YES	NULL	—	Compliance officer ID
reviewed_at	TEXT	YES	NULL	—	Review timestamp
created_at	TEXT	NO	datetime('now')	—	Alert creation

CREATE TABLE aml_alerts (
    id TEXT PRIMARY KEY,
    user_id TEXT NOT NULL REFERENCES users(id),
    alert_type TEXT NOT NULL,
    severity TEXT NOT NULL CHECK(severity IN ('low', 'medium', 'high', 'critical')),
    transaction_id TEXT REFERENCES transactions(id),
    details TEXT,
    status TEXT DEFAULT 'open' CHECK(status IN ('open', 'investigating', 'resolved', 'escalated', 'filed')),
    reviewed_by TEXT,
    reviewed_at TEXT,
    created_at TEXT DEFAULT (datetime('now'))
);

CREATE INDEX idx_aml_alerts_user ON aml_alerts(user_id);
CREATE INDEX idx_aml_alerts_status ON aml_alerts(status);

---

Table: str_reports

Purpose: Suspicious Transaction Reports (STRs) filed with Økokrim/EFE (Norwegian financial intelligence unit) per hvitvaskingsloven §26-28 requirements.

Column	Type	Nullable	Default	Constraints	Description
id	TEXT	NO	—	PRIMARY KEY
user_id	TEXT	NO	—	FK → users(id)	Subject user
alert_id	TEXT	YES	NULL	FK → aml_alerts(id)	Originating alert
report_type	TEXT	NO	—	NOT NULL	e.g., str, sar
status	TEXT	YES	'draft'	CHECK IN ('draft','submitted','acknowledged')	Filing status
filed_at	TEXT	YES	NULL	—	Submission timestamp
reference_number	TEXT	YES	NULL	—	Regulatory reference number from Økokrim
details	TEXT	YES	NULL	—	Report content (JSON or text)
created_at	TEXT	NO	datetime('now')	—

CREATE TABLE str_reports (
    id TEXT PRIMARY KEY,
    user_id TEXT NOT NULL REFERENCES users(id),
    alert_id TEXT REFERENCES aml_alerts(id),
    report_type TEXT NOT NULL,
    status TEXT DEFAULT 'draft' CHECK(status IN ('draft', 'submitted', 'acknowledged')),
    filed_at TEXT,
    reference_number TEXT,
    details TEXT,
    created_at TEXT DEFAULT (datetime('now'))
);

---

Table: screening_results

Purpose: Results from automated PEP/sanctions screening via Sumsub. Stores one record per screening run per user. Idempotency check for Sumsub webhook replay.

Column	Type	Nullable	Default	Constraints	Description
id	TEXT	NO	—	PRIMARY KEY
user_id	TEXT	NO	—	FK → users(id)	Screened user
screening_type	TEXT	NO	—	NOT NULL, CHECK IN ('pep','sanctions','adverse_media')	Type of screening
provider	TEXT	YES	NULL	—	Screening provider (e.g., sumsub)
result	TEXT	NO	—	NOT NULL, CHECK IN ('clear','match','potential_match','error')	Screening result
match_details	TEXT	YES	NULL	—	Match details if result != 'clear' (JSON)
screened_at	TEXT	NO	datetime('now')	—	Screening timestamp

CREATE TABLE screening_results (
    id TEXT PRIMARY KEY,
    user_id TEXT NOT NULL REFERENCES users(id),
    screening_type TEXT NOT NULL CHECK(screening_type IN ('pep', 'sanctions', 'adverse_media')),
    provider TEXT,
    result TEXT NOT NULL CHECK(result IN ('clear', 'match', 'potential_match', 'error')),
    match_details TEXT,
    screened_at TEXT DEFAULT (datetime('now'))
);

CREATE INDEX idx_screening_user ON screening_results(user_id);

---

Table: consents

Purpose: GDPR consent tracking (GDPR Art. 7). Records when users grant or withdraw consent for each type of data processing. Proof of consent includes IP address.

Column	Type	Nullable	Default	Constraints	Description
id	TEXT	NO	—	PRIMARY KEY	Format: con_{hex16}
user_id	TEXT	NO	—	FK → users(id)	Consent subject
consent_type	TEXT	NO	—	NOT NULL	terms, privacy, marketing, cookies_analytics, cookies_marketing
granted	INTEGER	NO	1	NOT NULL	1 = granted, 0 = withdrawn
granted_at	TEXT	YES	datetime('now')	—	When consent was granted
withdrawn_at	TEXT	YES	NULL	—	When consent was withdrawn (NULL if active)
ip_address	TEXT	YES	NULL	—	Client IP at time of consent action

CREATE TABLE consents (
    id TEXT PRIMARY KEY,
    user_id TEXT NOT NULL REFERENCES users(id),
    consent_type TEXT NOT NULL,
    granted INTEGER NOT NULL DEFAULT 1,
    granted_at TEXT DEFAULT (datetime('now')),
    withdrawn_at TEXT,
    ip_address TEXT
);

CREATE INDEX idx_consents_user ON consents(user_id);

---

Table: data_access_requests

Purpose: GDPR data subject requests (Art. 15–17): export, erasure, rectification, restriction. Tracks completion status for each request.

Column	Type	Nullable	Default	Constraints	Description
id	TEXT	NO	—	PRIMARY KEY
user_id	TEXT	NO	—	FK → users(id)	Requesting user
request_type	TEXT	NO	—	NOT NULL, CHECK IN ('export','erasure','rectification','restriction')	GDPR right exercised
status	TEXT	YES	'pending'	CHECK IN ('pending','processing','completed','rejected')	Processing status
requested_at	TEXT	NO	datetime('now')	—	Request submission
completed_at	TEXT	YES	NULL	—	Request fulfillment timestamp
download_url	TEXT	YES	NULL	—	Signed S3 URL for data export (if type=export)
notes	TEXT	YES	NULL	—	Internal processing notes

CREATE TABLE data_access_requests (
    id TEXT PRIMARY KEY,
    user_id TEXT NOT NULL REFERENCES users(id),
    request_type TEXT NOT NULL CHECK(request_type IN ('export', 'erasure', 'rectification', 'restriction')),
    status TEXT DEFAULT 'pending' CHECK(status IN ('pending', 'processing', 'completed', 'rejected')),
    requested_at TEXT DEFAULT (datetime('now')),
    completed_at TEXT,
    download_url TEXT,
    notes TEXT
);

CREATE INDEX idx_data_requests_user ON data_access_requests(user_id);

---

Table: complaints

Purpose: Formal customer complaint logging per Finansavtaleloven §3-53 (15 business day response obligation). Supports tracking, escalation, and resolution.

Column	Type	Nullable	Default	Constraints	Description
id	TEXT	NO	—	PRIMARY KEY	Format: cmp_{hex16}
user_id	TEXT	NO	—	FK → users(id)	Complainant
category	TEXT	NO	—	NOT NULL	transaction, service, fees, privacy, technical, other
subject	TEXT	NO	—	NOT NULL	Short complaint title (max 200 chars)
description	TEXT	NO	—	NOT NULL	Full complaint text (max 2000 chars)
status	TEXT	YES	'received'	CHECK IN ('received','investigating','resolved','escalated')	Processing status
resolution	TEXT	YES	NULL	—	Resolution description when status=resolved
created_at	TEXT	NO	datetime('now')	—	Complaint submission
resolved_at	TEXT	YES	NULL	—	Resolution timestamp

CREATE TABLE complaints (
    id TEXT PRIMARY KEY,
    user_id TEXT NOT NULL REFERENCES users(id),
    category TEXT NOT NULL,
    subject TEXT NOT NULL,
    description TEXT NOT NULL,
    status TEXT DEFAULT 'received' CHECK(status IN ('received', 'investigating', 'resolved', 'escalated')),
    resolution TEXT,
    created_at TEXT DEFAULT (datetime('now')),
    resolved_at TEXT
);

CREATE INDEX idx_complaints_user ON complaints(user_id);
CREATE INDEX idx_complaints_status ON complaints(status);

---

5. Enums & Check Constraints

Drop uses inline CHECK constraints rather than PostgreSQL ENUM types to maintain SQLite/PostgreSQL portability. The access layer handles both.

5.1 Inline Check Constraints

-- users
kyc_status IN ('pending', 'approved', 'rejected')
role IN ('user', 'merchant')

-- transactions
type IN ('remittance', 'qr_payment')
status IN ('processing', 'completed', 'failed')

-- cards
type IN ('virtual', 'physical')
status IN ('active', 'frozen', 'cancelled')

-- aml_alerts
severity IN ('low', 'medium', 'high', 'critical')
status IN ('open', 'investigating', 'resolved', 'escalated', 'filed')

-- str_reports
status IN ('draft', 'submitted', 'acknowledged')

-- screening_results
screening_type IN ('pep', 'sanctions', 'adverse_media')
result IN ('clear', 'match', 'potential_match', 'error')

-- data_access_requests
request_type IN ('export', 'erasure', 'rectification', 'restriction')
status IN ('pending', 'processing', 'completed', 'rejected')

-- complaints
status IN ('received', 'investigating', 'resolved', 'escalated')

5.2 Application-Enforced Enums (not in DB schema)

The following enumerations are enforced at the API layer but not in DB CHECK constraints:

Field	Allowed Values	Enforced In
recipients.country	RS, BA, PL, PK, TR	POST /api/recipients
settings.currency	EUR, USD, GBP, BAM, CHF, PLN, NOK, RSD, TRY, PKR	PATCH /api/settings
settings.language	nb, en, bs, sq	PATCH /api/settings
consents.consent_type	terms, privacy, marketing, cookies_analytics, cookies_marketing	POST /api/consents
complaints.category	transaction, service, fees, privacy, technical, other	POST /api/complaints
spending_limits.limit_type	daily, weekly, monthly, transaction	PUT /api/cards/{id}/limits

---

6. Views & Materialized Views

Current state: No views or materialized views defined in Phase 1. All queries are inline SQL in the Hono API route handlers.

Planned (Phase 2+):

View: active_users (planned)

Purpose: Filter out soft-deleted users

-- Phase 2 planning — users table does not have deleted_at yet
-- Requires schema migration to add soft-delete support before creating this view

Materialized View: merchant_daily_revenue (Phase 3 consideration)

Purpose: Pre-aggregate daily merchant revenue for dashboard queries Staleness acceptable: Up to 1 hour

-- To be defined in Phase 3 when merchant dashboard requires faster aggregation
-- Current: inline query on transactions table with GROUP BY

---

7. Stored Procedures & Functions

Current state: No stored procedures or database functions defined. All logic is implemented at the application layer in TypeScript. This aligns with the monolith-first strategy (ADR-005).

Exception — SQLite WAL init (db.ts:37-38):

PRAGMA journal_mode=WAL;
PRAGMA foreign_keys=ON;

---

8. Migration Strategy & Tooling

Current approach: initDb() function in db.ts executes CREATE TABLE IF NOT EXISTS statements on application startup. No versioned migration files.

Implications:

• Schema changes require manual ALTER TABLE or drop/recreate (development)
• No rollback capability for production schema changes
• No migration history tracking

Recommended upgrade path (Phase 2):

Tool: Flyway or custom SQL migration files Convention: V{timestamp}__{description}.sql Location: db/migrations/ Executed by: CI/CD pipeline before application deployment

Zero-Downtime Migration Checklist

Before every DDL migration in production:

• Can this run on a live table without locking? (Use CONCURRENTLY for index creation)
• Does this add a NOT NULL column? (Add as nullable first, backfill, then add constraint)
• Does this remove a column? (Ensure app code deployed without references first)
• Does this rename a column or table? (Use expansion-contraction pattern)
• Has this been tested against production-sized data on staging?

Expansion-Contraction Pattern for Renaming

Step 1 (Expand): Add new_column alongside old_column
Step 2 (App deploy): Write to both, read from old
Step 3 (Backfill): Copy data from old to new
Step 4 (App deploy): Read from new, write to both
Step 5 (App deploy): Write to new only
Step 6 (Contract): DROP old_column

---

9. Seed Data Requirements

9.1 Required Seed Data (production)

-- Exchange rates (required for all transactions — seeded by initDb() if empty)
INSERT OR IGNORE INTO exchange_rates (from_currency, to_currency, rate) VALUES
    ('NOK', 'RSD', 11.7),
    ('NOK', 'BAM', 1.04),
    ('NOK', 'PLN', 0.41),
    ('NOK', 'PKR', 26.8),
    ('NOK', 'TRY', 3.45),
    ('NOK', 'EUR', 0.089);

9.2 Development Seed Data

Condition: NODE_ENV !== 'production' OR SEED_DEMO=true Script: db.ts:530 (seedData() function)

Demo data (seeded automatically):
  - 1 user: usr_demo1 ([email protected], role: merchant, kyc_status: approved)
  - 3 recipients: Mama Jasmina (RS/RSD), Dedo Muhamed (BA/BAM), Mehmet (TR/TRY)
  - 1 merchant: Ahmetov Kebab (org: 123456789, fee_rate: 0.01)
  - 2 bank accounts: DNB primary (45,230 NOK), SpareBank 1 (12,800 NOK)
  - 3 transactions:
    - tx_rem_1: remittance to RS, 2000 NOK, 23400 RSD, completed
    - tx_rem_2: remittance to BA, 1000 NOK, 1040 BAM, completed
    - tx_qr_1: QR payment to Ahmetov Kebab, 129 NOK, completed

---

10. Performance Considerations

10.1 Indexing Strategy (Current)

Table	Index	Columns	Purpose
recipients	idx_recipients_user	user_id	List recipients by user
merchants	idx_merchants_org	org_number	Unique org number lookup
transactions	idx_transactions_user	user_id	List user's transactions
transactions	idx_transactions_merchant	merchant_id	Merchant transaction lookup
bank_accounts	idx_bank_accounts_user	user_id	User's bank accounts
cards	idx_cards_user	user_id	User's cards
sessions	idx_sessions_user	user_id	User's sessions
sessions	idx_sessions_token	token_hash	JWT validation on every request
notifications	idx_notifications_user	user_id	User's notifications
audit_log	idx_audit_log_user	user_id	User audit trail lookup
audit_log	idx_audit_log_timestamp	timestamp	Time-range queries
audit_log	idx_audit_log_action	action	Action-type filtering
aml_alerts	idx_aml_alerts_user	user_id	User's alerts
aml_alerts	idx_aml_alerts_status	status	Open alert queue
screening_results	idx_screening_user	user_id	User's screening history
consents	idx_consents_user	user_id	User's consent records
data_access_requests	idx_data_requests_user	user_id	User's GDPR requests
complaints	idx_complaints_user	user_id	User's complaints
complaints	idx_complaints_status	status	Open complaint queue

10.2 Query Performance Standards

Query Pattern	Target (p99)	Current Optimization
PK lookup (id)	< 5ms	B-tree on id
User's transactions list	< 50ms	idx_transactions_user
Session validation (every request)	< 5ms	idx_sessions_token
Exchange rate lookup	< 5ms	Full table scan (6 rows — acceptable)
Merchant dashboard aggregation	< 200ms	idx_transactions_merchant + GROUP BY
Audit log time-range query	< 500ms	idx_audit_log_timestamp

10.3 Connection Pooling

Phase 1: Direct connection from App Runner → PostgreSQL (single instance, low traffic expected) Phase 2+: Evaluate PgBouncer (transaction mode) when concurrent connections exceed 20

---

11. Backup & Recovery Procedures

Current status (Phase 1): TBD — requires AWS RDS configuration

Backup Type	Method	Frequency	Retention	Location
Continuous WAL	RDS automated backup	Continuous	7 days	AWS S3 (eu-north-1)
Base snapshot	RDS automated snapshot	Daily	7 days	AWS S3 (eu-north-1)
Logical dump	pg_dump	Weekly	4 weeks	AWS S3 Glacier
Schema-only	pg_dump --schema-only	On every migration	Indefinite	Git repository

RTO target: 1 hour (restore from daily snapshot) RPO target: 5 minutes (WAL archiving)

Recovery test schedule: Monthly Recovery runbook: TBD — requires AWS runbook documentation (Phase 2)

# Point-in-time recovery (RDS)
aws rds restore-db-instance-to-point-in-time \
  --source-db-instance-identifier drop-prod \
  --target-db-instance-identifier drop-prod-restored \
  --restore-time "2026-02-23T10:00:00Z"

SQLite Development Backup:

# Manual backup for dev environment
sqlite3 ./data/drop.db ".backup ./data/drop-backup-$(date +%Y%m%d).db"

---

Approval

Role	Name	Date	Signature
Author	Petter Graff	2026-02-23
DBA / Platform
Security Review
Tech Lead	John (AI Director)