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

Governance and reliability

• idempotency_keys
• admin_audit_logs

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

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.

Deep Dive

See Data Model and Migrations for implementation-level detail.