Chat App Database Schema (SQL + ERD + Prisma)

Output

SQL schema sample

CREATE TABLE users (id UUID PRIMARY KEY, username TEXT UNIQUE NOT NULL);
CREATE TABLE conversations (id UUID PRIMARY KEY, type TEXT NOT NULL);
CREATE TABLE conversation_members (conversation_id UUID REFERENCES conversations(id), user_id UUID REFERENCES users(id), PRIMARY KEY(conversation_id, user_id));
CREATE TABLE messages (id UUID PRIMARY KEY, conversation_id UUID REFERENCES conversations(id), sender_id UUID REFERENCES users(id), body TEXT NOT NULL, created_at TIMESTAMPTZ DEFAULT NOW());

Diagram

users *---* conversations (via conversation_members)
conversations 1---* messages
users 1---* messages (sender)