# Schema Analysis: Production vs feature/decomp-sprint

**Date:** 2026-01-23
**Analysis By:** Deploy Agent
**Purpose:** Source of Truth Comparison for Migration Strategy

---

## Executive Summary

**CRITICAL FINDING:** Production and `feature/decomp-sprint` have **divergent migration histories**.

### Key Metrics

| Metric | Production | decomp-sprint | Delta |
|--------|-----------|---------------|-------|
| **Tables** | 137 | 140 | +3 (decomp) |
| **Migrations Run** | 253 | 237 | +16 (prod) |
| **Latest Batch** | 1001 | 58 | +943 batches |
| **Latest Migration** | 2026-01-19 | 2025-12-08 | 42 days ahead |

---

## Schema Differences

### Tables in decomp-sprint NOT in production (NEW)
- `access_logs` - Domain work
- `features` - Feature flag system
- `gala_state_transitions` - GALA domain state machine
- `permission_overrides` - IAM domain
- `template_versions` - VENUE domain versioning
- `whatsapp_logs` - NOTIF domain (different structure than prod)

### Tables in production NOT in decomp (PROD ONLY)
- `orders_backup_20251116` - November backup table
- `seat_reservations_backup_20251116` - November backup table
- `seat_reservations_backup_held_20251116` - November backup table

---

## Migration Divergence (CRITICAL ISSUE)

### Example: WhatsApp Logs Table

**Production:**
- Migration: `2025_12_08_130000_create_whats_app_logs_table`
- Batch: 50
- Structure: Production version

**decomp-sprint:**
- Migration: `2025_12_08_122942_create_whatsapp_logs_table`
- Batch: 53  
- Structure: NOTIF domain version

**Issue:** SAME table, DIFFERENT migrations, DIFFERENT timestamps!

---

## Production Migration Timeline (After decomp divergence)

Production has run 16 additional migrations:

```
248: 2025_12_29 - add_status_values_to_ticket_validations (batch 56)
249: 2026_01_07 - create_verified_financial_entries_table (batch 999)
250: 2026_01_07 - create_eod_reports_table (batch 999)
251: 2026_01_08 - normalize_order_status_values (batch 999)
252: 2026_01_08 - normalize_payment_method_values (batch 999)
253: 2026_01_08 - add_currency_to_orders_and_normalize_events (batch 999)
254: 2026_01_08 - fix_order_audit_log_order_id_fk (batch 999)
255: 2026_01_08 - fix_coupon_usage_order_id_fk (batch 999)
256: 2026_01_15 - add_video_fields_to_events_table (batch 1000)
257: 2026_01_19 - add_video_collection_id_to_events_table (batch 1001)
```

---

## Architectural Implications

### Strangler Fig Pattern Status

**RATIONALE (User):** Production is "vastly outdated" due to strangler fig domain refactor.

**REALITY:** It's the opposite - production is AHEAD with:
- Order status normalization (batch 999)
- Payment method normalization (batch 999)
- Currency additions (batch 999)
- Financial reporting (batch 999)
- Video features (batches 1000-1001)

### Domain Decomposition Impact

The domain refactor has:
- ✅ Created new domain tables (gala_state_transitions, permission_overrides, template_versions)
- ✅ Reorganized code into domains
- ❌ Created conflicting migrations for shared tables (whatsapp_logs)
- ❌ Fallen behind production schema (42 days, 16 migrations)

---

## Testing Implications

### Why Tests Fail

**Root Cause:** Schema dump in decomp-sprint is from an EARLIER state than production.

When tests use `RefreshDatabase`:
1. Tries to load decomp-sprint schema dump
2. Runs migrations that ALREADY exist in schema
3. **Conflicts occur** - tables already exist

**Current State:**
- decomp schema: 237 migrations (batch 58)
- Production schema: 253 migrations (batch 1001)
- Gap: 16 migrations not in decomp

---

## Migration Strategy Options

### Option A: Merge Production Schema into decomp-sprint (RECOMMENDED)

**Steps:**
1. Copy production schema to decomp-sprint as new baseline
2. Archive ALL existing migration files (move to migrations_archive/)
3. Keep ONLY domain-specific NEW migrations (gala_state_transitions, permission_overrides, etc.)
4. Create new migrations for domain tables not in production
5. Test RefreshDatabase works

**Pros:**
- decomp-sprint catches up to production
- Domain tables get proper migrations
- Tests work correctly

**Cons:**
- Requires careful migration audit
- 2-3 hours of work

### Option B: Dual Schema Strategy (ARCHITECTURAL)

**Steps:**
1. Keep production schema as `mysql-schema.sql` (baseline)
2. Create `mysql_testing-schema.sql` with domain tables added
3. Run domain migrations AFTER schema load
4. Tests use testing-specific schema

**Pros:**
- Production schema remains source of truth
- Domain work is additive
- Clear separation

**Cons:**
- Maintaining two schemas
- Complexity

### Option C: Fresh Schema Dump from decomp-sprint Database (CLEAN SLATE)

**Steps:**
1. Load decomp-sprint schema into a clean database
2. Run ALL pending migrations
3. `php artisan schema:dump --prune`
4. This becomes the new baseline

**Pros:**
- Clean, no conflicts
- All domain work captured

**Cons:**
- Loses production migrations (normalization, financial, video)
- Would need to re-apply those manually

---

## Recommended Path Forward

**OPTION A: Merge Production + Domain Work**

1. **Use production schema as baseline** (253 migrations, batch 1001)
2. **Identify domain-only migrations** in decomp-sprint:
   - `create_gala_state_transitions`
   - `create_permission_overrides`  
   - `create_template_versions`
   - `create_access_logs`
   - `create_features`
3. **Create NEW migrations** for these domain tables (with 2026-01-23 timestamps)
4. **Archive all conflicting migrations** (whatsapp, support, etc.)
5. **Test suite should work** with clean migration history

**Timeline:** 2-3 hours for proper reconciliation

---

## Files Referenced

- **Production Schema:** `/Users/charlie/code/showprima-notif-decomp/database/schema/reference/production_source_of_truth_20260123.sql`
- **decomp-sprint Schema:** `/Users/charlie/code/showprima-notif-decomp/database/schema/mysql-schema.sql`
- **Production Latest:** 2026-01-19 (video_collection_id)
- **decomp-sprint Latest:** 2025-12-08 (whatsapp)