Background
Several service methods loop over query results and issue one additional
query/write per iteration instead of a bulk operation:
- BountiesService.expireOverdue: fetches all overdue bounties via a
query builder, then loops and awaits this.bountyRepo.save(bounty) once
per row, sequentially — one UPDATE per row.
- TeamsService.create: Promise.all over dto.members.map saving each split
— N inserts issued concurrently but as N round trips, not a single bulk
insert.
- BountiesService.markMergedAndRelease: for team payouts, Promise.all over
team.splits.map with an await this.userRepo.findOne(...) inside each
callback — one SELECT per team member instead of a single WHERE id IN
(...) query.
- ReputationService/AnalyticsService (inspect these — not fully reviewed
here) likely compute aggregate stats (earnings, merge rate, review time,
language breakdown) by loading full row sets into memory and reducing in
JS rather than using SQL aggregation, given the pattern established
elsewhere in the codebase.
Problem Statement
None of these are wrong today at toy data volumes, but MergeFi's whole
premise is many sponsors, many repos, many contributors, many bounties. At
even moderate scale (thousands of open bounties, hundreds of team members
across active bounties), expireOverdue run as a cron-ish job will issue
thousands of sequential round-trip UPDATEs (slow, and holds whatever
transaction/connection pool resources for the whole loop), and per-recipient
findOne calls during payout will add real, avoidable latency to the
already-slow escrow release critical path.
Requirements
- Convert expireOverdue to a single bulk UPDATE ... WHERE id IN (...)
(or a single UPDATE with the same WHERE clause used to find the
candidates, executed in one round trip) while preserving the return count.
- Convert the team-split recipient lookup in markMergedAndRelease to a
single WHERE id IN (...) query, then map results back to splits in
memory.
- Audit src/reputation/reputation.service.ts and
src/analytics/analytics.service.ts (read them as part of this issue)
for any full-table-scan-into-memory aggregate computations and convert the
worst offenders to SQL-level aggregation (SUM, COUNT, AVG, grouped
queries) instead of loading rows and reducing in JS.
- Add a lightweight benchmark/test (can be a script under test/ or a
documented manual benchmark in the PR) demonstrating query-count reduction
before/after for at least expireOverdue and the team-split lookup,
using TypeORM's query logging or a query-counting test double.
Acceptance Criteria
Technical Notes
Files: src/bounties/bounties.service.ts, src/teams/teams.service.ts,
src/reputation/reputation.service.ts, src/analytics/analytics.service.ts.
Difficulty Justification
Requires reading and correctly refactoring several services' query patterns
without changing observable behavior, plus building a real way to prove the
query-count reduction (most contributors would just "look faster" without
proving it) — genuine performance-engineering discipline, not a one-line
optimization.
Background
Several service methods loop over query results and issue one additional
query/write per iteration instead of a bulk operation:
query builder, then loops and awaits this.bountyRepo.save(bounty) once
per row, sequentially — one UPDATE per row.
— N inserts issued concurrently but as N round trips, not a single bulk
insert.
team.splits.map with an await this.userRepo.findOne(...) inside each
callback — one SELECT per team member instead of a single WHERE id IN
(...) query.
here) likely compute aggregate stats (earnings, merge rate, review time,
language breakdown) by loading full row sets into memory and reducing in
JS rather than using SQL aggregation, given the pattern established
elsewhere in the codebase.
Problem Statement
None of these are wrong today at toy data volumes, but MergeFi's whole
premise is many sponsors, many repos, many contributors, many bounties. At
even moderate scale (thousands of open bounties, hundreds of team members
across active bounties), expireOverdue run as a cron-ish job will issue
thousands of sequential round-trip UPDATEs (slow, and holds whatever
transaction/connection pool resources for the whole loop), and per-recipient
findOne calls during payout will add real, avoidable latency to the
already-slow escrow release critical path.
Requirements
(or a single UPDATE with the same WHERE clause used to find the
candidates, executed in one round trip) while preserving the return count.
single WHERE id IN (...) query, then map results back to splits in
memory.
src/analytics/analytics.service.ts (read them as part of this issue)
for any full-table-scan-into-memory aggregate computations and convert the
worst offenders to SQL-level aggregation (SUM, COUNT, AVG, grouped
queries) instead of loading rows and reducing in JS.
documented manual benchmark in the PR) demonstrating query-count reduction
before/after for at least expireOverdue and the team-split lookup,
using TypeORM's query logging or a query-counting test double.
Acceptance Criteria
overdue bounties (verified by a test asserting query count via a spy
on the repository/query runner).
queries.
SQL-side computation with a before/after query-count or row-count-loaded
comparison documented in the PR.
Technical Notes
Files: src/bounties/bounties.service.ts, src/teams/teams.service.ts,
src/reputation/reputation.service.ts, src/analytics/analytics.service.ts.
Difficulty Justification
Requires reading and correctly refactoring several services' query patterns
without changing observable behavior, plus building a real way to prove the
query-count reduction (most contributors would just "look faster" without
proving it) — genuine performance-engineering discipline, not a one-line
optimization.