jSrSSKrSSKrSSKrSSKrSSKrSSKJr SSKJ r \RR\RR\RR\RR\555S5r\RR\S5rSr\4S \S \R&4S jjr\4S \S S4S jjrGSRS jrSrGSRSjrS \4SjrS \4Sjr\4S \S S4SjjrS \4SjrSrS\S\S \4SjrGSRSjr\4S\S\S \S S4Sjjr \S4S \S \!4Sjjr"S\4S\S \S \#4Sjjr$S\4S\S\%S \S \&4Sjjr'S\4S\S\%S \S \#4S jjr(\4S!\S \S S4S"jjr)\4S \S \#4S#jjr*\4S \S \4S$jjr+1S%kr,S&r-S'r.S(r/S)r0S \%4S*jr1GSSS \24S+jjr3\4S \S \24S,jjr4S \#4S-jr5S \%4S.jr6S \!4S/jr7GSRS0jr8S1\4S \S \&4S2jjr9\4S \S \&4S3jjr:\4S \S S4S4jjr;S5S6S7S8S9S:S;S. rSC\4S \S \#4SDjjr?\4S \S S4SEjjr@S \24SFjrAS\S4S \S S4SGjjrBSHrCSIrD\4S \S S4SJjjrE\4S \S S4SKjjrFSC\S4S \S S4SLjjrGSC\4S \S S4SMjjrH\4S \S S4SNjjrISOSPSQSRSSSTSUSVSWSXSYSZS[S\. rJS]S^S_S`SaSbScSdSeSf. rKSgrLShrMSiSjSk.rNSlrOGSRSmjrPS \#4SnjrQSCSSSSSC\4S \S S4SojjrR\4S \S \&4SpjjrS\4S \S \&4SqjjrT\4S \S \&4SrjjrU\4S \S \&4SsjjrVSC\4S \S S4StjjrWS \&4SujrXSC\4S \S S4SvjjrYS \Z4Swjr[Sxr\S \24Syjr]GSSSzjr^\4S \S \&4S{jjr_SCSCSS\4S \S \#4S|jjr`SCSCS}\4S \S S4S~jjraSC\4S \S S4SjjrbSC\SS4S \S\S\S S4SjjrcSC\4S \S \24Sjjrd\4S \S \e4Sjjrf\4S \S \4SjjrgSC\4S \S S4SjjrhSC\4S \S \4SjjriSSSS.rjSSS.rk\4S \S \24Sjjrl\S4S \S\24SjjrmS\4S \4SjjrnSC\4S \S \%4SjjroSrpSSSS.SSSS.SSSS.SSSS./SSS.SSS.SSS./SSSS.SSS.SSS.SSS.SSSS.////SSS.SSS.SSS./SSS.SSS.SSS./S.rq\4S \S \#4SjjrrSrsSrt\4S \S \#4Sjjru/SQ/SQ/SQ/SQ/SQ/SQ/SQ/SQ/SQ/SQ/SQ/SQ/SQ/SQ/SQ/SQ/SQ/SQ//SQ/SQ/SQ/SQ/SQ/SQ/SQ/SQ/SQ/SQ/SQ/SQ/SQ/SQ/SQ/SQ/SQ/SQ/SQ/SQ/SQ/SQ/SQ/SQ/SQ/S.rv\4S \S \#4Sjjrw\4S \4SjjrxS \4SjryS \4SjrzS \#4Sjr{\4S \S \&4Sjjr|Sr}S\4S \S \#4Sjjr~\4S \S \e4Sjjr\4S \S \&4Sjjr\4S \S \&4Sjjr\4S \S \&4Sjjr\4S \S \&4Sjjr\4S \S \&4Sjjr\4S\%S \S S4Sjjr\4S\%S \S S4Sjjr\4S \S \&4Sjjr\4S \S \&4Sjjr\4S \S \&4Sjjr\4S \S \&4SjjrSSSS\4S\%S \S S4SjjrSSSS\4S\%S \S S4Sjjr\4S \S \%4Sjjr\4S \S \%4Sjjr\4S \S \24Sjjr\4S \S \#4Sjjr\4S\S \S \&4SjjrS\#S \&4SjrSCSCSCSC\S4S \S S4GSjjr\S4S \S S4GSjjr\4S \S S4GSjjr\GS.S \4GSjjrSSS\4S \S \%4GSjjr\4S \S \&4GSjjr\4S \S S4GSjjrGSrS\4S \S \%4GS jjr\4S \S \&4GS jjr\4S \S \%4GS jjr\4S \S S4GS jjr\4S \S \#4GS jjrGS\4S \S \%4GSjjr\4S \S \&4GSjjrSCSSCSCSCSC\S4S \S S4GSjjr\4S \4GSjjr\4S \S \#4GSjjrS\4S \S \&4GSjjr\4S \S \&4GSjjr\4S \4GSjjr\4S \S \e4GSjjr\4S \S \&4GSjjr\4S \S \e4GSjjr\4S \S \&4GSjjr\4S \S \&4GSjjr\4S \S \e4GSjjrS\4S \S \&4GSjjr\4S \S S4GSjjrGSrS \4GS jrS \24GS!jrS \e4GS"jrSSCSCSCSSS\S4 S \S S4GS#jjr\4S \S \&4GS$jjr\S4S \4GS%jjr\4S \S \#4GS&jjr\4S \S \e4GS'jjr\4S \S S4GS(jjr\4S \S \#4GS)jjr\4S \4GS*jjr\4S \S \#4GS+jjr\S4GS,\#S \S S4GS-jjr\4S \S \#4GS.jjrSC\4GS/\GS0\2GS1\S \S S4 GS2jjr\4S \S \#4GS3jjrSC\4GS4\GS0\2S\S \S S4 GS5jjr\4GS6\S\GS7\GS8\2S \S S4 GS9jjrGSrS\4S\S \S \&4GS:jjrS\S14GS6\S\GS0\2GS;\S \GS<\2S S4GS=jjr\4S \S \%4GS>jjr\4GS?\&S\S \S S4GS@jjr\4S \S \&4GSAjjr\S14S \S \&4GSBjjr\SS1S4S \S \%4GSCjjrS\S4S \S S4GSDjjrS\S4S \S S4GSEjjr\S4S \S S4GSFjjr\4S \4GSGjjr\S4S \S S4GSHjjrGSI\4S \4GSJjjrS\4S \S S4GSKjjrGSI\4S \S \%4GSLjjrGSMr\\4S \4GSNjjr\4S \S S4GSOjjrGSP\4S \S \%4GSQjjrg(Ta The single local database on the Mac Studio. SQLite, one file, no server. Phase 1 uses two of the tables: the cars entered by hand, and the assessment results from each run. The other tables are created now so the shape is ready for later phases (learned model figures, daily stock snapshots, sales history), but nothing writes to them yet. Everything stays local. Section 2 rule 5. N)name_make_model)platesdataz bidbrain.dbaA CREATE TABLE IF NOT EXISTS cars ( id INTEGER PRIMARY KEY AUTOINCREMENT, reg TEXT, make TEXT, model TEXT, derivative TEXT, year INTEGER, mileage INTEGER, owners INTEGER, grade INTEGER, reserve INTEGER, cap_clean INTEGER, distance_miles REAL, engine TEXT, service_history TEXT, vat_qualifying INTEGER, glass_retail INTEGER, cazana_retail INTEGER, photo_url TEXT, listing_url TEXT, source TEXT, actually_paid INTEGER, loaded_at TEXT ); CREATE TABLE IF NOT EXISTS assessments ( id INTEGER PRIMARY KEY AUTOINCREMENT, run_at TEXT, reg TEXT, status TEXT, governing_source TEXT, governing_value INTEGER, max_bid INTEGER, reasons TEXT, flags TEXT ); -- Phase 3: per model learned figures, rebuilt by the monthly run from -- sales_history. model_key groups naming styles (see readers/clickdealer.py). CREATE TABLE IF NOT EXISTS learned_models ( id INTEGER PRIMARY KEY AUTOINCREMENT, make TEXT, model TEXT, model_key TEXT, n_sold INTEGER, avg_days_to_sell REAL, avg_prep_total INTEGER, avg_margin INTEGER, prep_split TEXT, spread_held INTEGER, by_platform TEXT, updated_at TEXT ); CREATE TABLE IF NOT EXISTS stock_snapshots ( id INTEGER PRIMARY KEY AUTOINCREMENT, snapshot_date TEXT, make TEXT, model TEXT, in_stock_count INTEGER ); -- Phase 3: one row per sold car, ingested monthly from the Clickdealer sold -- vehicles and sales margins reports. source is the supplier the car was -- bought from (Motorway, Carwow, an auction house...). prep_total is the SIV. -- margin is net sales minus purchase minus prep, straight off the report. CREATE TABLE IF NOT EXISTS sales_history ( id INTEGER PRIMARY KEY AUTOINCREMENT, stock_number TEXT, reg TEXT, make TEXT, model TEXT, sold_date TEXT, days_to_sell INTEGER, sale_price INTEGER, purchase_price INTEGER, margin INTEGER, total_margin INTEGER, prep_total INTEGER, prep_split TEXT, source TEXT, UNIQUE(stock_number, sold_date) ); -- Cars Steven has actually bought, read daily from each platform's purchases -- screen after the sale closes. emailed_at records when the buy was sent to -- accounts; the first ever capture is baselined so history is not re emailed. CREATE TABLE IF NOT EXISTS purchases ( id INTEGER PRIMARY KEY AUTOINCREMENT, platform TEXT, vehicle_ref TEXT, reg TEXT, name TEXT, status TEXT, price INTEGER, total_price INTEGER, bought_date TEXT, first_seen TEXT, retail_estimate INTEGER, listing_url TEXT, emailed_at TEXT, UNIQUE(platform, vehicle_ref) ); -- A chip: an agreed reduction on a purchase, negotiated after the win -- (a fault on the video, or one the driver found at the door). A stored -- event, never live arithmetic: bid_before freezes what the effective bid -- was at the moment of the chip, and new_fee/new_vat freeze the re banded -- platform fee where one applies (Motorway on Motorway Pay; Carwow's fee -- never moves, Steven confirmed 2026-08-27). Nothing here is ever -- recomputed against a later fee table or a later platform read, which is -- what makes the double count trap impossible (see CLAUDE.md). CREATE TABLE IF NOT EXISTS purchase_chips ( id INTEGER PRIMARY KEY AUTOINCREMENT, purchase_id INTEGER NOT NULL, amount REAL NOT NULL, reason TEXT, bid_before REAL, new_fee REAL, new_vat REAL, created_at TEXT, who TEXT ); CREATE INDEX IF NOT EXISTS ix_chips_purchase ON purchase_chips(purchase_id); -- The per purchase timeline (Steven 2026-08-27: "every change to any -- vehicle record should be shown in the timeline and timestamped -- including all comments and status changes"). Every mutation of a -- purchase goes through set_purchase_field / record_chip / -- cancel_purchase / add_purchase_comment, each of which writes here, so -- nothing can change a record without the timeline knowing. kind is one -- of field / comment / milestone / sync / chip / cancel. CREATE TABLE IF NOT EXISTS purchase_events ( id INTEGER PRIMARY KEY AUTOINCREMENT, purchase_id INTEGER NOT NULL, ts TEXT, kind TEXT, text TEXT, field TEXT, was TEXT, now TEXT, body TEXT, who TEXT ); CREATE INDEX IF NOT EXISTS ix_events_purchase ON purchase_events(purchase_id); -- Every car seen on each platform each day, so repeat appearances can be -- flagged. Identity is the registration. One row per car per platform per -- sale date. CREATE TABLE IF NOT EXISTS sightings ( id INTEGER PRIMARY KEY AUTOINCREMENT, reg TEXT, platform TEXT, sale_date TEXT, make TEXT, model TEXT, derivative TEXT, year INTEGER, reserve INTEGER, cap_clean INTEGER, listing_url TEXT, recorded_at TEXT, UNIQUE(reg, platform, sale_date) ); -- The two plates one car is genuinely known by (2026-09-20): the plate the -- listing used, and the plate underneath it sells with when the seller keeps -- a private plate. BidBrain has read that second plate since June for the -- valuations (Motorway's own sellingVrm, Carwow's plate transfer field) and -- never kept it, so nothing that looks a car up by plate afterwards, the -- DealerKit lookups above all, could ever try it. Written by -- save_assessments, which every run path goes through after its detail -- reads, so both platforms are covered wherever they happen to read it. -- Kept for good: a car bought today can reach DealerKit weeks later, long -- after the listing caches have rotated away. CREATE TABLE IF NOT EXISTS plate_pairs ( reg TEXT NOT NULL, selling_vrm TEXT NOT NULL, first_seen TEXT, last_seen TEXT, UNIQUE(reg, selling_vrm) ); -- Cars Steven has hidden with a reason, suppressed from future daily lists. -- One row per registration. active 1 means hidden, reviewed 0 means the reason -- has not yet been turned into a rule. CREATE TABLE IF NOT EXISTS hidden_cars ( id INTEGER PRIMARY KEY AUTOINCREMENT, reg TEXT NOT NULL, reason TEXT, make TEXT, model TEXT, name TEXT, hidden_at TEXT, active INTEGER DEFAULT 1, reviewed INTEGER DEFAULT 0 ); CREATE UNIQUE INDEX IF NOT EXISTS ix_hidden_reg ON hidden_cars(reg); -- Steven's own daily bid shortlist. The app never bids, this is his pick and a -- learning signal. One row per car per sale date. CREATE TABLE IF NOT EXISTS bids ( id INTEGER PRIMARY KEY AUTOINCREMENT, reg TEXT NOT NULL, sale_date TEXT NOT NULL, make TEXT, model TEXT, name TEXT, max_bid INTEGER, reserve INTEGER, year INTEGER, mileage INTEGER, grade INTEGER, fuel TEXT, body_type TEXT, cap_clean INTEGER, platform TEXT, created_at TEXT, active INTEGER DEFAULT 1, UNIQUE(reg, sale_date, platform) ); -- Cars bought on a platform where the sale was then cancelled or voided. Steven -- never wants to see these again (he has already walked away once), so their -- registrations are suppressed from the daily page for good. CREATE TABLE IF NOT EXISTS walked_away ( id INTEGER PRIMARY KEY AUTOINCREMENT, reg TEXT NOT NULL, platform TEXT, name TEXT, status TEXT, noted_at TEXT ); CREATE UNIQUE INDEX IF NOT EXISTS ix_walked_away_reg ON walked_away(reg); -- Buying rule overrides edited from the cockpit's settings page, layered on -- top of pricing.DEFAULT_SETTINGS. Value is JSON so a row can hold a number, -- a list or a list of objects (for example value_caps). Empty table means -- every rule is still at its factory default. CREATE TABLE IF NOT EXISTS settings ( key TEXT PRIMARY KEY, value TEXT NOT NULL, updated_at TEXT NOT NULL ); -- Glass's and Cazana valuations, cached so re running the daily job (for -- example a genuine same day re run, or the same car reappearing across an -- Auction4Cars auction's several days) does not repeat a lookup for a retail -- figure that has not changed. Keyed on reg, mileage and source so a car -- that has genuinely been driven since last seen still gets a fresh look up. -- value is nullable, a confirmed no match is cached too, so an unmatched reg -- is not re queried every day either. Rows older than 48 hours are never -- used (and are pruned) regardless of what is in the table. CREATE TABLE IF NOT EXISTS valuation_cache ( reg TEXT NOT NULL, mileage INTEGER NOT NULL, source TEXT NOT NULL, make TEXT NOT NULL, model TEXT NOT NULL, value INTEGER, url TEXT, valued_at TEXT NOT NULL, UNIQUE(reg, mileage, source) ); -- Whatever a platform's own detail page visit adds to a car (owners, service -- history, disclosed issues, photos, and on Carwow/Auction4Cars the reserve -- itself, which only that page ever discloses), cached so a car reappearing -- across a multi day Auction4Cars, Carwow or DealerWay auction does not get -- its own page re visited every single run just to read the same, mostly -- unchanged, facts again (Mark 2026-08-24: "are these cars info... being -- added to a database... rather than a full scrape", the slow part of a -- refresh). detail_json is whichever car fields the detail read actually -- changed (a genuine before/after diff, not a fixed field list, so this -- never needs updating when a reader gains a new field), applied back onto -- the fresh car object on a hit. Reserve rides along in the same cache -- entry as everything else with the same short TTL (Mark's own choice, "yes -- cache price too with a short TTL"), not a separate longer one, so a -- cached figure is never more than a few hours stale. Dealer Auction is not -- covered, it has no detail page visit at all, every field including its -- own reserve stand in already comes straight off the list card. cache_key -- is a registration wherever one is already known before the detail visit -- (Carwow, DealerWay), but Auction4Cars' own reg is only ever read FROM its -- detail page, so a candidate's stable listing_url stands in as the key -- there instead, until the real reg lands in the cached fields themselves. CREATE TABLE IF NOT EXISTS listing_detail_cache ( cache_key TEXT NOT NULL, platform TEXT NOT NULL, detail_json TEXT NOT NULL, fetched_at TEXT NOT NULL, UNIQUE(cache_key, platform) ); -- Saved filter and sort combinations Mark can name and star, so a common -- slice of the list is one click away instead of setting several filters by -- hand each time (2026-08-20). criteria is the whole sidebar filter state -- (make, model, year, transmission, body type, the three price ranges, sort -- and view) as one JSON blob. Starred ones show as a quick button next to -- the combined sort and view control; unstarred ones are just kept, listed -- in the sidebar so they can be starred or deleted later. CREATE TABLE IF NOT EXISTS saved_views ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, criteria TEXT NOT NULL, starred INTEGER NOT NULL DEFAULT 0, created_at TEXT NOT NULL ); -- Every real auction bid on and lost, Mark 2026-08-24: the original -- BidBrain vision was to learn real prices from real data (best sellers, -- prep cost, margin) to make more educated bids; a lost auction's own real -- winning price is the other half of that picture purchases alone never -- carry, how much it actually would have taken to win. Identity is the -- platform's own vehicle reference (a plain reg can repeat across -- unrelated cars over time; the platform's own numeric id cannot), a lost -- outcome never changes once recorded so this is a plain insert once, no -- fill-in-later fields the way purchases has. CREATE TABLE IF NOT EXISTS lost_bids ( id INTEGER PRIMARY KEY AUTOINCREMENT, platform TEXT, vehicle_ref TEXT, reg TEXT, name TEXT, sold_for INTEGER, our_bid INTEGER, date_bid TEXT, first_seen TEXT, listing_url TEXT, mileage INTEGER, cazana_retail INTEGER, UNIQUE(platform, vehicle_ref) ); -- The one platform action BidBrain is ever allowed to take (approved by -- Steven 2026-06-09, built 2026-08-27, Mark: "when we shortlist a car in -- bidbrain add that to watched/tracked in auction sites"): starring or -- unstarring a car in the cockpit adds or removes it from that platform's -- own shortlist/watchlist. Never a bid, never anything else. This table -- holds the DESIRED state per (reg, platform), not a log of events, so a -- quick star then unstar before the sync ever runs just updates the one -- row rather than queuing duplicate work. synced_at is cleared to NULL on -- every queue (so a later toggle is always picked up again even if an -- earlier one already synced) and only ever set back on a confirmed, -- verified success; a failed attempt leaves it NULL (so the next pass -- retries) and records the real reason in error, surfaced the same way a -- dead login already is elsewhere in this project. CREATE TABLE IF NOT EXISTS watchlist_sync ( reg TEXT NOT NULL, platform TEXT NOT NULL, listing_url TEXT, watched INTEGER NOT NULL, queued_at TEXT NOT NULL, synced_at TEXT, error TEXT, UNIQUE(reg, platform) ); -- Cars won or offered on but not yet a confirmed, accepted purchase (Mark -- 2026-08-27: "add a under offer section to the main nav for cars that -- are won but bids not accepts this is for all auctions"), read from each -- platform's own real "Bids & offers" style screen, never the purchases -- table (a genuinely earlier stage, before a car is even guaranteed to -- become a real purchase at all). A live snapshot, not an accumulating -- history: record_under_offer replaces the whole set for one platform on -- every read, so a car that has since been accepted or lost simply is -- not in the next read and drops out on its own, no separate cleanup -- needed. CREATE TABLE IF NOT EXISTS under_offer ( id INTEGER PRIMARY KEY AUTOINCREMENT, platform TEXT NOT NULL, reg TEXT, name TEXT, max_bid INTEGER, noted_at TEXT, vehicle_ref TEXT, listing_url TEXT, read_at TEXT NOT NULL ); -- DealerKit's own in stock cars (2026-09-10): one row per vehicle from -- DealerKit's record, read with the stock list, so the purchase cost of -- every car in stock is DealerKit's figure whether BidBrain bought it or not. CREATE TABLE IF NOT EXISTS dealerkit_stock ( vehicle_id TEXT PRIMARY KEY, reg TEXT, make TEXT, model TEXT, purchase_cost INTEGER, retail_price INTEGER, life_cycle_status INTEGER, read_at TEXT NOT NULL ); -- What a sold car actually went for (2026-09-12, Steven: "cars dont always -- sell for what we list them up for retail for"). Dealer OS asks for one -- car at a time the moment DealerKit says it is Sold (a sale.price -- action); the row waits here until the quiet read has looked at the -- car's own DealerKit record and posted the figure back. CREATE TABLE IF NOT EXISTS sale_price_reads ( id INTEGER PRIMARY KEY AUTOINCREMENT, reg TEXT NOT NULL, api_id TEXT, name TEXT, sold_at TEXT, queued_at TEXT NOT NULL, done_at TEXT, result TEXT ); -- Cars Steven released from a hold (2026-09-10): the rules dropped the -- car, he said on Dealer OS "un hold it" and why. The car passes those -- rules on every later run, the reason is kept for tuning the rules. CREATE TABLE IF NOT EXISTS released_cars ( reg TEXT PRIMARY KEY, reason TEXT, rules TEXT, make TEXT, model TEXT, name TEXT, who TEXT, released_at TEXT NOT NULL, active INTEGER NOT NULL DEFAULT 1, reviewed INTEGER NOT NULL DEFAULT 0, updated_at TEXT ); -- The shortlist deep read (Dealer OS v4.1.0, 2026-09-17): one row per -- starred car Dealer OS asked this Mac to read in full, keyed the way -- Dealer OS keys a card (its platform and its key, the reg or the listing -- url for a car with no plate). wanted is 1 from the ask until the page -- has been read (or given up on), deep_json is the last answer and stays -- on the row so a re read pushes the old answer, with its older asked_at, -- until the new one lands, which is exactly how Dealer OS knows it is -- still reading. attempts counts passes that started on the row, so a -- page that keeps crashing the read is answered with an error after -- DEEP_READ_MAX_ATTEMPTS rather than tried forever. CREATE TABLE IF NOT EXISTS deep_reads ( id INTEGER PRIMARY KEY AUTOINCREMENT, car_key TEXT NOT NULL, platform TEXT NOT NULL, sale_date TEXT NOT NULL, reg TEXT, listing_url TEXT, name TEXT, asked_at TEXT, queued_at TEXT NOT NULL, wanted INTEGER NOT NULL DEFAULT 1, attempts INTEGER NOT NULL DEFAULT 0, read_at TEXT, deep_json TEXT, UNIQUE(car_key, platform, sale_date) ); pathreturnc[R"[RRU5SS9 [R "USS9n[R UlU$)NT)exist_ok)timeout)osmakedirsrdirnamesqlite3connectRow row_factoryrconns ,/Users/stevendouglas/BidBrain/bidbrain/db.pyrrs@KK%5 ??4 +D{{D Kc[U5nUR[5 [U5 UR 5 UR 5 g!UR 5 f=fN)r executescriptSCHEMA_migratecommitclosers rinit_dbrsA 4=D 6"   s 0AA cURS5Vs1sHoSiM nnSU;aURS5 SU;aURS5 URS5Vs1sHoSiM nnSH#upEXC;dM URS US U35 M% S U;aURS 5 [U5 URS 5Vs1sHoSiM nnS U;aURS5 URS5Vs1sHoSiM nnU(aSU;a|URS5 URS5R5H7nURS[R"5R US45 M9 URS5 S U;aURS5 SU;aURS5 URS5Vs1sHoSiM nnU(a.SU;aURS5 SU;aURS5 SHupURSU S 35Vs1sHoSiM nnU(dM5X;dM<URS!U 35R 5S"n U S#:XaURS$U 35 M}[U S%U S&35e URS'5Vs1sHoSiM n nS(HnXL;dM URS)US*35 M! S+U ;aURS,5 S-U ;aURS.5 S/U ;aURS05 S1U ;aURS25 S3U ;aURS45 S5U ;aURS65 S7U ;aURS85 S9V s/sH n X;dM U PM nn UHnURS)US:35 M U(aURS;5 SU ;aURS?5 S@U ;aURSA5 SBHnXL;dM URS)US:35 M! SCHnXL;dM URS)US*35 M! SDHnXL;dM URS)US:35 M! [U5 SEU ;aURSF5 SGU ;aURSH5 SIU ;aURSJ5 SKHnXL;dM URS)US*35 M! URSL5Vs1sHoSiM nnSMU;aURSN5 SOU;aURSP5 SQU;aURSR5 URSS5Vs1sHoSTiM nnSUU;aURSV5 URS5Vs1sHoSiM nnSWH$upEUU;dM URSXUS U35 M& SYU;aURSZ5 S[U;aURS\5 S]U;aURS^5 S_U;aURS`5 SYU;aURSa5 URS'5Vs1sHoSiM nnS[U;aURSb5 S]U;aURSc5 S_U;aURSd5 SYU;aURSe5 SMU;aURSf5 SgU;aURSh5 SiU;aURSj5 SkU;aURSl5 SmH nUU;dM URS)US:35 M" SnH nUU;dM URS)US:35 M" SoH$upEUU;dM URS)US U35 M& SpU;a"URSq5 URSr5 URSs5 StU;aURSu5 URSv5Vs1sHoSiM nnSwU;aURSx5 UR[5 [U5 [U5 gys snfs snfs snfs snfs snfs snfs snfs sn fs snfs snfs snfs snfs snf)zzAdd columns to existing tables that CREATE TABLE IF NOT EXISTS cannot add. Idempotent: only adds a column when it is missing, so old databases upgrade in place and existing rows keep their values (the new column reads NULL).zPRAGMA table_info(sightings)namereservez0ALTER TABLE sightings ADD COLUMN reserve INTEGER cap_cleanz2ALTER TABLE sightings ADD COLUMN cap_clean INTEGERPRAGMA table_info(bids)))yearINTEGERmileager&)grader&)fuelTEXT) body_typer+)r#r&)platformr+zALTER TABLE bids ADD COLUMN  updated_atz+ALTER TABLE bids ADD COLUMN updated_at TEXTzPRAGMA table_info(hidden_cars)z2ALTER TABLE hidden_cars ADD COLUMN updated_at TEXTzPRAGMA table_info(saved_views) remote_idz1ALTER TABLE saved_views ADD COLUMN remote_id TEXTzSELECT id FROM saved_viewsz-UPDATE saved_views SET remote_id=? WHERE id=?idzQCREATE UNIQUE INDEX IF NOT EXISTS ix_saved_views_remote ON saved_views(remote_id)z2ALTER TABLE saved_views ADD COLUMN updated_at TEXTdeletedzEALTER TABLE saved_views ADD COLUMN deleted INTEGER NOT NULL DEFAULT 0z PRAGMA table_info(sales_history) total_marginz9ALTER TABLE sales_history ADD COLUMN total_margin INTEGER prep_splitz4ALTER TABLE sales_history ADD COLUMN prep_split TEXT))learned_models model_key) sales_historyregzPRAGMA table_info()zSELECT COUNT(*) AS n FROM nrz DROP TABLE z has z> rows but an old shape. Not dropping data, migrate it by hand.zPRAGMA table_info(purchases) winning_bid motorway_fee transport_fee protect_fee vat_totalz!ALTER TABLE purchases ADD COLUMN z REAL cazana_115z0ALTER TABLE purchases ADD COLUMN cazana_115 REALpayment_estimatedz:ALTER TABLE purchases ADD COLUMN payment_estimated INTEGER photo_urlz/ALTER TABLE purchases ADD COLUMN photo_url TEXTservice_history_photoszALTER TABLE purchases ADD COLUMN dk_due_in_date_pushed_at TEXTdk_checkz.ALTER TABLE purchases ADD COLUMN dk_check TEXT)dk_mileage_pushed_atdk_fee_pushed_atdk_chip_pushed_atdk_transport_pushed_atdk_checked_in_at dk_removed_at) dk_price_sent dk_fee_sent)makemodeldocument_pathsz4ALTER TABLE purchases ADD COLUMN document_paths TEXTv5_local_pathsz4ALTER TABLE purchases ADD COLUMN v5_local_paths TEXTdealer_os_documentsz9ALTER TABLE purchases ADD COLUMN dealer_os_documents TEXT)finance_settlementnegative_equityzPRAGMA table_info(lost_bids)r(z0ALTER TABLE lost_bids ADD COLUMN mileage INTEGER cazana_retailz6ALTER TABLE lost_bids ADD COLUMN cazana_retail INTEGERcazana_checkedz7ALTER TABLE lost_bids ADD COLUMN cazana_checked INTEGERz!PRAGMA table_info(watchlist_sync)rattemptszIALTER TABLE watchlist_sync ADD COLUMN attempts INTEGER NOT NULL DEFAULT 0)r')enginer+) transmissionr+z!ALTER TABLE sightings ADD COLUMN r)z.ALTER TABLE sightings ADD COLUMN grade INTEGERr%z-ALTER TABLE lost_bids ADD COLUMN year INTEGERraz2ALTER TABLE lost_bids ADD COLUMN transmission TEXTr`z,ALTER TABLE lost_bids ADD COLUMN engine TEXTz.ALTER TABLE lost_bids ADD COLUMN grade INTEGERz-ALTER TABLE purchases ADD COLUMN year INTEGERz2ALTER TABLE purchases ADD COLUMN transmission TEXTz,ALTER TABLE purchases ADD COLUMN engine TEXTz.ALTER TABLE purchases ADD COLUMN grade INTEGERz0ALTER TABLE purchases ADD COLUMN mileage INTEGERcollection_datez5ALTER TABLE purchases ADD COLUMN collection_date TEXTretail_estimate_overridezAALTER TABLE purchases ADD COLUMN retail_estimate_override INTEGERcollection_date_overridez>ALTER TABLE purchases ADD COLUMN collection_date_override TEXT) cancelled_atcancelled_reasoncancelled_source) stock_status on_finance video_statusperson_collectingnotescollection_arranged logbook_statelocation checked_in_atsupplier))paid_forr&) manual_entryr&) fee_has_vatr&)transport_has_vatr& archived_atzcUPDATE purchases SET stock_status='In stock' WHERE archived_at IS NOT NULL AND stock_status IS NULLz-ALTER TABLE purchases DROP COLUMN archived_atzEUPDATE purchases SET stock_status='Due in' WHERE stock_status IS NULL selling_vrmz1ALTER TABLE purchases ADD COLUMN selling_vrm TEXTz"PRAGMA table_info(valuation_cache)urlz/ALTER TABLE valuation_cache ADD COLUMN url TEXTN) execute_bids_one_per_listingfetchalluuiduuid4hexfetchone RuntimeError_fill_purchase_make_modelrr_move_sunday_sightings_restore_motorway_amendments)rrcolsbcolscoltyphcolsvcolsscolstablemarkerr:pcolsc _dk_new_cols_dkcollbcolswcolssicolspcols2vccolss rrrs $||,JK LK!fIKD L GH$ IJ $ -F G H G1vY GE H+   LL7uAcUC D +5  BC$ $ -M N O N1vY NE O5  IJ $ -M N O N1vY NE O e # LLL M\\">?HHJ Ltzz|O_O_abcgahNijK LLl m u $ LLM N E ! LL` a !% -O P Q P1vY PE Q  & LLT U u $ LLO PT #'<<2DUG10M#NO#Na& #NO 4F& 9%ABKKMcRAAv {5'23"gU1#&**++U!% -K L M L1vY LE M[   LL'')//1 22r)Carwowr- sale_datecU[;dU(dU$[RRU5nUR 5S:Xa%U[R "SS9-R5$U$![a Us$f=f)zThe sale a lot read for sale_date really belongs to on platform: the Monday after, when sale_date is a Sunday and the platform holds no Sunday sale. Anything else, a blank or unreadable date included, is returned as given. Pure, tested.rdays)NO_SUNDAY_SALErdate fromisoformat ValueErrorweekday timedeltar)r-rdays rplatform_sale_dayrsy ~%Ymm)))4 {{}h((a00;;==  sA// A>=A>cSRS[[5-5nURSUS3[5R 5nUHn[ USUS5nXCS:XaMURSUSUSU45R 5nU(a6URS US US US 45 URS US 45 MURSXCS 45 M g)alRefile any sighting already logged under a Sunday for a platform with no Sunday sale onto the Monday it really was (see NO_SUNDAY_SALE). When the car was also logged on that Monday the two are one lot, so the Monday row keeps any reserve or CAP clean only the Sunday row read, and the Sunday row goes. Runs on every start and does nothing once no such row is left.,?zeSELECT id, reg, platform, sale_date, reserve, cap_clean FROM sightings WHERE platform IN (z%) AND strftime('%w', sale_date) = '0'r-rzCSELECT id FROM sightings WHERE reg=? AND platform=? AND sale_date=?r8z^UPDATE sightings SET reserve=COALESCE(reserve, ?), cap_clean=COALESCE(cap_clean, ?) WHERE id=?r"r#r1z DELETE FROM sightings WHERE id=?z+UPDATE sightings SET sale_date=? WHERE id=?N)rlenrryr{rr)rmarksrowsrmondaytwins rrrs HHS3~.. /E << $g%J N !  "1Z=!K.A {^ # || Q uXq}f -//7xz   LLp9q~tDz: < LL;agZ H LLFSWQXHY Zrc[X5n[U5n[5nUHn[[ USS55=(d* [ R "[ USS5=(d S5nU(dMSURSXqX&RURURUR[ USS5[ USS5URU[ USS5[ US S5=(d S[ US S5=(d S[ US S545 M UR5 UR5 g!UR5 f=f) a]Log every car seen on a platform for a given sale date. Cars without a readable registration cannot be tracked for repeats, so they are skipped. One row per car per platform per sale date, repeats on the same day ignored. A car whose listing plate could not be read but whose original plate was (Carwow's plate transfer field, Motorway's sellingVrm) is logged under that original plate (Steven 2026-09-28: S30NNT on Carwow read as never seen, because a listing with no readable plate was never logged on any day it appeared). The list read runs before the detail pages, so a reader calls this again once they are read; the same day's row is ignored the second time round. A platform with no Sunday sale files a Sunday read under the Monday it is really for (platform_sale_day), so one lot read on Saturday evening and again on Sunday is one sighting, not two.r8rrwa"INSERT OR IGNORE INTO sightings (reg, platform, sale_date, make, model, derivative, year, reserve, cap_clean, listing_url, recorded_at, mileage, engine, transmission, grade) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)r"Nr#r(r`rar))rrrrgetattrrtidyryrVrWrr%rrr)rr-rrrrrr8s rrecord_sightingsr-s "(6I 4=DfAGAub12ffkk'!]\^B_Bece6fC LL=  66177ALLIt4gad6SIt,ga4.H.PDND19T71gt;T V     s DEEc [U[5(dUcU/O [U5n/nUH/n[U5nU(dMXd;dMUR U5 M1 U(dgUSLnU=(d [ U5nSR S[U5-5nURSUSUS3XD-5R5n U Hhn U SU S 4HWn[R"U5nU(dM"[R"U5(dM?Xd;dMFUR U5 MY Mj [U5U(aUR!5 $$![Ra /n Nf=f!U(aUR!5 ff=f) aEvery plate a car's sightings may be filed under (2026-09-28). reg is one plate, or several (the listing plate and the original plate underneath, in that order); each is widened with the other half of any plate pair on file, so a car listed on a private plate one day and on its original plate another, or with no readable listing plate at all, still meets its own earlier sightings. Blank and not plate shaped values are dropped, and () means there is nothing to look up.Nrrrz7SELECT reg, selling_vrm FROM plate_pairs WHERE reg IN (z) OR selling_vrm IN (r9r8rw) isinstancerlistrappendrrrryr{rOperationalErrorrris_platetupler) r8rrgivenoutvrownrrrs rsighting_platesrVs^ S))S[SEd3iE C  aL 1 JJqM  $,C  74=Ds3x( <<I%Pefkellmn #8:  Ah- 01KKN1++ JJqM2 Sz  JJL '' D   JJL sB?E3)E-E37E3E3#E3E0-E3/E00E33F  before_datecH[U5nU(dg[U5nU(a"URSX45R5nO!URSU45R5nU(a [ U5OSUR 5 $!UR 5 f=f)a&The most recent sighting on file for this registration (any platform), optionally only counting a sighting on or before before_date (a car's mileage only ever goes up, so a sighting from after a bid was won or lost is not a safe stand in for its mileage at the time). None when this reg has never been sighted. Used to backfill mileage, year, engine and transmission for a lost bid or a purchase from BidBrain's own daily reads, before ever falling back to a fresh live lookup (Mark 2026-08-25: "populate this from our database as much as possible").NzTSELECT * FROM sightings WHERE reg=? AND sale_date<=? ORDER BY sale_date DESC LIMIT 1zCSELECT * FROM sightings WHERE reg=? ORDER BY sale_date DESC LIMIT 1)rrryrdictr)r8rrrrs rlatest_sighting_for_regr}s C.C  4=D  ,,2473EGGOxz ,,2476;;C8:  tCyT)  s ABB! window_daysc [U5n[XS9nU(d/UR5 $URSSR S[ U5-5S3/UQUPUPS[ U5S3P75R5nUVs/sHowSUS US S .PM snUR5 $s snf!UR5 f=f) aEvery earlier time this registration was seen in the window before sale_date, newest first, one row per sale day and platform: [{sale_date, platform, reserve}]. For the card's Seen tab on Dealer OS (Steven 2026-09-10: "if a car has been seen before i want to know how many days ago and if theres more than one i want to see them all"). reg may carry both of a car's plates, see sighting_plates.rzoSELECT sale_date, platform, MAX(reserve) AS reserve FROM sightings WHERE reg IN (rrz) AND sale_date < ? AND sale_date >= date(?, ?) GROUP BY sale_date, platform ORDER BY sale_date DESC, platform- daysrr-r")rr-r")rrrryrrintr{)r8rrrrregsrrs rsighting_historyrs 4=Ds. ||!hhsSY7891 4 Gd FI Fy FAc+.>-?u*E F   (* lppkofg n!J-TUV_T`akop q s#B8AB8B3 B83B88C c20/SS.n[U5n[XS9nU(dUUR5 $SUS'SRS[ U5-5nUR SUS 3/UQUPUPS [ U5S 3P75R5nUHn U S (dMU S US U S'M UR SUS3/UQUP75R5n [SU 55US'UUR5 $!UR5 f=f)aHow many previous times this registration was seen on each platform in the window before sale_date, plus whether it is on more than one platform on sale_date itself. Returns: {"previous": {platform: count}, "same_day_platforms": [..]}. Counts distinct earlier sale dates, so two sightings on one day count once. reg may carry both of a car's plates, see sighting_plates, and "known" says whether there was any plate to look up at all: a car with none has no history on file, which is not the same as a first sighting. F)previoussame_day_platformsknownrTrrrzkSELECT platform, COUNT(DISTINCT sale_date) AS n FROM sightings WHERE reg IN (zr) AND sale_date < ? AND sale_date >= date(?, ?) GROUP BY platformrrr:rr-zESELECT DISTINCT platform FROM sightings WHERE reg IN (z) AND sale_date = ?c3*# UH oSv M g7f)r-Nr.0rs r !repeat_summary..s*G$QZ=$r) rrrrrryrr{sorted) r8rrrrrrrrrsames rrepeat_summaryrsEe DC 4=Ds.4 3G s4y)||#W%! $ Gd FI Fy FAc+.>-?u*E F  (* Avv123J* . ||#W$7 ; d I   (* %+*G$*G$G !  sDA)D"ADD snapshot_datec[U5nURSU45 UHupEURSXU45 M UR5 UR5 g!UR5 f=f)zStore today's Clickdealer in stock list, one row per vehicle as a (make, model) pair. Re run safe: same date rows are replaced.z1DELETE FROM stock_snapshots WHERE snapshot_date=?zYINSERT INTO stock_snapshots (snapshot_date, make, model, in_stock_count) VALUES (?,?,?,1)Nrryrr)pairsrrrrVrWs rrecord_stock_snapshotrse 4=D  H=JZ[ KD LLke, !   s AA A2cSSKJn [U5nURS5R 5SnU(d0UR 5 $0nURSU45H>nU"USUS5nU(dMUR US5US =(d S-XF'M@ UUR 5 $!UR 5 f=f) z|The most recent stock snapshot as {model_key: count}. Empty dict when no snapshot exists yet, so callers degrade gracefully.rr63SELECT MAX(snapshot_date) AS d FROM stock_snapshotsdzMSELECT make, model, in_stock_count FROM stock_snapshots WHERE snapshot_date=?rVrWrin_stock_count)readers.clickdealerr6rryrrget)rr6rlastcountsrkeys rlatest_stock_countsrs/ 4=D ||QR[[]^ab _bfahjAAfIqz2Cs$jja0A6F4G4L1M j   s*B7,B7>(B77C c[U5nURS5R5nU(aUS(aUSOSUR5 $!UR5 f=f)zThe date of the most recent stock snapshot (ISO string), or None. Used to warn when the Clickdealer read has gone stale, so the gap flags are old.rrNrryrrrrrs rlatest_stock_dater#sQ 4=D LLN O X X Zqvqv4/  s 6AA'>Complete DeliveredChecks completeDelivery requestedReviewing documentsAwaiting confirmationr;)retail_estimaterAr() rCrDrErFrXrYrbr[r\c[U5nU(a2URSU45R5nU(a USUS4$[U5nU(aU$S$)zThe make and model for a bought car: the newest sighting of its plate (the very listing the card was drawn from, so the two agree), else the name read the way Dealer OS and CompMatch read it. (None, None) when neither says.zSELECT make, model FROM sightings WHERE reg=? AND make IS NOT NULL AND make<>'' AND model IS NOT NULL AND model<>'' ORDER BY recorded_at DESC, id DESC LIMIT 1rVrW)NN)rryrr)rr8r!seenmms r_purchase_make_modelr.Dsf C.C || ]_b^dffnfnfp  L$w-0 0  B2%%rcSnURS5R5HEn[XSUS5up4U(dM U(dM)URSX4US45 US- nMG U$)zFill make and model on every purchase row still without them. Returns how many rows were filled. Idempotent and cheap: only rows missing one of the two are looked at.rz^SELECT id, reg, name FROM purchases WHERE make IS NULL OR make='' OR model IS NULL OR model=''r8r!/UPDATE purchases SET make=?, model=? WHERE id=?r1r)ryr{r.)rr:rrVrWs rrrTsz A \\z {EEG*451V9E  4EE LLJTZ[\`ZaLb c FA G Hrc [R"U5[R"U5pT[R"U5(a [R"U5(aXE:XagU=(d [5nUR SXEX345 g)zRemember that these two plates are the same car. Refuses anything not plate shaped, and a plate paired with itself, so a bad read can never widen a later search.FzINSERT INTO plate_pairs (reg, selling_vrm, first_seen, last_seen) VALUES (?,?,?,?) ON CONFLICT(reg, selling_vrm) DO UPDATE SET last_seen=excluded.last_seenT)rrrrry)rr8rwrabs r_record_plate_pairr4ask ;;s V[[5q OOA  6??1#5#5!& -CLL W s rc[U5n[X0U5nUR5 UUR5 $!UR5 f=f)zL_record_plate_pair with a connection of its own, for a caller outside a run.)rr4rr)r8rwrroks rrecord_plate_pairr7ps= 4=D ; 7   s ;A c.^0mU4SjnURS5H8n[R"US5nU"USUR S55 M: URS5HnU"USUS5 M T$![[4a Mxf=f)a{plate: the other plate} for every pair on file. plate_pairs is the lasting record; the listing detail cache is read as well because it already carried these pairs for Carwow before plate_pairs existed, and it costs nothing (only rows that mention the field are parsed). Read BOTH ways round on purpose: a purchase may be recorded under either plate, depending which screen it came off, and either way the other plate is the one DealerKit may be holding.c>[R"U5[R"U5p[R"U5(a-[R"U5(aX:wa XsTU'TU'ggggr)rrr)r2r3rs rpair_plate_pair_map..pairsX{{1~v{{1~1 ??1  &//!"4"4NCFCF:@"4 rzvSELECT cache_key, detail_json FROM listing_detail_cache WHERE detail_json LIKE '%selling_vrm%' ORDER BY fetched_at ASC detail_json cache_keyrwz?SELECT reg, selling_vrm FROM plate_pairs ORDER BY last_seen ASCr8)ryjsonloads TypeErrorrr)rr:rrrs @r_plate_pair_maprA|s C"|| MN  3}-.A S quu]34N||]^ SZ]+,_ J :&   sBBBcURS5R5nU(dg[U5nSnUHOnUR[R "US55nU(dM4URSXTS45 US- nMQ U$)aFill selling_vrm on every purchase row still without one, from the plate pairs on file (2026-09-20, see the column's own note in _migrate and the plate_pairs table). Returns how many rows were filled. Idempotent and cheap: only rows missing it are looked at, and the pairs are only read when there is at least one such row. A row no pair covers is simply left alone, same as make and model; nothing is ever guessed, and a later read of that car fills it in then.zISELECT id, reg FROM purchases WHERE selling_vrm IS NULL OR selling_vrm=''rr8z-UPDATE purchases SET selling_vrm=? WHERE id=?r1r)ryr{rArrr)rrrr:rothers r_fill_purchase_selling_vrmrDs <<S UU]U]U_  D !E A  &++ah/0 5 LLH5TXRYJZ [ FA  Hrc^[T[5(a TROSU4Sjjn[R"U"S5U"S55$)zEvery plate one purchase might be known by on DealerKit, the recorded one first: the row's own reg, then the plate underneath. What every DealerKit lookup searches on, so a car with two real plates is found whichever one DealerKit holds.c:>UTR5;aTU$U$r)keys)krrs r!purchase_plates..s"QRURZRZR\_3q6CcbcCcrr8rwr)rrrr candidates)rrs` rpurchase_platesrLs9  T**#''1cC   SZ]); <? AND cancelled_at IS NULLzhNothing goes to accounts or to DealerKit for either sale until one of them is cancelled on its platform.QINSERT INTO purchase_events (purchase_id, ts, kind, text, who) VALUES (?,?,?,?,?)syncz was already won on r-z", so this car has been won twice. rr1z has now also been won on another platform)ryr{ _plain_list)rnew_idr8r-rtwinstailrs r_note_double_winrUs  LL[  xz   1DLL[ f 5$[1OAJ-1O%P$QR,,06 34> @A  _ tWc6u.x/M;M.NO004v78B D E2PsB#Fcf^^^^^ [U5n/n[5nSR[5nSR[5nSR[ 5nUGHmUR SUSUSUS3TS[TS545R5mSn T(db[TRS55(aCUR SUSUSUS 3[TRS5545R5n U (GaN[V s0sHoXbXOTRU 5_M sn m[ V s0sH*oTRU 5bTRU 5OX_M, sn m TRS 5SLn [V s0sHoU (aTRU 5OX_M! sn mTRS 5=(d S n U (a U [;aU O U S =(d S n SRS /[Q[Q[ Q755nUR SUS3TS[TS5TRS5TRS5TRS5TRS5U TRS5U (aTRS5(aSOSOU S/ U4Sj[5QU4Sj[5QU 4Sj[ 5QU SP75 U Sb SU SS3OSnUR S U SUS!TSS"TSS#USTSS$3S%45 UR0[T5ES&U S0E5 GMT(GaTS =(d S nTRS 5=(d S n US :H=(d U[;nU S :g=(a U [;nU(a U(aU OUn TS=(d TRS5=(d Sn[U4S'j[55nU=(a TS(+=(a TS(SLnTRS 5SLn UR S)TS45R5S*nU(a|U (auTS boTS U- n[TS U- 5S+:aRTSS,USS-3nUR S.TSU45R5nU(dUR S TSUS!US%45 U(dU(dU (d[V s0sHoTU _M sn mTSnU(aZTS(cTTRS(5bBTRS(5TS('TRS/5TS/'TRS5(aSOSnO[V s0sHoTRU 5_M sn mTRS5(aSOSnU (aTS bTS TS S+- :aw[!TS TS - S05nTS TS 'UR S1TSUS2TS3TS SSUS%45 UR S3TSUS4S5US6S7TS3STS S3STS S3S%45 [V s0sH n U TU bTU OTRU 5_M" sn m[ V s0sH,n U TRU 5bTRU 5OTU _M. sn m SRS8/[Q[Q[ Q755nUR S9US:3TRS5TRS5TRS5U UU/U4S;j[5QU4S<j[5QU 4S=j[ 5QTSP75 GMUR S>USUSUS?S@RSA[#[5-5SBS@RSA[#[5-5SBS@RSA[#[ 5-5SC3 TS[TS5[TRS55TRS 5TRS5TRS5TRS5TRS5UTRS5U(aUOSTRS5(aSOS/ U4SDj[5QU4SEj[5QU4SFj[ 5Q75 [%X3R SG5R5SH[TRS55TRS5U5 UR[T55 GM ['U5 [)U5 UR+5 UUR-5 $s sn fs sn fs sn fs sn fs sn fs sn fs sn f!UR-5 f=f)IaStore bought cars from the platform purchases screens. Returns the rows that are NEW (not seen before), which are the ones to email to accounts. Existing rows get their status refreshed, their name healed if it was ever stored blank or as a status word (a name that already looks real is never overwritten by a worse one), their bought_date filled in once, and their payment breakdown replaced wholesale on every read UNTIL it is confirmed (see _PURCHASE_PAYMENT_FIELDS), at which point it locks for good. retail_estimate, cazana_115 and mileage fill in once and never change after; everything in _PURCHASE_REFRESHABLE_FIELDS (photos, documents, collection_date) always takes a fresh value when a read actually finds one, since a stored url can go stale, a local file can be replaced by a better one, and a scheduled date can become a confirmed one or simply move. baseline=True marks new rows as already emailed, used once to seed history without emailing it., z1SELECT id, name, bought_date, payment_estimated, z2 FROM purchases WHERE platform=? AND vehicle_ref=?r- vehicle_refNr8z8SELECT id, name, price, bought_date, payment_estimated, zo FROM purchases WHERE reg=? AND COALESCE(manual_entry, 0) = 1 AND cancelled_at IS NULL ORDER BY id DESC LIMIT 1r<r!rc3*# UH oS3v M g7fz=?Nrr fs rr #record_purchases..%x%v34s"X%vr a!UPDATE purchases SET platform=?, vehicle_ref=?, manual_entry=0, listing_url=?, status=?, price=COALESCE(?, price), total_price=COALESCE(?, total_price), name=?, bought_date=COALESCE(bought_date, ?), payment_estimated=?, emailed_at=NULL, z# WHERE id=?rstatusprice total_price bought_date estimatedrrrBc3.># UH nTUv M g7frrr r\payments rr r] D+Cawqz+Cc3.># UH nTUv M g7frrr r\rs rr r] <';!s1v';rhc3.># UH nTUv M g7frrr r\refreshs rr r] H+Gawqz+Grhr1rrzno pricerNrOz9 now lists this car, so the row added by hand is now the zD purchase. Everything typed on it is kept; the hand typed price was z's figures now stand.r taken_overc32># UH nTUSLv M g7frr)r r\existings rr r]s!\C[a(1+T"9C[sr=rrru now shows the chipped price (£z), matching the chips on filez"DE E E ~V 7+H5zR((b1779 \\"`#&&**2(* ~ll$;=@FD # q 0   {I8(p.027:G s C8D..EzIn stockr_whenc [U5nURSU45R5nUc[SU35eUSnURSU=(d [ 5X045 UR 5 UR 5 [USU(dSOSUSS 3XS 9 Xs:wa[US S SXsXS 9 gg!UR 5 f=f)a The car has arrived on site. Stamps checked_in_at and moves the car to In stock (or the finished answer given), which is what clears it off the working list (Steven: once it is in stock, everything else is handled in DealerKit). The DealerKit half lives in serve.py.z-SELECT stock_status FROM purchases WHERE id=?Nrrhz?UPDATE purchases SET checked_in_at=?, stock_status=? WHERE id=? milestonezChecked in on sitezChecked in, arrived rrStatus changedr)rryrrrrrr)rrrr_rdrrrs rcheck_in_purchaseri s 4=D llJ'>++38: ;3K=AB B.! Vndff: <  {KT1EYmnrsvtvnwmxWy@CO };1A" H s A,B99C c U=(d SSSnU(dg[U5nURSU45R5nUc[SU35eUS=(d SnUSSU:XaUR 5 gURSX45 UR 5 UR 5 [ US S U3SU=(d SXUS 9 g !UR 5 f=f) zDealerKit's arrival date wins (2026-09-07): rewrite a checked in car's arrival time to the day DealerKit holds, when the two differ. Returns whether anything changed. `when` is a date or ISO datetime.rNrgFz.SELECT checked_in_at FROM purchases WHERE id=?rrpz/UPDATE purchases SET checked_in_at=? WHERE id=?rfz#Arrival date taken from DealerKit, rT)rryrrrrr)rrdrrrrrs rset_checked_in_atrk s JB D  4=D llKk^\eeg ;3K=AB B/"(b s8t   FH[\  {K3VW[V\1]et;$DJ  sA C >#C Cc[U5RS5=(d /Vs1sH,nURS5(dMURS5iM. sn$s snf)zdThe Status answers flagged finished (In stock by default): the car is done and off the working list.rhfinishedrget_purchase_dropdownsrros rfinished_statusesrr sX&-X -D rc [U5nURSU45R5nUc[SU35eUS(d,US=(d S[ U5;a[USS35eUS (a[USS 35eUSnURS X45 UR 5 UR 5 [US S X#S9 Xa:wa[USSSXaX#S9 gg!UR 5 f=f)a0Take a check in back: the arrival time is cleared and the car returns to the working list under `status`. Allowed only while DealerKit has not been checked in (dk_checked_in_at empty); once DealerKit shows the car In Stock, DealerKit is the truth and the change has to be made there (Steven, 2026-09-06).zSSELECT reg, stock_status, checked_in_at, dk_checked_in_at FROM purchases WHERE id=?Nrrprhrr8z# is not checked in, nothing to undorRzH is already In Stock on DealerKit. Change it there and BidBrain follows.zBUPDATE purchases SET checked_in_at=NULL, stock_status=? WHERE id=?rfz,Check in undone, the arrival time is clearedrrrhr)rryrrrrrrr)rr_rrrrrs r undo_check_inrx s 4=Dllp'>++38: ;3K=AB B?#^)<)BK\]aKb(bE |+NOP P ! "E |+stu u.! Y* ,  {K1_eht };1A" H s B+C))C;c[U5nURSU45R5nUR5 Uc[ SU35eU=(d Sn[ U5nX;n[ US5=(d US=(d SU;nU(a'U(d US(a [ S5e[XX1S9 g U(a$U(d[X=(d [U5X#S 9 g [USXUS 9 g !UR5 f=f) aStatus follows the check in (Steven, 2026-09-06): the one fact that says a car has arrived is its arrival time, and the Status word is a view of it, never typed on its own. Picking a finished answer on a car that is not checked in IS the check in; picking any other answer on a checked in car takes the check in back (undo_check_in's rule about DealerKit applies); anything else is a plain field write. Returns what happened: "checkin", "undo" or "set", so the caller can do the DealerKit half of a check in.OSELECT reg, stock_status, checked_in_at, cancelled_at FROM purchases WHERE id=?rrrprhrez+this sale is cancelled, nothing to check in)rrr_rrundoset) rryrrrrrrrirxrvr3) rrrrrrrm to_finishedarriveds rset_stock_statusr- s 4=Dlll'>++38:   {/ }=>> KRE &H#K3'(SS-@-FB8,SG7 ~ JK K+TH{k#@N4,@cU{NEN ' s !C55DOrderedSold)rgambergrey)rrc>^[[U5RS5=(d 05nSU;a[U4Sj[S55$[ US5n[U4SjU55(agUR TUSS.5 XCS'[SU0US9 g) aAdd a Status answer to this dealership's own saved list when it is missing (a list saved before Ordered and Sold existed keeps only what it had, get_purchase_dropdowns replaces the column whole). Finished, so the car counts as arrived. Returns whether anything was added.purchase_dropdownsrhc3J># UHoRS5T:Hv M g7frNrzr rqrs rr 'ensure_status_option..a s_4^q55>U*4^ #c3J># UHoRS5T:Hv M g7frrzrs rr rc s 0Cq55>U "CrFTrcolourrmrY)r get_settingsrrPURCHASE_DROPDOWN_DEFAULTSrr set_settings)rrrsavedrs` rensure_status_optionrY s d#''(<=C DEU"_4N~4^___ u^$ %C 0C 000JJ&dCD.&.T: rTaddc&[RU5nUcgU[U5RS5=(d /Vs1sHoDRS5iM sn;a,U(a$[U[RUS5US9(dgU$s snf)zThe Status word for a DealerKit stage number, or None when the stage means nothing here (Due In, Non-Stock, unknown). With add, a word this dealership's list lacks is added to it first, so DealerKit's stages can always be followed.NrhrrrY)DK_STAGE_STATUSrrorDK_STAGE_COLOUR)r_rrwordrqs rstage_status_wordrk s   v &D | ,B4,H,L,L^,\,b`b,bc,bqEE'N,bcc.t_5H5Hv5V]ab KdsBrc[XS9nUcg[U5nURSU45R5nUR 5 Ub US(ag[ U5n [ US5=(d US=(d SU ;n U=(d Un U (djU=(d SSS=(d, [RR5R5n [XXVU S 9 [US US9 [US S U S U 3XES9 g US=(d SU:Xag[USXdUS9 [US SU 3XES9 g!UR 5 f=f)aDealerKit wins (2026-09-07, "the dealer os purchases list needs to be in sync with dealerkit"): move a car's Status to DealerKit's stage. A car not yet arrived here that DealerKit has In Stock, Ordered or Sold is checked in with DealerKit's arrival date and its DealerKit check in stamped; an arrived car whose word differs is moved with a timeline line. Returns "checkin", "set" or None (nothing to do).rYNrzrerprhrrg)rrr_rdr dealerkitz$Checked in, matched from DealerKit: z there since rzStatus matched from DealerKit: r|)rrryrrrrrrrtodayrrirrr3) rr_stage deliveredrrrrrrmr~labelrds rmatch_dealerkit_stagery sN V /D | 4=Dlll'>++38:   {c.) &H3'(SS-@-FB8,SG MTE R"%J)<)<)>)H)H)J+TTRid3; 7[\a[bboptou5v| L N!rd*{NDM{K3RSXRY1Z`co ! s !D55Ec4[URS55nU(d [S5eURS5=(d SR5(d [S5eURS5nUS;a [S5e[ 5n[ U5n[ U5RS 5=(d /Vs1sH,nURS 5(dMURS 5iM. nnS n URS U45Hn U S =(d SU;dMU n O U b5U S=(d Sn U S(aSU S3OSn [USU U S35eURS5S;aSOSn URS5S;aSOSn[[URS55=(d S[-U -[URS55=(d S[-U--S5nURSSSUS[R"5RS S 3X0SR5[U5[U5URS!5S;aS O [US!5URS5=(d US S"XUS#SURS$5=(d S [URS55[URS55UURS%5=(d SR5=(d S [URS&55URS'5=(d SR5=(d S URS(5=(d SR5=(d S X45nUR n[#XcUS5unnU(aU(aURS)UUU45 UR%5 UR'5 [)US*S+XS,9 U$s snf!UR'5 f=f)-ajA car bought outside the platforms, a local trader for instance (Steven 2026-08-27). The same record, hand fed: platform "Manual", a generated reference, and with no platform behind it nothing ever overwrites it. Marked already emailed, since the accounts email is for platform buys landing overnight, not something typed in by hand. Returns the new purchase id.r8za registration is requiredr!rza vehicle name is requiredr`rza purchase price is requiredrhrmrNzjSELECT platform, bought_date, stock_status FROM purchases WHERE reg=? AND cancelled_at IS NULL ORDER BY idr-rPrbz on z- is already on the working list, bought from z,. Cancel that one first if this replaces it.rtr)rrrurrrsaINSERT INTO purchases (platform, vehicle_ref, reg, name, price, winning_bid, mileage, bought_date, first_seen, emailed_at, stock_status, manual_entry, notes, motorway_fee, transport_fee, vat_total, supplier, retail_estimate_override, person_collecting, collection_arranged, fee_has_vat, transport_has_vat) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)Manualzmanual-rr(rgrurlrqr*rkrmr0rfz Added by handr)rrrr;rrroryr _num_or_nonerr|r}r~rr lastrowidr.rrr)fieldsrrr8r`rrrqrmclashrwhererdfee_vattr_vatvatrbpid_make_models radd_manual_purchaser s FJJu% &C 566 JJv  $" + + - -566 JJw E 788 &C 4=DQ,D155nEKK*K()uuZ(#AEE'NK *<<CEHFLCN#)r(: L  *%;);E49-4HT% ./0bDu$QRWQXY]X^_JJK K zz-04JJ!PQjj!459OOUV\&**U"349XEO!&**["9:?a8KfTUVWYll GQtzz|'7'7';&<=s6NDXDXDZ 5\5<ZZ *j8Tc&BS>T ZZ & 2#cr(C q&**W-5 &**U+ ,l6::k;R.Sjj$* 1 1 3 ;t &**%67 8jj,-3 : : < Djj./52 < < > F$ / $Jmm-TvG v V LLJUTZ\_L` a  sKcM JO*J s+%PP P5*P#J?PPPc(US;aS$[U5$)Nr)rrs rrr s ?40a0rrublue)rrrtgreenrz NO FINANCErrz ON FINANCEnavyNEGATIVE EQUITYredr)skyzMore info neededzVideo requestedteal2zVideo receivedtealzVideo signed off)rrgatez No logbookzLogbook applied forz Logbook herezWe cancelled before collectionzWe cancelled at collectionz$Customer cancelled before collection)rhrirjrkrorqrnrfc[U5RS5=(d 0n0n[R5Hup4X1;aXOUX#'M [ URS55US'U$)aSThe effective option lists: whatever the settings table holds per column, falling back to the factory defaults per column (a saved column replaces that column's whole list, the same per key merge shape as the platforms settings, never a wholesale replace of the dict, which is the exact bug class the DealerWay settings hit on 2026-08-24).rri)rrrrwith_negative_equity_answer)rrrrfactorys rroroL se   " "#7 8 >BE C288: !$5:7;3CGGL4IJC  Jrcn[U=(d /5n[SU55(aU$USSS./-$)a"The On finance? answers with NEGATIVE EQUITY among them (2026-09-16): a dealership's saved list from before it gets the answer added, once, so both dealerships can mark a car whose seller owes more than the price. A list that already has an answer naming equity is left alone. Pure, tested.c3# UHbnSURS5=(d SR5;=(d+ SURS5=(d SR5;v Md g7f)equityrrnegativeNrlower)r rqs rr .with_negative_equity_answer..a sR vqulmHw-2446 6 f*wI][]HdHdHf:f fqusA*A,rrr)rr)optionsrs rrrZ s=  2 D vqu vvv .%@A AArcU=(d /HVnURS5=(d SR5nURS5(aMCSU;dSU;dMQUSs $ g)z@The live On finance? answer that names negative equity, or None.rrretiredrrNr)rrqrs rnegative_equity_answerrf sW ]] UU7^ !r ( ( *uuYX]jAoW:  rcH[U5RS5=(d 0$)Npurchase_columnsrrrYs rget_purchase_columnsrt s   ! !"4 5 ;;r)r i ) i+)KiI)L'ig)'i0i)i0:):i[Di)i\DNi) Na)a/ui)0u?)@O/)P_i)`oi)p8i=)8_i)_韆i)順#)ri)ii)ir)rri!)rri?)rriS)rriq)rrr)rri)rrr)rii)irr)riǯr)iȯriW)rii)iri)rii)iriG)rri)rrr)rri_)rii)iii')iii)iri)r>rc[U5RS5=(d 0n0n[R5Hup4X1;aXOUX#'M UHnX2;dM XX#'M U$)a}The effective fee band tables, per platform, saved table replacing that platform's factory one (per key merge, same shape as get_purchase_dropdowns). A platform with no table at all (Auction4Cars, Dealer Auction, DealerWay, Manual) simply is not here: a chip on one of those never computes a fee, it says the fee may change and to check the invoice instead, never a guessed figure. fee_bands)rrPURCHASE_FEE_BAND_DEFAULTSr)rrrplatrs r get_fee_bandsr sf   " "; / 52E C399; #'=EKg < ? CI Jrc~Ucg[U5RU/5Hup4nX1s=::a U::dMUs $ M g)zThe platform's purchase fee at this price, from the effective band table, None when the platform has no table or the price sits outside every band. Never guessed.N)rr)r-r`rrErFrs rfee_forr sF }$T*..x<  " J = rcRUcg[U5[U5:XaSUS3$SUS3$)zA money figure the way Steven reads one: no pence unless there are pence. Only ever for a sentence on screen, never for arithmetic.zno price on filerrr)rrrs r _money_wordsr s8 y! %aCF 2VAd8 I&48IIrcUVs/sH o=(d SPM nn[U5S::aU(aUS$S$SRUSS5S-US-$s snf)zwMotorway and Carwow. Motorway, Carwow and Dealer Auction. Steven's house style, so no dashes and no serial comma games.rPrrrWN and )rr)wordsws rrQrQ sd/4 4e $$ $eE 4 5zQ uQx8&88 99U3BZ 7 *U2Y 66 5sAcR^0nU=(d SH_nURS5(aM[URS55nU(dM>URU/5RU5 Ma 0nUR 5GHup5[ U5S:aMUVs0sHo"SURS5_M nn[ SUR 555n[ U5[ U5:nU(+=(a& [ U5S:=(a US S USS :Hn U(aU(dU (dUS SOS mTbUS S US S - OS n UGHnUV s/sH=oSUS:wdMU SU RS 5XkSU RS 5S.PM? n n U V s/sHoS PM n n [URS 5/U -5nTbTUS:Hn[U4SjU55nUVs/sHnUST:wdMUPM nnUSUSURS 5=(d SS[UT5S[UVs/sHnURS 5PM sn5S[ U5S:XaSOSS[U 5S3nO.U (aS nUSUS[US S 5S3nO S nUSUS3nU[ U5U URS 5/U -XbSTXXUUSUS3S. XBS'GM GM U$s snfs sn fs sn fs snfs snf)a {purchase id: its double win block} for every live purchase that shares a registration with another live purchase. Cancelled sales are not in the running, and a car with no readable plate cannot be matched to anything so it never joins a group. Pure, so the page, the email, the preflight and the tests read one rule. The block carries `others` (the id, platform and all in of each twin), `platforms` (every platform in the group, this row's first), `all_in`, `cheapest_id`, `keep` (this is the one to keep), `saving` (what cancelling the dearer sale saves), `unsure` (a price is missing, so nothing is recommended), `tied` (they cost the same), and two ready written sentences, `advice` for the screens and `hold` for the DealerKit preflight, so both sides word it identically. rrer8rsr1all_inc36# UHupUcM X!4v M g7frr)r rrs rr double_wins.. sI<411# UHoST:XdM Uv M g7f)r1Nr)r g cheapest_ids rr r sGu!$;0FAAus  z was won twice, on z . Keep the firstz sale at z all in and cancel the r.salesalesz, which saves .z, and both cost z< all in. Cancel whichever you like and the other one stands.zG. One of them has no price on file yet, so pick which to keep yourself.zS. Cancel one of the sales first, or DealerKit ends up with two records for one car.) r8countra platformsrr keepsavingunsuretiedadvicehold) rr setdefaultrrrr rQnextr)rgroupsrr8rgroupcostpricedrrrrqra others_nameseveryonerbestr worstrrr s @rrr sjF ZRZ 55 e % #r"))!,  Clln  u:> 278%Q$x(%8I4::<IIVs5z) PF aPF1IaLF1IaL4P'-fTfQilPT 3>3J&*Q-&)A,.PTA %<$!$1T7(:UQtW!%% 2C!%gquu]?SU$ <4::6ajM6L:"AEE*$5#6#EFH&"ag-GuGG$)DEqQtW -CED E!4XJk!XXj1N)&)A,789?? E!4XJ?OOSZ6eeJ/0<?w- & 5 3H:>$$ C$L3%X JS9<; E*Ls*L)L<1L3L LLL$cJ[U5nURS5R5Vs/sHn[U5PM nn0nURS5H0nUR US/5R [U55 M2 UHqnSH4n[ R"URU5=(d S5X&'M6 URS5nU(a[ R"U5OSUS'Ms [U5RS5nUHnnURS 5n URS 5n XS 'U b XS 'S US 'OU bU(aX:a XS 'SUS 'OS US 'URS5n U cMjXS'Mp [U5RS/5n UGHnURUS/5n XS'[SU 55US'[U5US'S US'UScKURS5S:Xa6[URS5XRS55nUb XS'SUS'USb USUS- OSUS'URS5URS5nnU HnUScM USUSnnM UUsUS 'US!'XRS"5URS#5U/n[!S$U55(a[S%U55OSUS&'URS5S:H=(a [#URS55nUSb+US(aU(dUSUS&=(d S'-US('GMURS)5bURS)5OURS5US('GM [%U5nUHnURUS5US*'M UUR'5 $s snf![[4a /X&'GM\f=f![[4a SUS'GM~f=f!UR'5 f=f)+aEvery recorded purchase, newest bought first (falling back to newest recorded first for the handful of rows with no bought_date), for the purchases working list. Cancelled sales stay in the table flagged with cancelled_at (never deleted: deleting also clears emailed_at, letting a later read email accounts about an old car again), and the page filters them into their own view. service_history_photos and photo_urls are both stored JSON encoded (a plain list of URLs each, see motorway.read_service_history_photos and read_purchase_gallery), decoded back to real lists here, [] when never read or genuinely empty, same convention as saved_views.criteria. This is also the single place the CANONICAL MONEY is derived, so every reader (the page, the DealerKit push, the bias bid comparison, the accounts email) agrees rather than each keeping its own fallback chain, which is exactly how the page ended up with five disagreeing definitions of "what was paid" (found 2026-08-27): bid_original what the car was won at, before any chips. None for a Motorway row with no winning_bid rather than the all in figure price holds there: honest absence, never a number that quietly includes the fees. chip_total the sum of every chip, 0 when none. car_price bid_original less the chips. What the car itself cost. effective_fee the platform fee after any re banding chip froze a new effective_vat one, else the figures as read. Never recomputed. fees_total effective fee + transport + protect + effective VAT, None when nothing at all is known. all_in car_price + fees, what actually left the bank. Falls back to total_price/price for a row whose bid was never known. zPSELECT * FROM purchases ORDER BY COALESCE(bought_date, first_seen) DESC, id DESCz4SELECT * FROM purchase_chips ORDER BY created_at, idr)rDrErFrXrYrZrrMNretail_price_capr*rcretail_estimate_rawFretail_estimate_cappedTrdrbr>r1rc3*# UH oSv M g7frMrrs rr !list_purchases.. s!=u!H+ur r bid_originalbid_provisionalr-r`r_rr=r@rRrSr effective_vatr>r?c3(# UHoSLv M g7frrr r"s rr r& s%CUtmUsc3.# UH ocMUv M g7frrr+s rr r& s"Ee11es  fees_totalrrra double_win)rryr{rrrr>r?rr@rrrrQrrHrrBrr)rrrrchips_by_purchaserr raw_checkcaprawoverride date_overridemw_bandsrestrrpartslist_is_all_infounds rlist_purchasesr:$ sC@ 4=Dg $ ^! (*!!1tAw!TUA  ( (=)92 > E Ed1g NVAJ"#zz!%%,*>$?AHJj)I %9B 9 5* 04 $$%78A%%)*Cuu78H'*# $#'/#$.3*+SSY'*#$.2*+.3*+EE"<=M('4#$)4!&**:r:A%))!D'26EgJ!!=u!==AlO -a 0An  $)A  (QUU:->*-L.quuW~xxY?(+n%+/A'(!">!2!> /!L/ADH kNuu^,aeeK.@CY<+ |Qy\C69# 2Ao / 2%%0!%% 2FLE"%%CU%C"C"C #"Ee"EEIM lOeeJ/:=N".says_no s9 GGIOO qyIFaKI7a<I9>IrcDUR5nSU;=(d SU;$)Nrr)rrAs r says_negative%finance_answer..says_negative s GGI1}/ a/rTrrFN)rr)rfinancedrqrrBrEnosyesess rfinance_answerrJ s  2 R !eeI.>A155>A D RJ0 $ ;t!wqz'::1W:tC ;"& cAWQwZ-@ZWXY`WaIbZQwZE c4CJ!OQx5SX]1v  S < ds.C"C"C"C', C';C,C,$ C,c[RR5[R"US9- RSS9n[ U5n0nUR SU45H>n[ R"US5nX;dM$XpcM+XpU[US5'M@ UUR5 $![[4a Mgf=f!UR5 f=f)a {reg: value} for one fact every platform's detail page read saved in the last `days` days (listing_detail_cache holds the fields a read changed, keyed by reg), newest wins. For facts worth keeping past the cache's own short reuse window, the finance flag above all.rrrzeSELECT cache_key, detail_json FROM listing_detail_cache WHERE fetched_at >= ? ORDER BY fetched_at ASCr<r=) rrrrrryr>r?r@rrr)rrrcutoffrrrrs rrecent_detail_factsrM s ##%(:(:(EE P PZc P dF 4=D <<w C JJs=12zah2348Ic+./0 z*   s< C &B6?C C C 6C C C  C Cc[U5nURSU45Vs1sHn[US5iM snUR5 $s snf!UR5 f=f)zuThe vehicle refs already on the Didn't win list for a platform, so a sync can stop at the first car it already knows.z2SELECT vehicle_ref FROM lost_bids WHERE platform=?rX)rryrr)r-rrrs r lost_bid_refsrO sk 4=D/3|| @8+0OP0O!Am$%0OP P sAA A AA$cr[U5n[5n/nUHnURSUS[US545R 5nU(aM=URSUS[US5[ UR S55UR S5UR S5UR S5UR S 5X5R S 54 5 UR[U55 M UR5 UUR5 $!UR5 f=f) avStores every real lost auction (Mark 2026-08-24, the "didn't win" section, motorway.read_didnt_win's own shape). A lost outcome never changes once recorded, unlike a purchase's own status, so this is a plain insert on a new (platform, vehicle_ref), skipping one already on file rather than any fill-in-later or lock-once logic. Returns only the rows that were genuinely new.z;SELECT id FROM lost_bids WHERE platform=? AND vehicle_ref=?r-rXzINSERT INTO lost_bids (platform, vehicle_ref, reg, name, sold_for, our_bid, date_bid, first_seen, listing_url) VALUES (?,?,?,?,?,?,?,?,?)r8r!sold_forour_biddate_bidr) rrryrrrrrrrr)rrrrrrrrs rrecord_lost_bidsrT s 4=DfA||M:Am$4 5688@   LL1:Am$4 5yu7Nvj 11553Cz"C})=?  @ JJtAw    s DD$$D6c[U5nURS5R5Vs/sHn[U5PM snUR 5 $s snf!UR 5 f=f)zqEvery recorded lost auction, newest bid first, for the Didn't win admin page. Mirrors list_purchases's own shape.zMSELECT * FROM lost_bids ORDER BY COALESCE(date_bid, first_seen) DESC, id DESCrr"s rlist_lost_bidsrV sg 4=D!% [" (*""AQ"  rc"[U5nURS5Vs/sH o"SUS4PM nnX1RS5Vs/sH o"SUS4PM sn- nUUR5 $s snfs snf!UR5 f=f)aVEvery real winning price BidBrain knows of, for a suggested bid (Mark 2026-08-24: "lets try and build a AI suggested bid... based on both won and lost (but sold for) data"). A won purchase's own winning_bid (or price, for the handful of older rows without a full breakdown) is exactly what it took to win that specific car, no less real a data point than a lost auction's own sold_for, the price someone else paid to win it. Both are precise, real market clearing prices, just for a car we ended up with versus one we did not. Returns a plain list of (name, amount) pairs, name still the raw "Make Model ..." free text neither table breaks out separately; bid_suggest.model_key does the grouping. No make/model filtering here, a genuinely small or malformed name is still handed on, left for the caller to decide there is not enough to group it usefully.zqSELECT name, COALESCE(winning_bid, price) AS amount FROM purchases WHERE COALESCE(winning_bid, price) IS NOT NULLr!rzISELECT name, sold_for AS amount FROM lost_bids WHERE sold_for IS NOT NULLrryrrrrrs r bid_compsrZ s 4=D26,, =3>?3>Q6AhK(3>? << W4YZ4YaF)Qx[)4YZ Z ?Z s'A<A2A<A7A<2 A<<Bc[U5nURS5Vs/sHn[U5PM snUR5 $s snf!UR5 f=f)aPEvery lost bid with no mileage on file yet, for the retail enrichment pass (Mark 2026-08-24: "% of cazana retail... should improve the accuracy"). Each one's own listing_url is still a real, viewable Motorway page even long after the sale (proven live), which is where the mileage comes from, the Didn't win list itself never showed it.z9SELECT * FROM lost_bids WHERE mileage IS NULL ORDER BY idrryrrr"s rlost_bids_missing_mileager]- sa 4=D!% G"IJ"IAQ"IJ J A AA A Ac[U5nURS5Vs/sHn[U5PM snUR5 $s snf!UR5 f=f)aEvery lost bid with a real mileage on file but never yet checked against Cazana, ready for the second half of the enrichment pass (the Cazana lookup itself). A car genuinely confirmed to have no Cazana match is cazana_checked=1 with cazana_retail still NULL, and is correctly never returned here again, the same "a confirmed no match is cached too, not just a hit" rule already used for the daily Glass's/Cazana valuation_cache.znSELECT * FROM lost_bids WHERE mileage IS NOT NULL AND (cazana_checked IS NULL OR cazana_checked=0) ORDER BY idr\r"s rlost_bids_missing_retailr`< sd 4=D!% K"LM"LAQ"LM M r^row_idc[U5nURSX45 UR5 UR5 g!UR5 f=f)Nz)UPDATE lost_bids SET mileage=? WHERE id=?r)rar(rrs rset_lost_bid_mileagercM s> 4=D @7BST   rc[U5nURSX45 UR5 UR5 g!UR5 f=f)zRecords a Cazana lookup outcome, match or a confirmed no match alike (cazana_retail stays NULL either way then, cazana_checked=1 marks it as settled either way, so lost_bids_missing_retail never offers this row again).zAUPDATE lost_bids SET cazana_retail=?, cazana_checked=1 WHERE id=?Nr)rar]rrs rset_lost_bid_retailreV sC 4=D X$- /   rc8[U5nURS5Vs/sHo"SUSUSS- 4PM nnX1RS5Vs/sHo"SUSUS4PM sn- nUUR5 $s snfs snf!UR5 f=f) aEvery real winning price BidBrain knows of AND can also relate to a real Cazana retail value, for the % of retail model (Mark 2026-08-24: "if we also knew that a 3 series winning bid was 90% of cazana retail and im always bidding 88% im lossing most of them... having this info should improve the accuracy"). Purchases already store a Cazana figure as cazana_115 (retail times 1.15, the buying brain's own uplifted figure), divided back down here to the plain retail value so it is directly comparable to a lost bid's own, unscaled cazana_retail. Returns (name, amount, retail) triples, only for a row that genuinely has both; most rows will not, this is a strict subset of bid_comps, deliberately never a guess at a missing retail figure.zSELECT name, COALESCE(winning_bid, price) AS amount, cazana_115 FROM purchases WHERE COALESCE(winning_bid, price) IS NOT NULL AND cazana_115 IS NOT NULLr!rrAgffffff?zlSELECT name, sold_for, cazana_retail FROM lost_bids WHERE sold_for IS NOT NULL AND cazana_retail IS NOT NULLrQr]rXrYs rbid_comps_with_retailrgd s 4=D JN,, XKYZKYQ6AhK<4)?@KYZ  GJHIJHAF)Qz]Ao,>?JHI I ZI s'BA=BB&B= BBc*[U5nURS5Vs/sHn[U5PM nnX1RS5Vs/sHn[U5PM sn- nUUR5 $s snfs snf!UR5 f=f)a Every real winning price, with whatever else is known about that exact car, for the like for like suggested bid model (Mark 2026-08-25: "compare year, transmission, engine size, mileage so the ai is comparing as similar cars as it can", then "add the transmission and year, engine cc, grade, from the scrape for better matching"). Returns one dict per comp: name, amount, retail (may be None), mileage, year, transmission, engine, grade (any of the last five may be None, never guessed). Retail, mileage, year, transmission, engine and grade all only ever fill in from a real BidBrain read (a Cazana lookup, or a sighting/purchase record), so most rows here will still be missing some of them; bid_suggest.py scores on whatever is actually present rather than requiring all of it.aSELECT name, COALESCE(winning_bid, price) AS amount, CASE WHEN cazana_115 IS NOT NULL THEN cazana_115 / 1.15 END AS retail, mileage, year, transmission, engine, grade, bought_date AS date FROM purchases WHERE COALESCE(winning_bid, price) IS NOT NULL AND COALESCE(manual_entry, 0) = 0zSELECT name, sold_for AS amount, cazana_retail AS retail, mileage, year, transmission, engine, grade, date_bid AS date FROM lost_bids WHERE sold_for IS NOT NULLr\rYs rbid_comps_fullri~ s 4=D "& 0 "1 2 "1AQ "1 2 ,, 8#9:#9Qa#9: : ' 2: s'BA6B A;B6 BBc[U5nURS5Vs/sHn[U5PM snUR5 $s snf!UR5 f=f)zEvery lost bid missing year, transmission, engine or grade, for the database first backfill pass (Mark 2026-08-25). No browser needed, this is a pure join against sightings already on file.zqSELECT * FROM lost_bids WHERE year IS NULL OR transmission IS NULL OR engine IS NULL OR grade IS NULL ORDER BY idr\r"s rlost_bids_missing_fieldsrk s] 4=D!% =">?">AQ">? ? r^c[U5nURS5Vs/sHn[U5PM snUR5 $s snf!UR5 f=f)zcEvery purchase missing year, transmission, engine or grade, same shape as lost_bids_missing_fields.zqSELECT * FROM purchases WHERE year IS NULL OR transmission IS NULL OR engine IS NULL OR grade IS NULL ORDER BY idr\r"s rpurchases_missing_fieldsrm s] 4=D!% =">?">AQ">? ? r^c[U5nURSXX4U45 UR5 UR5 g!UR5 f=f)zFills in whichever of year/transmission/engine/grade is still missing on a lost bid, never overwrites a value already on file (a sighting used to backfill an old row should not clobber a figure a later, more specific pass already found).zUPDATE lost_bids SET year=COALESCE(year, ?), transmission=COALESCE(transmission, ?), engine=COALESCE(engine, ?), grade=COALESCE(grade, ?) WHERE id=?Nrrar%rar`r)rrs rset_lost_bid_fieldsrp sN 4=D  N 7  9   %AAc[U5nURSXX4U45 UR5 UR5 g!UR5 f=f)NzUPDATE purchases SET year=COALESCE(year, ?), transmission=COALESCE(transmission, ?), engine=COALESCE(engine, ?), grade=COALESCE(grade, ?) WHERE id=?rros rset_purchase_fieldsrs sL 4=D  N 7  9   rqcSSKn[U5nSnUGHnURS5nURSURS5[ URS55URS5URS5URS 5URS 5URS 5URS 5URS 5URS5URS5U(aUR "U5OSURS54 5 US- nGM UR 5 UUR5 $!UR5 f=f)a Store sold cars from the monthly reports, one dict per car with keys stock_number, reg, make, model, sold_date, days_to_sell, sale_price, purchase_price, margin, prep_total, source. Re run safe via the (stock_number, sold_date) unique key; existing rows are refreshed.rNr4aYINSERT INTO sales_history (stock_number, reg, make, model, sold_date, days_to_sell, sale_price, purchase_price, margin, total_margin, prep_total, prep_split, source) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?) ON CONFLICT(stock_number, sold_date) DO UPDATE SET reg=excluded.reg, make=excluded.make, model=excluded.model, days_to_sell=excluded.days_to_sell, sale_price=excluded.sale_price, purchase_price=excluded.purchase_price, margin=excluded.margin, total_margin=excluded.total_margin, prep_total=excluded.prep_total, prep_split=COALESCE(excluded.prep_split, sales_history.prep_split), source=COALESCE(excluded.source, sales_history.source) stock_numberr8rVrW sold_date days_to_sell sale_pricepurchase_pricemarginr3 prep_totalrr)r>rrryrrrr)rrr>rr:rrs rrecord_sales_historyr| s  4=D AEE,'E LL O~& !%%,(?vw{!3QUU>5J|$aee,<&=quuX~&l(;&+E"quuX H & FA+,   s D)E EcSSKJnJn SSKn[ U5nUR S5R 5Vs/sHn[U5PM nn0nUHTnU"URS5URS55nU(dM3URU/5RU5 MV UR S5 [5n S n UR5GHup[U 5n U "U Vs/sHoUS PM sn5n U "U Vs/sHoUS PM sn5n[S U 55n0Snn/nU HnURS 5(dMUR"US 5nUS- nU"U5nUbURU5 UR5H'unnURUS5U=(d S-UU'M) M U "U5nU(aLUR$"['UR55VVs0sHunnU[)UU- 5_M snn5OSn0nU HnURS5=(d SR+5nU(dM4URUS//S.5nUS==S- ss'US bUSRUS 5 US cM|US RUS 5 M UR5VVs0sHqunnUUSUS(a'[-[US5[US5- S5OSUS (a&[)[US 5[US 5- 5OSS._Ms nnnUR SU SRS5U SRS5XU b [-U S5OSUb [)U5OSUb [)U5OSUXR$"U5U 4 5 GM UR/5 [U5UR15 $s snfs snfs snf![ ["4a GMf=fs snnfs snnf!UR15 f=f)aRoll sales_history up into learned_models, one row per model_key: how many sold, average days to sell, average prep, average margin, how many kept a positive margin after prep (spread_held), and the per platform split. Replaces the whole table each time, cheap at this scale.r)r6 actual_preprNzSELECT * FROM sales_historyrVrWzDELETE FROM learned_modelscxUVs/sH ocMUPM nnU(a[U5[U5- $S$s snfr)rQr)valsrs ravg#rebuild_learned_models..avgs5#5t!AtD5.2CID ) < <6s77rwrzc3L# UHoS=(d SS:dMSv M g7f)rzrrNrrs rr )rebuild_learned_models..s A$QX;+;!q*@qq$s$ $r4rr)r:rrzr:r)r:avg_days avg_marginaINSERT INTO learned_models (make, model, model_key, n_sold, avg_days_to_sell, avg_prep_total, avg_margin, prep_split, spread_held, by_platform, updated_at) VALUES (?,?,?,?,?,?,?,?,?,?,?))rr6r~r>rryr{rrrrrrrrQr?rr@rr rr;rrr)rr6r~r>rrsoldrrrrrr:rrzr split_sumssplit_nactualssaprrpreprrr4platssrcr" by_platforms rrebuild_learned_modelsrs < 4=DF!%.K!L!U!U!WX!WAQ!WXAAEE&M155>:C   c2 & - -a 0  12f = ICD A484a.)489Dt4t!H+t45FA$AAD#%aJG55&&! JJq7qLG$QB~r*'(wwy V*4..a*@FKa*P 3(1wB$B$$B6c[U5nURS5R5Vs0sHo"S[U5_M snUR 5 $s snf!UR 5 f=f)zGAll learned model figures keyed by model_key, for the daily card notes.zSELECT * FROM learned_modelsr6rr"s r learned_forrls\ 4=D15>\1]1f1f1hi1hA+Q'1hi j s!AAAAA0c r[U5n[XS9nU(d/UR5 $URSSR S[ U5-5S3/UQUP75R 5nUVs/sHn[U5PM snUR5 $s snf!UR5 f=f)aPrevious sightings of this registration with a recorded reserve, before sale_date, newest first. Used to spot a reserve that has been lowered on a car doing the rounds. Returns a list of {sale_date, reserve, cap_clean}. reg may carry both of a car's plates, see sighting_plates.rz`SELECT sale_date, reserve, cap_clean FROM sightings WHERE reg IN (rrzR) AND sale_date < ? AND reserve IS NOT NULL ORDER BY sale_date DESC)rrrryrrr{r)r8rrrrrrs rreserve_historyrus 4=D s. ||!hhsSY789' * d I   (* "&&AQ& ' s#B$AB$7B B$B$$B6summaryc /nURS05nU(a_/n[U5H(nX$nURUSUSUS:XaSOS35 M* URSSRU5-S -5 URS /5n[ U5S:a&URS S RU5-S -5 U$)zzTurn a repeat_summary into plain card lines, house style, no hyphens. Returns an empty list when there is nothing to flag.rz previous r.rr r zSeen before within 30 days: rWrrzIn both rz today.)rr rrr)rlinesprevr7r-r:rs rrepeat_flag_linesrs E ;;z2 &D t HA LLA3j !a1fF'3RS T%  3dii6FFLM ;;+R 0D 4y1} Z',,t"44y@A Lrc  [U5nU(dg[U5nURSXX#U[5U=(d [ 545 UR 5 UR 5 g!UR 5 f=f)aHide a car from all future daily lists, with a reason. Upserts one row per registration and resets reviewed so the new reason is surfaced again. updated_at is the moment of the change (UTC ISO); an action applied from Dealer OS passes the action's own time so the echo cannot bounce.NaINSERT INTO hidden_cars (reg, reason, make, model, name, hidden_at, active, reviewed, updated_at) VALUES (?,?,?,?,?,?,1,0,?) ON CONFLICT(reg) DO UPDATE SET reason=excluded.reason, make=excluded.make, model=excluded.model, name=excluded.name, hidden_at=excluded.hidden_at, active=1, reviewed=0, updated_at=excluded.updated_at)rrryrrrr)r8rrVrWr!rr/rs rhide_carrsi C.C  4=D   3 $tTVZ5L79 M    s AA11Bc[U5n[U5nURSU=(d [5U45 UR 5 UR 5 g!UR 5 f=f)Nz9UPDATE hidden_cars SET active=0, updated_at=? WHERE reg=?)rrryrrr)r8rr/rs r unhide_carrsS C.C 4=D P -GIs3 5   s 5AA0c [U5nURS5 [5nUHnURS[UR S55[ UR S55UR S5=(d SUR S5=(d SUR S5UR S 5UR S 5U45 M UR 5 UR5 g !UR5 f=f) zhReplace the DealerKit in stock records with this read (whole list, a car that has left is gone from it).zDELETE FROM dealerkit_stockzINSERT OR REPLACE INTO dealerkit_stock (vehicle_id, reg, make, model, purchase_cost, retail_price, life_cycle_status, read_at) VALUES (?,?,?,?,?,?,?,?)rr8rVrrW purchase_cost retail_pricelife_cycle_statusN)rryrrrrrr)rrrrrs rsave_dealerkit_stockrs 4=D  23iA LL/QUU<()9QUU5\+BAEE&MDWUWYZY^Y^_fYgYmkm'~)>FY@Z\_a  b   s CC99D rYc"[U5n[R"U/UQ76HBnURSU45R 5nU(dM-USs UR 5 $ UR 5 g!UR 5 f=f)aDealerKit's own vehicle id for a plate, from the stock records the in stock read keeps (2026-09-12): a sold car was in stock once, so its id is usually here, and the sale price read needs no list scan. These rows carry DEALERKIT's plate, so alt_regs takes the car's other plates (2026-09-20): a car whose seller kept a private plate is in here under the plate underneath, not the one the purchase was recorded on. Tried in the order given, so the first plate always wins.zPSELECT vehicle_id FROM dealerkit_stock WHERE reg=? ORDER BY read_at DESC LIMIT 1rN)rrrKryrr)r8ralt_regsrplaters rdealerkit_stock_vehicle_idrs~ 4=D&&s6X6E o#X''/xz q& 7   sAA<A<(A<<Bc [U5n[U5nURSU45R5nU(aUSUR 5 $URSXX#[ R R 5RSS945nUR5 URUR 5 $!UR 5 f=f)zOne car Dealer OS wants the deal price of. A car already waiting is not queued twice (DealerKit sends every sale notice twice).z?SELECT id FROM sale_price_reads WHERE reg=? AND done_at IS NULLr1zWINSERT INTO sale_price_reads (reg, api_id, name, sold_at, queued_at) VALUES (?,?,?,?,?)rr) rrryrrrrrrr)r8api_idr!sold_atrrrrbs rqueue_sale_price_readrs 4=D n LLZ]`\b c l l n T7 llt8I8I8M8M8O8Y8Ycl8Y8mnp }}  s7B?AB??Cc[U5nURS5R5Vs/sHn[U5PM snUR 5 $s snf!UR 5 f=f)Nz@SELECT * FROM sale_price_reads WHERE done_at IS NULL ORDER BY idrr"s rpending_sale_price_readsrsU 4=D!%.p!q!z!z!|}!|AQ!|} ~ rc[U5nURS[RR5R SS9[ U5SSU45 UR 5 UR5 g!UR5 f=f)Nz:UPDATE sale_price_reads SET done_at=?, result=? WHERE id=?rri)rryrrrrrr)rarrrs rfinish_sale_price_readrsr 4=D Q''++-777KSQW[Y]Z]M^`fg i   s AA88B c 8[U5nSnU=(d /GHFnU=(d 0n[URS5=(d2 URS5=(d URS5=(d S5R5n[URS5=(d S5R5R 5nU(aU(aU(dMUR SXxU[ URS5=(d S5=(d SURS5=(d SURS 5=(d SU[545 US - nGMI UR5 UUR5 $!UR5 f=f) a.The cars one shortlist.read action named, in the order Dealer OS sent them (soonest closing first). A car already waiting keeps its place and takes the newer ask; a car read earlier today is wanted again, its old answer kept until the new one lands. Returns how many rows are now waiting from this ask.rrr8rrr-a|INSERT INTO deep_reads (car_key, platform, sale_date, reg, listing_url, name, asked_at, queued_at, wanted, attempts) VALUES (?,?,?,?,?,?,?,?,1,0) ON CONFLICT(car_key, platform, sale_date) DO UPDATE SET reg=COALESCE(excluded.reg, deep_reads.reg), listing_url=COALESCE(excluded.listing_url, deep_reads.listing_url), name=COALESCE(excluded.name, deep_reads.name), asked_at=MAX(COALESCE(excluded.asked_at, ''), COALESCE(deep_reads.asked_at, '')), queued_at=excluded.queued_at, wanted=1, attempts=0Nr!r) rrrr;rryrrrr) rrasked_atrrr:rrr-s rqueue_deep_readsr s, 4=D AARAaeeElPaeeElPaeeM6JPbQWWYC155,2399;AACHhi LLK 9QUU5\5GR+H+PD}%-quuV}/DhPTPVX Y FA#$   s E'FFc[U5nURS5R5Vs/sHn[U5PM snUR 5 $s snf!UR 5 f=f)zEvery car still waiting to be read, the order it was asked in, leaving out one that has already been tried DEEP_READ_MAX_ATTEMPTS times (deep_read_pass answers those with an error itself).z3SELECT * FROM deep_reads WHERE wanted=1 ORDER BY idrr"s rpending_deep_readsr.sn 4=D!% A"CCK8:"NO"NAQ"NO O rc[U5nURSU45 UR5 URSU45R5nU(a[ US5OSUR 5 $!UR 5 f=f)zAOne more pass has started on this row. Returns the attempt count.z4UPDATE deep_reads SET attempts=attempts+1 WHERE id=?z*SELECT attempts FROM deep_reads WHERE id=?r_r)rryrrrr)rarrrs rstart_deep_readr:sl 4=D KfYW LLEy Q Z Z \%&s1Z=!A-  s AA99B c<[U5nURSU=(d 0RS5=(d [5[R "U=(d 05U45 UR 5 UR5 g!UR5 f=f)zgThe answer for one car, read or failed: kept whole as the push's deep block, the row no longer waiting.zAUPDATE deep_reads SET wanted=0, read_at=?, deep_json=? WHERE id=?read_atN)rryrrr>rrr)radeeprrs rfinish_deep_readrFsm 4=D Xzr&&y1;TVTZZ PR=SU[\ ^   s A+B Bc2[U5n0nURSU45R5H'n[R"US5X4SUS4'M) UUR5 $![ [ 4a MPf=f!UR5 f=f)zEvery answered deep read for a sale date, keyed (platform, car_key), for the push to hang on each car record. A car still waiting with no earlier answer is not here (its deep stays null, Dealer OS shows it as reading from the ask it logged).z_SELECT platform, car_key, deep_json FROM deep_reads WHERE sale_date=? AND deep_json IS NOT NULL deep_jsonr-car_key)rryr{r>r?r@rr)rrrrrs rdeep_reads_forrRs 4=D (l,,4HJ7A 59ZZ+5Oz]AiL127  z*   s.&B#A-B-B=BBBBcD[RR5[R"US9- R 5n[ U5nUR SU45nUR5 URUR5 $!UR5 f=f)z0Rows for sales more than keep_days ago, dropped.rz*DELETE FROM deep_reads WHERE sale_date < ?) rrrrrrryrr_r) keep_daysrrLrrbs rprune_old_deep_readsresqmm!!#h&8&8i&HH S S UF 4=DllG&S ||  s .B Bc[U5nURS5R5Vs/sHn[U5PM snUR 5 $s snf!UR 5 f=f)Nz*SELECT * FROM dealerkit_stock ORDER BY regrr"s rdealerkit_stock_listrqsU 4=D!%.Z![!d!d!fg!fAQ!fg h rc [U5nU(dg[U5n U RSX=(d S[R"[ U=(d /55U=(d SU=(d SU=(d SU=(d S[ 5U=(d [54 5 U R5 U R5 g!U R5 f=f)aMRelease a car from the rules that held it back (Steven 2026-09-10, on Dealer OS: "click a button to say un hold it back and add this to the list and give a reason why, so it teaches bidbrain more"). rules is the list of reasons the car was held on, kept beside his own reason so the two can be read together when the rules are tuned.NaINSERT INTO released_cars (reg, reason, rules, make, model, name, who, released_at, active, reviewed, updated_at) VALUES (?,?,?,?,?,?,?,?,1,0,?) ON CONFLICT(reg) DO UPDATE SET reason=excluded.reason, rules=excluded.rules, make=excluded.make, model=excluded.model, name=excluded.name, who=excluded.who, released_at=excluded.released_at, active=1, reviewed=0, updated_at=excluded.updated_atr) rrryr>rrrrrr) r8rrulesrVrWr!rrr/rs r release_carrys C.C  4=D  3,B 4 +< =tzr5;TV ZRDFJ,C') E    s BCCc[U5nURS[U545R5nU(aUS=(d USOSUR 5 $!UR 5 f=f)Nz=SELECT updated_at, released_at FROM released_cars WHERE reg=?r/ released_atrryrrrr8rrrs rreleased_updated_atrsa 4=D LLX#C.* ,,4HJ 89,31]#3tC  AA""A4ct[U5nURS5R5n0nUHMn[R"US=(d S5nUS=(d SUS=(d SUSUS.X4S 'MO UUR 5 $![ a /nNKf=f!UR 5 f=f) zBreg to {reason, who, released_at, rules} for every active release.zMSELECT reg, reason, who, released_at, rules FROM released_cars WHERE active=1rrrrrr)rrrrr8)rryr{r>r?rr)rrrrrrs r released_maprs 4=D ||-..6hj A  1W:#56(){'8b5R,-m,!=AQ!=> ? s2A,A'A,'A,,A>c[U5nURS5R5nUVs/sHn[U5PM snUR 5 $s snf!UR 5 f=f)z]Every active hide with its reason and timestamps, for the Dealer OS echo and the hidden list.z|SELECT reg, reason, make, model, name, hidden_at, active, updated_at FROM hidden_cars WHERE active=1 ORDER BY hidden_at DESCrrrrrs r hidden_listrsb 4=D|| FGGOxz "&&AQ& ' rc[U5nURS[U545R5nU(aUS=(d USOSUR 5 $!UR 5 f=f)Nz9SELECT updated_at, hidden_at FROM hidden_cars WHERE reg=?r/ hidden_atrrs rhidden_updated_atrs` 4=D LLT#C.* ,,4HJ 67,11[>TA  rc[U5nURS5R5nUVs1sHo3SiM snUR5 $s snf!UR5 f=f)z*The set of registrations currently hidden.z*SELECT reg FROM hidden_cars WHERE active=1r8rryr{rrs r hidden_regsrsU 4=D||HIRRT"&'$Q%$' ( #AAAAA(c [U5n/n[5nUHn[URS55nU(dM&UR SU45R 5nUR SXeRSS5URSS5URSS5U45 U(aMUR U5 M UR5 UR5 U$!UR5 f=f)zRecord cars bought on a platform whose sale was then cancelled or voided. rows are purchase dicts (platform, reg, name, status). Keyed by normalised reg, idempotent. Returns the regs newly added, so the run can log them.r8z%SELECT 1 FROM walked_away WHERE reg=?a INSERT INTO walked_away (reg, platform, name, status, noted_at) VALUES (?, ?, ?, ?, ?) ON CONFLICT(reg) DO UPDATE SET platform=excluded.platform, name=excluded.name, status=excluded.statusr-rr!r_) rrrrryrrrr)rrrrrrr8existss rrZrZs 4=D CfAAEE%L)C\\7#AAI  LL/ eeJ+QUU62->hPR@SUXY  [6 3  J sBC(1%C((C:c[U5nURS5R5nUVs1sHo3SiM snUR5 $s snf!UR5 f=f)zORegistrations of cars bought then cancelled or voided, never to be shown again.zSELECT reg FROM walked_awayr8rrs rwalked_away_regsrsW 4=D||9:CCE"&'$Q%$' ( rc[U5nURS5R5Vs/sHn[U5PM snUR 5 $s snf!UR 5 f=f)zFThe walked away cars with their detail, for audit and the CLI listing.zTSELECT reg, platform, name, status, noted_at FROM walked_away ORDER BY noted_at DESCrr"s rwalked_away_listr sg 4=D!% %"&&.hj"12"1AQ"12 2 rc[U5nURS5R5nUR5 /nUHjnUR SSUSUS=(d SUS=(d SUSUS =(d S R 5R 5S///////S .5 Ml U$!UR5 f=f) aWThe cars whose sale fell through that are NOT in the purchases table. walked_away and a cancelled purchase are the same real thing, a deal that died, they just arrived by different routes: a cancelled purchase is one we had already captured, walked_away also holds the ones read straight off Motorway's own Cancelled list going back further than our purchases do. So the purchases page's Cancelled view was showing 88 of 541 (Steven, 2026-08-30: "walked away cars are just cancelled cars with a different reason, we should always be able to see them when we look at cancelled cars unless filtered otherwise"). These are returned shaped like a purchase row so the page can show them in the same table, but with id None and shell True: there is no purchase record behind them, so nothing on such a row is editable and it opens no drawer. Every money field is None rather than 0, the honest absence, so they never move a total. Deliberately NOT folded into list_purchases(): that feeds the DealerKit push, the accounts email and the platform sync, none of which should ever see a row with no purchase behind it.zSELECT w.reg, w.platform, w.name, w.status, w.noted_at FROM walked_away w WHERE NOT EXISTS (SELECT 1 FROM purchases p WHERE REPLACE(UPPER(p.reg),' ','') = REPLACE(UPPER(w.reg),' ','')) ORDER BY w.noted_at DESCNTr8r!rr-noted_atr_ Cancelled)r1shellr8r!r-rerfrgrErDrFrXrYrZr)rryr{rrr; capitalize)rrrrrs rcancelled_shell_rowsrs* 4=D|| '()1 C  U8QvY_"!J-BUSUjM"#8!; B B D O O Q * Br\^   J s B00Cc0[U5[U5-$)zEvery registration that must never appear on the daily page: the cars Steven has hidden, plus the cars bought then cancelled on a platform.)rrrYs rsuppressed_regsrBs t /5 55rc[U5nSnU(aUS- nUS- nURU5R5Vs/sHn[U5PM snUR 5 $s snf!UR 5 f=f)z~Active hidden cars that have a reason, for the assistant to mine into rules. only_new limits to those not yet marked reviewed.zxSELECT reg, reason, make, model, name, hidden_at FROM hidden_cars WHERE active=1 AND reason IS NOT NULL AND reason != ''rz ORDER BY hidden_atrrs r hidden_notesrHsv 4=DH  $ $C $$!%c!2!;!;!=>!=AQ!=> ? s4A.A)A.)A..Bc[U5nUHnURS[U545 M! UR5 UR 5 g!UR 5 f=f)Nz-UPDATE hidden_cars SET reviewed=1 WHERE reg=?)rryrrr)rrrr8s rmark_notes_reviewedrWsM 4=DC LLH9UX>J[ \   5AA%c^URS5R5nU(aSUS=(d S;agURS5Vs/sHo"SPM nnUSRSS5nURS S S 5RS S S 5nURS 5 URU5 SRSU55nURSSRU5SUS35 URS5 URS5 UR 5 gs snf)aA star is one listing's, not one plate's (2026-09-14, Steven: a car on Motorway and Carwow the same day "still shares its ... star between the two cards. Fix this"). The bids table was unique on (reg, sale_date), so the second listing's star overwrote the first; it is unique on (reg, sale_date, platform) now, platform '' meaning any listing of the plate (the Mac's own older stars). SQLite cannot change a UNIQUE in place, so the table is rebuilt once, every row kept.z@SELECT sql FROM sqlite_master WHERE type='table' AND name='bids'z UNIQUE(reg, sale_date, platform)rrNr$r!zUNIQUE(reg, sale_date)zCREATE TABLE bidszCREATE TABLE bids_newrzCREATE TABLE "bids"zDROP TABLE IF EXISTS bids_newrWc34# UHoS:XaSOUv M g7f)r-zCOALESCE(platform, '')Nrrs rr (_bids_one_per_listing..qsWRVQz/.qHRVsz INSERT OR IGNORE INTO bids_new (z ) SELECT z FROM bidszDROP TABLE bidsz#ALTER TABLE bids_new RENAME TO bids)ryrrrr)rrrrrpickss rrzrzas ,,Y Z c c eC 4U9IrJ#||,EF GF!fIFD G e*  57Y ZC ++)+BA F N NOdf}@A BCLL01LL IIWRVW WELL3DIIdO3DIeWT^_`LL"#LL67KKM Hs D*cPU=(d SR5R5$r)r;r)r-s r _plat_keyrxs N ! ! # ) ) ++rcx[U5n[U5=(a X0;=(d US[U53U;$)zWhether this listing is starred: a star on its plate for any listing (platform ''), or one for its own platform. bidset is bids_for()'s.|)rrr)bidsetr8r-rs rstarredr|s9 #A 7 N M!Ai.A-B'Cv'MNrcVUVs1sHoRSS5SiM sn$s snf)Nrrr)r)rrHs r bidset_regsrs'(. /1GGCOA  // /s&c [U5nU(dgU=(d 0n [U 5n U(dU RS5(d~U RSU =(d [ 5X45R n U (d&U RSX45R 5(a"U R5 U R5 gU RSXX4XVUU RS5U RS5U RS5U RS 5U RS 5U RS 5U RS5=(d S [5U(aS OSU =(d [ 545 U(d;U RS5(a%U RSU =(d [ 5X45 U R5 U R5 g!U R5 f=f)a]Mark or unmark a car on Steven's bid shortlist for a sale date. details is an optional dict of extra attributes (year, mileage, grade, fuel, body_type, cap_clean, platform) captured so the app can learn Steven's taste over time. updated_at (UTC ISO) is what the Dealer OS sync compares; an action applied from Dealer OS passes the action's own time.Nr-zOUPDATE bids SET active=0, updated_at=? WHERE reg=? AND sale_date=? AND active=1z.SELECT 1 FROM bids WHERE reg=? AND sale_date=?a INSERT INTO bids (reg, sale_date, make, model, name, max_bid, reserve, year, mileage, grade, fuel, body_type, cap_clean, platform, created_at, active, updated_at) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ON CONFLICT(reg, sale_date, platform) DO UPDATE SET active=excluded.active, make=excluded.make, model=excluded.model, name=excluded.name, max_bid=excluded.max_bid, reserve=excluded.reserve, year=excluded.year, mileage=excluded.mileage, grade=excluded.grade, fuel=excluded.fuel, body_type=excluded.body_type, cap_clean=excluded.cap_clean, platform=excluded.platform, created_at=excluded.created_at, updated_at=excluded.updated_atr%r(r)r*r,r#rrrz_UPDATE bids SET active=0, updated_at=? WHERE reg=? AND sale_date=? AND platform='' AND active=1) rrrryrr_rrrr)r8ronrVrWr!max_bidr"detailsrr/rrr:s rset_bidrsv C.C  2A 4=D"!%% ++ n(5GIsFHHP DLL!QTWScdmmoo 2 1  3T$ UU6]AEE),aeeGnaeeFm UU; {!3QUU:5F5L" V"Q!Z%<79 > "aeeJ'' LLz$1 3B D   sB GDGG/c[U5nURSU45R5nUVs/sHn[U5PM snUR 5 $s snf!UR 5 f=f)zEvery bid row (on or off) for a sale date with its updated_at, for the Dealer OS echo. An unstarred row is sent too, so an unstar reaches the page.z[SELECT reg, sale_date, platform, active, created_at, updated_at FROM bids WHERE sale_date=?rrrrrrs rbids_with_updatedr sc 4=D|| i L"(* "&&AQ& ' s%AAAAA1c[U5nU(dJURS[U5U45R5nU(aUSOSUR 5 $URS[U5X=(d S45R5nU(aUS=(d USOSUR 5 $!UR 5 f=f)zWhen this plate's star for the sale date last changed: the listing's own row when platform is given (else the plate wide row), the newest of any row when it is not.zWSELECT MAX(COALESCE(updated_at, created_at)) AS t FROM bids WHERE reg=? AND sale_date=?rNzzSELECT updated_at, created_at FROM bids WHERE reg=? AND sale_date=? AND platform IN (?, '') ORDER BY platform DESC LIMIT 1rr/ created_atr)r8rrr-rrs rbid_updated_atr s 4=D  v'ni8::B(* 1S6D ( LL;=Fs^YXfdf.bands3d|AwtBx4D Q;OPPr)rby_makeby_modelrr`r(r%) rryr{r collectionsrr;rr most_commonr)rrrrrrrpricesmileagesyearsrmkmdr`rs rpreference_profilers 4=D|| +  (* #%,Y 79{G "beF io2 $ $ &jB % % ' K1 K " t1RD\ "a ' " Z= * &! + & !) 8) a l  MM% Y< OOAiL ) V99 LL6 #"QT&&(((*K(f>U  7 s FF,c H[U5nURSU45R5nUVs1sH>oDS=(d SR5(dUSOUSS[ US53iM@ snUR 5 $s snf!UR 5 f=f)zThe starred listings for a sale date: a bare reg for a star on the plate alone, "REG|platform" for a star on one listing. Ask starred().z=SELECT reg, platform FROM bids WHERE sale_date=? AND active=1r-rr8r)rryr{r;rrrs rbids_forr  s 4=D|| Ki\ (* vzzuypq:!4" ; ; = =%ahZqQZ[\]g[hQiPjCkkuyz { s%BAB 7B BB!c[U5n[5nUSSn[UVs1sHoURRiM sn5n[ S[ U5S5H?nXgUS-nURSSRS[ U5-5S3U/U-5 MA UGHnUR(aURROSn UR(aURROSn UR(aURROSn URS X5RRURXU S RUR5S RUR545 [!X%RR[#URS S5U5 GM UR%5 UR'5 gs snf!UR'5 f=f) aOne row per car per day. Every save carries the whole current list (run() writes after each platform finishes, carrying the earlier platforms' cars forward), so the same car's earlier rows for today are replaced, never stacked: before this (Steven 2026-09-02, Step 2 of the review) each save INSERTed the full list again, 3 to 7 times a day, and every count taken from this table was inflated by as much. Blank regs (a listing with no plate) are replaced as a group for the same reason, the batch is the complete current set of them.NrgrizDDELETE FROM assessments WHERE substr(run_at, 1, 10) = ? AND reg IN (rrr9zINSERT INTO assessments (run_at, reg, status, governing_source, governing_value, max_bid, reasons, flags) VALUES (?,?,?,?,?,?,?,?)z | rw)rrrcarr8rangerryrpricinggoverning_valuegoverning_sourcerr_reasonsflagsr4rrr) assessmentsrrrrr2rrchunkgvgsmbs rsave_assessmentsr.s 4=DfCR 4 1UUYY 45q#d)S)A1s7OE LL"xxc%j(89:!=% !* A./ii**TB/0yy++dB&'ii""TB LL/eeii22AII& 177(;=   tUUYY}d0SUX Y!"  554 sG.G)FG.)G..Hc[U5nURS5R5nUVs0sHo3S[R"US5_M! snUR 5 $s snf![ Ra,nS[U5;a0sSnAUR 5 $eSnAff=f!UR 5 f=f)ayEvery buying rule override saved from the settings page, as a plain dict. Empty when nothing has been saved yet, callers layer this over pricing.DEFAULT_SETTINGS. A database that has never been initialised at all (a fresh checkout before the first real run, CI's own bare runner) reads the same as one with nothing saved, rather than crashing whichever page render happened to read settings first (caught for real 2026-08-26: CI failed on render_purchases()'s own theme colour read, "no such table: settings", the same failure Mark's v3.0.1 push had already hit, present since theme colours started being read fresh at render time).zSELECT key, value FROM settingsrr no such tableN) rryr{r>r?rrrr)rrrres rrrAs 4=D||=>GGI:>?$Q%$**QwZ00$? @  # # c!f $I   sF#A.&A)A.)A..B.B)B.B1(B))B..B11CcF[U5nURSU45R5nU(aUSOSUR5 $![R a*nS[ U5;aSnAUR5 geSnAff=f!UR5 f=f)Nz+SELECT updated_at FROM settings WHERE key=?r/r0)rryrrrrr)rrrrr1s rsetting_updated_atr3Ys 4=D LLF O X X Z"#q-  # # c!f $   s/.A B !B0BBB  BB cD[U5nURS5Vs0sH o"SUS_M snUR5 $s snf![Ra,nS[ U5;a0sSnAUR5 $eSnAff=f!UR5 f=f)zB{key: updated_at} for every saved setting, for the Dealer OS push.z$SELECT key, updated_at FROM settingsrr/r0N)rryrrrr)rrrr1s rsettings_updated_atr5fs 4=D37<<@f3gh3ga%!L/)3gh i  # # c!f $I   sEA AA A B B.B /B BB  B BrcUb<[U[5(aUSSR5(d[SU<35e[ U5nU=(d [ 5nUR 5H.upVURSU[R"U5U45 M0 UR5 UR5 g!UR5 f=f)a(Save every key in values as a settings row, JSON encoded. Only touches the keys given, other saved settings are left alone. updated_at (UTC ISO) is what the Dealer OS sync compares; local saves stamp now. updated_at must look like a timestamp. A caller that passes a database path in that slot (a test, on 2026-09-02, while the argument order was briefly different) wrote its throwaway values into the REAL settings table with a temp file path as the stamp; refusing anything that is not a timestamp makes that mistake fail loudly instead of silently.Nz9set_settings updated_at must be a UTC ISO timestamp, got zINSERT INTO settings (key, value, updated_at) VALUES (?,?,?) ON CONFLICT(key) DO UPDATE SET value=excluded.value, updated_at=excluded.updated_at) rrisdigitrrrrryr>rrr)rrr/rrrrs rrrssz*c'B'BzRTST~G]G]G_G_TU_Tbcdd 4=D %GI ,,.JC LLjdjj'- )   s A%CCcH[U5RS5=(d 0$)aThe last known read outcome per platform site (motorway, carwow, auction4cars, dealerauction, dealerway, lecapital), so a login that has died can be flagged on the cockpit the moment it happens, not only noticed when someone next runs and reads the console. Stored under the settings table's own "site_health" key (reusing the existing generic key value store, no new table needed, this is just the latest state per site, never a history). Empty when nothing has ever been recorded. site_healthrrYs rget_site_healthr;s   ! !- 0 6B6rsiter6messagech[U5n[U5U[5S.X@'[SU0U5 g)aRecord whether the most recent attempt to read a platform succeeded, called from every place that already tries to read one (Mark 2026-08-24: "add a notification when its required to relogin", piggybacked on real runs rather than a separate poller, since Glass's single session account has repeatedly shown that logging in just to check is itself risky, see CLAUDE.md). A clean read overwrites a previous failure, so the notice clears itself the moment the site works again, no manual dismissal needed.)r6r=rr:N)r;rrr)r<r6r=rhealths rrecord_site_healthr@s1T "Fr(wdfMFL-($/rcH[U5RS5=(d 0$)a^When each thing that can be RUN last actually ran, and how it went (2026-08-31: "theres currently no way of seeing from the live auctions the current statuses of the last time the runs were made"). One entry per key: the five platforms, the on demand checks (glass, dealerkit_stock, dealerkit_purchases, dealerkit_check, lecapital_funding, compmatch, watchlist, under_offer, clickdealer), the two scheduled syncs (purchases, lost_bids), plus "run" for the daily run as a whole, each {"ok", "summary", "at"}. Deliberately separate from site_health, which looks like it would do but does not: that records whether a CONNECTION works, and is written both by a real read during a run AND by a nightly auto login, so its own checked_at can read fresh at 06:00 on a day no run happened at all. This one only ever moves when something really ran, so an age here answers "how old are the cars on my list", which is the question that stops a stale car being bid on. Both are shown, and they can honestly disagree: a connection can be fine while the read is a day old, and the Run modal says so. Empty when nothing has run yet. run_statusrrYs rget_run_statusrCs&   ! !, / 525rrch[U5n[U5U[5S.X@'[SU0U5 g)aORecord how one run finished. Same read modify write shape as record_site_health above, latest state per key, never a history, so a later success clears an earlier failure with no manual dismissal. summary is the one line the Run modal shows, and is normally the same sentence already written to the progress bar, see daily_run._finish.)r6ratrBN)rCrrr)rr6rrr_s rrecord_run_statusrFs1 D !FbgTVDFK,'.rr8rwatchedc U(aU(dg[U5nURS[U5X=(d SU(aSOS[545 UR 5 UR 5 g!UR 5 f=f)aRecord the desired shortlist/watchlist state for one car on its own platform (Mark 2026-08-27), called every time a star is toggled on the cockpit for a Motorway or Carwow car. Upserts on (reg, platform), so a quick star then unstar before the sync ever runs just leaves the LATEST desired state queued, never two conflicting rows. synced_at and error are always cleared on a fresh queue, so a car re starred after an earlier successful sync is genuinely re synced, not skipped as already done.Na*INSERT INTO watchlist_sync (reg, platform, listing_url, watched, queued_at, synced_at, error) VALUES (?, ?, ?, ?, ?, NULL, NULL) ON CONFLICT(reg, platform) DO UPDATE SET listing_url=excluded.listing_url, watched=excluded.watched, queued_at=excluded.queued_at, synced_at=NULL, error=NULL, attempts=0rrr)rryrrrr)r8r-rrGrrs rqueue_watchlist_syncrIsh h 4=D   S s^X'8bw!Atv V  X   s A A66BcL[U5nU(a'URSU[45R5nO%URS[45R5nUVs/sHn[ U5PM snUR 5 $s snf!UR 5 f=f)aEvery car still waiting to be synced to its own platform (synced_at IS NULL), newest queued first. Includes a car whose last sync attempt failed (error is set, synced_at stays NULL so it retries), until it has failed WATCHLIST_MAX_ATTEMPTS times, after which it is left alone.znSELECT * FROM watchlist_sync WHERE platform = ? AND synced_at IS NULL AND attempts < ? ORDER BY queued_at DESCz]SELECT * FROM watchlist_sync WHERE synced_at IS NULL AND attempts < ? ORDER BY queued_at DESC)rryWATCHLIST_MAX_ATTEMPTSr{rr)r-rrrrs rpending_watchlist_syncsrLs 4=D  <<;=EG]<^``h`h`j <<*,B+DFFNhj "&&AQ& ' sAB$B 9B BB#errorgive_upc[U5nU(a'URS[5[U5U45 O[U(a-URSU=(d S[[U5U45 O'URSU=(d S[U5U45 UR 5 UR 5 g!UR 5 f=f)aRecord the outcome of one sync attempt. A success clears the row's own pending state (synced_at set, error cleared); a failure leaves synced_at NULL, so the next pass retries it, with the real reason recorded for whoever is looking. Silently does nothing if the row was already re queued with a different desired state in the meantime (a star toggled again mid pass), never overwrites a newer request with an older attempt's own outcome. give_up ends the retries there and then, by counting the attempt as the last one allowed (2026-09-20). For a failure where the click genuinely reached the platform, retrying is not a free second go: a shortlist button is one switch, so a second click can undo what the first one did. Steven found exactly that on Carwow, cars this reported as failures were on his Carwow shortlist. A car starred again in the app still re queues from scratch, attempts back to 0.zjUPDATE watchlist_sync SET synced_at = ?, error = NULL WHERE reg = ? AND platform = ? AND synced_at IS NULLzfUPDATE watchlist_sync SET error = ?, attempts = ? WHERE reg = ? AND platform = ? AND synced_at IS NULLrzqUPDATE watchlist_sync SET error = ?, attempts = attempts + 1 WHERE reg = ? AND platform = ? AND synced_at IS NULLN)rryrrrKrr)r8r-r6rMrrNrs rmark_watchlist_syncedrPs$ 4=D LLG32 4 LLG"4inhO Q LLG"inh7 9   s BB77C cU=(d /Vs1sHo"(dM [U5iM nn[U5nURS5R5nUVs/sH!n[US5U;dMUSUS4PM# nnUHupxURSXx45 M UR 5 [ U5UR 5 $s snfs snf!UR 5 f=f)a-Drop every still pending row for a car no longer on the list at all (Steven 2026-09-02): once a car has sold or been withdrawn there is no listing left to star, so its row could only ever fail, on every sync, forever. Rows already synced are kept as the record they are. Returns how many were dropped.z@SELECT reg, platform FROM watchlist_sync WHERE synced_at IS NULLr8r-z9DELETE FROM watchlist_sync WHERE reg = ? AND platform = ?)rrryr{rrr) active_regsrractiverrr#r8r-s rprune_watchlist_syncrT$s &1%6B%6 =%6q1lil%6F = 4=D||^_hhj37]4a9QuX;NV\;\)51Z=)4]!MC LLTWZVe f" 4y >^ s- CC#CC6C:CCC+rc &[U5nURSU45 [5nUHnURSU[UR S5=(d S5UR S5=(d SUR S5UR S5=(d SUR S5=(d SUR S 5=(d SU45 M UR 5 UR 5 g !UR 5 f=f) a,Replace the whole under_offer snapshot for one platform with what was just read (Mark 2026-08-27), a live picture not a history: a car accepted, lost, or no longer offered on simply is not in `rows` any more and drops out here too, no separate cleanup needed. Other platforms' own rows are untouched.z*DELETE FROM under_offer WHERE platform = ?zINSERT INTO under_offer (platform, reg, name, max_bid, noted_at, vehicle_ref, listing_url, read_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?)r8rr!rrrXrN)rryrrrrr)rr-rrrrs rrecord_under_offerrV7s 4=D  AH;OfA LLU9QUU5\%7R8!%%-:M2y!155#4#:AEE->Dc[U5nURS5R5nUVs/sHn[U5PM snUR 5 $s snf!UR 5 f=f)zVEvery car currently under offer, across every platform read so far, newest read first.z8SELECT * FROM under_offer ORDER BY read_at DESC, id DESCrrs rlist_under_offerrXMs] 4=D|| FHHP  !%&AQ& ' rc~[U5nURSU(aSOS-S-5R5nUVs/sHLnUSUS[R"US5[ US5US US US [ US 5S .PMN snUR 5 $s snf!UR 5 f=f)zEvery saved view, starred ones first, newest first within each group. Deleted views are tombstones (kept so the delete reaches Dealer OS on the next echo), left out unless include_deleted.z`SELECT id, name, criteria, starred, created_at, remote_id, updated_at, deleted FROM saved_views rzWHERE deleted=0 z&ORDER BY starred DESC, created_at DESCr1r!criteriarr r0r/r2)r1r!rZrr r0r/r2)rryr{r>r?rr)rinclude_deletedrrrs rlist_saved_viewsr\Ys 4=D || )82>P R 4 5  (* :>?:>Aw& tzz!J-?X 9.a o nAlO 9.0:>? ? s2B*AB%B*%B**B<c [U5nU=(d [R"5RnU=(d [ 5nUR SU45R 5n U (a[UR SU[R"U5U(aSOSXS45 UR5 U SUR5 $UR SU[R"U5U(aSOS[5Xx45n UR5 U RUR5 $!UR5 f=f)a*Save the current sidebar filter and sort state under a name Mark chooses. criteria is a plain dict, stored JSON encoded. Returns the view's id. remote_id is the identity Dealer OS and this Mac share (a fresh uuid for a view made here); saving under an existing remote_id updates that view in place.z,SELECT id FROM saved_views WHERE remote_id=?zXUPDATE saved_views SET name=?, criteria=?, starred=?, updated_at=?, deleted=0 WHERE id=?rrr1ztINSERT INTO saved_views (name, criteria, starred, created_at, remote_id, updated_at, deleted) VALUES (?,?,?,?,?,?,0)) rr|r}r~rryrr>rrrrr) r!rZrr0rr/rridnow_urrbs r save_viewr`ls 4=D+4::<++'gillIC6R[[]  LLjtzz(+'Qq%TS U KKMt9 ll % 4::h'g1dfc Q  }}  sB-D3 AD33Ec[U5nURSU(aSOSU=(d [5U45 UR5 UR 5 g!UR 5 f=f)znStar or unstar a saved view. Starred ones show as a quick one click button next to the sort and view dropdown.z9UPDATE saved_views SET starred=?, updated_at=? WHERE id=?rrNrryrrr)view_idrrr/rs r star_viewrdsS 4=D P"a:+BGL N   ?AA/c[U5nURSU(aSOSU=(d [5U45 UR5 UR 5 g!UR 5 f=f)Nz@UPDATE saved_views SET starred=?, updated_at=? WHERE remote_id=?rrrb)r0rrr/rs rstar_view_remotergsQ 4=D W"a:+BIN P   rec[U5nURSU=(d [5U45 UR5 UR 5 g!UR 5 f=f)Nz@UPDATE saved_views SET deleted=1, updated_at=? WHERE remote_id=?rb)r0rr/rs rdelete_view_remoterisJ 4=D W -GIy9 ;   rc[U5nURSU45R5nU(aUS=(d USOSUR5 $!UR5 f=f)Nz@SELECT updated_at, created_at FROM saved_views WHERE remote_id=?r/r r!)r0rrrs rview_updated_atrks[ 4=D LL[# ''/xz 78,21\?dB  s :AA+c[U5nURSU=(d [5U45 UR5 UR 5 g!UR 5 f=f)zsA tombstone, not a real delete, so the deletion reaches Dealer OS on the next echo; list_saved_views leaves it out.z9UPDATE saved_views SET deleted=1, updated_at=? WHERE id=?Nrb)rcrr/rs r delete_viewrmsL 4=D P -GIw7 9   r0c[U5n[RR5[R"US9- R SS9n[ U5nUR SXX'45R5n UR5 U cgSn U "U S5U "U5:wdU "U S5U "U5:wagS U S U S 4$!UR5 f=f) asA cached Glass's or Cazana value for this exact reg, mileage and source, read within the last max_age_hours. Returns (hit, value, url): hit is False when there is no row, it has aged out, or its stored make/model does not match what is being asked for now (the plate may have moved to a different car since it was cached, so a mismatch forces a fresh, verified lookup rather than trusting a stale value for the wrong car). url is the real Cazana result page the value was read from (Mark 2026-08-25, "add a link on the card on the word Cazana to deep link to lookup page"), None for Glass's or a genuinely missed Cazana lookup.hoursrrz|SELECT value, url, make, model FROM valuation_cache WHERE reg=? AND mileage=? AND source=? AND valued_at >= ?)FNNcb[U=(d S5R5R5$r)rr;r)rs rrI&get_cached_valuation..s3qwB<%%'--/rrVrWTrrx) rrrrrrryrr) r8r(rrVrW max_age_hoursrrLrr_ns rget_cached_valuationrvs C.C##%(:(:(OO Z Zdm Z nF 4=Dll L 6 *  (*  { /B #f+"T("bW&6"U)&C Ws5z )) s "CCc [U5n[U5nURSXX#XEU[545 UR 5 UR 5 g!UR 5 f=f)NaNINSERT INTO valuation_cache (reg, mileage, source, make, model, value, url, valued_at) VALUES (?,?,?,?,?,?,?,?) ON CONFLICT(reg, mileage, source) DO UPDATE SET make=excluded.make, model=excluded.model, value=excluded.value, url=excluded.url, valued_at=excluded.valued_at)rrryrrr) r8r(rrVrWrrxrrs rsave_cached_valuationrxs[ C.C 4=D   C 6sDF C     s /AA*cB[RR5[R"US9- RSS9n[ U5nUR SU45nUR 5 URUR5 $!UR5 f=f)zDelete cached valuations older than max_age_hours, so the table does not grow forever. get_cached_valuation already never returns a stale row even if this has not run yet, this is just housekeeping. Returns the number of rows removed.rprrz/DELETE FROM valuation_cache WHERE valued_at < ? rrrrrryrr_rrtrrLrrbs rprune_old_valuation_cacher|s{ ##%(:(:(OO Z Zdm Z nF 4=DllLviX ||  .B Br7cb[RR5[R"US9- RSS9n[ U5nUR SXU45R 5nUR5 Ucg[R"US5$!UR5 f=f)aWhatever a platform's own detail page read for this exact key within the last max_age_hours (a plain dict of car field name to value, ready to apply straight back onto a fresh car object). None on a miss, no row, or the row has aged out. A short TTL by design (Mark's own choice, cache the reserve too but keep it a few hours only, not the whole auction's life), so a re seen car skips its own detail page visit only when it was genuinely just read, never a stale figure days old. cache_key is a normalised reg wherever the caller already has one, or a stable id (Auction4Cars' own listing_url) when it does not, see the table's own comment in SCHEMA.rprrzaSELECT detail_json FROM listing_detail_cache WHERE cache_key=? AND platform=? AND fetched_at >= ?Nr<) rrrrrryrrr>r?)r=r-rtrrLrrs rget_cached_detailrs##%(:(:(OO Z Zdm Z nF 4=Dll o & )  (*  { ::c-( )) s "BB.c[U5nURSX[R"U5[ 545 UR 5 UR 5 g!UR 5 f=f)zSave whichever car fields a detail page read actually changed (an empty dict is still worth saving, a car with a real detail page and nothing new to say should not be re visited either).zINSERT INTO listing_detail_cache (cache_key, platform, detail_json, fetched_at) VALUES (?,?,?,?) ON CONFLICT(cache_key, platform) DO UPDATE SET detail_json=excluded.detail_json, fetched_at=excluded.fetched_atN)rryr>rrrr)r=r-detailrrs rsave_cached_detailrsY 4=D   U$**V"4df =     s AAA1iPcB[RR5[R"US9- RSS9n[ U5nUR SU45nUR 5 URUR5 $!UR5 f=f)aSweep rows for cars long gone from every auction (a much longer cutoff than the short TTL that governs actual reuse, this is just housekeeping so the table does not grow forever, the same shape as prune_old_valuation_cache). Returns the number of rows removed.rprrz5DELETE FROM listing_detail_cache WHERE fetched_at < ?rzr{s rprune_old_listing_detail_cacher+s} ##%(:(:(OO Z Zdm Z nF 4=DllRU[T]^ ||  r})rNr)__doc__rr r>rr| car_namesrrrrrrabspath__file__DB_DIRDB_PATHrr Connectionrrrrrrrrrrrrrrrrrrrrrrrr#rrrrr.rrr4r7rArDrLrUrrrrrrrrrrrrrrrrrr r r6r-rrr rrrrr$r'r3r,r<rrrArBrHrJrUr]rcrirkr|rrrvrxrrrrrrrrrrorrrrrrrrQrr:rJrMrOrTrVrZr]r`rcrergrirkrmrprsr|rrrrrrrrrrrrDEEP_READ_MAX_ATTEMPTSrrrrrrrrrrrrrrrZrrrrrrrzrrrrr r rr r.rr3r5rr;r@rCrFrIrKrLrPrTrVrXr\r`rdrgrirkrmrvrxr|DETAIL_CACHE_TTL_HOURSrrrrrrrs   & bggoobggoobggooh6O&PQSY Z '',,v} -E P #G$6$6 #Db'T # &bRAcA11$+St23c3     [8GN&S&S&&RV&R&-4$s$E$N59gcRV6>@WSssY]2<>7*3*S*S*W[*ZBI  3 T  %,c(#*Cc&FhD2 \ &  s   D 5< c  T4  ,=E=E:%*wHH4HV%,c4-4c2=R!9EZ#= 6>P/>VX!3oN,3St"+-'CdB29 3 T  ? ?&*wTd0*.293T":A3T:<Y]+3+bf+>:<+3+UY+0APT$.5s%,cCJCd6AH#D079gfCfCRVfCR4613+#+D+ 5% 5<aD$D 618#D-/B'&:$'&:6:&:R;=" gA(+A:>A6-/G.Gc.G.Gb(*wjfjHHH`cHosH..0Ws0#*"C"c" '3,.7HSHtH8.0W!s!!P"yf=%v65<c$+2t C  FP]dWZD%'GdcddN1(f>TBwDAF= &&1&1$u5 *UtL$7#w7"f5$F %0'7; G4 3eD.%@8GLa5p(/  4  B&-O02E02E02E02E5  (*;=N,.A02E02E02E02E24J 68P ,&$"*1 3 JsJ7#7BDDN 'HH4HV.%'Gc.)0#C(/4> '  4 "Cd4+2 C d *13T"7 0S 0d 0S 0S 0W[ 0 '6646,:</3/D/3/3/UY/6=cSs#'/2AE<-1gc$*)-'+0%s%c%t%"%%47%$(%59%P3:Cc&?FTS$,") 3 T ")%3D&+2T5C"%: $T 3 d )-'dCW[/6$4,3S&- s $ KMZa*TW*<IMZaTWfj"-/G c  :P]d*WZ*2AH#D$29g  RU r