Sketch Force Deletion Leaves Orphaned Records and Causes Database Integrity Issues

[jeflagel]

Describe the bug

The force deletion feature (force_delete=True) for sketches contains critical bugs that leave the database in an inconsistent state, resulting in orphaned records, 404 errors, and foreign key constraint violations.
using the new "force delete" functionality implemented with this issue #3261

Environment

• Timesketch version: Latest (dev branch)
• Database: PostgreSQL
• Search backend: OpenSearch
• Python: 3.x

Problems Identified

1. ❌ Incorrect Operation Order (Line 618)

The sketch is marked as deleted BEFORE OpenSearch indices are deleted:

sketch.set_status(status="deleted")  # Line 618 - Happens first

if not force_delete:
    return HTTP_STATUS_CODE_OK

# OpenSearch index deletion (lines 625-647)
for timeline in sketch.timelines:
    # ... deletion happens here ...

Consequence: If OpenSearch deletion fails, times out, or encounters an error, the sketch remains in the database with status="deleted", making all subsequent requests return 404 errors.

2. ❌ Foreign Key Constraint Violation (Lines 638-645)

The deletion order doesn't respect foreign key constraints:

db_session.delete(searchindex)  # ❌ Parent deleted first
db_session.delete(timeline)     # ❌ FK error: timeline still references searchindex

Error encountered:

sqlalchemy.exc.IntegrityError: (psycopg2.errors.ForeignKeyViolation) 
update or delete on table "searchindex" violates foreign key constraint 
"timeline_searchindex_id_fkey" on table "timeline"
DETAIL: Key (id)=(1578) is still referenced from table "timeline".

3. ❌ No Atomic Transaction

Deletions are committed individually instead of as a single atomic transaction. If an error occurs mid-process, partially deleted data remains in an inconsistent state.

4. ❌ Insufficient Error Handling

OpenSearch exceptions (NotFoundError, ConnectionError, timeouts) are not properly handled, causing timeouts and leaving data in intermediate states.

5. ❌ Admin Check After Modification

The admin privileges check happens AFTER sketch.set_status(status="deleted") is called, allowing non-admin users to partially trigger deletions before authorization fails.

Impact

• ✗ Orphaned sketches: Remain in database with status="deleted" but OpenSearch indices are partially deleted
• ✗ 404 errors: Unable to access orphaned sketches via API
• ✗ Data loss: OpenSearch indices deleted but metadata remains in database
• ✗ Database corruption: Foreign key constraint violations
• ✗ Security issue: Non-admin users can partially trigger force deletion

Proposed Solution

Key Changes Required

1. Reorganize operation order:

   • Soft delete: Mark as deleted and commit immediately, return without touching OpenSearch
   • Hard delete: Delete OpenSearch indices first, THEN delete from database (no status change before)

2. Respect foreign key order:

   • Delete timeline before searchindex (child before parent)
   • Delete sketch last

3. Implement atomic transaction:

   • Collect all OpenSearch deletion results
   • Commit database changes in a single transaction only if all operations succeed
   • Automatic rollback if any error occurs

4. Improve error handling:

   • Catch NotFoundError, ConnectionError, and generic exceptions
   • Log detailed errors for debugging
   • Return HTTP 500 with clear message on failure

5. Move admin check before modifications:

   • Verify admin privileges before any state changes
   • Prevent non-admin users from triggering partial deletions

[jaegeral]

#3677 and #3657 are already in the making.

[jeflagel]

Sorry i come back from holiday you are too fast ;)
I won't make my PR. Do you want me to close as duplicate @jaegeral ?

[jaegeral]

It is okay to keep it open till the PRs are merged