mirror of
https://github.com/chaitin/MonkeyCode.git
synced 2026-11-22 19:39:31 +08:00
87 lines
3.3 KiB
SQL
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); |