bug: tasks - foreign key violated when stopping workspace

Is there an existing issue for this?

I have searched the existing issues

Current Behavior

Sometimes, when you stop a task workspace, the operation fails with this error:

internal provisionerserver error: complete job: execute transaction: update workspace build ai tasks flag: pq: insert or update on table "workspace_builds" violates foreign key constraint "workspace_builds_ai_task_sidebar_app_id_fkey"

Changing the workspace's version to an older version and then back again lets you work around the bug and stop the workspace.

Relevant Log Output

Expected Behavior

I'd expect to be able to stop the workspace without any problems.

Steps to Reproduce

I'm not sure how to reproduce this error. I've seen it happen when both the sidebar app and the coder_ai_task resource are created within a terraform module, that module is sourced from a git branch, and that branch gets updated between workspace start and stop. I don't know if these conditions are sufficient to reproduce the error.

Environment

Host OS: Linux
Coder version: 2.24.1

Additional Context

No response

I was able to reproduce this again by referencing our claude-code module from a git branch, starting my workspace, modifying the Display Name field on the claude-code-web app in the claude code module, and stopping the workspace.

I was able to track down the root cause of the error, and I believe there's a bug in ConvertState, which parses the Terraform state file. I'm not sure what the proper fix is yet. Here's what I found.

First, for context, this is the code that's running into an error.

coder/coderd/provisionerdserver/provisionerdserver.go

Lines 1964 to 1975 in 6ba4b5b

    
           err = db.UpdateWorkspaceBuildAITaskByID(ctx, database.UpdateWorkspaceBuildAITaskByIDParams{ 
        
           	ID: workspaceBuild.ID, 
        
           	HasAITask: sql.NullBool{ 
        
           		Bool:  hasAITask, 
        
           		Valid: true, 
        
           	}, 
        
           	SidebarAppID: sidebarAppID, 
        
           	UpdatedAt:    now, 
        
           }) 
        
           if err != nil { 
        
           	return xerrors.Errorf("update workspace build ai tasks flag: %w", err) 
        
           }

We have a foreign key constraint that makes sure that the sidebar app id points to a coder app in the database.

coder/coderd/database/dump.sql

Lines 3236 to 3237 in 6ba4b5b

    
           ALTER TABLE ONLY workspace_builds 
        
               ADD CONSTRAINT workspace_builds_ai_task_sidebar_app_id_fkey FOREIGN KEY (ai_task_sidebar_app_id) REFERENCES workspace_apps(id);

Using my debugger, I verified that the sidebar_app_id that causes the foreign key violation is a valid UUID:

So the issue is that we were expecting this coder app to be in the database, but it's not. With the debugger, I can see that we ran into this while processing a proto.CompletedJob_WorkspaceBuild_.

Workspace apps are always upserted before the line of code that errors out. The upsert is nested within 2 loops in the InsertWorkspaceResource function, which is called for every resource included in the jobType variable, which has the proto.CompletedJob_WorkspaceBuild_ type:

coder/coderd/provisionerdserver/provisionerdserver.go

Lines 1929 to 1939 in 6ba4b5b

    
           for _, protoResource := range jobType.WorkspaceBuild.Resources { 
        
           	for _, protoAgent := range protoResource.Agents { 
        
           		dur := time.Duration(protoAgent.GetConnectionTimeoutSeconds()) * time.Second 
        
           		agentTimeouts[dur] = true 
        
           	} 
        
           	err = InsertWorkspaceResource(ctx, db, job.ID, workspaceBuild.Transition, protoResource, telemetrySnapshot) 
        
           	if err != nil { 
        
           		return xerrors.Errorf("insert provisioner job: %w", err) 
        
           	} 
        
           }

coder/coderd/provisionerdserver/provisionerdserver.go

Line 2385 in 6ba4b5b

    
           func InsertWorkspaceResource(ctx context.Context, db database.Store, jobID uuid.UUID, transition database.WorkspaceTransition, protoResource *sdkproto.Resource, snapshot *telemetry.Snapshot) error {

coder/coderd/provisionerdserver/provisionerdserver.go

Lines 2415 to 2416 in 6ba4b5b

    
           for _, prAgent := range protoResource.Agents { 
        
           	// Similar logic is duplicated in terraform/resources.go.

coder/coderd/provisionerdserver/provisionerdserver.go

Lines 2665 to 2666 in 6ba4b5b

    
           for _, app := range prAgent.Apps { 
        
           	// Similar logic is duplicated in terraform/resources.go.

coder/coderd/provisionerdserver/provisionerdserver.go

Lines 2722 to 2725 in 6ba4b5b

    
           dbApp, err := db.UpsertWorkspaceApp(ctx, database.UpsertWorkspaceAppParams{ 
        
           	ID:          id, 
        
           	CreatedAt:   dbtime.Now(), 
        
           	AgentID:     dbAgent.ID,

The jobType variable is an argument to the completeWorkspaceBuildJob method:

coder/coderd/provisionerdserver/provisionerdserver.go

Lines 1830 to 1832 in 6ba4b5b

    
           // completeWorkspaceBuildJob handles completion of a workspace build job. 
        
           // Most database operations are performed within a transaction. 
        
           func (s *server) completeWorkspaceBuildJob(ctx context.Context, job database.ProvisionerJob, jobID uuid.UUID, jobType *proto.CompletedJob_WorkspaceBuild_, telemetrySnapshot *telemetry.Snapshot) error {

This object is the result of apply in the terraform provisioner:

coder/provisionerd/runner/runner.go

Lines 1117 to 1133 in 6ba4b5b

    
           return &proto.CompletedJob{ 
        
           	JobId: r.job.JobId, 
        
           	Type: &proto.CompletedJob_WorkspaceBuild_{ 
        
           		WorkspaceBuild: &proto.CompletedJob_WorkspaceBuild{ 
        
           			State:     applyComplete.State, 
        
           			Resources: applyComplete.Resources, 
        
           			Timings:   applyComplete.Timings, 
        
           			// Modules are created on disk by `terraform init`, and that is only 
        
           			// called by `plan`. `apply` does not modify them, so we can use the 
        
           			// modules from the plan response. 
        
           			Modules: planComplete.Modules, 
        
           			// Resource replacements are discovered at plan time, only. 
        
           			ResourceReplacements: planComplete.ResourceReplacements, 
        
           			AiTasks:              applyComplete.AiTasks, 
        
           		}, 
        
           	}, 
        
           }, nil

And the Resources field is taken from applyComplete. As a reminder Resources contains the list of workspace apps that should be upserted into the database (nested under Resources[*].Agents[*].Apps). In particular, it should contain the app that's missing from the database, and is causing the error we saw in the first place.

The value of applyComplete is created in the executor:

coder/provisioner/terraform/executor.go

Lines 566 to 583 in 6ba4b5b

    
           state, err := e.stateResources(ctx, killCtx) 
        
           if err != nil { 
        
           	return nil, err 
        
           } 
        
           statefilePath := filepath.Join(e.workdir, "terraform.tfstate") 
        
           stateContent, err := os.ReadFile(statefilePath) 
        
           if err != nil { 
        
           	return nil, xerrors.Errorf("read statefile %q: %w", statefilePath, err) 
        
           } 
        
           return &proto.ApplyComplete{ 
        
           	Parameters:            state.Parameters, 
        
           	Resources:             state.Resources, 
        
           	ExternalAuthProviders: state.ExternalAuthProviders, 
        
           	State:                 stateContent, 
        
           	Timings:               e.timings.aggregate(), 
        
           	AiTasks:               state.AITasks, 
        
           }, nil

And state is effectively the output of ConvertState:

coder/provisioner/terraform/executor.go

Lines 587 to 611 in 6ba4b5b

    
           func (e *executor) stateResources(ctx, killCtx context.Context) (*State, error) { 
        
           	ctx, span := e.server.startTrace(ctx, tracing.FuncName()) 
        
           	defer span.End() 
        
           	state, err := e.state(ctx, killCtx) 
        
           	if err != nil { 
        
           		return nil, err 
        
           	} 
        
           	rawGraph, err := e.graph(ctx, killCtx) 
        
           	if err != nil { 
        
           		return nil, xerrors.Errorf("get terraform graph: %w", err) 
        
           	} 
        
           	converted := &State{} 
        
           	if state.Values == nil { 
        
           		return converted, nil 
        
           	} 
        
           	converted, err = ConvertState(ctx, []*tfjson.StateModule{ 
        
           		state.Values.RootModule, 
        
           	}, rawGraph, e.server.logger) 
        
           	if err != nil { 
        
           		return nil, err 
        
           	} 
        
           	return converted, nil 
        
           }

Using my debugger, I was able to verify that even though the coder_app that's missing from the database is present in the actual JSON state file created by terraform, it is not returned by ConvertState. For reasons that I currently do not understand, ConvertState explicitly skips coder_apps when populating the Resources field:

coder/provisioner/terraform/resources.go

Lines 748 to 750 in 6ba4b5b

    
           if resource.Type == "coder_script" || resource.Type == "coder_agent" || resource.Type == "coder_agent_instance" || resource.Type == "coder_app" || resource.Type == "coder_metadata" { 
        
           	continue 
        
           }

and when iterating through the Terraform graph while looking for coder agents:

coder/provisioner/terraform/resources.go

Lines 1254 to 1257 in 6ba4b5b

    
           // Don't associate Coder resources with other Coder resources! 
        
           if strings.HasPrefix(resource.Type, "coder_") { 
        
           	continue 
        
           }

As a temporary workaround for the bug I submitted coder/registry#283. The idea is that since terraform_data is not one of the skipped names, it should always be included in the Resources field. Also, since it's indirectly dependent on the coder agent, it should be found by findResourcesInGraph. This solution did get rid of the error in my local setup, but I'm not certain it'll always work.

fix(claude-code): workaround for a coder bug (#283)

So workspace_apps are tied to a specific Agent via the agent_id, which is build-specific in most cases (claiming a prebuilt workspace is the exception).

So, generally speaking, if you stop a workspace, it will have no agents, and therefore no workspace_apps associated with that build.

I think the db constraints themselves are incorrect.

ALTER TABLE workspace_builds ADD CONSTRAINT workspace_builds_ai_task_sidebar_app_id_fkey FOREIGN KEY (ai_task_sidebar_app_id) REFERENCES workspace_apps(id);

-- if has_ai_task is true, ai_task_sidebar_app_id MUST be set
-- ai_task_sidebar_app_id can ONLY be set if has_ai_task is true
--
--   has_ai_task | ai_task_sidebar_app_id | Result
--   ------------|------------------------|---------------
--   NULL        | NULL                   | TRUE (passes)
--   NULL        | NOT NULL               | FALSE (fails)
--   FALSE       | NULL                   | TRUE (passes)
--   FALSE       | NOT NULL               | FALSE (fails)
--   TRUE        | NULL                   | FALSE (fails)
--   TRUE        | NOT NULL               | TRUE (passes)
ALTER TABLE workspace_builds
	ADD CONSTRAINT workspace_builds_ai_task_sidebar_app_id_required CHECK (
		((has_ai_task IS NULL OR has_ai_task = false) AND ai_task_sidebar_app_id IS NULL)
			OR (has_ai_task = true AND ai_task_sidebar_app_id IS NOT NULL)
		);

I'm assuming here that we want a stopped AI Task to still have has_ai_task = true. A stopped workspace doesn't need a sidebar app, so the check constraint needs to allow has_ai_task = true with transition = stop and ai_task_sidebar_app_id = NULL.

@spikecurtis

@spikecurtis the bug is also sometimes triggered on start transitions.

if you stop a workspace, it will have no agents

What makes you say that? I don't think we recommend altering the count value for coder_agent resources depending on the transition. I have triggered the bug in a template based on our docker example, which does not set count on the coder_agent.

Let me give a bit more context on how the coder/registry#283 workaround works - hopefully that'll make it easier to see the root cause of the bug.

Every resource in jobType.WorkspaceBuild.Resources has a list of agents it depends on in the Agents field. Every agent has a list of workspace apps that depend on it in the Apps field. In order to insert workspace apps into the database, completeWorkspaceBuildJob iterates through all these apps (jobType.WorkspaceBuild.Resources[*].Agents[*].Apps[*]).

The bug is triggered when both a coder_app and a coder_ai_task are present in the statefile returned by Terraform, but ConvertState returns a coder_ai_task and doesn't return any resources that depend on the agent that the coder_app depends on. Specifically, this can happen when the only resources that depend on the agent are prefixed with coder_.

For reasons I do not understand, ConvertState handles coder_-prefixed resources differently from other Terraform resources and skips their processing in places like:

coder/provisioner/terraform/resources.go

Lines 748 to 750 in 6ba4b5b

    
           if resource.Type == "coder_script" || resource.Type == "coder_agent" || resource.Type == "coder_agent_instance" || resource.Type == "coder_app" || resource.Type == "coder_metadata" { 
        
           	continue 
        
           }

coder/provisioner/terraform/resources.go

Lines 1254 to 1257 in 6ba4b5b

    
           // Don't associate Coder resources with other Coder resources! 
        
           if strings.HasPrefix(resource.Type, "coder_") { 
        
           	continue 
        
           }

The workaround ensures that there is always a non-coder_-prefixed resource present in the statefile that depends on the same agent as the coder_app - that's the terraform_data resource. It doesn't hit the coder_ edge cases, so it's present in jobType.WorkspaceBuild.Resources, and the coder_app is present in its Agents[0].Apps.

coder_ resource types are skipped in the general loop on line 748 because they have already been specifically handled earlier in the function.

coder_apps are handled here:

coder/provisioner/terraform/resources.go

Lines 479 to 485 in d4b4418

    
           // Associate Apps with agents. 
        
           appSlugs := make(map[string]struct{}) 
        
           for _, resources := range tfResourcesByLabel { 
        
           	for _, resource := range resources { 
        
           		if resource.Type != "coder_app" { 
        
           			continue 
        
           		}

coder_agents also have to be specifically associated with some sort of compute resource in order to be valid. This supresses Agents on stop transitions even without an explicit count parameter. We do this by looking at the resource graph:

coder/provisioner/terraform/resources.go

Lines 404 to 424 in d4b4418

    
           var agentResource *graphResource 
        
           for _, resource := range findResourcesInGraph(graph, tfResourcesByLabel, agentNode.Name, 0, true) { 
        
           	if agentResource == nil { 
        
           		// Default to the first resource because we have nothing to compare! 
        
           		agentResource = resource 
        
           		continue 
        
           	} 
        
           	if resource.Depth < agentResource.Depth { 
        
           		// There's a closer resource! 
        
           		agentResource = resource 
        
           		continue 
        
           	} 
        
           	if resource.Depth == agentResource.Depth && resource.Label < agentResource.Label { 
        
           		agentResource = resource 
        
           		continue 
        
           	} 
        
           } 
        
           if agentResource == nil { 
        
           	continue 
        
           }

Side note: before somebody starts emergency patching, please make sure the solution won't affect much the task lifecycle, especially coder/internal#837, pausing/resuming tasks.

chore(coderd/provisionerdserver): remove dangerous usage of dbtestuti…

My understanding from the original RFC is that a workspace that has the "AI Task" flag set should always have it set in perpetuity.

Given that we skip over agents with no corresponding compute resource (in reality, any non-Coder resource should do), having a nonexistent sidebar_app_id is a symptom of a deeper template issue.

It sounds like we need to:

Skip the update on a STOP transition while re-using the previous values for has_ai_task / sidebar_app_id
Improve the error messaging and gracefully handle the case on a START transition
Check for and validate/warn about this condition in the provider

fix(claude-code): revert workaround (#298)

chore(coderd/provisionerdserver): avoid fk error on invalid ai_task_s…

I've merged #19253 which includes a workaround for this. It does not modify the existing database constraints as we decided to avoid making modifications to those pending other decisions on the tasks UI.

Navigation Menu

Repository navigation

bug: tasks - foreign key violated when stopping workspace #18776

Is there an existing issue for this?

Current Behavior

Relevant Log Output

Expected Behavior

Steps to Reproduce

Environment

Additional Context

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

	err = db.UpdateWorkspaceBuildAITaskByID(ctx, database.UpdateWorkspaceBuildAITaskByIDParams{
	ID: workspaceBuild.ID,
	HasAITask: sql.NullBool{
	Bool: hasAITask,
	Valid: true,
	},
	SidebarAppID: sidebarAppID,
	UpdatedAt: now,
	})
	if err != nil {
	return xerrors.Errorf("update workspace build ai tasks flag: %w", err)
	}

	ALTER TABLE ONLY workspace_builds
	ADD CONSTRAINT workspace_builds_ai_task_sidebar_app_id_fkey FOREIGN KEY (ai_task_sidebar_app_id) REFERENCES workspace_apps(id);

	for _, protoResource := range jobType.WorkspaceBuild.Resources {
	for _, protoAgent := range protoResource.Agents {
	dur := time.Duration(protoAgent.GetConnectionTimeoutSeconds()) * time.Second
	agentTimeouts[dur] = true
	}

	err = InsertWorkspaceResource(ctx, db, job.ID, workspaceBuild.Transition, protoResource, telemetrySnapshot)
	if err != nil {
	return xerrors.Errorf("insert provisioner job: %w", err)
	}
	}

	for _, prAgent := range protoResource.Agents {
	// Similar logic is duplicated in terraform/resources.go.

	for _, app := range prAgent.Apps {
	// Similar logic is duplicated in terraform/resources.go.

	dbApp, err := db.UpsertWorkspaceApp(ctx, database.UpsertWorkspaceAppParams{
	ID: id,
	CreatedAt: dbtime.Now(),
	AgentID: dbAgent.ID,

	// completeWorkspaceBuildJob handles completion of a workspace build job.
	// Most database operations are performed within a transaction.
	func (s server) completeWorkspaceBuildJob(ctx context.Context, job database.ProvisionerJob, jobID uuid.UUID, jobType proto.CompletedJob_WorkspaceBuild_, telemetrySnapshot *telemetry.Snapshot) error {

	return &proto.CompletedJob{
	JobId: r.job.JobId,
	Type: &proto.CompletedJob_WorkspaceBuild_{
	WorkspaceBuild: &proto.CompletedJob_WorkspaceBuild{
	State: applyComplete.State,
	Resources: applyComplete.Resources,
	Timings: applyComplete.Timings,
	// Modules are created on disk by `terraform init`, and that is only
	// called by `plan`. `apply` does not modify them, so we can use the
	// modules from the plan response.
	Modules: planComplete.Modules,
	// Resource replacements are discovered at plan time, only.
	ResourceReplacements: planComplete.ResourceReplacements,
	AiTasks: applyComplete.AiTasks,
	},
	},
	}, nil

	state, err := e.stateResources(ctx, killCtx)
	if err != nil {
	return nil, err
	}
	statefilePath := filepath.Join(e.workdir, "terraform.tfstate")
	stateContent, err := os.ReadFile(statefilePath)
	if err != nil {
	return nil, xerrors.Errorf("read statefile %q: %w", statefilePath, err)
	}

	return &proto.ApplyComplete{
	Parameters: state.Parameters,
	Resources: state.Resources,
	ExternalAuthProviders: state.ExternalAuthProviders,
	State: stateContent,
	Timings: e.timings.aggregate(),
	AiTasks: state.AITasks,
	}, nil

	func (e executor) stateResources(ctx, killCtx context.Context) (State, error) {
	ctx, span := e.server.startTrace(ctx, tracing.FuncName())
	defer span.End()

	state, err := e.state(ctx, killCtx)
	if err != nil {
	return nil, err
	}
	rawGraph, err := e.graph(ctx, killCtx)
	if err != nil {
	return nil, xerrors.Errorf("get terraform graph: %w", err)
	}
	converted := &State{}
	if state.Values == nil {
	return converted, nil
	}

	converted, err = ConvertState(ctx, []*tfjson.StateModule{
	state.Values.RootModule,
	}, rawGraph, e.server.logger)
	if err != nil {
	return nil, err
	}
	return converted, nil
	}

	if resource.Type == "coder_script" \|\| resource.Type == "coder_agent" \|\| resource.Type == "coder_agent_instance" \|\| resource.Type == "coder_app" \|\| resource.Type == "coder_metadata" {
	continue
	}

	// Don't associate Coder resources with other Coder resources!
	if strings.HasPrefix(resource.Type, "coder_") {
	continue
	}

	// Associate Apps with agents.
	appSlugs := make(map[string]struct{})
	for _, resources := range tfResourcesByLabel {
	for _, resource := range resources {
	if resource.Type != "coder_app" {
	continue
	}

	var agentResource *graphResource
	for _, resource := range findResourcesInGraph(graph, tfResourcesByLabel, agentNode.Name, 0, true) {
	if agentResource == nil {
	// Default to the first resource because we have nothing to compare!
	agentResource = resource
	continue
	}
	if resource.Depth < agentResource.Depth {
	// There's a closer resource!
	agentResource = resource
	continue
	}
	if resource.Depth == agentResource.Depth && resource.Label < agentResource.Label {
	agentResource = resource
	continue
	}
	}

	if agentResource == nil {
	continue
	}

bug: tasks - foreign key violated when stopping workspace #18776

Description

Is there an existing issue for this?

Current Behavior

Relevant Log Output

Expected Behavior

Steps to Reproduce

Environment

Additional Context

Activity

hugodutka commented on Aug 2, 2025

spikecurtis commented on Aug 4, 2025

hugodutka commented on Aug 4, 2025

spikecurtis commented on Aug 4, 2025

mtojek commented on Aug 4, 2025

johnstcn commented on Aug 4, 2025

johnstcn commented on Aug 8, 2025

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions