#111 feat(inbox): data model + migration

[Anonymous]

Originally created by: Waqasahmedwaseer

v1.1 Inbox — Commit 1 of 6: data model + migration. Schema only, no services/UI yet.

Design: PR [#110] (docs/designs/inbox.md). Implements §3 of the design.

What's in it

• 3 net-new tables — contacts, conversations, messages (+ 3 enums)
• Contact: whatsappName (auto from pushName) + savedName (operator-set), unique (workspace, jid)
• Conversation: per (workspace, session, contact), status/unreadCount/tags/sessionDeletedAt, list index (workspace, status, last_message_at DESC)
• Message: idempotent on (workspace, wa_message_id), thread index (conversation, wa_timestamp DESC)
• Workspace gets virtual back-relations only — no new columns

Safety (all verified on real Postgres / Docker)

• ✅ Additive only — 0 ALTER/DROP on existing tables; FKs live on the new tables
• ✅ No backfill, no data loss possible
• ✅ Reversible — dropping the new objects runs clean in a txn; ROLLBACK leaves state intact
• ✅ Existing 8 tables untouched; tsc build passes; Prisma client generates

Deviations from the design draft (conformed to existing schema conventions)

1. IDs use UUID gen_random_uuid(), not cuid() — matches every existing model
2. tags is Json, not String[] — matches api_keys.permissions / webhooks.events convention
3. snake_case @map + @db.Timestamptz() throughout — matches codebase
4. No body search index yet — deferred to Commit 5 (pg_trgm GIN), per design §3
5. Seed skipped — avoids adding a TS-runner dep in a schema-only commit; the "real data only" testing rule means models get exercised via the real ingestion path in Commit 2

Migration SQL

CREATE TYPE "ConversationStatus" AS ENUM ('OPEN', 'RESOLVED', 'SNOOZED');
CREATE TYPE "MessageDirection" AS ENUM ('INBOUND', 'OUTBOUND');
CREATE TYPE "MessageDeliveryStatus" AS ENUM ('PENDING', 'SENT', 'DELIVERED', 'READ', 'FAILED');

CREATE TABLE "contacts" (id UUID PK default gen_random_uuid(), workspace_id UUID, jid TEXT,
  phone TEXT, whatsapp_name TEXT?, saved_name TEXT?, avatar_url TEXT?, created_at TIMESTAMPTZ, updated_at TIMESTAMPTZ);
CREATE TABLE "conversations" (id UUID PK, workspace_id UUID, contact_id UUID, session_id TEXT,
  status ConversationStatus default OPEN, last_message_at TIMESTAMPTZ, last_preview TEXT?, unread_count INT default 0,
  tags JSONB default '[]', session_deleted_at TIMESTAMPTZ?, metadata JSONB?, created_at, updated_at);
CREATE TABLE "messages" (id UUID PK, workspace_id UUID, conversation_id UUID, wa_message_id TEXT,
  direction MessageDirection, type TEXT, body TEXT?, media_url TEXT?, payload JSONB?,
  status MessageDeliveryStatus default SENT, from_me BOOL, wa_timestamp TIMESTAMPTZ, created_at TIMESTAMPTZ);

CREATE INDEX contacts(workspace_id, phone);  UNIQUE contacts(workspace_id, jid);
CREATE INDEX conversations(workspace_id, status, last_message_at DESC);  UNIQUE conversations(workspace_id, session_id, contact_id);
CREATE INDEX messages(conversation_id, wa_timestamp DESC);  UNIQUE messages(workspace_id, wa_message_id);
-- + 5 FKs (contacts/conversations/messages -> workspaces; conversations -> contacts; messages -> conversations) ON DELETE CASCADE

(Full verbatim SQL in prisma/migrations/20260603194714_add_inbox_models/migration.sql.)

---

v1.1 Inbox — Commit 1/6. Next: Commit 2 (ingestion + REST API).
🤖 Generated with Claude Code

[Anonymous]

Ticket changed by: Waqasahmedwaseer

• status: open --> closed