v1.0.9: Faster CRM & Billing Queries with a New Database Index

Released: v1.0.9 · Category: Database Performance

Overview

Version 1.0.9 ships a targeted database schema improvement: a composite index on customers(project_id, status). This is a backend-only change with no API or UI surface, but it meaningfully improves query performance across the CRM and billing subsystems.

Background

The SaaS Factory platform's CRM and billing layers issue a high volume of queries that filter customer records by project_id. Queries frequently also filter by status — for example, to find only active customers or to exclude churned accounts from health scoring. Prior to this release, neither column was indexed on the billing-schema customers table, meaning every such query required a full sequential scan.

As the number of customer records per project grows, the cost of these scans scales linearly — making this index increasingly important over time.

What Was Added

A composite B-tree index on:

CREATE INDEX ON customers (project_id, status);

The column order (project_id first) is intentional: it satisfies queries that filter on project_id alone and queries that filter on both project_id and status, covering the full range of access patterns observed in the platform.

Affected Query Patterns

Subsystem	Query Pattern	Benefit
CRM	Customer health scoring by project	Index scan instead of seq scan
CRM	Usage analytics filtered to project	Index scan instead of seq scan
CRM	Proactive engagement lookups	Index scan instead of seq scan
Billing	Active subscription lookups	Index scan instead of seq scan
Billing	Churn prediction queries	Index scan instead of seq scan
Billing	Invoicing — customer status checks	Index scan instead of seq scan

Upgrade Notes

No code changes required. This release contains only a schema migration.
The index is created automatically on deployment via the standard migration pipeline.
Existing data is indexed at migration time; no manual backfill step is needed.
On very large customers tables the index build may take a few seconds — it is safe to run while the application is live.