Save your data.

Until now, the API kept the latest reading per sensor in memory — in a Python dictionary. Memory is fast and easy, but the moment the program restarts (or the Pi loses power), every value is gone. That’s called volatile storage.

For an IoT lab to be useful, we want durable storage: data survives restarts. We’ll use SQLite, which is a database that lives in a single file on disk. No separate server, no extra service to install — just a file the API reads and writes.

By the end of this phase, the API will:

• Save every sensor reading to data/sqlite/platform.db.
• Auto-register devices and sensors on first sight (you don’t configure anything).
• Hydrate the in-memory cache from the database on startup so the dashboard isn’t empty after a restart.
• Run a nightly clean-up at 03:00 UTC that deletes readings older than 30 days.

Phase 4 adds new files to the platform repo (the database layer, the retention job). On the Pi:

cd /opt/iot-platform
git pull
docker compose build api
docker compose up -d

docker compose build api rebuilds the API image with the new Python dependencies (SQLAlchemy, Alembic, APScheduler). up -d restarts only what changed.

Watch the API logs:

docker compose logs -f api

You should see lines like:

INFO  alembic.runtime.migration  Running upgrade  -> 0001, initial schema
INFO  iot.api  cache hydrated: 0 sensor(s)
INFO  iot.api  retention scheduled at 03:00 UTC, 30-day window
INFO  iot.mqtt  MQTT connected to mosquitto:1883

The first line means Alembic created the database tables. Ctrl+C stops following the logs (the API keeps running).

The database lives in a single file:

ls -lh data/sqlite/

You should see platform.db and possibly platform.db-wal / platform.db-shm next to it. Those are SQLite’s “write-ahead log” helper files — they let writers and readers work at the same time without blocking each other.

SQLite has its own command-line tool. Install it if you don’t have it, then open the database:

sudo apt install -y sqlite3
sqlite3 data/sqlite/platform.db

You’re now in the SQLite shell. Try these:

.tables
.schema sensor_readings
SELECT COUNT(*) FROM sensor_readings;
.exit

Expected:

• .tables lists the seven tables: devices, sensors, sensor_readings, system_logs, modules, ai_prompts, ai_responses. (The last three are reserved for later phases.)
• .schema sensor_readings prints the CREATE TABLE statement — this is what Alembic ran for you.
• SELECT COUNT(*) returns 0 if no readings have been published yet, or a number if Phase 3 was running before you restarted.

Start the publisher from your laptop again (same command as Phase 3):

python3 scripts/publish-test-sensor.py \
  --host pi-iot-XX.local \
  --password "<your MQTT password>"

Let it run for at least 30 seconds. You should see the dashboard light up as before. Now, on the Pi, peek at the database again:

sqlite3 data/sqlite/platform.db <<'SQL'
.headers on
.mode column
SELECT COUNT(*) AS readings FROM sensor_readings;
SELECT id, device_id, metric, unit FROM sensors;
SELECT * FROM sensor_readings ORDER BY id DESC LIMIT 5;
SQL

You should see:

• A growing readings count — about 2 readings per second (one for temperature, one for humidity).
• Two rows in sensors: one for temperature, one for humidity, both for microbit-test-01.
• The five most recent readings with timestamps.

You don’t need sqlite3 to read the database. The API exposes the same data as JSON. From any machine on the LAN:

curl -s http://pi-iot-XX.local/api/sensors | python3 -m json.tool

This lists every sensor with its latest value. To get a small time series:

curl -s "http://pi-iot-XX.local/api/sensors/microbit-test-01.temperature/readings?limit=10" \
  | python3 -m json.tool

You get the last ten readings, newest first. limit is capped at 1000 to stop a runaway query from filling memory.

The point of all this is that the data survives. Let’s force a restart and see what happens.

1. Stop the publisher on your laptop (Ctrl+C).
2. On the Pi, restart just the API:
   terminal · on the PiCopy

   docker compose restart api

3. Watch the dashboard. Within a few seconds, the connection pill goes back to connected, and your two cards reappear — with their last values from before the restart, and a STALE badge.

That’s the database working. The API booted up, ran SELECT against the sensors and sensor_readings tables, populated the in-memory cache, and the dashboard saw the snapshot the moment it reconnected.

For an even stronger demo, reboot the whole Pi:

sudo reboot

Wait for it to come back up (~1 minute). SSH back in, check docker compose ps — everything should be Up. Open the dashboard — the cards are still there.

Storage isn’t free. If the publisher runs for a year at 1 reading per second, that’s 31 million rows for one sensor. Most of that history isn’t interesting after a month.

The API has a built-in scheduler. Every day at 03:00 UTC, it deletes sensor_readings rows older than 30 days and writes an audit row to system_logs. The retention window is configurable via the READINGS_RETENTION_DAYS environment variable.

You can browse the audit log via the API:

curl -s "http://pi-iot-XX.local/api/system/logs?limit=10" | python3 -m json.tool

You should see a boot event from when the API last started. After 03:00 tomorrow, you’ll see a reading_retention.run event with how many rows it deleted.

Tick everything below before declaring Phase 4 done.