PostgreSQL in production

PostgreSQL runs on port 5432 on the Apex server as the transactional database for anything that requires ACID guarantees: the trading brain (orders, positions, audit trail), the lead pipeline state machine, engagement metrics for lead scoring, and campaign state for the outbound email system.

The trading brain database has 25 tables and approximately 600K historical trades. It stores the authoritative position record that Apex references before sizing any new order. PostgreSQL is the right choice here, not ClickHouse, because the access pattern is point lookups: "what is the current position in AAPL?" requires a single index scan, not an analytical range query. ClickHouse's columnar storage makes it slower for point lookups by design.

The lead pipeline uses a state machine implemented as a PostgreSQL enum column with CHECK constraints that enforce valid transitions. A lead moves through: raw, enriched, verified, scored, ready, in-sequence, replied, booked, closed. Triggers on the transition column write audit rows to a leads_audit table. This is the same pattern used by the variant_stats table in the A/B testing system, where PostgreSQL tracks which email subject line variant each lead received.

Example workflow: implementing a state machine for a client's order management system. 1. Define a PostgreSQL ENUM type (order_status) with all valid states: pending, confirmed, in_fulfillment, shipped, delivered, refunded. 2. Add a CHECK constraint on the transition column that enforces valid moves using a lookup table of allowed (from, to) pairs stored in a small PostgreSQL table. 3. Create a BEFORE UPDATE trigger that rejects any transition not in the allowed-moves table, raising a descriptive exception. 4. Add an order_audit table with columns (order_id, from_status, to_status, changed_at, changed_by). The trigger writes a row here on every valid transition. 5. Expose the state machine transitions via a Next.js Server Action that wraps the UPDATE in a transaction. Any constraint violation rolls back cleanly. 6. Index the status column for the common query pattern: SELECT * FROM orders WHERE status = 'in_fulfillment' ORDER BY created_at.

The engagement_metrics table feeds the lead scoring V2 model. It holds open, click, reply, and unsubscribe events from Instantly webhook deliveries, keyed by lead email. The lead scoring training script joins this table against ClickHouse behavioral signals and SEC IAPD firmographic data to build the training set.

A REST proxy runs on port 3001 in front of PostgreSQL for any service that cannot use a native PostgreSQL driver. This is primarily used by the Next.js admin UI, which hits the proxy rather than maintaining a direct database connection pool.