Mark Dovgalyuk

Bullhorn's REST API Part 2: JPQL, Lucene, and pagination design

Bullhorn splits bulk reads across a relational JPQL engine and a Lucene index. The two order results differently, and that difference decides which pagination strategy works on each.

Minimalist black silhouette of three stacked layers, centered on an off-white background.
· 5 min read

Part 2 of a series on Bullhorn’s REST API. Part 1 covered its OAuth implementation.

Bullhorn’s read operations

Bullhorn exposes four primary read operations. /entity and /meta handle single-record lookups and schema exploration. /query and /search handle bulk reads, and they sit on different engines.

/query runs JPQL, which operates over JPA entity objects rather than database tables. Queries look and feel like SQL; the JPA provider translates them into native SQL against the underlying relational database. Most Bullhorn entities are reachable this way.

/search runs Lucene, a Java search library serving ranked, full-text, and k-nearest neighbor queries from an inverted index. Most Lucene-backed entities also support /query, but several are /search-only. Calling /query on Candidate returns:

JSON error response from Bullhorn: an errorMessage reading "Query operation not supported for Candidate, please use /search call instead.", an errorMessageKey of "errors.queryIndexedEntity", and an errorCode of 400.

Bullhorn doesn’t document why, but the boundary tracks payload weight. Candidate and Note carry free-text and denormalized fields (resumes, skills, comments) that would make unbounded JPQL scans expensive against a shared database.

So a bulk reader covering both query-supported and search-only entities has to handle two paging models.

Paging with start and count

Both endpoints take count (page size) and start (offset):

GET /query/JobOrder
  ?fields=id,title
  &where=isDeleted=false
  &orderBy=id
  &count=20
  &start=40

That’s offset paging: up to 20 records beginning at position 40. Bullhorn doesn’t publish an execution plan, but offset paging generally means walking and discarding everything before the offset, which carries two costs:

  • Performance: deeper pages cost progressively more.
  • Stability under writes: records created mid-walk shift every later offset, so pages silently duplicate or skip records.

Keyset paging on /query

Keyset pagination resumes from a position instead of skipping a count: start after the last ID you saw. On /query, the cursor lives in the where clause:

GET /query/JobOrder
  ?fields=id,title
  &where=id>12345
  &orderBy=id
  &count=20
  &start=0

start stays at 0, and each page filters on the last id returned by the previous one. There’s no offset to walk, so the engine has a position to seek to rather than a count to discard. This holds as long as id is evaluated numerically and the filter and the sort apply the same ordering — true on /query, and precisely what fails on /search.

Elasticsearch exposes search_after for deep reads on a Lucene index. Bullhorn’s /search exposes no cursor primitive.

Porting the /query pattern over doesn’t work either. Where an ID-like field is indexed and sorted as a string, filters and sorts compare it in lexicographic order: 1000, 16808, 525, and 9999 order as "1000" < "16808" < "525" < "9999". Casting client-side changes nothing, since the ordering belongs to the index mapping rather than the response. Mappings vary by field, so this is worth confirming against whichever field you intend to page on.

Nothing errors. A range predicate used as a /search cursor can terminate early or skip records while every request still returns 200, which is what makes it easy to miss.

Adaptive date-window partitioning

That leaves offset paging, with the depth bounded by the client. Bullhorn documents no depth limit to code against, so the threshold is empirical.

Split the range into date-bounded windows and page each one shallowly, keeping offsets under that threshold. A window that needs to page past it is too dense: halve its date range and repeat on each half until every window can be walked completely. If the query syntax forces inclusive boundaries, adjacent windows overlap by a day, so dedupe by ID.

Flowchart of adaptive date-window partitioning. A date window (start to now) is paged shallowly with offsets up to OFFSET_GUARD, then a "too dense?" check asks whether the offset ran past the guard. If yes, the window is split in half into two date sub-windows and the process recurses; if no, it is a leaf window that is paged through fully, emitting its records to the data lake. A note reads: adjacent windows share boundary days, so dedupe by ID within the run.

Each window pages shallowly until it would cross the offset guard. Too-dense windows split in half and recurse; sparse ones are leaf windows, paged through fully.

This still runs on offsets. The date windows just bound how deep any single /search query has to page before it hits the range where ordering stops being reliable.

Takeaways

Keyset paging fits /query, where id is a numeric column and the filter and the sort agree on ordering. /search can’t support it, so the search-only entities need offset depth bounded some other way; date windows are one option. Both endpoints take the same start and count parameters, which is exactly what makes it easy to assume one paging strategy covers both.

Part 3 covers watermarks, change detection, and reconciliation.

More writing

→ All posts
Black stylized bull head silhouette on an off-white background.

· 5 min read

Bullhorn's REST API Part 1: 3-legged OAuth and rotating refresh tokens

Bullhorn's API uses 3-legged OAuth, built for human-in-the-loop apps. Backend integrations have to design around that mismatch, plus the single-use refresh tokens Bullhorn issues and the failure modes that come with them.