Open Source · Chat Schema · PostgreSQL / MySQL

The database core for realtime conversations

Ship chat products faster with a practical relational schema for one-to-one chat, group messaging, moderation, device sessions, and attachment workflows. Ready for Node.js or Go backends with PostgreSQL and socket support.

View Repository Explore SQL Schema

One-to-one + group messaging
Report & block built in
16 relational tables

messenger.sql — schema

$ mysql -u app -p chatdb < messenger.sql
✓ 16 tables created

$ SHOW TABLES;
users                 user_verification
contacts              user_contact
conversation          participants
messages              attachments
deleted_messages      deleted_conversations
block_list            reports
devices               access
activities

$ SELECT * FROM messages;
✓ ready for your API layer

What's inside the schema

One relational design that covers the full messaging lifecycle — identity and contacts, conversation creation, message delivery, media, and moderation.

Core Messaging

Direct chat, group chat, participants, message persistence, and per-user delete visibility — soft deletes and conversation hiding modeled in from day one.

Trust & Safety

Built-in report and block models that support moderation pipelines, compliance reviews, and safer user experiences.

Device & Access

Device registration and token access modeling for multi-device realtime chat behavior, session tracking, and token revocation.

Media Ready

Attachment support for galleries and media messaging in modern mobile and web products.

Built for real chat products

A foundation that matches today's messaging patterns — team collaboration or consumer social — without redesigning the data layer later.

SaaS collaboration

Team workspace channels

Rooms for product, support, and operations communication with persistent, searchable history.
Customer-account chat

Workflows between users and success teams that keep context across the whole account lifecycle.
Compliance-aware trails

Activity and moderation entities preserve audit-friendly message trails for review.
Tenant-driven API layers

Role-driven or tenant-driven services built directly on top of the schema.

Dating app integration

Match-to-chat activation

Create a direct conversation the moment a match event fires.
Secure one-to-one messaging

Private conversations with media sharing between matched users.
Report & block flows

Safety workflows for abuse prevention, tracked from pending to resolved.
Privacy-centric controls

Per-user message and conversation hide behavior for privacy expectations.

Schema at a glance

Sixteen tables across five domains — identity, contact network, conversation, messaging, and moderation. A full ER-style diagram ships with the repository as Messenger.mwb and Messenger.png.

Table	Domain	Purpose
`users`, `user_verification`	Identity	Phone / email registration and temporary verification codes.
`contacts`, `user_contact`	Contact network	Contact import, sync, and user-to-contact linking.
`conversation`, `participants`	Conversation	One-to-one and group conversations with participant membership.
`messages`, `attachments`	Messaging	Message persistence, relational integrity, and media galleries.
`deleted_messages`, `deleted_conversations`	Messaging	Per-user delete visibility and conversation hide behavior.
`block_list`, `reports`	Moderation	Blocking, user reporting, and moderation status tracking.
`devices`, `access`	Device & session	Multi-device registration, token issuance, and revocation.
`activities`	Engagement	Activity feed generation and audit logging of major messaging actions.

Implementation guides

Step-by-step guides for wiring the schema into realtime backends, plus reference architecture and a prompt pack for agentic development.

Node.js + Express + TypeScript + PostgreSQL + Socket.IO

A full walkthrough for a realtime Node backend using the schema as its database core.

Read the Node.js guide

Go + PostgreSQL + WebSocket

The same schema wired into a Go backend with native WebSocket transport.

Read the Go guide

Architecture & lifecycle diagrams

Table domains, relationships, and design notes from user registration through report and block.

Open ARCHITECTURE.md

Prompt pack for agentic development

Repository prompts that scaffold a Node or Go backend, or a phased dual-backend roadmap, with your AI agent.

Browse the prompt pack

Quick start

From an empty database to a ready-to-wire messaging model in three steps.

1
Import the schema

Run messenger.sql against your database. MySQL-first, PostgreSQL-friendly.
2
Inspect the ER model

Open Messenger.mwb in MySQL Workbench to see and extend the design visually.
3
Wire your API layer

Connect Node.js + Socket.IO or Go + WebSocket and implement your messaging logic on the tables.

Database # Import the schema mysql -u app -p chatdb < messenger.sql # Core tables are ready: # users, conversations, participants, # messages, attachments, devices, access

Ship your chat backend on a proven schema.

Messenger is an open-source relational design for modern messaging: one-to-one and group chat, media, moderation, and multi-device access. Import the SQL, wire your API layer, and go.

Star on GitHub Read Documentation Node.js + Socket.IO guide Go + WebSocket guide

Let's build something that scales.

Planning an AI product, automating a workflow, or taking a platform to production? I'd love to hear what you're building.

Book a call Contact me