[Feature Request] Proper SQL cleanup #396

New Issue

OVERLORD · 2026-02-04T19:00:23+03:00

OVERLORD commented

2026-02-04 19:00:23 +03:00

Originally created by @daberlin on GitHub (Dec 17, 2023).

Seems like deleting objects leaves a lot of orphans in the DB.
Cleanup proposal:

DELETE FROM public.board             WHERE project_id NOT IN (SELECT id from public.project);
DELETE FROM public.label             WHERE board_id   NOT IN (SELECT id FROM public.board  );
DELETE FROM public.list              WHERE board_id   NOT IN (SELECT id FROM public.board  );
DELETE FROM public.card              WHERE list_id    NOT IN (SELECT id FROM public.list   );
DELETE FROM public.attachment        WHERE card_id    NOT IN (SELECT id FROM public.card   );
DELETE FROM public.card_label        WHERE card_id    NOT IN (SELECT id FROM card          )
                                        OR label_id   NOT IN (SELECT id FROM label         );
DELETE FROM public.card_membership   WHERE card_id    NOT IN (SELECT id FROM public.card   );
DELETE FROM public.card_subscription WHERE card_id    NOT IN (SELECT id FROM public.card   );

DELETE FROM public.session WHERE deleted_at IS NOT NULL;

Originally created by @daberlin on GitHub (Dec 17, 2023). Seems like deleting objects leaves a lot of orphans in the DB. Cleanup proposal: ``` DELETE FROM public.board WHERE project_id NOT IN (SELECT id from public.project); DELETE FROM public.label WHERE board_id NOT IN (SELECT id FROM public.board ); DELETE FROM public.list WHERE board_id NOT IN (SELECT id FROM public.board ); DELETE FROM public.card WHERE list_id NOT IN (SELECT id FROM public.list ); DELETE FROM public.attachment WHERE card_id NOT IN (SELECT id FROM public.card ); DELETE FROM public.card_label WHERE card_id NOT IN (SELECT id FROM card ) OR label_id NOT IN (SELECT id FROM label ); DELETE FROM public.card_membership WHERE card_id NOT IN (SELECT id FROM public.card ); DELETE FROM public.card_subscription WHERE card_id NOT IN (SELECT id FROM public.card ); DELETE FROM public.session WHERE deleted_at IS NOT NULL; ```

OVERLORD added the enhancement label 2026-02-04 19:00:23 +03:00

OVERLORD closed this issue

2026-02-04 19:00:25 +03:00

OVERLORD commented

2026-02-04 19:00:44 +03:00

@meltyshev commented on GitHub (Dec 20, 2023):

Hi! Thanks for sharing the cleanup proposal. We are planning to redesign the entire deletion process so that no related data is left behind when deleting.

@meltyshev commented on GitHub (Dec 20, 2023): Hi! Thanks for sharing the cleanup proposal. We are planning to redesign the entire deletion process so that no related data is left behind when deleting.

OVERLORD commented

2026-02-04 19:00:54 +03:00

@hwelch-fle commented on GitHub (Jan 17, 2024):

Hi! Thanks for sharing the cleanup proposal. We are planning to redesign the entire deletion process so that no related data is left behind when deleting.

Would this not be pretty easily accomplished by making sure the database is configured to cascade deletions from parent tables?

Would be pretty easy to roll out in a knex migration that adds the CASCADE keyword to related fields of child rows.

@hwelch-fle commented on GitHub (Jan 17, 2024): > Hi! Thanks for sharing the cleanup proposal. We are planning to redesign the entire deletion process so that no related data is left behind when deleting. Would this not be pretty easily accomplished by making sure the database is configured to cascade deletions from parent tables? Would be pretty easy to roll out in a knex migration that adds the CASCADE keyword to related fields of child rows.

OVERLORD commented

2026-02-04 19:01:04 +03:00

@meltyshev commented on GitHub (Jan 17, 2024):

Hi! Thanks for sharing the cleanup proposal. We are planning to redesign the entire deletion process so that no related data is left behind when deleting.

Would this not be pretty easily accomplished by making sure the database is configured to cascade deletions from parent tables?

Would be pretty easy to roll out in a knex migration that adds the CASCADE keyword to related fields of child rows.

Yes, that's one way to solve it, but then there would be a problem that we need to delete all uploaded attachments, meaning we would still have to do a separate query to get their id's before deleting them from the database. It also seems that there are no relations between tables in the database, as no foreign keys were used. Probably the easiest way would be to just add deleting related data to delete* helpers. We don't like that the data is not completely deleted now either and we'll definitely solve this problem.

@meltyshev commented on GitHub (Jan 17, 2024): > > Hi! Thanks for sharing the cleanup proposal. We are planning to redesign the entire deletion process so that no related data is left behind when deleting. > > Would this not be pretty easily accomplished by making sure the database is configured to cascade deletions from parent tables? > > Would be pretty easy to roll out in a knex migration that adds the CASCADE keyword to related fields of child rows. Yes, that's one way to solve it, but then there would be a problem that we need to delete all uploaded attachments, meaning we would still have to do a separate query to get their id's before deleting them from the database. It also seems that there are no relations between tables in the database, as no foreign keys were used. Probably the easiest way would be to just add deleting related data to delete* helpers. We don't like that the data is not completely deleted now either and we'll definitely solve this problem.

OVERLORD commented

2026-02-04 19:01:08 +03:00

@meltyshev commented on GitHub (May 13, 2025):

This should be resolved in v2, but feel free to reopen if the issue persists.

@meltyshev commented on GitHub (May 13, 2025): This should be resolved in v2, but feel free to reopen if the issue persists.

Sign in to join this conversation.

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: starred/planka#396