0d1933f
-- schema_extras.sql
-- Idempotent PostgreSQL DDL for constraints that SeaORM entity-first `sync`
-- cannot express via entity attributes. Applied by models::migrate::sync_schema
-- after each successful entity sync (fingerprint-gated), so it only runs when
-- the entity schema, one of the two SQL files, or the live database changed.
-- Safe to re-run on every schema change; 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;
-- One pending join request per (organization, user). Existing duplicates are
-- collapsed to the newest row before the unique index is (re)built.
DELETE FROM space_join_requests AS stale
USING space_join_requests AS keep
WHERE stale.organization_id = keep.organization_id
AND stale.user_id = keep.user_id
AND stale.status = 'pending'
AND keep.status = 'pending'
AND (stale.created_at, stale.id) < (keep.created_at, keep.id);
CREATE UNIQUE INDEX IF NOT EXISTS idx_space_join_requests_pending
ON space_join_requests (organization_id, user_id) WHERE status = 'pending';
-- ---------------------------------------------------------------------------
-- 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);
-- 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 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);
-- 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;
-- user_access_key is owned by KenOAuth now; GitData only normalises legacy rows
-- left in a database that still has the table.
DO $$ BEGIN
IF to_regclass('user_access_key') IS NOT NULL THEN
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');
END IF;
END $$;
-- ---------------------------------------------------------------------------
-- 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 $$;
-- ---------------------------------------------------------------------------
-- event_outbox: the relay orders per ordering_key by the DB-generated `seq`
-- (monotonic, independent of host wall clocks) and records the delivery
-- outcome in `status` (0 pending / 1 published / 2 discarded). `seq` is
-- intentionally not mapped on the entity: it is DB-owned, and only pending
-- rows need a value (already-published rows keep NULL until purged).
-- ---------------------------------------------------------------------------
DO $$
BEGIN
IF to_regclass('event_outbox') IS NOT NULL
AND NOT EXISTS (
SELECT 1 FROM information_schema.columns
WHERE table_schema = current_schema()
AND table_name = 'event_outbox'
AND column_name = 'seq'
)
THEN
ALTER TABLE event_outbox ADD COLUMN seq BIGINT;
WITH ordered AS (
SELECT id, row_number() OVER (ORDER BY occurred_at, id) AS n
FROM event_outbox WHERE status = 0
)
UPDATE event_outbox e SET seq = ordered.n FROM ordered WHERE e.id = ordered.id;
CREATE SEQUENCE IF NOT EXISTS event_outbox_seq;
PERFORM setval(
'event_outbox_seq',
GREATEST(COALESCE((SELECT max(seq) FROM event_outbox), 0) + 1, 1),
false
);
ALTER TABLE event_outbox ALTER COLUMN seq SET DEFAULT nextval('event_outbox_seq');
ALTER SEQUENCE event_outbox_seq OWNED BY event_outbox.seq;
END IF;
END $$;
-- The index definitions changed, so IF NOT EXISTS is not enough: drop the old
-- column lists first.
DROP INDEX IF EXISTS idx_event_outbox_pending;
DROP INDEX IF EXISTS idx_event_outbox_ordering;
DROP INDEX IF EXISTS idx_event_outbox_published;
CREATE INDEX IF NOT EXISTS idx_event_outbox_pending
ON event_outbox (available_at, seq) WHERE status = 0;
CREATE INDEX IF NOT EXISTS idx_event_outbox_ordering
ON event_outbox (ordering_key, seq) WHERE status = 0;
CREATE INDEX IF NOT EXISTS idx_event_outbox_published
ON event_outbox (published_at) WHERE status = 1;
CREATE INDEX IF NOT EXISTS idx_event_outbox_discarded
ON event_outbox (discarded_at) WHERE status = 2;
DO $$ BEGIN
IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'event_outbox_status_valid') THEN
ALTER TABLE event_outbox
ADD CONSTRAINT event_outbox_status_valid
CHECK (status IN (0, 1, 2));
END IF;
END $$;
-- ---------------------------------------------------------------------------
-- 2026-10-31: retire the local identity projection.
-- KenOAuth owns username / display name / email / account state / password /
-- TOTP. Only local settings and a local avatar asset remain in `users`.
-- ---------------------------------------------------------------------------
DO $$
BEGIN
IF to_regclass('users') IS NOT NULL THEN
IF EXISTS (
SELECT 1 FROM information_schema.columns
WHERE table_schema = current_schema()
AND table_name = 'users'
AND column_name = 'avatar_url'
) THEN
UPDATE users
SET local_avatar_url = avatar_url
WHERE local_avatar_url = ''
AND avatar_url LIKE 'storage://%';
END IF;
ALTER TABLE users
DROP COLUMN IF EXISTS username,
DROP COLUMN IF EXISTS lower_name,
DROP COLUMN IF EXISTS display_name,
DROP COLUMN IF EXISTS avatar_url,
DROP COLUMN IF EXISTS is_active,
DROP COLUMN IF EXISTS prohibit_login,
DROP COLUMN IF EXISTS bot,
DROP COLUMN IF EXISTS system,
DROP COLUMN IF EXISTS passwd_hash,
DROP COLUMN IF EXISTS password_last_changed_at,
DROP COLUMN IF EXISTS passwd_version,
DROP COLUMN IF EXISTS totp_encrypted_secret,
DROP COLUMN IF EXISTS totp_enabled,
DROP COLUMN IF EXISTS totp_last_used_at,
DROP COLUMN IF EXISTS totp_last_counter,
DROP COLUMN IF EXISTS created_at;
END IF;
DROP TABLE IF EXISTS user_emails CASCADE;
END $$;
-- dir.kind: legacy `Object` rows were created by the repo/issue-board domains.
-- Split them into concrete kinds so callers read the type from the dir row.
UPDATE dir SET kind = 3 WHERE kind = 1 AND id IN (SELECT dir_id FROM repos);
UPDATE dir SET kind = 2 WHERE kind = 1 AND id IN (SELECT dir_id FROM issues);
-- Channel dirs: space rooms created before the unified dir tree have no `dir`
-- row. Reuse the room id as the dir id so the mapping stays deterministic and
-- the insert can be retried safely.
INSERT INTO dir (
id, space_id, name, kind, parent_dir_id, color, position, icon, topic,
purpose, is_archived, archived_at, archived_by, created_at, updated_at,
created_by_uid
)
SELECT
r.id, r.space_id, r.name,
CASE WHEN r.kind IN (11, 12) THEN 5 ELSE 4 END,
r.parent_id, NULL, r.position, NULL, r.topic,
NULL, NOT r.is_active, NULL, NULL, r.created_at, r.updated_at,
r.owner_id
FROM rooms r
WHERE r.space_id IS NOT NULL
AND r.dir_id IS NULL
AND NOT EXISTS (SELECT 1 FROM dir d WHERE d.id = r.id);
UPDATE rooms SET dir_id = id WHERE dir_id IS NULL AND space_id IS NOT NULL;
-- Private channels move their access list onto dir overwrites: any row on a
-- dir makes the default "visible to every space member" an allow-list, so the
-- channel members and the owner need an explicit allow row.
INSERT INTO dir_overwrite_user (dir_id, user_id, allow_mask, deny_mask, expires_at)
SELECT r.dir_id, m.user_id, 1, 0, NULL
FROM rooms r
JOIN room_members m ON m.room_id = r.id
WHERE r.dir_id IS NOT NULL AND r.is_private AND m.left_at IS NULL
ON CONFLICT (dir_id, user_id) DO NOTHING;
INSERT INTO dir_overwrite_user (dir_id, user_id, allow_mask, deny_mask, expires_at)
SELECT r.dir_id, r.owner_id, 1, 0, NULL
FROM rooms r
WHERE r.dir_id IS NOT NULL AND r.is_private
ON CONFLICT (dir_id, user_id) DO NOTHING;
-- Channel moderation/access moved to dir overwrites: convert legacy bans and
-- drop the per-channel membership tables (room_members stays for DMs only).
DO $$
BEGIN
IF to_regclass('room_bans') IS NOT NULL THEN
INSERT INTO dir_overwrite_user (dir_id, user_id, allow_mask, deny_mask, expires_at)
SELECT r.dir_id, b.user_id, 0, 1, b.expires_at
FROM room_bans b
JOIN rooms r ON r.id = b.room_id
WHERE r.dir_id IS NOT NULL
ON CONFLICT (dir_id, user_id) DO UPDATE
SET deny_mask = EXCLUDED.deny_mask,
expires_at = EXCLUDED.expires_at;
DROP TABLE room_bans;
END IF;
IF to_regclass('room_invites') IS NOT NULL THEN
DROP TABLE room_invites;
END IF;
END $$;
DELETE FROM room_members
WHERE room_id IN (SELECT id FROM rooms WHERE space_id IS NOT NULL);