Skip to content
Tutorials Center
My learning

Topics

Lesson 35 of 37 ยท Projects and Practice

Project: A Data Logger with a Live Dashboard

Combine everything: log sensor data to SQLite, expose a JSON API with downsampling, and draw a live chart page. Includes architecture, a retention job and runnable aggregation code.

This project joins the pieces from the whole course into one useful system: a logger that collects sensor readings, a database that keeps them, a small web service that serves summaries, and a dashboard page that draws a chart which updates by itself. You will run it on one Pi, and the same design scales to several sensors.

Real hardware and Flask are not available on this site, so the sensor reads and the server code are read-only; the data handling is in runnable Python. Treat this as your capstone and extend it.

Architecture

flowchart LR
    A["Sensors"] --> B["logger.service"]
    B --> C["SQLite database"]
    C --> D["API: /api/series"]
    D --> E["Dashboard page + chart"]
    F["Timer: nightly cleanup"] --> C
    G["Timer: nightly backup"] --> C

Two services and two timers share one database file.

Responsibilities are separated so each can be restarted or replaced independently: the logger only writes, the API only reads, and the cleanup and backup jobs run from timers. SQLite handles one writer and many readers well; turn on write-ahead logging for a smoother experience with PRAGMA journal_mode=WAL;.

The data model

One narrow table works for any number of sensors: each row says which sensor, when, and what value. Adding a sensor means adding rows, not columns.

Python Create the schema and insert sample data

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The index on (sensor, ts) makes "give me this sensor for the last 24 hours" fast, even with millions of rows.

Downsampling for charts

A dashboard cannot draw 100,000 points; it needs about 200. Downsampling groups the data into time buckets and returns one summary per bucket (average, plus min and max to keep spikes visible). A single SQL query does it with integer division:

Python Bucket the series into one point per hour

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Choose the bucket size from the requested range: 1 minute for the last hour, 5 minutes for a day, 1 hour for a month. A tiny function does it, so the page never asks for too many points.

Python Pick a bucket size from the requested range

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The logger

The logger is a systemd service with a loop like the weather station's, but writing the narrow table. Read-only sketch:

Python
import sqlite3, time, logging

log = logging.getLogger("logger")
db = sqlite3.connect("/var/lib/pi-logger/data.db")
db.execute("PRAGMA journal_mode=WAL")

while True:
    now = int(time.time())
    try:
        t, h = read_bme280()          # your sensor code
        db.executemany("INSERT INTO sample VALUES (?,?,?)",
                       [(now, "room_c", t), (now, "humidity", h)])
        db.commit()
    except OSError:
        log.exception("sensor read failed")
    time.sleep(300 - now % 300)       # wake on the next 5-minute boundary

Sleeping to the next boundary (300 - now % 300) keeps samples aligned and avoids drift.

The API and the page

Using Flask (read-only, as in the web server lesson):

Python
from flask import Flask, request, jsonify
import sqlite3, time

app = Flask(__name__)

@app.get("/api/series")
def api_series():
    sensor = request.args.get("sensor", "room_c")
    hours = min(int(request.args.get("hours", 24)), 24 * 365)
    now = int(time.time())
    bucket = bucket_for(hours * 3600)
    db = sqlite3.connect("file:/var/lib/pi-logger/data.db?mode=ro", uri=True)
    rows = db.execute(SERIES_SQL, {"b": bucket, "s": sensor,
                                   "a": now - hours * 3600, "z": now}).fetchall()
    return jsonify([{"t": r[0], "avg": r[1], "min": r[2], "max": r[3]} for r in rows])

Note mode=ro: the API opens the database read-only, so a bug in it can never damage the data, and min(...) limits the range a client can request. The page uses fetch and draws with a small chart library or plain SVG:

HTML
<svg id="chart" viewBox="0 0 600 200"></svg>
<script>
async function draw() {
  const data = await (await fetch("/api/series?sensor=room_c&hours=24")).json();
  const lo = Math.min(...data.map(d => d.min)), hi = Math.max(...data.map(d => d.max));
  const x = i => i / (data.length - 1) * 600;
  const y = v => 200 - (v - lo) / (hi - lo) * 190 - 5;
  document.getElementById("chart").innerHTML =
    `<polyline fill="none" stroke="steelblue" stroke-width="2"
       points="${data.map((d, i) => x(i) + "," + y(d.avg)).join(" ")}"/>`;
}
draw(); setInterval(draw, 60000);
</script>

Cleanup and retention

Raw 5-minute data is fine for months, but years of it is unnecessary. A nightly job can aggregate old rows into hourly averages and delete the raw ones:

Python Aggregate old data and delete the raw rows

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Run the aggregation and delete inside one transaction, as here, so a crash cannot lose data. Follow with VACUUM occasionally to return space, and take backups before maintenance, as in the backups lesson.

Ideas to extend it

  • Add alert rules (temperature above a threshold for 15 minutes) and notifications.
  • Accept readings from other Pis over MQTT.
  • Add authentication with nginx basic auth and HTTPS.
  • Show the Pi's own health (CPU temperature, throttling) as sensors, with the monitoring lesson.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why use a narrow table (ts, sensor, value) instead of one column per sensor?

    New sensors are just new values in the sensor column.
  2. 2. What is downsampling for?

    Charts need a few hundred points, so rows are summarized into time buckets.
  3. 3. Why open the database with mode=ro in the API?

    Read-only access makes API bugs harmless to the stored data.
  4. 4. What is the advantage of sleeping until the next 5-minute boundary?

    300 - now % 300 compensates for the time spent reading and writing.

Summary

  • Separate logging, serving, cleanup and backup into small cooperating pieces.
  • A narrow (ts, sensor, value) table with an index on (sensor, ts) scales well.
  • Downsample with time buckets; choose bucket size from the requested range.
  • Use read-only connections in the API and transactions in maintenance jobs.
  • Aggregate old data instead of keeping every raw row forever.

Next: Troubleshooting Your Pi