synctv/migrations/20260426020_create_room_bans.sql

CREATE TABLE IF NOT EXISTS room_bans (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    room_id BIGINT NOT NULL REFERENCES rooms(id) ON DELETE RESTRICT,
    banned_by BIGINT REFERENCES users(id) ON DELETE RESTRICT,
    reason TEXT,
    starts_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ends_at TIMESTAMPTZ,
    revoked_at TIMESTAMPTZ,
    revoked_by BIGINT REFERENCES users(id) ON DELETE RESTRICT
);

CREATE INDEX IF NOT EXISTS idx_room_bans_room
    ON room_bans(room_id, starts_at DESC);

CREATE INDEX IF NOT EXISTS idx_room_bans_banned_by
    ON room_bans(banned_by, starts_at DESC)
    WHERE banned_by IS NOT NULL;