Phase 1 Data Model: Subscription Payment Failure Grace Period & Dunning Emails

Entity: UserPayment (existing table user_payments, extended)

Represents one subscription’s current billing-cycle payment record. This feature reuses the
existing model and adds grace-tracking columns; it does not introduce a new table.

State Transitions (status)

                 invoice.payment_failed (subscription_cycle)
   SETTLED  ──────────────────────────────────────────────────▶  PAST_DUE
      ▲                                                              │
      │   invoice.payment_succeeded / invoice.paid                   │
      └──────────────────────────────────────────────────────────────┘
                                                                      │
                                    customer.subscription.deleted    │
                                    (Stripe auto-cancel at day 7,     ▼
                                     or manual cancel)             DELETED

• SETTLED → PAST_DUE: on invoice.payment_failed for a subscription_cycle invoice. Resets all
  4 grace_*_sent_at columns to null and sets grace_period_started_at to the failed invoice’s
  billing period start — but only when this is a new grace cycle (i.e., status was not
  already PAST_DUE); repeated automatic retry failures within the same cycle do not reset the
  anchor or already-sent flags.
• PAST_DUE → SETTLED: on invoice.payment_succeeded/invoice.paid, guarded so it only fires
  when the current status is PAST_DUE (ignores unrelated success events).
• PAST_DUE → DELETED: on customer.subscription.deleted (Stripe’s own day-7 no-payment
  cancellation, or a manual subscriber cancellation during grace). Existing free-plan downgrade
  cleanup runs unchanged.
• Any status → DELETED remains possible directly from SETTLED too (pre-existing behavior,
  unchanged by this feature).

Validation / Invariants

• grace_period_started_at, and the four *_sent_at columns are only ever set from server-side
  webhook/cron logic — never user-writable.
• A grace_mail_N_sent_at column, once set, is never cleared except when a brand-new grace cycle
  begins (new PAST_DUE entry after a prior recovery).
• Mail 2/3 must only be sent when status === PAST_DUE at send time (re-checked at cron
  execution, not just at scheduling time), per FR-007.

Entity: Notification Email (behavioral entity — implemented as 4 Mailable classes, not a DB table)

Each Mailable receives the UserPayment (for plan/contract/fee/hosted-invoice-URL data) plus any
mail-specific computed value (e.g., the next scheduled payment-retry date shown in the body copy).
No new persistence entity is required for “which emails were sent” — the 4 guard columns on
UserPayment are sufficient given at most one open grace cycle per subscription at a time.

Relationships

• UserPayment.subscription_id ↔ UserPlan.subscription_id (existing, unchanged) — used to look
  up plan name/contract period/fee for the email body.
• UserPayment.user_id ↔ User (existing) — used to resolve the recipient’s login email via
  User.userLogin.

Out of scope for this data model

• No new “dunning mail history” table — the spec does not require an auditable log/history UI
  (unlike the unrelated, pre-existing order/reservation “payment dunning mail” feature which does
  have its own history table/endpoint). If a future requirement needs a full audit trail, that is a
  separate feature.
• No changes to Cashier’s native subscriptions/subscription_items tables — those continue to be
  synced exactly as they are today.