Database Schema Document

Database Schema Document

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

Document History

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

---

1. Database Technology & Version

Property	Value
Technology (Production)	{{DB_TECHNOLOGY}}PostgreSQL (e.g., PostgreSQL, MySQL, MongoDB, DynamoDB)16
VersionTechnology (Development)	{{DB_VERSION}}SQLite (via better-sqlite3)
Hosting (Production)	{{HOSTING}}AWS RDS (e.g.,PLANNED AWS— RDS,region: Cloudeu-north-1 SQL, self-hosted)Stockholm)
Instance type	{{INSTANCE_TYPE}}TBD — requires AWS sizing exercise
Storage	{{STORAGE_SIZE}}TBD — auto-scaling: {{YES/NO}}YES (RDS storage autoscaling)
Read replicas	{{N}} replicas0 in {{REGIONS}}Phase 1; evaluate at > 10K concurrent users
Connection pooling	{{POOLER}}None in Phase 1 (e.g.,direct PgBouncer,connection RDSfrom Proxy)App Runner) — poolevaluate size:PgBouncer {{POOL_SIZE}}at scale
Encoding	UTF-8
Timezone	UTC (all timestamps in UTC)
Migration tool	{{MIGRATION_TOOL}}Custom inline schema (e.g.,initDb() Flyway,in Liquibase,db.ts) Prisma— Migrate,no custom)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
    TENANTUSERS {
        uuidtext id PK
        stringtext name
        string slugemail UK
        stringtext planpassword_hash
        timestamptztext first_name
        text last_name
        text phone
        text date_of_birth
        text kyc_status
        text role
        text created_at
    timestamptz deleted_at
    }

    USERRECIPIENTS {
        uuidtext id PK
        uuidtext tenant_iduser_id FK
        stringtext emailname
        UKtext stringcountry
        full_nametext stringcurrency
        password_hashtext stringbank_account
        roletext booleanbank_name
        is_verified
        timestamptz last_login_at
        timestamptztext created_at
    timestamptz deleted_at
    }

    {{ENTITY_1}}MERCHANTS {
        uuidtext id PK
        uuidtext tenant_iduser_id FK
        uuidtext created_bybusiness_name
        FKtext stringorg_number {{field_1}}UK
        stringtext {{field_2}}address
        stringtext bank_account
        real fee_rate
        text status
        int version
        timestamptztext created_at
    timestamptz updated_at
        timestamptz deleted_at
    }

    {{ENTITY_2}}TRANSACTIONS {
        uuidtext id PK
        uuidtext {{entity_1_id}}user_id FK
        stringtext 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 {{field_1}
        integer id PK
        text from_currency
        text to_currency
        real rate
        text updated_at
    }

    decimalBANK_ACCOUNTS {{amount_field}
        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
    }

    timestamptzCARDS {
        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 {
        uuidtext id PK
        uuidtext tenant_idtimestamp
        text user_id FK
        uuidtext actor_idaction
        text resource_type
        text resource_id
        text details
        text ip_address
        text user_agent
    }

    AML_ALERTS {
        text id PK
        text user_id FK
        stringtext entity_typealert_type
        uuidtext entity_idseverity
        stringtext actiontransaction_id jsonbFK
        old_valuestext jsonbdetails
        new_valuestext stringstatus
        ip_addresstext timestamptzreviewed_by
        text reviewed_at
        text created_at
    }

    TENANTSTR_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{ USERRECIPIENTS : "has"
    TENANTUSERS ||--o{ {{ENTITY_1}}TRANSACTIONS : "owns"initiates"
    USERUSERS ||--o{ {{ENTITY_1}}BANK_ACCOUNTS : "creates"links"
    {{ENTITY_1}}USERS ||--o{ {{ENTITY_2}}CARDS : "contains"holds"
    USERUSERS ||--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	user_profilesbank_accounts, order_itemsaudit_log
Columns	snake_case	created_at, tenant_iduser_id
Primary keys	Always id (UUID)TEXT in Drop — custom prefixed IDs)	id UUIDTEXT PRIMARY KEY
Foreign keys	{referenced_table_singular}_id	user_id, tenant_idrecipient_id
Indexes	idx_{table}_{column(s)}column}	idx_users_emailidx_transactions_user
Unique indexes	uq_{table}_{column(s)}column} (convention; SQLite uses UNIQUE inline)	uq_users_tenant_emailuq_users_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)

Present ISO-8601

Column	Type	Nullable	DefaultIn	Description
id	All tables	Custom-prefixed unique identifier
UUIDuser_id	NOMost tables	FK to gen_random_uuid()users(id)	Surrogate— primaryuser keyownership
created_at	TIMESTAMPTZMost tables	NO	creation timestamp (SQLite default: NOW(datetime('now')	Immutable — set on insert
updated_at	TIMESTAMPTZ	NO	NOW()	Auto-updated via trigger
deleted_at	TIMESTAMPTZ	YES	NULL	Soft delete (NULL = active)
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 Identity & AccessUser Domain

Table: tenantsusers

Purpose: Top-levelPrimary multi-tenancyuser isolationaccounts. unit.Authentication Everyidentity. resourceKYC belongsstatus togate afor tenant.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.

Registration

Column	Type	Nullable	Default	Constraints	Description
id	UUIDTEXT	NO	gen_random_uuid()—	PKPRIMARY KEY	TenantFormat: identifierusr_{hex16}
nameemail	VARCHAR(255)TEXT	NO	—	UNIQUE NOT NULL	Normalized; used for legacy login
password_hash	TEXT	NO	—	NOT NULL	Displaybcrypt namewith 12 rounds (utils-server.ts:8-11)
slugfirst_name	VARCHAR(100)TEXT	NO	—	UNIQUE, NOT NULL	URL-safeExtracted identifierfrom BankID id_token.name split
planlast_name	tenant_planTEXT	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	'free'pending'	NOTCHECK NULLIN ('pending','approved','rejected')	SubscriptionUpdated planby Sumsub webhook
settingsrole	JSONBTEXT	NO	'{}'::jsonbuser'	CHECK IN ('user','merchant')	TenantElevated configurationto merchant on POST /api/merchants/register
created_at	TIMESTAMPTZTEXT	NO	NOW(datetime('now')	—
deleted_at	TIMESTAMPTZ	YES	NULL		timestamp

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

-- TablePostgreSQL equivalent
CREATE TABLE tenantsusers (
    id UUIDTEXT PRIMARY KEYKEY,
    DEFAULTemail gen_random_uuid(),TEXT name VARCHAR(255)UNIQUE NOT NULL,
    slugpassword_hash VARCHAR(100)TEXT NOT NULL,
    planfirst_name tenant_planTEXT NOT NULL,
    last_name TEXT NOT NULL,
    phone TEXT,
    date_of_birth TEXT,
    kyc_status TEXT NOT NULL DEFAULT 'free'pending' CHECK(kyc_status IN ('pending','approved','rejected')),
    settingsrole JSONBTEXT NOT NULL DEFAULT '{}user' CHECK(role IN ('user','merchant')),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    deleted_at TIMESTAMPTZCURRENT_TIMESTAMP
);

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

---

Table: userssessions

Purpose: SystemJWT users.session Authenticationtracking identity.for revocation support. Session records are validated on every authenticated request.

TOTP

Column	Type	Nullable	Default	Constraints	Description
id	UUIDTEXT	NO	gen_random_uuid()—	PKPRIMARY KEY	Format: ses_{hex16}
tenant_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_secretuser_id	TEXT	YESNO	—	FK → users(id)	Session owner
token_hash	NULLTEXT	NO	Encrypted—	NOT secretNULL	SHA-256 of the JWT string (auth.ts:59)
created_at	TIMESTAMPTZTEXT	NO	NOW(datetime('now')	—	Session start
updated_atexpires_at	TIMESTAMPTZTEXT	NO	—	NOT NULL	Web: +24h; Mobile: +7d
revoked	INTEGER	NO	NOW()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
deleted_atcurrency	TIMESTAMPTZTEXT	YESNO	NULL'NOK'	—	Display currency preference
versionlanguage	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 TYPE user_role AS ENUM ('owner', 'admin', 'member', 'viewer', 'api');

CREATE TABLE userssettings (
    iduser_id UUIDTEXT PRIMARY KEY DEFAULTREFERENCES gen_random_uuid()users(id),
    tenant_idcurrency UUID NOT NULL REFERENCES tenants(id) ON DELETE RESTRICT,
    email VARCHAR(320) NOT NULL,
    password_hash VARCHAR(255),
    full_name VARCHAR(255) NOT NULL,
    role user_role NOT NULLTEXT DEFAULT 'member'NOK',
    is_verifiedlanguage BOOLEAN NOT NULLTEXT DEFAULT FALSE,'nb',
    last_login_atpush_enabled TIMESTAMPTZ,
    mfa_enabled BOOLEAN NOT NULLINTEGER DEFAULT FALSE,1,
    mfa_secretemail_enabled TEXT,
    created_at TIMESTAMPTZ NOT NULLINTEGER DEFAULT NOW(),1,
    updated_at TIMESTAMPTZ NOT NULLTEXT DEFAULT NOW((datetime('now'),
    deleted_at TIMESTAMPTZ,
    version INTEGER NOT NULL DEFAULT 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 {{DOMAIN_NAME}}Financial Domain

Table: {{table_name}}bank_accounts

Purpose: {{TABLE_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	UUIDTEXT	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	gen_random_uuid(datetime('now')	PK—	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')	—
tenant_idpin_hash	UUIDTEXT	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.

Which ,weekly,

Column	Type	Nullable	Default	Constraints	Description
id	TEXT	NO	—	FKPRIMARY → tenants(id)KEY
created_byuser_id	UUIDTEXT	NO	—	FK → users(id)
{{field_1}}card_id	{{TYPE}}TEXT	{{YES/NO}}YES	NULL	FK → {{DEFAULT}}cards(id)	{{CONSTRAINTS}}	{{DESCRIPTION}}card this limit applies to
{{field_2}}limit_type	{{TYPE}}TEXT	{{YES/NO}}NO	—	NOT NULL	{{DEFAULT}}daily	{{CONSTRAINTS}}	{{DESCRIPTION}}monthly, or transaction
statusamount	{{status_enum}}REAL	NO	'{{DEFAULT_STATUS}}'—	NOT NULL	Limit amount in NOK
currency	TEXT	YES	'NOK'	—
created_at	TIMESTAMPTZTEXT	NO	NOW(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
updated_attimestamp	TIMESTAMPTZTEXT	NO	NOW(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
deleted_atuser_id	TIMESTAMPTZTEXT	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	NULL'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
versionuser_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

---

5. Enums & Lookup Tables

5.1 Enum Types

CREATE TYPETABLE user_role AS ENUMconsents ('owner'
    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('admin'now')),
    'member',withdrawn_at 'viewer',TEXT,
    'api'ip_address TEXT
);

CREATE TYPEINDEX tenant_planidx_consents_user ASON 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}}')consents(user_id);

5.2 Lookup Tables

---

Table: {{lookup_table}}data_access_requests

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

identifier label

Column	Type	Nullable	Default	Constraints	Description
codeid	VARCHAR(50)TEXT PK	Machine-readableNO	—	PRIMARY KEY
labeluser_id	VARCHAR(255)TEXT	Human-readableNO	—	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.

description ordering selectable

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	DetailedNO	—	NOT NULL	Full complaint text (max 2000 chars)
sort_orderstatus	INTEGERTEXT	DisplayYES	'received'	CHECK IN ('received','investigating','resolved','escalated')	Processing status
is_activeresolution	BOOLEANTEXT	WhetherYES	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

6.1state: Views

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_{{entities}}active_users (planned)

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

CREATE-- VIEWPhase active_{{entities}}2 ASplanning SELECT— *users FROMtable {{table_name}}does WHEREnot have deleted_at ISyet
NULL;-- Requires schema migration to add soft-delete support before creating this view

6.2 Materialized Views

Materialized View: {{entity}}_summarymerchant_daily_revenue (Phase 3 consideration)

Purpose: Pre-aggregatedaggregate summarydaily merchant revenue for dashboard queries Refreshed: Every {{INTERVAL}} via scheduled job Staleness acceptable: Up to {{MAX_STALENESS}}1 hour

CREATE-- MATERIALIZEDTo VIEWbe {{entity}}_summarydefined ASin SELECTPhase tenant_id,3 DATE_TRUNC('day',when created_at)merchant ASdashboard date,requires COUNT(*)faster ASaggregation
total,-- COUNT(*)Current: FILTERinline (WHEREquery statuson =transactions 'active')table AS active_count
    FROM {{table_name}}
    WHERE deleted_at IS NULLwith GROUP BY tenant_id, DATE_TRUNC('day', created_at);

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

-- Refresh command (run by scheduler):
-- REFRESH MATERIALIZED VIEW CONCURRENTLY {{entity}}_summary;

---

7. Stored Procedures & Functions

Current

updated_at_trigger()

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

Purpose:Exception — SQLite WAL init (db.ts:37-38): Auto-update updated_at column on any row update

CREATEPRAGMA ORjournal_mode=WAL;
REPLACEPRAGMA 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();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: {{MIGRATION_TOOL}}Flyway (e.g.,or Flyway,custom Liquibase,SQL Prismamigration Migrate)files 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:migration in production:

• Can this run on a live table without locking? (Use CONCURRENTLY for index creation)
• Does this add a columnNOT withNULL a default?column? (AvoidAdd volatileas defaultsnullable onfirst, largebackfill, tables)then add constraint)
• Does this remove a column? (Ensure app code is already deployed without references first)
• Does this rename?rename a column or table? (Use multi-step:expansion-contraction add new, backfill, update app, remove old)pattern)
• What'sHas thethis estimatedbeen locktested time?against (Testproduction-sized data on staging data of production size)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): DropDROP old_column

---

9. Seed Data Requirements

9.1 Required Seed Data (production)

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

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

9.2 Development Seed Data

Condition: NODE_ENV !== 'production' OR SEED_DEMO=true Script: db/seeds/development.sqldb.ts:530 Volume: {{N}} tenants, {{N}} users per tenant, {{N}} sample records Command: (npm run db:seedseedData() orfunction)

makeDemo seed-devdata (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 PartitioningIndexing Strategy (Current)

tenant

Table	Partition StrategyIndex	Partition KeyColumns	Partition SizePurpose
audit_logsrecipients	Range (time)idx_recipients_user	created_atuser_id	MonthlyList recipients by user
{{events_table}}merchants	Range (time)idx_merchants_org	created_atorg_number	WeeklyUnique org number lookup
{{large_table}}transactions	idx_transactions_user	user_id	List (tenant)user's transactions
transactions	tenant_ididx_transactions_merchant	Permerchant_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

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
Tenant-scopedUser's transactions list	< 50ms	Composite index (tenant_id, created_at)idx_transactions_user
Full-textSession searchvalidation (every request)	< 5ms	idx_sessions_token
Exchange rate lookup	< 5ms	Full table scan (6 rows — acceptable)
Merchant dashboard aggregation	< 200ms	GINidx_transactions_merchant index+ onGROUP search_vectorBY
AggregationAudit (dashboard)log time-range query	< 500ms	Materialized view
Cross-tenant report	< 30s	Data warehouseidx_audit_log_timestamp

10.3 Connection Pooling

Application
Phase connections1: Direct connection from App Runner → PostgreSQL (single instance, low traffic expected)
Phase 2+: Evaluate PgBouncer (transaction mode) →when PostgreSQLconcurrent Poolconnections size:exceed min={{MIN_POOL}}, max={{MAX_POOL}} per application instance
Max DB connections: {{MAX_DB_CONNECTIONS}} (= pool_size × instances + 10 reserve)

20

---

11. Backup & Recovery Procedures

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

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

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

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

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