# TQL reference

Source: https://iskue.com/docs/tql-reference/

> TQL reference for Iskue: grammar, operators, every field, date arithmetic, custom-field queries, ORDER BY, saved filters, subscriptions and scoping.

TQL (Ticket Query Language) is Iskue's language for finding tickets. It drives the Search page's TQL and builder modes, saved filters, filter-subscription emails and the `GET /api/v1/search/tql` endpoint. A query is a boolean expression over ticket fields, optionally followed by an `ORDER BY` clause; either part may stand alone.

## Grammar

*Effective TQL grammar (recursive-descent parser, search/tql/TqlParser.java)*

```text
query      := [expression] [ORDER BY order ("," order)*]
order      := sortField [ASC | DESC]
expression := term (OR term)*
term       := factor (AND factor)*
factor     := NOT factor | "(" expression ")" | comparison
comparison := field ("=" | "!=" | "~" | ">" | "<") value
            | field [NOT] IN "(" value ("," value)* ")"
field      := IDENT | cf "[" name "]"
value      := bare word | "quoted string" | function "()"
```

- `NOT` binds tightest, then `AND`, then `OR` — `a OR b AND c` means `a OR (b AND c)`. Use parentheses to group.
- Keywords (`AND`, `OR`, `NOT`, `IN`, `ORDER BY`, `ASC`, `DESC`) and field names are case-insensitive; `order by created desc` is valid.
- A blank query is valid: it matches every ticket you can see, sorted by `updated` descending.
- Syntax errors return HTTP 400 with the character position, e.g. `Unexpected token ')' (at position 11)`.

### Values and quoting

- Bare values may contain letters, digits and `_ . @ - #`, so emails (`jane.doe@example.com`), ticket keys (`CORE-12`) and labels (`front-end`, `#urgent`) need no quotes.
- Quote anything containing spaces or other punctuation, with double or single quotes: `status = "In Progress"`, `title ~ 'multi word value'`. There are no escape sequences; a double-quoted string may contain apostrophes and vice versa.
- Function values are written with empty parentheses: `currentUser()`, `now()`, `startOfDay()`, `startOfWeek()`, `startOfMonth()`.
- `""` (the empty string) is a valid value.

## Operators

| Operator | Meaning | Notes |
| --- | --- | --- |
| `=` | equals | Exact match. On `title`, an exact (case-insensitive) title match. |
| `!=` | not equal | Negation of `=`. Not valid on `title` or `text`. |
| `~` | contains | Substring on `title`, full-text on `text`, case-insensitive contains on custom fields. |
| `>` | greater than / after | Numeric and date fields only. |
| `<` | less than / before | Numeric and date fields only. |
| `IN (a, b, c)` | matches any listed value | At least one value; empty lists and trailing commas are rejected. |
| `NOT IN (a, b, c)` | matches none of the listed values |  |
| `NOT` | logical negation | Prefix; applies to the next comparison or parenthesised group. |

## Fields

Field names are case-insensitive. An unknown field is rejected with a message listing the available fields (that list is slightly stale: it omits `theme`, `startDate` and `due`, which all work). The autocomplete metadata at `GET /api/v1/search/tql/metadata` has a similar gap — it advertises only `=`, `!=`, `>` and `<` for the number fields, although `IN`/`NOT IN` are accepted there too. Value resolution varies: identity values are validated (an unknown project key or user email is an error), while catalogue names (status, type, version, sprint and similar) that match nothing simply return no results.

| Field | Operators | Values and behaviour |
| --- | --- | --- |
| `project` | `=` `!=` `IN` `NOT IN` | Project key, e.g. `CORE`. An unknown key is an error. |
| `type` | `=` `!=` `IN` `NOT IN` | Ticket-type name or code from the project type catalogues (per-project renames are honoured). Unknown values match nothing. |
| `status` | `=` `!=` `IN` `NOT IN` | Status name, case-insensitive. Unknown values match nothing. |
| `statusCategory` | `=` `!=` `IN` `NOT IN` | `Todo`, `InProgress`, `Done` (`"In Progress"`, `in-progress` and `IN_PROGRESS` also accepted). Invalid values are an error. |
| `priority` | `=` `!=` `IN` `NOT IN` | `HIGHEST`, `HIGH`, `MEDIUM`, `LOW`, `LOWEST` (case-insensitive). Invalid values are an error. |
| `assignee` | `=` `!=` `IN` `NOT IN` | User email, `me`, `currentUser()`, or `empty` for unassigned. Unknown emails are an error. |
| `reporter` | `=` `!=` `IN` `NOT IN` | As `assignee`. |
| `label` | `=` `!=` `IN` `NOT IN` | Label name, case-insensitive. |
| `fixVersion` | `=` `!=` `IN` `NOT IN` | Version name; unknown names match nothing. |
| `affectedVersion` | `=` `!=` `IN` `NOT IN` | As `fixVersion`. |
| `component` | `=` `!=` `IN` `NOT IN` | Component name; unknown names match nothing. |
| `sprint` | `=` `!=` `IN` `NOT IN` | Sprint name, or `empty` for backlog tickets. |
| `team` | `=` `!=` `IN` `NOT IN` | Team name, or `empty` for unowned tickets. |
| `pi` | `=` `!=` `IN` `NOT IN` | Program Increment name, or `empty` for unplanned tickets. |
| `theme` | `=` `!=` `IN` `NOT IN` | Strategic-theme name, or `empty` for untagged tickets. |
| `parent` | `=` `!=` `IN` `NOT IN` | Ticket key, e.g. `CORE-12`, or `empty` for top-level tickets. Unresolvable keys match nothing. |
| `linkedTo` | `=` `!=` `IN` `NOT IN` | Ticket key; matches tickets linked to it in either direction. |
| `linkType` | `=` `!=` `IN` `NOT IN` | `BLOCKS`, `RELATES`, `DUPLICATES`, `CLONES`, `CAUSES` (case-insensitive). Invalid values are an error. |
| `timeSpent` | `=` `!=` `>` `<` `IN` `NOT IN` | Whole number of seconds logged. |
| `originalEstimate` | `=` `!=` `>` `<` `IN` `NOT IN` | Seconds. |
| `remainingEstimate` | `=` `!=` `>` `<` `IN` `NOT IN` | Seconds. |
| `created` | `>` `<` | Date value (see below). |
| `updated` | `>` `<` | Date value. |
| `startDate` | `>` `<` | Date value (calendar date). |
| `due` | `>` `<` | Date value (calendar date). |
| `title` | `~` `=` | `~` case-insensitive substring; `=` exact title, case-insensitive. |
| `text` | `~` `=` | Full-text search over title and description; both operators behave identically. |
| `cf["Name"]` | `=` `!=` `~` | Custom field by display name (see below). |

> **Note:** The `empty` sentinel works on `assignee`, `reporter`, `sprint`, `team`, `pi`, `theme` and `parent`. Write `assignee = empty` for unassigned and `assignee != empty` to require a value. There is no `IS EMPTY` syntax — `assignee IS EMPTY` and `assignee is empty` are parse errors. `empty` is only recognised as a **single** value: `assignee IN (empty, alice)` does not match unassigned tickets.

> **Warning:** Negations never match empty fields: `assignee != alice`, `assignee NOT IN (alice, bob)` and `NOT assignee = alice` all exclude unassigned tickets, because an empty field compares as unknown. Add `OR assignee = empty` explicitly when you want them. The same applies to every nullable field (`sprint`, `team`, `pi`, `theme`, `parent`, `fixVersion`, …).

### Text search

`title ~ value` performs a case-insensitive substring match on the title; `title = value` requires the whole title to match, still ignoring case. `text` searches title **and** description with Postgres full-text search over a stored, GIN-indexed `search_vector` (the `ticket_fts` helper using `plainto_tsquery` with the `simple` dictionary): every word in the value must appear as a whole word, in any order and any case, with no stemming. For `text`, `~` and `=` behave identically.

### Dates and date arithmetic

`created` and `updated` are timestamps; `startDate` and `due` are calendar dates. All four accept only `>` (after) and `<` (before) — `created = 2025-01-01` is rejected with `Field 'created' supports > (after) or < (before) a date`. Values are evaluated in UTC and take any of the forms below; anything else fails with `'…' is not a date (use yyyy-MM-dd, now(), startOfWeek(), or -7d)`.

| Value | Meaning |
| --- | --- |
| `now()` | The current instant. |
| `startOfDay()` | Today at 00:00 UTC. |
| `startOfWeek()` | Monday of the current week, 00:00 UTC. |
| `startOfMonth()` | First day of the current month, 00:00 UTC. |
| `-7d` | Seven days ago, at start of day UTC. Units: `d` days, `w` weeks, `m` months, `y` years — e.g. `-2w`, `-3m`, `-1y`. A positive value (`7d`) is a future date. |
| `2025-01-01` | ISO `yyyy-MM-dd`, at start of day UTC. |

### Custom fields: cf["Name"]

`cf["Story Points"] = 5` filters on a custom field by its display name. The name is matched case-insensitively across the custom fields of every project; when several projects define a field with the same name, a ticket matches if any of them holds the value. `=` and `!=` compare the stored value exactly (case-sensitive), while `~` is a case-insensitive contains. Each clause takes a single value — `IN` is not supported here — and an unknown name is an error (`Unknown custom field '…'`). The bracketed name may be quoted or, for single-word names, bare: `cf[Severity] = Critical`.

## ORDER BY

Append `ORDER BY key [ASC|DESC], key …` to any query, or use it on its own to sort the whole ticket set. Direction defaults to `ASC`; keys are case-insensitive. Only the keys below are sortable — filter fields such as `assignee` are not — and an unknown key is rejected with `Cannot sort by '…'` listing the alternatives. Without `ORDER BY`, results are sorted by `updated` descending.

| Sort key | Sorts by |
| --- | --- |
| `created` | Creation time |
| `updated` | Last update |
| `priority` | Priority **name**, alphabetically — not urgency. `ORDER BY priority DESC` currently yields `Medium, Low, High, Critical…` rather than most-urgent-first; sort client-side or filter with `priority IN (…)` when ranking matters. |
| `number` | Ticket number within its project |
| `title` | Title |
| `type` | The ticket's legacy type column, not the per-project type-catalogue association — tickets of the same catalogue type sort together, but the order is by internal value rather than display name. |
| `timeSpent` | Logged time |
| `wsjf` | WSJF score |

## Examples

*Your unfinished work in CORE, most urgent first*

```text
project = CORE AND status IN ("To Do", "In Progress") AND assignee = me ORDER BY priority DESC
```

*Unassigned bugs raised in the last seven days*

```text
type = Bug AND created > -7d AND assignee = empty
```

*Sprint 12 items with more than a day (8 h) remaining, or estimated at 8 points*

```text
sprint = "Sprint 12" AND (remainingEstimate > 28800 OR cf["Story Points"] = 8)
```

*Full-text search combined with component and version scoping*

```text
text ~ "connection timeout" AND component = Backend AND fixVersion NOT IN ("1.0", "1.1")
```

*Active regressions across two projects touched this week*

```text
project IN (CORE, OPS) AND (type = Bug OR label IN (regression, customer-impact)) AND NOT statusCategory = Done AND updated > startOfWeek() ORDER BY priority DESC, updated DESC
```

### Running a query over the API

*Against the full-stack dev compose (http://localhost:8088)*

```bash
TOKEN='<your JWT>'
curl -sG 'http://localhost:8088/api/v1/search/tql' \
  -H "Authorization: Bearer $TOKEN" \
  --data-urlencode 'query=assignee = me AND statusCategory != Done ORDER BY priority DESC' \
  --data-urlencode 'page=0' \
  --data-urlencode 'size=50'
```

Results are paged: `page` defaults to `0` and `size` to `50`, capped at `200`. `GET /api/v1/search/tql/detailed` takes the same `query`, `page` and `size` and returns the same result set in the **full ticket shape** (every field, custom values and ids) — it feeds the cross-project **Sheet** mode of the Search page, where cells are edited in place, and it applies exactly the same scoping as `/tql`. `GET /api/v1/search/tql/metadata` returns the field list with per-field operators, the keywords, the sort keys and concrete value suggestions (project keys, labels, versions, sprints, custom-field options, user emails) scoped to your projects, plus instance-wide status names — the same data that powers the Search page's autocomplete.

## Quick search

- The Search page's Quick mode and `GET /api/v1/search?q=…` take a plain term — no TQL parsing; operators are treated literally.
- Terms shorter than 2 characters (after trimming) return nothing.
- Matches up to 5 projects whose name or key contains the term (case-insensitive), and up to 15 tickets whose title contains the term, most recently updated first.
- The same project-membership and restricted-ticket rules apply as for TQL.

## Saved filters

- Save any valid query under a name from the Search page or the API. The query is parse-validated on save; field and value resolution happens on each run. A blank query cannot be saved — to keep an "everything" filter, use a tautology such as `created > -100y` or an `ORDER BY` clause on its own.
- Filters are private by default; setting `shared` to `true` makes the filter visible to every user. Shared results still run under each viewer's own membership and visibility, so two users can see different results from the same filter.
- Names are limited to 100 characters and queries to 5000; names are unique per owner (case-insensitive), and a duplicate is rejected with a conflict.
- Only the owner can rename, edit, share, unshare or delete a filter. Everyone can list their own filters plus all shared ones.

### Subscriptions

- Subscribe to a filter to receive its current results by email on a schedule. You can subscribe to your own filters and to shared ones; another user's private filter returns not-found.
- The email runs the query **as you**, so it can never show tickets you cannot see.
- At most one email per period. The period is `app.filter-subscription.period-minutes` (default `1440`, i.e. daily); a background sweep checks due subscriptions every 60 seconds.
- When the filter currently matches nothing, no email is sent and the slot still advances to the next period.
- The email lists up to 20 matching tickets (key and title), followed by `…and N more` when the result is larger.

| Endpoint | Purpose |
| --- | --- |
| `GET /api/v1/filters` | List your filters plus all shared filters. |
| `POST /api/v1/filters` | Create a filter: `{"name", "query", "shared"}`. |
| `PATCH /api/v1/filters/{id}` | Update name, query or shared flag (owner only). |
| `DELETE /api/v1/filters/{id}` | Delete a filter (owner only). |
| `GET /api/v1/filters/subscriptions` | Ids of the filters you are subscribed to. |
| `PUT /api/v1/filters/{id}/subscription` | Subscribe to a filter. |
| `DELETE /api/v1/filters/{id}/subscription` | Unsubscribe. |

## Scoping and visibility

- Every search path — quick search, TQL, saved filters, subscription emails — is limited to projects you are a member of; global admins search all projects. With no memberships, results are empty.
- Restricted tickets appear in results only for their reporter, their assignee, project admins and global admins; for everyone else they are absent, and direct access returns not-found.
- Most value suggestions from the metadata endpoint (project keys, labels, versions, sprints, custom-field options, user emails) are scoped to your project memberships; status names (and team, PI and theme names) are suggested instance-wide.
- Saved-filter validation on save is syntax-only: resolution errors such as an unknown project key or user email surface when the filter runs, not when it is saved.
