[bounty] speed up queries with min / max length

[louis030195]

/bounty 100

Here's a summary for the GitHub bounty:

Summary

Found performance optimization opportunity in screenpipe's SQLite database queries, specifically around text length filtering operations which currently lack proper indexing.

Current Issue

• Database queries filtering on text length (min_length/max_length) are not optimized
• Using LENGTH() function in WHERE clauses without index support
• Affects OCR text, audio transcriptions, and UI monitoring queries

Proposed Solution

Add dedicated columns and indexes for text length to optimize queries:

1. Schema Updates:

- Add text_length columns to relevant tables
- Create indexes on these new columns
- Update existing records with text lengths

2. Code Updates:

• Modify insertion/update logic to maintain text_length columns
• Update search queries to use new columns instead of LENGTH() function

Tables Affected

• ocr_text
• audio_transcriptions
• ui_monitoring

Expected Outcome

• Faster queries when filtering by text length
• Better query plan optimization
• Reduced CPU usage for text length calculations

Bounty Requirements

1. Implement schema migrations
2. Update database operations code
3. Add tests to verify performance improvement
4. Document performance benchmarks before/after
5. Ensure backward compatibility

#tags: #performance #database #sqlite #optimization

[algora-pbc]

## 💎 $100 bounty • Screenpi.pe

### Steps to solve:
1. Start working: Comment /attempt #1240 with your implementation plan
2. Submit work: Create a pull request including /claim #1240 in the PR body to claim the bounty
3. Receive payment: 100% of the bounty is received 2-5 days post-reward. Make sure you are eligible for payouts

Thank you for contributing to mediar-ai/screenpipe!

Add a bounty • Share on socials

Attempt	Started (GMT+0)	Solution
🟢 @bernardev254	Jan 29, 2025, 7:47:35 PM	#1243

[bernardev254]

/attempt #1208

Options

• Cancel my attempt

[algora-pbc]

💡 @bernardev254 submitted a pull request that claims the bounty. You can visit your bounty board to reward.

[algora-pbc]

🎉🎈 @bernardev254 has been awarded $100! 🎈🎊

• Share on socials
• Give feedback