Building a Production-Ready Polymarket Trading Bot: PostgreSQL, Logging & Crash Recovery

Day 14 of the Polymarket Market Maker Bot project focuses on building a reliable infrastructure for the Python trading bot. The primary concern is maintaining the bot's trading state when the Virtual Private Server (VPS) restarts, as in-memory variables would be lost, potentially leading to inaccurate order submission, capital calculations, and portfolio exposure.

The solution involves implementing a PostgreSQL database to store orders, fills, and positions, persistent trading state, structured logging in Python, bot health monitoring, automated crash recovery, exchange reconciliation, and preventing duplicate event processing.

The first step is understanding the issue with in-memory trading state. Consider a scenario where the bot has a balance of 1000, 50 tokens in YES inventory, 20 in NO inventory, and 3 open orders. A VPS crash occurs, during which one YES order gets filled and another is canceled. Upon the VPS restarting, the bot incorrectly assumes the previous inventory values, which can lead to submitting duplicate orders, misestimating available capital, or exceeding portfolio limits. This highlights the need for persistent storage.

PostgreSQL is recommended as the primary database for the Python-based Polymarket trading system. Comparing technologies, PostgreSQL offers durability for orders, fills, positions, balances, and trading history, while Redis can be used for temporary state, caching, and coordination. SQLite can be employed for local development and lightweight testing, and JSON files can store configuration and small local records.

The architecture includes the Polymarket exchange, execution engine, trading events, PostgreSQL for persistent data, Redis for temporary state, recovery engine, exchange reconciliation, and portfolio state management.

Designing the trading database requires identifying the essential information to preserve. Important tables include 'markets' for market information, 'orders' for submitted and updated orders, 'fills' for individual trade executions, 'positions' for current inventory snapshots, 'balance_snapshots' for account balance history, 'risk_events' for risk decisions and warnings, 'bot_sessions' for startup and shutdown history, and 'risk_events' for risk decisions and warnings.

The 'markets' table should store unique identifiers for markets, order tables should include internal order ID, exchange order ID, market and token identifiers, order type, price, quantity, filled quantity, status, and timestamps, while the fill table should track each fill event's quantity and its impact on inventory, capital, and fees.

The positions table should store current inventory snapshots, and the balance_snapshots table should maintain an account balance history.