Webklar-Kundenbereich/analytics/test/ingest.test.js

import { test } from 'node:test';
import assert from 'node:assert/strict';
import { openDb } from '../src/db.js';
import net from 'node:net';
import { createPublicServer, createAdminServer } from '../src/server.js';
import { classifyUa } from '../src/ua.js';
import { createSalt } from '../src/salt.js';

const KEY = 'abcdef0123456789abcd';
const HOST = 'abc.project.webklar.com';
const ORIGIN = `https://${HOST}`;
const IP = '203.0.113.77';
const UA = 'Mozilla/5.0 (iPhone; CPU iPhone OS 17_0 like Mac OS X) Mobile/15E148 Safari/604.1 GEHEIMERUA';

async function setup({ now = () => Date.UTC(2026, 9, 1, 10, 30, 45), geo = { lookup: () => 'DE' } } = {}) {
  const db = openDb(':memory:');
  db.prepare('INSERT INTO sites (siteKey, projectId, domains, active) VALUES (?,?,?,1)')
    .run(KEY, 'p1', JSON.stringify([HOST, 'www.kunde.de']));
  const server = createPublicServer({ config: {}, db, geo, now });
  const invalidate = () => server.invalidateSites();
  await new Promise((r) => server.listen(0, '127.0.0.1', r));
  const base = `http://127.0.0.1:${server.address().port}`;
  const post = (ev, { origin = ORIGIN, ua = UA, ip = IP, left = '1.2.3.4', referer, raw, type = 'text/plain' } = {}) => {
    const headers = { 'Content-Type': type, 'User-Agent': ua, 'X-Forwarded-For': `${left}, ${ip}` };
    if (origin) headers.Origin = origin;
    if (referer) headers.Referer = referer;
    return fetch(base + '/a/e', { method: 'POST', headers, body: raw ?? JSON.stringify(ev) });
  };
  const rows = () => db.prepare('SELECT * FROM events_raw ORDER BY id').all();
  return { db, post, rows, base, server, invalidate, close: () => server.close() };
}
const pv = (o = {}) => ({ t: 'pv', site: KEY, path: '/', vp: 'd', ...o });

test('classifyUa: Bots, Geräte, leer', () => {
  assert.equal(classifyUa('').bot, true);
  assert.equal(classifyUa('Googlebot/2.1').bot, true);
  assert.equal(classifyUa('curl/8.0').bot, true);
  assert.equal(classifyUa('Mozilla/5.0 (Windows NT 10.0) Chrome/120').device, 'd');
  assert.equal(classifyUa('Mozilla/5.0 (iPhone; CPU) Mobile/15E').device, 'm');
  assert.equal(classifyUa('Mozilla/5.0 (Linux; Android 13; Pixel) Mobile Safari').device, 'm');
  assert.equal(classifyUa('Mozilla/5.0 (iPad; CPU OS 17)').device, 't');
  assert.equal(classifyUa('Mozilla/5.0 (Linux; Android 13; SM-T500) Safari').device, 't');
  assert.equal(classifyUa('Mozilla/5.0 (X11; Linux) Firefox/120').bot, false);
});

test('Salz: stabil am Tag, wechselt um Mitternacht Berlin', () => {
  let t = Date.UTC(2026, 9, 1, 10, 0, 0);
  let n = 0;
  const s = createSalt({ now: () => t, random: () => Buffer.alloc(32, ++n) });
  const a = s.visitorId('s', '1.1.1.1', 'ua');
  assert.match(a, /^[0-9a-f]{16}$/);
  t = Date.UTC(2026, 9, 1, 21, 59, 0); // 23:59 Berlin (MESZ)
  assert.equal(s.visitorId('s', '1.1.1.1', 'ua'), a);
  assert.notEqual(s.visitorId('s', '1.1.1.2', 'ua'), a);
  t = Date.UTC(2026, 9, 1, 22, 0, 1); // 00:00 Berlin
  assert.notEqual(s.visitorId('s', '1.1.1.1', 'ua'), a);
  assert.match(s.rateKey('s', '1.1.1.1'), /^[0-9a-f]{64}$/);
  assert.ok(!s.rateKey('s', '1.1.1.1').includes('1.1.1.1'));
});

test('pv: gespeichert ohne visitor, mit Land und Gerät, Cookie-frei', async () => {
  const t = await setup();
  const res = await t.post(pv({ path: '/preise?x=1#a', ref: 'https://Google.com/search?q=1' }));
  assert.equal(res.status, 204);
  assert.equal(res.headers.get('set-cookie'), null);
  const [r] = t.rows();
  assert.equal(r.type, 'pv');
  assert.equal(r.site, KEY);
  assert.equal(r.path, '/preise');
  assert.equal(r.ref, 'google.com');
  assert.equal(r.vp, 'd');
  assert.equal(r.country, 'DE');
  assert.equal(r.device, 'm');
  assert.equal(r.visitor, null);
  assert.equal(res.headers.get('access-control-allow-origin'), ORIGIN);
  assert.equal(res.headers.get('access-control-allow-credentials'), null);
  t.close();
});

test('pv: ref eigene Domain wird verworfen, ungültige Felder', async () => {
  const t = await setup();
  await t.post(pv({ ref: 'https://www.kunde.de/x' }));
  await t.post(pv({ ref: 'kein host!' }));
  assert.equal(t.rows()[0].ref, null);
  assert.equal(t.rows()[1].ref, null);
  await t.post(pv({ path: 'kein-slash' }));
  await t.post(pv({ path: '/' + 'a'.repeat(300) }));
  await t.post(pv({ path: '/a\u0001b' }));
  await t.post(pv({ vp: 'x' }));
  await t.post({ t: 'zz', site: KEY, path: '/', vp: 'd' });
  await t.post({ site: KEY, path: '/', vp: 'd' });
  await t.post(null, { raw: 'kein json' });
  await t.post(null, { raw: '[1]' });
  assert.equal(t.rows().length, 2);
  t.close();
});

test('Antwort immer 204; 413 bei großem Body; 405 bei falscher Methode', async () => {
  const t = await setup();
  assert.equal((await t.post(null, { raw: 'kein json' })).status, 204);
  assert.equal((await t.post(pv({ pad: 'x'.repeat(5000) }))).status, 413);
  assert.equal(t.rows().length, 0);
  assert.equal((await fetch(t.base + '/a/e')).status, 405);
  assert.equal((await fetch(t.base + '/a/e', { method: 'PUT', body: '{}' })).status, 405);
  t.close();
});

test('application/json wird ebenfalls angenommen; unbekannte Felder werden nicht gespeichert', async () => {
  const t = await setup();
  const res = await t.post(pv({ evil: 'GEHEIM', ip: '9.9.9.9' }), { type: 'application/json' });
  assert.equal(res.status, 204);
  assert.equal(t.rows().length, 1);
  assert.ok(!JSON.stringify(t.db.prepare('SELECT * FROM events_raw').all()).includes('GEHEIM'));
  t.close();
});

test('Origin: fremd, fehlend, Referer-Fallback, inaktiv, unbekannte Site', async () => {
  const t = await setup();
  assert.equal((await t.post(pv(), { origin: 'https://evil.example' })).headers.get('access-control-allow-origin'), null);
  await t.post(pv(), { origin: null });
  assert.equal(t.rows().length, 0);
  await t.post(pv(), { origin: null, referer: `${ORIGIN}/seite` });
  assert.equal(t.rows().length, 1);
  await t.post(pv(), { origin: 'https://www.kunde.de' });
  assert.equal(t.rows().length, 2);
  await t.post(pv({ site: 'zzzzzzzzzzzzzzzzzzzz' }));
  assert.equal(t.rows().length, 2);
  t.db.prepare('UPDATE sites SET active = 0').run();
  await t.post(pv());
  assert.equal(t.rows().length, 2);
  t.close();
});

test('Bot und leerer User-Agent: nichts gespeichert', async () => {
  const t = await setup();
  await t.post(pv(), { ua: 'Googlebot/2.1' });
  await t.post(pv(), { ua: '' });
  assert.equal(t.rows().length, 0);
  t.close();
});

test('bn: nur site, ts (Stunde), type, kind', async () => {
  const t = await setup();
  await t.post({ t: 'bn', site: KEY, a: 'shown', path: '/x', vp: 'm', visitor: 'v' });
  await t.post({ t: 'bn', site: KEY, a: 'accept' });
  await t.post({ t: 'bn', site: KEY, a: 'foo' });
  const rows = t.rows();
  assert.equal(rows.length, 2);
  for (const r of rows) {
    assert.equal(r.type, 'bn');
    for (const f of ['path', 'vp', 'country', 'device', 'visitor', 'ref', 'sec', 'scroll', 'host']) assert.equal(r[f], null, f);
    assert.equal(r.ts, Date.UTC(2026, 9, 1, 10, 0, 0));
  }
  assert.deepEqual(rows.map((r) => r.kind), ['shown', 'accept']);
  t.close();
});

test('en: sec/scroll begrenzt, mit visitor', async () => {
  const t = await setup();
  await t.post({ t: 'en', site: KEY, path: '/a?b', vp: 't', sec: 99999, scroll: 250 });
  await t.post({ t: 'en', site: KEY, path: '/a', vp: 't', sec: 12.7, scroll: -5 });
  await t.post({ t: 'en', site: KEY, path: '/a', vp: 't', sec: 'x', scroll: 1 });
  const rows = t.rows();
  assert.equal(rows.length, 2);
  assert.match(rows[0].visitor, /^[0-9a-f]{16}$/);
  assert.equal(rows[0].path, '/a');
  assert.equal(rows[0].sec, 3600);
  assert.equal(rows[0].scroll, 100);
  assert.equal(rows[1].sec, 12);
  assert.equal(rows[1].scroll, 0);
  t.close();
});

test('ck: in clicks, geklemmt, ohne visitor', async () => {
  const t = await setup();
  await t.post({ t: 'ck', site: KEY, path: '/a#h', vp: 'd', x: 0.123456, y: 1.7, sel: 'div>a'.repeat(100) });
  await t.post({ t: 'ck', site: KEY, path: '/a', vp: 'd', x: 'n', y: 0.5 });
  assert.equal(t.rows().length, 0);
  const c = t.db.prepare('SELECT * FROM clicks').all();
  assert.equal(c.length, 1);
  assert.equal(c[0].path, '/a');
  assert.equal(c[0].x, 0.123);
  assert.equal(c[0].y, 1);
  assert.equal(c[0].sel.length, 200);
  assert.deepEqual(Object.keys(c[0]).sort(), ['id', 'path', 'sel', 'site', 'ts', 'vp', 'x', 'y']);
  t.close();
});

test('ct: kind und host', async () => {
  const t = await setup();
  await t.post({ t: 'ct', site: KEY, path: '/k', kind: 'tel', host: 'ignored.de' });
  await t.post({ t: 'ct', site: KEY, path: '/k', kind: 'out', host: 'Instagram.COM' });
  await t.post({ t: 'ct', site: KEY, path: '/k', kind: 'out' });
  await t.post({ t: 'ct', site: KEY, path: '/k', kind: 'sms' });
  const rows = t.rows();
  assert.equal(rows.length, 2);
  assert.equal(rows[0].kind, 'tel');
  assert.equal(rows[0].host, null);
  assert.equal(rows[1].host, 'instagram.com');
  assert.match(rows[1].visitor, /^[0-9a-f]{16}$/);
  t.close();
});

test('Mengenlimit: 121. Ereignis verworfen, neue Minute frei', async () => {
  let now = Date.UTC(2026, 9, 1, 10, 30, 0);
  const t = await setup({ now: () => now });
  for (let i = 0; i < 125; i++) await t.post(pv());
  assert.equal(t.rows().length, 120);
  await t.post(pv(), { ip: '198.51.100.9' });
  assert.equal(t.rows().length, 121);
  now += 61000;
  await t.post(pv());
  assert.equal(t.rows().length, 122);
  t.close();
});

test('Land: nur 2 Großbuchstaben, sonst leer', async () => {
  const t = await setup({ geo: { lookup: () => 'xyz' } });
  await t.post(pv());
  assert.equal(t.rows()[0].country, '');
  t.close();
  const u = await setup({ geo: { lookup: () => 'at' } });
  await u.post(pv());
  assert.equal(u.rows()[0].country, 'AT');
  u.close();
});

test('CORS-Preflight nur für erlaubte Origin', async () => {
  const t = await setup();
  const ok = await fetch(t.base + '/a/e', { method: 'OPTIONS', headers: { Origin: ORIGIN, 'Access-Control-Request-Method': 'POST' } });
  assert.equal(ok.status, 204);
  assert.equal(ok.headers.get('access-control-allow-origin'), ORIGIN);
  assert.equal(ok.headers.get('access-control-allow-credentials'), null);
  const bad = await fetch(t.base + '/a/e', { method: 'OPTIONS', headers: { Origin: 'https://evil.example' } });
  assert.equal(bad.status, 204);
  assert.equal(bad.headers.get('access-control-allow-origin'), null);
  t.close();
});

test('Datenschutz: weder IP noch User-Agent irgendwo in der Datenbank', async () => {
  const t = await setup();
  await t.post(pv({ ref: 'https://google.com' }));
  await t.post({ t: 'bn', site: KEY, a: 'accept' });
  await t.post({ t: 'en', site: KEY, path: '/a', vp: 'd', sec: 5, scroll: 50 });
  await t.post({ t: 'ck', site: KEY, path: '/a', vp: 'd', x: 0.5, y: 0.5, sel: 'a' });
  await t.post({ t: 'ct', site: KEY, path: '/a', kind: 'mail' });
  let dump = '';
  for (const { name } of t.db.prepare("SELECT name FROM sqlite_master WHERE type='table'").all()) {
    dump += JSON.stringify(t.db.prepare(`SELECT * FROM ${name}`).all());
  }
  for (const needle of [IP, '1.2.3.4', 'GEHEIMERUA', 'Mozilla', 'iPhone']) assert.ok(!dump.includes(needle), needle);
  t.close();
});

test('t: nur eigene Feldlisten, kein Prototyp-Zugriff, keine Logzeile', async () => {
  const t = await setup();
  const errs = [];
  const orig = console.error;
  console.error = (...a) => errs.push(a.join(' '));
  try {
    for (const tt of ['__proto__', 'constructor', 'toString', 'hasOwnProperty', 'valueOf']) {
      assert.equal((await t.post(null, { raw: JSON.stringify({ t: tt, site: KEY, path: '/', vp: 'd' }) })).status, 204);
    }
    assert.equal((await t.post(null, { raw: `{"t":"__proto__","site":"${KEY}","path":"/"}` })).status, 204);
    assert.equal((await t.post({ t: ['pv'], site: KEY, path: '/', vp: 'd' })).status, 204);
    assert.equal((await t.post({ t: { toString: 1 }, site: KEY, path: '/', vp: 'd' })).status, 204);
  } finally { console.error = orig; }
  assert.equal(t.rows().length, 0);
  assert.deepEqual(errs, []);
  t.close();
});

test('Origin/Referer: nur https ohne Port zählt', async () => {
  const t = await setup();
  for (const origin of [`http://${HOST}`, `https://${HOST}:8080`, `https://evil-${HOST}`, `https://${HOST}.evil.com`, `ftp://${HOST}`]) {
    const res = await t.post(pv(), { origin });
    assert.equal(res.headers.get('access-control-allow-origin'), null, origin);
    const pre = await fetch(t.base + '/a/e', { method: 'OPTIONS', headers: { Origin: origin } });
    assert.equal(pre.headers.get('access-control-allow-origin'), null, origin);
  }
  await t.post(pv(), { origin: null, referer: `http://${HOST}/x` });
  await t.post(pv(), { origin: null, referer: `https://${HOST}:8080/x` });
  assert.equal(t.rows().length, 0);
  await t.post(pv(), { origin: null, referer: `https://${HOST}/x` });
  assert.equal(t.rows().length, 1);
  t.close();
});

function rawRequest(port, head, body = '') {
  return new Promise((resolve) => {
    const s = net.connect(port, '127.0.0.1');
    let buf = '';
    s.on('connect', () => s.write(head + '\r\n\r\n' + body));
    s.on('data', (d) => { buf += d; });
    s.on('close', () => resolve(buf));
    s.on('error', () => resolve(buf));
    setTimeout(() => { s.destroy(); }, 1500);
  });
}

test('Body: früh 413 bei Content-Length > 4096, Abbruch bei gestreamtem Überlauf', async () => {
  const t = await setup();
  const port = t.server.address().port;
  const head = `POST /a/e HTTP/1.1\r\nHost: x\r\nOrigin: ${ORIGIN}\r\nContent-Type: text/plain`;
  // Content-Length 1 MB angekündigt, aber nichts gesendet: Antwort kommt trotzdem sofort
  const early = await rawRequest(port, `${head}\r\nContent-Length: 1048576`);
  assert.match(early, /^HTTP\/1\.1 413/);
  // chunked ohne Content-Length: über 4 KB ⇒ 413 und Verbindung wird beendet
  const chunk = 'x'.repeat(3000);
  const body = `${chunk.length.toString(16)}\r\n${chunk}\r\n`.repeat(3);
  const streamed = await rawRequest(port, `${head}\r\nTransfer-Encoding: chunked`, body);
  assert.match(streamed, /^HTTP\/1\.1 413/);
  assert.equal(t.rows().length, 0);
  t.close();
});

test('Preflight: Hostliste gecacht (30 s), invalidateSites() und Admin-Änderung wirken sofort', async () => {
  let now = Date.UTC(2026, 9, 1, 10, 0, 0);
  const t = await setup({ now: () => now });
  const pre = async () => (await fetch(t.base + '/a/e', { method: 'OPTIONS', headers: { Origin: ORIGIN } })).headers.get('access-control-allow-origin');
  assert.equal(await pre(), ORIGIN);
  t.db.prepare('UPDATE sites SET active = 0').run();
  assert.equal(await pre(), ORIGIN); // noch aus dem Cache
  now += 31000;
  assert.equal(await pre(), null); // Cache abgelaufen
  t.db.prepare('UPDATE sites SET active = 1').run();
  assert.equal(await pre(), null);
  t.invalidate();
  assert.equal(await pre(), ORIGIN);
  // Admin-Listener meldet Änderungen an den öffentlichen Listener
  const admin = createAdminServer({ config: { adminToken: 'geheim' }, db: t.db, onSitesChanged: () => t.invalidate() });
  await new Promise((r) => admin.listen(0, '127.0.0.1', r));
  const res = await fetch(`http://127.0.0.1:${admin.address().port}/a/admin/sites/${KEY}`, {
    method: 'PUT', headers: { Authorization: 'Bearer geheim', 'Content-Type': 'application/json' },
    body: JSON.stringify({ projectId: 'p1', domains: [HOST], active: false }),
  });
  assert.equal(res.status, 200);
  assert.equal(await pre(), null);
  admin.close();
  t.close();
});

test('X-Forwarded-For: rechtester Eintrag zählt für das Mengenlimit', async () => {
  const t = await setup();
  for (let i = 0; i < 120; i++) await t.post(pv(), { left: `10.0.0.${i % 250}` }); // links variiert, rechts gleich
  assert.equal(t.rows().length, 120);
  await t.post(pv(), { left: '10.9.9.9' }); // gleicher Bucket ⇒ verworfen
  assert.equal(t.rows().length, 120);
  await t.post(pv(), { left: '1.2.3.4', ip: '198.51.100.9' }); // rechts anders ⇒ eigener Bucket
  assert.equal(t.rows().length, 121);
  t.close();
});

test('T2: pv ohne ref/vp wird gespeichert (vp NULL, ref NULL), Gerät und Land bleiben', async () => {
  const t = await setup();
  await t.post({ t: 'pv', site: KEY, path: '/' });
  const r = t.rows();
  assert.equal(r.length, 1);
  assert.equal(r[0].type, 'pv');
  assert.equal(r[0].vp, null);
  assert.equal(r[0].ref, null);
  assert.equal(r[0].device, 'm');
  assert.equal(r[0].country, 'DE');
  await t.post({ t: 'pv', site: KEY, path: '/', vp: 'x' });
  assert.equal(t.rows().length, 1);
  t.close();
});

test('T2: cx braucht gültiges vp, speichert ref, eigene Domain ergibt NULL', async () => {
  const t = await setup();
  await t.post({ t: 'cx', site: KEY, path: '/', ref: 'https://www.google.com/x' });
  await t.post({ t: 'cx', site: KEY, path: '/', ref: 'google.com', vp: 'x' });
  assert.equal(t.rows().length, 0);
  await t.post({ t: 'cx', site: KEY, path: '/', ref: 'https://www.google.com/x', vp: 'd' });
  await t.post({ t: 'cx', site: KEY, path: '/', ref: 'https://www.kunde.de/x', vp: 'm' });
  const r = t.rows();
  assert.equal(r.length, 2);
  assert.equal(r[0].type, 'cx');
  assert.equal(r[0].ref, 'www.google.com');
  assert.equal(r[0].vp, 'd');
  assert.equal(r[0].country, null);
  assert.equal(r[0].device, null);
  assert.equal(r[1].ref, null);
  t.close();
});

test('T3: alle Zeitstempel auf die volle Stunde', async () => {
  const t = await setup();
  const hour = Date.UTC(2026, 9, 1, 10, 0, 0);
  await t.post(pv());
  await t.post({ t: 'cx', site: KEY, path: '/', vp: 'd' });
  await t.post({ t: 'en', site: KEY, path: '/', vp: 'd', sec: 5, scroll: 10 });
  await t.post({ t: 'ct', site: KEY, path: '/', kind: 'tel' });
  await t.post({ t: 'ck', site: KEY, path: '/', vp: 'd', x: 0.5, y: 0.5 });
  const r = t.rows();
  assert.deepEqual(r.map((x) => x.type), ['pv', 'cx', 'en', 'ct']);
  for (const x of r) assert.equal(x.ts, hour);
  const c = t.db.prepare('SELECT ts FROM clicks').all();
  assert.equal(c.length, 1);
  assert.equal(c[0].ts, hour);
  assert.equal(t.db.prepare('SELECT COUNT(*) n FROM events_raw WHERE ts % 3600000 != 0').get().n, 0);
  t.close();
});

test('T14: kennungsartige Pfadsegmente werden zu :id, alle Typen', async () => {
  const t = await setup();
  const cases = [
    ['/kunde/123456789/rechnung', '/kunde/:id/rechnung'],
    ['/u/3f2b8c1e-5d4a-4e0b-9a7c-1b2c3d4e5f60', '/u/:id'],
    ['/mail/max%40example.com', '/mail/:id'],
    ['/mail/max@example.com', '/mail/:id'],
    ['/preise/2026', '/preise/2026'],
    ['/blog/12345', '/blog/12345'],
    ['/', '/'],
    ['/a/123456?x=1#h', '/a/:id'],
  ];
  for (const [p] of cases) await t.post(pv({ path: p }));
  assert.deepEqual(t.rows().map((r) => r.path), cases.map((c) => c[1]));
  await t.post({ t: 'cx', site: KEY, path: '/kunde/987654321', vp: 'd' });
  await t.post({ t: 'en', site: KEY, path: '/kunde/987654321', vp: 'd', sec: 1, scroll: 1 });
  await t.post({ t: 'ct', site: KEY, path: '/kunde/987654321', kind: 'tel' });
  await t.post({ t: 'ck', site: KEY, path: '/kunde/987654321', vp: 'd', x: 0.1, y: 0.1 });
  const paths = t.rows().slice(cases.length).map((r) => r.path);
  assert.deepEqual(paths, ['/kunde/:id', '/kunde/:id', '/kunde/:id']);
  assert.equal(t.db.prepare('SELECT path FROM clicks').get().path, '/kunde/:id');
  t.close();
});

test('T14: ungültige Prozent-Kodierung fällt auf den Rohwert zurück', async () => {
  const t = await setup();
  await t.post(pv({ path: '/a/%E0%A4%A/123456' }));
  await t.post(pv({ path: '/a/%E0%A4%A/x' }));
  await t.post(pv({ path: '/a/%E0%A4%A40/x' }));
  assert.deepEqual(t.rows().map((r) => r.path), ['/a/%E0%A4%A/:id', '/a/%E0%A4%A/x', '/a/%E0%A4%A40/x']);
  t.close();
});

test('T3: jede gespeicherte Zeile jedes Typs liegt auf der vollen Stunde (ungerade Uhrzeit)', async () => {
  const t = await setup({ now: () => Date.UTC(2026, 9, 1, 10, 37, 12, 345) });
  await t.post({ t: 'bn', site: KEY, a: 'shown' });
  await t.post(pv());
  await t.post({ t: 'cx', site: KEY, path: '/', vp: 'd' });
  await t.post({ t: 'en', site: KEY, path: '/', vp: 'd', sec: 5, scroll: 10 });
  await t.post({ t: 'ct', site: KEY, path: '/', kind: 'tel' });
  await t.post({ t: 'ck', site: KEY, path: '/', vp: 'd', x: 0.5, y: 0.5 });
  const r = t.rows();
  assert.deepEqual([...new Set(r.map((x) => x.type))].sort(), ['bn', 'ct', 'cx', 'en', 'pv']);
  for (const x of r) assert.equal(x.ts % 3600000, 0);
  for (const x of t.db.prepare('SELECT ts FROM clicks').all()) assert.equal(x.ts % 3600000, 0);
  t.close();
});