tasplanning_report/public/dashboard.php

<?php
require_once __DIR__ . '/_bootstrap.php';
/**
 * dashboard.php — Query monitoring dashboard
 * Place in /home/modulos_llm/public/
 * DB path read from TPR_DB env var, defaults to /data/telemetry.db
 */
declare(strict_types=1);

$dbPath = getenv('TPR_DB') ?: '/data/telemetry.db';

$dashPass = getenv('DASHBOARD_PASS') ?: '';
if ($dashPass) {
    if (($_SERVER['PHP_AUTH_PW'] ?? '') !== $dashPass) {
        header('WWW-Authenticate: Basic realm="Dashboard"');
        header('HTTP/1.0 401 Unauthorized');
        exit('Unauthorised');
    }
}

// Copy to a temp file www-data can open — source DB is owned by uid 10001
// Write to a staging path first, then rename atomically to avoid a window
// where the file is world-readable before chmod tightens permissions.
$tmpDb = sys_get_temp_dir() . '/tpr_dash_' . date('YmdH') . '.db';
if (!file_exists($tmpDb) || (filemtime($tmpDb) < time() - 55)) {
    $stagingDb = $tmpDb . '.tmp';
    $prevUmask = umask(0177); // 0600 effective permissions on created file
    $copied = @copy($dbPath, $stagingDb);
    umask($prevUmask);
    if ($copied) {
        @rename($stagingDb, $tmpDb); // atomic replace — never briefly world-readable
    }
}
$readPath = file_exists($tmpDb) ? $tmpDb : $dbPath;

$db = null; $error = null;
try {
    $db = new PDO("sqlite:{$readPath}", '', '', [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    ]);
} catch (Exception $e) { $error = $e->getMessage(); }

$stats = ['total'=>0,'today'=>0,'avg_latency'=>0,'thumbs_up'=>0,'thumbs_down'=>0,'models'=>[]];
$todayRows = []; $recentRows = []; $feedbackRows = []; $topQueries = [];

if ($db) {
    try {
        $today = date('Y-m-d');

        $r = $db->query("SELECT COUNT(*) as t, ROUND(AVG(latency_ms)) as a FROM ask_logs WHERE ok=1")->fetch();
        $stats['total'] = (int)($r['t']??0);
        $stats['avg_latency'] = (int)($r['a']??0);

        $stmt = $db->prepare("SELECT COUNT(*) as c FROM ask_logs WHERE ts LIKE ?");
        $stmt->execute([$today . '%']);
        $stats['today'] = (int)($stmt->fetch()['c'] ?? 0);

        foreach ($db->query("SELECT verdict, COUNT(*) as c FROM feedback GROUP BY verdict")->fetchAll() as $r) {
            if ($r['verdict']==='up')   $stats['thumbs_up']   = (int)$r['c'];
            if ($r['verdict']==='down') $stats['thumbs_down'] = (int)$r['c'];
        }

        $stats['models'] = $db->query("SELECT model, COUNT(*) as c FROM ask_logs GROUP BY model ORDER BY c DESC LIMIT 5")->fetchAll();

        $stmt = $db->prepare("SELECT id,ts,query,answer,latency_ms,model,scope,ok,COALESCE(topk_json,'[]') as topk_json FROM ask_logs WHERE ts LIKE ? ORDER BY ts DESC LIMIT 100");
        $stmt->execute([$today . '%']);
        $todayRows = $stmt->fetchAll();

        $stmt = $db->prepare("SELECT id,ts,query,answer,latency_ms,model,scope,ok,COALESCE(topk_json,'[]') as topk_json FROM ask_logs WHERE ts NOT LIKE ? ORDER BY ts DESC LIMIT 200");
        $stmt->execute([$today . '%']);
        $recentRows = $stmt->fetchAll();

        $feedbackRows = $db->query("SELECT id,ts,verdict,query,note,model,scope FROM feedback ORDER BY ts DESC LIMIT 50")->fetchAll();

        $topQueries = $db->query("SELECT normalized,COUNT(*) as hits,ROUND(AVG(latency_ms)) as avg_ms FROM ask_logs WHERE normalized!='' AND ok=1 GROUP BY normalized ORDER BY hits DESC LIMIT 10")->fetchAll();

    } catch (Exception $e) { $error = $e->getMessage(); }
}

function fmtLatency(int $ms): string {
    return $ms < 1000 ? "{$ms}ms" : round($ms/1000,1)."s";
}
function fmtTs(string $ts): string {
    try { return (new DateTime($ts))->format('d M H:i:s'); } catch(Exception $e) { return $ts; }
}
function trunc(string $s, int $n=80): string {
    $s = htmlspecialchars($s, ENT_QUOTES, 'UTF-8');
    return strlen($s)<=$n ? $s : substr($s,0,$n).'…';
}
function sources(string $j): string {
    $items = json_decode($j,true) ?: [];
    if (!$items) return '<span style="color:#4f6459">—</span>';
    $out=[];
    foreach(array_slice($items,0,3) as $s)
        $out[] = "<code class='src'>".htmlspecialchars($s['id']??'')."</code>";
    return implode(' ',$out);
}

$totalFb = $stats['thumbs_up']+$stats['thumbs_down'];
$satPct  = $totalFb > 0 ? round($stats['thumbs_up']/$totalFb*100) : null;
?>
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8"><meta name="viewport" content="width=device-width,initial-scale=1">
<title>Dashboard — TPS Assistant</title>
<meta name="robots" content="noindex,nofollow">
<link rel="preconnect" href="https://fonts.googleapis.com">
<link rel="preconnect" href="https://fonts.gstatic.com" crossorigin>
<link href="https://fonts.googleapis.com/css2?family=DM+Serif+Display:ital@0;1&family=DM+Sans:wght@300;400;500;600&family=JetBrains+Mono:wght@400;500&display=swap" rel="stylesheet">
<link href="https://cdn.jsdelivr.net/npm/bootstrap-icons@1.11.3/font/bootstrap-icons.css" rel="stylesheet">
<style>
:root{
  --bg:#0b0f0e;--bg1:#111614;--bg2:#181e1b;--bgc:#141a17;
  --br:rgba(255,255,255,.07);--brh:rgba(255,255,255,.13);
  --acc:#2ddc8a;--acd:rgba(45,220,138,.10);--acg:rgba(45,220,138,.20);
  --dan:#f08080;--wrn:#f0c060;
  --t1:#eaf0ec;--t2:#8fa899;--t3:#4f6459;
  --ser:'DM Serif Display',Georgia,serif;
  --san:'DM Sans',system-ui,sans-serif;
  --mon:'JetBrains Mono',ui-monospace,monospace;
}
*,*::before,*::after{box-sizing:border-box;margin:0;padding:0}
html{scroll-behavior:smooth}
body{font-family:var(--san);background:var(--bg);color:var(--t1);font-size:13.5px;line-height:1.6;-webkit-font-smoothing:antialiased}
::selection{background:var(--acc);color:#0b0f0e}

/* Nav */
.nav{background:rgba(11,15,14,.96);backdrop-filter:blur(12px);border-bottom:1px solid var(--br);
  position:sticky;top:0;z-index:50;padding:0 24px;height:50px;
  display:flex;align-items:center;justify-content:space-between}
.nav-brand{display:flex;align-items:center;gap:9px;font-size:.83rem;font-weight:500}
.live-dot{width:7px;height:7px;border-radius:50%;background:var(--acc);
  box-shadow:0 0 6px var(--acg);animation:pulse 2s ease-in-out infinite;flex-shrink:0}
@keyframes pulse{0%,100%{opacity:1}50%{opacity:.4}}
.nav-right{display:flex;align-items:center;gap:14px}
.nav-meta{font-size:.7rem;color:var(--t3)}
.btn-r{background:var(--acd);border:1px solid rgba(45,220,138,.2);color:var(--acc);
  border-radius:6px;padding:4px 12px;font-family:var(--san);font-size:.75rem;
  cursor:pointer;transition:all .15s;display:inline-flex;align-items:center;gap:5px}
.btn-r:hover{background:var(--acc);color:#0b0f0e}

/* Wrap */
.wrap{max-width:1440px;margin:0 auto;padding:24px 20px 80px}

/* Stat grid */
.stat-grid{display:grid;grid-template-columns:repeat(auto-fit,minmax(150px,1fr));gap:12px;margin-bottom:28px}
.sc{background:var(--bgc);border:1px solid var(--br);border-radius:12px;padding:16px 18px;transition:border-color .15s}
.sc:hover{border-color:var(--brh)}
.sl{font-size:.63rem;font-weight:600;letter-spacing:.1em;text-transform:uppercase;color:var(--t3);
  margin-bottom:7px;display:flex;align-items:center;gap:5px}
.sv{font-family:var(--ser);font-size:2rem;line-height:1;color:var(--t1)}
.sv.a{color:var(--acc)}.sv.w{color:var(--wrn)}.sv.sm{font-size:.88rem;font-family:var(--mon);margin-top:3px}
.ss{font-size:.68rem;color:var(--t3);margin-top:5px}

/* Section head */
.sh{display:flex;align-items:center;justify-content:space-between;
  margin-bottom:12px;padding-bottom:11px;border-bottom:1px solid var(--br)}
.sh h2{font-family:var(--ser);font-size:1.1rem;font-weight:400;display:flex;align-items:center;gap:8px}
.sh h2 i{font-size:.9rem;color:var(--acc)}
.bdg{background:var(--acd);border:1px solid rgba(45,220,138,.2);color:var(--acc);
  border-radius:999px;padding:2px 8px;font-size:.65rem;font-weight:500}
.bdg.w{background:rgba(240,192,96,.1);border-color:rgba(240,192,96,.2);color:var(--wrn)}
.bdg.d{background:rgba(240,128,128,.1);border-color:rgba(240,128,128,.2);color:var(--dan)}

/* Table */
.tw{overflow-x:auto;border-radius:12px;border:1px solid var(--br);margin-bottom:32px}
table{width:100%;border-collapse:collapse}
thead tr{background:var(--bg1)}
thead th{padding:9px 13px;text-align:left;font-size:.63rem;font-weight:600;
  letter-spacing:.09em;text-transform:uppercase;color:var(--t3);
  white-space:nowrap;border-bottom:1px solid var(--br)}
tbody tr{border-bottom:1px solid var(--br);transition:background .12s}
tbody tr:last-child{border-bottom:none}
tbody tr:hover{background:var(--bg1)}
td{padding:9px 13px;vertical-align:top}
.ts{font-family:var(--mon);font-size:.68rem;color:var(--t3);white-space:nowrap}
.qcell{font-size:.83rem;color:var(--t1);max-width:400px}
.lat{font-family:var(--mon);font-size:.75rem;white-space:nowrap}
.lf{color:var(--acc)}.lm{color:var(--wrn)}.ls{color:var(--dan)}
.mdl{font-family:var(--mon);font-size:.65rem;color:var(--t3);white-space:nowrap;max-width:160px;overflow:hidden;text-overflow:ellipsis}
.spill{display:inline-block;padding:1px 6px;border-radius:999px;
  font-size:.63rem;background:var(--bg2);border:1px solid var(--br);color:var(--t3)}
code.src{font-size:.65rem;color:var(--acc);background:var(--bg2);
  padding:1px 5px;border-radius:3px;font-family:var(--mon)}
.empty td{text-align:center;padding:28px;color:var(--t3);font-size:.83rem}
.fb-up   td:first-child{border-left:3px solid var(--acc)}
.fb-down td:first-child{border-left:3px solid var(--dan)}
.vu{color:var(--acc);font-weight:500}.vd{color:var(--dan);font-weight:500}

/* Top queries */
.tqg{display:grid;gap:7px;margin-bottom:32px}
.tqr{display:flex;align-items:center;gap:12px;background:var(--bgc);
  border:1px solid var(--br);border-radius:8px;padding:9px 14px}
.tqh{font-family:var(--mon);font-size:.88rem;font-weight:500;color:var(--acc);min-width:26px;text-align:right;flex-shrink:0}
.tqt{font-size:.83rem;color:var(--t2);flex:1}
.tqa{font-family:var(--mon);font-size:.68rem;color:var(--t3);flex-shrink:0}

/* Error */
.err{background:rgba(240,128,128,.08);border:1px solid rgba(240,128,128,.2);
  border-radius:8px;padding:12px 16px;color:var(--dan);font-size:.83rem;margin-bottom:20px}

::-webkit-scrollbar{width:4px;height:4px}
::-webkit-scrollbar-track{background:transparent}
::-webkit-scrollbar-thumb{background:var(--brh);border-radius:2px}
</style>
</head>
<body>

<nav class="nav">
  <div class="nav-brand">
    <span class="live-dot"></span>
    TPS Assistant &mdash; Query Dashboard
  </div>
  <div class="nav-right">
    <span class="nav-meta" id="navMeta">
      <?= htmlspecialchars(basename($dbPath)) ?> &nbsp;·&nbsp; <?= date('d M Y, H:i') ?> UTC
    </span>
    <button class="btn-r" onclick="location.reload()"><i class="bi bi-arrow-clockwise"></i> Refresh</button>
  </div>
</nav>

<div class="wrap">

<?php if ($error): ?>
<div class="err"><i class="bi bi-exclamation-circle"></i> DB error: <?= htmlspecialchars($error) ?></div>
<?php endif; ?>

<!-- Stats -->
<div class="stat-grid">
  <div class="sc">
    <div class="sl"><i class="bi bi-chat-dots"></i> Total queries</div>
    <div class="sv"><?= number_format($stats['total']) ?></div>
    <div class="ss">all time</div>
  </div>
  <div class="sc">
    <div class="sl"><i class="bi bi-calendar-day"></i> Today</div>
    <div class="sv a"><?= number_format($stats['today']) ?></div>
    <div class="ss"><?= date('d M Y') ?></div>
  </div>
  <div class="sc">
    <div class="sl"><i class="bi bi-speedometer2"></i> Avg latency</div>
    <div class="sv <?= $stats['avg_latency']>15000?'w':'' ?>"><?= fmtLatency($stats['avg_latency']) ?></div>
    <div class="ss">all successful queries</div>
  </div>
  <div class="sc">
    <div class="sl"><i class="bi bi-hand-thumbs-up"></i> Helpful</div>
    <div class="sv a"><?= $stats['thumbs_up'] ?></div>
    <div class="ss"><?= $satPct!==null ? "{$satPct}% satisfaction" : 'no feedback yet' ?></div>
  </div>
  <div class="sc">
    <div class="sl"><i class="bi bi-hand-thumbs-down"></i> Not helpful</div>
    <div class="sv <?= $stats['thumbs_down']>0?'w':'' ?>"><?= $stats['thumbs_down'] ?></div>
    <div class="ss">needs improvement</div>
  </div>
  <?php if (!empty($stats['models'])): $m=$stats['models'][0]; ?>
  <div class="sc">
    <div class="sl"><i class="bi bi-cpu"></i> Primary model</div>
    <div class="sv sm"><?= htmlspecialchars($m['model']) ?></div>
    <div class="ss"><?= number_format((int)$m['c']) ?> queries</div>
  </div>
  <?php endif; ?>
</div>

<!-- Top queries -->
<div class="sh"><h2><i class="bi bi-fire"></i> Most repeated queries</h2><span class="bdg">top 10</span></div>
<?php if (empty($topQueries)): ?>
<p style="color:var(--t3);margin-bottom:28px;">No data yet.</p>
<?php else: ?>
<div class="tqg">
<?php foreach ($topQueries as $q): ?>
<div class="tqr">
  <span class="tqh"><?= (int)$q['hits'] ?>×</span>
  <span class="tqt"><?= trunc($q['normalized'],130) ?></span>
  <span class="tqa"><?= fmtLatency((int)$q['avg_ms']) ?></span>
</div>
<?php endforeach; ?>
</div>
<?php endif; ?>

<!-- Today -->
<div class="sh">
  <h2><i class="bi bi-calendar-day"></i> Today's queries</h2>
  <span class="bdg"><?= count($todayRows) ?> rows</span>
</div>
<div class="tw">
<table>
  <thead><tr><th>Time</th><th>Query</th><th>Latency</th><th>Scope</th><th>Top sources</th><th>Model</th></tr></thead>
  <tbody>
  <?php if (empty($todayRows)): ?>
  <tr class="empty"><td colspan="6"><i class="bi bi-moon"></i> No queries yet today</td></tr>
  <?php else: foreach ($todayRows as $row):
    $ms=(int)$row['latency_ms'];
    $lc=$ms<5000?'lf':($ms<15000?'lm':'ls');
  ?>
  <tr>
    <td class="ts"><?= fmtTs($row['ts']) ?></td>
    <td class="qcell">
      <?= trunc($row['query']??'', 100) ?>
      <?php if (!empty($row['answer'])): ?>
      <details style="margin-top:5px;">
        <summary style="font-size:0.72rem;color:var(--t3);cursor:pointer;">Show answer</summary>
        <div style="font-size:0.78rem;color:var(--t2);margin-top:6px;white-space:pre-wrap;max-height:200px;overflow-y:auto;padding:8px;background:var(--bg2);border-radius:6px;border:1px solid var(--br);"><?= htmlspecialchars($row['answer']??'') ?></div>
      </details>
      <?php endif; ?>
    </td>
    <td class="lat <?= $lc ?>"><?= fmtLatency($ms) ?></td>
    <td><span class="spill"><?= htmlspecialchars($row['scope']??'—') ?></span></td>
    <td><?= sources($row['topk_json']) ?></td>
    <td class="mdl"><?= htmlspecialchars($row['model']??'—') ?></td>
  </tr>
  <?php endforeach; endif; ?>
  </tbody>
</table>
</div>

<!-- Feedback -->
<?php if (!empty($feedbackRows)): ?>
<div class="sh">
  <h2><i class="bi bi-chat-square-heart"></i> Recent feedback</h2>
  <span class="bdg <?= $stats['thumbs_down']>$stats['thumbs_up']?'d':'' ?>"><?= count($feedbackRows) ?> rows</span>
</div>
<div class="tw">
<table>
  <thead><tr><th>Time</th><th>Verdict</th><th>Query</th><th>Note</th><th>Model</th><th>Scope</th></tr></thead>
  <tbody>
  <?php foreach ($feedbackRows as $row): ?>
  <tr class="fb-<?= htmlspecialchars($row['verdict']) ?>">
    <td class="ts"><?= fmtTs($row['ts']) ?></td>
    <td class="<?= $row['verdict']==='up'?'vu':'vd' ?>">
      <?= $row['verdict']==='up' ? '<i class="bi bi-hand-thumbs-up-fill"></i> Helpful' : '<i class="bi bi-hand-thumbs-down-fill"></i> Not helpful' ?>
    </td>
    <td class="qcell">
      <?= trunc($row['query']??'', 100) ?>
      <?php if (!empty($row['answer'])): ?>
      <details style="margin-top:5px;">
        <summary style="font-size:0.72rem;color:var(--t3);cursor:pointer;">Show answer</summary>
        <div style="font-size:0.78rem;color:var(--t2);margin-top:6px;white-space:pre-wrap;max-height:200px;overflow-y:auto;padding:8px;background:var(--bg2);border-radius:6px;border:1px solid var(--br);"><?= htmlspecialchars($row['answer']??'') ?></div>
      </details>
      <?php endif; ?>
    </td>
    <td style="font-size:.8rem;color:var(--wrn);max-width:180px"><?= trunc($row['note']??'',80) ?></td>
    <td class="mdl"><?= htmlspecialchars($row['model']??'—') ?></td>
    <td><span class="spill"><?= htmlspecialchars($row['scope']??'—') ?></span></td>
  </tr>
  <?php endforeach; ?>
  </tbody>
</table>
</div>
<?php endif; ?>

<!-- Previous -->
<div class="sh">
  <h2><i class="bi bi-clock-history"></i> Previous queries</h2>
  <span class="bdg"><?= count($recentRows) ?> rows</span>
</div>
<div class="tw">
<table>
  <thead><tr><th>Date / Time</th><th>Query</th><th>Latency</th><th>Scope</th><th>Top sources</th><th>Model</th></tr></thead>
  <tbody>
  <?php if (empty($recentRows)): ?>
  <tr class="empty"><td colspan="6">No previous queries</td></tr>
  <?php else: foreach ($recentRows as $row):
    $ms=(int)$row['latency_ms'];
    $lc=$ms<5000?'lf':($ms<15000?'lm':'ls');
  ?>
  <tr>
    <td class="ts"><?= fmtTs($row['ts']) ?></td>
    <td class="qcell">
      <?= trunc($row['query']??'', 100) ?>
      <?php if (!empty($row['answer'])): ?>
      <details style="margin-top:5px;">
        <summary style="font-size:0.72rem;color:var(--t3);cursor:pointer;">Show answer</summary>
        <div style="font-size:0.78rem;color:var(--t2);margin-top:6px;white-space:pre-wrap;max-height:200px;overflow-y:auto;padding:8px;background:var(--bg2);border-radius:6px;border:1px solid var(--br);"><?= htmlspecialchars($row['answer']??'') ?></div>
      </details>
      <?php endif; ?>
    </td>
    <td class="lat <?= $lc ?>"><?= fmtLatency($ms) ?></td>
    <td><span class="spill"><?= htmlspecialchars($row['scope']??'—') ?></span></td>
    <td><?= sources($row['topk_json']) ?></td>
    <td class="mdl"><?= htmlspecialchars($row['model']??'—') ?></td>
  </tr>
  <?php endforeach; endif; ?>
  </tbody>
</table>
</div>

</div>
<script>
let secs=60;
const el=document.getElementById('navMeta');
const base=el.textContent.split('·')[0].trim();
setInterval(()=>{ secs--; if(secs>0) el.textContent=base+' · refresh in '+secs+'s'; },1000);
setTimeout(()=>location.reload(),60000);
</script>
</body>
</html>