Used car price tracker with deal scoring

# Used car price tracker

This tool tracks used car prices for a few models across listing sites. It stores each listing and its price history in Postgres, then scores each listing against comparable cars to answer one question: is this listing a good deal or not. Requirements are in SPEC.md and the build order is in PLAN.md.

## Stack

- Python 3.12, managed with uv
- Postgres, accessed with psycopg 3
- SQL migrations in db/migrations/
- pytest for tests, ruff for lint and format, mypy for types

## Layout

- src/the app/scrapers/: one module per listing site
- src/the app/normalize.py: price, mileage, year, and model cleanup
- src/the app/db/: connection, queries, migration runner
- src/the app/scoring.py: comparables and deal score
- src/the app/cli.py: the `the app` command
- models.yaml: tracked models and canonical names
- tests/: test files; tests/fixtures/ holds saved HTML and JSON
- db/migrations/: numbered SQL files

## Commands

- `uv sync`: install dependencies
- `uv run pytest -q`: run tests (uses the carprice_test database)
- `uv run ruff check .` and `uv run ruff format --check .`: lint and format check
- `uv run mypy src`: type check
- `uv run the app migrate`: apply migrations
- `uv run the app scrape --site SITE --dry-run`: parse a site without writing to the database
- `uv run the app score`: score current listings

## Working rules

- Run the verify skill before saying any task is done.
- Explain changes to the user in 3-5 plain lines: what changed and why.
- Ask before adding a dependency, running `git commit` or `git push`, or running `uv add`.

## Scraping rules

- Tests use saved fixtures in tests/fixtures/. Never call a live site from tests.
- Respect robots.txt and each site's terms.
- Wait at least 2 seconds between requests to the same site.
- Set a user agent and use timeouts with retries and backoff.
- Listings change or disappear. Dedupe on (site, listing_id).
- Never overwrite a price. Insert a price_history row when the price changes.

## Data rules

- Store price as an integer in whole currency units.
- Store mileage in km. Convert miles before saving.
- Map trim, year, and model to canonical names using models.yaml.
- Treat a missing or zero price as missing data, not as a value.

## Scoring rules

- Comparables: same model, year within +/-1, and a similar mileage band.
- Compare the listing price to the median of its comparables.
- Require a minimum sample size. If there are too few comparables, return "not enough data". Do not guess.
- Keep thresholds in config, not in code.
- Details and edge cases are in .claude/skills/score-deals/SKILL.md.

## Database rules

- Make every schema change in a new numbered migration file in db/migrations/.
- Never edit a migration that has already been applied.
- Read DATABASE_URL from the environment. In docs, use the placeholder YOUR_DATABASE_URL.
- Never commit .env or any real connection string.
- Never run destructive SQL (DROP, TRUNCATE, or DELETE without WHERE) against anything except the local carprice_test database. Ask first.
- Use parameterized queries only.

## Review

@REVIEW.md

## More detail

- SPEC.md: requirements and acceptance criteria
- PLAN.md: four build phases, each with a /goal prompt
- INSTALL.md: setup steps for a new machine

---
name: test-runner
description: Runs the the app pytest suite and returns only the failing tests, so test output stays out of the main context.
model: haiku
tools: Bash, Read, Grep
disallowedTools: Edit, Write
maxTurns: 8
---

You run the the app test suite and report failures. You do not fix code.

1. Run `uv run pytest -q` from the project root. Tests use saved fixtures and the carprice_test database only.
2. If everything passes, reply with one line: `PASS: N tests passed` using the count from the output.
3. If anything fails, reply with, for each failing test:
   - the test node id (path::name)
   - the assertion or exception message, trimmed to its key lines
   - the 10 source lines around the failure point, found with Read or Grep if the traceback does not already show them
4. If the run errors before tests start (import error, database connection refused, missing migration), report the first error block only and the command that would show more.
5. Never paste the full pytest output. Never edit or create files. Never run commands other than the pytest command and read-only lookups such as Read or Grep.

Keep the whole reply under 60 lines.

{
  "permissions": {
    "allow": [
      "Bash(uv run pytest:*)",
      "Bash(uv run ruff:*)",
      "Bash(uv run mypy:*)",
      "Bash(uv sync)",
      "Bash(git status)",
      "Bash(git diff:*)",
      "Bash(git log:*)",
      "Bash(psql:* -d carprice_test:*)"
    ],
    "ask": [
      "Bash(git push:*)",
      "Bash(git commit:*)",
      "Bash(uv add:*)",
      "Bash(pip install:*)"
    ],
    "deny": [
      "Read(.env)",
      "Read(.env.*)",
      "Bash(rm -rf:*)",
      "Bash(curl:*|sh)",
      "Bash(psql:*DROP*)"
    ],
    "blockWritesOutsideWorkingDirectories": true
  },
  "env": {
    "CLAUDE_CODE_SUBAGENT_MODEL": "haiku"
  },
  "hooks": {
    "PostToolUse": [
      {
        "matcher": "Edit|Write",
        "hooks": [
          {
            "type": "command",
            "command": "f=$(jq -r '.tool_input.file_path // empty'); case \"$f\" in *.py) uv run ruff format --quiet \"$f\";; esac"
          }
        ]
      }
    ]
  }
}

---
name: add-scraper
description: Recipe for adding a new listing site scraper with saved fixtures, required tests and polite rate limiting.
disable-model-invocation: true
argument-hint: SITE_NAME
---

# Add a scraper for $ARGUMENTS

Follow these steps in order. Do not write parser code until the site check passes.

## 1. Check the site

- Read the site's terms of use and robots.txt. Stop and ask the user if the terms forbid automated access or require a login.
- Look for an official API or feed first. Use it if one exists.
- Never bypass logins, CAPTCHAs or anti-bot protections. These are non-goals in SPEC.md.

## 2. Save fixtures

Save 2-3 pages to `tests/fixtures/`:

- `<site>_sample.html` (or `.json`): a normal set of listings
- `<site>_missing_fields.html`: a listing with no price or no mileage
- `<site>_removed.html`: a sold or removed listing

Fetch the pages once, by hand or with a one-off script. Tests never touch the network after this.

## 3. Write the parser

- Create `src/the app/scrapers/<site>.py`.
- The parser takes the saved HTML or JSON text and returns a list of `Listing` dataclass instances.
- Fields follow the SPEC.md data model: site, listing_id, url, make, model, year, trim, mileage_km, price, currency, location.
- Normalize through `src/the app/normalize.py`: price to whole currency units, mileage to km, make, model and trim to canonical names from `models.yaml`.
- Skip listings whose model is not in `models.yaml`. Do not guess a model.
- For a missing price or mileage, return the field as `None` so scoring can skip the listing. Do not crash.
- Register the scraper in `src/the app/scrapers/__init__.py`.

## 4. Rate limit and identity

- Wait at least 2 seconds between requests to the site.
- Send a descriptive User-Agent that names the project and gives a contact.
- Set a timeout on every request. Retry with backoff, with a fixed retry cap.
- Respect robots.txt.
- If shared rate-limit or user-agent settings exist in `src/the app/scrapers/`, use them. If not, add them there, not per site.

## 5. Required tests

Create `tests/test_scrapers_<site>.py` with these tests:

- Parses the sample fixture into the expected listings, checking count and key fields.
- Handles missing price and mileage without raising.
- Handles a sold or removed listing: it is marked inactive, not deleted.
- Makes no network calls. Use fixtures only.

## 6. Finish

- Run the verify skill. Do not say the scraper works until verify passes.
- Update CLAUDE.md only if a command changed.
- Summarize in 3-5 lines: what you added, and why it is safe to run.

---
name: score-deals
description: Guidance for changing deal scoring, including comparable selection, thresholds, edge cases and a SQL sanity check.
when_to_use: Use when changing deal scoring or comparable selection, adjusting score thresholds, or when a listing's score looks wrong.
---

# Score deals

## How comparables are chosen

- Same canonical model as the listing.
- Year within plus or minus 1 of the listing's year.
- Mileage within the listing's mileage band. Set the band width in the scoring config, not in code.
- Active listings only, excluding the listing being scored.
- Minimum sample size: 8 comparables. Below that, return "not enough data". Never guess. Set the minimum in config.

## Formula and labels

deal_score = (median_comparable - price) / median_comparable

Default labels, all set in config:

- great: 10% or more
- good: 5% to 10%
- fair: -5% to 5%
- overpriced: below -5%

Test each boundary value, since that is where labels most often go wrong.

## Rules in code

- Use the median, not the mean.
- Check for zero comparables and a zero median before dividing.
- A null or zero price gets no score. Return a "no price" reason.
- Never compare across currencies. Drop comparables whose currency differs from the listing's.
- The median resists outliers. Still drop comparables with null or zero prices before computing it.

## Sanity check on a SQL sample

Run read-only queries against `carprice_test`:

```
psql -d carprice_test
```

1. Pick 5 active listings:

```sql
SELECT id, model, year, mileage_km, price, currency
FROM listings
WHERE active
ORDER BY random()
LIMIT 5;
```

2. For each listing, list its comparables using the selection rules above, substituting the listing's values:

```sql
SELECT price
FROM listings
WHERE model = 'MODEL' AND currency = 'CUR'
  AND year BETWEEN YEAR - 1 AND YEAR + 1
  AND mileage_km BETWEEN LOW AND HIGH
  AND active AND id <> ID
ORDER BY price;
```

3. Work out the count, median and score by hand.
4. Compare with the output of `uv run the app check`. Report the 5 listings with comparable count, median, score and label.

If the hand calculation and the code disagree, the bug is in the scoring code. Write a failing test first, then fix the code.

## Tests

- Unit tests on synthetic data for each label, including boundaries.
- A test that 7 comparables returns "not enough data" and 8 returns a score.
- Tests for zero comparables, null or zero price, and currency mismatch.
- Run the verify skill before saying the change is done.

---
name: verify
description: Run tests, lint, type check and a dry-run smoke test before declaring work done.
when_to_use: Use after any code change in this project, before saying a task is done.
---

# Verify

Proves a change works for the the app stack. Run the steps in order and stop at the first failure.

## Before starting

- Use the `carprice_test` database only. Check that DATABASE_URL points there by printing the database name, not the password.
- Never point verify at the production database.

## Steps

### 1. Lint and format

```
uv run ruff check .
uv run ruff format --check .
```

### 2. Type check

```
uv run mypy src
```

### 3. Tests

```
uv run pytest -q
```

Tests use `tests/fixtures/` and the `carprice_test` database. If a test reaches the live network, treat that as a failing test and report it.

### 4. Smoke test

```
uv run the app scrape --site SITE --dry-run --fixture tests/fixtures/SITE_sample.html
```

Replace SITE with the site name. Pass only if the output reports a parsed listings count above zero. A dry run must write nothing to the database.

### 5. Migrations (only if `db/migrations/` changed)

Work only on `carprice_test`:

1. Recreate it empty: `dropdb carprice_test && createdb carprice_test`
2. Apply migrations: `uv run the app migrate`
3. Run `uv run the app migrate` again. Expect no changes and no errors.
4. Run it once more. Expect the same result.

## Report format

- One line per step: pass, fail or skipped (with reason).
- For a failing step, show only the first failing output, trimmed to the relevant lines.
- Do not fix anything silently. If you change code to get a step passing, say what you changed and why, then rerun the failed step and every step after it.
- Say "done" only when every step passes or is skipped with a reason.

# Install and start

This guide sets up the used car price tracker on your computer and shows how to start building it with Claude Code.

## What you need

- **Python 3.12**: the programming language the tool is written in.
- **uv**: a tool that installs Python packages and runs the project for you.
- **Postgres 15 or newer**: the database that stores listings and price history. It must be running locally.
- **Claude Code 2.1.296 or newer**: the AI coding assistant you will run in this folder. Check with `claude --version`.

## Step 1: Create the databases

Run these once in your terminal:

```
createdb the app
createdb carprice_test
```

- `the app` holds your real data.
- `carprice_test` is used by tests and the verify step. Claude is only allowed to touch this one.

## Step 2: Set up the project folder

1. Create a folder for the project and open a terminal in it.
2. Run `git init` to start version control.
3. Copy the generated files into the folder: CLAUDE.md, SPEC.md, PLAN.md, REVIEW.md, INSTALL.md and the `.claude/` folder.

## Step 3: Add your database address

Create a file named `.env` in the project folder with this line:

```
DATABASE_URL=YOUR_DATABASE_URL
```

Replace `YOUR_DATABASE_URL` with your local connection string, for example one that points at the `the app` database on your machine. The `.env` file must stay out of git. Check that `.gitignore` lists it before your first commit.

## Step 4: Install the project

```
uv sync
uv run the app migrate
```

- `uv sync` installs the Python packages the project needs.
- `uv run the app migrate` creates the tables. It is safe to run twice.

## Step 5: Install the Claude Code plugins

Start Claude Code in the folder:

```
claude
```

Then run these three commands inside Claude Code:

```
/plugin install code-review@claude-plugins-official
/plugin install feature-dev@claude-plugins-official
/plugin install commit-commands@claude-plugins-official
```

- **code-review** checks each phase's changes before you commit.
- **feature-dev** guides the explore, design and build steps for scrapers and scoring.
- **commit-commands** makes clean commits per phase.

## What each file does

- **CLAUDE.md**: the project memory Claude reads first. It lists commands, layout and rules.
- **SPEC.md**: what the tool should do. You edit this with your models and sites.
- **PLAN.md**: the four build phases, each with a ready-to-run prompt.
- **REVIEW.md**: the checklist `/code-review` uses.
- **.claude/settings.json**: which commands run without asking and which need your approval.
- **.claude/skills/verify/**: the check Claude runs before saying work is done.
- **.claude/skills/add-scraper/**: the recipe for adding a new listing site.
- **.claude/skills/score-deals/**: notes on the scoring rules and how to sanity-check them.
- **.claude/agents/test-runner.md**: a small helper that runs tests and reports only failures.

## How to start

1. Open SPEC.md and fill in your tracked models and two listing sites.
2. Check each site's terms and robots.txt before building a scraper for it.
3. In Claude Code, paste the Phase 1 `/goal` prompt from PLAN.md.
4. When Phase 1 finishes, run `/clear`, then start Phase 2 the same way.

## Keeping costs down

- Run `/clear` between unrelated tasks so old conversation does not get reloaded.
- Run `/compact` at natural breaks in a long task.
- Run `/usage` to see where your tokens went.
- Test runs use a small, cheap helper agent, so the expensive model is not used for test output.

## Legal and polite scraping

- Read each site's terms of use before scraping it. Some sites forbid it.
- Respect robots.txt. The tool does this automatically, but you should check it too.
- Scrape slowly. The tool waits at least 2 seconds between requests.
- Prefer official APIs or data feeds when a site offers them.
- The tool does not log in, bypass anti-bot checks or contact sellers.

# Build plan

Build one phase at a time. Each phase ends with a `/goal` prompt.

Before each phase:
- Use plan mode and read SPEC.md and CLAUDE.md.
- Run `/clear` before starting the phase.

## Phase 1: Skeleton and database

Creates the project layout, the Postgres schema, `models.yaml`, the test database setup and a CLI stub.

```
/goal Phase 1 of PLAN.md: set up the project skeleton.
End state: (1) uv project on Python 3.12 with src/the app/ and a `the app` CLI with stub commands scrape, score, report, check; (2) db/migrations/001_init.sql creating listings, price_history, tracked_models per SPEC.md, plus `uv run the app migrate`, safe to run twice; (3) models.yaml with the tracked models from SPEC.md; (4) tests/conftest.py using carprice_test, with at least 3 passing tests: migrations apply to an empty DB, rerun is idempotent, models.yaml loads; (5) ruff and mypy configured.
Check: `uv run ruff check .`, `uv run mypy src` and `uv run pytest -q` all succeed. Show the output.
Constraints: touch only carprice_test in tests; no live network calls; read DATABASE_URL from env, never commit secrets; add only psycopg, pyyaml, typer, httpx, pytest, ruff, mypy; ask before adding anything else; keep CLAUDE.md under 90 lines and update it if commands change.
Start by reading SPEC.md and CLAUDE.md and proposing a short plan. Stop after 25 turns and report what remains.
```

## Phase 2: First scraper and normalization

Adds the first site parser, normalization and idempotent writes.

```
/goal Phase 2 of PLAN.md: add the first listing scraper.
End state: (1) a parser for SITE_1 in src/the app/scrapers/ that turns saved HTML into Listing dataclasses; (2) normalization in src/the app/normalize.py: integer prices, km mileage, canonical make/model/trim/year from models.yaml; (3) an upsert keyed on (site, listing_id) that writes a price_history row only when the price changes; (4) `uv run the app scrape --site SITE_1 --dry-run --fixture tests/fixtures/SITE_1_sample.html` prints the parsed count and writes nothing; (5) tests for: parses the fixture, missing price or mileage is flagged not crashed, a second run adds no rows, a price change adds one history row.
Fixtures: save real pages if SPEC.md names a site you can reach. If not, write clearly labeled synthetic HTML and say so in the final report.
Check: `uv run ruff check .`, `uv run mypy src`, `uv run pytest -q` and the dry-run command all succeed. Show the output.
Constraints: tests use saved fixtures only, no live network; never edit an applied migration; touch only carprice_test in tests; no new dependencies without asking.
Stop after 30 turns and report what remains.
```

## Phase 3: Second site, dedupe, politeness, scheduled runs

Adds the second scraper, cross-site dedupe, rate limiting, robots.txt checks and a full run.

```
/goal Phase 3 of PLAN.md: add a second site and make scraping polite and repeatable.
End state: (1) a parser for SITE_2 with its own fixture and tests; (2) dedupe so the same car listed on both sites is one row per site with a shared key only where SPEC.md allows, and no duplicate rows on rerun; (3) a rate limiter with at least 2 seconds between requests per host, a descriptive user agent, timeouts, and retries with backoff; (4) a robots.txt check before each site is fetched, with the result cached per run; (5) `uv run the app scrape --all` runs every configured site, and a README section in CLAUDE.md or INSTALL.md shows a cron line for it.
Check: `uv run ruff check .`, `uv run mypy src`, `uv run pytest -q` succeed. Then run `uv run the app scrape --all` twice against fixtures and show that the second run inserts 0 rows.
Constraints: tests use fixtures only, no live network; mock time and sleep in tests; never edit an applied migration; touch only carprice_test in tests; no new dependencies without asking.
Stop after 30 turns and report what remains.
```

## Phase 4: Deal scoring and reports

Adds comparable selection, the scoring rule, the report and the single-listing check.

```
/goal Phase 4 of PLAN.md: implement deal scoring and reporting.
End state: (1) src/the app/scoring.py selects comparables (same model, year within 1, mileage within the configured band) and computes deal_score = (median_comparable - price) / median_comparable; (2) labels great (>=10%), good (5% to 10%), fair (-5% to 5%), overpriced (< -5%), and "not enough data" when there are fewer than 8 comparables; thresholds and minimum sample size come from config; (3) edge cases handled: zero comparables, null or zero price, currency mismatch, outliers (median or trimmed); no division by zero; (4) `the app report` lists the best current deals per tracked model; (5) `the app check URL_OR_ID` prints the listing, its comparables count, median and label.
Check: `uv run ruff check .`, `uv run mypy src`, `uv run pytest -q` succeed. Unit tests use synthetic data and cover great, good, fair, overpriced, not-enough-data and each edge case. Show the output.
Constraints: no live network; no edits to applied migrations; touch only carprice_test in tests; no new dependencies without asking.
Stop after 30 turns and report what remains.
```

## Optional Phase 5: Ideas only

Not planned yet. Do not start without a fresh plan.

- Price trend alerts when a tracked model drops below its median.
- A small local web view of the report.

## Tips

- Use plan mode before each phase. Read the diff before you accept it.
- Run `/clear` between phases to keep context small.
- Check `/usage` after each phase to see what it cost.
- If a `/goal` stops early, read the report, fix the gap, then start a new `/goal` with the remaining work.

# Review checklist

Used by `/code-review`. CLAUDE.md imports this file.

## Data correctness

- Units are normalized: mileage in km, price in whole currency units or integer cents, one currency per comparison.
- Year, make and model map to canonical names from models.yaml. Trim strings are stripped and normalized.
- Price parsing failures are logged and surfaced, never silently set to 0 or null.

## Idempotency

- Rerunning a scrape never creates duplicate listings. Dedupe key is (site, listing_id).
- Price changes add a price_history row. Existing history is never overwritten or deleted.
- Listings that disappear are marked inactive, not removed.

## SQL safety

- Parameterized queries only. No f-strings, `.format()` or concatenation in SQL.
- Migrations are numbered and additive. Applied migrations are never edited.
- No DROP, TRUNCATE or unscoped DELETE outside carprice_test.

## Scraper hygiene

- Rate limit of at least 2 seconds between requests to the same site.
- User agent is set and identifies the project.
- robots.txt is checked before fetching. Disallowed paths are skipped.
- Timeouts are set. Retries use backoff and have a cap.
- No login, CAPTCHA or anti-bot bypass code.

## Scoring

- Sample size guard: fewer than the minimum comparables returns "not enough data".
- No division by zero when the median is 0 or missing.
- Outliers are trimmed or handled by using the median.
- Thresholds come from config, not hard-coded values.

## Tests and secrets

- Tests use saved fixtures in tests/fixtures/. No live network calls.
- Tests touch only carprice_test.
- No secrets, real DATABASE_URL values or .env files committed.

## Scope

- Flag anything over-engineered for a solo project: extra abstractions, unused config, premature plugin systems.
- Flag new dependencies that were not agreed in SPEC.md or PLAN.md.

# Used car price tracker: spec

## Goal

Answer one buyer question for a few car models: "Is this listing a good deal?"

The tool collects listings from listing sites, keeps price history, and scores each listing against comparable cars.

## Tracked models

Edit this list. Keep 3 to 5 entries and mirror them in `models.yaml`.

- YOUR_MAKE YOUR_MODEL 2016-2021
- YOUR_MAKE YOUR_MODEL 2018-2022
- YOUR_MAKE YOUR_MODEL 2015-2019

## Sources

Start with 2 or 3 sites. Fill these in:

- SITE_1: URL, and whether it has an official API or feed
- SITE_2: URL, and whether it has an official API or feed

Before adding a site, check its terms and robots.txt. Prefer official APIs or feeds where they exist. Do not log in or get around anti-bot protection.

## Data model

listings
- site, listing_id (unique together; this is the dedupe key)
- url, make, model, year, trim
- mileage_km, price, currency, location
- first_seen, last_seen, active

price_history
- listing_id, price, seen_at (one new row when the price changes)

tracked_models
- make, model, year_from, year_to

## Normalization

- Price is an integer in whole currency units.
- Mileage is stored in km.
- Model, trim and year use canonical names from `models.yaml`.

## Scoring

Comparables: same model, year within plus or minus 1, mileage within a band set in config.

deal_score = (median_comparable - price) / median_comparable

Labels:
- great: score of 10% or more
- good: 5% to 10%
- fair: -5% to 5%
- overpriced: below -5%
- not enough data: fewer than 8 comparables

Thresholds and the minimum sample size live in config, not code.

## Outputs

- `the app report`: current best deals per tracked model
- `the app check URL_OR_ID`: score one listing
- `the app scrape --site SITE --dry-run`: parse and print, write nothing
- Optional: CSV export of the report

## Non-goals (v1)

- No user accounts
- No web UI
- No contacting sellers
- No bypassing logins or anti-bot protection

## Acceptance criteria

- Saved fixtures for each site parse into listings with the expected count and fields.
- Running a scrape twice adds no duplicate listings and no duplicate price history rows when prices are unchanged.
- A price change adds exactly one price_history row.
- A listing missing price or mileage is stored and flagged, not crashed on.
- Scoring returns "not enough data" when there are fewer than 8 comparables.
- Labels match the thresholds on synthetic data.
- `uv run pytest -q` passes with no network access.

## Open questions

- Which 3 to 5 models and year ranges?
- Which 2 or 3 sites? Does any of them offer an API or feed?
- Which country and currency?
- How wide should the mileage band be?
- How often should scrapes run (daily, weekly)?
- Is CSV export needed for v1?