AWS Motor Club App — Breakdown (LIVING DOC)

THIS FILE is the portable breakdown — the one that travels. It is what docs.appolis.app renders (the hub renders markdown at build time, so a published page can no longer lag the doc), what git diffs, and what anyone without an app login can read. Keep it current: it is the only version of this document the outside world ever sees.

/breakdown in the running app is a LIVE TWIN, not a mirror — it cannot be published. It fetches /api/config, /api/stats, /api/products and /api/dealers at runtime and its theme + term-rule editors write back (config/app-config.json / the term_rules table), so it is always true inside the app and worthless outside it: hosted anywhere else those relative fetches hit the wrong origin and the page renders as an empty shell whose write-back buttons POST into the void. That is exactly what happened when it was copied to docs.appolis.app; the published copy has been pulled and docs-hub/build.js now carries a ⛔ DELIBERATELY NOT COPIED note next to it. Two ways back onto the hub, both fine: (a) this markdown in the hub's renders list — the chosen route, and the prerequisite is met; or (b) a static snapshot export that bakes the API values in at build time (not built; see §9). Do not add web/breakdown.html to the hub's copies list.

Same rule as Flip CMS: this document is updated on every app change.

What it is: customer-service command center for AWS Motor Club (AWS = Addendum & Warranty Solutions) vehicle protection products: TireGuard — tire repair/replacement program; GlassGuard — windshield protection; more products to come. ⚠️ GlassGuard naming exception: GlassGuard is windshield protection everywhere, now and going forward. The only exception is the existing Mercedes-Benz of South Orlando book, where an employee incorrectly applied the GlassGuard name to a touchscreen radio screen protector and AWS had to honour it. So the 1,846 imported GlassGuard registrations are that touchscreen product; every GlassGuard sold from here on is a windshield. Do not "correct" the historical rows, and do not let this exception leak into product copy, decks or new dealer setups. Replaces the Google AppSheet + spreadsheet-pipeline setup: instead of dealership sheets being copy-pasted through staging spreadsheets into a master sheet, dealership data goes straight into a real database and every derived number (term, expiry, dealer profit) is computed by code.

Status: v0.7.1 — LIVE ON CLOUDFLARE at aws-motor-club.pages.dev (deployed 2026-08-17, migration first, then code). Real data: 8,136 customers, 10,733 registrations — 10,717 live plus 16 recorded as cancelled — after the Dec-2025 → Jul-2026 statement backfill (1,141 added, 144 repaired) and the 2026-08-18 AppSheet enrichment: 1,331 VIN-less legacy rows matched back to their AppSheet source (999 by contract number), every recovered VIN decoded to year/make/model, December-2025 cleared of its 123 double-counted placeholders, and 570 duplicate customer records merged so each person holds ALL their products on one profile. Remaining known specks: 8 rows whose AppSheet match was ambiguous, 4 VINs NHTSA cannot decode, 44 vehicles whose two products carry different surnames (family members or resales — left for human review). The live app is behind per-person login (§1.5): approved people only, each with their own password. Default theme is the ✨ New look (gloss-black shield, v0.5.0); ↩ Classic is one click away in the sidebar foot. Every registration carries a decoded year/make/model (v0.5.3). 30 claims ($9,831.10 paid — the AppSheet claims history landed 2026-08-18: 11 new claims imported, 12 legacy claims corrected for a doubling bug in the old master workbooks where the same amount sat in two columns, and 12 claims now correctly showing in progress per the dealer), 1 dealership (Mercedes-Benz of South Orlando), 14 F&I managers, 19 note activities carried over from AppSheet.

Version is single-sourced here. APP_VERSION in web/index.html (the badge + refresh prompt), package.json, this Status line, the top of §10, and the deck (web/features.html) must all read the same number. They drifted five ways once (badge v0.5.2 / Status v0.5.0 / changelog v0.5.3 / package.json 0.4.0 / deck v0.4.1) — if you bump one, bump all five. Two things only land after a command: the badge reaches users after npm run deploy, and the hub copy of the doc + deck only refreshes after node build.js && npx wrangler deploy in F:\Claude Code\docs-hub.

Brand palette (from the AWS logo): gloss black #101214/#17191c, silver #9ba1a6, bright green #63b32e (accents/active states), white cards.

1. Run / develop

• Run: cd "F:\Claude Code\AWS Motor Club"; npm start → http://localhost:8788 (zero deps, needs Node ≥22.5 for node:sqlite; Node 24.18 at C:\Program Files\nodejs\node.exe, not on PATH in some shells — use full path)
• Tests: npm test (72 tests, node:test)
• DB: SQLite at data/aws-motor-club.db (WAL). AWS_DB env overrides; seed only loads into an empty DB, so real data is never overwritten.
• Preview launch config: aws-app in F:\Claude Code\.claude\launch.json (port 8788; Flip CMS owns 8787).
• Cloudflare (v0.4.1, LIVE at aws-motor-club.pages.dev) — Flip CMS pattern, but deployed as a Pages project, not a plain Worker: Pages URLs carry only the project name (no account subdomain), which keeps "flipmylife" out of the AWS Motor Club URL (Tyler's requirement 2026-07-03; the old aws-motor-club.flipmylife.workers.dev worker was deleted after cutover).
• Async db facade server/lib/dbwrap.js (wrapNodeSqlite locally / wrapD1 in prod); server/api.js is runtime-agnostic (no node imports) and shared by server/server.js (node) and server/worker.js (fetch handler + APP_KEY cookie gate; app config lives in the app_config D1 table there, in config/app-config.json locally — injected via handleApi hooks).
• Deploy: npm run deploy (gate on green npm test first) = esbuild-bundle server/worker.js → web/_worker.js (Pages advanced mode; excluded from static upload) + wrangler pages deploy --branch main. Bindings (D1 + APP_KEY) come from wrangler.jsonc (pages_build_output_dir form).
• Do NOT rewrite / or /breakdown paths in worker.js — Pages pretty-URLs 308-redirect .html paths, which loops against rewrites; env.ASSETS.fetch(req) pass-through is correct.
• D1 aws-motor-club id 4b687ecd-7c0b-41f1-94ca-ec7719d8381d (ENAM), account tyler@spartanstudios.com (shared account is fine; only the URL needed separating). Data refresh: npm run export-data → npx wrangler d1 execute aws-motor-club --remote --file deploy/out/schema.sql -y then --file deploy/out/data.sql -y (data.sql is idempotent: defers FKs, wipes children→parents, re-inserts).
• Known nuance: the Worker computes "today" in UTC, so coverage flips at UTC midnight (can differ from local by a few registrations near the boundary).
• npx needs node on PATH in this shell: $env:Path = "C:\Program Files\nodejs;$env:Path".

1.5 🔐 Who can get in (v0.6.0, todo_1095)

The app used to sit behind ONE shared device key: anyone holding it was in, and the app could never tell who did what. That is gone. Access is now per person, and the account list is a closed allowlist — there is no self-registration anywhere in the app, so a stranger with a valid-looking email cannot create anything.

• Approved accounts live in the users table, seeded from ALLOWLIST in server/db.js: tyler@spartanstudios.com (Tyler Bradford) and matt@awsmotorclub.com (Matt Dunkle), both role admin. Adding a third person = add a row there AND run the same INSERT ... ON CONFLICT against prod D1. Seeding never touches an existing password_hash, so re-running it cannot wipe someone's password.
• First sign-in sets the password. A seeded account starts with password_hash = NULL ("unclaimed"); the first successful login for that email stores whatever password was typed, permanently. Nobody — including us — sets it for them.
• Passwords are PBKDF2-HMAC-SHA256, per-user 16-byte random salt, 100,000 iterations, 256-bit key. Only the derived key is stored, and verification uses a constant-time compare so response timing leaks nothing. ⚠️ 100,000 is a hard Cloudflare Workers ceiling, not a preference — Workers throws at runtime above it (a bare "error code: 1101") while Node happily runs any number, so a higher value passes every local test and fails ONLY in production. That shipped once, at 210k, on 2026-07-26. MAX_WORKERS_PBKDF2_ITERATIONS in server/lib/auth.js pins it and a regression test asserts stored iterations never exceed it. More work factor cannot come from raising this number.
• The only password rule is length: at least 10 characters. No required digits or symbols — that follows NIST SP 800-63B, which recommends against composition rules because they push people toward predictable shapes like Password1 while blocking strong passphrases.
• Sessions: 32 random bytes in an HttpOnly; SameSite=Lax; Secure cookie (aws_sess), 30 days. Only the SHA-256 of the token is stored, so a database leak cannot be replayed as a login. Signing out deletes the row; changing your password signs out every OTHER device.
• Wrong email and wrong password return the identical message, so the allowlist itself can't be enumerated.
• The gate (isOpenPath in server/lib/auth.js) leaves exactly the login surface open — /login, /api/auth/login, /api/auth/me, the icon and the manifest. Everything else, including /breakdown, /features and every /api/* route, needs a session: HTML redirects to /login, API returns 401.
• Everyone on the allowlist is admin and sees exactly the same app (Tyler's call, 2026-07-26). The role column exists for a future ladder, but nothing reads it today except the reset permission.
• The Account screen (#/account, reached by clicking your name in the sidebar) does two things: change your own password (needs the current one; signs out every other device) and reset a teammate's when they're locked out. A reset clears their password and kills their sessions, so their next sign-in runs the same claim flow they used originally. You cannot reset yourself — a hijacked session could otherwise lock the real owner out — and a reset can never create an account.
• Why an admin reset instead of a "forgot password" email link: there is no email provider wired up yet. Once the comms backbone is chosen (§6, Twilio/Telnyx), a real emailed one-time link becomes possible. Break-glass if BOTH admins are ever locked out at once: npx wrangler d1 execute aws-motor-club --remote -y --command "UPDATE users SET password_hash=NULL WHERE email='someone@example.com'". There is deliberately no standing bypass key.
• Secrets are no longer in the repo. ID_SECRET and GOOGLE_PLACES_KEY are encrypted Pages secrets; APP_KEY and ACCESS_KEY.txt were deleted outright. Set or rotate one from bash (never PowerShell — it appends CRLF and the value silently never matches): printf 'value' | npx wrangler pages secret put NAME --project-name aws-motor-club.
• The local dev server is no longer wide open. npm start binds to 127.0.0.1 only and runs the same login gate. It previously listened on every interface with no auth at all, publishing the whole member database to the LAN. HOST=0.0.0.0 still allows a deliberate device test and prints a warning.

2. Business rules (the important numbers)

Term length comes from what the product cost the DEALER (term_rules table):

• expires_on = deal_date + term_years. Coverage is active through the expiry date itself, expired the day after. Feb-29 deals roll to Mar-1.
• term_source: rule (price matched), default (product has no price rules — normal for GlassGuard), unmatched (product HAS rules but the price matched none → UI shows a "verify" badge).
• Product price is set at the DEALERSHIP level (dealer_products table, editable on the dealer page): what we charge that dealer per unit. The dealer sells at whatever price they choose; dealer profit = sale price − dealer cost. New registrations default their dealer cost from dealer_products when left blank (and term follows from it). Import backfilled MBSO: TireGuard $299, GlassGuard $185 (dominant observed costs).
• Money stored as integer cents everywhere.
• Deal dates: future-dated cells in the sheets were month/day transpositions (12/1/2026 meant 1/12/2026) — fixDateSwap() in deploy/lib/xlsx-lite.js corrects them at import; zero future deal dates remain.

3. Data model (SQLite, server/db.js)

• products (code, name, default_term_years) + term_rules (product, dealer_cost_cents → term_years) — new products/prices are rows, not code.
• dealerships (name, city/state, phone, contact_name)
• customers (first/last name, phone, email, address/city/state/zip, lat/lng) — dealership registration often arrives with LAST NAME + VIN only; full contact info is usually captured at claim time. UI treats missing fields as normal.
• registrations — customer × product × vehicle: deal_date, vin, year/make/model, fi_manager, dealer_cost_cents, sale_price_cents, dealer_profit_cents (derived), term_years / term_source / expires_on (derived via insertRegistration()).
• claims — registration_id, status pipeline new → in_progress → awaiting_customer → approved → closed_paid | closed_denied, issue, resolution, opened_at/closed_at. Status changes auto-log to activity.
• claim_items — the "what was actually done" ledger: action (repair/replace/roadside/reimburse/other), description, tire_position (LF/RF/LR/RR/SPARE), amount_cents, vendor, date. Claim payout = SUM(items).
• activity — timestamped notes/calls/texts/emails per customer and/or claim (replaces the AppSheet notes blob).
• roadside_vendors — vendor directory: phones, city/state/zip, lat/lng, coverage radius, services, our 1–5 rating, times_used, notes, and category: 'ranger' (on-location operator who goes TO the customer — Tyler's definition of a Road Ranger) vs 'shop' (fixed drop-off location). The two categories are separate sections in the UI.

4. API (all JSON, server/api.js)

• GET /api/search?q= — one box searches first/last name, phone, email, VIN, address/city/state/zip, dealership name, make, model, year, contract #.
• GET /api/customers?q&sort&dir&coverage&product&claims&page&per — the full list: server-side sort (name/city/regs/last_deal/next_expiry/open_claims/ coverage), filters, pagination, totals.
• GET/POST/PATCH /api/customers/:id, POST /api/customers — card = customer + registrations (+coverage) + claims (+items, payouts) + activity + totals.
• POST /api/registrations — derives term/expiry/profit on insert; dealer cost defaults from dealer_products when omitted.
• GET /api/dealers/:id/report?group=month|quarter|year|manager|product|manager_product &from&to&products&managers — the report engine behind the dealer drill-down.
• POST /api/dealers/:id/pricing — upsert per-product dealer cost.
• GET /api/roadside/discover?customer_id|city,state,zip|lat,lng[&radius][&kind=shops|mobile] — auto-discovery: Nominatim geocode (customer coords persisted back) + distance-ranked results with in-directory flags. kind=shops = tire/repair shops (Overpass tags). kind=mobile = Road Rangers, TWO engines merged: (a) Overpass tag pull (tyres/car_repair/towing tag + craft/car_parts/ truck_repair/office=company for mobile) filtered by MOBILE_RE in code — Overpass case-insensitive name regexes are unusably slow, don't reinstate; (b) bounded Nominatim free-text ("mobile tire", "roadside assistance", "towing", "mobile mechanic", sequential w/ 400ms gaps per usage policy), gated by MOBILE_STRICT_RE to keep fuzzy junk out. Duplicate names merge, filling missing phone/website across sources. Default radius is 20 mi for BOTH kinds (user report 2026-07-31; was 60 mobile / 40 shops — a wide default buried the closest help, which is the whole point when someone is stranded). The picker still offers 20/40/60/100 for rural gaps. kumi.systems Overpass mirror fallback. GET/POST /api/roadside take category (ranger|shop); near filters by it.
• GET /api/comms — recent conversation threads. POST /api/activity takes direction (in/out).
• GET /api/claims[?open=1], POST /api/claims, PATCH /api/claims/:id (status/issue/resolution), POST /api/claims/:id/items.
• POST /api/activity — log note/call/text/email.
• GET /api/dealers, POST /api/dealerships, GET /api/dealers/:id/report?granularity=month|year — Program Overview data.
• GET/POST/PATCH /api/roadside, GET /api/roadside/near?customer_id=|city=&state=&zip= — haversine when both sides have lat/lng, else state→city→zip-closeness fallback (server/lib/geo.js).
• GET /api/products, GET /api/stats (dashboard).

5. UI tour (web/index.html, vanilla JS, hash-routed)

• Dashboard — customers, registrations, active coverage, open claims, total claims paid, dealer profit generated, per-product active/expired.
• Customers — the FULL master list, always visible: sortable column headers (▲▼), coverage/product/open-claims filters, search box (state lives in the hash, so views are linkable), 50/page pagination; new-customer form.
• Customer card — expired = UNMISSABLE (full-width red banner; green banner when active), coverage panels with contract #, VIN/deal/term/F&I/costs, Google Maps embed when an address exists ("no address yet" is normal), claims with item tables (tire position!), status dropdown, add repair/replace item, Communications & notes chat thread + composer (text/call/email/note, in/out — everything stays in-app, no tel:/sms: bounce; Grasshopper send/receive pending), and a green Find roadside help button.
• Claims — open-claims pipeline table (toggle to all); click-through to customer.
• Comms — recent conversation threads across all customers.
• Dealerships — list with units + profit; click → the report builder: group by month/quarter/year/manager/product/manager×product, date range, product + F&I manager filters, sortable columns, totals row, avg profit/unit, print → PDF. Includes the Program pricing panel (per-product dealer cost, drives registration defaults + term rules).
• Road Rangers — ON-LOCATION operators only (Tyler 2026-07-03: "people that will go on location to the person and help them get the repair facilitated and bring what they need to them"): curated directory + find-near + auto-discovery in mobile mode (towing/mobile/roadside operators). OSM under-maps one-man mobile outfits, so Google Maps hunt links + Tyler's phone contacts are first-class sources here.
• Tire Shops — separate tab for fixed drop-off shops (driveable vehicles): same find-near + auto-discovery (shops mode) + its own directory (category='shop'). Cross-links between the two sections preserve the customer/location being searched.
• List ↔ Map toggle (both vendor sections, when a target is set): Leaflet + OSM tiles (CDN, lazy-loaded, no API key). Red = customer/target, green = saved vendors, blue = discovered, dashed ring = search radius; popups carry phone (tel:), distance, and a save-to-directory button. View state lives in the hash (&view=map).

6. Integration seams (not built yet)

• Comms backbone — ⛔ GRASSHOPPER IS OUT. Confirmed 2026-07-17 (board todo_684): Grasshopper has no API at all, so the in-app calls + texting hub needs a different provider. Candidates: Twilio (reliability-first, ~$1.15/mo per number, ~$0.008/SMS, ~$0.014/min voice) or Telnyx (same capabilities, roughly 30–50% cheaper). Both can port the existing Grasshopper numbers (1–2 weeks, zero downtime if scheduled) and both handle A2P 10DLC registration, which is required for business-texting deliverability. Middle path if staff also want a normal phone app alongside the in-app hub: OpenPhone (~$15–23/user/mo, API is shallower). Blocked on Tyler picking a provider and opening the account; the hub itself is built and logging, so wiring is the only work left.
• Google Places (LIVE 2026-07-05, key configured) — third discovery engine in server/lib/places.js: Text Search (New) with rankPreference: DISTANCE (surfaces small independents over prominent chains) + per-kind query sets (rangers: mobile tire repair / towing service / roadside assistance / mobile mechanic; shops: tire shop / used tires / tire repair shop). Returns verified phone, website, hours, rating + review count (Enterprise SKU fields — ~1k free calls/mo, then ~3.5¢/call; ~4-7 calls per discovery click). Key lookup (lazy, no restart needed): local = GOOGLE_PLACES_KEY env or config/secrets.json.googlePlacesKey; cloud = Pages secret GOOGLE_PLACES_KEY (npx wrangler pages secret put GOOGLE_PLACES_KEY --project-name aws-motor-club). Without a key everything falls back to OSM and the UI says so. GOTCHA: pipe the secret via bash printf (PowerShell piping appends CRLF → "API key not valid" 400s); worker also trims the value defensively.
• Category gates (v0.4.5) — EVERY discovery result from EVERY engine must pass rangerAccept / shopAccept (places.js): NEGATIVE_RE kills computer/phone/appliance/car-wash/mobile-home/etc. (Tyler saw a mobile computer repair shop in Road Rangers); rangers additionally require a towing type/tag or an on-location vehicle-service name; shops require tire/auto signals. Gate cases are unit-tested — extend the tests when adding patterns.
• Geocoding — Nominatim with full→coarse→zip fallback; customer coords persisted on first discovery.
• VIN decode (SHIPPED v0.5.3) — deploy/decode-vins.js backfills year/make/model from the VIN via the free NHTSA vPIC batch API (50 VINs per POST, polite gaps). It only touches rows missing BOTH year and make, so re-runs never clobber hand-entered vehicles, and one decode fans out to every registration sharing that VIN. All 9,715 registrations now carry a vehicle.

7. Spreadsheet import (DONE 2026-07-03)

Both master lists (intake/*.xlsx, sheet "MASTER For APP") imported by deploy/import-masters.js using deploy/lib/xlsx-lite.js (zero-dep xlsx reader; needs the file expanded as a zip first). Columns are located by header title, not position. Actual layout (identical in both files): Dealership · Deal Date · Deal Number · VIN · F&I Manager · Customer Name (one full-name column, ALL CAPS, sometimes company or bare last name) · Dealer Cost · Sale Price · Dealer Profit · Term · "TG Customer #" (→ contract_no) · Status · Date of Last Claim or Service · Customer Email/Phone/Address · Notes · 1st–5th Service or Claim Amount · Total Amount Paid · Claim In Progress (· Front/Rear Tires, TireGuard only, empty).

Import rules that matter:

• Rows with no name+VIN+date are skipped (arrayformula artifacts: 238 TG, 4,196 GG).
• Customers merge by VIN always; by name only when multi-word (a bare "LEE" must not fuse different people). Fleet LLCs legitimately hold up to 8 vehicles.
• Names parsed to first/last via server/lib/names.js (companies kept verbatim).
• Term/profit recomputed from §2 rules; sheet values cross-checked. Results: 0 profit mismatches, 0 term mismatches, 586 status mismatches (sheet says EXPIRED where live math says active — same-day-expiry boundary + stale sheet formulas; the app's computed status is authoritative). 1 row ($299.30 cost) fell back to the sheet's term (term_source='sheet').
• Claim amounts → one claim per row with per-amount claim_items; "Claim In Progress" → status in_progress (3 open). Notes → activity.
• Re-run: expand both xlsx to folders, then node deploy/import-masters.js --tire <dir> --glass <dir> --wipe (refuses to touch a non-empty db without --wipe). node deploy/check-import.js prints a sanity report.

Known source-data quirks (imported faithfully, worth cleaning over time): junk contact cells (e.g. an email of "51"), a handful of future deal dates (e.g. 2026-12-01), GlassGuard dealer costs vary ($185 dominant, $399/$599 strays — all 1-yr by product default, so harmless).

8. OPEN ITEMS FOR TYLER

1. Pick the comms provider (Twilio or Telnyx — Grasshopper has no API, see §6) and open the account. The comms hub is built and logging; wiring real send/receive is the next big piece, and everything stays in-app with no tel:/sms: bouncing.
2. The roadside contacts currently "saved to phones" — send them in any format.
3. ~~AppSheet-only notes~~ DONE v0.5.3 — Tyler supplied the export and all 17 notes matched in (6 new). Still outstanding: photos. None are embedded in the AppSheet export, so any per-customer images still live only in AppSheet and need their own route out.
4. Auth model before hosting: single shared access key (Flip CMS style) or per-user logins?
5. The 586 sheet-vs-computed status mismatches + future-dated deals: the app shows computed truth; flag any specific contract that looks wrong.

9. TODO board

Done items are deleted, not left sitting here looking open. Shipped and gone: real-data import (v0.3), the Cloudflare Pages + D1 port (v0.4/v0.4.1), geocoding (v0.4/v0.4.3), VIN decode and the AppSheet notes import (v0.5.3), and the styled breakdown mirror — superseded by the live /breakdown twin.

• P1 — wire the comms backbone (§6). The comms hub logs everything today but cannot yet send or receive; this is the biggest open piece and it is blocked on Tyler choosing Twilio vs Telnyx and opening the account. Grasshopper is ruled out — no API.
• P2 — auth follow-ons now that per-person login exists (§1.5): a self-serve password reset (today Tyler re-arms it with one D1 command), an in-app "change my password" screen (the POST /api/auth/password endpoint is built and tested but has no UI yet), and per-role permissions — both accounts are admin today, so the role column exists but nothing reads it. Also: this app still has no Appolis ID identity, so it cannot join the suite's signed identity envelope until it does.
• P1 — claim-time intake flow polish: one form that captures address + updates customer + opens claim in one step (current flow is edit-then-claim).
• P2 — static breakdown snapshot export (the alternative route named at the top of this file): a build step that fetches /api/config, /api/stats, /api/products and /api/dealers once and bakes the values into a flat HTML file, so the rendered breakdown could be published without the live twin's runtime fetches. Not built — the hub renders this markdown instead, which covers the need. Only worth building if a stakeholder needs the twin's exact rendered look outside the app.
• P2 — seed the Road Ranger directory from the operators saved on the team's phones (§8.2); email/send the dealer report directly (currently print → PDF).
• P3 — CSV/xlsx upload screen so future dealership sheets are imported through the UI instead of scripts.
• P3 — the 3 imported "in progress" claims dating to 2022/2023 need closing or resolving in-app (ops task, not code).

10. Changelog

Newest first. Every entry is ONE source line on purpose — the hub's renderer is line-based and a wrapped bullet shreds the list (see the note at the top of this file).

Renumbering note (reconciled 2026-07-26, todo_1086): two releases had been given numbers that were already taken. v0.5 (2026-07-04, mobile build-out) collided with v0.5.0 (2026-07-17, the New Look), and v0.5.1 was held by both the 2026-07-05 Google Places release and the 2026-07-17 bug-button sweep. The 2026-07-17 numbers are baked into shipped code (web/index.html comments read "NEW LOOK (v0.5.0, todo_682)") and into board todos 682/727/731, so they kept their numbers; the two earlier releases moved down into the v0.4.x line, where they also sit correctly by date. v0.5 → v0.4.4 and Google Places v0.5.1 → v0.4.5. No release was deleted and none changed content.

• v0.7.1 (2026-08-18) — 🔒 Security-audit fixes (both from the five-point machine-door audit; findings first, fixes after agreement). (1) The roadside-discovery GET no longer writes. It used to persist a freshly geocoded customer's coordinates inline, so a pasted link or a browser prefetch performed a database write; the geocode now comes back flagged in the response and the UI persists it through the normal customer PATCH — same caching, method-honest routes. (2) The dev server's static-path guard is stricter and extracted for testing (server/lib/staticpath.js): traversal attempts are now REJECTED rather than silently re-rooted inside the web folder, and containment requires the web root plus a path separator, so a sibling folder whose name merely starts with "web" can never be served. Neither hole was exploitable in practice (the write was session-gated and idempotent; no sibling folder exists) — these close them on principle. Both fixes carry behaviour tests that fail if reverted: the discovery test stubs the network and asserts the GET leaves coordinates NULL; the path test feeds the guard traversal and sibling-escape paths. Also refreshed the deck's headline numbers to post-backfill reality. 72/72 tests.
• v0.7.0 (2026-08-17) — 📥 IMPORT STATEMENTS: the dealership's monthly sheet becomes customer data, in the app. New #/imports screen: pick the .xlsx the dealer sends, see exactly what it will do to every row, then commit. Nothing is written by the preview, and the commit re-reads the file server-side rather than trusting anything the browser reports. Built on a new zero-dependency .xlsx reader (server/lib/xlsx.js) that runs in the Worker as well as locally — the existing deploy/lib/xlsx-lite.js needs a pre-expanded archive on disk and can only ever serve the import scripts. Eight months of real statements (Dec-2025 → Jul-2026, 1,921 rows) drove every rule: day-first dates repaired (workbooks saved under a day-first locale turned days 1-12 into real date cells with month and day transposed while days 13-31 stayed text — the December and January sheets are both affected and were silently wrong); cancelled deals recognised in all five shapes the dealers use (a FLAT CXL banner, an UNWIND/CANCEL heading, a per-row note column, a negative cost, and an accounting $(185.00)) and stored as registrations.unwound so they exist as history but are excluded from every count, report and coverage total; columns matched by header name because the layout moves month to month (June drops FI-WIP, July's GlassGuard sheet carries a spare column) and because the June GlassGuard sheet is headed TGRGCOST/TGRGSALE — trusting that would file a month of GlassGuard as TireGuard. The dealer's own cheque total is used as an independent audit: our arithmetic must reproduce it to the penny (live cost above the line, minus refunds above it) and the import refuses to write when it does not. That check earned its place immediately — it caught a live March deal being deleted because the customer's surname is CANCELLIERI and the cancellation pattern was unanchored. Re-uploading a statement is a no-op, a corrected sheet shows its changes for approval rather than duplicating, a blank F&I manager never erases one already recorded, and a re-bought vehicle gets a second registration instead of overwriting the first (deal number is the identity — a VIN can carry several deals over time). 68/68 tests, 18 of them new. Two further defects were caught by generating the production SQL and reading it before running it, rather than importing blind: a statement row only has to carry what the dealer printed, and a December refund line names just a VIN and a clawback amount — writing its blanks over the matched registration would have erased that deal's sold date, sale price and expiry, so every field a statement may omit is now COALESCEd and only the cancellation flag is written unconditionally; and dealer_profit_cents / expires_on, although derived, are now COMPARED, because production held rows whose profit was never computed (NULL, so February and March reported $0.00 profit against real six-figure sales) and a re-upload previously skipped them as "unchanged". Deployed 2026-08-17 in the required order — deploy/migrate-v0.7.0.sql against D1 first, then the code — followed by the Dec-2025 → Jul-2026 backfill: 1,141 registrations added, 144 repaired, 502 customers created, 16 cancellations recorded, all eight months reconciling to the figure the importer predicted, with 0 unmatched terms and 0 future-dated deals. 69/69 tests.
• v0.6.3 (2026-07-31) — 📍 Roadside discovery now defaults to 20 miles for both Road Rangers and Tire Shops (user report via the 🐛 reporter; was 60 / 40). A stranded customer wants the nearest help first, and the wide default pushed it down the list. The radius picker still reaches 100 mi for rural gaps. Both accounts are claimed and in use — Tyler 2026-07-29, Matt 2026-07-30.
• v0.6.2 (2026-07-26) — 🚑 HOTFIX: nobody could actually sign in. Tyler hit "failed" on two passwords. Root cause: PBKDF2 was set to 210,000 iterations, and Cloudflare Workers refuses PBKDF2 above 100,000 — it throws at runtime, so every valid password died as a bare error code: 1101 while every invalid one returned a clean 400. Node has no such cap, so all 49 tests passed. The verification gap was mine: to avoid claiming Tyler's account I had only ever tested prod with deliberately-invalid passwords, which never reach the hashing call. Fixes: iterations pinned to 100k behind MAX_WORKERS_PBKDF2_ITERATIONS with a regression test asserting stored iterations never exceed the ceiling; the login page now distinguishes a server fault from a bad password instead of blaming your connection; and password rules dropped composition requirements for length-only per NIST SP 800-63B (the "must contain a number" rule was also rejecting reasonable passphrases). New verification standard: prove the real hashing path in prod with a TEMPORARY account, then delete it — done here, then removed, leaving both real accounts untouched and unclaimed. 50/50 tests.
• v0.6.1 (2026-07-26) — 🔑 ACCOUNT SCREEN: self-serve password reset + change password. #/account (click your name in the sidebar): change your own password (requires the current one, signs out every other device) and — since no email provider exists yet for a "forgot password" link — any admin can re-arm a locked-out teammate so their next sign-in sets a new password. Guards: reset needs a session, refuses self-targeting, rejects unknown emails, and can never create an account; the team list is session-gated and never ships salts or hashes to the browser. Also added the house-standard styled askConfirm() (this app was still missing it) so the reset confirmation is not a bare browser dialog. Tyler confirmed Matt has the same access level as Tyler — both admin, same app. 49/49 tests.
• v0.6.0 (2026-07-26) — 🔐 PER-PERSON LOGIN (closes todo_1095). The shared device key is gone; the app now knows WHO, not just "somebody with the key". Closed allowlist (Tyler + Matt, seeded unclaimed), first-sign-in-sets-your-password, PBKDF2-210k hashed passwords, hash-only session storage, identical error text for bad email and bad password, and a gate that leaves only the login surface open. Also closed three holes the credential audit found: the local dev server used to serve all 28 API routes unauthenticated on every network interface — the whole member/vehicle database to the LAN — and now binds to 127.0.0.1 behind the same gate; APP_KEY + ID_SECRET came out of wrangler.jsonc (ID_SECRET is an encrypted Pages secret, APP_KEY and ACCESS_KEY.txt deleted as dead); and wrangler.jsonc left .gitignore since it no longer holds credentials. Sidebar shows who is signed in with a sign-out. 46/46 tests (12 new auth tests incl. the allowlist being un-registerable and a weak claim leaving the account unclaimed). Verified on prod: old key cookie now 401, both accounts still unclaimed and waiting for their owners.
• v0.5.3 (2026-07-17) — 📤 AppSheet notes landed (closes todo_685). Tyler supplied the AppSheet export (intake/AWS MANUAL INPUT LIST FOR APP.xlsx, tab "MASTER For APP" — 10,135 rows, combined TG+GG with a product column); new deploy/import-appsheet-notes.js matches notes by contract # → VIN and dedupes by body, so re-runs are safe. Result: the AppSheet universe holds 17 notes total — all 17 matched, 6 were new (11 already imported from the masters), 0 unmatched; prod D1 synced idempotently (19 note activities live). No photos are embedded in the export. The "missing notes" perception was mostly the missing VEHICLES (fixed in this same release — item 1 below) — the notes data was always this small. (1) VEHICLES EXIST NOW: every one of the 9,715 imported registrations lacked year/make/model (the sheets only carried VINs) — new deploy/decode-vins.js backfills them via the free NHTSA vPIC batch API (50 VINs/POST, polite gaps, only touches rows missing BOTH year+make so re-runs never clobber hand-entered vehicles; one decode fans out to every registration sharing the VIN). vehicleStr() lights up across the app automatically. (2) junk contact cells: 17 junk emails (the "29"/"51" artifacts) nulled in data, and the UI now renders phone/email only when they look real (okPhone/okEmail — customers list, card header, comms; the edit form still shows raw values so junk stays correctable). (3) NOTES ROOT-CAUSED (the "notes not imported" half of todo_682): the import was NOT at fault — both master workbooks contain only ~12 notes across every tab, and all imported. The real per-customer notes/photos live ONLY in AppSheet's backing data → Tyler exports it (board todo_685), then a matcher-importer lands them as activity. Stale finding for ops: 2 of the 3 imported "in progress" claims date to 2022/2023 — close or resolve in-app. 34/34 tests.
• v0.5.2 (2026-07-17) — 📸 REPORTER SCREENSHOTS (todo_731): the 🐛 popup takes an image now — attach + passthrough to the intake.
• v0.5.1 (2026-07-17) — 🐛 BUG-BUTTON SWEEP (todo_727): the user reporter lands here — bottom-left 🐛 button + styled popup (🐛 broken / 💡 feature, auto device basics), POST /api/bug-report forwards over the new KOSMOS_HUB service binding (works in both looks; the whole app sits behind the device key).
• v0.5.0 (2026-07-17) — ✨ THE NEW LOOK (todo_682): gloss-black shield theme — the design language the Appolis suite proved out (depth, glow, chrome-gradient stat numbers, glass panels, green-gradient primary buttons), rendered entirely in AWS's OWN brand palette. Ships as an html.v2 CSS layer gating every rule, so ↩ Classic look is one click away in the sidebar foot (persisted per device, total revert — the /breakdown palette editor still drives Classic and is untouched by the new theme). Default = New look. Verified live on both themes: dark cards/inputs/dialogs/comms bubbles, badges re-tuned for dark, banners keep the unmissable expired-red.
• v0.4.5 (2026-07-05) — 🔎 GOOGLE PLACES, the third discovery engine, plus strict category gates. server/lib/places.js: Text Search (New), DISTANCE-ranked, per-kind queries incl "used tires" for indie shops; results carry rating/review count (shown in list + map popups). rangerAccept/shopAccept gate ALL engines — mobile computer repair etc. can no longer appear (Tyler's report). Key plumbing local (env/secrets.json) + cloud (Pages secret), lazy-read, graceful OSM-only fallback with "not configured" surfaced in UI. Key landed the same day and was verified live: rangers came back 40/40 with a phone number, and indie tire shops (Sandlake Tire Shop 4.8★, 1.1 mi) now surface ahead of the chains. 34/34 tests. (Renumbered from v0.5.1 — see the note above.)
• v0.4.4 (2026-07-04) — MOBILE BUILD-OUT (Tyler uses it from his phone). ≤840px: fixed top bar + hamburger → off-canvas drawer nav (overlay, closes on navigate); tables shed low-value columns via .hide-sm (customers/claims/comms/dealers/vendors/discovery keep the essentials; anything still wide scrolls inside .panel { overflow-x:auto }); 16px inputs (kills iOS focus-zoom), ≥40px touch targets, 2-col stat cards, 62vh map, scrollable dialogs, safe-area insets. PWA basics: manifest + SVG icon + theme-color → "Add to Home Screen" runs standalone. Discovered phones are now tel: links. Desktop layout untouched (all scoped to the media query). Verified at 375×812: zero horizontal overflow on every page, drawer + map + card OK. (Renumbered from v0.5 — see the note above.)
• v0.4.3 (2026-07-03) — deeper Road Ranger discovery + map view. Mobile discovery = dual engine (wider Overpass tag net + bounded Nominatim free-text, merged/deduped; 1 result → 10 near Orlando test customer; strict name filter keeps fuzzy junk out; default 60 mi). List↔Map toggle on both vendor sections (Leaflet/OSM tiles, customer pin + radius ring + saved/discovered markers w/ save-from-popup). 32/32 tests.
• v0.4.2 (2026-07-03) — Road Rangers vs Tire Shops split. Road Rangers = on-location operators only (discovery kind=mobile: towing tag + mobile/roadside-named, filtered in code — Overpass ,i name regexes are unusably slow, don't reinstate them); new Tire Shops tab holds the fixed drop-off shops. roadside_vendors.category (ranger|shop) separates the directories (remote D1 ALTERed). Overpass mirror fallback added. 32/32 tests.
• v0.4.1 (2026-07-03) — moved to aws-motor-club.pages.dev (Pages project = no account subdomain in the URL; Tyler didn't want "flipmylife" in the AWS Motor Club address). Same D1 database; old workers.dev worker deleted; deploy script now bundles web/_worker.js.
• v0.4 (2026-07-03) — CLOUDFLARE LIVE + report builder + discovery. Whole stack async (dbwrap facade node:sqlite/D1); deployed with D1 + assets + APP_KEY cookie gate (17,991 rows imported). Customers tab = full sortable/filterable paged list. Dealer drill-down rebuilt as a report builder (6 group-bys, date/product/manager filters, print). Road Ranger auto-discovery via Nominatim + Overpass with save-to-directory. dealer_products per-dealership pricing (backfilled MBSO TG $299 / GG $185; registration default + editable panel). Date-swap fix: future deal dates were month/day transpositions — corrected at import (0 remain). 31/31 tests.
• v0.3 (2026-07-03) — REAL DATA + comms hub. Imported both master lists (header-mapped, §7): 8,204 customers / 9,715 registrations / 19 claims. New: Comms tab + chat-style per-customer thread with in-app logging composer (text/call/email/note, in/out direction; tel:/sms: links removed — Grasshopper send integration pending creds). F&I manager performance rollups on the dealer Program Overview (per-manager totals + product split, profit-ranked). Expired made unmissable: red full-width banner on expired customers, red-tinted coverage panels, uppercase EXPIRED badges. Schema: registrations.contract_no (searchable) + deal_number, activity.direction; idempotent addColumn migrations; nullable deal_date. 27/27 tests. Verified live on real data in preview.
• v0.2 (2026-07-03) — rebrand: GuardHQ → AWS Motor Club app (company = AWS Motor Club / Addendum & Warranty Solutions). Logo color scheme (black / silver / green), folder renamed to F:\Claude Code\AWS Motor Club, db file data/aws-motor-club.db, env override AWS_DB, launch entry aws-app. Import plan rewritten: read ALL master-list column headers and carry claim notes/payout history over (§7).
• v0.1 (2026-07-03) — initial build from verbal spec. Zero-dep Node + node:sqlite + vanilla JS UI. Master search, customer cards w/ coverage + claims + notes + map, open-claims pipeline, dealer Program Overview (MoM/YoY, printable), Road Ranger finder w/ proximity ranking + discovery links, term/expiry/profit engines w/ rule tables. Seed data (12 customers, 14 registrations, 6 claims, 5 vendors). 21/21 tests. Verified live in preview.