turnley.dev

PHP and MySQL code for faucet operators

Tools & Calculators

FRD-015

Presence tracking that stores nothing worth stealing

A live view of who is on the site needs a hashed session id, a page name and two timestamps. No address, no user agent, no history.

Seeing who is on the site right now is genuinely useful for an operator — it shows whether a release broke something, and what people actually do. It is also an easy place to accumulate a surveillance log nobody asked for.

Keep one row per browser session: a hash of the session id, the user id, a page name, when the session started and when it was last seen. That supports a live roll call and nothing else.

Prune it from cron and on sign-out. There is no reason for yesterday's presence to exist.

PHP 
/*
CREATE TABLE presence (
  skey CHAR(64) NOT NULL PRIMARY KEY,     -- sha256 of the session id, never the id
  user_id INT UNSIGNED NOT NULL,
  page VARCHAR(40) NOT NULL DEFAULT '',
  started_at DATETIME NOT NULL,
  seen_at DATETIME NOT NULL,
  KEY ix_seen (seen_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
*/

function presence_touch(PDO $pdo, int $userId, string $page): void
{
    if ($userId <= 0) {
        return;                                      // signed-in users only; no guest rows
    }
    try {
        $skey = hash('sha256', session_id());        // the raw id never lands in a table
        $pdo->prepare(
            'INSERT INTO presence (skey, user_id, page, started_at, seen_at)
             VALUES (?, ?, ?, NOW(), NOW())
             ON DUPLICATE KEY UPDATE page = VALUES(page), seen_at = NOW()'
        )->execute([$skey, $userId, substr($page, 0, 40)]);
    } catch (Throwable $e) {
        // Presence must never be able to break a page.
    }
}

function presence_now(PDO $pdo, int $idleSeconds = 300): array
{
    $st = $pdo->prepare(
        'SELECT p.page, p.started_at, p.seen_at, u.username,
                TIMESTAMPDIFF(SECOND, p.started_at, NOW()) AS session_seconds,
                TIMESTAMPDIFF(SECOND, p.seen_at, NOW())    AS idle_seconds
         FROM presence p JOIN users u ON u.id = p.user_id
         WHERE p.seen_at > DATE_SUB(NOW(), INTERVAL ? SECOND)
         ORDER BY p.seen_at DESC'
    );
    $st->execute([$idleSeconds]);
    return $st->fetchAll(PDO::FETCH_ASSOC);
}

Using it

Poll the admin view from its own JSON endpoint every few seconds and pause when the tab is hidden. A live view that keeps polling in a background tab is a self-inflicted load problem.

Mask usernames for preview or demo admins, the same as everywhere else.

Delete rows on sign-out, and prune anything older than an hour from cron.

What bites people

Wrap every write. A missing presence table should not white-screen the site.

Do not add the address "just for debugging". The value of this design is that there is nothing in it to leak.

Excluding guests is deliberate: starting a session for every anonymous visitor creates rows describing nobody and inflates your numbers.

Also in Anti-Bot and Fraud