fix: make workspace id change faster and limit to 100k jobs

[hugocasa]

No description provided.

[claude]

Claude finished @hugocasa's task in 1m 27s —— View job

---

PR Review: Make Workspace ID Change Faster

• Analyze PR changes and context
• Review backend changes (workspaces_extra.rs)
• Review frontend changes (ChangeWorkspaceId.svelte)
• Review SQL query files
• Provide comprehensive feedback
• Add reproduction instructions

---

Summary

This PR makes two key improvements to the workspace ID change functionality:

1. 100k job limit: Adds a pre-check to prevent workspace ID migration for workspaces with more than 100,000 jobs, directing users to use the CLI instead
2. Faster migration: Changes the job migration logic to only migrate completed jobs and non-running queued jobs (parent jobs only), skipping running jobs and child jobs
3. Missing triggers: Adds migration support for postgres_trigger, mqtt_trigger, gcp_trigger, and sqs_trigger tables that were previously missing

Code Quality

Positives:

• Clean, consistent code style matching the existing codebase
• Proper error handling with descriptive error messages
• Good use of transactions for atomic operations
• Added FOR UPDATE SKIP LOCKED to avoid blocking running jobs

Issues to address:

• See inline comments for performance optimization suggestion on the COUNT query
• The child job migration behavior should be documented or reconsidered (see inline comment)

Security

No security concerns identified. The authorization checks remain in place.

Performance

The new approach is faster because:

• It skips running jobs entirely
• Uses FOR UPDATE SKIP LOCKED to avoid waiting on locked rows
• The 100k limit prevents very large, slow migrations

However, the COUNT(*) query could be optimized for workspaces with millions of jobs.

---

Reproduction Instructions

Navigate to the Workspace Settings page (gear icon → Settings → General tab). Click the pencil/edit icon next to the "Workspace ID" field. A modal will appear with two alerts: one info alert explaining the operation may take a few minutes, and one warning alert about running jobs not being migrated. Enter a new workspace name and ID, then click "Save". If your workspace has more than 100,000 jobs, you should see an error message directing you to use the Windmill CLI instead.

[claude]

Performance consideration: The COUNT(*) query on v2_job without any index hint may be slow for workspaces with many jobs. Since we're just checking if it exceeds 100k, consider using an early exit approach:

Suggested change

	.unwrap_or(0);
	// Check total job count before attempting migration
	let job_count = sqlx::query_scalar!(
	"SELECT COUNT(*) FROM v2_job WHERE workspace_id = $1 LIMIT 100001",
	&old_id
	)

Actually, LIMIT doesn't work with COUNT(*) this way. An alternative is to use a subquery:

SELECT COUNT(*) FROM (SELECT 1 FROM v2_job WHERE workspace_id = $1 LIMIT 100001) AS t

This stops counting after 100001 rows, making the check faster for workspaces with millions of jobs.

[claude]

Potential issue: This query has a subtle correctness concern. The logic is:

1. Update v2_job_queue rows where running = false AND the job has no parent
2. Then update v2_job where the workspace_id was already updated in v2_job_queue OR v2_job_completed

However, this means:

• Running jobs are not migrated (intentional based on the UI warning)
• Child jobs (with parent_job IS NOT NULL) are not migrated - this could leave orphaned child job records

Is excluding child jobs intentional? If so, the frontend warning should mention this. If not, you may want to migrate child jobs along with their parents.

[claude]

Good catch adding these missing trigger types! The postgres_trigger, mqtt_trigger, gcp_trigger, and sqs_trigger tables were missing from the workspace migration. This is an important fix.

[claude]

Good UX improvement: Splitting the alerts into an info alert and a warning alert makes the messaging clearer. The warning about running jobs not being migrated is helpful for users to understand what to expect.

[cloudflare-workers-and-pages]

Deploying windmill with    Cloudflare Pages

Latest commit:	a342210
Status:	✅  Deploy successful!
Preview URL:	https://e4d4a8bf.windmill.pages.dev
Branch Preview URL:	https://hc-make-workspace-id-change.windmill.pages.dev

View logs