35 lines
1.3 KiB
SQL
35 lines
1.3 KiB
SQL
CREATE TYPE workspace_role AS ENUM ('owner', 'admin', 'member');
|
|
|
|
CREATE TABLE workspaces (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
name TEXT NOT NULL,
|
|
slug TEXT NOT NULL UNIQUE,
|
|
owner_id 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 workspace_members (
|
|
workspace_id UUID NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
|
|
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
role workspace_role NOT NULL DEFAULT 'member',
|
|
display_name TEXT,
|
|
avatar_url TEXT,
|
|
joined_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
PRIMARY KEY (workspace_id, user_id)
|
|
);
|
|
|
|
CREATE TABLE workspace_invitations (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
workspace_id UUID NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
|
|
created_by UUID NOT NULL REFERENCES users(id),
|
|
token TEXT NOT NULL UNIQUE,
|
|
max_uses INT,
|
|
use_count INT NOT NULL DEFAULT 0,
|
|
expires_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX workspace_members_user_id_idx ON workspace_members(user_id);
|