# POS Reconciliation Diagnostic Report

**Event:** Mohamed Abdo NYE 2025
**Generated:** 5 January 2026
**Methodology:** Amount+Date matching for POS orders without Revolut IDs

---

## EXECUTIVE SUMMARY

| Category | Count | Amount | Status |
|----------|-------|--------|--------|
| **Exact matches found** | 42 | £86,600 | ✅ Can link |
| POS orders still unmatched | 34 | £102,950 | ⚠️ Needs investigation |
| Pre-event orphaned | 22 | £34,301 | ⚠️ Includes refunds |
| Event-day orphaned unmatched | 54 | £51,911 | ⚠️ Extra Revolut payments |

---

## MATCHING METHODOLOGY

### Exact Match Criteria
1. Amount matches exactly (within £0.01)
2. Same calendar date
3. Revolut payment not already linked to another DB order

### Results
- **42 DB POS orders** successfully matched to orphaned Revolut payments
- **£86,600** can now be verified against Revolut API
- SQL statements generated for linking

---

## DAILY RECONCILIATION

| Date | DB POS | DB £ | Revolut Orphaned | Revolut £ | Gap |
|------|--------|------|------------------|-----------|-----|
| 2025-11-09-14 | 0 | £0 | ~40 | £73,300* | -£73,300 |
| 2025-12-08-27 | 0 | £0 | ~6 | £12,800* | -£12,800 |
| 2025-12-28 | 3 | £9,250 | 6 | £8,361 | +£889 |
| 2025-12-29 | 29 | £69,650 | 37 | £56,000 | +£13,650 |
| 2025-12-30 | 43 | £109,550 | 49 | £73,150 | +£36,400 |
| 2025-12-31 | 1 | £1,100 | 4 | £1,000 | +£100 |

*Pre-event includes test transactions and already-identified payments (Talal, Arwa)

### Key Observations

1. **Pre-event Revolut payments (Nov-Dec 27):**
   - £51,800 = Talal + Arwa (already identified, Orders 530 & 568)
   - £34,301 = Other pre-event payments (some marked as refunds)
   - No matching POS orders in DB for these dates

2. **Event days (Dec 28-31):**
   - DB shows £189,550 in POS orders
   - Revolut has £138,511 in orphaned payments
   - Gap of +£51,039 (DB higher than Revolut)

---

## PRE-EVENT ORPHANED ANALYSIS

### Refunds/Errors (should be excluded from revenue)
| Amount | Description | Date |
|--------|-------------|------|
| £3,000 | Webhook failure recovery - refund | 2025-12-09 |
| £1,800 | Customer Requested Refund | 2025-12-10 |
| £1,400 | Customer requested refund | 2025-12-15 |
| £700 | Customer Request Refund | 2025-12-16 |
| £1,100 | Booking error - seats not available | 2025-12-21 |
| £1,100 | Booking error - seats not available | 2025-12-21 |
| **£9,100** | **Total refunds/errors** | |

### Early Bookings (Nov 13-14) - Not linked to DB
| Amount | Cardholder | Description |
|--------|------------|-------------|
| £3,000 | NO CARDHOLDER | Global Gala Event Booking |
| £2,400 | abdulraman alqurashi | Global Gala Event Booking |
| £2,400 | abdulraman alqurashi | Global Gala Event Booking |
| £2,400 | abdulraman alqurashi | Global Gala Event Booking |
| £2,000 | ahmed althani | Global Gala Event Booking |
| £1,950 | Abdulaziz almeshari | Global Gala Event Booking |
| £1,800 | Meshari Alkharashi | Global Gala Event Booking |
| £1,500 | Awad Alharthi | Global Gala Event Booking |
| £1,400 | Raghad Alruwaudan | Global Gala Event Booking |
| £1,200 | Saja Ghaleb | Global Gala Event Booking |
| £750 | Mohammad Alajmi | Global Gala Event Booking |
| £700 | Mohammd Alderei | Global Gala Event Booking |
| **£21,500** | | **Not in DB - needs investigation** |

---

## AMOUNT DISTRIBUTION ANALYSIS

### DB POS Orders (without Revolut ID)
- **Min:** £550
- **Max:** £7,800
- **Median:** £2,000

### Revolut Orphaned Payments
- **Min:** £0.01 (test)
- **Max:** £25,900 (Talal/Arwa)
- **Median:** £1,000

### Observation
DB POS orders tend to be higher-value (median £2,000) than Revolut orphaned (median £1,000). This suggests:
1. Some high-value POS orders may not have been processed through Revolut
2. Or multiple smaller Revolut transactions per single DB order

---

## UNMATCHED DB POS ORDERS (£102,950)

These 34 orders have no matching Revolut payment by amount+date:

| Order | Amount | Customer | Date |
|-------|--------|----------|------|
| 724 | £7,800 | Lama Almusallam | 2025-12-30 |
| 752 | £6,650 | Ahmed Abuobaid | 2025-12-30 |
| 729 | £5,550 | maryam al kuwari | 2025-12-30 |
| 744 | £5,550 | atheer | 2025-12-30 |
| 675 | £4,350 | Manal Zaid | 2025-12-29 |
| 679 | £4,350 | aalaa khelaidi | 2025-12-29 |
| 688 | £4,350 | homood almutairi | 2025-12-29 |
| 691 | £4,350 | Mohammed Alothman | 2025-12-29 |
| 674 | £4,000 | Razan al alwani | 2025-12-29 |
| 718 | £4,000 | Yasser al saleh | 2025-12-30 |
| 656 | £3,700 | Turkey Mohamed | 2025-12-28 |
| 657 | £3,700 | awatif alsabah | 2025-12-28 |
| 702 | £3,700 | Mohamed Bader | 2025-12-29 |
| 745 | £3,700 | muteb alotaibi | 2025-12-30 |
| 747 | £3,700 | tamim alabdulla | 2025-12-30 |

### Possible Explanations
1. **Different payment terminal** - Not processed through Revolut Reader
2. **Manual DB entry** - Orders created without actual card payment
3. **Split payments** - Large order paid in multiple smaller transactions
4. **Cash + Card mix** - Partially cash, partially card

---

## UNMATCHED REVOLUT PAYMENTS (Event Days)

54 Revolut payments (£51,911) on event days with no matching DB order:

These are likely:
1. **Bar/merchandise purchases** - Not event tickets
2. **Walk-up sales** - Not entered in ticketing system
3. **Multiple taps** - Single order paid with multiple card taps

---

## RECONCILIATION SUMMARY

```
DB POS ORDERS (no Revolut ID):           £189,550  (76 orders)
  ├─ Exact matched to Revolut:           -£86,600  (42 orders) ✅
  └─ Still unmatched:                    £102,950  (34 orders) ⚠️

ORPHANED REVOLUT PAYMENTS:               £224,612  (148 payments)
  ├─ Test transactions:                  -£0.51    (28 payments)
  ├─ Already identified (530, 568):      -£51,800  (2 payments) ✅
  ├─ Exact matched to DB:                -£86,600  (42 payments) ✅
  ├─ Pre-event (refunds/errors):         -£9,100   (6 payments)
  ├─ Pre-event (other):                  -£25,201  (16 payments) ⚠️
  └─ Event days unmatched:               £51,911   (54 payments) ⚠️

NET DISCREPANCY (DB - Revolut):          +£51,039
```

---

## RECOMMENDATIONS

### Immediate Actions
1. **Apply 42 exact matches** - Link DB orders to Revolut IDs (SQL provided)
2. **Apply previous corrections** - Orders 522, 523, 568, 530

### Investigation Needed
1. **34 unmatched DB POS orders (£102,950)**
   - Check if processed through different terminal
   - Verify if orders were actually paid
   - Look for split payment patterns

2. **Pre-event cardholders (£21,500)**
   - Search DB for customers: abdulraman alqurashi, ahmed althani, Abdulaziz almeshari, etc.
   - These may be linked to existing orders under different IDs

3. **Event-day extra Revolut (£51,911)**
   - Likely bar/merchandise sales
   - Confirm with venue if separate POS for drinks

### System Improvements
1. Ensure POS always captures Revolut payment ID
2. Add real-time reconciliation alerts
3. Daily automated matching reports

---

*Report generated via Revolut API forensic analysis*