# Paystack Direct Debit authorization integrity — read-only repair review

**Status: PROPOSAL ONLY. Nothing here has been executed. Every query below is a `SELECT`.**
No charge, retry, counter reset, meta edit or mandate change is part of this document.

## Why this exists

Direct Debit collection now resolves the charge identity from the **active `PaymentMandate`**
(`provider=paystack`, `type=direct_debit`, `status=active`, unexpired):

| Part of the identity | Source (authoritative) |
|---|---|
| authorization code | `payment_mandates.provider_mandate_id` |
| Paystack customer | `payment_mandates.provider_customer_id` (fallback: payload `customer.code`) |
| charge email (alias) | provider payload `customer.email`, else `metadata.paystack_dd_email` on the mandate |

`users.meta.paystack_auth_code / paystack_customer_code / paystack_auth_channel / paystack_dd_email`
are a compatibility cache only and can never override the mandate. The borrower's personal `users.email`
is never a DD charge identity.

> `users.meta.paystack_customer_code` is **not** expected to equal the mandate's `provider_customer_id`.
> It is the borrower's own Paystack customer (BVN / dedicated account, created with the personal email);
> the DD customer is a different customer created with the `ddN@` alias. Do **not** "repair" it to match.

## 0. Pre-deploy blast-radius check (run BEFORE deploying)

The new rules fail closed (no Paystack call, no attempt counted) when DD is claimed but the mandate is
unusable. These lists show who would be affected. Ideal result for every query: **0 rows**.

```sql
-- A. Meta claims Direct Debit but there is no active, unexpired Paystack DD mandate
SELECT u.id
FROM users u
WHERE JSON_UNQUOTE(JSON_EXTRACT(u.meta, '$.paystack_auth_channel')) = 'direct_debit'
  AND NOT EXISTS (
    SELECT 1 FROM payment_mandates m
    WHERE m.user_id = u.id AND m.provider = 'paystack' AND m.type = 'direct_debit'
      AND m.status = 'active' AND (m.expires_at IS NULL OR m.expires_at > NOW()));

-- B. More than one active mandate with different authorization codes (ambiguous -> fails closed)
SELECT user_id, COUNT(DISTINCT provider_mandate_id) AS distinct_auth_codes, COUNT(*) AS active_rows
FROM payment_mandates
WHERE provider = 'paystack' AND type = 'direct_debit' AND status = 'active'
  AND (expires_at IS NULL OR expires_at > NOW())
GROUP BY user_id
HAVING COUNT(DISTINCT provider_mandate_id) > 1 OR SUM(provider_mandate_id IS NULL OR provider_mandate_id = '') > 0;

-- C. Active mandate missing part of the identity bundle (no secrets are selected)
SELECT id, user_id,
  (provider_mandate_id IS NULL OR provider_mandate_id = '')                           AS missing_authorization,
  (COALESCE(provider_customer_id,'') = ''
     AND COALESCE(JSON_UNQUOTE(JSON_EXTRACT(provider_response,'$.customer.code')),'') = '') AS missing_customer,
  (COALESCE(JSON_UNQUOTE(JSON_EXTRACT(provider_response,'$.customer.email')),'') = ''
     AND COALESCE(JSON_UNQUOTE(JSON_EXTRACT(metadata,'$.paystack_dd_email')),'') = '')      AS missing_alias
FROM payment_mandates
WHERE provider = 'paystack' AND type = 'direct_debit' AND status = 'active'
HAVING missing_authorization OR missing_customer OR missing_alias;

-- D. Active mandate bound to the borrower's personal email (rejected under the alias strategy)
SELECT m.id, m.user_id
FROM payment_mandates m JOIN users u ON u.id = m.user_id
WHERE m.provider = 'paystack' AND m.type = 'direct_debit' AND m.status = 'active'
  AND LOWER(JSON_UNQUOTE(JSON_EXTRACT(m.provider_response,'$.customer.email'))) = LOWER(u.email);
```

Any row in A–D means that borrower's Paystack rail will report
`Paystack Direct Debit authorization requires reconciliation before another collection attempt.`
(no debit, no counters) until the mandate data is reconciled. Decide per borrower before deploy.

## 1. User 587 — identity comparison (booleans only, no values)

```sql
SELECT
  m.id AS mandate_id, m.status, m.expires_at,
  (m.provider_mandate_id IS NOT NULL AND m.provider_mandate_id <> '')                                   AS mandate_has_authorization,
  (COALESCE(m.provider_customer_id,'') <> '')                                                           AS mandate_has_customer,
  (m.provider_mandate_id = JSON_UNQUOTE(JSON_EXTRACT(m.provider_response,'$.authorization_code')))      AS mandate_auth_matches_payload,
  (m.provider_customer_id = JSON_UNQUOTE(JSON_EXTRACT(m.provider_response,'$.customer.code')))          AS mandate_customer_matches_payload,
  (m.provider_mandate_id = JSON_UNQUOTE(JSON_EXTRACT(u.meta,'$.paystack_auth_code')))                   AS meta_auth_equals_mandate,        -- expected FALSE (the bug)
  (m.provider_customer_id = JSON_UNQUOTE(JSON_EXTRACT(u.meta,'$.paystack_customer_code')))              AS meta_customer_equals_mandate,    -- FALSE is normal (different customer)
  (LOWER(JSON_UNQUOTE(JSON_EXTRACT(m.provider_response,'$.customer.email')))
     = LOWER(JSON_UNQUOTE(JSON_EXTRACT(u.meta,'$.paystack_dd_email'))))                                 AS meta_alias_equals_mandate_alias,
  (LOWER(JSON_UNQUOTE(JSON_EXTRACT(m.provider_response,'$.customer.email'))) = LOWER(u.email))          AS mandate_email_is_personal_email  -- must be FALSE
FROM users u
JOIN payment_mandates m ON m.user_id = u.id
 AND m.provider = 'paystack' AND m.type = 'direct_debit'
WHERE u.id = 587
ORDER BY m.id DESC;
```

## 2. Schedules #13 and #14 — state and history

```sql
SELECT id, loan_request_id, installment_number, status, amount, original_amount, attempts,
       collection_failure_runs, collection_success_runs, transaction_reference, collection_method,
       collection_attempt_provider, collection_attempt_reference, collection_attempt_amount,
       collection_reconciliation_required, collection_reconciliation_reason, collection_in_progress_at,
       JSON_EXTRACT(meta, '$.last_failure_type')  AS last_failure_type,
       JSON_EXTRACT(meta, '$.failure_history')    AS failure_history,
       JSON_EXTRACT(meta, '$.paystack_authorization_review') AS authorization_review
FROM repayment_schedules WHERE id IN (13, 14);   -- (paid_amount was listed in the request; add it if the column exists)

SELECT id, created_at, status, provider, amount, reference,
       JSON_UNQUOTE(JSON_EXTRACT(metadata,'$.repayment_schedule_id')) AS schedule_id,
       JSON_UNQUOTE(JSON_EXTRACT(metadata,'$.normalized_outcome'))   AS outcome,
       JSON_UNQUOTE(JSON_EXTRACT(metadata,'$.failure_type'))         AS failure_type,
       JSON_UNQUOTE(JSON_EXTRACT(metadata,'$.failure_reason'))       AS failure_reason,
       JSON_UNQUOTE(JSON_EXTRACT(metadata,'$.attempt_source'))       AS attempt_source
FROM user_activity_logs
WHERE user_id = 587 AND category = 'payment'
  AND JSON_UNQUOTE(JSON_EXTRACT(metadata,'$.repayment_schedule_id')) IN ('13','14')
ORDER BY id;
```

What to look for on **#13**: a Paystack row whose `failure_reason` is the "Email does not match Authorization code"
message, followed by a successful Mono (`mono`/`mono_mandate`) row. Those Paystack failures were identity
mismatches and were counted as hard attempts (`attempts`, `collection_failure_runs`, `failure_history`).

## 3. Proposed minimal correction (NOT executed — needs an explicit go)

Order matters: **deploy the code first**, because after the fix collection works from the mandate even
while meta is still stale. The data cleanup is hygiene, not a prerequisite.

1. `users.meta` for user 587 (compat cache only):
   - `paystack_auth_code`  ← the active mandate's `provider_mandate_id`
   - `paystack_dd_email`   ← the mandate alias email
   - `paystack_auth_channel` stays `direct_debit`
   - **do not touch** `paystack_customer_code` (borrower's own Paystack customer).
2. Schedule #14 counters (`attempts` 4/5, `collection_failure_runs` 4): the identity-mismatch failures
   were not economic failures. Reviewers decide whether to restore `attempts`/`collection_failure_runs`
   to the count of *genuine* failures. Until then **do not retry #14**: one more counted failure would
   move it to `defaulting`. After the code fix a retry cannot increment counters for an identity problem,
   but a *genuine* provider failure still would.
3. Schedule #13: review only; it already resolved via Mono. Consider whether its inflated counters
   matter for reporting.
4. Re-run section 1: `meta_auth_equals_mandate` and `meta_alias_equals_mandate_alias` should be `1`,
   `mandate_email_is_personal_email` must remain `0`.