Fix Payment History Records — Specification

Status: Confirmed (2026-07-20, rev.2 — keeps stripe_invoice_id + unique index; drops new webhook handler)
Scope: Backend (Laravel) only. One migration, one trait change, one artisan command, tests. No frontend, no Stripe dashboard changes, no new webhook events.

1. Overview

1.1 Problem

On /user/accounts/plan?tab=3 (支払・履歴 tab), every supplier account shows exactly 2 rows in the 支払履歴 (payment history) table — the initial subscription row and the most recent plan-change row. The table must show one row per payment occurrence (one per successful monthly/yearly renewal).

1.2 Root cause

ReserveApp\Common\Packages\Util\Subscription::newOrUpdateUserSubscription() writes to user_payments via:

$user->userPayments()->updateOrCreate(
    ['subscription_id' => $data['id']],
    [/* payment_date, expense, status, ... */]
);

Stripe keeps the same subscription_id across every renewal. The customer.subscription.updated webhook fires on every renewal and calls this method, which updates the same row — overwriting payment_date and expense — instead of inserting a new one. Only a plan change creates a new row, because PaymentInteractor creates a brand-new Stripe subscription (new subscription_id). Hence exactly 2 rows per account.

1.3 Goal

The 支払履歴 table shows one row per successful payment, with correct billing_date, total_amount, payment_method, last4 — going forward (via the existing renewal webhook) and for the past (via a one-shot backfill from Stripe invoices).

1.4 Out of scope

• Frontend changes (page, table, OpenAPI contract are correct as-is).
• New Stripe webhook events or dashboard reconfiguration.
• Receipt modal linking by invoice (follow-up ticket).
• Follow-up cleanups noted previously: PlanPurchaseHistoryCollection::toArray() paginator handling; PlanPurchaseLastResource hardcoded PaymentMethod::from(1).

2. Design (rev.2)

2.1 Key insight

Every code path that writes a payment row already has the Stripe invoice ID available as $data['latest_invoice'] (present in the subscription payload used by both OrderInteractor and PaymentWebhookController::handleCustomerSubscriptionUpdated). We therefore key the payment-row upsert on the invoice ID — which is unique per payment — instead of the subscription ID — which is constant per subscription. No new webhook event is needed because customer.subscription.updated already fires on every renewal with a fresh latest_invoice.

2.2 Data model

Add one column to user_payments:

• Index added online (ALGORITHM=INPLACE, LOCK=NONE).
• subscription_id stays a plain FK; one subscription now legitimately has many user_payments rows.
• Add stripe_invoice_id to UserPayment::$fillable.

2.3 Upsert rule change (the core fix)

In newOrUpdateUserSubscription():

invoiceId = $data['latest_invoice'] ?? null

if not invoiceId:
    return // Skip saving. A trial without an invoice is not a payment.

userPayments->updateOrCreate(['stripe_invoice_id' => invoiceId], payload)

Behavior per scenario:

expense fidelity is unchanged from today: handleCustomerSubscriptionUpdated and OrderInteractor both already set plan.amount from invoice.amount_paid when available.

2.4 Backfill (one-shot artisan command)

php artisan payments:backfill-historical {--dry-run}:

• Pre-requisite: Clear legacy, inaccurate rows to prevent duplication. Before inserting, delete all user_payments where subscription_id IS NOT NULL (or do this in the up() migration).
• Global Stream: Paginate Stripe\Invoice::all(['status' => 'paid', 'limit' => 100]) globally instead of iterating per user. This drastically reduces Stripe API calls from $O(users)$ to $O(invoices / 100)$ and naturally avoids rate limits.
• Match invoice customer to user_payment_settings.stripe_id to get the user_id.
• Per invoice: resolve plan via getUserPlanConfigByIdProduct(invoice.lines.data[0].price.product); skip + log if unresolved.
• Insert via UserPayment::updateOrCreate(['stripe_invoice_id' => invoice.id], [...]) with payment_date = invoice.status_transitions.paid_at (Asia/Tokyo), expense = amount_paid, status = SETTLED, card details from the invoice’s payment method when retrievable.
• --dry-run reports counts without inserting/deleting. Output: scanned / inserted / skipped / errors.

Note: By treating Stripe as the absolute source of truth and rebuilding the table, we avoid complex legacy-row duplication scenarios.

2.5 Tests

• Trait unit test (common or user app): newOrUpdateUserSubscription —
  1. no existing row → creates one with stripe_invoice_id;
  2. same invoice ID delivered twice → still one row (update, not insert);
  3. new invoice ID on existing subscription → second row created (renewal);
  4. null latest_invoice → legacy subscription-keyed upsert (no regression).
• API feature test: GET /api/user/subscriptions/payments returns {data, pagination} shape and only SETTLED rows.
• Command feature test: inserts rows for paid invoices; second run inserts 0; --dry-run inserts 0.

2.6 Deploy order

1. Migration (additive, online index).
2. Trait + command code.
3. Run backfill on staging → verify UI → run on production (low-traffic window).

No Stripe dashboard changes. No FE deploy required.

2.7 Rollback

• Revert code; drop column/index via migration down().
• Remove backfilled rows if ever needed: DELETE FROM user_payments WHERE stripe_invoice_id IS NOT NULL; — note this also removes post-fix webhook rows, which can then be rebuilt by re-running the backfill.

3. Acceptance criteria

• An account renewed ≥ 3 times shows ≥ 4 rows in 支払履歴 (initial + renewals). Pre-fix: exactly 2.
• A renewal (staging, Stripe test clock or real) produces a new row keyed by the new invoice ID.
• Redelivering the same customer.subscription.updated payload does not create duplicates (unique index holds).
• Backfill is idempotent: second run reports inserted=0.
• GET /api/user/subscriptions/payments shape unchanged (data + pagination).

4. Explicitly rejected alternatives

• New invoice.payment_succeeded handler (rev.1): more canonical event, but requires dashboard config + new handler + tests. Rejected because subscription.updated already carries latest_invoice and fires on every renewal; the unique index provides the same idempotency. May be added later for amount-paid accuracy at event time.
• Read invoices from Stripe at request time: adds Stripe latency to every page load, complicates pagination across subscriptions, breaks local-data assumptions of receipts.
• Period-keyed idempotency without stripe_invoice_id (rev.2 draft): rejected by requirement — invoice ID must be stored and idempotency DB-enforced.