mirror of
https://github.com/chaitin/MonkeyCode.git
synced 2026-11-22 11:28:46 +08:00
95 lines
4.4 KiB
SQL
95 lines
4.4 KiB
SQL
CREATE TABLE IF NOT EXISTS git_identities (
|
|
id uuid PRIMARY KEY DEFAULT uuid_generate_v1() NOT NULL,
|
|
user_id uuid NOT NULL,
|
|
platform character varying(12) NOT NULL,
|
|
base_url text,
|
|
access_token text,
|
|
username text,
|
|
email text,
|
|
remark text,
|
|
created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
|
|
updated_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
|
|
deleted_at timestamp with time zone,
|
|
installation_id bigint,
|
|
oauth_refresh_token text DEFAULT ''::text,
|
|
oauth_expires_at timestamp with time zone,
|
|
oauth_site_id uuid,
|
|
CONSTRAINT fk_git_identities_oauth_site_id FOREIGN KEY (oauth_site_id) REFERENCES team_oauth_sites(id) ON DELETE SET NULL
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_git_identities_installation_id ON git_identities USING btree (installation_id);
|
|
CREATE INDEX IF NOT EXISTS idx_git_identities_oauth_site_id ON git_identities USING btree (oauth_site_id);
|
|
CREATE INDEX IF NOT EXISTS idx_git_identities_user_id ON git_identities USING btree (user_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS projects (
|
|
id uuid PRIMARY KEY DEFAULT uuid_generate_v1() NOT NULL,
|
|
user_id uuid NOT NULL,
|
|
name character varying(255) NOT NULL,
|
|
description text,
|
|
created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
|
|
deleted_at timestamp with time zone,
|
|
repo_url text,
|
|
platform character varying(255),
|
|
git_identity_id uuid,
|
|
branch character varying(255),
|
|
image_id uuid,
|
|
env_variables jsonb
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_projects_created_at_id ON projects USING btree (created_at, id);
|
|
CREATE INDEX IF NOT EXISTS idx_projects_git_identity_id ON projects USING btree (git_identity_id);
|
|
CREATE INDEX IF NOT EXISTS idx_projects_user_id ON projects USING btree (user_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS project_collaborators (
|
|
id uuid PRIMARY KEY DEFAULT uuid_generate_v1() NOT NULL,
|
|
project_id uuid NOT NULL,
|
|
user_id uuid NOT NULL,
|
|
role character varying(50) DEFAULT 'read_only'::character varying NOT NULL,
|
|
created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
|
|
deleted_at timestamp with time zone
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_project_collaborators_project_id ON project_collaborators USING btree (project_id);
|
|
CREATE INDEX IF NOT EXISTS idx_project_collaborators_user_id ON project_collaborators USING btree (user_id);
|
|
CREATE UNIQUE INDEX IF NOT EXISTS unique_idx_project_collaborators_project_user ON project_collaborators USING btree (project_id, user_id)
|
|
WHERE (deleted_at IS NULL);
|
|
|
|
CREATE TABLE IF NOT EXISTS project_issues (
|
|
id uuid PRIMARY KEY DEFAULT uuid_generate_v1() NOT NULL,
|
|
user_id uuid NOT NULL,
|
|
project_id uuid NOT NULL,
|
|
status character varying(50) DEFAULT 'todo'::character varying NOT NULL,
|
|
title text NOT NULL,
|
|
created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
|
|
deleted_at timestamp with time zone,
|
|
requirement_document text,
|
|
assignee_id uuid,
|
|
priority integer DEFAULT 2 NOT NULL,
|
|
design_document text,
|
|
summary text
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_project_issues_created_at_id ON project_issues USING btree (created_at, id);
|
|
CREATE INDEX IF NOT EXISTS idx_project_issues_project_id ON project_issues USING btree (project_id);
|
|
CREATE INDEX IF NOT EXISTS idx_project_issues_status ON project_issues USING btree (status);
|
|
CREATE INDEX IF NOT EXISTS idx_project_issues_user_id ON project_issues USING btree (user_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS project_issue_comments (
|
|
id uuid PRIMARY KEY DEFAULT uuid_generate_v1() NOT NULL,
|
|
user_id uuid NOT NULL,
|
|
issue_id uuid NOT NULL,
|
|
parent_id uuid,
|
|
comment text NOT NULL,
|
|
created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
|
|
deleted_at timestamp with time zone
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_project_issue_comments_created_at_id ON project_issue_comments USING btree (created_at, id);
|
|
CREATE INDEX IF NOT EXISTS idx_project_issue_comments_issue_id ON project_issue_comments USING btree (issue_id);
|
|
CREATE INDEX IF NOT EXISTS idx_project_issue_comments_parent_id ON project_issue_comments USING btree (parent_id);
|
|
CREATE INDEX IF NOT EXISTS idx_project_issue_comments_user_id ON project_issue_comments USING btree (user_id);
|