Skip to content

README.md

Latest commit

 

History

History
executable file
·
89 lines (68 loc) · 4.31 KB

README.md

File metadata and controls

executable file
·
89 lines (68 loc) · 4.31 KB
Raw

mariadb

MariaDB 11.8 — central time-series database for the home energy system.

Setup

cd ~/docker/mariadb
docker compose --env-file "../.env" up -d

The database and user are created automatically from the .env variables on first start. Data is stored on a USB SSD at /mnt/usb/mariadb (volume-mounted for persistence).

Schema

The full schema is in schema.sql. Import it into a fresh database:

mysql -h 127.0.0.1 -u root -p your_db_name < schema.sql

Tables

Table Written by Purpose
energy read_p1, read_seplos, read_resol, read_otthing, transfer_p60 One row per 5-minute interval — all sensor data
battery_schedule battery_optimizer 192-slot rolling schedule (48 h horizon, 15-min slots)
electricity_prices battery_optimizer Quarter-hour EPEX spot prices incl. VAT
gas_prices battery_optimizer Daily gas spot prices incl. VAT via EnergyZero
energy_tariffs manual Contract tariffs per period (variable purchase, tax, feed-in)
fixed_costs manual Fixed daily costs per period (delivery, grid, tax reduction)
cost_simulation battery_optimizer Per-run cost comparison across scenarios A/B/C/D
pv_om_forecast battery_optimizer Per-quarter Open-Meteo raw PV forecast (latest snapshot)
pv_solcast_forecast battery_optimizer Per-quarter Solcast PV forecast (latest snapshot, when available)
pv_knmi_nowcast read_knmi Per-quarter KNMI satellite radiation nowcast, every run kept — analysis-only
predicted_grid_snapshot battery_optimizer The day's plan frozen at its start — grid kWh + euros for the predicted line
battery_alert_latch common/battery_alert.py One row per alert key — active/cleared state + acknowledgement

battery_alert_latch table

Column Description
alert_key (PK) vdelta_taper | vmin_taper | soc_low_lock | vmin_low_lock
active 1 while condition is active, 0 after it clears
triggered_at When the condition first triggered
cleared_at When the condition cleared
trigger_message Message recorded at trigger time — never overwritten
message Message recorded at clear time
acknowledged 1 after user clicks "Gezien" in WordPress battery page
acknowledged_at Timestamp of acknowledgement

Alert keys are set by common/battery_alert.py (alert_trigger / alert_clear), shared between read_seplos and control_growatt. WordPress battery page shows a red banner for every row where acknowledged = 0. The homepage tile blinks when any unacknowledged alert exists.

energy table — column groups

Prefix Source Data
p1_* read_p1 Grid import/export (kWh, W), gas (m³)
sph_* read_p1 (via inverter) PV power/energy, battery charge/discharge, inverter faults
seplos_* read_seplos Cell voltages, SoC, temperature, alarms, daily energy
resol_* read_resol Solar thermal temperatures, flow rates, relay states
sparrow_* transfer_p60 Weheat P60 heat pump power, COP, temperatures, status
honeywell_ot_* read_otthing OpenTherm boiler/thermostat data (temperatures, flame, modulation)

battery_schedule table — key columns

Column Description
slot_dt Quarter-hour slot start (local time)
action LOAD_FIRST | BATTERY_FIRST+CHARGE | BATTERY_FIRST+PV_CHARGE | BATTERY_FIRST+DISCHARGE | STANDBY
charge_kw Scheduled charge power kW
soc_start_pct / soc_end_pct Predicted SoC at start/end of slot
pv_curtail_kwh PV curtailed in this slot (triggers PV contactor switching in read_growatt)
created_at Timestamp of the optimiser run that produced this row

Connection

Host:  192.168.x.x (DB_HOST in .env)
Port:  3306
DB:    DB_NAME in .env
User:  DB_USER / DB_PASSWORD in .env

Backup

A restic backup of /mnt/usb/mariadb runs via cron. The MariaDB container has stop_grace_period: 60s to allow a clean InnoDB shutdown before the backup snapshot.

Never run docker stop $(docker ps -q) — the 10 s default grace period causes MariaDB to receive SIGKILL, which can corrupt tc.log. Always stop the container individually: docker compose down.