Faster Revenue Analytics: Composite Index on Invoices

Version: 1.0.49
Category: Performance / Database

Overview

Starting in v1.0.49, revenue analytics queries — including MRR time series and ARR summaries — are backed by a dedicated composite database index on the invoices table. This eliminates full table scans that previously caused query cost to grow linearly with billing volume.

Background

The platform's revenue analytics engine powers MRR and ARR dashboards by querying the invoices table. Every one of these queries shares the same filter pattern:

WHERE project_id = $1
  AND paid_at BETWEEN $2 AND $3
  AND status = 'paid'

Prior to this release, no index covered this access pattern. The database was forced to scan the entire invoices table on every request — a cost that grew proportionally with the number of rows as billing volume increased.

What Changed

A composite B-tree index has been added to the invoices table:

CREATE INDEX ON invoices (project_id, paid_at, status);

Column Order Rationale

Position	Column	Reason
1	`project_id`	Equality filter — highest selectivity, narrows the scan immediately
2	`paid_at`	Range filter — sorted within each `project_id` partition for efficient range scans
3	`status`	Low-cardinality equality filter — included to enable index-only scans and avoid a heap fetch for the common `status = 'paid'` predicate

Performance Impact

Query complexity for MRR and ARR analytics drops from O(n) (full table scan) to O(log n + k) (index range scan), where n is the total number of invoice rows and k is the number of rows matching the filter.

This improvement is most significant for projects with high billing volume, where the invoices table accumulates millions of rows over time.

Compatibility

No application code changes are required.
No API changes — all existing endpoints and response formats are unaffected.
No schema breaking changes — the index is purely additive.
The migration is safe to run on a live database; index creation does not lock the table in supported Postgres versions.

Affected Features

MRR time series dashboard
ARR summary dashboard
Any revenue analytics query that filters invoices by project_id, paid_at, and status