1588610
-- 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;
-- ---------------------------------------------------------------------------
-- Partial unique indexes (polymorphic targets, WHERE-clause filtered).
-- Not expressible as #[sea_orm(unique_key)] — overlapping columns and partial
-- predicates have no entity attribute. `sync` drops undeclared unique indexes,
-- so these are recreated here on every startup.
-- ---------------------------------------------------------------------------
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_project_favorites_unique
ON project_favorites (project_id, user_id, COALESCE(folder, ''));
-- ---------------------------------------------------------------------------
-- 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.
-- ---------------------------------------------------------------------------
CREATE UNIQUE INDEX IF NOT EXISTS idx_meet_active_per_channel ON meet (channel_id) WHERE status = 1;
CREATE UNIQUE INDEX IF NOT EXISTS idx_meet_room_name ON meet (room_name);
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);