Repository navigation
bounties table has no index supporting BountyExpiryScheduler's hourly full-table sweep #347
Description
BountiesService.expireOverdue (src/bounties/bounties.service.ts) runs WHERE bounty.deadline IS NOT NULL AND bounty.deadline < :now AND bounty.status IN ('open','funded','claimed'). BountyExpiryScheduler.sweepOverdueBounties (src/bounties/bounty-expiry.scheduler.ts) invokes this every hour via @Cron(CronExpression.EVERY_HOUR) — note this scheduler is what closed the "expireOverdue is dead code" gap (#152), so this query now genuinely executes hourly in production, not just in theory.
None of Bounty's indexes cover this access pattern: the claimedById-keyed index doesn't help; the status/paidAt indexes added in #13/AddAnalyticsBountyIndexes are partial indexes restricted to WHERE status = 'paid' — the exact opposite status subset from what expireOverdue filters on (open/funded/claimed). So every hourly sweep does a full sequential scan of the entire bounties table, checking every row regardless of status, to find the (typically tiny) subset that's actually overdue.
Fix: add a partial index, e.g. @Index('IDX_bounties_deadline_open_statuses', ['deadline'], { where: \"status" IN ('open','funded','claimed')` })`, mirroring the partial-index pattern the codebase already uses for the paid-status index.
Activity
shobhamerabacha-star commentedon Sep 18, 2026
Hello team,
I would like to resolve this issue. Here is my proposed execution plan:
bounties table has no index supporting BountyExpiryScheduler's hourly full-table sweepwith strict schema validation and idempotent execution.I can start working on this right away. Please assign!
DanielTobiDev commentedon Sep 25, 2026
@DanielTobiDev has applied to work on this issue as part of the Stellar Wave Program's 9th wave.
ℹ️ Repo Maintainers: To accept this application, review their application or assign @DanielTobiDev to this issue.
CelestinaBeing commentedon Sep 25, 2026
@CelestinaBeing has applied to work on this issue as part of the Stellar Wave Program's 9th wave.
ℹ️ Repo Maintainers: To accept this application, review their application or assign @CelestinaBeing to this issue.
drips-wave commentedon Sep 25, 2026
Congratulations, @CelestinaBeing! 🎉 Your application was accepted by the repo's maintainers, and the issue is due on September 30, 2026.
🧑💻 @CelestinaBeing: Please resolve the issue such that the repo's maintainers have enough time to review your contribution before the due date. You'll earn Points for completing the issue on-time, which will make you eligible for a share of the Stellar Wave Program's reward pool.
Warning
When opening a PR, please link it to this issue to ensure it gets tracked accurately. Points are awarded when this issue is marked as completed by the maintainer.
🤠 Repo maintainers: Please keep an eye on the contributor's progress and review their work before the due date. You can manage this issue, including adjusting its complexity and points, here.
🌊 Happy Wave 🌊
drips-wave commentedon Sep 25, 2026
This issue has been completed by @CelestinaBeing as part of the Stellar Wave Program's 9th Wave 🥳
😎 @CelestinaBeing: You earned 200 Points for completing this issue! After the current Wave ends, you'll be eligible for a percentage of the Wave's reward pool based on the percentage of total points you've earned. Learn more here. You can also Leave a review to share your experience working on this issue.
🧑💻 Repo maintainers: How'd the contributor do? Leave a review to share your experience working with them.
perf(db): add missing indexes on live filter/join columns