Two campaigns in one city import the listing once, and the cache key is a half degree square

In the last post I wrote about how Nakodo's business campaigns get their list of businesses: DuckDB, over HTTP, against Overture Maps' public Parquet files. The headline number there was that a cold query for one small area took 45 seconds.

So the import is not something you want to run twice for the same place. And it is not something you want to run per campaign, because two different brands both looking for independent cafes within 10 km of Leeds are asking for exactly the same bytes.

The problem is that what a user gives us is not a cacheable thing. It is a point and a radius, chosen from a drop-down of 2, 5, 10, 25, 50, 100 and 200 km. Two brands will never pick the same centre, because one typed Leeds and the other typed Headingley.

Quantise the circle

The fix is the oldest trick in tiled mapping. Stop treating the circle as the unit of work and snap it to a grid:

const CELL = 0.5;
const KM_PER_DEGREE = 111.32;
const cellStart = (x: number) => Math.floor(x / CELL) * CELL;
const cellKey = (lat0: number, lng0: number) => `cell:${lat0},${lng0}`;

export function cellsAround(lat: number, lng: number, radiusKm: number): Area[] {
  const dLat = radiusKm / KM_PER_DEGREE;
  const dLng = radiusKm / (KM_PER_DEGREE * Math.max(0.05, Math.cos((lat * Math.PI) / 180)));
  // ...
}

Cells are 0.5 degrees square and named by their south-west corner, so the key for the square containing Leeds is the string cell:53.5,-2. Headingley is in the same square. The two campaigns now collide on purpose.

Three things in those four lines are worth saying out loud.

A degree of latitude is about 111.32 km everywhere. A degree of longitude is 111.32 km only at the equator and shrinks by cos(latitude) as you go north or south, which is why dLng has the cosine in it and dLat does not. Forget it and a 100 km radius in Scotland quietly asks for a box the right height and much too narrow.

The Math.max(0.05, ...) is a divide-by-almost-zero guard. At 89 degrees north the cosine is 0.017, and a 200 km radius would compute a longitude span of over 100 degrees, which is a request for a quarter of the planet. Clamping the cosine at 0.05 caps the damage. Nobody is running outreach to businesses at the pole, but the clamp costs one function call and the alternative is a Trigger.dev run that tries to read a hemisphere.

And the grid covers the circle's bounding box, not the circle. A few corner cells get imported that contain nothing inside the radius. That is fine: the pipeline filters by real distance later, and a cell that two cities share is a cache hit, not waste.

The antimeridian gets its own test

Stepping longitude from lng - dLng to lng + dLng in half degree increments is trivially correct until somebody targets Fiji, where that range runs off the end of the number line at 180 and has to reappear at -180:

const lng0 = ((((i * CELL + 180) % 360) + 360) % 360) - 180;

The double modulo is the usual JavaScript tax, because % keeps the sign of the dividend and -181 % 360 is -181 rather than 179. The test is the part that matters:

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"));
});

A 10 km circle that close to the line has to produce cells on both sides of it. Without the wrap you get the key cell:-17.5,180, which is a perfectly well-formed string that no import will ever write and no lookup will ever match, so that half of Fiji silently stays empty while the code reports success.

A country is not a grid

You can also target a whole country, and tiling France into 0.5 degree cells to import it would be absurd. So a country is its own single area, keyed country:FR, read in one run, and narrowed differently:

if (l.lat !== null && l.lng !== null && l.radiusKm !== null) {
  for (const a of cellsAround(l.lat, l.lng, l.radiusKm)) areas.set(a.key, a);
} else if (l.bbox) {
  areas.set(`country:${l.countryCode}`, { key: `country:${l.countryCode}`, bbox: l.bbox, countryCode: l.countryCode });
}

The countryCode is carried through into the query so that the reader adds a predicate on the place's own address country. A bounding box around France contains chunks of Belgium, Germany, Switzerland, Italy and Spain, and a brand that asked for France should not be emailing a bakery in Freiburg.

The cache is a table with a composite primary key

The thing being cached is not "this area", it is "this area, for this category". A campaign for pubs in Leeds tells you nothing about whether cafes in Leeds have been read. So the record is a pair:

export const placeImports = pgTable("place_imports", {
  area: text().notNull(),        // "cell:53.5,-2" or "country:GB"
  category: text().notNull(),
  release: text().notNull(),
  rows: integer().notNull().default(0),
  importedAt: timestamp({ withTimezone: true }).notNull().defaultNow(),
}, (t) => [primaryKey({ columns: [t.area, t.category] })]);

Freshness is 35 days, chosen because Overture publishes monthly and 30 would make a late release look like a stale cache every single month. Working out what to do is then a set difference:

const have = new Set(fresh.map((r) => `${r.area}|${r.category}`));
const missing = areas.filter((a) => categories.some((c) => !have.has(`${a.key}|${c}`)));

Missing cells are regrouped before they are queued

One import run reads one bounding box, and from the previous post we know the expensive part of a run is the Parquet footers, paid once per run regardless of how big the box is. So queueing one run per missing cell is the worst possible arrangement. Missing cells get grouped into blocks of four by four cells, two degrees on a side, and each block becomes one run with a box covering all of it. Countries stay alone.

The test asserts the invariant rather than the output, because the grouping is an optimisation and should be free to change:

const blocks = runs.filter((r) => !r.countryCode);
assert.equal(blocks.flatMap((r) => r.areas).length, cells.length);
for (const b of blocks) assert.ok(b.bbox[2] - b.bbox[0] <= 2 && b.bbox[3] - b.bbox[1] <= 2);

Every cell appears in exactly one run, and no run reads a box bigger than two degrees. How they are bundled is an implementation detail.

The idempotency key has a two hour TTL, and that is the design

Each run is handed to Trigger.dev with a key derived from its own payload:

const key = createHash("sha1").update(JSON.stringify(run)).digest("hex");
await tasks.trigger<typeof placeImport>("place-import", run, {
  idempotencyKey: `place-import:${key}`,
  idempotencyKeyTTL: "2h",
  tags: run.countryCode ? [`country:${run.countryCode}`] : ["cells"],
});

The pipeline ticks often, so without a key the same import would be queued on every tick until it finished. With a permanent key, an import that failed for a reason the task's own retries could not fix would never be attempted again, and that area would stay empty forever.

Two hours is the compromise, and it is the kind of number that only makes sense when you say what each end of the range breaks. Under a few minutes and a tick storm re-queues work that is still running. Measured in days, a transient S3 problem costs a user their search for days.

Note also that the key is a hash of the payload, which includes the sorted category list. Reorder the categories in the UI and you would get a different hash for identical work, which is why the caller sorts before it builds the runs.

And a search that is waiting says so rather than failing

requestImports returns the number of areas still to come. The search job uses that to re-queue itself without burning a retry attempt:

const pending = await requestImports(campaign.targeting, targetedCategories(campaign.targeting));
// ... add this campaign's next businesses from whatever is already loaded ...
if (pending > 0 && Date.now() - job.createdAt.getTime() < IMPORT_WAIT_MS) {
  throw new DeferJob(new Date(Date.now() + 2 * 60_000));
}

DeferJob puts the job back with a run-after two minutes out and does not count an attempt, so a search can sit politely in the queue for the six hours of IMPORT_WAIT_MS while a country import grinds through, and then give up for the day rather than failing the campaign. Meanwhile it is still adding businesses from the cells that have landed, so a user watching the screen sees results arriving while the rest is fetched.

One more thing worth copying: if TRIGGER_SECRET_KEY is not set at all, the function logs a warning and returns 0 rather than throwing. That is what makes the whole feature degrade to "use the places already in the database" in a local checkout, instead of being a hard dependency on a third-party queue to boot the app.

See the rules we publish

The user-facing version of all of this, including the radius choices and the fact that place names go through OpenStreetMap, is on nakodo.app/how-it-works#businesses. The radius list in that section is the same RADII_KM array the location code offers, and the plan limits that bound how many of these areas one account can ask for are on nakodo.app/pricing.