Data Model

# Data Model

The platform runs on Supabase Postgres with Row Level Security (RLS) enforced on all tables. Every table is scoped by `tenant_id` for multi-tenant isolation. This document covers the table inventory, relationships, access patterns, and common pitfalls.

## Core tables

### entities

The central table. All structured data -- regardless of domain -- lives here.

| Column | Type | Notes |
|---|---|---|
| `id` | uuid | Primary key (default `gen_random_uuid()`) |
| `tenant_id` | uuid | FK tenants, RLS scoping |
| `entity_type_id` | uuid | FK entity_types |
| `entity_type_slug` | text | Denormalized, auto-synced via `sync_entity_type_slug` trigger |
| `slug` | text | URL-safe identifier |
| `title` | text | Display name |
| `content` | jsonb | Typed fields defined by entity_type.json_schema |
| `metadata` | jsonb | System metadata (e.g., `lockedFields`, scoring data) |
| `tags` | text[] | GIN-indexed tag array |
| `owner_id` | uuid | FK auth.users |
| `parent_id` | uuid | FK entities (nullable, for hierarchical entities) |
| `created_at` | timestamptz | Auto-set |

Use `entity_type_slug` for filtering queries -- it avoids a join to resolve slug to UUID:

```sql
-- Correct: direct slug filter
SELECT * FROM entities WHERE tenant_id = $1 AND entity_type_slug = 'opportunity';

-- Avoid: slug -> UUID lookup
SELECT * FROM entities WHERE entity_type_id = (
  SELECT id FROM entity_types WHERE slug = 'opportunity'
);
```

Task work-model queries also rely on task-scoped expression indexes on `entities.content` rather than legacy `public.tasks.metadata` reads. Migration `20260423000000_task_entity_work_model_indexes.sql` backfills missing `content.delegation_state` to `human_only` for `entity_type_slug='task'`, then maintains these hot-path indexes:

- `(tenant_id, (content ->> 'delegation_state')) WHERE entity_type_slug = 'task'`
- `(tenant_id, (content ->> 'work_category')) WHERE entity_type_slug = 'task' AND content ? 'work_category'`
- `(tenant_id, (content ->> 'measurement_cadence')) WHERE entity_type_slug = 'task' AND content ? 'measurement_cadence'`
- `(tenant_id, ((content ->> 'readiness_score')::numeric)) WHERE entity_type_slug = 'task' AND content ? 'readiness_score'`

These keep Command Center filters and delegation-queue loads on the entity-backed Task model.

### entity_types

Schema definitions for entity types. Each row defines a data type with its JSON schema, field configuration, and UI settings.

| Column | Type | Notes |
|---|---|---|
| `id` | uuid | **No default** -- must generate via `crypto.randomUUID()` |
| `slug` | text | URL-safe identifier (e.g., "opportunity", "company") |
| `name` | text | Display name |
| `json_schema` | jsonb | JSON Schema defining the entity's fields |
| `config` | jsonb | `EntityTypeConfig`: scoring, UI, AI enrichment, field configs |
| `tenant_id` | uuid | Nullable -- NULL means global (visible to all tenants) |
| `visibility` | text | Access control |
| `version_number` | integer | Schema version tracking |

### entity_relations

Graph edges connecting entities. Directional but often queried in both directions.

| Column | Type | Notes |
|---|---|---|
| `from_entity_id` | uuid | FK entities |
| `to_entity_id` | uuid | FK entities |
| `relationship_type` | text | E.g., "relates-to", "parent-of" |
| `tenant_id` | uuid | FK tenants |
| `metadata` | jsonb | First-class relation fields stamp `{ field, rank, source }` here — see below |

Composite indexes exist on `(from_entity_id)` and `(to_entity_id)` for efficient graph traversal. Two partial indexes on `(tenant_id, from_entity_id, ((metadata->>'field')))` and `(tenant_id, to_entity_id, ((metadata->>'field')))` support the first-class relation field queries (filter by field, sort by rank).

**Relation field metadata shape:**

```json
{
  "field": "target_markets",
  "rank": 0,
  "source": "manual" | "ai" | "extraction" | "response"
}
```

When a relation is created via a first-class relation field declared on `FieldConfig.relation`, `metadata.field` records which field owns the row so the data table can filter "product ideas where target_markets = Enterprise SaaS". Rankable fields additionally stamp `metadata.rank` with the 0-indexed position so users can drag-drop to reorder and the order round-trips through the picker. Legacy relations without `metadata.field` are still visible in "any connection" queries and match on `relationship_type` as a fallback.

Bundle seed relations may also write domain-specific keys into `metadata` (e.g., `ownershipPct`, `ownershipKind` for the Structure Map workspace). Migration `20260612060000_install_bundle_seed_relation_metadata.sql` redefines `install_bundle` to merge seed `metadata` alongside the `installed_by_bundle` marker rather than overwriting it, so multiple metadata namespaces coexist on the same row.

### activities

Every entity write (create, update, delete) generates an activity record. This powers the activity feed and provides a full audit trail of data changes.

| Column | Type | Notes |
|---|---|---|
| `tenant_id` | uuid | FK tenants |
| `entity_id` | uuid | FK entities |
| `actor_id` | uuid | FK auth.users |
| `action` | text | E.g., "created", "updated", "deleted" |
| `title` | text | Human-readable description |
| `metadata` | jsonb | Additional context (changed fields, old values) |

## Auth tables

### profiles

User profile data. Created automatically on signup via a Supabase Auth trigger.

| Column | Type | Notes |
|---|---|---|
| `id` | uuid | FK auth.users (primary key) |
| `email` | text | User's email |
| `display_name` | text | Display name |
| `active_tenant_id` | uuid | Last selected workspace |

### user_tenants

Tenant membership. **This is the membership table -- NOT `tenant_members`.**

| Column | Type | Notes |
|---|---|---|
| `user_id` | uuid | FK auth.users |
| `tenant_id` | uuid | FK tenants |
| `role_id` | uuid | FK roles |

Unique constraint on `(user_id, tenant_id)`. When a row is inserted, a trigger runs `rebuild_user_permissions()` to materialize the user's effective permissions.

### roles

RBAC role definitions. Six global roles ship with the platform:

| Slug | UUID | Notes |
|---|---|---|
| `system_admin` | `00000000-0000-0000-0000-000000000010` | Owner -- all permissions |
| `tenant_admin` | `00000000-0000-0000-0000-000000000011` | Admin -- team + own level |
| `editor` | `00000000-0000-0000-0000-000000000012` | Editor -- own CRUD + team read |
| `member` | `00000000-0000-0000-0000-000000000001` | Member -- own CRUD + team read |
| `viewer` | `00000000-0000-0000-0000-000000000013` | Viewer -- read-only |
| `guest` | `00000000-0000-0000-0000-000000000014` | **Default signup role** -- read-only |

Global roles have `tenant_id IS NULL` and `is_global_role = true`. Tenant-specific custom roles can also be created.

### role_permissions

Maps roles to permissions from the `app_permission` enum (63 values). Format: `{resource}.{level}.{action}`.

### user_permissions

Materialized view of effective permissions per user per tenant. Built from `role_permissions` joined through `user_tenants`. Rebuilt automatically when `role_permissions` changes (via trigger `tg_sync_permissions_from_role_permissions`).

The `authorize(permission)` SQL function checks this table for the current user. RLS policies call `authorize()` to gate access.

## Agent tables

### agents

AI agent definitions. Can be code-defined (via `registerAgent()`) or DB-managed (created in Admin > Agents).

| Column | Type | Notes |
|---|---|---|
| `tenant_id` | uuid | FK tenants |
| `slug` | text | Unique identifier |
| `name` | text | Display name |
| `description` | text | What the agent does |
| `icon` | text | Icon identifier |
| `system_prompt` | text | Agent's system prompt |
| `model` | text | LLM model identifier |
| `config` | jsonb | `AgentConfig`: `{ toolGroups?, customTools?, skills?, heartbeat? }` |
| `is_system` | boolean | Whether it's a system agent |
| `enabled` | boolean | Whether it's active |
| `created_by` | uuid | FK auth.users (nullable) |
| `role_id` | uuid | FK roles -- determines permissions for autonomous execution |
| `reports_to` | uuid | FK agents (nullable, for org chart) |
| `role` | text | Org chart role title |
| `title` | text | Org chart display title |

### agent_connections

External agent connections (OpenClaw, A2A protocol, MCP).

| Column | Type | Notes |
|---|---|---|
| `tenant_id` | uuid | FK tenants |
| `name` | text | Connection name |
| `type` | text | `openclaw`, `a2a`, or `mcp` |
| `url` | text | Connection endpoint |
| `credentials` | jsonb | Encrypted credentials |
| `status` | text | Connection status |
| `config` | jsonb | Connection-specific configuration |
| `created_by` | uuid | FK auth.users |

### agent_heartbeat_runs

Logs of autonomous agent executions triggered by cron schedules.

| Column | Type | Notes |
|---|---|---|
| `agent_id` | uuid | FK agents |
| `tenant_id` | uuid | FK tenants |
| `status` | text | running, completed, failed |
| `prompt` | text | The prompt used for this run |
| `response` | text | Agent's response |

### agent_config_versions

Version history for agent configuration and prompt changes. Supports rollback.

| Column | Type | Notes |
|---|---|---|
| `agent_id` | uuid | FK agents |
| `version_number` | integer | Sequential version |
| `config` | jsonb | Snapshot of agent config |
| `system_prompt` | text | Snapshot of system prompt |

## Chat tables

### chats

Chat session metadata. Each chat belongs to a user and optionally scopes to an entity.

| Column | Type | Notes |
|---|---|---|
| `tenant_id` | uuid | FK tenants |
| `user_id` | uuid | FK auth.users |
| `title` | text | Chat title (auto-generated or user-set) |
| `entity_id` | uuid | FK entities (nullable -- entity-scoped chats) |
| `entity_type_slug` | text | For entity-scoped context |
| `agent_id` | uuid | FK agents (nullable) |

### messages

Chat messages with AI SDK v6 support. **Use this table, NOT `chat_messages`** (which is retired to `_legacy_chat_messages`).

| Column | Type | Notes |
|---|---|---|
| `chat_id` | uuid | FK chats |
| `role` | text | `user`, `assistant`, `system` |
| `content` | text | Plain text content |
| `parts` | jsonb | AI SDK v6 parts array (tool calls, tool results, rich content) |
| `sender_type` | text | Maps to role for backward compatibility |
| `sender_id` | uuid | FK auth.users |
| `completion_id` | text | AI completion tracking |
| `metadata` | jsonb | Arbitrary metadata |

## Tool tables

### tool_runs

Execution log for all tool runs (human via form, agent via chat, external via API).

| Column | Type | Notes |
|---|---|---|
| `tenant_id` | uuid | FK tenants |
| `tool_slug` | text | Tool identifier |
| `user_id` | uuid | FK auth.users |
| `input` | jsonb | Tool input parameters |
| `output` | jsonb | Tool output |
| `status` | text | success, error |
| `duration_ms` | integer | Execution time |
| `source` | text | `web`, `agent`, or `api` |
| `chat_id` | uuid | FK chats (nullable -- if run from chat) |
| `session_id` | uuid | FK tool_sessions (nullable -- if part of collaborative session) |

### tool_sessions

Collaborative tool sessions where multiple users submit inputs and view aggregated results.

| Column | Type | Notes |
|---|---|---|
| `tenant_id` | uuid | FK tenants |
| `tool_slug` | text | Tool identifier |
| `title` | text | Session title |
| `entity_ids` | uuid[] | Associated entities |
| `config` | jsonb | Session configuration |
| `status` | text | `active`, `completed`, `cancelled` |
| `share_token` | text | Unique shareable link token |
| `created_by` | uuid | FK auth.users |

## Work execution tables

The legacy workflow and extraction runtime tables were removed in the unified
sessions migration. Current work execution uses visible `actions`, one runtime
`sessions` table, append-only `session_events`, and submitted
`entity_responses`.

### actions

Visible units of work created by people, agents, system sync, or automation.

| Column | Type | Notes |
|---|---|---|
| `tenant_id` | uuid | FK tenants |
| `slug` | text | Unique within `(tenant_id, workspace_id)` |
| `name` | text | Human-readable title |
| `description` | text | Optional long-form description |
| `instructions` | text | Prompt or playbook the executor receives |
| `entity_id` | uuid | Optional target entity |
| `entity_type_id` | uuid | Optional target entity type |
| `trigger_type` | text | manual, cron, entity_created, entity_updated, field_changed, webhook |
| `trigger_config` | jsonb | Trigger-specific parameters (cron expression, field filters, …) |
| `output_type` | text | field, fields, entity, entities, relation-entity, document, status, none |
| `output_config` | jsonb | Output contract (target field, schema, …) |
| `agent_slug` | text | Optional agent assignee (resolved from `agents.slug`) |
| `assigned_to` | uuid | Optional user assignee (FK auth.users) |
| `parent_id` | uuid | Optional FK to parent action for nested DAGs |
| `depends_on` | text[] | Slugs of upstream actions in the DAG |
| `status` | text | draft, active, paused, disabled, completed |
| `priority` | text | Optional priority bucket |
| `due_at` | timestamptz | Optional deadline |
| `last_run_at` | timestamptz | Most recent run timestamp |
| `last_run_status` | text | Result of the last run |
| `run_count` | int | Total runs to date |
| `is_system` | bool | Bundle / built-in actions are flagged so they survive cleanup |
| `bundle_version`, `installed_by_bundle`, `installed_by_workspace_id` | — | Bundle provenance (cleared on workspace override-clone) |
| `metadata` | jsonb | Caller-defined extras |

### sessions and session_events

Runtime execution state and transcript. Sessions hold the current status and
ownership for a run; session events hold the ordered activity stream, tool calls,
messages, elicitation events, errors, and lifecycle transitions.

| Table | Purpose |
|---|---|
| `sessions` | One row per agent run, action run, response draft, tool run, or automation execution |
| `session_events` | Append-only event log for transcript, observability, replay, and realtime UI updates |

### entity_responses

Canonical submitted values. Manual edits, agent-generated values, extraction
outputs, and scored responses all converge here before promotion into
`entities.content`.

| Column | Type | Notes |
|---|---|---|
| `tenant_id` | uuid | FK tenants |
| `entity_id` | uuid | Target entity |
| `target_type` | text | Field, criteria set, or structured response target |
| `values` | jsonb | Submitted values keyed by field or dimension |
| `source` | text | Manual, agent, API, extraction, or feedback |
| `status` | text | Submitted, promoted, rejected, or superseded |
| `session_id` | uuid | Runtime session that produced the response |
| `promoted_fields` | text[] | Values promoted into `entities.content` |

## Document tables

### documents

File metadata for uploaded documents.

| Column | Type | Notes |
|---|---|---|
| `tenant_id` | uuid | FK tenants |
| `entity_id` | uuid | FK entities (nullable -- can be unattached) |
| `title` | text | Document title |
| `file_name` | text | Original filename |
| `file_path` | text | Storage path |
| `file_size` | integer | Size in bytes |
| `mime_type` | text | MIME type |
| `status` | text | Processing status |
| `metadata` | jsonb | Extracted metadata |
| `uploaded_by` | uuid | FK auth.users |

### document_chunks

Chunked document content for hybrid search and RAG.

| Column | Type | Notes |
|---|---|---|
| `document_id` | uuid | FK documents |
| `chunk_index` | integer | Sequential chunk position |
| `content` | text | Chunk text content |
| `metadata` | jsonb | Chunk metadata |
| `embedding` | vector | Vector embedding (optional, requires `vector` extension) |

## Additional tables

| Table | Purpose |
|---|---|
| `tenants` | Workspace definitions (slug, name, tenant_type, logo_url) |
| `views` | Configurable block layouts. Key columns: `blocks` (jsonb flat map `Record<string,BlockConfig>`), `block_order` (text[] display order), `data_sources` (jsonb named entity queries), `layout`, `scope`, `page_type`, `publish_token`, `publish_status`, `publish_config` |
| `nav_configs` | Sidebar navigation configuration (tenant-level + user-level overrides) |
| `comments` | Threaded comments on entities (one level of nesting via `parent_id`) |
| `user_favorites` | Bookmarked entities (unique on user_id + entity_id) |
| `user_recent_views` | Recently viewed entities with timestamps |
| `skills` | Reusable agent instruction modules (slug, name, instructions, category) |
| `agent_skills` | Agent-to-skill junction table |
| `cost_events` | AI usage cost tracking (model, tokens, cost_cents) |
| `webhook_endpoints` | Outgoing webhook configuration (url, secret, events, enabled) |
| `api_keys` | Programmatic API access (key_hash, key_prefix, scopes) |
| `notifications` | User notifications (type, title, body, read, link) |
| `chat_feedback` | Message-level feedback (rating: up/down) |
| `shared_context` | Tenant-level corrections and learnings (key-value store) |
| `analytics_events` | Event tracking for analytics |
| `audit_logs` | Change tracking (table_name, operation, old_data, new_data, changed_by) |

## Key gotchas

These are the most common sources of bugs when working with the database:

### Naming

| Correct | Incorrect | Notes |
|---|---|---|
| `user_tenants` | `tenant_members` | The membership table |
| `messages` | `chat_messages` | Chat messages table (legacy retired to `_legacy_chat_messages`) |

### Schema quirks

- **`entity_types.id` has no default.** You must generate the UUID yourself: `id: crypto.randomUUID()`. Inserting without an `id` will fail.
- **`entity_type_slug` is denormalized.** The `sync_entity_type_slug` trigger keeps `entities.entity_type_slug` in sync with `entity_types.slug`. Always use it for filtering instead of joining.
- **Default tenant UUID:** `00000000-0000-0000-0000-000000000000` (slug: `"default"`). Use `DEFAULT_TENANT_ID` from `features/tenant/constants.ts` -- never hardcode the UUID.
- **Default signup role:** `guest` (UUID: `00000000-0000-0000-0000-000000000014`), NOT `member`. New users are read-only until promoted.

### Query patterns

**Never use `.single()` on UPDATE or DELETE:**

```typescript
// WRONG -- will throw PGRST116 if RLS filters the row
const { data } = await supabase
  .from("entities")
  .update(changes)
  .eq("id", id)
  .select("*")
  .single();  // <-- dangerous

// CORRECT -- use array pattern
const { data, error } = await supabase
  .from("entities")
  .update(changes)
  .eq("id", id)
  .select("*");

if (error) throw new Error(`Update failed: ${error.message}`);
if (!data || data.length === 0) throw new Error("Not found or no permission");
return data[0];
```

| Method | 0 rows | 1 row | 2+ rows | When to use |
|---|---|---|---|---|
| `.single()` | ERROR | object | ERROR | SELECT by primary key, INSERT |
| `.maybeSingle()` | null | object | ERROR | SELECT that may not exist |
| `.select()` (array) | `[]` | `[obj]` | `[obj, ...]` | All UPDATE/DELETE + returning |

## RLS patterns

### Tenant scoping

Every RLS policy checks `tenant_id` against the user's membership:

```sql
CREATE POLICY "tenant_isolation" ON entities
  USING (
    tenant_id IN (
      SELECT tenant_id FROM user_tenants
      WHERE user_id = (SELECT auth.uid())
    )
  );
```

### Auth.uid() caching

Always wrap `auth.uid()` in a SELECT subquery in RLS policies:

```sql
-- CORRECT: evaluated once, cached
WHERE user_id = (SELECT auth.uid())

-- INCORRECT: re-evaluated per row
WHERE user_id = auth.uid()
```

### Permission checks

The `authorize(permission)` function checks the materialized `user_permissions` table:

```sql
CREATE POLICY "entities_read" ON entities
  FOR SELECT USING (
    authorize('entities.team.read')
    OR (owner_id = (SELECT auth.uid()) AND authorize('entities.own.read'))
  );
```

## Supabase client selection

| Client | When to use | RLS |
|---|---|---|
| `createClient()` (authenticated) | User-scoped reads/writes | Enforced |
| `createAdminClient()` | Cross-user/system ops: tenant management, provisioning, background jobs, extraction | Bypassed |

Default to the authenticated client. Use the admin client only when you explicitly need to bypass RLS -- typically in background jobs (Inngest functions), user provisioning, or cross-tenant operations.

## Vector search functions

Three SQL functions support similarity search:

- `hybrid_search_entities(query_text, query_embedding, ...)` -- combined text + vector search on entities
- `hybrid_search_chunks(query_text, query_embedding, ...)` -- combined search on document_chunks
- `match_chunks(query_embedding, ...)` -- pure vector search on document_chunks

## Atomic merge functions

Two Postgres RPCs support atomic JSONB writes on entities:

- `merge_entity_content(entity_id, tenant_id, new_content)` -- merges incoming keys into `entities.content`
- `merge_entity_metadata(entity_id, tenant_id, new_metadata)` -- merges incoming keys into `entities.metadata`

Both functions scope the update by entity ID and tenant ID, then perform a single database-side `UPDATE` with `COALESCE(column, '{}'::jsonb) || new_value`. They exist to eliminate the read-merge-write race in entity update, extraction recovery, and bulk update paths.

These require the `vector` extension and populated embeddings in the relevant tables.

## Migration workflow

Migrations live in `supabase/migrations/` with timestamp-based naming:

```
00000000000000_baseline.sql          # Full schema snapshot
20260316010000_description.sql       # Forward migrations
```

### Commands

```bash
pnpm db:start    # Start local Supabase
pnpm db:reset    # Apply migrations + seed
pnpm db:types    # Regenerate TypeScript types
pnpm db:push     # Push migrations to remote
pnpm db:diff     # Diff schema changes
```

After any migration: run `pnpm db:types` to regenerate `lib/supabase/database.types.ts`, then verify `pnpm build` passes.

## Audit logging

The `audit_logs` table tracks INSERT/UPDATE/DELETE operations with full before/after snapshots:

```sql
-- Enable audit tracking on a table
SELECT enable_audit_tracking('table_name');
```

Currently tracked: entities, entity_types, entity_relations, tenants, profiles, user_tenants, roles, role_permissions, chats, agents, user_favorites, user_recent_views.

Each audit log entry includes `table_name`, `operation`, `old_data` (jsonb), `new_data` (jsonb), `changed_by` (auth.uid()), `tenant_id`, and `timestamp`.