The database core for realtime conversations
One relational schema for one-to-one chat, group messaging, moderation, device sessions, and attachments — importing cleanly on both PostgreSQL and MySQL. Point a Node.js or Go backend at it over Socket.IO or WebSocket.
- One-to-one + group messaging
- Report & block built in
- 15 relational tables
$ mysql -u app -p chatdb < messenger.sql ✓ 15 tables created $ SELECT * FROM messages; users user_verification contacts user_contact conversation participants messages attachments deleted_messages deleted_conversations block_list reports devices access activities ✓ 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
Most chat products are one of two shapes: rooms for a team, or one-to-one threads between two people. The tables underneath are the same either way.
Team and support chat
Team workspace channels
Rooms for product, support and ops, with history that persists and stays searchable.
Customer-account chat
Conversations that hold their context across the whole account lifecycle, not just the moment of contact.
Audit-friendly trails
Activity and moderation entities keep a record you can actually hand to a reviewer.
Dating and social apps
Match-to-chat activation
Open a direct conversation the moment a match event fires.
Private media sharing
One-to-one threads with attachments, scoped to the two people in them.
Report and block flows
Abuse reports tracked from pending to resolved, with blocking sitting alongside them.
Quick start
From an empty database to a ready-to-wire messaging model in three steps.
# Import the schema
mysql -u app -p chatdb < messenger.sql
# Core tables are ready:
# users, conversations, participants,
# messages, attachments, devices, access Implementation guides
Step-by-step guides for wiring the schema into realtime backends, plus reference architecture and a prompt pack for handing the job to an agent.
Node.js + Express + TypeScript + PostgreSQL + Socket.IO
A full walkthrough for a realtime Node backend, schema included.
Read the Node.js guideGo + PostgreSQL + WebSocket
The same schema wired into a Go backend with native WebSocket transport.
Read the Go guideArchitecture & lifecycle diagrams
Table domains, relationships, and design notes from user registration through report and block.
Open ARCHITECTURE.mdPrompt pack
Repository prompts that scaffold a Node or Go backend for your agent, including a phased route through both.
Browse the prompt packSchema at a glance
Fifteen tables across seven domains — identity, contact network, conversation,
messaging, device & session, moderation, and engagement. 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. |
Common questions
Straight answers about the schema, the stack, and where it stops.
Why MySQL and PostgreSQL instead of one engine?
The design stays on relational features both engines speak fluently, and the
repo ships one messenger.sql
that imports cleanly on either. Pick the database your stack already runs —
there's no lock-in.
Is this production-ready or more of a blueprint?
It's a migration-ready starting schema rather than an ORM. Column types, keys, indexes, and constraints are laid out so a backend can ship against them directly — you own the migrations your product will eventually need.
How do per-user deletes actually work?
Messages and conversations are never hard-deleted from the audit trail.
deleted_messages and deleted_conversations
record who hid what and when, so visibility stays per-user while moderators
keep the history.
Does it handle end-to-end encryption?
No. The schema models devices, sessions, and token-based access so key management has a home, but message bodies are stored in plaintext. Wire TLS at the transport and decide on E2EE at the application layer.
Can I bolt this onto an existing auth system?
Yes. users and user_verification are
deliberately simple — phone or email registration plus temporary
verification codes — so you can map them onto your own identity tables and
skip the built-in flows.
The schema is the part you don’t have to design.
MIT-licensed, and it imports cleanly on MySQL or PostgreSQL. Load messenger.sql, open Messenger.mwb if you want the ER diagram, then start adding realtime endpoints against tables that already handle soft deletes, moderation and sessions.
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.