Store and retain MQTT time-series data in SQLite

Steps

Screens captured against JupyterLite Python 3.12.7 / Pyodide 0.27.6; Chromium 148.0.7778.96 on 2026-09-10; the tool may have moved on — the text steps are the contract.

1. 1 Step 1

   Do

   Run `python3 main.py --step 1` to create the in-memory readings table and inspect its index contract.

   You will see

   The terminal prints the `CREATE TABLE` and `CREATE INDEX` SQL, then the PRAGMA rows `0 sensor` and `1 observed_at`.

   Why it matters

   Seeing both schema statements and SQLite's returned index metadata makes identity and observed-time access reviewable before data arrives.

2. 2 Step 2

   Do

   Run `python3 main.py --step 2` online to publish six fictional readings to two sensor topics on the public broker.

   You will see

   Six `topic payload` lines show r1 through r6 arriving on living-room and kitchen topics before `Delivery check: six validated payloads reached the subscriber.`

   Why it matters

   The actual received topics and JSON establish which sensor identities and fields reached intake behind the final delivery count.

3. 3 Step 3

   Do

   Run `python3 main.py --step 3` to validate a payload after deliberately removing its humidity field.

   You will see

   The displayed rejected JSON lacks humidity beside `Rule: every Reading field is required before storage.` and `Broken rule: required field check -> missing=humidity`.

   Why it matters

   Putting the malformed payload next to the violated rule lets the learner inspect why rejection is deliberate rather than a silent storage gap.

4. 4 Step 4

   Do

   Run `python3 main.py --step 4` to insert the validated fixture into SQLite.

   You will see

   After the printed SELECT statement, a six-row table lists each event, sensor, observed time, and temperature; the duplicate rule names event_id as primary key.

   Why it matters

   The returned rows prove what was stored, while the identity rule explains how a replayed event would be constrained.

5. 5 Step 5

   Do

   Run `python3 main.py --step 5` to query only the living-room sensor window.

   You will see

   The detail SELECT returns r1=20.0, r2=21.0, and r3=22.0; the aggregate SELECT then returns `3 21.0 20.0 22.0`.

   Why it matters

   Showing both SQL statements, source rows, and aggregate row lets the learner recompute the rollup and see what its average hides.

6. 6 Step 6

   Do

   Run `python3 main.py --step 6` to add one reading whose observed time belongs inside the earlier window but whose receive time is later.

   You will see

   The late-1 row shows observed_at=105 and received_at=205 beside a before/after table changing 3 at 21.0 to 4 at 21.8.

   Why it matters

   The late row and repeated aggregate expose why the result changes and why consumers must receive the CORRECTED label.

7. 7 Step 7

   Do

   Run `python3 main.py --step 7` to dry-run deletion of readings older than the cutoff; the transaction is rolled back.

   You will see

   The retention SELECT lists r1, r4, r2, and r5 as candidates, followed by the DELETE SQL and `Transaction action: ROLLBACK; no candidate was deleted.`

   Why it matters

   Showing candidate rows before the destructive statement lets the learner audit cutoff scope and confirm the dry run remained reversible.

8. 8 Step 8

   Do

   Run `python3 main.py --step 8` to copy the database with SQLite's backup API and query the restored copy.

   You will see

   The source and restored SELECT statements each return 6, followed by `Comparison: row counts match, so the restored sample is readable.`

   Why it matters

   Running the same read against both databases demonstrates recoverability instead of treating a completed backup call as sufficient evidence.