pal-e-docs

PostgreSQL Architecture: Prediction Assistant

arch-postgres Architecture

architecture active
Project

PostgreSQL Architecture: Kalshi Assistant

Database schema and storage architecture. Answers: what are the tables and how does the strategy engine use them?

Diagram




          
  
Components



          
  

    

        
          

              Component
              Purpose
              Notes
          

        
      
          

              users
              Keycloak-authenticated user reference
              Maps keycloak_sub to local user; no password stored locally
          

          

              credentials
              Encrypted Kalshi API credentials per user
              RSA private key encrypted at rest via Rails credentials
          

          

              strategies
              Strategy definitions (watchdog, stacking)
              Polymorphic via strategy_type + jsonb parameters; avoids STI
          

          

              watchdog_configs
              Threshold parameters for watchdog auto-buy
              buy_threshold=0.85, take_profit=0.95, cut_loss=0.80
          

          

              sizing_configs
              YES/NO allocation for Option D sizing
              Default: yes_pct=83, no_pct=17; per-strategy override
          

          

              portfolios
              Daily trading session container
              Scoped to trading_date; one active per day per strategy
          

          

              trades
              Individual order records with Kalshi reconciliation
              Tracks kalshi_order_id; pnl computed on settlement
          

          

              markets
              Cached Kalshi market data
              TTL-based refresh; indexed on ticker and series_ticker
          

          

              market_scans
              Audit log of polling cycles
              Tracks markets_found and above_threshold per scan
          

      
    

  



          
  
Key Decisions



          
  
    
      
  • CNPG shared cluster with 4 databases — primary (app data), cache (Solid Cache), queue (Solid Queue), cable (Solid Cable); follows Rails 8 Solid stack convention on shared CloudNativePG cluster
    • 
      
  • Strategy polymorphism via strategy_type + jsonb — avoids STI complexity; keeps schema simple while supporting future strategy types beyond watchdog and stacking
    • 
      
  • Market data cached in PostgreSQL not Redis — simpler ops; strategy engine needs SQL joins between markets and thresholds for filtering
    • 
      
  • Credentials encrypted at rest — RSA private keys are highly sensitive; validated on save via test API call to Kalshi demo environment
    • 
      
  • Portfolio scoped to trading_date — enforces same-day-only rule at database level; prevents stale multi-day positions from accumulating
    • 
      
  • Decimal types for all monetary and price columns — avoids floating-point rounding errors in trade PnL calculations and Option D sizing math
    •