Giving the Dashboard a Memory — The Database

Why session storage was always a sandcastle

Open progress.php and read the comment at the top — past-you (or whoever scaffolded this) literally wrote that progress lives in $_SESSION and you should "switch to a JSON file or DB later if you want it to survive a cookie wipe." That day is today. Session storage is tied to a single browser's session cookie. Clear your cookies, switch browsers, or let the session expire, and every "✓ done" you earned vanishes. It's a sandcastle — fine for a demo, useless for something you actually rely on over weeks.

A database fixes this permanently. The data lives on the server, in a table, on disk. It doesn't care what browser you use or whether you cleared cookies. And — crucially for the next five chapters — a database is a shared shelf: tasks, stats, practice logs, and user accounts can all live there and reference each other. That's why this one chapter unblocks everything after it.

The connection helper — you've written this before

Good news: you already built this exact helper twice, once for MedTrack and once for HomeStream. Same pattern, new database name. The db() function uses a static variable so the PDO connection is created once and reused for the whole request — no reconnecting on every query.

<?php
declare(strict_types=1);

function db(): PDO {
    static $pdo = null;
    if ($pdo === null) {
        $pdo = new PDO(
            'mysql:host=127.0.0.1;dbname=dashboard;charset=utf8mb4',
            'dashboard_user',
            getenv('DB_PASS') ?: 'your-password-here',
            [
                PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                PDO::ATTR_EMULATE_PREPARES   => false,
            ]
        );
    }
    return $pdo;
}

Those three PDO options are the same trio from Part 2, and each one earns its place: ERRMODE_EXCEPTION makes failed queries throw (so you find out immediately instead of silently getting false); FETCH_ASSOC gives you clean associative arrays instead of duplicated numeric+named columns; EMULATE_PREPARES = false uses real server-side prepared statements, which is the correct, safe choice. Reading the password from getenv('DB_PASS') means you can keep the real secret out of the file (more on that in the polish chapter's security pass).

🐍 Python brain: PDO is your sqlite3/psycopg2. The static $pdo trick is like a module-level connection singleton. Prepared statements with ? placeholders are exactly cursor.execute(sql, params) — the parametrized-query pattern that stops SQL injection cold.

Designing the schema

We'll create one database with a handful of small tables. Don't overthink it — like the HomeStream media table, "good enough for a personal dashboard" really is good enough. Here's everything the next few chapters need:

CREATE DATABASE IF NOT EXISTS dashboard
    CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

USE dashboard;

-- chapter progress (replaces the $_SESSION version)
CREATE TABLE progress (
    id        INT PRIMARY KEY AUTO_INCREMENT,
    module    VARCHAR(20)  NOT NULL,
    chapter   VARCHAR(40)  NOT NULL,
    done_at   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_progress (module, chapter)
);

-- tasks / queue (chapter 5)
CREATE TABLE tasks (
    id         INT PRIMARY KEY AUTO_INCREMENT,
    label      VARCHAR(200) NOT NULL,
    tag        VARCHAR(20)  NOT NULL DEFAULT 'WORK',
    lane       ENUM('home','work') NOT NULL DEFAULT 'work',
    done       TINYINT(1)   NOT NULL DEFAULT 0,
    created_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- activity feed + the source of real stats (chapter 6)
CREATE TABLE activity (
    id         INT PRIMARY KEY AUTO_INCREMENT,
    tag        VARCHAR(20)  NOT NULL,
    message    VARCHAR(255) NOT NULL,
    happened_at DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_when (happened_at)
);

-- music practice log (chapter 7)
CREATE TABLE practice_log (
    id         INT PRIMARY KEY AUTO_INCREMENT,
    on_date    DATE         NOT NULL,
    minutes    INT          NOT NULL,
    UNIQUE KEY uq_day (on_date)
);

A couple of design notes that'll pay off later. The UNIQUE KEY on (module, chapter) in progress means a chapter can only be marked done once — and lets us use a clean "insert or delete" toggle without worrying about duplicates. The tasks.tag column reuses the same tag vocabulary the dashboard already shows (the little colored qtag chips). The activity table is deliberately generic — one row per thing that happens — because chapter 6 builds the entire Home dashboard's "real numbers" by running aggregate queries over it. And practice_log's unique key on on_date lets us "add to today's total" with a single upsert.

Migrating progress without touching index.php

Here's a genuinely satisfying bit of engineering. index.php calls three functions — is_done(), set_done(), and module_stats() — and it doesn't care how they store data. So if we rewrite progress.php to use the database but keep those exact function signatures, index.php doesn't change by a single character. That's the power of a clean interface: we swap the entire storage engine underneath and the caller never notices.

<?php
declare(strict_types=1);
require_once __DIR__ . '/lib/db.php';

function is_done(string $module, string $chapter): bool {
    $stmt = db()->prepare(
        "SELECT 1 FROM progress WHERE module = ? AND chapter = ?"
    );
    $stmt->execute([$module, $chapter]);
    return (bool) $stmt->fetchColumn();
}

function set_done(string $module, string $chapter, bool $done): void {
    if ($done) {
        // INSERT IGNORE: the UNIQUE key makes re-marking a no-op
        $stmt = db()->prepare(
            "INSERT IGNORE INTO progress (module, chapter) VALUES (?, ?)"
        );
        $stmt->execute([$module, $chapter]);
    } else {
        $stmt = db()->prepare(
            "DELETE FROM progress WHERE module = ? AND chapter = ?"
        );
        $stmt->execute([$module, $chapter]);
    }
}

function module_stats(string $module, array $subs): array {
    $stmt = db()->prepare("SELECT COUNT(*) FROM progress WHERE module = ?");
    $stmt->execute([$module]);
    $done  = (int) $stmt->fetchColumn();
    $total = count($subs);
    return [
        'done'  => $done,
        'total' => $total,
        'pct'   => $total > 0 ? (int) round($done * 100 / $total) : 0,
    ];
}

Notice set_done uses INSERT IGNORE — because of the unique key, marking an already-done chapter just gets silently ignored instead of erroring. Unmarking is a plain DELETE. Same two-state toggle as before, now durable. The one thing to watch: module_stats counts all rows for a module, which assumes every stored chapter is still a real chapter — fine for us, but if you ever rename a slug you'd want to clean up orphans.

Heads up: the moment you switch progress.php to the database, your existing session-based progress is gone — it was never in the DB. That's expected. Just re-tick the chapters you'd finished. From now on it sticks for real.