-- +goose Up
-- Add the missing composite foreign key on aedt_candidate_notices.disclosed_by. Like the candidate FK
-- already on this table, it leads with organization_id, so the discloser must be a user in the SAME
-- organization (a cross tenant reference is impossible) and a user delete sets the column null rather
-- than orphaning the row. disclosed_by is nullable; under the default match rule a null column leaves the
-- constraint satisfied, so existing rows without a discloser are unaffected.
ALTER TABLE aedt_candidate_notices
    ADD CONSTRAINT fk_notice_disclosed_by FOREIGN KEY (organization_id, disclosed_by)
        REFERENCES users (organization_id, id) ON DELETE SET NULL;

-- +goose Down
ALTER TABLE aedt_candidate_notices DROP CONSTRAINT IF EXISTS fk_notice_disclosed_by;