feat(db): migration 0010 — collapse `'builtin'` from runtime tables #939

New issue

Closed

opened 2026-05-08 08:56:27 +00:00 by claude-desktop · 0 comments

claude-desktop commented

2026-05-08 08:56:27 +00:00

Collaborator

As a service operator, I want a one-shot migration that drops every scope='builtin' row and rewrites the table CHECK constraints so they no longer permit 'builtin', so that the runtime invariant ("no builtin layer") is enforced by the database itself.

This is the load-bearing story of tracker #934. It must land after the constants + wizard stories (otherwise existing installs lose factory data) and before the SQL-filter cleanup (otherwise dropped filters allow stale builtin rows to leak).

Acceptance criteria

Migration `0010_collapse_builtin.sql`

Idempotent up-migration covering the runtime tables: agent_type, agent_type_config, agent_type_container, agent_type_routing, mcp_server, plugin_binding, plugin_marketplace, skill, system_prompt, service_config, label_catalog.
For service_config and label_catalog: before deleting the builtin rows, INSERT-OR-IGNORE the deleted values into the scope='global' layer so existing installs keep their factory data. (Wizard story handles fresh installs; this branch is the upgrade path for live DBs.)
After the upgrade-path inserts, DELETE FROM <table> WHERE scope='builtin' on every listed table.
Recreate each table with a CHECK clause that drops 'builtin' from the allowed scope set. SQLite-friendly approach: CREATE TABLE …_new, INSERT INTO …_new SELECT … FROM …, DROP TABLE …, ALTER TABLE …_new RENAME TO ….
Wrap the whole migration in a single transaction so a partial failure rolls back cleanly.

Schema files

Update Drizzle schema files (apps/server/src/infrastructure/database/schema/*.ts) to mirror the new CHECK clauses — drop the "still permits 'builtin'" allowance and the comments that justify it.

Tests

Migration test: seed a DB with one builtin row per affected table, run migration 0010, assert no builtin rows survive and the global row carries the upgraded values for service_config / label_catalog.
Migration test: a CHECK-violating insert (scope='builtin') post-migration fails with a CHECK constraint error.
Existing migration suite (apps/server/src/infrastructure/database/migrate.test.ts or similar) still green.

Out of scope

Removing the NON_BUILTIN_SCOPE_FILTER SQL fragment + Drizzle ne(*.scope, "builtin") predicates — that's the next chore. Keeping those filters in place during this migration is intentional defense in depth.
Frontend SCOPE_ORDER narrowing — separate web story.

References

Meta tracker: #934
DOB-2 / #794 — narrowed ConfigScope type but did not touch CHECK clauses
Current builtin allowances: apps/server/src/infrastructure/database/drizzle/0000_init.sql:7-63

As a service operator, I want a one-shot migration that drops every `scope='builtin'` row and rewrites the table CHECK constraints so they no longer permit `'builtin'`, so that the runtime invariant ("no builtin layer") is enforced by the database itself. This is the load-bearing story of tracker #934. It must land **after** the constants + wizard stories (otherwise existing installs lose factory data) and **before** the SQL-filter cleanup (otherwise dropped filters allow stale builtin rows to leak). ## Acceptance criteria ### Migration `0010_collapse_builtin.sql` - [ ] Idempotent up-migration covering the runtime tables: `agent_type`, `agent_type_config`, `agent_type_container`, `agent_type_routing`, `mcp_server`, `plugin_binding`, `plugin_marketplace`, `skill`, `system_prompt`, `service_config`, `label_catalog`. - [ ] For `service_config` and `label_catalog`: **before** deleting the builtin rows, INSERT-OR-IGNORE the deleted values into the `scope='global'` layer so existing installs keep their factory data. (Wizard story handles fresh installs; this branch is the upgrade path for live DBs.) - [ ] After the upgrade-path inserts, `DELETE FROM <table> WHERE scope='builtin'` on every listed table. - [ ] Recreate each table with a CHECK clause that drops `'builtin'` from the allowed scope set. SQLite-friendly approach: `CREATE TABLE …_new`, `INSERT INTO …_new SELECT … FROM …`, `DROP TABLE …`, `ALTER TABLE …_new RENAME TO …`. - [ ] Wrap the whole migration in a single transaction so a partial failure rolls back cleanly. ### Schema files - [ ] Update Drizzle schema files (`apps/server/src/infrastructure/database/schema/*.ts`) to mirror the new CHECK clauses — drop the "still permits `'builtin'`" allowance and the comments that justify it. ### Tests - [ ] Migration test: seed a DB with one builtin row per affected table, run migration 0010, assert no builtin rows survive and the global row carries the upgraded values for `service_config` / `label_catalog`. - [ ] Migration test: a CHECK-violating insert (`scope='builtin'`) post-migration fails with a CHECK constraint error. - [ ] Existing migration suite (`apps/server/src/infrastructure/database/migrate.test.ts` or similar) still green. ## Out of scope - Removing the `NON_BUILTIN_SCOPE_FILTER` SQL fragment + Drizzle `ne(*.scope, "builtin")` predicates — that's the next chore. Keeping those filters in place during this migration is intentional defense in depth. - Frontend SCOPE_ORDER narrowing — separate web story. ## References - Meta tracker: #934 - DOB-2 / #794 — narrowed `ConfigScope` type but did not touch CHECK clauses - Current builtin allowances: `apps/server/src/infrastructure/database/drizzle/0000_init.sql:7-63`

claude-desktop added the

area:database

type:user-story

labels

2026-05-08 08:57:30 +00:00