Files

87 lines
3.3 KiB
SQL

CREATE TABLE IF NOT EXISTS users (
id UUID PRIMARY KEY,
name VARCHAR(255) NOT NULL,
email VARCHAR(255),
avatar_url VARCHAR(512),
password VARCHAR(255),
role VARCHAR(50),
status VARCHAR(50),
is_blocked BOOLEAN DEFAULT FALSE,
default_configs JSONB,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
deleted_at TIMESTAMP WITH TIME ZONE
);
CREATE TABLE IF NOT EXISTS user_identities (
id UUID PRIMARY KEY,
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
platform VARCHAR(50) NOT NULL,
identity_id VARCHAR(255) NOT NULL,
username VARCHAR(255) NOT NULL,
email VARCHAR(255),
avatar_url VARCHAR(512),
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE,
deleted_at TIMESTAMP WITH TIME ZONE,
UNIQUE(user_id, platform)
);
CREATE TABLE IF NOT EXISTS teams (
id UUID PRIMARY KEY,
name VARCHAR(255) NOT NULL,
member_limit INTEGER,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
deleted_at TIMESTAMP WITH TIME ZONE
);
CREATE TABLE IF NOT EXISTS team_members (
id UUID PRIMARY KEY,
team_id UUID NOT NULL REFERENCES teams(id) ON DELETE CASCADE,
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
role VARCHAR(50),
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
UNIQUE(team_id, user_id)
);
CREATE TABLE IF NOT EXISTS team_groups (
id UUID PRIMARY KEY,
team_id UUID NOT NULL REFERENCES teams(id) ON DELETE CASCADE,
name VARCHAR(255) NOT NULL,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
deleted_at TIMESTAMP WITH TIME ZONE
);
CREATE TABLE IF NOT EXISTS team_group_members (
id UUID PRIMARY KEY,
group_id UUID NOT NULL REFERENCES team_groups(id) ON DELETE CASCADE,
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
UNIQUE(group_id, user_id)
);
CREATE TABLE IF NOT EXISTS audits (
id UUID PRIMARY KEY,
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
operation VARCHAR(100) NOT NULL,
source_ip VARCHAR(50),
user_agent VARCHAR(512),
request TEXT,
response TEXT,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
CREATE INDEX IF NOT EXISTS idx_users_deleted_at ON users(deleted_at);
CREATE INDEX IF NOT EXISTS idx_user_identities_user_id ON user_identities(user_id);
CREATE INDEX IF NOT EXISTS idx_teams_deleted_at ON teams(deleted_at);
CREATE INDEX IF NOT EXISTS idx_team_members_team_id ON team_members(team_id);
CREATE INDEX IF NOT EXISTS idx_team_members_user_id ON team_members(user_id);
CREATE INDEX IF NOT EXISTS idx_team_groups_team_id ON team_groups(team_id);
CREATE INDEX IF NOT EXISTS idx_team_groups_deleted_at ON team_groups(deleted_at);
CREATE INDEX IF NOT EXISTS idx_team_group_members_group_id ON team_group_members(group_id);
CREATE INDEX IF NOT EXISTS idx_team_group_members_user_id ON team_group_members(user_id);
CREATE INDEX IF NOT EXISTS idx_audits_user_id ON audits(user_id);
CREATE INDEX IF NOT EXISTS idx_audits_created_at ON audits(created_at);