Tiered vs Flat-Rate Subscription Database Design

The first irreversible decision in a billing schema is how you represent price: a flat-rate plan is one immutable number, while a tiered plan is a graduated function of usage. Pick a flat-only schema and you will be migrating production tables the day product adds a usage tier; over-engineer a tiered schema for a flat-only product and every invoice query carries needless joins. The right answer is one polymorphic schema that resolves both in sub-50ms, which is exactly what Hybrid Pricing Models demand. This page builds that schema against Subscription Billing Architecture & Pricing Models conventions: UUID keys, NUMERIC money, and idempotent, ACID-safe billing cycles.

Trade-offs

The schema decision is really two axes: flat versus tiered resolution, and — for tiered — relational rows versus a JSONB document. Each combination trades resolution latency against auditability and migration cost. The map places the three viable options.

The choice between modeling tiers as relational rows versus a JSONB blob, and flat versus tiered resolution, has concrete cost and latency consequences.

Use relational tiers when financial auditability and SQL analytics matter; reach for JSONB only when pricing changes faster than you can ship migrations and the rating engine resolves tiers entirely in memory.

Why the strategy enum beats table-per-strategy

A tempting alternative is one table per pricing shape — flat_plans, tiered_plans, volume_plans — joined through a supertype row. That buys nothing here and costs a UNION ALL on every plan lookup, because the resolver still has to discover which subtype a given plan_id belongs to before it can price a line. The single pricing_strategy enum collapses that discovery into a column read: one index-only scan on plans tells the engine whether to skip the tier join entirely. For a flat plan the resolver never touches pricing_tiers, so the ~1ms path in the table above is a genuine O(1) primary-key lookup on plan_id, not a join that happens to return one row. Keep the discriminator on the parent row and the branch is a cheap CASE in application code, not a schema traversal that the planner has to cost every time.

Graduated versus volume is the second axis the enum encodes, and the difference is arithmetic, not storage. A graduated ('tiered') plan charges each bracket’s price_per_unit only on the units that fall inside that bracket, so a 12,000-unit month spanning three brackets sums three partial products. A volume ('volume') plan finds the single bracket the total lands in and charges that one rate on every unit. Both read the identical pricing_tiers rows; the LATERAL query differs only in whether it sums all matching brackets or keeps the last. Storing both shapes in one table means a plan can flip from graduated to volume by changing the enum and re-running resolution — no row rewrite, no migration, and no second code path to keep in sync.

Cardinality and the cost of a wrong index

Tier tables are tiny — a plan with more than a dozen brackets is unusual — so pricing_tiers almost always lives in shared buffers and the join cost is dominated by the usage aggregation, not the bracket lookup. subscription_usage is the opposite: it grows unbounded at metering rate, and a single busy subscription_id can accumulate millions of rows per period. That asymmetry is why the composite index leads with subscription_id and carries recorded_at — the period filter in the period_usage CTE becomes a bounded range scan rather than a full-table read. Get the index column order backwards (recorded_at first) and every resolution scans the whole meter history before filtering by subscription, turning a 5ms aggregate into a 500ms one under invoice-run load.

Step-by-Step Implementation

The schema is five tables plus an index set, wired so one strategy enum routes flat and tiered plans through the same rating path. The entity map shows how plans, tiers, subscriptions, and usage relate — the enum on plans is the discriminator the rating engine branches on.

1. Create the plans table with a strategy enum

A single enum routes resolution between code paths, so flat and tiered plans coexist.

CREATE TYPE pricing_strategy AS ENUM ('flat', 'tiered', 'volume');

CREATE TABLE plans (
  plan_id       UUID             PRIMARY KEY DEFAULT gen_random_uuid(),
  name          VARCHAR(64)      NOT NULL,
  strategy      pricing_strategy NOT NULL DEFAULT 'flat',
  base_amount   NUMERIC(19,4)    NOT NULL CHECK (base_amount >= 0),
  currency_code CHAR(3)          NOT NULL DEFAULT 'USD'
);

2. Add a pricing_tiers table

Each tier is a half-open range. A NULL upper_bound marks the unbounded top tier.

CREATE TABLE pricing_tiers (
  tier_id        UUID          PRIMARY KEY DEFAULT gen_random_uuid(),
  plan_id        UUID          NOT NULL REFERENCES plans(plan_id) ON DELETE CASCADE,
  tier_index     INT           NOT NULL,
  lower_bound    BIGINT        NOT NULL,
  upper_bound    BIGINT,       -- NULL = unbounded top tier
  price_per_unit NUMERIC(19,4) NOT NULL,
  UNIQUE (plan_id, tier_index)
);

3. Create the subscriptions table

The subscription references its plan and carries period boundaries used by Proration Logic & Calculations during mid-cycle changes.

CREATE TABLE subscriptions (
  subscription_id      UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
  customer_id          UUID        NOT NULL,
  plan_id              UUID        NOT NULL REFERENCES plans(plan_id),
  status               VARCHAR(20) NOT NULL
                         CHECK (status IN ('active','past_due','canceled','trialing')),
  current_period_start TIMESTAMPTZ NOT NULL,
  current_period_end   TIMESTAMPTZ NOT NULL,
  CHECK (current_period_end > current_period_start)
);

4. Add a subscription_usage table

Tiered evaluation needs raw usage, recorded in UTC.

CREATE TABLE subscription_usage (
  usage_id        BIGSERIAL   PRIMARY KEY,
  subscription_id UUID        NOT NULL REFERENCES subscriptions(subscription_id),
  meter_id        VARCHAR(64) NOT NULL,
  quantity        BIGINT      NOT NULL CHECK (quantity >= 0),
  recorded_at     TIMESTAMPTZ NOT NULL DEFAULT now()
);

5. Index for range-query resolution

Composite indexes turn tier resolution and period aggregation into index scans.

CREATE INDEX idx_pricing_tiers_plan_tier ON pricing_tiers(plan_id, tier_index);
CREATE INDEX idx_usage_sub_recorded ON subscription_usage(subscription_id, recorded_at DESC);
CREATE INDEX idx_usage_meter ON subscription_usage(subscription_id, meter_id, recorded_at);

Resolve a tiered amount with a LATERAL join so the bracket math stays in the query planner instead of an application loop:

WITH period_usage AS (
  SELECT SUM(quantity) AS total_qty
  FROM subscription_usage
  WHERE subscription_id = $1
    AND recorded_at >= $2
    AND recorded_at <  $3
)
SELECT pt.tier_index, pt.price_per_unit,
       GREATEST(0, LEAST(u.total_qty, COALESCE(pt.upper_bound, u.total_qty)) - pt.lower_bound)
         AS billable_units
FROM period_usage u
CROSS JOIN LATERAL (
  SELECT * FROM pricing_tiers
  WHERE plan_id = $4 AND u.total_qty >= lower_bound
  ORDER BY tier_index
) pt;

Routing flat and tiered through one resolver

The payoff of the enum shows up in the rating engine, not the schema. A flat plan resolves to base_amount with no query beyond the plans read; a tiered plan runs the LATERAL bracket sum and, for graduated plans, adds base_amount as a platform fee on top of the metered charge. Because both paths return the same (amount, currency_code) shape, the invoice writer never branches — it receives a resolved minor-unit total and appends a line item keyed by subscription_id and the period boundaries. That uniformity is what lets a plan graduate from flat to tiered in place: point the subscription at a plan whose strategy flipped to 'tiered', insert the bracket rows, and the next cycle rates through the tier path with zero application changes.

One subtlety when you compute the graduated total: sum in NUMERIC and convert to integer minor units exactly once, at the end. If you round each bracket’s partial product to cents before summing, a plan with five brackets can accumulate five separate rounding errors in the same invoice, and the total will disagree with an auditor recomputing it in a single pass. Keep price_per_unit at NUMERIC(19,4) so sub-cent rates — fractional cents per API call are common in metered plans — survive multiplication, and let the final ROUND(..., 2) be the only place precision is discarded.

Snapshotting the price a line was rated at

plans and pricing_tiers describe live configuration, but an invoice is an immutable record of what a customer was charged under the config that existed at rating time. If pricing changes next month, last month’s invoice_id must still recompute to the same total. The durable fix is to write the resolved rate and, for tiered plans, the bracket breakdown onto the invoice line at rating time — a small snapshot of tier_index, lower_bound, upper_bound, and price_per_unit per bracket — rather than joining back to pricing_tiers when someone reopens an old bill. This decouples invoice history from configuration churn and is the same discipline that makes ON DELETE CASCADE on pricing_tiers safe to keep on the live path.

Verification & Testing

The schema’s correctness lives in its constraints: every tiered plan needs a catch-all top tier, tier ranges must not gap or overlap, and every money column must be NUMERIC. The panel lists the assertions worth encoding as CI checks and scheduled reconciliation queries.

Assert that every plan with strategy = 'tiered' has at least one pricing_tiers row and exactly one row with upper_bound IS NULL, or resolution can return no bracket for a usage spike. Test the bracket math at boundary values — total_qty exactly equal to a lower_bound and one unit below an upper_bound — to catch off-by-one errors in the half-open ranges. Run all monetary math in NUMERIC and assert no column is FLOAT. A reconciliation query worth scheduling:

-- Flag tiered plans with gaps or overlaps in their tier ranges
SELECT plan_id, tier_index, lower_bound, upper_bound
FROM pricing_tiers a
WHERE strategy_is_tiered(plan_id)
  AND upper_bound IS NOT NULL
  AND NOT EXISTS (
    SELECT 1 FROM pricing_tiers b
    WHERE b.plan_id = a.plan_id AND b.lower_bound = a.upper_bound
  );

Test the graduated-versus-volume divergence explicitly: feed a usage total that lands mid-bracket and assert the graduated resolver bills partial units in the top bracket while the volume resolver bills every unit at the top rate. A plan that silently rates volume math through a graduated query over-bills every customer whose usage crosses a boundary, and the error scales with how high that boundary sits. Snapshot a known plan’s expected total for a fixed usage vector as a golden test so a later refactor of the LATERAL query cannot drift the arithmetic unnoticed.

Property-based testing pays off on the contiguity invariant. Generate random ascending bound sequences, insert them as a plan’s tiers, and assert that summing billable_units across all brackets equals the raw total_qty for any usage value — if it does not, the ranges gap or overlap somewhere. This catches the same class of bug the reconciliation query above finds in production, but before the rows ever ship. Pair it with a boundary sweep that walks total_qty from one below each lower_bound to one above each upper_bound, since half-open ranges concentrate off-by-one errors exactly at the transitions.

Gotchas & Production Pitfalls

The pitfalls here are schema-shaped: a missing top tier, float money, a cascade that erases invoice history, local-time aggregation, and N+1 tier resolution. The map groups them by the layer they corrupt.

No catch-all top tier. A usage spike beyond every defined bound returns zero rows and silently under-bills. Always define an upper_bound IS NULL tier and assert its existence in CI.

Floating-point unit rates. Computing price_per_unit as FLOAT introduces drift that compounds over high-volume usage. Enforce NUMERIC(19,4) on every monetary and rate column; round only at the final line item.

Cascade deletes erasing invoice history. ON DELETE CASCADE on pricing_tiers is right for live config but destroys the tier snapshot a past invoice was computed against. Add a soft-delete (deleted_at) or snapshot tiers onto the invoice if you must reconstruct historical bills.

DST and timezone drift in aggregation windows. Aggregating usage in local time shifts the period boundary across a DST change and mis-buckets events. Store TIMESTAMPTZ, aggregate in UTC, and convert only for display.

Resolving tiers in an N+1 application loop. Iterating tiers in app code per subscription crushes throughput at invoice time. Push bracket mapping into a LATERAL join so the planner does it once per resolution.

Concurrent tier edits during an open period. Editing pricing_tiers mid-cycle while usage is still accruing means resolution can read a half-updated bracket set — the old top tier removed before the new one commits, or two overlapping ranges visible between statements. Wrap tier edits in a single transaction, and prefer versioning the tier set (a new plan_id, or a version column the subscription pins) over mutating rows a live subscription points at, so an in-flight run for a subscription_id never sees a torn configuration.

Assuming tier_index ordering matches bound ordering. Nothing in the schema forces tier_index to ascend with lower_bound; a data-entry slip can produce index 2 with a lower bound below index 1. The LATERAL query orders by tier_index, so a mis-numbered set rates brackets in the wrong sequence and a graduated total comes out wrong without any constraint firing. Add a reconciliation that asserts lower_bound increases strictly with tier_index for every plan, and run it in the same job that checks contiguity.

Frequently Asked Questions

Should tiers be stored as rows or as a JSON structure? Rows, with an ordered upper bound per tier. A JSON blob is faster to write and impossible to query, and tier analysis is exactly the query pricing teams ask for.

How are tier boundaries best represented? As an upper bound per tier with an implicit lower bound from the previous one, which avoids the overlapping-range bugs that explicit pairs invite.

Does a flat plan need the same schema as a tiered one? It fits comfortably as a single tier with no upper bound, which keeps one rating path rather than two. A separate flat-rate code path is a second implementation that will drift.

How should price changes be handled? By versioning rather than editing. Existing subscribers stay on the version they agreed to, and historical invoices continue to reference the version that produced them.

Related

• Hybrid Pricing Models for SaaS
• Proration Logic & Calculations
• Usage-Based Billing Implementation
• Subscription Billing Architecture & Pricing Models