feat: first-class workspace entities with membership and cross-workspace sharing

[ilblackdragon]

Problem / Motivation

Workspaces are currently implicit — each user_id string gets its own siloed data. The WorkspacePool in server.rs caches per-user Workspace instances, but there's no shared-workspace concept beyond workspace_read_scopes: Vec<String> on UserIdentity, which is a flat string list with no DB backing, no membership semantics, and no write sharing.

This means:

• No team collaboration — users can't share jobs, routines, or memory within a team workspace
• Cross-user reads are token-config-only — workspace_read_scopes is set at token creation, not manageable at runtime
• All data is scoped to a single user_id string — there's no way to assign a job or routine to a team/project rather than an individual
• No workspace lifecycle — can't create, archive, or transfer workspaces

Depends on Issue #1 (DB-backed user management) — workspaces need real user records to reference.

Proposed Solution

New Database Tables

CREATE TABLE workspaces (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name TEXT NOT NULL,
    slug TEXT NOT NULL UNIQUE,                  -- URL-safe identifier
    description TEXT NOT NULL DEFAULT '',
    status TEXT NOT NULL DEFAULT 'active',       -- active | archived
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    created_by UUID NOT NULL REFERENCES users(id),
    settings JSONB NOT NULL DEFAULT '{}'        -- workspace-level config overrides
);

CREATE TABLE workspace_members (
    workspace_id UUID NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    role TEXT NOT NULL DEFAULT 'member',         -- owner | admin | member | viewer (enforced in Issue #3)
    joined_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    invited_by UUID REFERENCES users(id),
    PRIMARY KEY (workspace_id, user_id)
);
CREATE INDEX idx_workspace_members_user ON workspace_members(user_id);

Data Model Changes

Every table currently scoped by user_id TEXT gains an optional workspace_id UUID column:

ALTER TABLE conversations ADD COLUMN workspace_id UUID REFERENCES workspaces(id);
ALTER TABLE agent_jobs ADD COLUMN workspace_id UUID REFERENCES workspaces(id);
ALTER TABLE memory_documents ADD COLUMN workspace_id UUID REFERENCES workspaces(id);
ALTER TABLE routines ADD COLUMN workspace_id UUID REFERENCES workspaces(id);
ALTER TABLE settings ADD COLUMN workspace_id UUID REFERENCES workspaces(id);

• workspace_id = NULL means personal/user-scoped (backward compatible)
• workspace_id = <uuid> means workspace-scoped — visible to all members of that workspace

Default Personal Workspace

Every user gets an implicit "personal" workspace (not stored in the workspaces table). Queries with workspace_id IS NULL AND user_id = ? continue to work unchanged. This ensures zero migration disruption for existing data.

New Database Traits

Add a WorkspaceMgmtStore sub-trait (distinct from the existing WorkspaceStore which handles memory documents):

#[async_trait]
pub trait WorkspaceMgmtStore {
    async fn create_workspace(&self, name: &str, slug: &str, created_by: Uuid) -> Result<WorkspaceRecord, DatabaseError>;
    async fn get_workspace(&self, id: Uuid) -> Result<Option<WorkspaceRecord>, DatabaseError>;
    async fn get_workspace_by_slug(&self, slug: &str) -> Result<Option<WorkspaceRecord>, DatabaseError>;
    async fn list_workspaces_for_user(&self, user_id: Uuid) -> Result<Vec<(WorkspaceRecord, String)>, DatabaseError>; // (workspace, role)
    async fn update_workspace(&self, id: Uuid, name: &str, description: &str) -> Result<(), DatabaseError>;
    async fn archive_workspace(&self, id: Uuid) -> Result<(), DatabaseError>;

    // Membership
    async fn add_workspace_member(&self, workspace_id: Uuid, user_id: Uuid, role: &str, invited_by: Option<Uuid>) -> Result<(), DatabaseError>;
    async fn remove_workspace_member(&self, workspace_id: Uuid, user_id: Uuid) -> Result<(), DatabaseError>;
    async fn list_workspace_members(&self, workspace_id: Uuid) -> Result<Vec<(UserRecord, String)>, DatabaseError>; // (user, role)
    async fn get_member_role(&self, workspace_id: Uuid, user_id: Uuid) -> Result<Option<String>, DatabaseError>;
    async fn update_member_role(&self, workspace_id: Uuid, user_id: Uuid, role: &str) -> Result<(), DatabaseError>;
    async fn is_workspace_member(&self, workspace_id: Uuid, user_id: Uuid) -> Result<bool, DatabaseError>;
}

Query Scoping Changes

Existing _for_user methods in JobStore, SandboxStore, SettingsStore, RoutineStore need parallel _for_workspace variants:

async fn list_agent_jobs_for_workspace(&self, workspace_id: Uuid) -> Result<Vec<AgentJobRecord>, DatabaseError>;
async fn list_sandbox_jobs_for_workspace(&self, workspace_id: Uuid) -> Result<Vec<SandboxJobRecord>, DatabaseError>;

Handler logic becomes: if request has ?workspace=<slug>, verify membership, query by workspace_id. Otherwise, query by user_id (personal scope).

WorkspacePool Refactor

WorkspacePool in src/channels/web/server.rs currently caches by user_id: String. It must support workspace-scoped memory:

• Cache key changes from user_id to (user_id, Option<workspace_id>)
• Memory documents in a workspace are readable/writable by all members
• Personal workspace memory remains user-scoped

Replace workspace_read_scopes

The current workspace_read_scopes: Vec<String> on UserIdentity is replaced by workspace membership. If a user is a viewer or member of workspace X, they can read its memory. The hybrid_search_multi method in WorkspaceStore already takes user_ids: &[String] — extend this to accept workspace IDs too.

New API Endpoints

POST   /api/workspaces                          — create workspace
GET    /api/workspaces                          — list my workspaces
GET    /api/workspaces/{slug}                   — get workspace details
PATCH  /api/workspaces/{slug}                   — update workspace
POST   /api/workspaces/{slug}/archive           — archive workspace
POST   /api/workspaces/{slug}/members           — add member
DELETE /api/workspaces/{slug}/members/{user_id} — remove member
PATCH  /api/workspaces/{slug}/members/{user_id} — update member role
GET    /api/workspaces/{slug}/members           — list members

Existing endpoints (/api/jobs, /api/routines, etc.) accept an optional ?workspace=<slug> query param to switch context.

Architecture Notes

• Workspace context propagation: The AuthenticatedUser extractor should be augmented or a new WorkspaceContext extractor added that resolves the optional ?workspace= param, verifies membership, and provides (UserIdentity, Option<WorkspaceRecord>) to handlers.
• IncomingMessage already carries user_id: String through the agent pipeline. Add an optional workspace_id: Option<String> alongside it rather than replacing user_id. The agent loop, JobContext, and tool execution all need to propagate this.
• The Workspace struct in src/workspace/ is the memory/document system — don't confuse it with the new workspace entity. Consider naming the new entity Team or Organization if naming collisions become painful, but workspace matches the user-facing concept better.
• Slug uniqueness: enforce at DB level (UNIQUE constraint) and validate format (lowercase alphanumeric + hyphens, 3-48 chars) at API level.

Code Pointers

• src/channels/web/server.rs — WorkspacePool, GatewayState — pool needs workspace-aware caching
• src/channels/web/auth.rs — UserIdentity.workspace_read_scopes — being replaced
• src/db/mod.rs — Database supertrait, JobStore, SandboxStore, SettingsStore — add WorkspaceMgmtStore and _for_workspace query variants
• src/workspace/ — memory system Workspace struct — needs workspace_id-aware document scoping
• src/agent/ — JobContext — needs optional workspace_id field
• src/channels/channel.rs — IncomingMessage — needs optional workspace_id
• src/tools/builtin/memory.rs — WorkspaceResolver / CachedWorkspaceResolver — cache key must include workspace context
• src/channels/web/handlers/jobs.rs — example of handler needing ?workspace= support

Acceptance Criteria

• workspaces and workspace_members tables in both PostgreSQL and libSQL
• workspace_id column added to conversations, agent_jobs, memory_documents, routines, settings (nullable, backward compatible)
• WorkspaceMgmtStore trait implemented for both backends
• API endpoints for workspace CRUD and membership management
• Membership verification before any workspace-scoped data access
• ?workspace=<slug> query param on existing /api/jobs, /api/routines, /api/memory/*, /api/settings endpoints
• Personal workspace (no workspace_id) continues to work identically to current behavior
• workspace_read_scopes field deprecated in favor of membership-based access
• WorkspacePool caches by (user_id, workspace_id) tuple
• Memory documents in a workspace visible to all workspace members
• SSE broadcasts for workspace-scoped events delivered to all workspace members
• Creator automatically added as owner role on workspace creation
• Archived workspaces return 410 Gone on data access
• Unit tests for WorkspaceMgmtStore (both backends)
• Integration tests for workspace create → add member → query scoped data → remove member → verify no access
• cargo clippy zero warnings, cargo fmt clean

Pitfalls & Landmines

• Naming collision: the existing Workspace struct in src/workspace/ is the persistent memory system, not a team/org entity. Be very careful with naming. The new DB entity should use distinct type names like WorkspaceEntity or TeamWorkspace in Rust to avoid confusion.
• Dual-backend: libSQL has no UUID type — use TEXT. No arrays — use JSON. See src/db/libsql_migrations.rs patterns.
• Nullable workspace_id migration: existing rows have workspace_id = NULL which means "personal". Queries must handle WHERE workspace_id IS NULL AND user_id = ? OR WHERE workspace_id = ? — don't accidentally return personal data when querying a workspace or vice versa.
• SSE broadcast fan-out: workspace-scoped events need to go to all online members. SseManager.broadcast_for_user() currently takes a single user_id. You'll need a broadcast_for_workspace() that resolves members and broadcasts to each. Be careful about N+1 DB queries — cache workspace membership.
• workspace_read_scopes backward compatibility: the env-var GATEWAY_USER_TOKENS path still supports workspace_read_scopes. Keep it working during the deprecation period, but map scopes to workspace memberships at startup if both systems are active.
• Don't change the existing WorkspaceStore trait in src/db/mod.rs — it handles memory documents and has methods like hybrid_search_multi. The new workspace management is a separate trait.

Non-Goals

• RBAC enforcement — roles are stored in workspace_members.role but not enforced yet. That's Issue Onboarding: show Telegram in channel selection and auto-install bundled channel #3.
• Admin UI — that's Issue feat: Sandbox jobs #4.
• Workspace-level billing or quotas — out of scope.
• Workspace templates or cloning — out of scope.

---

Part 2 of 4 — depends on #1605 (User Management). See also: RBAC (#3), Admin Panel (#4)