Feature/m1 db consolidation #7

2026-06-12T20:31:55+02:00

tliu93 commented

2026-06-12 20:31:55 +02:00

No description provided.

tliu93 added 9 commits 2026-06-12 20:31:56 +02:00

M1-T01: add app-chain revision creating location + poo_records tables 427a491380

Add alembic_app revision 20260611_06_merge_location_poo_tables that builds
empty location and poo_records tables (REAL float columns, matching the
production schema and the adopt scripts' EXPECTED_*_TABLE_INFO constants).
Update APP_BASELINE_REVISION to the new head. Schema-only; data migration
is handled separately by scripts/migrate_legacy_data.py (M1-T02).

M1-T02: add idempotent legacy data migration script bc8dd062d5

scripts/migrate_legacy_data.py copies rows from the legacy locationRecorder.db
/ pooRecorder.db into the unified app DB's location / poo_records tables using
ATTACH + INSERT OR IGNORE (idempotent via PK-conflict skip; explicit columns,
never SELECT *). After copy it reconciles every source row against the target
and raises / exits non-zero on any shortfall. Missing legacy files are a safe
no-op (skipped); --dry-run writes nothing. Not part of the Alembic chain; run
manually once at cut-over. Never deletes or overwrites any file.

Validated end-to-end on copies of the real production DBs: dry-run reported
75103 location + 874 poo rows and wrote nothing; the real run copied all rows
with reconciliation passing; a second run copied 0 (idempotent).

M1-T03: unify data layer, models, deps and routes onto single app DB 3d3c2bcc57

Collapse the three data layers into one. app/db.py now exposes a single
Base, a cached engine bound to app_database_url with SQLite WAL enabled, and
get_engine/get_session_local/reset_db_caches/get_db_session. Delete
app/auth_db.py, app/poo_db.py and app/models/base.py. All models (auth,
config, public_ip, location, poo) inherit the one Base and register on a
single metadata. Dependencies converge to a single get_db; all routes use it.

Also update the alembic env.py files (app/location/poo) and tests that
imported the removed modules so the suite stays green, and drop the obsolete
test_legacy_style_location_db test whose flow (app reading a separate location
DB) no longer exists. Location/poo Alembic chains, adopt scripts and adoption
tests remain for M1-T04; config fields remain for M1-T05.

pytest 109 passed; ruff clean (pre-existing only); WAL verified; single
Base.metadata holds all seven tables.

M1-T04: converge startup chain onto the single app DB 0d898e09f2

run_all_migrations() now adopts/initializes only the app DB and returns
{'app': ...}. app/main.py drops the location/poo readiness checks
(ensure_location_db_ready / ensure_poo_db_ready) and their imports;
ensure_runtime_dirs only provisions the app DB path; lifespan still
fail-closes on a missing/unmanaged app DB. Delete the retired
location/poo adopt scripts and the alembic_location / alembic_poo
chains. Update tests to single-DB expectations and drop the obsolete
location/poo adoption + readiness tests.

pytest 95 passed; ruff clean (pre-existing only); a fresh app DB
initialized via scripts.run_migrations contains location + poo_records.

M1-T05: drop location/poo database config from Settings and tests af8c602988

Remove the dead location_database_url / poo_database_url fields and the
location_sqlite_path / poo_sqlite_path computed properties from Settings;
drop them from the config-page payload and from .env.example. Update the
test hardcodes (test_config, test_public_ip, test_smtp) and reduce the
conftest test_database_urls fixture to the single app DB. The one-time
migration script keeps reading legacy URLs from env/CLI, independent of
Settings.

pytest 95 passed; ruff clean (pre-existing only).

M1-T06: remove Grafana from compose and delete provisioning dc624bb7e5

Drop the grafana service and the homeautomation_grafana_storage volume
declaration from docker-compose.yml (migration and app services unchanged),
and delete the grafana/ provisioning + dashboards directory. Visualization
moves to the M2 React frontend; the named volume's actual data is retired
manually as an ops step, never by automation.

docker compose config -q passes; pytest 95 passed; ruff clean.

M1-T07: align docs to single-DB reality and re-export OpenAPI 2f634006d2

Rewrite README (single app.db + one alembic_app chain, legacy data moved
once via scripts.migrate_legacy_data, accurate test list) and remove the
Grafana Provisioning section. Update architecture-overview to the unified
data layer (one Base, app-DB engine with WAL) and retire the
alembic_location / alembic_poo sections. Mark M1 done in the roadmap.
Re-export openapi/, which catches the spec up to the already-existing
/config/smtp/test and /public-ip/check endpoints (purely additive; M1's
DB-session dependency swap produced no schema change).

pytest 95 passed; ruff clean (pre-existing only); OpenAPI export idempotent.

M1-rework: harden legacy-migration reconciliation to full-row equality 1cbe6c46d2

Audit finding (review-notes/M1-full-review-1.md, FINDING 1): _reconcile only
checked primary-key presence, so a source row skipped by INSERT OR IGNORE due
to a value difference against a pre-existing same-PK target row would
false-pass. Compare ALL columns with SQLite's NULL-safe IS operator instead,
so reconciliation is a true full-row guarantee (idempotent re-runs still pass
because the rows match column-for-column). Add tests for the value-mismatch
abort and for idempotency under full-row reconciliation. Remove the now-unused
pk_cols parameter.

pytest 97 passed; ruff clean (pre-existing only); data-safety grep still empty.

M1-rework: rename leftover pk_cols param in reconciliation test stubs

pytest / test (push) Successful in 48s

Details

pytest / test (pull_request) Failing after 10m49s

Details

a337b06c94

Round-2 audit nit (review-notes/M1-full-review-2.md): two _always_fail
monkeypatch stubs still named their (ignored) third positional parameter
pk_cols after _reconcile switched to a full columns list. Rename to columns
for consistency. Test-only, no behavior change.

pytest 97 passed; ruff clean.

tliu93 merged commit 1e0b235cef into main

2026-06-12 20:33:35 +02:00

Sign in to join this conversation.

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: tliu93/home-automation#7