gmail-hook/apps/watcher/test/audit.test.js

import assert from "node:assert/strict";
import { describe, it } from "node:test";
import Database from "better-sqlite3";

/**
 * Exercise audit_log insert/list against an in-memory schema that mirrors
 * apps/watcher/src/db.js (without opening the live app.db).
 */
function openAuditDb() {
  const db = new Database(":memory:");
  db.pragma("foreign_keys = ON");
  db.exec(`
    CREATE TABLE users (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      google_sub TEXT NOT NULL UNIQUE,
      email TEXT NOT NULL,
      name TEXT,
      picture TEXT,
      is_default INTEGER NOT NULL DEFAULT 0,
      created_at TEXT NOT NULL DEFAULT (datetime('now'))
    );
    CREATE TABLE audit_log (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      user_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
      mailbox TEXT NOT NULL,
      actor TEXT NOT NULL,
      action TEXT NOT NULL,
      reason TEXT,
      message_ids TEXT,
      payload TEXT,
      status TEXT NOT NULL,
      error TEXT
    );
  `);
  return db;
}

describe("audit_log schema behavior", () => {
  it("inserts and lists with reason + status", () => {
    const db = openAuditDb();
    const { lastInsertRowid: userId } = db
      .prepare(
        `INSERT INTO users (google_sub, email, is_default) VALUES ('sub1', 'a@example.com', 1)`,
      )
      .run();

    const insert = db.prepare(`
      INSERT INTO audit_log (
        user_id, mailbox, actor, action, reason, message_ids, payload, status, error
      ) VALUES (?, ?, 'cli', ?, ?, ?, ?, ?, ?)
    `);
    insert.run(
      userId,
      "a@example.com",
      "archive",
      "newsletter noise",
      JSON.stringify(["msg1"]),
      JSON.stringify({ removeLabelIds: ["INBOX"] }),
      "ok",
      null,
    );
    insert.run(
      userId,
      "a@example.com",
      "archive",
      "preview",
      JSON.stringify(["msg2"]),
      JSON.stringify({ dryRun: true }),
      "dry-run",
      null,
    );

    const rows = db
      .prepare(
        `SELECT * FROM audit_log WHERE action = ? ORDER BY id DESC LIMIT 10`,
      )
      .all("archive");
    assert.equal(rows.length, 2);
    assert.equal(rows[0].status, "dry-run");
    assert.equal(rows[1].reason, "newsletter noise");
    assert.deepEqual(JSON.parse(rows[1].message_ids), ["msg1"]);
  });

  it("setDefault clears other defaults", () => {
    const db = openAuditDb();
    db.prepare(
      `INSERT INTO users (google_sub, email, is_default) VALUES ('s1', 'a@example.com', 1)`,
    ).run();
    db.prepare(
      `INSERT INTO users (google_sub, email, is_default) VALUES ('s2', 'b@example.com', 0)`,
    ).run();

    const clearAndSet = db.transaction((id) => {
      db.prepare(`UPDATE users SET is_default = 0`).run();
      db.prepare(`UPDATE users SET is_default = 1 WHERE id = ?`).run(id);
    });
    const b = db
      .prepare(`SELECT id FROM users WHERE email = 'b@example.com'`)
      .get();
    clearAndSet(b.id);

    const defaults = db
      .prepare(`SELECT email FROM users WHERE is_default = 1`)
      .all();
    assert.deepEqual(
      defaults.map((r) => r.email),
      ["b@example.com"],
    );
  });
});