Directus (MySQL): Remove old activities, revisions, flow logs, notifications

directus-cleaning.sql

/*
DISCLAIMER: Use at your own risk!
Tested with Directus 9.23.4 and MySQL

This will delete all flow execution logs, revisions and acitivites which are older
than the specified MinTimestamp and are not in the top <specified MinItems> latest
items. Activities related to saved revisions / flow logs are also saved even when
they normally would have been deleted.

Archived notifiactions are deleted when the creation of the notifaction is older
than the specified MinTimestamp.

Feel free to adjust the 4 variables at the start to your liking.
*/

START TRANSACTION;

SET @MinFlowLogs = 10;
SET @MinRevisions = 10;
SET @MinActivities = 300;
SET @MinTimestamp = DATE_ADD(CURRENT_DATE(), INTERVAL -2 WEEK);

-- Change revisions 'parent' constraint
ALTER TABLE directus_revisions 
DROP CONSTRAINT directus_revisions_parent_foreign

ALTER TABLE directus_revisions
  ADD CONSTRAINT directus_revisions_parent_foreign
    FOREIGN KEY (parent) REFERENCES directus_revisions (id)
      ON DELETE SET NULL;

-- Preselection for revisions
CREATE TEMPORARY TABLE flow_logs 
SELECT
  revisions.id AS revisions_id,
  activity.id AS activity_id,
  activity.timestamp,
  ROW_NUMBER() OVER (
    PARTITION BY revisions.item ORDER BY activity.timestamp DESC
  ) AS `row_number`
FROM directus_revisions AS revisions
INNER JOIN directus_activity AS activity
  ON activity.id = revisions.activity
WHERE action = 'run';

CREATE TEMPORARY TABLE normal_revisions
SELECT
  revisions.id AS revisions_id,
  activity.id AS activity_id,
  activity.timestamp,
  ROW_NUMBER() OVER (
    PARTITION BY revisions.item, revisions.collection ORDER BY activity.timestamp DESC
  ) AS `row_number`
FROM directus_revisions AS revisions
INNER JOIN directus_activity AS activity
  ON activity.id = revisions.activity
WHERE action != 'run';

-- Delete old flow logs
DELETE revisions, flow_logs
FROM flow_logs
INNER JOIN directus_revisions AS revisions
  ON revisions.id = flow_logs.revisions_id
WHERE flow_logs.timestamp < @MinTimestamp
  AND flow_logs.row_number > @MinFlowLogs;

-- Delete old normal (no flow logs) revisions
DELETE revisions, normal_revisions
FROM normal_revisions
INNER JOIN directus_revisions AS revisions
  ON revisions.id = normal_revisions.revisions_id
WHERE normal_revisions.timestamp < @MinTimestamp
  AND normal_revisions.row_number > @MinRevisions;

-- Count activities
CREATE TEMPORARY TABLE activities
SELECT
  id,
  ROW_NUMBER() OVER (ORDER BY `timestamp` DESC) AS `row_number`
FROM directus_activity;

-- Delete old activities
DELETE directus_activity
FROM directus_activity
INNER JOIN activities
  ON directus_activity.id = activities.id
WHERE `timestamp` < @MinTimestamp
  AND `row_number` > @MinActivities;
  AND directus_activity.id NOT IN (
  SELECT activity_id FROM (
    SELECT activity_id FROM normal_revisions
    UNION
    SELECT activity_id FROM flow_logs
  ) AS saved_activities
);

-- Delete old archived notifications
DELETE FROM directus_notifications
WHERE `timestamp` < @MinTimestamp
  AND `status` = 'archived';

-- Re-add old parent constraint
ALTER TABLE directus_revisions 
DROP CONSTRAINT directus_revisions_parent_foreign;

ALTER TABLE directus_revisions
  ADD CONSTRAINT directus_revisions_parent_foreign
    FOREIGN KEY (parent) REFERENCES directus_revisions (id)
      ON UPDATE RESTRICT
      ON DELETE RESTRICT;

COMMIT;