OQL query language

OQL is a typed, ACL-aware query language for tickets. It's the canonical way to express filter combinations that the per-entity list endpoints (/projects/:id/tickets?status=…&assignee=…) can't reach without growing yet another querystring parameter.

OQL also accepts JQL syntax through a thin compatibility adapter - paste a JQL query, get the same result.

At a glance

project = ACME AND assignee = currentUser() AND statusCategory != done
ORDER BY priority DESC, dueDate ASC
LIMIT 25

• Endpoint: POST /query (cursor-paginated, rate-limited at 60/min/user).
• Surface: REST endpoint, MCP tool orboto_query, CLI shortcut orboto query "<oql>", ⌘K palette OQL toggle.
• Authorisation: every query starts from a project-membership join + private-ticket guard. External users get is_private = false hard-pinned regardless of how the OQL is shaped, and never see a sub-ticket of a private ticket.

Writing your first query

If you've never written OQL before, build it up one clause at a time rather than reading the grammar cold. Every example below is real and runnable through any surface.

Start with one condition. A query is just field operator value:

assignee = currentUser()

This alone returns every ticket assigned to you, across every project you're a member of - no LIMIT, no ORDER BY, nothing else required. currentUser() is a function call that resolves to your own user id at query time, so the same query text means "my tickets" for whoever runs it.

Add a second condition with AND. Combine conditions the way you'd expect from any query language:

assignee = currentUser() AND statusCategory != done

Now it's your tickets that aren't finished yet - != excludes rather than includes, and statusCategory is the four-value workflow bucket (todo / in_progress / in_review / done) rather than a project-specific status name, so this works the same regardless of how any individual project labels its statuses.

Sort the results. ORDER BY goes after every filter condition, and takes one or more fields with an optional direction (ASC is the default when you omit it):

assignee = currentUser() AND statusCategory != done
ORDER BY priority DESC, dueDate ASC

This reads as "my open work, most urgent priority first, and for ties on priority, whichever is due soonest."

Cap how many rows come back. LIMIT goes last:

assignee = currentUser() AND statusCategory != done
ORDER BY priority DESC, dueDate ASC
LIMIT 10

Scope to a project, and search text. Add project = <key> to narrow to one project, and ~ for a case-insensitive substring match on a text field:

project = ACME AND title ~ "timeout" AND statusCategory != done

Check for something being missing. IS EMPTY / IS NOT EMPTY on a multi-valued field, and IS NULL / IS NOT NULL on a scalar one:

assignee IS EMPTY AND statusCategory = todo

That's the whole shape of the language: pick fields from the field reference below, combine them with AND / OR / NOT and parentheses for grouping, optionally sort and limit. Everything past this point is reference material for the specific fields, functions, and edge cases available to you.

Grammar

Formal PEG grammar lives in apps/api/src/services/oql/grammar.peggy. Informally:

Added in v0.222.0 A query may start with FIND tickets WHERE. The frame is a pure alias: FIND tickets WHERE status = Done ORDER BY dueDate ASC returns exactly what status = Done ORDER BY dueDate ASC returns. FIND, tickets and WHERE are case-insensitive; FIND without WHERE, or another entity after FIND, is a syntax error. The frame works in JQL mode too.

query        = (FIND tickets WHERE)? expression (ORDER BY orderList)? (LIMIT integer)?
expression   = orExpr
orExpr       = andExpr (OR andExpr)*
andExpr      = notExpr (AND notExpr)*
notExpr      = NOT? predicate
predicate    = "(" expression ")"
             | field operator value
             | field "IN" "(" valueList ")"
             | field "IS" ("NOT")? ("NULL" | "EMPTY")
operator     = "=" | "!=" | "<" | "<=" | ">" | ">=" | "~" | "!~"
value        = stringLit | numberLit | dateLit | functionCall | identifier
functionCall = "currentUser()" | "now()" | "startOfWeek()" | "endOfWeek()"
             | "startOfMonth()" | "endOfMonth()" | "daysAgo(n)"

• Keywords (AND, OR, NOT, IN, IS, NULL, EMPTY, ORDER, BY, LIMIT, ASC, DESC) are case-insensitive and word-bounded - andrew is an identifier, not AND + rew.
• Strings: "double" or 'single' quotes. Backslash-escapes \" and \' work.
• Identifiers: [a-zA-Z0-9_][a-zA-Z0-9_-]* - the trailing dashes let ACME-42 parse as a bareword, and the leading-digit class lets digit-leading project keys like 10M or 3D parse without quoting. Pure-digit tokens still parse as numbers because NumberLit is tried first in value position.
• Dates: ISO format "YYYY-MM-DD" as a quoted string. Function calls return either ISO timestamps (now(), daysAgo(n)) or strings (currentUser() → user UUID).
• Empty input is valid and matches every visible row (subject to ACL).

Field reference

Grouped by what the field describes, so you can jump straight to the category you need. Every field accepts the operators listed; anything else returns 400 with errorKey: errors.oql.unknown_field.

Identity

Status, type, priority

People

Labels, dependencies, git

Organization

Dates

Effort and time

Function reference

The function whitelist is closed - anything else (pg_sleep(), version(), current_user, …) is rejected at translate time.

Operators

• =, != - equality. For multi-valued fields (assignee, labels) this is membership.
• <, <=, >, >= - ordering on numbers and dates.
• ~, !~ - case-insensitive ILIKE. Pattern is automatically wrapped in %…%.
• IN (a, b, c) - equality against any of the listed values.
• IS NULL / IS NOT NULL - null checks on scalar fields.
• IS EMPTY / IS NOT EMPTY - multi-valued field has zero / non-zero rows.

ORDER BY + LIMIT

… ORDER BY priority DESC, dueDate ASC
… LIMIT 25

Multiple sort keys allowed. Direction defaults to ASC when omitted. The route appends tickets.id as a tie-breaker so cursor walks don't stutter.

LIMIT caps the page size at the source. The route also clamps to 200 - anything larger is silently capped.

Cookbook

# My open work, blockers first
assignee = currentUser() AND statusCategory != done
ORDER BY priority DESC, dueDate ASC

# Tickets I touched this week
assignee = currentUser() AND updatedAt >= daysAgo(7)
ORDER BY updatedAt DESC

# Anything blocking ORB this week
project = ACME AND priority IN (blocker, high) AND dueDate <= endOfWeek()

# Unowned bugs in the backlog
labels = bug AND assignee IS EMPTY AND statusCategory = todo

# At-risk for budget - logged exceeds estimate
loggedMinutes > estimatedTimeMinutes AND statusCategory != done

# Children of an epic
parentKey = ACME-200 ORDER BY ticketKey ASC

# Tickets shipped in the last sprint
statusCategory = done AND closedAt >= startOfMonth() AND closedAt < endOfMonth()

# Stale review queue - sitting in review for 3+ days
statusCategory = in_review AND updatedAt <= daysAgo(3)

# All my assigned bugs in the current quarter
assignee = currentUser() AND labels = bug AND createdAt >= startOfMonth()

# Free-text contains in a ticket key prefix
key ~ "ACME-2"

# Everything filed by guests across my projects
reporterType = guest ORDER BY createdAt DESC

# Open tickets that have a guest assignee
assigneeType = guest AND statusCategory != done

# All guest-related tickets (reported by OR assigned to a guest)
reporterType = guest OR assigneeType = guest

JQL compatibility

Pass syntax: 'jql' (or --syntax=jql from the CLI) to accept JQL-flavoured input. The adapter pre-rejects unsupported tokens then translates the survivor through the OQL parser with field/value renames applied.

Field aliases (JQL → OQL)

Value aliases on resolution / statusCategory

Unsupported features (and what to do instead)

Each unsupported token produces a 400 with errorKey: errors.jql.unsupported_feature carrying a suggestion field the UI surfaces verbatim.

Endpoint

POST /query
Authorization: Bearer <token>
Content-Type: application/json

{
  "oql": "project = ACME AND assignee = currentUser()",
  "syntax": "oql",      // or "jql" - defaults to "oql"
  "cursor": "...",      // opaque, from a previous response.nextCursor
  "limit": 25           // 1-200, default 25
}

Response:

{
  "items": [Ticket, ...],
  "nextCursor": "opaque-token-or-null",
  "queryPlan": { "sql": "...", "params": [], "ast": {...} }   // ?explain=true, super-admin only
}

Rate limit

Added in v0.223.0 Each account may run 60 OQL queries per minute by default. This covers POST /query and bulk edits that select tickets by a query. An administrator changes the limit for the whole workspace under Admin → System settings → OQL queries per minute per account (1 to 10,000).

Bot and evaluation accounts can get their own limit, for example a training account that checks many queries at once. Open Admin → Agents → Identities, choose Query rate limit in the account's row menu and enter the queries per minute; Use the workspace setting removes it again. From the command line:

orboto agents query-limit <bot-email> --set 600
orboto agents query-limit <bot-email> --clear

People's accounts always use the workspace setting. A change applies at once on the server that took it and within 30 seconds everywhere else. Every change to the workspace setting or to an account's limit is recorded in the audit log, with who changed it and from where (web, CLI, MCP or API).

Added in v0.225.0 POST /query is counted by this limit only: the general API limit per credential (600 requests per minute by default) does not apply to it, so other requests of the same credential do not use up your query budget and an override above 600 is honoured in full. A bulk edit by query costs one query from this limit and one request from the general limit; a bulk edit by ticket ids counts against the general limit only. Requests are counted before their body is checked, so malformed requests count too.

Every answer of POST /query and of a bulk edit by query carries three headers that match the limit above:

A bulk edit by query that reaches the general limit first gets the general limit's 429 and headers instead.

Over the limit, orboto answers 429 with a Retry-After header: the seconds until the next minute starts. errorParams carries the same value as retryAfterSeconds plus limitPerMinute. The orboto CLI waits that long and retries up to three times, the MCP tool orboto_query retries twice and then tells the agent when to try again. If you write your own client, read Retry-After instead of guessing a backoff.

Errors

All 4xx errors follow the ErrorResponseSchema shape (error, errorKey, errorParams):

?explain=true

Super-admin only. Adds queryPlan: { sql, params, ast } to the response so an operator can debug "why does my OQL return 0 rows" without database access. Non-admins receive the same 200 response without the field - no leak that the param exists.

Troubleshooting

Authorisation

The translator embeds the auth pipeline into the WHERE clause as the FIRST predicate, in this exact order:

1. Project membership - EXISTS project_members WHERE user_id = :requester. Super-admin bypass collapses to TRUE.
2. Private-ticket visibility - is_private = false OR EXISTS ticket_access_acl OR has-global ticket:view_private. Added in v0.230.0 The same rule must hold for every ancestor: a sub-ticket of a private ticket is visible only to someone who may see that private ancestor, and a sub-ticket of a ticket in a private milestone only to someone with milestone:view_private. A grant on the sub-ticket itself never opens an ancestor.
3. External-user hard pin - when the user row has is_external = true, is_private = false is appended LAST. No OQL clause can widen the filter past this guard.

Every query - * SELECT, IS NULL, OR-of-everything - runs with this predicate AND-ed in. The translator's security tests assert this contract.

Surfaces

Performance notes

• The translator is pure-function and < 1 ms per call. The compiled peggy parser caches at module-load.
• loggedMinutes uses a correlated SUM over time_entries; on workspaces with millions of time entries this slows queries that filter on it. A materialised view is the planned escape hatch when the slow-query threshold trips.
• ORDER BY on text fields (title, key via ~) doesn't use the search-vector index - keep complex text searches on the dedicated /search endpoint and use OQL for the structured filter combinations.
• The cursor is (updated_at, id) tuple-comparison so default-ordered queries paginate efficiently. Custom ORDER BY queries pay an in-memory sort if the order doesn't match an existing index.

What OQL does not do

OQL selects tickets. These are deliberately not part of it:

• Multi-entity queries (FROM tickets, comments).
• Aggregation (COUNT, SUM, GROUP BY) - the analytics endpoints cover those.
• Update queries (UPDATE tickets SET status = ...) - too dangerous for a public DSL; bulk edits take a query as their selection instead.
• Custom-field schema - not in the data model.
• Free-text body search - that is /search.
• Query history and performance insights.