projector(comments): pmp_comments is keyed by the bare GitHub numeric ID — an issue comment, a review comment and a review with the same ID silently overwrite each other

Problem

pmp_comments is keyed by the bare GitHub numeric ID alone, but the projector writes three different kinds of GitHub objects into that one table — issue comments, PR review comments, and PR reviews — and GitHub assigns those IDs from independent sequences per object type. The same number can therefore denote an issue comment and a review on the same (or different) items, and when it does, the second object silently replaces the first (main b3f6d2b):

pm-stack/projector/schema.sql:47 — id TEXT PRIMARY KEY, -- GitHub comment/review id, no kind or repo qualifier.
pm-stack/projector/src/store.ts:207 — upsertComment is ON CONFLICT(id) DO UPDATE SET over every column including ref, kind, body and deleted (guarded only by source_updated_at >=, which a later-created colliding object passes).
Every writer passes the raw per-type ID: webhook commentCommon (src/projector.ts:80, String(c.id)), the delete paths (src/projector.ts:159 and :213, markCommentDeleted(String(comment.id)) — a delete for one kind can tombstone another kind's row), and reconciliation (src/reconcile.ts:76 issue/review comments, :116 reviews, both String(...id)).

The unified activity stream (schema view pmp_v_subject_activity, getCommentsForSubject) then shows the surviving object and has lost the other one — no error anywhere, and reconciliation re-derives the same collision on every poll.

Evidence (verified this run)

Probe on the laptop (~/git/fleet/probe-2026-10-30-laneB-projector/probe.ts, real store.upsertComment/getComments against an in-memory libSQL DB migrated from the real schema.sql, main b3f6d2b): upsert an issue_comment with id 424242 (created 2026-01-01), then upsert a review with the same id 424242 (created 2026-01-02):

collision: rows=1 kinds=review bodies=review body
pmp_comments total rows = 1

The issue-comment row is gone — overwritten in place by the review, including its ref and kind. (The fixtures hint the namespaces were kept apart by hand: fixtures/03-issue-comment.json uses id 9001, 05-pr-review.json 10001, 06-pr-review-comment.json 11001.)

Why it matters

The projector's whole purpose is a trustworthy unified comment/activity projection across the four repos (aggregator requirement #2). A cross-kind ID collision silently drops a real comment or review from every subject view and can cross-wire a deletion onto the wrong object. Because the monotonic guard compares source_updated_at, the corruption sticks: re-delivering the overwritten object's webhook is a no-op duplicate or loses the guard comparison, so the lost row does not heal itself.

Acceptance criteria

Comment identity distinguishes the object kind (e.g. a composite key or a kind-qualified ID) at the schema level and in upsertComment / markCommentDeleted, so an issue comment, a review comment, and a review with the same numeric ID coexist as three rows.
A delete event for one kind never tombstones a row of another kind.
Store/projector tests pin the same-ID-different-kind case for all three kinds, plus the cross-kind delete case.
Existing rows and the read model (getComments, getCommentsForSubject, pmp_v_subject_activity) keep working; the migration path for an existing database is stated in the PR (the DB is documented as disposable/rebuildable in schema.sql, if that is the chosen route).

Distinct from #2455 (deleted comments resurrected by stale upserts — the monotonic guard's key, not the primary key's namespace) and #2456 (failed webhook deliveries unretryable — the deliveries table, not comments).

🤖 Agent note · filed

Agent: new-issue-creator-code-b
Session / ID: new-issue-creator-code-b cron run 2026-10-30 05:50 CDT
Found via: filed this run
Task: Filed the projector pmp_comments ID-namespace collision: schema.sql:47 keys the table by the bare GitHub numeric ID while webhook (projector.ts:80) and reconcile (reconcile.ts:76,116) write issue comments, review comments and reviews — three independent GitHub ID sequences — into it, so a same-ID object of another kind silently overwrites the row (verified on the laptop with the real store against an in-memory libSQL DB, main b3f6d2b: rows=1, kind flipped to review, issue-comment body gone).
Expectation: effort xs–s; done when comment identity is kind-qualified, cross-kind deletes cannot tombstone the wrong row, and tests pin the same-ID-different-kind cases.
Links: projector(comments): a deleted comment is resurrected by any stale upsert — the comments monotonic guard keys on created_at and markCommentDeleted never advances it #2455 projector(comments): a deleted comment is resurrected by any stale upsert — the comments monotonic guard keys on created_at and markCommentDeleted never advances it #2455 (stale-upsert resurrection — guard key, distinct) · projector(webhook): a delivery that fails to apply can never be retried — its error row makes every redelivery a 'duplicate', and push events are lost permanently #2456 projector(webhook): a delivery that fails to apply can never be retried — its error row makes every redelivery a 'duplicate', and push events are lost permanently #2456 (deliveries table, distinct)

🤖 Agent note · picked up

Agent: queue worker (repo-worker-code), lane M shift
Session / ID: queue task 7962 (triage)
Found via: queue claim · coverage pass 2026-11-03
Task: Triage projector(comments): pmp_comments is keyed by the bare GitHub numeric ID — an issue comment, a review comment and a review with the same ID silently overwrite each other #2694 at current main: confirm the pmp_comments bare-ID collision is still live, or close with evidence if already handled.
Expectation: effort/xs — done = verdict with fresh-main evidence + fix path named (no duplicate follow-up if one already exists)
Links: issue projector(comments): pmp_comments is keyed by the bare GitHub numeric ID — an issue comment, a review comment and a review with the same ID silently overwrite each other #2694 · PR fix(projector): scope comment identity by kind #3132 (existing fix)

🤖 Agent note · done (triage verdict: CONFIRMED, fix already in flight)

Agent: queue worker (repo-worker-code), lane M shift
Session / ID: queue task 7962 (triage, coverage pass 2026-11-03)
Found via: queue claim · coverage join
Task: Re-verify projector(comments): pmp_comments is keyed by the bare GitHub numeric ID — an issue comment, a review comment and a review with the same ID silently overwrite each other #2694 at current main and route the fix without duplicating existing work

Verdict: CONFIRMED still live on current main 77f911fbfc5 (checked fresh 2026-11-03).

pm-stack/projector/schema.sql:47 on main still declares id TEXT PRIMARY KEY for pmp_comments with kind as a plain column — the bare-ID keying this issue files is unchanged on main.

Fix path already exists — no new follow-up enqueued (would duplicate):

This issue was already triaged by queue task 4085 (2026-10-31, verdict CONFIRMED with a behavioural probe), which routed follow-up fix queue task 4413.
Task 4413's fix is open as PR fix(projector): scope comment identity by kind #3132 "fix(projector): scope comment identity by kind" (branch queue/4413-2694-comment-identity): comment identity becomes (id, kind), webhook deletions are scoped to their kind, legacy schemas are rejected before startup, and the PR reports bun test 48 passed including same-ID cross-kind collisions. Codex review on PR fix(projector): scope comment identity by kind #3132 completed with no findings; the only check shown is CodeRabbit (NEUTRAL, review-limit notice).
Remaining step is PR fix(projector): scope comment identity by kind #3132's own review/merge under task 4413, including the PR's stated one-way-door database procedure (stop writers, back up the whole DB — especially pmp_links — drop only pmp_v_subject_activity + pmp_comments, restart, reconcile). This triage does not merge another task's PR.

Routing: none new — task 4413 / PR #3132 own the fix. This coverage triage (task 7962) completes as triaged.
(Note: the user-token gh comment path hit the secondary rate limit this run; this note was posted via the developing.today GitHub App per the standing fallback order.)

🤖 Agent note · picked up + triage verdict

Agent: repo-worker-code (lane R, code-r)
Session / ID: cron repo-queue-worker-shift-r · queue task 12073
Found via: atomic claim from repo-health-code-pilot (triage-doctrine re-queue, queue: no reconciliation between open issues and queue coverage — non-creator filings never get queue items #7016)

Triage verdict — task 12073 (lane R, 2026-11-07): REAL AND CURRENT — pmp_comments still keyed by bare GitHub ID on main; fix PR #3132 still open

Fresh verification (the claimed/in-progress labels on this issue were stale leftovers from the 2026-11-03 triage — no live holder; this claim is the live one):

Issue projector(comments): pmp_comments is keyed by the bare GitHub numeric ID — an issue comment, a review comment and a review with the same ID silently overwrite each other #2694 is OPEN, no hard-stop labels.
Bug confirmed on current origin/main: pm-stack/projector/schema.sql:56-57 — pmp_comments declares id TEXT PRIMARY KEY -- GitHub comment/review id with kind as a plain column; pm-stack/projector/src/store.ts upsertComment does INSERT ... ON CONFLICT(id) DO UPDATE, replacing every field. An issue comment, a review comment, and a review that share a numeric GitHub ID still silently overwrite each other, exactly as filed.
The fix exists: PR fix(projector): scope comment identity by kind #3132 "fix(projector): scope comment identity by kind" (head 4422a7974721, touching projector schema/store/server/projector + tests) — still OPEN, untouched since 2026-10-31. The done-task-only audit (queue: no reconciliation between open issues and queue coverage — non-creator filings never get queue items #7016) is right: nothing for this issue has landed.

Routing: enqueued task 12648 (pr_shepherd, dedupe key repo-health:v1:pr:developing-today/code:pr:3132) scoped to: rebase/revalidate PR #3132 (including the migration story for existing rows if the key changes), projector tests including a cross-kind same-ID collision test, Codex disposition, merge, and close this issue with the evidence. No competing implementation.

🤖 Agent note · done

Agent: repo-worker-code (lane R, code-r)
Session / ID: queue task 12073 completed (triaged)
Links: PR fix(projector): scope comment identity by kind #3132 · follow-up task 12648

Navigation Menu

Repository navigation

projector(comments): pmp_comments is keyed by the bare GitHub numeric ID — an issue comment, a review comment and a review with the same ID silently overwrite each other #2694

Problem

Evidence (verified this run)

Why it matters

Acceptance criteria

21 remaining items

Triage verdict — task 12073 (lane R, 2026-11-07): REAL AND CURRENT — pmp_comments still keyed by bare GitHub ID on main; fix PR #3132 still open

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

projector(comments): pmp_comments is keyed by the bare GitHub numeric ID — an issue comment, a review comment and a review with the same ID silently overwrite each other #2694

Description

Problem

Evidence (verified this run)

Why it matters

Acceptance criteria

Activity

dezren39 commented on Oct 30, 2026

21 remaining items

developing-today commented on Nov 3, 2026

developing-today commented on Nov 3, 2026

developing-today commented on Nov 8, 2026

Triage verdict — task 12073 (lane R, 2026-11-07): REAL AND CURRENT — pmp_comments still keyed by bare GitHub ID on main; fix PR #3132 still open

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions