perf(db): overhaul PostgreSQL connection pooling and indexing

[0dillon]

Summary

Our database interaction layer is currently suffering from connection leaks and unoptimized queries. Under high load, we are occasionally spinning up new connections per request rather than utilizing a global pool, which quickly exhausts the PostgreSQL connection limits. Furthermore, endpoints like the admin dashboard are triggering full table scans on the transactions table.

This overhaul requires standardizing the pg connection pool with strict lifecycle management (e.g., graceful shutdown on SIGTERM). We also need to introduce composite indexes on frequently queried columns (like status and created_at) to optimize the SEP-31 receiver lookups and prevent race conditions during concurrent webhook deliveries.

Acceptance Criteria

• Implement a singleton pg connection pool with a max of 50 connections.
• Add graceful shutdown hooks to drain the pool when the Node process exits.
• Add composite indexes to the transactions table to eliminate sequential scans.
• Refactor the SEP-31 lookup to use robust transaction isolation levels.

Tech Stack

TypeScript (Node.js 24), pg (node-postgres), PostgreSQL 16.