Missing `PRAGMA foreign_keys = ON` — All Foreign Key Constraints Are Silently Dead Code

[ionfwsrijan]

Description

SQLite ships with foreign key enforcement disabled by default (PRAGMA foreign_keys = OFF). It must be explicitly enabled per-connection via PRAGMA foreign_keys = ON. The Database.connect() method in backend/secuscan/database.py never executes this pragma.

This means every FOREIGN KEY ... ON DELETE CASCADE and FOREIGN KEY ... ON DELETE SET NULL defined across 6+ tables is completely dead code — referential actions never fire, and orphaned rows accumulate silently.

The codebase contains comments and logic that explicitly depend on these cascading behaviors working:

routes.py:1346-1348 — comment says: "Sweep up any of the caller's findings not linked to a task (task_id was set NULL by ON DELETE)" — but the ON DELETE SET NULL never fires.

Impact

• Data integrity failure: Deleting a parent tasks row does not cascade-delete crawl_runs, asset_services, notification_history rows — orphaned records accumulate permanently
• Wrong query results: The ON DELETE SET NULL on workflow_runs.version_id never fires — stale version IDs persist, potentially pointing to non-existent workflow_versions rows
• Silent bloat: Over thousands of scan runs and deletions, orphaned rows in crawl_runs, asset_services, and notification_history tables grow unbounded, slowing all queries on these tables
• Unreachable cleanup code: Any code path that relies on cascading deletes (like sweeping orphaned findings) is permanently broken — the code assumes it works but it never does
• No warning or error: SQLite silently ignores every FK violation — no errors are raised, no warnings are logged, making this a completely silent data corruption bug

Root cause

File: backend/secuscan/database.py:33-42

async def connect(self):
    """Establish database connection and ensure schema exists."""
    Path(self.db_path).parent.mkdir(parents=True, exist_ok=True)
    conn = await aiosqlite.connect(self.db_path)
    self._connection = conn
    conn.row_factory = aiosqlite.Row
    await self._create_schema()
    await self._run_migrations()

There is no await conn.execute("PRAGMA foreign_keys = ON") anywhere in connect(). The pragma is only used in the migration code (database.py:523-553) where it is temporarily toggled OFF and ON for a table rebuild — but this only affects that specific migration's connection, not subsequent operations.

All affected foreign key definitions

Table	Column	References	Action	Location
crawl_runs	task_id	tasks(id)	ON DELETE CASCADE	database.py:195
asset_services	task_id	tasks(id)	ON DELETE CASCADE	database.py:212
workflow_versions	workflow_id	workflows(id)	ON DELETE CASCADE	database.py:289
workflow_runs	workflow_id	workflows(id)	ON DELETE CASCADE	database.py:301
workflow_runs	version_id	workflow_versions(id)	ON DELETE SET NULL	database.py:302
notification_history	rule_id	notification_rules(id)	ON DELETE CASCADE	database.py:330
notification_history	finding_id	findings(id)	ON DELETE CASCADE	database.py:331

Affected files

File	Lines	Issue
backend/secuscan/database.py	33–42	connect() never executes PRAGMA foreign_keys = ON
backend/secuscan/database.py	523–553	Migration code temporarily toggles pragma but only for that connection
backend/secuscan/routes.py	1176–1252	delete_task_records() manually deletes from findings, reports, audit_log, tasks but omits crawl_runs, asset_services, notification_history — relying on cascades that never fire
backend/secuscan/routes.py	1346–1348	Comment referencing ON DELETE SET NULL behavior that never works
backend/secuscan/executor.py	362–375	Optimistic lock assumes cascade on task deletion works
backend/secuscan/platform_resources.py	63–105	persist_crawl_run() inserts into crawl_runs with FK to tasks
backend/secuscan/platform_resources.py	108–171	replace_asset_services() inserts into asset_services with FK to tasks
backend/secuscan/notification_service.py	266–288	record_delivery() inserts into notification_history with FK to findings

Proposed fix

1. Add PRAGMA foreign_keys = ON to every connection in database.py

In the connect() method, immediately after creating the connection:

async def connect(self):
    Path(self.db_path).parent.mkdir(parents=True, exist_ok=True)
    conn = await aiosqlite.connect(self.db_path)
    self._connection = conn
    conn.row_factory = aiosqlite.Row
    await conn.execute("PRAGMA foreign_keys = ON")    # ADD THIS
    await self._create_schema()
    await self._run_migrations()

2. Also apply to get_db() in routes.py

If routes.py creates connections independently (check for separate aiosqlite.connect() calls), the pragma must be set there too.

3. Add explicit deletes to delete_task_records() as a belt-and-suspenders fix

Even after enabling FK enforcement, it's good practice to also explicitly delete from tables that cascade:

await db.execute_no_commit(
    f"DELETE FROM crawl_runs WHERE task_id IN ({placeholders})", tuple(chunk)
)
await db.execute_no_commit(
    f"DELETE FROM asset_services WHERE task_id IN ({placeholders})", tuple(chunk)
)
await db.execute_no_commit(
    f"DELETE FROM notification_history WHERE finding_id IN (SELECT id FROM findings WHERE task_id IN ({placeholders}))", tuple(chunk)
)

4. Add a connection validation test

Add a test that opens a connection, inserts rows with foreign key relationships, deletes the parent, and asserts that child rows are cascade-deleted. This would catch any future regression where PRAGMA foreign_keys is forgotten again.

[ionfwsrijan]

@utksh1 Please review and assign this issue

[madsysharma]

/assign
I would like to work on this as part of GSSoC 2026.

[tmdeveloper007]

Closing this issue as it has been open without a linked pull request for an extended period. If this is still needed, please reopen with more specific scope.

[tmdeveloper007]

Closing this issue as it has been open without a linked pull request for an extended period. If this is still needed, please reopen with a more specific scope or consider breaking it into smaller, actionable items.

[tmdeveloper007]

Closing this issue as it has been open without a linked pull request for an extended period. If this is still needed, please reopen with a more specific scope or consider breaking it into smaller, actionable items.