# Phase 15 Preparation: Database Persistence Implementation

## Quick Reference: What's Ready

✅ **Database Foundation Complete:**
- 9 Ecto schemas (Merchant 5, Remittance 1, Disputes 2, Cards 1)
- 5 migrations (9 tables total)
- Design document with MySQL constraints, indexes, idempotency patterns

❌ **Missing: Write-Through Layer**
- Persistence modules (5 needed)
- Store integration (4 stores to update)
- Integration tests (validation)
- Migration/backfill scripts

---

## Next Session: 4-Step Execution Plan

### Step 1: Create Persistence Modules (1-2 hours)

**File: `apps/wallet_database/lib/wallet_database/write_through/merchant_persistence.ex`**
```elixir
defmodule WalletDatabase.WritThrough.MerchantPersistence do
  alias WalletDatabase.{Repo, Schemas}

  def store_merchant(merchant) do
    # Get → Insert/Update pattern
    case Repo.get(Schemas.Merchants.Merchant, merchant.merchant_id) do
      nil -> insert_merchant(merchant)
      existing -> update_merchant(existing, merchant)
    end
  end

  def store_compliance_check(check) do
    # Similar pattern for compliance checks
  end

  def store_risk_profile(profile) do
    # Similar pattern for risk profiles
  end

  def store_pos_transaction(txn) do
    # Similar pattern for POS transactions
  end

  def store_qr_code(qr) do
    # Similar pattern for QR codes
  end

  # Private helpers
  defp insert_merchant(merchant) do
    %Schemas.Merchants.Merchant{}
    |> Schemas.Merchants.Merchant.changeset(to_map(merchant))
    |> Repo.insert()
    |> handle_result()
  end

  defp update_merchant(existing, new) do
    existing
    |> Schemas.Merchants.Merchant.changeset(to_map(new))
    |> Repo.update()
    |> handle_result()
  end

  defp handle_result({:ok, _}), do: :ok
  defp handle_result({:error, e}) do
    Logger.warning("Merchant persistence failed: #{inspect(e)}")
    {:error, e}
  end

  defp to_map(merchant) do
    Map.from_struct(merchant)
  end
end
```

**Create 4 more similar modules:**
- `RemittancePersistence` (1 function: store_remittance)
- `DisputePersistence` (2 functions: store_dispute, store_refund_request)
- `CardPersistence` (2 functions: store_card, store_linked_card)

### Step 2: Update ETS Stores (1 hour)

**File: `apps/wallet_merchant/lib/wallet_merchant/merchant_store.ex`**

After the `store/1` function, add:
```elixir
def store(%__MODULE__{} = merchant) do
  ETS.insert(@table, {merchant.merchant_id, merchant})
  # NEW: Persist to DB
  case WalletDatabase.WritThrough.MerchantPersistence.store_merchant(merchant) do
    :ok -> {:ok, merchant}
    {:error, _} ->
      Logger.warning("DB persistence failed for merchant #{merchant.merchant_id}")
      {:ok, merchant}  # Still return success to avoid blocking
  end
end
```

**Update 4 stores:**
- MerchantStore `store/1` → call MerchantPersistence
- MerchantComplianceCheckStore `store/1` → call MerchantPersistence
- MerchantRiskProfileStore `store/1` → call MerchantPersistence
- (Similar for Remittance, Disputes, Cards)

### Step 3: Create Integration Tests (2 hours)

**File: `apps/wallet_database/test/wallet_database/merchant_persistence_test.exs`**

```elixir
defmodule WalletDatabase.MerchantPersistenceTest do
  use ExUnit.Case, async: false

  alias WalletDatabase.Repo
  alias WalletDatabase.Schemas.Merchants.Merchant
  alias WalletMerchant.{Merchant as DomainMerchant, MerchantStore}
  alias WalletDatabase.WritThrough.MerchantPersistence

  setup do
    Repo.delete_all(Merchant)
    MerchantStore.reset()
    :ok
  end

  test "merchant stored in ETS → persisted to DB" do
    # Create merchant via domain
    {:ok, merchant} = DomainMerchant.new([
      name: "Test Merchant",
      registration_number: "REG-123",
      merchant_type: :sme,
      category: "5411",
      country_code: "AE",
      contact_email: "test@merchant.com",
      contact_phone: "+97150123456"
    ])

    # Store via ETS
    MerchantStore.store(merchant)

    # Verify in DB
    assert Repo.get(Merchant, merchant.merchant_id) != nil
    db_merchant = Repo.get(Merchant, merchant.merchant_id)
    assert db_merchant.name == "Test Merchant"
  end

  test "idempotency: duplicate stores → single record" do
    # Store same merchant twice
    # Verify count in DB is 1, not 2
  end

  test "concurrent writes: only first one wins" do
    # Test race condition handling
  end
end
```

**Create 3 more test files:**
- `remittance_persistence_test.exs`
- `dispute_persistence_test.exs`
- `card_persistence_test.exs`

### Step 4: Migration & Backfill Script (2 hours)

**File: `scripts/backfill_merchants.exs`**

```elixir
# Usage: mix run scripts/backfill_merchants.exs

alias WalletDatabase.Repo
alias WalletMerchant.MerchantStore
alias WalletDatabase.WritThrough.MerchantPersistence
alias WalletDatabase.Schemas.Merchants.Merchant

# 1. Get all merchants from ETS
merchants = MerchantStore.list_all()
IO.puts("Found #{length(merchants)} merchants in ETS")

# 2. Backfill into DB
Enum.each(merchants, fn merchant ->
  case MerchantPersistence.store_merchant(merchant) do
    :ok -> IO.write(".")
    {:error, reason} ->
      IO.puts("\nERROR: #{merchant.merchant_id} - #{inspect(reason)}")
  end
end)

# 3. Verify counts match
ets_count = length(merchants)
db_count = Repo.aggregate(Merchant, :count)
IO.puts("\n\nVerification:")
IO.puts("ETS count: #{ets_count}")
IO.puts("DB count: #{db_count}")
IO.puts(if ets_count == db_count, do: "✅ MATCH", else: "❌ MISMATCH")
```

---

## Implementation Checklist

### Week 1: Persistence Layer (This Week)
- [ ] Create 5 persistence modules (200 lines each)
- [ ] Update 4 ETS stores (call persistence after store/update)
- [ ] Write 4 integration test files (20+ tests total)
- [ ] Create 4 backfill scripts (merchants, remittance, disputes, cards)
- [ ] Manual testing: ETS write → DB read verification

### Week 2: Validation & Rollout
- [ ] Run backfill scripts on all 4 domains
- [ ] Verify record counts match
- [ ] Run nightly reconciliation query (ETS vs DB)
- [ ] Deploy with dual-write enabled
- [ ] Monitor for persistence failures
- [ ] Switch to DB-only after 1 week successful dual-write

### Week 3: Remaining Domains (P1)
- [ ] Reporting schemas + persistence
- [ ] GL schemas + persistence
- [ ] Loans schemas + persistence
- [ ] Full validation suite

### Week 4: Phase 15 Kickoff
- [ ] All critical domains persisted
- [ ] Zero data loss on restart
- [ ] Queries work from DB consistently
- [ ] New Phase 15 models include persistence by default

---

## Code Patterns to Reuse

**Error Handling (fire-and-forget):**
```elixir
case persist_to_db(record) do
  :ok -> {:ok, record}
  {:error, reason} ->
    Logger.warning("DB persist failed: #{inspect(reason)}")
    {:ok, record}  # Don't block app
end
```

**Idempotency (get → insert/update):**
```elixir
case Repo.get(Schema, id) do
  nil -> %Schema{} |> Schema.changeset(attrs) |> Repo.insert()
  existing -> existing |> Schema.changeset(attrs) |> Repo.update()
end
```

**Concurrent Safety:**
- Repo.get/1 returns current DB state
- Changeset.update validates current values
- On conflict, second write wins (last-write-wins)

---

## Testing Strategy

**Unit:** Persistence module writes correct fields
**Integration:** ETS store → DB query works
**E2E:** Phase 14 E2E tests now query DB instead of ETS

**Validation Queries:**
```sql
-- Count verification
SELECT COUNT(*) FROM merchants;  -- Should match MerchantStore.list_all() length

-- Status distribution
SELECT status, COUNT(*) FROM merchants GROUP BY status;

-- Idempotency check
SELECT idempotency_key, COUNT(*) FROM inward_remittance_records
GROUP BY idempotency_key HAVING COUNT(*) > 1;
```

---

## Risk Mitigation

| Risk | Mitigation |
|------|-----------|
| DB write fails | Log + continue (fire-and-forget) |
| App restart loses data | ETS persisted to DB before restart |
| Duplicate records | Idempotency keys + unique constraints |
| DB connection down | Cache DB failures, async retry queue |
| Data inconsistency | Nightly reconciliation query |

---

## Success Criteria

✅ All 4 critical domains persist to DB
✅ No data loss on app restart
✅ Queries work from DB (list_by_merchant, list_by_status, etc.)
✅ Idempotency prevents duplicates
✅ Zero blocks due to DB failures
✅ Phase 15 can proceed with DB persistence enabled

---

## Time Estimates

| Task | Est. Hours |
|------|-----------|
| Write persistence modules (5) | 2 |
| Update ETS stores (4) | 1 |
| Integration tests (4 files) | 2-3 |
| Backfill scripts (4) | 1-2 |
| Manual testing & validation | 1-2 |
| **Total** | **~8-10 hours** |

**Can be completed in 1 focused day with minimal interruptions.**