mirror of
https://github.com/Significant-Gravitas/AutoGPT.git
synced 2026-01-15 01:58:23 -05:00
f81d7e6a56
### Changes 🏗️ This PR optimizes database performance by adding missing foreign key indexes, removing unused/redundant indexes, cleaning up all legacy untracked indexes, and adding performance indexes for materialized views to achieve 100% optimized database indexing. **Foreign Key Indexes Added:** - `AgentGraph`: `[forkedFromId, forkedFromVersion]` - For fork relationship queries - `AgentGraphExecution`: `[agentPresetId]` - For preset-based execution filtering - `AgentNodeExecution`: `[agentNodeId]` - For node execution lookups - `AgentNodeExecutionInputOutput`: `[agentPresetId]` - For preset input/output queries - `AgentPreset`: `[agentGraphId, agentGraphVersion]` & `[webhookId]` - For graph and webhook lookups - `LibraryAgent`: `[agentGraphId, agentGraphVersion]` & `[creatorId]` - For agent and creator queries - `StoreListing`: `[agentGraphId, agentGraphVersion]` - For marketplace agent lookups - `StoreListingReview`: `[reviewByUserId]` - For user review queries **Unused/Redundant Indexes Removed:** - `User.email` - Unused index identified by linter - `AnalyticsMetrics.userId` - Unused index causing write overhead - `AnalyticsDetails.type` - Redundant (covered by composite `[userId, type]`) - `APIKey.key`, `APIKey.status` - Unused indexes - Named index `"analyticsDetails"` - Converted to standard composite index **All Legacy Untracked Indexes Removed:** - `idx_store_listing_version_status` - Redundant with Prisma composite index - `idx_slv_agent` - Redundant with Prisma-managed `[agentGraphId, agentGraphVersion]` - `idx_store_listing_version_approved_listing` - Redundant with unique constraint - `StoreListing_agentId_owningUserId_idx` - Legacy index superseded by current strategy - `StoreListing_isDeleted_isApproved_idx` - Replaced by optimized composite index - `StoreListing_isDeleted_idx` - Redundant with composite index - `StoreListingVersion_agentId_agentVersion_isDeleted_idx` - Legacy index replaced - `idx_store_listing_approved` - Redundant with existing `[owningUserId, slug]` unique constraint and `[isDeleted, hasApprovedVersion]` index - `idx_slv_categories_gin` - Specialized array search index removed (can be re-added if category filtering is implemented) - `idx_profile_user` - Duplicate of Prisma-managed `Profile_userId_idx` **Materialized View Performance Indexes Added:** - `idx_mv_review_stats_rating` on `mv_review_stats(avg_rating DESC)` - Optimizes sorting agents by rating - `idx_mv_review_stats_count` on `mv_review_stats(review_count DESC)` - Optimizes sorting agents by review count **Result: 100% Optimized Database Indexing** - All database indexes are now defined and managed through Prisma schema - No more untracked indexes requiring manual SQL maintenance - Added performance indexes for materialized views used by marketplace views - Improved query performance for agent sorting and filtering - Enhanced maintainability and consistency across environments **Schema Comments Updated:** - Removed all references to dropped untracked indexes - Simplified documentation to reflect Prisma-only approach for regular tables - Added comprehensive documentation for materialized view indexes and their purposes - Maintained documentation for materialized view refresh strategy ### Checklist 📋 #### For code changes: - [x] I have clearly listed my changes in the PR description - [x] I have made a test plan - [x] I have tested my changes according to the test plan: - [x] Schema changes compile successfully with Prisma - [x] Migration adds required FK indexes and materialized view performance indexes - [x] Migration drops all legacy indexes and redundant untracked indexes - [x] All pre-commit hooks pass (linting, formatting, type checking) - [x] No breaking changes to existing foreign key relationships - [x] Verified existing Prisma indexes cover all query patterns - [x] Schema comments comprehensively document all indexing strategy - [x] Materialized view performance indexes optimize marketplace sorting 🤖 Generated with [Claude Code](https://claude.ai/code) --------- Co-authored-by: Swifty <craigswift13@gmail.com> Co-authored-by: Claude <noreply@anthropic.com>