Indexing Strategy
Indexing Strategy
Version: 1.0
Date: 2026-02-21
Status: Approved
Owner: Database Architect
Overview
Drop's indexing strategy is designed around actual user flow query patterns. Every index exists because a specific query needs it. No speculative indexes.
Current index count: 16 indexes across 19 original tables (defined in db.ts schema). 8 additional indexes are recommended for the 6 new operational tables (FR-073 through FR-077).
Query Patterns by User Flow
Login Flow (BankID OIDC)
sequenceDiagram
participant U as User
participant API as API
participant DB as Database
U->>API: BankID callback (code, state)
API->>DB: Q1: Find user by national_id_hash
DB-->>API: User or NULL
alt New user
API->>DB: Q2: INSERT users
API->>DB: Q3: INSERT settings (defaults)
end
API->>DB: Q4: INSERT sessions
API-->>U: JWT cookie + redirect
| Query ID |
SQL Pattern |
Index Required |
Current Coverage |
| Q1 |
SELECT * FROM users WHERE national_id_hash = ? |
idx_users_national_id (partial: WHERE NOT NULL) |
Covered |
| Q2 |
INSERT INTO users (...) |
None (PK insert) |
N/A |
| Q3 |
INSERT INTO settings (...) |
None (PK insert) |
N/A |
| Q4 |
INSERT INTO sessions (...) |
None (PK insert) |
N/A |
Authentication Middleware (every authenticated request)
| Query ID |
SQL Pattern |
Index Required |
Current Coverage |
| Q5 |
SELECT * FROM sessions WHERE token_hash = ? AND revoked = 0 AND expires_at > ? |
idx_sessions_token |
Covered |
| Q6 |
SELECT * FROM users WHERE id = ? |
PRIMARY KEY |
Covered |
Dashboard View
sequenceDiagram
participant U as User
participant API as API
participant DB as Database
U->>API: GET /auth/me
API->>DB: Q5: Verify session (token_hash)
API->>DB: Q6: Get user by PK
API->>DB: Q7: Get bank accounts for user
DB-->>API: Bank accounts with cached balances
API-->>U: User profile + total balance
| Query ID |
SQL Pattern |
Index Required |
Current Coverage |
| Q7 |
SELECT * FROM bank_accounts WHERE user_id = ? |
idx_bank_accounts_user |
Covered |
Transaction History
| Query ID |
SQL Pattern |
Index Required |
Current Coverage |
| Q8 |
SELECT * FROM transactions WHERE user_id = ? [AND type = ?] [AND status = ?] ORDER BY created_at DESC LIMIT ? OFFSET ? |
idx_transactions_user |
Covered (user_id) |
| Q9 |
SELECT COUNT(*) FROM transactions WHERE user_id = ? |
idx_transactions_user |
Covered |
Note: The type and status filters are applied after the user_id index lookup. At current scale (< 10K transactions per user), this is efficient. A composite index (user_id, created_at DESC) would optimize the ORDER BY for users with many transactions.
Create Remittance
| Query ID |
SQL Pattern |
Index Required |
Current Coverage |
| Q10 |
SELECT * FROM recipients WHERE id = ? AND user_id = ? |
idx_recipients_user + PK |
Covered |
| Q11 |
SELECT * FROM exchange_rates WHERE to_currency = ? |
Sequential scan (6 rows) |
Acceptable (tiny table) |
| Q12 |
SELECT * FROM bank_accounts WHERE user_id = ? AND is_primary = 1 |
idx_bank_accounts_user |
Covered |
| Q13 |
UPDATE bank_accounts SET balance = balance - ? WHERE id = ? AND balance >= ? |
PK |
Covered |
| Q14 |
INSERT INTO transactions (...) |
None (PK insert) |
N/A |
Create QR Payment
| Query ID |
SQL Pattern |
Index Required |
Current Coverage |
| Q15 |
SELECT * FROM merchants WHERE id = ? |
PK |
Covered |
| Q12 |
(Same as remittance -- primary bank account) |
idx_bank_accounts_user |
Covered |
| Q13 |
(Same as remittance -- balance debit) |
PK |
Covered |
| Q14 |
(Same as remittance -- insert transaction) |
N/A |
N/A |
Notifications List
| Query ID |
SQL Pattern |
Index Required |
Current Coverage |
| Q16 |
SELECT * FROM notifications WHERE user_id = ? ORDER BY created_at DESC |
idx_notifications_user |
Covered |
| Q17 |
UPDATE notifications SET read = 1 WHERE id IN (?, ?, ...) AND user_id = ? |
PK + idx_notifications_user |
Covered |
Settings View/Update
| Query ID |
SQL Pattern |
Index Required |
Current Coverage |
| Q18 |
SELECT * FROM settings WHERE user_id = ? |
PK (user_id IS the PK) |
Covered |
| Q19 |
UPDATE settings SET ... WHERE user_id = ? |
PK |
Covered |
Merchant Dashboard
| Query ID |
SQL Pattern |
Index Required |
Current Coverage |
| Q20 |
SELECT * FROM merchants WHERE user_id = ? |
Sequential scan (1 merchant per user) |
Acceptable |
| Q21 |
SELECT * FROM transactions WHERE merchant_id = ? AND created_at >= ? |
idx_transactions_merchant |
Partially covered (no composite with created_at) |
Recipient Management
| Query ID |
SQL Pattern |
Index Required |
Current Coverage |
| Q22 |
SELECT * FROM recipients WHERE user_id = ? LIMIT ? OFFSET ? |
idx_recipients_user |
Covered |
| Q23 |
SELECT * FROM recipients WHERE id = ? AND user_id = ? |
PK + idx_recipients_user |
Covered |
| Q24 |
DELETE FROM recipients WHERE id = ? AND user_id = ? |
PK |
Covered |
Compliance Queries (Admin/Internal)
| Query ID |
SQL Pattern |
Index Required |
Current Coverage |
| Q25 |
SELECT * FROM audit_log WHERE user_id = ? ORDER BY timestamp DESC |
idx_audit_log_user |
Covered |
| Q26 |
SELECT * FROM audit_log WHERE action = ? AND timestamp BETWEEN ? AND ? |
idx_audit_log_action + idx_audit_log_timestamp |
Partially (separate indexes, no composite) |
| Q27 |
SELECT * FROM aml_alerts WHERE user_id = ? AND status IN ('open','investigating') |
idx_aml_alerts_user |
Covered |
| Q28 |
SELECT * FROM aml_alerts WHERE status = 'open' ORDER BY created_at |
idx_aml_alerts_status (proposed) |
Not covered (needs new index) |
| Q29 |
SELECT * FROM complaints WHERE user_id = ? ORDER BY created_at DESC |
idx_complaints_user |
Covered |
| Q30 |
SELECT * FROM complaints WHERE status IN ('received','investigating') |
idx_complaints_status (proposed) |
Not covered (needs new index) |
Index Inventory
Current Indexes (defined in db.ts)
| Index Name |
Table |
Column(s) |
Type |
Rationale |
idx_users_national_id |
users |
national_id_hash |
B-tree, partial (WHERE NOT NULL) |
BankID login deduplication -- find user by hashed national ID |
idx_recipients_user |
recipients |
user_id |
B-tree |
List recipients per user, verify ownership |
idx_transactions_user |
transactions |
user_id |
B-tree |
Transaction history per user (most frequent query) |
idx_transactions_merchant |
transactions |
merchant_id |
B-tree |
Merchant dashboard -- transactions for merchant (documented in DATABASE-SCHEMA.md) |
idx_tx_idempotency |
transactions |
idempotency_key |
B-tree, unique, partial (WHERE NOT NULL) |
Prevent duplicate transaction submission |
idx_bank_accounts_user |
bank_accounts |
user_id |
B-tree |
Dashboard balance lookup, transaction source |
idx_sessions_user |
sessions |
user_id |
B-tree |
Revoke all sessions on logout |
idx_sessions_token |
sessions |
token_hash |
B-tree |
Auth middleware -- validate session on every request |
idx_notifications_user |
notifications |
user_id |
B-tree |
Notifications list per user (documented in DATABASE-SCHEMA.md) |
idx_audit_log_user |
audit_log |
user_id |
B-tree |
User investigation, DSAR compliance |
idx_audit_log_action |
audit_log |
action |
B-tree |
Event type filtering for monitoring |
idx_audit_log_timestamp |
audit_log |
timestamp |
B-tree |
Time-range queries for compliance reporting (documented in DATABASE-SCHEMA.md) |
idx_aml_alerts_user |
aml_alerts |
user_id |
B-tree |
Per-user AML alert lookup |
idx_aml_alerts_status |
aml_alerts |
status |
B-tree |
Open alerts dashboard (documented in DATABASE-SCHEMA.md) |
idx_complaints_user |
complaints |
user_id |
B-tree |
Per-user complaint history |
idx_screening_user |
screening_results |
user_id |
B-tree |
Per-user screening history |
Indexes from DATABASE-SCHEMA.md (not in db.ts code)
The DATABASE-SCHEMA.md documentation lists additional indexes that may not be in the current db.ts SQLITE_SCHEMA string:
| Index Name |
Table |
Column(s) |
Status |
idx_merchants_org |
merchants |
org_number |
Documented but covered by UNIQUE constraint |
idx_cards_user |
cards |
user_id |
Documented, may not be in db.ts |
idx_spending_limits_user |
spending_limits |
user_id |
Documented, may not be in db.ts |
idx_spending_limits_card |
spending_limits |
card_id |
Documented, may not be in db.ts |
idx_consents_user |
consents |
user_id |
Documented, may not be in db.ts |
idx_data_requests_user |
data_access_requests |
user_id |
Documented, may not be in db.ts |
idx_complaints_status |
complaints |
status |
Documented, may not be in db.ts |
Recommendation: Reconcile DATABASE-SCHEMA.md with actual db.ts code. Add missing indexes to the schema if the queries justify them.
Recommended Additional Indexes
Based on query pattern analysis, the following indexes should be added:
| Proposed Index |
Table |
Column(s) |
Justification |
idx_transactions_user_created |
transactions |
(user_id, created_at DESC) |
Optimizes paginated transaction history (Q8) -- avoids sort after index lookup |
idx_complaints_status |
complaints |
status |
Admin dashboard query for open complaints (Q30) |
idx_consents_user |
consents |
user_id |
DSAR export needs all consents for user |
idx_data_requests_user |
data_access_requests |
user_id |
DSAR tracking per user |
idx_audit_log_resource |
audit_log |
(resource_type, resource_id) |
Resource-specific audit trail lookup |
New Table Indexes (FR-073 through FR-077)
Indexes for the 6 operational tables added by FR-073, FR-075, FR-076, and FR-077:
reconciliation_reports (FR-073)
Proposed Index |
Table |
Column(s) |
Type |
Justification |
|---|
idx_reconciliation_reports_date |
reconciliation_reports |
report_date |
B-tree |
Admin dashboard queries by reconciliation date — the primary lookup pattern (e.g., "show me yesterday's report") |
idx_reconciliation_reports_status |
reconciliation_reports |
status |
B-tree |
Filter for discrepancies_found or pending reports — ops team monitors unresolved reports |
reconciliation_discrepancies (FR-073)
Proposed Index |
Table |
Column(s) |
Type |
Justification |
|---|
idx_reconciliation_discrepancies_report |
reconciliation_discrepancies |
report_id |
B-tree |
Fetch all discrepancies for a given reconciliation report — primary join pattern |
idx_reconciliation_discrepancies_resolution |
reconciliation_discrepancies |
resolution_status |
B-tree |
Ops dashboard filter for open and investigating discrepancies requiring action |
circuit_breaker_state (FR-075)
Proposed Index |
Table |
Column(s) |
Type |
Justification |
|---|
idx_circuit_breaker_dependency |
circuit_breaker_state |
dependency_name |
B-tree, UNIQUE |
Enforces uniqueness per dependency and enables O(1) lookup by name. This is the only query pattern: SELECT * FROM circuit_breaker_state WHERE dependency_name = ? on every outbound call |
Note: The UNIQUE constraint on dependency_name creates this index implicitly in PostgreSQL. Explicit creation is not required.
webhook_events (FR-076)
Proposed Index |
Table |
Column(s) |
Type |
Justification |
|---|
idx_webhook_events_webhook_id |
webhook_events |
webhook_id |
B-tree, UNIQUE |
Deduplication check on every received webhook — must be O(1). Unique constraint creates this implicitly |
idx_webhook_events_processing_status |
webhook_events |
processing_status |
B-tree |
Background worker queries for failed and received events eligible for retry or async processing |
idx_webhook_events_transaction |
webhook_events |
transaction_id |
B-tree, partial (WHERE NOT NULL) |
Lookup all webhooks for a given transaction — used during dispute investigation |
webhook_dlq (FR-076)
Proposed Index |
Table |
Column(s) |
Type |
Justification |
|---|
idx_webhook_dlq_resolution |
webhook_dlq |
resolution |
B-tree |
Ops dashboard filter for pending DLQ entries requiring manual review — the primary query pattern |
disputes (FR-077)
Proposed Index |
Table |
Column(s) |
Type |
Justification |
|---|
idx_disputes_transaction |
disputes |
transaction_id |
B-tree |
Check for existing dispute on a transaction before showing "Rapporter uautorisert transaksjon" button |
idx_disputes_user |
disputes |
user_id |
B-tree |
User dispute history lookup — compliance officer review of prior disputes during investigation |
idx_disputes_status |
disputes |
status |
B-tree |
Compliance dashboard filter for submitted, acknowledged, investigating — open cases |
idx_disputes_reference |
disputes |
dispute_reference |
B-tree, UNIQUE |
Operator lookup by reference (user provides reference when contacting support). Unique constraint creates this implicitly |
Partial Index Opportunities (PostgreSQL)
These are PostgreSQL-specific optimizations to add after migration:
| Proposed Index |
Table |
Column(s) |
Condition |
Justification |
idx_sessions_active |
sessions |
user_id |
WHERE revoked = 0 |
Auth middleware only queries active sessions |
idx_aml_alerts_open |
aml_alerts |
created_at |
WHERE status IN ('open','investigating') |
Dashboard shows only open alerts |
idx_notifications_unread |
notifications |
user_id |
WHERE read = 0 |
Badge count for unread notifications |
idx_users_active |
users |
email |
WHERE deleted_at IS NULL |
Login only checks non-deleted users |
Connection Pooling Configuration
SQLite (Development)
SQLite uses a single connection with WAL mode:
| Setting |
Value |
Source |
| Connection mode |
Single instance (lazy init) |
db.ts:29-38 |
| Journal mode |
WAL |
db.ts:35 |
| Foreign keys |
ON |
db.ts:36 |
| Concurrency |
Single writer, multiple readers (WAL) |
SQLite default |
PostgreSQL (Production)
PostgreSQL uses pg.Pool for connection pooling:
| Setting |
Value |
Source |
Notes |
| Pool library |
pg.Pool |
db.ts:16-21 |
Node-postgres built-in pool |
| Connection string |
DATABASE_URL env var |
db.ts:18 |
Standard PostgreSQL URL format |
| Max connections |
Default (10) |
pg.Pool default |
Adjust based on App Runner instance count |
| Idle timeout |
10,000ms |
pg.Pool default |
Close idle connections after 10s |
| Connection timeout |
0 (no timeout) |
pg.Pool default |
Wait indefinitely for connection |
Recommended Production Pool Configuration
// Recommended pg.Pool configuration for production
const pool = new pg.Pool({
connectionString: process.env.DATABASE_URL,
max: 20, // Max connections per instance
idleTimeoutMillis: 30000, // Close idle after 30s
connectionTimeoutMillis: 5000, // Fail if no connection in 5s
ssl: { rejectUnauthorized: true } // Require SSL for RDS
});
| Parameter |
Recommended Value |
Rationale |
max |
20 |
Balance between connection availability and RDS connection limits. With 2-3 App Runner instances, total connections = 40-60 (well under RDS default 100). |
idleTimeoutMillis |
30,000 |
Close idle connections to free RDS slots, but keep them long enough to avoid reconnection overhead for bursty traffic. |
connectionTimeoutMillis |
5,000 |
Fail fast on connection issues rather than hanging. API should return 503 to client. |
ssl |
{ rejectUnauthorized: true } |
Encrypt connections to RDS. Required for compliance. |
PgBouncer Consideration
At current projected scale (3,000 users, ~100 concurrent connections), direct pg.Pool is sufficient. PgBouncer should be evaluated when:
- Connection count exceeds RDS limits
- Multiple services need to share the same database
- Transaction-mode pooling would reduce connection overhead
EXPLAIN ANALYZE Examples
Transaction History Query (most common)
-- SQLite
EXPLAIN QUERY PLAN
SELECT * FROM transactions
WHERE user_id = 'usr_demo1'
ORDER BY created_at DESC
LIMIT 20 OFFSET 0;
-- Expected: SEARCH TABLE transactions USING INDEX idx_transactions_user (user_id=?)
-- PostgreSQL (after migration)
EXPLAIN ANALYZE
SELECT * FROM transactions
WHERE user_id = 'usr_demo1'
ORDER BY created_at DESC
LIMIT 20 OFFSET 0;
-- Expected: Index Scan using idx_transactions_user on transactions
-- Sort Key: created_at DESC
-- Rows Removed by Index: 0 (all rows match user_id)
Session Validation (every request)
-- SQLite
EXPLAIN QUERY PLAN
SELECT * FROM sessions
WHERE token_hash = 'abc123...'
AND revoked = 0
AND expires_at > '2026-02-21T00:00:00';
-- Expected: SEARCH TABLE sessions USING INDEX idx_sessions_token (token_hash=?)
-- PostgreSQL
EXPLAIN ANALYZE
SELECT * FROM sessions
WHERE token_hash = 'abc123...'
AND revoked = 0
AND expires_at > '2026-02-21T00:00:00';
-- Expected: Index Scan using idx_sessions_token on sessions (cost=0.28..8.30)
-- Filter: (revoked = 0 AND expires_at > ...)
Audit Log by User (investigation)
-- PostgreSQL
EXPLAIN ANALYZE
SELECT * FROM audit_log
WHERE user_id = 'usr_demo1'
ORDER BY timestamp DESC
LIMIT 100;
-- Expected: Index Scan Backward using idx_audit_log_user
Key Metrics to Track
| Metric |
Target |
Alert Threshold |
Query |
| Session validation latency |
< 5ms |
> 20ms |
SELECT * FROM sessions WHERE token_hash = ? |
| Transaction list latency |
< 50ms |
> 200ms |
SELECT * FROM transactions WHERE user_id = ? ORDER BY created_at DESC LIMIT 20 |
| Audit log write latency |
< 10ms |
> 50ms |
INSERT INTO audit_log (...) |
| Index bloat |
< 20% |
> 50% |
pg_stat_user_indexes |
| Sequential scans on large tables |
0 |
Any |
pg_stat_user_tables.seq_scan for transactions, audit_log |
Periodic Index Maintenance (PostgreSQL)
-- Check index usage
SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;
-- Check for bloated indexes
SELECT pg_size_pretty(pg_relation_size(indexrelid)) as size, indexrelid::regclass
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;
-- Reindex if bloated
REINDEX INDEX CONCURRENTLY idx_transactions_user;
Cross-References
