# Phase 11-14 Database Persistence Implementation Guide

## Current Session Deliverables

### ✅ Created Database Schemas & Migrations (22 files)

#### Merchant Domain (Phase 14)
**Schemas:**
- `WalletDatabase.Schemas.Merchants.Merchant` — Main merchant record with 5-state lifecycle
- `WalletDatabase.Schemas.Merchants.MerchantComplianceCheck` — KYB/AML/Sanctions/PEP checks
- `WalletDatabase.Schemas.Merchants.MerchantRiskProfile` — Risk scoring & tiering
- `WalletDatabase.Schemas.Merchants.PosTransaction` — POS authorization/capture/settle flow
- `WalletDatabase.Schemas.Merchants.MerchantQrCode` — Fixed/variable QR code lifecycle

**Migrations:**
- `20260329000016_create_merchants` — Merchant family tables with constraints
- `20260329000017_create_merchant_transactions_and_qr` — POS and QR code tables

#### Integrations Domain (Phase 14)
**Schemas:**
- `WalletDatabase.Schemas.Integrations.InwardRemittanceRecord` — Remittance tracking with idempotency

**Migrations:**
- `20260329000018_create_inward_remittance_records` — Remittance table with idempotency unique index

#### Disputes Domain (Phase 13)
**Schemas:**
- `WalletDatabase.Schemas.Disputes.Dispute` — 5-status dispute lifecycle
- `WalletDatabase.Schemas.Disputes.RefundRequest` — Refund workflow (pending→approved→posted)

**Migrations:**
- `20260329000019_create_disputes` — Disputes and refund request tables with constraints

#### Cards Domain (Phase 10A)
**Schemas:**
- `WalletDatabase.Schemas.Cards.Card` — Debit/credit cards with limits & freeze tracking

**Migrations:**
- `20260329000020_create_cards` — Cards table with limit tracking

## Schema Coverage

| Domain | Schema Count | Migration | Status |
|--------|---|---|---|
| Merchants | 5 | 2 migrations | ✅ Complete |
| Remittance | 1 | 1 migration | ✅ Complete |
| Disputes | 2 | 1 migration | ✅ Complete |
| Cards | 1 | 1 migration | ✅ Complete |
| **TOTAL** | **9** | **5 migrations** | **Ready** |

## Next Priority: Write-Through Persistence Layer

### For Each Domain (Create 2 files per domain):

**1. Merchant Persistence Module**
```elixir
# apps/wallet_database/lib/wallet_database/write_through/merchant_persistence.ex
- store_merchant/1 — After MerchantStore.store(merchant)
- store_compliance_check/1 — After MerchantComplianceCheckStore.store(check)
- store_risk_profile/1 — After MerchantRiskProfileStore.store(profile)
- store_pos_transaction/1 — After PosTransactionStore.store(txn)
- store_qr_code/1 — After MerchantQrCodeStore.store(qr)
```

**2. Update Merchant Stores**
```elixir
# apps/wallet_merchant/lib/wallet_merchant/merchant_store.ex
# After: MerchantStore.store(merchant)
# Call: MerchantPersistence.store_merchant(merchant)
```

Similarly for:
- **RemittancePersistence** ← InwardRemittanceStore
- **DisputePersistence** ← DisputeStore, RefundRequestStore
- **CardPersistence** ← CardStore, LinkedCardStore

### Phased Implementation

#### Phase 1: Critical Path (This Week)
1. ✅ Create schemas & migrations for Merchant, Remittance, Disputes, Cards
2. → Create persistence modules (5 modules, ~200 lines each)
3. → Update ETS stores to call persistence after writes
4. → Test idempotency & write-through behavior
5. → Migration script to backfill any historical data

#### Phase 2: Complete Remaining Domains (Next Week)
6. Reporting (RegulatoryTemplate, ReportJob, ReportRequest)
7. GL (GlPostingRecord, GlVariance, GlReconciliation)
8. Rewards (Offer, RewardTransaction, PointsBalance)
9. Loans (Loan records)
10. Insurance (InsuranceProduct, InsurancePolicy)
11. Resilience (HealthCheck, SloViolation, IncidentRecord)

#### Phase 3: Migration & Rollout (Following Week)
12. Export current ETS data to JSON/CSV
13. Run migrations on test DB
14. Backfill from JSON into new tables
15. Validation tests (record counts, data integrity)
16. Gradual rollout with dual-write verification

## Code Example: Write-Through Pattern

```elixir
# apps/wallet_database/lib/wallet_database/write_through/merchant_persistence.ex
defmodule WalletDatabase.WritThrough.MerchantPersistence do
  alias WalletDatabase.Repo
  alias WalletDatabase.Schemas.Merchants

  def store_merchant(merchant) do
    attrs = %{
      merchant_id: merchant.merchant_id,
      name: merchant.name,
      registration_number: merchant.registration_number,
      # ... all fields
    }

    case Repo.get(Merchants.Merchant, merchant.merchant_id) do
      nil ->
        %Merchants.Merchant{}
        |> Merchants.Merchant.changeset(attrs)
        |> Repo.insert()

      existing ->
        existing
        |> Merchants.Merchant.changeset(attrs)
        |> Repo.update()
    end
    |> case do
      {:ok, _record} -> :ok
      {:error, changeset} -> {:error, changeset}
    end
  rescue
    e -> {:error, e}
  end
end
```

## Testing Strategy

### Unit Tests
- Verify persistence module writes correct fields
- Test idempotency (duplicate writes → single record)
- Validate MySQL-compatible NULL/constraint handling

### Integration Tests
- Store to ETS → verify in DB
- Concurrent writes → verify no duplicates
- Migration validates pre-existing test data

### E2E Tests
- Phase 14 E2E tests now query from DB instead of ETS
- Verify lookup queries work (list_by_merchant, list_by_status, etc.)

## Configuration

No config changes needed—uses existing `WalletDatabase.Repo` setup:
- `:wallet_database, :start_repo` controls whether Repo starts
- Test: `config :wallet_database, start_repo: false` (existing)
- Prod: `config :wallet_database, start_repo: true`

## Risk Mitigation

1. **Dual-write during transition:**
   - ETS writes first (always succeeds)
   - DB persistence is async/fire-and-forget initially
   - Switch to sync after 1 week validation

2. **Rollback procedure:**
   - Keep ETS stores functional
   - If DB write fails, log warning but don't block app
   - Replay queue for failed writes

3. **Data verification:**
   - Nightly reconciliation: compare ETS vs DB record counts
   - Audit trail: every write logs correlation_id + timestamps

## Timeline

- **Today (2026-03-29):** Schema + migration foundation DONE
- **Tomorrow:** Write-through modules + store integration (2-3 hours)
- **Day 3:** E2E integration tests (2-3 hours)
- **Day 4:** Migration script + backfill (2-3 hours)
- **Day 5:** Full validation suite (2-3 hours)

**Total effort: ~15-20 hours for critical path (Merchant, Remittance, Disputes, Cards)**

## Remaining Domains After This Session

### Medium Priority (P1, weeks of Phase 15)
- **Reporting**: RegulatoryTemplate, ReportJob (~4 tables)
- **GL**: GlPostingRecord, GlVariance, GlReconciliation (~3 tables)
- **Loans**: Loan records (~1 table)

### Lower Priority (P2, future phases)
- **Rewards**: Offer, RewardTransaction, PointsBalance (~3 tables)
- **Insurance**: InsuranceProduct, InsurancePolicy (~2 tables)
- **Resilience**: HealthCheck, SloViolation, Incident (~3 tables)

## Approval Checklist

Before proceeding to Phase 15:
- [ ] All merchant records persist to DB
- [ ] All remittance records persist to DB
- [ ] All dispute records persist to DB
- [ ] All card records persist to DB
- [ ] Migration script created & tested
- [ ] Write-through + ETS consistency verified
- [ ] E2E tests validate DB queries
- [ ] No data loss on restart