Subscription Management System Low-Level Design

By /

What is Subscription Management?

A subscription management system handles recurring billing: plans, upgrades/downgrades, trial periods, proration, cancellations, and payment retries. Stripe, Recurly, and Chargebee are dedicated subscription billing platforms. Building your own subscription system is complex — understanding the design is essential for interviews at Stripe, Shopify, Atlassian, and any SaaS company. Key challenges: proration (charging the right amount when changing plans mid-cycle), dunning (retrying failed payments), and idempotent billing operations.

Requirements

  • Plans: monthly/annual billing, multiple tiers (free, starter, pro, enterprise)
  • Upgrades and downgrades with proration (credit for unused time on old plan)
  • Free trials with automatic conversion to paid
  • Failed payment handling: retry up to 4 times over 7 days before cancellation
  • Webhooks to application when subscription status changes
  • Revenue reporting: MRR, churn, upgrade/downgrade revenue

Data Model

Plan(plan_id UUID, name VARCHAR, price_cents INT, interval ENUM(MONTH, YEAR),
     features JSONB, is_active BOOL)

Subscription(
    subscription_id UUID PRIMARY KEY,
    user_id UUID, plan_id UUID,
    status ENUM(TRIALING, ACTIVE, PAST_DUE, CANCELLED, PAUSED),
    trial_start TIMESTAMPTZ, trial_end TIMESTAMPTZ,
    current_period_start TIMESTAMPTZ,
    current_period_end TIMESTAMPTZ,
    cancel_at_period_end BOOL DEFAULT false,
    cancelled_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ
)

Invoice(
    invoice_id UUID PRIMARY KEY,
    subscription_id UUID,
    user_id UUID,
    amount_cents INT,
    status ENUM(DRAFT, OPEN, PAID, VOID, UNCOLLECTIBLE),
    due_date TIMESTAMPTZ,
    paid_at TIMESTAMPTZ,
    attempt_count INT DEFAULT 0,
    next_retry_at TIMESTAMPTZ
)

InvoiceLineItem(line_id UUID, invoice_id UUID,
    description VARCHAR,  -- 'Pro Plan (Jan 1 - Feb 1)', 'Proration credit'
    amount_cents INT,     -- negative for credits
    period_start TIMESTAMPTZ, period_end TIMESTAMPTZ)

Proration on Plan Change

def upgrade_plan(subscription_id, new_plan_id):
    sub = db.get(subscription_id)
    old_plan = db.get_plan(sub.plan_id)
    new_plan = db.get_plan(new_plan_id)
    now = datetime.utcnow()

    days_remaining = (sub.current_period_end - now).days
    days_in_period = (sub.current_period_end - sub.current_period_start).days

    # Credit for unused time on old plan
    credit_cents = int(old_plan.price_cents * days_remaining / days_in_period)

    # Charge for remaining time on new plan
    charge_cents = int(new_plan.price_cents * days_remaining / days_in_period)

    proration_charge = charge_cents - credit_cents  # positive = charge, negative = credit

    # Create invoice for proration amount
    invoice = create_invoice(sub.user_id, proration_charge, [
        InvoiceLineItem('Pro Plan (remaining days)', charge_cents, now, sub.current_period_end),
        InvoiceLineItem('Starter Plan (unused days) credit', -credit_cents, now, sub.current_period_end)
    ])

    # Update subscription
    db.update(subscription_id, plan_id=new_plan_id)
    collect_payment(invoice)

Failed Payment / Dunning

# Retry schedule: attempt 1 (day 0) → 2 (day 3) → 3 (day 5) → 4 (day 7) → cancel
RETRY_SCHEDULE = [0, 3, 5, 7]  # days after first failure

def handle_payment_failure(invoice_id):
    invoice = db.get(invoice_id)
    sub = db.get_subscription(invoice.subscription_id)

    if invoice.attempt_count >= len(RETRY_SCHEDULE):
        # Max retries exceeded — cancel subscription
        db.update(sub.subscription_id, status='CANCELLED')
        notify_user(sub.user_id, 'subscription_cancelled')
        return

    # Schedule retry
    retry_day = RETRY_SCHEDULE[invoice.attempt_count]
    next_retry = invoice.created_at + timedelta(days=retry_day)
    db.update(invoice_id, status='OPEN', attempt_count=invoice.attempt_count+1,
              next_retry_at=next_retry)
    db.update(sub.subscription_id, status='PAST_DUE')
    notify_user(sub.user_id, 'payment_failed', retry_date=next_retry)

# Cron job runs hourly: retry all OPEN invoices with next_retry_at <= NOW()

Revenue Metrics

-- Monthly Recurring Revenue (MRR)
SELECT SUM(
    CASE p.interval
        WHEN 'MONTH' THEN p.price_cents
        WHEN 'YEAR'  THEN p.price_cents / 12
    END
) / 100.0 AS mrr_dollars
FROM Subscription s JOIN Plan p ON s.plan_id = p.plan_id
WHERE s.status IN ('ACTIVE', 'TRIALING');

-- Churn rate (subscriptions cancelled this month / active at start of month)
SELECT
    cancelled_this_month * 100.0 / active_at_start AS churn_rate_pct
FROM (
    SELECT
        COUNT(*) FILTER (WHERE cancelled_at >= date_trunc('month', NOW())) AS cancelled_this_month,
        COUNT(*) FILTER (WHERE created_at < date_trunc('month', NOW())) AS active_at_start
    FROM Subscription WHERE status IN ('ACTIVE', 'PAST_DUE', 'CANCELLED')
) stats;

Key Design Decisions

  • Day-based proration — simple and auditable; Stripe uses this approach
  • Invoice line items for proration transparency — customers see exactly what they’re charged/credited
  • cancel_at_period_end flag — user retains access until period end; no immediate service disruption
  • PAST_DUE status — service continues during retry window; prevent churn from temporary payment issues
  • Idempotent invoice creation — invoice_id based on (subscription_id + period_start) prevents duplicate charges on retry

{“@context”:”https://schema.org”,”@type”:”FAQPage”,”mainEntity”:[{“@type”:”Question”,”name”:”How does proration work when upgrading a subscription mid-cycle?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”Calculate the remaining days in the current billing period. Credit the user for unused time on the old plan: credit = old_plan_price × (days_remaining / days_in_period). Charge for remaining time on the new plan: charge = new_plan_price × (days_remaining / days_in_period). Net proration = charge – credit. If upgrading, this is positive (user pays more). If downgrading, this is negative (user gets a credit applied to the next invoice). Create invoice line items showing both amounts for transparency.”}},{“@type”:”Question”,”name”:”How do you handle failed subscription payments (dunning)?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”Use a dunning retry schedule: attempt again on day 3, day 5, and day 7 after the first failure. After 4 failed attempts (day 0, 3, 5, 7), cancel the subscription. Set status to PAST_DUE (not immediately CANCELLED) — the user retains access during the retry window, reducing churn from temporary payment issues. Send email reminders before each retry with a link to update payment method. After max retries, send a final cancellation email with a reactivation link.”}},{“@type”:”Question”,”name”:”What is cancel_at_period_end and why is it preferred over immediate cancellation?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”cancel_at_period_end=true marks the subscription to cancel when the current billing period ends, without immediate service interruption. The user paid for the full period — they should retain access until it expires. Immediate cancellation without refund triggers chargebacks; immediate cancellation with prorated refund is complex. cancel_at_period_end is simpler: no refund, no proration, user accesses until period end, subscription cancels automatically. Stripe uses this as the default cancellation behavior.”}},{“@type”:”Question”,”name”:”How do you prevent duplicate charges in a subscription billing system?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”Use idempotent invoice creation: derive the invoice_id deterministically from (subscription_id + current_period_start). If a billing job runs twice (network failure, retry), the second attempt tries to insert with the same invoice_id and hits a unique constraint — no duplicate charge. For payment processing: use payment processor idempotency keys (Stripe idempotency_key header). Store the payment processor’s payment_intent_id and check for it before creating a new charge.”}},{“@type”:”Question”,”name”:”How do you calculate MRR for a SaaS business?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”Monthly Recurring Revenue = sum of normalized monthly revenue from all active subscriptions. For monthly plans: plan_price. For annual plans: annual_price / 12. SQL: SELECT SUM(CASE WHEN interval=MONTH THEN price_cents WHEN interval=YEAR THEN price_cents/12 END) / 100.0 AS mrr FROM Subscription JOIN Plan USING (plan_id) WHERE status IN (ACTIVE, TRIALING). Track MRR movements: new MRR (new subscribers), expansion MRR (upgrades), contraction MRR (downgrades), churned MRR (cancellations).”}}]}

Subscription billing and payment system design is discussed in Stripe system design interview questions.

Subscription management and SaaS billing design is covered in Atlassian system design interview preparation.

Subscription and recurring billing system design is discussed in Shopify system design interview guide.

Scroll to Top