Description

The schema defines:

CREATE TABLE IF NOT EXISTS convergence_trajectories (
    id TEXT PRIMARY KEY,
    task_id TEXT NOT NULL,
    ...
);

But there is no foreign key reference to tasks(id) and no ON DELETE CASCADE. If a task is deleted (or if task records are cleaned up), associated convergence_trajectories rows become orphaned.

Compare to task_dependencies which correctly uses:

task_id TEXT NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,

Similarly, worktrees.task_id has no foreign key reference:

task_id TEXT NOT NULL,  -- Missing REFERENCES tasks(id)

And agent_instances.current_task_id has no reference either.

Expected behavior

All task_id columns in child tables should reference tasks(id) with appropriate cascade behavior.

Suggested fix

In a new migration, alter these tables to add foreign key references:

-- For worktrees and convergence_trajectories
-- (SQLite requires recreating the table to add FK constraints)

At minimum, add a periodic orphan cleanup job and document the missing constraints.

Enable SQLite foreign key support at connection time:

sqlx::query("PRAGMA foreign_keys = ON").execute(&pool).await?;

Written by Polish's bot. :)