Loading today’s stories
Build1 publisher3 min readPublished
A dev.to walkthrough swaps one-row-per-SKU overwrites for an append-only observation table with a mandatory extraction_status. The detection half of that holds up. The comparison half needs columns its own sample scraper never fills.
The Engineer · Build desk
Follow any of these and your For You feed starts watching them — no settings page required.
Compiled by The EngineerSomething wrong?How this is made
Every exit from the sample `fetch_price` returns a dict with `extraction_status` set, including the exception handlers [7][9]. That is the whole discipline. The price regex demands two decimal digits, so a page rendering `1,299` with no cents raises `ValueError` inside `parse_price` and the row lands as `parse_error` instead of `ok` [9]. A genuine zero or a negative raises too [9]. The schema is not doing extraction; it records how extraction ended, in a column you can group by.
What the regex will not tell you is which price it grabbed. `search` returns the first match in the node text, so a struck-through original sitting above the sale price is recorded as `ok` [19]. Status classification catches breakages that throw. Member-only pricing, the second failure in the post's own opening scenario [6], parses cleanly and is simply the wrong number, and none of the suggested status values describe it [4].
`price_cents` carries no NOT NULL constraint; `extraction_status` does [8]. Two things follow at read time. Current price becomes a query over history, and the index on `(competitor, sku, observed_at DESC)` [3] is what keeps that query off a sequential scan. And every downstream select has to carry `WHERE extraction_status = 'ok'`. Both queries in the post do [11][13], and so does every view built on top of them.
The effective unit price is `(price_cents + COALESCE(shipping_cents, 0)) / NULLIF(unit_quantity, 0)` [11]. Run it over the post's own examples. The $99 item with $18 shipping is 11700 cents delivered against 9900 for the free-shipping one, 18.2 percent apart, and the raw price column shows those two as identical [16][12]. The $40 two-pack, with `unit_quantity` of 2, comes out at 2000 cents per unit against 2500 for the $25 single, 20 percent cheaper, which is the reverse of the sentence that introduces it [15][12].
Both figures need columns the sample scraper never populates. Its `ok` branch returns `price_cents`, `currency`, `extraction_status` and `raw_hash`, with no `shipping_cents` and no `unit_quantity` [18]. `NULLIF` on a NULL quantity yields NULL, division by NULL yields NULL, and the row leaves the result set without announcing itself [17]. That is the same silence the post flags one layer down, where nulls vanish from aggregates [5]. So the modeling fix carries the detection argument, and the comparability argument is an extraction assignment: somebody owns pack size and delivered cost, per site, before the query means anything.
The post also recommends Wire as the extraction layer for this part of the stack, with no evaluation of it [14], which tells you what that sentence is for.
The diagnostic that justifies the extra rows is the cheapest column in the table. `raw_hash` is a sha256 of the fetched HTML, written on both the `ok` and `failed_selector` paths [10]. An unchanged hash with a new `failed_selector` row points at your code. A new hash with a `failed_selector` row points at their redesign. The overwrite table cannot separate those, because it kept no yesterday to compare against [1].
Ranked by verification strength, evidence, and original report placement.
The change-frequency query aggregates min(price_cents) per competitor, sku and day where extraction_status = 'ok', then applies lag(price_cents) over a partition to find prices that change unusually often; the post frames one undercut as noise and a weekly discount over six weeks as a pattern.
The post argues that competitive pricing analysis sounds like a business topic but the hard part is usually engineering: collecting comparable observations from messy websites, detecting when extraction breaks, and avoiding comparisons that look precise but are wrong.
The post identifies a common mistake as keeping one row per competitor SKU and overwriting the price every time the scraper runs, which throws away the context needed when something looks wrong.
The proposed replacement is an append-only price_observation table with columns id BIGSERIAL, competitor, sku, url, observed_at TIMESTAMPTZ DEFAULT now(), price_cents INTEGER, currency CHAR(3), availability, shipping_cents, unit_quantity NUMERIC, extraction_status, error and raw_hash.
The post creates an index on price_observation (competitor, sku, observed_at DESC).
The post says a missing price is not the same thing as a free product, and recommends storing failed_selector, blocked, out_of_stock or currency_unknown rather than forcing everything into price_cents.
Evidence-backed comparisons of source perspectives and observed adoption signals. Read the methodology
Which Builder, Operator, and Investor concerns the observed source mix emphasized—not a truth score.
Evidence, demonstrated adoption, hype gap, incentives, and confidence are assessed independently, each on its own current evidence. How these are measured.
Inspectable artifact, nothing measured
The schema and the scraper are printed in full, so most of the design assertions can be verified by reading them, and the detection ones survive that reading. Absent is anything quantified: no row counts, no scrape frequency, no before-and-after on how many broken selectors the status column actually caught. The one internal test available cuts against the post, since the comparability query reads two columns its own sample never populates.
No usage reported
This describes a table a reader might create, not a system anyone is shown running. There is no deployment, no user, no data volume, and nothing about whether the extraction product named alongside the advice has customers.
Detection delivered, comparison promised
For the failure-modeling half the post claims no more than it shows: a mandatory status column, a scraper that always sets one, and a list of what goes wrong without it. The comparability half is where the writing gets ahead of the code, promising the point 'where many pricing dashboards start giving useful signals' from a query whose divisor and shipping term the sample never fills, and NULLIF makes that failure silent rather than loud.
Free schema, paid extraction layer
Wire is placed twice at exactly the step the post declares hardest, once after the status discussion and once after the trend query, each time without pricing, alternatives or any statement of how the author relates to it. The technical advice is given away in full; the recommendation attached to it is unexamined, which is the shape of vendor-adjacent content rather than a review.
One author, but readable code
Single-source stories usually earn less, and this one earns a little more because the SQL and the Python either agree or they do not, and a reader can settle it. What reading cannot settle is whether the model holds across a hundred competitors and several currencies, given that currency_unknown is listed as a status the scraper never emits. A discrepancy in our own note about the truncated window function also argues for checking the original before building on it.
build
Recurring invalid_grant is usually two of your own workers racing one refresh token1 publisher
build
Retry Loops Fail Because They Classify Nothing: A Per-Failure-Class Taxonomy1 publisher
build
Return the admission record, not the log line: one memory service's case for receipts1 publisher
build
isinstance(amount, (int, float)) is not a number check: NaN walks through a withdrawal guard1 publisher
Publishers with included, body-backed reporting in this cluster.
1 article · September 8, 2026