# Silver Storage And Homelab Setup This milestone loads finalized local raw runs into your **existing PostgreSQL server**. It does not deploy a second production server or schedule scraping. Development and CLI execution currently happen on your laptop. Raw files remain on the laptop, and only the database connection crosses the network: ```text Laptop: raw JSONL -> Python silver importer -> Homelab VM: PostgreSQL/car_tracker ``` You do not need to install PostgreSQL on the laptop or move the repository to the homelab for this milestone. The optional local container at the end of this document is exclusively for isolated tests, not the application database. PostgreSQL 14+ is required; PostgreSQL 17 is used for integration testing to match the homelab's PostgreSQL major version. No PostGIS or other extension is needed for this first map-ready dataset. ## Actions On The Homelab Create a dedicated database and an application login. Run these commands as your PostgreSQL administrator, for example with `sudo -u postgres psql` on the Debian VM: ```sql CREATE ROLE car_tracker LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE NOREPLICATION; SET password_encryption = 'scram-sha-256'; \password car_tracker CREATE DATABASE car_tracker OWNER car_tracker; REVOKE ALL ON DATABASE car_tracker FROM PUBLIC; \connect car_tracker REVOKE CREATE ON SCHEMA public FROM PUBLIC; ``` `\password` prompts locally without putting the password in SQL history. These are one-time provisioning commands; inspect existing roles/databases rather than rerunning blindly if the names already exist. The importer connects as `car_tracker`, not `postgres`. Owning this dedicated database allows it to create its `silver` schema and run migrations without cluster-wide administrative privileges. If importing from another machine, verify PostgreSQL is already listening on the VM's LAN address. If not, adjust `listen_addresses` in the existing cluster's `postgresql.conf`. A change to that setting requires a cluster restart, which affects other clients: schedule it rather than restarting the VM blindly. Restrict the firewall to the importer machine's IP and PostgreSQL port, normally 5432. Do not expose PostgreSQL publicly. With server TLS configured, add a narrowly scoped `pg_hba.conf` rule, replacing `` with the actual client address: ```text hostssl car_tracker car_tracker /32 scram-sha-256 ``` Rules are first-match; review existing rules and their order. Reload authentication changes with `SELECT pg_reload_conf();`. `hostssl` requires PostgreSQL server TLS; the client hostname must match the server certificate for `verify-full` below. If your server has no TLS yet, configure it or use a secured SSH/VPN connection appropriate to your network before sending data/passwords across it. Do not solve connection errors by adding a broad `trust` rule or opening access to all hosts. ## Local Connection Configuration Run the following on the **laptop**, pointing `PGHOST` at the **homelab VM**, not `localhost`. In the server access rule above, `` is currently the laptop's LAN/VPN address as seen by PostgreSQL, not a future deployment container. The CLI uses standard libpq environment variables or a `DATABASE_URL` supplied outside the repository. Prefer a password file rather than a password in commands: ```sh export PGHOST=postgres.your-lan.example export PGPORT=5432 export PGDATABASE=car_tracker export PGUSER=car_tracker export PGSSLMODE=verify-full export PGSSLROOTCERT=/path/to/your/postgres-ca.crt export PGPASSFILE="$HOME/.pgpass" ``` The matching password-file entry has this form: ```text postgres.your-lan.example:5432:car_tracker:car_tracker: ``` Protect it with `chmod 600 "$HOME/.pgpass"`. Escape `:` and `\` in passwords using libpq's password-file rules. The example hostname and certificate path are placeholders. Do not commit credentials or send them in chat. `.env` is ignored by Git, but it is **not automatically loaded** by this CLI. `DATABASE_URL`, if present, can override the equivalent `PG*` settings; unset stale values when switching DBs. Without an explicit host, service, or connection URL, the CLI refuses to run instead of guessing that a local PostgreSQL server exists. With `psql` installed, verify connectivity before importing: ```sh psql -c 'SELECT current_database(), current_user, version();' ``` Alternatively, no local `psql` installation is needed for the read-only CLI check: ```sh uv run car-tracker-db check ``` It connects with the same settings as migrations/imports and executes only `SELECT 1`. Enable backups for the new database and include it in your existing monitoring. Do not point integration tests at it. ## Connection Troubleshooting The CLI reports safe error categories, not raw connection exceptions that may contain credentials or private data. `no pg_hba.conf entry` means PostgreSQL was reached, but its access rules do not allow the client/database/role combination. The rule must use the **client IP seen by PostgreSQL**, which can be a VPN or NAT address rather than the laptop's Wi-Fi/LAN address. Use the actual database and role names you created; `car_tracker` in this document is an example, not a hardcoded application requirement. On the homelab, locate the active configuration and inspect parsed rules as an administrator: ```sql SHOW hba_file; SELECT line_number, type, database, user_name, address, auth_method, error FROM pg_hba_file_rules; ``` Add the appropriate narrow `hostssl` rule in the file returned by `SHOW hba_file`, then run `SELECT pg_reload_conf();`. Check the `error` column and server logs if it still fails; a successful reload request alone does not prove every rule is valid. Do not change passwords or use `trust` to work around a missing HBA rule. Once access is permitted, rerun `car-tracker-db check` from the laptop before migrating. Other categories point to DNS/VPN, refused/timed-out connections, authentication, TLS verification, missing databases, or insufficient privileges. This check does not mutate schema or verify that the role has migration privileges. ## Migrate And Import From the repository root, with the connection configured: ```sh uv sync uv run car-tracker-db migrate uv run car-tracker-db import data/raw/leboncoin/2026-09-08/ed585bcc-188d-401e-bc9f-8bc1318d4ca6/manifest.json ``` The example uses the existing 35-search/one-detail live run. Choose any finalized manifest to import another run. Repeating the same command returns `status: already_imported`, without additional observations or false price changes. Migrations are packaged SQL files applied transactionally with stored checksums. Add a new migration to evolve the schema; never edit an already-applied migration. Run migrations explicitly before imports. They are not performed implicitly on each run. Verified on 2026-09-08 against disposable PostgreSQL 16 using a non-superuser database owner: 36 observations accepted, zero rejected, 35 latest listings with coordinates and source location metadata. An identical replay inserted nothing. The sample has 72 field issues: both source timestamps lack timezones on all 36 observations. No timezone was invented to make those issues disappear. This test did not connect to or change the homelab database. Import exit codes: | Code | Meaning | | --- | --- | | 0 | Import committed (possibly with field issues), or identical run already imported | | 1 | Input integrity, connection, or database failure; import did not commit | | 2 | Import or replay has rejected records; inspect `v_validation_issues` | A successful import is not a successful collection. Importing a partial or failed source run is intentional and preserves its coverage status. A `running` manifest is refused because files may still be changing; investigate interrupted runs before finalizing their metadata. The importer never changes the raw files or source status. ## Schema And Cleaning All objects live in the private `silver` schema: | Table | Purpose | | --- | --- | | `schema_migrations` | Applied migration checksums | | `collection_runs` | Original manifest, source status, timestamps, transform version | | `raw_files` | Referenced snapshot names, compressed-byte checksums, accepted/rejected counts | | `search_coverage` | Every planned query, including queries never attempted | | `detail_coverage` | Discovered detail IDs and their collection outcomes | | `listings` | Source + listing ID, not a physical-vehicle identity | | `observations` | Immutable-on-import typed observations, original payload, field issues | | `rejected_records` | Invalid record provenance, reason, and input hash; no text copied into errors | The initial transformation contract is version 1: - Euro prices come from a singleton `price` array and/or `price_cents / 100`, using decimal arithmetic. Conflicting units, multiple candidate prices, fractional cents, negative values, and invalid formats become NULL with an issue. Currency is EUR under this Leboncoin source contract, not inferred for other platforms. Numeric JSON tokens are decoded and persisted with decimal precision, without an intermediate binary-float rounding step. - Make/model remain source values. Model taxonomy markers such as `model_all` are not alternative vehicle models. Fiscal power reads `horsepower`; DIN power reads `horse_power_din`. Fuel, gearbox, and body style use source labels when available; unlabeled numeric category codes remain unknown rather than guessed. - Year, mileage, and power are parsed conservatively with documented bounds in `transform.py`. Missing stays NULL. Unknown variants and generations are not inferred from year, title, or discovery family. - Observation times must have a timezone and match the manifest. Source publication and update times lacking a timezone become NULL with `timezone_missing`; their original strings remain in `raw_payload`. No assumed UTC or Paris offset is added. - Coordinates must be a complete valid latitude/longitude pair. Bounds include overseas France. Location precision is source metadata, never an assertion that a map pin is the seller's exact address. When `origin_type`/`type` are blank, the observed `location.source` values `city` or `address` are retained as metadata. No geocoding occurs. - Titles/descriptions and raw attributes are retained privately, without NLP or redaction. Missing description in a search row is not filled from a later detail. Version-1 and version-2 raw files are supported because both already exist locally. The importer reads only files referenced by the manifest, rejects path traversal, verifies row counts, and ignores orphan/temp files. Missing or corrupt files fail the whole run. Invalid individual record envelopes are quarantined as rejected records; valid records with invalid field values remain observations with NULLs and issues. A run imports in one transaction. Per-run database locks serialize concurrent replays; original manifest/file checksums prevent silently accepting changed input under an existing run ID. Replays do not apply newer cleaning rules retroactively. A future transformation upgrade needs an explicit, tested reprocessing migration using preserved payloads, not a normal replay that overwrites historical observations. ## Query The Milestone Inspect source coverage and silver quality separately: ```sql SELECT * FROM silver.v_collection_coverage ORDER BY started_at DESC; SELECT * FROM silver.search_coverage ORDER BY run_id, search_index; SELECT * FROM silver.v_validation_issues ORDER BY run_id, raw_file, raw_line; ``` Map-ready latest observations: ```sql SELECT source_listing_id, price_eur, make, model, model_year, mileage_km, city, postcode, latitude, longitude, location_precision, observed_at, record_type, collection_status, quality_issue_count FROM silver.v_latest_listings WHERE latitude IS NOT NULL AND longitude IS NOT NULL; ``` `v_latest_listings` selects a single latest observation per listing, preferring detail only on an equal timestamp. A newer search row can have fewer fields than an older detail row; the view does not manufacture a combined observation. Search family remains provenance, not a validated model match. Detail family can be found by joining search observations on `(source, run_id, source_listing_id)`; multiple discovery families are possible. Price history and observed changes: ```sql SELECT * FROM silver.v_price_history WHERE previous_observed_price_eur IS NOT NULL AND price_eur <> previous_observed_price_eur ORDER BY source, source_listing_id, observed_at; ``` `v_listing_observations` provides the full typed history. Price history includes both search and detail records with their timestamps; differences can occur within a run. An unavailable detail or absence from a later search does not set a sold flag. All these views are private analytical tools, not a public API. ## Privacy And Retention Base tables contain raw descriptions and precise source locations. SQL views omit free text/raw payloads but still expose linkable IDs and map points; that does not make them anonymized. No public grants are made. Do not give a public dashboard the owner account; define a separate read-only role and reviewed/redacted views before exposing it. Deleting 30-day-old raw files does **not** remove their copies from PostgreSQL. Apply the same 30-day initial retention to private `title`, `description`, and `raw_payload` copies and relevant backups before unattended operation. Retention enforcement remains a separate operational task, not implemented automatically in this milestone. Keep typed historical market data private too; decide its retention and location precision explicitly before publication. Raw checksums provide integrity/provenance, not recovery after the files are deleted. ## Tests All ordinary tests are offline and use synthetic data: ```sh uv run python -m unittest discover -s tests -v ``` Database integration tests are skipped unless `TEST_DATABASE_URL` is set. They **truncate the silver data tables** and require an explicit database name ending `_test`. Use only a disposable database, never the homelab production database. One local example (test password only; localhost-only port; ephemeral data): ```sh docker run --detach --rm --name car-tracker-silver-test \ --env POSTGRES_PASSWORD=local-test-only --env POSTGRES_DB=car_tracker_test \ --publish 127.0.0.1:55432:5432 --tmpfs /var/lib/postgresql/data postgres:17 docker exec car-tracker-silver-test pg_isready -U postgres -d car_tracker_test TEST_DATABASE_URL='postgresql://postgres:local-test-only@127.0.0.1:55432/car_tracker_test' \ uv run python -m unittest discover -s tests -v docker stop car-tracker-silver-test ``` Wait until `pg_isready` succeeds before running the tests. This temporary container is only a testing option, not the production deployment architecture.