Every job search I have seen, including my own, starts the same way: a dozen browser tabs, a spreadsheet with a status column, and a growing suspicion that you have seen this posting before under a slightly different title.
That is a data problem. There are multiple sources with different schemas. There are duplicates across them. Records disappear without notice. There is state you own (what you applied to, what you sent, what happened next) mixed in with state you do not (the postings). And there is a ranking question on top that depends on criteria you will change your mind about.
So I built a warehouse for it. Job-Hunter-Flow ingests openings from permitted public job board APIs every four hours, lands them raw in Postgres, transforms them with dbt into a tested and deduplicated model, and serves the result through a FastAPI app with a small HTMX UI. This post is about the design decisions that made it pleasant to live with, most of which apply to warehouses that have nothing to do with job hunting.
Python stays dumb#
The first decision was the most important one: no source-specific field ever reaches Python.
Every source, however exotic, yields the same record: an identity, a timestamp, and the provider's untouched JSON. That lands in a single JSONB column via dlt. Ingestion code does not know what a "title" or "salary" is. It knows how to page, how to back off, and how to hand over bytes.
The payoff is that a provider reshaping its response cannot break ingestion. It can only break the transform, and that is exactly where I want it to break.
dbt does the shaping. Each source owns one staging model that unpacks its own JSONB into a shared column contract, declared with enforced: true. If a provider renames a field, dbt build fails on that one model, with that one source's name on it, instead of the mart quietly filling with nulls.
The contract is written in three places: the Python record type, a dbt macro, and the enforced model schemas. Three copies of anything will drift, so a unit test asserts all three agree. That test runs offline in CI, which means drift is caught on the commit, not at 4am when the schedule fires.
A source is a directory#
Adding a source is the operation I expected to do most often, so I wanted it boring. A source is a directory with three files:
config.yaml: filters, rate limit, page cap, and a kill switch.source.py: a function that returns something with afetch(window)method.README.md: the source's terms-of-service posture, written down.
A registry discovers the directories. The Dagster asset factory builds one asset per entry. The API's /sources endpoint and the UI's filter options read the same registry. None of those three places names a source, so the UI cannot offer a filter for a source the pipeline no longer runs.
There is exactly one manual step: adding the new staging model to a list in dbt_project.yml so it joins the union. I could not find a clean way to make dbt discover it, so I made forgetting it loud instead. A macro asserts the union is complete and fails the build naming the missing model, and the offline contract test catches it before that.
Politeness, enforced rather than promised#
Every source goes through one HTTP client. It has a token-bucket rate limiter, a hard page cap, exponential backoff that honours Retry-After, and a RateLimitedError that fails the run rather than retrying harder.
The rule I like most is that a source's config can only make itself more conservative. The effective rate limit is the minimum of the source's value and the global budget. A typo in one YAML file cannot turn the pipeline into a scraper hammering someone's API. That is unit-tested, because it is the kind of property that erodes when nobody is looking.
Blocked is not broken#
One aggregator I wanted serves a bot challenge to every automated client, on every path. The only honest response is to not try to get past it.
What I did instead was leave the source switched on. Every run attempts it, records the attempt with status='blocked', and the ops page shows "blocked by provider" next to the live sources. The day access opens, it starts ingesting with nothing to switch on.
The key detail is that a blocked source does not fail the pipeline. A provider deciding who may read their site is not a fault in my system. If I treated it as one, the main job would go red every four hours forever. A pipeline that is always red teaches you to ignore red, and that is how a real outage goes unnoticed. So a ProviderBlockedError is distinct from an HTTP error, and the asset check for it is a warning, not a blocking failure.
Checks that block, and checks that only warn#
Every scheduled run evaluates a set of Dagster asset checks beside the dbt tests:
| Check | Severity | Catches |
|---|---|---|
| raw table has rows | blocking | a "successful" load that wrote nothing |
| raw rows not lost | blocking | a merge-upserted table that shrank |
| payload is a JSON object | blocking | payloads staging would read as all-null |
| raw reaches the fact table | blocking | fewer than 98% of raw rows arriving downstream |
| ingest not blocked | warning | a provider refusing us |
Blocking checks stop the downstream dbt build, so bad raw data never reaches a mart. Freshness policies on the raw and served tables warn at five hours and fail at nine. Those are the only thing that notices when the schedule itself has stopped, which is the failure mode every scheduled system eventually has.
Deduplication with a hashed natural key#
The same job shows up on its company's applicant tracking system and on an aggregator. I collapse them with job_key = sha256(normalized company | title | location).
There are two grains. fct_job_posting keeps every posting, at the grain of source plus source id. dim_job keeps one row per real job. dim_job being smaller than the fact table is the visible proof the dedup is working.
When sources disagree about a field, the one closest to the origin wins. An applicant tracking system is one step from the company; an aggregator is two.
Schema ownership is the whole game#
This is the decision I would carry to any warehouse.
| Schema | Owner | Rebuildable? |
|---|---|---|
raw | dlt | yes: drop it and re-ingest |
staging, marts | dbt | yes: dbt build --full-refresh |
app | Alembic | no |
The app schema holds applications and their status history, notes, saved searches, tags, the profile, uploaded documents with their original bytes, and every tailoring run. Nothing can regenerate any of it. So no pipeline command is allowed to destroy it, and the only command that does (make nuke) asks first.
I got this wrong in the first version. dbt pre-joined application status into a prospect mart. That was fine while the UI was read-only. It was wrong the moment the board became interactive: the mart is a table rebuilt every four hours, so a card I had just dragged to a new column kept showing its old status until the next run.
The fix was to move that join into the API's read layer, where it runs per request. It costs nothing at this scale (a few hundred personal rows against a couple of thousand postings) and it is current by construction. The useful side effect was that dbt stopped referencing the app schema entirely. The rebuildable world and the irreplaceable world no longer touch.
One consequence I had to handle on purpose: a posting counts as "active" if it is fresh or if I engaged with it. Without that, the staleness cutoff would hide exactly the jobs I invested in. A company pulls the ad two weeks after you tailored a resume for it, and it silently vanishes along with your notes.
Ranking never hides anything#
The discover list is ranked against criteria I can edit on a profile page. The score is computed on every request from those values, so changing one re-ranks the list immediately. Nothing is stored per posting and there is no refresh job.
I learned two things tuning it against real data rather than in the abstract.
Title has to outweigh company. With company preference weighted higher, a recruiting role at a company I rated well outscored an actual data engineering role elsewhere. Company answers "would I want to work there". Title answers "is this even my job". The second question comes first.
Unknown is zero, never negative. Most postings publish no salary. Penalising silence would bury exactly the employers who publish least.
Ranking and exclusion are different operations, and I kept them separate. Ranking never hides a posting; a match on nothing just sorts to the bottom. Exclusion rules do drop rows, so whatever they remove is always counted, with a one-click reveal: "N hidden by your exclusion rules, show them". An exclusion you cannot see is indistinguishable from a bug.
The exclusion matcher also strips negations before it tests for a requirement, because "no X required" contains the string "X required". Without that, a perfectly good posting gets deleted for saying the opposite of what the rule looks for. There are tests in both directions.
The LLM part, with the doors closed#
The feature people ask about is resume tailoring: send a profile and a posting to Claude, get back a structured result with a fit score, matched and missing skills, likely interview gaps, a tailored draft and a list of what changed.
Two rules made it something I was willing to use.
No tools, ever. The prompt contains a job description scraped from a public website, which is attacker-controllable text going straight into a model. The model runs with no tools, no settings sources, no MCP servers, a minimal environment without database credentials, and an empty working directory. The posting is fenced and labelled untrusted, and the output is validated against a JSON Schema before anything is stored. There is a regression test that feeds a real prompt-injection string through the prompt builder.
No invented experience. The model's whole task is to make you look like a match, which is precisely the pressure that produces fabrication. The system prompt allows rephrasing, reordering and re-emphasis, and forbids new employers, dates, titles, metrics, degrees or technologies. Then a deterministic check compares every figure (with its unit), technology and proper noun in the draft against the master document and flags anything that is new. It does not block. You are allowed to add something true that the master omits, but you have to see it, and the honest fix is to update the master.
The UI shows what the model says it changed next to a line diff of what actually changed, and a generated document is never marked as submitted automatically.
What I would reuse#
Strip away the subject matter and the pattern is general:
- Land raw payloads untouched and do all shaping in the transform layer, behind enforced contracts.
- Make the thing you add most often a directory, and make the one manual step fail loudly when forgotten.
- Separate data you can rebuild from data you cannot, give them different owners, and do not let one reference the other.
- Distinguish "the world said no" from "my system broke", or your alerts will train you to ignore them.
- Make every hidden row countable.
None of that is specific to job postings. It is just easier to see when the warehouse is small enough to hold in your head.