# Soft Delete Architecture for Trading Systems

## Why This Error Happens

**Error:** `SQLSTATE[42S22]: Unknown column 'trade_histories.deleted_at'`

**Root Cause:**
1. TradeHistory model uses `use SoftDeletes;` trait
2. The trait automatically adds a global scope: `WHERE deleted_at IS NULL`
3. The trade_histories table was created without the `deleted_at` column
4. Eloquent tries to apply the global scope but the column doesn't exist

**Why It Occurred:**
- Migration was created without `softDeletes()` initially
- Table was created without the column
- Model was updated to use SoftDeletes trait
- Database schema doesn't match model expectations

## Solution Applied

Created migration: `2026_05_11_042309_add_deleted_at_to_trade_histories_table.php`

```php
public function up(): void
{
    Schema::table('trade_histories', function (Blueprint $table) {
        $table->softDeletes();
    });
}

public function down(): void
{
    Schema::table('trade_histories', function (Blueprint $table) {
        $table->dropSoftDeletes();
    });
}
```

## Why Soft Delete is Recommended for Trading Systems

### 1. Audit Trail
- Every trade deletion is recorded with timestamp
- Prevents accidental permanent data loss
- Maintains complete transaction history
- Required for regulatory compliance

### 2. Trade Recovery
- Restore accidentally deleted trades
- Recover from sync errors
- Rollback incorrect settlements
- Debug data integrity issues

### 3. Settlement Rollback
- If profit distribution needs reversal
- Restore trades to active state
- Recalculate distributions
- Maintain financial accuracy

### 4. Historical Compliance
- Financial regulations require complete records
- Audit trails must show all data modifications
- Soft deletes provide reversible actions
- Supports data retention policies

## Eloquent SoftDeletes Mechanics

### 1. Trait Usage
```php
class TradeHistory extends Model
{
    use SoftDeletes;
}
```

### 2. Global Query Scope
When you query TradeHistory, Eloquent automatically adds:
```sql
SELECT * FROM trade_histories WHERE deleted_at IS NULL
```

### 3. Soft Delete Operation
```php
$trade->delete(); // Sets deleted_at = NOW(), doesn't actually delete row
```

### 4. Restore Operation
```php
$trade->restore(); // Sets deleted_at = NULL
```

### 5. Force Delete
```php
$trade->forceDelete(); // Actually deletes the row from database
```

### 6. Include Deleted Records
```php
$trades = TradeHistory::withTrashed()->get(); // Includes soft deleted
$trades = TradeHistory::onlyTrashed()->get(); // Only soft deleted
```

## Relationship Between Components

```
Model (SoftDeletes Trait)
    ↓
Global Query Scope (WHERE deleted_at IS NULL)
    ↓
Database Column (deleted_at TIMESTAMP NULL)
    ↓
Automatic Timestamp Management
```

## Database Schema

### With SoftDeletes
```sql
CREATE TABLE trade_histories (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    mt5_account_id BIGINT UNSIGNED NOT NULL,
    ticket VARCHAR(255) NOT NULL,
    symbol VARCHAR(50) NOT NULL,
    type ENUM('BUY', 'SELL') NOT NULL,
    volume DECIMAL(10, 2) NOT NULL,
    open_price DECIMAL(15, 5) NOT NULL,
    close_price DECIMAL(15, 5) NOT NULL,
    profit DECIMAL(15, 2) NOT NULL,
    swap DECIMAL(15, 2) DEFAULT 0,
    commission DECIMAL(15, 2) DEFAULT 0,
    open_time TIMESTAMP NULL,
    close_time TIMESTAMP NULL,
    comment VARCHAR(255) NULL,
    synced_at TIMESTAMP NULL,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    deleted_at TIMESTAMP NULL,  ← Soft delete column
    FOREIGN KEY (mt5_account_id) REFERENCES mt5_accounts(id) ON DELETE CASCADE
);
```

## Best Practice Architecture

### 1. Audit Trail Strategy
```php
// Log every soft delete
TradeHistory::deleting(function ($trade) {
    Log::info("Trade soft deleted", [
        'ticket' => $trade->ticket,
        'deleted_at' => now(),
        'reason' => 'User request'
    ]);
});
```

### 2. Trade Recovery Flow
```php
// Restore deleted trade
$deletedTrade = TradeHistory::onlyTrashed()
    ->where('ticket', $ticket)
    ->first();

if ($deletedTrade) {
    $deletedTrade->restore();
    Log::info("Trade restored", ['ticket' => $ticket]);
}
```

### 3. Settlement Rollback
```php
// Rollback profit distribution
DB::transaction(function () use ($distributionId) {
    $distribution = ProfitDistribution::find($distributionId);
    
    // Restore trades
    foreach ($distribution->relatedTrades as $trade) {
        $trade->restore();
    }
    
    // Reverse distribution
    $distribution->delete();
    
    Log::info("Settlement rolled back", ['distribution_id' => $distributionId]);
});
```

### 4. Historical Compliance
```php
// Archive soft deleted trades after 7 years
TradeHistory::onlyTrashed()
    ->where('deleted_at', '<', now()->subYears(7))
    ->chunk(1000, function ($trades) {
        foreach ($trades as $trade) {
            ArchiveTrade::create($trade->toArray());
            $trade->forceDelete(); // Permanent delete after archival
        }
    });
```

## Queue Restart Commands

### Stop Current Worker
```bash
# Press Ctrl+C to stop current queue worker
```

### Start New Worker
```bash
cd d:/Trade/FTP
php artisan queue:work
```

### Worker with Options
```bash
php artisan queue:work --sleep=3 --tries=3 --timeout=3600
```

## Queue Retry Commands

### List Failed Jobs
```bash
php artisan queue:failed
```

### Retry All Failed Jobs
```bash
php artisan queue:retry all
```

### Retry Specific Job
```bash
php artisan queue:retry <job-id>
```

### Flush Failed Jobs
```bash
php artisan queue:flush
```

### Clear All Jobs
```bash
php artisan queue:clear
```

## Testing Checklist

- [ ] deleted_at column exists in trade_histories table
- [ ] TradeHistory model uses SoftDeletes trait
- [ ] Migration ran successfully
- [ ] Laravel cache cleared
- [ ] Queue worker processes jobs without errors
- [ ] Soft delete operations work correctly
- [ ] Restore operations work correctly
- [ ] withTrashed() scope works
- [ ] onlyTrashed() scope works
- [ ] Force delete works

## Troubleshooting

### Issue: Column still doesn't exist after migration
**Solution:** Check if migration was actually run: `php artisan migrate:status`

### Issue: Query still fails
**Solution:** Clear config cache: `php artisan config:clear`

### Issue: Global scope not applied
**Solution:** Verify model uses trait: `use SoftDeletes;`

### Issue: Can't restore deleted record
**Solution:** Use `withTrashed()` to find deleted record first

## Production Considerations

### 1. Data Retention
- Keep soft deleted records for 7 years (regulatory)
- Archive to cold storage after retention period
- Implement cleanup job for old records

### 2. Performance
- Add index on deleted_at column for faster queries
- Monitor query performance with soft deletes
- Consider partitioning by deleted_at status

### 3. Monitoring
- Monitor soft delete rate
- Alert on unusual deletion patterns
- Track restore operations
- Monitor disk space usage

### 4. Backup Strategy
- Include soft deleted records in backups
- Test restore procedures with soft deletes
- Maintain point-in-time recovery capability

## Complete Migration Example

### Initial Migration (Correct)
```php
Schema::create('trade_histories', function (Blueprint $table) {
    $table->id();
    $table->foreignId('mt5_account_id')->constrained()->onDelete('cascade');
    $table->string('ticket');
    // ... other columns
    $table->timestamps();
    $table->softDeletes(); // ← Include from the start
});
```

### Add Column Later (Current Fix)
```php
Schema::table('trade_histories', function (Blueprint $table) {
    $table->softDeletes();
});
```

### Rollback
```php
Schema::table('trade_histories', function (Blueprint $table) {
    $table->dropSoftDeletes();
});
```

## Next Steps

1. Test queue worker: `php artisan queue:work`
2. Run Python sync: `python mt5_sync.py`
3. Verify jobs process successfully
4. Check Laravel logs: `tail -f storage/logs/laravel.log`
5. Verify trade history persists correctly