### 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>
f81d7e6a56