A row whose release is the word "requested", and the six hours that make it mean "on its way"

Nakodo's business campaigns need an open places listing to work from. Businesses come from Overture Maps, which publishes monthly, and our how it works page says so along with the rest of the method: the kinds of business you choose, the places you choose, nearest first, listings Overture is unsure about left out.

Reading Overture is not a request you make while someone waits. It is a background job over GeoParquet files, it takes minutes, and it runs on Trigger.dev. So the campaign's search and the import are separated by a table, place_imports, with one row per (area, category) pair holding the release it loaded and when.

The interesting part is that the same table also holds the fact that an import has been asked for, and the whole mechanism is one sentinel string:

const REQUESTED = "requested";
export const IMPORT_HOURS = 6;
const RETRY_DAYS = 7;

Three different nos

Before asking for anything, importNeeds sorts every area-and-category pair the campaign needs into one of three answers. It is a pure function, which is why it can be tested without a database:

const r = byPair.get(`${a.key}|${category}`);
const age = r ? now - r.importedAt.getTime() : Infinity;
const asked = r?.release === REQUESTED;
if (r && !asked && age < FRESH_DAYS * DAY_MS) continue;
if (asked && age < IMPORT_HOURS * HOUR_MS) coming.add(a.key);
else if (!asked || age >= RETRY_DAYS * DAY_MS) missing.set(a.key, [...(missing.get(a.key) ?? []), category]);

• A real release loaded less than 35 days ago: nothing to do, because Overture publishes monthly.
• A requested mark less than six hours old: an import is on its way, so the campaign waits rather than asking again. Trigger.dev runs two of these at a time, and six hours is the window in which a queued run is still plausibly a run.
• A requested mark older than six hours: that import failed. And this is the bit worth stopping on.

// Trigger.dev frees a failed run's idempotency key, so without the mark every
// look would start it again.

A failed run gives its idempotency key back, which is reasonable behaviour from the platform and lethal in combination with a loop that asks for what it is missing. The mark is the only memory that the attempt happened, so a failure deliberately keeps its row and blocks retries for a week. Nothing else in the system knows the difference between "this area has no cafes" and "we could not read this area's cafes", and a week of not hammering a failing job is the cheapest way not to care.

The arrivedAt the function also returns ignores the marks entirely:

for (const r of rows) {
  if (r.release !== REQUESTED && (!arrivedAt || r.importedAt > arrivedAt)) arrivedAt = r.importedAt;
}

A search deferring itself until data lands needs the time real data landed, not the time somebody wrote a note saying it might.

The mark is written with a compare and set

Two campaigns in two tabs, or two searches in one, can want the same cell. Marking first solves the duplicate request, but only if the mark itself is atomic against what was read a moment ago. That is what setWhere is for:

await db.insert(placeImports)
  .values([...missing].flatMap(([area, cats]) => cats.map((category) => ({ area, category, release: REQUESTED, rows: 0, importedAt: at }))))
  .onConflictDoUpdate({
    target: [placeImports.area, placeImports.category],
    set: { release: REQUESTED, rows: 0, importedAt: at },
    setWhere: or(
      and(eq(placeImports.release, REQUESTED), lt(placeImports.importedAt, new Date(now - RETRY_DAYS * DAY_MS))),
      and(ne(placeImports.release, REQUESTED), lt(placeImports.importedAt, new Date(now - FRESH_DAYS * DAY_MS))),
    ),
  })
  .returning({ area: placeImports.area, category: placeImports.category });

ON CONFLICT DO UPDATE ... WHERE is the trick. The predicate restates the same two timeouts from the other side: overwrite a mark only if it is a week stale, overwrite a real release only if it is 35 days stale. A row updated by somebody else in between fails the predicate, is not returned, and therefore is not in the list of things we go on to trigger. returning turns the upsert into the reservation: whatever came back is ours to ask for, and nothing else is.

No advisory lock, no transaction around a job trigger, no SELECT ... FOR UPDATE held across a network call to another service.

And the mark can be taken back

Marking before triggering means a failure between the two leaves a mark for an import nobody asked for. Six hours of a campaign waiting on a request that was never made is a bad trade for a crash, so the catch undoes the marks that have not been used yet:

} catch (e) {
  // Not asked for after all: the marks go, so the next look asks again.
  for (const r of runs.slice(i)) {
    await db.delete(placeImports).where(and(
      eq(placeImports.release, REQUESTED),
      eq(placeImports.importedAt, at),
      inArray(placeImports.area, r.areas),
      inArray(placeImports.category, r.categories),
    ));
  }
  throw e;
}

runs.slice(i) is the unsent tail: runs already accepted by Trigger.dev keep their marks, because those imports are genuinely coming. The delete is keyed on release = 'requested' and on the exact importedAt timestamp this call wrote, so it can only ever remove its own marks, never a mark another request made a second later. And it rethrows: the campaign's search sees the failure, rather than quietly believing an import is in flight.

The degenerate case is a warning, not an error

export const placeImportsEnabled = () => Boolean(env("TRIGGER_SECRET_KEY"));
// ...
if (!placeImportsEnabled()) {
  console.warn(`${missing.size} areas of business listings not loaded: TRIGGER_SECRET_KEY isn't set.`);
  return { pending: coming.size, arrivedAt };
}

On a developer machine with no Trigger.dev key, business campaigns run against whatever places are already loaded. The alternative, failing the search, would mean nobody could work on scoring or outreach without a background job platform configured. This check sits after the needs are computed and before any mark is written, so a key-less environment also never leaves marks nothing will ever fulfil.

Five tests, no database

Because cellsAround, areasFor, importRuns and importNeeds are all pure, the whole policy is testable as data, and the last test is a timeline:

// Pubs in, cafés asked for an hour ago: wait, and ask for nothing more.
assert.equal(asked.missing.size, 0);
assert.deepEqual([...asked.coming].sort(), [a, b].sort());
assert.deepEqual(asked.arrivedAt, hoursAgo(2));

// The cafés never came: not asked for again, nor waited on, for a week.
const failed = importNeeds(areas, ["pub", "cafe"], [row(a, "pub", "2026-09", hoursAgo(30)), row(a, "cafe", "requested", hoursAgo(IMPORT_HOURS + 1))], now);
assert.equal(failed.coming.size, 0);
assert.deepEqual(failed.missing.get(a), undefined);

All five pass. The one above it is my favourite kind of test, because it only exists to document a shape:

test("cells wrap across the antimeridian", () => {
  const keys = cellsAround(-17, 179.97, 10).map((c) => c.key);
  assert.ok(keys.includes("cell:-17.5,179.5"));
  assert.ok(keys.includes("cell:-17.5,-180"));
});

Fiji has businesses too, and a 10 km radius there straddles 180 degrees.

Business outreach is on the paid plans, and the pricing page lists it next to the website research runs each plan gets, which is the other half of this pipeline: the listing says a business exists, and its own site is what gets read afterwards.