fix: optimize sysadmin dashboard queries and add missing indexes#165
Merged
ManukMinasyan merged 5 commits intomainfrom Feb 25, 2026
Merged
fix: optimize sysadmin dashboard queries and add missing indexes#165ManukMinasyan merged 5 commits intomainfrom
ManukMinasyan merged 5 commits intomainfrom
Conversation
Add composite indexes (team_id, deleted_at, creation_source, created_at) to all entity tables — PostgreSQL does not auto-create indexes for FK constraints. Rewrite TopTeamsTableWidget to use EXISTS pre-filtering instead of double-computing records_count. Replace SignupTrendChartWidget N+1 COUNT queries with 2 GROUP BY queries. Remove SQLite compatibility code. Update copilot instructions with PostgreSQL-only and no-down() conventions.
There was a problem hiding this comment.
Pull request overview
This PR optimizes the sysadmin dashboard performance by addressing N+1 query issues and adding missing composite indexes to support efficient queries across 2400+ teams. The changes include database schema optimizations, query refactoring, and codebase convention updates.
Changes:
- Added composite indexes
(team_id, deleted_at, creation_source, created_at)to entity tables for PostgreSQL query optimization - Rewrote
TopTeamsTableWidgetto use EXISTS subqueries instead of computing record counts twice - Replaced
SignupTrendChartWidgetN+1 queries with efficient GROUP BY aggregations using PostgreSQL'sto_char() - Updated migrations to remove SQLite compatibility code and prepare for future squashing
- Documented PostgreSQL-only and no-down-method conventions in copilot instructions
Reviewed changes
Copilot reviewed 10 out of 12 changed files in this pull request and generated no comments.
Show a summary per file
| File | Description |
|---|---|
database/migrations/2026_02_25_124942_add_team_id_indexes_to_entity_tables.php |
New migration adding composite indexes with idempotency checks |
database/migrations/2025_04_30_143551_add_creation_source_to_entity_tables.php |
Simplified column addition logic, removed down method, added idempotency |
database/migrations/2024_09_22_114735_create_opportunities_table.php |
Baked creation_source column and composite index into original migration |
database/migrations/2024_09_22_091034_create_people_table.php |
Baked creation_source column and composite index into original migration |
database/migrations/2024_09_22_084119_create_notes_table.php |
Baked creation_source column and composite index into original migration |
database/migrations/2024_09_11_114549_create_tasks_table.php |
Baked creation_source column and composite index into original migration |
database/migrations/2024_08_24_133803_create_companies_table.php |
Baked creation_source column and composite index into original migration |
composer.lock |
Dependency updates for Laravel packages and Sentry |
app-modules/SystemAdmin/src/Filament/Widgets/TopTeamsTableWidget.php |
Replaced double-count filter with EXISTS pre-filtering, removed SQLite compatibility |
app-modules/SystemAdmin/src/Filament/Widgets/SignupTrendChartWidget.php |
Replaced N+1 COUNT queries with 2 GROUP BY queries using PostgreSQL to_char() |
.gitignore |
Added .github/skills to ignore list |
.github/copilot-instructions.md |
Documented PostgreSQL-only and no-down-method conventions |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Add this suggestion to a batch that can be applied as a single commit.This suggestion is invalid because no changes were made to the code.Suggestions cannot be applied while the pull request is closed.Suggestions cannot be applied while viewing a subset of changes.Only one suggestion per line can be applied in a batch.Add this suggestion to a batch that can be applied as a single commit.Applying suggestions on deleted lines is not supported.You must change the existing code in this line in order to create a valid suggestion.Outdated suggestions cannot be applied.This suggestion has been applied or marked resolved.Suggestions cannot be applied from pending reviews.Suggestions cannot be applied on multi-line comments.Suggestions cannot be applied while the pull request is queued to merge.Suggestion cannot be applied right now. Please check back later.
Summary
(team_id, deleted_at, creation_source, created_at)to all 5 entity tables — PostgreSQL does not auto-create indexes for FK constraints, causing full table scans on 2400+ teamsTopTeamsTableWidgetto useEXISTSpre-filtering instead of double-computingrecords_countviaWHERE (subquery) > 0SignupTrendChartWidgetN+1 COUNT queries (60 queries for 30-day period) with 2GROUP BYqueriescreation_sourcecolumn and full composite index into original create migrations for future squashdown()conventions.github/skills/to.gitignore(dotfiles symlinks)Test plan
php artisan migrate— indexes created successfullyphp artisan test --compact --filter=SystemAdmin— 28 tests passvendor/bin/phpstan analyse— 0 errorsvendor/bin/pint --dirty— passEXPLAIN ANALYZEshows Index Scan instead of Seq Scan on entity tables