Delete revisions in record.

[koksikus]

Hi,
how to correctly remove revisions from the database. Let's assume that with one record I stupidly clicked many times and I already have 20 revisions and I would like to reset it or leave the last one as the first. I need to delete a record in both the revision database and the activity database? Somewhere else too? Can it be done through Directus?
What if the revisions base grows too much? Do you only use SQL to delete old redundant data...

[benhaynes]

I defer to @rijkvanzanten on any foreign key constraints that might inhibit this. We probably won't add a way to delete revisions in Directus, since it's used for accountability.

[rijkvanzanten]

We probably won't add a way to delete revisions in Directus, since it's used for accountability.

This is correct. There's currently no way to truncate / delete from those tables from Directus itself. That being said, you can safely delete anything from directus_revisions without running into key constraints

[koksikus]

Thank you!!!!! Actually I can delete without problems also in activity. I just need to create SQL code well to remove unnecessary data in both databases at the same time.

[rijkvanzanten]

Yeah! Revisions has a foreign key that points to the activity record, so that's the only relationship to be aware of. You can delete revisions while leaving activity, or delete both at the same time. You cannot delete activity and leave revisions 👍🏻

[sonyarianto]

great, this is makes my Directus light, I disable all revisions settings on all collections :)

[Cyon85]

What if the revisions base grows too much? Do you only use SQL to delete old redundant data...

we implement a schedule-hook, where we define the retention rules ...
for example:

import {parseFilter} from '@directus/shared/utils';

export default async ({schedule}, {services, getSchema, logger}) => {
	const {ItemsService} = services;

	const activityService = new ItemsService('directus_activity', {schema: await getSchema()});

	const getReadyToDeleteActivities = async (action, collection, pastTime, limit) => {
		const _sort = ['id'];
		const _limit = limit;

		const _filter = {
			_and: [
				parseFilter({
					timestamp: {
						_lte: '$NOW(-' + pastTime + ')',
					}
				}, null),
				{
					action: {
						_eq: action
					}
				},
				{
					collection: {
						_eq: collection
					}
				}
			],
		}

		const records = await activityService.readByQuery({
			fields: ['id'],
			filter: _filter,
			limit: _limit,
			sort: _sort
		});
		return records;
	};

	const cleanupActivities = async (action, collection, pastTime, limit) => {
		try {
			await activityService.deleteMany(
				await getReadyToDeleteActivities(action, collection, pastTime, limit)
					.then((res) => {
						return res.map(function (elem) {
							return elem.id;
						});
					})
			);
		} catch (error) {
			logger.error(error);
		}
	};

	schedule('0 * * * *', async () => {
		await cleanupActivities('login', 'directus_users', '30 days', 100);
	});
	schedule('*/1 * * * *', async () => {
		await cleanupActivities('create', 'mycollection', '10 days', 100);
		await cleanupActivities('update', 'mycollection', '1 day', 50);
	});

};

to get this work, we need to set the parent field to 'null' onDelete-Event because there can be a dependency for files ...
See this discussion: #13481