What it takes to say a job has been open 47 days

fable12 shows 68,278 open roles from 385 employers, and every one of them carries how long it has actually been open. Not a freshness score, not the day we found it — the date the employer’s own hiring system reports. That is the entire product. It is also the only genuinely hard thing in it, and this is a note on what defending it costs.

The claim is one number#

Job boards mostly do not tell you how old a listing is, and when they do, the number is usually theirs rather than the employer’s. Re-post a requisition and it looks new. Edit a word in the description and the date resets. The result is that “posted 2 days ago” on an aggregator can mean a role that has been sitting unfilled since last spring.

fable12 takes one position: publish the employer’s own publish timestamp, or do not publish the employer. That single rule decides everything downstream — which companies are indexed, what the database stores, which listings get thrown away, and what the interface is allowed to say. Workday tenants are absent from the corpus entirely, and the reason is one line: Workday reports Posted Today and Posted 30+ Days Ago, which cannot support a date.

Why there is a database at all#

The obvious design is to proxy: hold nothing, fetch the boards on demand. Two facts kill it, and the second is the interesting one.

Aggregates over ~9k listings cannot be recomputed from forty HTTP calls per page view; and, more importantly, a listing that disappears from an employer’s board is gone from the internet — the day it closed exists only if we wrote it down. So nothing here deletes. A listing that stops appearing is marked closed and keeps its dates, because “how long did that req stay open” is the question the whole site is built to answer.

packages/corpus/src/db.ts

A proxy can tell you what is open now. It can never tell you what closed, or how long it took, because that fact only exists in the difference between two observations and nobody was there for the first one. 5,579 listings have now been watched from open to close. Every one of those is a fact that would not exist if the architecture were stateless.

So: SQLite, one file, in WAL mode, written by a single sweeper process and read by everything else. Not a distributed anything. The corpus is around 400MB and the queries that matter finish in single-digit milliseconds.

Partial indexes, because nothing is ever deleted#

Once you decide never to delete, the shape of the index problem changes. Closed listings accumulate forever; open listings stay roughly flat. And closed = 0 is the predicate on essentially every query a reader ever causes.

CREATE INDEX IF NOT EXISTS jobs_live_pub  ON jobs(published_ts DESC, id) WHERE closed = 0;
CREATE INDEX IF NOT EXISTS jobs_live_co   ON jobs(company_slug)           WHERE closed = 0;
CREATE INDEX IF NOT EXISTS jobs_live_dept ON jobs(department)             WHERE closed = 0
                                                                           AND department IS NOT NULL;

A full index would make every scan pay for history no reader wants. These do not. It is a small thing that follows directly from a product decision three layers up, which is the general shape of most of the good decisions in this codebase.

Eleven systems, eleven answers to “when”#

There are eleven applicant tracking systems behind those 385 employers, and no two agree on how to express a publish date. This table is the actual content of the ingestion layer; everything else is plumbing.

Two of those rows are worth expanding, because they are the ones where the system is not merely inconvenient but actively misleading.

Eightfold, and dates that move in bulk

Eightfold tenants expose both creationTs and postedTs. Microsoft’s postedTs is meaningful. Qualcomm’s and NVIDIA’s are bulk-refreshed, so a requisition open sixteen months reports as opened yesterday. The adapter therefore picks the field per tenant rather than per platform — the kind of configuration that looks like technical debt until you know why it exists.

Apple, and the 81 rows that lie

Apple re-stamps some requisitions at request time. Finding that out required fetching the whole board twice, nine minutes apart, and diffing 6,142 rows:

81 rows changed — every one managedPipelineRole: true. 6,061 rows identical — every one managedPipelineRole: false. Not a single crossover in either direction.

commit afab116

The near-miss is the better half of the story. The obvious discriminator — those rows carry nanosecond precision where the others carry milliseconds — is a property of that endpoint’s serialiser and not of the data; a different Apple endpoint re-stamps the very same requisitions and renders them at millisecond precision, where that test would have passed them. And all 81 sit at positions 0–80 of the recency sort precisely because re-stamping floats them to the top, which is why sampling the head made the entire field look synthetic.

The floor, and what crawled under it#

Every adapter runs its timestamp through one gate:

export function isPlausiblePublish(ats: Ats, ts: number): boolean {
  if (!Number.isFinite(ts) || ts <= 0) return false;
  // A day of slack: clock skew and timezone rendering are not fraud.
  if (ts > Date.now() + 86_400_000) return false;
  if (STAMPS_TIME_OF_DAY[ats] && ts % 86_400_000 === 0) return false;
  return ts >= ATS_EPOCH[ats];
}

ATS_EPOCH came first, and it came from a genuinely embarrassing number. Palantir ’s Lever board reported a Forward Deployed Software Engineer requisition created 2009-12-05. Lever did not exist until 2012. That single row was the source of the site’s oldest headline figure — open for 16.7 years — which is exactly the kind of number a sceptical reader reaches for to dismiss an entire dataset. So the gate learned each platform’s founding year and refused anything older. One row out of 34,898.

That fix was correct and insufficient, which I only discovered while writing this post. The next artifact of the same migration is dated 2012-12-20 — inside a floor set at Lever’s founding year, still not a publish date, and it inherited the headline the moment the 2009 row was gone. The site went on claiming a 13-year-old job.

What separates the fakes from the real postings turns out not to be the year at all. It is the precision. Lever records createdAt in milliseconds, so a genuine one lands on an exact midnight UTC roughly once every 86,400,000. I fetched all eighteen Lever boards in the registry and counted:

All seven belong to Palantir, and they are precisely its seven oldest. The oldest Palantir posting carrying a real time of day is 2016-02-24T23:37:05.251Z. Seven independent one-in-86-million coincidences is not what happened; a migration carrying calendar dates across is. The gate now refuses them, and the company’s longest-open figure drops from 13 years to 10.5 — still a long time, but a duration Lever’s own clock will stand behind.

A listing that disappears#

Closure is inferred, not reported: a listing that stops appearing in its board’s feed is closed. That works only because each board hands over its whole listing set at once, and it is scoped per company so that one failing board cannot mass-close somebody else’s history.

UPDATE jobs SET closed = 1, closed_ts = @at
 WHERE company_slug = @slug AND closed = 0 AND last_seen < @at

That one statement is the most dangerous in the codebase, because the failure mode is silent and destroys history rather than merely being wrong. It has three guards in front of it:

• A failed fetch never reconciles. An empty result from a broken endpoint is indistinguishable from “this company closed every role”, so a non-ok fetch returns before reconciliation is even considered.
• A mass-closure guard. If a sweep would close more than 60% of a board of at least ten listings, it refuses — but only on first sighting, and only for six hours. A company really can wind down; Marqeta’s board genuinely went to zero. A guard that never relents keeps their listings open forever, which is the same lie in the other direction.
• A registry retirement guard. Removing companies whose entries have vanished from the registry throws rather than proceeds if that would retire more than a fifth of the registry at once.

The second guard exists because of an incident worth its own paragraph. A Greenhouse token can return HTTP 200, with real jobs and real timestamps, and belong to somebody else entirely. wise served an unnamed US insurance field-sales outfit posting “Supplemental Sales Agent” roles, and all eighteen were being published under Wise’s name and Wise’s sector. runway serves cfo.ai rather than the video-generation lab. Both boards were live. Both had timestamps. n > 0 is not a check.

And removing the bad board did not remove the bad jobs: the registry sync upserts and never removes, so nothing swept those eighteen listings and nothing ever reconciled them closed. They stayed live under Wise’s name after the board was gone. Deleting the cause did not delete the effect, which is a good general thing to be suspicious of.

Being a good guest on somebody else’s server#

Every one of these endpoints is public and none of them owe us anything. The client identifies itself honestly — fable12-jobs-indexer (+https://fable12.com), no browser cosplay — retries twice with exponential backoff and jitter, and honours Retry-After on a 429 because the host has just said when to come back.

The subtler lesson is about how you express politeness. The natural implementation is “each worker sleeps D milliseconds between requests”. That is not a rate:

A rate, not a per-worker sleep. The difference matters and cost us a board: with N workers each sleeping D between requests, the real rate is N / (D + latency), which nobody writes down and which drifts with the network. One tenant documented as “1.8 req/s” was measured at 2.7.

packages/corpus/src/sources/index.ts

So there is a Pacer that hands out slots at a fixed measured rate. And then two failure modes that only appear once you have one:

Two tenants, one edge

Netflix, NVIDIA and Qualcomm all sit behind the same CloudFront distribution. Each was individually polite and neither was at fault, but they add up at the edge. NVIDIA and Qualcomm each completed cleanly alone and both came back blocked on the first production sweep that ran them together. Every request now waits for two slots — its tenant’s and its edge’s.

Two passes, one employer

Descriptions are fetched separately from listings, because asking Greenhouse for description content on the sweep path takes Stripe’s board from 360KB to 4.4MB — 12×, across 191 boards, 48 times a day, to re-fetch prose that did not change. So there are two loops: a sweep every 30 minutes and a description pass every 10. Each paced against a rate measured safe alone. Their clocks guarantee they will meet.

NVIDIA’s Eightfold pod took 0.9 req/s from a sweep and 0.45 from a description pass without complaint, and refused 1.2 at request 73.

packages/corpus/src/inflight.ts

The sum of two safe rates is not a safe rate, and nothing in a scheduler tells you that. It needed a shared in-flight registry that both passes consult.

Where the query time went, three times#

This is a synchronous better-sqlite3 app running as a single fork process, which changes how you read a slow query. It is not latency in the abstract — it is the event loop blocked, with every other visitor queued behind it.

Act one: the database had never been analysed. Without statistics the planner costs every index the same and picked a non-covering one for the facet counts — 65,000 row lookups to answer a sixteen-row GROUP BY. Adding partial indexes and running ANALYZE:

Act two: the shape was still wrong. These aggregates only change when a sweep completes, so recomputing them per visitor is work nobody asked for. They moved into a single-row cache table written once per sweep:

About 71ms of homepage aggregate work reduced to under one. The sweeper now pays 75ms once every thirty minutes instead.

Act three: descriptions made everything else slower. Storing description prose inline in the jobs table took the average row from a few hundred bytes to about 6KB, and SQLite must read whole pages to count rows even when the count never references the column:

The cost is not the search index and not the transport. It is the twenty-millisecond jump from matching 32,000 rows to touching them. The fix is to move descriptions to a side table, and it has deliberately not been done: that is a schema migration on a 400MB database that the sweeper is actively writing and that is three-quarters through its first backfill. It is written down with the numbers that justify it rather than performed at the end of a long day on a system that is currently healthy.

One SQLite rule worth carrying away#

The listing view collapses duplicate roles — the same title at the same employer across eight offices becomes one row. Doing that in a single pass relies on a SQLite guarantee that is real but narrower than people assume: the bare columns of a GROUP BY come from the row that supplied the MIN or MAX. It lets you fetch the representative row without a window function or a second query per group.

The narrow part:

SQLite’s bare-column rule — that the plain j.* columns come from the row which supplied the min or max — only holds while there is a single such aggregate in the statement. Asking for MIN and MAX together leaves which row you get undefined, and the representative listing would then be whichever one the planner happened to leave in the register.

packages/corpus/src/queries.ts

So the query picks one, and which one depends on what the reader asked for: const pick = sort === 'oldest' ? 'MIN' : 'MAX'. The other end of the group comes from a sibling query that has to run anyway. A bug here would not throw; it would quietly show the wrong job.

A second one from the same file, because it is the kind of thing that only shows up when measured: IN and EXISTS return identical rows here and the planner treats them completely differently. EXISTS drives from jobs and probes the country table once per candidate row; IN builds the country’s job list once from a covering index and drives from that. On a country filter over 68,000 listings: 54ms against 3.9ms.

Versioning a cache by shape, not by time#

The aggregate cache is one JSON blob in one row. That invited a specific bug three separate times: a payload written by yesterday’s build, read by today’s, missing a field the page now renders. A missing array is not a rendering bug that shows up in review — it is a 500 on a page nobody touched.

So the payload carries a shape version, and a payload stamped with an older one is simply not used; everything falls back to computing live until the next sweep. What makes it work in practice is the discipline about when to bump it. Adding a new optional field does not require a bump, because every reader already falls back when it is absent — and bumping discards every cached aggregate the instant the new build starts, which on a live box means the whole site computing market-wide figures per request until a sweep finishes. A field that can be missing costs one ?? and none of that.

Serving it#

The web app opens the same SQLite file read-only, in-process, through a single shared connection. There is nothing to pool: better-sqlite3 is synchronous and reads are sub-millisecond. The read handle sets query_only at the connection level, which exists because of a real near-miss — the web app used to call the same openDb the sweeper does, so every web process executed CREATE TABLE against production at boot. A rolled-back deploy could therefore re-create something a migration had just dropped. Readers have no business writing DDL, so now they cannot.

Almost every route is force-dynamic, for a reason specific to this data: they aggregate over the whole corpus, so any staleness surfaces as a headline number that disagrees with the list underneath it. The exceptions are the two routes that render a single row — job and company pages — which are cached for thirty minutes, the same interval the sweeper runs at, so the cache is never more wrong than the data.

Keeping it#

Three pm2 processes on one box behind Caddy: the sweeper, the Next app, and an MCP server. Litestream replicates the SQLite file continuously to Cloudflare R2 every ten minutes, and a nightly cron takes a self-contained snapshot. Those are deliberately different mechanisms — Litestream would faithfully replicate corruption, and the snapshots are opened and row-counted before anything is pruned.

The backup story has the best lesson in the repository. It ran nightly for weeks and wrote nothing.

The crontab sourced an env file before the script. That file did not exist, and . is a POSIX special builtin — when one fails, a non-interactive shell exits on the spot. So the shell died on the first command and never reached the backup, while 2>/dev/null swallowed the reason. The log stayed zero bytes, which reads as “nothing to report” rather than “this has never once run”.

commit a6b9496

An empty log is not evidence of success. The backup script now verifies the gzip, opens the snapshot, counts open listings, fails if there are fewer than a thousand, and only then prunes the previous one — and the R2 upload is read back and byte-compared, because an upload nobody checked is a hope.

The deploy runbook has a similar flavour. It was written down wrong, and I found out by following it: npm ci removes and recreates node_modules underneath the running processes, which kills them, and pm2 restarts them straight into freshly pulled code against an unmigrated database. The processes must be stopped first. Also, non-obviously, npm run build is what actually applies migrations — the root layout renders a freshness stamp, so a build opens the corpus.

What generalises#

Very little of this is about job boards. The parts I would carry to anything that ingests somebody else’s data:

• Decide what single claim you are making, then let it decide the architecture. “The date is the employer’s” is why there is a database, why Workday is absent, why 81 Apple rows are dropped, and why partial indexes are the right shape.
• Liveness is not identity. A 200 with plausible data can be the wrong organisation entirely. Verify what a source is, not merely that it answered.
• Absence is not a signal until you have ruled out failure. Every inference from missing data needs a guard between it and anything destructive.
• Two individually safe rates are not a safe rate. Politeness composes badly, and it composes across process boundaries you did not think of as shared.
• Silence is the most dangerous status. An empty log, a zero-byte file, a job that exits 0 having done nothing.
• A wrong number is worse than a missing one. Every fix above trades coverage for trust, and on a site whose whole proposition is a number, that trade is never close.

The corpus is swept every thirty minutes; the footer of every page carries when the boards were last read. You can see what it currently believes at the market page, read the rules it enforces at how we know, or go straight to the roles that have been open longest — which is, after all, the number all of this exists to protect.