Developer Documentation

Database Index Review

Database Index Review

Stage 9 reviewed the high-growth financial and operational tables.

| Table | Current Indexes | Added In Stage 9 | Query Pattern | Future Risk |

| --- | --- | --- | --- | --- |

| transactions | public/reference unique, user, wallet, merchant, customer, type, status, created_at, merchant+created, customer+created, status+created, type+status+created | none | histories, monitoring, reversals, receipts | partition monthly by created_at when row count grows into tens of millions |

| ledger_entries | public unique, wallet, transaction, entry_type, created_at | none | wallet audit, transaction timeline, ledger reconciliation | immutable growth; partition monthly and keep cold storage |

| payments | public/reference unique, idempotency, merchant, customer, status, created_at, merchant+created, customer+created | status,created_at optional migration | customer/merchant history, payment monitoring | provider refs may need extra composite index later |

| withdrawals | public/reference unique, idempotency, merchant, customer, status, created_at, merchant+created, customer+created | status,created_at optional migration | merchant requests, withdrawal monitoring | status-heavy queues may need status+merchant+created |

| provider_transactions | public unique, provider_reference, provider_code, status+created, payment, withdrawal | provider+created | provider reconciliation and provider dashboards | avoid JSON scans; normalize frequently-filtered provider fields |

| webhook_events | public unique, provider event unique, provider_reference, status+created | created_at | replay, duplicate detection, provider debugging | raw JSON payloads grow quickly; archive by month |

| audit_logs | public unique, actor, action, entity, created_at | none | admin activity, compliance audit trail | compliance retention and partitioning needed |

| request_logs | public unique, request_id, path+created, status+created | none | observability, latency reports | high write volume; short retention or archive |

| error_logs | public unique, request_id, level+created | none | incident response | retain longer than request logs |

| jobs | queue+status+available | priority/lock indexes | queue claiming, stale lock release | keep completed-job cleanup policy |

| notifications | public unique, user+created, status | created_at | mobile inbox, notification worker | archive sent notifications after retention window |

General rule: no high-volume endpoint should rely on OFFSET at scale. Cursor pagination and date filters are mandatory for operational views.