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:
It's deterministic. Given tokens consumed, feature tier, and region, the cost is always the same. No side effects, no external API calls.
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.
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 );
Enter fullscreen mode
Exit fullscreen mode
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 );
Enter fullscreen mode
Exit fullscreen mode
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 ();
Enter fullscreen mode
Exit fullscreen mode
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 " }
Enter fullscreen mode
Exit fullscreen mode
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 );
Enter fullscreen mode
Exit fullscreen mode
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 ;
Enter fullscreen mode
Exit fullscreen mode
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
STORED
For billing, I use STORED because:
Inserts are rare; queries are constant.
Audit trails and exports must be fast .
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_