# Audit Session Summary - 5 January 2026

**Event:** Mohamed Abdo NYE 2025
**Venue:** The Great Hall, JW Marriott Grosvenor House Ballroom
**Methodology:** Revolut API as Source of Truth

---

## EXECUTIVE SUMMARY

### Final Verified Revenue

| Source | Amount | Status |
|--------|--------|--------|
| **Revolut Card (API verified)** | **£812,462.53** | ✅ Source of truth |
| Bank Transfers | £109,967.60 | ⚠️ Client verify |
| Cash | £26,000.00 | Trust accounting |
| Split Payments | £16,400.00 | Partial card |
| **GROSS REVENUE** | **£964,830.13** | |
| Estimated Fees (~0.9%) | -£8,932.35 | |
| **NET REVENUE** | **£955,897.78** | |
| Complimentary (written off) | £7,550.00 | Non-revenue |

### Key Findings

1. **DB Over-reports by ~£73k** due to duplicates and test orders
2. **MBS Terminal shows ~£250k but Revolut shows £224k** - normal terminal overreporting
3. **£98,600 in DB orders have no Revolut match** - different terminal or not charged
4. **£47,561 in Revolut has no DB match** - door sales not entered in system

---

## REVOLUT API ANALYSIS

### Total Revolut Payments (60 days)
- **500 completed payments**
- **£812,462.53 total**

### Breakdown

| Category | Payments | Amount |
|----------|----------|--------|
| Matched to DB (UUID) | 171 | £324,050 |
| Matched to DB (hold_token) | 182 | £264,900 |
| **Subtotal Matched** | **353** | **£588,950** |
| Orphaned (event days) | 96 | £138,511 |
| Orphaned (pre-event) | 24 | £86,101 |
| Test transactions | 28 | £0.53 |
| **TOTAL** | **500** | **£812,463** |

---

## DB ORDER BREAKDOWN

### By Channel (486 total orders)

| Channel | Count | Amount |
|---------|-------|--------|
| Admin (Bank Transfers) | 21 | £109,967.60 |
| POS Sales | 94 | £231,950.00 |
| - Credit Card | 75 | £187,700.00 |
| - Debit Card | 1 | £1,850.00 |
| - Cash | 13 | £26,000.00 |
| - Split | 5 | £16,400.00 |
| Online Sales | 368 | £696,325.00 |
| Complimentary | 3 | £7,550.00 |
| **TOTAL** | **486** | **£1,045,792.60** |

---

## IDENTIFIED ISSUES

### 1. Duplicate Orders (£51,800)
- **Orders 522, 523** are empty duplicates of Order 530
- Same customer (Talal), same timestamp, 0 line items
- Order 530 is the valid order with 14 line items

### 2. Test Orders (£4,500)
- **Orders 133, 248** show £4,500 in DB but Revolut charged £0.02
- Customer: Ahmed Yacine (test customer)

### 3. Mislabeled Payment (£25,900)
- **Order 568** labeled as 'stripe' but actually Revolut
- Revolut ID: `6916df3d-525e-a6ac-a649-bcf5907fc4aa`

### 4. Wrong Revolut ID Format (£25,900)
- **Order 530** has Stripe ID in revolut_payment_id field
- Correct Revolut ID: `69171609-480e-adb0-8c67-816037b4fa78`

---

## POS RECONCILIATION

### Summary

| Category | DB | Revolut | Gap |
|----------|-----|---------|-----|
| Card POS Orders | 76 orders, £189,550 | - | - |
| Orphaned (event days) | - | 96 payments, £138,511 | - |
| **Matched by timestamp** | 35 | 35 | £72,250 |
| **Matched by amount** | 44 | 44 | £90,950 |

### Two-Way Mismatch

| Issue | Count | Amount | Explanation |
|-------|-------|--------|-------------|
| **DB ONLY** (no Revolut) | 32 | £98,600 | Different terminal? Never charged? |
| **REV ONLY** (no DB) | 52 | £47,561 | Door sales not entered |

### DB ONLY Amounts (Not in Revolut)
- £7,800 x 1
- £6,650 x 1
- £5,550 x 2
- £4,350 x 4
- £3,700 x 6 (excess)
- £1,850 x 5 (excess)
- £1,100 x 4

### REV ONLY Amounts (Not in DB)
- £500 x 11
- £700 x 5
- £2,100 x 3
- Plus various smaller amounts

---

## MBS TERMINAL COMPARISON

```
MBS Terminal Report:     ~£250,000 (client estimate)
Revolut POS Actual:       £224,612
Overreport:               ~£25,000
```

**Explanation:** MBS terminal shows gross attempts including declined, cancelled, and test transactions. Revolut API shows actual settled payments only.

---

## DB vs REVOLUT VARIANCE

| Item | Amount |
|------|--------|
| DB Card Total | £885,875.00 |
| Less: Duplicates (522, 523) | -£51,800.00 |
| Less: Test Orders (133, 248) | -£4,500.00 |
| **Adjusted DB** | **£829,575.00** |
| Revolut API Total | £812,462.53 |
| **Remaining Gap** | **£17,112.47** |

The £17k gap is likely additional test/cancelled orders or fee variances.

---

## SQL FIXES (Deferred)

All fixes documented in `SQL_FIXES_DEFERRED.sql`:

### Section 1: Critical Fixes (4 orders)
```sql
-- Cancel duplicates
UPDATE orders SET status='cancelled', payment_status='voided' WHERE id IN (522, 523);

-- Fix Order 568 (stripe→revolut)
UPDATE orders SET payment_method='revolut', revolut_payment_id='6916df3d-...' WHERE id=568;

-- Fix Order 530 (add correct Revolut ID)
UPDATE orders SET revolut_payment_id='69171609-...' WHERE id=530;
```

### Section 2: POS Matches (35-44 orders)
Link POS orders to orphaned Revolut payments by timestamp/amount match.

---

## FILES CREATED

### Local (`/Users/charlie/code/showprima/storage/audit/`)

| File | Purpose |
|------|---------|
| `SESSION_SUMMARY_2026-01-05.md` | This summary |
| `AUDIT_SUMMARY.md` | Executive summary |
| `FORENSIC_RECONCILIATION_FINAL.md` | Complete forensic report |
| `CHANNEL_RECONCILIATION_FINAL.md` | Channel breakdown |
| `POS_RECONCILIATION_DIAGNOSTIC.md` | POS analysis |
| `POS_MISSING_ORDERS_CRITICAL.md` | Door sales without orders |
| `SQL_FIXES_DEFERRED.sql` | All SQL corrections (46 statements) |
| `VERIFICATION_CHECKLIST.md` | Bank transfer verification list |
| `CORRECTED_VERIFICATION_REPORT.md` | Verification report |

### Server (`storage/audit/`)

| File | Purpose |
|------|---------|
| `revolut_all_completed.json` | Raw Revolut API data (500 payments) |
| `orphaned_payments.json` | Orphaned payment list |
| `orphaned_with_cardholders.json` | Enriched with cardholder names |
| `pos_reconciliation_report.json` | POS matching by amount+date |
| `pos_revolut_timestamp_matches.json` | POS matching by timestamp |
| `pos_revolut_reconciliation.json` | Full reconciliation data |
| `cardholder_matching_report.json` | Name matching analysis |
| `unmatched_door_sales.json` | 54 orphaned payments list |
| `channel_breakdown_report.json` | Channel analysis |
| `amount_matching.php` | Amount comparison script |

---

## NEXT STEPS

### Immediate Actions
1. [ ] Review SQL fixes with stakeholder approval
2. [ ] Apply fixes to staging first, then production
3. [ ] Backup database before any changes

### Client Verification Needed
1. [ ] Verify 21 bank transfers (£109,967.60)
2. [ ] Confirm what terminal was used for £98,600 in DB-only orders
3. [ ] Investigate 52 door sales (£47,561) without orders

### Process Improvements
1. [ ] Disable "Custom Amount" on Revolut Reader
2. [ ] Require order creation before payment
3. [ ] Real-time reconciliation alerts
4. [ ] Daily automated matching reports

---

## KEY TAKEAWAYS

1. **Revolut is Source of Truth** - £812,462.53 is actual card revenue
2. **DB Over-reports** - Duplicates, test orders, and phantom entries
3. **MBS Over-reports** - Normal terminal behavior (shows attempts not settlements)
4. **Process Gap** - Door sales not always entered into system
5. **Two-Terminal Issue?** - Some DB orders may have used different payment terminal

---

*Session completed 5 January 2026*
*All Revolut data verified against merchant.revolut.com API*