Schema Overview

PostgreSQL schema reference for the Crawbl orchestrator. All tables live in the orchestrator schema, set via search_path on the database connection.

Entity Hierarchy

users
 +-- user_preferences          (1:1, CASCADE delete)
 +-- user_push_tokens          (1:1, CASCADE delete)
 +-- usage_quotas              (1:N, CASCADE delete)
 +-- workspaces                (1:N, CASCADE delete)
      +-- agents               (1:N, CASCADE delete)
      |    +-- agent_settings  (1:1, CASCADE delete)
      |    +-- agent_prompts   (1:N, CASCADE delete)
      |    +-- agent_usage_counters (1:1, CASCADE delete)
      +-- conversations        (1:N, CASCADE delete)
      |    +-- messages        (1:N, CASCADE delete)
      |    +-- agent_history   (1:N, CASCADE delete)
      +-- agent_delegations    (1:N)
      +-- agent_messages       (1:N)
      +-- artifacts            (1:N)
      |    +-- artifact_versions (1:N)
      |    +-- artifact_reviews  (1:N)
      +-- workflow_definitions (1:N)
           +-- workflow_executions (1:N)
                +-- workflow_step_executions (1:N)

usage_counters          (per user per month)
usage_plans             (reference table)
model_pricing           (reference table)
tools                   (global catalog)
models                  (global catalog)
tool_categories         (global catalog)
integration_categories  (global catalog)
integration_providers   (global catalog)
integration_connections (per user)
mcp_audit_logs          (append-only audit)

Memory Palace (per workspace):
 memory_drawers -> memory_entities -> memory_triples
 memory_identities

Tables

Table	Parent	Delete	Description
Core
users	--	Soft delete	User accounts
user_preferences	users	CASCADE	Theme, language, currency
user_push_tokens	users	CASCADE	FCM push token
workspaces	users	CASCADE	Isolated work environments
Agents
agents	workspaces	CASCADE	AI agents within a workspace
agent_settings	agents	CASCADE	Model, response length config
agent_prompts	agents	CASCADE	System prompt templates
agent_history	agents	CASCADE	Conversation history summaries
Chat
conversations	workspaces	CASCADE	Chat threads (swarm or agent)
messages	conversations	CASCADE	Individual messages
Multi-Agent
agent_delegations	workspaces	CASCADE	Agent-to-agent task handoffs
agent_messages	workspaces	CASCADE	Inter-agent message log
Artifacts & Workflows
artifacts	workspaces	CASCADE	Versioned documents/plans/code
artifact_versions	artifacts	CASCADE	Version history
artifact_reviews	artifacts	CASCADE	Review comments
workflow_definitions	workspaces	CASCADE	Multi-step workflow templates
workflow_executions	workflow_definitions	CASCADE	Running/completed executions
workflow_step_executions	workflow_executions	CASCADE	Per-step execution state
Integrations
integration_connections	users	CASCADE	OAuth connections per user
Catalogs
tools	--	--	Global tool definitions
models	--	--	Available LLM models
tool_categories	--	--	Tool category groupings
integration_categories	--	--	Integration category groupings
integration_providers	--	--	OAuth provider configs
Usage & Billing
model_pricing	--	--	Per-token costs (append-only)
usage_plans	--	--	Plan definitions (free, pro)
usage_quotas	users	CASCADE	Per-user plan assignment
usage_counters	users	CASCADE	Monthly running totals
agent_usage_counters	agents	CASCADE	Lifetime agent totals
Audit
mcp_audit_logs	--	--	MCP tool call audit trail
Memory Palace
memory_drawers	workspaces	CASCADE	Topic-based memory containers
memory_entities	memory_drawers	CASCADE	Named entities (people, places)
memory_triples	memory_entities	CASCADE	Subject-predicate-object facts
memory_identities	workspaces	CASCADE	User identity recognition
Triggers
agent_triggers	agents	CASCADE	Event-driven agent triggers
agent_trigger_executions	agent_triggers	CASCADE	Trigger execution log
memory_triggers	--	CASCADE	Memory-based triggers

Conventions

Convention	Detail
Primary keys	UUIDs generated in Go, not database-generated
Timestamps	All tables have created_at and updated_at (TIMESTAMPTZ)
Soft deletes	deleted_at column (only users currently)
Foreign keys	ON DELETE CASCADE by default
Agent references	ON DELETE SET NULL (conversations and messages keep the row when an agent is removed)
Schema	orchestrator, set via search_path on the connection

ClickHouse Tables

Analytics data is stored in a separate ClickHouse database. See the ClickHouse reference for details.

Table	Engine	TTL	Description
llm_usage	MergeTree	13 months	Raw per-LLM-call events
llm_usage_daily	SummingMergeTree	25 months	Auto-aggregated daily rollups