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 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.

Step-by-Step Implementation

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;

Verification & Testing

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
  );

Gotchas & Production Pitfalls

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.

Related

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