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.

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

-- 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.

entity_relations

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

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:

{
  "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.

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.

Auth tables

profiles

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

user_tenants

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

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:

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).

agent_connections

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

agent_heartbeat_runs

Logs of autonomous agent executions triggered by cron schedules.

agent_config_versions

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

Chat tables

chats

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

messages

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

Tool tables

tool_runs

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

tool_sessions

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

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.

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.

entity_responses

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

Document tables

documents

File metadata for uploaded documents.

document_chunks

Chunked document content for hybrid search and RAG.

Additional tables

Key gotchas

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

Naming

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:

// 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];

RLS patterns

Tenant scoping

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

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:

-- 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:

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

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

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:

-- 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.