Queue Service

A small FastAPI + SQLAlchemy + Alembic REST API for appointment queues, where each employer's working day is automatically sliced into equal time slots derived from how long one appointment takes.

Built in August 2024 during an internship, as a practice project for learning FastAPI, SQLAlchemy ORM patterns and Alembic migrations.

How it works

The interesting part is that slots are never stored as rows. There is no slots table and no seeding step. An employer only records how long one appointment takes — the spent_time field — and the full list of slots for a day is computed on the fly, every time it is asked for.

Generating the slots

utils/employer.py::get_times(spent_time) does the whole job:

1. spent_time is a datetime.time, but it is read as a duration, not as a clock reading. Its hour, minute and second are unpacked into a timedelta. So 01:00:00 means "one hour per appointment", 00:30:00 means "thirty minutes per appointment".
2. The number of slots is int(timedelta(days=1) / spent_time_timedelta) — a whole day divided by one appointment, floored. Because it is floored, a duration that does not divide 1440 minutes evenly simply leaves the tail of the day unused; nothing rounds up and no partial slot is emitted.
3. Starting from timedelta() (midnight), the function walks forward spent_time at a time. On each step it keeps the previous boundary as the start and the new accumulator as the end, and formats the pair as the string "<start>-<end>" using Python's timedelta string form.

So the boundaries are pure accumulation from midnight — slot n runs from n × spent_time to (n+1) × spent_time. Concretely:

spent_time	slots	first	last
01:00:00	24	0:00:00-1:00:00	23:00:00-24:00:00
00:30:00	48	0:00:00-0:30:00	23:30:00-24:00:00
02:30:00	9	0:00:00-2:30:00	20:00:00-22:30:00
00:50:00	28	0:00:00-0:50:00	22:30:00-23:20:00

Note the last two rows: 2h30m and 50m do not divide the day evenly, so the last slot ends before midnight and the remainder is dropped.

There is one special case in the loop. When the running total lands exactly on the end of the day, str(timedelta) would render it as "1 day, 0:00:00", giving an ugly 23:00:00-1 day, 0:00:00. The code checks for that exact string and substitutes 24:00:00, which is why the even divisions above end in -24:00:00.

Deciding what is free and what is busy

GET /api/v1/employers/{employer_id}/free-times (in routes/employer.py) puts the generated list to work:

1. Generate the full day with get_times(db_employer.spent_time) — this is all_times.
2. Copy it into free_times.
3. Walk the employer's related queue rows. Each booking carries a time string. Append it to busy_times, and if that exact string is present in free_times, remove it.
4. Return all three lists as {"all_times": [...], "free_times": [...], "busy_times": [...]}.

Occupancy is therefore decided by exact string equality between a booking's time column and a generated slot label. A booking whose time does not match any generated label still shows up in busy_times but removes nothing from free_times — the two lists are built independently.

Data model

Two tables, one-to-many, defined in database/models.py.

employer — the person or desk being booked.

Column	Type	Notes
id	Integer	primary key
name	String	indexed
spent_time	Time	duration of a single appointment; the input to slot generation

queue — one booking.

Column	Type	Notes
id	Integer	primary key
name	String	indexed; the visitor's name
phone_number	String	indexed
employer_id	Integer	foreign key → employer.id
description	String
time	String	the slot label, e.g. "9:00:00-10:00:00"

The relationship is Employer.queues ↔ Queue.employer, wired with back_populates on both sides. An employer holds many queue entries; each queue entry points at one employer.

Pydantic schemas mirroring these live in schemas/employer.py and schemas/queue.py, including EmployerWithQueues, which nests the bookings inside the employer response.

Database access goes through database/orm.py, a hand-rolled generic ORMBase class wrapping get / create / update / delete / all / filter / count / get_or_create. It is instantiated once per model as EmployerDB and QueueDB, so the route handlers stay thin.

API

All routers are mounted under the /api/v1 prefix in main.py.

Employers — /api/v1/employers

Method	Path	Description
POST	/	Create an employer. Returns 201.
GET	/	List employers. Query params skip (≥ 0, default 0) and limit (> 0, default 10).
GET	/{employer_id}	One employer with its nested queue entries.
GET	/read_employer_simple/{employer_id}	The same employer without the nested queues.
GET	/{employer_id}/free-times	The slot calculation above: all_times, free_times, busy_times.
PUT	/{employer_id}	Update name and spent_time.
DELETE	/{employer_id}	Delete an employer.

Queues — /api/v1/queues

Method	Path	Description
POST	/	Create a booking. Returns 201.
GET	/	List bookings, with skip / limit (defaults 0 / 10).
GET	/{queue_id}	One booking, 404 if missing.
PUT	/{queue_id}	Update a booking.
DELETE	/{queue_id}	Delete a booking.

Custom — /api/v1/custom

Method	Path	Description
GET	/{employer_id}	An employer serialized by hand.

routes/custom.py is a learning exercise rather than a production route: it converts a SQLAlchemy instance into a dict by iterating __table__.columns, does the same for the nested queues, and feeds the result into locally redeclared Pydantic models. The regular GET /employers/{id} does the same thing with from_attributes.

Running it

Requires Python 3.10 or newer — the code uses str | None union syntax in the schemas.

python -m venv venv
source venv/bin/activate          # Windows: venv\Scripts\activate
pip install -r requirements.txt

cp .env.example .env              # optional; SQLite is the default

Configuration is a single environment variable, DATABASE_URL, read in database/config.py. When unset it falls back to sqlite:///./sql_app.db. The SQLite-only check_same_thread connect argument is applied only when the URL is a SQLite one, so a PostgreSQL URL works without edits.

Then start the server:

python main.py

main.py starts uvicorn on port 8000 with reload enabled. Interactive docs are at http://127.0.0.1:8000/docs.

You do not run a migration first. main.py calls Base.metadata.create_all(bind=engine) at import time, so the schema — including the time column — is built by SQLAlchemy on startup. There is one migration in alembic/versions/, which adds time to queue and backfills existing rows with '00:00-00:00', but it was written against a database that already existed and it cannot be applied to a fresh one: on an empty database it fails with no such table: queue, and after create_all it fails with duplicate column name: time. If you want Alembic to be usable for future migrations, mark the existing revision as already applied instead of running it:

alembic stamp head

Honest limitations

This was written to learn the stack, and it shows in places worth naming up front:

• No date dimension. A booking stores a slot label but not a day. Free/busy is therefore computed across every booking an employer has ever had, not per calendar date.
• No double-booking protection. Nothing stops two queue rows from claiming the same slot, and nothing validates that a submitted time is one of the generated labels. Matching is raw string comparison.
• Slots always start at midnight and cover 24 hours. There are no working hours, no breaks and no weekends — the day is sliced from 00:00 regardless of whether anyone works at 03:00.
• spent_time = 00:00:00 raises ZeroDivisionError in get_times; the duration is never validated.
• spent_time is a Time column used as a duration, which is a type mismatch. An interval or an integer number of minutes would model it properly.
• DELETE handlers declare status_code=204 but return a JSON body, which contradicts the no-content status.
• Error handling is uneven. ORMBase.create catches IntegrityError, logs it and returns None, so a conflict surfaces as a response-model validation failure rather than a clean 4xx. ORMBase.update raises ValueError for a missing id instead of a 404.
• No authentication or authorization. Every endpoint is open.
• The single Alembic migration cannot be applied to a fresh database (see above); schema creation in practice relies on create_all at startup, so the two mechanisms overlap.
• No tests, no pinned dependency versions, and a few leftover print calls and commented-out experiments in the route and ORM code.
• Some inline comments are in Uzbek.

Credits

Written by Ilyosxon Maxmudov.