# Data Model The source of truth is structured data. Markdown is only for docs and exported reports. ## Tables - identity: `developers`, `tenants`, `tenant_memberships`, `workspaces`, `workspace_memberships`, `mcp_tokens`, `setup_device_grants` - work: `tasks`, `workstreams`, `work_events`, `work_intents`, `work_embeddings`, `overlap_warnings`, `scope_verdicts`, `preflight_checks` - sessions and telemetry: `agent_sessions`, `agent_profiles`, `agent_spans`, `usage_events`, `agent_feedback_events`, `session_contexts`, `session_activity` - local capture: `repo_commits`, `local_worktrees` - reporting and sharing: `weekly_summaries`, `share_links`, `paxel_runs` - runtime config: `app_config` Most tables also carry a `workspace_id` scope column, omitted from the lists below. ## Developers - `id` - `tenant_id` - `workspace_id` - `auth_provider_user_id` - `name` - `email` - `handle` - `role` - `avatar_url` - `created_at` - `updated_at` Every task has a `workstream_id`; the server assigns a best matching or auto-created workstream when omitted (details in [`mcp-contract.md`](./mcp-contract.md)). Auto-created workstreams are grouping containers; overlap checks score the tasks inside them, not the container. Linked task events also store `workstream_id` so activity and summaries can show the parent scope. Planned tasks use the same `tasks` table with `status = 'planned'`; the owner can edit fields until start (edits write `task.planned_updated` and re-embed). ## Tenants - `id` - `external_id` - `workos_organization_id` - `name` - `slug` - `created_by_developer_id` - `created_at` - `updated_at` Tenants are the company/account boundary. In WorkOS mode, each tenant maps to a WorkOS Organization through `workos_organization_id`; without an AuthKit organization id, a stable `external_id` fallback tied to the provider user is used. ## Tenant Memberships - `id` - `tenant_id` - `developer_id` - `role` - `status` - `workos_membership_id` - `created_at` - `updated_at` Tenant memberships carry the account-level `admin` or `member` role. Tenant admins can create workspaces and users. ## Workspaces - `id` - `tenant_id` - `workos_organization_id` - `name` - `slug` - `created_by_developer_id` - `created_at` - `updated_at` Workspaces are team/project spaces inside a tenant. The old `workos_organization_id` column remains only for legacy migration; new WorkOS organization linkage belongs on `tenants`. ## Workspace Memberships - `id` - `workspace_id` - `developer_id` - `role` - `status` - `invited_email` - `invited_by_developer_id` - `workos_membership_id` - `created_at` - `updated_at` Workspace memberships scope product reads and writes. Members create MCP tokens for themselves; tokens log under the owner's developer id and inherit the workspace's tenant. ## MCP Tokens - `id` - `developer_id` - `tenant_id` - `workspace_id` - `token_hash` - `label` - `agent_type` - `revoked_at` - `created_at` - `last_used_at` `agent_type` is null for untyped tokens, or `claude-code`/`codex`/`cursor` for typed per-agent tokens, which win over the agent's self-reported type at session start. The device-flow setup mints one typed token per agent through `setup_device_grants` (device codes stored hashed, tokens delivered exactly once) — see [`device-setup.md`](./device-setup.md). Never store raw tokens. Structured exports include token metadata only. `token_hash` is not exported. ## Schema Health `/api/health` and `npm run doctor` verify required tables and columns. If an older legacy database is missing a column, health returns degraded status with `missing_columns` before agents start work. For the PostgreSQL production path, `npm run db:postgres:init` creates the same tables and installs tenant/workspace row-level-security policies. Workspace scoped tables use `app.workspace_id`; tenant scoped tables use `app.tenant_id`. `npm run smoke:postgres` verifies that a non-owner app role cannot read or write another workspace's rows. `npm run smoke:postgres:runtime` verifies the app repository path sets tenant/workspace scope and stores tenant-aware MCP tokens before agent tool calls. ## Agent Sessions - `id` - `developer_id` - `agent_profile_id` - `agent_type` - `provider` - `model` - `repo` - `branch` - `started_at` - `last_seen_at` - `ended_at` - `status` ## Performance Data `agent_profiles`, `agent_spans`, and derived Performance views are detailed in [`performance-data-model.md`](./performance-data-model.md). They store counters, provenance, hashes, and read-time rollups, not raw prompts or manager notes. ## Local Capture `repo_commits` and `local_worktrees` are written by the companion's `covibe_session` snapshot: recent commits with `parent_sha -> sha` lineage, branch, per-file status, and line deltas, plus one current-state row per (developer, repo, worktree, branch). They feed the per-repo local graph on Coordination. `session_activity` stores one counters row per session from the agent transcript (wall time, token/context counters, skill/subagent/tool counts, line deltas) via `covibe_session` `operation: "activity"`. All three store counts and paths only, never file or message content. ## Share Links `share_links` freezes a sanitized weekly-summary snapshot in `payload_json` at creation. The public read is by opaque token only and never joins tenant data; rows expire (`expires_at`) and can be revoked (`revoked_at`). ## Workstreams - `id` - `owner_id` - `title` - `scope` - `status` - `expected_duration` - `overlap_status` - `auto_created` - `started_at` - `completed_at` - `created_at` - `updated_at` Workstream and task statuses are `planned`, `active`, `blocked`, `done`, or `cancelled`. Planned workstreams use the same table with `status = 'planned'`, `auto_created = 0`, and a null `started_at` until activation; cancel is planned-only. ## Tasks - `id` - `developer_id` - `session_id` - `workstream_id` - `title` - `description` - `scope` - `action` - `component` - `priority` - `task_type` - `target_files_json` - `status` - `repo` - `branch` - `overlap_status` - `started_at` - `completed_at` - `created_at` - `updated_at` `task_type` is the declared kind of work (`feature|bug|refactor|chore|docs|test|spike`). It is REQUIRED at registration (`check`/`plan`/`start`) with no default; `start_planned` reuses the stored value. Rows created before the field existed stay null and render as `—`. ## Session Contexts - `id` - `tenant_id` - `workspace_id` - `developer_id` - `session_id` (nullable — a context outlives the agent session that wrote it) - `title` - `summary` (plain text: what I'm doing plus key decisions) - `files_touched_json`, `next_actions_json`, `blockers_json`, `task_ids_json` (JSON arrays; files are repo-relative paths) - `repo` - `branch` - `published_at` - `expires_at` (default 24h TTL, `ttl_hours` overrides) - `superseded_by` (set when a newer publish for the same repo/branch replaces it) - `revoked_at` (set when the owner revokes) - `created_at` - `updated_at` A context is active when it is unexpired, unrevoked, and not superseded. Inactive rows stay for audit. Reads redact private developers; metadata only — no prompts, transcripts, or file contents. See [`session-context.md`](./session-context.md). ## Work Events - `id` - `event_type` - `developer_id` - `session_id` - `task_id` - `workstream_id` - `summary` - `payload_json` - `created_at` Successful MCP tools write work events when they change work state; coordination claims and messages also live here. ## Usage Events - `id` - `developer_id` - `agent_session_id` - `tool_name` - `status` - `feedback_type` - `duration_ms` - `created_at` Every MCP tool call writes a usage event. ## Agent Feedback Events - `id` - `developer_id` - `tool_name` - `feedback_type` - `message` - `required_action` - `warning_id` - `created_at` Warnings and blocks are stored here so audits can prove agents received feedback. ## Preflight Checks - `id` - `developer_id` - `check_kind` - `title_key` - `warning_id` - `created_at` Task and workstream starts require a recent preflight check.