PostgreSQL

Chat / messaging database schema

A schema for a Slack/Discord-style chat app with workspaces, channels, direct messages, threads, reactions, and read receipts. Designed to scale message reads with cursor-based pagination.

Start for free

Database schema previewChat / messaging database schema

Recreate this in Raltey

Copy this prompt

Paste this into Raltey's AI agent to generate the same diagram, then tweak it on the canvas.

Prompt

Design a PostgreSQL database schema for: Chat / messaging database schema.

A schema for a Slack/Discord-style chat app with workspaces, channels, direct messages, threads, reactions, and read receipts. Designed to scale message reads with cursor-based pagination.

Include these tables: workspace, user_account, membership, channel, channel_member, message, attachment, reaction, read_cursor.

Use proper primary keys, foreign keys, and indexes. Export SQL DDL for PostgreSQL.

Context

- Workspace-scoped, channel-based chat plus DMs.
- Threads (replies) live in their own table to avoid bloating message reads.
- Read receipts are per-user, per-conversation, stored as a cursor.
- Reactions are normalized for easy aggregation.

Patterns

- Threads as messages with a parent_id pointer, not a separate table.
- Read receipts as a single cursor per (channel, user), not one row per message.
- Reactions deduplicated by (message_id, user_id, emoji).

Tables

The schema, table by table

workspace

DBML - workspace

Table workspace {
  id    uuid pk
  slug  text unique not null
  name  text not null
}

user_account

DBML - user_account

Table user_account {
  id    uuid pk
  email text unique not null
  name  text not null
}

membership

DBML - membership

Table membership {
  workspace_id uuid [ref: > workspace.id]
  user_id      uuid [ref: > user_account.id]
  role         text not null
  pk (workspace_id, user_id)
}

channel

Either a public/private channel or a DM container.

DBML - channel

Table channel {
  id           uuid pk
  workspace_id uuid [ref: > workspace.id, not null]
  kind         text not null   // channel, dm, group_dm
  name         text
  is_private   boolean default false
  created_at   timestamptz default `now()`
}

channel_member

DBML - channel_member

Table channel_member {
  channel_id uuid [ref: > channel.id]
  user_id    uuid [ref: > user_account.id]
  joined_at  timestamptz default `now()`
  pk (channel_id, user_id)
}

message

DBML - message

Table message {
  id          uuid pk
  channel_id  uuid [ref: > channel.id, not null]
  author_id   uuid [ref: > user_account.id, not null]
  body        text
  parent_id   uuid [ref: > message.id]   // thread root
  created_at  timestamptz default `now()`
  edited_at   timestamptz
  deleted_at  timestamptz
}

attachment

DBML - attachment

Table attachment {
  id         uuid pk
  message_id uuid [ref: > message.id]
  url        text not null
  mime_type  text
  size_bytes bigint
}

reaction

DBML - reaction

Table reaction {
  message_id uuid [ref: > message.id]
  user_id    uuid [ref: > user_account.id]
  emoji      text not null
  pk (message_id, user_id, emoji)
}

read_cursor

Last read message per user per channel.

DBML - read_cursor

Table read_cursor {
  channel_id      uuid [ref: > channel.id]
  user_id         uuid [ref: > user_account.id]
  last_read_at    timestamptz not null
  last_message_id uuid [ref: > message.id]
  pk (channel_id, user_id)
}

Relationships

From	To	Type
workspace	channel	1:N
channel	channel_member	1:N
channel	message	1:N
message	attachment	1:N
message	reaction	1:N
message	message	1:N
channel	read_cursor	1:N

Notes from the field

Index message(channel_id, created_at DESC) for fast scrollback.
For tens of millions of messages per workspace, consider partitioning message by month.
Soft-delete messages with deleted_at to keep replies that reference them intact.

Frequently asked

Why not store threads in a separate table?

A self-referencing parent_id on message keeps the read path simple and avoids cross-table joins. The thread root and replies share the same indexes.

How do read receipts scale?

Store one cursor row per (channel, user) instead of one row per message read. Updating the cursor on the latest read message is O(1).

More templates

FlowchartSaaS onboarding email flow FlowchartCustomer support runbook flow FlowchartPayment webhook retry and error flow FlowchartGitHub PR review and merge flow FlowchartLogin with magic link flow ArchitectureThree-tier SaaS architecture on AWS ArchitectureMicroservices architecture on Kubernetes ArchitectureReal-time data pipeline on GCP

Open this template in Raltey

Edit it on the canvas, ask the AI agent to extend it, then share or export.