This system solves the critical issue where RevenueCat creates multiple IDs/aliases for the same user, causing purchased credits to not appear correctly in the app. The solution uses a "Duplicate-and-Cleanup" approach that ensures users always see their purchased credits regardless of which ID RevenueCat uses.

RevenueCat's ID management creates complexity:

• iOS app generates a canonical user ID and sends it to RevenueCat
• RevenueCat sometimes treats this as the primary app_user_id, sometimes as an alias
• RevenueCat may create its own ID and make the canonical ID an alias
• Webhook events contain multiple potential IDs:
  • app_user_id: Could be canonical OR RevenueCat-generated
  • original_app_user_id: Another potential ID
  • aliases[]: Array of additional IDs

Critical Issue: Credit grants from webhooks may use non-canonical IDs, causing users to not see their purchased credits.

1. Canonical Tracking: Maintain a table of known canonical IDs from app requests
2. Duplicate Grants: Create credit grants for ALL aliases in webhook events
3. One-Time Cleanup: When seeing a new canonical ID, consolidate any existing grants
4. Simple Queries: Always query by canonical ID for balance checks

• Canonical IDs come ONLY from app requests (Authorization header)
• Webhook grants are duplicated across all provided aliases
• Cleanup happens ONCE per user, not on every request
• Transaction IDs ensure idempotency despite duplicates
• Storage is cheap; data correctness is critical

CREATE TABLE IF NOT EXISTS canonical_ids_v1 (
  canonical_id TEXT PRIMARY KEY,
  first_seen_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  aliases_processed BOOLEAN DEFAULT FALSE
);

CREATE TABLE IF NOT EXISTS credit_grants_v1 (
  -- ... existing columns ...
  is_consolidated BOOLEAN DEFAULT FALSE
);

Webhook Arrives
    ↓
Extract ALL IDs (app_user_id, original_app_user_id, aliases[])
    ↓
For Each ID:
    Create Grant (idempotent via transaction_id)
    ↓
Result: Multiple grants with same tx_id (safe)

User Authenticates (canonical_id in header)
    ↓
Is this a new canonical_id?
    ├─ No → Continue normally
    └─ Yes → 
        ├─ Register canonical_id
        ├─ Fetch aliases from RevenueCat
        ├─ Consolidate existing grants
        └─ Mark as processed

GET /api/credits/balance

• Returns current credit balance for authenticated user
• Requires customer ID in Authorization header
• Response:

{
  "customer_id": "canonical_123",
  "balance": 125000,
  "total_granted": 125000,
  "total_consumed": 0
}

GET /admin/aliases/status

• Monitor alias system health
• Requires admin authorization
• Response:

{
  "unconsolidated_grants": 42,
  "users_with_multiple_grants": 5,
  "unprocessed_canonical_ids": 2,
  "details": {
    "users_with_multiple_grants": [...],
    "unprocessed_canonical_ids": [...]
  }
}

GET /admin/credits/:customerId

• View detailed credit information for a specific user
• Requires admin authorization
• Response includes all grants and current balance

1. Webhook-First Purchase: User purchases before opening app

   • Webhook creates grants for all aliases
   • When user opens app, grants are consolidated to canonical ID

2. Multiple Aliases: RevenueCat provides various IDs

   • All aliases receive grants
   • Transaction ID prevents true duplicates

3. Overlapping Aliases: Same alias in multiple purchases

   • Transaction ID idempotency prevents double-granting

4. Refunds: Negative grants for refunded purchases

   • Applied to all aliases like regular grants
   • Consolidated the same way

Run the test suite to verify the implementation:

deno run --allow-net --allow-env test/test-aliases.ts

Tests cover:

• Webhook-first purchases
• Multiple purchases with overlapping aliases
• Refund processing
• Alias extraction from RevenueCat data
• Monitoring queries

1. Unconsolidated Grants: Should decrease over time
2. Users with Multiple Grants: Normal, especially for new users
3. Unprocessed Canonical IDs: Should be near zero after initial processing

-- Check for unconsolidated grants
SELECT COUNT(*) as unconsolidated_count 
FROM credit_grants_v1 
WHERE is_consolidated = FALSE;

-- Find users with multiple grant records
SELECT customer_id, COUNT(*) as grant_count 
FROM credit_grants_v1 
GROUP BY customer_id 
HAVING COUNT(*) > 5;

-- Check for unprocessed canonical IDs
SELECT canonical_id, first_seen_at 
FROM canonical_ids_v1 
WHERE aliases_processed = FALSE 
AND first_seen_at < datetime('now', '-1 hour');

If issues arise:

1. Revert auth middleware: Remove alias consolidation logic
2. Keep webhook changes: Duplicate grants are harmless
3. Manual cleanup: Can deduplicate grants later if needed

The system is designed to be fail-safe:

• Duplicate grants don't break functionality
• Transaction IDs prevent actual double-spending
• Consolidation is optional for system to work

• Check for unconsolidated grants older than 7 days
• Review users with excessive grant records

• Analyze alias patterns for optimization opportunities
• Review storage usage

• Consider archiving old consolidated records
• Performance optimization review

This aliasing system ensures that:

• Users always see their purchased credits
• The system handles all RevenueCat ID variations
• Performance remains optimal with one-time consolidation
• Data integrity is maintained through idempotency
• Monitoring and rollback options are available

The key insight: duplicate grants with the same transaction ID are harmless, and one-time consolidation when we first see a canonical ID keeps the system clean and performant.