194c44c
-- schema_extras.sql
-- Idempotent PostgreSQL DDL for constraints that SeaORM entity-first `sync`
-- cannot express via entity attributes. Applies after every `sync` when
-- auto_migrate is enabled (historical convention: lib/db/schema.rs; the db
-- crate is currently removed and must apply this file when restored).
-- Safe to re-run on every startup; every statement is guarded with
-- IF NOT EXISTS / DO-block checks.
-- ---------------------------------------------------------------------------
-- Partial unique indexes (polymorphic targets, WHERE-clause filtered).
-- Not expressible as #[sea_orm(unique_key)] — overlapping columns and partial
-- predicates have no entity attribute. The plain unique indexes declared in
-- the models (issue_watch.issue_user / project_user, issue_reaction.*) cover
-- non-NULL rows; these partial variants additionally guard the NULL-tolerant
-- lookup patterns the sync layer would otherwise drop.
-- ---------------------------------------------------------------------------
CREATE UNIQUE INDEX IF NOT EXISTS idx_issue_watch_issue_user
ON issue_watch (issue_id, user_id) WHERE issue_id IS NOT NULL;
CREATE UNIQUE INDEX IF NOT EXISTS idx_issue_watch_project_user
ON issue_watch (project_id, user_id) WHERE project_id IS NOT NULL;
CREATE UNIQUE INDEX IF NOT EXISTS idx_issue_reaction_issue
ON issue_reaction (issue_id, user_id, emoji) WHERE issue_id IS NOT NULL;
CREATE UNIQUE INDEX IF NOT EXISTS idx_issue_reaction_comment
ON issue_reaction (comment_id, user_id, emoji) WHERE comment_id IS NOT NULL;
CREATE UNIQUE INDEX IF NOT EXISTS idx_issue_reaction_project
ON issue_reaction (project_id, user_id, emoji) WHERE project_id IS NOT NULL;
CREATE UNIQUE INDEX IF NOT EXISTS idx_branch_protection_whitelist_user
ON repo_branch_protection_whitelist (policy_id, kind, user_id) WHERE user_id IS NOT NULL;
CREATE UNIQUE INDEX IF NOT EXISTS idx_branch_protection_whitelist_group
ON repo_branch_protection_whitelist (policy_id, kind, group_id) WHERE group_id IS NOT NULL;
-- ---------------------------------------------------------------------------
-- Composite non-unique indexes (entity attributes only express single-column
-- indexed/unique; lookups below span two columns).
-- ---------------------------------------------------------------------------
CREATE INDEX IF NOT EXISTS idx_comment_parent_id ON comment (parent_id);
CREATE INDEX IF NOT EXISTS idx_git_commit_status_sha ON git_commit_status (repo_id, sha);
CREATE INDEX IF NOT EXISTS idx_user_session_user_created ON user_session (user_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_user_session_expires ON user_session (expires_at);
-- Single-column indexes for reserved project_id columns (parity with the
-- historical project module; columns are kept until project is restored).
CREATE INDEX IF NOT EXISTS idx_comment_project ON comment (project_id);
CREATE INDEX IF NOT EXISTS idx_issue_reaction_uniq_proj ON issue_reaction (project_id);
CREATE INDEX IF NOT EXISTS idx_issue_watch_project ON issue_watch (project_id);
CREATE INDEX IF NOT EXISTS idx_issue_event_project ON issue_event_line (project_id);
CREATE INDEX IF NOT EXISTS idx_issue_pin_project ON issue_pin (project_id);
-- ---------------------------------------------------------------------------
-- CHECK constraints (polymorphic XOR / exactly-one / range guards).
-- ALTER TABLE ... ADD CONSTRAINT has no IF NOT EXISTS, so guard each with a
-- DO block against pg_constraint.
-- ---------------------------------------------------------------------------
DO $$ BEGIN
IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'comment_target_xor') THEN
ALTER TABLE comment
ADD CONSTRAINT comment_target_xor
CHECK ((issue_id IS NOT NULL) <> (project_id IS NOT NULL));
END IF;
END $$;
DO $$ BEGIN
IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'issue_reaction_target_exactly_one') THEN
ALTER TABLE issue_reaction
ADD CONSTRAINT issue_reaction_target_exactly_one
CHECK (((issue_id IS NOT NULL)::int + (comment_id IS NOT NULL)::int + (project_id IS NOT NULL)::int) = 1);
END IF;
END $$;
DO $$ BEGIN
IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'issue_watch_target_xor') THEN
ALTER TABLE issue_watch
ADD CONSTRAINT issue_watch_target_xor
CHECK ((issue_id IS NOT NULL) <> (project_id IS NOT NULL));
END IF;
END $$;
DO $$ BEGIN
IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'issue_event_target_xor') THEN
ALTER TABLE issue_event_line
ADD CONSTRAINT issue_event_target_xor
CHECK ((issue_id IS NOT NULL) <> (project_id IS NOT NULL));
END IF;
END $$;
DO $$ BEGIN
IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'issue_pin_target_xor') THEN
ALTER TABLE issue_pin
ADD CONSTRAINT issue_pin_target_xor
CHECK ((issue_id IS NOT NULL) <> (project_id IS NOT NULL));
END IF;
END $$;
DO $$ BEGIN
IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'branch_protection_whitelist_principal_xor') THEN
ALTER TABLE repo_branch_protection_whitelist
ADD CONSTRAINT branch_protection_whitelist_principal_xor
CHECK ((user_id IS NOT NULL) <> (group_id IS NOT NULL));
END IF;
END $$;
DO $$ BEGIN
IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'product_review_rating_range') THEN
ALTER TABLE product_review
ADD CONSTRAINT product_review_rating_range
CHECK (rating BETWEEN 1 AND 5);
END IF;
END $$;
DO $$ BEGIN
IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'products_price_non_negative') THEN
ALTER TABLE products
ADD CONSTRAINT products_price_non_negative
CHECK (price IS NULL OR price >= 0);
END IF;
END $$;
-- ---------------------------------------------------------------------------
-- Chat domain (lib/chat): message ordering, idempotency, FTS, and composite
-- indexes that entity attributes cannot express.
-- ---------------------------------------------------------------------------
CREATE INDEX IF NOT EXISTS idx_chat_message_thread_seq
ON chat_message (thread_id, seq) WHERE thread_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_chat_message_author_created
ON chat_message (author_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_chat_message_mentions
ON chat_message USING gin (mentions);
CREATE INDEX IF NOT EXISTS idx_chat_message_pinned
ON chat_message (room_id, seq DESC) WHERE pinned;
CREATE INDEX IF NOT EXISTS idx_chat_message_edit_message
ON chat_message_edit (message_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_chat_thread_room
ON chat_thread (room_id, archived, last_reply_at DESC NULLS LAST);
CREATE INDEX IF NOT EXISTS idx_chat_thread_member_user
ON chat_thread_member (user_id);
CREATE INDEX IF NOT EXISTS idx_chat_read_state_user
ON chat_read_state (user_id);
CREATE INDEX IF NOT EXISTS idx_chat_room_setting_user
ON chat_room_user_setting (user_id);
CREATE INDEX IF NOT EXISTS idx_chat_reaction_message
ON chat_reaction (message_id);
CREATE INDEX IF NOT EXISTS idx_chat_attachment_user_created
ON chat_attachment (user_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_chat_bookmark_room
ON chat_room_bookmark (room_id, position);
CREATE INDEX IF NOT EXISTS idx_chat_reminder_due
ON chat_message_reminder (status, remind_at);
CREATE UNIQUE INDEX IF NOT EXISTS idx_chat_draft_unique
ON chat_user_draft (user_id, room_id, thread_id) NULLS NOT DISTINCT;
CREATE UNIQUE INDEX IF NOT EXISTS idx_space_join_request_pending
ON space_join_request (space_id, user_id) WHERE status = 'pending';
CREATE INDEX IF NOT EXISTS idx_chat_webhook_token
ON chat_room_webhook (token_hash);
CREATE UNIQUE INDEX IF NOT EXISTS idx_chat_dm_pair
ON rooms (lower_name) WHERE kind = 15 AND space_id IS NULL;
CREATE UNIQUE INDEX IF NOT EXISTS idx_chat_gdm_key
ON rooms (lower_name) WHERE kind = 1 AND space_id IS NULL;
CREATE UNIQUE INDEX IF NOT EXISTS idx_chat_message_room_nonce
ON chat_message (room_id, author_id, nonce) WHERE nonce IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_chat_outbound_webhook_room
ON chat_outbound_webhook (room_id);
CREATE INDEX IF NOT EXISTS idx_chat_webhook_delivery_due
ON chat_webhook_delivery (status, next_retry_at);
ALTER TABLE chat_message ADD COLUMN IF NOT EXISTS search_vector tsvector;
CREATE INDEX IF NOT EXISTS idx_chat_message_fts
ON chat_message USING gin (search_vector);
CREATE OR REPLACE FUNCTION chat_message_search_vector_update() RETURNS trigger AS $$
BEGIN
NEW.search_vector := to_tsvector('simple', coalesce(NEW.content, ''));
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
DROP TRIGGER IF EXISTS chat_message_search_vector_trigger ON chat_message;
CREATE TRIGGER chat_message_search_vector_trigger
BEFORE INSERT OR UPDATE OF content ON chat_message
FOR EACH ROW EXECUTE FUNCTION chat_message_search_vector_update();
UPDATE chat_message SET search_vector = to_tsvector('simple', coalesce(content, ''))
WHERE search_vector IS NULL;
-- phase-2 data backfills (idempotent)
UPDATE repo_watch SET mode = 0 WHERE mode = 1;
UPDATE issue_watch SET mode = 0 WHERE mode = 1;
UPDATE room_members rm SET role = 3
FROM rooms r WHERE rm.room_id = r.id AND rm.user_id = r.owner_id AND rm.left_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_workflow_run_repo
ON workflow_run (repo_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_workflow_run_concurrency
ON workflow_run (repo_id, concurrency_group) WHERE concurrency_group IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_workflow_run_commit
ON workflow_run (repo_id, commit_sha);
CREATE INDEX IF NOT EXISTS idx_workflow_job_claim
ON workflow_job (state, created_at);
CREATE INDEX IF NOT EXISTS idx_workflow_job_lease
ON workflow_job (lease_expires_at) WHERE lease_expires_at IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_workflow_step_job
ON workflow_step (job_id, step_index);
CREATE INDEX IF NOT EXISTS idx_ci_log_chunk_job
ON ci_log_chunk (job_id, seq);
CREATE INDEX IF NOT EXISTS idx_ci_secret_repo
ON ci_secret (repo_id, name) WHERE repo_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_ci_secret_space
ON ci_secret (space_id, name) WHERE space_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_ci_secret_environment
ON ci_secret (environment_id, name) WHERE environment_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_ci_environment_space
ON ci_environment (space_id, name);
CREATE INDEX IF NOT EXISTS idx_ci_deployment_environment
ON ci_deployment (environment_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_repo_webhook_delivery_due
ON repo_webhook_delivery (status, next_retry_at);
UPDATE user_access_key SET scope = 'write' WHERE scope IN ('repo', 'all');
UPDATE user_access_key SET scope = 'read' WHERE scope NOT IN ('read', 'write');
-- ---------------------------------------------------------------------------
-- Pull Request domain (lib/pulls): list/filter lookups and inline-comment
-- shape guards that entity attributes cannot express.
-- ---------------------------------------------------------------------------
CREATE INDEX IF NOT EXISTS idx_pull_request_repo_state
ON pull_request (repo_id, has_merged, closed_at);
CREATE INDEX IF NOT EXISTS idx_pull_request_repo_author
ON pull_request (repo_id, author_id);
CREATE INDEX IF NOT EXISTS idx_pull_request_head
ON pull_request (head_repo_id, head_branch);
CREATE INDEX IF NOT EXISTS idx_pull_request_author_created
ON pull_request (author_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_pull_request_issue
ON pull_request (issue_id);
CREATE INDEX IF NOT EXISTS idx_pull_request_review_pr
ON pull_request_review (pull_request_id, created_at);
CREATE INDEX IF NOT EXISTS idx_pull_request_review_user
ON pull_request_review (user_id, pull_request_id);
CREATE INDEX IF NOT EXISTS idx_pull_request_comment_pr
ON pull_request_comment (pull_request_id, created_at);
CREATE INDEX IF NOT EXISTS idx_pull_request_comment_file
ON pull_request_comment (pull_request_id, path, line);
CREATE INDEX IF NOT EXISTS idx_pull_request_comment_parent
ON pull_request_comment (parent_comment_id);
CREATE INDEX IF NOT EXISTS idx_pull_request_reviewer_user
ON pull_request_reviewer (user_id, state);
CREATE INDEX IF NOT EXISTS idx_pull_request_reviewer_pr
ON pull_request_reviewer (pull_request_id, state);
CREATE INDEX IF NOT EXISTS idx_pull_request_assignee_user
ON pull_request_assignee (user_id);
CREATE INDEX IF NOT EXISTS idx_pull_request_event_pr
ON pull_request_event_line (pull_request_id, created_at);
CREATE INDEX IF NOT EXISTS idx_pull_request_label_pr
ON pull_request_label (pull_request_id);
CREATE INDEX IF NOT EXISTS idx_issue_relation_pull_pr
ON issue_relation_pull (pull_request_id);
DO $$ BEGIN
IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'pull_request_comment_inline_shape') THEN
ALTER TABLE pull_request_comment
ADD CONSTRAINT pull_request_comment_inline_shape
CHECK ((path IS NULL) = (commit_sha IS NULL));
END IF;
END $$;
DO $$ BEGIN
IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'pull_request_comment_side') THEN
ALTER TABLE pull_request_comment
ADD CONSTRAINT pull_request_comment_side
CHECK (side IS NULL OR side IN ('left', 'right'));
END IF;
END $$;
DO $$ BEGIN
IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'pull_request_merged_consistent') THEN
ALTER TABLE pull_request
ADD CONSTRAINT pull_request_merged_consistent
CHECK ((has_merged = false) OR merged_at IS NOT NULL);
END IF;
END $$;
CREATE INDEX IF NOT EXISTS idx_ci_runner_state
ON ci_runner (state, last_seen_at DESC);
CREATE UNIQUE INDEX IF NOT EXISTS idx_ci_run_event_key ON workflow_run(event_key) WHERE event_key IS NOT NULL;
CREATE UNIQUE INDEX IF NOT EXISTS idx_ci_job_run_key ON workflow_job(run_id,job_key);
CREATE UNIQUE INDEX IF NOT EXISTS idx_ci_step_key_stage ON workflow_step(job_id,step_key,stage) WHERE step_key IS NOT NULL;
CREATE UNIQUE INDEX IF NOT EXISTS idx_ci_env_repo_name ON ci_environment(repo_id,name) WHERE repo_id IS NOT NULL;
CREATE UNIQUE INDEX IF NOT EXISTS idx_ci_schedule_key ON ci_actions_schedule(repo_id,workflow_file,cron);
CREATE UNIQUE INDEX IF NOT EXISTS idx_ci_variable_name ON ci_variable(repo_id,name);
CREATE UNIQUE INDEX IF NOT EXISTS idx_ci_actions_cache_key ON ci_actions_cache(repo_id,git_ref,key,version);
-- ---------------------------------------------------------------------------
-- search index queue: coalesce duplicate queued jobs per (kind, target) and
-- arrive-once state per repository for incremental code indexing.
-- ---------------------------------------------------------------------------
DELETE FROM search_index_job s
USING search_index_job d
WHERE s.state = 'queued'
AND d.state = 'queued'
AND s.kind = d.kind
AND s.target_id IS NOT DISTINCT FROM d.target_id
AND (s.created_at > d.created_at OR (s.created_at = d.created_at AND s.id > d.id));
CREATE UNIQUE INDEX IF NOT EXISTS uniq_search_index_job_queued
ON search_index_job (kind, COALESCE(target_id, '00000000-0000-0000-0000-000000000000'::uuid))
WHERE state = 'queued';
DO $$ BEGIN
IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'search_repo_state_repo_id_fkey') THEN
ALTER TABLE search_repo_state
ADD CONSTRAINT search_repo_state_repo_id_fkey
FOREIGN KEY (repo_id) REFERENCES repos(id) ON DELETE CASCADE;
END IF;
END $$;