> ## Documentation Index
> Fetch the complete documentation index at: https://raindrop.ai/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# RQL reference

> Supported RQL tables, fields, and functions.

This reference is generated from the query compiler schema. Use the [RQL guide](/docs/mcp/rql) for limits and examples.

## Tables and fields

### events

AI event records (user message + agent response), with signals, properties, tool calls, errors, token usage, and user context. Recent reprojections can temporarily expose multiple physical versions until background merges settle.

* `project_id` (string): Owning project id — selectable and filterable inside the server-injected read scope
* `event_id` (string): Internal Raindrop database event id — accepted by get\_event and used for joins and exact counts
* `timestamp` (datetime): Event time (UTC)
* `event_name` (string): Instrumented event name (e.g. chat\_message)
* `user_id` (string): End-user id
* `convo_id` (string): Conversation id
* `custom_event_id` (string): Caller-supplied public event id — shown as id by get\_event when present
* `ai_input` (string): User input text
* `ai_output` (string): AI response text
* `ai_model` (string): Model that produced the response
* `signals` (array(string)): Signal ids attached to this event — use has(signals, '...') or arrayJoin(signals)
* `properties` (map(string)): Custom event properties — access with properties.my\_key
* `user_traits` (map(string)): User traits at event time — access with user\_traits.plan
* `feature_flags` (map(string)): Feature flags at event time — access with feature\_flags.my\_flag
* `user_plan` (string): User plan at event time
* `user_created_at` (datetime): When the user was first seen
* `is_internal` (boolean): Marked as internal traffic
* `is_excluded_user` (boolean): User is on the exclusion list
* `input_words` (number): Word count of ai\_input
* `output_words` (number): Word count of ai\_output
* `tool_calls` (array(json)): Tool calls made during the event (JSON)
* `tool_names` (array(string)): Names of tools called during the event
* `tool_call_count` (number): Number of tool calls in the event
* `error_spans` (array(json)): Error spans recorded on the event (JSON)
* `error_names` (array(string)): Span names that errored during the event
* `error_count` (number): Number of error spans on the event
* `has_errors` (boolean): Event has at least one error span
* `token_usage` (array(json)): Per-span token usage (JSON)
* `input_tokens` (number): Total input tokens across the event's LLM calls
* `output_tokens` (number): Total output tokens across the event's LLM calls
* `total_tokens` (number): Total tokens (input + output) across the event's LLM calls

### traces

OTel span records — LLM calls, tool executions, and agent steps, joinable to events by event\_id. Recent reprojections can temporarily expose multiple physical versions until background merges settle.

* `project_id` (string): Owning project id — selectable and filterable inside the server-injected read scope
* `event_id` (string): Owning event id
* `trace_id` (string): OTel trace id
* `span_id` (string): OTel span id
* `parent_span_id` (string): Parent span id
* `span_type` (string): Span type: LLM\_GENERATION, LLM\_GENERATION\_STREAM, TOOL\_CALL, or INTERNAL — filter LLM calls with span\_type LIKE 'LLM%'
* `span_name` (string): Span name
* `status` (string): Span status (OK / ERROR)
* `timestamp` (datetime): Span start time (UTC)
* `end_timestamp` (datetime): Span end time (UTC)
* `duration_ms` (number): Span duration in milliseconds
* `input_payload` (string): Span input payload
* `output_payload` (string): Span output payload
* `input_tokens` (number): Input tokens (LLM spans)
* `output_tokens` (number): Output tokens (LLM spans)
* `provider` (string): LLM provider (e.g. openai.chat)
* `model` (string): LLM model id
* `user_id` (string): End-user id (from the owning event when the span doesn't carry one)
* `convo_id` (string): Conversation id (from the owning event when the span doesn't carry one)
* `attributes` (map(string)): String span attributes — access with attributes.my\_attr
* `attributes_num` (map(number)): Numeric span attributes — access with attributes\_num.my\_attr

### users

One row per project and end-user from the maintained user-activity rollup — activity, plan, traits, and signals.

* `project_id` (string): Owning project id
* `user_id` (string): End-user id
* `event_count` (number): Approximate distinct events from this user
* `first_seen` (datetime): First event time
* `last_seen` (datetime): Most recent event time
* `active_days` (number): Distinct days with activity
* `plan` (string): Latest known plan
* `traits` (map(string)): Latest user traits — access with traits.my\_trait
* `signals` (array(string)): All signal ids seen across the user's events

### conversations

One row per project and conversation from the maintained conversation rollup — anchor event, user, activity window, message count, and signals.

* `project_id` (string): Owning project id
* `convo_id` (string): Conversation id
* `event_id` (string): Most recent event id, used as the conversation's clickable anchor
* `user_id` (string): User id on the most recent event
* `start_ts` (datetime): First event time
* `end_ts` (datetime): Most recent event time
* `message_count` (number): Rollup event rows; use uniqExact(event\_id) on events for an exact distinct count
* `duration_seconds` (number): Seconds between the first and most recent event
* `signals` (array(string)): All materialized signal ids seen in the conversation

## Functions

* `count()` (aggregate): Number of rows
* `countIf(cond)` (aggregate): Rows matching a condition
* `countDistinct(x)` (aggregate): Exact distinct count (alias of uniqExact)
* `uniq(x)` (aggregate): Approximate distinct count
* `uniqExact(x)` (aggregate): Exact distinct count
* `uniqExactIf(x, cond)` (aggregate): Exact distinct count for rows matching a condition
* `sum(x)` (aggregate): Sum
* `sumIf(x, cond)` (aggregate): Conditional sum
* `avg(x)` (aggregate): Average
* `avgIf(x, cond)` (aggregate): Conditional average
* `min(x)` (aggregate): Minimum
* `minIf(x, cond)` (aggregate): Conditional minimum
* `max(x)` (aggregate): Maximum
* `maxIf(x, cond)` (aggregate): Conditional maximum
* `median(x)` (aggregate): Approximate median
* `quantile(0.95)(x)` (aggregate): Approximate quantile
* `quantiles(0.5, 0.9, 0.99)(x)` (aggregate): Multiple quantiles
* `any(x)` (aggregate): Any value in the group
* `argMin(x, order)` (aggregate): x at the minimum of order
* `argMax(x, order)` (aggregate): x at the maximum of order
* `groupArray(x)` (aggregate): Collect values into an array
* `groupUniqArray(x)` (aggregate): Collect distinct values into an array
* `topK(10)(x)` (aggregate): Approximate top-K most frequent values
* `toStartOfMinute(t)` (date): Truncate to minute
* `toStartOfHour(t)` (date): Truncate to hour
* `toStartOfDay(t)` (date): Truncate to day
* `toStartOfWeek(t)` (date): Truncate to week
* `toStartOfMonth(t)` (date): Truncate to month
* `toDate(t)` (date): Cast to date
* `toDateTime('2026-01-31 00:00:00', 'UTC')` (date): Cast a value to a whole-second UTC datetime; direct datetime-column string comparisons are converted automatically
* `toDateTime64('2026-01-31 00:00:00.123', 3, 'UTC')` (date): Cast a value to a UTC datetime with fractional-second precision
* `toHour(t)` (date): Hour of day (0-23)
* `toDayOfWeek(t)` (date): Day of week (1=Mon)
* `now()` (date): Current timestamp
* `today()` (date): Current date
* `yesterday()` (date): Yesterday's date
* `dateDiff('hour', a, b)` (date): Difference between two datetimes
* `dateAdd('day', 1, t)` (date): Add an interval — or just use t + INTERVAL 1 DAY
* `dateSub('day', 1, t)` (date): Subtract an interval — or just use t - INTERVAL 1 DAY
* `formatDateTime(t, '%Y-%m-%d')` (date): Format a datetime
* `toUnixTimestamp(t)` (date): Datetime to unix seconds
* `fromUnixTimestamp(n)` (date): Unix seconds to datetime
* `age('hour', a, b)` (date): Full units between datetimes
* `lower(s)` (string): Lowercase
* `upper(s)` (string): Uppercase
* `length(x)` (string): String or array length
* `substring(s, from, len)` (string): Substring (1-based)
* `substr(s, from, len)` (string): Alias of substring (1-based)
* `concat(a, b, ...)` (string): Concatenate strings
* `trim(s)` (string): Strip whitespace
* `splitByChar(',', s)` (string): Split into an array
* `replaceAll(s, from, to)` (string): Replace all occurrences
* `replaceRegexpAll(s, re, to)` (string): Regex replace
* `extract(s, re)` (string): First regex capture
* `match(s, re)` (string): Regex test
* `position(haystack, needle)` (string): 1-based substring position (0 = absent)
* `positionCaseInsensitive(haystack, needle)` (string): Case-insensitive position
* `startsWith(s, prefix)` (string): Prefix test
* `endsWith(s, suffix)` (string): Suffix test
* `empty(x)` (string): True when empty
* `notEmpty(x)` (string): True when not empty
* `has(arr, x)` (array): Array contains value
* `hasAny(arr, [a, b])` (array): Array contains any of the values
* `hasAll(arr, [a, b])` (array): Array contains all of the values
* `arrayJoin(arr)` (array): Explode an array into rows
* `arrayCount(x -> cond, arr)` (array): Count matching elements
* `arrayFilter(x -> cond, arr)` (array): Filter elements
* `arrayMap(x -> expr, arr)` (array): Transform elements
* `arrayExists(x -> cond, arr)` (array): Any element matches
* `arrayDistinct(arr)` (array): Distinct elements
* `arraySort(arr)` (array): Sort elements
* `arraySlice(arr, from, len)` (array): Slice an array
* `arrayElement(arr, n)` (array): Element at index (1-based)
* `arrayStringConcat(arr, sep)` (array): Join array to string
* `indexOf(arr, x)` (array): 1-based index of value (0 = absent)
* `JSONExtractString(json, 'key')` (json): Extract a string field
* `JSONExtractInt(json, 'key')` (json): Extract an integer field
* `JSONExtractFloat(json, 'key')` (json): Extract a float field
* `JSONExtractBool(json, 'key')` (json): Extract a boolean field
* `JSONExtractRaw(json, 'key')` (json): Extract raw JSON
* `JSONExtractArrayRaw(json)` (json): JSON array to array of raw JSON
* `JSONExtractKeys(json)` (json): Keys of a JSON object
* `JSONHas(json, 'key')` (json): JSON field exists
* `toJSONString(x)` (json): Serialize a value to JSON
* `mapKeys(m)` (map): Keys of a map
* `mapValues(m)` (map): Values of a map
* `mapContains(m, 'key')` (map): Map has key
* `if(cond, then, else)` (conditional): Conditional
* `multiIf(c1, r1, c2, r2, else)` (conditional): Multi-branch conditional
* `coalesce(a, b, ...)` (conditional): First non-null value
* `ifNull(x, alt)` (conditional): Fallback when null
* `nullIf(a, b)` (conditional): Null when equal
* `assumeNotNull(x)` (conditional): Treat as non-null
* `isNull(x)` (conditional): Is null
* `isNotNull(x)` (conditional): Is not null
* `round(x, n)` (math): Round
* `floor(x)` (math): Floor
* `ceil(x)` (math): Ceiling
* `abs(x)` (math): Absolute value
* `greatest(a, b, ...)` (math): Largest argument
* `least(a, b, ...)` (math): Smallest argument
* `exp(x)` (math): e^x
* `log(x)` (math): Natural log
* `log2(x)` (math): Base-2 log
* `sqrt(x)` (math): Square root
* `pow(x, y)` (math): x^y
* `intDiv(a, b)` (math): Integer division
* `toString(x)` (type): Cast to string
* `toInt64(x)` (type): Cast to integer
* `toFloat64(x)` (type): Cast to float
* `tuple(a, b)` (misc): Tuple constructor
* `bar(x, min, max, width)` (misc): Unicode bar chart of a value
* `formatReadableQuantity(n)` (misc): 1234567 -> 1.23 million
* `formatReadableTimeDelta(sec)` (misc): Seconds -> human duration