Designing Tiered vs Flat-Rate Subscription Databases

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. It covers 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
 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 with id, subscription_id, meter_id, quantity, and recorded_at 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 on (plan_id, tier_index) and (subscription_id, recorded_at) 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

Diagnostic workflow for resolving the correct price at runtime. Flat-rate lookups are O(1) via direct join. Tiered resolution requires window functions or application-side aggregation.

Implement a deterministic pricing resolver that caches tier thresholds. Validate usage boundaries before invoice generation to prevent billing anomalies.

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', execute a lateral join against pricing_tiers to 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, pt.upper_bound) - 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 ASC
) pt;

Step 4: Calculate tiered cost using SUM(LEAST(usage, upper_bound) - lower_bound * unit_price) logic. 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 floating-point 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) AS usage_date,
 SUM(quantity) AS daily_qty
FROM subscription_usage
GROUP BY 1, 2, 3
WITH DATA;

Proration & Mid-Cycle State Transitions

Step-by-step workflow for handling plan changes without corrupting billing cycles. 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 using SELECT ... FOR UPDATE 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 in the current period: current_period_end - NOW(). Use EXTRACT(EPOCH FROM (current_period_end - NOW())) / 86400.0 for fractional day precision. Never truncate early; 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 negative amount for credits, positive for upgrades.

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 current_period_end if the cycle shifts, or append a new billing period if mid-cycle. Commit the transaction only after all line items are persisted. Emit a domain event (SubscriptionPlanChanged) to downstream ledger services.

Implementation Patterns

  • PostgreSQL LATERAL JOIN with generate_series for tier boundary mapping without application-side loops. Use generate_series(1, tier_count) when tier boundaries are dynamic or require interpolation. This eliminates N+1 application queries and pushes computation to the query planner.
  • Idempotent invoice generation using idempotency_key and ON CONFLICT DO NOTHING on invoice_line_items. Network retries during webhook processing must never duplicate charges. Store keys in a separate idempotency_keys table with TTL to prevent unbounded index growth.
  • JSONB pricing_rules column for storing tiered thresholds when schema flexibility outweighs strict relational normalization. Use jsonb_path_query for rapid tier evaluation. Validate schema at the application layer using JSON Schema to prevent malformed pricing payloads.
  • Database-level triggers on subscription_usage to auto-update current_tier_index and cache resolved pricing for read-heavy dashboards. Implement AFTER INSERT triggers with pg_advisory_lock to serialize heavy recalculations. Avoid synchronous trigger execution for high-throughput metering; defer to async materialized views instead.

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. Apply ROUND() only at the final currency formatting step.
  • 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, leading to NULL pointer exceptions in pricing resolvers. Enforce ON DELETE CASCADE on pricing_tiers. Implement a soft-delete pattern (deleted_at) if historical invoice reconstruction requires tier snapshots.
  • Usage spikes exceeding max_usage bounds, causing tier resolution to return NULL or default to incorrect bracket. Define an explicit upper_bound IS NULL catch-all tier. Validate application-side ingestion pipelines with rate limiters and circuit breakers to prevent database write saturation during traffic spikes.

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.