Tiered vs Flat-Rate Subscription Database Design

Architecting a billing database requires a deliberate choice between flat-rate simplicity and tiered complexity. Flat-rate models rely on static price_id foreign keys and predictable monthly cycles. Tiered architectures demand dynamic threshold evaluation, usage aggregation, and graduated rate calculations.

When engineering a Subscription Billing Architecture & Pricing Models foundation, teams must normalize pricing rules into queryable tables. This prevents schema drift as product requirements scale.

This guide provides a step-by-step implementation workflow covering unified subscription databases that scale from simple flat-rate plans to complex tiered structures. The design ensures ACID compliance, idempotent billing cycles, and sub-50ms pricing resolution.

Core Schema Design for Flat-Rate vs Tiered Models

Begin by establishing a polymorphic pricing schema. Flat-rate subscriptions map directly to a single subscriptions.plan_id. Tiered models require a pricing_tiers table with lower_bound, upper_bound, and price_per_unit columns.

Use a pricing_strategy ENUM ('flat', 'tiered', 'volume') to route query execution paths. Embedding Hybrid Pricing Models logic early prevents future table migrations when product requirements evolve.

Step 1: Create plans table with id, name, pricing_strategy, and base_amount.

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

CREATE TABLE plans (
  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'
);

Step 2: Create pricing_tiers table with plan_id, tier_index, lower_bound, upper_bound, and price_per_unit.

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

Step 3: Implement subscriptions table with id, customer_id, plan_id, status, current_period_start, and current_period_end.

CREATE TABLE subscriptions (
  id                   UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
  customer_id          UUID        NOT NULL,
  plan_id              UUID        NOT NULL REFERENCES plans(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)
);

Step 4: Add subscription_usage table for tiered evaluation.

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

Step 5: Apply composite indexes for range query optimization.

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

Dynamic Pricing Resolution & Query Workflows

Flat-rate lookups are O(1) via direct join. Tiered resolution requires window functions or application-side aggregation against pricing_tiers. Implement a deterministic pricing resolver that caches tier thresholds and validates usage boundaries before invoice generation.

Step 1: Query subscription_usage for the current billing period, grouping by meter_id. Aggregate usage within the exact UTC window. Avoid local timezone conversions during aggregation to prevent DST boundary shifts.

Step 2: If pricing_strategy = 'flat', return plans.base_amount directly.

SELECT base_amount
FROM plans
WHERE id = $1 AND strategy = 'flat';

Step 3: If pricing_strategy = 'tiered', map aggregated usage to the correct bracket.

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
    AND (upper_bound IS NULL OR u.total_qty > lower_bound)
  ORDER BY tier_index ASC
) pt;

Step 4: Calculate tiered cost using NUMERIC arithmetic. Wrap calculations in a transactional function. Use NUMERIC types exclusively to avoid IEEE-754 floating-point drift. Apply ROUND(result, 2) only at the final invoice line-item stage.

Step 5: Validate output against a pre-computed materialized view to catch drift before invoicing.

-- Materialized view for daily aggregation (refresh via cron)
CREATE MATERIALIZED VIEW mv_daily_tier_usage AS
SELECT
  subscription_id,
  meter_id,
  DATE(recorded_at AT TIME ZONE 'UTC') AS usage_date,
  SUM(quantity) AS daily_qty
FROM subscription_usage
GROUP BY 1, 2, 3
WITH DATA;

Proration & Mid-Cycle State Transitions

Mid-cycle upgrades/downgrades require precise day-weighted calculations. State machine transitions prevent double-charging or revenue leakage.

Step 1: Lock the target subscription row to prevent concurrent modifications.

BEGIN;
SELECT id, plan_id, current_period_start, current_period_end
FROM subscriptions
WHERE id = $1 FOR UPDATE SKIP LOCKED;

If SKIP LOCKED returns zero rows, abort and retry with exponential backoff to avoid deadlock timeouts under high concurrency.

Step 2: Calculate remaining days using fractional precision. Use EXTRACT(EPOCH FROM (current_period_end - NOW())) / 86400.0 for fractional day precision. Never truncate; carry full precision through the credit calculation.

Step 3: Compute credit for unused flat-rate days or tiered usage adjustments. For flat-rate: credit = (remaining_days / total_days) * old_plan_amount. For tiered: Re-evaluate usage against the old tier boundaries up to the switch timestamp. Generate a delta adjustment.

Step 4: Generate a proration_line_item record with idempotency guard.

INSERT INTO invoice_line_items (
  invoice_id, subscription_id, type, amount, idempotency_key
) VALUES (
  $1, $2, 'proration_credit', $3, $4
) ON CONFLICT (idempotency_key) DO NOTHING;

The idempotency_key must be derived from subscription_id + plan_change_timestamp + direction.

Step 5: Update plan_id and reset period boundaries, then emit a domain event. Commit the transaction only after all line items are persisted. Emit a SubscriptionPlanChanged event to downstream ledger services.

Implementation Patterns

PostgreSQL LATERAL JOIN for tier boundary mapping without application-side loops. This eliminates N+1 application queries and pushes computation to the query planner.
Idempotent invoice generation using idempotency_key and ON CONFLICT DO NOTHING. Network retries during webhook processing must never duplicate charges.
JSONB pricing_rules column for storing tiered thresholds when schema flexibility outweighs strict relational normalization. Validate schema at the application layer using JSON Schema to prevent malformed pricing payloads.
Materialized views for daily aggregated tier calculations on read-heavy dashboards. Use REFRESH MATERIALIZED VIEW CONCURRENTLY to avoid table locks during refresh.

Edge Cases & Failures

Race conditions during concurrent plan upgrades causing duplicate proration charges. Mitigate with SELECT ... FOR UPDATE SKIP LOCKED and strict idempotency_key constraints. Implement optimistic locking via version columns if row-level locking causes unacceptable latency.
Floating-point precision loss when calculating fractional tier rates across high-volume usage. Enforce NUMERIC(19,4) for all monetary and unit-rate columns. Never use FLOAT or DOUBLE PRECISION for billing math.
Timezone boundary mismatches where current_period_end shifts across UTC/local DST transitions. Store all timestamps in TIMESTAMPTZ (UTC). Perform period boundary calculations in UTC. Convert to local time only at the presentation layer.
Orphaned pricing_tiers records after plan deletion. Enforce ON DELETE CASCADE on pricing_tiers. Implement a soft-delete pattern (deleted_at) if historical invoice reconstruction requires tier snapshots.
Usage spikes exceeding all tier bounds causing resolution to return no matching row. Define an explicit upper_bound IS NULL catch-all tier. Validate application-side ingestion pipelines with rate limiters to prevent database write saturation.

FAQ

Should I normalize tier thresholds into separate tables or store them as JSONB? Use separate relational tables if you require strict ACID compliance, foreign key constraints, and complex analytical queries. Use JSONB if your tiers change frequently, require rapid deployment without migrations, or if your pricing engine resolves them entirely in application memory.

How do I prevent double-charging during mid-cycle plan switches? Implement row-level locking (SELECT FOR UPDATE) during the proration workflow, generate a unique idempotency_key for each billing event, and use database transactions with SERIALIZABLE isolation to detect and abort conflicting concurrent updates.

What indexing strategy optimizes tiered usage aggregation queries? Create a composite index on (subscription_id, recorded_at) with INCLUDE (quantity, meter_id). For high-throughput systems, partition the subscription_usage table by month and maintain a materialized view for daily aggregated tier calculations.