# Payment Retry & Dunning — Implementation Plan

**Spec:** docs/specs/2026-05-31-payment-retry-dunning.md  ·  **Slug:** payment-retry-dunning  ·  **Wave:** 11
**Depends on:** foundation-auth-rbac, invoices-core, payment-gateway-adapters, system-communications-notifications

## Goal
Adds post-due-date collection automation on top of `payment-gateway-adapters` (spec 49). Tenants on Business+ configure a dunning schedule (days-past-due → action); a daily cron walks overdue invoices and, per pending step, emails the customer a payment reminder, flags the invoice for OWNER/ADMIN review, or (opt-in) freezes the customer's portal access. Staff see dunning history and can send a manual reminder from the invoice detail page.

## Architecture
- **New tables:** `dunning_schedules` (per-tenant schedule rows) and `dunning_log` (one row per invoice per executed step — the idempotency ledger the cron consults to skip already-sent steps).
- **Consumes upstream (exact names):**
  - `invoices` table (`invoices-core`): reads `id, tenant_id, customer_id, due_date, status`. Targets `status IN ('SENT','TAX_ISSUED','PARTIALLY_PAID')`. `due_date` is a `DATE`.
  - `customers` table (`customers-module`): reads `customers.email` for the reminder recipient.
  - `customer_portal_users` table + `setPortalUserStatus` helper (`customers-module`): `suspend_access` flips status to `'frozen'` (valid enum value).
  - `sendEmail` (`@zync/notifications`): all reminder email sending. **No raw Resend calls** — the comms adapter owns delivery.
  - `createNotification` (`@zync/notifications`): `flag_for_review` creates an in-app notification for OWNER/ADMIN.
  - `requireTier`, `requirePermission`, `authMiddleware` (`@zync/auth`): Business+ gate and RBAC on routes.
  - `tenant_settings` (base table owned by `foundation-auth-rbac`): the `dunning_suspend_access` opt-in toggle is a typed `BOOLEAN` column here (module behavior config — distinct from `tenants.settings` JSONB business-profile prefs). This plan adds it via `ALTER TABLE tenant_settings ADD COLUMN IF NOT EXISTS`.
- **Payment-link decision (settled):** the cron runs server-side and `POST /api/invoices/:id/payment/session` is portal-auth (customer-scoped), so the cron does **not** pre-create a gateway session. The reminder email's "Pay now" link points to the customer portal invoice page (`https://{portal_slug}.zync.is/portal/invoices/{invoiceId}`) where the existing Pay Now button lives. This is the spec's "portal link otherwise" fallback and works whether or not a gateway is configured.
- **Boundary with `invoice-payment-reminders` (spec 79) — documented, not built here:** reminders own the pre-due / at-due window (`offset_days <= 0`, all tiers); dunning owns the post-due escalation window (`offset_days > 0`, Business+). The dedup guard that suppresses spec 79's post-due stages when `dunning_schedules` rows exist lives in spec 79's cron — this plan does not implement it. The two crons are time-separated: reminders at 07:00 UTC, dunning at **08:00 UTC**. Preserve the 08:00 dunning time exactly; it is load-bearing for the no-double-send guarantee.

## Tech Stack
- **DB/ORM:** Neon Postgres via Cloudflare Hyperdrive, Drizzle ORM. Schema in `packages/db`.
- **API:** Hono on Cloudflare Workers (`apps/zync-api`). Zod validation on all bodies; `tenantQuery`/`systemQuery` wrappers (no raw Drizzle from routes).
- **Cron:** Cloudflare scheduled handler (`apps/zync-api`), wrangler `[triggers] crons` entry `0 8 * * *`.
- **UI:** Vite + React app (`apps/zync-app`) — settings page + invoice-detail dunning panel. `@zync/ui` primitives, `@zync/types`. RTL/Hebrew + a11y + reduced-motion preserved.
- **Email/notifications:** `@zync/notifications` (`sendEmail`, `createNotification`).
- **Bindings:** Hyperdrive (DB), existing `RATE_LIMITER_*` (no new bindings or queues required).

## Wave Plan
| Sub-wave | Tasks | Files touched | Parallelizable? |
|----------|-------|---------------|-----------------|
| A — schema | 1 | `packages/db/src/schema/dunning.ts`, migration | No (blocks all) |
| B — server logic | 2, 3, 4 | `packages/db` queries, `apps/zync-api` dunning service | 2 then 3,4 parallel |
| C — API routes | 5 | `apps/zync-api/src/routes/dunning.ts` | After B |
| D — cron + seed | 6, 7 | `apps/zync-api` scheduled handler, wrangler.toml, seed hook | After B (parallel with C) |
| E — UI | 8, 9 | `apps/zync-app` settings + invoice-detail | After C |
| F — i18n/a11y/verify | 10 | locale files, tests | Last |

## Tasks

### Task 1: Database schema — `dunning_schedules` & `dunning_log`
**Blocks:** 2,3,4,5,6,7,8,9  ·  **Blocked by:** —
**Files:**
- Create: `packages/db/src/schema/dunning.ts`
- Modify: `packages/db/src/schema/index.ts` (export new tables)
- Create: `packages/db/migrations/<timestamp>_dunning.sql`
**Steps:**
- [ ] Define both tables in Drizzle matching the canonical DDL below.
- [ ] Export `dunningSchedules` and `dunningLog` Drizzle table objects from the schema barrel.
- [ ] Write the raw SQL migration (Postgres) and register it in the Drizzle migration set.
- [ ] Add the `dunning_suspend_access` toggle to `tenant_settings` via `ALTER TABLE tenant_settings ADD COLUMN IF NOT EXISTS` (base table owned by `foundation-auth-rbac`; do NOT create it).
**Schema / Interfaces:**
```sql
CREATE TABLE dunning_schedules (
  id                UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id         UUID NOT NULL REFERENCES tenants(id),
  offset_days       INTEGER NOT NULL,            -- days after invoice due_date to trigger
  action            TEXT NOT NULL CHECK (action IN ('email_reminder', 'suspend_access', 'flag_for_review')),
  email_template_id UUID,                         -- optional custom email template (soft ref, intentionally no FK)
  created_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (tenant_id, offset_days)
);

CREATE TABLE dunning_log (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id   UUID NOT NULL REFERENCES tenants(id),
  invoice_id  UUID NOT NULL REFERENCES invoices(id),
  offset_days INTEGER NOT NULL,                   -- the schedule offset this row records; -1 = manual reminder
  action      TEXT NOT NULL,
  sent_at     TIMESTAMPTZ NOT NULL DEFAULT now(),
  result      TEXT NOT NULL CHECK (result IN ('sent', 'skipped', 'error')),
  error_msg   TEXT
);
CREATE INDEX idx_dunning_log_invoice ON dunning_log(invoice_id);

-- dunning behavior toggle on the shared tenant_settings table (base owned by foundation-auth-rbac).
ALTER TABLE tenant_settings ADD COLUMN IF NOT EXISTS dunning_suspend_access BOOLEAN NOT NULL DEFAULT false;
```
**Acceptance:**
- [ ] `pnpm --filter @zync/db build` succeeds; migration applies cleanly to a Neon branch.
- [ ] Both tables exist with UUID PKs, UUID→UUID FKs, the two CHECK constraints, and `UNIQUE (tenant_id, offset_days)`.

### Task 2: DB query layer for schedules & log
**Blocks:** 3,4,5,6  ·  **Blocked by:** 1
**Files:**
- Create: `packages/db/src/queries/dunning.ts`
- Modify: `packages/db/src/queries/index.ts` (export)
**Steps:**
- [ ] `getDunningSchedule(db, tenantId)` → all `dunning_schedules` rows for tenant ordered by `offset_days ASC`.
- [ ] `replaceDunningSchedule(db, tenantId, steps)` → in one DB transaction, delete all rows for tenant then insert the provided steps (replace-all semantics for `PUT`). Reject any step with `offset_days <= 0` (those belong to spec 79).
- [ ] `getDunningLog(db, invoiceId)` → `dunning_log` rows for an invoice ordered by `sent_at ASC`.
- [ ] `insertDunningLog(db, { tenantId, invoiceId, offsetDays, action, result, errorMsg })`.
- [ ] `findPendingDunningSteps(db)` → runs the cron query (Task 6 DDL) returning matched rows.
- [ ] `getDunningSuspendFlag(db, tenantId)` → reads `tenant_settings.dunning_suspend_access` for the tenant (default false).
- [ ] `setDunningSuspendFlag(db, tenantId, enabled)` → updates `tenant_settings.dunning_suspend_access` for the tenant.
- [ ] All queries scoped via `tenantQuery`/`systemQuery`; no raw Drizzle leaks to routes.
**Schema / Interfaces:**
```ts
export interface DunningStep { offset_days: number; action: 'email_reminder' | 'suspend_access' | 'flag_for_review'; email_template_id?: string | null }
export interface DunningLogEntry { id: string; invoice_id: string; offset_days: number; action: string; sent_at: string; result: 'sent' | 'skipped' | 'error'; error_msg: string | null }
export interface PendingDunningRow { invoice_id: string; tenant_id: string; due_date: string; customer_id: string; offset_days: number; action: string; email_template_id: string | null }

export function getDunningSchedule(db: Db, tenantId: string): Promise<DunningStep[]>
export function replaceDunningSchedule(db: Db, tenantId: string, steps: DunningStep[]): Promise<void>
export function getDunningLog(db: Db, invoiceId: string): Promise<DunningLogEntry[]>
export function insertDunningLog(db: Db, row: { tenantId: string; invoiceId: string; offsetDays: number; action: string; result: 'sent' | 'skipped' | 'error'; errorMsg?: string | null }): Promise<void>
export function findPendingDunningSteps(db: Db): Promise<PendingDunningRow[]>
export function getDunningSuspendFlag(db: Db, tenantId: string): Promise<boolean>
export function setDunningSuspendFlag(db: Db, tenantId: string, enabled: boolean): Promise<void>
```
**Acceptance:**
- [ ] `replaceDunningSchedule` rejects (throws/422 upstream) any step with `offset_days <= 0`.
- [ ] `replaceDunningSchedule` is atomic — a failed insert rolls back the delete.
- [ ] `getDunningSuspendFlag` returns `false` when the JSONB key is absent.

### Task 3: Dunning execution service (per-step action runner)
**Blocks:** 5,6  ·  **Blocked by:** 2
**Files:**
- Create: `apps/zync-api/src/services/dunning.ts`
**Steps:**
- [ ] `executeDunningStep(env, db, row)` dispatches on `row.action`:
  - `email_reminder` → build portal payment link `https://{portalSlug}.zync.is/portal/invoices/{invoiceId}`; call `sendEmail` to `customers.email` with subject/body from the reminder template (custom if `email_template_id` set, else default). On success `insertDunningLog(result:'sent')`; if `customers.email` is null → `insertDunningLog(result:'skipped')`.
  - `flag_for_review` → `createNotification` for OWNER/ADMIN of the tenant (type `invoice_flagged_for_review`, links to `/invoices/:id`); `insertDunningLog(result:'sent')`.
  - `suspend_access` → only if `getDunningSuspendFlag(tenantId)` is true; then `setPortalUserStatus` to `'frozen'` for all `customer_portal_users` where `customer_id = row.customer_id AND tenant_id = row.tenant_id`; `insertDunningLog(result:'sent')`. If flag false → `insertDunningLog(result:'skipped')`.
  - On any thrown error → catch, `insertDunningLog(result:'error', errorMsg)`, continue to next row (one bad step must not abort the batch).
- [ ] Use `setPortalUserStatus` and `createNotification` and `sendEmail` — never raw UPDATE / raw Resend (honors `no-raw-drizzle-from-routes`).
- [ ] Always insert exactly one `dunning_log` row per processed (invoice, offset_days) — this is what makes the cron idempotent.
**Schema / Interfaces:**
```ts
export function executeDunningStep(env: Env, db: Db, row: PendingDunningRow): Promise<void>
export function buildPortalPaymentLink(portalSlug: string, invoiceId: string): string
```
**Acceptance:**
- [ ] `suspend_access` is a no-op (logged `skipped`) when the tenant's `dunning_suspend_access` flag is false.
- [ ] A thrown error in one step logs `error` and does not prevent subsequent rows from processing.
- [ ] Re-running on the same (invoice, offset) does not re-send because a `dunning_log` row now exists (verified via the NOT EXISTS guard in Task 6).

### Task 4: Manual reminder service
**Blocks:** 5  ·  **Blocked by:** 2
**Files:**
- Modify: `apps/zync-api/src/services/dunning.ts`
**Steps:**
- [ ] `sendManualReminder(env, db, tenantId, invoiceId)` loads the invoice + customer, sends the reminder email via `sendEmail` (same template + portal payment link as Task 3), and inserts a `dunning_log` row with `offset_days = -1`, `action = 'email_reminder'`, `result = 'sent'` (or `'skipped'`/`'error'`).
- [ ] Runs regardless of schedule (always sends), so it does NOT consult the NOT EXISTS guard.
**Schema / Interfaces:**
```ts
export function sendManualReminder(env: Env, db: Db, tenantId: string, invoiceId: string): Promise<{ result: 'sent' | 'skipped' | 'error'; error_msg?: string }>
```
**Acceptance:**
- [ ] Produces a `dunning_log` row with `offset_days = -1` distinguishable from scheduled steps.
- [ ] Sends even when scheduled steps for the same invoice already exist.

### Task 5: API routes (`/api/settings/dunning`, invoice dunning)
**Blocks:** 8,9  ·  **Blocked by:** 2,3,4
**Files:**
- Create: `apps/zync-api/src/routes/dunning.ts`
- Modify: `apps/zync-api/src/index.ts` (mount router)
**Steps:**
- [ ] `GET /api/settings/dunning` → `authMiddleware` + `requireTier('business')` + `requirePermission` (OWNER/ADMIN); returns `{ steps: DunningStep[], suspend_access: boolean }` (schedule via `getDunningSchedule`, flag via `getDunningSuspendFlag`).
- [ ] `PUT /api/settings/dunning` → `authMiddleware` + `requireTier('business')` + OWNER only; Zod-validate `{ steps: [{ offset_days:int>0, action:enum, email_template_id?:uuid }], suspend_access?: boolean }`; call `replaceDunningSchedule` then `setDunningSuspendFlag`. Reject `offset_days <= 0` with 422.
- [ ] `POST /api/invoices/:id/dunning/remind` → `authMiddleware` + `requirePermission('invoices:write')`; calls `sendManualReminder`; returns the result.
- [ ] `GET /api/invoices/:id/dunning` → `authMiddleware` + `requirePermission('invoices:read')`; returns `getDunningLog` rows.
- [ ] Validate all `:id` params and bodies with Zod (`require-zod-validation-in-routes`).
**Schema / Interfaces:**
```
GET  /api/settings/dunning            → { steps: DunningStep[], suspend_access: boolean }   (OWNER/ADMIN, Business+)
PUT  /api/settings/dunning            body { steps: DunningStep[], suspend_access?: boolean } (OWNER, Business+)
POST /api/invoices/:id/dunning/remind → { result, error_msg? }                              (invoices:write)
GET  /api/invoices/:id/dunning        → DunningLogEntry[]                                    (invoices:read)
```
**Acceptance:**
- [ ] A Freelancer-tier token receives 403 from both `/api/settings/dunning` endpoints (`requireTier` gate).
- [ ] `PUT` with a step `offset_days: 0` or negative returns 422.
- [ ] `GET /api/invoices/:id/dunning` returns the log ordered by `sent_at`.

### Task 6: Dunning cron — scheduled handler + wrangler trigger
**Blocks:** —  ·  **Blocked by:** 2,3
**Files:**
- Create: `apps/zync-api/src/cron/invoice-dunning.ts`
- Modify: `apps/zync-api/src/index.ts` (`scheduled` handler dispatch)
- Modify: `apps/zync-api/wrangler.toml` (cron trigger)
**Steps:**
- [ ] Add wrangler cron trigger `0 8 * * *` (daily 08:00 UTC) — keep this exact time (07:00 reminders / 08:00 dunning separation is load-bearing).
- [ ] In the `scheduled` handler, route the `0 8 * * *` cron to `runInvoiceDunning(env)`.
- [ ] `runInvoiceDunning` calls `findPendingDunningSteps(db)` (the SQL below), then `executeDunningStep` for each matched row.
- [ ] Process rows in batches; wrap each row's execution in its own try/catch (already in Task 3 service) so one failure never aborts the run.
**Schema / Interfaces:**
```sql
-- findPendingDunningSteps: invoices past due with a not-yet-executed dunning step
SELECT i.id AS invoice_id, i.tenant_id, i.due_date, i.customer_id,
       ds.offset_days, ds.action, ds.email_template_id
FROM invoices i
JOIN dunning_schedules ds ON ds.tenant_id = i.tenant_id
WHERE i.status IN ('SENT', 'TAX_ISSUED', 'PARTIALLY_PAID')
  AND i.due_date IS NOT NULL
  AND i.due_date + ds.offset_days * interval '1 day' <= now()
  AND NOT EXISTS (
    SELECT 1 FROM dunning_log dl
    WHERE dl.invoice_id = i.id AND dl.offset_days = ds.offset_days
  );
```
```ts
export async function runInvoiceDunning(env: Env): Promise<void>
```
**Acceptance:**
- [ ] `wrangler.toml` contains `crons = ["0 8 * * *"]` (alongside any existing crons) and the scheduled handler dispatches to `runInvoiceDunning`.
- [ ] Two consecutive runs against the same data send each step exactly once (NOT EXISTS guard + per-step log row).
- [ ] Only invoices in `SENT`/`TAX_ISSUED`/`PARTIALLY_PAID` with a non-null `due_date` are considered.

### Task 7: Default schedule seed on Business+ upgrade
**Blocks:** —  ·  **Blocked by:** 2
**Files:**
- Create: `packages/db/src/queries/dunning-seed.ts` (or extend Task 2 file)
- Modify: `apps/zync-api/src/routes/dunning.ts` (lazy-seed in `GET`)
**Steps:**
- [ ] Implement `seedDunningSchedule(db, tenantId)` inserting the default steps (idempotent — `ON CONFLICT (tenant_id, offset_days) DO NOTHING`): `+3 email_reminder`, `+7 email_reminder`, `+14 email_reminder`, `+21 flag_for_review`.
- [ ] Lazy-seed hook (chosen approach): in `GET /api/settings/dunning`, when the tenant is Business+ and `getDunningSchedule` returns zero rows, call `seedDunningSchedule` first, then return. This avoids a cross-wave edit to the already-built zync-subscription tier-change path while still guaranteeing the documented default appears on first view after upgrade.
**Schema / Interfaces:**
```ts
export function seedDunningSchedule(db: Db, tenantId: string): Promise<void>
// Default rows: [{offset_days:3,action:'email_reminder'},{offset_days:7,action:'email_reminder'},
//                {offset_days:14,action:'email_reminder'},{offset_days:21,action:'flag_for_review'}]
```
**Acceptance:**
- [ ] First `GET /api/settings/dunning` for a Business+ tenant with no rows returns the 4 default steps.
- [ ] `seedDunningSchedule` run twice produces no duplicate rows (ON CONFLICT DO NOTHING).

### Task 8: Settings UI — `/settings/invoicing/dunning`
**Blocks:** —  ·  **Blocked by:** 5
**Files:**
- Create: `apps/zync-app/src/pages/settings/DunningSettingsPage.tsx`
- Create: `apps/zync-app/src/hooks/useDunningSchedule.ts`
- Modify: settings route registry / sidebar nav
**Steps:**
- [ ] `useDunningSchedule` (react-query) wraps `GET`/`PUT /api/settings/dunning`.
- [ ] Render editable rows: numeric `offset_days` input (min 1), `action` select (`email_reminder`/`suspend_access`/`flag_for_review`), `email_template_id` select (Default / custom templates), per-row remove button. "+ Add step" appends a row.
- [ ] Checkbox "Suspend customer portal access on dunning (aggressive)" bound to `suspend_access`.
- [ ] "Save schedule" → `PUT` with replace-all payload; client-side block on `offset_days <= 0` with an inline error.
- [ ] Gate the whole page behind Business+ (`useTierGate` / upgrade modal for lower tiers).
- [ ] **A11y:** each input has an associated `<label>`/`aria-label`; remove buttons have `aria-label`; the select uses `@zync/ui` `Select`. **RTL/Hebrew:** layout uses logical properties; mirror under `dir="rtl"`. **prefers-reduced-motion:** any add/remove row animation respects it.
**Acceptance:**
- [ ] Saving a schedule round-trips: reload shows the saved rows + suspend flag.
- [ ] Setting `offset_days` to 0 shows an inline validation error and blocks save.
- [ ] Page renders correctly mirrored under `dir="rtl"` and passes axe checks for labels/roles.

### Task 9: Invoice detail — Dunning activity panel
**Blocks:** —  ·  **Blocked by:** 5
**Files:**
- Create: `apps/zync-app/src/components/invoices/DunningActivityPanel.tsx`
- Modify: `apps/zync-app/src/pages/invoices/InvoiceDetailPage.tsx` (mount collapsible panel)
- Create: `apps/zync-app/src/hooks/useInvoiceDunning.ts`
**Steps:**
- [ ] `useInvoiceDunning(invoiceId)` wraps `GET /api/invoices/:id/dunning` and the `POST .../remind` mutation.
- [ ] Collapsible "Dunning activity" section: table of log rows (date `sent_at`, offset label `+N days` or `Manual` for `-1`, action, result icon ✓/skipped/error). Pending scheduled steps with no log row may render as "(pending)".
- [ ] "Send manual reminder" button → `POST /api/invoices/:id/dunning/remind`, gated by `invoices:write`; on success invalidate the query + toast.
- [ ] **A11y:** collapsible uses proper `aria-expanded`/`aria-controls`; result icons have text alternatives; live region announces send result. **RTL** mirrored; **reduced-motion** respected on the expand/collapse.
**Acceptance:**
- [ ] Panel lists dunning_log rows for the invoice in chronological order with correct offset labels (incl. `Manual` for -1).
- [ ] "Send manual reminder" adds a new log row visible after refetch.
- [ ] Button is hidden/disabled for users lacking `invoices:write`.

### Task 10: i18n strings, RTL/a11y verification & tests
**Blocks:** —  ·  **Blocked by:** 8,9
**Files:**
- Modify: `packages/i18n` (or app locale files) — `en` + `he` keys
- Create: `apps/zync-api/test/dunning.test.ts`
**Steps:**
- [ ] Add `en` and `he` translation keys for all new UI labels (schedule headings, action names, suspend checkbox, manual reminder, result statuses). No hardcoded strings in components.
- [ ] Tests: cron idempotency (two runs send once), `offset_days <= 0` rejected by `PUT`, `requireTier` 403 for Freelancer, `suspend_access` skipped when flag off, manual reminder logs `offset_days = -1`.
- [ ] Verify RTL mirroring and axe-clean on both new UI surfaces.
**Acceptance:**
- [ ] All new visible text resolves from `en` and `he` locale catalogs.
- [ ] Test suite passes covering idempotency, tier gate, validation, suspend opt-in, and manual reminder.