Build a ticket service with Python and SQLite

The project brief

Build the ticket service for a community repair desk. A volunteer records a broken item and later marks its ticket resolved. The desk needs durable records and predictable errors; a complicated framework would distract from those requirements. Start with the business layer, then use the next project to expose it through HTTP and a browser.

Your deliverable is a Python module, a repeatable test suite, and a written API contract. You need Python 3.9 or newer with its SQLite module, a terminal, and an editor. Check the interpreter before starting. All runtime dependencies in this lesson belong to Python's standard library; no package installation or account is required.

Review backend API fundamentals and testing. Try a parameterized query in the SQL playground if rows, keys, or transactions are unfamiliar. Reserve two focused sessions: one for implementation and one for explaining failure behavior.

Requirements and data model

A ticket has an integer primary key, a title, and a status. Titles contain between one and 120 characters after surrounding whitespace is removed. Status is either open or resolved. Creation always starts open. Resolution is idempotent: resolving an already resolved ticket returns the same record. Resolving a missing ticket reports absence instead of silently succeeding.

List records in ascending identifier order so tests and clients have stable output. Keep this first version deliberately small: no deletion, assignments, attachments, or authentication. The exercise contains only fictional public repair descriptions. Production ownership and personal data require additional design, not an extra title field.

The schema mirrors the essential validation with NOT NULL and CHECK constraints. An integer primary key also supplies the lookup structure for resolution. There is no extra status index because this version never filters on status. Add an index when a real query and its access pattern justify one.

Milestone 1: create a runnable workspace

Use a new directory, outside any existing application repository:

mkdir repair-desk
cd repair-desk
python3 --version
python3 -c "import sqlite3; print(sqlite3.sqlite_version)"

Save the following complete implementation as tickets.py. Each operation opens its own connection, uses a short transaction when writing, and closes the connection explicitly. The official sqlite3 documentation explains parameter binding and connection behavior. A connection context manager commits or rolls back; it does not close the connection.

import sqlite3
from contextlib import closing


class TicketStore:
    def __init__(self, path):
        self.path = str(path)
        with closing(self.connect()) as db, db:
            db.execute("""
                CREATE TABLE IF NOT EXISTS tickets (
                    id INTEGER PRIMARY KEY,
                    title TEXT NOT NULL
                        CHECK(length(trim(title)) BETWEEN 1 AND 120),
                    status TEXT NOT NULL DEFAULT 'open'
                        CHECK(status IN ('open', 'resolved'))
                )
            """)

    def connect(self):
        db = sqlite3.connect(self.path, timeout=2)
        db.row_factory = sqlite3.Row
        return db

    def create(self, title):
        if not isinstance(title, str):
            raise ValueError("title must be text")
        title = title.strip()
        if not 1 <= len(title) <= 120:
            raise ValueError("title must contain 1 to 120 characters")
        with closing(self.connect()) as db, db:
            cursor = db.execute(
                "INSERT INTO tickets (title) VALUES (?)", (title,)
            )
            row = db.execute(
                "SELECT id, title, status FROM tickets WHERE id = ?",
                (cursor.lastrowid,),
            ).fetchone()
            return dict(row)

    def list(self):
        with closing(self.connect()) as db:
            rows = db.execute(
                "SELECT id, title, status FROM tickets ORDER BY id"
            ).fetchall()
            return [dict(row) for row in rows]

    def resolve(self, ticket_id):
        if type(ticket_id) is not int or ticket_id <= 0:
            raise ValueError("ticket id must be a positive integer")
        with closing(self.connect()) as db, db:
            cursor = db.execute(
                "UPDATE tickets SET status = 'resolved' WHERE id = ?",
                (ticket_id,),
            )
            if cursor.rowcount == 0:
                raise LookupError("ticket not found")
            row = db.execute(
                "SELECT id, title, status FROM tickets WHERE id = ?",
                (ticket_id,),
            ).fetchone()
            return dict(row)

Returning plain dictionaries keeps callers independent of SQLite row objects. Notice that the update finds the ticket and changes it in one statement. Reading first and updating later would introduce an unnecessary interval in which another writer could act. The transaction also keeps the returned state tied to this write.

Milestone 2: prove the rules

Save this as test_tickets.py. Temporary directories give each test a separate database; a successful run must not depend on a developer's old local data. File-backed storage is intentional: using separate connections to :memory: would create separate databases.

import tempfile
import unittest
from pathlib import Path
from tickets import TicketStore


class TicketTests(unittest.TestCase):
    def setUp(self):
        self.temp = tempfile.TemporaryDirectory()
        self.addCleanup(self.temp.cleanup)
        self.path = Path(self.temp.name) / "tickets.db"
        self.store = TicketStore(self.path)

    def test_create_and_reopen(self):
        ticket = self.store.create("  Fix bicycle brake  ")
        self.assertEqual(ticket, {
            "id": 1, "title": "Fix bicycle brake", "status": "open"
        })
        self.assertEqual(TicketStore(self.path).list(), [ticket])

    def test_invalid_titles_do_not_insert(self):
        for title in (None, 123, "", " \n ", "x" * 121):
            with self.subTest(title=title):
                with self.assertRaises(ValueError):
                    self.store.create(title)
        self.assertEqual(self.store.list(), [])
        self.assertEqual(len(self.store.create("x" * 120)["title"]), 120)

    def test_resolve_is_repeatable(self):
        ticket = self.store.create("Repair lamp")
        first = self.store.resolve(ticket["id"])
        self.assertEqual(first["status"], "resolved")
        self.assertEqual(self.store.resolve(ticket["id"]), first)

    def test_missing_and_invalid_ids(self):
        with self.assertRaises(LookupError):
            self.store.resolve(999)
        for value in (0, -1, True, "1"):
            with self.subTest(value=value):
                with self.assertRaises(ValueError):
                    self.store.resolve(value)

    def test_order_and_sql_like_text(self):
        title = "'); DROP TABLE tickets; --"
        first = self.store.create(title)
        second = self.store.create("Repair kettle")
        self.assertEqual(self.store.list(), [first, second])


if __name__ == "__main__":
    unittest.main()

python3 -m unittest -v
python3 -c "from tickets import TicketStore; s=TicketStore('tickets.db'); print(s.create('Repair lamp')); print(s.list())"

Expect five passing tests. The demonstration writes one persistent record, so repeating it creates another ticket. That is a useful distinction: resolution is idempotent, creation is not. A production client retrying creation after a timeout needs an idempotency design, such as a unique request key scoped to an authenticated actor.

Milestone 3: define the transport contract

Before adding HTTP, write down these mappings. They are implemented in the full-stack lesson, which imports this exact module.

Use a JSON error object with a string error property. Do not serialize exception details into public responses. Database paths, query text, and stack traces help operators but give clients no useful recovery action. Explore the contract in the API playground, then explain which errors a client can correct and which deserve a retry.

Failure and security review

Parameterized SQL treats a malicious-looking title as data; the corresponding test verifies that the table survives. It does not establish authorization. Without an owner column and verified identity, every caller would operate on the same records. Read authentication before adding accounts; never accept a user identifier as proof of ownership.

Force a storage failure by constructing the store with a database path inside a nonexistent directory. Explain why this is an operational failure, not a malformed ticket. A lock timeout can also fail writes. The HTTP layer should report temporary unavailability while preserving internal diagnostics. Do not automatically retry arbitrary writes: the client might not know whether an earlier attempt committed.

The application trims Python whitespace; SQLite's default trim has narrower whitespace semantics. The database constraint is a backstop, not a promise that direct SQL accepts exactly the same input. Keep application validation authoritative and document any importer that bypasses it. Likewise, CREATE TABLE IF NOT EXISTS initializes this exercise but does not migrate an older schema.

Deployment approach

Deliver the tested business module first; it is not a running network service by itself. Add the local HTTP adapter from the next lesson for demonstrations. Python explicitly says http.server is unsuitable for production. Public deployment needs a supported server stack, request limits, authentication where appropriate, and operational monitoring.

For a future Render deployment, select durable storage deliberately: persistent disks preserve only files under their mount path and constrain scaling to one instance. Render's free service documentation says free web services cannot attach persistent disks. A local SQLite file on that tier is therefore disposable. Use synthetic data in previews, and plan managed database storage before introducing multiple application instances. Practice deployment reasoning with deployment.

Review rubric and interview followups

Score correctness out of four: validation, stable listing, missing-record behavior, and repeatable resolution each earn one point. Score evidence out of three: isolated tests, persistence across reopening, and injection-shaped input. Score explanation out of three: connection lifetime, transaction boundaries, and a credible durable deployment plan. Eight points is a good review threshold; any lost-data deployment assumption requires revision regardless of score.

In an interview, explain why booleans are rejected as identifiers even though Python considers them integers. Describe what changes when two volunteers edit a ticket simultaneously. How would you introduce assignments without trusting a browser-supplied owner? How would you safely evolve the schema? Finally, distinguish a commit failure from a response timeout. Strong answers connect observable behavior to a specific invariant and a test that would catch its violation.

Continue on this track

Continue with the full-stack project to expose this exact business module, then practice review and delivery in the team workflow project.

Course navigation

Course overview · Previous lesson · Next lesson