Skip to main content

​
Data Model

The detailed typed schema lives in the Supabase migrations and packages/database/types.ts. This page summarizes the parts of the model that matter for product and operational reasoning.

​
Design rules

  • Supabase Postgres is the system of record.
  • Webhooks are stored as first-class operational records.
  • Conversations are derived from durable message history.
  • Outbox jobs are explicit rows, not hidden queue internals.
  • Workspace scoping and RLS are mandatory on every table.

​
Core entity groups

​
Workspace and access

  • workspaces
  • workspace_members
  • api_keys
These tables scope all business data and enforce operator permissions. api_keys are workspace-scoped and used for public REST API v1 authentication. All key operations filter by the caller’s resolved workspace_id — keys from other workspaces are never visible or operable.

​
Channel and contact plane

  • channels
  • contacts
  • tags
  • contact_tag_links
These tables define the WhatsApp number registry, normalized contact identities, subscriber state, and segmentation primitives.

​
Inbox and message ledger

  • conversations
  • messages
  • message_events
The platform treats message state as an append-only ledger. Conversation rows are convenience projections over that ledger, not the sole source of truth.

​
Templates, campaigns, and journeys

  • templates
  • campaigns
  • campaign_recipients
  • journeys
These tables model outbound programs, provider template state, and automation definitions.

​
AI and Margaret layer

  • llm_provider_configs — per-workspace LLM provider configuration (OpenAI, Anthropic, OpenRouter, Ollama, vLLM). References secrets stored in Supabase Vault.
  • workspace_secret_bindings — maps a workspace to a named Vault secret (e.g., an LLM API key or Meta access token). Secrets never appear in plain-text columns.
  • ai_agents — defines an AI agent persona, model selection, tool permissions, and PII pseudonymization settings per workspace.
  • ai_threads — a thread of AI conversation context associated with a contact/conversation pair.
  • ai_thread_messages — individual messages within an AI thread (user, assistant, tool roles).
  • ai_agent_executions — audit log of tool-use runs during agent execution (BORD-140–143). Each execution records the agent, tools invoked, input/output, and outcome.

​
Webhook and operational plane

  • webhook_events
  • outbox_jobs
  • audit_logs
This is the core operational plane:
  • webhook_events stores inbound provider and partner deliveries
  • outbox_jobs stores asynchronous follow-up work including message sends
  • audit_logs stores operator and system actions (member changes, GDPR erasures, secret rotations, write operations that need review)

​
Webhook-first design

Webhook records carry:
  • source and topic
  • verification result
  • dedupe keys
  • processing status
  • attempt counts
  • payload summaries
That gives the platform replay, forensics, and incident recovery without depending on external provider consoles.

​
Realtime posture

The schema enables Supabase Realtime on operational tables such as:
  • conversations
  • messages
  • webhook_events
  • outbox_jobs
  • campaigns
The intended use is private, workspace-scoped operator updates, not unrestricted fan-out. Broadcast topics are used rather than Postgres Changes to avoid scaling bottlenecks.

​
Current implementation note

The repo preserves route and UI contracts with a typed in-memory implementation in @repo/database when WHATSAPP_PLATFORM_DATA_MODE=mock. That is a delivery choice, not a different schema. The canonical schema lives in the Supabase migrations and generated types.