10 - RouteTrack Pi — Shift Mode (SQLite + Flask API + Dashboard Controls)

Date: December 25, 2025
Category: Raspberry Pi / GPS / SQLite / Flask / Leaflet
Backlink: 09 – RouteTrack Pi — Dashboard Autostart (Gunicorn + systemd)

---

Project Goal

This phase introduces Shift Mode to RouteTrack.

Shift Mode allows RouteTrack to track work sessions independently of calendar days, which is essential for a portable, vehicle-mounted system that:

• Is powered down frequently

• Moves between locations

• Does not follow strict midnight-to-midnight boundaries

• Needs accurate per-shift metrics (hours, stops, time-on-site)

The dashboard now supports a simple workflow: Start Shift → Drive → End Shift

---

Why Shift Mode Matters

Daily summaries work well for reporting, but they don’t match real-world truck usage:

• Overnight work can cross calendar boundaries

• Reboots/power loss interrupt sessions

• Short test runs clutter daily totals

Shift Mode solves this by creating a clean “session boundary” that the user controls.

---

Database Changes

New shifts Table

A new SQLite table stores shift metadata independently of GPS data.

Table: shifts

CREATE TABLE IF NOT EXISTS shifts (
  id INTEGER PRIMARY KEY AUTOINCREMENT,AUTOINCREMENT,
  start_ts TEXT NOT NULL,NULL,
  end_ts TEXT,TEXT,
  note TEXT
);

CREATE INDEX IF NOT EXISTS idx_shifts_start_ts
  ON shifts(start_ts)shifts(start_ts);

Design Notes:

• start_ts and end_ts stored as UTC ISO-8601 strings

• end_ts stays NULL while a shift is active

• Only one active shift allowed at a time

• Lightweight, isolated table to minimize lock contention

---

Applying the Schema Safely

Because GPS logging writes constantly to SQLite, stop the logger before applying schema changes.

sudo systemctl stop routetrack-logger.service
sqlite3 /opt/routetrack/data/routetrack.sqlite < /opt/routetrack/config/schema.sql
sqlite3 /opt/routetrack/data/routetrack.sqlite ".tables"
sudo systemctl start routetrack-logger.service

Expected tables:

daily_summary  gps_points  shifts  stop_events

---

Flask API Enhancements (Full app.py)

Shift Mode is implemented via additional Flask API endpoints.

New Endpoints

Method	Endpoint	Purpose
GET	/api/shift/active	Returns the active shift (if any)
POST	/api/shift/start	Starts a new shift
POST	/api/shift/end	Ends the active shift
GET	/api/shift/summary	Returns live stats for the active shift

Replace /opt/routetrack/web/app.py

Edit:

sudo nano /opt/routetrack/web/app.py

Paste the full file:

#!/usr/bin/env python3
"""
RouteTrack Local Dashboard (Flask)
----------------------------------

Provides:
- Web UI page (Leaflet map)
- JSON API endpoints:
  - /api/summary/<date>
  - /api/points/<date>
  - /api/stops/<date>

Shift Mode endpoints:
  - GET  /api/shift/active
  - POST /api/shift/start
  - POST /api/shift/end
  - GET  /api/shift/summary

Notes:
- This dashboard is READ-ONLY for GPS-derived tables:
    gps_points, stop_events, daily_summary
- Shift Mode DOES write to SQLite, but only into the "shifts" table.
  This avoids lock contention with the logger and keeps writes minimal.
"""

import sqlite3
from datetime import datetime,datetime, timezone

from flask import Flask,Flask, jsonify,jsonify, render_template,render_template, request

DB_PATH = "/opt/routetrack/data/routetrack.sqlite"

app = Flask(__name__)Flask(__name__)


def db(db():
    """
    Open SQLite connection with Row output so we can jsonify results
    via dict(row).
    """
    conn = sqlite3.connect(DB_PATH)sqlite3.connect(DB_PATH)
    conn.conn.row_factory = sqlite3.sqlite3.Row
    return conn


def utc_now_iso(utc_now_iso():
    """
    Return current UTC timestamp in ISO-8601 format (no microseconds).
    Example: 2025-12-25T16:05:00+00:00
    """
    return datetime.now(timezone.utc)datetime.replace(microsecond=0)now(timezone.isoformat(utc).replace(microsecond=0).isoformat()


@app.route(@app.route("/")
def index(index():
    """Serve the dashboard HTML page (Leaflet UI)."""
    return render_template(render_template("index.html")


# ============================================================
# Existing Daily Views (READ-ONLY)
# ============================================================

@app.route(@app.route("/api/summary/<day>")
def api_summary(day)api_summary(day):
    """Return the daily_summary row for YYYY-MM-DD."""
    conn = db(db()
    cur = conn.cursor(conn.cursor()
    cur.execute(cur.execute("SELECT * FROM daily_summary WHERE date = ?", (day,day,))
    row = cur.fetchone(cur.fetchone()
    conn.close(conn.close()

    if not row:row:
        return jsonify(jsonify({"error": "No summary for this date"}), 404

    return jsonify(dict(row)jsonify(dict(row))


@app.route(@app.route("/api/points/<day>")
def api_points(day)api_points(day):
    """
    Return route points for a given day as a list of [lat, lon]
    suitable for drawing a Leaflet polyline.
    """
    conn = db(db()
    cur = conn.cursor(conn.cursor()

    start = f"{day}day}T00:00:00Z"
    end = f"{day}day}T23:59:59Z"

    cur.execute(cur.execute("""
        SELECT ts, lat, lon
        FROM gps_points
        WHERE ts >= ? AND ts <= ?
          AND mode = 3
          AND lat IS NOT NULL
          AND lon IS NOT NULL
        ORDER BY ts
    """, (start,start, end)end))

    rows = cur.fetchall(cur.fetchall()
    conn.close(conn.close()

    return jsonify(jsonify([[r[r["lat"], r[r["lon"]] for r in rows]rows])


@app.route(@app.route("/api/stops/<day>")
def api_stops(day)api_stops(day):
    """
    Return stop events that START on a given day.
    """
    conn = db(db()
    cur = conn.cursor(conn.cursor()

    start = f"{day}day}T00:00:00Z"
    end = f"{day}day}T23:59:59Z"

    cur.execute(cur.execute("""
        SELECT start_ts, end_ts, duration_seconds, lat, lon
        FROM stop_events
        WHERE start_ts >= ? AND start_ts <= ?
        ORDER BY start_ts
    """, (start,start, end)end))

    rows = cur.fetchall(cur.fetchall()
    conn.close(conn.close()

    return jsonify(jsonify([dict(r)dict(r) for r in rows]rows])


# ============================================================
# Shift Mode (writes only to shifts table)
# ============================================================

@app.route(@app.route("/api/shift/active")
def api_shift_active(api_shift_active():
    """
    Returns the currently active shift (where end_ts is NULL),
    or {"active": false} if none exists.
    """
    conn = db(db()
    cur = conn.cursor(conn.cursor()
    cur.execute(cur.execute("""
        SELECT *
        FROM shifts
        WHERE end_ts IS NULL
        ORDER BY id DESC
        LIMIT 1
    """)
    row = cur.fetchone(cur.fetchone()
    conn.close(conn.close()

    if not row:row:
        return jsonify(jsonify({"active": False}False})

    return jsonify(dict(row)jsonify(dict(row))


@app.route(@app.route("/api/shift/start", methods=methods=["POST"])
def api_shift_start(api_shift_start():
    """
    Start a new shift.
    Prevents multiple active shifts at once.

    Optional JSON body:
      {"note": "optional note here"}
    """
    note = ""
    try:try:
        payload = request.get_json(silent=True)request.get_json(silent=True) or {}
        note = payload.get(payload.get("note", "") or ""
    except Exception:Exception:
        note = ""

    conn = db(db()
    cur = conn.cursor(conn.cursor()

    # Block starting a shift if one is already active
    cur.execute(cur.execute("SELECT id FROM shifts WHERE end_ts IS NULL LIMIT 1")
    if cur.fetchone(cur.fetchone():
        conn.close(conn.close()
        return jsonify(jsonify({"error": "A shift is already active."}), 409

    start_ts = utc_now_iso(utc_now_iso()
    cur.execute(cur.execute(
        "INSERT INTO shifts (start_ts, note) VALUES (?, ?)",
        (start_ts,start_ts, note)note)
    )
    conn.commit(conn.commit()

    cur.execute(cur.execute("SELECT * FROM shifts WHERE id = last_insert_rowid()")
    row = cur.fetchone(cur.fetchone()
    conn.close(conn.close()

    return jsonify(dict(row)jsonify(dict(row))


@app.route(@app.route("/api/shift/end", methods=methods=["POST"])
def api_shift_end(api_shift_end():
    """
    End the currently active shift by setting end_ts.
    """
    conn = db(db()
    cur = conn.cursor(conn.cursor()

    cur.execute(cur.execute("""
        SELECT *
        FROM shifts
        WHERE end_ts IS NULL
        ORDER BY id DESC
        LIMIT 1
    """)
    row = cur.fetchone(cur.fetchone()

    if not row:row:
        conn.close(conn.close()
        return jsonify(jsonify({"error": "No active shift."}), 404

    end_ts = utc_now_iso(utc_now_iso()
    cur.execute(cur.execute("UPDATE shifts SET end_ts = ? WHERE id = ?", (end_ts,end_ts, row[row["id"]))
    conn.commit(conn.commit()

    cur.execute(cur.execute("SELECT * FROM shifts WHERE id = ?", (row[row["id"],))
    updated = cur.fetchone(cur.fetchone()
    conn.close(conn.close()

    return jsonify(dict(updated)jsonify(dict(updated))


@app.route(@app.route("/api/shift/summary")
def api_shift_summary(api_shift_summary():
    """
    Returns a lightweight summary for the ACTIVE shift window.

    Current output:
      - shift window (start -> now)
      - number of gps points in that window (mode=3)
      - stop count + stopped seconds for stop_events inside window

    NOTE:
      This does not compute miles yet. That comes next.
    """
    conn = db(db()
    cur = conn.cursor(conn.cursor()

    # Find active shift
    cur.execute(cur.execute("""
        SELECT *
        FROM shifts
        WHERE end_ts IS NULL
        ORDER BY id DESC
        LIMIT 1
    """)
    shift = cur.fetchone(cur.fetchone()

    if not shift:shift:
        conn.close(conn.close()
        return jsonify(jsonify({"error": "No active shift."}), 404

    start_ts = shift[shift["start_ts"]
    end_ts = utc_now_iso(utc_now_iso()  # "now" for active shift

    # gps_points stores timestamps with trailing "Z"
    # shifts stores timestamps with "+00:00"
    # Convert bounds for gps_points query
    start_bound = start_ts.replace(start_ts.replace("+00:00", "Z")
    end_bound = end_ts.replace(end_ts.replace("+00:00", "Z")

    cur.execute(cur.execute("""
        SELECT COUNT(*) as point_count
        FROM gps_points
        WHERE ts >= ? AND ts <= ?
          AND mode = 3
          AND lat IS NOT NULL
          AND lon IS NOT NULL
    """, (start_bound,start_bound, end_bound)end_bound))
    point_row = cur.fetchone(cur.fetchone()

    cur.execute(cur.execute("""
        SELECT COUNT(*) as stop_count,
               COALESCE(SUM(duration_seconds), 0) as stopped_s
        FROM stop_events
        WHERE start_ts >= ? AND end_ts <= ?
    """, (start_ts,start_ts, end_ts)end_ts))
    stop_row = cur.fetchone(cur.fetchone()

    conn.close(conn.close()

    return jsonify(jsonify({
        "shift_id": shift[shift["id"],
        "start_ts": start_ts,start_ts,
        "end_ts": end_ts,end_ts,
        "points": int(point_row[int(point_row["point_count"] or 0)0),
        "stop_count": int(stop_row[int(stop_row["stop_count"] or 0)0),
        "stopped_time_seconds": int(stop_row[int(stop_row["stopped_s"] or 0)0),
        "note": shift[shift["note"] or ""
    })


if __name__ == "__main__":
    # Local dev run (manual)
    app.run(host=app.run(host="0.0.0.0", port=5000,port=5000, debug=False)debug=False)

Make executable:

sudo chmod +x /opt/routetrack/web/app.py

Restart dashboard service:

sudo systemctl restart routetrack-dashboard.service

---

Dashboard UI Enhancements (Full index.html)

The dashboard top bar now includes Shift controls:

• Start Shift

• End Shift

• Refresh Shift

• Shift status pill

A new Active Shift section shows live stats and refreshes every 30 seconds.

Replace /opt/routetrack/web/templates/index.html

Edit:

sudo nano /opt/routetrack/web/templates/index.html

Paste the full file:

<!doctype html>
<html>
<head>
  <meta charset=charset="utf-8"8" />
  <title>RouteTrack Dashboard</title>
  <meta name=name="viewport"viewport" content=content="width=device-width, initial-scale=1"1" />

  <!-- Leaflet (CDN) -->
  <link
    rel=rel="stylesheet"stylesheet"
    href=href="https://unpkg.com/leaflet@1.9.4/dist/leaflet.css"css"
  />
  <script src=src="https://unpkg.com/leaflet@1.9.4/dist/leaflet.js"js"></script>

  <style>
    body { margin:margin: 0;0; font-family:family: Arial,Arial, sans-serif;serif; }
    #topbar { padding:padding: 10px;10px; background:background: #111;#111; color:color: #fff;#fff; display:display: flex;flex; gap:gap: 10px;10px; align-items:items: center;center; flex-wrap:wrap: wrap;wrap; }
    #topbar button { cursor:cursor: pointer;pointer; }
    #map { height:height: 70vh;70vh; }
    #stats { padding:padding: 10px;10px; }
    .row { margin:margin: 6px 0;0; }
    code { background:background: #eee;#eee; padding:padding: 2px 4px;4px; border-radius:radius: 4px;4px; }
    .pill { display:display: inline-block;block; padding:padding: 2px 8px;8px; border-radius:radius: 999px;999px; font-size:size: 12px;12px; background:background: #333;#333; color:color: #fff;#fff; }
  </style>
</head>

<body>
  <div id=id="topbar"topbar">
    <strong>RouteTrack</strong> — Local Dashboard

    <span>|</span>

    <span>Date:</span>
    <input id=id="day"day" type=type="date"date" />
    <button onclick=onclick="loadAll()">Load Day</button>

    <span>|</span>

    <button onclick=onclick="startShift()">Start Shift</button>
    <button onclick=onclick="endShift()">End Shift</button>
    <button onclick=onclick="loadShift()">Refresh Shift</button>

    <span id=id="shiftStatus"shiftStatus" class=class="pill"pill">Shift: Unknown</span>
  </div>

  <div id=id="map"map"></div>

  <div id=id="stats"stats">
    <h3>Active Shift</h3>
    <div id=id="shift"shift"></div>

    <h3>Daily Summary</h3>
    <div id=id="summary"summary"></div>

    <h3>Stops</h3>
    <div id=id="stops"stops"></div>
  </div>

<script>
  // Default date = today (browser local time)
  const dayInput = document.getElementById("day");
  dayInput.valueAsDate = new Date();

  const shiftDiv = document.getElementById("shift");
  const shiftStatus = document.getElementById("shiftStatus");

  const map = L.map("map").setView([38.7153, -89.94], 13);

  L.tileLayer("https://{s}.tile.openstreetmap.org/{z}/{x}/{y}.png", {
    maxZoom: 19,
    attribution: "&copy; OpenStreetMap contributors"
  }).addTo(map);

  let routeLine = null;
  let stopMarkers = [];

  // -----------------------------
  // Day-Based Views
  // -----------------------------
  async function loadAll() {
    const day = dayInput.value;
    await loadRoute(day);
    await loadStops(day);
    await loadSummary(day);
  }

  async function loadRoute(day) {
    const res = await fetch(`/api/points/${day}`);
    const pts = await res.json();

    if (routeLine) map.removeLayer(routeLine);
    if (!pts.length) return;

    routeLine = L.polyline(pts, { weight: 4 }).addTo(map);
    map.fitBounds(routeLine.getBounds());
  }

  async function loadStops(day) {
    stopMarkers.forEach(m => map.removeLayer(m));
    stopMarkers = [];

    const res = await fetch(`/api/stops/${day}`);
    const stops = await res.json();

    const stopsDiv = document.getElementById("stops");
    stopsDiv.innerHTML = "";

    if (!Array.isArray(stops) || !stops.length) {
      stopsDiv.innerHTML = "<div class=class='row'row'>No stops found.</div>";
      return;
    }

    stops.forEach(s => {
      const durMin = Math.round(s.duration_seconds / 60);
      stopsDiv.innerHTML += `<div class=class="row"row">
        Stop: <code>${s.start_ts}</code> → <code>${s.end_ts}</code>
        (${durMin} min)
      </div>`;

      if (s.lat && s.lon) {
        const m = L.marker([s.lat, s.lon]).addTo(map)
          .bindPopup(`Stop (${durMin} min)<br>${s.start_ts}`);
        stopMarkers.push(m);
      }
    });
  }

  async function loadSummary(day) {
    const summaryDiv = document.getElementById("summary");
    summaryDiv.innerHTML = "";

    const res = await fetch(`/api/summary/${day}`);
    const data = await res.json();

    if (data.error) {
      summaryDiv.innerHTML = `<div class=class="row"row">No summary for ${day}. Run processor first.</div>`;
      return;
    }

    summaryDiv.innerHTML = `
      <div class=class="row"row">Start: <code>${data.start_ts}</code></div>
      <div class=class="row"row">End: <code>${data.end_ts}</code></div>
      <div class=class="row"row">Distance: <strong>${data.total_distance_miles}</strong> miles</div>
      <div class=class="row"row">Moving: <strong>${Math.round(data.moving_time_seconds/60)}</strong> minutes</div>
      <div class=class="row"row">Stopped: <strong>${Math.round(data.stopped_time_seconds/60)}</strong> minutes</div>
      <div class=class="row"row">Stops: <strong>${data.stop_count}</strong></div>
    `;
  }

  // -----------------------------
  // Shift Mode
  // -----------------------------
  async function startShift() {
    const res = await fetch("/api/shift/start", {
      method: "POST",
      headers: {"Content-Type": "application/json"},
      body: JSON.stringify({ note: "" })
    });

    const data = await res.json();
    if (!res.ok) {
      alert(data.error || "Failed to start shift");
      return;
    }
    await loadShift();
  }

  async function endShift() {
    const res = await fetch("/api/shift/end", { method: "POST" });
    const data = await res.json();
    if (!res.ok) {
      alert(data.error || "Failed to end shift");
      return;
    }
    await loadShift();
  }

  async function loadShift() {
    shiftDiv.innerHTML = "";

    const activeRes = await fetch("/api/shift/active");
    const active = await activeRes.json();

    if (!active || active.active === false || !active.id) {
      shiftStatus.textContent = "Shift: Inactive";
      shiftDiv.innerHTML = "<div class=class='row'row'>No active shift. Click <strong>Start Shift</strong> to begin.</div>";
      return;
    }

    shiftStatus.textContent = `Shift: ACTIVE (#${active.id})`;

    const sumRes = await fetch("/api/shift/summary");
    const s = await sumRes.json();

    if (s.error) {
      shiftDiv.innerHTML = `<div class=class='row'row'>${s.error}</div>`;
      return;
    }

    shiftDiv.innerHTML = `
      <div class=class="row"row"><strong>Shift ID:</strong> ${s.shift_id}</div>
      <div class=class="row"row"><strong>Start (UTC):</strong> <code>${s.start_ts}</code></div>
      <div class=class="row"row"><strong>Now (UTC):</strong> <code>${s.end_ts}</code></div>
      <div class=class="row"row"><strong>Stops (inside window):</strong> ${s.stop_count}</div>
      <div class=class="row"row"><strong>Stopped Minutes:</strong> ${Math.round(s.stopped_time_seconds / 60)}</div>
      <div class=class="row"row"><strong>GPS Points (mode=3):</strong> ${s.points}</div>
    `;
  }

  // Auto-load on page open
  loadAll();
  loadShift();

  // Auto-refresh active shift every 30s (handy for truck use)
  setInterval(loadShift, 30000);
</script>
</body>
</html>

Restart dashboard:

sudo systemctl restart routetrack-dashboard.service

---

Validation & Testing

Before starting a shift:

curl http://localhost:5000/api/shift/active

Expected:

{"active":false}

curl http://localhost:5000/api/shift/summary

Expected:

{"error":"No active shift."}

Dashboard validation:

• Start Shift creates an active session

• Refresh Shift updates live metrics

• End Shift closes session cleanly

• Status pill returns to “Shift: Inactive”

---

Result

RouteTrack now supports:

• Continuous GPS logging

• Stop detection + daily summaries

• Local dashboard (Flask + Leaflet)

• Shift Mode with user-controlled Start/End

• Live shift summary panel in the UI

This moves RouteTrack closer to a true truck-ready route tracking + session logging system.

---

Next Steps

Now that Shift Mode works end-to-end, next upgrades will add:

1. Shift mileage + moving time

   • Apply Haversine logic inside shift window

2. Persist final shift totals

   • Save a shift summary row when ending a shift

3. Shift history

   • List past shifts and export (CSV/GeoJSON)

4. Optional: offline map tiles