Fix Payment History Records Implementation Plan

For agentic workers: REQUIRED SUB-SKILL: Use superpowers:subagent-driven-development (recommended) or superpowers:executing-plans to implement this plan task-by-task. Steps use checkbox (- [ ]) syntax for tracking.

Goal: Make the 支払履歴 table on /user/accounts/plan?tab=3 show one row per successful payment by keying user_payments upserts on a new unique stripe_invoice_id column (filled from latest_invoice already present in every subscription payload) and backfilling historical paid invoices from Stripe.

Architecture:

  1. Add stripe_invoice_id (nullable, unique) to user_payments — DB-enforced idempotency.
  2. Change Subscription::newOrUpdateUserSubscription() to upsert by stripe_invoice_id when $data['latest_invoice'] is present, falling back to the current subscription_id-keyed upsert when null. No new webhook handler; customer.subscription.updated already fires on every renewal with a fresh invoice ID.
  3. One-shot artisan command payments:backfill-historical paginates paid Stripe invoices per user and upserts rows by stripe_invoice_id.

Tech Stack: PHP 8.2, Laravel 10, Stripe PHP SDK, PHPUnit.

File Structure

New files:

  • common/database/migrations/2026_07_20_000001_add_stripe_invoice_id_to_user_payments_table.php
  • admin/app/Console/Commands/BackfillHistoricalPayments.php
  • user/tests/Unit/SubscriptionNewOrUpdateUserSubscriptionTest.php
  • user/tests/Feature/PlanPurchaseHistoryActionTest.php
  • admin/tests/Feature/BackfillHistoricalPaymentsTest.php

Modified files:

  • common/src/Models/UserPayment.php — add stripe_invoice_id to $fillable
  • common/src/Packages/Util/Subscription.php — invoice-keyed upsert in newOrUpdateUserSubscription()

Not modified: PaymentWebhookController.php, any route, any FE file, Stripe dashboard.

Task 1: Migration — add stripe_invoice_id with unique index

Files:

  • Create: common/database/migrations/2026_07_20_000001_add_stripe_invoice_id_to_user_payments_table.php

  • Step 1: Create the migration

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    public function up(): void
    {
        Schema::table('user_payments', function (Blueprint $table) {
            $table->string('stripe_invoice_id', 255)
                ->nullable()
                ->after('subscription_id')
                ->comment('Stripe invoice ID (in_...)');
        });

        DB::statement(
            'ALTER TABLE user_payments '
            . 'ADD UNIQUE INDEX user_payments_stripe_invoice_id_unique (stripe_invoice_id) '
            . 'ALGORITHM=INPLACE LOCK=NONE'
        );
    }

    public function down(): void
    {
        DB::statement(
            'ALTER TABLE user_payments '
            . 'DROP INDEX user_payments_stripe_invoice_id_unique '
            . 'ALGORITHM=INPLACE LOCK=NONE'
        );

        Schema::table('user_payments', function (Blueprint $table) {
            $table->dropColumn('stripe_invoice_id');
        });
    }
};
  • Step 2: Run migration locally

Run from yoyacoo_be/admin:

php artisan migrate

Expected: migration succeeds; user_payments has stripe_invoice_id + unique index. Nullable unique index allows multiple NULLs (MySQL), so existing rows are unaffected.

  • Step 3: Commit
git add yoyacoo_be/common/database/migrations/2026_07_20_000001_add_stripe_invoice_id_to_user_payments_table.php
git commit -m "feat(db): add stripe_invoice_id to user_payments with unique index"

Task 2: Add stripe_invoice_id to UserPayment::$fillable

Files:

  • Modify: common/src/Models/UserPayment.php

  • Step 1: Update $fillable — add 'stripe_invoice_id' right after 'subscription_id':

protected $fillable = [
    'user_id',
    'application_date',
    'payment_date',
    'expense',
    'status',
    'plan',
    'subscription_id',
    'stripe_invoice_id',
    'payment_method_id',
    'payment_intent_id',
    'payment_method_type',
    'billing_detail_name',
    'exp_month_card',
    'exp_year_card',
    'display_brand',
    'last4',
];
  • Step 2: Commit
git add yoyacoo_be/common/src/Models/UserPayment.php
git commit -m "feat(model): add stripe_invoice_id to UserPayment fillable"

Task 3: Invoice-keyed upsert in newOrUpdateUserSubscription()

Files:

  • Modify: common/src/Packages/Util/Subscription.php (method at lines 134-172)

  • Step 1: Write the failing unit test first

Create user/tests/Unit/SubscriptionNewOrUpdateUserSubscriptionTest.php:

<?php declare(strict_types=1);

namespace Tests\Unit;

use ReserveApp\Common\Enums\UserPayment\Status as PaymentStatus;
use ReserveApp\Common\Enums\UserPlan\Plan;
use ReserveApp\Common\Models\User;
use ReserveApp\Common\Models\UserPayment;
use ReserveApp\Common\Packages\Util\Subscription;
use Tests\TestCase;

class SubscriptionNewOrUpdateUserSubscriptionTest extends TestCase
{
    use Subscription;

    private function subscriptionPayload(string $subId, ?string $invoiceId): array
    {
        return [
            'id' => $subId,
            'status' => 'active',
            'latest_invoice' => $invoiceId,
            'current_period_start' => now()->subDay()->timestamp,
            'current_period_end' => now()->addMonth()->timestamp,
            'plan' => ['interval' => 'month', 'amount' => 1200],
            'items' => ['data' => [['id' => 'si_x', 'price' => ['id' => 'price_x', 'product' => 'prod_x'], 'quantity' => 1]]],
        ];
    }

    public function test_creates_row_with_invoice_id_when_none_exists(): void
    {
        $user = User::factory()->create();
        $this->newOrUpdateUserSubscription($user, $this->subscriptionPayload('sub_1', 'in_1'), [], Plan::STANDARD->value);

        $row = UserPayment::where('stripe_invoice_id', 'in_1')->first();
        $this->assertNotNull($row);
        $this->assertSame('sub_1', $row->subscription_id);
        $this->assertSame(PaymentStatus::SETTLED->value, $row->status);
    }

    public function test_same_invoice_delivered_twice_does_not_duplicate(): void
    {
        $user = User::factory()->create();
        $payload = $this->subscriptionPayload('sub_1', 'in_1');
        $this->newOrUpdateUserSubscription($user, $payload, [], Plan::STANDARD->value);
        $this->newOrUpdateUserSubscription($user, $payload, [], Plan::STANDARD->value);

        $this->assertSame(1, UserPayment::where('stripe_invoice_id', 'in_1')->count());
    }

    public function test_new_invoice_on_same_subscription_creates_second_row(): void
    {
        $user = User::factory()->create();
        $this->newOrUpdateUserSubscription($user, $this->subscriptionPayload('sub_1', 'in_1'), [], Plan::STANDARD->value);
        $this->newOrUpdateUserSubscription($user, $this->subscriptionPayload('sub_1', 'in_2'), [], Plan::STANDARD->value);

        $this->assertSame(2, UserPayment::where('subscription_id', 'sub_1')->count());
    }

    public function test_null_invoice_falls_back_to_subscription_keyed_upsert(): void
    {
        $user = User::factory()->create();
        $payload = $this->subscriptionPayload('sub_1', null);
        $this->newOrUpdateUserSubscription($user, $payload, [], Plan::STANDARD->value);
        $this->newOrUpdateUserSubscription($user, $payload, [], Plan::STANDARD->value);

        $this->assertSame(1, UserPayment::where('subscription_id', 'sub_1')->whereNull('stripe_invoice_id')->count());
    }
}

Run from yoyacoo_be/user:

./vendor/bin/phpunit tests/Unit/SubscriptionNewOrUpdateUserSubscriptionTest.php

Expected: tests 1, 3, 4 FAIL (current code keys everything on subscription_id); test 2 passes trivially today.

  • Step 2: Modify the upsert in newOrUpdateUserSubscription()

In common/src/Packages/Util/Subscription.php, replace the $user->userPayments()->updateOrCreate(...) block (currently lines 152-169) with:

        $paymentAttributes = [
            'application_date' => $applicationDate,
            'payment_date' => $applicationDate,
            'expense' => (int)$price,
            'last4' => $payment['card']['last4'] ?? null,
            'exp_month_card' => $payment['card']['exp_month'] ?? null,
            'exp_year_card' => $payment['card']['exp_year'] ?? null,
            'billing_detail_name' => $payment['billing_details']['name'] ?? null,
            'payment_method_id' => $payment['id'] ?? null,
            'payment_method_type' => !empty($payment['type'] ?? null) ? 1 : 0,
            'display_brand' => $payment['card']['display_brand'] ?? null,
            'status' => $data['status'] === StripeSubscription::STATUS_ACTIVE
                ? PaymentStatus::SETTLED->value
                : PaymentStatus::UNSETTLED->value,
            'plan' => $plan,
        ];

        $invoiceId = $data['latest_invoice'] ?? null;
        // $data['latest_invoice'] can be an Invoice object in some SDK paths; normalize to string id.
        if (is_object($invoiceId)) {
            $invoiceId = $invoiceId->id ?? null;
        }

        if (!empty($invoiceId)) {
            $user->userPayments()->updateOrCreate(
                ['stripe_invoice_id' => $invoiceId],
                $paymentAttributes + ['subscription_id' => $data['id']]
            );
        } else {
            // Legacy fallback (e.g. trialing subscription with no invoice yet).
            $user->userPayments()->updateOrCreate(
                ['subscription_id' => $data['id']],
                $paymentAttributes
            );
        }

Note: updateOrCreate(keys, attributes) merges keys into the created model automatically, so stripe_invoice_id is set on insert. $paymentAttributes + ['subscription_id' => ...] preserves paymentAttributes on key collision — none exist here since subscription_id is not in $paymentAttributes.

  • Step 3: Run the unit test — all 4 pass
./vendor/bin/phpunit tests/Unit/SubscriptionNewOrUpdateUserSubscriptionTest.php

Expected: 4 passed.

  • Step 4: Commit
git add yoyacoo_be/common/src/Packages/Util/Subscription.php yoyacoo_be/user/tests/Unit/SubscriptionNewOrUpdateUserSubscriptionTest.php
git commit -m "feat(subscription): upsert user_payments by stripe_invoice_id (one row per paid invoice)"

Task 4: API shape + status filter feature test

Files:

  • Create: user/tests/Feature/PlanPurchaseHistoryActionTest.php

  • Step 1: Create the test

<?php declare(strict_types=1);

namespace Tests\Feature;

use Laravel\Sanctum\Sanctum;
use ReserveApp\Common\Enums\UserPayment\Status as PaymentStatus;
use ReserveApp\Common\Models\User;
use ReserveApp\Common\Models\UserPayment;
use Tests\TestCase;

class PlanPurchaseHistoryActionTest extends TestCase
{
    public function test_returns_only_settled_payments_with_data_and_pagination_keys(): void
    {
        $user = User::factory()->create();

        UserPayment::factory()->create([
            'user_id' => $user->id,
            'status' => PaymentStatus::SETTLED->value,
            'stripe_invoice_id' => 'in_KEPT',
        ]);
        UserPayment::factory()->create([
            'user_id' => $user->id,
            'status' => PaymentStatus::UNSETTLED->value,
            'stripe_invoice_id' => 'in_UNSETTLED',
        ]);
        UserPayment::factory()->create([
            'user_id' => $user->id,
            'status' => PaymentStatus::DELETED->value,
            'stripe_invoice_id' => 'in_DELETED',
        ]);

        Sanctum::actingAs($user);

        $response = $this->getJson('/api/user/subscriptions/payments?page=1&per_page=10')
            ->assertOk()
            ->assertJsonStructure([
                'data' => [['id', 'billing_date', 'total_amount', 'payment_method', 'last4']],
                'pagination' => ['current', 'last', 'per', 'total'],
            ]);

        $ids = collect($response->json('data'))->pluck('id');
        $kept = UserPayment::where('stripe_invoice_id', 'in_KEPT')->first();
        $this->assertTrue($ids->contains($kept->id));
        $this->assertCount(1, $response->json('data'));
    }
}
  • Step 2: Run
cd yoyacoo_be/user && ./vendor/bin/phpunit tests/Feature/PlanPurchaseHistoryActionTest.php

Expected: passes (existing controller already satisfies this; guards against regression).

  • Step 3: Commit
git add yoyacoo_be/user/tests/Feature/PlanPurchaseHistoryActionTest.php
git commit -m "test(api): cover PlanPurchaseHistory response shape and status filter"

Task 5: Backfill artisan command

Files:

  • Create: admin/app/Console/Commands/BackfillHistoricalPayments.php

  • Step 1: Create the command

<?php declare(strict_types=1);

namespace App\Console\Commands;

use Illuminate\Console\Command;
use Illuminate\Support\Carbon;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use ReserveApp\Common\Enums\UserPayment\Status as PaymentStatus;
use ReserveApp\Common\Models\User;
use ReserveApp\Common\Models\UserPayment;
use ReserveApp\Common\Base\Traits\StripePaymentSubscription;
use Stripe\Invoice as StripeInvoice;

class BackfillHistoricalPayments extends Command
{
    use StripePaymentSubscription;

    protected $signature = 'payments:backfill-historical
        {--dry-run : Report only; insert nothing}
        {--user= : Process only this user_id}';

    protected $description = 'Backfill user_payments rows from paid Stripe invoices (keyed by stripe_invoice_id).';

    public function handle(): int
    {
        $dryRun = (bool) $this->option('dry-run');
        $userIdFilter = $this->option('user');

        $scanned = $inserted = $skipped = $errors = 0;

        $query = User::query()->whereHas('userPaymentSetting', fn ($q) => $q->whereNotNull('stripe_id'));
        if ($userIdFilter) {
            $query->where('id', (int) $userIdFilter);
        }

        $query->chunkById(100, function ($users) use (&$scanned, &$inserted, &$skipped, &$errors, $dryRun) {
            foreach ($users as $user) {
                $scanned++;
                $stripeId = $user->userPaymentSetting->stripe_id ?? null;
                if (!$stripeId) {
                    $skipped++;
                    continue;
                }

                foreach ($this->fetchPaidInvoices($stripeId) as $invoice) {
                    try {
                        if (UserPayment::where('stripe_invoice_id', $invoice->id)->exists()) {
                            $skipped++;
                            continue;
                        }

                        $priceProduct = $invoice->lines->data[0]->price->product ?? null;
                        $config = $priceProduct ? $user->getUserPlanConfigByIdProduct($priceProduct) : null;
                        if (!$config) {
                            Log::warning('[backfill] plan not resolved', [
                                'user_id' => $user->id,
                                'invoice' => $invoice->id,
                                'price_product' => $priceProduct,
                            ]);
                            $skipped++;
                            continue;
                        }

                        if ($dryRun) {
                            $this->line("[dry-run] would insert invoice={$invoice->id} user={$user->id}");
                            $inserted++;
                            continue;
                        }

                        DB::transaction(function () use ($user, $invoice, $config) {
                            $this->insertFromInvoice($user, $invoice, $config['plan']);
                        });
                        $inserted++;
                    } catch (\Throwable $e) {
                        $errors++;
                        Log::error('[backfill] failed', [
                            'user_id' => $user->id,
                            'invoice' => $invoice->id ?? null,
                            'error' => $e->getMessage(),
                        ]);
                    }
                }
            }
        });

        $this->info("scanned={$scanned} inserted={$inserted} skipped={$skipped} errors={$errors}");
        return self::SUCCESS;
    }

    private function insertFromInvoice(User $user, StripeInvoice $invoice, int $plan): void
    {
        $last4 = $expMonth = $expYear = $displayBrand = $billingName = $paymentMethodId = null;
        $paymentMethodType = 0;

        $pmId = $invoice->payment->payment_method ?? null;
        if ($pmId) {
            try {
                $pm = $this->getPaymentMethod($pmId);
                $paymentMethodId = $pm->id ?? null;
                $last4 = $pm->card->last4 ?? null;
                $expMonth = $pm->card->exp_month ?? null;
                $expYear = $pm->card->exp_year ?? null;
                $displayBrand = $pm->card->display_brand ?? null;
                $billingName = $pm->billing_details->name ?? null;
                $paymentMethodType = !empty($pm->type) ? 1 : 0;
            } catch (\Throwable $e) {
                Log::warning('[backfill] payment method unavailable', [
                    'invoice' => $invoice->id,
                    'error' => $e->getMessage(),
                ]);
            }
        }

        $paidAt = !empty($invoice->status_transitions->paid_at)
            ? Carbon::createFromTimestamp($invoice->status_transitions->paid_at)->timezone('Asia/Tokyo')
            : now();

        $subscriptionId = is_string($invoice->subscription)
            ? $invoice->subscription
            : ($invoice->subscription->id ?? null);

        $user->userPayments()->updateOrCreate(
            ['stripe_invoice_id' => $invoice->id],
            [
                'subscription_id' => $subscriptionId,
                'application_date' => $paidAt,
                'payment_date' => $paidAt,
                'expense' => (int) $invoice->amount_paid,
                'payment_method_id' => $paymentMethodId,
                'payment_method_type' => $paymentMethodType,
                'last4' => $last4,
                'exp_month_card' => $expMonth,
                'exp_year_card' => $expYear,
                'display_brand' => $displayBrand,
                'billing_detail_name' => $billingName,
                'status' => PaymentStatus::SETTLED->value,
                'plan' => $plan,
            ]
        );
    }

    /** @return iterable<\Stripe\Invoice> */
    private function fetchPaidInvoices(string $stripeCustomerId): iterable
    {
        $params = ['customer' => $stripeCustomerId, 'status' => 'paid', 'limit' => 100];

        do {
            $page = $this->callStripeWithRetry(fn () => StripeInvoice::all($params));
            foreach ($page->data as $invoice) {
                yield $invoice;
            }
            $params['starting_after'] = $page->has_more ? end($page->data)->id : null;
        } while ($page->has_more);
    }

    private function callStripeWithRetry(callable $fn, int $maxAttempts = 5): mixed
    {
        $attempt = 0;
        while (true) {
            try {
                return $fn();
            } catch (\Stripe\Exception\RateLimitException $e) {
                if (++$attempt > $maxAttempts) {
                    throw $e;
                }
                sleep(min(30, 2 ** $attempt));
            }
        }
    }
}

Verify StripePaymentSubscription trait exposes getPaymentMethod($id) — it is already used by PlanPurchaseLastInteractor and PaymentWebhookController, so the method exists on that trait.

  • Step 2: Verify registration
cd yoyacoo_be/admin && php artisan list | grep payments:backfill-historical

Expected: command listed.

  • Step 3: Commit
git add yoyacoo_be/admin/app/Console/Commands/BackfillHistoricalPayments.php
git commit -m "feat(command): add payments:backfill-historical artisan command"

Task 6: Backfill command test

Files:

  • Create: admin/tests/Feature/BackfillHistoricalPaymentsTest.php

  • Step 1: Create the test

<?php declare(strict_types=1);

namespace Tests\Feature;

use Illuminate\Support\Facades\Artisan;
use ReserveApp\Common\Models\User;
use ReserveApp\Common\Models\UserPayment;
use ReserveApp\Common\Models\UserPaymentSetting;
use Tests\TestCase;

class BackfillHistoricalPaymentsTest extends TestCase
{
    public function test_inserts_rows_for_paid_invoices_and_is_idempotent(): void
    {
        $user = User::factory()->create();
        UserPaymentSetting::create([
            'user_id' => $user->id,
            'stripe_id' => 'cus_TEST_'.uniqid(),
        ]);

        $this->fakeStripeInvoices([$this->fakeInvoice('in_A'), $this->fakeInvoice('in_B')]);

        $this->assertSame(0, Artisan::call('payments:backfill-historical', ['--user' => $user->id]));
        $this->assertSame(1, UserPayment::where('stripe_invoice_id', 'in_A')->count());
        $this->assertSame(1, UserPayment::where('stripe_invoice_id', 'in_B')->count());

        // Second run inserts nothing.
        Artisan::call('payments:backfill-historical', ['--user' => $user->id]);
        $this->assertSame(1, UserPayment::where('stripe_invoice_id', 'in_A')->count());
    }

    public function test_dry_run_inserts_nothing(): void
    {
        $user = User::factory()->create();
        UserPaymentSetting::create([
            'user_id' => $user->id,
            'stripe_id' => 'cus_TEST_'.uniqid(),
        ]);

        $this->fakeStripeInvoices([$this->fakeInvoice('in_C')]);

        Artisan::call('payments:backfill-historical', ['--user' => $user->id, '--dry-run' => true]);
        $this->assertSame(0, UserPayment::where('stripe_invoice_id', 'in_C')->count());
    }

    private function fakeInvoice(string $id): array
    {
        return [
            'object' => 'invoice',
            'id' => $id,
            'amount_paid' => 1200,
            'currency' => 'jpy',
            'status' => 'paid',
            'customer' => 'cus_TEST',
            'subscription' => 'sub_TEST_'.$id,
            'lines' => ['data' => [['price' => ['product' => 'prod_TEST_default']]]],
            'payment' => ['payment_method' => null],
            'status_transitions' => ['paid_at' => time()],
        ];
    }

    /**
     * Stub Stripe\Invoice::all. If the project's test setup cannot mock
     * static Stripe calls, inject a fetcher instead: extract
     * fetchPaidInvoices() into a constructor-injected collaborator and
     * bind a fake in the container here.
     */
    private function fakeStripeInvoices(array $invoices): void
    {
        $page = new \Stripe\Collection();
        $page->data = array_map(fn ($i) => new \Stripe\Invoice($i), $invoices);
        $page->has_more = false;

        \Stripe\Invoice::shouldReceive('all')->andReturn($page);
    }
}

Note: getUserPlanConfigByIdProduct('prod_TEST_default') must resolve to a plan in the test env — seed the plan config or use a product ID from the project’s test fixtures. If the user factory doesn’t wire plan config, seed the minimum required records first (mirror how existing subscription tests do it).

  • Step 2: Run
cd yoyacoo_be/admin && ./vendor/bin/phpunit tests/Feature/BackfillHistoricalPaymentsTest.php

Expected: 2 passed.

  • Step 3: Commit
git add yoyacoo_be/admin/tests/Feature/BackfillHistoricalPaymentsTest.php
git commit -m "test(command): cover backfill insert, idempotency, dry-run"

Task 7: Quality gates

  • Step 1: Full test suites
cd yoyacoo_be/admin && ./vendor/bin/phpunit
cd yoyacoo_be/user && ./vendor/bin/phpunit

Expected: all pass (new + existing). Pay attention to existing subscription/order tests that assert user_payments counts — the invoice-keyed upsert changes insert semantics; update those fixtures to include latest_invoice if they break.

  • Step 2: Static analysis
cd yoyacoo_be && composer phpstan

Expected: no new errors.

  • Step 3: Migration round-trip check
cd yoyacoo_be/admin && php artisan migrate:rollback --step=1 && php artisan migrate

Expected: down + up both succeed.

  • Step 4: Final commit (only if gates produced changes)
git add -A
git commit -m "chore: quality gates pass after payment history fix"

Post-deploy verification (staging, then production)

  1. php artisan payments:backfill-historical --dry-run → sane counts, errors=0.
  2. php artisan payments:backfill-historical → completes.
  3. Open https://stg.yoyacoo.jp/user/accounts/plan?tab=3 for ≥ 3 accounts with renewal history → expect one row per paid invoice.
  4. Force a renewal in Stripe test mode → new row appears (via existing subscription.updated webhook, no dashboard change needed).
  5. Re-run backfill → inserted=0 (idempotent).
  6. Repeat backfill on production during low-traffic window.

Out of scope (follow-up tickets)

  • PlanPurchaseHistoryCollection::toArray() paginator handling cleanup.
  • PlanPurchaseLastResource hardcoded PaymentMethod::from(1).
  • Receipt modal linkage via stripe_invoice_id.
  • Optional: invoice.payment_succeeded handler for amount-paid accuracy at event time.
Previous Fix Payment History Records — Specification Next Subscription History Fix Deployment Runbook