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

Overlap prevention is defense in depth: three fast layers that catch the common races, and one schema-level constraint that makes overlap outright impossible. The diagram shows the layers as concentric guards — a request gets past the outer ones only to be stopped by the innermost invariant if a bug slips through.

Concentric guards: the outer three are latency optimizations; the innermost constraint is the guarantee.

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.

Why not lean on a single layer

It is tempting to drop the outer layers once the exclusion constraint is in place — after all, the database will never let two active periods overlap, so why pay for a Redis lookup and a row lock on every switch? The answer is what a bare constraint violation costs downstream. If you rely on EXCLUDE alone, a raced switch surfaces as a 23P01 exclusion_violation thrown out of the middle of a transaction that has already captured a proration charge or emitted a half-finished event. You then have to unwind that work, translate an opaque Postgres error code into a user-facing message, and decide whether the caller should retry. The row lock turns that race into a clean serialization: the second writer blocks, wakes up, re-reads the now-switched state, and either no-ops or returns the same result the first writer produced. Idempotency collapses it earlier still, before a connection is even checked out of the pool. Each outer layer converts a loud, expensive failure into a cheaper, quieter one; the constraint is there for the case where every one of those layers has a bug on the same day.

Advisory locks versus row locks

SELECT ... FOR UPDATE on the subscriptions row is the right default because the lock is scoped to exactly the row you are about to mutate and it releases at commit. Some teams reach for pg_advisory_xact_lock(hashtext(subscription_id::text)) instead so they can serialize on a subscription that may not yet have a row, or across tables the switch touches. That works, but advisory locks are keyed on a 64-bit hash, so two different subscription_id values can collide into the same lock and serialize customers that have nothing to do with each other. Under a retry storm that collision widens the critical section and hurts throughput for reasons that are invisible in the query plan. Prefer the row lock unless you have a concrete reason the row does not exist yet; if you do use advisory locks, key them on the full UUID and accept that hash collisions are a real, if rare, tail-latency source.

The cost of SERIALIZABLE

Escalating the whole transaction to SERIALIZABLE isolation is the sledgehammer: it removes the need to reason about which rows to lock, because Postgres tracks read/write dependencies and aborts any transaction that would produce a non-serializable schedule. The trade is a 40001 serialization_failure abort rate that climbs with contention — 5 to 15 percent on a hot subscription during a coordinated retry storm — and every abort means a full retry of the switch, including re-reading state and recomputing proration. That is acceptable for a low-volume admin path but wasteful on the customer-facing switch endpoint, where the explicit row lock plus exclusion constraint gives you the same guarantee at a fraction of the retry cost. Reserve SERIALIZABLE for flows where you genuinely cannot enumerate the rows to lock ahead of time.

Step-by-Step Implementation

The build order runs from the strongest guarantee outward: first the schema constraint that makes overlap impossible, then request dedup, then the atomic switch, then payment compensation. The timeline below shows the atomic switch itself — the old period closes at exactly the instant the new one opens, sharing a half-open boundary so there is neither gap nor overlap.

A shared half-open boundary means the closing instant of the old period is the opening instant of the new — no gap, no overlap.

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')
);

Two details of that constraint earn their keep. The partial WHERE (status = 'active') predicate means the exclusion index only holds rows that are currently live, so the GiST tree stays small even as a long-lived subscription accumulates dozens of switched and cancelled periods over its lifetime — a customer who has been on the product for three years might have forty historical rows but only ever one indexed. It also means a switch does not need to delete history: closing a period flips its status out of active, which removes it from the constraint’s scope while keeping the audit trail intact. The btree_gist extension is what lets you mix the scalar equality on subscription_id WITH = with the range overlap on active_period WITH && inside one index; without it Postgres cannot build a GiST operator class over a plain UUID column and the CREATE TABLE fails outright.

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;

The statement order inside that transaction is not incidental. The UPDATE that closes the old period must run before the INSERT that opens the new one, because until the old period’s upper bound has been pulled back to $switch_at it still overlaps [switch_at, next_anchor), and the exclusion constraint is evaluated on insert. Reverse the two and the INSERT trips exclusion_violation against a period you are about to close a microsecond later — a self-inflicted failure that only reproduces under the exact interleaving you were trying to defend against. The version bump on subscriptions is the second half of the safety story: locking the row with FOR UPDATE serializes writers, but the WHERE version = $expected predicate catches the case where a reader loaded the subscription, sat behind a slow proration call for two seconds, and is now trying to write against a state that a faster switch already moved past. Zero rows updated means a lost update; roll the whole transaction back and let the caller re-read and retry against fresh state.

A subtle point about the switch instant: capture $switch_at once, in the application, at the top of the transaction, and reuse that exact value for both the old period’s new upper bound and the new period’s lower bound. Do not call now() twice inside the SQL — under a busy server the two evaluations can land microseconds apart, and a lower bound that is even one microsecond earlier than the upper bound it is supposed to abut opens a real, constraint-passing overlap that no test with second-granularity timestamps will ever catch.

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

The property to prove is simple to state and simple to query: no subscription ever has two active periods that overlap. The three tests below attack it from different angles — a direct invariant query, a concurrency race, and a forced constraint violation.

Prove the property three ways — query it, race it, and try to break the constraint on purpose.

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

The pitfalls here are mostly about boundaries and clocks — the exact places where a plan switch touches time. The map groups them so the fix is obvious: use half-open ranges, cap lock waits, do calendar-aware math, and dedupe on the gateway event id.

Every pitfall lives at a time boundary — half-open ranges, bounded waits, calendar math, and id-based dedup close them.

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.

Frequently Asked Questions

Why does overlap happen at all? Because a switch implemented as cancel-then-create has a window in which both exist, and any failure inside that window leaves both active. Amending in place avoids the window entirely where the provider supports it.

How is overlap detected after the fact? A nightly query for accounts with more than one active subscription of the same product. It should always return zero rows, and any result is an incident rather than a report.

What should happen when overlap is found? Cancel the newer duplicate, credit any charge it produced, and notify the customer before they notice. Overlap is one of the few billing defects customers escalate publicly.

Does the same risk exist for add-ons? Yes, and it is easier to miss because two add-on subscriptions can be legitimate. Key the uniqueness check on the product rather than on the subscription count.