Skip to content

Database Queries

adham90 edited this page Feb 20, 2026 · 3 revisions

Database Queries

Comprehensive guide to querying the RubyLLM::Agents::Execution model for analytics, debugging, and reporting.

Execution Model

All agent executions are stored in the ruby_llm_agents_executions table:

RubyLLM::Agents::Execution

Schema Overview

In v2.0, execution data is split across two tables for performance. The lean executions table is optimized for analytics queries, while large payloads live in execution_details.

Executions Table (ruby_llm_agents_executions)

Column Type Description
agent_type string Agent class name (e.g., "SearchAgent")
execution_type string Type of execution (chat, embed, etc.)
model_id string Configured LLM model
chosen_model_id string Actual model used (for fallbacks)
model_provider string Provider name
temperature decimal Temperature setting
status string running, success, error, timeout
started_at datetime Execution start time
completed_at datetime Execution end time
duration_ms integer Duration in milliseconds
input_tokens integer Input token count
output_tokens integer Output token count
total_tokens integer Total tokens
cached_tokens integer Cached tokens count
input_cost decimal Cost of input tokens (USD)
output_cost decimal Cost of output tokens (USD)
total_cost decimal Total cost (USD)
metadata json Custom metadata (includes TTFT, rate_limited, etc.)
error_class string Exception class if failed
streaming boolean Whether streaming was used
cache_hit boolean Whether response was from cache
finish_reason string stop, length, content_filter, tool_calls
tool_calls_count integer Number of tool calls
attempts_count integer Number of attempts
messages_count integer Number of messages in conversation
tenant_id string Multi-tenant identifier
trace_id string Distributed trace ID
request_id string Request ID
parent_execution_id bigint Parent execution (nested calls)
root_execution_id bigint Root execution (nested calls)

Execution Details Table (ruby_llm_agents_execution_details)

Large payloads are stored separately for query performance:

Column Type Description
system_prompt text System prompt used
user_prompt text User prompt used
response json LLM response data
error_message text Error details (if failed)
parameters json Input parameters (sanitized)
tool_calls json Array of tool invocations
attempts json Array of all attempt details
fallback_chain json Models attempted in order
messages_summary json Conversation messages summary
routed_to string Routing destination
classification_result json Classification output
cached_at datetime When cached
cache_creation_tokens integer Tokens used for cache creation

Note: Detail fields are transparently accessible on Execution instances via delegation. For example, execution.error_message works even though the data is stored in execution_details.

Metadata JSON Fields

These fields are stored in the metadata JSON column with getter/setter methods:

Field Description
time_to_first_token_ms TTFT (streaming only)
rate_limited Whether rate limit was hit
retryable Whether error was retryable
fallback_reason Why fallback was triggered
span_id Span ID for tracing
response_cache_key Cache key used

Query Scopes

All scopes are chainable.

Time-Based Scopes

Execution.today Execution.yesterday Execution.this_week Execution.this_month Execution.last_n_days(7) Execution.recent(100) # Most recent N records Execution.oldest(100) # Oldest N records Execution.between(start_date, end_date)

Status-Based Scopes

Execution.running # In progress Execution.successful # Completed successfully Execution.failed # Error or timeout Execution.errors # Error status only Execution.timeouts # Timeout status only Execution.completed # Not running

Agent/Model Filtering

Execution.by_agent("SearchAgent") # Also includes aliased names Execution.by_agent(SearchAgent) # Pass the class directly Execution.by_model("gpt-4o")

Note: by_agent is alias-aware. If SearchAgent declares aliases "OldSearchAgent", the scope automatically includes executions from both names. See Agent DSL - aliases.

Performance Filtering

Execution.expensive(1.00) # Cost >= $1.00 Execution.slow(5000) # Duration >= 5 seconds Execution.high_token(10000) # Tokens >= 10k

Caching Scopes

Execution.cached # Cache hits Execution.cache_miss # Cache misses

Streaming Scopes

Execution.streaming # Used streaming Execution.non_streaming # Did not use streaming

Tool Call Scopes

Execution.with_tool_calls # Made tool calls Execution.without_tool_calls # No tool calls

Reliability Scopes

Execution.with_fallback # Used fallback model Execution.rate_limited # Was rate limited Execution.retryable_errors # Has retryable errors

Finish Reason Scopes

Execution.truncated # Hit max_tokens Execution.content_filtered # Blocked by safety Execution.by_finish_reason("stop") Execution.by_finish_reason("tool_calls")

Tracing Scopes

Execution.by_trace("trace-123") Execution.by_request("request-456") Execution.root_executions # Top-level only Execution.child_executions # Nested only Execution.children_of(execution_id)

Multi-Tenancy Scopes

Execution.by_tenant("tenant_123") Execution.for_current_tenant # Uses configured resolver Execution.with_tenant # Has tenant_id Execution.without_tenant # No tenant_id

Parameter Filtering (JSONB)

Execution.with_parameter(:query) Execution.with_parameter(:user_id, 123)

Search

Execution.search("error text")

Instance Methods

execution = RubyLLM::Agents::Execution.last # Status checks execution.cached? # Was this a cache hit? execution.streaming? # Was streaming used? execution.truncated? # Did it hit max_tokens? execution.content_filtered? # Was it blocked by safety? execution.has_tool_calls? # Were tools called? execution.used_fallback? # Did it use fallback model? execution.has_retries? # Were there multiple attempts? execution.rate_limited? # Was it rate limited? # Hierarchy (nested executions) execution.root? # Is this a root execution? execution.child? # Is this a child execution? execution.depth # Nesting level (0 = root) # Attempt analysis execution.successful_attempt # The successful attempt data execution.failed_attempts # Array of failed attempts execution.short_circuited_attempts # Circuit breaker blocked

Aggregation Methods

scope = RubyLLM::Agents::Execution.by_agent("SearchAgent").this_week scope.total_cost_sum # Sum of total_cost scope.total_tokens_sum # Sum of total_tokens scope.avg_duration # Average duration_ms scope.avg_tokens # Average total_tokens

Analytics Methods

Daily Report

RubyLLM::Agents::Execution.daily_report # => { # date: Date.current, # total_executions: 156, # successful: 150, # failed: 6, # total_cost: 12.50, # total_tokens: 500000, # avg_duration_ms: 1200, # error_rate: 3.85, # by_agent: { "SearchAgent" => 100, "ChatAgent" => 56 }, # top_errors: { "RateLimitError" => 4, "TimeoutError" => 2 } # }

Cost Breakdown

RubyLLM::Agents::Execution.cost_by_agent(period: :this_week) # => { "ContentAgent" => 45.50, "SearchAgent" => 12.30 }

Agent Statistics

RubyLLM::Agents::Execution.stats_for("SearchAgent", period: :today) # => { # agent_type: "SearchAgent", # count: 100, # total_cost: 5.25, # avg_cost: 0.0525, # total_tokens: 150000, # avg_tokens: 1500, # avg_duration_ms: 800, # success_rate: 98.0, # error_rate: 2.0 # }

Trend Analysis

RubyLLM::Agents::Execution.trend_analysis(agent_type: "SearchAgent", days: 7) # => [ # { date: 7.days.ago.to_date, count: 100, total_cost: 5.0, avg_duration_ms: 850, error_count: 2 }, # { date: 6.days.ago.to_date, count: 120, ... }, # ... # ]

Dashboard Data

# Real-time metrics RubyLLM::Agents::Execution.now_strip_data(range: "today") # => { # running: 2, # success_today: 150, # errors_today: 3, # timeouts_today: 1, # cost_today: 12.50, # executions_today: 156, # success_rate: 96.2 # } # Ranges: "today", "7d", "30d" RubyLLM::Agents::Execution.now_strip_data(range: "7d")

Chart Data

RubyLLM::Agents::Execution.activity_chart_json(range: "today") # Hourly RubyLLM::Agents::Execution.activity_chart_json(range: "7d") # Daily RubyLLM::Agents::Execution.activity_chart_json(range: "30d") # Daily

Performance Metrics

RubyLLM::Agents::Execution.today.cache_hit_rate # => 45.2 RubyLLM::Agents::Execution.today.streaming_rate # => 12.5 RubyLLM::Agents::Execution.today.avg_time_to_first_token # => 150 (ms) RubyLLM::Agents::Execution.today.rate_limited_rate # => 0.5

Finish Reason Distribution

RubyLLM::Agents::Execution.today.finish_reason_distribution # => { "stop" => 145, "tool_calls" => 8, "length" => 3 }

Common Query Examples

Recent Executions for an Agent

RubyLLM::Agents::Execution.by_agent("SearchAgent").recent(10)

Failed Executions Today

RubyLLM::Agents::Execution.today.failed

Expensive Executions This Week

RubyLLM::Agents::Execution.this_week.expensive(0.50)

Slow Streaming Executions

RubyLLM::Agents::Execution.streaming.slow(5000)

Cache Hit Rate

hits = RubyLLM::Agents::Execution.today.cached.count total = RubyLLM::Agents::Execution.today.count rate = total > 0 ? (hits.to_f / total * 100).round(1) : 0

Total Cost This Month

RubyLLM::Agents::Execution.this_month.sum(:total_cost)

Average Duration by Agent

RubyLLM::Agents::Execution.group(:agent_type).average(:duration_ms)

Token Usage by Model

RubyLLM::Agents::Execution.group(:model_id).sum(:total_tokens)

Executions with Fallbacks

RubyLLM::Agents::Execution.with_fallback .select(:agent_type, :model_id, :chosen_model_id)

Tool Usage Statistics

RubyLLM::Agents::Execution.with_tool_calls.group(:agent_type).count

Nested Executions

RubyLLM::Agents::Execution.child_executions RubyLLM::Agents::Execution.root_executions RubyLLM::Agents::Execution.children_of(parent_execution_id)

Rails Console Examples

# Quick stats puts "Today: #{Execution.today.count} executions, $#{Execution.today.sum(:total_cost).round(2)}" puts "Errors: #{Execution.today.errors.count}" puts "Cache hits: #{Execution.today.cached.count}" # Find problematic executions (error_message is in execution_details) Execution.today.errors.includes(:detail).map { |e| [e.agent_type, e.error_class, e.error_message] } # Cost breakdown by agent Execution.this_month.group(:agent_type).sum(:total_cost).sort_by(&:last).reverse # Slowest executions Execution.today.order(duration_ms: :desc).limit(5).pluck(:agent_type, :duration_ms) # Recent execution details e = Execution.last puts "Agent: #{e.agent_type}" puts "Model: #{e.model_id} (chosen: #{e.chosen_model_id})" puts "Status: #{e.status}" puts "Duration: #{e.duration_ms}ms" puts "Tokens: #{e.total_tokens}" puts "Cost: $#{e.total_cost}" puts "Cache hit: #{e.cache_hit}" puts "Tool calls: #{e.tool_calls_count}"

Agent-Centric Queries

Instead of querying Execution directly, you can query from the agent class itself. Every agent class includes DSL::Queryable, which provides scoped queries and convenience methods.

Scoped Queries via .executions

# Returns ActiveRecord::Relation scoped to this agent SearchAgent.executions SearchAgent.executions.successful.today SearchAgent.executions.expensive(0.50) SearchAgent.executions.by_tenant("acme").this_week

Convenience Methods

# Most recent execution SearchAgent.last_run # Recent failures (default: last 24 hours) SearchAgent.failures SearchAgent.failures(since: 7.days) # Total cost SearchAgent.total_spent SearchAgent.total_spent(since: 1.month) # Stats summary SearchAgent.stats # => { total: 150, successful: 145, failed: 5, success_rate: 96.7, # avg_duration_ms: 850, total_cost: 1.80, total_tokens: 75000, ... } SearchAgent.stats(since: 24.hours) # Cost breakdown by model SearchAgent.cost_by_model # => { "gpt-4o" => { count: 100, total_cost: 5.00, avg_cost: 0.05 }, ... } # Filter by parameter values SearchAgent.with_params(user_id: "u123") SearchAgent.with_params(user_id: "u123", category: "billing")

Replay Executions

Re-execute a previous run with the same or overridden inputs:

run = SearchAgent.last_run # Replay with same settings new_run = run.replay # Replay with different model new_run = run.replay(model: "gpt-4o-mini") # Replay with parameter overrides new_run = run.replay(query: "updated search term") # Check if an execution can be replayed run.replayable? # => true # Check if this execution is itself a replay run.replay? # => false run.replay_source # => nil (not a replay) # Find all replays of a given execution run.replays # => ActiveRecord::Relation

See Querying Executions for full documentation.

Related Pages

Clone this wiki locally