Data model

xuly.io is built on Postgres. All business tables live in the publicschema with Row-Level Security scoping every row to an org_id. The core entity graph:

organizations ─┬─ memberships ─── auth.users
               │
               ├─ integrations ─┬─ sync_runs
               │                └─ stats_daily   (unique: integration_id + date + campaign + source + sub_id)
               │
               ├─ campaigns  ─┐
               ├─ sources    ─┼─ deals (ties commission terms to integration × source)
               ├─ sub_affiliates ─── invoices
               ├─ automation_rules ─── alerts
               └─ audit_log

Core tables

organizations

The tenant boundary. Created automatically at user signup. Fields include plan (free / pro / business / enterprise), base_currency, and white_label (JSONB with agency branding overrides).

memberships

Composite PK on (user_id, org_id). Each row holds a role — one of owner, admin, analyst, viewer, finance. RLS helpers is_org_member() and has_org_role() read from this.

brands

Global catalog (221 rows). Not tenant-scoped; readable by any authenticated user. Fields: adapter_key (maps to the adapter code), auth_type, status(active / beta / deprecated / broken), rate_limit.

integrations

Per-org connection to one brand. Credentials live encrypted in vault.secrets (pgsodium) and the integration row references them via vault_secret_id. Plaintext is only ever decrypted inside the sync worker via the get_integration_credentials() RPC. Status lifecycle: pending → active (after first success) → error (on failure) or paused.

stats_daily

The fact table. One row per (integration, date, campaign, source, sub_id)tuple. Facts: clicks,signups, ftd_count,deposits_amount, ggr,ngr, commission. Both original and base-currency values are stored.

sync_runs

Audit trail for every sync invocation. Status is running / success / partial / failed, with duration, row count, and error message. The Trigger.dev run ID is persisted for drill-down.

audit_log

Every mutation of a significant entity (integrations, invoices, team) is logged here with actor, IP, user agent, and metadata. Append-only — no soft-delete, no updates.

Indexes that matter

• stats_daily (org_id, date desc) — drives every dashboard query
• stats_daily (org_id, brand_id, date desc) — "top brands" aggregation
• integrations (next_sync_at) WHERE status='active' — how the scheduled fan-out finds due syncs
• unique on stats_daily grain — enables idempotent UPSERT on every sync