Add missing database indexes on tenant_id, created_at, and release_state #12

New Issue

2026-04-20T09:35:46Z

sharang commented

2026-04-20 09:35:46 +00:00

Problem

Migrations show no indexes on columns that are filtered and sorted in every request:

tenant_id — appears in WHERE clause of every query across all tables
created_at — used for default sort order on list endpoints
release_state — filtered in canonical_controls queries

At scale, these cause full table scans (O(n)) instead of index seeks (O(log n)).

Required Actions

Audit all tables in migrations/ — identify tables missing index on tenant_id
Create a migration adding composite indexes:
- CREATE INDEX idx_<table>_tenant_created ON <table>(tenant_id, created_at DESC)
- CREATE INDEX idx_canonical_controls_release_state ON canonical_controls(tenant_id, release_state)
Use CONCURRENTLY to avoid table locks on live data: CREATE INDEX CONCURRENTLY
Run EXPLAIN ANALYZE on the 5 most common query patterns before and after

Acceptance Criteria

All tenant-scoped tables have a (tenant_id, created_at) index
EXPLAIN ANALYZE shows index scan (not seq scan) for common queries
Note: requires [migration-approved] commit marker per CLAUDE.md

## Problem Migrations show no indexes on columns that are filtered and sorted in every request: - `tenant_id` — appears in WHERE clause of every query across all tables - `created_at` — used for default sort order on list endpoints - `release_state` — filtered in canonical_controls queries At scale, these cause full table scans (O(n)) instead of index seeks (O(log n)). ## Required Actions 1. Audit all tables in `migrations/` — identify tables missing index on `tenant_id` 2. Create a migration adding composite indexes: - `CREATE INDEX idx_<table>_tenant_created ON <table>(tenant_id, created_at DESC)` - `CREATE INDEX idx_canonical_controls_release_state ON canonical_controls(tenant_id, release_state)` 3. Use `CONCURRENTLY` to avoid table locks on live data: `CREATE INDEX CONCURRENTLY` 4. Run EXPLAIN ANALYZE on the 5 most common query patterns before and after ## Acceptance Criteria - All tenant-scoped tables have a `(tenant_id, created_at)` index - EXPLAIN ANALYZE shows index scan (not seq scan) for common queries - Note: requires `[migration-approved]` commit marker per CLAUDE.md

sharang added this to the M2: Data Integrity & Reliability milestone 2026-04-20 09:35:46 +00:00

sharang added the severity: medium data-integrity labels 2026-04-20 09:35:46 +00:00

Sign in to join this conversation.

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: Benjamin_Boenisch/breakpilot-compliance#12