AgentHub/server/routes/query.js

/**
 * @file Query Explorer: `GET /api/query?entity=sessions|events|agents` filters
 * one table (free text, AI tool, status, local date range, cwd, data scope),
 * sorts it by a column from a per-entity ALLOWLIST, and answers one page as
 * JSON — or, with `format=csv|json`, the whole filtered set (capped at
 * `EXPORT_CAP` rows) as a download. No column, direction or table name is ever
 * taken from the request into SQL; every value is a bound parameter.
 * @author Nguyễn Ngọc Trí Vĩ <vinnt@smartgift.vn>
 */

const { Router } = require("express");
const { db } = require("../db");
const { parseScope, parseTool, BAD_TOOL, parseRange, BAD_RANGE } = require("../lib/source-filter");

const router = Router();

const EXPORT_CAP = 50_000;
const PAGE_MAX = 500;

/**
 * Per entity: the FROM (always joined to its session as `s`), the selected
 * columns (also the CSV header), the sortable ones mapped to SQL, the timestamp
 * a date range bounds, the free-text columns, and whether it has a status.
 */
const ENTITIES = {
  sessions: {
    from: "sessions s",
    columns: {
      id: "s.id",
      name: "s.name",
      status: "s.status",
      tool: "s.tool",
      model: "s.model",
      cwd: "s.cwd",
      started_at: "s.started_at",
      ended_at: "s.ended_at",
    },
    sort: ["started_at", "ended_at", "name", "status", "tool", "model", "cwd"],
    time: "s.started_at",
    text: ["s.id", "s.name", "s.cwd"],
    status: "s.status",
    statuses: ["active", "completed", "error", "abandoned"],
    tiebreak: "s.id",
  },
  events: {
    from: "events e JOIN sessions s ON s.id = e.session_id",
    columns: {
      id: "e.id",
      session_id: "e.session_id",
      agent_id: "e.agent_id",
      event_type: "e.event_type",
      tool_name: "e.tool_name",
      summary: "e.summary",
      tool: "s.tool",
      cwd: "s.cwd",
      created_at: "e.created_at",
    },
    sort: ["created_at", "event_type", "tool_name", "session_id", "tool"],
    time: "e.created_at",
    text: ["e.summary", "e.event_type", "e.tool_name", "e.session_id"],
    status: null,
    statuses: [],
    tiebreak: "e.id",
  },
  agents: {
    from: "agents a JOIN sessions s ON s.id = a.session_id",
    columns: {
      id: "a.id",
      session_id: "a.session_id",
      name: "a.name",
      type: "a.type",
      subagent_type: "a.subagent_type",
      status: "a.status",
      task: "a.task",
      tool: "s.tool",
      cwd: "s.cwd",
      started_at: "a.started_at",
      ended_at: "a.ended_at",
    },
    sort: ["started_at", "ended_at", "name", "type", "subagent_type", "status", "tool"],
    time: "a.started_at",
    text: ["a.id", "a.name", "a.task", "a.subagent_type"],
    status: "a.status",
    statuses: ["working", "waiting", "completed", "error"],
    tiebreak: "a.id",
  },
};

const likeEscape = (s) => s.replace(/[\\%_]/g, "\\$&");
/** Send a 400 and return null, for `parseQuery`'s early exits. */
function bad(res, message) {
  res.status(400).json({ error: { code: "INVALID_INPUT", message } });
  return null;
}

/**
 * Validate the request into `{spec, entity, where, params, order}`, or send
 * the 400 and return null.
 */
function parseQuery(req, res) {
  const q = req.query;
  const entity = q.entity === undefined ? "sessions" : q.entity;
  const spec = Object.prototype.hasOwnProperty.call(ENTITIES, entity) ? ENTITIES[entity] : null;
  if (!spec) return bad(res, "entity must be sessions, events or agents");
  if (parseTool(req) === false) return bad(res, BAD_TOOL.error.message);
  const range = parseRange(req);
  if (range === false) return bad(res, BAD_RANGE.error.message);

  const where = [];
  const params = [];
  const scope = parseScope(req);
  if (scope) {
    where.push(`s.id IN (SELECT id FROM sessions WHERE ${scope.where})`);
    params.push(...scope.params);
  }
  if (range?.start) {
    where.push(`${spec.time} >= ?`);
    params.push(range.start);
  }
  if (range?.end) {
    where.push(`${spec.time} < ?`);
    params.push(range.end);
  }
  const text = typeof q.q === "string" ? q.q.trim() : "";
  if (text) {
    const like = `%${likeEscape(text)}%`;
    where.push(`(${spec.text.map((c) => `${c} LIKE ? ESCAPE '\\'`).join(" OR ")})`);
    params.push(...spec.text.map(() => like));
  }
  if (q.status !== undefined && q.status !== "") {
    if (!spec.statuses.includes(q.status)) {
      return bad(res, `status must be one of: ${spec.statuses.join(", ") || "(none)"}`);
    }
    where.push(`${spec.status} = ?`);
    params.push(q.status);
  }
  if (typeof q.cwd === "string" && q.cwd.trim()) {
    // The folder itself or anything under it (path boundary, no wildcards).
    const cwd = q.cwd.trim().replace(/\/+$/, "");
    where.push("(s.cwd = ? OR s.cwd LIKE ? ESCAPE '\\')");
    params.push(cwd, `${likeEscape(cwd)}/%`);
  }

  const sort = q.sort === undefined || q.sort === "" ? spec.sort[0] : q.sort;
  if (!spec.sort.includes(sort)) return bad(res, `sort must be one of: ${spec.sort.join(", ")}`);
  const dir = q.dir === undefined || q.dir === "" ? "desc" : q.dir;
  if (dir !== "asc" && dir !== "desc") return bad(res, "dir must be asc or desc");
  // Both pieces come from the allowlist above, never from the request text.
  const sqlDir = dir === "asc" ? "ASC" : "DESC";
  const order = `${spec.columns[sort]} ${sqlDir}, ${spec.tiebreak} ${sqlDir}`;

  return {
    spec,
    entity,
    where: where.length ? `WHERE ${where.join(" AND ")}` : "",
    params,
    order,
  };
}

function select(p, limit, offset) {
  const cols = Object.entries(p.spec.columns)
    .map(([name, sql]) => `${sql} AS ${name}`)
    .join(", ");
  return db
    .prepare(`SELECT ${cols} FROM ${p.spec.from} ${p.where} ORDER BY ${p.order} LIMIT ? OFFSET ?`)
    .all(...p.params, limit, offset);
}

function count(p) {
  return db.prepare(`SELECT COUNT(*) AS n FROM ${p.spec.from} ${p.where}`).get(...p.params).n;
}

/**
 * One CSV cell (RFC 4180): quoted when it holds a quote, comma or line break.
 * A text cell starting with = + - @ (or a tab / CR) gets a leading `'`, so a
 * spreadsheet never runs it as a formula.
 */
function csvCell(v) {
  if (v === null || v === undefined) return "";
  let s = String(v);
  if (typeof v === "string" && /^[=+\-@\t\r]/.test(s)) s = `'${s}`;
  return /[",\r\n]/.test(s) ? `"${s.replace(/"/g, '""')}"` : s;
}

function toCsv(columns, rows) {
  const lines = [columns.map(csvCell).join(",")];
  for (const r of rows) lines.push(columns.map((c) => csvCell(r[c])).join(","));
  return lines.join("\r\n") + "\r\n";
}

// GET /api/query — a page, or an export with `format=csv|json`.
router.get("/", (req, res) => {
  const p = parseQuery(req, res);
  if (!p) return;
  const format = req.query.format;
  const total = count(p);

  if (format === "csv" || format === "json") {
    const rows = select(p, EXPORT_CAP, 0);
    const truncated = total > rows.length;
    const name = `agenthub-${p.entity}-${new Date().toISOString().slice(0, 10)}.${format}`;
    res.setHeader("Content-Disposition", `attachment; filename="${name}"`);
    res.setHeader("X-Total-Count", String(total));
    res.setHeader("X-Truncated", truncated ? "1" : "0");
    if (format === "json") {
      return res.json({ entity: p.entity, total, truncated, cap: EXPORT_CAP, rows });
    }
    res.type("text/csv; charset=utf-8");
    // The BOM makes spreadsheet apps read the file as UTF-8.
    return res.send("\uFEFF" + toCsv(Object.keys(p.spec.columns), rows));
  }
  if (format !== undefined && format !== "") return bad(res, "format must be csv or json");

  const limit = Math.min(Math.max(parseInt(req.query.limit, 10) || 50, 1), PAGE_MAX);
  const offset = Math.max(parseInt(req.query.offset, 10) || 0, 0);
  res.json({
    entity: p.entity,
    columns: Object.keys(p.spec.columns),
    sortable: p.spec.sort,
    statuses: p.spec.statuses,
    rows: select(p, limit, offset),
    total,
    limit,
    offset,
    export_cap: EXPORT_CAP,
  });
});

module.exports = router;
module.exports.csvCell = csvCell;
module.exports.EXPORT_CAP = EXPORT_CAP;