Prevent Subscription Overlap on Plan Switches

You face this the first time a customer double-clicks Upgrade, or when a UI retry and a gateway webhook both try to switch the same plan within a few hundred milliseconds. The result is two active periods for one subscription: the customer is billed twice and entitlement checks see conflicting plans. This page sits under Subscription Lifecycle States and shows how to make a plan switch atomic and non-overlapping by construction — using row locks, a PostgreSQL range exclusion constraint, and request-level idempotency rather than hoping the application code never races.

Trade-offs

The defense against overlap is layered. Each layer has a different cost and catches a different class of failure; production systems use all of them, but it helps to see what each buys you.

Mechanism	Catches	Latency cost	Failure mode it misses	When to rely on it
`Idempotency-Key` dedup (Redis)	UI double-click, client retry, webhook replay	~1 ms cache lookup	Two different keys racing the same row	Always — first line of defense
`SELECT ... FOR UPDATE` row lock	Concurrent writers to one subscription	Lock wait up to statement timeout	Logic bugs that open a second period in a different txn	Always, around the switch
Optimistic `version` check	Lost updates across long-lived reads	Negligible; one extra predicate	High-contention hot rows (retry storms)	Stateless APIs, read-then-write flows
`tstzrange` EXCLUDE constraint	Any overlap, including bugs above	One GiST index check on insert	Nothing — it is the backstop	Always — the schema-level guarantee
`SERIALIZABLE` isolation	All write skew	Higher abort/retry rate (5–15% under load)	—	When you cannot enumerate every lock

The key insight: idempotency and row locks prevent overlap in the common path, but the tstzrange exclusion constraint is the only mechanism that makes overlap impossible regardless of application bugs. Treat it as the invariant and the rest as latency optimizations.

Step-by-Step Implementation

1. Enforce non-overlap at the schema level

Store each billing period as a tstzrange and add an exclusion constraint so the database itself rejects any second active period for the same subscription. This is your backstop — it holds even if every line of application code is wrong.

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE subscription_periods (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    subscription_id UUID NOT NULL,
    price_id        UUID NOT NULL,
    active_period   TSTZRANGE NOT NULL,
    status          TEXT NOT NULL DEFAULT 'active',
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    -- ✗ rejects any two active periods that overlap for one subscription
    EXCLUDE USING gist (
        subscription_id WITH =,
        active_period   WITH &&
    ) WHERE (status = 'active')
);

2. Deduplicate the request before any work

Require an Idempotency-Key and return the stored response for replays. This absorbs double-clicks and webhook redeliveries before they ever reach the database.

async function handleSwitch(req: Request, res: Response): Promise<Response> {
  const key = req.header('Idempotency-Key');
  if (!key) return res.status(400).json({ error: 'Missing Idempotency-Key' });

  const cacheKey = `idemp:switch:${key}`;
  const cached = await redis.get(cacheKey);
  if (cached) return res.status(200).json(JSON.parse(cached)); // ⚠️ replay — return prior result

  const result = await executePlanSwitch(req.body);             // ✅ first time through
  // TTL matches the gateway's max webhook retry window (72h)
  await redis.set(cacheKey, JSON.stringify(result), 'EX', 259200, 'NX');
  return res.status(200).json(result);
}

3. Switch atomically: close the old period, open the new one

Inside one transaction, lock the subscription, validate its state, close the current period at the switch instant, and insert the new period starting at that same instant. The shared boundary is what guarantees zero overlap.

BEGIN;

SELECT id, current_state, version
FROM subscriptions
WHERE id = $1
FOR UPDATE;                       -- serialize concurrent switches

-- (application asserts current_state IN ('active','trialing') and version = $expected)

-- Close the old period exactly at the switch instant
UPDATE subscription_periods
SET active_period = tstzrange(lower(active_period), $switch_at, '[)'),
    status = 'switched'
WHERE subscription_id = $1 AND status = 'active';

-- Open the new period starting at the same instant — no gap, no overlap
INSERT INTO subscription_periods (subscription_id, price_id, active_period, status)
VALUES ($1, $2, tstzrange($switch_at, $next_anchor, '[)'), 'active');

UPDATE subscriptions
SET current_price_id = $2, version = version + 1, updated_at = now()
WHERE id = $1 AND version = $expected;   -- ✗ zero rows = lost update, roll back

COMMIT;

4. Compensate on payment failure

If the upgrade requires an immediate proration charge and it declines, roll back to the original period rather than leaving the row between plans. The proration math itself is covered in How To Calculate Prorated Charges For Mid Cycle Upgrades.

try {
  await db.tx(async (t) => {
    await switchPeriods(t, subId, newPriceId, switchAt);   // step 3
    await captureProration(t, subId, prorationCents);      // may throw on decline
  });
} catch (err) {
  // ✗ hard decline: nothing committed — old period is intact, emit failure event
  await emit('plan_switch.failed', { subId, reason: err.code });
}

Verification & Testing

Assert the invariant directly. After a switch, query for overlapping active periods and require zero rows — this is the test that proves the property, independent of how the switch was implemented.

-- Must return zero rows for a correct system
SELECT a.subscription_id
FROM subscription_periods a
JOIN subscription_periods b
  ON a.subscription_id = b.subscription_id
 AND a.id <> b.id
 AND a.status = 'active' AND b.status = 'active'
 AND a.active_period && b.active_period;

Drive a concurrency test that fires two switch requests at the same subscription from two connections with no idempotency key, and assert that exactly one commits and the other either blocks-then-no-ops or fails the version check. Replay an identical request with the same Idempotency-Key and assert the second call returns the cached response and creates no new period row. Finally, force the exclusion constraint to fire by attempting to insert an overlapping period manually and assert PostgreSQL raises exclusion_violation.

Gotchas & Production Pitfalls

Half-open ranges or you self-overlap. Use '[)' bounds (inclusive start, exclusive end) so the closing instant of the old period equals the opening instant of the new one without the two ranges touching. Inclusive-inclusive [] overlaps on the shared boundary and the constraint rejects a legitimate switch.
Statement timeout, not infinite lock waits. Set a 3s statement timeout. If lock acquisition exceeds it, return 429 so a retry storm degrades gracefully instead of exhausting the connection pool.
DST and month-end drift. Compute the next anchor with calendar-aware math, not fixed 30-day arithmetic; store everything in TIMESTAMPTZ. A fixed-day assumption shifts the boundary by a day across a DST change and can open a one-day overlap or gap.
Webhook replays carry their own clock. A redelivered customer.subscription.updated may report a switch you already applied. Dedupe on the gateway event_id against a processed-events table, not on payload contents, which can differ between deliveries.
Idempotency key scope. Scope the key to subscription_id plus a payload hash. A key reused across two genuinely different switches will return the first switch’s response and silently drop the second.