Database Schema

Complete reference for the KOSMOS PostgreSQL database schema.

Auto-Generated

This documentation is automatically extracted from SQL migration files.

Entity Relationship Diagram

Database Schemas

KOSMOS uses a multi-schema PostgreSQL design for logical separation:

Schema	Purpose
`agents`	Agent registry, state, and metrics
`audit`	Immutable audit logging
`core`	Users, tenants, conversations, messages
`governance`	Pentarchy voting, proposals, cost tracking
`knowledge`	RAG documents, embeddings, knowledge graph
`metrics`	Performance and operational metrics
`routing`	Application data

`agents` Schema

registry

AGENTS SCHEMA - Agent State and Configuration

Column	Type	Nullable	Default	References
`id` PK	`VARCHAR(50)`	YES	-	-
`name`	`VARCHAR(100)`	NO	-	-
`domain`	`VARCHAR(100)`	NO	-	-
`description`	`TEXT`	YES	-	-
`tools`	`JSONB`	YES	`'[]'`	-
`mcp_servers`	`JSONB`	YES	`'[]'`	-
`config`	`JSONB`	YES	`'{}'`	-
`status`	`VARCHAR(20)`	YES	`'active'`	-
`created_at`	`TIMESTAMPTZ`	YES	`NOW()`	-
`updated_at`	`TIMESTAMPTZ`	YES	`NOW()`	-

state

Agent state (for persistence)

Column	Type	Nullable	Default	References
`id` PK	`UUID`	YES	`uuid_generate_v4()`	-
`agent_id`	`VARCHAR(50)`	NO	-	`agents.registry.id`
`conversation_id`	`UUID`	YES	-	`core.conversations.id`
`state`	`JSONB`	NO	-	-
`version`	`INTEGER`	NO	`1`	-
`created_at`	`TIMESTAMPTZ`	YES	`NOW()`	-
`updated_at`	`TIMESTAMPTZ`	YES	`NOW()`	-

metrics

Agent metrics

Column	Type	Nullable	Default	References
`id` PK	`UUID`	YES	`uuid_generate_v4()`	-
`agent_id`	`VARCHAR(50)`	NO	-	`agents.registry.id`
`metric_name`	`VARCHAR(100)`	NO	-	-
`metric_value`	`DOUBLE PRECISION`	NO	-	-
`labels`	`JSONB`	YES	`'{}'`	-
`recorded_at`	`TIMESTAMPTZ`	YES	`NOW()`	-

`audit` Schema

events

AUDIT SCHEMA - Immutable Audit Log

Column	Type	Nullable	Default	References
`id` PK	`UUID`	YES	`uuid_generate_v4()`	-
`tenant_id`	`UUID`	NO	-	-
`event_type`	`VARCHAR(100)`	NO	-	-
`actor_id`	`VARCHAR(100)`	NO	-	-
`resource_type`	`VARCHAR(100)`	YES	-	-
`resource_id`	`VARCHAR(100)`	YES	-	-
`action`	`VARCHAR(50)`	NO	-	-
`details`	`JSONB`	YES	`'{}'`	-
`ip_address`	`INET`	YES	-	-
`user_agent`	`TEXT`	YES	-	-
`created_at`	`TIMESTAMPTZ`	YES	`NOW()`	-

`core` Schema

tenants

CORE SCHEMA - Tenants, Users, Conversations

Column	Type	Nullable	Default	References
`id` PK	`UUID`	YES	`uuid_generate_v4()`	-
`name`	`VARCHAR(255)`	NO	-	-
`slug`	`VARCHAR(100)`	NO	-	-
`settings`	`JSONB`	YES	`'{}'`	-
`created_at`	`TIMESTAMPTZ`	YES	`NOW()`	-
`updated_at`	`TIMESTAMPTZ`	YES	`NOW()`	-

users

Users table

Column	Type	Nullable	Default	References
`id` PK	`UUID`	YES	`uuid_generate_v4()`	-
`tenant_id`	`UUID`	NO	-	`core.tenants.id`
`external_id`	`VARCHAR(255),`	YES	-	-
`email`	`VARCHAR(255)`	NO	-	-
`name`	`VARCHAR(255)`	YES	-	-
`role`	`VARCHAR(50)`	YES	`'user'`	-
`preferences`	`JSONB`	YES	`'{}'`	-
`created_at`	`TIMESTAMPTZ`	YES	`NOW()`	-
`updated_at`	`TIMESTAMPTZ`	YES	`NOW()`	-

conversations

Conversations table

Column	Type	Nullable	Default	References
`id` PK	`UUID`	YES	`uuid_generate_v4()`	-
`tenant_id`	`UUID`	NO	-	`core.tenants.id`
`user_id`	`UUID`	NO	-	`core.users.id`
`title`	`VARCHAR(500)`	YES	-	-
`metadata`	`JSONB`	YES	`'{}'`	-
`started_at`	`TIMESTAMPTZ`	YES	`NOW()`	-
`ended_at`	`TIMESTAMPTZ`	YES	-	-
`created_at`	`TIMESTAMPTZ`	YES	`NOW()`	-
`updated_at`	`TIMESTAMPTZ`	YES	`NOW()`	-

messages

Messages table

Column	Type	Nullable	Default	References
`id` PK	`UUID`	YES	`uuid_generate_v4()`	-
`conversation_id`	`UUID`	NO	-	`core.conversations.id`
`tenant_id`	`UUID`	NO	-	`core.tenants.id`
`content`	`TEXT`	NO	-	-
`agent_id`	`VARCHAR(50)`	YES	-	-
`tool_calls`	`JSONB`	YES	-	-
`tokens_input`	`INTEGER`	YES	-	-
`tokens_output`	`INTEGER`	YES	-	-
`cost_usd`	`DECIMAL(10, 6)`	YES	-	-
`latency_ms`	`INTEGER`	YES	-	-
`created_at`	`TIMESTAMPTZ`	YES	`NOW()`	-

`governance` Schema

proposals

GOVERNANCE SCHEMA - Pentarchy Voting and Approvals

Column	Type	Nullable	Default	References
`id` PK	`UUID`	YES	`uuid_generate_v4()`	-
`tenant_id`	`UUID`	NO	-	`core.tenants.id`
`conversation_id`	`UUID`	YES	-	`core.conversations.id`
`action`	`VARCHAR(200)`	NO	-	-
`description`	`TEXT`	YES	-	-
`estimated_cost`	`DECIMAL(10, 2)`	YES	-	-
`context`	`JSONB`	YES	`'{}'`	-
`outcome`	`VARCHAR(20)`	YES	-	-
`decided_by`	`VARCHAR(50)`	YES	-	-
`created_at`	`TIMESTAMPTZ`	YES	`NOW()`	-
`resolved_at`	`TIMESTAMPTZ`	YES	-	-

votes

Votes

Column	Type	Nullable	Default	References
`id` PK	`UUID`	YES	`uuid_generate_v4()`	-
`proposal_id`	`UUID`	NO	-	`governance.proposals.id`
`voter_agent`	`VARCHAR(50)`	NO	-	-
`reasoning`	`TEXT`	YES	-	-
`created_at`	`TIMESTAMPTZ`	YES	`NOW()`	-

cost_tracking

Cost tracking

Column	Type	Nullable	Default	References
`id` PK	`UUID`	YES	`uuid_generate_v4()`	-
`tenant_id`	`UUID`	NO	-	`core.tenants.id`
`proposal_id`	`UUID`	YES	-	`governance.proposals.id`
`tool_name`	`VARCHAR(100)`	NO	-	-
`agent_id`	`VARCHAR(50)`	YES	-	-
`estimated_cost`	`DECIMAL(12, 4)`	NO	-	-
`actual_cost`	`DECIMAL(12, 4)`	YES	-	-
`variance_pct`	`DECIMAL(5, 2)`	YES	-	-
`governance_decision`	`VARCHAR(20)`	YES	-	-
`approved_by`	`VARCHAR(50)`	YES	-	-
`created_at`	`TIMESTAMPTZ`	YES	`NOW()`	-
`completed_at`	`TIMESTAMPTZ`	YES	-	-

`knowledge` Schema

documents

KNOWLEDGE SCHEMA - RAG and Documents

Column	Type	Nullable	Default	References
`id` PK	`UUID`	YES	`uuid_generate_v4()`	-
`tenant_id`	`UUID`	NO	-	`core.tenants.id`
`source_url`	`TEXT`	YES	-	-
`title`	`VARCHAR(500)`	YES	-	-
`content`	`TEXT`	NO	-	-
`content_type`	`VARCHAR(50)`	YES	`'text/plain'`	-
`metadata`	`JSONB`	YES	`'{}'`	-
`status`	`VARCHAR(20)`	YES	`'active'`	-
`created_at`	`TIMESTAMPTZ`	YES	`NOW()`	-
`updated_at`	`TIMESTAMPTZ`	YES	`NOW()`	-

chunks

Document chunks with embeddings

Column	Type	Nullable	Default	References
`id` PK	`UUID`	YES	`uuid_generate_v4()`	-
`document_id`	`UUID`	NO	-	`knowledge.documents.id`
`tenant_id`	`UUID`	NO	-	`core.tenants.id`
`content`	`TEXT`	NO	-	-
`embedding`	`vector(768),`	YES	-	-
`chunk_index`	`INTEGER`	NO	-	-
`token_count`	`INTEGER`	YES	-	-
`metadata`	`JSONB`	YES	`'{}'`	-
`created_at`	`TIMESTAMPTZ`	YES	`NOW()`	-

entities

Full-text search column

Column	Type	Nullable	Default	References
`id` PK	`UUID`	YES	`uuid_generate_v4()`	-
`tenant_id`	`UUID`	NO	-	`core.tenants.id`
`user_id`	`UUID`	YES	-	`core.users.id`
`name`	`VARCHAR(255)`	NO	-	-
`entity_type`	`VARCHAR(100)`	NO	-	-
`observations`	`JSONB`	YES	`'[]'`	-
`embedding`	`vector(1536),`	YES	-	-
`importance_score`	`FLOAT`	YES	`0.5`	-
`decay_rate`	`FLOAT`	YES	`0.01`	-
`created_at`	`TIMESTAMPTZ`	YES	`NOW()`	-
`accessed_at`	`TIMESTAMPTZ`	YES	`NOW()`	-

relations

Memory relations

Column	Type	Nullable	Default	References
`id` PK	`UUID`	YES	`uuid_generate_v4()`	-
`tenant_id`	`UUID`	NO	-	`core.tenants.id`
`from_entity_id`	`UUID`	NO	-	`knowledge.entities.id`
`to_entity_id`	`UUID`	NO	-	`knowledge.entities.id`
`relation_type`	`VARCHAR(100)`	NO	-	-
`properties`	`JSONB`	YES	`'{}'`	-
`created_at`	`TIMESTAMPTZ`	YES	`NOW()`	-

`routing` Schema

intent_categories

INTENT TAXONOMY - Hierarchical Intent Classification

Column	Type	Nullable	Default	References
`id` PK	`VARCHAR(50)`	YES	-	-
`name`	`VARCHAR(100)`	NO	-	-
`description`	`TEXT`	YES	-	-
`parent_category_id`	`VARCHAR(50)`	YES	-	`routing.intent_categories.id`
`priority_weight`	`DECIMAL(3, 2)`	YES	`1.0`	-
`created_at`	`TIMESTAMPTZ`	YES	`NOW()`	-

intents

Intent definitions with embeddings

Column	Type	Nullable	Default	References
`id` PK	`VARCHAR(100)`	YES	-	-
`category_id`	`VARCHAR(50)`	NO	-	`routing.intent_categories.id`
`name`	`VARCHAR(200)`	NO	-	-
`description`	`TEXT`	YES	-	-
`example_utterances`	`TEXT[]`	NO	-	-
`embedding`	`vector(768),`	YES	-	-
`target_agent`	`VARCHAR(50)`	NO	-	-
`secondary_agents`	`VARCHAR(50)[]`	YES	`'{}'`	-
`required_capabilities`	`VARCHAR(100)[]`	YES	`'{}'`	-
`confidence_threshold`	`DECIMAL(3, 2)`	YES	`0.75`	-
`priority`	`INTEGER`	YES	`5`	-
`metadata`	`JSONB`	YES	`'{}'`	-
`is_active`	`BOOLEAN`	YES	`TRUE`	-
`created_at`	`TIMESTAMPTZ`	YES	`NOW()`	-
`updated_at`	`TIMESTAMPTZ`	YES	`NOW()`	-

intent_utterance_embeddings

Intent embeddings for all example utterances

Column	Type	Nullable	Default	References
`id` PK	`UUID`	YES	`uuid_generate_v4()`	-
`intent_id`	`VARCHAR(100)`	NO	-	`routing.intents.id`
`utterance`	`TEXT`	NO	-	-
`embedding`	`vector(768)`	NO	-	-
`created_at`	`TIMESTAMPTZ`	YES	`NOW()`	-

keyword_rules

ROUTING RULES - Fallback and Override Logic

Column	Type	Nullable	Default	References
`id` PK	`UUID`	YES	`uuid_generate_v4()`	-
`pattern`	`TEXT`	NO	-	-
`target_agent`	`VARCHAR(50)`	NO	-	-
`priority`	`INTEGER`	YES	`5`	-
`confidence_boost`	`DECIMAL(3, 2)`	YES	`0.0`	-
`is_active`	`BOOLEAN`	YES	`TRUE`	-
`created_at`	`TIMESTAMPTZ`	YES	`NOW()`	-

context_overrides

Context-aware routing overrides

Column	Type	Nullable	Default	References
`id` PK	`UUID`	YES	`uuid_generate_v4()`	-
`context_condition`	`JSONB`	NO	-	-
`target_agent`	`VARCHAR(50)`	NO	-	-
`priority`	`INTEGER`	YES	`10`	-
`valid_duration_minutes`	`INTEGER`	YES	`60`	-
`is_active`	`BOOLEAN`	YES	`TRUE`	-
`created_at`	`TIMESTAMPTZ`	YES	`NOW()`	-

decisions

ROUTING HISTORY - Analytics and Learning

Column	Type	Nullable	Default	References
`id` PK	`UUID`	YES	`uuid_generate_v4()`	-
`tenant_id`	`UUID`	NO	-	-
`conversation_id`	`UUID`	YES	-	-
`message_id`	`UUID`	YES	-	-
`input_text`	`TEXT`	NO	-	-
`input_embedding`	`vector(768)`	YES	-	-
`matched_intent_id`	`VARCHAR(100)`	YES	-	`routing.intents.id`
`semantic_confidence`	`DECIMAL(5, 4)`	YES	-	-
`keyword_match`	`BOOLEAN`	YES	`FALSE`	-
`context_override`	`BOOLEAN`	YES	`FALSE`	-
`selected_agent`	`VARCHAR(50)`	NO	-	-
`alternative_agents`	`JSONB`	YES	`'[]'`	-
`routing_latency_ms`	`INTEGER`	YES	-	-
`created_at`	`TIMESTAMPTZ`	YES	`NOW()`	-

performance_metrics

Routing performance aggregates (materialized for dashboards)

Column	Type	Nullable	Default	References
`id` PK	`UUID`	YES	`uuid_generate_v4()`	-
`metric_date`	`DATE`	NO	-	-
`intent_id`	`VARCHAR(100)`	YES	-	-
`agent_id`	`VARCHAR(50)`	YES	-	-
`total_routes`	`INTEGER`	YES	`0`	-
`avg_confidence`	`DECIMAL(5, 4)`	YES	-	-
`avg_latency_ms`	`DECIMAL(10, 2)`	YES	-	-
`positive_feedback`	`INTEGER`	YES	`0`	-
`negative_feedback`	`INTEGER`	YES	`0`	-
`misroutes`	`INTEGER`	YES	`0`	-
`created_at`	`TIMESTAMPTZ`	YES	`NOW()`	-

experiments

A/B TESTING - Routing Strategy Experiments

Column	Type	Nullable	Default	References
`id` PK	`UUID`	YES	`uuid_generate_v4()`	-
`name`	`VARCHAR(200)`	NO	-	-
`description`	`TEXT`	YES	-	-
`strategy_a`	`JSONB`	NO	-	-
`strategy_b`	`JSONB`	NO	-	-
`traffic_split`	`DECIMAL(3, 2)`	YES	`0.5`	-
`started_at`	`TIMESTAMPTZ`	YES	-	-
`ended_at`	`TIMESTAMPTZ`	YES	-	-
`winner`	`VARCHAR(10),`	YES	-	-
`created_at`	`TIMESTAMPTZ`	YES	`NOW()`	-

experiment_assignments

'a', 'b', or null

Column	Type	Nullable	Default	References
`id` PK	`UUID`	YES	`uuid_generate_v4()`	-
`experiment_id`	`UUID`	NO	-	`routing.experiments.id`
`user_id`	`UUID`	NO	-	-
`created_at`	`TIMESTAMPTZ`	YES	`NOW()`	-

Indexes

Performance-optimized indexes:

Index	Table	Columns
`idx_users_tenant`	`core.users`	`tenant_id`
`idx_users_email`	`core.users`	`email`
`idx_conversations_tenant`	`core.conversations`	`tenant_id`
`idx_conversations_user`	`core.conversations`	`user_id`
`idx_messages_conversation`	`core.messages`	`conversation_id`
`idx_messages_created`	`core.messages`	`created_at`
`idx_agent_state_agent`	`agents.state`	`agent_id`
`idx_agent_state_conversation`	`agents.state`	`conversation_id`
`idx_agent_metrics_agent`	`agents.metrics`	`agent_id`
`idx_agent_metrics_recorded`	`agents.metrics`	`recorded_at`
`idx_proposals_tenant`	`governance.proposals`	`tenant_id`
`idx_proposals_status`	`governance.proposals`	`status`
`idx_votes_proposal`	`governance.votes`	`proposal_id`
`idx_cost_tracking_tenant`	`governance.cost_tracking`	`tenant_id`
`idx_documents_tenant`	`knowledge.documents`	`tenant_id`
`idx_chunks_document`	`knowledge.chunks`	`document_id`
`idx_chunks_tenant`	`knowledge.chunks`	`tenant_id`
`idx_entities_tenant`	`knowledge.entities`	`tenant_id`
`idx_entities_user`	`knowledge.entities`	`user_id`
`idx_entities_type`	`knowledge.entities`	`entity_type`
`idx_relations_from`	`knowledge.relations`	`from_entity_id`
`idx_relations_to`	`knowledge.relations`	`to_entity_id`
`idx_audit_tenant`	`audit.events`	`tenant_id`
`idx_audit_created`	`audit.events`	`created_at`
`idx_audit_actor`	`audit.events`	`actor_type`, `actor_id`
`idx_audit_resource`	`audit.events`	`resource_type`, `resource_id`
`idx_intents_category`	`routing.intents`	`category_id`
`idx_intents_agent`	`routing.intents`	`target_agent`
`idx_intents_active`	`routing.intents`	`is_active`
`idx_utterances_intent`	`routing.intent_utterance_embeddings`	`intent_id`
`idx_decisions_tenant`	`routing.decisions`	`tenant_id`
`idx_decisions_created`	`routing.decisions`	`created_at`
`idx_decisions_agent`	`routing.decisions`	`selected_agent`
`idx_decisions_intent`	`routing.decisions`	`matched_intent_id`
`idx_performance_date`	`routing.performance_metrics`	`metric_date`
`idx_keyword_rules_active`	`routing.keyword_rules`	`is_active`
`idx_context_overrides_active`	`routing.context_overrides`	`is_active`

Row Level Security

KOSMOS implements multi-tenancy using PostgreSQL Row Level Security (RLS):

-- Example: Tenant isolation policy
CREATE POLICY tenant_isolation_users ON core.users
    USING (tenant_id = current_setting('app.current_tenant', true)::uuid);

All tenant-scoped tables have RLS enabled to ensure data isolation.

Extensions

Required PostgreSQL extensions:

Extension	Purpose
`uuid-ossp`	UUID generation
`pgvector`	Vector embeddings for RAG
`pg_trgm`	Fuzzy text search

Entity Relationship Diagram​

Database Schemas​

agents Schema​

registry​

state​

metrics​

audit Schema​

events​

core Schema​

tenants​

users​

conversations​

messages​

governance Schema​

proposals​

votes​

cost_tracking​

knowledge Schema​

documents​

chunks​

entities​

relations​

routing Schema​

intent_categories​

intents​

intent_utterance_embeddings​

keyword_rules​

context_overrides​

decisions​

performance_metrics​

experiments​

experiment_assignments​

Indexes​

Row Level Security​

Extensions​