Skip to main content

Migration Strategy

Migration Strategy: SQLite to PostgreSQL

STATUS: COMPLETED (2026-03-03) This document describes the completed migration from the old dual-driver architecture to PostgreSQL-only. The migration is done. Current architecture: PostgreSQL 16 (all environments), Drizzle ORM. See ADR-014 for the authoritative current state.

Version: 1.0 Date: 2026-02-21 Status: Completed — migration done per ADR-014 Owner: Database Architect

Overview

HISTORICAL NOTE: The dual-driver architecture and better-sqlite3 dependency described in this document have been removed. The codebase now uses Drizzle ORM with PostgreSQL 16 exclusively. db.ts and USE_PG no longer exist. See ADR-014.

This document captures the migration plan that was executed when transitioning from SQLite (development) + PostgreSQL (production) to PostgreSQL 16 in all environments. It is preserved as a historical record.

Migration Execution Flow

flowchart TD
    A[Phase 1: Prepare] --> B[Phase 2: Schema Migration]
    B --> C[Phase 3: Data Migration]
    C --> D[Phase 4: Validation]
    D --> E{All checks pass?}
    E -->|Yes| F[Phase 5: Cutover]
    E -->|No| G[Fix issues]
    G --> D
    F --> H[Phase 6: Post-migration]

    subgraph "Phase 1: Prepare"
        A1[Provision PostgreSQL instance]
        A2[Configure DATABASE_URL]
        A3[Create shadow database for testing]
        A4[Backup SQLite database file]
    end

    subgraph "Phase 2: Schema"
        B1[Run PostgreSQL schema DDL]
        B2[Create indexes]
        B3[Verify constraints]
    end

    subgraph "Phase 3: Data"
        C1[Export SQLite data as INSERT statements]
        C2[Transform data types]
        C3[Load into PostgreSQL]
        C4[Reset sequences]
    end

    subgraph "Phase 4: Validate"
        D1[Row count comparison]
        D2[Checksum validation]
        D3[Application smoke tests]
        D4[Run full test suite against PG]
    end

    subgraph "Phase 5: Cutover"
        F1[Set DATABASE_URL in production]
        F2[Deploy application]
        F3[Verify health endpoint]
    end

    subgraph "Phase 6: Post-migration"
        H1[Monitor error rates]
        H2[Monitor query performance]
        H3[Archive SQLite file]
    end

Data Type Mapping

The dual-driver layer already handles SQL syntax differences. The schema migration must map SQLite types to PostgreSQL equivalents:

SQLite Type PostgreSQL Type Tables Using It Notes
TEXT TEXT All tables Direct mapping, no change
TEXT PRIMARY KEY TEXT PRIMARY KEY All except exchange_rates Same behavior
INTEGER (boolean) BOOLEAN or INTEGER sessions.revoked, notifications.read, settings.push_enabled, settings.email_enabled, bank_accounts.is_primary, consents.granted, users.sanctions_cleared Keep as INTEGER for dual-driver compat, or convert to BOOLEAN in PG-only mode
INTEGER (currency) BIGINT transactions.amount, transactions.fee, transactions.send_amount, transactions.receive_amount, bank_accounts.balance, spending_limits.amount Use BIGINT for amounts in minor units to prevent overflow
INTEGER PRIMARY KEY AUTOINCREMENT SERIAL PRIMARY KEY exchange_rates.id Only auto-increment in schema
INTEGER (unix timestamp) INTEGER rate_limits.reset_at Unix epoch, no conversion needed
REAL DOUBLE PRECISION transactions.exchange_rate, merchants.fee_rate Direct mapping
TEXT DEFAULT (datetime('now')) TEXT DEFAULT CURRENT_TIMESTAMP All created_at, updated_at columns Handled by adaptSqlForPg() in db.ts

SQLite-specific SQL Adaptations (already implemented in db.ts:46-52)

SQLite SQL PostgreSQL Equivalent Handler
INSERT OR IGNORE INTO INSERT INTO ... ON CONFLICT DO NOTHING runIgnore() function
INSERT OR REPLACE INTO INSERT INTO ... ON CONFLICT (col) DO UPDATE SET runUpsert() function
datetime('now') CURRENT_TIMESTAMP adaptSqlForPg() regex
? placeholders $1, $2, ... positional convertPlaceholders()
randomblob(32) gen_random_bytes(32) Schema-level change (merchants.qr_hmac_key default)
hex() encode(..., 'hex') Schema-level change

PostgreSQL Schema DDL

The PostgreSQL schema must be created separately from the SQLite schema since CREATE TABLE IF NOT EXISTS syntax is shared but defaults and functions differ:

-- PostgreSQL schema for Drop
-- Run once when provisioning production database

CREATE TABLE IF NOT EXISTS users (
    id TEXT PRIMARY KEY,
    email TEXT UNIQUE NOT NULL,
    password_hash TEXT NOT NULL DEFAULT 'EIDONLY',
    auth_provider TEXT DEFAULT 'bankid',
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    phone TEXT,
    date_of_birth TEXT,
    kyc_status TEXT DEFAULT 'pending' CHECK(kyc_status IN ('pending','approved','rejected')),
    role TEXT DEFAULT 'user' CHECK(role IN ('user','merchant')),
    risk_level TEXT DEFAULT 'low' CHECK(risk_level IN ('low','medium','high')),
    pep_status TEXT DEFAULT 'not_checked' CHECK(pep_status IN ('not_checked','clear','match','pending_review')),
    sanctions_cleared INTEGER DEFAULT 0,
    kyc_method TEXT CHECK(kyc_method IN ('bankid','document','simplified')),
    kyc_verified_at TEXT,
    national_id_hash TEXT,
    deleted_at TEXT,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_users_national_id ON users(national_id_hash) WHERE national_id_hash IS NOT NULL;

CREATE TABLE IF NOT EXISTS 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 CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_recipients_user ON recipients(user_id);

CREATE TABLE IF NOT EXISTS 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 DOUBLE PRECISION DEFAULT 0.01,
    status TEXT DEFAULT 'active',
    qr_hmac_key TEXT NOT NULL DEFAULT encode(gen_random_bytes(32), 'hex'),
    created_at TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS 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 BIGINT NOT NULL,
    currency TEXT DEFAULT 'NOK',
    fee BIGINT DEFAULT 0,
    recipient_id TEXT REFERENCES recipients(id),
    merchant_id TEXT REFERENCES merchants(id),
    send_amount BIGINT,
    send_currency TEXT,
    receive_amount BIGINT,
    receive_currency TEXT,
    exchange_rate DOUBLE PRECISION,
    purpose_code TEXT,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
    completed_at TEXT,
    idempotency_key TEXT
);
CREATE UNIQUE INDEX IF NOT EXISTS idx_tx_idempotency ON transactions(idempotency_key) WHERE idempotency_key IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_transactions_user ON transactions(user_id);

CREATE TABLE IF NOT EXISTS exchange_rates (
    id SERIAL PRIMARY KEY,
    from_currency TEXT DEFAULT 'NOK',
    to_currency TEXT NOT NULL,
    rate DOUBLE PRECISION NOT NULL,
    updated_at TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS 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 BIGINT DEFAULT 0,
    balance_synced_at TEXT,
    currency TEXT DEFAULT 'NOK',
    is_primary INTEGER DEFAULT 0,
    connected_at TEXT DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_bank_accounts_user ON bank_accounts(user_id);

-- Remaining tables follow the same pattern...
-- See full DDL in migration script

Sequence/Auto-increment Migration

Only one table uses auto-increment: exchange_rates.

Aspect SQLite PostgreSQL Migration Step
Type INTEGER PRIMARY KEY AUTOINCREMENT SERIAL PRIMARY KEY Schema DDL change
Sequence reset N/A (built into rowid) SELECT setval('exchange_rates_id_seq', (SELECT MAX(id) FROM exchange_rates)) After data load
Gap behavior Gaps allowed Gaps allowed No difference

JSON Handling

Aspect SQLite PostgreSQL Impact
Storage type TEXT (plain string) TEXT (could use JSONB) No change needed for compatibility
JSON columns audit_log.details, aml_alerts.details, str_reports.details, screening_results.match_details Same columns, stored as TEXT Keep as TEXT for dual-driver compatibility
Querying JSON Not used (JSON is stored, not queried in SQL) Could use ->>/@> operators Future optimization: add JSONB indexes for audit log queries
Validation None (application layer) Could add CHECK with jsonb cast Future enhancement

Decision: Keep JSON columns as TEXT for now. Converting to JSONB is a future optimization that would break dual-driver compatibility.

Date/Time Handling

Aspect SQLite PostgreSQL Migration
Default value datetime('now') CURRENT_TIMESTAMP Handled by adaptSqlForPg()
Storage format ISO 8601 TEXT ISO 8601 TEXT (not TIMESTAMP type) No conversion needed
Timezone UTC (application convention) UTC (application convention) Consistent
Date arithmetic datetime('now', '-3 days') CURRENT_TIMESTAMP - INTERVAL '3 days' Only used in seed data, not production queries

Note: All timestamps are stored as TEXT in ISO 8601 format (YYYY-MM-DDTHH:MM:SS) in both databases. This is intentional for dual-driver compatibility. A future PostgreSQL-only optimization could convert to TIMESTAMPTZ.

Migration Checklist

Pre-Migration

  • Provision PostgreSQL instance (AWS RDS or equivalent)
  • Configure connection pooling (built-in pg.Pool, max connections TBD)
  • Set DATABASE_URL environment variable
  • Create shadow database for testing
  • Backup current SQLite file: cp data/drop.db data/drop.db.backup.$(date +%s)
  • Run full test suite against SQLite (baseline)
  • Review all raw SQL queries for SQLite-specific syntax (should be none -- all go through db.ts)

Schema Migration

  • Run PostgreSQL DDL script to create all 19 tables
  • Create all indexes (11 indexes defined in db.ts schema)
  • Verify all CHECK constraints are active
  • Verify all foreign key constraints are active
  • Test initDb() function with DATABASE_URL set

Data Migration

  • Export SQLite data using sqlite3 drop.db .dump or custom export script
  • Transform INTEGER PRIMARY KEY AUTOINCREMENT to SERIAL
  • Transform randomblob() defaults to gen_random_bytes()
  • Load data into PostgreSQL
  • Reset exchange_rates_id_seq sequence
  • Verify row counts match per table

Validation

  • Row count comparison (all 19 tables)
  • Spot-check 10 records per table for data integrity
  • Run application smoke tests:
    • Login (BankID flow)
    • View dashboard (bank accounts, balance)
    • List transactions
    • Create remittance
    • Create QR payment
    • View notifications
    • Update settings
  • Run full test suite with DATABASE_URL set
  • Verify GET /api/health returns db: "connected" with acceptable latency

Cutover

  • Set DATABASE_URL in production environment
  • Deploy application
  • Verify health endpoint
  • Monitor error rates for 1 hour
  • Monitor query latency for 1 hour

Post-Migration

  • Archive SQLite file
  • Update documentation to reflect PostgreSQL as primary
  • Consider PostgreSQL-specific optimizations (JSONB, TIMESTAMPTZ, partial indexes)
  • Configure automated backups (pg_dump cron or RDS snapshots)

Rollback Procedure

flowchart TD
    A[Issue detected in PostgreSQL] --> B{Is it data corruption?}
    B -->|Yes| C[Stop application immediately]
    B -->|No| D{Is it a query/performance issue?}
    D -->|Yes| E[Fix query and redeploy]
    D -->|No| F[Investigate further]

    C --> G[Remove DATABASE_URL env var]
    G --> H[Redeploy application]
    H --> I[Application falls back to SQLite]
    I --> J[Investigate PostgreSQL issue offline]
    J --> K[Fix and retry migration]

NOTE:Rollback is simple: TheRemove SQLiteor fallbackunset describedthe inDATABASE_URL thisenvironment rollback procedure has been REMOVED per ADR-014.variable. The application no longerimmediately falls back to SQLiteSQLite. when DATABASE_URLThis is unsetthe —primary itadvantage failsof tothe startdual-driver (required env var). Production rollback is via RDS point-in-time restore or App Runner revision revert.architecture.

Rollback Scenario Action Downtime
Schema issue in PostgreSQL Unset DATABASE_URL, redeploy ~2 minutes (deploy time)
Data integrity issue Unset DATABASE_URL, redeploy with SQLite backup ~2 minutes
Performance regression Unset DATABASE_URL, optimize PG offline ~2 minutes
Partial migration failure Drop PostgreSQL schema, fix script, retry No production impact (still on SQLite)

Key constraint: Rollback only works if no new data has been written to PostgreSQL that does not exist in SQLite. In practice, this means the migration window should be short and the SQLite database should be read-only during cutover.

Zero-Downtime Migration Using Dual-Driver

The dual-driver architecture enables a phased migration with zero downtime:

sequenceDiagram
    participant App as Application
    participant SQLite as SQLite (current)
    participant PG as PostgreSQL (new)

    Note over App,SQLite: Phase A: Normal operation (SQLite)
    App->>SQLite: All reads/writes

    Note over App,PG: Phase B: Provision and schema
    App->>SQLite: All reads/writes (unchanged)
    Note right of PG: Create schema, indexes

    Note over App,PG: Phase C: Data migration
    App->>SQLite: All reads/writes (unchanged)
    Note right of PG: Bulk load from SQLite export

    Note over App,PG: Phase D: Final sync + cutover
    App->>SQLite: Brief read-only mode
    Note right of PG: Delta sync (new records since bulk load)
    Note over App: Set DATABASE_URL
    App->>PG: All reads/writes

    Note over App,PG: Phase E: Production on PostgreSQL
    App->>PG: All reads/writes
    Note left of SQLite: Archived as backup

Total downtime: Only during Phase D final sync + deploy, estimated at 2-5 minutes for the current data volume.

Testing Approach

Shadow Database Testing

Before production migration, run the full application against a shadow PostgreSQL database:

  1. Provision shadow PG: Same version and configuration as production target
  2. Run schema creation: Execute PostgreSQL DDL
  3. Load production-like data: Export SQLite demo data, transform, load
  4. Run test suite: DATABASE_URL=<shadow> npm test
  5. Run integration tests: Full API flow tests against shadow
  6. Load testing: Verify query performance under expected load

Data Integrity Checks

-- Row count comparison (run against both databases)
SELECT 'users' as tbl, COUNT(*) as cnt FROM users
UNION ALL SELECT 'transactions', COUNT(*) FROM transactions
UNION ALL SELECT 'bank_accounts', COUNT(*) FROM bank_accounts
UNION ALL SELECT 'recipients', COUNT(*) FROM recipients
UNION ALL SELECT 'merchants', COUNT(*) FROM merchants
UNION ALL SELECT 'sessions', COUNT(*) FROM sessions
UNION ALL SELECT 'notifications', COUNT(*) FROM notifications
UNION ALL SELECT 'settings', COUNT(*) FROM settings
UNION ALL SELECT 'exchange_rates', COUNT(*) FROM exchange_rates
UNION ALL SELECT 'cards', COUNT(*) FROM cards
UNION ALL SELECT 'spending_limits', COUNT(*) FROM spending_limits
UNION ALL SELECT 'rate_limits', COUNT(*) FROM rate_limits
UNION ALL SELECT 'audit_log', COUNT(*) FROM audit_log
UNION ALL SELECT 'aml_alerts', COUNT(*) FROM aml_alerts
UNION ALL SELECT 'str_reports', COUNT(*) FROM str_reports
UNION ALL SELECT 'screening_results', COUNT(*) FROM screening_results
UNION ALL SELECT 'consents', COUNT(*) FROM consents
UNION ALL SELECT 'data_access_requests', COUNT(*) FROM data_access_requests
UNION ALL SELECT 'complaints', COUNT(*) FROM complaints;

Cross-References

Back to top