How to build Jitbit help desk dashboards in Metabase

{
  "mcpServers": {
    "jitbit": {
      "command": "npx",
      "args": [
        "-y",
        "mcp-remote",
        "https://yourcompany.jitbit.com/api/mcp",
        "--header",
        "Authorization:Bearer YOUR_JITBIT_TOKEN"
      ]
    },
    "metabase": {
      "command": "npx",
      "args": ["-y", "mcp-remote", "https://your-metabase.example.com/api/metabase-mcp"]
    }
  }
}

Create a polished Metabase dashboard for Jitbit help desk analytics using the
available Jitbit tables in this database.

Goal: Help IT support leaders understand volume, responsiveness, SLA, category
drivers, and technician workload from Jitbit data.

First, inspect the schema and identify the available Jitbit tables. Do not assume
exact table names. Map the available raw tables into these analytical concepts
where possible: Tickets, Comments, Users (technicians and end users), Categories,
Statuses, Priorities, Tags, and Departments.

Important:
- Treat MCP data access as exploratory only.
- Build the dashboard from durable database/warehouse tables (or, on self-hosted
  Jitbit, a read replica of the SQL Server database).
- Use medians (p50) and p90 for response and resolution times, never averages.
- Define "first response" as the first technician comment, excluding internal
  notes and automated messages.
- If status-change history is missing, do not calculate reopen rate or
  time-in-status. Use a caveat instead.
- Do not claim Metabase connects natively to the Jitbit SaaS API; on self-hosted
  it connects to the underlying SQL Server database directly.

Dashboard title: Jitbit Help Desk Overview

Sections:
1. Executive summary (KPI cards): Tickets created last 7 days; Closed last 7 days;
   Open backlog; Median first response time; Volume by category.
2. Volume & backlog: Created vs closed by day; Open by status; Backlog aging;
   Volume by category.
3. SLA & response time: First response p50/p90 by week; Overdue by category;
   Reopened by week (only if history exists).
4. Technician performance: Closed by technician; Median resolution time by
   technician; Open assigned by technician; Volume by priority.
5. Categories & drivers: Volume by category over time; Top recurring issue types;
   Tickets by department; Repeat requesters.

Filters: Category, Technician, Priority, Status, Department, Date range.

Before finalizing, create or recommend reusable Metabase models:
modeled_jitbit_tickets, modeled_jitbit_comments, modeled_jitbit_users, and
modeled_jitbit_categories.

Output: Build the dashboard if you have permission; otherwise provide the exact
questions, SQL, model definitions, and layout. Include caveats for any metric
that cannot be calculated from the available schema. Keep it practical, dense,
and executive-readable. Avoid vanity metrics.

The basic volume trend over the last 30 days (StatusID 3 = closed).

SELECT
  CAST(t.IssueDate AS date) AS day,
  COUNT(*)                                          AS created,
  COUNT(*) FILTER (WHERE t.StatusID = 3)            AS closed
FROM Tickets t
WHERE t.IssueDate >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY CAST(t.IssueDate AS date)
ORDER BY 1;

Median from the first non-internal technician comment per ticket.

WITH first_tech_comment AS (
  SELECT
    c.IssueID,
    MIN(c.CommentDate) AS first_reply_at
  FROM Comments c
  JOIN Users u ON u.UserID = c.UserID
  WHERE u.IsAdmin = true       -- technician
    AND c.ForTechsOnly = false
  GROUP BY c.IssueID
)
SELECT
  date_trunc('week', t.IssueDate) AS week,
  percentile_cont(0.5) WITHIN GROUP (
    ORDER BY EXTRACT(EPOCH FROM (f.first_reply_at - t.IssueDate)) / 60.0
  ) AS median_first_reply_min
FROM Tickets t
JOIN first_tech_comment f ON f.IssueID = t.TicketID
GROUP BY 1
ORDER BY 1;

Top IT ticket drivers over the last 90 days.

SELECT
  cat.CategoryName   AS category,
  COUNT(*)           AS tickets
FROM Tickets t
JOIN Categories cat ON cat.CategoryID = t.CategoryID
WHERE t.IssueDate >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY cat.CategoryName
ORDER BY tickets DESC;

Open tickets bucketed by how long they've been waiting.

SELECT
  CASE
    WHEN CURRENT_DATE - t.IssueDate::date <= 1  THEN '0-1 days'
    WHEN CURRENT_DATE - t.IssueDate::date <= 3  THEN '2-3 days'
    WHEN CURRENT_DATE - t.IssueDate::date <= 7  THEN '4-7 days'
    ELSE '8+ days'
  END                AS age_bucket,
  COUNT(*)           AS open_tickets
FROM Tickets t
WHERE t.StatusID <> 3
GROUP BY 1
ORDER BY MIN(CURRENT_DATE - t.IssueDate::date);