Workspace data residency

Voice: Product capabilities and UI copy follow user language. This page describes which database layer data lives on, for implementation and integration mapping.

Source docs/en/site/mech-data-residency.md

Voice: Product capabilities and UI copy follow user language. This page describes which database layer data lives on, for implementation and integration mapping.

1. Principles

Where it lives	How to decide
Main schema (`public` / main DB)	Account identity, login, admin console, cross-workspace directory and member ties, global product templates; data that must still exist after a workspace is deleted
Workspace schema (`workspacens`: Postgres `ws_*` or SQLite `workspaces/{id}/plugin_ns.db`)	Business content that only makes sense in that workspace; may be destroyed together when the workspace is deleted

User accounts do not enter the workspace schema (one person may belong to many workspaces). In-workspace “member / handler” stores only a user ID soft reference.

2. Kept on the main schema

Users and auth: users, refresh_tokens; admin admin_*
Workspace directory and members: workspaces, workspace_members, invites/join requests, and similar (listing “my workspaces” after login does not fan out every shard)
Namespace registry: workspace_data_namespaces
Global template catalog: e.g. agent_templates
Platform plugin registration (mostly config, not tenant business tables)

3. Moved into the workspace schema (by domain)

Domain	Content	Status
Plugin business	HR `hr_*` and similar	Shipped
Workflows	`workflow_*` (including work tickets)	Shipped
App desktop	`workspace_apps` / suites / templates	Shipped
Standards / assets / taxonomy	`workspace_standards` / assets / taxonomy*	Shipped
Data sources	sources / datasets / grants / mutations	Shipped
Agents	`user_agents`, train jobs, embed tokens (workspace-owned)	Shipped (admin fans out shards)
Workspace-owned skills	`user_conversation_skills` and versions/files	Shipped
Conversations / messages	`chat_sessions` / `chat_messages`	Shipped (workspace-owned agent/scheduled); help / no workspace still on the main DB

The same workspace shares one ws_* / plugin_ns.db with HR; Go side wsdb.With unifies migrate + empty-store copy by domain (ws_residency_meta).

4. Gray area

Uploads: currently by user; multi-workspace references need an object index + workspace-side refs or per-workspace buckets — a dedicated design.
No workspace selected / help-assistant conversations: stay on the main schema (do not enter workspace shards).
Cross-workspace to-dos / admin overview: fan out shards; conversation detail still mainly help behavior on the main DB; workspace-owned conversations need fan-out fill-in.
Scheduled-task tables agent_scheduled_tasks: still on the main DB; agent names resolved from shards.
Agent team meetings agent_teams / agent_team_meetings / coding_*: stay on the main DB like im_* (project groups are on the main DB); role agents still resolved from workspace shards.

4.1 Dual conversation paths

Create: session_kind=help or no workspace → main DB; workspace-owned agent / scheduled → workspace shard (depends on WorkspaceDataNS injection).
Read/write by conversation: query the injected shard first, then fall back to the main DB (resolveChatDB).
Empty-store copy: domain chat, only workspace_id=? AND session_kind IN ('agent','scheduled','workflow') and their messages; help is not copied.
Sidebar recent activity: with a workspace, shard query for workspace-owned conversations + main DB query for help conversations, then merge.

5. Access convention

Ensure(workspace) → open workspace shard → wsdb migrate → copyIfNeeded by domain → main DB DELETE by workspace → business read/write
On startup FinalizeMainResidency: after all workspaces reside, DROP migrated tables (keep chat_*)

HTTP: WorkspaceDataNS middleware injects the shard for requests that carry a workspace (URL {id} or signed-in current workspace). Store prefers writing the shard via DBTXFromContext / BeginTx. Cross-workspace read-only (admin agent lists and similar) fans out via WithWorkspaceShard.

Background tasks (scheduled runs, memory adopt, and similar) have no HTTP middleware; they must open the target workspace shard with store.WithWorkspaceDBTX. When a stream/long task detaches from request cancel, use store.DetachContextKeepingDBTX to keep the injected connection; do not use a bare context.Background().

Main-DB directory tables such as workspaces always query the main *sql.DB; do not go through shard DBTX (SQLite shards do not have that table).

5.1 Main-DB cleanup

Delete rows by workspace: after each domain copies successfully (or is marked copied), purgeMainDomain deletes that workspace’s matching rows from the main DB.
Conversation exception: only delete session_kind IN ('agent','scheduled','workflow'); help conversation rows stay on main-DB chat_*.
DROP tables: FinalizeMainResidency, after every workspace has finished residing, DROPs migrated business tables from the main schema (user_agents, workflows, apps, standards, data sources, skills, embed/train, and similar); does not DROP chat_sessions / chat_messages.
Postgres DROP uses CASCADE: drops FKs from remaining main tables (e.g. agent_scheduled_tasks, im_*) that pointed at user_agents; columns stay as soft references; those dependent tables are not deleted.
After Finalize succeeds, write mindlink_schema_meta.main_resided_tables_dropped; later main-DB Open skips CREATE/ALTER of migrated tables, and no longer “create empty shells then DROP”.

6. Related implementation

backend/internal/workspacens/
backend/internal/wsdb/
backend/internal/middleware/workspace_ns.go
HR sample: plugins/hr/lib/db.ts, migrate.ts