24 lines
1007 B
SQL
24 lines
1007 B
SQL
CREATE TYPE conversation_kind AS ENUM ('dm', 'group', 'channel');
|
|
|
|
CREATE TABLE conversations (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
workspace_id UUID REFERENCES workspaces(id) ON DELETE CASCADE,
|
|
kind conversation_kind NOT NULL,
|
|
name TEXT,
|
|
is_public BOOLEAN NOT NULL DEFAULT true,
|
|
created_by UUID NOT NULL REFERENCES users(id),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
deleted_at TIMESTAMPTZ
|
|
);
|
|
|
|
CREATE TABLE conversation_members (
|
|
conversation_id UUID NOT NULL REFERENCES conversations(id) ON DELETE CASCADE,
|
|
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
joined_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
PRIMARY KEY (conversation_id, user_id)
|
|
);
|
|
|
|
CREATE INDEX conversations_workspace_id_idx ON conversations(workspace_id);
|
|
CREATE INDEX conversation_members_user_id_idx ON conversation_members(user_id);
|