32 lines
1.3 KiB
MySQL
32 lines
1.3 KiB
MySQL
|
-- Migration 069: Groups and group members (social groups)
|
||
|
|
-- Required for v0.302 Lot S2 - groups may be used by GORM before this migration in some setups
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS groups (
|
||
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
|
|
name VARCHAR(255) NOT NULL,
|
||
|
|
description TEXT,
|
||
|
|
creator_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
||
|
|
avatar_url VARCHAR(500),
|
||
|
|
is_public BOOLEAN NOT NULL DEFAULT true,
|
||
|
|
member_count INT NOT NULL DEFAULT 1,
|
||
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
|
|
deleted_at TIMESTAMPTZ
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_groups_creator_id ON groups(creator_id);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_groups_is_public ON groups(is_public);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_groups_deleted_at ON groups(deleted_at);
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS group_members (
|
||
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
|
|
group_id UUID NOT NULL REFERENCES groups(id) ON DELETE CASCADE,
|
||
|
|
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
||
|
|
role VARCHAR(50) NOT NULL DEFAULT 'member',
|
||
|
|
joined_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
|
|
UNIQUE(group_id, user_id)
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_group_members_group_id ON group_members(group_id);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_group_members_user_id ON group_members(user_id);
|