Autoarhiiv.ee

What didn’t work: a hand-picked speed for each source. Those numbers were really guesses: too cautious for one source, too fast for another. When a source’s servers changed, the numbers went stale and nobody noticed.

How it works now: each source’s pace adjusts itself, on the same principle an internet connection uses to choose its speed. When responses come back fast and clean, collection speeds up step by step. When a source signals load, the system backs off sharply straight away. Each source finds its own comfortable speed and my guesses aren’t needed.

Everything runs at 3 a.m.: all sources, the photo check, database maintenance and finally a sync to the web server over an encrypted connection. For older history, before the archive itself started, I used public web archives.

What else didn’t work: one night, 4,636 of one marketplace’s 5,838 listings were marked removed at once. The site hadn’t emptied. A couple of network errors in the middle of walking the pages looked exactly like “the list has ended”, and everything the walk never reached seemed to be gone. Now a failed request is told apart from a genuinely empty page. After one retry the whole walk is abandoned and no removals are done that night, because you can’t tell what has disappeared from half a list.

A second bug with the same shape was even bigger. The quick-update comparison took the “on sale now” list from one marketplace only, but the “active here” list from all of them. Every night it marked every other marketplace’s listings as removed, 96,900 in total, and the next night they were rediscovered as “new”. The fix came with a recovery: a listing removed by mistake turns active again when it shows up on sale, and no invented price change goes into the history because of it.

The same logic decided where speed may be gained. When a whole page of a newest-first list is already known, the walk could stop. But detecting removals needs the whole list, so that shortcut is only allowed where removals aren’t worked out from the same walk. On one such marketplace that meant 8 pages and under a minute instead of 2,280 pages and over two hours.

The link between plate and VIN is looked up in public lookup services. Every attempt is recorded: if there’s definitely no answer it isn’t asked again, but after a connection error it’s retried later. That way the services don’t get the same question every night.

The first three characters of a VIN identify the manufacturer. The system checks that they match the make stated in the listing, and it knows that Cupra and Seat, Dacia and Renault or Lexus and Toyota belong to the same family. If the VIN and the make disagree, neither is trusted.

The most important rule, though, is that the two kinds of mistake aren’t equal. If the system fails to merge two listings of the same car, that can always be fixed later. If it merges two different cars, one car’s history is corrupted and that’s very hard to spot. So the bar for merging is deliberately high.

In that case the plate is read from the listing photos. A detection model (YOLO) first finds where the plate is in the picture, then text recognition (OCR) reads its characters. Both models are small and run on an ordinary processor.

A single photo can be wrong: a reflection, a bad angle, half the plate hidden. So the acceptance rule is strict. A result counts if one photo is very confident (at least 85%) or if at least two different photos arrive at the same plate. One uncertain reading changes nothing in the database.

That plate read from a photo is often the only evidence that two listings on different portals are the same car. When the text had no identifier, two rows were added, and only the photo showed they belong together.

What didn’t work: the confidence bar started at 50%. I tested it on 500 cars whose real plate was already known. About one accepted read in six was wrong, some at 97% confidence: a sign in the frame or another car’s plate. At 85% the share of cars with a wrong accepted read fell from 3.6% to 0.8%.

Later a new error appeared that a random sample hadn’t shown. One dealer’s photos carried a repeated sticker or watermark such as “100PRE”, and text recognition read it as a plate at 83–99% confidence. On the 200 most recent cars, precision was only 40%. The fix was a second reader, trained only on number plates, that looks at the same crop again. It rejected all six wrong reads and confirmed all four right ones. Together they scored 7 out of 7 on the same sample.

The third lesson was about foreign plates. At first I took ABC123-style reads for mistakes, because the archive held almost no plates like that. They were in fact cars imported from Finland, still on the dealer’s lot with Finnish plates. The crop showed the blue EU band reading FIN. Such a plate is the car’s previous one and isn’t in the Estonian register, but it’s correct and helps recognise the same car.

Speed needed a look too. A fallback that read text from the whole photo produced about 5% of the results but took about 80% of the time and 8 GB of memory. With it off by default, processing went from 3.6 to 30 cars a minute, an 8,335-car backlog from 38 hours to 4.6, and memory use down to 0.24 GB.

What didn’t work: treating the plate as a permanent identifier. When the same number showed up on a new car, the new car “stole” it from the old one, and the old car’s history was left without a plate, so search could no longer find it.

How it works now: plates have their own log, which is only ever added to, never deleted from. Each car shows which plate it had and when. When the new car’s VIN turns up, the system repairs the link on its own.

The data also showed how risky merging by plate would be: in about 10% of cases where two rows shared a plate, their VINs contradicted each other, for example a Mercedes C 200 and a Nissan Qashqai. So a shared plate is now only a hint. Merges are decided by details that can’t move between cars.

The mistake was in the source’s own data, but my system made it worse. Every time the listing was processed again, the wrong VIN took the plate away from the Porsche and the system created a second Porsche. The database now held a car that didn’t exist.

The fix had two parts. First, a VIN’s manufacturer code is checked before it’s trusted at all: a Volvo code on a Porsche listing is rejected. Second, a plate moving to another car can now only weaken the old link, never delete it. If something goes wrong, it can always be reversed.

A similar case came up when two Mercedes rows were merged on a shared VIN. The merge itself was right, but the surviving row kept the older description, which was wrong. Now the official registry settles that kind of disagreement: a field is only changed when the registry confirms one value and contradicts the other.

What happened: the same car showed up on four portals within 80 minutes with the wrong price and was corrected the next day. The same thing happened with two Škoda Enyaqs: 99,000 € instead of 29,990 €. The old logic would have recorded that as a price drop and put the car at the top of the “biggest price drops” list.

My first thought was that I’d misread the data. I hadn’t: one of the sources provides the price in machine-readable form and it said 99,000 there too. Most likely the mistake came from dealer software that sends listings to several portals at once.

How it works now: a new car’s first price isn’t published straight away if it doesn’t fit any confirmed price for the same model. If there are too few comparable cars, the system decides nothing and labels nothing as wrong. I also checked real cases: the fabricated prices were 2.8 to 9 times the real ones, while the largest genuine same-day price cut was 1.25 times. With a gap that big, a bad price and a real discount are easy to tell apart.

Placeholders belong here too. A price of 1, 3, 11 or 21 € really means “make an offer” and never becomes the car’s headline price. Real minimum prices start at 51 €. For auctions only the final price counts, never the running bids.

What else didn’t work: one marketplace’s old price reader stored a three-digit price as its first digit only: a €399 car was priced at €3. The reader had been fixed long before, but the broken rows were still there and on the site. Luckily the whole number survived in another field, 899 next to 8, for example. I only repaired a value when it was a single digit equal to the first digit of that other field. That fixed 473 values without guessing anything. The same rule would have done harm on the auction start-price field: a €1 opening bid and a €131 final price pass the test by pure coincidence. So that field was left alone.

Suspicious prices are also checked against an archived copy of the ad. On the first run, 80% came back as “no evidence”. That looked like a clean bill of health, but I had actually asked for a copy at the exact moment the price was saved, and there usually wasn’t one. Now the nearest existing copy is used. A price is only called wrong when that copy is at most 7 days away. With a more distant copy the seller may really have changed the price in between.

That led to a rule: no evidence, no data. 622 cars with no listing or link behind them were deleted along with their prices, and so were 255 price points no listing confirmed. Prices read during our own collection stay, because they’re the only record of what an ad asked in between.

This really happened: a dealer put the same car up as two separate listings. The system tracked the last price per car, not per listing, so the two listings “fought” over the price every night and made-up price changes piled up in the history. The fix was small but important: the last price is now tracked for each listing separately.

Merging has another trap. When two rows turn out to be one car, the value isn’t the plate (that was already known), it’s everything hanging off the second row: its listings and above all its old prices. Two portals can describe the same car 17 minutes apart, so neither knows about the other. A merge moves the whole history onto one row and the old link redirects to the new one.

By default the merge tool does nothing and only shows what it would do. A real run takes a backup first and writes every merge to a log before applying it, so any bad merge can be traced. Later it turned out some price drops were missing from rows merged earlier, and a separate script restored them.

There were two causes. Sellers often publish the text first and add photos hours or days later. If the system reached the listing in between, the car was left without photos for good. Second, on some portals photo addresses change every time the seller edits the gallery, and the old links vanish immediately. I measured it: on one portal 13% of active and 97% of removed listings had a broken cover photo. On another, photos of active listings worked 100% of the time and removed ones 93%. It depends on the source, not on how old the photo is.

The case that started this: a 2024 VW Passat was listed on two portals. The site preferred the newer listing, and 11 of its 28 photos, including the cover, were broken. Visitors saw an empty placeholder even though the other portal’s working gallery was right there.

How it works now: a nightly check goes over the photos, asks the source for new ones when needed and writes back only when the new set is demonstrably better. The rule is simple: a good photo is never replaced with nothing. An empty response, a timeout or a redesigned source can’t delete a single photo. A link is only dropped when the image server itself says it’s gone.

Every portal writes the same things its own way. Škoda Octavia alone appeared in 35 different forms. Without cleanup you can’t do statistics or model pages, because one model is split into dozens of pieces.

Each field (make, model, fuel, gearbox, body type) has its own rules that bring the different spellings under one name. That brought the number of make and model combinations down from 3,863 to 2,225. The most important rule is that nothing is guessed. If a value can’t be mapped to one name with certainty, it stays as it was. One untidy row is better than a wrong model.

A case of its own was cars whose ad had no make at all. There were 11. The first three characters of a VIN identify the manufacturer, and the database already held other cars with the same code and a known make. VR7, for example, was on 484 cars, and every one was a Citroën. That identified 8 cars from the database itself. One trap was SsangYong: the company renamed itself KGM in 2023 but kept the manufacturer code. Linking the code to only one name would have made the system treat every new KGM’s VIN as a contradiction and delete it. The last two were a Bolloré Bluecar (the former Paris rental electric car) and a Compass touring caravan, which went with about 240 other non-cars into their own group. Out of 121,371 cars, one is still unidentified.

The VIN column had junk too. A VIN never contains the letters I, O or Q, because they’re too easy to confuse with 1 and 0. A VIN with one of them is a transcription error with a known correction. On codes that carry a check digit, the correction held in six cases out of six. That repaired 37 codes. 25 values weren’t VINs at all, such as an ad phrase meaning “with lowering springs” or 12345678912345679, and they were deleted.

I measured before changing anything. The main table was 460 MB, and 262 MB of that was large data fields (photo lists and similar) stored before the ordinary fields. For about two thirds of the rows, a row didn’t fit on one database page. Every search had to read far more from disk than it actually needed.

How it works now: the database doesn’t change between nightly updates. So every night a background job makes a separate, simplified copy just for reading, with only the fields search needs: 32.7 MB instead of 460 MB. It fits entirely in memory.

The result: search in 0.085 seconds instead of 0.411 (4.8 times faster), per-make statistics in 0.119 seconds instead of 0.889 (7.5 times faster), depreciation 6.1 times faster and the suspicious-mileage search 3.3 times faster. Page 1,060 of the list opens as fast as the first page, in about 7 milliseconds.

The system compares the same car’s data at different times and flags it when the mileage has dropped by more than 5,000 km. Most of these aren’t fraud: the seller typed 10,100 instead of 102,000 and fixed it the next day. So flagged active listings are checked again later, and corrected ones drop off the list.

The page says so honestly too: most flags are typos, not fraud. The list is a hint of what to check, not an accusation.

What didn’t work: the threshold has changed three times. At 50 km the list filled up with rounding. At 500 km it still caught ordinary noise between listings, where the seller rounds or reads the odometer at a different time. At 5,000 km what remains is worth a real look.

The archive needs a car’s history, not people’s details. So e-mail addresses, phone numbers and private sellers’ names are removed from the ads already during collection, and the same was done to everything stored earlier. A private seller’s name simply becomes “Private seller”.

Numbers are the hardest part. “Fiat 500 2014” and an Estonian mobile number look alike. So a number isn’t treated as a phone number if it’s a year, if letters are attached to it (a VIN or a part number) or if a unit follows it. It is still treated as a phone number if a word like “tel” comes before it or a language note after it (“53269898 EST RUS”), or if it’s deliberately disguised, such as “5648085one”. Everything that writes or shows the data uses the same rule, so they can’t drift apart.

Removing names took away something one feature relied on, though: did the car change hands in between? Now each car only keeps a seller-group number, which says nothing except whether two listings come from the same seller. It can’t identify a person or link the sellers of two different cars. “New seller” still shows on the timeline and the price chart.

A photographer once asked for their photos of one car to be taken down. The archive doesn’t store photos, it shows the marketplace’s image link, so the link had to go. Deleting it once wouldn’t have held, because ten collectors and the nightly photo check would have brought it back the next night. Now there’s a permanent block list, applied both at the end of the nightly processing and right before sending data to the website. If it can’t be applied, nothing is sent that night. The website checks the list too, so the photos don’t reappear even if a step is skipped.

Every night a check looks for impossible situations: a price no listing confirms, a headline that matches no listing, or a VIN that puts the car in a different year from the ad. It fixes nothing by itself. It only reports new findings, not the whole old list, so a new bug stands out at once.

What didn’t work: the first run found 976 problems. Most of them were bugs in the check, not in the data. 392 “wrong headline” findings were actually repairs that had just made the headline right. Of 475 year conflicts, 216 were exactly two years apart and almost all new stock: the VIN gives the model year, the ad the first registration, and an unsold car is often two model years old when it’s first registered.

The most interesting case was Mazda. The tenth character of a VIN gives the model year because a US rule requires it, and that rule only binds cars built for the US market. I measured, for each manufacturer code, how often that character agreed with the ad’s year. Most agreed 98–100% of the time, but European-market Mazda codes only 0–58%. US-market Mazdas agreed 100%, exactly as the rule predicts. A Mazda 2 whose VIN “says” 1992 is a model that started production in 2002. The year was right; decoding the VIN was simply meaningless for that car. Those codes are now left out of the year check.

Once the check itself was fixed, 976 findings became 268, and those were real data errors that could be fixed one by one.