Appearance
Communications data model
Five of the nine tables on this page are BUILT; four are DESIGNED, NOT BUILT.20260901120000_v2_communications_whatsapp.sql created whatsapp_business_accounts, whatsapp_phone_numbers, whatsapp_message_templates, communication_messages and communication_message_events, service-role only, and 20260901140000_v2_whatsapp_registry_seed.sql seeded the platform's WABA, its number and three APPROVED qrsetu_otp templates. That migration names the other four as deliberately absent: communication_consents, communication_campaigns, whatsapp_inbound_messages and communication_usage_daily. This page was written as Phase-0 documentation before the code, per the repo's own D10 discipline; where a column below differs from the migration (for example whatsapp_message_templates.status also allows IN_APPEAL, and whatsapp_phone_numbers.status also allows disconnected), the migration is the contract.
Naming, decided up front
The feature scope is communication for channel-agnostic tables and whatsapp for Meta-specific ones — per the feature-scoped naming rule (QRS-436). whatsapp_message_templates is deliberately verbose: templates already collided twice in this repo (Setu Card manifests vs Supabase Auth email templates), and a third bare use would repeat the exact incident the naming gate encodes. Columns stay short (template_key, category); the table carries the disambiguation.
The split that shapes everything: envelope vs channel
One channel-agnostic envelope (communication_messages) records every outbound message QRSETU ever sends, on any channel. Channel-specific registries (whatsapp_*) record the WhatsApp account plumbing. This is the same seam discipline as @qrsetu/observability's pluggable sink: the platform reasons about messages; only the dispatcher knows how a WhatsApp send differs from an email send. Adding a channel later (email via ZeptoMail already exists conceptually in ADR-0008, push, SMS if ever forced) adds a dispatcher arm, never a second envelope.
WhatsApp account plumbing
whatsapp_business_accounts
One row per WABA. Today that is exactly one (QRSETU's own, under the "QR Setu" Meta app); the table exists so that the future per-tenant model (an enterprise customer's own WABA onboarded via Embedded Signup) is a new row, never a schema change — the same "consumer identity in the first schema" posture that made category 3 a client-side build.
| column | type | why |
|---|---|---|
id | uuid PK | internal ref |
waba_id | text unique | Meta's WABA id — the join key to every Graph API call |
meta_business_id | text | the owning Business portfolio |
owner_workspace_id | uuid null FK → workspaces | null = platform-owned (the only value in R1); set = a tenant's own WABA (future) |
display_name | text | operator label |
status | text | active / restricted / disabled — mirrored from account_update webhooks, never guessed |
whatsapp_phone_numbers
One row per registered sender number. Quality and messaging-limit tier are mirrored from Meta (webhooks + a reconciling read), because both change without us acting and both gate what the dispatcher may send.
| column | type | why |
|---|---|---|
id | uuid PK | |
waba_id | text FK → whatsapp_business_accounts.waba_id | |
phone_number_id | text unique | Meta's id — what the send API actually addresses |
display_number | text | +91… for humans |
quality_rating | text | GREEN / YELLOW / RED, from phone_number_quality_update |
messaging_limit_tier | text | current business-initiated tier, from the same webhook family |
status | text | connected / flagged / restricted |
whatsapp_message_templates
The template registry — QRSETU's mirror of what Meta holds, plus the platform's own metadata. Meta is the system of record for approval status (only Meta can approve); this table is the system of record for usage (which QRSETU flows depend on which template — the dependency tracking the Admin Hub needs before anyone edits or deletes one).
| column | type | why |
|---|---|---|
id | uuid PK | |
waba_id | text FK | templates live under a WABA |
template_name | text | Meta's key (with language) — there is no template versioning in the Meta API; a breaking change is a NEW NAME, exactly the Setu Card manifest discipline (immutable versions, supersede never edit) applied because the provider forces it |
language | text | e.g. en, hi, mr — one row per translation, same lifecycle each |
category | text | MARKETING / UTILITY / AUTHENTICATION — Meta may recategorize; the mirrored value is truth, not our intent |
meta_template_id | text | Graph API id |
status | text | PENDING / APPROVED / REJECTED / PAUSED / DISABLED — from message_template_status_update |
rejection_reason | text null | verbatim from Meta |
components | jsonb | header/body/footer/buttons as submitted — the render contract |
parameter_names | text[] | the named variables, so a send with a missing variable fails at OUR boundary with a real error, not at Meta's with a template error |
used_by | text[] | flow keys (order.placed.buyer, payment.captured.buyer, …) that depend on this template — dependency tracking as data |
Immutability note: an APPROVED template's components are edit-limited at Meta and every edit re-enters review. The registry therefore treats components as replace-on-sync, never hand-edited — the sync (below) is the only writer.
The channel-agnostic core
communication_messages
One row per outbound message attempt-set (retries reuse the row; each attempt is an event). This is the ledger; every count the Admin Hub or a merchant report ever shows is a fold over it — never a separately maintained counter (QRS-249's duplicate-source class).
| column | type | why |
|---|---|---|
id | uuid PK | |
channel | text | whatsapp now; email / push are future arms of the same envelope |
category | text | transactional / marketing / authentication — OUR axis, checked against the template's Meta category at enqueue (a marketing send through a utility template is a policy violation waiting for enforcement) |
recipient_phone | text | E.164, normalized once at the boundary (the place-public-order normalizer already exists for Indian mobiles) |
recipient_user_id | uuid null FK → users | null = anonymous buyer — nullable by the same rule as orders.buyer_user_id: a high-volume table cannot gain an identity column cheaply later |
workspace_id | uuid null FK → workspaces | the merchant this message is about (null for platform-level messages such as signup) — this is what makes per-merchant usage reporting a GROUP BY, not a redesign |
template_id | uuid null FK → whatsapp_message_templates | null only for free-form session messages |
variables | jsonb | the rendered parameter values (PII-lean: ids and short strings, never full payloads) |
correlation_type / correlation_id | text / uuid | order / payment / campaign / reminder / account — the trace from a business event to the message it caused |
campaign_id | uuid null FK → communication_campaigns | set for campaign fan-out rows |
idempotency_key | text unique | REQUIRED, supplied by the enqueuing flow (e.g. order-placed:{order_id}:buyer) — a retried enqueue is a no-op by constraint, the same mechanism place-public-order already relies on |
provider_message_id | text unique null | the WhatsApp wamid, written on accepted send — the join key for every status webhook |
status | text | queued → sent → delivered → read, or failed / suppressed — derived from the event ledger by rank, never set directly from a webhook, because WhatsApp statuses arrive out of order exactly as Razorpay's do (read must not be undone by a late delivered; the monotonic-rank rule from the payments model, reused) |
suppressed_reason | text null | no_consent / quality_protection / quiet_hours / limit — a message the dispatcher REFUSED to send is recorded, not dropped, or the Admin Hub cannot answer "why did nobody get the campaign" |
error_code / error_detail | text null | Meta's error verbatim on terminal failure |
attempt_count / next_attempt_at | int / timestamptz | dispatcher retry state |
communication_message_events (append-only)
Verbatim provider events, exactly the payment_events pattern — recorded before processed, so a processing bug is replayable rather than a permanent loss.
| column | type | why |
|---|---|---|
id | uuid PK | |
message_id | uuid null FK → communication_messages | null when correlation fails — a dead-lettered event is still evidence |
provider | text | meta_whatsapp |
provider_event_id | text | unique with provider — the idempotency key; Meta redelivers, and redelivery must hit a constraint, not a code path |
event_type | text | sent / delivered / read / failed / template_status / quality / account |
payload | jsonb | verbatim |
received_at / processed_at | timestamptz | the lag between them is the webhook-health metric |
communication_consents
Keyed by phone, not user, because the largest recipient population is anonymous buyers (category 3, anonymous-first). A signed-in user's consent references the same row via the phone.
| column | type | why |
|---|---|---|
id | uuid PK | |
phone | text | E.164, unique with channel + category |
channel | text | whatsapp |
category | text | marketing (transactional consent is the interaction itself — placing an order with a phone number is the opt-in for messages about that order; marketing requires its own explicit grant, per both Meta policy and DPDP) |
granted | boolean | current state |
source | text | order_checkout / card_form / inbound_stop / admin — an opt-out via WhatsApp reply ("STOP") is a consent write with source inbound_stop |
granted_at / revoked_at | timestamptz | the audit pair |
The dispatcher's rule is absolute: no marketing message leaves without a granted=true row. Fail closed — the same posture as promo_slot.
communication_campaigns
⚠ Not ADR-0025's campaigns. ADR-0025 campaigns are offers rendered on Setu Cards; these are outbound message batches. They will often be two halves of one merchant action ("publish the offer AND announce it"), which is a workflow that references both — never a merged table. Conflating them is the promo_slot vs offer mistake with new nouns.
| column | type | why |
|---|---|---|
id | uuid PK | |
workspace_id | uuid null FK | null = platform campaign (QRSETU announcing to merchants); set = a merchant's campaign (future, entitlement-gated) |
template_id | uuid FK | must be APPROVED at schedule time AND at send time — approval can be lost in between (paused templates), so the dispatcher re-checks |
audience | jsonb | a declarative selection (consented marketing contacts × filters), resolved at execution into message rows |
scheduled_for | timestamptz | |
status | text | draft → scheduled → running → completed / cancelled / failed |
counts | — | not stored; delivered/read/failed are folds over communication_messages by campaign_id |
whatsapp_inbound_messages
Every user reply, kept for three reasons: the 24-hour customer-service window is computed from the latest inbound per phone (free-form replies are only legal inside it); STOP/opt-out handling must be evidenced; and the future bridge into the platform's existing chat domain (@qrsetu/domain chat, one module for both halves) needs the raw material. Columns: phone_number_id, from_phone, wamid unique, message_type, body jsonb verbatim, received_at.
communication_usage_daily
A rollup, not a source: day × channel × category × workspace, message counts by terminal status, plus meta_reported_cost_minor when the Meta analytics read supplies it. Exists so cost reporting does not scan the ledger, and so the Admin Hub can show "our count vs Meta's count" — the reconciliation seam. Same detect-only philosophy as the payments reconciler: a mismatch is an exception to investigate, never something a job "fixes".
RLS posture
Service-role only, permanently, for every table above — the same stance as public.outbox and for the same reason: these tables carry phone numbers and message content. There is noTO authenticated policy on any of them, which matters more here than anywhere: authenticated includes every consumer (three-categories rule). Merchant-visible reporting arrives later as scoped RPCs (get_my_communication_summary) projecting only that workspace's aggregates, never row grants. Platform-admin access requires the is_admin() identity that QRS-803 records as not yet existing — the Admin Hub read path is blocked on that decision, and this page inherits the block rather than inventing a second admin identity.
What the first migration should NOT include
- No per-tenant WABA columns beyond
owner_workspace_id— Embedded Signup onboarding state is future scope; one nullable column keeps the door open. - No stored campaign counters — folds only.
- No message body storage for free-form sends beyond what
variablesneeds — PII minimization; the template registry holds the copy. - No
communication_channelstable — a channel is an enum until a second channel actually ships through this envelope; a registry of one row is speculative abstraction.