Add database performance indexes with comprehensive testing

[spotty118]

✅ Database Migration Implementation:

• Added 2 optimized performance indexes for frequently queried fields
• idx_archon_tasks_project_status: Tasks by project + status
• idx_archon_crawled_pages_source_chunk: Pages by source + chunk (covers source-only queries)
• Dropped redundant idx_archon_crawled_pages_source_id index to reduce overhead

✅ Production Safety Features:

• Uses CONCURRENTLY for zero-downtime deployment and index removal
• Uses IF NOT EXISTS/IF EXISTS for proper duplicate handling
• Only adds missing indexes, removes redundant ones

✅ Comprehensive Testing Completed:

• Set up local Supabase test environment
• Created full Archon schema in test database
• Successfully executed migration script
• Verified all indexes created correctly
• Confirmed Archon server starts and runs with new indexes
• Validated database connectivity and service initialization

Performance improvements ready for production deployment.

[coderabbitai[bot]]

[!WARNING]

Rate limit exceeded

@spotty118 has exceeded the limit for the number of commits or files that can be reviewed per hour. Please wait 6 minutes and 27 seconds before requesting another review.

⌛ How to resolve this issue?

After the wait time has elapsed, a review can be triggered using the @coderabbitai review command as a PR comment. Alternatively, push new commits to this PR.

We recommend that you space out your commits to avoid hitting the rate limit.

🚦 How do rate limits work?

CodeRabbit enforces hourly rate limits for each developer per organization.

Our paid plans have higher rate limits than the trial, open-source and free plans. In all cases, we re-allow further reviews after a brief timeout.

Please see our FAQ for further information.

📥 Commits

Reviewing files that changed from the base of the PR and between 012d2c58ed406bb025728d1d6fd24cf9e550743e and 906fd6fbca2083a2131b3eb109ed478a2cf9e233.

📒 Files selected for processing (1)

• migration/add_performance_indexes.sql (1 hunks)

✨ Finishing Touches

🧪 Generate unit tests

• [ ] Create PR with unit tests
• [ ] Post copyable unit tests in a comment

---

Thanks for using CodeRabbit! It's free for OSS, and your support helps us grow. If you like it, consider giving us a shout-out.

❤️ Share

• X
• Mastodon
• Reddit
• LinkedIn

_{Comment @coderabbitai help to get the list of available commands and usage tips.}

[coderabbitai[bot]]

✅ Actions performed

Review triggered.

Note: CodeRabbit is an incremental review system and does not re-review already reviewed commits. This command is applicable only when automatic reviews are paused.

[coderabbitai[bot]]

✅ Actions performed

Review triggered.

Note: CodeRabbit is an incremental review system and does not re-review already reviewed commits. This command is applicable only when automatic reviews are paused.

[spotty118]

@coderabbitai review

[coderabbitai[bot]]

✅ Actions performed

Review triggered.

Note: CodeRabbit is an incremental review system and does not re-review already reviewed commits. This command is applicable only when automatic reviews are paused.

[leex279]

I am no expert on this, but the whole indexing stuff increases the storage by a lot (according to claude by 3x). For production usecases its fine, but for testing and a lot of people running it locally with supabase cloud, this will lead into running into the storage limit for the free tier. so maybe we should move the whole indexing into the new migration sql file you created here and the user can decide themself to use it or not.

@coleam00 what do you think? Am I wrong with my assumption?

[Wirasm]

I will Close this based on @leex279 Comment

Thank you for the contribution @spotty118