Subscription Manager Low-Level Design: Plan Lifecycle, Renewal, Proration, and Dunning

Core Schema

The subscription manager tracks the relationship between users, plans, and billing periods.

CREATE TABLE subscriptions (
  subscription_id     UUID PRIMARY KEY,
  user_id             BIGINT NOT NULL,
  plan_id             VARCHAR(64) NOT NULL,
  status              ENUM('trialing','active','past_due','cancelled') NOT NULL,
  current_period_start TIMESTAMPTZ NOT NULL,
  current_period_end   TIMESTAMPTZ NOT NULL,
  cancel_at_period_end BOOLEAN NOT NULL DEFAULT FALSE,
  trial_end           TIMESTAMPTZ,
  created_at          TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE plans (
  plan_id    VARCHAR(64) PRIMARY KEY,
  name       VARCHAR(128) NOT NULL,
  price      INTEGER NOT NULL,  -- in cents
  currency   CHAR(3) NOT NULL,
  interval   ENUM('monthly','annual') NOT NULL,
  trial_days INTEGER NOT NULL DEFAULT 0,
  features   JSONB NOT NULL DEFAULT '[]'
);

Renewal Flow

A scheduled job runs every hour and processes subscriptions due for renewal:

Query: WHERE status IN ('active','past_due') AND current_period_end <= now() + interval '1 day'
For each subscription: attempt charge via payment provider using stored payment method.
On success: advance current_period_start and current_period_end, set status='active', create invoice record.
On failure: start dunning workflow, set status='past_due'.

Use idempotency keys on payment provider calls (subscription_id + period_start) to prevent double-charging if the job runs twice.

Dunning Workflow

Dunning is the process of retrying failed payments before cancelling the subscription:

Day 0: Payment fails → status=past_due → send “payment failed” email → log dunning attempt
Day 1: Retry charge → success: resume; failure: send reminder email
Day 3: Retry charge → success: resume; failure: send urgent reminder
Day 7: Retry charge → success: resume; failure: send final warning
Day 14: Cancel subscription → status=cancelled → revoke access → send cancellation email

Access behavior during dunning: options are grace period (full access for X days) or immediate restriction (read-only mode on first failure). For SaaS, grace period is better for retention. For content subscriptions, restrict immediately.

Proration on Plan Change

When a user upgrades mid-cycle:

Calculate unused days in current period: days_remaining = period_end - now()
Credit: current_plan_daily_rate * days_remaining
Charge: new_plan_price - credit
Start new period immediately at new plan level.

Example: User is on $30/month plan, 15 days remaining. Upgrades to $60/month. Credit = $15, charge = $60 – $15 = $45. New period starts today, runs 30 days.

On downgrade: do not charge or credit immediately. Apply the downgrade at the end of the current period (cancel_at_period_end=false but record pending plan change). At renewal, bill at new lower price.

Cancellation

Two cancellation modes:

Cancel at period end: Set cancel_at_period_end=true. User retains access through current_period_end. On that date, set status=cancelled and revoke access. No refund.
Immediate cancel: Set status=cancelled immediately. Calculate pro-rated refund for unused days and issue to original payment method. Access revoked at cancellation time.

Most consumer products default to cancel-at-period-end (better for retention, simpler accounting). Immediate cancel with refund is common in B2B where annual contracts are involved.

Trial Management

Trials are a first billing period with no charge:

On signup: create subscription with status=trialing, trial_end = now() + trial_days
No payment required to start trial (or require card on file but do not charge)
Scheduled job: when trial_end <= now() and payment method is on file → attempt first charge → success: switch to active; failure: dunning or cancel
When trial_end <= now() and no payment method → send “add payment method” email → grace period of 3 days → cancel

Access Control Integration

Subscription status maps to access levels:

active or trialing: full feature access
past_due: restricted access (read-only or limited feature set)
cancelled: downgrade to free tier or block entirely

Access decisions should read from a cache keyed on user_id with short TTL (60 seconds). Do not query the subscriptions table on every API request. On status change, invalidate the cache entry immediately.

Invoice Generation

Create an invoice record for every charge attempt (success or failure):

CREATE TABLE invoices (
  invoice_id      UUID PRIMARY KEY,
  subscription_id UUID NOT NULL,
  user_id         BIGINT NOT NULL,
  amount          INTEGER NOT NULL,
  currency        CHAR(3) NOT NULL,
  status          ENUM('paid','failed','refunded') NOT NULL,
  payment_method  JSONB,
  charged_at      TIMESTAMPTZ,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

Invoice records are immutable once created. Refunds create a separate refund record rather than modifying the invoice. This gives a complete audit trail for billing disputes and tax reporting.

{
“@context”: “https://schema.org”,
“@type”: “FAQPage”,
“mainEntity”: [
{
“@type”: “Question”,
“name”: “How does subscription renewal work with automatic payment retry?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “On the renewal date, a scheduler enqueues a charge job that calls the payment processor (e.g., Stripe) with the customer's stored payment method; if the charge succeeds, the subscription's current_period_end is extended by one billing interval and a renewal receipt is issued. If the charge fails, the job transitions the subscription to a grace period state and schedules retry attempts on an exponential backoff schedule (e.g., day 3, 5, 7) before ultimately cancelling and triggering dunning communications.”
}
},
{
“@type”: “Question”,
“name”: “How is proration calculated on a mid-cycle plan upgrade?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “Proration credits the unused portion of the current plan — computed as (days_remaining / days_in_period) * old_plan_price — and charges the proportional cost of the new plan for the same remaining days, netting the two amounts into a single immediate charge or credit. This ensures customers are billed fairly for the time on each plan without waiting for the next renewal cycle.”
}
},
{
“@type”: “Question”,
“name”: “What is a dunning workflow and how does it handle failed payments?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “A dunning workflow is an automated sequence of payment retry attempts and escalating customer communications (email, in-app banner, account restriction) triggered when a subscription renewal charge fails. Each retry attempt uses smart retry logic — informed by the payment processor's decline codes and historical retry success rates by day-of-week and card network — to maximize recovery before the subscription is cancelled.”
}
},
{
“@type”: “Question”,
“name”: “How is feature access controlled based on subscription status?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “Feature access is enforced by an entitlement service that maps subscription plan and status (active, grace period, cancelled) to a set of feature flags, which are evaluated on every authenticated API request via middleware. The entitlement cache (Redis) is invalidated immediately on subscription status changes via a webhook or event from the billing system, ensuring access revocation propagates within seconds rather than waiting for cache TTL expiry.”
}
}
]
}