Database Schema Document

Database Schema Document

Project: Drop{{PROJECT_NAME}} Database: drop_db (PostgreSQL prod) / drop.db (SQLite dev){{DATABASE_NAME}} Version: 1.0{{VERSION}} Date: 2026-02-23{{DATE}} Author: Petter Graff, Senior Enterprise Architect{{AUTHOR}} Status: Draft | In Review | Approved Reviewers: Alem Bašić (CEO), John (AI Director){{REVIEWERS}}

Document History

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

---

1. Database Technology & Version

Property	Value
Technology (Production)	PostgreSQL{{DB_TECHNOLOGY}} 16(e.g., PostgreSQL, MySQL, MongoDB, DynamoDB)
Technology (Development)Version	SQLite (via better-sqlite3){{DB_VERSION}}
Hosting (Production)	{{HOSTING}} (e.g., AWS RDSRDS, (PLANNEDCloud —SQL, region: eu-north-1 Stockholm)self-hosted)
Instance type	TBD — requires AWS sizing exercise{{INSTANCE_TYPE}}
Storage	TBD{{STORAGE_SIZE}} — auto-scaling: YES (RDS storage autoscaling){{YES/NO}}
Read replicas	0{{N}} replicas in Phase 1; evaluate at > 10K concurrent users{{REGIONS}}
Connection pooling	None in Phase 1{{POOLER}} (directe.g., connectionPgBouncer, fromRDS App Runner)Proxy) — evaluatepool PgBouncersize: at scale{{POOL_SIZE}}
Encoding	UTF-8
Timezone	UTC (all timestamps in UTC)
Migration tool	Custom inline schema{{MIGRATION_TOOL}} (initDb()e.g., inFlyway, db.ts)Liquibase, —Prisma noMigrate, 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)custom)

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
    USERSTENANT {
        textuuid id PK
        textstring name
        string slug UK
        string plan
        timestamptz created_at
        timestamptz deleted_at
    }

    USER {
        uuid id PK
        uuid tenant_id FK
        string email UK
        textstring full_name
        string password_hash
        text first_name
        text last_name
        text phone
        text date_of_birth
        text kyc_status
        textstring role
        textboolean is_verified
        timestamptz last_login_at
        timestamptz created_at
        timestamptz deleted_at
    }

    RECIPIENTS{{ENTITY_1}} {
        textuuid id PK
        textuuid user_idtenant_id FK
        textuuid namecreated_by textFK
        countrystring text{{field_1}}
        currencystring text{{field_2}}
        bank_accountstring textstatus
        bank_nameint textversion
        timestamptz created_at
        timestamptz updated_at
        timestamptz deleted_at
    }

    MERCHANTS{{ENTITY_2}} {
        textuuid id PK
        textuuid user_id{{entity_1_id}} FK
        textstring business_name{{field_1}}
        textdecimal org_number{{amount_field}}
        UK
        text address
        text bank_account
        real fee_rate
        text status
        texttimestamptz 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 {
        textuuid id PK
        textuuid timestamp
        text user_idtenant_id FK
        textuuid actor_id FK
        string entity_type
        uuid entity_id
        string action
        textjsonb resource_typeold_values
        textjsonb resource_idnew_values
        text details
        textstring 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
        texttimestamptz 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
    }

    USERSTENANT ||--o{ RECIPIENTSUSER : "has"
    USERSTENANT ||--o{ TRANSACTIONS{{ENTITY_1}} : "initiates"owns"
    USERSUSER ||--o{ BANK_ACCOUNTS{{ENTITY_1}} : "links"creates"
    USERS{{ENTITY_1}} ||--o{ CARDS{{ENTITY_2}} : "holds"contains"
    USERS ||--o{ SESSIONS : "has"
    USERS ||--o{ NOTIFICATIONS : "receives"
    USERS ||--o{ SETTINGS : "configures"
    USERS ||--o{ SPENDING_LIMITS : "sets"
    USERSUSER ||--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_accountsuser_profiles, audit_logorder_items
Columns	snake_case	created_at, user_idtenant_id
Primary keys	Always id (TEXT in Drop — custom prefixed IDs)UUID)	id TEXTUUID PRIMARY KEY
Foreign keys	{referenced_table_singular}_id	user_id, recipient_idtenant_id
Indexes	idx_{table}_{column}column(s)}	idx_transactions_useridx_users_email
Unique indexes	uq_{table}_{column}column(s)} (convention; SQLite uses UNIQUE inline)	uq_users_emailuq_users_tenant_email
Enum types	snake_case	user_role, order_status
Junction tables	{table1}_{table2} (alphabetical)	role_permissions
Sequences	Auto (via gen_random_uuid())

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 (all tables)

In tocreationtimestamp

Column	PresentType	Nullable	Default	Description
id	All tables	Custom-prefixed unique identifier
user_idUUID	Most tablesNO	FKgen_random_uuid()	Surrogate users(id)primary — user ownershipkey
created_at	Most tablesTIMESTAMPTZ	ISO-8601NO	NOW()	Immutable — set on insert
updated_at	TIMESTAMPTZ	NO	NOW()	Auto-updated via trigger
deleted_at	TIMESTAMPTZ	YES	NULL	Soft delete (SQLiteNULL default:= active)
datetime('now'version	INTEGER	NO	1	Optimistic lock counter

3.3 Data Type Standards

Data	PostgreSQL Type	Notes
Primary keys	UUID	gen_random_uuid()) default
Short strings	VARCHAR(N)	Specify max length
Long text	TEXT	No length limit
Money / currency	NUMERIC(19, 4)	Never FLOAT for money
Booleans	BOOLEAN	NOT NULL with DEFAULT
Enums	custom ENUM type	Define in migrations
JSON data	JSONB	Prefer JSONB over JSON
IP addresses	INET	Native IP type
URLs	TEXT	Validated at app layer
Timestamps	TIMESTAMPTZ	Always with timezone
Dates (no time)	DATE
Durations	INTERVAL

---

4. Tables by Domain

4.1 UserIdentity & Access Domain

Table: userstenants

Purpose: PrimaryTop-level usermulti-tenancy accounts.isolation Authenticationunit. identity.Every KYCresource statusbelongs gateto fora 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.tenant.

timestamp

Column	Type	Nullable	Default	Constraints	Description
id	TEXTUUID	NO	—gen_random_uuid()	PRIMARY KEYPK	Format:Tenant usr_{hex16}identifier
emailname	TEXTVARCHAR(255)	NO	—	UNIQUE NOT NULL	Normalized; used for legacy login
password_hash	TEXT	NO	—	NOT NULL	bcryptDisplay with 12 rounds (utils-server.ts:8-11)name
first_nameslug	TEXTVARCHAR(100)	NO	—	UNIQUE, NOT NULL	ExtractedURL-safe from BankID id_token.name splitidentifier
last_nameplan	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	TEXTtenant_plan	NO	'pending'free'	CHECKNOT IN ('pending','approved','rejected')NULL	UpdatedSubscription by Sumsub webhookplan
rolesettings	TEXTJSONB	NO	'user'{}'::jsonb	CHECK IN ('user','merchant')	ElevatedTenant to merchant on POST /api/merchants/registerconfiguration
created_at	TEXTTIMESTAMPTZ	NO	datetime('now'NOW()	—	Registration
deleted_at	TIMESTAMPTZ	YES	NULL

-- SQLiteEnum
CREATE TABLETYPE userstenant_plan (AS 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 INENUM ('pending','approved','rejected'))free', role TEXT DEFAULT'starter', 'user' CHECK(role IN ('user','merchant'))pro', created_at TEXT DEFAULT (datetime('now'))
enterprise');

-- PostgreSQL equivalentTable
CREATE TABLE userstenants (
    id TEXTUUID PRIMARY KEY,KEY emailDEFAULT TEXTgen_random_uuid(),
    UNIQUEname VARCHAR(255) NOT NULL,
    password_hashslug TEXTVARCHAR(100) NOT NULL,
    first_nameplan TEXT NOT NULL,
    last_name TEXT NOT NULL,
    phone TEXT,
    date_of_birth TEXT,
    kyc_status TEXTtenant_plan NOT NULL DEFAULT 'pending' CHECK(kyc_status IN ('pending','approved','rejected'))free',
    rolesettings TEXTJSONB NOT NULL DEFAULT 'user' CHECK(role IN ({}'user','merchant')),
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMPNOW(),
    deleted_at TIMESTAMPTZ
);

-- Indexes
CREATE UNIQUE INDEX uq_tenants_slug ON tenants(slug) WHERE deleted_at IS NULL;

---

Table: sessionsusers

Purpose: JWTSystem sessionusers. trackingAuthentication for revocation support. Session records are validated on every authenticated request.identity.

EncryptedTOTP

Column	Type	Nullable	Default	Constraints	Description
id	TEXTUUID	NO	—gen_random_uuid()	PRIMARY KEYPK	Format: ses_{hex16}
user_idtenant_id	UUID	NO		FK → tenants(id)	Tenant membership
email	VARCHAR(320)	NO		NOT NULL	Normalized lowercase
password_hash	VARCHAR(255)	YES	NULL		bcrypt/Argon2 hash. NULL for SSO users
full_name	VARCHAR(255)	NO		NOT NULL
role	user_role	NO	'member'	NOT NULL	RBAC role
is_verified	BOOLEAN	NO	FALSE	NOT NULL	Email verified
last_login_at	TIMESTAMPTZ	YES	NULL
mfa_enabled	BOOLEAN	NO	FALSE	NOT NULL
mfa_secret	TEXT	NOYES	—	FK → users(id)NULL	Session owner
token_hash	TEXT	NO	—	NOT NULL	SHA-256 of the JWT string (auth.ts:59)secret
created_at	TEXTTIMESTAMPTZ	NO	datetime('now'NOW()	—	Session start
expires_atupdated_at	TEXT	NO	—	NOT NULL	Web: +24h; Mobile: +7d
revoked	INTEGERTIMESTAMPTZ	NO	0NOW()	—	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
currencydeleted_at	TEXTTIMESTAMPTZ	NOYES	'NOK'NULL	—	Display currency preference
language	TEXT	NO	'nb'	—	App language (nb, en, bs, sq)
push_enabledversion	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 TYPE user_role AS ENUM ('owner', 'admin', 'member', 'viewer', 'api');

CREATE TABLE settingsusers (
    user_idid TEXTUUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES users(tenants(id) ON DELETE RESTRICT,
    email VARCHAR(320) NOT NULL,
    password_hash VARCHAR(255),
    currencyfull_name TEXTVARCHAR(255) NOT NULL,
    role user_role NOT NULL DEFAULT 'NOK'member',
    languageis_verified TEXTBOOLEAN NOT NULL DEFAULT 'nb'FALSE,
    last_login_at TIMESTAMPTZ,
    mfa_enabled BOOLEAN NOT NULL DEFAULT FALSE,
    mfa_secret TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    push_enabledupdated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    deleted_at TIMESTAMPTZ,
    version INTEGER DEFAULTNOT 1,
    email_enabled INTEGERNULL DEFAULT 1,
    updated_at TEXT DEFAULT (datetime('now'))1
);

CREATE UNIQUE INDEX uq_users_tenant_email ON users(tenant_id, lower(email))
    WHERE deleted_at IS NULL;
CREATE INDEX idx_users_tenant_id ON users(tenant_id)
    WHERE deleted_at IS NULL;

---

4.2 Financial{{DOMAIN_NAME}} Domain

Table: bank_accounts{{table_name}}

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.{{TABLE_PURPOSE}}

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	TEXTUUID	NO	datetime('now'gen_random_uuid()	—	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')	—PK
pin_hashtenant_id	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.

card this limit applies to

Column	Type	Nullable	Default	Constraints	Description
id	TEXTUUID	NO	—	PRIMARYFK KEY→ tenants(id)
user_idcreated_by	TEXTUUID	NO	—	FK → users(id)
card_id{{field_1}}	TEXT{{TYPE}}	YES{{YES/NO}}	NULL	FK → cards(id){{DEFAULT}}	Which{{CONSTRAINTS}}	{{DESCRIPTION}}
limit_type{{field_2}}	TEXT{{TYPE}}	{{YES/NO}}	{{DEFAULT}}	{{CONSTRAINTS}}	{{DESCRIPTION}}
status	{{status_enum}}	NO	—'{{DEFAULT_STATUS}}'	NOT NULL	daily, weekly, monthly, or transaction
amount	REAL	NO	—	NOT NULL	Limit amount in NOK
currency	TEXT	YES	'NOK'	—
created_at	TEXTTIMESTAMPTZ	NO	datetime('now'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
timestampupdated_at	TEXTTIMESTAMPTZ	NO	datetime('now'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_iddeleted_at	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	TEXTTIMESTAMPTZ	YES	'open'NULL	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
grantedversion	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

---

5. Enums & Lookup Tables

5.1 Enum Types

CREATE TABLETYPE consentsuser_role AS ENUM (
    id TEXT PRIMARY KEY,
    user_id TEXT NOT NULL REFERENCES users(id)'owner', consent_type TEXT NOT NULL,
    granted INTEGER NOT NULL DEFAULT 1,
    granted_at TEXT DEFAULT (datetime('now'))admin', withdrawn_at'member', TEXT,'viewer', ip_address TEXT
'api');
CREATE INDEXTYPE idx_consents_usertenant_plan ONAS consents(user_id)ENUM ('free', 'starter', 'pro', 'enterprise');
CREATE TYPE {{entity_1}}_status AS ENUM ('draft', 'active', 'suspended', 'archived');
CREATE TYPE {{entity_2}}_type AS ENUM ('{{VALUE_1}}', '{{VALUE_2}}', '{{VALUE_3}}');

---

5.2 Lookup Tables

Table: data_access_requests{{lookup_table}}

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

Machine-readable Human-readable

Column	Type	Nullable	Default	Constraints	Description
idcode	TEXTVARCHAR(50) PK	NO	—	PRIMARY KEY	identifier
user_idlabel	TEXTVARCHAR(255)	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.

Detailed Display Whether

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)label
description	TEXT	NO	—	NOT NULL	Full complaint text (max 2000 chars)description
statussort_order	TEXTINTEGER	YES	'received'	CHECK IN ('received','investigating','resolved','escalated')	Processing statusordering
resolutionis_active	TEXTBOOLEAN	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}/limitsselectable

---

6. Views & Materialized Views

Current state:

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

Planned (Phase 2+):

Views

View: active_usersactive_{{entities}} (planned)

Purpose: Filter out soft-deleted usersrecords for common queries Refreshed: N/A (standard view)

--CREATE PhaseVIEW 2active_{{entities}} planningAS
    —SELECT users* tableFROM does{{table_name}}
    not haveWHERE deleted_at yetIS -- Requires schema migration to add soft-delete support before creating this viewNULL;

6.2 Materialized Views

Materialized View: merchant_daily_revenue{{entity}}_summary (Phase 3 consideration)

Purpose: Pre-aggregateaggregated daily merchant revenuesummary for dashboard queries Refreshed: Every {{INTERVAL}} via scheduled job Staleness acceptable: Up to 1 hour{{MAX_STALENESS}}

--CREATE ToMATERIALIZED beVIEW defined{{entity}}_summary inAS
    PhaseSELECT
        3tenant_id,
        whenDATE_TRUNC('day', merchantcreated_at) dashboardAS requiresdate,
        fasterCOUNT(*) aggregationAS total,
        COUNT(*) FILTER (WHERE status = 'active') AS active_count
    FROM {{table_name}}
    WHERE deleted_at IS NULL
    GROUP BY tenant_id, DATE_TRUNC('day', created_at);

CREATE UNIQUE INDEX ON {{entity}}_summary(tenant_id, date);

-- Current:Refresh inlinecommand query(run onby transactionsscheduler):
table-- withREFRESH GROUPMATERIALIZED BYVIEW CONCURRENTLY {{entity}}_summary;

---

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).

updated_at_trigger()

ExceptionPurpose: —Auto-update SQLiteupdated_at WALcolumn initon (db.ts:37-38):any row update

PRAGMACREATE journal_mode=WAL;OR PRAGMAREPLACE foreign_keys=ON;FUNCTION updated_at_trigger()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Apply to every table with updated_at:
CREATE TRIGGER set_updated_at
    BEFORE UPDATE ON {{table_name}}
    FOR EACH ROW EXECUTE FUNCTION updated_at_trigger();

---

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{{MIGRATION_TOOL}} or(e.g., customFlyway, SQLLiquibase, migrationPrisma filesMigrate) Convention: V{timestamp}__{description}.sql or {NNN}_{description}.{up|down}.sql Location: db/migrations/ Executed by: CI/CD pipeline before application deployment

Zero-Downtime Migration Checklist

Before every DDL migration in production:migration:

• Can this run on a live table without locking? (Use CONCURRENTLY for index creation)
• Does this add a NOTcolumn NULLwith column?a default? (AddAvoid asvolatile nullabledefaults first,on backfill,large then add constraint)tables)
• Does this remove a column? (Ensure app code is already deployed without references first)
• Does this rename a column or table?rename? (Use expansion-contractionmulti-step: pattern)add new, backfill, update app, remove old)
• HasWhat's thisthe beenestimated testedlock againsttime? production-sized(Test on staging data onof staging?production size)

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): DROPDrop old_column

---

9. Seed Data Requirements

9.1 Required Seed Data (production)

-- ExchangeSystem ratestenant (required for allinternal transactions — seeded by initDb() if empty)operations)
INSERT OR IGNORE INTO exchange_ratestenants (from_currency,id, to_currency,name, rate)slug, plan)
VALUES ('NOK'00000000-0000-0000-0000-000000000001', 'RSD'System', 11.7)'system', 'enterprise')
ON CONFLICT DO NOTHING;

-- Default lookup values
INSERT INTO {{lookup_table}} (code, label, sort_order) VALUES
    ('{{VALUE_1}}', '{{LABEL_1}}', 1),
    ('NOK'{{VALUE_2}}', 'BAM'{{LABEL_2}}', 1.04),2)
ON CONFLICT ('NOK',code) 'PLN',DO 0.41),UPDATE ('NOK',SET 'PKR',label 26.8),= ('NOK', 'TRY', 3.45),
    ('NOK', 'EUR', 0.089);EXCLUDED.label;

9.2 Development Seed Data

Condition: NODE_ENV !== 'production' OR SEED_DEMO=true Script: db.ts:530db/seeds/development.sql (Volume: {{N}} tenants, {{N}} users per tenant, {{N}} sample records Command: seedData()npm run db:seed function)or make seed-dev

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 IndexingPartitioning

Strategy (Current) Per

Table	IndexPartition Strategy	ColumnsPartition Key	PurposePartition Size
recipientsaudit_logs	Range (time)	idx_recipients_usercreated_at	user_id	List recipients by userMonthly
merchants{{events_table}}	Range (time)	idx_merchants_orgcreated_at	org_number	Unique org number lookupWeekly
transactions	idx_transactions_user	user_id{{large_table}}	List user's transactions(tenant)
transactionstenant_id	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 queuetenant

CREATE TABLE audit_logs (
    id UUID NOT NULL,
    tenant_id UUID NOT NULL,
    created_at TIMESTAMPTZ NOT NULL
) PARTITION BY RANGE (created_at);

CREATE TABLE audit_logs_2024_01
    PARTITION OF audit_logs
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

10.2 Query Performance Standards

Query Pattern	Target (p99)	Current Optimization
PK lookup (id)	< 5ms	B-tree index on id
User's transactionsTenant-scoped list	< 50ms	idx_transactions_userComposite index (tenant_id, created_at)
SessionFull-text validation (every request)	< 5ms	idx_sessions_token
Exchange rate lookup	< 5ms	Full table scan (6 rows — acceptable)
Merchant dashboard aggregationsearch	< 200ms	GIN index on idx_transactions_merchantsearch_vector + GROUP BY
AuditAggregation log time-range query(dashboard)	< 500ms	idx_audit_log_timestampMaterialized view
Cross-tenant report	< 30s	Data warehouse

10.3 Connection Pooling

Phase

Application 1: Direct connection from App Runnerconnections → PostgreSQL (single instance, low traffic expected)
Phase 2+: Evaluate PgBouncer (transaction mode) when→ concurrentPostgreSQL
connectionsPool exceedsize: 20
min={{MIN_POOL}}, max={{MAX_POOL}} per application instance
Max DB connections: {{MAX_DB_CONNECTIONS}} (= pool_size × instances + 10 reserve)

---

11. Backup & Recovery Procedures

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

Backup Type	Method	Frequency	Retention	Location
Continuous WAL	RDS automated backuppg_wal_archive	Continuous	7{{N}} days	AWS S3 (eu-north-1){{BACKUP_LOCATION}}
Base snapshot	RDSpg_basebackup automated/ cloud snapshot	Daily	7{{N}} days	AWS S3 (eu-north-1){{BACKUP_LOCATION}}
Logical dump	pg_dump (select tables)	Weekly	4{{N}} weeks	AWS S3 Glacier{{COLD_STORAGE}}
Schema-only	pg_dump --schema-only	On every migration	Indefinite	Git repository

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

Recovery test schedule: Monthly ({{DAY_OF_MONTH}}) Recovery runbook: TBD — requires AWS runbook documentation (Phase 2){{LINK_TO_RUNBOOK}}

# Point-in-time recovery (RDS)command
aws rds restore-db-instance-to-point-in-timepg_restore \
  --source-db-instance-identifierhost={{HOST}} drop-prod\
  --port=5432 \
  --username={{USER}} \
  --dbname={{DB}} \
  --target-db-instance-identifier drop-prod-restoredtime="{{TIMESTAMP}}" \
  --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"{{BACKUP_FILE}}

---

Approval

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