8 Releases 9 Tags

RSS Feed

• v1.1.0 c1a5d7a425

  Compare

  v1.1.0 — Single-database consolidation (M1) Stable

  tliu93 released this 2026-06-12 20:53:09 +02:00 | 33 commits to main since this release

  Consolidates the project's three separate SQLite databases (app / location / poo) into a single app.db: one SQLAlchemy Base, one engine, one Alembic chain. Cleans up the data-access code that had accumulated across the codebase and removes Grafana. No historical data is lost — the location/poo rows are moved by a one-time, idempotent, reconciled migration script.

  ▎ First step of the post-v1.0.3 roadmap (M1 → M2 frontend → M3 mobile): lay a clean foundation first. This is a structural change with a breaking configuration change and a mandatory one-time data migration after deploy — read the Upgrade Guide before rolling out.

  ✨ Highlights

  • Single database. location and poo_records are merged into app.db. The data layer is unified onto one Base + a cached engine bound to app_database_url with SQLite WAL, behind a single get_db. app/auth_db.py, app/poo_db.py, app/models/base.py removed.
  • One Alembic chain. A new alembic_app revision creates location / poo_records (column types match the legacy schema exactly — REAL/TEXT, nullability, PK order). The alembic_location / alembic_poo chains and adoption scripts are deleted.
  • One-time migration script scripts/migrate_legacy_data.py: ATTACH + INSERT OR IGNORE (idempotent), explicit columns, full-row NULL-safe reconciliation that aborts non-zero on any missing/differing row. Never deletes or overwrites files; not in the Alembic chain or startup path.
  • Config cleanup (⚠️ breaking). Settings drops location_database_url / poo_database_url and their *_sqlite_path; only APP_DATABASE_URL remains. Config page and .env.example updated.
  • Grafana removed. Service, the homeautomation_grafana_storage volume declaration, and the grafana/ directory deleted. Visualization moves to the M2 React frontend (intentional interim gap).
  • Docs & OpenAPI. README / architecture-overview / roadmap updated to single-DB; openapi/ re-exported (also catching up /config/smtp/test, /public-ip/check, PublicIPCheckResponse).

  ⚠️ Upgrade guide (required after deploy, in order)

  The new code only opens app.db at runtime; deploying does not move historical data by itself — it only creates the empty tables.

  1. Back up production app.db, locationRecorder.db, pooRecorder.db.
  2. Edit .env: remove LOCATION_DATABASE_URL / POO_DATABASE_URL (now ignored); keep APP_DATABASE_URL.
  3. Deploy the new image → the migration job (python -m scripts.run_migrations) upgrades app.db and creates the empty location / poo_records.
  4. Run the one-time data migration (otherwise historical location/poo data is absent):
     python -m scripts.migrate_legacy_data
     --app-db sqlite:///./data/app.db
     --location-db sqlite:///./data/locationRecorder.db
     --poo-db sqlite:///./data/pooRecorder.db

  exit code must be 0; add --dry-run first to preview

  4. Prefer a brief quiet/maintenance window (SQLite single-writer; avoids a new write colliding with a historical PK and tripping reconciliation).
  5. Verify: app.db location / poo_records row counts equal the legacy DB counts.
  6. Retire the old DBs (manual, after verification, keep an archive): archive the legacy files (don't delete blindly); decommission the Grafana container and the homeautomation_grafana_storage volume.

  ▎ No automated path ever deletes the old files — retiring them is always a manual, reversible step.

  🔒 Data safety

  The migration reads the legacy DBs read-only (no os.remove/unlink/shutil/truncate/DROP/DELETE); idempotency comes from INSERT OR IGNORE. Verified end-to-end against copies of the real production databases: dry-run wrote nothing, the real run migrated every row with matching per-row checksums, a second run was idempotent, a constraint-violating row aborted reconciliation non-zero, and the legacy files were md5-identical throughout.

  🧹 Internal cleanup

  Startup converged (lifespan validates only the app DB, still fail-closed; run_migrations returns {"app": ...}); routes/deps/models on the single data layer; test suite converged (legacy adoption tests removed, migration-script tests added); pytest green (97 passed); openapi/ re-exported.

  Changelog

  PR #7 (feature/m1-db-consolidation → main, merge 1e0b235): T01 table revision · T02 migration script · T03 unified data layer (WAL) · T04 startup convergence · T05 config cleanup · T06 remove Grafana · T07 docs + OpenAPI · review rework (full-row reconciliation). Full diff: v1.0.3...v1.1.0

  Downloads

  • Source Code (ZIP)
  • Source Code (TAR.GZ)