Skip to main content

Data Model & Database Design

The authoritative schema is defined in packages/db/src/schema/index.ts and versioned through Drizzle migrations.

Core Principles​

  1. Wallet and audit domains are append-only where integrity matters.
  2. Tenant isolation is encoded in schema and reinforced in middleware.
  3. Deduplication is enforced by DB constraints, not only in memory.
  4. Historical explainability is preserved through versioned rules and linked reward events.

Domain Tables​

Identity and tenancy​

  • users
  • tenants
  • tenant_users

POS integration​

  • pos_connections
  • pos_webhook_events_raw

Transactions and rewards​

  • transactions
  • reward_rules
  • reward_events

Wallets and financial trail​

  • wallets
  • wallet_ledger
  • redemption_codes

Operator access​

  • tenant_operator_invites

Governance and reliability​

  • idempotency_keys
  • admin_audit_logs
  • merchant_campaigns
  • campaign_recipients
  • marketing_preferences
  • earn_multiplier_windows
  • promotion_bonus_configs
  • promotion_bonus_grants

High-Value Constraints​

Transaction dedup​

transactions has unique key:

  • (tenant_id, provider, provider_transaction_id)

This is a critical idempotency guard for webhook and poll replays.

Single wallet per user per tenant​

wallets has unique key:

  • (tenant_id, user_id)

Ledger invariants​

wallet_ledger includes checks:

  • entry_type IN ('credit', 'debit')
  • points > 0

Balance direction is represented by entry type, not signed values.

Migration Progression​

Current sequence:

  1. 0000_puzzling_proudstar.sql - baseline schema
  2. 0001_lovely_patch.sql - onboarding fields on tenants
  3. 0002_tired_cannonball.sql - Square poll watermark on pos connections
  4. 0003_redemption_operators.sql - redemption codes and store operators
  5. 0004_tenant_branding.sql - tenant branding storage
  6. 0005_pos_connections_tenant_pos_unique.sql - unique connection constraint
  7. 0006_merchant_campaigns.sql - campaigns and marketing preferences
  8. 0007_campaign_signature_banner.sql - campaign banner and signature fields
  9. 0008_clover_payments_watermark.sql - Clover poll watermark on pos connections
  10. 0009_brand_colors_program_name.sql - program name and brand colors
  11. 0010_promotion_bonuses.sql - earn multipliers, promotion bonuses, and consumer date of birth
  12. 0011_promotion_rls.sql - row level security on promotion tables

ER Snapshot​

Operational Notes​

  • users.id is mapped to Supabase user UUID and is not auto-generated by app logic.
  • OAuth tokens are stored encrypted in pos_connections.
  • Raw webhook payload storage supports debugging and replay-safe processing.
  • Campaign recipients are materialized before send so scheduled campaigns are deterministic.

Deep Dive​

See Data Model and Migrations for implementation-level detail.

Last updated on
Written byDhruv Doshi