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.