AceTrader Docs

Appearance

PromoCodeStorageManager ​

SQLite storage and query layer for promo codes, snapshots, and usage records.

Purpose ​

PromoCodeStorageManager is the data access layer for the PromoCodeRegistry Durable Object. It encapsulates all SQLite read and write operations for promo codes, including CRUD operations, snapshot creation, usage recording, and paginated listing with filters.

The manager supports a unified model of campaign codes (admin-created marketing promotions) and referral codes (user-generated, either system or custom). It uses a snapshot system to preserve the promo code's state at the time of redemption, ensuring renewals apply the original discount terms even if the code is later modified.

All code lookups are case-insensitive via normalizeCode(). Referral codes are generated from wallet addresses using the ACE-{FIRST5}-{LAST3} format with collision handling by appending -1, -2, etc.

High-Level Design ​

Role: Pure Data Access Layer ​

PromoCodeStorageManager owns all SQLite operations across three tables (promo_codes, promo_code_snapshots, promo_code_usages). It contains no business logic or validation — that responsibility belongs to PromoCodeValidationManager, which calls into this manager for all data access.

Unified Code Model ​

Campaign codes and referral codes share a single promo_codes table, distinguished by:

  • referralWalletUserId = null → Campaign code (admin-created)
  • referralWalletUserId != null → Referral code (user-generated)
    • isSystemGenerated = true → Auto-generated ACE-XXXXX-XXX format
    • isSystemGenerated = false → Custom user-chosen code

Runtime-Calculated Status ​

Code status (active, scheduled, expired, disabled, exhausted) is not stored — it's computed at query time via calculatePromoCodeStatus() from isActive, validFrom, validTo, and currentUsageCount. This means status-based filtering requires fetching all rows and filtering in memory, while non-status queries use efficient SQL LIMIT/OFFSET.

Snapshot-Based Discount Preservation ​

When a user redeems a code, recordUsage() atomically (via transactionSync()) captures a snapshot of the code's discount terms at that moment. Subsequent subscription renewals reference the snapshot — not the live code — so admin edits to a code don't retroactively change existing users' discounts.

Custom ↔ System Referral Code Sync ​

Each user can have one system-generated and one custom referral code. When a custom code's discountValue is updated, the system-generated code is automatically synced to match. This ensures referees get the same discount regardless of which code variant they use.

Referral Code Generation & Collision Handling ​

Codes are derived from wallet addresses: ACE-{first5}-{last3}. On collision, the manager counts existing codes with the same prefix and appends a numeric suffix (-1, -2, etc.).

Edge Cases & Error Handling ​

  • Sort column injection prevented via VALID_SORT_COLUMNS whitelist; throws on invalid sortBy
  • Status filtering requires full table scan (status is runtime-calculated, not stored)
  • Custom referral code discount updates automatically sync to the user's system-generated code
  • Hard delete only allowed for scheduled (future validFrom) codes; active codes must be deactivated
  • recordUsage uses transactionSync() for atomic snapshot + usage count increment + usage insert
  • incrementRenewalCycle is idempotent per charge date via last_discounted_renewal_at < ? guard
  • Referral code collision handled by counting existing codes with the same prefix and appending a numeric suffix
  • All user IDs normalized to lowercase for consistent lookups

See Also ​