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.

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:

query        = 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
}

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.
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.

Roadmap

Out of scope for v1 (intentionally):

• Multi-entity queries (FROM tickets, comments).
• Aggregation (COUNT, SUM, GROUP BY) - the analytics endpoints already cover those.
• Update queries (UPDATE tickets SET status = ...) - too dangerous for a public DSL.
• Custom-field schema - not in the data model.
• Free-text body search - stays on /search.
• Query history / performance insights.

If any of these become hard requirements, let us know.