Payment Record Architecture

Implementation plan for ADR-008. Eliminates Stripe metadata dependencies, unifies payment records, and refactors webhooks to operate on pre-existing DB records.

Scope

In: Schema changes, data migration, webhook refactors, checkout initialization changes, confirmation page, transfer handler, field cleanups, bookkeeping view simplification. All UI changes needed to keep the app functional.

Out: Phase 0 immediate fixes (separate agent). New features. UI improvements beyond correctness.

Deployment: All PRs merged in a single maintenance window. No backward compatibility. No transition period.

PR Overview

PR	Scope	Files (est.)	Risk
1	Model renames	~150	Low (mechanical)
2	Unified Payment model + data migration	~55	Medium (data integrity)
3	Subscription renewal handler refactors	~10	High (revenue path)
4	Schema + subscription_create handler	~15	High (revenue path)
5	One-time purchase checkout init + webhooks	~30	High (revenue path)
6	Confirmation + transfers + field cleanups	~35	Medium

All PRs target main. Each is independently reviewable but not independently deployable.

PR 1: Model Renames

No logic changes. Rename Prisma models; keep DB table names via existing @@map.

Current	New	Occurrences	Files
`Waitlist`	`WaitlistEntry`	120	37
`UserCreditBalance`	`CreditBalance`	447	75
`CheckoutSession`	`CheckoutInvitation`	275	40

ReservationPayment and WaitlistPayment are NOT renamed here — they're replaced by Payment in PR 2.

Schema

Rename Prisma model names. @@map already exists on all three (waitlists, user_credit_balances, checkout_sessions), so no DB migration needed — just a model rename in the schema and code.

CreditBalance services merge

Merge UserCreditBalanceService.ts (single create function) into CreditBalanceService.ts. Delete UserCreditBalanceService.ts. Update all imports.

Key files

apps/api/prisma/schema.prisma
All services, routes, tests, scripts, and web components referencing these models
apps/api/src/services/user/WaitlistService.ts — rename file
apps/api/src/services/credits/UserCreditBalanceService.ts — merge into CreditBalanceService.ts

Testing

All existing tests pass with just the renames. No new tests needed.

PR 2: Unified Payment Model

New schema

enum PaymentStatus {
  PENDING
  PAID
  FAILED
  RETRYING
}

model Payment {
  id                    String         @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  status                PaymentStatus  @default(PENDING)

  stripeInvoiceId       String?        @map("stripe_invoice_id")
  stripeSubscriptionId  String?        @map("stripe_subscription_id")
  stripeChargeId        String?        @map("stripe_charge_id")
  stripePaymentIntentId String?        @map("stripe_payment_intent_id")

  reservationId         String?        @db.Uuid @map("reservation_id")
  reservation           Reservation?   @relation(fields: [reservationId], references: [id])
  creditBalanceId       String?        @db.Uuid @map("credit_balance_id")
  creditBalance         CreditBalance? @relation(fields: [creditBalanceId], references: [id])
  waitlistEntryId       String?        @db.Uuid @map("waitlist_entry_id")
  waitlistEntry         WaitlistEntry? @relation(fields: [waitlistEntryId], references: [id])

  transactionId         String?        @db.Uuid @map("transaction_id")
  transaction           Transaction?   @relation(fields: [transactionId], references: [id])
  amountCents           Int?           @map("amount_cents")
  recurringAmountCents  Int?           @map("recurring_amount_cents")
  billingDate           DateTime?      @map("billing_date") @db.Timestamptz(6)
  createdAt             DateTime       @default(now()) @map("created_at") @db.Timestamptz(6)

  @@index([reservationId])
  @@index([creditBalanceId])
  @@index([waitlistEntryId])
  @@index([transactionId])
  @@index([stripeInvoiceId])
  @@index([stripeSubscriptionId])
  @@index([stripePaymentIntentId])
  @@map("payments")
}

PaymentStatus reuses the same values as the existing ReservationPaymentStatus enum. Drop the old enum after migration.

Data migration

Single Prisma migration with SQL:

-- 1. Create payments table (Prisma handles this from schema)

-- 2. Migrate ReservationPayment → Payment
INSERT INTO payments (
  id, status, stripe_invoice_id, stripe_subscription_id,
  reservation_id, transaction_id, amount_cents,
  recurring_amount_cents, billing_date, created_at
)
SELECT
  id, status::text::payment_status, stripe_invoice_id, stripe_subscription_id,
  reservation_id, transaction_id, amount_cents,
  recurring_amount_cents, billing_date, created_at
FROM reservation_payments;

-- 3. Migrate WaitlistPayment → Payment
--    Pull financial fields from the waitlists table
INSERT INTO payments (
  id, status, stripe_charge_id, stripe_payment_intent_id,
  waitlist_entry_id, transaction_id, amount_cents, created_at
)
SELECT
  wp.id,
  'PAID'::payment_status,
  w.stripe_charge_id,
  w.stripe_checkout_id,  -- this is actually the PI id
  wp.waitlist_id,
  wp.transaction_id,
  w.gross_amount_cents,
  wp.created_at
FROM waitlist_payments wp
JOIN waitlists w ON w.id = wp.waitlist_id;

-- 4. Backfill credit subscription renewals (no payment record today)
INSERT INTO payments (
  id, status, stripe_invoice_id, stripe_subscription_id,
  credit_balance_id, transaction_id, created_at
)
SELECT
  gen_random_uuid(),
  'PAID'::payment_status,
  t.stripe_invoice_id,
  ucb.stripe_subscription_id,
  t.balance_id,
  t.id,
  t.created_at
FROM transactions t
JOIN user_credit_balances ucb ON ucb.id = t.balance_id
WHERE t.reason = 'subscription_renewal'
  AND t.unit = 'CREDITS'
  AND t.stripe_invoice_id IS NOT NULL;

-- 5. Drop old tables
DROP TABLE reservation_payments;
DROP TABLE waitlist_payments;

Transaction Stripe IDs (stripe_payment_intent_id, stripe_invoice_id) are NOT removed here — webhook code still reads them until PR 4 refactors it. Dropped in PR 5.

Waitlist deposit fields

Data migration copies deposit fields from WaitlistEntry into Payment records (step 3 above). The columns stay on WaitlistEntry until PR 5 — webhook/transfer code still reads them.

stripeChargeId → copied to Payment.stripeChargeId (dropped from WaitlistEntry in PR 5)
grossAmountCents → copied to Payment.amountCents (dropped from WaitlistEntry in PR 5)
stripeCheckoutId → copied to Payment.stripePaymentIntentId (dropped from WaitlistEntry in PR 5)

Keep on WaitlistEntry permanently (transfer events, not payment events):

depositTransferredAt
stripeTransferId
netAmountCents (net after Stripe fees — used for transfer amount)

Code changes

Replace prisma.reservationPayment → prisma.payment (220 occurrences, 39 files)
Replace prisma.waitlistPayment → prisma.payment (22 occurrences, 13 files)
ReservationPaymentService.ts → PaymentService.ts or absorb into existing services
CreditBalanceService — stop querying Transaction.stripeInvoiceId, use Payment.creditBalanceId
Update web components: useReservationPayments → usePayments, staff reservation-payments page
API routes: reservation-payments → payments

Testing

Verify migration preserves all records: row counts, financial totals match
All existing tests pass with Payment model
New test: credit subscription renewal has a Payment record

PR 3: Subscription Renewal Handler Refactors

Fixes the root cause of ADR-008 — the subscription_cycle path stops reading Stripe metadata and uses DB lookups instead. The subscription_create path is deferred to PR 4 because it has side-effect metadata dependencies that require schema changes and checkout init modifications (see "Side-effect metadata" below).

Subscription cycle fix

Current (broken): Iterates invoice line items → getPriceMetadata(priceId) (Stripe API call) → checks price.metadata.resource_id → 4 legacy prices have studio_id instead → silently skips. Credit path delegates to StripeService.handlePaymentSucceeded which reads subscription.metadata.purchaseType and creditPackageId.

New: DB lookup by subscription ID:

subscriptionId = invoice.parent.subscription_details.subscription

reservation = findFirst({ where: { stripeSubscriptionId } })
if reservation → lockout renewal → create Transaction + Payment(PAID)

creditPurchase = findFirst({ where: { stripeSubscriptionId } })
if creditPurchase → credit renewal → create DEBIT Transaction (USD_CENTS) + CreditBalance + CREDIT Transaction (CREDITS) + Payment(PAID)

else → log warning (unknown subscription type)

Remove getPriceMetadata entirely (no remaining callers)
Remove delegation to StripeService.handlePaymentSucceeded from subscription_cycle
Credit renewals now create Payment records and DEBIT/USD_CENTS transactions (both previously missing — initial purchases had both but renewals only had the CREDIT transaction)
All invoice mocks updated to use modern parent.subscription_details.subscription format (Stripe API version 2025-06-30.basil)

Key files

apps/api/src/services/payment/StripeWebhookService.ts — subscription_cycle path only

Testing

subscription_cycle lockout renewal finds reservation by subscriptionId (no price metadata)
subscription_cycle lockout updates existing PENDING payment
subscription_cycle credit renewal creates CreditBalance + Transaction + Payment via DB lookup
subscription_cycle with unknown subscription logs warning, no crash

PR 4: Schema + Subscription Create Handler

Add schema columns needed to eliminate subscription metadata dependencies, then refactor the subscription_create handler to use DB lookups. Extract the handler into its own file.

Schema changes

Add two nullable columns to Reservation:

waitlistEntryId (FK → WaitlistEntry) — set by initialize-lockout when user has a pending deposit
referredByUserId (FK → User) — set by initialize-lockout when request includes referrerId

These replace three subscription metadata fields:

Metadata field	Used for	DB replacement
`subscription.metadata.locationId`	Customer balance transfer, waitlist deposit transfer	Already in DB: `Reservation.locationId`
`subscription.metadata.waitlistId`	Transfer pending waitlist deposit to location	`Reservation.waitlistEntryId` (new)
`subscription.metadata.referrerId`	Referral credit/payout processing	`Reservation.referredByUserId` (new)

Checkout init changes (`initialize-lockout`)

After creating the Stripe subscription, update the reservation with:

stripeSubscriptionId — currently set later by fulfillLockout; moving earlier so the handler can find the reservation by subscriptionId
waitlistEntryId — when user has a pending waitlist deposit (already looked up for the invoice credit)
referredByUserId — when request includes referrerId

Metadata is still SET on the Stripe subscription (unchanged) — the webhook just stops reading it. Same pattern as PR 3's subscription_cycle refactor.

Subscription_create handler refactor

Refactor handleInvoicePaid to route by DB lookup instead of subscription.metadata.purchaseType:

Find Reservation by stripeSubscriptionId → lockout path
Find CreditPurchase by stripeSubscriptionId → credits path
Neither found → fall back to org-dedicated-room metadata check (minor flow, no pre-existing DB entity)

For lockout:

PENDING payment path (lines 410-432): unchanged — create Transaction, transition to PAID
Cold-start path (lines 433-448): still delegates to fulfillLockout (it still reads metadata for reservation setup details like insurance, access codes — cleaning that up is future work)
Side effects (lines 451-546): replace metadata reads with reservation field reads (reservation.locationId, reservation.waitlistEntryId, reservation.referredByUserId)

For credits:

Find CreditPurchase by subscriptionId → get creditPackageId from the record instead of metadata
Still delegates to fulfillCreditPackage for fulfillment

Extract the subscription_create handler into its own file during this refactor.

Key files

apps/api/prisma/schema.prisma — add columns
apps/api/src/services/payment/StripeWebhookService.ts — subscription_create path
apps/api/src/app/api/checkout/initialize-lockout/route.ts — set subscriptionId + new columns

Testing

subscription_create lockout finds reservation by subscriptionId (no metadata routing)
subscription_create lockout with PENDING payment transitions to PAID
subscription_create lockout cold-start still works via fulfillLockout fallback
subscription_create lockout runs side effects (waitlist transfer, referral) from DB fields
subscription_create credit finds CreditPurchase by subscriptionId
subscription_create with unknown subscription logs warning

PR 5: One-Time Purchase Checkout Init + Webhook Refactors

Move entity creation from webhooks to checkout initialization for all one-time purchase types. Webhooks become find-and-confirm. Extract handlers into separate files.

Hourly reservations (`initialize-hourly` + `handleHourlyReservationPayment`)

Current: CheckoutInvitation stores params → webhook creates Reservation + Payment from PI metadata
New: Checkout init creates Reservation (optimistic) + Payment(PENDING) → webhook finds Payment by paymentId metadata, creates Transaction, marks PAID
Need: payment failure handling — cancel reservation, revoke access codes, release availability

Batch hourly (`initialize-hourly` batch path + `handleBatchHourlyReservationPayment`)

Same pattern. Multiple reservations created at init.

Credit one-time (`initialize-credits` + `handleOnetimeCreditPurchase`)

CreditPurchase already created at init (PENDING) — good
Add: create Payment(PENDING) at init → webhook finds Payment, marks PAID

Waitlist deposit (`initialize-waitlist` + `handleWaitlistPaymentIntent` + `FulfillmentService.fulfillWaitlist`)

Current: No WaitlistEntry exists before payment. FulfillmentService.fulfillWaitlist creates it in the webhook from PI metadata.
New: Checkout init creates WaitlistEntry(PENDING) + Payment(PENDING) → webhook finds Payment, marks PAID, records deposit
Move the WaitlistEntry creation logic from FulfillmentService.fulfillWaitlist into initialize-waitlist.

Webhook handler simplification

After checkout init creates entities, all one-time webhook handlers become find-and-confirm:

payment_intent.succeeded — unified for all one-time purchases:

Find Payment by paymentId from PI metadata
Create Transaction (DEBIT, USD_CENTS)
For credits: create credit grant Transaction (CREDIT, CREDITS) + mark CreditPurchase PAID
Mark Payment PAID

payment_intent.payment_failed — find Payment, mark FAILED:

Hourly: cancel reservation, revoke access codes
Credits: mark CreditPurchase FAILED
Waitlist: cancel WaitlistEntry

Metadata simplification

All Stripe objects get minimal metadata:

metadata: {
  paymentId: payment.id,       // lookup key — the only thing webhooks read
  customerName: user.name,     // Stripe dashboard convenience only
  customerEmail: user.email,   // Stripe dashboard convenience only
  purchaseType: 'hourly',      // Stripe dashboard convenience only
}

Remove from metadata: resourceId, startTime, endTime, userId, creditPackageId, checkoutSessionId, locationId, etc.

Payment failure handling for hourly

Hourly reservations created optimistically need failure cleanup. Model on existing lockout cancellation (via handleSubscriptionDeleted):

Cancel reservation
Revoke access codes (if granted)
Release availability blocks via updateResourceAvailability
Mark Payment FAILED

Key files

apps/api/src/services/payment/StripeWebhookService.ts — one-time purchase handlers
apps/api/src/services/payment/FulfillmentService.ts (fulfillWaitlist moves to init)
apps/api/src/app/api/checkout/initialize-hourly/route.ts
apps/api/src/app/api/checkout/initialize-waitlist/route.ts
apps/api/src/app/api/checkout/initialize-credits/route.ts

Testing

payment_intent.succeeded finds pre-existing Payment
payment_intent.payment_failed cancels optimistic reservation
Waitlist checkout creates WaitlistEntry at init, webhook confirms
Batch hourly creates multiple reservations at init

PR 6: Confirmation + Transfers + Field Cleanups

Confirmation page

Current: useCheckoutSessionPolling.ts polls GET /checkout/session/{id} every 1500ms for up to 30s, checking params.reservationIds written back by the webhook.

New: Poll Payment status directly. Since the reservation exists from checkout init, the confirmation page has the reservation ID immediately. It just needs to know when payment is confirmed.

Files:

apps/web/src/hooks/api/useCheckoutSessionPolling.ts → rewrite to poll Payment status
apps/web/src/app/reservations/confirmation/ConfirmationPolling.tsx
apps/web/src/app/reservations/confirmation/page.tsx
apps/api/src/app/api/checkout/session/[id]/route.ts → add/modify payment status endpoint
Remove webhook write-back to CheckoutInvitation.params (in handleHourlyReservationPayment line 819-834 and handleBatchHourlyReservationPayment)

Transfer handler

Current: handleChargeUpdated (line 2123) reads paymentIntent.metadata.purchaseType and paymentIntent.metadata.locationId, or falls back to subscription.metadata.purchaseType and subscription.metadata.locationId.

New: DB lookup chain:

charge.payment_intent → Payment (by stripePaymentIntentId)
  → reservation → resource → location → stripeAccountId

For subscription charges without a direct PI match, use Payment.stripeInvoiceId or Payment.stripeSubscriptionId.

Transaction.reason enum

Current: free-form String with 17+ distinct values mixing snake_case and prose.

enum TransactionReason {
  LOCKOUT_PURCHASE          // 'Initial lockout purchase', 'purchase' (lockout context)
  LOCKOUT_RENEWAL           // 'Monthly lockout payment', 'Monthly renewal' (lockout)
  HOURLY_PURCHASE           // 'reservation', 'hourly_payment_applied', 'hourly_batch_payment_applied'
  CREDIT_PACKAGE_PURCHASE   // 'credit_package_purchase'
  CREDIT_GRANT              // 'purchase' (credit grant from package)
  CREDIT_SUBSCRIPTION_RENEWAL // 'subscription_renewal', 'Monthly renewal' (credit)
  CREDIT_CONSUMPTION        // 'reservation' (credit-paid booking), 'completion'
  CREDIT_RESTORATION        // 'refund' (from BalanceOperationService cancellation path)
  WAITLIST_DEPOSIT           // 'waitlist_purchase'
  RESERVATION_CANCELLATION  // 'reservation_cancellation', 'cancellation' (reservation context)
  WAITLIST_CANCELLATION      // 'waitlist_cancellation', 'cancellation' (waitlist context)
  REFUND                    // 'Refund for charge ...'
  REFERRAL_CREDIT           // 'Referral credit for code ...'
  PROMO_CREDIT              // offer.title (dynamic — title captured from related offer)
  STRIPE_BALANCE_CREDIT     // 'Stripe customer balance credit'
  STRIPE_BALANCE_CHARGE     // 'Stripe customer balance charge'
}

Data migration: UPDATE statements mapping old string values to enum values. The ambiguous ones:

'reservation' is used for both hourly purchases and credit consumption — distinguish by unit (USD_CENTS vs CREDITS)
'purchase' is used for both credit grants and lockout purchases — distinguish by unit
'cancellation' is used for both reservation and customer balance — distinguish by context
'Monthly renewal' is used for both lockout and credit — distinguish by whether balanceId is set

Decision needed: How to handle offer.title (dynamic promo names). Options:

Use PROMO_CREDIT enum + display the offer title from the related offer record
Add a description field for supplementary display text

CreditPurchase.purchaseType enum

enum CreditPurchaseType {
  ONETIME
  SUBSCRIPTION
}

Straightforward migration: 'onetime' → ONETIME, 'subscription' → SUBSCRIPTION.

Remove deferred fields

Deferred from PR 2 so each PR compiles independently (all PRs ship together):

Transaction.stripePaymentIntentId, Transaction.stripeInvoiceId — drop columns after PR 5 stops reading them
WaitlistEntry.stripeCheckoutId, WaitlistEntry.stripeChargeId, WaitlistEntry.grossAmountCents — drop columns after PR 5 stops reading them

Bookkeeping view simplification

Remove metadata fallback paths from L2 attribution. Single join path:

Payment → reservation/creditBalance/waitlistEntry → resource → location

Replace COALESCE chains across charge metadata, subscription metadata, and price metadata.

Testing

Confirmation polling works with Payment-based status
Transfer handler resolves location via DB chain
Transaction.reason enum migration maps all values correctly (verify with SELECT DISTINCT reason FROM transactions)
Bookkeeping views produce same financial totals as before

Risks

Risk	Mitigation
Webhook fires before checkout init commits	Stripe retries failed webhooks. Add short retry/backoff on Payment lookup.
Orphaned records from abandoned checkouts	Periodic cleanup: expire PENDING Payments older than threshold (24h hourly, 7d credits).
Data migration loses records	Verify row counts + financial totals before/after. Run on staging first.
Optimistic hourly needs failure cleanup	Model on existing lockout cancellation. Test payment failure scenarios.
`StripeWebhookService.ts` is 2474 lines	Extract per-type handlers into separate files during PR 4/5 refactors.
Transaction.reason ambiguity (`'reservation'`, `'purchase'`, `'cancellation'` each used for multiple meanings)	Distinguish by `unit` (USD_CENTS vs CREDITS) and `balanceId` presence. Verify mapping against production data before migrating.

Decisions Needed

~~CreditBalance service naming~~ — merge UserCreditBalanceService into CreditBalanceService (decided)
Promo credit display — PROMO_CREDIT enum + derive title from offer record, or add description field to Transaction?
~~StripeWebhookService structure~~ — extract per-type handlers during PR 4 (decided)

Current State

[x] PR 1: Model renames (branch: claude/pr1-model-renames, PR #521)
[x] PR 2: Payment model + data migration (branch: claude/pr2-unified-payment, PR #526)
[x] PR 3: Subscription renewal handler refactors (branch: claude/pr3-subscription-handlers, PR #528)
[x] PR 4: Schema + subscription_create handler (branch: claude/pr4-subscription-create-handler, PR #529)
[x] PR 5: One-time purchase checkout init + webhook refactors (branch: claude/pr5-onetime-checkout-init)
[ ] PR 6: Confirmation + transfers + field cleanups

Payment Record Architecture

Scope

PR Overview

PR 1: Model Renames

Schema

CreditBalance services merge

Key files

Testing

PR 2: Unified Payment Model

New schema

Data migration

Waitlist deposit fields

Code changes

Testing

PR 3: Subscription Renewal Handler Refactors

Subscription cycle fix

Key files

Testing

PR 4: Schema + Subscription Create Handler

Schema changes

Checkout init changes (initialize-lockout)

Subscription_create handler refactor

Key files

Testing

PR 5: One-Time Purchase Checkout Init + Webhook Refactors

Hourly reservations (initialize-hourly + handleHourlyReservationPayment)

Batch hourly (initialize-hourly batch path + handleBatchHourlyReservationPayment)

Credit one-time (initialize-credits + handleOnetimeCreditPurchase)

Waitlist deposit (initialize-waitlist + handleWaitlistPaymentIntent + FulfillmentService.fulfillWaitlist)

Webhook handler simplification

Metadata simplification

Payment failure handling for hourly

Key files

Testing

PR 6: Confirmation + Transfers + Field Cleanups

Confirmation page

Transfer handler

Transaction.reason enum

CreditPurchase.purchaseType enum

Remove deferred fields

Bookkeeping view simplification

Testing

Risks

Decisions Needed

Current State

Checkout init changes (`initialize-lockout`)

Hourly reservations (`initialize-hourly` + `handleHourlyReservationPayment`)

Batch hourly (`initialize-hourly` batch path + `handleBatchHourlyReservationPayment`)

Credit one-time (`initialize-credits` + `handleOnetimeCreditPurchase`)

Waitlist deposit (`initialize-waitlist` + `handleWaitlistPaymentIntent` + `FulfillmentService.fulfillWaitlist`)