TicketDesk-AI

A support ticketing system that reads like a trading terminal — built on Databricks Lakebase.

---

_{Running as a Databricks App against Lakebase. Stat tape, live SLA clocks, signal chips, three-pane terminal layout.}

---

The idea

Most internal tools are a table and a form. But a support ticket behaves a lot like a position: it has an age that only grows, a priority that reads like a signal, and a status that eventually resolves. So this one is built as a trading desk.

• Stat tape across the header — counters that flash when a number moves
• SLA clocks that tick every second, coloured like P&L: green fresh, amber ageing, red breached
• Signal chips for priority — up-triangle escalating, square steady, down-triangle low
• Three-pane terminal layout, 31px rows, monospace tabular numerics that never jitter
• Keyboard-first — j/k to move, / to search, n for new, esc to close

No framework, no build step, no dependencies. One HTML file, served by Flask.

---

Why Lakebase

Lakebase is Databricks-managed PostgreSQL — OLTP sitting next to the lakehouse. That distinction is the whole point of this project.

	Delta table (analytics)	Lakebase (this app)
Storage	Columnar, append-oriented	Row-oriented OLTP
Single-row read	Full/partition scan	Primary key, milliseconds
Foreign keys	None	Enforced
Constraints	None	CHECK, NOT NULL, UNIQUE
Row updates	Rewrite files	UPDATE / ON CONFLICT
Latency	Seconds	Milliseconds

Storing tickets as an analytics table would mean nothing stops a message from referencing a ticket that does not exist, and two agents changing a status at once would race.

The same rows, straight from Lakebase — including TKT-007, created through the deployed app rather than seeded:

The interesting part is that Lakebase does not force the choice. Change Data Feed streams these same rows into Unity Catalog Delta tables automatically — no Debezium, no connector. Both tables here already have REPLICA IDENTITY FULL set, so CDF can be switched on from the UI with no migration, and analytics gets an append-only history to build Silver/Gold layers on while the app keeps its transactional store.

---

Architecture

flowchart TD
    UI["Browser<br/><i>single page, vanilla JS</i>"]
    APP["app.py<br/><i>routes · JSON errors · identity</i>"]
    VAL["validation.py<br/><i>input rules</i>"]
    REPO["repository.py<br/><i>every SQL statement</i>"]
    LB["lakebase.py<br/><i>pooled psycopg2</i>"]
    DB[("Lakebase<br/>PostgreSQL 17")]
    CDF["Unity Catalog<br/><i>lb_*_history</i>"]

    UI -->|fetch| APP
    APP --> VAL
    APP --> REPO
    REPO --> LB
    LB --> DB
    DB -.->|Change Data Feed| CDF

Loading

Every SQL statement lives in repository.py. Nothing above it builds SQL, nothing below it knows what a ticket is. That boundary keeps the tests honest and means the data layer can be reused by an agent without Flask attached.

tickets                                ticket_messages
------------------------------         --------------------------------
ticket_id   BIGINT identity PK   <---  ticket_id    BIGINT NOT NULL FK
title       TEXT   NOT NULL            message_id   BIGINT identity PK
status      TEXT   NOT NULL            message_text TEXT   NOT NULL
priority    TEXT   NOT NULL            author       TEXT   NOT NULL
category    TEXT   NOT NULL            created_at   TIMESTAMPTZ
created_by  TEXT   NOT NULL
created_at  TIMESTAMPTZ
updated_at  TIMESTAMPTZ

ON DELETE CASCADE on the foreign key means deleting a ticket removes its thread in the same transaction — an orphaned message is not representable.

---

Engineering decisions worth reading

Validation lives in three layers, each with a different job. The database CHECK constraint guarantees a bad status can never be stored, even via direct SQL. validation.py turns what would be a constraint violation into a sentence a person can act on — "Priority must be one of: low, medium, high, urgent. Got 'apocalyptic'." The UI renders illegal transitions as disabled buttons so the situation mostly never arises. Belt, braces, and a sign telling you which one failed.

Status changes follow a workflow, not a dropdown. closed is terminal — reopening creates a new ticket so the original thread stays an accurate record. Illegal moves return a message naming the legal ones.

Every error is JSON, including the unexpected ones. A global handler catches anything unhandled, so the frontend's resp.json() never receives an HTML error page. Errors carry a field key, which is what lets the UI highlight the specific input that was wrong instead of showing a generic banner.

Credentials resolve environment-first, then secret scope. One code path for local development and deployed runs. Production still uses only the Databricks secret scope — app.yaml references the scope by name and never holds a value.

Statistics are computed in Postgres, not Python. One pass with FILTER clauses rather than pulling rows into the app and counting them there.

Colour is never the only signal. Every status and priority carries a glyph and a text label as well as a colour. The status control is a real radiogroup; blocked transitions use aria-disabled instead of the disabled attribute so they stay reachable by keyboard and a screen reader can say why a move is unavailable, rather than the option silently not existing. The two-step delete announces that it is armed — a colour change and a pulse convey nothing to someone who cannot see them. Toasts sit in a polite live region, errors in an assertive one.

---

Testing

180 passed

The suite runs against a real PostgreSQL server, never mocks. Lakebase is managed Postgres, so a local Postgres exercises the same SQL, the same constraints, the same cascade, and the same transaction semantics production will. Mocking the database would only prove the mocks agree with themselves.

If TEST_DATABASE_URL is unset, the fixtures boot a private throwaway cluster on a spare port and tear it down afterwards. Your system Postgres is untouched.

pip install -r requirements-dev.txt
pytest tests/ -q

File	Covers
test_schema.py	Columns, the FK and its cascade, CHECK constraints, seed integrity, idempotency
test_validation.py	Every input rule in both directions — including the wording of error messages
test_repository.py	CRUD, cascade, filtering, statistics maths, SQL-injection resistance
test_app.py	Every route, error shapes, and a full create → message → status → delete lifecycle

Two bugs the suite caught during development, both now regression-tested: seed messages that predated the ticket they replied to, and whole-day time offsets that made every age clock render as a fake-looking Nd00h.

---

API

Method	Path
GET	/api/tickets	List; ?status= &priority= &q= &limit=
POST	/api/tickets	Create, with optional opening message
GET	/api/tickets/<id>	Ticket plus full thread
PATCH	/api/tickets/<id>/status	Change status
DELETE	/api/tickets/<id>	Delete ticket and thread
POST	/api/tickets/<id>/messages	Add a message
DELETE	/api/messages/<id>	Delete a message
GET	/api/stats	Aggregates for the stat tape
GET	/healthz · /readyz	Liveness · readiness

---

Running it

Local

python -m venv venv && source venv/bin/activate
pip install -r requirements.txt

psql "$LAKEBASE_URL" -f schema.sql     # schema + seed data, idempotent
export LAKEBASE_URL='postgresql://...'
python app.py                          # http://localhost:8000

On Databricks

1. Compute → Lakebase → Lakebase Postgres → New project.
2. Project settings → Database connections → enable Password (native Postgres roles). Off by default — without it, roles only get 1-hour OAuth tokens.
3. Create a role and grant it the schema:

   CREATE ROLE ticketdesk_app WITH LOGIN PASSWORD '...';
   GRANT CONNECT ON DATABASE databricks_postgres TO ticketdesk_app;
   GRANT USAGE, CREATE ON SCHEMA public TO ticketdesk_app;

4. Run schema.sql, then python setup_secrets.py to store the URL as the secret lubo-ticketdesk/lakebase-url. Secret scopes are workspace-wide, so pick a name specific enough that nothing else can collide with it.
5. Workspace → Create → Git folder, then Compute → Apps → Create app pointed at it. app.yaml supplies the rest.

No credential appears anywhere in this repository.

---

Tech

Python 3.13 · Flask · psycopg2 (pooled) · PostgreSQL 17 on Databricks Lakebase · pytest · vanilla JS, zero frontend dependencies

---

Built by Lubo Bali · lubot.ai · LinkedIn