Use PostgreSQL CASE expressions, CTEs, and aggregate functions to compute tiered or usage-based SaaS subscription prices directly in SQL.
Running pricing logic in SQL keeps billing close to the data, removes app code duplication, and lets finance teams audit numbers quickly.
You need a subscriptions table (customer_id, plan, seats, usage_units) and a pricing table (plan, base_fee, unit_price, included_units).
Join subscriptions to pricing and use CASE to add overage charges when usage exceeds included units.
total = base_fee + GREATEST(usage_units – included_units, 0) * unit_price
Create a CTE that aggregates usage events per subscription per month, then apply the same formula.
Store contract_term and discount_pct; multiply the monthly total by 12 and apply the discount in one CASE expression.
Index foreign keys, cache pricing in a materialized view, and schedule recalculations with REFRESH CONCURRENTLY.
Version your pricing table with valid_from/valid_to columns and reference the correct row using WHERE current_date BETWEEN.
.
Yes. Use a cron job or Postgres cron extension to insert invoice lines nightly.
Store charges in the smallest unit (cents) and convert in the reporting layer or with a rates table joined by date.