Bullhorn batch ingestion Part 2: JPQL, Lucene, and pagination design

Welcome to Part 2 of my Bullhorn data pipeline series!

This post walks through how Bullhorn’s two main query mechanisms behave differently, and how those differences can shape the pagination strategy used for each.

Bullhorn’s read operations

Bullhorn exposes four primary read operations, but in this post we’ll focus on /query and /search, as they are the primary mechanisms for handling bulk records.

The other two, /entity and /meta, are mainly used to retrieve records by known ID and inspect entity schemas.

To understand how to best handle /query and /search, it helps to briefly cover their underlying frameworks: JPQL and Lucene.

JPQL in Bullhorn

JPQL (Jakarta Persistence Query Language) operates over JPA entity objects instead of directly on database tables. You write JPQL queries that look and feel like SQL, but the JPA provider translates them into native database queries.

Many (but not all) entities in Bullhorn’s API are accessible through /query, which performs the query against Bullhorn’s relational database.

Lucene in Bullhorn

Apache Lucene is a search engine library written in Java.

It is designed for operations such as ranked and full-text search, which it performs against a search index rather than directly against the relational database.

Lucene can be embedded as a search engine library in applications.

You use the /search operation to access Bullhorn’s Lucene-backed entities.

Of those Lucene-backed entities, the majority also support JPQL /query, but several do not.

For example, try to /query on Candidate and you get this error:

Bullhorn does not publicly document why particular entities are limited to one operation. But architecturally, we need to consider that /query and /search operate against different systems and expose different pagination behavior.

The consequence is that you may need to design a cohesive pipeline that can accurately sync data from two engines with different query and pagination capabilities.

What is pagination?

In a data pipeline, pagination is how you walk a result set that’s too large to fetch in a single request. If your first request pulls page 1 with 500 entities, pagination is the strategy that decides how you fetch page 2, page 3, and so on.

There are two common approaches:

• Offset-based pagination
• Keyset pagination, also called seek or cursor pagination

Understanding how JPQL and Lucene differ forces you to choose between these approaches carefully.

Offset pagination is simple, but can become expensive and unstable

Offset pagination is a very common default in APIs and ORMs because it’s easy to implement and matches SQL’s native LIMIT and OFFSET semantics.

Conceptually, you tell the database how many rows to skip and how many to return. A SQL query like:

SELECT ...
FROM ...
WHERE ...
ORDER BY some_column
LIMIT 20 OFFSET 40;

means: give me 20 rows, starting after the first 40.

The offset is the number of earlier results that should be skipped. Those skipped results are not necessarily free: the database still has to identify them before returning the requested page. The exact execution plan depends on the database and available indexes, but the work often grows as the offset becomes deeper.

Note: In JPQL, you don’t write LIMIT and OFFSET in the query string itself. Instead, you implement the same pattern programmatically:

TypedQuery<MyEntity> q = em.createQuery(
    "SELECT e FROM MyEntity e ORDER BY e.id",
    MyEntity.class
);
q.setFirstResult(40); // offset
q.setMaxResults(20);  // limit
List<MyEntity> page = q.getResultList();

There are several tradeoffs to consider with offset pagination:

• Performance: Deeper pages can become progressively more expensive because the engine must identify the results that come before the requested offset.
• Stability under changes: If records are inserted, deleted, or reordered while you’re paging, the meaning of a numerical offset can shift. That can cause records to appear twice or be skipped between pages.
• Ordering: Offset pagination still requires a deterministic order. If the ORDER BY values are not unique, records with the same value may move between pages. Adding a unique tie-breaker, such as id, gives the result set a stable order.

Despite being the default across many frameworks, offset pagination can be a poor fit for large, changing result sets, especially when the API gives you the fields needed for keyset pagination.

Keyset pagination

Keyset pagination, also called seek or cursor pagination, avoids deep offsets by resuming from a concrete value rather than skipping an abstract number of rows.

Instead of saying “skip 20 rows,” you say “start after the last row I saw,” typically using a stable, ordered key.

For example:

SELECT ...
FROM ...
WHERE id > :last_seen_id
ORDER BY id
LIMIT 20;

In JPQL, you express the same pattern in the query and control only the page size programmatically:

TypedQuery<MyEntity> q = em.createQuery(
    "SELECT e FROM MyEntity e " +
    "WHERE e.id > :lastSeenId " +
    "ORDER BY e.id",
    MyEntity.class
);
q.setParameter("lastSeenId", lastSeenId);
q.setMaxResults(20); // page size, no offset
List<MyEntity> page = q.getResultList();

Here, page 1 might fetch the first 20 rows ordered by ID. Page 2 then uses the ID of the last row in page 1 as last_seen_id.

When the cursor field is indexed, the database can seek to values after that key instead of repeatedly working through the earlier pages.

This pattern works well for many JPQL-backed /query entities because Bullhorn entities have an integer primary key named id, and /query allows both filtering and ordering by entity properties.

The ordering must still be unique and stable. For a full historical walk, id > last_seen_id ORDER BY id provides a straightforward continuation point.

An ID cursor does not, however, detect later updates to records with smaller IDs. That is a separate change-detection problem, typically handled through modification timestamps, watermarks, or reconciliation logic.

Why Bullhorn’s Lucene index can be tricky to paginate

For JPQL-backed /query endpoints, both approaches are possible. Bullhorn exposes start for offset paging, while JPQL predicates make keyset pagination possible. For a large ordered backfill, keyset pagination is often the stronger default.

For Lucene-backed /search, however, we face a different constraint. Offset pagination carries similar performance and stability concerns, but Bullhorn does not expose an equivalent cursor primitive.

Lucene itself provides searchAfter for efficient deep paging. Bullhorn’s public /search operation, however, documents only start, count, and sort. It does not expose a continuation token tied to the last returned result.

Without that primitive, you might instinctively try to reproduce the JPQL keyset pattern inside a Lucene query.

The problem is that Bullhorn does not document a numeric cursor contract for fields in its search index. The Lucene query-parser documentation linked from Bullhorn states that range queries are evaluated lexicographically.

If an ID is represented as a text term, values such as 1000, 16808, 525, and 9999 would be ordered as "1000" < "16808" < "525" < "9999". In that case, a range intended to mean “all IDs greater than 16808” would not behave like a numeric database comparison.

The public documentation does not expose enough of Bullhorn’s underlying index mapping to assume this behavior for every field or environment. Before treating any /search field as a cursor, an integration should inspect the schema returned by /search/{Entity} with no parameters and test both its range and sort behavior.

The safer conclusion is that database-style numeric semantics are not part of Bullhorn’s documented /search contract. Even where sorting appears usable, Bullhorn still does not return a cursor tied to that ordering or a snapshot of the index.

One possible alternative is to keep using Bullhorn’s supported offset pagination, but partition the search into small enough ranges that no individual query requires a deep offset.

A practical alternative: adaptive date-window partitioning

A useful name for this approach is adaptive date-window partitioning. It combines date-range partitioning with shallow offset pagination.

The idea is that instead of trying to page one huge, unbounded result set, you break the result set into smaller date-bounded windows. Within each window, the integration uses offset pagination while keeping the offset below a chosen guard threshold.

The date field should be one exposed by the entity’s runtime search schema. For a historical backfill, an immutable creation timestamp is generally easier to reason about. A modification timestamp can support an incremental sync, but it requires overlap and reconciliation because its value changes along with the record.

If a given window turns out to be too dense, meaning the next page would cross the offset guard, the integration splits that window into smaller date ranges and applies the same logic to each half.

Concretely, an implementation can define an OFFSET_GUARD value. If the next /search request would need a start value beyond that guard, the current date window is treated as too dense. The window is divided in half, and each half is processed independently.

The process continues until each window is small enough to page through without crossing the guard.

Window boundaries need to be explicit. Half-open ranges, such as “greater than or equal to the start and less than the end,” avoid accidental overlap when the query syntax and timestamp precision allow it. Otherwise, the windows can deliberately overlap at the boundary and the results can be deduplicated by ID.

Each date window uses shallow offsets until it would cross the guard. Too-dense windows split in half; smaller leaf windows page through fully.

This is not keyset pagination in the relational sense because there is no stable cursor anchored to one numeric field. Instead, the date ranges partition the result set so that Bullhorn’s supported offset mechanism stays relatively shallow.

The key point is that the date windows are a partitioning device. They limit pagination depth; they do not create a frozen snapshot of the search index.

An implementation should capture a fixed upper time boundary at the start of a run so the target range does not continue expanding while it is being processed. It should also deduplicate overlapping results and use a later reconciliation pass to catch records that moved while the index was being read.

The recursive split also needs a stopping condition. If one timestamp contains more records than the offset guard allows, dividing the same timestamp again will not make the window smaller. At that point, the implementation needs a finer timestamp, another partitioning field, or a controlled exception to the offset guard.

Watermarks, overlap, and reconciliation are the next layer of this design, and I’ll cover them in Part 3.

Takeaways and how this sets up Part 3

The core lesson here is that there is no single pagination strategy that fits both Bullhorn query engines.

JPQL-backed /query entities can support ordinary keyset pagination when a stable, indexed key is available. Bullhorn’s Lucene-backed /search operation exposes offset pagination but no documented cursor, so an integration may need another way to keep those offsets shallow.

Adaptive date-window partitioning is one possible approach. It does not replace change detection or reconciliation, but it can divide a large search into smaller ranges that are easier to page through.

In Part 3, I’ll cover how watermarks, overlapping lookback windows, and reconciliation sweeps can be paired with these pagination strategies to keep downstream data accurate as Bullhorn records continue to change.