# ADR Compliance Gap Analysis & Implementation Plan (2026-04-02)

## Executive Summary

Phase 11-14 ETS-to-MySQL persistence migration succeeded in adding write-through durability across 88 ETS-backed stores and 29 OTP apps. However, **8 priority compliance gaps** were introduced where phase 11+ implementation diverged from the ADRs.

**Status:** 2 P0 gaps identified, 6 P1/P2 gaps follow.  
**Risk Level:** Medium — structural soundness is good, but cross-cutting concerns (traceability, encryption, partitioning) missing.

---

## Priority Gaps and Implementation Plan

### P0-001: Missing `correlation_id` in 62 Schemas (ADR 0007)

**Status:** IMPLEMENTED (rollout validation pending on clean DB/staging)  
**Effort:** Medium (2 hours)  
**Risk:** Observability blind spot for business transactions; audit trail incomplete.

**Scope:**
- 100 total schemas identified
- 38 had `correlation_id`
- **62 were missing** (now updated)

**Impacted Domains:**
- wallet_loans (Loan, ReversalRecord)
- wallet_insurance (Product, Policy, PremiumPayment)
- wallet_merchant (6 schemas)
- wallet_cards (Card, LinkedCard)
- wallet_rewards (Offer, RewardTransaction, PointsBalance)
- wallet_disputes (3 schemas)
- wallet_gl (2 schemas)
- wallet_reporting (3 schemas)
- wallet_wps (3 schemas)
- wallet_state (IdempotencyRecord, tokenization, etc.)
- wallet_integrations (5 schemas)
- wallet_limits_fees (2 schemas)
- wallet_journey (Journey)
- wallet_production (DrCheckpoint, ReadinessApproval)
- wallet_resilience (HealthCheck, SloViolation, IncidentRecord)
- wallet_accounts (Account, SubWallet, VirtualIban, CurrencyConfig)
- wallet_auth (Device, Session, RefreshToken, OtpChallenge, Pin)
- admin (AdminConfig, RegulatoryTemplate, ReportRequest)

**Implementation Plan:**

1. **Phase A (1 hour):** Add `correlation_id` field to all 62 schemas
   - Schema change: `field :correlation_id, :string, null: true` (nullable for existing records)
   - Ensure field is indexed for queries
   - Schemas can remain unchanged in ETS structs (write-through only)

2. **Phase B (30 min):** Create migration to add column to all affected tables
   - Single migration: `20260402000026_add_correlation_id_to_all_business_tables.exs`
   - Up: `ALTER TABLE ... ADD COLUMN correlation_id VARCHAR(36)` (36 = UUID length)
   - Add index on all tables: `CREATE INDEX idx_<table>_correlation_id ON <table>(correlation_id)`

3. **Phase C (30 min):** Update write-through modules to capture `correlation_id`
  - Implemented centrally in `WalletDatabase.WriteThrough.upsert/3`
  - Pulls correlation_id from process/logger metadata when schema supports it
  - Fallback remains `nil` when context is not available

**Acceptance Criteria:**
- All 62 schemas include `correlation_id: :string` field
- Migration applies cleanly to test/staging
- All financial transaction tables indexed on `correlation_id`

---

### P0-002: Update ADR 0001 App List (17 → 29 Apps)

**Status:** COMPLETED  
**Effort:** Small (30 min)  
**Risk:** Low — documentation only, no code impact.

**Gap Details:**
ADR 0001 lists 17 target apps. Codebase now has 29. The following 12 are undocumented:

**Added Post-ADR:**
1. `wallet_database` — shared DB infrastructure
2. `wallet_cards` — card lifecycle
3. `wallet_rewards` — rewards, points, offers
4. `wallet_loans` — loan lifecycle
5. `wallet_insurance` — insurance products & policies
6. `wallet_merchant` — merchant onboarding & POS
7. `wallet_disputes` — dispute & refund management
8. `wallet_gl` — general ledger & AFEX adapter
9. `wallet_reporting` — reporting & regulatory templates
10. `wallet_wps` — WPS salary credit processing
11. `wallet_production` — production readiness & features
12. `wallet_resilience` — health checks, SLOs, incidents

**Implementation Plan:**

1. **Update Section: "Target Application Set"** in ADR 0001
   - Add the 12 new apps to the list
   - Add subsection: "Apps Added Post-ADR (Phases 10-14)"

2. **Update Section: "System of Record Ownership"**
   - Add SoR declarations for new domains:
     - Card lifecycle SoR: `wallet_cards`
     - Loan lifecycle SoR: `wallet_loans`
     - Insurance product/policy SoR: `wallet_insurance`
     - Merchant profile/lifecycle SoR: `wallet_merchant`
     - Dispute & refund SoR: `wallet_disputes`
     - GL posting & reconciliation SoR: `wallet_gl`
     - Reporting & audit trail SoR: `wallet_reporting`
     - WPS salary credit SoR: `wallet_wps`
     - Production readiness & feature gates SoR: `wallet_production`
     - Resilience & incident tracking SoR: `wallet_resilience`
     - Reward transaction SoR: `wallet_rewards`

3. **Update Section: "Dependency Rules"**
   - Reaffirm: all new apps follow same rules as original set
   - Add note: `wallet_database` is shared infrastructure, no domain depends on it upward

**Acceptance Criteria:**
- All 29 apps listed in ADR 0001
- All SoR ownership declared
- Updated README cross-reference to ADR in each new app's README (already done in commit 34590f6)

---

### P1-001: PII Encryption in Phase 11+ Schemas (ADR 0006)

**Status:** IMPLEMENTED (high + medium risk schema coverage complete; staging rollout pending)  
**Effort:** Medium (2-3 hours)  
**Risk:** Medium — sensitive data exposure if not encrypted.

**Gap Details:**
Only `virtual_ibans` schema has encrypted fields. New phase 11+ schemas hold PII without field-level encryption:

**Schemas Requiring Encryption Audit:**
- **wallet_cards:** `Card` (PAN, last_4, expiry) — **HIGH**
- **wallet_merchant:** Multiple (legal_name, tax_id, phone, email) — **HIGH**
- **wallet_loans:** `Loan` (user financial details) — **MEDIUM**
- **wallet_insurance:** Product, Policy (user health/coverage details) — **MEDIUM**
- **wallet_integrations:** `AfexProfile` (customer ID, bank details) — **MEDIUM**
- **wallet_accounts:** `User` (email, phone) — **HIGH**

**Implementation Plan:**

1. **Phase A (30 min):** Audit each schema
   - Identify PII fields
   - Classify by sensitivity (HIGH/MEDIUM/LOW)
   - Document in schema comments
   - Decision: encrypt vs. externalize

2. **Phase B (1.5 hours):** Add field-level encryption
   - Use `Cloak.Ecto` or similar library (check existing `wallet_database` setup)
   - Wrap high-sensitivity fields with `:encrypted, ... , vault: :default` (example syntax)
   - Create migration to back-encrypt existing unencrypted data

3. **Phase C (30 min):** Update write-through modules
   - Ensure encrypted fields are passed through correctly
   - No changes to ETS structs (encryption happens at DB boundary)

**Acceptance Criteria:**
- All HIGH-sensitivity PII fields encrypted
- Migration applies without data loss
- Decryption tested in tests
- Schema documentation includes encryption markers

**Implementation Progress (2026-04-02):**
- Added `WalletDatabase.Types.EncryptedDeterministic` for Ecto field-level encryption.
- Added backward-compatible plaintext read support to avoid breaking existing rows before backfill.
- Added production runtime key config via `WALLET_DB_FIELD_ENCRYPTION_KEY`.
- Wired encrypted type for high-risk fields in:
  - `WalletDatabase.Schemas.Auth.User` (`email`, `phone_number`, `display_name`)
  - `WalletDatabase.Schemas.Cards.Card` (`last_four`)
  - `WalletDatabase.Schemas.Cards.LinkedCard` (`nickname`, `last_four`)
  - `WalletDatabase.Schemas.Merchants.Merchant` (`name`, `registration_number`, `contact_email`, `contact_phone`)
  - `WalletDatabase.Schemas.Merchants.WalletMerchant` (`name`, `registration_number`, `contact_email`, `contact_phone`)
  - `WalletDatabase.Schemas.Integrations.AfexProfile` (`afex_customer_id`, `full_name`, `email`, `phone`)
- Added deterministic encryption tests under `apps/wallet_database/test/wallet_database/types/encrypted_deterministic_test.exs`.
- Added executable backfill utility: `mix wallet_database.backfill_encrypted_fields` (supports `--dry-run`).
- Extended medium-risk coverage in:
  - `WalletDatabase.Schemas.Loans.Loan` (`user_id`)
  - `WalletDatabase.Schemas.Loans.LoanRepayment` (`user_id`, `payment_reference`)
  - `WalletDatabase.Schemas.Insurance.InsurancePolicy` (`user_id`)
  - `WalletDatabase.Schemas.Integrations.AfexProfile` (`afex_account_id`)
- Extended backfill targets for the above fields.

**Remaining for P1-001 completion:**
- Run staged backfill in staging and confirm plaintext residual count reaches zero.

---

### P1-002: Data Partitioning for Append-Heavy Tables (ADR 0009)

**Status:** NOT STARTED  
**Effort:** High (4-5 hours)  
**Risk:** High — required for production scale.

**Gap Details:**
Zero migrations use table partitioning. The following tables will grow unbounded:

**Critical Tables Needing Partitioning:**

| Table | Type | Partition Key | Baseline |
|---|---|---|---|
| `ledger_entries` | Tier A (Financial SoR) | `posted_at` (monthly) | Unlimited |
| `ledger_journals` | Tier A (Financial SoR) | `posted_at` (monthly) | Unlimited |
| `audit_log` | Tier B (Audit evidence) | `occurred_at` (monthly) | 7+ years |
| `settlement_batches` | Tier C (Operational) | `settlement_date` (monthly) | 12-24 months |
| `gl_posting_records` | Tier A (GL SoR) | `posted_at` (monthly) | Unlimited |
| `reconciliation_runs` | Tier C (Operational) | `reconciliation_date` (monthly) | 12 months |
| `merchant_transactions` (POS) | Tier C (Operational) | `transaction_date` (monthly) | 24 months |

**Implementation Plan:**

1. **Phase A (1 hour):** Design partitioning scheme
   - Decide: MySQL RANGE partitioning vs. Postgres declarative partitioning (DB-agnostic)
   - Decide: 1-month vs. 1-quarter partition windows
   - Define sub-partition keys where needed (e.g., by currency or account type)
   - Document in migration comments

2. **Phase B (2 hours):** Create partitioning migrations
   - One migration per table requiring partitioning
   - Use `PARTITION BY RANGE (YEAR_MONTH(...))` syntax (MySQL)
   - Pre-create 12 months of future partitions for automation
   - Create stored proc or script for automated partition addition

3. **Phase C (1 hour):** Document and test
   - Add comment to ADR 0009: partition implementation status
   - Test partition pruning in key queries
   - Document partition maintenance runbook

**Acceptance Criteria:**
- All 7 tables have time-based partitions
- Future partition creation automated
- Query plans show partition pruning
- Partition maintenance runbook exists

---

### P1-003: Persistence Layer Tests (ADR 0012)

**Status:** NOT STARTED  
**Effort:** High (4-5 hours)  
**Risk:** Medium — missing safety nets for financial writes.

**Gap Details:**
- Migration dry-run tests: none
- Write-through concurrency tests: none
- Contract tests for phase 11+ event schemas: partial
- Idempotency tests: partial (only for transfers)

**Implementation Plan:**

1. **Phase A (1.5 hours):** Migration apply/rollback tests
   - Add test suite: `test/wallet_database/migrations/migration_test.exs`
   - Test: all 63 migrations apply cleanly
   - Test: rollback works for reversible migrations
   - Test: idempotent migration re-runs don't error

2. **Phase B (1.5 hours):** Write-through concurrency tests
   - Add test: concurrent ETS writes + persistence for stores (e.g., CardStore, MerchantStore, LoanStore)
   - Property test: 100 concurrent writes to same store, all persist
   - Verify: no data loss, all records end up in DB

3. **Phase C (1.5 hours):** Event schema contract tests
   - Test: all 50+ event types validate against schema
   - Test: backward compatibility for new fields
   - Test: version suffix present on all events (e.g., `.v1`)

**Acceptance Criteria:**
- All 63 migrations have passing tests
- 3+ critical stores pass concurrency tests
- Event schema tests cover 100% of event types
- CI gates require passing migration & persistence tests

---

### P2-001: Explicit Migration Rollback Paths (ADR 0010)

**Status:** NOT STARTED  
**Effort:** Small (1.5 hours)  
**Risk:** Low — rollback is rare, but critical when needed.

**Gap Details:**
62 of 63 migrations use `def change` (auto-reversible); only 1 has explicit `def down`. ADR 0010 recommends explicit rollback paths for critical migrations.

**Critical Migrations:**
- `20260401000015_create_ledger_journals_and_entries.exs` — financial posting
- `20260401000010_create_merchant_domain_tables.exs` — merchant SoR
- `20260401000009_create_disputes_domain_tables.exs` — dispute SoR
- `20260401000011_create_gl_posting_and_reconciliation_tables.exs` — GL SoR
- `20260401000014_create_wallet_journeys_table.exs` — critical workflow

**Implementation Plan:**

1. **Add explicit `def down` to 5 critical migrations**
   - Convert from `def change` to `def up / def down` pair
   - Ensure down is reverse of up (drop, not truncate)
   - Add comment: "Critical financial table — manual review required before rollback"

2. **Document rollback runbook**
   - Add to `scripts/MIGRATION_ROLLBACK_RUNBOOK.md`
   - Include: pre-rollback checks, rollback command, post-rollback validation

**Acceptance Criteria:**
- 5 critical migrations have explicit `def down`
- Rollback runbook exists and is linked from CONTRIBUTING.md

---

### P2-002: ADR 0009 Data Tier Classification (ADR 0009)

**Status:** NOT STARTED  
**Effort:** Small (1 hour)  
**Risk:** Low — documentation, no code changes.

**Implementation Plan:**

1. **Create governance doc:** `docs/data-tier-and-retention-policy-implementation.md`
   - Map all 100 schemas to Tier A/B/C/D/E classification
   - Include: retention requirement, encryption status, backup frequency
   - Reference: which migrations implement that tier

2. **Add marker comments to migrations**
   - Each migration: add comment `# Tier: A (Financial)` or similar
   - Helps future developers understand retention obligations

3. **Update ADR 0009**
   - Add implementation status section
   - List completed tables and their retention policy

**Acceptance Criteria:**
- Data tier map created and coverage is 100%
- All migrations include tier marker
- ADR 0009 marked as "Partially Implemented"

---

### P2-003: DR Drill Schema Updates (ADR 0014)

**Status:** NOT STARTED  
**Effort:** Small (1 hour)  
**Risk:** Low — operational documentation.

**Implementation Plan:**

1. **Update BC/DR policy**
   - DB is now critical dependency (was ETS-only before)
   - Add: full 63-migration schema to backup/restore procedure
   - Add: partition recovery and verification steps

2. **Update runbook**
   - Add: automated partition maintenance step to recovery procedure

**Acceptance Criteria:**
- Backup/restore procedure includes full schema
- Partition maintenance added to runbook
- Runbook tested in non-prod

---

## Detailed Schema List Needing `correlation_id`

### By App & Count

```
wallet_accounts (9):
  - account.ex
  - device.ex
  - session.ex
  - refresh_token.ex
  - user_credential.ex
  - virtual_iban.ex
  - wallet_currency_config.ex
  - wallet_product.ex
  - wallet_product_type.ex

wallet_auth (5):
  - otp_challenge.ex
  - pin.ex
  - pin_recovery_tracker.ex
  - refresh_token.ex (duplicate?)
  - session.ex (duplicate?)

wallet_cards (2):
  - card.ex
  - linked_card.ex

wallet_insurance (3):
  - insurance_policy.ex
  - insurance_product.ex
  - premium_payment.ex

wallet_loans (2):
  - loan.ex
  - loan_repayment.ex

wallet_merchant (6):
  - merchant.ex
  - merchant_compliance_check.ex
  - merchant_profile.ex
  - merchant_qr_code.ex
  - merchant_risk_profile.ex
  - pos_transaction.ex

wallet_disputes (3):
  - dispute.ex
  - dispute_case.ex
  - dispute_refund_request.ex

wallet_gl (2):
  - gl_posting_record.ex
  - gl_reconciliation_run.ex

wallet_reporting (3):
  - report_request.ex
  - report_schedule.ex
  - regulatory_template.ex

wallet_wps (3):
  - salary_credit.ex
  - salary_credit_exception.ex
  - wps_file.ex

wallet_integrations (5):
  - afex_profile.ex
  - afex_profile_sync_record.ex
  - iban_routing_record.ex
  - inbox_record.ex
  - inward_remittance_record.ex

wallet_state (1):
  - idempotency_record.ex

wallet_limits_fees (2):
  - fee_policy.ex
  - limit_policy.ex

wallet_journey (1):
  - journey.ex

wallet_production (1):
  - readiness_approval.ex

admin (3):
  - admin_configuration.ex
  - admin_configuration_audit.ex
  - report_request.ex (duplicate?)

wallet_notifications (2):
  - delivery_failure_record.ex
  - preference.ex

wallet_settlement (2):
  - exception_record.ex
  - reconciliation_run.ex

wallet_rewards (3):
  - offer.ex
  - points_balance.ex
  - reward_transaction.ex

wallet_transfers (4):
  - idempotency_record.ex (duplicate?)
  - money_request.ex
  - split_payment.ex
  - sub_wallet_transfer.ex

wallet_auth (extras not yet listed):
  - extra schemas TBD

Unaccounted: misc schemas
```

**Total: 62 schemas missing `correlation_id`**

---

## Implementation Sequence

### Week 1: P0 Gaps (2-3 hours)

**Monday:**
1. Add `correlation_id` to all 62 schemas (~1.5 hours)
2. Create migration for correlation_id columns (~30 min)
3. Update ADR 0001 app list & SoR ownership (~30 min)

**Tuesday:**
- Test migration on staging
- Commit: `feat(compliance): add correlation_id to all business schemas for ADR 0007 traceability`
- Commit: `docs(adr): update ADR 0001 with post-phase-10 apps and SoR ownership`

### Week 1-2: P1 Gaps (8-10 hours)

**Wednesday-Thursday:**
- PII encryption audit & implementation (~2.5 hours)
- Persistence layer tests (~4.5 hours)
- Commit: `feat(security): add field-level encryption for PII schemas per ADR 0006`
- Commit: `test(persistence): add migration, concurrency, and event contract tests`

**Friday:**
- Partitioning design (~1 hour)
- Commit as WIP: `design(partitioning): schema partitioning plan for ADR 0009`

### Week 2+: P2 Gaps (3-4 hours)

**As bandwidth allows:**
- Partitioning implementation (~4.5 hours)
- Migration rollback paths (~1.5 hours)
- Data tier classification (~1 hour)
- DR drill updates (~1 hour)

---

## Risk and Rollback

**Risk if not fixed:**
- P0 gaps block production compliance (no traceability, incomplete app registry)
- P1 gaps limit observability and operational safety (missing PII encryption, no partition strategy)
- P2 gaps delay post-launch governance (no rollback paths, no data tier tracking)

**If issues arise during implementation:**
- Migration rollback: reverting `correlation_id` is safe (additive change, revert is `DROP COLUMN`)
- Encryption rollback: if Cloak library causes issues, can disable field encryption and fall back to application-level hashing (no data loss)
- Partitioning: if MySQL partitioning causes issues, can defer to post-launch optimization (no risk to running system)

---

## Success Criteria

- [x] All 62 schemas have `correlation_id` field
- [x] ADR 0001 updated with 29-app list and SoR ownership
- [ ] All HIGH-sensitivity PII fields identified and encrypted
- [ ] 3+ persistence tests pass for critical stores
- [ ] Partitioning design approved and documented
- [ ] Migrations reviewed and applied cleanly to staging (clean DB path)
- [ ] Branch merged to main with clean Git history