23b64ff
-- schema_pre_sync.sql
-- Drops hand-managed unique indexes that sea-orm schema sync would reject.
-- The sync only compares the indexed column list against the entity's
-- #[sea_orm(indexed)] / unique_key declarations; a predicate, a NULLS NOT
-- DISTINCT clause or a column list the entity cannot express all look like a
-- stale unique key, and Postgres then issues DROP CONSTRAINT on a plain index,
-- which fails. These indexes are recreated by schema_extras.sql after sync.
--
-- Partial indexes are dropped even when their column list matches an entity
-- unique key: sync compares only column lists and then issues DROP CONSTRAINT,
-- which fails because these are plain indexes. Recreating them is cheap enough
-- because the gate below runs on schema changes or drift, not on every start.
-- Legacy plain indexes left behind by older unique_key definitions. They no
-- longer match the entity column list, so sync would try to drop them and
-- abort because they are plain indexes.
DROP INDEX IF EXISTS "idx-issue_watch-issue_user";
DROP INDEX IF EXISTS "idx-issue_reaction-issue_user_emoji";
-- Hand-managed per-target unique keys (predicates not expressible in entities).
DROP INDEX IF EXISTS idx_issue_watch_issue_user;
DROP INDEX IF EXISTS idx_issue_watch_project_user;
DROP INDEX IF EXISTS idx_issue_reaction_issue;
DROP INDEX IF EXISTS idx_issue_reaction_comment;
DROP INDEX IF EXISTS idx_issue_reaction_project;
DROP INDEX IF EXISTS idx_branch_protection_whitelist_user;
DROP INDEX IF EXISTS idx_branch_protection_whitelist_group;
DROP INDEX IF EXISTS idx_chat_message_room_nonce;
DROP INDEX IF EXISTS idx_chat_dm_pair;
DROP INDEX IF EXISTS idx_chat_gdm_key;
DROP INDEX IF EXISTS idx_chat_draft_unique;
DROP INDEX IF EXISTS idx_space_join_requests_pending;
-- 上面被删掉的部分唯一索引在旧版本里可能「删了没建成」（extras 曾整份回滚），
-- 期间写入的重复行会让 sync 自建唯一键、以及 extras 重建部分索引时失败。
-- 因此先按业务键收敛到最新一行；表不存在（全新库）时整段跳过。
DO $$ BEGIN
IF to_regclass('issue_watch') IS NOT NULL THEN
DELETE FROM issue_watch AS stale
USING issue_watch AS keep
WHERE stale.issue_id IS NOT NULL
AND stale.issue_id = keep.issue_id
AND stale.user_id = keep.user_id
AND (stale.created_at, stale.id) < (keep.created_at, keep.id);
DELETE FROM issue_watch AS stale
USING issue_watch AS keep
WHERE stale.project_id IS NOT NULL
AND stale.project_id = keep.project_id
AND stale.user_id = keep.user_id
AND (stale.created_at, stale.id) < (keep.created_at, keep.id);
END IF;
IF to_regclass('issue_reaction') IS NOT NULL THEN
DELETE FROM issue_reaction AS stale
USING issue_reaction AS keep
WHERE stale.issue_id IS NOT NULL
AND stale.issue_id = keep.issue_id
AND stale.user_id = keep.user_id
AND stale.emoji = keep.emoji
AND (stale.created_at, stale.id) < (keep.created_at, keep.id);
DELETE FROM issue_reaction AS stale
USING issue_reaction AS keep
WHERE stale.comment_id IS NOT NULL
AND stale.comment_id = keep.comment_id
AND stale.user_id = keep.user_id
AND stale.emoji = keep.emoji
AND (stale.created_at, stale.id) < (keep.created_at, keep.id);
DELETE FROM issue_reaction AS stale
USING issue_reaction AS keep
WHERE stale.project_id IS NOT NULL
AND stale.project_id = keep.project_id
AND stale.user_id = keep.user_id
AND stale.emoji = keep.emoji
AND (stale.created_at, stale.id) < (keep.created_at, keep.id);
END IF;
END $$;
-- scheduled messages feature removed; drop the legacy table before sync
DROP TABLE IF EXISTS user_scheduled_message;
-- legacy REST admin retired; drop its tables before sync
DROP TABLE IF EXISTS admin_audit_log;
DROP TABLE IF EXISTS admin_user;
-- CI, webhook delivery, import and search features removed
DROP TABLE IF EXISTS ci_job_token CASCADE;
DROP TABLE IF EXISTS ci_runner_token CASCADE;
DROP TABLE IF EXISTS ci_runner CASCADE;
DROP TABLE IF EXISTS ci_log_chunk CASCADE;
DROP TABLE IF EXISTS workflow_step CASCADE;
DROP TABLE IF EXISTS workflow_job CASCADE;
DROP TABLE IF EXISTS workflow_artifact CASCADE;
DROP TABLE IF EXISTS workflow_run CASCADE;
DROP TABLE IF EXISTS ci_workflow_counter CASCADE;
DROP TABLE IF EXISTS ci_actions_identity CASCADE;
DROP TABLE IF EXISTS ci_actions_schedule CASCADE;
DROP TABLE IF EXISTS ci_actions_cache CASCADE;
DROP TABLE IF EXISTS ci_deployment CASCADE;
DROP TABLE IF EXISTS ci_environment_review CASCADE;
DROP TABLE IF EXISTS ci_secret CASCADE;
DROP TABLE IF EXISTS ci_variable CASCADE;
DROP TABLE IF EXISTS ci_environment CASCADE;
DROP TABLE IF EXISTS repo_webhook_delivery CASCADE;
DROP TABLE IF EXISTS repo_webhook CASCADE;
DROP TABLE IF EXISTS webhook_delivery CASCADE;
DROP TABLE IF EXISTS webhook CASCADE;
DROP TABLE IF EXISTS import_job CASCADE;
DROP TABLE IF EXISTS search_index_job CASCADE;
DROP TABLE IF EXISTS search_repo_state CASCADE;
-- chat message full-text search removed
DROP FUNCTION IF EXISTS chat_message_search_vector_update() CASCADE;
DO $$ BEGIN
IF to_regclass('chat_message') IS NOT NULL THEN
DROP TRIGGER IF EXISTS chat_message_search_vector_trigger ON chat_message;
ALTER TABLE chat_message DROP COLUMN IF EXISTS search_vector;
END IF;
END $$;
-- pipeline tab removed from repository settings
DO $$ BEGIN
IF to_regclass('repos') IS NOT NULL THEN
ALTER TABLE repos DROP COLUMN IF EXISTS pipelines_public;
END IF;
END $$;
-- rooms.parent_id used to be declared as a self-reference, so sync created
-- `fk-rooms-parent_id -> rooms(id)`. The column actually points at a directory
-- node (dir.id) — update_room validates the target is a same-space DirKind::Dir
-- row before writing — so every attempt to file a channel into a folder was
-- rejected by that constraint and the feature never once succeeded. Sync only
-- adds missing foreign keys and treats a same-named constraint as present, so
-- the wrong one has to go before the corrected entity can be built.
-- Guarded on confrelid so this is a no-op once the constraint is correct.
DO $$ BEGIN
IF to_regclass('rooms') IS NOT NULL
AND to_regclass('dir') IS NOT NULL
AND EXISTS (
SELECT 1 FROM pg_constraint
WHERE conname = 'fk-rooms-parent_id'
AND conrelid = to_regclass('rooms')
AND confrelid <> to_regclass('dir')
) THEN
ALTER TABLE rooms DROP CONSTRAINT "fk-rooms-parent_id";
-- Defensive: anything the old constraint let through cannot be a dir
-- node, so it is not a valid folder. Reset it instead of letting the
-- corrected foreign key fail to build.
UPDATE rooms SET parent_id = NULL
WHERE parent_id IS NOT NULL
AND NOT EXISTS (SELECT 1 FROM dir WHERE dir.id = rooms.parent_id);
END IF;
END $$;
DROP TABLE IF EXISTS pipeline_run;
-- idempotent column adds for phase-2 model changes (sync layer cannot
-- express NOT NULL DEFAULT additions on populated tables).
-- Guarded with to_regclass: on a fresh database the tables do not exist yet
-- (they are created by the registry sync right after, with these columns
-- already included), so the ALTERs must be skipped instead of failing.
DO $$ BEGIN
IF to_regclass('room_members') IS NOT NULL THEN
ALTER TABLE room_members ADD COLUMN IF NOT EXISTS role SMALLINT NOT NULL DEFAULT 0;
END IF;
END $$;
DO $$ BEGIN
IF to_regclass('issue') IS NOT NULL THEN
ALTER TABLE issue ADD COLUMN IF NOT EXISTS priority SMALLINT NOT NULL DEFAULT 0;
ALTER TABLE issue ADD COLUMN IF NOT EXISTS due_at TIMESTAMP WITH TIME ZONE;
END IF;
END $$;
DO $$ BEGIN
IF to_regclass('user_access_key') IS NOT NULL THEN
ALTER TABLE user_access_key ADD COLUMN IF NOT EXISTS expires_at TIMESTAMP WITH TIME ZONE;
ALTER TABLE user_access_key ADD COLUMN IF NOT EXISTS last_used_at TIMESTAMP WITH TIME ZONE;
END IF;
END $$;
DO $$ BEGIN
IF to_regclass('user_preferences') IS NOT NULL THEN
ALTER TABLE user_preferences ADD COLUMN IF NOT EXISTS board_view_mode VARCHAR NOT NULL DEFAULT 'board';
END IF;
END $$;
DO $$ BEGIN
IF to_regclass('chat_message_reminder') IS NOT NULL THEN
ALTER TABLE chat_message_reminder ADD COLUMN IF NOT EXISTS note TEXT;
END IF;
END $$;
-- event_outbox: `status` splits the delivery outcome from the legacy
-- `published_at` marker. Existing rows start as terminal (1) so they are never
-- re-delivered; only rows still pending flip to 0. New inserts default to 0.
DO $$ BEGIN
IF to_regclass('event_outbox') IS NOT NULL THEN
ALTER TABLE event_outbox ADD COLUMN IF NOT EXISTS status SMALLINT NOT NULL DEFAULT 1;
UPDATE event_outbox SET status = 0 WHERE status = 1 AND published_at IS NULL;
ALTER TABLE event_outbox ALTER COLUMN status SET DEFAULT 0;
END IF;
END $$;
-- pull_request domain (PR API): the sync layer cannot relax NOT NULL nor add
-- NOT NULL DEFAULT columns on populated tables, so both are applied here.
DO $$ BEGIN
IF to_regclass('pull_request') IS NOT NULL THEN
ALTER TABLE pull_request ALTER COLUMN issue_id DROP NOT NULL;
ALTER TABLE pull_request ADD COLUMN IF NOT EXISTS head_repo_id UUID;
ALTER TABLE pull_request ADD COLUMN IF NOT EXISTS head_label VARCHAR;
ALTER TABLE pull_request ADD COLUMN IF NOT EXISTS is_draft BOOLEAN NOT NULL DEFAULT false;
ALTER TABLE pull_request ADD COLUMN IF NOT EXISTS merge_method SMALLINT NOT NULL DEFAULT 0;
ALTER TABLE pull_request ADD COLUMN IF NOT EXISTS squash_commit_sha VARCHAR;
ALTER TABLE pull_request ADD COLUMN IF NOT EXISTS auto_merge BOOLEAN NOT NULL DEFAULT false;
END IF;
END $$;
DO $$ BEGIN
IF to_regclass('pull_request_comment') IS NOT NULL THEN
ALTER TABLE pull_request_comment ALTER COLUMN commit_sha DROP NOT NULL;
ALTER TABLE pull_request_comment ALTER COLUMN path DROP NOT NULL;
ALTER TABLE pull_request_comment ADD COLUMN IF NOT EXISTS parent_comment_id UUID;
ALTER TABLE pull_request_comment ADD COLUMN IF NOT EXISTS original_commit_sha VARCHAR;
ALTER TABLE pull_request_comment ADD COLUMN IF NOT EXISTS side VARCHAR;
ALTER TABLE pull_request_comment ADD COLUMN IF NOT EXISTS original_line INTEGER;
ALTER TABLE pull_request_comment ADD COLUMN IF NOT EXISTS resolved_at TIMESTAMP WITH TIME ZONE;
ALTER TABLE pull_request_comment ADD COLUMN IF NOT EXISTS resolved_by UUID;
END IF;
END $$;
DO $$ BEGIN
IF to_regclass('repo_branch_protection_policy') IS NOT NULL THEN
ALTER TABLE repo_branch_protection_policy
ADD COLUMN IF NOT EXISTS required_status_contexts VARCHAR NOT NULL DEFAULT '';
END IF;
END $$;
-- 2026-10-31: local users table keeps only KenApi-owned settings. This column
-- must exist before SeaORM sync adds it as NOT NULL on an existing table.
ALTER TABLE IF EXISTS users
ADD COLUMN IF NOT EXISTS local_avatar_url text NOT NULL DEFAULT '';