Database Schema Documentation

erDiagram %% Schema: kalshimarkets kalshimarkets_raw_markets { market_id VARCHAR(255) PK,NOT NULL event_ticker VARCHAR(255) NOT NULL title TEXT NOT NULL description TEXT category VARCHAR(255) subcategory VARCHAR(255) status VARCHAR(50) NOT NULL open_time TIMESTAMP NOT NULL close_time TIMESTAMP NOT NULL settlement_time TIMESTAMP settlement_value NUMERIC(20, 8) yes_bid NUMERIC(20, 8) yes_ask NUMERIC(20, 8) no_bid NUMERIC(20, 8) no_ask NUMERIC(20, 8) volume NUMERIC open_interest NUMERIC liquidity NUMERIC last_trade_price NUMERIC(20, 8) last_trade_time TIMESTAMP rules_primary TEXT rules_secondary TEXT is_primary_market BOOLEAN raw_metadata JSON created_at TIMESTAMP updated_at TIMESTAMP last_checked TIMESTAMP } %% Schema: limitlessctfexchange limitlessctfexchange_fee_charged { id VARCHAR PK,NOT NULL receiver TEXT NOT NULL token_id NUMERIC NOT NULL amount NUMERIC NOT NULL timestamp TIMESTAMP NOT NULL block INTEGER NOT NULL tx_hash TEXT NOT NULL } limitlessctfexchange_hot_block { height INTEGER PK,NOT NULL hash TEXT NOT NULL } limitlessctfexchange_hot_change_log { block_height INTEGER PK,NOT NULL index INTEGER PK,NOT NULL change JSONB NOT NULL } limitlessctfexchange_migrations { id INTEGER PK,NOT NULL timestamp INTEGER NOT NULL name VARCHAR NOT NULL } limitlessctfexchange_new_admin { id VARCHAR PK,NOT NULL new_admin_address TEXT NOT NULL admin TEXT NOT NULL timestamp TIMESTAMP NOT NULL block INTEGER NOT NULL tx_hash TEXT NOT NULL } limitlessctfexchange_new_operator { id VARCHAR PK,NOT NULL new_operator_address TEXT NOT NULL admin TEXT NOT NULL timestamp TIMESTAMP NOT NULL block INTEGER NOT NULL tx_hash TEXT NOT NULL } limitlessctfexchange_order { id VARCHAR PK,NOT NULL salt NUMERIC NOT NULL maker TEXT NOT NULL signer TEXT NOT NULL taker TEXT NOT NULL token_id NUMERIC NOT NULL maker_amount NUMERIC NOT NULL taker_amount NUMERIC NOT NULL expiration NUMERIC NOT NULL nonce NUMERIC NOT NULL fee_rate_bps NUMERIC NOT NULL side INTEGER NOT NULL signature_type INTEGER NOT NULL signature TEXT NOT NULL timestamp TIMESTAMP NOT NULL block INTEGER NOT NULL tx_hash TEXT NOT NULL status_id VARCHAR } limitlessctfexchange_order_cancelled { id VARCHAR PK,NOT NULL order_hash TEXT NOT NULL timestamp TIMESTAMP NOT NULL block INTEGER NOT NULL tx_hash TEXT NOT NULL } limitlessctfexchange_order_filled { id VARCHAR PK,NOT NULL order_hash TEXT NOT NULL maker TEXT NOT NULL taker TEXT NOT NULL maker_asset_id NUMERIC NOT NULL taker_asset_id NUMERIC NOT NULL maker_amount_filled NUMERIC NOT NULL taker_amount_filled NUMERIC NOT NULL fee NUMERIC NOT NULL timestamp TIMESTAMP NOT NULL block INTEGER NOT NULL tx_hash TEXT NOT NULL } limitlessctfexchange_order_status { id VARCHAR PK,NOT NULL is_filled_or_cancelled BOOLEAN NOT NULL remaining NUMERIC NOT NULL last_updated TIMESTAMP NOT NULL block INTEGER NOT NULL } limitlessctfexchange_orders_matched { id VARCHAR PK,NOT NULL taker_order_hash TEXT NOT NULL taker_order_maker TEXT NOT NULL maker_asset_id NUMERIC NOT NULL taker_asset_id NUMERIC NOT NULL maker_amount_filled NUMERIC NOT NULL taker_amount_filled NUMERIC NOT NULL timestamp TIMESTAMP NOT NULL block INTEGER NOT NULL tx_hash TEXT NOT NULL } limitlessctfexchange_proxy_factory_updated { id VARCHAR PK,NOT NULL old_proxy_factory TEXT NOT NULL new_proxy_factory TEXT NOT NULL timestamp TIMESTAMP NOT NULL block INTEGER NOT NULL tx_hash TEXT NOT NULL } limitlessctfexchange_removed_admin { id VARCHAR PK,NOT NULL removed_admin TEXT NOT NULL admin TEXT NOT NULL timestamp TIMESTAMP NOT NULL block INTEGER NOT NULL tx_hash TEXT NOT NULL } limitlessctfexchange_removed_operator { id VARCHAR PK,NOT NULL removed_operator TEXT NOT NULL admin TEXT NOT NULL timestamp TIMESTAMP NOT NULL block INTEGER NOT NULL tx_hash TEXT NOT NULL } limitlessctfexchange_safe_factory_updated { id VARCHAR PK,NOT NULL old_safe_factory TEXT NOT NULL new_safe_factory TEXT NOT NULL timestamp TIMESTAMP NOT NULL block INTEGER NOT NULL tx_hash TEXT NOT NULL } limitlessctfexchange_status { id INTEGER PK,NOT NULL height INTEGER NOT NULL hash TEXT nonce INTEGER } limitlessctfexchange_token_registered { id VARCHAR PK,NOT NULL token0 NUMERIC NOT NULL token1 NUMERIC NOT NULL condition_id TEXT NOT NULL timestamp TIMESTAMP NOT NULL block INTEGER NOT NULL tx_hash TEXT NOT NULL } limitlessctfexchange_token_registry { id VARCHAR PK,NOT NULL complement NUMERIC NOT NULL condition_id TEXT NOT NULL last_updated TIMESTAMP NOT NULL block INTEGER NOT NULL } limitlessctfexchange_trading_paused { id VARCHAR PK,NOT NULL pauser TEXT NOT NULL timestamp TIMESTAMP NOT NULL block INTEGER NOT NULL tx_hash TEXT NOT NULL } limitlessctfexchange_trading_unpaused { id VARCHAR PK,NOT NULL pauser TEXT NOT NULL timestamp TIMESTAMP NOT NULL block INTEGER NOT NULL tx_hash TEXT NOT NULL } limitlessctfexchange_user_state { id VARCHAR PK,NOT NULL is_admin BOOLEAN NOT NULL is_operator BOOLEAN NOT NULL nonce NUMERIC NOT NULL proxy_wallet_address TEXT safe_address TEXT last_updated TIMESTAMP NOT NULL block INTEGER NOT NULL } %% Schema: migration_system migration_system_migration_history { id INTEGER PK,NOT NULL version VARCHAR(50) UNIQUE,NOT NULL description TEXT sql_hash VARCHAR(64) NOT NULL deployed_at TIMESTAMP deployed_by VARCHAR(100) status VARCHAR(20) rollback_sql TEXT } %% Schema: pmf pmf_authors { id UUID PK,NOT NULL "Unique identifier (UUID primary key)" privy_user_id VARCHAR(255) UNIQUE,NOT NULL "Privy user ID from authentication token" wallet_address VARCHAR(255) UNIQUE,NOT NULL "Unique blockchain wallet address for the author" email VARCHAR(255) UNIQUE,NOT NULL "Email address from Privy linked accounts" twitter VARCHAR(255) "Twitter handle (optional)" telegram VARCHAR(255) "Telegram username (optional)" is_active BOOLEAN NOT NULL "Whether author is approved/active (admin-controlled, default false for applications)" created_at TIMESTAMP NOT NULL "Timestamp when author record was created" updated_at TIMESTAMP NOT NULL "Timestamp when author record was last updated (auto-updated via trigger)" } pmf_group_nav_hourly { group_id UUID PK,FK,NOT NULL "Foreign key to market_groups" ts TIMESTAMP PK,NOT NULL "Snapshot timestamp, truncated to hour boundary" nav NUMERIC(12, 2) NOT NULL "Total portfolio value in base currency" nav_normalized NUMERIC(10, 6) NOT NULL "Normalized NAV (1.0 at inception)" cash_value NUMERIC(12, 2) NOT NULL "Uninvested cash portion" invested_value NUMERIC(12, 2) NOT NULL "Market value of all positions" invested_ratio NUMERIC(5, 4) NOT NULL "Percentage invested (0.0 to 1.0)" base_currency TEXT NOT NULL "Currency unit for NAV calculations" nav_metadata JSONB NOT NULL "Additional metadata (quality indicators, errors, etc.)" } pmf_portfolio_rebalances { id UUID PK,NOT NULL "Unique identifier for the rebalance event" group_id UUID FK,NOT NULL "Foreign key to market_groups (portfolio)" rebalance_timestamp TIMESTAMP NOT NULL "When the rebalance occurred" portfolio_value_before NUMERIC(12, 2) NOT NULL "Total portfolio value before rebalancing" portfolio_value_after NUMERIC(12, 2) NOT NULL "Total portfolio value after rebalancing (should be same)" allocations JSONB NOT NULL "JSON array of allocations with percentages, amounts, and shares calculated" rebalance_type VARCHAR(50) "Type of rebalance: manual (user), automatic (scheduled), initial (first allocation)" triggered_by VARCHAR(255) "User ID or system identifier that triggered the rebalance" notes TEXT "Optional notes about the rebalance" created_at TIMESTAMP NOT NULL updated_at TIMESTAMP NOT NULL } %% Schema: polymarket polymarket_raw_markets { condition_id VARCHAR(255) PK,NOT NULL question_id VARCHAR(255) NOT NULL question TEXT NOT NULL description TEXT category VARCHAR(255) end_date_iso TIMESTAMP game_start_time TIMESTAMP market_slug VARCHAR(255) active BOOLEAN closed BOOLEAN seconds_delay NUMERIC icon TEXT fpmm VARCHAR(255) minimum_order_size NUMERIC(20, 8) minimum_tick_size NUMERIC(20, 8) min_incentive_size NUMERIC(20, 8) max_incentive_spread NUMERIC(20, 8) best_bid_price NUMERIC(20, 8) best_bid_size NUMERIC(20, 8) best_ask_price NUMERIC(20, 8) best_ask_size NUMERIC(20, 8) midpoint_price NUMERIC(20, 8) spread NUMERIC(20, 8) volume NUMERIC(20, 8) liquidity NUMERIC(20, 8) token_yes_id VARCHAR(255) token_yes_outcome TEXT token_no_id VARCHAR(255) token_no_outcome TEXT raw_metadata JSON created_at TIMESTAMP updated_at TIMESTAMP last_checked TIMESTAMP } polymarket_transformed_markets { market_id VARCHAR(255) PK,NOT NULL question TEXT NOT NULL description TEXT category VARCHAR(255) status VARCHAR(50) end_date TIMESTAMP volume NUMERIC(20, 8) liquidity NUMERIC(20, 8) best_buy_price NUMERIC(20, 8) best_buy_size NUMERIC(20, 8) best_sell_price NUMERIC(20, 8) best_sell_size NUMERIC(20, 8) midpoint NUMERIC(20, 8) spread NUMERIC(20, 8) is_active BOOLEAN created_at TIMESTAMP updated_at TIMESTAMP last_checked TIMESTAMP } %% Schema: public public_grouped_markets { id VARCHAR(255) PK,NOT NULL title TEXT NOT NULL final_end_date TIMESTAMP NOT NULL created_at TIMESTAMP NOT NULL updated_at TIMESTAMP NOT NULL market_ids JSONB NOT NULL platforms JSONB NOT NULL metrics JSONB NOT NULL } public_kalshi_markets { market_id VARCHAR(255) PK,NOT NULL event_ticker VARCHAR(255) NOT NULL title TEXT NOT NULL description TEXT status VARCHAR(50) NOT NULL expiration_date TIMESTAMP category VARCHAR(255) subcategory VARCHAR(255) volume NUMERIC open_interest NUMERIC volume_24h NUMERIC total_trades INTEGER last_trade_time TIMESTAMP best_bid_price NUMERIC(20, 8) best_bid_size NUMERIC(20, 8) best_ask_price NUMERIC(20, 8) best_ask_size NUMERIC(20, 8) last_trade_price NUMERIC(20, 8) adjusted_midpoint NUMERIC(20, 8) created_at TIMESTAMP NOT NULL updated_at TIMESTAMP NOT NULL last_checked TIMESTAMP NOT NULL group_id VARCHAR(255) FK group_similarity NUMERIC(20, 8) } public_market_group_items { id UUID PK,NOT NULL group_id UUID NOT NULL market_id VARCHAR NOT NULL market_type VARCHAR NOT NULL created_at TIMESTAMP } public_polymarket_markets { market_id VARCHAR(255) PK,NOT NULL question TEXT NOT NULL description TEXT category VARCHAR(255) end_date TIMESTAMP status VARCHAR(50) volume NUMERIC(20, 8) liquidity NUMERIC(20, 8) is_active BOOLEAN group_id VARCHAR(255) FK group_similarity NUMERIC(20, 8) yes_token_id VARCHAR(255) yes_best_buy_price NUMERIC(20, 8) yes_best_buy_size NUMERIC(20, 8) yes_best_sell_price NUMERIC(20, 8) yes_best_sell_size NUMERIC(20, 8) yes_midpoint NUMERIC(20, 8) yes_spread NUMERIC(20, 8) no_token_id VARCHAR(255) no_best_buy_price NUMERIC(20, 8) no_best_buy_size NUMERIC(20, 8) no_best_sell_price NUMERIC(20, 8) no_best_sell_size NUMERIC(20, 8) no_midpoint NUMERIC(20, 8) no_spread NUMERIC(20, 8) created_at TIMESTAMP updated_at TIMESTAMP last_checked TIMESTAMP } public_schema_migrations { version TEXT PK,NOT NULL applied_at TIMESTAMP NOT NULL checksum TEXT NOT NULL execution_time_ms INTEGER status TEXT } %% Schema: trading trading_opportunities { id UUID PK,NOT NULL group_id UUID FK,NOT NULL timestamp TIMESTAMP NOT NULL apy NUMERIC(10, 2) NOT NULL "Annualized percentage yield = (spread / days_to_close) * 365" spread NUMERIC(10, 4) NOT NULL "Profit margin after all fees (1 - total_cost)" days_to_close INTEGER NOT NULL kalshi_yes_price NUMERIC(10, 4) kalshi_no_price NUMERIC(10, 4) poly_yes_price NUMERIC(10, 4) poly_no_price NUMERIC(10, 4) limitless_yes_price NUMERIC(10, 4) limitless_no_price NUMERIC(10, 4) yes_platform TEXT "Platform to buy YES on for this opportunity" no_platform TEXT "Platform to buy NO on for this opportunity" total_cost NUMERIC(10, 4) "Total cost including all fees and gas" traded BOOLEAN "Whether a position was opened for this opportunity" trade_reason TEXT position_id UUID FK market_title TEXT created_at TIMESTAMP yes_orderbook_liquidity NUMERIC(20, 2) "Total YES liquidity available in orderbook ($)" no_orderbook_liquidity NUMERIC(20, 2) "Total NO liquidity available in orderbook ($)" executable_size NUMERIC(20, 2) "Maximum size that can be executed given orderbook depth" yes_best_ask NUMERIC(10, 6) "Best ask price for YES at opportunity detection" no_best_ask NUMERIC(10, 6) "Best ask price for NO at opportunity detection" yes_ask_depth INTEGER "Number of ask levels in YES orderbook" no_ask_depth INTEGER "Number of ask levels in NO orderbook" orderbook_freshness_seconds INTEGER "Age of orderbook data in seconds" execution_method TEXT "Whether opportunity was calculated from market prices or orderbook" } trading_orderbook_snapshots { id UUID PK,NOT NULL group_id UUID FK,NOT NULL timestamp TIMESTAMP NOT NULL kalshi_yes_liquidity NUMERIC(20, 2) kalshi_no_liquidity NUMERIC(20, 2) kalshi_spread NUMERIC(10, 6) kalshi_mid_price NUMERIC(10, 6) kalshi_bid_levels INTEGER kalshi_ask_levels INTEGER polymarket_yes_liquidity NUMERIC(20, 2) polymarket_no_liquidity NUMERIC(20, 2) polymarket_spread NUMERIC(10, 6) polymarket_mid_price NUMERIC(10, 6) polymarket_bid_levels INTEGER polymarket_ask_levels INTEGER limitless_yes_liquidity NUMERIC(20, 2) limitless_no_liquidity NUMERIC(20, 2) limitless_spread NUMERIC(10, 6) limitless_mid_price NUMERIC(10, 6) limitless_bid_levels INTEGER limitless_ask_levels INTEGER max_executable_arbitrage NUMERIC(20, 2) "Maximum arbitrage size given orderbook constraints" best_yes_platform TEXT best_no_platform TEXT arbitrage_spread NUMERIC(10, 4) arbitrage_apy NUMERIC(10, 2) created_at TIMESTAMP } trading_positions { id UUID PK,NOT NULL session_id UUID FK opportunity_id UUID strategy_name TEXT NOT NULL is_simulation BOOLEAN NOT NULL created_at TIMESTAMP NOT NULL yes_market_id UUID yes_platform TEXT NOT NULL yes_target_price NUMERIC(20, 8) yes_executed_price NUMERIC(20, 8) yes_execution_time TIMESTAMP yes_size NUMERIC(20, 8) yes_fees NUMERIC(20, 8) yes_slippage NUMERIC(20, 8) no_market_id UUID no_platform TEXT NOT NULL no_target_price NUMERIC(20, 8) no_executed_price NUMERIC(20, 8) no_execution_time TIMESTAMP no_size NUMERIC(20, 8) no_fees NUMERIC(20, 8) no_slippage NUMERIC(20, 8) status TEXT NOT NULL total_invested NUMERIC(20, 8) max_risk NUMERIC(20, 8) exit_time TIMESTAMP exit_reason TEXT market_resolution TEXT gross_pnl NUMERIC(20, 8) total_fees_paid NUMERIC(20, 8) net_pnl NUMERIC(20, 8) execution_latency_ms INTEGER failure_reason TEXT metadata JSONB group_id UUID FK "Link to the verified market group being traded" entry_apy NUMERIC(10, 2) "APY at time of trade entry (%)" entry_spread NUMERIC(10, 4) "Price spread at entry (decimal)" days_to_resolution INTEGER "Days until market resolution at entry" yes_levels_consumed INTEGER "Number of orderbook levels consumed for YES execution" no_levels_consumed INTEGER "Number of orderbook levels consumed for NO execution" yes_avg_execution_price NUMERIC(10, 6) "Volume-weighted average price for YES execution from orderbook" no_avg_execution_price NUMERIC(10, 6) "Volume-weighted average price for NO execution from orderbook" max_available_liquidity NUMERIC(20, 2) "Maximum executable size based on orderbook liquidity at entry" liquidity_constrained_by TEXT "Which side limited the trade size (yes/no/both/none)" price_impact_pct NUMERIC(10, 4) "Price impact from walking the orderbook (%)" orderbook_timestamp TIMESTAMP "Timestamp of orderbook data used for execution" } trading_sessions { id UUID PK,NOT NULL name TEXT NOT NULL mode TEXT NOT NULL started_at TIMESTAMP NOT NULL ended_at TIMESTAMP total_positions INTEGER total_pnl NUMERIC(20, 8) status TEXT NOT NULL metadata JSONB } %% Schema: unified unified_archived_markets { id UUID PK,NOT NULL "UUID primary key" platform TEXT NOT NULL "Trading platform: kalshi, polymarket, or limitless" platform_market_id TEXT NOT NULL "Platform-specific unique identifier" title TEXT NOT NULL "Market question/title as displayed" description TEXT "Detailed market description" category TEXT "Market category" subcategory TEXT "Market subcategory" status TEXT NOT NULL "Market status: open, closed, resolved" is_active BOOLEAN "Whether market is currently tradeable" created_at TIMESTAMP "When we first saw this market" updated_at TIMESTAMP "Last update timestamp" last_checked TIMESTAMP "Last API check timestamp" market_open_time TIMESTAMP market_close_time TIMESTAMP "When market closes for trading" resolution_time TIMESTAMP "When market resolves" last_trade_time TIMESTAMP volume_24h NUMERIC(20, 8) "Trading volume in last 24 hours" total_volume NUMERIC(20, 8) "Total historical volume" contract_volume NUMERIC(20, 8) liquidity NUMERIC(20, 8) "Available liquidity" open_interest NUMERIC(20, 8) "Open interest value" total_trades INTEGER unique_traders INTEGER best_bid_price NUMERIC(20, 8) best_bid_size NUMERIC(20, 8) best_ask_price NUMERIC(20, 8) best_ask_size NUMERIC(20, 8) last_trade_price NUMERIC(20, 8) "Last traded price" midpoint_price NUMERIC(20, 8) "Midpoint between bid and ask" spread NUMERIC(20, 8) "Bid-ask spread" adjusted_midpoint NUMERIC(20, 8) yes_token_id TEXT yes_best_buy_price NUMERIC(20, 8) "Best bid for YES token" yes_best_buy_size NUMERIC(20, 8) yes_best_sell_price NUMERIC(20, 8) "Best ask for YES token" yes_best_sell_size NUMERIC(20, 8) yes_midpoint NUMERIC(20, 8) yes_spread NUMERIC(20, 8) no_token_id TEXT no_best_buy_price NUMERIC(20, 8) "Best bid for NO token" no_best_buy_size NUMERIC(20, 8) no_best_sell_price NUMERIC(20, 8) "Best ask for NO token" no_best_sell_size NUMERIC(20, 8) no_midpoint NUMERIC(20, 8) no_spread NUMERIC(20, 8) event_ticker TEXT market_slug TEXT limitless_id INTEGER max_spread NUMERIC(20, 8) min_size NUMERIC(20, 8) collateral_token_symbol TEXT collateral_token_address TEXT collateral_token_decimals INTEGER categories ARRAY group_id UUID group_similarity NUMERIC(20, 8) platform_metadata JSONB last_realtime_update TIMESTAMP "Timestamp of last real-time WebSocket update" last_api_update TIMESTAMP "Timestamp of last API polling update" data_confidence_score NUMERIC(3, 2) data_freshness_score NUMERIC(3, 2) data_completeness_score NUMERIC(3, 2) platform_reliability_score NUMERIC(3, 2) overall_quality_score NUMERIC(3, 2) last_quality_check TIMESTAMP quality_check_count INTEGER normalized_title TEXT similarity_hash TEXT cross_platform_group_id UUID fee_percentage NUMERIC(5, 4) fee_function_type VARCHAR(50) yes_price NUMERIC(10, 6) no_price NUMERIC(10, 6) price_confidence NUMERIC(3, 2) derived_event_id TEXT title_embedding NULL description_embedding NULL } unified_batch_operations { id UUID PK,NOT NULL platform TEXT NOT NULL operation_type TEXT NOT NULL started_at TIMESTAMP completed_at TIMESTAMP duration_seconds INTEGER "Automatically calculated duration in seconds" total_items INTEGER successful_items INTEGER failed_items INTEGER retried_items INTEGER success_rate NUMERIC(5, 4) "Automatically calculated success rate (0-1)" batch_metadata JSONB performance_stats JSONB error_summary JSONB parent_batch_id UUID FK status TEXT } unified_builder_orders { id UUID PK,NOT NULL "Unique identifier for the order record" token_id TEXT NOT NULL "Polymarket token ID for the market outcome" side TEXT NOT NULL "Order side: BUY or SELL" order_volume NUMERIC(20, 8) NOT NULL "Order volume in USDC (up to 8 decimal places)" price NUMERIC(20, 8) "Limit order price between 0.01 and 0.99 (optional, null for market orders)" group_id UUID FK "Reference to market group (optional, preserved even if group is deleted)" writer_id UUID FK "Reference to author/writer (optional, preserved even if author is deleted)" request_path TEXT NOT NULL "HTTP request path from the original order request" request_method TEXT NOT NULL "HTTP request method from the original order request (typically POST)" created_at TIMESTAMP NOT NULL "Timestamp when the order was signed and recorded" } unified_dead_letter_queue { id UUID PK,NOT NULL message_type TEXT NOT NULL "Type of message (market_update, websocket_event, api_call)" payload JSONB NOT NULL "Original message payload as JSON" platform TEXT NOT NULL "Platform that generated the message" market_id TEXT "Market ID if applicable" original_timestamp TIMESTAMP NOT NULL failure_reason TEXT NOT NULL "Reason for message failure" retry_count INTEGER NOT NULL "Number of retry attempts made" max_retries INTEGER NOT NULL "Maximum retry attempts allowed" status TEXT NOT NULL "Current message status" created_at TIMESTAMP NOT NULL updated_at TIMESTAMP NOT NULL next_retry_at TIMESTAMP "When to retry the message next" resolved_at TIMESTAMP "When the message was successfully resolved" metadata JSONB "Additional metadata as JSON" } unified_embedding_queue { id UUID PK,NOT NULL entity_type TEXT NOT NULL entity_id UUID NOT NULL text_content TEXT NOT NULL embedding_type TEXT NOT NULL status TEXT created_at TIMESTAMP processed_at TIMESTAMP error_message TEXT retry_count INTEGER } unified_event_series { event_id UUID PK,FK,NOT NULL series_id UUID PK,FK,NOT NULL position INTEGER created_at TIMESTAMP } unified_events { id UUID PK,NOT NULL title TEXT NOT NULL normalized_title TEXT description TEXT category TEXT subcategory TEXT start_date TIMESTAMP end_date TIMESTAMP resolution_date TIMESTAMP status TEXT NOT NULL is_active BOOLEAN total_markets INTEGER active_markets INTEGER resolved_markets INTEGER total_volume NUMERIC(20, 8) platforms ARRAY platform_count INTEGER kalshi_event_id TEXT UNIQUE kalshi_event_ticker TEXT polymarket_event_id TEXT UNIQUE polymarket_event_slug TEXT limitless_event_id TEXT similarity_hash TEXT UNIQUE title_embedding NULL tags ARRAY platform_metadata JSONB created_at TIMESTAMP updated_at TIMESTAMP last_synced TIMESTAMP } unified_fund_prices_daily { id UUID PK,NOT NULL group_id UUID FK,NOT NULL "Reference to the market group (fund) this price belongs to" price_date DATE NOT NULL "Date for this price snapshot (date only, no time component)" price NUMERIC(10, 6) NOT NULL "Weighted average price of all markets in the fund (0.0 to 1.0), accounting for position types" market_count INTEGER NOT NULL "Total number of markets included in the computation" polymarket_count INTEGER NOT NULL "Number of Polymarket markets included (subset of market_count)" total_weight NUMERIC(12, 4) NOT NULL "Sum of all market weights used in the computation" data_completeness NUMERIC(5, 2) "Percentage of markets that had price data available (0-100)" avg_price_age_hours INTEGER "Average age in hours of the constituent market prices used" computation_method VARCHAR(50) NOT NULL "How this price was computed: batch (scheduled job) or on_demand (API request)" computed_at TIMESTAMP NOT NULL "Timestamp when this price was computed" computation_duration_ms INTEGER "Time taken to compute this price in milliseconds" metadata JSONB NOT NULL "Additional context as JSON (e.g., markets skipped, errors encountered, data sources)" created_at TIMESTAMP NOT NULL "Timestamp when this record was first created" updated_at TIMESTAMP NOT NULL "Timestamp when this record was last updated" } unified_group_market_relationships { id UUID PK,NOT NULL group_id UUID FK,NOT NULL market_id UUID NOT NULL weight NUMERIC(10, 4) position_type VARCHAR(20) metadata JSONB added_at TIMESTAMP updated_at TIMESTAMP } unified_group_membership_history { id UUID PK,NOT NULL group_id UUID FK,NOT NULL market_id UUID NOT NULL "Market UUID - no FK constraint to preserve history even if market is deleted" action VARCHAR(20) NOT NULL "Type of change: added (new market), removed (deleted market), weight_changed (existing market weight updated)" timestamp TIMESTAMP NOT NULL "When the change occurred (defaults to NOW but can be backdated for imports)" weight NUMERIC(10, 4) "Weight after the change (NULL for removed markets)" position_type VARCHAR(20) changed_by VARCHAR(100) metadata JSONB NOT NULL "Additional context as JSON. For resolution events, includes: { "settlement_value": 100.50, -- Total value received (shares × price) "settlement_price": 1.0, -- Resolution price (0.0, 0.5, or 1.0) "resolution_outcome": "YES", -- YES, NO, or INVALID "resolution_time": "2024-12-09T15:00:00Z", "position_details": { "shares": 100.0, -- Number of shares held "entry_price": 0.75, -- Average entry price "exit_price": 1.0, -- Resolution price (adjusted for position_type) "position_type": "long", -- long or short "cost_basis": 75.0 -- Total cost (shares × entry_price) }, "profit_loss": 25.0, -- settlement_value - cost_basis "profit_loss_percent": 33.33, -- (profit_loss / cost_basis) × 100 "holding_period_days": 14, -- Days from first entry to resolution "platform_market_id": "0xabc123", -- Platform-specific ID "market_title": "Will MongoDB beat earnings?", "platform": "polymarket", -- polymarket or kalshi "source": "automatic_cleanup", -- Always automatic for resolutions "cleanup_job_id": "uuid", -- ID of cleanup job that processed this "processed_at": "2024-12-10T03:00:00Z" }" created_at TIMESTAMP NOT NULL } unified_group_templates { id UUID PK,NOT NULL name VARCHAR(100) UNIQUE,NOT NULL description TEXT group_type VARCHAR(10) NOT NULL template_config JSONB NOT NULL is_active BOOLEAN created_at TIMESTAMP updated_at TIMESTAMP version INTEGER author VARCHAR(100) tags ARRAY usage_count INTEGER last_used_at TIMESTAMP validation_rules JSONB required_permissions ARRAY validation_status VARCHAR(20) } unified_grouping_log { id INTEGER PK,NOT NULL group_id UUID action VARCHAR(20) "Type of action: created, verified, rejected, expired" actor VARCHAR(50) "System or username who performed the action" details TEXT created_at TIMESTAMP } unified_market_events_recent { id BIGINT PK,NOT NULL platform TEXT NOT NULL platform_market_id TEXT NOT NULL event_type TEXT NOT NULL trade_price NUMERIC(10, 6) trade_size NUMERIC(20, 8) trade_side TEXT old_status TEXT new_status TEXT status_reason TEXT event_data JSONB occurred_at TIMESTAMP websocket_session_id TEXT created_at TIMESTAMP } unified_market_group_members { id UUID PK,NOT NULL group_id UUID FK,NOT NULL market_id UUID NOT NULL similarity_score NUMERIC(5, 4) is_representative BOOLEAN added_at TIMESTAMP } unified_market_group_verification_log { id UUID PK,NOT NULL group_id UUID FK,NOT NULL action TEXT NOT NULL previous_state JSONB new_state JSONB verified_by TEXT NOT NULL created_at TIMESTAMP } unified_market_groups { id UUID PK,NOT NULL title TEXT NOT NULL description TEXT status TEXT similarity_threshold NUMERIC(3, 2) created_at TIMESTAMP updated_at TIMESTAMP title_embedding NULL description_embedding NULL platform_count INTEGER market_count INTEGER total_volume NUMERIC(15, 2) total_liquidity NUMERIC(15, 2) consensus_price NUMERIC(10, 6) price_variance NUMERIC(10, 6) human_verified BOOLEAN "When true, this group is tradeable. human_verified = tradeable in our simplified system." verified_at TIMESTAMP verified_by TEXT "Username/ID of admin who verified this group" verification_notes TEXT "Admin notes about the verification decision" is_valid_group BOOLEAN "Admin determination: NULL=not reviewed, TRUE=correctly grouped, FALSE=incorrectly grouped" confidence NUMERIC(3, 2) "Automated confidence score (0.00-1.00) for group quality" grouping_method VARCHAR(50) "Algorithm used to create group (e.g., multi_stage_v2)" rejection_count INTEGER "Number of times this group has been rejected in verification" is_system_generated BOOLEAN is_template BOOLEAN template_name VARCHAR(100) metadata JSONB "Type-specific configuration including agent_deployment: { "agent_deployment": { "agent_id": "uuid", "wallet_address": "0x...", "telegram_bot_id": "123456789:ABC...", "telegram_chat_id": "987654321", "deployment_status": "pending|deploying|deployed|waiting_wallet|ready|failed", "tee_endpoint": "https://...", "deployed_at": "2025-01-15T10:30:00Z", "wallet_retrieved_at": "2025-01-15T10:31:00Z", "whitelisted_at": "2025-01-15T10:32:00Z", "trading_config": { "max_position_size": 100, "min_confidence_threshold": 0.7, "unsupervised_mode": false }, "error_message": "optional error details if deployment fails" } }" display_settings JSONB group_type VARCHAR(10) source_template_id UUID FK template_version INTEGER auto_update_from_template BOOLEAN max_spread NUMERIC(5, 4) has_arbitrage_opportunity BOOLEAN max_arbitrage_profit NUMERIC(10, 4) last_arbitrage_check TIMESTAMP final_resolution_time TIMESTAMP manager VARCHAR(255) "Name or identifier of the group manager/creator" logo_url TEXT "URL to the group logo or branding image" is_paper_trading BOOLEAN NOT NULL "Flag indicating if this is a simulated (paper trading) portfolio vs real money portfolio" initial_capital NUMERIC(12, 2) "Starting capital for paper trading portfolios (default $100)" current_cash NUMERIC(12, 2) "Current unallocated cash in the portfolio" paper_trading_config JSONB "Configuration for paper trading simulations (fee settings, etc.)" track_nav BOOLEAN NOT NULL "Enable hourly NAV computation for this group" base_currency TEXT NOT NULL "Currency for NAV calculations (USDC, USD, etc.)" } unified_market_history { timestamp TIMESTAMP PK,NOT NULL market_id UUID PK,NOT NULL platform TEXT NOT NULL yes_price NUMERIC(5, 4) no_price NUMERIC(5, 4) volume NUMERIC(15, 2) liquidity NUMERIC(15, 2) } unified_market_history_2025_09 { timestamp TIMESTAMP PK,NOT NULL market_id UUID PK,NOT NULL platform TEXT NOT NULL yes_price NUMERIC(5, 4) no_price NUMERIC(5, 4) volume NUMERIC(15, 2) liquidity NUMERIC(15, 2) } unified_market_history_2025_10 { timestamp TIMESTAMP PK,NOT NULL market_id UUID PK,NOT NULL platform TEXT NOT NULL yes_price NUMERIC(5, 4) no_price NUMERIC(5, 4) volume NUMERIC(15, 2) liquidity NUMERIC(15, 2) } unified_market_history_2025_11 { timestamp TIMESTAMP PK,NOT NULL market_id UUID PK,NOT NULL platform TEXT NOT NULL yes_price NUMERIC(5, 4) no_price NUMERIC(5, 4) volume NUMERIC(15, 2) liquidity NUMERIC(15, 2) } unified_market_prices_current { platform TEXT PK,NOT NULL platform_market_id TEXT PK,NOT NULL best_bid_price NUMERIC(10, 6) best_ask_price NUMERIC(10, 6) last_trade_price NUMERIC(10, 6) volume_24h NUMERIC(20, 8) volume_total NUMERIC(20, 8) liquidity_depth NUMERIC(20, 8) spread NUMERIC(10, 6) mid_price NUMERIC(10, 6) price_change_24h NUMERIC(10, 6) price_change_pct_24h NUMERIC(10, 4) high_24h NUMERIC(10, 6) low_24h NUMERIC(10, 6) market_status TEXT resolution_value NUMERIC(10, 6) resolved_at TIMESTAMP last_ticker_update TIMESTAMP last_trade_update TIMESTAMP last_orderbook_update TIMESTAMP updated_at TIMESTAMP update_count BIGINT websocket_session_id TEXT } unified_market_similarity_cache { id UUID PK,NOT NULL market_a_id UUID NOT NULL market_b_id UUID NOT NULL similarity_score NUMERIC(5, 4) NOT NULL similarity_method VARCHAR(50) NOT NULL calculated_at TIMESTAMP } unified_markets { id UUID PK,NOT NULL platform TEXT NOT NULL platform_market_id TEXT NOT NULL title TEXT description TEXT status TEXT category TEXT market_type TEXT market_open_time TIMESTAMP market_close_time TIMESTAMP settlement_time TIMESTAMP settlement_value NUMERIC yes_price NUMERIC no_price NUMERIC yes_bid NUMERIC yes_ask NUMERIC no_bid NUMERIC no_ask NUMERIC best_bid_price NUMERIC best_ask_price NUMERIC best_bid_size NUMERIC best_ask_size NUMERIC last_trade_price NUMERIC last_trade_time TIMESTAMP total_volume NUMERIC volume_24h NUMERIC open_interest NUMERIC liquidity NUMERIC implied_probability NUMERIC url TEXT rules TEXT metadata JSONB created_at TIMESTAMP updated_at TIMESTAMP is_active BOOLEAN PK,NOT NULL event_ticker TEXT subcategory TEXT rules_primary TEXT rules_secondary TEXT expected_settlement_time TIMESTAMP settlement_source TEXT settlement_description TEXT tags ARRAY image_url TEXT platform_created_at TIMESTAMP platform_updated_at TIMESTAMP market_slug TEXT yes_token_id TEXT no_token_id TEXT midpoint_price NUMERIC spread NUMERIC volume_yes NUMERIC volume_no NUMERIC liquidity_yes NUMERIC liquidity_no NUMERIC trade_count INTEGER trader_count INTEGER minimum_order_size NUMERIC minimum_tick_size NUMERIC data_source TEXT is_test_market BOOLEAN raw_data JSONB market_group_id UUID group_confidence_score NUMERIC group_method TEXT title_embedding NULL description_embedding NULL combined_embedding NULL data_quality_score NUMERIC has_sufficient_volume BOOLEAN has_recent_activity BOOLEAN has_stable_prices BOOLEAN has_trading_bonus BOOLEAN bonus_amount NUMERIC bonus_currency TEXT expiry_date TIMESTAMP event_id UUID contract_volume NUMERIC yes_best_buy_size NUMERIC no_best_buy_size NUMERIC yes_best_sell_size NUMERIC no_best_sell_size NUMERIC total_trades INTEGER unique_traders INTEGER } unified_markets_active { id UUID PK,NOT NULL platform TEXT NOT NULL platform_market_id TEXT NOT NULL title TEXT description TEXT status TEXT category TEXT market_type TEXT market_open_time TIMESTAMP market_close_time TIMESTAMP settlement_time TIMESTAMP settlement_value NUMERIC yes_price NUMERIC no_price NUMERIC yes_bid NUMERIC yes_ask NUMERIC no_bid NUMERIC no_ask NUMERIC best_bid_price NUMERIC best_ask_price NUMERIC best_bid_size NUMERIC best_ask_size NUMERIC last_trade_price NUMERIC last_trade_time TIMESTAMP total_volume NUMERIC volume_24h NUMERIC open_interest NUMERIC liquidity NUMERIC implied_probability NUMERIC url TEXT rules TEXT metadata JSONB created_at TIMESTAMP updated_at TIMESTAMP is_active BOOLEAN PK,NOT NULL event_ticker TEXT subcategory TEXT rules_primary TEXT rules_secondary TEXT expected_settlement_time TIMESTAMP settlement_source TEXT settlement_description TEXT tags ARRAY image_url TEXT platform_created_at TIMESTAMP platform_updated_at TIMESTAMP market_slug TEXT yes_token_id TEXT no_token_id TEXT midpoint_price NUMERIC spread NUMERIC volume_yes NUMERIC volume_no NUMERIC liquidity_yes NUMERIC liquidity_no NUMERIC trade_count INTEGER trader_count INTEGER minimum_order_size NUMERIC minimum_tick_size NUMERIC data_source TEXT is_test_market BOOLEAN raw_data JSONB market_group_id UUID group_confidence_score NUMERIC group_method TEXT title_embedding NULL description_embedding NULL combined_embedding NULL data_quality_score NUMERIC has_sufficient_volume BOOLEAN has_recent_activity BOOLEAN has_stable_prices BOOLEAN has_trading_bonus BOOLEAN bonus_amount NUMERIC bonus_currency TEXT expiry_date TIMESTAMP event_id UUID contract_volume NUMERIC yes_best_buy_size NUMERIC no_best_buy_size NUMERIC yes_best_sell_size NUMERIC no_best_sell_size NUMERIC total_trades INTEGER unique_traders INTEGER } unified_markets_inactive { id UUID PK,NOT NULL platform TEXT NOT NULL platform_market_id TEXT NOT NULL title TEXT description TEXT status TEXT category TEXT market_type TEXT market_open_time TIMESTAMP market_close_time TIMESTAMP settlement_time TIMESTAMP settlement_value NUMERIC yes_price NUMERIC no_price NUMERIC yes_bid NUMERIC yes_ask NUMERIC no_bid NUMERIC no_ask NUMERIC best_bid_price NUMERIC best_ask_price NUMERIC best_bid_size NUMERIC best_ask_size NUMERIC last_trade_price NUMERIC last_trade_time TIMESTAMP total_volume NUMERIC volume_24h NUMERIC open_interest NUMERIC liquidity NUMERIC implied_probability NUMERIC url TEXT rules TEXT metadata JSONB created_at TIMESTAMP updated_at TIMESTAMP is_active BOOLEAN PK,NOT NULL event_ticker TEXT subcategory TEXT rules_primary TEXT rules_secondary TEXT expected_settlement_time TIMESTAMP settlement_source TEXT settlement_description TEXT tags ARRAY image_url TEXT platform_created_at TIMESTAMP platform_updated_at TIMESTAMP market_slug TEXT yes_token_id TEXT no_token_id TEXT midpoint_price NUMERIC spread NUMERIC volume_yes NUMERIC volume_no NUMERIC liquidity_yes NUMERIC liquidity_no NUMERIC trade_count INTEGER trader_count INTEGER minimum_order_size NUMERIC minimum_tick_size NUMERIC data_source TEXT is_test_market BOOLEAN raw_data JSONB market_group_id UUID group_confidence_score NUMERIC group_method TEXT title_embedding NULL description_embedding NULL combined_embedding NULL data_quality_score NUMERIC has_sufficient_volume BOOLEAN has_recent_activity BOOLEAN has_stable_prices BOOLEAN has_trading_bonus BOOLEAN bonus_amount NUMERIC bonus_currency TEXT expiry_date TIMESTAMP event_id UUID contract_volume NUMERIC yes_best_buy_size NUMERIC no_best_buy_size NUMERIC yes_best_sell_size NUMERIC no_best_sell_size NUMERIC total_trades INTEGER unique_traders INTEGER } unified_order_book_current { id INTEGER PK,NOT NULL "Auto-incrementing primary key" platform TEXT NOT NULL "Trading platform: polymarket, kalshi, or limitless" market_id TEXT NOT NULL "Platform-specific market identifier (e.g., Polymarket condition_id)" token_type TEXT NOT NULL "Binary outcome token: YES (buy outcome) or NO (sell outcome)" bids JSONB NOT NULL "Array of bid levels [{price, size},...] sorted by price DESC. Price in [0,1], size in base units." asks JSONB NOT NULL "Array of ask levels [{price, size},...] sorted by price ASC. Price in [0,1], size in base units." best_bid_price NUMERIC(10, 6) "Highest bid price (top of book). Null if no bids." best_bid_size NUMERIC(20, 8) "Size available at best bid price" best_ask_price NUMERIC(10, 6) "Lowest ask price (top of book). Null if no asks." best_ask_size NUMERIC(20, 8) "Size available at best ask price" spread NUMERIC(10, 6) "Computed: ask - bid. Indicates market liquidity (smaller = more liquid)" mid_price NUMERIC(10, 6) "Computed: (bid + ask) / 2. Fair value estimate." sequence_number BIGINT "Platform sequence number for detecting gaps/replays" exchange_timestamp TIMESTAMP "When the exchange generated this orderbook state" received_at TIMESTAMP "When our system first received this update" updated_at TIMESTAMP "Last modification time (auto-updated by trigger)" } unified_raw_events { id UUID PK,NOT NULL "Auto-incrementing primary key" platform TEXT NOT NULL "Source platform" event_type TEXT NOT NULL "Type of event received" market_id TEXT NOT NULL "Platform market identifier" event_data JSONB NOT NULL "Complete event payload (JSONB)" blockchain_data JSONB ingested_at TIMESTAMP "When event was received" processed_at TIMESTAMP "When event was processed" processing_status TEXT "Processing status" retry_count INTEGER "Number of retry attempts" error_message TEXT "Error details if failed" api_endpoint TEXT "API endpoint called" http_method TEXT "HTTP method used (GET, POST, etc.)" response_time_ms INTEGER "API response time in ms" response_status_code INTEGER "HTTP status code" rate_limit_remaining INTEGER "Number of requests remaining in rate limit window" rate_limit_reset_at TIMESTAMP "When rate limit window resets" data_freshness_seconds INTEGER "Age of data in seconds when received" api_version TEXT "API version used" request_headers JSONB "HTTP request headers" response_headers JSONB "HTTP response headers" pagination_cursor TEXT "Cursor for paginated requests" batch_size INTEGER "Number of items in batch request" is_incremental_update BOOLEAN "True if this is an incremental update" parent_request_id UUID "Parent request ID for related requests" batch_operation_id UUID FK batch_sequence_number INTEGER batch_context JSONB is_realtime BOOLEAN "Whether from WebSocket/realtime" websocket_session_id TEXT "Session ID for WebSocket connection that generated this event" } unified_schema_migrations { version TEXT PK,NOT NULL description TEXT applied_at TIMESTAMP } unified_series { id UUID PK,NOT NULL title TEXT NOT NULL normalized_title TEXT description TEXT category TEXT series_type TEXT frequency TEXT status TEXT NOT NULL is_active BOOLEAN total_events INTEGER total_markets INTEGER active_markets INTEGER total_volume NUMERIC(20, 8) platforms ARRAY platform_count INTEGER kalshi_series_id TEXT UNIQUE kalshi_series_ticker TEXT polymarket_series_id TEXT UNIQUE polymarket_series_slug TEXT limitless_series_id TEXT similarity_hash TEXT UNIQUE tags ARRAY platform_metadata JSONB created_at TIMESTAMP updated_at TIMESTAMP last_synced TIMESTAMP } unified_template_instantiations { id UUID PK,NOT NULL template_id UUID FK,NOT NULL group_id UUID FK,NOT NULL instantiated_at TIMESTAMP instantiated_by VARCHAR(100) instantiation_config JSONB } %% Relationships pmf_group_nav_hourly ||--|| unified_market_groups : "group_id -> id" pmf_portfolio_rebalances }o--|| unified_market_groups : "group_id -> id" public_kalshi_markets }o--|| public_grouped_markets : "group_id -> id" public_polymarket_markets }o--|| public_grouped_markets : "group_id -> id" trading_opportunities }o--|| unified_market_groups : "group_id -> id" trading_opportunities }o--|| trading_positions : "position_id -> id" trading_orderbook_snapshots }o--|| unified_market_groups : "group_id -> id" trading_positions }o--|| unified_market_groups : "group_id -> id" trading_positions }o--|| trading_sessions : "session_id -> id" unified_batch_operations }o--|| unified_batch_operations : "parent_batch_id -> id" unified_builder_orders }o--|| unified_market_groups : "group_id -> id" unified_builder_orders }o--|| pmf_authors : "writer_id -> id" unified_event_series ||--|| unified_events : "event_id -> id" unified_event_series ||--|| unified_series : "series_id -> id" unified_fund_prices_daily ||--|| unified_market_groups : "group_id -> id" unified_group_market_relationships ||--|| unified_market_groups : "group_id -> id" unified_group_membership_history }o--|| unified_market_groups : "group_id -> id" unified_market_group_members ||--|| unified_market_groups : "group_id -> id" unified_market_group_verification_log }o--|| unified_market_groups : "group_id -> id" unified_market_groups }o--|| unified_group_templates : "source_template_id -> id" unified_raw_events }o--|| unified_batch_operations : "batch_operation_id -> id" unified_template_instantiations ||--|| unified_market_groups : "group_id -> id" unified_template_instantiations ||--|| unified_group_templates : "template_id -> id"