3774060
ZhenYi
fix(session): persist store on login and pow issue, validate __Host- cookie prefix rules
3774060
-- 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 $$;