Data model

The Prisma schema (apps/service/prisma/schema.prisma, ~1,970 lines) holds 52 models across eight domains. This page groups them and calls out the constraints and design decisions that change how you have to write code against them.

Migrations are hand-managed from a baseline. Use prisma migrate dev — never db push, which desyncs the history.

Platform and tenancy

Tenant also carries per-workspace counters and settings: ticketSeq and the globally-unique ticketPrefix that render native ticket numbers, contactSeq for generated contact labels, and defaultCountry (ISO alpha-2) which lets a bare national number like 0711… parse to a country and a canonical E.164. That falls back to INBOX_DEFAULT_PHONE_REGION, then KE.

Connections

SocialConnection.ownerUserId records the Accounts user who connected the account. Credentials are encrypted at rest (AES-256-GCM) and always read from the binding, never shared between integrations.

Threads and entries

The two uniqueness keys are the deduplication story: a provider redelivering the same webhook upserts onto the same thread and the same entry rather than creating duplicates. It is why ingest can be retried freely.

seq is the ordering, not createdAt

InboxEntry.seq is a bigint assigned by a Postgres sequence, and it is the authoritative order.

createdAt is millisecond-precision, so two entries written in the same millisecond tie, and ordering then falls to the cuid id — which is arbitrary, not chronological. A message and a fast auto-reply could render in either order, and differently on each read. This was found while building the AI channel, where a generated answer routinely lands in the same millisecond as the message it answers. Sort by seq.

InboxThread.firstUnansweredInboundAt opens a response turn (set-if-null on ingest, cleared by any non-campaign outbound); InboxEntry.responseTimeSeconds is measured from it, on the first outbound answering an inbound only. Every SLA and response-time report derives from that column.

Tickets and annotations

Two invariants live in raw SQL

Prisma cannot express a partial unique index, so two constraints are hand-written in migration SQL and are invisible if you only read schema.prisma:

CREATE UNIQUE INDEX "inbox_ticket_one_open_per_thread"
  ON "inbox_ticket" ("threadId")
  WHERE "status" = 'open';

CREATE UNIQUE INDEX IF NOT EXISTS "inbox_ticket_native_number_unique"
  ON "inbox_ticket" ("ticketNumber")
  WHERE "ticketNumber" IS NOT NULL AND "legacyTicketId" IS NULL AND "ticketNumber" ~ '[A-Za-z]';

The first enforces one open ticket per thread at the database, which is what makes concurrent webhook-driven opens safe — the loser of the race gets a constraint violation rather than a second open ticket. The second scopes ticket-number uniqueness to natively issued numbers, so imported tickets can keep their original (differently formatted) numbers without colliding.

Because ticketSeq is bumped atomically and a raced open wastes its number, native ticket numbers are monotonic but not gapless.

Contacts and consent

Contacts are not tenant-scoped

This is the surprise in the schema. InboxContact has no tenantId. Identity is global: the @@unique([kind, value]) on the identifier means one phone number is one person, estate-wide. That constraint is what makes cross-workspace merges work at all.

It is also why junk values must be filtered before an import. A blocklist miss collapses thousands of unrelated people onto one contact row, irreversibly.

Everything that is a fact about a person in one workspace lives on InboxContactTenant instead: source, engagementScore, engagementRating, firstSeenAt, lastSeenAt, and marketing consent. Unsubscribing from one workspace must not unsubscribe you from another, and an engagement score earned in one says nothing about the other.

Consent is layered. InboxContactTenant.unsubscribedAt is the hard, all-channels opt-out for that workspace and is checked first. A per-channel opt-out is one row per (contact, tenant, channel) in InboxContactChannelConsent, so adding a channel is a data change, not a schema migration. The legacy emailUnsubscribedAt column is readable only until its backfill lifts it into a channel='email' consent row — new code must not write it.

Phone values are stored normalised (E.164) and emails lowercased, so matching is exact rather than fuzzy.

Segments, templates and campaigns

Segment membership is materialised as explicit rows rather than stored as a query. A rule-based segment can be layered on later without changing how anything reads it.

Flows

@@unique([flowId, dedupeKey]) refuses a duplicate enrolment at the database rather than in application logic, which is the only place that holds under concurrency.

Publishing

Reviews, warm-up, files, webhooks, import

Two conventions that apply everywhere

Audit tables have no foreign keys, deliberately. InboxContactMerge stores targetContactId and sourceContactId as plain strings; TenantMerge stores plain tenant ids plus the ws_ external refs captured at merge time. A cascading foreign key would erase exactly the history these tables exist to preserve — the audit has to outlive the row it describes, including a later hard delete. Dangling references here are the design, not a bug.

Every *UserId is an Accounts id. ownerUserId, sentByUserId, actorUserId, assignedUserId and the rest are opaque Accounts user identifiers with no local table behind them, because Inbox stores no users. They will never join; resolve them against Accounts. A null actorUserId on a merge row means a machine key with no user context performed it — itself worth recording.

Related

Architecture · Tenancy · Glossary