# Billing and Invoicing System Low-Level Design: Subscription Cycles, Proration, and Invoice Generation

Source: https://www.techinterview.org/post/3233468933/lld-billing-invoicing/
Updated: 2026-06-30 · techinterview.org

## What Is a Billing and Invoicing System?

A billing and invoicing system manages subscription lifecycles, generates accurate invoices, collects payment, and handles failure recovery. At low level it must handle proration when users change plans mid-cycle, prevent double-charging on retries, integrate with tax services, and implement dunning for failed payments — all while maintaining an immutable audit trail.

## Subscription Lifecycle

A subscription moves through a defined set of states:

  - **trialing:** service provided without charge; billing starts after trial end.

  - **active:** payment collected successfully; service fully available.

  - **past_due:** latest invoice payment failed; service degraded or restricted; dunning in progress.

  - **canceled:** explicitly canceled or max dunning retries exceeded.

  - **paused:** subscription suspended at user request; billing halted.

The `billing_anchor` is the day-of-month (or day-of-year for annual) on which renewal invoices are generated. It is set at subscription creation and does not drift even after plan changes or failed payment retries.

## Schema


```
CREATE TABLE Plan (
  id             BIGSERIAL PRIMARY KEY,
  name           VARCHAR(64) NOT NULL,
  price_cents    INT NOT NULL,
  interval       VARCHAR(16) NOT NULL,  -- 'month' or 'year'
  features       JSONB
);

CREATE TABLE Subscription (
  id                   BIGSERIAL PRIMARY KEY,
  user_id              BIGINT NOT NULL,
  plan_id              BIGINT NOT NULL REFERENCES Plan(id),
  status               VARCHAR(16) NOT NULL DEFAULT 'trialing',
  current_period_start DATE NOT NULL,
  current_period_end   DATE NOT NULL,
  billing_anchor       INT NOT NULL,  -- day of month
  trial_end            DATE,
  created_at           TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE Invoice (
  id              BIGSERIAL PRIMARY KEY,
  subscription_id BIGINT NOT NULL REFERENCES Subscription(id),
  period_start    DATE NOT NULL,
  period_end      DATE NOT NULL,
  status          VARCHAR(16) NOT NULL DEFAULT 'draft',  -- draft, open, paid, void, uncollectible
  total_cents     INT NOT NULL DEFAULT 0,
  tax_cents       INT NOT NULL DEFAULT 0,
  charge_id       VARCHAR(128) UNIQUE,  -- idempotency key: sub_id + period_start
  created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE InvoiceLineItem (
  id          BIGSERIAL PRIMARY KEY,
  invoice_id  BIGINT NOT NULL REFERENCES Invoice(id),
  description VARCHAR(255) NOT NULL,
  amount_cents INT NOT NULL,
  type        VARCHAR(32) NOT NULL  -- 'subscription', 'usage', 'proration_credit', 'proration_charge', 'tax'
);

CREATE TABLE PaymentAttempt (
  id              BIGSERIAL PRIMARY KEY,
  invoice_id      BIGINT NOT NULL REFERENCES Invoice(id),
  attempted_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  status          VARCHAR(16) NOT NULL,  -- 'succeeded', 'failed', 'pending'
  failure_code    VARCHAR(64),
  payment_method  VARCHAR(64)
);
```


## Invoice Generation

Invoice generation runs at the start of each billing period (triggered by a scheduler keyed on billing_anchor):

  - Create an Invoice record in `draft` status for the period.

  - Add a base fee line item for the plan price.

  - Add any usage-based charges accumulated during the prior period.

  - Add any proration line items from mid-cycle plan changes.

  - Call the tax service (TaxJar/Avalara) to compute tax on the taxable subtotal; add a tax line item.

  - Sum all line items to set `total_cents`; transition invoice to `open`.

  - Attempt charge via payment processor.

## Proration Calculation

When a user upgrades or downgrades mid-cycle, the system issues a credit for unused days on the old plan and a charge for remaining days on the new plan:


```
from datetime import date
from decimal import Decimal

def compute_proration(subscription_id: int, new_plan_id: int, change_date: date) -> dict:
    db = get_db()
    sub = db.execute(
        "SELECT * FROM Subscription WHERE id = %s", (subscription_id,)
    ).fetchone()
    old_plan = db.execute(
        "SELECT * FROM Plan WHERE id = %s", (sub['plan_id'],)
    ).fetchone()
    new_plan = db.execute(
        "SELECT * FROM Plan WHERE id = %s", (new_plan_id,)
    ).fetchone()

    period_start = sub['current_period_start']
    period_end   = sub['current_period_end']
    total_days   = (period_end - period_start).days

    days_used      = (change_date - period_start).days
    days_remaining = total_days - days_used

    daily_old = Decimal(old_plan['price_cents']) / total_days
    daily_new = Decimal(new_plan['price_cents']) / total_days

    credit_cents = int(daily_old * days_remaining)
    charge_cents = int(daily_new * days_remaining)
    net_cents    = charge_cents - credit_cents

    return {
        'credit_cents': credit_cents,
        'charge_cents': charge_cents,
        'net_cents':    net_cents,
        'days_remaining': days_remaining
    }
```


## Idempotent Charge Execution

The `charge_id` on Invoice is set to `sub_{subscription_id}_{period_start}` before the charge is attempted. This value is passed as the idempotency key to the payment processor (Stripe's `idempotency_key` header). If the charge request is retried due to a network timeout, the processor returns the original charge result rather than creating a duplicate charge.


```
import stripe
from db import get_db

def execute_charge(invoice_id: int) -> bool:
    db = get_db()
    invoice = db.execute("SELECT * FROM Invoice WHERE id = %s", (invoice_id,)).fetchone()
    sub = db.execute(
        "SELECT * FROM Subscription WHERE id = %s", (invoice['subscription_id'],)
    ).fetchone()
    user = db.execute("SELECT * FROM User WHERE id = %s", (sub['user_id'],)).fetchone()

    charge_id = f"sub_{invoice['subscription_id']}_{invoice['period_start']}"
    db.execute(
        "UPDATE Invoice SET charge_id = %s WHERE id = %s", (charge_id, invoice_id)
    )
    db.commit()

    try:
        charge = stripe.PaymentIntent.create(
            amount=invoice['total_cents'],
            currency='usd',
            customer=user['stripe_customer_id'],
            payment_method=user['default_payment_method_id'],
            confirm=True,
            idempotency_key=charge_id
        )
        db.execute("""
            INSERT INTO PaymentAttempt (invoice_id, status)
            VALUES (%s, 'succeeded')
        """, (invoice_id,))
        db.execute(
            "UPDATE Invoice SET status = 'paid' WHERE id = %s", (invoice_id,)
        )
        db.execute(
            "UPDATE Subscription SET status = 'active' WHERE id = %s",
            (invoice['subscription_id'],)
        )
        db.commit()
        return True
    except stripe.error.CardError as e:
        db.execute("""
            INSERT INTO PaymentAttempt (invoice_id, status, failure_code)
            VALUES (%s, 'failed', %s)
        """, (invoice_id, e.code))
        db.execute(
            "UPDATE Subscription SET status = 'past_due' WHERE id = %s",
            (invoice['subscription_id'],)
        )
        db.commit()
        return False

def generate_invoice(subscription_id: int, period_start: date) -> int:
    db = get_db()
    sub = db.execute(
        "SELECT * FROM Subscription WHERE id = %s", (subscription_id,)
    ).fetchone()
    plan = db.execute(
        "SELECT * FROM Plan WHERE id = %s", (sub['plan_id'],)
    ).fetchone()

    invoice_id = db.execute("""
        INSERT INTO Invoice (subscription_id, period_start, period_end, status)
        VALUES (%s, %s, %s, 'draft') RETURNING id
    """, (subscription_id, period_start, sub['current_period_end'])).fetchone()[0]

    db.execute("""
        INSERT INTO InvoiceLineItem (invoice_id, description, amount_cents, type)
        VALUES (%s, %s, %s, 'subscription')
    """, (invoice_id, f"{plan['name']} - monthly subscription", plan['price_cents']))

    db.execute(
        "UPDATE Invoice SET status = 'open', total_cents = %s WHERE id = %s",
        (plan['price_cents'], invoice_id)
    )
    db.commit()
    return invoice_id
```


## Dunning Strategy

When a charge fails, the subscription moves to `past_due` and a dunning sequence begins:

  - **Day 0:** First failure. Send "payment failed" email with update payment link.

  - **Day 3:** Retry charge. Send reminder if still failing.

  - **Day 5:** Retry charge. Send urgent warning.

  - **Day 7:** Final retry. If failed, cancel subscription and send cancellation notice.

Each retry records a PaymentAttempt row. The dunning scheduler queries for `past_due` subscriptions and open invoices, checking the last attempt timestamp to determine retry eligibility.

See also: [Stripe Interview Guide 2026: Process, Bug Bash Round, and Payment Systems](https://www.techinterview.org/post/3233460268/stripe-interview-guide-2026-process-bug-bash-round-and-payment-systems/)

See also: [Shopify Interview Guide](https://www.techinterview.org/post/3233459924/shopify-interview/)

See also: [Scale AI Interview Guide 2026: Data Infrastructure, RLHF Pipelines, and ML Engineering](https://www.techinterview.org/post/3233460275/scale-ai-interview-guide-2026-data-infrastructure-rlhf-pipelines-and-ml-engineering/)