Chat & Presence — Data Schema

PostgreSQL (Prisma)

New model: `ChatMessage`

Add to packages/db/prisma/schema.prisma:


model ChatMessage {
  id          String    @id @default(cuid())
  tenantId    String                        // tenant scope; for super-admin DMs use recipient's tenantId
  senderId    String
  recipientId String
  content     String
  createdAt   DateTime  @default(now())
  readAt      DateTime?                     // null = unread
 
  tenant      Tenant    @relation(fields: [tenantId], references: [id], onDelete: Cascade)
  sender      User      @relation("SentMessages", fields: [senderId], references: [id])
  recipient   User      @relation("ReceivedMessages", fields: [recipientId], references: [id])
 
  @@index([tenantId, createdAt])
  @@index([senderId, recipientId, createdAt])   // conversation thread lookup
  @@index([recipientId, readAt])                // unread count lookup
  @@map("chat_messages")
}

Additions to existing models


// Append to User model
model User {
  // ... existing fields ...
  sentMessages     ChatMessage[] @relation("SentMessages")
  receivedMessages ChatMessage[] @relation("ReceivedMessages")
}
 
// Append to Tenant model
model Tenant {
  // ... existing fields ...
  chatMessages ChatMessage[]
}

Why no `ChatThread` / `Conversation` table?

A conversation between user A and user B is always identified by the sorted pair of their IDs. Queries are by (senderId, recipientId) OR (senderId=B, recipientId=A). A separate conversation table adds a join with no benefit until we need group chat or conversation metadata (title, archived state). That can be added later.

Migration


# From packages/db/
pnpm prisma migrate dev --name add_chat_messages
pnpm prisma generate

Redis Key Conventions

All keys are namespaced under chat: or presence: to avoid collision with existing BullMQ and audit-log keys.

Presence

Key	Type	Value	TTL	Purpose
`presence:{userId}`	STRING	`"1"`	90s	Online heartbeat; expiry = offline
`presence:tenant:{tenantId}`	ZSET	member=userId, score=unix timestamp	none	Fast lookup of all users ever connected to tenant

The sorted set is a roster — it never shrinks automatically. The presence:{userId} key is the source of truth for online/offline. Before serving the list, filter the sorted set by checking which members have a live presence:{userId} key.

Unread counts (optional optimisation)

Key	Type	Value	TTL	Purpose
`unread:{userId}:{senderId}`	STRING	integer	none	Unread messages from a specific sender

Increment on chat:send, reset to 0 when recipient emits chat:read for that sender. Allows the UI to show badge counts without a database query. Cleared (DEL) when the user reads the conversation.

Socket.IO adapter

The @socket.io/redis-adapter uses keys prefixed socket.io#. Do not manually read or write these keys.

Retention & Cleanup

Chat messages are retained indefinitely by default (same as other content in the platform). A future cleanup job could archive messages older than 90 days to cold storage if needed. No schema changes are required for that — it is an operational concern.