feat(coderd/database): add schema for task pause/resume lifecycle #21557
Conversation
43354bf to
83364af
Compare
Creates migration 000409 with the database foundation for pausing and resuming task workspaces. The task_snapshots table stores conversation history (AgentAPI messages) so users can view task logs even when the workspace is stopped. Each task gets one snapshot, overwritten on each pause. Three new build_reason values (task_auto_pause, task_manual_pause, task_resume) let us distinguish task lifecycle events in telemetry and audit logs from regular workspace operations. Uses a regular table rather than UNLOGGED for snapshots. While UNLOGGED would be faster, losing snapshots on database crash creates user confusion (logs disappear until next pause). We can switch to UNLOGGED post-GA if write performance becomes a problem. Closes coder/internal#1250
83364af to
1beffe1
Compare
| CREATE TABLE task_snapshots ( | ||
| task_id UUID NOT NULL PRIMARY KEY REFERENCES tasks (id) ON DELETE CASCADE, | ||
| log_snapshot JSONB NOT NULL, | ||
| log_snapshot_at TIMESTAMPTZ NOT NULL DEFAULT NOW() |
There was a problem hiding this comment.
nit: created_at for consistency with other database fields, but definitely not a blocker
There was a problem hiding this comment.
what is the purpose behind the consistency? I have seen other tables that do not have it. We have audit logs that track creation and other changes, right? What does the created_at field give us?
There was a problem hiding this comment.
Consistency for consistency's sake more than anything.
| ALTER TABLE ONLY task_snapshots | ||
| ADD CONSTRAINT task_snapshots_task_id_fkey FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE; | ||
|
|
There was a problem hiding this comment.
Would it make sense to add a constraint such that a task snapshot cannot be inserted after the related task is deleted?
There was a problem hiding this comment.
We could add that, we also need to add them to dbpurge I suppose (for soft deleted tasks).
|
|
||
| -- Add build reasons for task lifecycle events. | ||
| -- These distinguish task pause/resume operations from regular workspace lifecycle events. | ||
| ALTER TYPE build_reason ADD VALUE IF NOT EXISTS 'task_auto_pause'; |
There was a problem hiding this comment.
Does this warning not apply to this migration?
Is there any risk we might reference these values in a future migration?
There was a problem hiding this comment.
No risk. Since task pause/resume is something we're implementing now, there's no motivation to migrate old data. 👍🏻
|
Follow-up issue for dbpurge (non-critical): coder/internal#1283 |
Creates migration 000409 with the database foundation for pausing and
resuming task workspaces.
The task_snapshots table stores conversation history (AgentAPI messages)
so users can view task logs even when the workspace is stopped. Each task
gets one snapshot, overwritten on each pause.
Three new build_reason values (task_auto_pause, task_manual_pause,
task_resume) let us distinguish task lifecycle events in telemetry and
audit logs from regular workspace operations.
Uses a regular table rather than UNLOGGED for snapshots. While UNLOGGED
would be faster, losing snapshots on database crash creates user confusion
(logs disappear until next pause). We can switch to UNLOGGED post-GA if
write performance becomes a problem.
Closes coder/internal#1250