Files
MonkeyCode/backend/migration/000004_task_notify.up.sql

86 lines
2.9 KiB
SQL

-- Task/Notify 相关表
-- tasks 任务表
CREATE TABLE IF NOT EXISTS tasks (
id UUID PRIMARY KEY,
deleted_at TIMESTAMPTZ,
user_id UUID NOT NULL REFERENCES users(id),
kind VARCHAR NOT NULL,
sub_type VARCHAR,
content TEXT NOT NULL,
summary TEXT,
status VARCHAR NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
completed_at TIMESTAMPTZ
);
CREATE UNIQUE INDEX IF NOT EXISTS idx_tasks_user_id ON tasks(user_id);
-- project_tasks 项目任务关联表
CREATE TABLE IF NOT EXISTS project_tasks (
id UUID PRIMARY KEY,
task_id UUID NOT NULL REFERENCES tasks(id),
model_id UUID NOT NULL REFERENCES models(id),
image_id UUID NOT NULL REFERENCES images(id),
git_identity_id UUID,
project_id UUID,
issue_id UUID,
repo_url VARCHAR,
repo_filename VARCHAR,
branch VARCHAR,
cli_name VARCHAR,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
-- task_virtualmachines 任务-虚拟机关联表
CREATE TABLE IF NOT EXISTS task_virtualmachines (
id UUID PRIMARY KEY,
task_id UUID NOT NULL REFERENCES tasks(id),
virtualmachine_id VARCHAR NOT NULL REFERENCES virtualmachines(id),
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
-- notify_channels 通知渠道表
CREATE TABLE IF NOT EXISTS notify_channels (
id UUID PRIMARY KEY,
deleted_at TIMESTAMPTZ,
owner_id UUID NOT NULL,
owner_type VARCHAR NOT NULL DEFAULT 'user',
name VARCHAR(64) NOT NULL,
kind VARCHAR NOT NULL,
webhook_url TEXT NOT NULL,
secret TEXT DEFAULT '',
headers JSONB,
enabled BOOLEAN NOT NULL DEFAULT TRUE,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_notify_channels_owner ON notify_channels(owner_id, owner_type);
-- notify_subscriptions 通知订阅表
CREATE TABLE IF NOT EXISTS notify_subscriptions (
id UUID PRIMARY KEY,
deleted_at TIMESTAMPTZ,
channel_id UUID NOT NULL REFERENCES notify_channels(id),
scope VARCHAR NOT NULL DEFAULT 'self',
event_types JSONB NOT NULL,
enabled BOOLEAN NOT NULL DEFAULT TRUE,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_notify_subscriptions_channel ON notify_subscriptions(channel_id);
-- notify_send_logs 通知发送日志表
CREATE TABLE IF NOT EXISTS notify_send_logs (
id UUID PRIMARY KEY,
subscription_id UUID NOT NULL,
channel_id UUID NOT NULL,
event_type VARCHAR NOT NULL,
event_ref_id VARCHAR NOT NULL,
status VARCHAR NOT NULL,
error TEXT DEFAULT '',
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_notify_send_logs_dedup ON notify_send_logs(subscription_id, event_type, event_ref_id);
CREATE INDEX IF NOT EXISTS idx_notify_send_logs_status ON notify_send_logs(status, created_at);