René's URL Explorer Experiment
Title: chore(db): drop the legacy folder tables and adopt the deferred folder_id FKs by waleedlatif1 · Pull Request #6051 · simstudioai/sim · GitHub
Open Graph Title: chore(db): drop the legacy folder tables and adopt the deferred folder_id FKs by waleedlatif1 · Pull Request #6051 · simstudioai/sim
X Title: chore(db): drop the legacy folder tables and adopt the deferred folder_id FKs by waleedlatif1 · Pull Request #6051 · simstudioai/sim
Description: Summary
Contract migration closing out the generic-folders cutover: drops workflow_folder and workspace_file_folders, and adopts the two folder_id foreign keys 0272 deliberately deferred.
Runs a final insert-only reconcile first, so no stranded legacy folder is lost by the drop. In production this is a no-op (verified 0 stranded); it exists for deployments that never ran the post-drain reconcile as an operational step — self-hosted upgrades above all, where a rolling restart strands folders the same way and nobody is watching for it.
Removes both table definitions from schema.ts and the testing schema mock.
Re-roots active children before the retention sweep hard-deletes a folder (see "FK side effect" below).
The thing to review closely: three re-root paths, three unique indexes
The migration re-roots rows in three places, and every destination is covered by a partial unique index keyed on a coalesced nullable column — so a name that is free in the source scope may already be taken in the destination:
index
key
folder_workspace_resource_parent_name_active_unique
(workspace_id, resource_type, coalesce(parent_id,''), name)
workflow_workspace_folder_name_active_unique
(workspace_id, coalesce(folder_id,''), name)
workspace_files_workspace_folder_name_active_unique
(workspace_id, coalesce(folder_id,''), original_name)
Three bugs were found and fixed here (all would abort the migration; all are 0-row in production and reachable on self-hosted):
Step 1's free-suffix probe consulted only the folder table. Stranded rows are by definition absent from it, so a stranded row keeping the name X (1) was invisible to the probe run for a stranded X — both got assigned X (1). Fixed by also probing the batch's own claimed names (kept).
Step 2 re-rooted dangling folder_ids with no dedupe at all, colliding on the workflow / workspace_files indexes. Note the asymmetry that gave it away: step 1 dedupes meticulously when it re-roots, step 2 didn't.
A stranded folder whose parent lives in another workspace passed the parent resolver but fails the folder_parent_resource_type_match trigger (the legacy self-FK never checked workspace). Now re-roots on mismatch.
Also: the stranded-row guard now keys on id alone, matching the primary key it protects, and suffix exhaustion falls back to the row id instead of the base name (which would guarantee a collision and report a misleading error).
FK side effect worth calling out
Adopting ON DELETE SET NULL changes what happens when the retention sweep hard-deletes a folder: previously the child's folder_id was left dangling (invisible in the sidebar), now Postgres re-roots it — an improvement, except it can collide on those same indexes, and chunkedBatchDelete turns a 23505 into hasMore = false, permanently stalling folder retention for that workspace chunk. reRootActiveFolderChildren now renames such children before the delete, leaving the SET NULL a no-op. Only workflow and workspace_files have these indexes; KB and tables do not.
Why the migration is hand-written
drizzle-kit generate produces a correct-looking version that is unsafe to run:
It drops the tables before adding the FKs, with no step to NULL unresolvable folder_ids. One dangling id makes ADD CONSTRAINT fail after the legacy tables are already gone — unrecoverable.
It adds the FKs with immediate validation. workspace_files is ~1.7M rows / 1.8GB, so that holds ACCESS EXCLUSIVE across a full scan of a hot table. This uses NOT VALID → COMMIT → VALIDATE, which takes only SHARE UPDATE EXCLUSIVE.
It uses DROP TABLE ... CASCADE. Nothing references either table, so this drops them plainly and lets the absence of CASCADE act as the safety check.
The generated snapshot is kept, so drizzle-kit generate reports "No schema changes".
Production preconditions (verified read-only before writing the migration)
0 stranded rows in either tree; 0 unresolvable folder_ids; 0 active rows filed under a soft-deleted folder.
FULL-ROW comparison (name, parent, deleted/archived state, workspace, user, sort order, locked) clean on both trees — a row count is not sufficient, since the pre-0274 divergence was in row contents.
The only divergence is 47 workflow-folder names, all matching (N) (verified by regex shape, not by the count matching) — 0272's deliberate dedup renames. That is exactly why the reconcile is insert-only: an upsert would revert all 47.
Testing
Executed end-to-end against local Postgres 17.9. The fixture is first run against the pre-fix migration to prove it reproduces all three bugs (it fails on Batch (1), Shared, and report.pdf), then against the fixed version. Asserted:
batch-internal suffix collision → Batch, Batch (1), Batch (2) (skips the suffix another batch row claims)
root-collision re-root → Shared / Shared (1), and two danglers from different ghost folders → Twin / Twin (1)
an archived dangler is re-rooted but never renamed (index is partial), coexisting with an active row of the same name
cross-workspace parent → re-rooted to NULL
stranded locked folder → locked preserved; stranded child of stranded parent → parent preserved
soft-deleted collider kept verbatim; already-mirrored rows untouched including a 0272 rename (insert-only confirmed)
FK rejects a dangling id; ON DELETE SET NULL fires; workflow_folder_sort_idx (an index on the live workflow table) survives the drop
re-running the whole file is a clean no-op with no double-suffixing — replay-safe
Typecheck, check:migrations, check:api-validation, lint, and 1196 tests pass.
Type of Change
Chore / migration
Checklist
Code follows project style guidelines
Self-reviewed my changes
Tests added/updated and passing
No new warnings introduced
I confirm that I have read and agree to the terms outlined in the Contributor License Agreement (CLA)
Open Graph Description: Summary
Contract migration closing out the generic-folders cutover: drops workflow_folder and workspace_file_folders, and adopts the two folder_id foreign keys 0272 deliberately deferred.
Runs a f...
X Description: Summary
Contract migration closing out the generic-folders cutover: drops workflow_folder and workspace_file_folders, and adopts the two folder_id foreign keys 0272 deliberately deferred.
Runs a f...
Opengraph URL: https://github.com/simstudioai/sim/pull/6051
X: @github
direct link
Domain: github.com
| react-profiling | 0 |
| react-import-map | react,react-dom,react-dom/client,react-dom/profiling,react-is,react-reconciler,react/compiler-runtime,react/jsx-dev-runtime,react/jsx-runtime,scheduler@d5750093facf |
| route-pattern | /:user_id/:repository/pull/:id/files(.:format) |
| route-controller | pull_requests |
| route-action | files |
| fetch-nonce | v2:3a12a59d-af4c-f439-9ed5-72c6100e3412 |
| current-catalog-service-hash | ae870bc5e265a340912cde392f23dad3671a0a881730ffdadd82f2f57d81641b |
| request-id | 9702:9CA74:59A44A:7C357F:6AFB3E7D |
| html-safe-nonce | fa2eb7e75b385e25eeba141c0d474cb37ba01efaa4a42e25725930b85d18e859 |
| visitor-payload | eyJyZWZlcnJlciI6IiIsInJlcXVlc3RfaWQiOiI5NzAyOjlDQTc0OjU5QTQ0QTo3QzM1N0Y6NkFGQjNFN0QiLCJ2aXNpdG9yX2lkIjoiMTE1Mjc0ODA1ODQ0MjQxNTc0MSIsInJlZ2lvbl9lZGdlIjoiaWFkIiwicmVnaW9uX3JlbmRlciI6ImlhZCJ9 |
| visitor-hmac | fa69d82b1fc12df6b052e2a6861b0e826cfa49fa9df4e85c523dc9bd067600aa |
| hovercard-subject-tag | pull_request:4162634027 |
| github-keyboard-shortcuts | repository,pull-request-list,pull-request-conversation,pull-request-files-changed,copilot |
| google-site-verification | Apib7-x98H0j5cPqHWwSMm6dNU4GmODRoqxLiDzdx9I |
| octolytics-url | https://collector.github.com/github/collect |
| analytics-location | ///pull_requests/show/files |
| fb:app_id | 1401488693436528 |
| apple-itunes-app | app-id=1477376905, app-argument=https://github.com/simstudioai/sim/pull/6051/files |
| twitter:image | https://avatars.githubusercontent.com/u/40672544?s=400&v=4 |
| twitter:card | summary_large_image |
| og:image | https://avatars.githubusercontent.com/u/40672544?s=400&v=4 |
| og:image:alt | Summary
Contract migration closing out the generic-folders cutover: drops workflow_folder and workspace_file_folders, and adopts the two folder_id foreign keys 0272 deliberately deferred.
Runs a f... |
| og:site_name | GitHub |
| og:type | object |
| hostname | github.com |
| expected-hostname | github.com |
| None | 2169b0dfbef67ed6b128074a5ed45bb7d5d1859783b10178b09a7765ad5f3693 |
| turbo-cache-control | no-preview |
| diff-view | unified |
| go-import | github.com/simstudioai/sim git https://github.com/simstudioai/sim.git |
| octolytics-dimension-user_id | 199344406 |
| octolytics-dimension-user_login | simstudioai |
| octolytics-dimension-repository_id | 912559512 |
| octolytics-dimension-repository_nwo | simstudioai/sim |
| octolytics-dimension-repository_public | true |
| octolytics-dimension-repository_is_fork | false |
| octolytics-dimension-repository_network_root_id | 912559512 |
| octolytics-dimension-repository_network_root_nwo | simstudioai/sim |
| turbo-body-classes | logged-out env-production page-responsive full-width |
| disable-turbo | true |
| browser-stats-url | https://api.github.com/_private/browser/stats |
| browser-errors-url | https://api.github.com/_private/browser/errors |
| release | 3b4fc8df5ef1c6757e733a23e6b81b6725021823 |
| ui-target | full |
| theme-color | #1e2327 |
| color-scheme | light dark |
Links:
| Skip to content | https://github.com/simstudioai/sim/pull/6051/files#start-of-content |
| https://github.com/ |
| Sign in | https://github.com/login?return_to=https%3A%2F%2Fgithub.com%2Fsimstudioai%2Fsim%2Fpull%2F6051%2Ffiles |
| GitHubRepository hosting, code review, and collaboration | https://github.com/platform |
| CopilotAI coding agent for tasks, projects, and automated work | https://github.com/features/copilot |
| ActionsCI/CD and automation | https://github.com/features/actions |
| Code QualityQuality analysis, fixes, and enforcement | https://github.com/features/code-quality |
| CodespacesCloud development environments | https://github.com/features/codespaces |
| Code SecurityVulnerability detection and remediation | https://github.com/security/advanced-security/code-security |
| Secret ProtectionSecret scanning and leak prevention | https://github.com/security/advanced-security/secret-protection |
| All Features | https://github.com/features |
| Documentation | https://docs.github.com/en |
| Training | https://learn.github.com/skills |
| Certifications | https://learn.github.com/credentials |
| Build with Copilot | https://github.com/features/ai |
| Customize Copilot | https://docs.github.com/en/copilot/how-tos/copilot-on-github/customize-copilot/customize-copilot-overview |
| Integrations | https://github.com/marketplace |
| Trending repos | https://github.com/trending |
| GitHub Sponsors | https://github.com/open-source/sponsors |
| Developer articles | https://github.com/resources/articles |
| ChangelogYour source for the latest features, improvements, and fixes across GitHub products, all in one place. | https://github.blog/changelog/ |
| Enterprises | https://github.com/enterprise |
| Team | https://github.com/team |
| Startups | https://github.com/enterprise/startups |
| Nonprofits | https://github.com/solutions/industry/nonprofits |
| Healthcare | https://github.com/solutions/industry/healthcare |
| Financial services | https://github.com/solutions/industry/financial-services |
| Manufacturing | https://github.com/solutions/industry/manufacturing |
| Government | https://github.com/solutions/industry/government |
| See all industries | https://github.com/solutions/industry |
| Why GitHub | https://github.com/why-github |
| Customer Stories | https://github.com/customer-stories |
| Business insights | https://github.com/solutions/executive-insights |
| Trust Center | https://github.com/trust-center |
| GitHub Advanced SecurityEnterprise-grade code security | https://github.com/security/advanced-security |
| Copilot for businessEnterprise-grade AI features | https://github.com/features/copilot/copilot-business |
| Premium SupportEnterprise-grade 24/7 support | https://github.com/enterprise/premium-support |
| Documentation | https://docs.github.com/en |
| Resource center | https://github.com/resources |
| Blog | https://github.blog/ |
| Events & webinars | https://github.com/resources/events |
| Community Forum | https://github.com/orgs/community/discussions |
| Customer Stories | https://github.com/customer-stories |
| Integrations | https://github.com/marketplace |
| Customer support | https://help.github.com/ |
| Training | https://learn.github.com/skills |
| GitHub Status | https://www.githubstatus.com/ |
| About | https://github.com/about |
| Partners | https://github.com/partners |
| Changelog | https://github.blog/changelog/ |
| GitHub UniverseThe event for developers, by developers — talks, workshops, and what’s next for GitHub. | https://githubuniverse.com/ |
| Pricing | https://github.com/pricing |
| Sign in | https://github.com/login?return_to=https%3A%2F%2Fgithub.com%2Fsimstudioai%2Fsim%2Fpull%2F6051%2Ffiles |
| Sign up | https://github.com/signup?ref_cta=Sign+up&ref_loc=header+logged+out&ref_page=%2F%3Cuser-name%3E%2F%3Crepo-name%3E%2Fpull_requests%2Fshow%2Ffiles&source=header-repo&source_repo=simstudioai%2Fsim |
| Reload | https://github.com/simstudioai/sim/pull/6051/files |
| Reload | https://github.com/simstudioai/sim/pull/6051/files |
| Reload | https://github.com/simstudioai/sim/pull/6051/files |
| simstudioai | https://github.com/simstudioai |
| sim | https://github.com/simstudioai/sim |
| Code | https://github.com/simstudioai/sim |
| Issues129 (129) | https://github.com/simstudioai/sim/issues |
| Pull requests202 (202) | https://github.com/simstudioai/sim/pulls |
| Actions | https://github.com/simstudioai/sim/actions |
| Projects | https://github.com/simstudioai/sim/projects |
| Security and quality | https://github.com/simstudioai/sim/security |
| Insights | https://github.com/simstudioai/sim/pulse |
| Sign up for GitHub
| https://github.com/signup?return_to=%2Fsimstudioai%2Fsim%2Fissues%2Fnew%2Fchoose |
| terms of service | https://docs.github.com/terms |
| privacy statement | https://docs.github.com/privacy |
| Sign in | https://github.com/login?return_to=%2Fsimstudioai%2Fsim%2Fissues%2Fnew%2Fchoose |
| waleedlatif1 | https://github.com/waleedlatif1 |
| staging | https://github.com/simstudioai/sim/tree/staging |
| feat/drop-legacy-folder-tables | https://github.com/simstudioai/sim/tree/feat/drop-legacy-folder-tables |
|
Conversation
31
| https://github.com/simstudioai/sim/pull/6051 |
|
Commits
2
| https://github.com/simstudioai/sim/pull/6051/commits |
|
Checks
26
| https://github.com/simstudioai/sim/pull/6051/checks |
|
Files changed
| https://github.com/simstudioai/sim/pull/6051/files |
| Please reload this page | https://github.com/simstudioai/sim/pull/6051/files |
|
chore(db): drop the legacy folder tables and adopt the deferred folder_id FKs
| https://github.com/simstudioai/sim/pull/6051/files#top |
|
Show all changes
2 commits
| https://github.com/simstudioai/sim/pull/6051/files |
|
c1dc251
chore(db): drop the legacy folder tables and adopt the deferred folde…
waleedlatif1 Jul 29, 2026
| https://github.com/simstudioai/sim/pull/6051/commits/c1dc25189511e13d73f94ff3f0db93324f6b7f96 |
|
797bdce
fix(db): stop the re-root dedupe renaming personal workflows
waleedlatif1 Jul 29, 2026
| https://github.com/simstudioai/sim/pull/6051/commits/797bdcee76d14443d3feef658604066a0c2cc244 |
|
Clear filters
| https://github.com/simstudioai/sim/pull/6051/files |
| Please reload this page | https://github.com/simstudioai/sim/pull/6051/files |
| Please reload this page | https://github.com/simstudioai/sim/pull/6051/files |
|
cleanup-soft-deletes.test.ts
| https://github.com/simstudioai/sim/pull/6051/files#diff-7e22c35b1e0f894a4804c728a349279bf85c81dc84d790ce61e3fa8af30c66ea |
|
cleanup-soft-deletes.ts
| https://github.com/simstudioai/sim/pull/6051/files#diff-0d27a0e21a9e9287bfa78ce2693f0694d417ae9bde9b2379b06f1d7bdd4052d6 |
|
batch-delete.test.ts
| https://github.com/simstudioai/sim/pull/6051/files#diff-df0c52c4e8c1b0100a3a616c95e3ed8b86a2ac5a24381d710ab0b5209b7db41c |
|
batch-delete.ts
| https://github.com/simstudioai/sim/pull/6051/files#diff-f07b958aaec9193a4b4c14e384154d004e0aa8d937949fcf60403269307315a5 |
|
workspace-file-manager.ts
| https://github.com/simstudioai/sim/pull/6051/files#diff-fcf305d0b37403347596bca42fc6889e9038c9f8e2c69ba20676c0f0d8fead34 |
|
0276_drop_legacy_folder_tables.sql
| https://github.com/simstudioai/sim/pull/6051/files#diff-cbfdd907aed42262229149ae067a7294196bc511993902ef8d0fe1c196397430 |
|
0276_snapshot.json
| https://github.com/simstudioai/sim/pull/6051/files#diff-acce71a6c11e8400b7ee8f1b5429e3a966b78c3de52f047fa6f752880d7fd1c5 |
|
_journal.json
| https://github.com/simstudioai/sim/pull/6051/files#diff-37150cc65ad16ee6fbc5fa318fc096be7730e16d63eaf863e3df5521b7030649 |
|
schema.ts
| https://github.com/simstudioai/sim/pull/6051/files#diff-9a959e2ce3cb4c86957bbae021d06eae364f7b0cb18964f5873bab53220af928 |
|
schema.mock.ts
| https://github.com/simstudioai/sim/pull/6051/files#diff-3b648ee44a059e5fc069169fdfd7c32c89200a3e79b5d0dc25227d3470da73af |
| apps/sim/background/cleanup-soft-deletes.test.ts | https://github.com/simstudioai/sim/pull/6051/files#diff-7e22c35b1e0f894a4804c728a349279bf85c81dc84d790ce61e3fa8af30c66ea |
|
View file
| https://github.com/simstudioai/sim/blob/797bdcee76d14443d3feef658604066a0c2cc244/apps/sim/background/cleanup-soft-deletes.test.ts |
|
Open in desktop
| https://desktop.github.com |
| https://github.co/hiddenchars |
| https://github.com/simstudioai/sim/pull/6051/{{ revealButtonHref }} |
|
| https://github.com/simstudioai/sim/pull/6051/files#diff-7e22c35b1e0f894a4804c728a349279bf85c81dc84d790ce61e3fa8af30c66ea |
|
| https://github.com/simstudioai/sim/pull/6051/files#diff-7e22c35b1e0f894a4804c728a349279bf85c81dc84d790ce61e3fa8af30c66ea |
|
| https://github.com/simstudioai/sim/pull/6051/files#diff-7e22c35b1e0f894a4804c728a349279bf85c81dc84d790ce61e3fa8af30c66ea |
|
| https://github.com/simstudioai/sim/pull/6051/files#diff-7e22c35b1e0f894a4804c728a349279bf85c81dc84d790ce61e3fa8af30c66ea |
|
| https://github.com/simstudioai/sim/pull/6051/files#diff-7e22c35b1e0f894a4804c728a349279bf85c81dc84d790ce61e3fa8af30c66ea |
|
| https://github.com/simstudioai/sim/pull/6051/files#diff-7e22c35b1e0f894a4804c728a349279bf85c81dc84d790ce61e3fa8af30c66ea |
|
| https://github.com/simstudioai/sim/pull/6051/files#diff-7e22c35b1e0f894a4804c728a349279bf85c81dc84d790ce61e3fa8af30c66ea |
|
| https://github.com/simstudioai/sim/pull/6051/files#diff-7e22c35b1e0f894a4804c728a349279bf85c81dc84d790ce61e3fa8af30c66ea |
| Please reload this page | https://github.com/simstudioai/sim/pull/6051/files |
| Please reload this page | https://github.com/simstudioai/sim/pull/6051/files |
|
| https://github.com |
| Terms | https://docs.github.com/site-policy/github-terms/github-terms-of-service |
| Privacy | https://docs.github.com/site-policy/privacy-policies/github-privacy-statement |
| Security | https://github.com/security |
| Status | https://www.githubstatus.com/ |
| Community | https://github.community/ |
| Docs | https://docs.github.com/ |
| Contact | https://support.github.com?tags=dotcom-footer |
Viewport: width=device-width
URLs of crawlers that visited me.