Support Ticket System — Databricks App + Lakebase

An internal support system where users can raise support tickets and add messages to them. It is built as a Databricks App — a Streamlit UI backed by Lakebase (managed Postgres).

Every ticket has a status, a severity, and an owning department, and keeps its own message thread.

Built and tested on Databricks Free Edition. It runs entirely inside the workspace — no local setup, and nothing here requires a paid tier.

Features

• Dashboard — seven live metrics (total, open, in progress, waiting, resolved, closed, critical)
• Ticket list — search, filter by status / severity / department, sortable columns, first 100 rows
• Ticket detail — full metadata plus the conversation thread, reachable by a real URL (?ticket_id=42)
• Quick Actions — update status, add a message, change severity/department, close, or delete
• Severity-first default ordering — critical before high before normal before low

Architecture

Streamlit UI  (app.py)          — pages, dialogs, table rendering
      ↓
Service layer (tickets.py)      — validation, CRUD, stats; contains all SQL
      ↓
Connection    (lakebase.py)     — psycopg2 helper, RealDictCursor
      ↓
Lakebase (Postgres)             — tickets, ticket_messages

tickets.py imports no UI code, so the same functions can be driven from a job, a notebook, or an agent without Streamlit in the loop.

File	Purpose
app.py	Streamlit UI — dashboard, ticket detail, create/quick-action dialogs
tickets.py	Business logic and all SQL — the only module that touches the tables
lakebase.py	Reads the connection URL from a secret (or env var) and runs queries
app.yaml	Databricks Apps start command + the name of the secret. No credentials.
schema.sql	Tables, grants, and seed data
setup_secrets.py	One-time script storing the Lakebase URL as a Databricks secret
requirements.txt	Python dependencies

Data model

tickets(ticket_id, title, status, severity, department, created_by, created_at, updated_at)
ticket_messages(message_id, ticket_id → tickets, message_text, author, created_at)

Allowed values are defined in tickets.py:

• status — open, in_progress, waiting, resolved, closed
• severity — low, normal, high, critical
• department — IT, HR, Facilities, Finance, Legal, Operations, General

Prerequisites

• A Databricks workspace with Apps and Lakebase. Free Edition includes both.
• A Lakebase database instance with a native-password Postgres role for the app. The examples use abhi_ticket — substitute your own role name throughout schema.sql.

Everything below is done from within the workspace. There is nothing to install on your machine.

Setup

1. Create the Databricks App (Custom app) in your workspace.

2. Store the connection URL as a secret. Clone this repo into the workspace, then open a notebook and choose Run → Open terminal. getpass needs a real TTY, so this must be an interactive terminal — a %sh cell will fail. From inside the cloned folder:

python setup_secrets.py

Paste the URL at the hidden prompt:

postgresql://<role>:<password>@<host>.database.<region>.cloud.databricks.com:5432/databricks_postgres?sslmode=require

It is written to the secret database/lakebase-url; the password never enters the repo. The script also grants READ on the scope so the app can retrieve it.

3. Create the schema. In the Lakebase SQL Editor, connected as the database owner, replace abhi_ticket with your app's role name and run schema.sql. It creates both tables, grants the role table and sequence access, and inserts five sample tickets with six messages. Delete the INSERT statements if you want to start empty.

4. Deploy. Point the app at this Git folder and deploy. app.yaml provides the start command and tells lakebase.py which secret to read:

env:
  - name: LAKEBASE_SECRET_SCOPE
    value: "database"
  - name: LAKEBASE_SECRET_KEY
    value: "lakebase-url"

The app's service principal needs READ on that secret scope, and the Postgres role in the URL needs the grants from schema.sql.

License

MIT