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

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.

{
“@context”: “https://schema.org”,
“@type”: “FAQPage”,
“mainEntity”: [
{
“@type”: “Question”,
“name”: “How is proration calculated when a user changes plans mid-cycle?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “The system computes unused days on the old plan as a credit (daily_rate * days_remaining) and charges for those same days at the new plan's daily rate. The net amount is the difference. For upgrades this is typically a positive charge; for downgrades it is a credit applied to the next invoice. Daily rate is computed as monthly_price / days_in_period to handle months of different lengths.”
}
},
{
“@type”: “Question”,
“name”: “How does the system prevent double-charging on retry?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “The invoice's charge_id (composed of subscription_id and period_start) is passed as the idempotency key to the payment processor before the charge is attempted. If the HTTP request times out and is retried, the processor recognizes the key and returns the original result instead of creating a new charge. The charge_id is written to the database before the API call so it survives application crashes.”
}
},
{
“@type”: “Question”,
“name”: “What is a dunning strategy and how many retries are standard?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “Dunning is the process of reattempting failed payments and communicating with the customer to recover revenue. A common schedule is retries at day 3, 5, and 7 after initial failure, with subscription cancellation after the final retry fails. The optimal schedule varies by business: SaaS companies with annual contracts may retry for 30 days, while consumer subscriptions typically cancel faster to reduce involuntary churn impact on metrics.”
}
},
{
“@type”: “Question”,
“name”: “How is tax integration handled in invoice generation?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “Tax calculation is handled by an external service (TaxJar or Avalara) called during invoice finalization. The system sends the customer's billing address, product tax codes, and line item amounts. The service returns the applicable tax amount and jurisdiction breakdown. The result is stored as a tax line item on the invoice. Tax service calls are cached by (address, product_code, amount) for a short window to avoid redundant API calls for identical invoices.”
}
}
]
}

{
“@context”: “https://schema.org”,
“@type”: “FAQPage”,
“mainEntity”: [
{
“@type”: “Question”,
“name”: “How is proration calculated when a user upgrades mid-cycle?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “The unused fraction of the current plan (remaining_days / total_days * plan_price) is credited; the new plan is charged for the remaining fraction. Both amounts are line items on the same invoice.”
}
},
{
“@type”: “Question”,
“name”: “How does idempotent charging prevent double-billing on retries?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “The charge_id is composed of (subscription_id + period_start_date); this key is passed to the payment provider as the idempotency key, so retrying the same billing period always returns the original charge result.”
}
},
{
“@type”: “Question”,
“name”: “What is a dunning schedule and how is it implemented?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “Dunning is the sequence of retry attempts for failed payments; a scheduled job queries invoices with status=past_due and payment_due_at in the retry window, re-attempts the charge, and sends escalating email reminders between attempts.”
}
},
{
“@type”: “Question”,
“name”: “How are subscription state transitions managed safely?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “State transitions (e.g., active to past_due) use UPDATE … WHERE status = 'active' with row-level locking; an unexpected current state causes the transition to fail, preventing race conditions from double-transitioning.”
}
}
]
}