Tasks: Database cleanup for soft-deleted task snapshots #1283

Repo: coder/coder

Scope: Add automatic cleanup of task snapshots when tasks are soft-deleted.

Changes:

• Add query to delete task snapshots for soft-deleted tasks
• Integrate cleanup into dbpurge daemon with logging and metrics
• Add authorization for delete operation
• Make the task soft-delete query also remove data from the task_snapshots table

Files:

• coderd/database/queries/tasks.sql
• coderd/database/dbpurge/dbpurge.go
• coderd/database/dbpurge/dbpurge_test.go
• coderd/database/dbauthz/dbauthz.go

Acceptance criteria:

• Snapshots for soft-deleted tasks are removed on next purge iteration
• Snapshots for the task are deleted when a task is deleted
• Snapshots for active tasks are preserved
• Tests verify cleanup behavior and authorization

Dependencies:

• Tasks: Database migrations for task lifecycle #1250 (Tasks: Database migrations for task lifecycle)

References:

• PRD: Start/Pause/Resume the Task Workspace
• RFC: Tasks: Start/Pause/Resume Lifecycle