Data Model and Schema Specification

Complete database schema for River Agents -- 11 core tables in the river_agents schema within the shared PostgreSQL instance, full JSONB column schemas (including the AgentContext runtime object), indexing strategy, foreign key cascade rules, and three-layer tenant isolation enforcement.

Quick Navigation

• Schema Overview
• Entity Relationship Diagram
• Table Definitions
• JSONB Schema Specifications
• Indexing Strategy
• Foreign Key Cascade Rules
• Tenant Isolation Enforcement

---

Schema Overview

Table	Owning Service	Primary Responsibility
agents	Agent Management (Backend :8005)	Core agent definition, lifecycle state, and current version reference
agent_versions	Agent Management (Backend :8005)	Immutable configuration snapshots; one record per deploy action
agent_version_tools	Agent Management (Backend :8005)	Tool bindings per agent version
agent_version_data_sources	Agent Management (Backend :8005)	Data source bindings per agent version
agent_triggers	Trigger Ingestion (Backend :8005)	Trigger configurations attached to an agent
agent_executions	Execution Runner (Backend :8005)	One record per agent run; central Temporal workflow state
agent_logs	Execution Logging (Backend :8005)	Turn-level reasoning and tool call records within an execution
approval_requests	Governance (Backend :8005)	HITL approval gate records generated during execution
agent_templates	Agent Management (Backend :8005)	Pre-built agent configuration templates
governance_policies	Governance (Backend :8005)	Policy definitions applied to agent versions
audit_logs	Audit (Backend :8005)	Write-once immutable audit event records

Schema namespace: All tables use the river_agents. prefix. Cross-schema references to IAM tables (organizations, workspaces, users) and platform tables (data_sources) use fully qualified names. No database-level foreign keys cross schema boundaries; integrity is enforced at the application layer.

Versioning invariant: agent_versions records are never mutated after creation. Any configuration change (instruction set edit, tool change, trigger update) creates a new agent_versions record with an incremented version_number. Active executions are pinned to the agent_version_id that was current at trigger ingestion time -- not at the time of each LLM call.

---

Entity Relationship Diagram

Cross-schema references (not shown): agents.org_id -> iam.organizations.id, agents.workspace_id -> iam.workspaces.id, agents.owner_user_id -> iam.users.id, agent_version_data_sources.data_source_id -> platform.data_sources.id.

---

Table Definitions

agents

Root entity. Stores identity, lifecycle state, accumulated long-term memory, and a pointer to the current deployed version.

Column	Type	Constraints	Description
id	SERIAL	PK	Surrogate primary key
org_id	INT	NOT NULL, FK organizations.id	Organization scope; every query must filter by this
workspace_id	INT	NOT NULL, FK workspaces.id	Workspace scope within org
owner_user_id	INT	NOT NULL, FK users.id	User responsible for this agent
current_version_id	INT	NULLABLE, FK agent_versions.id	NULL when agent is in draft with no deployed version
template_id	INT	NULLABLE, FK agent_templates.id	Set if agent was created from a template; NULL otherwise
name	VARCHAR(255)	NOT NULL	Human-readable display name
description	TEXT	NULLABLE	Agent description
business_function	VARCHAR(100)	NOT NULL	Agent category: customer_support, sales_lead_qualification, finance_reconciliation, risk_compliance, data_analyst_insights, operations_monitoring, executive_decision_support, custom_enterprise
domain	VARCHAR(100)	NULLABLE	Business domain tag (e.g., "billing", "operations") for workspace-level grouping
status	VARCHAR(50)	NOT NULL, DEFAULT 'draft'	Lifecycle state; valid values listed below
action_level	VARCHAR(50)	NOT NULL	Autonomy level: read_respond, recommend, act_with_approval, fully_automated
governance_level	VARCHAR(20)	NOT NULL, DEFAULT 'standard'	Policy strictness applied to this agent: standard, strict, custom
health_status	VARCHAR(20)	NOT NULL, DEFAULT 'unknown'	healthy, degraded, critical, or unknown; recalculated after each run on a 5-minute cycle
long_term_context	JSONB	NOT NULL, DEFAULT '{}'	Accumulated knowledge from past runs; injected into system prompt on each execution; updated non-blocking after run completion
notification_config	JSONB	NOT NULL, DEFAULT '{}'	Agent-level alert channel and trigger overrides; merged with workspace defaults
created_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Creation timestamp
updated_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Last modification
deleted_at	TIMESTAMPTZ	NULLABLE	Soft delete timestamp; NULL means active

Valid status values: draft, configured, validated, deployed, active, running, awaiting_approval, paused, archived

Implementation note: long_term_context is written non-blocking after execution completes. A failed write does not fail the run. The river-agent microservice receives this field in the context bundle on each turn call but does not write it directly -- all writes go through Backend :8005.

---

agent_versions

Immutable configuration snapshot per deploy action. No updated_at or deleted_at columns -- these records are never modified after creation.

Column	Type	Constraints	Description
id	SERIAL	PK	Surrogate primary key
agent_id	INT	NOT NULL, FK agents.id	Parent agent reference
version_number	INT	NOT NULL	Monotonically increasing integer per agent
instruction_set	TEXT	NOT NULL	The agent's goal in natural language; input to the system prompt builder in river-agent
action_level	VARCHAR(50)	NOT NULL	read_respond, recommend, act_with_approval, or fully_automated; copied from agent at snapshot time
governance_level	VARCHAR(20)	NOT NULL, DEFAULT 'standard'	Policy strictness for this version; copied from agent at snapshot time
max_turns	INT	NOT NULL, DEFAULT 15	Maximum reasoning loop iterations; enforced by Backend :8005, not river-agent
llm_timeout_seconds	INT	NOT NULL, DEFAULT 120	Per-turn LLM inference timeout enforced by river-agent
tool_timeout_seconds	INT	NOT NULL, DEFAULT 30	Per-tool execution timeout enforced at TLO Gateway
approval_config	JSONB	NOT NULL, DEFAULT '{}'	Approval rules, auto-approve conditions, escalation config; see JSONB spec
notification_config	JSONB	NOT NULL, DEFAULT '{}'	Run completion and failure notification channel configuration for this version
deployment_state	VARCHAR(20)	NOT NULL, DEFAULT 'draft'	draft, active, or archived; only one version per agent holds active at a time
created_by	INT	NOT NULL, FK users.id	User who deployed this version
created_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Version creation timestamp; immutable

Unique constraint: UNIQUE (agent_id, version_number)

---

agent_version_tools

Junction table binding tools to a specific version. One row per tool per version.

Column	Type	Constraints	Description
id	SERIAL	PK	Surrogate primary key
agent_version_id	INT	NOT NULL, FK agent_versions.id	Version this binding belongs to
tool_name	VARCHAR(255)	NOT NULL	Tool identifier matching the Tool Registry registration name
config_overrides	JSONB	NOT NULL, DEFAULT '{}'	Version-specific overrides for tool defaults (e.g., per-tool timeout, retry count)
acl_settings	JSONB	NOT NULL, DEFAULT '{}'	Per-tool ACL override for this version (e.g., restrict execute_query to specific data source IDs)

Unique constraint: UNIQUE (agent_version_id, tool_name)

---

agent_version_data_sources

Junction table binding data sources to a specific version. One row per data source per version.

Column	Type	Constraints	Description
id	SERIAL	PK	Surrogate primary key
agent_version_id	INT	NOT NULL, FK agent_versions.id	Version this binding belongs to
data_source_id	INT	NOT NULL, FK data_sources.id	Reference to platform data source record
access_level	VARCHAR(50)	NOT NULL, DEFAULT 'read'	read or read_write; enforced by TLO Gateway ACL check on every tool call
schema_filter	JSONB	NOT NULL, DEFAULT '{}'	Restricts visible tables and columns to a subset; empty object means full schema access

Unique constraint: UNIQUE (agent_version_id, data_source_id)

---

agent_triggers

Trigger configurations attached to an agent. Mutations to a deployed or active agent's triggers create a new agent_versions draft rather than updating the existing version.

Column	Type	Constraints	Description
id	SERIAL	PK	Surrogate primary key
agent_id	INT	NOT NULL, FK agents.id	Parent agent reference
trigger_type	VARCHAR(50)	NOT NULL	manual, scheduled, event, api, threshold, or workflow
trigger_config	JSONB	NOT NULL	Type-specific configuration; see JSONB spec per trigger type
is_active	BOOLEAN	NOT NULL, DEFAULT true	Soft disable without deleting; inactive triggers are not evaluated
last_fired_at	TIMESTAMPTZ	NULLABLE	Updated by the Trigger Ingestion Service after each successful dispatch
created_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Trigger creation timestamp
deleted_at	TIMESTAMPTZ	NULLABLE	Soft delete timestamp

---

agent_executions

One record per agent run. Central state record for the Temporal workflow. The context_snapshot column enables approval gate hibernation and resumption.

Column	Type	Constraints	Description
id	SERIAL	PK	Surrogate primary key; used as Temporal workflow ID suffix (agent-run-{id})
agent_id	INT	NOT NULL, FK agents.id	Parent agent reference (denormalized for query performance)
agent_version_id	INT	NOT NULL, FK agent_versions.id	Version pinned at trigger ingestion; never updated during execution
org_id	INT	NOT NULL	Denormalized from agents.org_id for partition-friendly queries
workspace_id	INT	NOT NULL	Denormalized from agents.workspace_id
trigger_type	VARCHAR(50)	NOT NULL	Trigger type that initiated this run
trigger_source	VARCHAR(255)	NULLABLE	Identifier of the specific trigger source (e.g., "cron:daily-8am", "webhook:zendesk")
trigger_payload	JSONB	NULLABLE	Raw payload from the triggering event
status	VARCHAR(50)	NOT NULL, DEFAULT 'queued'	Current execution status; valid values below
turn_count	INT	NOT NULL, DEFAULT 0	Number of reasoning turns completed; incremented per turn
tokens_consumed	INT	NOT NULL, DEFAULT 0	Cumulative LLM tokens across all turns
final_output	JSONB	NULLABLE	Agent's final response or output artifact; set on terminal state transition
context_snapshot	JSONB	NULLABLE	Serialized AgentContext written when execution pauses at an approval gate; cleared on resume
error_code	VARCHAR(100)	NULLABLE	Machine-readable error code on terminal failed status
error_message	TEXT	NULLABLE	Human-readable error description
started_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Execution start timestamp
completed_at	TIMESTAMPTZ	NULLABLE	Terminal state timestamp

Valid status values: queued, running, completed, failed, cancelled, approval_pending, budget_exhausted, max_turns_exceeded, approval_expired

---

agent_logs

Turn-level audit trail for every reasoning step, tool call, and observation within an execution. High-write table with BIGINT primary key. Append-only; rows are never updated.

Column	Type	Constraints	Description
id	BIGINT	PK, AUTO_INCREMENT	Surrogate primary key
execution_id	INT	NOT NULL, FK agent_executions.id	Parent execution reference
turn_number	INT	NOT NULL	Zero-based reasoning turn index within the execution
log_type	VARCHAR(50)	NOT NULL	Entry type; valid values below
content	JSONB	NOT NULL	Type-specific payload; see JSONB spec
model_used	VARCHAR(100)	NULLABLE	LLM model identifier for reasoning entries
tokens_in	INT	NOT NULL, DEFAULT 0	Input token count
tokens_out	INT	NOT NULL, DEFAULT 0	Output token count
latency_ms	INT	NULLABLE	Elapsed milliseconds (tool calls: execution time; reasoning: LLM inference time)
tool_name	VARCHAR(255)	NULLABLE	Tool name for tool_call and tool_result entries; NULL for reasoning entries
status	VARCHAR(50)	NOT NULL, DEFAULT 'success'	success, error, blocked, or suggested
created_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Log entry creation timestamp

Valid log_type values: reasoning, tool_call, tool_result, observation, approval_requested, approval_resolved, error

---

approval_requests

HITL approval gate records. One record per approval gate event per execution. A PATCH /api/v1/approvals/{id} resolution writes an audit event synchronously before sending the Temporal workflow signal.

Column	Type	Constraints	Description
id	SERIAL	PK	Surrogate primary key
execution_id	INT	NOT NULL, FK agent_executions.id	Execution that triggered this gate
agent_version_id	INT	NOT NULL, FK agent_versions.id	Version at time of request (for governance audit)
turn_number	INT	NOT NULL	Turn at which the approval gate was triggered
tool_name	VARCHAR(255)	NOT NULL	Tool call that triggered the gate
tool_arguments	JSONB	NOT NULL	Arguments as originally proposed by the LLM
modified_arguments	JSONB	NULLABLE	Approver-supplied replacement arguments; set only on edited_approved resolution
reasoning_summary	TEXT	NULLABLE	Agent's explanation for why it wants to take this action; surfaced in the approval modal
risk_context	TEXT	NULLABLE	Policy evaluation result and any warnings computed before the gate was triggered
status	VARCHAR(50)	NOT NULL, DEFAULT 'pending'	pending, approved, rejected, edited_approved, or expired
assigned_approver_role	VARCHAR(100)	NULLABLE	Role required to approve; NULL means any member with agent:approve permission
assigned_approver_id	INT	NULLABLE, FK users.id	Specific approver; NULL means role-based assignment
resolved_by	INT	NULLABLE, FK users.id	User who resolved the request
resolution_note	TEXT	NULLABLE	Required for rejected; optional for approved
created_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Request creation timestamp
resolved_at	TIMESTAMPTZ	NULLABLE	Resolution timestamp
expires_at	TIMESTAMPTZ	NOT NULL	Auto-expire timestamp; computed from workspace approval_timeout_hours setting

---

agent_templates

Pre-built configuration templates. Platform-provided templates have org_id = NULL; workspace-specific custom templates have a non-NULL org_id.

Column	Type	Constraints	Description
id	SERIAL	PK	Surrogate primary key
org_id	INT	NULLABLE, FK organizations.id	NULL for platform templates; non-NULL for org-scoped custom templates
name	VARCHAR(255)	NOT NULL	Template display name
category	VARCHAR(100)	NOT NULL	business_function value this template targets
description	TEXT	NULLABLE	Full description shown on Template Detail page
icon_name	VARCHAR(50)	NULLABLE	Lucide icon identifier for the template card
icon_bg	VARCHAR(50)	NULLABLE	Background color class
icon_color	VARCHAR(50)	NULLABLE	Icon foreground color class
configuration	JSONB	NOT NULL	Complete agent configuration payload used to pre-fill the Guided Wizard; see JSONB spec
capabilities	JSONB	NOT NULL, DEFAULT '[]'	Array of capability descriptor objects shown on Template Detail
integrations	JSONB	NOT NULL, DEFAULT '[]'	Recommended data source types and tool names
governance_default	VARCHAR(50)	NOT NULL, DEFAULT 'act_with_approval'	Default action_level for agents instantiated from this template
is_active	BOOLEAN	NOT NULL, DEFAULT true	Inactive templates are hidden from the Template Library
is_platform_template	BOOLEAN	NOT NULL, DEFAULT false	True for templates maintained by the platform team
created_by	INT	NULLABLE, FK users.id	NULL for platform templates
created_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Creation timestamp
updated_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Last update timestamp
deleted_at	TIMESTAMPTZ	NULLABLE	Soft delete

---

governance_policies

Policy definitions applied to agent versions. A policy with workspace_id = NULL applies org-wide. Policy evaluation runs on every tool call dispatch; the enforcement_action determines whether the call is blocked, gated, logged, or alerted.

Column	Type	Constraints	Description
id	SERIAL	PK	Surrogate primary key
org_id	INT	NOT NULL, FK organizations.id	Organization scope
workspace_id	INT	NULLABLE, FK workspaces.id	NULL for org-wide policies; non-NULL for workspace-specific
name	VARCHAR(255)	NOT NULL	Policy display name
description	TEXT	NULLABLE	Policy description
scope	VARCHAR(50)	NOT NULL, DEFAULT 'workspace'	org or workspace
condition_expression	TEXT	NOT NULL	Policy condition in the RiverCore policy language (WHEN/THEN/WITH DSL)
enforcement_action	VARCHAR(50)	NOT NULL	block, gate, log, or alert
version	INT	NOT NULL, DEFAULT 1	Policy version; incremented on each edit
active	BOOLEAN	NOT NULL, DEFAULT true	Inactive policies are not evaluated at runtime
created_by	INT	NOT NULL, FK users.id	User who created this policy
created_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Creation timestamp
updated_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Last update timestamp

---

audit_logs

Write-once immutable audit records. No updated_at, no deleted_at, no soft delete. The application layer has no DELETE or UPDATE path against this table. BIGINT primary key for high-volume write performance.

Column	Type	Constraints	Description
id	BIGINT	PK, AUTO_INCREMENT	Surrogate primary key
org_id	INT	NOT NULL	Organization scope (denormalized for partition-friendly queries)
workspace_id	INT	NULLABLE	Workspace scope; NULL for org-level events
agent_id	INT	NULLABLE, FK agents.id SET NULL	NULL for events not tied to a specific agent; SET NULL on agent delete so audit entries survive
execution_id	INT	NULLABLE, FK agent_executions.id SET NULL	NULL for events outside an execution context
actor_user_id	INT	NULLABLE, FK users.id	NULL for system-initiated events
actor_type	VARCHAR(50)	NOT NULL	human, agent, or system
event_type	VARCHAR(100)	NOT NULL	Namespaced event category (e.g., agent.created, run.started, approval.resolved, policy.violated, security.auth)
action	VARCHAR(255)	NOT NULL	Specific action description (e.g., "Agent deployed to production")
event_payload	JSONB	NOT NULL	Full event context; content varies by event_type
outcome	VARCHAR(50)	NOT NULL	success, failure, or blocked
ip_address	VARCHAR(45)	NULLABLE	Source IP address of the actor; captured for security events
user_agent	TEXT	NULLABLE	Browser or API client user agent; captured for security events
created_at	TIMESTAMPTZ	NOT NULL, DEFAULT NOW()	Event timestamp; not modifiable

---

JSONB Schema Specifications

agent_triggers.trigger_config

The schema varies by trigger_type. The trigger_type column is the discriminator.

scheduled trigger:

{
  "cron_expression": "0 8 * * 1-5",
  "timezone": "America/New_York",
  "cooldown_seconds": 300,
  "payload": {}
}

event trigger:

{
  "event_types": ["ticket.created", "ticket.updated_by_customer"],
  "source": "zendesk_webhook",
  "payload_conditions": {
    "priority": { "$in": ["high", "urgent"] },
    "channel": { "$ne": "internal" }
  }
}

payload_conditions uses MongoDB-style comparison operators evaluated against the incoming event payload. Supported operators: $eq, $ne, $in, $nin, $gt, $lt, $gte, $lte, $exists.

threshold trigger:

{
  "metric_source": "datadog",
  "metric_name": "system.cpu.user",
  "condition": "avg > 90",
  "evaluation_window_seconds": 300,
  "cooldown_seconds": 600
}

api trigger:

{
  "api_key_id": "key_abc123",
  "rate_limit_per_minute": 60,
  "payload_schema": {
    "type": "object",
    "required": ["event_type"],
    "properties": {
      "event_type": { "type": "string" },
      "data": { "type": "object" }
    }
  }
}

workflow trigger:

{
  "signal_name": "data_collected",
  "parent_workflow_id_pattern": "agent-run-*",
  "output_key": "$.report_url"
}

manual trigger:

{
  "input_schema": {
    "type": "object",
    "properties": {
      "customer_id": { "type": "integer" },
      "issue_summary": { "type": "string" }
    },
    "required": ["customer_id"]
  }
}

---

agent_versions.approval_config

{
  "require_approval_for": ["issue_refund", "update_ledger_status", "revoke_access"],
  "auto_approve_conditions": [
    {
      "tool": "draft_response",
      "condition": "confidence_score > 0.95"
    }
  ],
  "approver_roles": ["admin", "editor"],
  "approver_user_id": null,
  "expiry_hours": 24,
  "escalation_timeout_minutes": 60,
  "escalation_channel": "pagerduty"
}

require_approval_for is an array of tool names. Any tool in this list triggers an approval gate regardless of the agent's action_level. auto_approve_conditions bypass the gate for specific tools when the stated condition is satisfied.

---

agents.notification_config and agent_versions.notification_config

These two columns have the same schema. The version-level config overrides the agent-level config for the duration of runs using that version.

{
  "channels": {
    "slack": {
      "workspace_id": "T12345",
      "channel_id": "C67890",
      "channel_name": "#agent-alerts"
    },
    "email": {
      "recipients": ["admin@company.com", "team-lead@company.com"]
    },
    "pagerduty": {
      "service_id": "P12345",
      "severity": "high"
    }
  },
  "triggers": {
    "on_failure": true,
    "on_approval_needed": true,
    "on_completion": false,
    "on_health_degraded": true,
    "on_policy_violation": true
  }
}

---

agent_executions.context_snapshot

Written when an execution pauses at an approval gate. Contains sufficient state to resume the Temporal workflow from the exact point of interruption. The governance_token field is re-validated on resume to ensure the governance context has not changed during the wait.

{
  "agent_id": "42",
  "version": 3,
  "execution_id": "8821",
  "config": {
    "instruction_set": "Monitor customer support tickets and draft responses...",
    "action_level": "act_with_approval",
    "governance_level": "strict",
    "data_sources": [
      { "id": 7, "name": "Zendesk", "type": "saas_api", "access": "read_write" },
      { "id": 12, "name": "Confluence KB", "type": "saas_api", "access": "read_only" }
    ],
    "tools": ["search_knowledge_base", "get_ticket_history", "draft_response", "issue_refund"],
    "approval_rules": {
      "require_approval_for": ["issue_refund", "draft_response"]
    }
  },
  "runtime": {
    "turn_count": 4,
    "conversation_history": [
      { "turn": 1, "type": "reasoning", "content": "I need to check the customer history first..." },
      { "turn": 1, "type": "action", "tool": "get_ticket_history", "args": { "customer_id": "C-123" } },
      { "turn": 1, "type": "observation", "result": { "tickets": 3, "refunds": 0 } }
    ],
    "pending_tool_call": {
      "tool_name": "issue_refund",
      "arguments": { "amount": 49.99, "charge_id": "ch_abc123" }
    },
    "trigger_payload": { "event": "ticket.created", "ticket_id": 9912 }
  },
  "memory": {
    "last_processed_ticket": "TKT-9911",
    "common_issues": ["billing", "product_defect", "shipping"],
    "escalation_patterns": ["legal_threat", "data_breach", "executive_complaint"]
  },
  "governance_token": "gt_eyJhbGc...",
  "snapshot_at": "2024-04-14T10:23:11Z"
}

runtime.pending_tool_call is the exact tool call that triggered the approval gate. On approved resolution, this is the call that will execute (possibly with modified_arguments from the approver substituted for arguments).

---

agent_logs.content

Content varies by log_type.

reasoning type:

{
  "thought": "Customer is a VIP with no prior refunds. Purchase was 12 days ago. Policy allows refunds up to 30 days.",
  "next_action": "execute tool: issue_refund",
  "confidence": 0.91,
  "model_used": "claude-sonnet-4-6",
  "tokens_in": 2840,
  "tokens_out": 187
}

tool_call type:

{
  "tool_name": "issue_refund",
  "arguments": { "amount": 49.99, "charge_id": "ch_abc123" },
  "governance_decision": "APPROVAL_REQUIRED",
  "dispatch_id": "disp_7f3a91"
}

tool_result type:

{
  "tool_name": "issue_refund",
  "dispatch_id": "disp_7f3a91",
  "output": { "refund_id": "TX-882", "status": "processed" },
  "approval_id": 884,
  "resolution": "approved",
  "duration_ms": 312
}

approval_requested type:

{
  "approval_request_id": 884,
  "tool_name": "issue_refund",
  "arguments": { "amount": 49.99, "charge_id": "ch_abc123" },
  "expires_at": "2024-04-15T10:23:11Z"
}

approval_resolved type:

{
  "approval_request_id": 884,
  "resolution": "edited_approved",
  "resolved_by": 102,
  "modified_arguments": { "amount": 25.00, "charge_id": "ch_abc123" },
  "reviewer_note": "Partial refund per policy section 4.2"
}

error type:

{
  "error_type": "tool_timeout",
  "message": "Tool execute_query did not respond within 30 seconds",
  "retry_count": 2,
  "recoverable": true
}

---

agent_templates.configuration

The full pre-fill payload applied to the Guided Wizard when a user selects a template.

{
  "name_template": "{{category}} Agent",
  "description_template": "Autonomous {{category}} agent for enterprise operations.",
  "instruction_set_template": "You are a {{category}} specialist. Your goal is to monitor incoming tickets, retrieve relevant customer context, draft responses, and escalate when needed...",
  "business_function": "customer_support",
  "action_level": "act_with_approval",
  "governance_level": "strict",
  "recommended_tools": [
    "search_knowledge_base",
    "get_ticket_history",
    "draft_response",
    "issue_refund"
  ],
  "recommended_data_source_types": ["ticketing", "knowledge_base", "crm"],
  "default_triggers": {
    "events": ["ticket.created", "ticket.updated_by_customer"]
  },
  "default_approval_rules": {
    "require_approval_for": ["send_response", "issue_refund"],
    "approver_roles": ["admin", "editor"]
  }
}

---

Indexing Strategy

Index Name	Table	Columns	Type	Purpose
idx_agents_workspace_status	agents	(workspace_id, status)	BTREE	Agent Library page filter queries
idx_agents_org_deleted	agents	(org_id, deleted_at)	BTREE PARTIAL (deleted_at IS NULL)	Tenant-scoped soft-delete filtering
idx_agent_versions_agent	agent_versions	(agent_id, version_number DESC)	BTREE	Version history list and rollback queries
idx_agent_executions_agent_started	agent_executions	(agent_id, started_at DESC)	BTREE	Per-agent run history page
idx_agent_executions_workspace_status	agent_executions	(workspace_id, status, started_at DESC)	BTREE	Global Runs Management page filtering
idx_agent_executions_version	agent_executions	(agent_version_id)	BTREE	FK support for version-scoped queries
idx_agent_logs_execution_turn	agent_logs	(execution_id, turn_number)	BTREE	Turn-by-turn log viewer (most read index)
idx_agent_logs_content_gin	agent_logs	content	GIN	Full-text search within log content
idx_approval_requests_execution	approval_requests	(execution_id, status)	BTREE	Governance tab approval history
idx_approval_requests_approver	approval_requests	(assigned_approver_id, status)	BTREE PARTIAL (status = 'pending')	Pending approval notification queries
idx_audit_logs_org_created	audit_logs	(org_id, created_at DESC)	BTREE	Audit trail page chronological queries
idx_audit_logs_agent	audit_logs	(agent_id, created_at DESC)	BTREE	Per-agent scoped audit trail
idx_audit_logs_execution	audit_logs	(execution_id)	BTREE	Execution-scoped audit entries
idx_agent_triggers_agent_type	agent_triggers	(agent_id, trigger_type, is_active)	BTREE	Trigger evaluation at dispatch time

Implementation note: idx_agent_logs_execution_turn is the most heavily read index in the system during active runs. The WebSocket emitter and monitoring service query this index on every turn. It must be a BTREE on (execution_id, turn_number) -- GIN or HASH would not support the sequential turn-order access pattern.

---

Foreign Key Cascade Rules

Relationship	On Parent Delete	Rationale
agents -> agent_versions	RESTRICT	Versions are compliance artifacts; agent deletion must be soft delete only
agents -> agent_executions	RESTRICT	Execution history must be retained; soft delete only
agents -> agent_triggers	CASCADE	Triggers are owned by the agent; physical delete of an agent (only via admin tooling) removes its triggers
agent_versions -> agent_version_tools	CASCADE	Tool bindings are part of the version snapshot
agent_versions -> agent_version_data_sources	CASCADE	Same as tool bindings
agent_executions -> agent_logs	CASCADE	Log entries have no meaning without their parent execution
agent_executions -> approval_requests	RESTRICT	Approval records are compliance artifacts; must be retained after execution
audit_logs -> agents	SET NULL	Audit entries must survive agent deletion; agent_id set to NULL while event_payload retains the agent name at event time
audit_logs -> agent_executions	SET NULL	Same rationale as above

Physical delete policy: No application code path issues DELETE statements against agents, agent_versions, agent_executions, approval_requests, or audit_logs. All removal operations are soft deletes (deleted_at) or status transitions (status = 'archived'). Physical deletes on these tables are reserved for admin data retention tooling only and must produce an audit_logs entry before executing.

---

Tenant Isolation Enforcement

Tenant isolation operates at three concurrent layers. All three must be active simultaneously; failure of any one layer does not degrade to unprotected access.

Layer 1 -- Application query scoping: Every repository method in Backend :8005 accepts org_id and workspace_id parameters and includes them in the WHERE clause of every query. These values are extracted from validated JWT claims by TLO Gateway and propagated as request headers (X-Org-ID, X-Workspace-ID). No query against River Agents tables is issued without both parameters.

Layer 2 -- PostgreSQL Row-Level Security: RLS policies are enabled on agents, agent_executions, and audit_logs. The policy condition uses current_setting('app.org_id'), which Backend :8005 sets via SET LOCAL app.org_id = {org_id} at the start of each database transaction. If the application-layer header injection fails, RLS prevents any cross-tenant data access at the database level.

Layer 3 -- river-agent context bundle scoping: The AgentContext object passed to river-agent :8007 on each turn contains an explicit workspace_id field. Tool calls referencing data sources are validated against agent_version_data_sources, which are already scoped to the version's workspace. The river-agent microservice has no direct database access and cannot issue unscoped queries.

Isolation failure detection: Any query that returns a row with a mismatched org_id triggers an audit_logs entry with event_type = 'security.cross_tenant_access_attempt' and outcome = 'blocked'. This event is forwarded to the Security Alert Stream in real time.