Data Model and Migrations

SalesArck data definitions live in packages/db/src/schema/index.ts and are migrated via Drizzle SQL files under packages/db/drizzle.

Migration Sequence

0000_puzzling_proudstar.sql

Initial schema bootstrap:

• users, tenants, tenant_users
• pos_connections, pos_webhook_events_raw
• transactions
• wallets, wallet_ledger
• reward_rules, reward_events
• idempotency_keys
• admin_audit_logs
• constraints and indexes

0001_lovely_patch.sql

Merchant onboarding fields added to tenants:

• pos_provider_preference
• onboarding_profile (JSONB)
• updated_at

0002_tired_cannonball.sql

Square polling watermark added:

• pos_connections.square_payments_watermark_at

Table Reference

Users and Identity

Column	Type	Constraints	Description
id	uuid	PK	Supabase user UUID (not auto-generated)
email	text	unique, nullable	User email address
mobile	text	unique, nullable	User phone number
role	text	not null	consumer, client, pos_operator, admin
status	text	not null	active, pending_approval, suspended
created_at	timestamp	default now()
updated_at	timestamp

Tenants

Column	Type	Constraints	Description
id	uuid	PK, default gen	Tenant identifier
name	text	not null	Business name
country	text	not null, default 'US'	ISO 3166-1 alpha-2
status	text	not null, default 'active'
pos_provider_preference	text	nullable	square, clover, other
onboarding_profile	jsonb	nullable	Business setup fields
created_at	timestamp	default now()
updated_at	timestamp	nullable

Tenant Users

Column	Type	Constraints	Description
id	uuid	PK
user_id	uuid	FK -> users, not null
tenant_id	uuid	FK -> tenants, not null
role	text	not null	client, pos_operator
(user_id, tenant_id)		unique	Prevents duplicate membership

POS Connections

Column	Type	Constraints	Description
id	uuid	PK
tenant_id	uuid	FK -> tenants, not null
pos_type	text	not null	square, clover
access_token_enc	text	not null	AES-256-GCM encrypted
refresh_token_enc	text	not null	AES-256-GCM encrypted (empty for Clover)
merchant_account_id	text	not null	Provider's merchant identifier
status	text	not null	active, disconnected, token_expired, error
token_expires_at	timestamp	nullable
last_synced_at	timestamp	nullable
square_payments_watermark_at	timestamp	nullable	High-water mark for polling
created_at	timestamp	default now()

POS Webhook Events Raw

Column	Type	Constraints	Description
id	uuid	PK
tenant_id	uuid	FK -> tenants, not null
provider	text	not null	square, clover
event_type	text	not null
raw_payload	jsonb	not null	Unmodified webhook body
signature_header	text	nullable	For replay verification
processed	boolean	default false	Processing marker
created_at	timestamp	default now()

Transactions

Column	Type	Constraints	Description
id	uuid	PK
tenant_id	uuid	FK -> tenants, not null
user_id	uuid	FK -> users, nullable	Null for anonymous transactions
provider	text	not null	square, clover
provider_transaction_id	text	not null
amount_minor_units	integer	not null	Cents
currency	text	not null, default 'USD'
customer_ref	jsonb	nullable	{ email?, mobile?, externalCustomerId? }
line_items	jsonb	nullable
transaction_at	timestamp	not null
created_at	timestamp	default now()
(tenant_id, provider, provider_transaction_id)		unique	Dedup guard

Wallets

Column	Type	Constraints	Description
id	uuid	PK
tenant_id	uuid	FK -> tenants, not null
user_id	uuid	FK -> users, not null
points_balance	integer	not null, default 0	Fast-read snapshot (truth is ledger)
status	text	not null, default 'active'	active, frozen
created_at	timestamp	default now()
updated_at	timestamp
(tenant_id, user_id)		unique	One wallet per tenant+user

Wallet Ledger

Append-only. No UPDATE or DELETE in production.

Column	Type	Constraints	Description
id	uuid	PK
wallet_id	uuid	FK -> wallets, not null
entry_type	text	not null, CHECK in (credit, debit)
points	integer	not null, CHECK > 0	Always positive; direction from entry_type
description	text	nullable
source_transaction_id	uuid	FK -> transactions, nullable
created_by_admin	uuid	FK -> users, nullable	For admin adjustments
created_at	timestamp	default now()

Reward Rules

Column	Type	Constraints	Description
id	uuid	PK
tenant_id	uuid	FK -> tenants, not null
version	integer	not null	Auto-incremented per tenant
is_active	boolean	default true	Only one active per tenant
rule_config	jsonb	not null	RuleConfig schema
effective_from	timestamp	not null
created_at	timestamp	default now()

rule_config JSONB schema:

{
  "conversionRate": 1.0,
  "minSpendMinorUnits": 0,
  "maxPointsPerTransaction": 500,
  "minBalanceToRedeem": 100,
  "maxRedemptionPercentage": 100,
  "roundingPolicy": "floor"
}

Reward Events

Column	Type	Constraints	Description
id	uuid	PK
wallet_id	uuid	FK -> wallets, not null
transaction_id	uuid	FK -> transactions, nullable
rule_version_id	uuid	FK -> reward_rules, nullable
points_earned	integer	default 0
points_redeemed	integer	default 0
created_at	timestamp	default now()

Idempotency Keys

Column	Type	Constraints	Description
key	text	PK	{provider}:{merchantId}:{providerTxnId}
tenant_id	uuid	FK -> tenants, nullable
handler	text	not null	E.g. square_webhook, clover_webhook
result_snapshot	jsonb	nullable
expires_at	timestamp	not null	Default: 30 days from creation
created_at	timestamp	default now()

Admin Audit Logs

Immutable. No UPDATE or DELETE.

Column	Type	Constraints	Description
id	uuid	PK
actor_user_id	uuid	FK -> users, not null	Admin who performed action
action	text	not null	E.g. user.approve, wallet.freeze
target_entity_id	text	nullable
target_entity_type	text	nullable
before_state	jsonb	nullable	State before action
after_state	jsonb	nullable	State after action
reason	text	nullable
created_at	timestamp	default now()

Indexing and Query Behavior

Indexes are aligned to operational queries:

• tenant_users_tenant_idx — membership lookups
• Transaction timeline and tenant/user filters
• processed marker on pos_webhook_events_raw — unprocessed event queries
• Active reward rule lookup per tenant
• Wallet ledger time-series reads
• Audit actor/target/time queries

ORM Client

packages/db/src/client.ts uses pg.Pool + Drizzle with:

• Max connections: 10
• Idle timeout: 20s
• Connection timeout: 15s

Runtime uses DATABASE_URL (typically Supabase Transaction Pooler). Migrations should use direct DB URL where required by platform.

Data Integrity Conventions

1. Tenant ID appears on all tenant-scoped aggregate tables.
2. Mutable snapshots are paired with immutable ledger/event records.
3. Dedup is enforced at DB layer, not only application memory.
4. Audit logs are additive, never update-in-place.
5. Amounts stored in minor units (cents) to avoid floating-point errors.
6. JSONB columns used for flexible extensibility (onboardingProfile, ruleConfig, customerRef).