⚠️ Definition of Done: this issue must be completed in full, in a single PR. Do not split this
work across multiple PRs, and do not defer any Deliverable below to a follow-up issue. A PR that
satisfies only some of the Deliverables, stubs a required test, or leaves a checkbox
partially-done does NOT resolve this issue and will be closed.
Context
src/db/repositories.ts:5089-5110's query at line 5097 has no tiebreaker. Every other "pick the most recent row" query in repositories.ts explicitly breaks ties on rowid/id (e.g. getLatestAdvisoryForPullRequest at line 5025: ORDER BY updated_at DESC, rowid DESC, with a comment explaining a rapid synchronize burst can tie on the millisecond-precision text timestamp; listSignalSnapshots at line 5463 has the identical fix with an even more detailed comment citing an adversarial-id regression). getLatestPublishedAiReview selects the most-recently-published ai_review_cache row across all of a PR's historical head SHAs, but is missing the same tiebreak -- two head SHAs published within the same millisecond return a nondeterministic result.
Requirements
Change line 5097 to ORDER BY published_at DESC, rowid DESC LIMIT 1, matching the sibling functions' tiebreak pattern exactly.
Deliverables
All of the above Deliverables are required in the same PR unless the deliverable text itself states otherwise.
Test Coverage Requirements
src/** -- 99%+ patch coverage, branch-counted, including the new tiebreak test.
Expected Outcome
getLatestPublishedAiReview returns a deterministic result even when two head SHAs were published within the same millisecond.
Links & Resources
src/db/repositories.ts:5025,5089-5110,5463
test/unit/ai-review-cache.test.ts
Context
src/db/repositories.ts:5089-5110's query at line 5097 has no tiebreaker. Every other "pick the most recent row" query inrepositories.tsexplicitly breaks ties onrowid/id(e.g.getLatestAdvisoryForPullRequestat line 5025:ORDER BY updated_at DESC, rowid DESC, with a comment explaining a rapidsynchronizeburst can tie on the millisecond-precision text timestamp;listSignalSnapshotsat line 5463 has the identical fix with an even more detailed comment citing an adversarial-id regression).getLatestPublishedAiReviewselects the most-recently-publishedai_review_cacherow across all of a PR's historical head SHAs, but is missing the same tiebreak -- two head SHAs published within the same millisecond return a nondeterministic result.Requirements
Change line 5097 to
ORDER BY published_at DESC, rowid DESC LIMIT 1, matching the sibling functions' tiebreak pattern exactly.Deliverables
getLatestPublishedAiReview's query includesrowid DESCas a tiebreakertest/unit/ai-review-cache.test.tswith two rows sharing an identicalpublished_at, asserting a stable, deterministic resultAll of the above Deliverables are required in the same PR unless the deliverable text itself states otherwise.
Test Coverage Requirements
src/**-- 99%+ patch coverage, branch-counted, including the new tiebreak test.Expected Outcome
getLatestPublishedAiReviewreturns a deterministic result even when two head SHAs were published within the same millisecond.Links & Resources
src/db/repositories.ts:5025,5089-5110,5463test/unit/ai-review-cache.test.ts