robinhood/TRADE_PERSISTENCE_IMPLEMENTATION.md

# Trade Persistence Implementation Guide

## Overview
This guide details how to integrate the new **persistent trading API** that saves trades to the database, ensuring data survives browser refresh and server restart.

---

## Backend Changes (✅ Complete)

### 1. New File: `backend/app/api/trading_persistent.py`
- **Purpose**: Replace in-memory trading with database-backed persistence
- **Key Features**:
  - All trades saved to `trades` table
  - Position state saved to `positions` table
  - Simulation state saved to `simulations` table
  - Automatic creation of simulation on first use
  - Full CRUD operations for portfolio management

### 2. Updated: `backend/app/main.py`
- Changed import from `trading` to `trading_persistent as trading`
- All existing API endpoints remain the same (`/api/trading/...`)
- No breaking changes to API interface

### 3. Database Models (Already Exist)
- `Simulation`: Tracks overall trading session
- `Trade`: Individual trade records with P&L
- `Position`: Current position state
- All relationships configured correctly

---

## API Endpoints

### 1. **POST /api/trading/execute**
Execute a trade and save to database.

**Request:**
```json
{
  "action": "BUY" | "SELL",
  "quantity": 1.5,
  "price": 2650.50,
  "symbol": "XAU/USD",
  "notes": "Optional trade notes",
  "stop_loss": 2640.0,  // Optional
  "take_profit": 2670.0  // Optional
}
```

**Response:**
```json
{
  "trade": {
    "id": 123,
    "action": "BUY",
    "quantity": 1.5,
    "price": 2650.50,
    "total": 3975.75,
    "pnl": null,
    "timestamp": 1704841200
  },
  "portfolio": {
    "cash": 96024.25,
    "initial_capital": 100000.0,
    "position": {
      "symbol": "XAU/USD",
      "quantity": 1.5,
      "avg_price": 2650.50,
      "current_price": 2650.50,
      "unrealized_pnl": 0.0,
      "unrealized_pnl_percent": 0.0
    },
    "trades": [...],
    "equity_history": [...],
    "total_pnl": 0.0,
    "total_pnl_percent": 0.0
  }
}
```

### 2. **GET /api/trading/portfolio**
Get current portfolio state from database.

**Response:** Same `portfolio` object as above

### 3. **POST /api/trading/reset**
Reset simulation to initial state (deletes all trades/positions).

**Response:**
```json
{
  "message": "Simulation reset successfully",
  "portfolio": { /* New empty portfolio */ }
}
```

### 4. **GET /api/trading/history?limit=100**
Get trade history.

**Response:**
```json
[
  {
    "id": 123,
    "action": "BUY",
    "quantity": 1.5,
    "price": 2650.50,
    "total": 3975.75,
    "pnl": null,
    "timestamp": 1704841200
  },
  ...
]
```

### 5. **GET /api/trading/stats**
Get trading statistics.

**Response:**
```json
{
  "total_trades": 25,
  "winning_trades": 15,
  "losing_trades": 10,
  "win_rate": 60.0,
  "total_pnl": 2500.50,
  "total_pnl_percent": 2.5,
  "total_profit": 5000.0,
  "total_loss": 2500.0,
  "profit_factor": 2.0,
  "current_capital": 102500.50,
  "initial_capital": 100000.0
}
```

---

## Frontend Integration

### 1. **New File: `frontend/src/services/tradingAPI.ts`** (✅ Created)
API service layer for communicating with persistent backend.

**Key Functions:**
```typescript
executeTradeAPI(trade: TradeRequest): Promise<TradeResponse>
getPortfolioAPI(): Promise<PortfolioState>
resetSimulationAPI(): Promise<{ message: string; portfolio: PortfolioState }>
getTradeHistoryAPI(limit?: number): Promise<Array<any>>
getTradingStatsAPI(): Promise<TradingStats>
convertBackendPortfolio(backendPortfolio, currentPrice): Portfolio
```

### 2. **Updates Needed in `frontend/src/App.tsx`**

#### Step 1: Import the API service
```typescript
import { 
  executeTradeAPI, 
  getPortfolioAPI, 
  resetSimulationAPI, 
  convertBackendPortfolio 
} from './services/tradingAPI'
```

#### Step 2: Add loading state
```typescript
const [isLoadingTrade, setIsLoadingTrade] = useState(false)
```

#### Step 3: Add portfolio loader function
```typescript
const loadPortfolioFromBackend = useCallback(async () => {
  try {
    const backendPortfolio = await getPortfolioAPI()
    const converted = convertBackendPortfolio(backendPortfolio, currentPrice)
    setPortfolio(converted)
    console.log('✅ Portfolio loaded from backend:', converted)
  } catch (error) {
    console.error('Failed to load portfolio from backend:', error)
    setPortfolio(recalcPortfolio(createInitialPortfolio(), currentPrice))
  }
}, [currentPrice])
```

#### Step 4: Load portfolio on mount
```typescript
useEffect(() => {
  if (syncToBackend) {
    loadPortfolioFromBackend()
  }
}, []) // Only run once on mount
```

#### Step 5: Update handleBuy
Replace the existing `handleBuy` function with:
```typescript
const handleBuy = useCallback(async (quantity: number) => {
  if (quantity <= 0 || Number.isNaN(quantity)) return
  
  if (!syncToBackend) {
    // Keep original in-memory logic for backward compatibility
    // ... existing code ...
    return
  }

  // NEW: Backend-persisted logic
  setIsLoadingTrade(true)
  try {
    await executeTradeAPI({
      action: 'BUY',
      quantity,
      price: currentPrice,
      symbol: TRADING_SYMBOL
    })
    
    // Reload portfolio from backend
    const backendPortfolio = await getPortfolioAPI()
    const converted = convertBackendPortfolio(backendPortfolio, currentPrice)
    setPortfolio(converted)
    
    console.log('✅ BUY trade executed and synced')
  } catch (error: any) {
    console.error('❌ Trade execution failed:', error)
    if (error.response?.data?.detail) {
      alert(`Trade failed: ${error.response.data.detail}`)
    } else {
      alert('Trade execution failed. Please try again.')
    }
  } finally {
    setIsLoadingTrade(false)
  }
}, [currentPrice, syncToBackend])
```

#### Step 6: Update handleSell
Similar pattern to handleBuy:
```typescript
const handleSell = useCallback(async (quantity: number, reason = 'Manual exit') => {
  if (!syncToBackend) {
    // Keep original in-memory logic
    // ... existing code ...
    return
  }

  setIsLoadingTrade(true)
  try {
    const currentPortfolio = await getPortfolioAPI()
    if (!currentPortfolio.position) {
      alert('No open position to close')
      return
    }
    
    const size = Math.min(quantity, currentPortfolio.position.quantity)
    
    await executeTradeAPI({
      action: 'SELL',
      quantity: size,
      price: currentPrice,
      symbol: TRADING_SYMBOL,
      notes: reason
    })
    
    const backendPortfolio = await getPortfolioAPI()
    const converted = convertBackendPortfolio(backendPortfolio, currentPrice)
    setPortfolio(converted)
    
    console.log('✅ SELL trade executed and synced')
  } catch (error: any) {
    console.error('❌ Trade execution failed:', error)
    alert(`Trade failed: ${error.response?.data?.detail || 'Please try again'}`)
  } finally {
    setIsLoadingTrade(false)
  }
}, [currentPrice, syncToBackend])
```

#### Step 7: Update handleReset
```typescript
const handleReset = useCallback(async () => {
  if (!syncToBackend) {
    setPortfolio(recalcPortfolio(createInitialPortfolio(), currentPrice))
    setAiAnalysis(null)
    return
  }

  setIsLoadingTrade(true)
  try {
    const response = await resetSimulationAPI()
    const converted = convertBackendPortfolio(response.portfolio, currentPrice)
    setPortfolio(converted)
    setAiAnalysis(null)
    console.log('✅ Simulation reset and synced')
  } catch (error) {
    console.error('❌ Reset failed:', error)
    alert('Failed to reset simulation. Please try again.')
  } finally {
    setIsLoadingTrade(false)
  }
}, [currentPrice, syncToBackend])
```

#### Step 8: Add loading indicator (optional but recommended)
In your Trade panel component, disable buttons during trades:
```typescript
<button 
  onClick={() => handleBuy(quantity)} 
  disabled={isLoadingTrade}
>
  {isLoadingTrade ? 'Processing...' : 'Buy'}
</button>
```

---

## Testing Checklist

### Backend Tests
1. ✅ Start backend: `cd backend && ./start.sh`
2. ✅ Check database tables exist: `simulations`, `trades`, `positions`
3. ✅ Test endpoints with curl or Postman:
   ```bash
   # Get portfolio
   curl http://localhost:8001/api/trading/portfolio
   
   # Execute trade
   curl -X POST http://localhost:8001/api/trading/execute \
     -H "Content-Type: application/json" \
     -d '{"action":"BUY","quantity":1.5,"price":2650.50,"symbol":"XAU/USD"}'
   
   # Reset
   curl -X POST http://localhost:8001/api/trading/reset
   ```

### Frontend Tests
1. ✅ Ensure `syncToBackend` is enabled (toggle in UI)
2. ✅ Refresh browser → Portfolio should load from DB
3. ✅ Execute BUY trade → Should save to DB
4. ✅ Execute SELL trade → Should update DB
5. ✅ Refresh browser → Trades should persist
6. ✅ Restart backend → Trades should still exist
7. ✅ Reset simulation → Should clear all trades

### Integration Tests
1. ✅ Execute multiple trades
2. ✅ Restart backend server
3. ✅ Refresh browser
4. ✅ Verify all trades are present
5. ✅ Verify P&L is correct
6. ✅ Verify equity history is preserved

---

## Rollback Plan

If issues arise, you can revert by:
1. Change `backend/app/main.py`: `from app.api import trading` (remove `_persistent`)
2. Restart backend
3. In-memory trading will be restored

---

## Key Benefits

✅ **Persistence**: Trades survive browser refresh and server restart
✅ **Data Integrity**: All trades stored in relational database with ACID guarantees
✅ **Audit Trail**: Complete history of all trades with timestamps
✅ **Statistics**: Real-time trading stats from database queries
✅ **Scalability**: Ready for multi-user support (user_id field exists)
✅ **Backward Compatible**: In-memory mode still available when `syncToBackend=false`

---

## Troubleshooting

### "Trade failed: Insufficient funds"
- Check `current_capital` in database: `SELECT * FROM simulations;`
- Verify trade total doesn't exceed available cash

### "No open position to close"
- Check positions table: `SELECT * FROM positions;`
- Ensure position exists before selling

### Portfolio not loading on refresh
- Check backend logs for errors
- Verify API endpoint returns 200 OK
- Check browser console for CORS or network errors

### Database locked errors
- Ensure only one backend instance is running
- Check for zombie processes: `ps aux | grep python`
- Kill if needed: `pkill -f "uvicorn app.main:app"`

---

## Next Steps

1. **Implement frontend updates** (follow steps in Frontend Integration section)
2. **Test thoroughly** (use Testing Checklist)
3. **Monitor logs** for any errors
4. **Add loading indicators** for better UX
5. **Consider adding optimistic updates** (update UI immediately, sync in background)

---

## File Reference

- ✅ `backend/app/api/trading_persistent.py` - New persistent trading API
- ✅ `backend/app/main.py` - Updated to use persistent trading
- ✅ `backend/app/models/models.py` - Database models (already complete)
- ✅ `backend/app/db/database.py` - Database connection (already complete)
- ✅ `frontend/src/services/tradingAPI.ts` - API service layer
- ⏳ `frontend/src/App.tsx` - Needs updates (follow guide above)

---

## Support

If you encounter any issues:
1. Check backend logs: `tail -f backend/server.log`
2. Check browser console for errors
3. Verify database state: SQLite browser or `sqlite3 backend/test_phase1.db`
4. Review this guide for troubleshooting steps