directus-cleanup-history.sql

/*
DISCLAIMER: Use at your own risk!

Discussion: https://github.com/directus/directus/discussions/18297#discussioncomment-5695833
Based on: https://gist.github.com/lukas-schaetzle/f93eff3d961ac595d0e1ab4be5f34536

Tested with Directus 10.11.2 and PostgreSQL

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.
*/

BEGIN;

-- Ensure indexes are in place for performance
CREATE INDEX IF NOT EXISTS idx_activity_timestamp ON directus_activity (timestamp);
CREATE INDEX IF NOT EXISTS idx_revisions_activity ON directus_revisions (activity);
CREATE INDEX IF NOT EXISTS idx_revisions_item_collection ON directus_revisions (item, collection);
CREATE INDEX IF NOT EXISTS idx_notifications_timestamp ON directus_notifications (timestamp);

DO $$
DECLARE
    MinFlowLogs INT := 10;
    MinRevisions INT := 10;
    MinActivities INT := 300;
    MinTimestamp TIMESTAMP := CURRENT_DATE - INTERVAL '2 weeks';
BEGIN
    -- Change revisions 'parent' constraint
    ALTER TABLE directus_revisions 
    DROP CONSTRAINT IF EXISTS 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 using temporary tables
    CREATE TEMP TABLE temp_flow_logs AS
    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 TEMP TABLE temp_normal_revisions AS
    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 FROM directus_revisions
    WHERE id IN (
        SELECT revisions_id
        FROM temp_flow_logs
        WHERE timestamp < MinTimestamp
          AND row_number > MinFlowLogs
    );

    -- Delete old normal (no flow logs) revisions
    DELETE FROM directus_revisions
    WHERE id IN (
        SELECT revisions_id
        FROM temp_normal_revisions
        WHERE timestamp < MinTimestamp
          AND row_number > MinRevisions
    );

    -- Count activities and use a temporary table
    CREATE TEMP TABLE temp_activities AS
    SELECT
        id,
        timestamp,
        ROW_NUMBER() OVER (ORDER BY timestamp DESC) AS row_number
    FROM directus_activity;

    -- Delete old activities
    DELETE FROM directus_activity
    WHERE id IN (
        SELECT id
        FROM temp_activities
        WHERE timestamp < MinTimestamp
          AND row_number > MinActivities
          AND id NOT IN (
              SELECT activity_id FROM temp_normal_revisions
              UNION
              SELECT activity_id FROM temp_flow_logs
          )
    );

    -- 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 IF EXISTS 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;

END $$;

COMMIT;

-- Drop temporary tables
DROP TABLE IF EXISTS temp_flow_logs;
DROP TABLE IF EXISTS temp_normal_revisions;
DROP TABLE IF EXISTS temp_activities;


BEGIN;

-- Ensure indexes are in place for performance
CREATE INDEX IF NOT EXISTS idx_activity_timestamp ON directus_activity (timestamp);
CREATE INDEX IF NOT EXISTS idx_revisions_activity ON directus_revisions (activity);
CREATE INDEX IF NOT EXISTS idx_revisions_item_collection ON directus_revisions (item, collection);
CREATE INDEX IF NOT EXISTS idx_notifications_timestamp ON directus_notifications (timestamp);

DO $$
DECLARE
    MinFlowLogs INT := 10;
    MinRevisions INT := 10;
    MinActivities INT := 300;
    MinTimestamp TIMESTAMP := CURRENT_DATE - INTERVAL '2 weeks';
BEGIN
    -- Change revisions 'parent' constraint
    ALTER TABLE directus_revisions 
    DROP CONSTRAINT IF EXISTS 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 using temporary tables
    CREATE TEMP TABLE temp_flow_logs AS
    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 TEMP TABLE temp_normal_revisions AS
    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 FROM directus_revisions
    WHERE id IN (
        SELECT revisions_id
        FROM temp_flow_logs
        WHERE timestamp < MinTimestamp
          AND row_number > MinFlowLogs
    );

    -- Delete old normal (no flow logs) revisions
    DELETE FROM directus_revisions
    WHERE id IN (
        SELECT revisions_id
        FROM temp_normal_revisions
        WHERE timestamp < MinTimestamp
          AND row_number > MinRevisions
    );

    -- Count activities and use a temporary table
    CREATE TEMP TABLE temp_activities AS
    SELECT
        id,
        timestamp,
        ROW_NUMBER() OVER (ORDER BY timestamp DESC) AS row_number
    FROM directus_activity;

    -- Delete old activities
    DELETE FROM directus_activity
    WHERE id IN (
        SELECT id
        FROM temp_activities
        WHERE timestamp < MinTimestamp
          AND row_number > MinActivities
          AND id NOT IN (
              SELECT activity_id FROM temp_normal_revisions
              UNION
              SELECT activity_id FROM temp_flow_logs
          )
    );

    -- 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 IF EXISTS 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;

END $$;

COMMIT;

-- Drop temporary tables
DROP TABLE IF EXISTS temp_flow_logs;
DROP TABLE IF EXISTS temp_normal_revisions;
DROP TABLE IF EXISTS temp_activities;