30c47b3
-- schema_extras.sql
-- Idempotent PostgreSQL DDL for constraints that SeaORM entity-first `sync`
-- cannot express via entity attributes. Runs after every `sync` when
-- auto_migrate is enabled (see lib/db/schema.rs). Safe to re-run on every
-- startup; every statement is guarded with IF NOT EXISTS / DO-block checks.
--
-- This is the single hand-maintained SQL file kept after the old
-- lib/db/migrations/ folder was removed (entity-first strategy).
-- ---------------------------------------------------------------------------
-- Legacy tables dropped by the channel-tree refactor (no entities remain).
-- `sync` is non-destructive, so these must be dropped here.
-- ---------------------------------------------------------------------------
DROP TABLE IF EXISTS channel_members;
DROP TABLE IF EXISTS chat_members;
-- ---------------------------------------------------------------------------
-- Composite non-unique indexes (entity attributes are single-column only).
-- ---------------------------------------------------------------------------
CREATE INDEX IF NOT EXISTS idx_project_space_order ON project (space_id, sort_order);
CREATE INDEX IF NOT EXISTS idx_project_views_project ON project_views (project_id, sort_order);
CREATE INDEX IF NOT EXISTS idx_project_activities_feed ON project_activities (project_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_project_activities_actor ON project_activities (actor_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_project_favorites_user ON project_favorites (user_id, created_at DESC);
-- Single-column indexes from the removed migrations (parity on fresh DBs).
CREATE INDEX IF NOT EXISTS idx_project_members_user ON project_members (user_id);
CREATE INDEX IF NOT EXISTS idx_project_members_project ON project_members (project_id);
CREATE INDEX IF NOT EXISTS idx_project_views_creator ON project_views (creator_id);
CREATE INDEX IF NOT EXISTS idx_project_documents_project ON project_documents (project_id);
CREATE INDEX IF NOT EXISTS idx_project_documents_parent ON project_documents (parent_id);
CREATE INDEX IF NOT EXISTS idx_project_links_project ON project_links (project_id);
CREATE INDEX IF NOT EXISTS idx_project_links_related_project ON project_links (related_project_id);
CREATE INDEX IF NOT EXISTS idx_project_state ON project (state);
CREATE INDEX IF NOT EXISTS idx_project_target_date ON project (target_date);
CREATE INDEX IF NOT EXISTS idx_project_lead ON project (lead_id);
CREATE INDEX IF NOT EXISTS idx_comment_parent_id ON comment (parent_id);
CREATE INDEX IF NOT EXISTS idx_comment_project ON comment (project_id);
CREATE INDEX IF NOT EXISTS idx_issue_reaction_project 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 targets).
-- 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 = 'project_links_not_self') THEN
ALTER TABLE project_links
ADD CONSTRAINT project_links_not_self CHECK (project_id <> related_project_id);
END IF;
END $$;
-- ---------------------------------------------------------------------------
-- Payment composite indexes (multi-column; entity attributes are single-column).
-- ---------------------------------------------------------------------------
CREATE INDEX IF NOT EXISTS idx_payment_transaction_ref ON payment_transaction (ref_type, ref_id);
CREATE INDEX IF NOT EXISTS idx_payment_webhook_status_created ON payment_webhook_event (status, created_at);
-- ---------------------------------------------------------------------------
-- LiveKit meet / participant indexes.
-- Partial unique index: each channel has at most one active meet at a time.
-- The `idx_meet_room_name` non-partial unique is owned by entity
-- (`#[sea_orm(unique_key = "room_name")]` on meet.room_name). The
-- `idx_meet_active_per_channel` partial unique is the only remaining
-- schema-extras-owned unique index and is recreated below after the entity
-- sync pass; on a fresh DB it is the source of truth, while on existing DBs
-- the pre-sync `DROP INDEX IF EXISTS` in the cleanup section above resets
-- the index to match the entity-declared non-partial unique.
-- ---------------------------------------------------------------------------
CREATE UNIQUE INDEX IF NOT EXISTS idx_meet_active_per_channel ON meet (channel_id) WHERE status = 1;
-- ---------------------------------------------------------------------------
-- repo_branch_protection_policy additions for H6 (pre-receive enforcement).
-- Both columns are non-breaking; existing rows get `enable_force_push=false`
-- and `branch_pattern=NULL`. `sync` is non-destructive on existing columns,
-- so for environments with `auto_migrate: false` these ALTERs make the
-- columns available immediately on next startup.
-- ---------------------------------------------------------------------------
ALTER TABLE repo_branch_protection_policy
ADD COLUMN IF NOT EXISTS enable_force_push BOOLEAN NOT NULL DEFAULT FALSE;
ALTER TABLE repo_branch_protection_policy
ADD COLUMN IF NOT EXISTS branch_pattern TEXT NULL;
CREATE INDEX IF NOT EXISTS idx_meet_participants_meet_user ON meet_participants (meet_id, user_id);
CREATE INDEX IF NOT EXISTS idx_meet_participants_user_joined ON meet_participants (user_id, joined_at DESC);