Schema
Table groups and what each table models.
The full schema lives in convex/schema.ts (~2,200 lines). Better Auth owns users, sessions, accounts, verifications directly (not declared here). All userId fields are v.string() (Better Auth string IDs).
Table groups
| Group | Tables |
|---|---|
| Users/identity | userProfiles (role, Shopify link, prefs), avatarCatalog, adminRequests, accountLinks, shopifySyncJobs |
| Beauty | beautyProfiles, productBeautyTags, routineStepConfigs, skinScanSessions |
| Shopify mirror | shopifyProducts, shopifyVariants, shopifyCollections, collectionProducts |
| Gamification | stampCampaigns, campaignSpecialPositions, userStampProgress, stampHistory, purchaseOrderProcessing, shakeOpportunities (vestigial) |
| Loyalty misc | gamificationStatsDaily, loyaltyCarousels |
| Vouchers | voucherTemplates, vouchers |
| Cart/checkout | userCarts, cartItems, checkoutSessions |
| Orders | orders, orderLineItems |
| Campaigns | duitRayaCampaigns, duitRayaClaimCodes, duitRayaShopifyPool, duitRayaGenerationJobs |
| Content | carouselItems, marketingCampaigns |
| Notifications | notifications, notificationTemplates, pushTokens, notificationPreferences |
| Engagement | productComments, commentVotes, userReports, userBlocks, userProductLists, listProducts, productInteractions |
| Webhooks | webhookEvents, webhookPayloads, webhookSubscriptions |
| Dashboard | productOverrides (+localizations), productGroups, productGroupItems, dashboardSettings, exportReports |
| Misc | contactMessages, r2UploadSessions, auditLogs, userAddresses |
The eight aggregates (silent-corruption surface)
convex.config.ts mounts eight @convex-dev/aggregate instances. Six are defined in convex/aggregates.ts, and the two claim-code aggregates in convex/duitRaya/aggregate.ts.
| Aggregate (export) | Table | Namespace / sort key / sum |
|---|---|---|
usersByRole | userProfiles | role / createdAt / 1 |
orderTotals | orders | revenue or excluded (from financialStatus) / processedAt / totalAmount in cents |
ordersByChannel | orders | revenue|<channel> or excluded|<channel> / processedAt / totalAmount in cents |
productRatings | productComments | productId / createdAt / rating |
campaignStamps | stampHistory | campaignId / createdAt / stampsEarned |
voucherCounts | vouchers | <status>|<templateId> / createdAt / count only |
duitRayaCodeStats (codeStats) | duitRayaClaimCodes | campaignId / claimedAt ?? 0 / count only |
duitRayaCodesByDenom (codesByDenom) | duitRayaClaimCodes | campaign, prefix and denomination / claimedAt ?? 0 / count only |
Every write to vouchers goes through gamificationLoyalty/voucherCounts.ts, and every claim-code write goes through claimCodeAggregates in duitRaya/aggregate.ts, which keeps both claim-code aggregates in step. The points and tier aggregates (pointsTransactionTotals, userTierCounts) were unmounted with points on 2026-10-16.
Clear, backfill, rebuild and probe functions for each aggregate live in convex/migrations/backfill.ts (see Runbooks). clearAllAggregates in convex/migrations/resetAnalytics.ts clears all eight, and the rest of that file deletes customer data.
Other denormalized invariants
finalizeOrderRewardswritesorders.stampsAwarded,stampShortfallCentsand the finalstampOutcomeafter counting the order'sstampHistoryrows. The webhook, the dashboard link and status edits, the 30-day claim and the reprocess mutation set only the pre-run outcomes (no_app_user,awaiting_payment,needs_review). Never patch these fields by hand. Rerun the order with Reprocess stamps orstartOrderRewardswithforce.orders.buyerEmail,buyerPhoneandshippingPhonehold normalized buyer keys (lowercase email, E.164 phone) that the 30-day claim queries by index. Write them throughorderBuyerKeys, never raw.activeVouchers:<userId>sharded counter ↔ count ofvouchersrows withstatus: "active"— every status transition must move the counter; repairrepairActiveVouchersCounter.unread:<userId>sharded counter ↔ unread in-app notifications — condition shared viacountsTowardUnread.