a8n: DB trigger to clean up references to campaigns/changesets on deletion

Created by: mrnugget

Before this change, if you'd remove an external_service that delete would cascade and also delete the repos associated with the external_service.

That in turn would also cascade and delete the changeset whose repo_id matches the deleted repo.

There are still a lot of unanswered questions around deletion in the a8n subparts of the system (e.g.: should we delete a campaign if the external service was deleted, and possibly recreated?).

Until we answer those and come up with a full strategy around deletion, we try to at least not leave invalid state around when we delete something.

To do that without switching to a JOIN table we add two triggers to the changesets and campaigns tables respectively that delete references to each other in case a row gets deleted.

Example: if a changeset is deleted, the trigger will remove its ID from the changeset_ids columns of campaigns to which it has been added.

cc @lguychard: I think we should cherry-pick this onto 3.8 along with the other a8n changes we make this week. A8N itself is behind a non-public feature-flag, so it's safe for us to.

Testing: I used BEGIN;/ROLLBACK; to test the triggers and the migration to drop them.

```sql BEGIN;

-- When we delete a changeset we remove its ID from the changeset_ids -- column on campaigns CREATE OR REPLACE FUNCTION delete_changeset_reference_on_campaigns() RETURNS TRIGGER AS delete_changeset_reference_on_campaigns BEGIN UPDATE campaigns SET changeset_ids = campaigns.changeset_ids - OLD.id::text WHERE campaigns.changeset_ids ? OLD.id::text;

    RETURN OLD;
END;

delete_changeset_reference_on_campaigns LANGUAGE plpgsql;

CREATE TRIGGER trig_delete_changeset_reference_on_campaigns BEFORE DELETE on changesets FOR EACH ROW EXECUTE PROCEDURE delete_changeset_reference_on_campaigns();

-- The reverse: -- When we delete a campaign we remove its ID from the campaign_ids -- column on changesets CREATE OR REPLACE FUNCTION delete_campaign_reference_on_changesets() RETURNS TRIGGER AS delete_campaign_reference_on_changesets BEGIN UPDATE changesets SET campaign_ids = changesets.campaign_ids - OLD.id::text WHERE changesets.campaign_ids ? OLD.id::text;

    RETURN OLD;
END;

delete_campaign_reference_on_changesets LANGUAGE plpgsql;

CREATE TRIGGER trig_delete_campaign_reference_on_changesets BEFORE DELETE on campaigns FOR EACH ROW EXECUTE PROCEDURE delete_campaign_reference_on_changesets();

--- TESTING

SELECT id, campaign_ids FROM changesets; SELECT id, changeset_ids FROM campaigns;

DELETE FROM campaigns WHERE id = 1;

SELECT id, campaign_ids FROM changesets;

DROP TRIGGER IF EXISTS trig_delete_changeset_reference_on_campaigns ON changesets; DROP FUNCTION IF EXISTS delete_changeset_reference_on_campaigns();

DROP TRIGGER IF EXISTS trig_delete_campaign_reference_on_changesets ON campaigns; DROP FUNCTION IF EXISTS delete_campaign_reference_on_changesets();

ROLLBACK;

</details>