purge_scaling_group fails with IntegrityError when scaling group has sessions with kernels

[seedspirit]

Summary

ScalingGroupDBSource.purge_scaling_group raises sqlalchemy.dialects.postgresql.asyncpg.IntegrityError when attempting to delete a scaling group that has sessions with associated kernels. The cascade deletion sequence is missing a KernelRow deletion step before SessionRow deletion, causing a foreign key constraint violation on fk_kernels_session_id_sessions.

Steps to Reproduce

1. Create a scaling group
2. Create a session belonging to that scaling group
3. Create a kernel belonging to that session
4. Call purge_scaling_group on the scaling group

Expected Behavior

The scaling group and all its dependent records (sessions, kernels, endpoints, routes) are deleted successfully without errors.

Actual Behavior

sqlalchemy.dialects.postgresql.asyncpg.IntegrityError: update or delete on table "sessions" violates foreign key constraint "fk_kernels_session_id_sessions" on table "kernels"

The operation fails because KernelRow.session_id references sessions.id with no ON DELETE CASCADE (default RESTRICT), and the code attempts to delete sessions without first deleting their kernels.

Root Cause

In ScalingGroupDBSource.purge_scaling_group, the cascade deletion order was:

1. RoutingRow (by session)
2. EndpointRow (by scaling group)
3. SessionRow (by scaling group) — fails here
4. ScalingGroupRow (via purger)

KernelRow deletion was entirely missing from the sequence. Since KernelRow.session_id FK has no cascade delete,

the database rejects the session deletion while kernels still reference those sessions.

Solution

Add a KernelRow deletion step between endpoint and session deletion

Corrected cascade order:

1. RoutingRow (by session — RESTRICT, manual delete)
2. EndpointRow (by scaling group — routes CASCADE auto)
3. KernelRow (by session — NO cascade, manual delete) ← added
4. SessionRow (by scaling group)
5. ScalingGroupRow (via purger)

JIRA Issue: BA-4282