Database Performance: Composite Indexes on Core Tables

This page documents the composite database indexes present on the platform's core schema tables, why they exist, and which queries they serve. The indexes described here were introduced in v0.1.374 as part of the PERF-10 performance investigation.

Background

Without compound indexes on the right columns, queries that filter by org_id and a second dimension (status, date, etc.) fall back to a full table scan. On small datasets this is invisible; on a production database with tens of thousands of tenancies, deposit releases, compliance checks, and audit events, scan times grow linearly and work queues, compliance dashboards, and activity feeds can take seconds per request instead of milliseconds.

PERF-10 identified 5 priority queries without adequate indexes. All 5 are now covered.

Index reference

`tenancies`

Index name	Columns	Purpose
`tenancies_org_id_idx`	`(org_id)`	Broad org-scoped scans
`tenancies_property_id_idx`	`(property_id)`	Property-detail lookups
`tenancies_org_status_idx`	`(org_id, status)`	Work queue queries — every dashboard load filters by org + status
`tenancies_org_end_date_idx`	`(org_id, end_date)`	Upcoming checkout and overdue tenancy detection

Migration note: tenancies_org_status_idx was previously named tenancies_status_idx. The SQL migration creates the canonical name with IF NOT EXISTS; both names resolve to equivalent index definitions.

`deposit_releases`

Index name	Columns	Purpose
`deposit_releases_org_id_idx`	`(org_id)`	Org-scoped scans
`deposit_releases_tenancy_id_idx`	`(tenancy_id)`	Per-tenancy lookups
`deposit_releases_org_status_idx`	`(org_id, status)`	Deposit release dashboard kanban view — filters by org + status

`compliance_checks`

Index name	Columns	Purpose
`compliance_checks_org_id_idx`	`(org_id)`	Org-scoped scans
`compliance_checks_tenancy_id_idx`	`(tenancy_id)`	Every compliance query JOIN on tenancy
`compliance_checks_org_status_idx`	`(org_id, status)`	Compliance dashboard portfolio table — filters by org + status
`compliance_checks_tenancy_rule` (unique)	`(tenancy_id, rule_id)`	Upsert guard on re-evaluation

`audit_log`

Index name	Columns	Purpose
`audit_log_org_created_idx`	`(org_id, created_at DESC)`	Activity feed and audit log queries — filters by org, ordered by recency

Schema note: audit_log is owned by the shell package and is not declared in src/db/schema.ts. Its index is applied only via the SQL migration 0007_perf10_core_indexes.sql.

Running the migration

The migration file is src/db/migrations/0007_perf10_core_indexes.sql. All statements use CREATE INDEX IF NOT EXISTS, so the migration is idempotent and safe to run multiple times or on environments where partial indexes may already exist.

-- Example: manually applying on an existing database
psql $DATABASE_URL -f src/db/migrations/0007_perf10_core_indexes.sql

For standard deployments the migration runs automatically as part of the database migration step in your deployment pipeline.

Adding indexes in the future

When adding a new query that filters a high-cardinality table by org_id plus at least one other column:

Add a named index() entry in the relevant table definition in src/db/schema.ts.
Add the corresponding CREATE INDEX IF NOT EXISTS statement to a new SQL migration file.
Follow the naming convention {table}_{col1}_{col2}_idx (e.g. tenancies_org_end_date_idx).

For tables outside the Drizzle schema (such as shell-package tables), skip step 1 and apply the index only via SQL migration.