Design: a projection writes only what the event proves — one application contract for the PM projector #4302

Design doc: files/designs/code-projector-application-contract.md in the keep-the-four-repos-healthy goal workspace. Audit at origin/main 23c2c514 — every defect below re-verified present at the cited lines this pass (2026-11-02).

Summary

The PM projector (pm-stack/projector/) is the fleet dashboard's source of truth for GitHub activity: webhook deliveries and a reconciliation poller both write pmp_items, pmp_comments, pmp_deliveries and pmp_push_events, and the aggregator reads the projection as fact. Seven filings in five days are one defect with seven addresses: the rules for applying an event to the projection were never stated, so each writer invents them — and each invention lets the projection assert something the source event never proved.

• Delivery identity is neither required nor earned — server.ts:90 defaults a missing X-GitHub-Delivery header to "", so every header-less delivery shares one identity and distinct events are dropped as "duplicate" (projector(webhook): a missing X-GitHub-Delivery header collapses every later delivery into the first one's identity — distinct events are silently dropped as 'duplicate' #2982); and deliveryApplied (store.ts:337-343) counts any recorded row, including the outcome:"error" row written when application fails, so a transient failure makes redelivery a permanent duplicate and the event — for pushes, which have no reconcile stream, absolutely — is lost (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).
• Comment identity is not namespaced — schema.sql:47 keys pmp_comments by the bare GitHub numeric ID while three object kinds with independent ID sequences (issue comments, review comments, reviews) write into it; a collision silently overwrites the earlier object, and a delete for one kind can tombstone another's row (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).
• The comments guard key is not the object's time — commentCommon (projector.ts:78-95) keys every webhook comment row on its creation time and markCommentDeleted (store.ts:247-252) never advances it, so a stale replay resurrects a deleted comment with its pre-edit body (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); and a review, whose payload has no created_at, gets a fabricated delivery-time "now" as its creation time and guard key, after which reconcile's correct submitted_at row is guard-refused forever (projector(reviews): webhook reviews get a fabricated created_at (delivery time, not submitted_at) — reconcile's correct row is then guard-refused forever #3087). Items already key on updatedAt — comments are the asymmetric case.
• Absence is read as a default — fromIssueOrPr (projector.ts:59-67) computes merged/draft/merged_at for every payload shape, but issue-shaped payloads carry none of those fields, so any later issues/issue_comment event for a merged PR (labelling one is routine) resets it to merged = 0, merged_at = NULL (projector(items): an issues/issue_comment event for a merged PR resets merged to 0 and clears merged_at — issue-shaped payloads carry no merge fields #4158).
• Deletion has no path for items — applyEvent's case "issues" (projector.ts:167-170) upserts for every action including deleted; there is no deleteItem, so a deleted issue is re-upserted by its own deletion event and stays in the read model forever (projector(items): an issues deleted event is upserted, not removed — deleted GitHub issues stay in pmp_items forever (no delete path exists, and reconcile only upserts) #4210). Comment deletions are handled in the same switch — items are the one missing case.

The contract

A projection writes only what the event proves. A delivery is deduplicated by a real identity and only once it has been applied. An object's identity is its kind plus its id. A row's ordering key is the source object's own last-update time — never its creation time, never the delivery time. A field the payload does not carry is unknown and writes nothing. Deletion is a state every projected kind can reach, and for comments it is absorbing: no upsert clears it.

Recorded decisions (full reasoning, alternatives and risks in the design doc):

1. A missing delivery id is rejected (400 missing_delivery_id, nothing recorded) — not minted locally, which would defeat dedupe on redelivery. GitHub always sends the GUID; replay tooling can set the header. (projector(webhook): a missing X-GitHub-Delivery header collapses every later delivery into the first one's identity — distinct events are silently dropped as 'duplicate' #2982)
2. Dedupe counts success and permanent failure only — applied/ignored outcomes, plus invalid_json error rows (that payload can never change); a transient apply-error row is audit only, redelivery re-applies, and recordDelivery upserts the row retaining the prior error in detail. (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)
3. Comment identity is kind:id, built by one commentKey(kind, numericId) helper at every writer and delete path; the store DB is documented disposable/rebuildable, so migration is a rebuild. (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)
4. The comments guard key is last-update time (updated_at ?? created_at; reviews: submitted_at for both created_at and the guard key — converging the webhook path onto reconcile's existing correct construction); the new Date() timestamp fallback is removed — a payload with none of its kind's timestamps writes no row. Webhook and reconcile share the row constructors after this phase. (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(reviews): webhook reviews get a fabricated created_at (delivery time, not submitted_at) — reconcile's correct row is then guard-refused forever #3087)
5. Comment deletion is absorbing — the conflict clause sets deleted = MAX(deleted, excluded.deleted). GitHub has no un-delete, which answers 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's open post-delete-write question: none exists. (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)
6. Item deletion removes the item, its links and its comments in one deleteItem(ref) — an issue deleted because its content should no longer be shown must not leave its comment bodies rendering under a missing item. (projector(items): an issues deleted event is upserted, not removed — deleted GitHub issues stay in pmp_items forever (no delete path exists, and reconcile only upserts) #4210)
7. Items' absence mechanism — PR-only fields (merged/draft/merged_at) are decided by key presence in the source object, null for absent, and upsertItem applies COALESCE(excluded.x, pmp_items.x) for those three columns only, so a PR-shaped payload's genuine merged = 0 still updates the row. (projector(items): an issues/issue_comment event for a merged PR resets merged to 0 and clears merged_at — issue-shaped payloads carry no merge fields #4158)

Phased breakdown

Phases adopt the seven origin issues as sub-issues (no parallels); Phase 4 is a new closing-audit issue, gated on Phases 1–3.

• Phase 1 — delivery identity & success-only dedupe (adopted: 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 missing X-GitHub-Delivery header collapses every later delivery into the first one's identity — distinct events are silently dropped as 'duplicate' #2982). Lands first: it stops loss at the door. Independent in code from Phases 2–3.
• Phase 2 — comment identity, guard key & absorbing delete (adopted: 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 + 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(reviews): webhook reviews get a fabricated created_at (delivery time, not submitted_at) — reconcile's correct row is then guard-refused forever #3087).
• Phase 3 — items: absence writes nothing, deletion removes (adopted: projector(items): an issues/issue_comment event for a merged PR resets merged to 0 and clears merged_at — issue-shaped payloads carry no merge fields #4158 + projector(items): an issues deleted event is upserted, not removed — deleted GitHub issues stay in pmp_items forever (no delete path exists, and reconcile only upserts) #4210).
• Phase 4 — closing writer inventory + contract statement (new issue, gated on Phases 1–3): the contract written into the schema.sql header / projector README; an inventory of every writer of the four tables with a per-writer disposition; required dispositions — transferred issues (projector(items): an issues deleted event is upserted, not removed — deleted GitHub issues stay in pmp_items forever (no delete path exists, and reconcile only upserts) #4210's explicit out-of-scope), the item row's own new Date() fallback (projector.ts:43), recordPush dedupe under Phase 1, secondary comment fields under the absence rule.

Open questions (in the design doc, not filed)

Transferred issues' source-row disposition; pmp_deliveries retention; push identity beyond the delivery id.

Related

Design doc (full problem/evidence/alternatives/risks): files/designs/code-projector-application-contract.md (goal workspace). Origins: #2456, #2982, #2694, #2455, #3087, #4158, #4210. Neighbours, not this design: #3068 (parsed-shape validation — input shape, not event application), #2747 (projector read-path decode), #2576 (aggregator document ingest).

Filed by the design architect (pass 37, 2026-11-02): design issue for the projector application contract. The seven origin issues are adopted as its phases — triage of each origin should record the phase structure and the decisions above rather than re-filing or re-scoping. Phase 4's closing audit is filed separately and gated on Phases 1–3.

Acceptance criteria

• Each of the seven cited defects is either fixed under the contract or explicitly dispositioned as not-a-defect with evidence: delivery identity required and earned (no shared empty-string identity), dedupe only after successful application, comment identity namespaced by object kind, comment guard keyed on the object's own update time, absence of a field never read as a default, and a deletion path for items.
• New or updated projector tests cover: header-less deliveries not collapsing into one identity, a failed application not counting as applied for redelivery, cross-kind comment ID collisions not overwriting, stale comment replays not resurrecting deleted comments, issue-shaped events not resetting a merged PR's merged/merged_at fields, and an issue deletion removing the item from the read model.
• The aggregator's read model shows no regression on the existing projector test suite.