Python Streamlit support dashboard

A support dashboard helps coworkers track open tickets, response time, queue history, category backlog, and notes for the next shift. Tokay runs the Streamlit app as one private Web Service without adding sign in code to the project.

Last updated

This is a good fit for an internal tool built quickly from Python data components. Streamlit stays focused on the dashboard, PostgreSQL keeps shared state, and Tokay decides who may reach it.

Runtime
Python
Framework
Streamlit
Service types
Web Service
Resources
PostgreSQL
Required secrets
None

What it does

The top row shows how many tickets are open now, how many opened or resolved in the selected range, and the median first response time.

Change Date range to update the queue history chart. Current backlog compares open work by category, and Newest open tickets gives the team a compact working list.

Under Team annotations, edit the context attached to a recent date and press Save annotations. The next coworker sees the same note.

Deploy it

Bring this folder to Tokay any way you like. Paste or upload it in the dashboard, push it with git, or hand it to your AI agent along with our agent instructions. Tokay figures out the rest.

Working in Claude, ChatGPT, VS Code, or another MCP client? Connect the Tokay MCP server and ask your agent to deploy this example. It signs in through your browser, so there is no token to paste.

New to Tokay? The getting started guide walks you through your first deploy.

Finish setup in Tokay

The dashboard needs PostgreSQL to keep the ticket history and team notes. Open Environment and find DATABASE_URL under From your infrastructure. Choose Confirm database, keep the suggested app name, then choose Confirm.

Continue through the deployment tracker after DATABASE_URL reaches Good to go. Choose Go Live if it appears. Before sharing the finished Service URL, use the steps below to make the dashboard private.

Make the dashboard private

  1. Choose App audience from the Project.
  2. Set Audience to Specific people. Leave Project members selected so your teammates can sign in.
  3. Choose Save audience. Review Restrict who can visit, then choose Save.
  4. To invite someone else, choose Add visitor, enter the coworker's email, and choose Add. They will receive a magic link when they first visit.

Open the live URL in a signed out browser. Tokay starts the sign in flow before Streamlit appears.

Try it

  1. Narrow Date range and note how the queue chart and metrics change.
  2. Edit one team annotation and press Save annotations.
  3. Deploy the dashboard again. Your note and the 90 day ticket history remain in PostgreSQL.

How it works

You can build the whole interface with ordinary Streamlit components. Pandas supplies the chart data, while SQLAlchemy reads and updates the same PostgreSQL database for every coworker.

The Alembic migration stays beside the app, and Tokay tests it against a copy before applying it. Fictional ticket history appears only when the tables are empty.

Audience settings stop unapproved visitors before Streamlit handles their request. You can add Project members, approved email domains, or specific people without changing Python code.

Secrets

You do not need an application secret. Tokay supplies DATABASE_URL, and the Project audience controls coworker access.

Grow from here

Try visitor registration when one path in a private app needs to remain public.

Source code

Download ZIP GitHub

migrations/versions/0001_support_dashboard.py Raw 

"""Create support tickets and annotations.

Revision ID: 0001_support_dashboard
Revises:
"""

from collections.abc import Sequence

from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import postgresql

revision: str = "0001_support_dashboard"
down_revision: str | None = None
branch_labels: str | Sequence[str] | None = None
depends_on: str | Sequence[str] | None = None


def upgrade() -> None:
    op.create_table(
        "support_tickets",
        sa.Column("id", postgresql.UUID(as_uuid=True), primary_key=True),
        sa.Column("subject", sa.String(length=180), nullable=False),
        sa.Column("category", sa.String(length=40), nullable=False),
        sa.Column("priority", sa.String(length=20), nullable=False),
        sa.Column("opened_at", sa.DateTime(timezone=True), nullable=False),
        sa.Column("resolved_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("first_response_minutes", sa.Integer(), nullable=False),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("now()")),
        sa.CheckConstraint(
            "category IN ('Account access', 'Billing', 'Delivery', 'Product help')",
            name="support_tickets_category_check",
        ),
        sa.CheckConstraint(
            "priority IN ('Low', 'Normal', 'High', 'Urgent')",
            name="support_tickets_priority_check",
        ),
        sa.CheckConstraint("first_response_minutes >= 0", name="support_tickets_response_check"),
    )
    op.create_index("support_tickets_opened_at_idx", "support_tickets", ["opened_at"])
    op.create_index("support_tickets_resolved_at_idx", "support_tickets", ["resolved_at"])
    op.create_index("support_tickets_category_idx", "support_tickets", ["category"])

    op.create_table(
        "support_annotations",
        sa.Column("id", postgresql.UUID(as_uuid=True), primary_key=True),
        sa.Column("annotation_date", sa.Date(), nullable=False, unique=True),
        sa.Column("note", sa.String(length=240), nullable=False),
        sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("now()")),
    )


def downgrade() -> None:
    op.drop_table("support_annotations")
    op.drop_index("support_tickets_category_idx", table_name="support_tickets")
    op.drop_index("support_tickets_resolved_at_idx", table_name="support_tickets")
    op.drop_index("support_tickets_opened_at_idx", table_name="support_tickets")
    op.drop_table("support_tickets")

migrations/env.py Raw 

import os
from logging.config import fileConfig

from alembic import context
from sqlalchemy import engine_from_config, pool

config = context.config
if config.config_file_name is not None:
    fileConfig(config.config_file_name)

database_url = os.environ["DATABASE_URL"]
if database_url.startswith("postgresql://"):
    database_url = database_url.replace("postgresql://", "postgresql+psycopg://", 1)
elif database_url.startswith("postgres://"):
    database_url = database_url.replace("postgres://", "postgresql+psycopg://", 1)
config.set_main_option("sqlalchemy.url", database_url.replace("%", "%%"))

target_metadata = None


def run_migrations_offline() -> None:
    context.configure(
        url=config.get_main_option("sqlalchemy.url"),
        target_metadata=target_metadata,
        literal_binds=True,
        dialect_opts={"paramstyle": "named"},
    )
    with context.begin_transaction():
        context.run_migrations()


def run_migrations_online() -> None:
    connectable = engine_from_config(
        config.get_section(config.config_ini_section, {}),
        prefix="sqlalchemy.",
        poolclass=pool.NullPool,
    )
    with connectable.connect() as connection:
        context.configure(connection=connection, target_metadata=target_metadata)
        with context.begin_transaction():
            context.run_migrations()


if context.is_offline_mode():
    run_migrations_offline()
else:
    run_migrations_online()

alembic.ini Raw 

[alembic]
script_location = migrations
prepend_sys_path = .
sqlalchemy.url = postgresql+psycopg://placeholder:placeholder@localhost/placeholder

[loggers]
keys = root,sqlalchemy,alembic

[handlers]
keys = console

[formatters]
keys = generic

[logger_root]
level = WARN
handlers = console
qualname =

[logger_sqlalchemy]
level = WARN
handlers =
qualname = sqlalchemy.engine

[logger_alembic]
level = INFO
handlers =
qualname = alembic

[handler_console]
class = StreamHandler
args = (sys.stderr,)
level = NOTSET
formatter = generic

[formatter_generic]
format = %(levelname)-5.5s [%(name)s] %(message)s
datefmt = %H:%M:%S

app.py Raw 

import os
import random
import uuid
from datetime import date, datetime, time, timedelta, timezone

import pandas as pd
import streamlit as st
from sqlalchemy import create_engine, text


DATABASE_URL = os.environ.get("DATABASE_URL")
if not DATABASE_URL:
    raise RuntimeError("DATABASE_URL is required")


@st.cache_resource
def database_engine():
    url = DATABASE_URL
    if url.startswith("postgresql://"):
        url = url.replace("postgresql://", "postgresql+psycopg://", 1)
    elif url.startswith("postgres://"):
        url = url.replace("postgres://", "postgresql+psycopg://", 1)
    return create_engine(url, pool_pre_ping=True)


engine = database_engine()

CATEGORIES = ["Account access", "Billing", "Delivery", "Product help"]
SUBJECTS = {
    "Account access": [
        "Password reset link expired",
        "New teammate cannot sign in",
        "Account email needs changing",
    ],
    "Billing": [
        "Receipt needed for last month",
        "Duplicate charge question",
        "Billing address needs updating",
    ],
    "Delivery": [
        "Shipment has not moved",
        "Address correction requested",
        "Package marked delivered early",
    ],
    "Product help": [
        "Export does not include notes",
        "Need help setting up a template",
        "Report filter is confusing",
    ],
}
SEED_NAMESPACE = uuid.UUID("7f9b2cd4-9f08-4f8f-9146-28994e845bd5")


def seed_dashboard() -> None:
    today = datetime.now(timezone.utc).date()
    randomizer = random.Random(4817)
    tickets = []
    for days_ago in range(89, -1, -1):
        opened_on = today - timedelta(days=days_ago)
        weekday_weight = 2 if opened_on.weekday() < 5 else 0
        count = randomizer.randint(1, 4) + weekday_weight
        if 24 <= days_ago <= 27:
            count += 4
        for number in range(count):
            category = randomizer.choices(CATEGORIES, weights=[24, 18, 31, 27], k=1)[0]
            priority = randomizer.choices(["Low", "Normal", "High", "Urgent"], weights=[10, 59, 25, 6], k=1)[0]
            opened_at = datetime.combine(
                opened_on,
                time(hour=randomizer.randint(8, 17), minute=randomizer.choice([5, 15, 25, 35, 45, 55])),
                tzinfo=timezone.utc,
            )
            response_minutes = max(3, int(randomizer.lognormvariate(3.25, 0.55)))
            open_chance = 0.06 if days_ago > 14 else 0.18 if days_ago > 3 else 0.42
            stays_open = randomizer.random() < open_chance
            resolution_hours = randomizer.randint(2, 70) + (22 if category == "Delivery" else 0)
            resolved_at = None if stays_open else opened_at + timedelta(hours=resolution_hours)
            if resolved_at and resolved_at > datetime.now(timezone.utc):
                resolved_at = None
            seed_key = f"{opened_on.isoformat()}-{number}"
            tickets.append({
                "id": uuid.uuid5(SEED_NAMESPACE, f"ticket-{seed_key}"),
                "subject": randomizer.choice(SUBJECTS[category]),
                "category": category,
                "priority": priority,
                "opened_at": opened_at,
                "resolved_at": resolved_at,
                "first_response_minutes": response_minutes,
            })

    notes = [
        (today - timedelta(days=25), "Backlog spike followed a carrier routing change."),
        (today - timedelta(days=14), "Account questions rose after the new teammate rollout."),
        (today - timedelta(days=4), "Response time improved after the morning queue review."),
    ]

    with engine.begin() as connection:
        connection.execute(text("SELECT pg_advisory_xact_lock(hashtext('support-dashboard-seed'))"))
        if connection.execute(text("SELECT count(*) FROM support_tickets")).scalar_one() == 0:
            connection.execute(text("""
                INSERT INTO support_tickets
                    (id, subject, category, priority, opened_at, resolved_at, first_response_minutes)
                VALUES
                    (:id, :subject, :category, :priority, :opened_at, :resolved_at, :first_response_minutes)
            """), tickets)
        if connection.execute(text("SELECT count(*) FROM support_annotations")).scalar_one() == 0:
            connection.execute(text("""
                INSERT INTO support_annotations (id, annotation_date, note)
                VALUES (:id, :annotation_date, :note)
            """), [{
                "id": uuid.uuid5(SEED_NAMESPACE, f"note-{annotation_date.isoformat()}"),
                "annotation_date": annotation_date,
                "note": note,
            } for annotation_date, note in notes])


def load_frame(query: str, params=None) -> pd.DataFrame:
    return pd.read_sql(text(query), engine, params=params or {})


seed_dashboard()

today = date.today()
default_start = today - timedelta(days=29)

st.set_page_config(page_title="Support Desk Pulse", page_icon="◉", layout="wide")
st.markdown("""
<style>
  [data-testid="stAppViewContainer"] { background: #f4f2ed; }
  [data-testid="stHeader"] { background: transparent; }
  [data-testid="stMetric"] { background: #fffdf8; border: 1px solid #ded9ce; border-radius: 12px; padding: 16px; }
  .support-kicker { color: #a24e3a; font-size: .76rem; font-weight: 750; letter-spacing: .12em; text-transform: uppercase; }
  .support-note { color: #66706b; max-width: 760px; font-size: 1.05rem; line-height: 1.6; }
</style>
""", unsafe_allow_html=True)

st.markdown('<p class="support-kicker">Customer support operations</p>', unsafe_allow_html=True)
st.title("Support desk pulse")
st.markdown(
    '<p class="support-note">A working view of the queue for the people answering it. Filter the history, find the pressure, and leave context for the next shift.</p>',
    unsafe_allow_html=True,
)

with st.sidebar:
    st.header("View")
    selected_range = st.date_input(
        "Date range",
        value=(default_start, today),
        min_value=today - timedelta(days=89),
        max_value=today,
    )
    st.caption("The dashboard includes 90 days of fictional support activity.")

if isinstance(selected_range, (tuple, list)) and len(selected_range) == 2:
    start_date, end_date = selected_range
else:
    start_date, end_date = default_start, today

metrics = load_frame("""
    SELECT
        count(*) FILTER (WHERE resolved_at IS NULL)::int AS open_now,
        count(*) FILTER (WHERE opened_at::date BETWEEN :start_date AND :end_date)::int AS opened_in_range,
        count(*) FILTER (WHERE resolved_at::date BETWEEN :start_date AND :end_date)::int AS resolved_in_range,
        coalesce(round(percentile_cont(0.5) WITHIN GROUP (ORDER BY first_response_minutes)
            FILTER (WHERE opened_at::date BETWEEN :start_date AND :end_date))::int, 0) AS median_response
    FROM support_tickets
""", {"start_date": start_date, "end_date": end_date}).iloc[0]

metric_columns = st.columns(4)
metric_columns[0].metric("Open now", int(metrics["open_now"]))
metric_columns[1].metric("Opened in range", int(metrics["opened_in_range"]))
metric_columns[2].metric("Resolved in range", int(metrics["resolved_in_range"]))
metric_columns[3].metric("Median first response", f"{int(metrics['median_response'])} min")

daily = load_frame("""
    WITH days AS (
        SELECT generate_series(CAST(:start_date AS date), CAST(:end_date AS date), interval '1 day')::date AS day
    )
    SELECT
        days.day,
        count(tickets.id) FILTER (WHERE tickets.opened_at::date = days.day)::int AS opened,
        count(tickets.id) FILTER (WHERE tickets.resolved_at::date = days.day)::int AS resolved,
        count(tickets.id) FILTER (
            WHERE tickets.opened_at < days.day + interval '1 day'
              AND (tickets.resolved_at IS NULL OR tickets.resolved_at >= days.day + interval '1 day')
        )::int AS open_tickets
    FROM days
    LEFT JOIN support_tickets AS tickets
      ON tickets.opened_at < days.day + interval '1 day'
    GROUP BY days.day
    ORDER BY days.day
""", {"start_date": start_date, "end_date": end_date})
daily = daily.rename(columns={"opened": "Opened", "resolved": "Resolved", "open_tickets": "Open tickets"})

st.subheader("Queue history")
st.line_chart(daily, x="day", y=["Opened", "Resolved", "Open tickets"], height=330)

left, right = st.columns([1, 1.45])
with left:
    st.subheader("Current backlog")
    backlog = load_frame("""
        SELECT category, count(*)::int AS open_tickets
        FROM support_tickets
        WHERE resolved_at IS NULL
        GROUP BY category
        ORDER BY open_tickets DESC, category
    """)
    backlog = backlog.rename(columns={"category": "Category", "open_tickets": "Open tickets"})
    st.bar_chart(backlog, x="Category", y="Open tickets", height=280)

with right:
    st.subheader("Newest open tickets")
    recent = load_frame("""
        SELECT subject AS "Subject", category AS "Category", priority AS "Priority",
               opened_at::date AS "Opened", first_response_minutes AS "First response minutes"
        FROM support_tickets
        WHERE resolved_at IS NULL
        ORDER BY opened_at DESC
        LIMIT 8
    """)
    st.dataframe(recent, hide_index=True, width="stretch")

st.divider()
st.subheader("Team annotations")
st.write("Edit the note that explains a change in the queue, then save it for the next person who opens the dashboard.")
annotations = load_frame("""
    SELECT id::text AS "ID", annotation_date AS "Date", note AS "Note"
    FROM support_annotations
    ORDER BY annotation_date DESC
""")
edited_annotations = st.data_editor(
    annotations,
    hide_index=True,
    width="stretch",
    disabled=["ID", "Date"],
    column_config={"ID": None, "Date": st.column_config.DateColumn(format="MMM D, YYYY")},
    key="support_annotations_editor",
)

if st.button("Save annotations", type="primary"):
    updates = []
    for row in edited_annotations.to_dict("records"):
        note = str(row["Note"]).strip()
        if not note or len(note) > 240:
            st.error("Keep every annotation between 1 and 240 characters.")
            break
        updates.append({"id": row["ID"], "note": note})
    else:
        with engine.begin() as connection:
            connection.execute(text("""
                UPDATE support_annotations
                SET note = :note, updated_at = now()
                WHERE id = CAST(:id AS uuid)
            """), updates)
        st.success("Annotations saved.")

requirements.txt Raw 

streamlit==1.59.2
pandas==3.0.3
SQLAlchemy==2.0.51
psycopg[binary]==3.3.4
alembic==1.18.5

Built from revision 52c7b7f

Guide: Host an internal tool your coworkers can sign into