directus-cleanup-history-with-notice.sql

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
    RAISE NOTICE '1. Starting cleanup process';

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

    RAISE NOTICE '2. Changed revisions parent constraint';

    -- 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';

    RAISE NOTICE '3. Created temporary tables for flow logs and normal revisions';

    -- 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
    );

    RAISE NOTICE '4. Deleted old flow logs';

    -- 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
    );

    RAISE NOTICE '5. Deleted old normal revisions';

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

    RAISE NOTICE '6. Created temporary table for activities';

    -- 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
          )
    );

    RAISE NOTICE '7. Deleted old activities';

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

    RAISE NOTICE '8. Deleted old archived notifications';

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

    RAISE NOTICE '9. Re-added old parent constraint';

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;

RAISE NOTICE '10. Cleanup process completed';