Schema Design
Authoritative reference for the river_agents PostgreSQL schema defined in rgen-repo-backend/rgen-backend/db/river-agents-schema.sql -- covering all 15 tables, 14 schema-scoped enum types, design conventions, complete JSONB column specifications, cross-schema references, primary query patterns, data lifecycle flows, and the Alembic migration strategy.
Quick Navigation
Schema Namespace
All River Agents database objects reside in the river_agents PostgreSQL schema, created via:
CREATE SCHEMA IF NOT EXISTS river_agents;
This follows the same pattern as the automation schema. Namespace isolation provides clear ownership boundaries and allows the schema file to load independently of other modules.
DDL file location:
rgen-repo-backend/rgen-backend/db/river-agents-schema.sql
Schema Dependencies
| Dependency | Tables Referenced | Purpose |
|---|---|---|
iam-schema.sql | organizations, users, workspaces | Tenant isolation, user references, workspace scoping |
platform-schema.sql | data_sources | Data source bindings in agent_data_sources |
Cross-schema foreign key constraints are defined as commented-out ALTER TABLE statements at the bottom of the file and applied after the IAM and platform schemas are loaded. The River Agents schema loads independently without them.
Design Conventions
| Convention | Rule |
|---|---|
| Primary keys | INT GENERATED BY DEFAULT AS IDENTITY on all tables -- compact 4-byte integers, auto-increment, allows explicit values during seed data loading |
| Tenant isolation | organization_id INTEGER NOT NULL and workspace_id INTEGER NOT NULL on all top-level tables; composite index on both columns on every such table |
| Tenant inheritance | Child tables (agent_versions, agent_triggers, agent_data_sources, agent_tools, execution_steps, governance_policies, agent_metrics) do not carry organization_id/workspace_id -- they inherit tenant context via FK to their parent |
| Soft deletes | deleted_at TIMESTAMPTZ on agents only; NULL means active; partial index filters active records efficiently; no other table uses soft delete |
| Immutable records | agent_versions, execution_steps, audit_logs -- no updated_at, no deleted_at; rows are never modified after insert |
| Audit timestamps | created_at TIMESTAMPTZ NOT NULL DEFAULT NOW() on all tables; updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW() on all mutable tables |
| JSONB columns | All JSONB columns are NOT NULL DEFAULT '{}'::jsonb (objects) or '[]'::jsonb (arrays); validation is delegated to Pydantic models at the application layer |
| Enum types | All enums are schema-scoped: river_agents.agent_status, etc. -- prevents naming collisions with other schemas |
| Denormalized statistics | agents carries pre-computed runtime stats (total_runs, successful_runs, failed_runs, avg_duration_seconds, last_run_status, health_score) updated after each execution; authoritative data is in executions |
| Cross-schema FKs | Defined as logical references only; no database-level REFERENCES constraints cross schema boundaries; application-layer enforcement at service boundaries |
| Index strategy | Composite (organization_id, workspace_id) on every tenant-scoped table; partial indexes on boolean and soft-delete columns; GIN indexes on JSONB array columns |
Enum Types
14 schema-scoped enum types defined in river_agents:
river_agents.agent_status (9 values)
Full agent lifecycle from creation to archival. Maps to PRD Section 11 state machine.
| Value | Description | Transitions From |
|---|---|---|
draft | Initial state after creation | (creation) |
configured | All required fields populated, not yet validated | draft |
validated | Configuration validated by the governance engine | configured |
deployed | Deployed to the runtime environment | validated |
active | Live and ready to accept triggers | deployed |
running | Currently executing a run | active |
awaiting_approval | Execution paused at an approval gate | running |
paused | Manually paused by an administrator | active, running |
archived | Decommissioned; preserved for audit | any state |
Used on: agents.status
river_agents.agent_category (8 values)
Classifies agents by business function. Maps to PRD Section 6.
| Value | Description |
|---|---|
customer_support | Handles tickets, drafts responses, routes escalations |
sales_lead_qualification | Scores leads, enriches CRM, routes to sales reps |
finance_reconciliation | Matches transactions, detects discrepancies, generates reports |
risk_compliance | Monitors risk indicators, flags policy violations |
data_analyst | Runs queries, builds reports, answers data questions |
operations_monitoring | Watches infrastructure metrics, triggers alerts |
executive_decision | Multi-source analysis for executive-level decision support |
custom_enterprise | User-defined category for bespoke enterprise workflows |
Used on: agents.category, templates.category
river_agents.action_level (4 values)
Bounded-autonomy level from PRD Section 7. Controls what an agent may do without human intervention.
| Value | Description |
|---|---|
read_only | Agent reads data and produces text responses only. No mutations. |
recommend | Agent reads and produces recommendations but cannot take action. |
act_with_approval | Agent proposes actions but requires human approval before execution. |
fully_automated | Agent takes actions autonomously without human approval. |
Used on: agents.action_level, workspace_settings.default_action_level, workspace_settings.require_approval_above_level
river_agents.trigger_type (6 values)
How an agent run is initiated. Maps to PRD Section 8.
| Value | Description |
|---|---|
manual | User clicks "Run Now" in the UI |
scheduled | Cron schedule (e.g., every weekday at 9am) |
event | External event (webhook, SaaS event, pub/sub message) |
workflow | Completion of another agent (agent chaining) |
api | External system via the River Agents API |
threshold | Monitored metric crosses a defined threshold |
Used on: agent_triggers.trigger_type, executions.trigger_type
river_agents.execution_status (8 values)
State of a single agent execution.
| Value | Description |
|---|---|
pending | Execution record created, not yet started |
queued | In the task queue waiting for a worker |
running | Agentic loop actively processing |
awaiting_approval | Paused at an approval gate |
success | Completed successfully |
failed | Failed with an error |
cancelled | Cancelled by a user or policy |
timeout | Exceeded the configured timeout |
Used on: executions.status, agents.last_run_status
river_agents.approval_status (5 values)
Resolution state of an approval request.
| Value | Description |
|---|---|
pending | Awaiting human review |
approved | Approved; execution resumes with original action |
rejected | Rejected; execution skips or fails the action |
expired | Approval window elapsed without response |
auto_approved | Automatically approved by a governance policy rule |
Used on: approval_requests.status
river_agents.step_type (6 values)
Phase classification for each step in the agentic Reason/Act/Observe loop.
| Value | Phase | Description |
|---|---|---|
reasoning | Reason | LLM generates a thought and decides the next action |
tool_call | Act | Agent invokes a tool |
observation | Observe | Agent processes the tool result and updates context |
interaction | Interact | Agent communicates with the user via ask_user |
approval_gate | Gate | Execution pauses to request human approval |
error_recovery | Recovery | Agent attempts to recover from a tool failure |
Used on: execution_steps.step_type
river_agents.audit_event_type (33 values)
Every auditable event in the system, organized by domain.
Agent Lifecycle (7): agent_created, agent_updated, agent_deployed, agent_paused, agent_resumed, agent_archived, agent_deleted
Execution Events (4): execution_started, execution_completed, execution_failed, execution_cancelled
Approval Events (4): approval_requested, approval_granted, approval_rejected, approval_expired
Trigger Events (3): trigger_created, trigger_updated, trigger_deleted
Policy Events (3): policy_created, policy_updated, policy_violated
Settings and API Events (4): settings_updated, api_key_created, api_key_revoked, webhook_created
Webhook Events (3): webhook_updated, webhook_deleted, webhook_delivery_failed
Template Events (2): template_created, template_updated
Data Source and Tool Events (4): data_source_bound, data_source_unbound, tool_enabled, tool_disabled
Used on: audit_logs.event_type
river_agents.policy_type (8 values)
Classifies governance policies by the type of constraint enforced.
| Value | Description |
|---|---|
budget_limit | Caps LLM token and API call spending |
rate_limit | Limits how frequently an agent can run |
data_access_restriction | Restricts which tables, schemas, or operations an agent can access |
action_restriction | Restricts which tools an agent can invoke |
time_window | Limits when an agent can execute |
token_limit | Caps token consumption per run and per day |
approval_threshold | Defines automatic approval rules based on cost or row count |
output_validation | Validates agent output format and content |
Used on: governance_policies.policy_type
river_agents.data_access_level (3 values)
Read/write permission for a bound data source.
| Value | Description |
|---|---|
read | Agent can query/read data only |
write | Agent can write/mutate data only (used for write-only sinks) |
read_write | Agent can both read and write data |
Used on: agent_data_sources.access_level
river_agents.tool_category (3 values)
| Value | Description |
|---|---|
reasoning | AI reasoning tools (e.g., classify_intent, generate_query). Execute locally; no ACL check. |
execution | Tools that call external services via TLO Gateway. Per-call ACL check required. |
interaction | User-facing communication tools (e.g., ask_user). No ACL check. |
Used on: agent_tools.tool_category, execution_steps.tool_category
river_agents.priority (4 values)
low, medium (default), high, critical
Used on: executions.priority, workspace_settings.compute_priority
river_agents.log_level (5 values)
debug, info, warning, error, critical
Available for use in execution step metadata and audit log severity tagging.
river_agents.webhook_event (9 values)
Events that trigger outbound webhook delivery: execution.started, execution.completed, execution.failed, approval.requested, approval.resolved, agent.deployed, agent.paused, agent.error, policy.violated
Used on: webhooks.events (as JSONB array of selected values)
Table Summary
| # | Table | Purpose | Tenant-Scoped | Soft Delete | Row Volume |
|---|---|---|---|---|---|
| 1 | agents | Core agent entity with identity, config, and runtime statistics | Yes | Yes | Low-medium (100s-1000s per org) |
| 2 | agent_versions | Immutable configuration snapshots for version tracking | No (inherits) | No | Medium (grows per config change) |
| 3 | agent_triggers | Trigger definitions -- how an agent is activated | No (inherits) | No | Low-medium |
| 4 | agent_data_sources | Agent-to-data-source bindings with access level control | No (inherits) | No | Low-medium (junction) |
| 5 | agent_tools | Agent-to-tool bindings with per-tool configuration | No (inherits) | No | Low-medium (junction) |
| 6 | executions | One record per agent run -- the primary runtime record | Yes | No | High (one per run) |
| 7 | execution_steps | Turn-level trace of the agentic Reason/Act/Observe loop | No (inherits) | No | Very high (many per execution) |
| 8 | approval_requests | Human-in-the-loop approval gates | Yes | No | Medium |
| 9 | templates | Pre-configured agent blueprints | Optional | No | Low |
| 10 | governance_policies | Per-agent governance rules evaluated before each tool call | No (inherits) | No | Low-medium |
| 11 | agent_metrics | Pre-aggregated daily metrics per agent | No (inherits) | No | High (time-series) |
| 12 | workspace_settings | Workspace-level defaults for the Settings page | Yes | No | One row per workspace |
| 13 | api_keys | API keys for triggering agents via the API trigger type | Yes | No | Low |
| 14 | webhooks | Outbound webhook subscriptions | Yes | No | Low |
| 15 | audit_logs | Immutable, append-only audit trail | Yes | No | Very high (BIGSERIAL PK) |
Supporting tables not in the 15-table core but defined in the schema: agent_memory (persistent key-value memory per agent), agent_notifications (notification delivery records).
Table Definitions
agents
Purpose: Core agent entity. One row per logical agent. Source of truth for the agent list, dashboard cards, and detail pages. Carries denormalized runtime statistics to avoid expensive aggregation queries on render.
| Column | Type | Nullable | Default | Description |
|---|---|---|---|---|
id | INT (identity) | No | Auto | Primary key |
organization_id | int | No | -- | Tenant isolation. References organizations.id |
workspace_id | int | No | -- | Workspace scope. References workspaces.id |
created_by | int | No | -- | User who created the agent. References users.id |
name | varchar(255) | No | -- | Human-readable agent name |
description | text | Yes | -- | Detailed description of the agent's purpose |
category | agent_category | No | -- | Business function category (8 values) |
status | agent_status | No | 'draft' | Current lifecycle state (9 values) |
action_level | action_level | No | 'read_only' | Bounded-autonomy level (4 values) |
icon_name | varchar(100) | Yes | -- | Lucide icon name for UI display |
icon_color | varchar(50) | Yes | -- | Tailwind color class for the icon |
icon_bg | varchar(50) | Yes | -- | Tailwind background class |
goal | text | No | -- | Natural language goal statement |
instructions | text | Yes | -- | Detailed behavioral instructions (the instruction_set) |
system_prompt | text | Yes | -- | Custom system prompt template (overrides generated prompt) |
model_config | jsonb | No | '{}' | Multi-LLM routing preferences. See JSONB spec. |
notification_config | jsonb | No | '{}' | Notification channel and trigger settings. See JSONB spec. |
tags | jsonb | No | '[]' | Array of string tags for filtering and search |
metadata | jsonb | No | '{}' | Flexible key-value metadata bag |
current_version_id | int | Yes | -- | FK to agent_versions.id -- the currently active version |
owner_user_id | int | No | -- | Agent owner/administrator. References users.id |
deployed_at | timestamptz | Yes | -- | Timestamp of last deployment |
deployed_by | int | Yes | -- | User who last deployed. References users.id |
last_run_at | timestamptz | Yes | -- | Denormalized: when the last execution started |
last_run_status | execution_status | Yes | -- | Denormalized: status of the last execution |
total_runs | int | No | 0 | Denormalized: total execution count |
successful_runs | int | No | 0 | Denormalized: successful execution count |
failed_runs | int | No | 0 | Denormalized: failed execution count |
avg_duration_seconds | numeric(10,2) | Yes | -- | Denormalized: average execution duration |
health_score | numeric(5,2) | Yes | -- | Denormalized: computed health score (0.00-100.00) |
created_at | timestamptz | No | NOW() | Creation timestamp |
updated_at | timestamptz | No | NOW() | Last modification timestamp |
deleted_at | timestamptz | Yes | -- | Soft delete timestamp. NULL = active |
Foreign Keys (within schema): current_version_id -> agent_versions.id (applied post-creation via ALTER TABLE)
Foreign Keys (cross-schema, commented-out): organization_id -> organizations.id ON DELETE CASCADE, workspace_id -> workspaces.id ON DELETE CASCADE, created_by -> users.id ON DELETE SET NULL, owner_user_id -> users.id ON DELETE SET NULL
Indexes:
| Index | Columns | Type | Rationale |
|---|---|---|---|
idx_agents_org_workspace | (organization_id, workspace_id) | B-tree | Tenant-isolated listing queries |
idx_agents_status | (status) | B-tree | Dashboard status filters |
idx_agents_category | (category) | B-tree | Category facet filtering |
idx_agents_action_level | (action_level) | B-tree | Action level facet filtering |
idx_agents_owner | (owner_user_id) | B-tree | "My Agents" view |
idx_agents_deleted_at | (deleted_at) WHERE deleted_at IS NULL | Partial B-tree | Active-only queries |
idx_agents_tags | (tags) | GIN | JSONB array containment queries (@>) |
UI Pages: Agent List (cards), Agent Detail, Agent Builder (wizard), Dashboard
agent_versions
Purpose: Immutable configuration snapshots. A new version row is created every time an agent's configuration is saved or deployed. Enables rollback, audit comparison, and deployment history. Rows are never modified after insert.
| Column | Type | Nullable | Default | Description |
|---|---|---|---|---|
id | INT (identity) | No | Auto | Primary key |
agent_id | int | No | -- | Parent agent. FK to agents.id ON DELETE CASCADE |
version_number | int | No | -- | Sequential version number (1, 2, 3...) |
config_snapshot | jsonb | No | '{}' | Full agent brain configuration at this point in time. See JSONB spec. |
is_active | boolean | No | false | Whether this is the currently deployed version |
change_summary | text | Yes | -- | Human-readable description of what changed |
created_by | int | No | -- | User who created this version. References users.id |
created_at | timestamptz | No | NOW() | Version creation timestamp |
Unique Constraint: (agent_id, version_number) -- version numbers are unique per agent
Indexes:
| Index | Columns | Type | Rationale |
|---|---|---|---|
idx_agent_versions_unique | (agent_id, version_number) | Unique B-tree | Business-rule uniqueness |
idx_agent_versions_active | (agent_id) WHERE is_active = true | Partial B-tree | Fast lookup of the active version |
UI Pages: Agent Detail (Version History tab), Deployment History
agent_triggers
Purpose: Defines how an agent is activated. Each agent can have multiple triggers (e.g., a cron schedule AND a webhook). The config JSONB structure varies by trigger_type.
| Column | Type | Nullable | Default | Description |
|---|---|---|---|---|
id | INT (identity) | No | Auto | Primary key |
agent_id | int | No | -- | Parent agent. FK to agents.id ON DELETE CASCADE |
trigger_type | trigger_type | No | -- | One of: manual, scheduled, event, workflow, api, threshold |
config | jsonb | No | '{}' | Type-specific configuration. See JSONB spec (6 variants). |
is_active | boolean | No | true | Whether this trigger is currently enabled |
last_triggered_at | timestamptz | Yes | -- | Last time this trigger fired |
next_trigger_at | timestamptz | Yes | -- | Next scheduled fire time (for scheduled triggers) |
created_at | timestamptz | No | NOW() | Creation timestamp |
updated_at | timestamptz | No | NOW() | Last modification timestamp |
Indexes:
| Index | Columns | Type | Rationale |
|---|---|---|---|
idx_agent_triggers_agent | (agent_id) | B-tree | List all triggers for an agent |
idx_agent_triggers_type | (trigger_type) | B-tree | Filter by trigger type |
idx_agent_triggers_active | (is_active) WHERE is_active = true | Partial B-tree | Active triggers only |
idx_agent_triggers_next | (next_trigger_at) WHERE next_trigger_at IS NOT NULL | Partial B-tree | Scheduler polling for next-due triggers |
UI Pages: Agent Builder (Triggers step), Agent Detail (Triggers tab)
agent_data_sources
Purpose: Many-to-many binding between agents and platform data sources. Controls what data an agent can access and at what permission level.
| Column | Type | Nullable | Default | Description |
|---|---|---|---|---|
id | INT (identity) | No | Auto | Primary key |
agent_id | int | No | -- | Parent agent. FK to agents.id ON DELETE CASCADE |
data_source_id | int | No | -- | Platform data source. References data_sources.id (platform schema) |
access_level | data_access_level | No | 'read' | read, write, or read_write |
config | jsonb | No | '{}' | Optional scope restrictions (allowed tables, denied operations) |
created_at | timestamptz | No | NOW() | Creation timestamp |
Unique Constraint: (agent_id, data_source_id) -- an agent cannot bind the same data source twice
Indexes:
| Index | Columns | Type | Rationale |
|---|---|---|---|
idx_agent_ds_unique | (agent_id, data_source_id) | Unique B-tree | Prevents duplicate bindings |
idx_agent_ds_agent | (agent_id) | B-tree | List all data sources for an agent |
idx_agent_ds_source | (data_source_id) | B-tree | Find all agents using a given data source |
UI Pages: Agent Builder (Data Sources step), Agent Detail (Data Sources tab)
agent_tools
Purpose: Binds individual tools to an agent. Controls which tools the agent can use and provides per-tool configuration overrides.
| Column | Type | Nullable | Default | Description |
|---|---|---|---|---|
id | INT (identity) | No | Auto | Primary key |
agent_id | int | No | -- | Parent agent. FK to agents.id ON DELETE CASCADE |
tool_name | varchar(255) | No | -- | Tool identifier (e.g., execute_query, search_catalog) |
tool_category | tool_category | No | -- | reasoning, execution, or interaction |
is_enabled | boolean | No | true | Whether this tool is currently active for the agent |
config | jsonb | No | '{}' | Per-tool configuration overrides (timeout, retry count, disabled flag) |
created_at | timestamptz | No | NOW() | Creation timestamp |
Unique Constraint: (agent_id, tool_name) -- no duplicate tool bindings per agent
Indexes:
| Index | Columns | Type | Rationale |
|---|---|---|---|
idx_agent_tools_unique | (agent_id, tool_name) | Unique B-tree | Prevents duplicate tool names per agent |
idx_agent_tools_agent | (agent_id) | B-tree | List all tools for an agent |
idx_agent_tools_enabled | (agent_id) WHERE is_enabled = true | Partial B-tree | Fast lookup of only enabled tools |
UI Pages: Agent Builder (Tools step), Agent Detail (Tools tab)
executions
Purpose: One record per agent run. The primary runtime record. Source of truth for the Runs tab, agent activity feed, analytics dashboards, and cost tracking.
| Column | Type | Nullable | Default | Description |
|---|---|---|---|---|
id | INT (identity) | No | Auto | Primary key |
organization_id | int | No | -- | Tenant isolation. References organizations.id |
workspace_id | int | No | -- | Workspace scope. References workspaces.id |
agent_id | int | No | -- | Agent that was executed. FK to agents.id ON DELETE CASCADE |
agent_version_id | int | Yes | -- | Version used for this run. FK to agent_versions.id ON DELETE SET NULL |
trigger_type | trigger_type | No | 'manual' | What initiated this run |
trigger_id | int | Yes | -- | Specific trigger record. FK to agent_triggers.id ON DELETE SET NULL |
triggered_by_user_id | int | Yes | -- | User who triggered (for manual runs). References users.id |
input_prompt | text | Yes | -- | Natural language prompt that initiated the run |
status | execution_status | No | 'pending' | Current execution state (8 values) |
priority | priority | No | 'medium' | Execution priority for queue ordering |
started_at | timestamptz | Yes | -- | When execution began processing |
completed_at | timestamptz | Yes | -- | When execution finished (any terminal state) |
duration_seconds | numeric(10,2) | Yes | -- | Total wall-clock duration |
input_context | jsonb | No | '{}' | Full context sent to the agent runtime. See JSONB spec. |
turn_count | int | No | 0 | Total reasoning turns in the agentic loop |
tool_calls_count | int | No | 0 | Total tool invocations |
tokens_used | jsonb | No | '{}' | Per-model token usage breakdown. See JSONB spec. |
total_tokens | int | No | 0 | Sum of all tokens across all models |
cost_estimate | numeric(10,4) | Yes | -- | Estimated cost in USD |
result_summary | jsonb | No | '{}' | Structured result of the execution. See JSONB spec. |
error_message | text | Yes | -- | Error message if status = failed |
error_code | varchar(100) | Yes | -- | Machine-readable error code |
retry_count | int | No | 0 | Number of retries attempted |
temporal_workflow_id | varchar(255) | Yes | -- | Temporal workflow ID for correlation |
temporal_run_id | varchar(255) | Yes | -- | Temporal run ID for correlation |
context_snapshot | jsonb | Yes | -- | Serialized agent context at an approval gate. NULL if no approval gate was hit. See JSONB spec. |
metadata | jsonb | No | '{}' | Flexible metadata bag |
created_at | timestamptz | No | NOW() | Creation timestamp |
updated_at | timestamptz | No | NOW() | Last update timestamp |
Indexes:
| Index | Columns | Type | Rationale |
|---|---|---|---|
idx_executions_org_workspace | (organization_id, workspace_id) | B-tree | Tenant-isolated queries |
idx_executions_agent | (agent_id) | B-tree | List runs for a specific agent |
idx_executions_status | (status) | B-tree | Filter by execution status |
idx_executions_trigger_type | (trigger_type) | B-tree | Filter by trigger type |
idx_executions_created_at | (created_at) | B-tree | Time-range queries for execution history |
idx_executions_temporal | (temporal_workflow_id) WHERE temporal_workflow_id IS NOT NULL | Partial B-tree | Correlate with Temporal workflows |
UI Pages: Runs tab, Agent Detail (Activity section), Monitoring Dashboard, Analytics
execution_steps
Purpose: Records each step of the agentic Reason/Act/Observe loop. Provides full turn-level traceability for the execution log viewer. Rows are append-only.
| Column | Type | Nullable | Default | Description |
|---|---|---|---|---|
id | INT (identity) | No | Auto | Primary key |
execution_id | int | No | -- | Parent execution. FK to executions.id ON DELETE CASCADE |
step_number | int | No | -- | Sequential step index within the execution |
step_type | step_type | No | -- | Phase of the agentic loop (6 values) |
tool_name | varchar(255) | Yes | -- | Tool called (null for reasoning/observation steps) |
tool_category | tool_category | Yes | -- | Category of the tool called |
input_data | jsonb | No | '{}' | Input sent to this step. See JSONB spec. |
output_data | jsonb | No | '{}' | Output produced by this step. See JSONB spec. |
model_used | varchar(100) | Yes | -- | LLM model ID (for reasoning steps) |
model_provider | varchar(50) | Yes | -- | LLM provider (gemini, openai, anthropic, deepseek) |
tokens_input | int | Yes | -- | Input tokens consumed (for reasoning steps) |
tokens_output | int | Yes | -- | Output tokens produced (for reasoning steps) |
duration_ms | int | Yes | -- | Step execution time in milliseconds |
status | varchar(50) | No | 'success' | Step outcome: success, error, timeout, skipped, blocked |
governance_decision | varchar(50) | Yes | -- | PROCEED, APPROVAL_REQUIRED, BLOCKED, SUGGEST_ONLY (for tool steps) |
error_message | text | Yes | -- | Error details if step failed |
created_at | timestamptz | No | NOW() | Step creation timestamp |
Indexes:
| Index | Columns | Type | Rationale |
|---|---|---|---|
idx_exec_steps_execution | (execution_id) | B-tree | List all steps for an execution |
idx_exec_steps_order | (execution_id, step_number) | B-tree | Ordered step retrieval for the log viewer |
idx_exec_steps_tool | (tool_name) WHERE tool_name IS NOT NULL | Partial B-tree | Tool usage analytics |
UI Pages: Execution Log Viewer, Agent Detail (Run Details panel)
approval_requests
Purpose: Created when an agent execution hits an approval gate (action_level = act_with_approval). The execution pauses via Temporal workflow.wait_condition() until this record is resolved. Drives the Pending Approvals queue.
| Column | Type | Nullable | Default | Description |
|---|---|---|---|---|
id | INT (identity) | No | Auto | Primary key |
organization_id | int | No | -- | Tenant isolation. References organizations.id |
workspace_id | int | No | -- | Workspace scope. References workspaces.id |
execution_id | int | No | -- | Parent execution. FK to executions.id ON DELETE CASCADE |
agent_id | int | No | -- | Related agent. FK to agents.id ON DELETE CASCADE |
execution_step_id | int | Yes | -- | Step that triggered the gate. FK to execution_steps.id ON DELETE SET NULL |
action_description | text | No | -- | Human-readable description of what the agent wants to do |
action_tool | varchar(255) | Yes | -- | Tool the agent wants to invoke |
action_payload | jsonb | No | '{}' | Full payload the agent intends to execute. See JSONB spec. |
context_snapshot | jsonb | No | '{}' | Serialized conversation state and pending tool call for resume |
status | approval_status | No | 'pending' | Current resolution state (5 values) |
requested_at | timestamptz | No | NOW() | When the approval was requested |
expires_at | timestamptz | Yes | -- | When this request expires (from escalation_timeout_minutes) |
responded_at | timestamptz | Yes | -- | When the approver responded |
responded_by_user_id | int | Yes | -- | User who approved/rejected. References users.id |
response_note | text | Yes | -- | Approver's comment |
temporal_workflow_id | varchar(255) | Yes | -- | Temporal workflow to signal on resolution |
temporal_signal_name | varchar(255) | Yes | -- | Temporal signal name (approval_resolution) |
created_at | timestamptz | No | NOW() | Creation timestamp |
updated_at | timestamptz | No | NOW() | Last update timestamp |
Indexes:
| Index | Columns | Type | Rationale |
|---|---|---|---|
idx_approvals_org_workspace | (organization_id, workspace_id) | B-tree | Tenant-isolated queries |
idx_approvals_execution | (execution_id) | B-tree | List approvals for a specific execution |
idx_approvals_agent | (agent_id) | B-tree | List all approvals for an agent |
idx_approvals_status | (status) | B-tree | Filter by resolution status |
idx_approvals_pending | (status, expires_at) WHERE status = 'pending' | Partial B-tree | Pending approval queue with expiry ordering |
UI Pages: Pending Approvals page, Agent Detail (Approvals tab), Notification center
templates
Purpose: Pre-configured agent blueprints. System templates (is_system = true) are platform-provided with organization_id = NULL. Custom templates are organization-scoped. Templates populate the "Create Agent" wizard gallery.
| Column | Type | Nullable | Default | Description |
|---|---|---|---|---|
id | INT (identity) | No | Auto | Primary key |
organization_id | int | Yes | -- | Owning organization (NULL for system templates) |
workspace_id | int | Yes | -- | Workspace scope (NULL for org-wide or system templates) |
name | varchar(255) | No | -- | Template name |
description | text | Yes | -- | Template description |
category | agent_category | No | -- | Agent category this template targets |
icon_name | varchar(100) | Yes | -- | Lucide icon name |
icon_color | varchar(50) | Yes | -- | Tailwind icon color class |
icon_bg | varchar(50) | Yes | -- | Tailwind background color class |
config | jsonb | No | '{}' | Full template configuration. See JSONB spec. |
integrations | jsonb | No | '[]' | Expected integration identifiers |
governance | jsonb | No | '{}' | Default governance settings |
is_system | boolean | No | false | Whether this is a platform-provided template |
is_active | boolean | No | true | Whether the template is currently available |
usage_count | int | No | 0 | How many agents have been created from this template |
created_by | int | Yes | -- | Template author (NULL for system templates). References users.id |
created_at | timestamptz | No | NOW() | Creation timestamp |
updated_at | timestamptz | No | NOW() | Last update timestamp |
Indexes:
| Index | Columns | Type | Rationale |
|---|---|---|---|
idx_templates_org | (organization_id) WHERE organization_id IS NOT NULL | Partial B-tree | List custom templates for an org |
idx_templates_category | (category) | B-tree | Filter by agent category |
idx_templates_system | (is_system) WHERE is_system = true | Partial B-tree | Fast retrieval of system templates |
idx_templates_active | (is_active) WHERE is_active = true | Partial B-tree | Active templates in the gallery only |
UI Pages: Agent Creation Wizard (template gallery), Template Management (admin)
governance_policies
Purpose: Per-agent governance rules. Multiple policies of different types can be attached to a single agent. All active policies are evaluated before each tool call during execution.
| Column | Type | Nullable | Default | Description |
|---|---|---|---|---|
id | INT (identity) | No | Auto | Primary key |
agent_id | int | No | -- | Agent this policy applies to. FK to agents.id ON DELETE CASCADE |
policy_type | policy_type | No | -- | Type of governance constraint (8 values) |
policy_config | jsonb | No | '{}' | Type-specific policy configuration. See JSONB spec. |
is_active | boolean | No | true | Whether this policy is currently enforced |
created_at | timestamptz | No | NOW() | Creation timestamp |
updated_at | timestamptz | No | NOW() | Last update timestamp |
Indexes:
| Index | Columns | Type | Rationale |
|---|---|---|---|
idx_gov_policies_agent | (agent_id) | B-tree | List all policies for an agent |
idx_gov_policies_type | (policy_type) | B-tree | Filter by policy type |
idx_gov_policies_active | (agent_id) WHERE is_active = true | Partial B-tree | Only evaluate active policies during execution |
UI Pages: Agent Builder (Governance step), Agent Detail (Governance tab)
agent_metrics
Purpose: Pre-aggregated daily metrics per agent. A background job populates this table after each execution completes. Drives monitoring dashboard charts, trend sparklines on agent cards, and analytics pages.
| Column | Type | Nullable | Default | Description |
|---|---|---|---|---|
id | INT (identity) | No | Auto | Primary key |
agent_id | int | No | -- | Agent this metric row belongs to. FK to agents.id ON DELETE CASCADE |
metric_date | date | No | -- | The date these metrics cover |
total_runs | int | No | 0 | Total executions on this date |
successful_runs | int | No | 0 | Successful executions |
failed_runs | int | No | 0 | Failed executions |
cancelled_runs | int | No | 0 | Cancelled executions |
timed_out_runs | int | No | 0 | Timed-out executions |
avg_duration_seconds | numeric(10,2) | Yes | -- | Average run duration on this date |
max_duration_seconds | numeric(10,2) | Yes | -- | Maximum run duration |
min_duration_seconds | numeric(10,2) | Yes | -- | Minimum run duration |
avg_turns_per_run | numeric(5,2) | Yes | -- | Average agentic loop turns per run |
total_tokens_used | bigint | No | 0 | Sum of all tokens consumed on this date |
total_cost_usd | numeric(10,4) | No | 0 | Estimated total cost in USD |
tool_usage | jsonb | No | '{}' | Per-tool invocation breakdown. See JSONB spec. |
approval_requests | int | No | 0 | Approval gates triggered |
approvals_granted | int | No | 0 | Approvals approved |
approvals_rejected | int | No | 0 | Approvals rejected |
created_at | timestamptz | No | NOW() | When this metric row was created |
Unique Constraint: (agent_id, metric_date) -- one row per agent per day
Indexes:
| Index | Columns | Type | Rationale |
|---|---|---|---|
idx_agent_metrics_daily | (agent_id, metric_date) | Unique B-tree | Primary lookup and uniqueness enforcement |
idx_agent_metrics_date | (metric_date) | B-tree | Cross-agent daily aggregation queries |
UI Pages: Agent Detail (Monitoring tab), Workspace Dashboard (charts), Analytics page
workspace_settings
Purpose: One row per workspace. Contains global defaults across all 6 tabs of the Settings page: General, Compute and Limits, Governance, Access Control, Notifications, API and Webhooks.
Unique Constraint: (organization_id, workspace_id) -- exactly one settings row per workspace
Selected columns by Settings tab:
General Tab:
| Column | Type | Default | Description |
|---|---|---|---|
organization_id | int | -- | Tenant isolation |
workspace_id | int | -- | Workspace scope |
default_action_level | action_level | 'read_only' | Default autonomy level for new agents |
default_model_tier | varchar(50) | 'balanced' | Default LLM tier |
max_agents_per_workspace | int | 50 | Maximum number of agents allowed |
auto_archive_after_days | int | -- | Auto-archive inactive agents after N days (NULL = never) |
default_notification_channels | jsonb | '["email"]' | Default notification channels for new agents |
Compute and Limits Tab:
| Column | Type | Default | Description |
|---|---|---|---|
max_concurrent_executions | int | 5 | Max simultaneous agent executions |
max_turns_per_execution | int | 15 | Max agentic loop turns per run |
execution_timeout_seconds | int | 300 | Max wall-clock time per execution |
max_tokens_per_execution | int | 50000 | Max total tokens per execution |
max_retries | int | 3 | Max automatic retries on failure |
retry_delay_seconds | int | 5 | Delay between retries |
compute_priority | priority | 'medium' | Default execution priority |
Governance Tab:
| Column | Type | Default | Description |
|---|---|---|---|
require_approval_above_level | action_level | 'act_with_approval' | Actions at or above this level require approval |
strict_governance_mode | boolean | false | When true, all governance policies are strictly enforced |
budget_limit_monthly_usd | numeric(10,2) | -- | Workspace-wide monthly budget cap (NULL = unlimited) |
budget_alert_threshold_percent | int | 80 | Alert when budget usage reaches this percentage |
require_certification_for_deploy | boolean | true | Whether agents must be validated before deployment |
policy_violation_action | varchar(50) | 'block' | On policy violation: block, warn, or log |
Access Control Tab:
| Column | Type | Default | Description |
|---|---|---|---|
default_agent_visibility | varchar(50) | 'workspace' | Default visibility scope for new agents |
allowed_categories | jsonb | '[]' | Restricted set of allowed agent categories (empty = all) |
allowed_data_source_ids | jsonb | '[]' | Restricted set of allowed data source IDs (empty = all) |
role_permissions | jsonb | '{}' | Role-based permission overrides |
Notifications Tab:
| Column | Type | Default | Description |
|---|---|---|---|
notifications_enabled | boolean | true | Master toggle for all notifications |
notify_on_execution_complete | boolean | true | Notify when an execution completes |
notify_on_execution_failure | boolean | true | Notify when an execution fails |
notify_on_approval_required | boolean | true | Notify when an approval is needed |
notify_on_policy_violation | boolean | true | Notify on policy violation |
notification_recipients | jsonb | '[]' | Default notification recipients |
slack_webhook_url | text | -- | Slack incoming webhook URL |
teams_webhook_url | text | -- | Microsoft Teams webhook URL |
API and Webhooks Tab:
| Column | Type | Default | Description |
|---|---|---|---|
api_access_enabled | boolean | false | Whether API-triggered executions are allowed |
api_rate_limit_per_minute | int | 60 | API request rate limit |
api_allowed_origins | jsonb | '[]' | CORS allowed origins for API access |
created_at | timestamptz | NOW() | Creation timestamp |
updated_at | timestamptz | NOW() | Last update timestamp |
UI Pages: Settings page (all 6 tabs)
api_keys
Purpose: API keys for triggering agents via the api trigger type. Keys are one-way hashed at creation; the plaintext is shown only once. Keys may be scoped to a specific agent or apply workspace-wide.
| Column | Type | Nullable | Default | Description |
|---|---|---|---|---|
id | INT (identity) | No | Auto | Primary key |
organization_id | int | No | -- | Tenant isolation. References organizations.id |
workspace_id | int | No | -- | Workspace scope. References workspaces.id |
agent_id | int | Yes | -- | Optional agent scope. NULL = workspace-level key. FK to agents.id ON DELETE CASCADE |
name | varchar(255) | No | -- | Human-readable key name (e.g., "Production API Key") |
key_prefix | varchar(10) | No | -- | First characters of the key for identification (e.g., "ra_k_abc") |
key_hash | varchar(255) | No | -- | One-way hash of the full API key |
permissions | jsonb | No | '{}' | Permissions this key grants. See JSONB spec. |
is_active | boolean | No | true | Whether the key is currently valid |
expires_at | timestamptz | Yes | -- | Optional expiration timestamp |
last_used_at | timestamptz | Yes | -- | Last time the key was used |
usage_count | int | No | 0 | Total number of times the key has been used |
created_by | int | No | -- | User who generated the key. References users.id |
created_at | timestamptz | No | NOW() | Creation timestamp |
Indexes:
| Index | Columns | Type | Rationale |
|---|---|---|---|
idx_api_keys_org_workspace | (organization_id, workspace_id) | B-tree | Tenant-isolated listing |
idx_api_keys_hash | (key_hash) | B-tree | Key lookup on inbound API requests |
idx_api_keys_agent | (agent_id) WHERE agent_id IS NOT NULL | Partial B-tree | Agent-scoped key lookup |
idx_api_keys_active | (is_active) WHERE is_active = true | Partial B-tree | Active keys only |
UI Pages: Settings (API and Webhooks tab), Agent Detail (API Keys tab)
webhooks
Purpose: Outbound webhook subscriptions. When a subscribed event occurs, the notification service delivers an HTTP POST to the registered URL with a JSON payload.
| Column | Type | Nullable | Default | Description |
|---|---|---|---|---|
id | INT (identity) | No | Auto | Primary key |
organization_id | int | No | -- | Tenant isolation. References organizations.id |
workspace_id | int | No | -- | Workspace scope. References workspaces.id |
agent_id | int | Yes | -- | Optional agent scope. NULL = workspace-wide subscription. FK to agents.id ON DELETE CASCADE |
name | varchar(255) | No | -- | Human-readable webhook name |
url | text | No | -- | Delivery endpoint URL |
events | jsonb | No | '[]' | Array of subscribed webhook_event values. See JSONB spec. |
headers | jsonb | No | '{}' | Custom HTTP headers to include in delivery. See JSONB spec. |
secret | varchar(255) | Yes | -- | HMAC secret for payload signature verification (stored hashed) |
is_active | boolean | No | true | Whether this subscription is currently enabled |
last_triggered_at | timestamptz | Yes | -- | Last time this webhook was triggered |
last_status_code | int | Yes | -- | HTTP status code from the last delivery attempt |
failure_count | int | No | 0 | Consecutive delivery failure count |
created_by | int | Yes | -- | User who registered this webhook. References users.id |
created_at | timestamptz | No | NOW() | Creation timestamp |
updated_at | timestamptz | No | NOW() | Last update timestamp |
Indexes:
| Index | Columns | Type | Rationale |
|---|---|---|---|
idx_webhooks_org_workspace | (organization_id, workspace_id) | B-tree | Tenant-isolated listing |
idx_webhooks_agent | (agent_id) WHERE agent_id IS NOT NULL | Partial B-tree | Agent-scoped webhook lookup |
idx_webhooks_active | (is_active) WHERE is_active = true | Partial B-tree | Active webhooks only |
UI Pages: Settings (API and Webhooks tab)
audit_logs
Purpose: Immutable, append-only audit trail for all auditable events. Write-once enforced at the application layer. Uses BIGSERIAL primary key to support very high row volumes.
| Column | Type | Nullable | Default | Description |
|---|---|---|---|---|
id | BIGINT (identity) | No | Auto | Primary key (BIGSERIAL -- very high volume) |
organization_id | int | No | -- | Tenant isolation. References organizations.id |
workspace_id | int | No | -- | Workspace scope |
agent_id | int | Yes | -- | Related agent (NULL for workspace-level events) |
execution_id | int | Yes | -- | Related execution (NULL for non-execution events) |
event_type | audit_event_type | No | -- | Categorized event (33 values) |
actor_user_id | int | Yes | -- | User who performed the action. References users.id |
actor_type | varchar(50) | No | -- | "user", "agent", "system", "scheduler" |
resource_type | varchar(100) | No | -- | Type of resource affected (e.g., "agent", "execution", "policy") |
resource_id | int | Yes | -- | ID of the affected resource |
previous_state | jsonb | Yes | -- | Resource state before the action (NULL if creation event) |
new_state | jsonb | Yes | -- | Resource state after the action (NULL if deletion event) |
details | jsonb | No | '{}' | Event-specific supplementary data |
ip_address | varchar(45) | Yes | -- | Source IP address (IPv4 or IPv6) |
user_agent | text | Yes | -- | User-Agent header from the request |
created_at | timestamptz | No | NOW() | When the event occurred |
Indexes:
| Index | Columns | Type | Rationale |
|---|---|---|---|
idx_audit_logs_org_workspace | (organization_id, workspace_id) | B-tree | Tenant-isolated audit queries |
idx_audit_logs_agent | (agent_id, created_at) | B-tree | Agent-scoped audit history |
idx_audit_logs_event_type | (event_type) | B-tree | Filter by event category |
idx_audit_logs_org_event_time | (organization_id, event_type, created_at) | B-tree | Compliance export queries |
idx_audit_logs_execution | (execution_id) WHERE execution_id IS NOT NULL | Partial B-tree | Execution-scoped audit trail |
UI Pages: Audit Log page (admin), Compliance exports
agent_memory (supporting table)
Purpose: Persistent key-value memory entries scoped to an agent and workspace. The river-agent microservice reads all non-expired memory entries during context assembly. Memory writes are applied non-blocking after execution completes -- a failed memory write does not fail the run.
| Column | Type | Nullable | Default | Description |
|---|---|---|---|---|
id | BIGINT (identity) | No | Auto | Primary key |
agent_id | int | No | -- | FK to agents.id ON DELETE CASCADE |
organization_id | int | No | -- | Tenant org |
workspace_id | int | No | -- | Tenant workspace |
memory_key | varchar(255) | No | -- | Logical key for retrieval (e.g., "customer_tier") |
content | jsonb | No | -- | Memory entry payload. See JSONB spec. |
source_execution_id | int | Yes | -- | Execution that last wrote this entry. FK ON DELETE SET NULL |
expires_at | timestamptz | Yes | -- | NULL means the entry never expires |
created_at | timestamptz | No | NOW() | When the entry was first written |
updated_at | timestamptz | No | NOW() | When the entry was last overwritten |
deleted_at | timestamptz | Yes | -- | Soft delete; NULL means active |
Unique Constraint: (agent_id, memory_key, deleted_at) -- one active entry per key per agent. NULL deleted_at values are not treated as equal in PostgreSQL unique indexes, allowing key reuse after soft-delete.
Indexes: idx_memory_agent_active ON (agent_id, deleted_at) WHERE deleted_at IS NULL
agent_notifications (supporting table)
Purpose: Notification delivery records. One row per dispatch attempt. Failed notifications are retried up to 3 times.
| Column | Type | Nullable | Default | Description |
|---|---|---|---|---|
id | BIGINT (identity) | No | Auto | Primary key |
organization_id | int | No | -- | Tenant org |
workspace_id | int | No | -- | Tenant workspace |
agent_id | int | Yes | -- | FK to agents.id; NULL for system-level notifications |
execution_id | int | Yes | -- | FK to executions.id |
approval_request_id | int | Yes | -- | FK to approval_requests.id |
channel | varchar(50) | No | -- | email, webhook, slack, in_app |
recipient_id | int | Yes | -- | users.id of the intended recipient; NULL for webhook dispatch |
payload | jsonb | No | '{}' | Channel-specific notification content. See JSONB spec. |
attempt_count | smallint | No | 0 | Number of delivery attempts made |
sent_at | timestamptz | Yes | -- | When delivery succeeded |
failed_at | timestamptz | Yes | -- | When the final delivery attempt failed |
error_message | text | Yes | -- | Failure detail from the delivery provider |
created_at | timestamptz | No | NOW() | When the notification was enqueued |
JSONB Column Specifications
Field-level documentation for every JSONB column across all tables. These specifications are the source of truth for Pydantic model validation in the Backend service.
agent_triggers.config
Configuration specific to each trigger_type. The trigger_type column determines which variant applies.
Variant: manual
{
"input_schema": {
"type": "object",
"properties": {
"customer_id": { "type": "integer" },
"issue_summary": { "type": "string" }
},
"required": ["customer_id"]
}
}
| Field | Type | Required | Description |
|---|---|---|---|
input_schema | object | No | JSON Schema describing the expected input payload; empty object if no input required |
Variant: scheduled
{
"cron": "0 9 * * 1-5",
"timezone": "America/New_York",
"input_template": {
"report_type": "daily_reconciliation"
}
}
| Field | Type | Required | Description |
|---|---|---|---|
cron | string | Yes | Standard cron expression (5 fields: minute hour day month weekday) |
timezone | string | Yes | IANA timezone identifier (e.g., "America/New_York") |
input_template | object | No | Static input payload merged into each triggered run |
Variant: event
{
"event_source": "webhook",
"event_type": "order.created",
"filter_expression": "$.order.amount > 1000",
"input_mapping": {
"order_id": "$.order.id",
"customer_id": "$.order.customer_id",
"amount": "$.order.amount"
}
}
| Field | Type | Required | Description |
|---|---|---|---|
event_source | string | Yes | One of: webhook, database, pubsub |
event_type | string | Yes | Specific event type to match (e.g., "order.created") |
filter_expression | string | No | JSONPath or CEL expression evaluated against the event payload; run skipped if false |
input_mapping | object | No | Key-to-JSONPath mapping that extracts fields from the event payload into agent input |
Variant: threshold
{
"metric": "error_rate",
"operator": "gt",
"value": 0.05,
"window_seconds": 300,
"cooldown_seconds": 1800
}
| Field | Type | Required | Description |
|---|---|---|---|
metric | string | Yes | Metric identifier (e.g., "error_rate", "queue_depth") |
operator | string | Yes | Comparison operator: gt, lt, gte, lte, eq |
value | number | Yes | Threshold value to compare against |
window_seconds | integer | Yes | Rolling window in seconds over which the metric is evaluated |
cooldown_seconds | integer | Yes | Minimum seconds between consecutive triggers |
Variant: api
{
"auth_required": true,
"input_schema": {
"type": "object",
"properties": { "lead_id": { "type": "integer" } },
"required": ["lead_id"]
},
"allowed_callers": ["crm-service", "sales-dashboard"]
}
| Field | Type | Required | Description |
|---|---|---|---|
auth_required | boolean | Yes | Whether the caller must present a valid API key |
input_schema | object | No | JSON Schema for the expected request body |
allowed_callers | array | No | Allowlist of caller identifiers; empty = any authenticated caller |
Variant: workflow
{
"source_agent_id": 42,
"output_key": "$.report_url",
"input_mapping": {
"report_url": "$.report_url",
"summary": "$.executive_summary"
},
"fire_on_status": ["success"]
}
| Field | Type | Required | Description |
|---|---|---|---|
source_agent_id | integer | Yes | agents.id of the upstream agent whose completion fires this trigger |
output_key | string | No | JSONPath into the upstream run's result; extracts a value as input |
input_mapping | object | No | Key-to-JSONPath mapping from upstream output to this agent's input |
fire_on_status | array | No | Upstream completion statuses that fire this trigger (default: ["success"]) |
agent_versions.config_snapshot
Full agent brain configuration at the time this version was created. Immutable after insert.
| Field | Type | Required | Description |
|---|---|---|---|
goal | string | Yes | Natural language goal statement |
instructions | string | Yes | Detailed behavioral instructions |
action_level | string | Yes | Snapshot of action_level at this version |
model_config | object | Yes | Multi-LLM routing preferences (see agents.model_config spec) |
tools | array | Yes | List of tool names enabled for this version |
data_source_ids | array | Yes | List of bound data source IDs |
governance_policy_ids | array | Yes | List of bound governance policy IDs |
approval_rules | object | Yes | Approval gate configuration snapshot |
notification_config | object | Yes | Notification channel snapshot |
{
"goal": "Resolve Tier 1 support tickets automatically.",
"instructions": "Check ticket priority first. For billing disputes, verify the charge before responding.",
"action_level": "act_with_approval",
"model_config": { "default_tier": "balanced", "max_turns": 15, "token_budget": 100000 },
"tools": ["classify_intent", "execute_query", "write_back", "ask_user"],
"data_source_ids": [14, 22],
"governance_policy_ids": [1, 4],
"approval_rules": {
"require_approval_for": ["write_back"],
"approver_roles": ["admin", "editor"],
"escalation_timeout_minutes": 1440
},
"notification_config": { "channels": ["email", "in_app"], "notify_on_failure": true }
}
agents.model_config
Multi-LLM routing preferences and execution budget configuration.
| Field | Type | Required | Description |
|---|---|---|---|
default_tier | string | No | LLM tier for standard turns: fast, balanced, reasoning, coding. Default: balanced |
reasoning_tier | string | No | Override tier for turns that the agent explicitly routes to reasoning |
provider_preference | string | No | anthropic, openai, gemini, deepseek, or any. Default: any |
temperature | number | No | Sampling temperature 0.0-1.0. Default: 0.7 |
max_tokens | integer | No | Maximum tokens per LLM response. Default: 4096 |
max_turns | integer | No | Turn limit for each execution. Default: 15 |
token_budget | integer | No | Maximum total tokens per execution. Default: 100000 |
timeout_seconds | integer | No | Per-turn LLM inference timeout in seconds. Default: 30 |
{
"default_tier": "balanced",
"reasoning_tier": "reasoning",
"provider_preference": "anthropic",
"temperature": 0.5,
"max_tokens": 8192,
"max_turns": 15,
"token_budget": 100000,
"timeout_seconds": 30
}
executions.tokens_used
Per-model token usage breakdown for the full execution.
| Field | Type | Required | Description |
|---|---|---|---|
total_input | integer | Yes | Total input tokens across all turns |
total_output | integer | Yes | Total output tokens across all turns |
total | integer | Yes | Sum of input and output |
by_model | object | No | Map of model ID to {input, output, turns, cost_usd} |
{
"total_input": 12840,
"total_output": 1420,
"total": 14260,
"by_model": {
"claude-sonnet-4-6": {
"input": 10200,
"output": 1200,
"turns": 8,
"cost_usd": 0.0482
},
"deepseek-v3": {
"input": 2640,
"output": 220,
"turns": 2,
"cost_usd": 0.0012
}
}
}
executions.result_summary
Structured result of a completed execution.
| Field | Type | Required | Description |
|---|---|---|---|
status | string | Yes | Final execution status |
summary | string | No | Human-readable summary of what the agent accomplished |
actions_taken | array | No | Write actions performed during the run |
output_artifacts | array | No | Generated reports, query results, or exported data references |
recommendations | array | No | Suggested follow-up actions |
turn_count | integer | No | Number of reasoning turns executed |
duration_ms | integer | No | Total wall-clock duration in milliseconds |
{
"status": "success",
"summary": "Processed 14 high-priority Zendesk tickets. Drafted responses for 12. Escalated 2 to Tier 2.",
"actions_taken": [
{"tool": "write_back", "table": "tickets", "operation": "update", "rows_affected": 12}
],
"output_artifacts": [],
"recommendations": ["Review escalated tickets ZEND-8821 and ZEND-8834 before EOD."],
"turn_count": 11,
"duration_ms": 24180
}
executions.context_snapshot
Serialized agent context captured at the moment an approval gate is triggered. Used to resume execution after approval resolution. NULL for runs that never hit an approval gate.
| Field | Type | Required | Description |
|---|---|---|---|
turn_number | integer | Yes | Turn number at which the gate was triggered |
conversation_history | array | Yes | Full conversation history up to and including the turn that requested approval |
memory_state | object | Yes | Agent memory as of the snapshot moment |
pending_tool_call | object | Yes | The tool call awaiting approval: {"tool_name": "...", "inputs": {...}} |
governance_token | string | Yes | The governance token active at snapshot time; re-validated on resume |
snapshot_taken_at | string | Yes | ISO 8601 timestamp of when the snapshot was written |
{
"turn_number": 4,
"conversation_history": [
{"role": "user", "content": "Process the billing dispute for customer #4821."},
{"role": "assistant", "content": "I have identified a $142.50 discrepancy on invoice #1094."}
],
"memory_state": {
"customer_tier": "gold",
"refund_policy": "30-day window"
},
"pending_tool_call": {
"tool_name": "write_back",
"inputs": {
"table": "refunds",
"record": {"customer_id": 4821, "amount": 142.50, "invoice_id": 1094}
}
},
"governance_token": "gt_eyJhbGc...",
"snapshot_taken_at": "2026-05-10T08:14:22Z"
}
execution_steps.input_data and execution_steps.output_data
The schema of input_data and output_data varies by step_type.
step_type: reasoning
input_data:
{
"model_used": "claude-sonnet-4-6",
"model_tier": "balanced",
"provider": "anthropic",
"input_tokens": 2140,
"turn_number": 3
}
output_data:
{
"thinking": "The customer is VIP with no prior refunds. I should verify the charge amount in Stripe before proceeding.",
"plan": "1. Query Stripe for charge details. 2. Compare with invoice amount. 3. Assess refund eligibility.",
"output_tokens": 187,
"latency_ms": 1240
}
step_type: tool_call
input_data:
{
"tool_name": "execute_query",
"tool_category": "execution",
"arguments": {
"data_source_id": 14,
"query": "SELECT * FROM invoices WHERE id = 1094",
"max_rows": 100
},
"dispatch_id": "disp_7f3a91"
}
output_data:
{
"tool_name": "execute_query",
"dispatch_id": "disp_7f3a91",
"result": {
"columns": ["id", "amount", "status", "payment_date"],
"rows": [[1094, 284.50, "paid", "2026-04-12"]]
},
"duration_ms": 42,
"status": "success"
}
step_type: observation
output_data:
{
"summary": "Invoice #1094 shows a billed amount of $284.50 but the payment record shows $142.00. A discrepancy of $142.50 exists.",
"key_findings": [
"Invoice amount: $284.50",
"Recorded payment: $142.00",
"Discrepancy: $142.50"
]
}
step_type: approval_gate
input_data:
{
"approval_request_id": 884,
"action_description": "Issue refund of $142.50 to customer #4821 for invoice #1094",
"proposed_tool": "write_back",
"proposed_inputs": {"table": "refunds", "record": {"customer_id": 4821, "amount": 142.50}},
"expires_at": "2026-05-11T08:14:22Z"
}
output_data (after resolution):
{
"approval_request_id": 884,
"resolution": "approved",
"resolved_by": 102,
"modified_args": null,
"reviewer_note": "Approved per customer billing policy."
}
step_type: error_recovery
output_data:
{
"error_type": "tool_timeout",
"message": "Tool execute_query did not respond within 30 seconds",
"retry_count": 2,
"recoverable": true
}
approval_requests.action_payload
The complete description of the action awaiting approval, stored at gate creation time. If the approver modifies arguments, the modified values are stored separately in the approval resolution response.
| Field | Type | Required | Description |
|---|---|---|---|
tool_name | string | Yes | The tool that will execute upon approval |
inputs | object | Yes | The proposed input arguments |
description | string | Yes | Human-readable explanation of what this action will do |
estimated_impact | string | No | Agent's impact assessment |
supporting_evidence | array | No | Key findings from the run that justify this action |
confidence_score | float | No | Agent's self-assessed confidence (0.0-1.0) |
risk_context | string | No | Policy evaluation results and warnings |
{
"tool_name": "write_back",
"inputs": {
"table": "refunds",
"record": {"customer_id": 4821, "amount": 142.50, "invoice_id": 1094}
},
"description": "Issue a $142.50 refund to customer #4821 for billing error on invoice #1094",
"estimated_impact": "Decreases accounts receivable by $142.50; creates a refund record in the billing system.",
"supporting_evidence": [
"Invoice #1094 billed $284.50; payment system recorded $142.00",
"Customer is Gold tier; within 30-day refund window"
],
"confidence_score": 0.94,
"risk_context": "Write operation on refunds table. Within standard refund policy thresholds."
}
governance_policies.policy_config
Type-specific policy configuration. The policy_type column determines which variant applies.
policy_type: budget_limit
{
"monthly_limit_usd": 500.00,
"per_run_limit_usd": 5.00,
"alert_threshold_percent": 80,
"enforcement": "block"
}
policy_type: rate_limit
{
"max_runs_per_hour": 10,
"max_runs_per_day": 50,
"cooldown_seconds": 300,
"enforcement": "block"
}
policy_type: data_access_restriction
{
"denied_tables": ["users", "payment_methods"],
"max_rows_per_query": 10000,
"denied_operations": ["DELETE", "DROP"],
"enforcement": "block"
}
policy_type: action_restriction
{
"denied_tools": ["delete_data_source"],
"tools_requiring_confirmation": ["write_back", "apply_governance_policy"],
"enforcement": "require_approval"
}
policy_type: time_window
{
"allowed_hours_utc": {"start": 6, "end": 22},
"allowed_days": ["monday", "tuesday", "wednesday", "thursday", "friday"],
"enforcement": "block"
}
policy_type: token_limit
{
"max_tokens_per_run": 50000,
"max_tokens_per_day": 500000,
"enforcement": "block"
}
policy_type: approval_threshold
{
"auto_approve_below_cost_usd": 10.00,
"require_approval_above_rows": 5000,
"approver_roles": ["admin", "compliance_officer"],
"enforcement": "require_approval"
}
policy_type: output_validation
{
"max_output_length": 50000,
"prohibited_patterns": ["SSN:\\d{3}-\\d{2}-\\d{4}", "\\d{4}[- ]?\\d{4}[- ]?\\d{4}[- ]?\\d{4}"],
"require_structured_output": false,
"enforcement": "block"
}
agent_metrics.tool_usage
Per-tool invocation breakdown for the metric date.
{
"execute_query": {"calls": 142, "errors": 3, "avg_duration_ms": 88},
"write_back": {"calls": 18, "errors": 0, "avg_duration_ms": 145},
"classify_intent": {"calls": 62, "errors": 0, "avg_duration_ms": 480},
"ask_user": {"calls": 4, "errors": 0, "avg_duration_ms": 0}
}
api_keys.permissions
Permissions this API key grants. Controls which operations the key is authorized to perform.
{
"can_trigger": true,
"allowed_trigger_types": ["api"],
"max_executions_per_hour": 30,
"allowed_agent_ids": [14, 22],
"can_view_results": true
}
| Field | Type | Required | Description |
|---|---|---|---|
can_trigger | boolean | Yes | Whether the key can trigger agent executions |
allowed_trigger_types | array | No | Trigger types this key can invoke (default: ["api"]) |
max_executions_per_hour | integer | No | Rate limit override for this specific key |
allowed_agent_ids | array | No | Restrict to specific agents (empty = all agents in scope) |
can_view_results | boolean | No | Whether the key can read execution results |
webhooks.events
Array of subscribed webhook_event values. The webhook fires when any listed event occurs.
["execution.completed", "execution.failed", "approval.requested"]
webhooks.headers
Custom HTTP headers included in every webhook delivery.
{
"X-RiverGen-Source": "production",
"X-Custom-Auth": "Bearer secret-token-value"
}
agent_memory.content
The memory entry payload. Retrieved by key and injected into the context bundle on each turn.
| Field | Type | Required | Description |
|---|---|---|---|
type | string | Yes | entity, fact, preference, or context |
value | any | Yes | The stored value (string, number, object, or array) |
confidence | number | No | Agent-assigned confidence score 0.0-1.0 |
notes | string | No | Agent's rationale for storing this entry |
{
"type": "entity",
"value": {
"segment": "enterprise",
"tier": "gold",
"primary_contact": "alice@example.com"
},
"confidence": 0.95,
"notes": "Retrieved from CRM during run 4481; confirmed by customer in ticket."
}
agent_notifications.payload
Channel-specific notification content. The schema varies by channel.
channel: email
{
"to": "alice@example.com",
"subject": "Approval Required: Customer Refund Action",
"body_text": "Agent 'L1 Support Specialist' is requesting approval to issue a $142.50 refund...",
"action_url": "https://app.rivergen.com/agents/runs/9871/approvals/884"
}
channel: webhook
{
"url": "https://hooks.example.com/rivergen-events",
"method": "POST",
"headers": {"X-RiverGen-Event": "approval_requested"},
"body": {
"approval_id": 884,
"agent_name": "L1 Support Specialist",
"action": "write_back",
"amount_usd": 142.50
}
}
channel: slack
{
"workspace_id": "T0123456",
"channel_id": "C0987654",
"message": "Approval needed for L1 Support Specialist: Issue $142.50 refund to customer #4821."
}
channel: in_app
{
"title": "Approval Required",
"body": "L1 Support Specialist is requesting approval to issue a refund of $142.50",
"link": "/agents/runs/9871/approvals/884",
"priority": "high"
}
Cross-Schema References
All cross-schema references are enforced at the application layer only. No database-level FOREIGN KEY ... REFERENCES constraints cross schema boundaries.
| river_agents Column | References | Foreign Schema | Enforcement |
|---|---|---|---|
agents.organization_id | organizations.id | IAM | Application layer; TLO Gateway validates org membership on every request |
agents.workspace_id | workspaces.id | IAM | Application layer; verified at workspace settings lookup |
agents.created_by | users.id | IAM | Application layer |
agents.owner_user_id | users.id | IAM | Application layer |
agent_versions.created_by | users.id | IAM | Application layer |
agent_data_sources.data_source_id | data_sources.id | Platform | Application layer; verified during async validation at deploy time |
executions.organization_id | organizations.id | IAM | Application layer |
executions.triggered_by_user_id | users.id | IAM | Application layer |
approval_requests.responded_by_user_id | users.id | IAM | Application layer |
audit_logs.actor_user_id | users.id | IAM | Application layer |
Migrations in river_agents do not need to be ordered relative to IAM or Platform migrations because database-level FK constraints are absent. Logical consistency is maintained by the Backend service, which validates all cross-schema references before committing agent records.
Key Query Patterns
Primary query patterns that drive the indexing strategy. All examples use PostgreSQL syntax with river_agents. schema qualification.
List active agents for a workspace
SELECT
a.id,
a.name,
a.category,
a.action_level,
a.status,
av.version_number AS current_version
FROM river_agents.agents a
JOIN river_agents.agent_versions av ON av.id = a.current_version_id
WHERE a.organization_id = $1
AND a.workspace_id = $2
AND a.status IN ('active', 'deployed', 'running', 'awaiting_approval')
AND a.deleted_at IS NULL
ORDER BY a.updated_at DESC
LIMIT $3 OFFSET $4;
-- Index used: idx_agents_org_workspace, idx_agents_deleted_at (partial)
Get execution history for an agent
SELECT
e.id,
e.status,
e.started_at,
e.completed_at,
e.total_tokens,
e.turn_count,
e.cost_estimate,
EXTRACT(EPOCH FROM (e.completed_at - e.started_at)) AS duration_seconds
FROM river_agents.executions e
WHERE e.agent_id = $1
AND e.organization_id = $2
ORDER BY e.started_at DESC
LIMIT $3 OFFSET $4;
-- Index used: idx_executions_agent
Get pending approvals for a workspace
SELECT
ar.id,
ar.agent_id,
ar.execution_id,
ar.action_description,
ar.action_tool,
ar.action_payload,
ar.requested_at,
ar.expires_at,
a.name AS agent_name
FROM river_agents.approval_requests ar
JOIN river_agents.agents a ON a.id = ar.agent_id
WHERE ar.organization_id = $1
AND ar.workspace_id = $2
AND ar.status = 'pending'
AND (ar.expires_at IS NULL OR ar.expires_at > NOW())
ORDER BY ar.requested_at ASC;
-- Index used: idx_approvals_pending (partial index: status='pending')
Retrieve execution steps (log viewer)
SELECT
es.step_number,
es.step_type,
es.tool_name,
es.tool_category,
es.input_data,
es.output_data,
es.model_used,
es.tokens_input,
es.tokens_output,
es.duration_ms,
es.status,
es.governance_decision,
es.created_at
FROM river_agents.execution_steps es
WHERE es.execution_id = $1
ORDER BY es.step_number ASC;
-- Index used: idx_exec_steps_order (execution_id, step_number)
Audit log query with event filter and date range
SELECT
al.id,
al.event_type,
al.actor_user_id,
al.actor_type,
al.resource_type,
al.resource_id,
al.details,
al.created_at
FROM river_agents.audit_logs al
WHERE al.organization_id = $1
AND al.event_type = ANY($2::river_agents.audit_event_type[])
AND al.created_at >= $3
AND al.created_at < $4
ORDER BY al.created_at DESC
LIMIT $5 OFFSET $6;
-- Index used: idx_audit_logs_org_event_time
Agent metrics time-series for monitoring dashboard
SELECT
m.metric_date,
m.total_runs,
m.successful_runs,
m.failed_runs,
m.avg_duration_seconds,
m.total_tokens_used,
m.total_cost_usd
FROM river_agents.agent_metrics m
WHERE m.agent_id = $1
AND m.metric_date >= NOW() - INTERVAL '30 days'
ORDER BY m.metric_date ASC;
-- Index used: idx_agent_metrics_daily
Retrieve active memory for context assembly
Called once per execution at context assembly time. The result is passed to river-agent in the ExecutionRequest.
SELECT
am.memory_key,
am.content
FROM river_agents.agent_memory am
WHERE am.agent_id = $1
AND am.organization_id = $2
AND am.workspace_id = $3
AND am.deleted_at IS NULL
AND (am.expires_at IS NULL OR am.expires_at > NOW())
ORDER BY am.updated_at DESC;
-- Index used: idx_memory_agent_active (partial)
Upsert daily metrics (aggregation job)
INSERT INTO river_agents.agent_metrics (
agent_id, metric_date,
total_runs, successful_runs, failed_runs, cancelled_runs, timed_out_runs,
avg_duration_seconds, total_tokens_used, total_cost_usd
)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
ON CONFLICT (agent_id, metric_date)
DO UPDATE SET
total_runs = EXCLUDED.total_runs,
successful_runs = EXCLUDED.successful_runs,
failed_runs = EXCLUDED.failed_runs,
avg_duration_seconds = EXCLUDED.avg_duration_seconds,
total_tokens_used = EXCLUDED.total_tokens_used,
total_cost_usd = EXCLUDED.total_cost_usd;
Data Lifecycle Flows
Agent Creation and Deployment
Execution Run Data Flow
Approval Gate Resolution
Metrics Aggregation
Alembic Migration Strategy
Directory Structure
rgen-repo-backend/rgen-backend/
alembic/
env.py
script.py.mako
versions/
0001_baseline_river_agents_schema.py
0002_add_agent_memory_table.py
0003_add_execution_context_snapshot.py
0004_add_webhook_event_enum_value.py
...
Migration File Conventions
| Convention | Rule |
|---|---|
| Naming | {4-digit sequence}_{snake_case_description}.py |
| Revision chaining | Each migration declares down_revision pointing to its predecessor |
| Schema qualification | All DDL uses the full river_agents. prefix |
| Reversibility | Every migration provides a working downgrade() function; irreversible operations require a comment justifying the decision |
| Baseline migration | 0001 creates the schema, all enums, all tables, and all indexes in a single migration; subsequent changes are incremental |
Baseline Migration Structure
def upgrade() -> None:
# 1. Create schema
op.execute("CREATE SCHEMA IF NOT EXISTS river_agents")
# 2. Create all 14 enum types (must precede tables that reference them)
op.execute("""
CREATE TYPE river_agents.agent_status AS ENUM (
'draft', 'configured', 'validated', 'deployed',
'active', 'running', 'awaiting_approval', 'paused', 'archived'
)
""")
# ... remaining 13 enum types ...
# 3. Create all 15 tables in dependency order
op.execute("""
CREATE TABLE river_agents.agents (
id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
organization_id INTEGER NOT NULL,
workspace_id INTEGER NOT NULL,
...
)
""")
# ... remaining tables in FK dependency order ...
# 4. Create all indexes
op.execute("""
CREATE INDEX idx_agents_org_workspace
ON river_agents.agents (organization_id, workspace_id)
""")
op.execute("""
CREATE INDEX idx_agents_deleted_at
ON river_agents.agents (deleted_at)
WHERE deleted_at IS NULL
""")
# ... remaining indexes ...
def downgrade() -> None:
# Drop in reverse dependency order
op.execute("DROP TABLE IF EXISTS river_agents.audit_logs CASCADE")
op.execute("DROP TABLE IF EXISTS river_agents.agent_notifications CASCADE")
op.execute("DROP TABLE IF EXISTS river_agents.agent_memory CASCADE")
# ... remaining tables ...
op.execute("DROP SCHEMA IF EXISTS river_agents CASCADE")
Adding a New Enum Value
PostgreSQL does not allow ALTER TYPE ... ADD VALUE inside a transaction block. Migrations that add enum values must commit before any table column using the new value can be altered.
# transactional_ddl must be False at module level for this migration
transactional_ddl = False
def upgrade() -> None:
op.execute(
"ALTER TYPE river_agents.step_type ADD VALUE IF NOT EXISTS 'memory_write'"
)
# Commit is implicit before any subsequent DDL referencing this value
def downgrade() -> None:
# Enum value removal is not supported in PostgreSQL without recreating the type.
# This downgrade is intentionally a no-op; document in the migration comment.
pass
Set transactional_ddl = False at the module level in the migration file. The env.py must be configured to honor this flag.
Incremental Migration Example
"""Add context_snapshot column to executions
Revision ID: 0003
Revises: 0002
"""
revision = "0003"
down_revision = "0002"
def upgrade() -> None:
op.execute("""
ALTER TABLE river_agents.executions
ADD COLUMN context_snapshot JSONB
""")
def downgrade() -> None:
op.execute("""
ALTER TABLE river_agents.executions
DROP COLUMN IF EXISTS context_snapshot
""")
Seed Data
The seed data migration (0010_insert_seed_data.py) populates built-in agent templates and default governance policies. All seed records use fixed IDs in the range id < 100. User-created records start at sequence values above the seed range via ALTER SEQUENCE ... RESTART WITH 100.
Built-in Agent Templates
One template per agent_category. All templates have is_system = true and organization_id = NULL (global visibility).
| Template ID | Name | Category | Default Action Level |
|---|---|---|---|
| 1 | Customer Support Agent | customer_support | recommend |
| 2 | Sales Lead Qualifier | sales_lead_qualification | recommend |
| 3 | Finance Reconciliation Agent | finance_reconciliation | act_with_approval |
| 4 | Risk and Compliance Monitor | risk_compliance | act_with_approval |
| 5 | Data Analyst Agent | data_analyst | read_only |
| 6 | Operations Monitoring Agent | operations_monitoring | act_with_approval |
| 7 | Executive Decision Support Agent | executive_decision | read_only |
| 8 | Custom Enterprise Agent | custom_enterprise | act_with_approval |
Default Governance Policies
Three system-level policies inserted as global defaults. Applied to all agents in all organizations. Organization admins can bind additional policies on top of these; system policies cannot be unbound.
| Policy ID | Name | Condition | Enforcement |
|---|---|---|---|
| 1 | PII Write Gate | Any write tool call where the target data source is classified as PII | require_approval -- requires compliance officer approval |
| 2 | Financial Write Gate | Any write action with input_amount_usd > 10000 | require_approval -- requires finance manager approval |
| 3 | Off-Hours Block | Any fully_automated write action between 22:00-06:00 UTC on weekdays, or any time on weekends | block -- no automated writes during off-hours |