PostgreSQL Generated Columns for Tenant Cost Attribution: Computing Per-User AI Inference Spend Without Application-Layer ETL

I ship AI features. Every time a tenant’s user consumes tokens—whether that’s Claude generating a summary, embedding search results, or classifying documents—that event becomes a billing line item. For two years, I computed these costs in Python: FastAPI endpoints wrote usage events to PostgreSQL, then nightly jobs aggregated them, applied multipliers for region and feature tier, and wrote results back to billing tables. It was fragile. A refactored job missed a feature flag. A schema migration created duplicate charges. Auditing meant reading through eight years of git history.

Generated columns fixed this.

PostgreSQL’s GENERATED ALWAYS AS clause lets you declare a column whose value is computed automatically by the database engine. For billing, this means your entire cost calculation—tokens × tier multiplier × region surcharge—lives in DDL, not in application code. No ETL. No data drift. One source of truth that survives refactors.

Why Generated Columns Beat Application Logic for Billing

Billing logic has three properties that make generated columns uniquely suited:

1. It’s deterministic. Given tokens consumed, feature tier, and region, the cost is always the same. No side effects, no external API calls.
2. It must be auditable. Every invoice trace back to raw data. Generated columns are queryable; you can SELECT ... WHERE cost_usd > 0 and trust the math.
3. It gets refactored constantly. New features tier at different rates. Regions change surcharges. If that logic lives in three FastAPI endpoints, a cron job, and a Stripe webhook handler, someone will miss a place.

Application-layer ETL pushes billing logic away from your data, into scatter. Generated columns pull it back where it belongs: in your schema.

The Schema Pattern

Here’s the real implementation from CitizenApp. We track three tables: feature usage events, tenant configurations, and the billing view.

CREATE TABLE usage_events (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,
    user_id UUID NOT NULL,
    feature_id VARCHAR(50) NOT NULL,
    region VARCHAR(10) NOT NULL CHECK (region IN ('us-east', 'eu-west', 'ap-south')),
    tokens_consumed INTEGER NOT NULL CHECK (tokens_consumed > 0),
    event_timestamp TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
);

CREATE TABLE feature_tiers (
    id SERIAL PRIMARY KEY,
    feature_id VARCHAR(50) NOT NULL,
    tier_name VARCHAR(50) NOT NULL,
    cost_per_1k_tokens DECIMAL(8, 4) NOT NULL,
    UNIQUE(feature_id, tier_name)
);

CREATE TABLE tenant_subscriptions (
    id SERIAL PRIMARY KEY,
    tenant_id UUID NOT NULL UNIQUE,
    feature_id VARCHAR(50) NOT NULL,
    tier_name VARCHAR(50) NOT NULL,
    active_from DATE NOT NULL DEFAULT CURRENT_DATE,
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    FOREIGN KEY (feature_id, tier_name) REFERENCES feature_tiers(feature_id, tier_name)
);

CREATE TABLE region_multipliers (
    region VARCHAR(10) PRIMARY KEY,
    multiplier DECIMAL(4, 2) NOT NULL CHECK (multiplier > 0)
);

INSERT INTO region_multipliers VALUES
    ('us-east', 1.0),
    ('eu-west', 1.15),
    ('ap-south', 1.25);

Now the billing table with generated columns:

CREATE TABLE billing_line_items (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,
    user_id UUID NOT NULL,
    usage_event_id UUID NOT NULL UNIQUE,
    feature_id VARCHAR(50) NOT NULL,
    tokens_consumed INTEGER NOT NULL,
    region VARCHAR(10) NOT NULL,
    
    -- Join to feature tier
    tier_name VARCHAR(50) NOT NULL,
    cost_per_1k_tokens DECIMAL(8, 4) NOT NULL,
    
    -- Calculated columns: one source of truth
    base_cost_usd DECIMAL(10, 6) GENERATED ALWAYS AS (
        (tokens_consumed::DECIMAL / 1000.0) * cost_per_1k_tokens
    ) STORED,
    
    region_multiplier DECIMAL(4, 2) GENERATED ALWAYS AS (
        COALESCE(
            (SELECT multiplier FROM region_multipliers WHERE region = billing_line_items.region),
            1.0
        )
    ) STORED,
    
    final_cost_usd DECIMAL(10, 6) GENERATED ALWAYS AS (
        base_cost_usd * region_multiplier
    ) STORED,
    
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    FOREIGN KEY (usage_event_id) REFERENCES usage_events(id) ON DELETE CASCADE,
    FOREIGN KEY (region) REFERENCES region_multipliers(region)
);

CREATE INDEX idx_billing_tenant_period ON billing_line_items(tenant_id, created_at);

The magic: final_cost_usd is always correct. You can’t insert a row without populating the source columns; the database computes the rest.

Inserting Usage and Auto-Generating Costs

Your FastAPI service writes usage events. A trigger—or application code, your choice—creates the billing line item. Here’s the trigger approach:

CREATE OR REPLACE FUNCTION populate_billing_from_usage()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO billing_line_items (
        tenant_id, user_id, usage_event_id, feature_id, tokens_consumed, region,
        tier_name, cost_per_1k_tokens
    )
    SELECT
        NEW.tenant_id,
        NEW.user_id,
        NEW.id,
        NEW.feature_id,
        NEW.tokens_consumed,
        NEW.region,
        ts.tier_name,
        ft.cost_per_1k_tokens
    FROM tenant_subscriptions ts
    JOIN feature_tiers ft ON ts.feature_id = ft.feature_id AND ts.tier_name = ft.tier_name
    WHERE ts.tenant_id = NEW.tenant_id
      AND ts.feature_id = NEW.feature_id
      AND ts.active_from <= CURRENT_DATE;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER after_usage_event_insert
AFTER INSERT ON usage_events
FOR EACH ROW
EXECUTE FUNCTION populate_billing_from_usage();

Or in FastAPI (if you prefer application control):

from fastapi import FastAPI
from sqlalchemy import text
from sqlalchemy.orm import Session

app = FastAPI()

@app.post("/api/v1/usage")
async def log_usage(event: UsageEvent, db: Session):
    # Write the usage event
    usage = UsageEventModel(**event.dict())
    db.add(usage)
    db.flush()
    
    # Query subscription tier
    subscription = db.query(TenantSubscription).filter(
        TenantSubscription.tenant_id == event.tenant_id,
        TenantSubscription.feature_id == event.feature_id
    ).first()
    
    # Create billing line item
    # Generated columns compute cost automatically
    billing = BillingLineItem(
        tenant_id=event.tenant_id,
        user_id=event.user_id,
        usage_event_id=usage.id,
        feature_id=event.feature_id,
        tokens_consumed=event.tokens_consumed,
        region=event.region,
        tier_name=subscription.tier_name,
        cost_per_1k_tokens=subscription.cost_per_1k_tokens
    )
    db.add(billing)
    db.commit()
    
    return {"status": "logged"}

No math in the endpoint. The database handles it.

Billing Queries Become Trivial

Monthly invoice for a tenant:

SELECT
    DATE_TRUNC('month', created_at) AS period,
    COUNT(*) AS line_items,
    SUM(tokens_consumed) AS total_tokens,
    SUM(final_cost_usd) AS total_usd
FROM billing_line_items
WHERE tenant_id = $1
  AND created_at >= $2
  AND created_at < $3
GROUP BY DATE_TRUNC('month', created_at);

Audit why a specific charge happened:

SELECT
    bli.id,
    bli.tokens_consumed,
    bli.cost_per_1k_tokens,
    bli.base_cost_usd,
    bli.region_multiplier,
    bli.final_cost_usd,
    ue.feature_id,
    ue.event_timestamp
FROM billing_line_items bli
JOIN usage_events ue ON bli.usage_event_id = ue.id
WHERE bli.id = $1;

The math is visible and verifiable. No debugging cryptic Python logic.

The Gotcha: STORED vs. VIRTUAL, and Performance

PostgreSQL offers two generated column modes:

• VIRTUAL (default): Computed on-read. Saves disk space; slower queries.
• STORED (my choice): Computed on-write, persisted to disk. Slower inserts; instant queries.

For billing, I use STORED because:

1. Inserts are rare; queries are constant.
2. Audit trails and exports must be fast.
3. You’ll export this data to Stripe or your accounting system. Pre-computed values avoid on-the-fly calculations across millions of rows.

The tradeoff: if region_multipliers changes (you adjust EU pricing), old rows still reflect the old multiplier. This is correct. You want immutable cost history. If a region multiplier changes mid-month, create a new tier or add a `valid_