Data Model and Migrations

The authoritative schema lives in packages/db/src/schema/index.ts and is migrated through the SQL files in packages/db/drizzle.

Migration Progression

Current migrations in this repo are:

1. 0000_puzzling_proudstar.sql - baseline schema
2. 0001_lovely_patch.sql - tenant onboarding fields
3. 0002_tired_cannonball.sql - Square polling watermark
4. 0003_redemption_operators.sql - redemption and store-operator support
5. 0004_tenant_branding.sql - tenant branding storage path
6. 0005_pos_connections_tenant_pos_unique.sql - one connection per tenant/provider
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 polling watermark
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

Current Schema Domains

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 redemption

• wallets
• wallet_ledger
• redemption_codes

Merchant operations

• tenant_operator_invites
• merchant_campaigns
• campaign_recipients
• marketing_preferences
• earn_multiplier_windows
• promotion_bonus_configs
• promotion_bonus_grants

Governance and reliability

• idempotency_keys
• admin_audit_logs

High-Value Constraints

Transaction deduplication

transactions is protected by the unique key on (tenant_id, provider, provider_transaction_id).

Single wallet per user per tenant

wallets is protected by the unique key on (tenant_id, user_id).

Append-only ledger semantics

wallet_ledger enforces:

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

Single POS connection per tenant/provider

pos_connections is protected by the unique key on (tenant_id, pos_type).

Campaign recipient materialization

campaign_recipients stores send state per recipient so scheduled campaigns can be resumed and audited.

Schema Facts Worth Remembering

• users.id is the Supabase auth UUID.
• Monetary values are stored in minor units.
• OAuth tokens are encrypted at rest in pos_connections.
• Raw webhook payloads are persisted for replay and recovery.
• Campaign and marketing data are tenant-scoped.

ORM Client

packages/db/src/client.ts exposes the Drizzle client used by the API.

Runtime uses DATABASE_URL. The API expects a pooled connection string; migrations can use a direct database URL where required.

Data Integrity Conventions

1. Tenant ID appears on all tenant-scoped aggregate tables.
2. Mutable snapshots are paired with immutable ledger/event records.
3. Deduplication is enforced by the database, not by memory.
4. Audit logs are additive.
5. Campaign recipient rows are materialized before send so the sweep job is deterministic.