Migrating to a unified payment order model

The most dangerous point in a payment migration is not a failed ALTER TABLE . It is the moment when both the old and new systems return success but disagree about what constitutes the same order. Course purchases, subscriptions, and coaching bookings often store customers, amounts, and provider references separately. Combining those tables without a contract can grant access twice, miss a refund, or classify an unfinished Checkout as revenue. As of 2026-07-29, Stripe's official guidance still requires signature verification at webhook endpoints and warns that an endpoint can receive duplicate events. PostgreSQL transactions and ON CONFLICT provide the foundation for atomic writes and conflict handling. A safe migration turns these behaviors into schema invariants instead of assuming that a webhook normally arrives once. Implementation steps Define the vocabulary before moving data. A useful separation is orders , order_items , payment_attempts , and payment_events . An order represents what the customer intends to buy. A payment attempt represents one interaction with a payment provider. An event is an immutable provider message. Refunds and disputes should be state transitions or ledger entries; do not erase the original payment by overwriting its amount with zero. Store amounts as integers in the currency's smallest unit and store the currency alongside them. Do not use floating point. Avoid unconstrained status strings. At minimum, distinguish business states such as pending , paid , cancelled , and refunded , and define which component may perform each transition. This abbreviated schema must be extended with product-specific foreign keys and permissions: create table orders ( id uuid primary key default gen_random_uuid(), customer_id uuid not null, status text not null check (status in ('pending','paid','cancelled','refunded')), currency text not null, amount_total bigint not null check (amount_total >= 0), source_system text not null, source_order_id text not null, created_at timestamptz not null default now(), unique (source_system, source_order_id) ); create table payment_events ( provider text not null, provider_event_id text not null, event_type text not null, payload jsonb not null, received_at timestamptz not null default now(), processed_at timestamptz, primary key (provider, provider_event_id) ); Next, build a source mapping and profiling report. Record the primary key, user key, amount unit, currency, timezone, nullable columns, provider IDs, and status semantics for every legacy table. Enumerate orphan rows, duplicate provider references, invalid amounts, and records without a resolvable user. Do not invent repairs. Move ambiguous records to a quarantine report that preserves the original row and reason for a traceable decision. Create a rerunnable backfill. Derive a stable (source_system, source_order_id) for every legacy row and use INSERT ... ON CONFLICT so a rerun does not create another order. Write the order, items, and mapping inside one transaction per batch; any constraint failure rolls the batch back. Select batch size from measured lock duration and real data volume, not from a generic number. Add shadow reads and controlled dual writes. Let the new model receive writes while the legacy path remains the entitlement authority. For each purchase, calculate old and new outcomes and record differences without changing user access. Dual writes should not be two unrelated HTTP calls. If one database transaction cannot cover the external provider, use an outbox: commit the order and a pending message together, then let a worker call the provider with an idempotency key. Rebuild webhook processing around verification and deduplication. Verify Stripe's signature against the raw request body and endpoint secret before parsing the event. Attempt to insert (provider, provider_event_id) . A conflict means the event was processed or is being processed, so acknowledge it without granting access again. Stripe also notes that distinct Event objects can sometimes represent the same object event; use the provider object ID and event type for business-level deduplication where appropriate. Use an idempotency key tied to the internal order when creating Checkout or another Stripe object with a POST. Stripe's low-level error guide explains that a client facing an ambiguous network failure should retry the same parameters with the same key. Do not reuse that key after changing parameters. Put the internal order_id in a supported provider reference or metadata field so the webhook can reconcile it. The browser's success URL is not payment proof. Only cut over the read path after reconciliation. Compare order count, paid count, refunds, and total amount per currency. Match provider references, users, and course or plan entitlements row by row. Differences must be zero or belong to an explicit, approved exception list. Start the cutover with an internal view or a low-risk reader, observe it, and only then make the new model the entitlement source of truth. Failure and recovery If a backfill stops, retain a migration run ID, the last successful batch, and per-batch hashes. Stable source keys and upserts make it possible to resume from the last verified point. Do not delete the entire target first. Send constraint failures to quarantine rather than removing NOT NULL and calling the import successful. If dual-write comparisons diverge, turn off the new read flag so the legacy system continues to serve existing entitlements. Stop the writer that expands the difference, then replay missing operations from the outbox or event log. Never grant a purchase from the success page: the customer may close, refresh, or fabricate that request. If signature verification fails, return a non-success response and log a request identifier, timestamp, and error class without logging the secret or a complete sensitive payload. Plan webhook secr