Messenger: a MySQL schema for chat, with the safety tables included

What the repository actually delivers

Messenger is described as a relational database design for modern messaging applications, providing a practical schema to build both one-to-one and group chat systems with support for common messaging workflows. The README asks that if you use the project in production or for learning, you feel free to share your implementation.

The deliverables are small and specific. There is messenger.sql, the SQL schema for a messaging platform. There is Messenger.mwb, a MySQL Workbench model file for visual schema design. And there is Messenger.png, an ER-style diagram preview of the schema. Those three artefacts are the product.

Around them is documentation. ARCHITECTURE.md covers table domains, relationships and design notes. AGENTS.md holds cross-agent development rules for the repository, CLAUDE.md holds Claude-specific implementation guidance, and .github/copilot-instructions.md holds Copilot-specific coding guidance, with docs/AGENTIC_DEVELOPMENT.md describing the AI-assisted development workflow. There are two implementation guides, one for a Node.js, Express, TypeScript, PostgreSQL and Socket.IO stack and one for a Go, PostgreSQL and WebSocket stack.

The repository also carries a prompt pack, with three prompt files under .github/prompts. One generates a full Node.js backend scaffold, one generates a full Go backend scaffold, and one generates a phased delivery roadmap for both stacks.

So the shape of the project is a schema plus a growing pile of agent-oriented instructions. The primary language is recorded as unknown, which is right for a repository that is mostly SQL, a Workbench binary and Markdown. The licence is not a standard identifier either, the default branch is master, the last push was on 2026-07-29 with the project not archived, and there are no GitHub releases.

The homepage is a project page at yoosuf.me/messenger, and the README closes with implementation support offered through Crew, an open innovation house based in Sri Lanka.

The core capabilities, and where the design spends its effort

The core capabilities list is short, and each entry corresponds to a table or a relationship rather than to a feature of a running system.

One-to-one conversation support and group chat support are the obvious pair, and the model treats them as the same thing, which is the design decision worth noticing. A conversation is a row, participants join users to it, and a one-to-one chat is simply a conversation with two participants. The alternative design, where direct messages and group messages live in separate tables, doubles the write paths for every feature that touches a message.

Message persistence and relational integrity is the second capability, and it is where the foreign keys matter. The third is attachment and gallery table support for media messages, which is a separate table rather than a column on the message, so a message can carry many attachments and a gallery view is a join.

The fourth item is the one that reveals the project's maturity: device access modelling improvements from previous versions. A messaging schema that models only users and messages is a schema for a web chat. A schema that models devices, access tokens and sessions is a schema for a phone application, and the improvements wording means the device model was revised after earlier versions.

Two tables that are easy to overlook make the difference between a demo and something usable. A deleted_messages table records that a message was removed, per user, which is a soft delete for user-visible cleanup, and a deleted_conversations table does the same for conversations, letting one participant hide a conversation without affecting the other participants. Neither is visible in a schema diagram until you need it, and both are the kind of thing that gets bolted on badly in production.

The activity log is the third of these. It exists to record what happened for auditing and for abuse detection, and the use case list names activity feed generation and auditing major messaging actions as separate reasons for it.

The table map, which is the clearest statement of the model

The use case to table mapping in the README is the single most useful artefact for understanding the design, because it lists the use cases and the tables that answer them side by side.

Account signup maps to the users table and to user_verification. Contact sync maps to contacts and to a user_contact linking table, which is the piece that lets a contact book be personalised per user rather than a single global list. Creating a conversation maps to conversation and to participants. Sending a message maps to messages and to attachments.

Then the per-user visibility tables appear. Deleting a message for a user maps to deleted_messages, and hiding a conversation for a user maps to deleted_conversations. Blocking or reporting a user maps to block_list and to reports.

Session access maps to devices and to access, which is the multi-device part of the model. Tracking a user event maps to activities.

The last three rows of the mapping are the interesting ones, because they show the same tables serving different products. A SaaS workspace channel chat maps to the same conversation, participants and messages tables. A dating match chat maps to those plus block_list and reports, because a match conversation needs the trust and safety tables attached to it.

That reuse is the argument for a shared schema. A one-to-one chat product, a team chat product and a dating product have different feature language but the same underlying relational structure, and this design lets all three sit on the same tables with different feature exposure.

The platform flow diagram in the README shows the same thing as a sequence. User registration leads to user verification, then device registration, then an access token being issued, then contact sync, then conversation creation, then a participant joining, then a message being sent, and from that send there are three branches: an attachment save, an activity log entry, and moderation checks, which lead in turn to a report or block.

Getting started, which is three steps and no code

The quick start is three steps, and the first one is the only one that involves running anything.

Import messenger.sql into your MySQL-compatible database. Then open Messenger.mwb in MySQL Workbench if you want to inspect or extend the model visually. And then connect your API or service layer to this schema and implement your messaging logic.

That third step is the honest boundary of this project. There is no server here, no API, no client library, and no application. The repository is a design artefact, and the README is explicit that the messaging logic is yours to write.

The suggested use cases indicate the intended audience, and they range from a mobile chat backend foundation and a SaaS communication module to prototyping chat APIs, learning relational modelling for messaging apps, and dating app real-time chat with trust and safety messaging.

Two of the implementation guides sit in an interesting middle position. The Node and Express with TypeScript, PostgreSQL and Socket.IO guide and the Go with PostgreSQL and WebSocket guide are both for PostgreSQL, while the schema itself is MySQL and ships as a MySQL Workbench model. So the guides are about the service layer above the schema, in two stacks, for people who want to see how a real-time messaging backend is assembled on top of a relational model, rather than a straightforward port of the SQL.

The prompt pack continues that pattern. A prompt file that generates a full Node.js backend scaffold is a way to start from the design rather than from a blank repository, and the third one, a phased delivery roadmap for both stacks, is aimed at teams that want to sequence the work.

The mermaid diagrams in the README are the other artefact to look at, since they are the only visual documentation of behaviour rather than of structure. There is the platform flow, the use case to table map, and a dating app integration blueprint that runs from a match service emitting a match event, through conversation creation and inserting two participants, to enabling a socket room, allowing message and attachment events, running trust and safety checks, and reporting or blocking if needed.

The moderation and safety model, which is the most mature part

A large part of the use case library is about moderation, and that is unusual for a schema repository, so it is worth asking why.

The answer is visible in the use case list itself. Dating app integration, trust and safety messaging, abuse detection, report and block flows, and moderation analytics all appear as first-class requirements rather than as an afterthought. A schema designed for chat in general has to accommodate the most demanding product domain, and the most demanding domain here is the one where a conversation is between two strangers who matched.

The mechanism is three tables working together. Block_list handles blocking a user in a conversation context. Reports handles reporting users or conversation participants, and the README is specific that the reporting flow is tracked through a moderation status that runs from pending to resolved. And activities records user events, which is the record for both the abuse detection mentioned in the dating use cases and the moderation analytics mentioned in the product and engagement use cases.

The dating app integration blueprint spells out the runtime sequence, and it is a good illustration of how the schema is meant to be driven. A match service emits a match event, which creates a conversation and inserts two participants, which enables a socket room, which allows message and attachment events, which go through trust and safety checks, which lead to a report or a block if needed.

Note what that blueprint assumes about the service layer. The database does not create the conversation, it records it. A match service, an event, and a socket room are all things you build. The schema supplies the shape of the state and the order in which that state has to exist, which is the real value of a schema in a project like this.

The use case library also covers the SaaS collaboration domain, where a team workspace channel for a project or department maps onto the same conversation and participants tables, and where secure conversation audit trails for compliance review are an explicit requirement, served by the activity log.

How old is this, and what does the version history tell you

The version history is derived from the git commit history, and it is the part of the README that most needs a careful reading.

Version 1.0.0 was the initial release on 2015-05-28. Version 1.0.1 on 2015-11-25 updated the access-to-device relationship to one-to-one. Version 1.0.2 on 2018-02-03 was a fix for issue 4. Version 1.0.3 on 2020-01-11 added attachment table support for message galleries, and version 1.0.4 on 2020-09-14 added a unique index on participants(conversation_id, user_id).

So the last recorded schema change is from September 2020, and the README lists five versions in total over five years. The repository was last pushed to on 2026-07-29, but the version history has no entry after 1.0.4, which tells you the recent activity has been documentation, agent instruction files and prompt packs rather than schema changes.

That distinction matters for anyone evaluating this as a foundation. The schema itself is a design from 2015 to 2020, and the last two changes are exactly the two you would want a mature messaging schema to have: a unique index preventing duplicate participants in a conversation, and the attachments table for media galleries.

The unique index on participants is a small change with a large effect, because without it a concurrent join could insert the same user into a conversation twice and every message query would double-count. It is the kind of fix that only gets made after a schema has been used in production, and its presence is a reasonable signal about the rest of the model.

The other thing the version history implies is what you will not find here. There is no partitioning strategy, no sharding design, no read replica guidance and no retention policy for the messages and activities tables, which on a system with real volume are the questions that decide whether the schema survives contact with production. The README's own framing is the right one to hold on to: this is a practical schema for building chat systems, a starting design you extend and adapt, and the ARCHITECTURE.md document is where the reasoning lives.

Editorial conclusion

Adopt the Messenger schema if you are building a chat backend and want a considered relational model to start from, particularly if your product needs the safety features, per-user soft deletes, device and token modelling and the activity log that the tables here already cover. Do not adopt it as a finished system, because there is no application layer in the repository and the schema design itself dates from 2015 to 2020 with no partitioning, retention or scaling guidance. Before you commit, read ARCHITECTURE.md rather than only the diagram, confirm the unique index on participants(conversation_id, user_id) is present in the SQL you import, and plan how you will handle the messages and activities tables once they grow, since that is the part the design does not answer.

Frequently asked questions

Official sources

1. Issues
2. Project website
3. README
4. yoosuf/Messenger on GitHub