Drops the `tasks`, `task_workspace_apps` and `task_snapshots` tables, the `tasks_with_status` view and the `task_status` enum, and recreates `workspaces_expanded` without the `tasks` join and `task_id` column. Deletes `queries/tasks.sql` and every wrapper, fixture and test that existed only for those queries (dbauthz, dbmock, dbmetrics, dbfake, dbgen, querier_test), plus the `task_id` projection in `GetWorkspaceAgentAndWorkspaceByAuthToken`. **Breaking change:** migration 000594 deletes all task rows. The down migration restores the schema (tables, constraints, indexes, views), not the data. **Review changes:** the schema-only migration test was removed per review (`TestMigrate/UpDownUp` still exercises the down path), the test file no longer reorders existing tests, and the migration moved from 000593 to 000594. The `dump.sql` reorder is pg_dump dependency ordering: `tasks_with_status` pulled its four workspace tables ahead of it, and without the view they return to alphabetical positions. **Stack context:** Coder Tasks removal stack (CODAGT-642): #28743 frontend → #28788 CLI → #28789 API → #28790 provisioner → #29084 notifications → #29085 build reasons → **#29086 tables** → #28750 permissions. The last four PRs replace the single data-drop migration that used to live in #28750; each carries one concern and its own migration number. > Created by Xum (AI agent) on behalf of @ibetitsmike. ## Delivery record **Decision (2026-09-11 ~14:40Z): merge** through the stack merge endpoint, squash, pinned to head `f1d236a0c82f123b8a0d6878799c227ef9bd5776` (merge SHA appended below once landed). **Independent substantive reviews on this PR (3):** | # | Kind | Reviewed commit | Findings and disposition | Source | |---|---|---|---|---| | 1 | Codex code review (bot), PR opened | `24535f8410` | No findings, no threads | [summary](https://github.com/coder/coder/pull/29086#issuecomment-5583877627) | | 2 | Human review, spikecurtis (COMMENTED) | `24535f8410` | 4 findings: dump.sql reorder question (answered: pg_dump dependency hoisting by `tasks_with_status`); two moved tests (undone, the test file is no longer touched); schema-only migration test (deleted). All resolved. | [review](https://github.com/coder/coder/pull/29086#pullrequestreview-5142045164) | | 3 | Human review, spikecurtis (APPROVED) | `f1d236a0c8` (this head) | Approval on the exact final head | [review](https://github.com/coder/coder/pull/29086#pullrequestreview-5179353695) | The four COMMENTED reviews by @ibetitsmike on `f35688f40b` are thread-reply wrappers and are not counted. **Pre-split history relevant to this diff:** this layer's content lived in the monolithic #28750 until 2026-09-08, which received 10 completed Codex code reviews (2026-08-28 to 2026-09-01) and spikecurtis's CHANGES_REQUESTED review of 2026-09-04 that asked for the split. Counting those, the completed-review total relevant to this diff exceeds the six-review budget, so no further discretionary review or fresh final advisory was commissioned. The usable final recommendation for the final diff is review 3 above: the reviewer who authored every finding on this layer approved the exact head. **Exact-head checks:** `required` SUCCESS in [run 34580949353](https://github.com/coder/coder/actions/runs/34580949353/job/103205933212); rollup 15 success, 8 skipped, 1 failure. The failure is the non-required `Analyze PR for Documentation Updates Needed` job ([103204101137](https://github.com/coder/coder/actions/runs/34580949071/job/103204101137)), caused by the dormant `doc-check-pr-29086` review workspace (CODAGT-1025), not a code finding; GitHub flags only `required` as required for this PR. Merge-ref freshness: `refs/pull/29086/merge` has the exact head as second parent and `25af21b5d5d` (the #29085 squash) as first parent; main has since gained #29231, #28639 and #29218 (12 files, none overlapping this PR). The composed tree (`git merge-tree` of current main and this head) builds (`go build ./...`) and vets (`go vet` on `coderd`, `coderd/database/...`, `coderd/wsrelated/...`) cleanly. Layer diff parity with the previously pushed layer verified by numstat after both GitHub restacks. `gh stack push` did not run the local pre-push suite; CI is the validation evidence. **Deferred:** the dormant doc-check workspace (CODAGT-1025) is outside this PR. Next in the stack, #28750, stays blocked: CHANGES_REQUESTED, its approval and CI predate the restack, and its review budget is exhausted.
210 lines
11 KiB
SQL
210 lines
11 KiB
SQL
-- Drop views before modifying underlying tables and columns.
|
|
DROP VIEW IF EXISTS workspaces_expanded;
|
|
|
|
-- Recreate task status enum type.
|
|
CREATE TYPE task_status AS ENUM (
|
|
'pending',
|
|
'initializing',
|
|
'active',
|
|
'paused',
|
|
'unknown',
|
|
'error'
|
|
);
|
|
|
|
-- Recreate tasks table. Deleted task rows cannot be recovered.
|
|
CREATE TABLE tasks (
|
|
id uuid NOT NULL,
|
|
organization_id uuid NOT NULL,
|
|
owner_id uuid NOT NULL,
|
|
name text NOT NULL,
|
|
workspace_id uuid,
|
|
template_version_id uuid NOT NULL,
|
|
template_parameters jsonb DEFAULT '{}'::jsonb NOT NULL,
|
|
prompt text NOT NULL,
|
|
created_at timestamp with time zone NOT NULL,
|
|
deleted_at timestamp with time zone,
|
|
display_name character varying(127) DEFAULT ''::character varying NOT NULL
|
|
);
|
|
|
|
COMMENT ON COLUMN tasks.display_name IS 'Display name is a custom, human-friendly task name.';
|
|
|
|
ALTER TABLE ONLY tasks
|
|
ADD CONSTRAINT tasks_pkey PRIMARY KEY (id);
|
|
|
|
CREATE INDEX tasks_organization_id_idx ON tasks USING btree (organization_id);
|
|
CREATE INDEX tasks_owner_id_idx ON tasks USING btree (owner_id);
|
|
CREATE UNIQUE INDEX tasks_owner_id_name_unique_idx ON tasks USING btree (owner_id, lower(name)) WHERE (deleted_at IS NULL);
|
|
COMMENT ON INDEX tasks_owner_id_name_unique_idx IS 'Index to ensure uniqueness for task owner/name';
|
|
CREATE INDEX tasks_workspace_id_idx ON tasks USING btree (workspace_id);
|
|
|
|
ALTER TABLE ONLY tasks
|
|
ADD CONSTRAINT tasks_organization_id_fkey FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE;
|
|
ALTER TABLE ONLY tasks
|
|
ADD CONSTRAINT tasks_owner_id_fkey FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE CASCADE;
|
|
ALTER TABLE ONLY tasks
|
|
ADD CONSTRAINT tasks_template_version_id_fkey FOREIGN KEY (template_version_id) REFERENCES template_versions(id) ON DELETE CASCADE;
|
|
ALTER TABLE ONLY tasks
|
|
ADD CONSTRAINT tasks_workspace_id_fkey FOREIGN KEY (workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE;
|
|
|
|
-- Recreate task_workspace_apps table.
|
|
CREATE TABLE task_workspace_apps (
|
|
task_id uuid NOT NULL,
|
|
workspace_agent_id uuid,
|
|
workspace_app_id uuid,
|
|
workspace_build_number integer NOT NULL
|
|
);
|
|
|
|
ALTER TABLE ONLY task_workspace_apps
|
|
ADD CONSTRAINT task_workspace_apps_pkey PRIMARY KEY (task_id, workspace_build_number);
|
|
|
|
ALTER TABLE ONLY task_workspace_apps
|
|
ADD CONSTRAINT task_workspace_apps_task_id_fkey FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE;
|
|
ALTER TABLE ONLY task_workspace_apps
|
|
ADD CONSTRAINT task_workspace_apps_workspace_agent_id_fkey FOREIGN KEY (workspace_agent_id) REFERENCES workspace_agents(id) ON DELETE CASCADE;
|
|
ALTER TABLE ONLY task_workspace_apps
|
|
ADD CONSTRAINT task_workspace_apps_workspace_app_id_fkey FOREIGN KEY (workspace_app_id) REFERENCES workspace_apps(id) ON DELETE CASCADE;
|
|
|
|
-- Recreate task_snapshots table.
|
|
CREATE TABLE task_snapshots (
|
|
task_id uuid NOT NULL,
|
|
log_snapshot jsonb NOT NULL,
|
|
log_snapshot_created_at timestamp with time zone DEFAULT now() NOT NULL
|
|
);
|
|
|
|
COMMENT ON TABLE task_snapshots IS 'Stores snapshots of task state when paused, currently limited to conversation history.';
|
|
COMMENT ON COLUMN task_snapshots.task_id IS 'The task this snapshot belongs to.';
|
|
COMMENT ON COLUMN task_snapshots.log_snapshot IS 'Task conversation history in JSON format, allowing users to view logs when the workspace is stopped.';
|
|
COMMENT ON COLUMN task_snapshots.log_snapshot_created_at IS 'When this log snapshot was captured.';
|
|
|
|
ALTER TABLE ONLY task_snapshots
|
|
ADD CONSTRAINT task_snapshots_pkey PRIMARY KEY (task_id);
|
|
|
|
ALTER TABLE ONLY task_snapshots
|
|
ADD CONSTRAINT task_snapshots_task_id_fkey FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE;
|
|
|
|
-- Recreate views.
|
|
CREATE VIEW workspaces_expanded AS
|
|
SELECT workspaces.id,
|
|
workspaces.created_at,
|
|
workspaces.updated_at,
|
|
workspaces.owner_id,
|
|
workspaces.organization_id,
|
|
workspaces.template_id,
|
|
workspaces.deleted,
|
|
workspaces.name,
|
|
workspaces.autostart_schedule,
|
|
workspaces.ttl,
|
|
workspaces.last_used_at,
|
|
workspaces.dormant_at,
|
|
workspaces.deleting_at,
|
|
workspaces.automatic_updates,
|
|
workspaces.favorite,
|
|
workspaces.next_start_at,
|
|
workspaces.group_acl,
|
|
workspaces.user_acl,
|
|
visible_users.avatar_url AS owner_avatar_url,
|
|
visible_users.username AS owner_username,
|
|
visible_users.name AS owner_name,
|
|
organizations.name AS organization_name,
|
|
organizations.display_name AS organization_display_name,
|
|
organizations.icon AS organization_icon,
|
|
organizations.description AS organization_description,
|
|
templates.name AS template_name,
|
|
templates.display_name AS template_display_name,
|
|
templates.icon AS template_icon,
|
|
templates.description AS template_description,
|
|
tasks.id AS task_id,
|
|
COALESCE(( SELECT jsonb_object_agg(acl.key, jsonb_build_object('name', COALESCE(g.name, ''::text), 'avatar_url', COALESCE(g.avatar_url, ''::text))) AS jsonb_object_agg
|
|
FROM (jsonb_each(workspaces.group_acl) acl(key, value)
|
|
LEFT JOIN groups g ON ((g.id = (acl.key)::uuid)))), '{}'::jsonb) AS group_acl_display_info,
|
|
COALESCE(( SELECT jsonb_object_agg(acl.key, jsonb_build_object('name', COALESCE(vu.name, ''::text), 'avatar_url', COALESCE(vu.avatar_url, ''::text))) AS jsonb_object_agg
|
|
FROM (jsonb_each(workspaces.user_acl) acl(key, value)
|
|
LEFT JOIN visible_users vu ON ((vu.id = (acl.key)::uuid)))), '{}'::jsonb) AS user_acl_display_info
|
|
FROM ((((workspaces
|
|
JOIN visible_users ON ((workspaces.owner_id = visible_users.id)))
|
|
JOIN organizations ON ((workspaces.organization_id = organizations.id)))
|
|
JOIN templates ON ((workspaces.template_id = templates.id)))
|
|
LEFT JOIN tasks ON ((workspaces.id = tasks.workspace_id)));
|
|
|
|
COMMENT ON VIEW workspaces_expanded IS 'Joins in the display name information such as username, avatar, and organization name.';
|
|
|
|
CREATE VIEW tasks_with_status AS
|
|
SELECT tasks.id,
|
|
tasks.organization_id,
|
|
tasks.owner_id,
|
|
tasks.name,
|
|
tasks.workspace_id,
|
|
tasks.template_version_id,
|
|
tasks.template_parameters,
|
|
tasks.prompt,
|
|
tasks.created_at,
|
|
tasks.deleted_at,
|
|
tasks.display_name,
|
|
COALESCE(workspaces.group_acl, '{}'::jsonb) AS workspace_group_acl,
|
|
COALESCE(workspaces.user_acl, '{}'::jsonb) AS workspace_user_acl,
|
|
CASE
|
|
WHEN (tasks.workspace_id IS NULL) THEN 'pending'::task_status
|
|
WHEN (build_status.status <> 'active'::task_status) THEN build_status.status
|
|
WHEN (agent_status.status <> 'active'::task_status) THEN agent_status.status
|
|
ELSE app_status.status
|
|
END AS status,
|
|
jsonb_build_object('build', jsonb_build_object('transition', latest_build_raw.transition, 'job_status', latest_build_raw.job_status, 'computed', build_status.status), 'agent', jsonb_build_object('lifecycle_state', agent_raw.lifecycle_state, 'computed', agent_status.status), 'app', jsonb_build_object('health', app_raw.health, 'computed', app_status.status)) AS status_debug,
|
|
task_app.workspace_build_number,
|
|
task_app.workspace_agent_id,
|
|
task_app.workspace_app_id,
|
|
agent_raw.lifecycle_state AS workspace_agent_lifecycle_state,
|
|
app_raw.health AS workspace_app_health,
|
|
task_owner.owner_username,
|
|
task_owner.owner_name,
|
|
task_owner.owner_avatar_url
|
|
FROM (((((((((tasks
|
|
LEFT JOIN workspaces ON ((workspaces.id = tasks.workspace_id)))
|
|
CROSS JOIN LATERAL ( SELECT vu.username AS owner_username,
|
|
vu.name AS owner_name,
|
|
vu.avatar_url AS owner_avatar_url
|
|
FROM visible_users vu
|
|
WHERE (vu.id = tasks.owner_id)) task_owner)
|
|
LEFT JOIN LATERAL ( SELECT task_app_1.workspace_build_number,
|
|
task_app_1.workspace_agent_id,
|
|
task_app_1.workspace_app_id
|
|
FROM task_workspace_apps task_app_1
|
|
WHERE (task_app_1.task_id = tasks.id)
|
|
ORDER BY task_app_1.workspace_build_number DESC
|
|
LIMIT 1) task_app ON (true))
|
|
LEFT JOIN LATERAL ( SELECT workspace_build.transition,
|
|
provisioner_job.job_status,
|
|
workspace_build.job_id
|
|
FROM (workspace_builds workspace_build
|
|
JOIN provisioner_jobs provisioner_job ON ((provisioner_job.id = workspace_build.job_id)))
|
|
WHERE ((workspace_build.workspace_id = tasks.workspace_id) AND (workspace_build.build_number = task_app.workspace_build_number))) latest_build_raw ON (true))
|
|
LEFT JOIN LATERAL ( SELECT workspace_agent.lifecycle_state
|
|
FROM workspace_agents workspace_agent
|
|
WHERE (workspace_agent.id = task_app.workspace_agent_id)) agent_raw ON (true))
|
|
LEFT JOIN LATERAL ( SELECT workspace_app.health
|
|
FROM workspace_apps workspace_app
|
|
WHERE (workspace_app.id = task_app.workspace_app_id)) app_raw ON (true))
|
|
CROSS JOIN LATERAL ( SELECT
|
|
CASE
|
|
WHEN (latest_build_raw.job_status IS NULL) THEN 'pending'::task_status
|
|
WHEN (latest_build_raw.job_status = ANY (ARRAY['failed'::provisioner_job_status, 'canceling'::provisioner_job_status, 'canceled'::provisioner_job_status])) THEN 'error'::task_status
|
|
WHEN ((latest_build_raw.transition = ANY (ARRAY['stop'::workspace_transition, 'delete'::workspace_transition])) AND (latest_build_raw.job_status = 'succeeded'::provisioner_job_status)) THEN 'paused'::task_status
|
|
WHEN ((latest_build_raw.transition = 'start'::workspace_transition) AND (latest_build_raw.job_status = 'pending'::provisioner_job_status)) THEN 'pending'::task_status
|
|
WHEN ((latest_build_raw.transition = 'start'::workspace_transition) AND (latest_build_raw.job_status = ANY (ARRAY['running'::provisioner_job_status, 'succeeded'::provisioner_job_status]))) THEN 'active'::task_status
|
|
ELSE 'unknown'::task_status
|
|
END AS status) build_status)
|
|
CROSS JOIN LATERAL ( SELECT
|
|
CASE
|
|
WHEN ((agent_raw.lifecycle_state IS NULL) OR (agent_raw.lifecycle_state = ANY (ARRAY['created'::workspace_agent_lifecycle_state, 'starting'::workspace_agent_lifecycle_state]))) THEN 'initializing'::task_status
|
|
WHEN (agent_raw.lifecycle_state = ANY (ARRAY['ready'::workspace_agent_lifecycle_state, 'start_timeout'::workspace_agent_lifecycle_state, 'start_error'::workspace_agent_lifecycle_state])) THEN 'active'::task_status
|
|
WHEN (agent_raw.lifecycle_state <> ALL (ARRAY['created'::workspace_agent_lifecycle_state, 'starting'::workspace_agent_lifecycle_state, 'ready'::workspace_agent_lifecycle_state, 'start_timeout'::workspace_agent_lifecycle_state, 'start_error'::workspace_agent_lifecycle_state])) THEN 'unknown'::task_status
|
|
ELSE 'unknown'::task_status
|
|
END AS status) agent_status)
|
|
CROSS JOIN LATERAL ( SELECT
|
|
CASE
|
|
WHEN (app_raw.health = 'initializing'::workspace_app_health) THEN 'initializing'::task_status
|
|
WHEN (app_raw.health = 'unhealthy'::workspace_app_health) THEN 'error'::task_status
|
|
WHEN (app_raw.health = ANY (ARRAY['healthy'::workspace_app_health, 'disabled'::workspace_app_health])) THEN 'active'::task_status
|
|
ELSE 'unknown'::task_status
|
|
END AS status) app_status)
|
|
WHERE (tasks.deleted_at IS NULL);
|