kit: fold webhook dedup into webhookEvents and drop webhookIdempotencyKeys (staged migration)

[hyochan]

Tracking the DEFERRED(schema-cleanup) note in packages/kit/convex/webhooks/internal.ts (~line 137).

The webhookIdempotencyKeys table duplicates the dedup guarantee that the webhookEvents.by_project_and_notification_id index already provides, roughly doubling per-webhook write amplification. Removing it needs a staged migration across 2-3 coordinated Convex deploys: (1) decide whether the events index key needs source added first - the current dedup key is (projectId, source, sourceNotificationId) while the index alone lacks source, so an Apple/Google notification-id collision within one project could mis-dedup; (2) switch dedup reads to a direct webhookEvents lookup and stop writing webhookIdempotencyKeys; (3) let the table drain past WEBHOOK_RETENTION_MS - legacy rows (pre-projectId, missing eventId) must age out, and the fallback paths at internal.ts:162-223 still handle them today; (4) drop the table from schema.ts plus the hourly prune cron (crons.ts) and the project/account-deletion cascades.

Context: identified during the 2026-07-21 repo-wide health patrol.

[hyochan]

Revalidated against current main after #243. This remains a valid IAPKit refactor, but two details in the issue description need to be corrected before implementation.

Corrections

1. webhookEvents.by_project_and_notification_id does not enforce uniqueness. Convex indexes are ordered lookup structures (with _creationTime as a tie-breaker), and .unique() only asserts that a query returned at most one row. Dedup safety must continue to come from a source-aware read-before-insert inside the serializable recordWebhookEvent mutation. See Convex indexes and OCC/atomicity.
2. Do not remove the hourly pruneWebhookEvents cron. It enforces the documented 30-day retention contract for webhookEvents as well as pruning idempotency keys. The final phase should remove only the key-table work from that mutation and keep the event-retention schedule.

The optimization is still worthwhile: every newly accepted Apple/Google notification currently inserts both an event row and a key row, and the key row maintains four indexes. This doubles the dedup-layer documents, although it does not literally double every write performed by the full webhook pipeline.

Safe three-deploy rollout

1. Add a separate source-aware event index on (projectId, source, sourceNotificationId) (stage/backfill it first if production volume warrants it). Keep the existing two-field index because the public SSE Last-Event-ID cursor currently has no source input.
2. Switch recordWebhookEvent and the Google preflight query to the new event lookup, mapping the short Apple/Google source to the event source enum. Observe production behavior, then stop key-table reads/writes while retaining the table, pruner, and deletion cascades for rollback and drain.
3. After at least WEBHOOK_RETENTION_MS and an explicit zero-row check, remove webhookIdempotencyKeys, its legacy fallbacks/indexes, and key-specific cascade/pruner code. Keep pruneWebhookEvents as an event-only retention mutation and keep its cron.

Regression coverage should include same-key redelivery, Apple/Google ID collisions within one project, cross-project reuse, Google preflight behavior, Horizon events, legacy linked/orphan rows, retention batches, and project/account deletion.

There is also a related SSE-contract decision: source-aware dedup fixes ingestion, but two sources sharing the same public notification ID can still make the current source-less cursor lookup ambiguous. That should be explicitly accepted or included in this migration.

[hyochan]

Phase 1 of this staged migration is now complete.

• refactor(kit): prefer webhook events for dedup #245 added the source-aware (projectId, source, sourceNotificationId) event index and switched Apple/Google ingestion, Google preflight, and Horizon dedup to event-first lookup.
• The production IAPKit deployment completed successfully on 2026-07-23: https://github.com/hyodotdev/openiap/actions/runs/29974957195
• webhookIdempotencyKeys fallback reads and writes are intentionally still active, and new Apple/Google events still create key rows. The 30-day drain clock has therefore not started.

This issue remains open for the remaining stages. Phase 2 should stop key-table reads/writes only after production observation, while retaining the table, pruner, and project/account deletion cascades for rollback and drain. Before that change, we should add direct coverage for linked legacy-key reuse and linked/orphan retention pruning, define the production observation/zero-row check, and explicitly decide the source-less SSE Last-Event-ID ambiguity.

Phase 3 may remove the key table and key-specific cleanup only after the Phase 2 deployment time plus at least 30 days, a subsequent hourly prune pass, and an explicit production zero-row check. The event-retention cron itself must remain.

[hyochan]

Status update after #254:

• Phase 1 remains complete via refactor(kit): prefer webhook events for dedup #245: inbound Apple, Google, and Horizon dedup prefers the source-aware webhookEvents lookup.
• feat(kit): add scoped publishable and secret keys #254 removed the outbound IAPKit-to-SDK SSE surface completely. The former source-less Last-Event-ID cursor ambiguity is therefore no longer part of this migration and does not block the next phase.
• webhookIdempotencyKeys is still read as a rollback/legacy fallback and is still written for newly accepted Apple and Google webhook events. Its schema, indexes, retention cleanup, and project/account deletion cascades also remain. The retention drain clock has not started.

The remaining rollout is:

1. Ship Phase 2 in a separate deployment: stop webhookIdempotencyKeys fallback reads and new writes while retaining the table and cleanup paths for rollback and drain.
2. Record the Phase 2 production deployment time and observe inbound webhook dedup behavior.
3. Wait at least WEBHOOK_RETENTION_MS (30 days), allow the hourly retention job to run, and explicitly confirm that production has zero remaining key rows.
4. Ship Phase 3: remove the table, its indexes, and key-specific pruning/deletion-cascade code. Keep the webhookEvents retention mutation and hourly cron.

This issue should remain open until the Phase 3 deployment and production zero-row verification are complete.

[hyochan]

Status update after #292:

• The write-stop portion of the staged migration is now live. fix: harden IAPKit sync, verification, and MCP sessions #292 stopped creating new webhookIdempotencyKeys rows and kept the source-aware webhookEvents lookup authoritative.
• The production Kit deployment completed successfully on 2026-08-07 at 01:25 UTC: https://github.com/hyodotdev/openiap/actions/runs/31137544408
• The 30-day retention drain clock therefore started from that deployment boundary. Using the conservative job-completion time, final cleanup becomes eligible no earlier than 2026-09-06 at 01:25 UTC.
• Legacy fallback reads, the table and its indexes, key-specific pruning, and project/account deletion cascades remain intentionally present during the drain window. No new key rows are being written.

The remaining completion gate is:

1. Continue observing production inbound webhook deduplication after the write stop.
2. After the retention cutoff, allow the hourly pruner to drain every eligible batch and explicitly confirm that production contains zero webhookIdempotencyKeys rows.
3. Remove the legacy fallback reads, table/index definitions, and key-specific pruning/deletion-cascade code. Keep the webhookEvents retention mutation and hourly cron.
4. Deploy and verify the final cleanup, then close this issue.

This supersedes the July 26 status: the drain clock is now running, but this issue should remain open until the final deployment and production zero-row verification are complete.

[hyochan]

Status check against main at 42f35513. Phases 1–2 have landed; the issue body still describes the pre-work state, and the anchor it names is gone.

The tracked note moved. This issue says it tracks a DEFERRED(schema-cleanup) comment at packages/kit/convex/webhooks/internal.ts:~137. That string no longer exists anywhere under packages/kit/convex. It was replaced by a comment naming this issue directly, at internal.ts:60-66:

Phases 1-2 of issue #241 make webhookEvents the authoritative dedup record. No new idempotency rows are written; the reads below remain only for rows still in the table, and go away with it in phase 4.

Where each phase stands

Phase	State	Evidence
1 · decide whether the events dedup key needs source	done	findWebhookEventByDedupKey takes projectId + storedSourceForDedupSource(source) + sourceNotificationId (internal.ts:83)
2 · read from webhookEvents, stop writing keys	done	0 insert("webhookIdempotencyKeys" sites in non-test code
3 · let legacy rows drain	in progress	8 read sites remain across internal.ts and projects/helpers.ts; WEBHOOK_RETENTION_MS is 30 days (internal.ts:13)
4 · drop the table, cron and cascades	not started	schema.ts:711 still defines it; deletes remain at internal.ts:481,500 and helpers.ts:241,260

What phase 4 still has to remove

• the table in schema.ts:711
• the retention prune path in internal.ts (:481, :500)
• the project and account deletion cascades in projects/helpers.ts (:241, :260), including the widen-phase legacy branch at :253-260
• the legacy-row fallback in lookupExistingEvent and recordWebhookEvent

One thing worth deciding before phase 4. lookupExistingEvent deliberately does not check legacy rows (projectId == null), and its comment says such a row "can still slip a duplicate Play API call through" while recordWebhookEvent retains the fallback and dedups the event row itself. That is a documented, bounded gap for as long as legacy rows exist — worth confirming they have aged past the 30-day window before the fallback is deleted, rather than deleting it and finding out.

No code changed for this comment; it only records verified state so the body is not read as current.