learning_ai_invt_trdg/backend/ORDER_STATUS_SYNC.md

Order Status Synchronization - Solution Documentation

Problem Statement

Orders were getting stuck in pending_new status indefinitely, causing stale data in the dashboard. This happened because:

1. One-way data flow: Bot → Database (no sync back)
2. No status updates on fill: When orders were filled, the database was never updated
3. No background reconciliation: No periodic check to sync actual order statuses from the exchange

Solution Overview

We implemented a three-layer solution to handle stale order statuses:

1. Immediate Updates (Real-time Fix)

Files Modified:

• src/services/TradeExecutor.ts

Changes:

• Added updateOrderStatus() call when orders are filled (previously only called on cancel/reject)
• Added status update for both entry and exit orders
• Ensures database is updated immediately when order status changes

// After order is verified as filled
supabaseService.updateOrderStatus?.(order.id, verifiedOrder.status || 'filled', new Date());

2. Background Sync Service (Periodic Reconciliation)

Files Created:

• src/services/OrderStatusSyncService.ts

Files Modified:

• src/services/SupabaseService.ts - Added getStaleOrders() method
• src/index.ts - Integrated sync service into main bot

How it works:

1. Runs every 5 minutes in the background
2. Queries database for orders in pending_new status older than 5 minutes
3. Checks actual status on the exchange via exchange.getOrder()
4. Updates database with real status
5. Marks very old orders (>24h) as unknown if not found on exchange

Key Features:

• Non-blocking (runs in background)
• Handles up to 100 stale orders per sync
• Graceful error handling
• Detailed logging for monitoring

3. Visual Indicators (User Awareness)

Files Modified:

• src/tabs/PositionsTab.tsx (Dashboard)

Features:

• Warning banner at top when stale orders detected
• Yellow badge on individual stale orders (>5 min old)
• Warning icon (⚠️) next to stale order status
• Real-time age calculation

Architecture

┌─────────────────────────────────────────────────────────────┐
│                     Trading Bot Flow                         │
└─────────────────────────────────────────────────────────────┘

1. Order Placed
   ↓
2. TradeExecutor.openPosition()
   ↓
3. waitForFill() - Poll exchange for status
   ↓
4. ✅ NEW: updateOrderStatus() - Update DB immediately
   ↓
5. Track position locally

┌─────────────────────────────────────────────────────────────┐
│                Background Sync Service                       │
└─────────────────────────────────────────────────────────────┘

Every 5 minutes:
1. Query DB for pending_new orders > 5 min old
   ↓
2. For each order:
   - Check exchange.getOrder()
   - Get actual status
   - Update DB
   ↓
3. Mark >24h orders as 'unknown' if not found

┌─────────────────────────────────────────────────────────────┐
│                    Dashboard Display                         │
└─────────────────────────────────────────────────────────────┘

1. Fetch orders from DB
   ↓
2. Calculate age for each order
   ↓
3. Detect stale orders (pending_new > 5 min)
   ↓
4. Show warning banner + visual indicators

Database Schema Assumptions

The solution assumes the orders table has:

• id or order_id - Primary key
• status - Order status (pending_new, filled, canceled, etc.)
• created_at - Timestamp when order was created
• updated_at - Timestamp of last update
• filled_at - Timestamp when order was filled (optional)

Configuration

Sync Interval

Default: 5 minutes

To change, modify in src/index.ts:

const orderSyncService = new OrderStatusSyncService(
    dataExchange, 
    10 * 60 * 1000  // 10 minutes
);

Stale Threshold

Default: 5 minutes

To change, modify in src/services/SupabaseService.ts:

async getStaleOrders(staleThresholdMinutes: number = 10) // 10 minutes

Manual Cleanup

For one-time cleanup of very old stale orders:

# Run the cleanup script
npm run cleanup-stale-orders

This will mark all orders >24 hours old in pending_new status as unknown.

Monitoring & Logs

Look for these log messages:

Successful Sync

[OrderSync] Found 5 stale orders to check
[OrderSync] Updating order abc123: pending_new → filled
[OrderSync] Sync complete: 5 updated, 0 not found on exchange, 0 failed

No Stale Orders

[OrderSync] No stale orders found

Errors

[OrderSync] Failed to sync order abc123: Order not found on exchange
[Supabase] Error fetching stale orders: <error message>

Testing

Test Immediate Updates

1. Place a new order via the bot
2. Check database - should see pending_new initially
3. Wait for order to fill (~3-30 seconds)
4. Check database - should see filled status

Test Background Sync

1. Manually set an order to pending_new in DB (that's actually filled)
2. Wait 5 minutes
3. Check logs for sync activity
4. Verify order status updated to filled

Test Dashboard Indicators

1. Create a stale order (pending_new > 5 min)
2. Open dashboard
3. Should see:
   • Yellow warning banner at top
   • Yellow badge on the order
   • ⚠️ icon next to status

Troubleshooting

Orders still showing as pending_new after sync

Possible causes:

1. Exchange doesn't support getOrder() API
2. Order ID mismatch between bot and exchange
3. Supabase credentials not configured

Solution:

• Check logs for specific error messages
• Verify exchange connector implements getOrder()
• Confirm order IDs match between systems

Sync service not running

Check:

# Look for this in logs on bot startup:
[OrderSync] Background order status sync service started

If missing:

• Verify OrderStatusSyncService is imported in index.ts
• Check for startup errors

Database not updating

Possible causes:

1. Supabase credentials missing/invalid
2. Table permissions issue
3. Field name mismatch (id vs order_id)

Solution:

• Check Supabase connection logs
• Verify table has both id and order_id columns (or update code)
• Test with manual query

Performance Considerations

• Sync batch size: Limited to 100 orders per sync to avoid overload
• Sync frequency: 5 minutes balances freshness vs API rate limits
• Exchange API calls: One call per stale order (consider rate limits)

Future Enhancements

1. WebSocket status updates - Real-time order status from exchange
2. Retry logic - Exponential backoff for failed syncs
3. Metrics dashboard - Track sync success rate, stale order trends
4. Alert on persistent stale orders - Notify if orders stay stale >1 hour
5. Bulk status check - If exchange supports batch order queries

Related Files

Core Implementation

• src/services/TradeExecutor.ts - Immediate status updates
• src/services/OrderStatusSyncService.ts - Background sync
• src/services/SupabaseService.ts - Database queries
• src/index.ts - Service initialization

Dashboard

• src/tabs/PositionsTab.tsx - Visual indicators

Utilities

• src/scripts/cleanupStaleOrders.ts - Manual cleanup

Summary

This solution ensures order statuses are always accurate through:

1. ✅ Immediate updates when orders fill
2. ✅ Background reconciliation every 5 minutes
3. ✅ Visual warnings for users when stale data detected
4. ✅ Manual cleanup tools for maintenance

Result: No more stale pending_new orders! 🎉