SaaS Pricing Models and How They Affect Your Database Schema

#saas #database #node #architecture

Most SaaS architecture articles treat pricing as a product decision that lives in Stripe. The database schema is an afterthought. But your pricing model directly shapes your data model, and choosing the wrong schema for your pricing model creates expensive migration work when you need to change either one.

Here's how the four main SaaS pricing models translate to database architecture decisions.

Model 1: Flat Rate (One Price, Everyone Gets Everything)

Simple product, simple schema. Every user gets the same features for the same price.

CREATE TABLE subscriptions (
  id UUID PRIMARY KEY,
  tenant_id UUID REFERENCES tenants(id),
  stripe_subscription_id VARCHAR(100),
  status VARCHAR(20),  -- 'active', 'cancelled', 'past_due'
  current_period_end TIMESTAMP,
  cancelled_at TIMESTAMP
);

This is the simplest model and works well for early-stage SaaS. The limitation: you can't charge different customers different amounts or offer different feature sets.

Model 2: Tiered Pricing (Starter / Growth / Enterprise)

Most SaaS companies end up here. Different plans with different feature sets and price points.

CREATE TABLE plans (
  id UUID PRIMARY KEY,
  name VARCHAR(50),         -- 'starter', 'growth', 'enterprise'
  stripe_price_id VARCHAR(100),
  monthly_price_cents INT,
  features JSONB,           -- feature flags per plan
  limits JSONB              -- usage limits per plan
);

-- Example features/limits JSON:
-- { "api_access": true, "webhooks": true, "sso": false }
-- { "max_users": 10, "max_projects": 50, "api_calls_monthly": 100000 }

CREATE TABLE subscriptions (
  id UUID PRIMARY KEY,
  tenant_id UUID REFERENCES tenants(id),
  plan_id UUID REFERENCES plans(id),
  stripe_subscription_id VARCHAR(100),
  status VARCHAR(20),
  current_period_start TIMESTAMP,
  current_period_end TIMESTAMP
);

The features and limits JSONB columns let you change plan configurations without schema migrations. Adding a new feature flag is a data change, not a schema change.

Model 3: Usage-Based Pricing (Pay for What You Use)

Usage-based pricing (Twilio, AWS, OpenAI all use this model) requires a metering infrastructure that most schema designs don't account for.

-- Raw usage events: append-only, never update
CREATE TABLE usage_events (
  id UUID PRIMARY KEY,
  tenant_id UUID REFERENCES tenants(id),
  metric VARCHAR(50),      -- 'api_calls', 'ai_tokens', 'storage_gb', 'emails_sent'
  quantity DECIMAL(12,4),
  recorded_at TIMESTAMP DEFAULT NOW(),
  idempotency_key VARCHAR(100) UNIQUE  -- prevent double-counting
) PARTITION BY RANGE (recorded_at);  -- partition by month for query performance

-- Aggregate materialized view for billing calculations
CREATE MATERIALIZED VIEW monthly_usage AS
SELECT
  tenant_id,
  metric,
  DATE_TRUNC('month', recorded_at) AS month,
  SUM(quantity) AS total
FROM usage_events
GROUP BY tenant_id, metric, DATE_TRUNC('month', recorded_at);

-- Refresh periodically (or on demand for billing)
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_usage;

The idempotency_key is critical, usage events come from application code that might retry, and double-counting usage is a billing error that will upset customers and require manual reconciliation.

Model 4: Per-Seat Pricing (Charge Per User)

SaaS products that charge per user (Slack, Notion, Linear) need seat management infrastructure:

CREATE TABLE subscriptions (
  id UUID PRIMARY KEY,
  tenant_id UUID REFERENCES tenants(id),
  plan_id UUID REFERENCES plans(id),
  stripe_subscription_id VARCHAR(100),
  status VARCHAR(20),
  -- Seat tracking
  included_seats INT,       -- seats in current billing cycle
  overage_seats INT DEFAULT 0,
  max_seats INT             -- hard limit if applicable
);

-- Track seat changes for proration calculation
CREATE TABLE seat_change_log (
  id UUID PRIMARY KEY,
  subscription_id UUID REFERENCES subscriptions(id),
  change_type VARCHAR(20),  -- 'added', 'removed'
  quantity INT,
  changed_at TIMESTAMP DEFAULT NOW(),
  stripe_adjustment_id VARCHAR(100)  -- proration invoice item
);

When a user is added mid-billing-cycle, you prorate and charge immediately. When removed, you credit the remaining days. Stripe handles the math, but you need to record the events.

The Add-On and Feature Flag Layer

Almost every SaaS eventually sells add-ons: individual features available for purchase on top of a base plan. The schema needs to support this without becoming a nightmare:

-- Add-ons available in your product
CREATE TABLE add_ons (
  id UUID PRIMARY KEY,
  name VARCHAR(100),
  stripe_price_id VARCHAR(100),
  feature_key VARCHAR(50) UNIQUE  -- matches feature flag name
);

-- Tenants that have purchased add-ons
CREATE TABLE tenant_add_ons (
  id UUID PRIMARY KEY,
  tenant_id UUID REFERENCES tenants(id),
  add_on_id UUID REFERENCES add_ons(id),
  stripe_subscription_item_id VARCHAR(100),
  active_from TIMESTAMP,
  active_until TIMESTAMP,  -- null = active indefinitely
  UNIQUE(tenant_id, add_on_id)
);

Feature gate check that merges plan features and add-ons:

async function tenantHasFeature(tenantId: string, featureKey: string): Promise<boolean> {
  const result = await db.query(`
    SELECT 1 FROM (
      -- Check plan features
      SELECT 1
      FROM subscriptions s
      JOIN plans p ON s.plan_id = p.id
      WHERE s.tenant_id = $1
        AND s.status = 'active'
        AND (p.features->$2)::boolean = true

      UNION ALL

      -- Check purchased add-ons
      SELECT 1
      FROM tenant_add_ons ta
      JOIN add_ons a ON ta.add_on_id = a.id
      WHERE ta.tenant_id = $1
        AND a.feature_key = $2
        AND ta.active_from <= NOW()
        AND (ta.active_until IS NULL OR ta.active_until > NOW())
    ) AS has_feature
  `, [tenantId, featureKey]);

  return result.rowCount > 0;
}

The Schema Decision That Costs the Most to Change

The hardest migration in SaaS architecture: moving from single-column plan field to a full subscription and usage model. Companies that start with tenants.plan = 'pro' and add billing complexity on top pay for it in data migrations.

If there's any chance your pricing will evolve beyond a single tier, build the subscription model from the start. The overhead is a few extra tables. The alternative is a multi-week migration while your billing system is live.

Pricing architecture is one of the foundational decisions in SaaS development that most tutorials overlook. I design SaaS data models with pricing models in mind from the start, for US clients building products that need to scale their business model alongside their code. Learn more at waqarhabib.com/services/saas-development.

Originally published at waqarhabib.com