Support Tickets API

A small REST API for managing support tickets, built with FastAPI and PostgreSQL.

Stack

• Python 3.12, FastAPI, psycopg, Pydantic v2, raw parameterized SQL

---

Setup

git clone https://github.com/yl3847/Laminar-Interview.git
cd Laminar-Interview
python3 -m venv venv
source venv/bin/activate
pip install -r requirements.txt

Database Initialization

# Create the database (if it doesn't exist)
createdb laminar_exercise

# Apply the schema
psql -d laminar_exercise -f schema.sql

To use a custom database URL:

export DATABASE_URL="postgresql://user:password@localhost:5432/laminar_exercise"

Run the Server

uvicorn main:app --reload

---

API Examples

GET /health

curl -s http://localhost:8000/health | python3 -m json.tool

POST /tickets — valid input

curl -s -X POST http://localhost:8000/tickets \
  -H "Content-Type: application/json" \
  -d '{
    "customer_id": "cust-101",
    "subject": "Cannot log in",
    "priority": "high",
    "status": "open",
    "created_by": "alice@example.com"
  }' | python3 -m json.tool

POST /tickets — invalid priority (422 response)

curl -s -X POST http://localhost:8000/tickets \
  -H "Content-Type: application/json" \
  -d '{
    "customer_id": "cust-101",
    "subject": "Test",
    "priority": "critical",
    "status": "open",
    "created_by": "alice@example.com"
  }' | python3 -m json.tool

GET /tickets — by customer

curl -s "http://localhost:8000/tickets?customer_id=cust-101" | python3 -m json.tool

GET /tickets — with optional filters

curl -s "http://localhost:8000/tickets?customer_id=cust-101&status=open&priority=high" | python3 -m json.tool

PATCH /tickets/{id} — update status

curl -s -X PATCH http://localhost:8000/tickets/1 \
  -H "Content-Type: application/json" \
  -d '{"status": "resolved"}' | python3 -m json.tool

PATCH /tickets/{id} — non-existent ticket (404 response)

curl -s -X PATCH http://localhost:8000/tickets/999 \
  -H "Content-Type: application/json" \
  -d '{"status": "closed"}' | python3 -m json.tool

GET /customers/{customer_id}/tickets/summary

curl -s "http://localhost:8000/customers/cust-101/tickets/summary" | python3 -m json.tool

---

Design Notes

Schema choices

• BIGINT GENERATED ALWAYS AS IDENTITY for id — the modern PostgreSQL standard over SERIAL.
• customer_id is VARCHAR rather than an integer foreign key — keeps the service self-contained without requiring a customers table.
• CHECK constraints on priority and status enforce validity at the database level, not just the application level.
• TIMESTAMPTZ for timestamps to store timezone-aware values.

Index choices

• Four composite indexes on (customer_id, [status,] [priority,] created_at DESC) directly mirror the four query shapes the API supports: filter by customer only, customer + status, customer + priority, and all three. A composite index starting with customer_id also serves as the basic customer lookup, so no separate single-column index on customer_id is needed.

Validation approach

• Pydantic Literal types (Literal["low", "medium", "high", "urgent"]) handle enum validation declaratively — no manual @field_validator boilerplate.
• EmailStr validates created_by format automatically.
• FastAPI's Query(...) with typed Optional[Status] / Optional[Priority] parameters validates query string values before they reach any SQL.
• All SQL values go through psycopg %s parameterized queries — no f-string interpolation of user input.

API design

• GET /customers/{customer_id}/tickets/summary uses a path parameter rather than a query string, which reads more naturally as a resource and avoids a route conflict with GET /tickets.
• PATCH /tickets/{id} updates only status and touches updated_at — minimal surface area for the stated requirement.
• Pydantic validation failures return FastAPI's standard 422 response with field-level detail; no custom error handler needed for an interview scope.

Improvements with more time

• Connection pooling with psycopg_pool instead of opening a new connection per request.
• Pagination (limit / offset) on GET /tickets.
• A customers table with a proper foreign key on tickets.customer_id.
• Structured logging and request IDs for traceability.
• Integration tests with a real test database using pytest and httpx.