Spaces:

Asmitha-28
/

subscription-intelligence-pipeline

Sleeping

subscription-intelligence-pipeline / sql /schema.sql

asmitha

Polish subscription intelligence portfolio project

fed039b 5 months ago

7.9 kB

-- =============================================================================
-- Subscription Intelligence Pipeline - PostgreSQL Schema
-- =============================================================================
-- Multi-tenant SaaS billing schema with:
--   - Row-level security for tenant isolation
--   - Indexes on hot query columns
--   - Materialized view for fast revenue dashboards
--
-- Run:
--   psql -h localhost -U billing_user -d billing_db -f sql/schema.sql
-- =============================================================================

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Clean slate for local development.
DROP MATERIALIZED VIEW IF EXISTS mv_monthly_revenue;
DROP TABLE IF EXISTS billing_events CASCADE;
DROP TABLE IF EXISTS invoices CASCADE;
DROP TABLE IF EXISTS subscriptions CASCADE;
DROP TABLE IF EXISTS tenants CASCADE;

-- =============================================================================
-- 1. TENANTS - dimension table
-- =============================================================================
CREATE TABLE tenants (
    tenant_id       VARCHAR(20)  PRIMARY KEY,
    company_name    VARCHAR(255) NOT NULL,
    industry        VARCHAR(100),
    plan            VARCHAR(50)  NOT NULL CHECK (plan IN ('starter','growth','business','enterprise')),
    country         VARCHAR(100),
    signup_date     DATE         NOT NULL,
    is_active       BOOLEAN      DEFAULT TRUE,
    created_at      TIMESTAMPTZ  DEFAULT NOW(),
    updated_at      TIMESTAMPTZ  DEFAULT NOW()
);

CREATE INDEX idx_tenants_plan    ON tenants(plan);
CREATE INDEX idx_tenants_country ON tenants(country);
CREATE INDEX idx_tenants_signup  ON tenants(signup_date);

-- =============================================================================
-- 2. SUBSCRIPTIONS - one row per active subscription period
-- =============================================================================
CREATE TABLE subscriptions (
    subscription_id UUID         PRIMARY KEY DEFAULT uuid_generate_v4(),
    tenant_id       VARCHAR(20)  NOT NULL REFERENCES tenants(tenant_id),
    plan            VARCHAR(50)  NOT NULL CHECK (plan IN ('starter','growth','business','enterprise')),
    status          VARCHAR(30)  NOT NULL CHECK (status IN ('active','cancelled','paused','trial')),
    monthly_amount  INTEGER      NOT NULL,
    seats           INTEGER      DEFAULT 1,
    start_date      DATE         NOT NULL,
    end_date        DATE,
    trial_end_date  DATE,
    created_at      TIMESTAMPTZ  DEFAULT NOW()
);

CREATE INDEX idx_subs_tenant     ON subscriptions(tenant_id);
CREATE INDEX idx_subs_status     ON subscriptions(status);
CREATE INDEX idx_subs_plan       ON subscriptions(plan);
CREATE INDEX idx_subs_start_date ON subscriptions(start_date);

-- =============================================================================
-- 3. INVOICES - one row per billing cycle
-- =============================================================================
CREATE TABLE invoices (
    invoice_id      VARCHAR(20)  PRIMARY KEY,
    tenant_id       VARCHAR(20)  NOT NULL REFERENCES tenants(tenant_id),
    subscription_id UUID         REFERENCES subscriptions(subscription_id),
    amount          INTEGER      NOT NULL,
    currency        CHAR(3)      DEFAULT 'INR',
    status          VARCHAR(20)  NOT NULL CHECK (status IN ('paid','failed','pending','refunded')),
    billing_date    DATE         NOT NULL,
    due_date        DATE,
    paid_date       DATE,
    created_at      TIMESTAMPTZ  DEFAULT NOW()
);

CREATE INDEX idx_inv_tenant       ON invoices(tenant_id);
CREATE INDEX idx_inv_billing_date ON invoices(billing_date);
CREATE INDEX idx_inv_status       ON invoices(status);
CREATE INDEX idx_inv_mrr_query    ON invoices(tenant_id, billing_date, status);

-- =============================================================================
-- 4. BILLING_EVENTS - append-only event log
-- =============================================================================
CREATE TABLE billing_events (
    event_id        VARCHAR(20)  PRIMARY KEY,
    tenant_id       VARCHAR(20)  NOT NULL REFERENCES tenants(tenant_id),
    event_type      VARCHAR(50)  NOT NULL,
    plan            VARCHAR(50),
    amount          INTEGER      DEFAULT 0,
    currency        CHAR(3)      DEFAULT 'INR',
    event_date      TIMESTAMPTZ  NOT NULL,
    status          VARCHAR(20)  DEFAULT 'success',
    invoice_id      VARCHAR(20)  REFERENCES invoices(invoice_id),
    seats           INTEGER,
    trial           BOOLEAN      DEFAULT FALSE,
    created_at      TIMESTAMPTZ  DEFAULT NOW()
);

-- For larger event volumes, this table can be recreated with:
-- PARTITION BY RANGE (event_date)

CREATE INDEX idx_events_tenant      ON billing_events(tenant_id);
CREATE INDEX idx_events_type        ON billing_events(event_type);
CREATE INDEX idx_events_date        ON billing_events(event_date);
CREATE INDEX idx_events_tenant_date ON billing_events(tenant_id, event_date);

-- =============================================================================
-- 5. MATERIALIZED VIEW - monthly revenue summary
-- =============================================================================
CREATE MATERIALIZED VIEW mv_monthly_revenue AS
SELECT
    t.tenant_id,
    t.company_name,
    t.industry,
    t.plan,
    t.country,
    DATE_TRUNC('month', i.billing_date) AS billing_month,
    COUNT(i.invoice_id)                 AS invoice_count,
    SUM(i.amount)                       AS total_mrr,
    SUM(i.amount) * 12                  AS arr,
    AVG(i.amount)                       AS avg_invoice_amount
FROM invoices i
JOIN tenants t ON i.tenant_id = t.tenant_id
WHERE i.status = 'paid'
GROUP BY
    t.tenant_id,
    t.company_name,
    t.industry,
    t.plan,
    t.country,
    DATE_TRUNC('month', i.billing_date);

CREATE UNIQUE INDEX idx_mv_revenue_unique
    ON mv_monthly_revenue(tenant_id, billing_month);

-- Refresh with:
-- REFRESH MATERIALIZED VIEW CONCURRENTLY mv_monthly_revenue;

-- =============================================================================
-- 6. ROW-LEVEL SECURITY - tenant isolation
-- =============================================================================
-- Application sessions should set:
--   SET app.tenant_id = 'TNT_00001';

ALTER TABLE tenants        ENABLE ROW LEVEL SECURITY;
ALTER TABLE billing_events ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoices       ENABLE ROW LEVEL SECURITY;
ALTER TABLE subscriptions  ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation_tenants ON tenants
    USING (tenant_id = current_setting('app.tenant_id', TRUE));

CREATE POLICY tenant_isolation_events ON billing_events
    USING (tenant_id = current_setting('app.tenant_id', TRUE));

CREATE POLICY tenant_isolation_invoices ON invoices
    USING (tenant_id = current_setting('app.tenant_id', TRUE));

CREATE POLICY tenant_isolation_subs ON subscriptions
    USING (tenant_id = current_setting('app.tenant_id', TRUE));

-- =============================================================================
-- 7. SAMPLE ANALYTICAL QUERIES
-- =============================================================================

-- MRR trend for a tenant:
-- SELECT billing_month, total_mrr, arr
-- FROM mv_monthly_revenue
-- WHERE tenant_id = 'TNT_00001'
-- ORDER BY billing_month;

-- Top 10 tenants by lifetime revenue:
-- SELECT tenant_id, company_name, SUM(total_mrr) AS lifetime_value
-- FROM mv_monthly_revenue
-- GROUP BY tenant_id, company_name
-- ORDER BY lifetime_value DESC
-- LIMIT 10;

-- Churn candidates with no invoice in the last 60 days:
-- SELECT t.tenant_id, t.company_name, MAX(i.billing_date) AS last_payment
-- FROM tenants t
-- LEFT JOIN invoices i ON t.tenant_id = i.tenant_id AND i.status = 'paid'
-- GROUP BY t.tenant_id, t.company_name
-- HAVING MAX(i.billing_date) < NOW() - INTERVAL '60 days'
--    OR MAX(i.billing_date) IS NULL;

	-- =============================================================================
	-- Subscription Intelligence Pipeline - PostgreSQL Schema
	-- =============================================================================
	-- Multi-tenant SaaS billing schema with:
	-- - Row-level security for tenant isolation
	-- - Indexes on hot query columns
	-- - Materialized view for fast revenue dashboards
	--
	-- Run:
	-- psql -h localhost -U billing_user -d billing_db -f sql/schema.sql
	-- =============================================================================

	CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

	-- Clean slate for local development.
	DROP MATERIALIZED VIEW IF EXISTS mv_monthly_revenue;
	DROP TABLE IF EXISTS billing_events CASCADE;
	DROP TABLE IF EXISTS invoices CASCADE;
	DROP TABLE IF EXISTS subscriptions CASCADE;
	DROP TABLE IF EXISTS tenants CASCADE;

	-- =============================================================================
	-- 1. TENANTS - dimension table
	-- =============================================================================
	CREATE TABLE tenants (
	tenant_id VARCHAR(20) PRIMARY KEY,
	company_name VARCHAR(255) NOT NULL,
	industry VARCHAR(100),
	plan VARCHAR(50) NOT NULL CHECK (plan IN ('starter','growth','business','enterprise')),
	country VARCHAR(100),
	signup_date DATE NOT NULL,
	is_active BOOLEAN DEFAULT TRUE,
	created_at TIMESTAMPTZ DEFAULT NOW(),
	updated_at TIMESTAMPTZ DEFAULT NOW()
	);

	CREATE INDEX idx_tenants_plan ON tenants(plan);
	CREATE INDEX idx_tenants_country ON tenants(country);
	CREATE INDEX idx_tenants_signup ON tenants(signup_date);

	-- =============================================================================
	-- 2. SUBSCRIPTIONS - one row per active subscription period
	-- =============================================================================
	CREATE TABLE subscriptions (
	subscription_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
	tenant_id VARCHAR(20) NOT NULL REFERENCES tenants(tenant_id),
	plan VARCHAR(50) NOT NULL CHECK (plan IN ('starter','growth','business','enterprise')),
	status VARCHAR(30) NOT NULL CHECK (status IN ('active','cancelled','paused','trial')),
	monthly_amount INTEGER NOT NULL,
	seats INTEGER DEFAULT 1,
	start_date DATE NOT NULL,
	end_date DATE,
	trial_end_date DATE,
	created_at TIMESTAMPTZ DEFAULT NOW()
	);

	CREATE INDEX idx_subs_tenant ON subscriptions(tenant_id);
	CREATE INDEX idx_subs_status ON subscriptions(status);
	CREATE INDEX idx_subs_plan ON subscriptions(plan);
	CREATE INDEX idx_subs_start_date ON subscriptions(start_date);

	-- =============================================================================
	-- 3. INVOICES - one row per billing cycle
	-- =============================================================================
	CREATE TABLE invoices (
	invoice_id VARCHAR(20) PRIMARY KEY,
	tenant_id VARCHAR(20) NOT NULL REFERENCES tenants(tenant_id),
	subscription_id UUID REFERENCES subscriptions(subscription_id),
	amount INTEGER NOT NULL,
	currency CHAR(3) DEFAULT 'INR',
	status VARCHAR(20) NOT NULL CHECK (status IN ('paid','failed','pending','refunded')),
	billing_date DATE NOT NULL,
	due_date DATE,
	paid_date DATE,
	created_at TIMESTAMPTZ DEFAULT NOW()
	);

	CREATE INDEX idx_inv_tenant ON invoices(tenant_id);
	CREATE INDEX idx_inv_billing_date ON invoices(billing_date);
	CREATE INDEX idx_inv_status ON invoices(status);
	CREATE INDEX idx_inv_mrr_query ON invoices(tenant_id, billing_date, status);

	-- =============================================================================
	-- 4. BILLING_EVENTS - append-only event log
	-- =============================================================================
	CREATE TABLE billing_events (
	event_id VARCHAR(20) PRIMARY KEY,
	tenant_id VARCHAR(20) NOT NULL REFERENCES tenants(tenant_id),
	event_type VARCHAR(50) NOT NULL,
	plan VARCHAR(50),
	amount INTEGER DEFAULT 0,
	currency CHAR(3) DEFAULT 'INR',
	event_date TIMESTAMPTZ NOT NULL,
	status VARCHAR(20) DEFAULT 'success',
	invoice_id VARCHAR(20) REFERENCES invoices(invoice_id),
	seats INTEGER,
	trial BOOLEAN DEFAULT FALSE,
	created_at TIMESTAMPTZ DEFAULT NOW()
	);

	-- For larger event volumes, this table can be recreated with:
	-- PARTITION BY RANGE (event_date)

	CREATE INDEX idx_events_tenant ON billing_events(tenant_id);
	CREATE INDEX idx_events_type ON billing_events(event_type);
	CREATE INDEX idx_events_date ON billing_events(event_date);
	CREATE INDEX idx_events_tenant_date ON billing_events(tenant_id, event_date);

	-- =============================================================================
	-- 5. MATERIALIZED VIEW - monthly revenue summary
	-- =============================================================================
	CREATE MATERIALIZED VIEW mv_monthly_revenue AS
	SELECT
	t.tenant_id,
	t.company_name,
	t.industry,
	t.plan,
	t.country,
	DATE_TRUNC('month', i.billing_date) AS billing_month,
	COUNT(i.invoice_id) AS invoice_count,
	SUM(i.amount) AS total_mrr,
	SUM(i.amount) * 12 AS arr,
	AVG(i.amount) AS avg_invoice_amount
	FROM invoices i
	JOIN tenants t ON i.tenant_id = t.tenant_id
	WHERE i.status = 'paid'
	GROUP BY
	t.tenant_id,
	t.company_name,
	t.industry,
	t.plan,
	t.country,
	DATE_TRUNC('month', i.billing_date);

	CREATE UNIQUE INDEX idx_mv_revenue_unique
	ON mv_monthly_revenue(tenant_id, billing_month);

	-- Refresh with:
	-- REFRESH MATERIALIZED VIEW CONCURRENTLY mv_monthly_revenue;

	-- =============================================================================
	-- 6. ROW-LEVEL SECURITY - tenant isolation
	-- =============================================================================
	-- Application sessions should set:
	-- SET app.tenant_id = 'TNT_00001';

	ALTER TABLE tenants ENABLE ROW LEVEL SECURITY;
	ALTER TABLE billing_events ENABLE ROW LEVEL SECURITY;
	ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
	ALTER TABLE subscriptions ENABLE ROW LEVEL SECURITY;

	CREATE POLICY tenant_isolation_tenants ON tenants
	USING (tenant_id = current_setting('app.tenant_id', TRUE));

	CREATE POLICY tenant_isolation_events ON billing_events
	USING (tenant_id = current_setting('app.tenant_id', TRUE));

	CREATE POLICY tenant_isolation_invoices ON invoices
	USING (tenant_id = current_setting('app.tenant_id', TRUE));

	CREATE POLICY tenant_isolation_subs ON subscriptions
	USING (tenant_id = current_setting('app.tenant_id', TRUE));

	-- =============================================================================
	-- 7. SAMPLE ANALYTICAL QUERIES
	-- =============================================================================

	-- MRR trend for a tenant:
	-- SELECT billing_month, total_mrr, arr
	-- FROM mv_monthly_revenue
	-- WHERE tenant_id = 'TNT_00001'
	-- ORDER BY billing_month;

	-- Top 10 tenants by lifetime revenue:
	-- SELECT tenant_id, company_name, SUM(total_mrr) AS lifetime_value
	-- FROM mv_monthly_revenue
	-- GROUP BY tenant_id, company_name
	-- ORDER BY lifetime_value DESC
	-- LIMIT 10;

	-- Churn candidates with no invoice in the last 60 days:
	-- SELECT t.tenant_id, t.company_name, MAX(i.billing_date) AS last_payment
	-- FROM tenants t
	-- LEFT JOIN invoices i ON t.tenant_id = i.tenant_id AND i.status = 'paid'
	-- GROUP BY t.tenant_id, t.company_name
	-- HAVING MAX(i.billing_date) < NOW() - INTERVAL '60 days'
	-- OR MAX(i.billing_date) IS NULL;