alvis
d37801806d
Compose and supporting code for four services that had been running or prototyped without their config tracked here, per the repo convention that agap_git holds the compose + config while application source lives in each service's own Gitea repo. Only placeholder credentials are included: mood/.env.example and moodtracker/.env.example ship dummy values, and overleaf/variables.env carries app name and feature flags only. Real values stay in Vaultwarden. Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
35 lines
2.0 KiB
Markdown
35 lines
2.0 KiB
Markdown
# Personal Sensing Store
|
|
|
|
Source-agnostic local SQLite archive for personal health and activity data. The store schema (`schema.sql`) and storage layer (`src/store.py`) are fed by an ETL pipeline that aggregates data from Home Assistant (Companion sensors, integrations) and Health Connect (Android). This service does not ingest data directly; it only provides the normalized storage layer. Data ingestion is handled by the HA→Agap ETL (see Kanboard #207).
|
|
|
|
## Schema
|
|
|
|
- **data_points**: Time-series metrics (steps, heart rate, calories, weight, distance, etc.) — one row per (metric, interval, source).
|
|
- **sessions**: Workouts and sleep sessions — keyed on stable session ID.
|
|
- **sleep_segments**: Sleep stage breakdowns (awake, light, deep, REM, out-of-bed) — one row per stage segment.
|
|
- **sync_state**: Incremental-sync cursor per stream — tracks high-water mark for resumable ingestion.
|
|
- **ingest_runs**: Audit log of ingestion runs — observability and staleness detection for Zabbix.
|
|
|
|
All writes are idempotent UPSERTs keyed on the row's natural identity, so re-runs over overlapping windows are no-ops.
|
|
|
|
## Storage Layer
|
|
|
|
`src/store.py` provides connection, schema initialization, and read/write functions:
|
|
|
|
- `connect(db_path)` — open or create the SQLite database
|
|
- `init_db(conn)` — run schema.sql
|
|
- `upsert_data_points(conn, rows, source="ha")` — insert/update metric points
|
|
- `upsert_sessions(conn, rows, source="ha")` — insert/update sessions
|
|
- `upsert_sleep_segments(conn, rows, source="ha")` — insert/update sleep segments
|
|
- `daily_metric(conn, metric, days=14)` — aggregate metric by day
|
|
- `recent_sessions(conn, days=30, limit=50)` — query recent sessions
|
|
- `sleep_by_night(conn, days=14)` — aggregate sleep by stage and night
|
|
- `summary(conn)` — compact health snapshot (row counts, coverage, freshness)
|
|
|
|
## Sources
|
|
|
|
- `ha`: Home Assistant (HA Companion sensors, integrations)
|
|
- `takeout`: Legacy import placeholder (unused)
|
|
|
|
Default source for new data is `ha`.
|