Undesired state when removing a timeline from a sketch

[jaegeral]

timesketch/timesketch/api/v1/resources/timeline.py

Line 613 in ee96b46

sketch.timelines.remove(timeline)

That might result in orphaned timelines without any tie to a sketch. Might need further investigation.

[MridulTailor]

Hi @jaegeral! I can help investigate this issue.
From the code at line 613, it looks like sketch.timelines.remove(timeline) only removes the many-to-many relationship, which could leave timelines without any sketch associations.
Before proposing a fix, I wanted to confirm:

• Should timelines be automatically cleaned up when they have no more sketch associations?
• Or is there a use case where timelines should exist independently of sketches?

I'll look into the Timeline model and sketch relationships to better understand the intended behavior. Happy to submit a fix once we clarify the expected outcome!

[jaegeral]

Hi @MridulTailor, thanks for the interest in this issue!

To answer your question: Timelines in Timesketch where designed to be many-to-many. A single timeline index in OpenSearch could be associated with multiple sketches. Automatically cleaning them up could lead to data loss if another sketch is still using that index but we have moved away from that concept a while ago but still have some historical code for it.

Before you look at a PR, could you clarify:

• Have you reproduced this in a local dev environment to see if the orphaned timeline still appears?
• How would you propose handling the underlying OpenSearch index if a timeline is truly orphaned?

Since this touches the data persistence layer, I want to make sure the fix doesn't have unintended side effects for multi-tenant setups. Also because we had flyby AI / LLM contributions in the past, please indicate how much of your effort is based on AI / LLMs.

Thank you

(fwiw, I already started working on the issue as well)

[MridulTailor]

Hi @jaegeral, thanks for the guidance!

1. Yes, I've set up the local Docker environment and reproduced it. By default, sketch.timelines.remove(timeline) just removes the association. Because the SQLAlchemy relationship on Sketch uses cascade="save-update, merge, delete" (without delete orphan), removing it sets timeline.sketch_id = None in postgres, leaving that timeline row fully orphaned in the DB.

2. In TimelineResource.delete, current logic -> checks if the SearchIndex is shared across any other timelines or sketches. If it's not used anywhere else (close_index == True), it currently closes the OS index (client.indices.close()) and archives the status.
   If a timeline is truly orphaned and the index is verifiably unused, I propose that we:

• delete the Timeline from Postgres: db_session.delete(timeline)
• delete the SearchIndex row: db_session.delete(searchindex)

I am using an AI coding assistant to help search and understand the codebase quicker, I am manually making changes and running tests.

[jaegeral]

Thx for spending time on it, but I would recommend to focus on a feature or problem that is a little less exposed, the deletion of timelines and sketches is complicated and has many edge cases. As I indicated, I already started to work on the issue.