orb(dashboard): the 8-week slop/duplicate trend can only ever cover ~4 days of history

[JSONbored]

⚠️ 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/services/maintainer-slop-duplicate-trend.ts:10-11:

/** Max queue-health snapshots read per repo when shaping the maintainer trend card — two per week of history. */
export const SLOP_DUPLICATE_TREND_SNAPSHOT_LIMIT = SLOP_DUPLICATE_TREND_WEEKS * 2;   // = 16

The "two per week" premise is false. queue-health snapshots are written once per repo per generate-signal-snapshots run (src/queue/signal-snapshot.ts:188-196), which is enqueued in the six-hourly full-sync window (src/index.ts:126 isFullSyncWindow = isHourly && hour % 6 === 0; :351) — 4 snapshots/day, ~28/week. listRecentSignalSnapshotsForTargets (src/db/repositories.ts:6054-6085) returns the newest perTargetLimit rows per target via row_number() OVER (PARTITION BY target_key ORDER BY generated_at DESC), with no time predicate at all and perTargetLimit hard-clamped to 100 (:6062).

So the call site at src/api/routes.ts:1878-1882 retrieves ~4 days of points for an 8-week card. buildMaintainerSlopDuplicateTrend finds points only in the newest bucket or two; buckets 0–6 aggregate to openPullRequests === 0, ratePct returns null, and the card permanently renders 7 empty weeks. Raising the constant cannot fix it: 28 x 8 = 224 > the 100-row cap.

The data is deliberately there. src/db/retention.ts:29 keeps signal_snapshots for 90 days, and LATEST_ONLY_SIGNAL_SNAPSHOT_TYPES explicitly excludes queue-health, with the reason stated at :353-357: "NOT the whole table, which intentionally keeps bounded multi-row history for the one signal type genuinely read as a trend/change series (queue-health)." The retention layer preserves multi-row queue-health history precisely so this card can be built, and the reader throws it away with a row-count guess.

Requirements

• Add an optional sinceIso?: string option to listRecentSignalSnapshotsForTargets (src/db/repositories.ts:6054), applied as an additional AND generated_at >= ? inside the windowed subquery so the row-number ranking is computed over the time-bounded set, not the whole table.
• Keep maxPerTarget as a hard safety cap; the time bound is the primary constraint and the row cap is the backstop.
• src/api/routes.ts:1878-1882 passes sinceIso computed as SLOP_DUPLICATE_TREND_WEEKS weeks before the same generatedAt the builder already receives (:1876), so the read window and the render window are derived from one instant.
• Replace SLOP_DUPLICATE_TREND_SNAPSHOT_LIMIT's definition and its stale "two per week of history" doc comment with a value and comment that describe a safety cap, not a cadence assumption. It must be at least SLOP_DUPLICATE_TREND_WEEKS * 28 clamped to the function's own 100-row ceiling, and the comment must state that the read is time-bounded and this is only the per-repo row ceiling.
• No change to buildMaintainerSlopDuplicateTrend's pure bucketing logic, to MIN_OPEN_PRS_FOR_RATE, or to the snapshot write cadence.

⚠️ Required pattern: listStrandedPendingNotificationDeliveries (src/notifications/stranded-delivery-sweep.ts:47-52) is the house shape for "time-bounded scan plus a row cap" — a sinceIso/lookback pair with a separate SCAN_LIMIT. It does NOT satisfy this issue to only raise SLOP_DUPLICATE_TREND_SNAPSHOT_LIMIT (the 100-row clamp at src/db/repositories.ts:6062 makes 8 weeks unreachable); to remove or raise that 100-row clamp instead of adding the time bound; or to change the generate-signal-snapshots cadence.

Deliverables

• listRecentSignalSnapshotsForTargets accepts sinceIso and applies it inside the windowed subquery — asserted by a unit test seeding rows on both sides of the boundary and checking only the in-window rows return.
• With sinceIso omitted, the function's behaviour is byte-identical to today — asserted by a test.
• src/api/routes.ts passes an 8-week sinceIso derived from the request's generatedAt.
• Named regression test: 40 daily queue-health snapshots spanning 8 weeks for one repo produce a MaintainerSlopDuplicateTrend with a non-null slopFlagRatePct in at least 6 distinct weekly buckets (previously at most 1–2).
• SLOP_DUPLICATE_TREND_SNAPSHOT_LIMIT's doc comment no longer claims "two per week" and the constant is at least SLOP_DUPLICATE_TREND_WEEKS * 28 (or the 100 ceiling) — asserted by an invariant test on the constant.

All Deliverables above are required in a single PR. A PR that satisfies only some of them — for example adding sinceIso to the repository function without passing it from src/api/routes.ts — does not resolve this issue.

Test Coverage Requirements

99%+ Codecov patch coverage, branch-counted, on src/**. src/db/repositories.ts, src/api/routes.ts and src/services/maintainer-slop-duplicate-trend.ts are inside coverage.include. Both arms of the new optional-sinceIso conditional need a test (supplied and omitted), and the multi-week regression test is required.

Expected Outcome

The maintainer dashboard's slop/duplicate trend card renders real 8-week history instead of six or seven permanently blank buckets, using the multi-row queue-health history the retention layer already preserves for exactly this purpose.

Links & Resources

src/services/maintainer-slop-duplicate-trend.ts:9-15, :51; src/db/repositories.ts:6054-6085; src/api/routes.ts:1876-1893; src/queue/signal-snapshot.ts:188-196; src/index.ts:126, :351; src/db/retention.ts:29, :353-358.

[linear-code]

JSO-6762