rails-database-performancelisted

# Rails Database Index & Query Performance Audit ## Overview A systematic checklist for auditing `db/schema.rb` and ActiveRecord models for missing indexes and query anti-patterns. Work through every section below — do not stop early. ## How to Run This Audit 1. Open `db/schema.rb` 2. Open all model files (`app/models/**/*.rb`) 3. Work through every checklist item below 4. For each issue found: generate a migration, do not bundle unrelated indexes together --- ## Checklist ### 1. Foreign Key Indexes Every column ending in `_id` must have an index. ```bash grep -n "_id" db/schema.rb | grep -v "index\|#" ``` Cross-reference with: ```bash grep -n "add_index\|t\.index" db/schema.rb | grep "_id" ``` **Fix:** ```ruby add_index :table_name, :other_model_id ``` --- ### 2. Polymorphic Association Indexes Any polymorphic association (`_type` + `_id` pair) needs a **composite** index on both columns together, not two separate indexes. ```bash grep -n "_type" db/schema.rb ``` **Fix:** ```ruby add_index :comments, [:commentable_type, :commentable_id] ``` --- ### 3. Frequently Queried Scope Columns Check every model for scopes using `where`. Each column in a `where` clause on a frequently-called scope is a candidate for an index. ```bash grep -rn "scope.*where" app/models/ ``` Common patterns that need indexes: - `where(published: true)` → index on `published` - `where(featured: true)` → index on `featured` - `where(active: true)` → index on `active` - `where(status: .