Table Definitions

Full column specifications for all tables in the orchestrator schema.

users

Column	Type	Constraints	Description
id	UUID	PK	User identifier
subject	TEXT	NOT NULL, UNIQUE	Auth provider subject (Firebase UID or dev token)
email	TEXT	NOT NULL	User email
nickname	TEXT	NOT NULL, default ''	Display nickname
name	TEXT	NOT NULL, default ''	First name
surname	TEXT	NOT NULL, default ''	Last name
avatar_url	TEXT	NULL	Profile avatar URL
country_code	TEXT	NULL	ISO country code
date_of_birth	TIMESTAMPTZ	NULL	Date of birth
is_banned	BOOLEAN	NOT NULL, default false	Ban flag
has_agreed_with_terms	BOOLEAN	NOT NULL, default false	Terms acceptance
has_agreed_with_privacy_policy	BOOLEAN	NOT NULL, default false	Privacy acceptance
created_at	TIMESTAMPTZ	NOT NULL	Creation timestamp
updated_at	TIMESTAMPTZ	NOT NULL	Last update timestamp
deleted_at	TIMESTAMPTZ	NULL	Soft delete timestamp

Indexes: idx_users_subject on subject

---

user_preferences

Column	Type	Constraints	Description
user_id	UUID	PK, FK -> users(id) CASCADE	User reference
platform_theme	TEXT	NULL	UI theme preference
platform_language	TEXT	NULL	Language code
currency_code	TEXT	NULL	Preferred currency
updated_at	TIMESTAMPTZ	NOT NULL	Last update timestamp

---

user_push_tokens

Column	Type	Constraints	Description
user_id	UUID	PK, FK -> users(id) CASCADE	User reference
push_token	TEXT	NOT NULL	FCM token
updated_at	TIMESTAMPTZ	NOT NULL	Last update timestamp

---

workspaces

Column	Type	Constraints	Description
id	UUID	PK	Workspace identifier
user_id	UUID	NOT NULL, FK -> users(id) CASCADE	Owner
name	TEXT	NOT NULL	Workspace display name
created_at	TIMESTAMPTZ	NOT NULL	Creation timestamp
updated_at	TIMESTAMPTZ	NOT NULL	Last update timestamp

Indexes: idx_workspaces_user_id on user_id

Runtime status comes from UserSwarm.status in Kubernetes, not this table.

---

agents

Column	Type	Constraints	Description
id	UUID	PK	Agent identifier
workspace_id	UUID	NOT NULL, FK -> workspaces(id) CASCADE	Parent workspace
name	TEXT	NOT NULL	Agent display name
role	TEXT	NOT NULL	Agent role (e.g. "default")
avatar_url	TEXT	NOT NULL, default ''	Agent avatar
sort_order	INTEGER	NOT NULL, default 0	Display order
created_at	TIMESTAMPTZ	NOT NULL	Creation timestamp
updated_at	TIMESTAMPTZ	NOT NULL	Last update timestamp

Indexes: idx_agents_workspace_role UNIQUE on (workspace_id, role), idx_agents_workspace_id on workspace_id

---

conversations

Column	Type	Constraints	Description
id	UUID	PK	Conversation identifier
workspace_id	UUID	NOT NULL, FK -> workspaces(id) CASCADE	Parent workspace
agent_id	UUID	NULL, FK -> agents(id) SET NULL	Associated agent
type	TEXT	NOT NULL	Conversation type
title	TEXT	NOT NULL	Conversation title
unread_count	INTEGER	NOT NULL, default 0	Unread message count
created_at	TIMESTAMPTZ	NOT NULL	Creation timestamp
updated_at	TIMESTAMPTZ	NOT NULL	Last update timestamp

Indexes: idx_conversations_workspace_id on workspace_id, idx_conversations_workspace_type on (workspace_id, type)

---

messages

Column	Type	Constraints	Description
id	UUID	PK	Message identifier
conversation_id	UUID	NOT NULL, FK -> conversations(id) CASCADE	Parent conversation
role	TEXT	NOT NULL	Message role (user, assistant, system)
content	JSONB	NOT NULL	Message content (structured)
status	TEXT	NOT NULL	Message status
local_id	TEXT	NULL	Client-generated ID for deduplication
agent_id	UUID	NULL, FK -> agents(id) SET NULL	Agent that sent/received
attachments	JSONB	NOT NULL, default '[]'	File attachments
created_at	TIMESTAMPTZ	NOT NULL	Creation timestamp
updated_at	TIMESTAMPTZ	NOT NULL	Last update timestamp

Indexes: idx_messages_conversation_created_at on (conversation_id, created_at DESC, id DESC), idx_messages_local_id on local_id

---

mcp_audit_logs

Audit log for every MCP tool call made by ZeroClaw agent pods. Writes are async (goroutine with 5s timeout) to avoid response latency. Required for ISO 27001 compliance.

Column	Type	Constraints	Description
id	UUID	PK	Audit log identifier
user_id	TEXT	NOT NULL	User who triggered the tool call
workspace_id	TEXT	NOT NULL	Workspace context
session_id	TEXT	NULL	MCP session identifier
tool_name	TEXT	NOT NULL	MCP tool name (e.g., send_push_notification)
input	JSONB	NOT NULL	Tool call input parameters
output	JSONB	NULL	Tool call output
api_calls	JSONB	NULL	External API calls made during execution
success	BOOLEAN	NOT NULL	Whether the tool call succeeded
error_message	TEXT	NULL	Error message on failure
duration_ms	INTEGER	NOT NULL	Execution time in milliseconds
remote_addr	TEXT	NULL	Calling pod's IP address
created_at	TIMESTAMPTZ	NOT NULL	Timestamp of the tool call