Collapse the workspace_<entity> relation tables into nullable workspace_id columns

[ivicac]

Rule change

The convention documented in .agents/resource-visibility.md says a workspace-scoped entity gets a
nullable workspace_id BIGINT column, not a workspace_<entity> relation table — and it then
carves out an exception: "Six relation tables deliberately remain … the resulting mixed state is
intentional, not drift."

That exception is withdrawn. The rule now applies to the existing tables on master too: collapse
every workspace_* relation table into a nullable workspace_id column on the related table.

workspace_user is the one exception that stays — it is genuinely many-to-many (a user belongs to
several workspaces with equal standing) and is correct as a relation table. workspace itself is not a
relation table and is untouched.

Scope — five tables

Relation table (module)	Target table (module)	Java entity	Call sites
workspace_connection (automation-configuration)	connection (platform-connection)	WorkspaceConnection	~14 files
workspace_api_key (automation-configuration)	api_key (platform-security)	WorkspaceApiKey	~2 files
workspace_data_table (automation-data-table)	data_table (platform-data-table)	WorkspaceDataTable	~4 files
workspace_knowledge_base (automation-knowledge-base)	knowledge_base (platform-knowledge-base)	WorkspaceKnowledgeBase	~4 files
workspace_mcp_server (automation-ai-mcp)	mcp_server (platform-mcp)	WorkspaceMcpServer	~24 files

Each also has a Workspace*Repository, a Workspace*Service/Impl, and in some cases a
Workspace*Facade built on top.

Why this is the right shape

• The code already assumes an owner, not a membership. WorkspaceConnectionRepository.findByConnectionId
  returns Optional<WorkspaceConnection>, not a List. workspace_knowledge_base,
  workspace_data_table and workspace_mcp_server all carry a UNIQUE (workspace_id, <entity>_id)
  constraint. Nothing exposes an API that puts one row in two workspaces.
• The current state is already inconsistent within a single module. knowledge_base uses the
  relation table, while its own child knowledge_base_source carries a plain workspace_id column
  (20260508000001_platform_knowledge_base_source.xml).
• It removes a join from every list query. These relations are read on nearly every workspace-scoped
  list and visibility check.

Design constraints

1. The column goes on a platform table, so it must be nullable. connection, api_key,
data_table, knowledge_base and mcp_server are all platform-owned and shared with embedded —
mcp_server even has a type (PlatformType) column and embedded writes rows through
EmbeddedMcpServerFacadeImpl. Embedded rows keep workspace_id = NULL; null is a real state, and the
Java field is Long, never long.

2. No foreign key to workspace. Follow the existing platform precedent,
platform-notification/…/20260720000003_notification_add_workspace_id.xml: nullable BIGINT, a plain
index on the column, no addForeignKeyConstraint — workspace is an automation-owned table and a
platform changelog must not depend on it. Read that file first; its comment block on empty <rollback/>
under MARK_RAN preconditions applies verbatim here. Confirm on the way through that nothing relied on
the current FK to block workspace deletion (there is no WorkspaceService.delete today).

3. These tables are released — do not edit init changelogs in place. workspace_connection and
workspace_knowledge_base are defined in 00000000000001_*_init.xml files and ship in v0.31.4. Each
collapse needs a new changelog per module: add column → backfill → drop the relation table.

4. Two tables have no uniqueness guarantee. workspace_connection and workspace_api_key have no
UNIQUE (workspace_id, …) constraint, so a duplicate row is possible in a customer database even though
the code never creates one. The backfill must pick deterministically (e.g. MIN(id)) rather than assume
one row, and should report anything it collapses.

5. workspace_data_table is a straight revert.
20260612000001_automation_data_table_workspace_relation.xml changesets -2/-3 migrated
data_table.workspace_id into the relation table and dropped the column. The new changelog undoes
exactly that, and must stay idempotent for databases that have run the round trip in both directions
(guard with columnExists / not columnExists preconditions, as that file already does).

6. workspace_mcp_server backfill has a hardcoded workspace. Changeset 20250827000002-2 assigns
every mcp_server row to workspace 1049. The reverse backfill must read from the relation table, not
re-derive that constant.

Work per table

• New Liquibase changelog in the platform module: addColumn workspace_id BIGINT (nullable) +
  createIndex, guarded by a not columnExists precondition with an empty <rollback/>.
• Backfill changeset: UPDATE <table> SET workspace_id = (SELECT … FROM workspace_<table> …),
  deduplicated where no unique constraint exists.
• Drop the relation table (separate changeset, guarded by tableExists).
• Delete the Workspace<Entity> domain class, its repository, its service interface + impl, and
  their tests.
• Move the field onto the target entity as Long workspaceId with @Column("workspace_id").
• Rewrite call sites: findAllByWorkspaceId on the relation repository becomes a
  findAllByWorkspaceId (or a workspace_id predicate) on the target repository; create(entityId, workspaceId) becomes a field set at creation; delete<Entity>(entityId) disappears because the
  column dies with the row.
• Check the facades that resolve visibility through these joins — WorkspaceConnectionFacadeImpl,
  WorkspaceKnowledgeBaseFacadeImpl — and the comments in them that describe the join table.

Docs to update in the same change

• .agents/resource-visibility.md — delete the "Six relation tables deliberately remain" paragraph;
  state that workspace_user is the only remaining relation table.
• CLAUDE.md — the cross-cutting bullet currently ends "Six pre-existing relation tables deliberately
  remain."
• docs/superpowers/specs/2026-07-25-workspace-relation-table-convention-revision.md — add a note that
  the grandfather clause was withdrawn, and why.

Out of scope

• workspace_user — stays as a relation table.
• The resource_grant table — named-user grants are the sanctioned polymorphic relation table and are
  unaffected.
• The visibility column model — orthogonal; workspace_id expresses ownership, visibility expresses
  reach.

Acceptance

• No workspace_<entity> table remains except workspace_user.
• grep -rn 'tableName="workspace' --include="*.xml" server returns only workspace and workspace_user
  creations plus the new drop changesets.
• A database restored from a v0.31.4 dump migrates with every row's workspace preserved, and embedded
  rows land with workspace_id IS NULL.
• ./gradlew check and ./gradlew testIntegration pass — the Testcontainers int tests build the schema
  from scratch and are the real proof the changelogs are correct.