Add covering DB indexes for sync_jobs claim query and leaderboard/profile aggregation queries

[Jagadeeshftw]

📌 Description

The hot sync_jobs claim query in internal/syncjobs/worker.go filters WHERE status='pending' AND run_at <= now() ORDER BY run_at ASC ... FOR UPDATE SKIP LOCKED, but the migrations add no composite index on (status, run_at). The leaderboard (internal/handlers/leaderboard.go) and profile (internal/handlers/user_profile.go) queries run multi-CTE aggregations over contributions; existing indexes (migrations 000014/000015) may not cover the exact predicates. Missing indexes cause sequential scans as data grows.

💡 Why it matters: The claim query runs every second per worker and the leaderboard is on the landing page; missing indexes degrade both as the dataset grows.

🧩 Requirements and context

• Add a new migration creating idx_sync_jobs_status_run_at on sync_jobs(status, run_at) with a matching .down.sql.
• Use EXPLAIN ANALYZE to confirm the claim and leaderboard queries hit indexes; add any additional covering indexes the plans reveal.
• Verify no duplicate/redundant indexes are introduced (reconcile with 000014/000015).
• Document the index rationale in the migration and SYNC_ISSUES_PR_TROUBLESHOOTING.md.
• Keep migrations idempotent (IF NOT EXISTS).

Non-functional requirements

• Must be secure, tested, and documented.
• Should be efficient and easy to review.

🛠️ Suggested execution

1. Fork the repo and create a branch

git checkout -b perf/sync-jobs-and-aggregation-indexes

2. Implement changes

• Write/modify the relevant source: new migrations/0000XX_*.up.sql + .down.sql
• Write comprehensive tests: internal/migrate smoke test that up/down applies cleanly
• Add documentation: migration comments + troubleshooting doc
• Include SQL comments explaining each index
• Validate security assumptions: indexes do not change row visibility/permissions

3. Test and commit

• Run tests:

go test ./internal/migrate/... && go test ./...

• Cover edge cases: down migration drops the new indexes only
• Include EXPLAIN ANALYZE before/after in the PR description.

Example commit message

perf(db): index sync_jobs claim and contribution aggregation queries

✅ Acceptance criteria

• Composite index on sync_jobs(status, run_at) added with a down migration
• EXPLAIN ANALYZE shows index usage for claim and leaderboard queries
• No redundant indexes vs 000014/000015
• Up/down apply cleanly

🔒 Security notes

Index-only change; ensure the down migration removes exactly the added indexes and nothing else.

📋 Guidelines

• Minimum 95% test coverage
• Clear documentation
• Timeframe: 96 hours

[mxrtins04]

Hi, can i this please?