Criterion D: Development

Structure of the product

PracticePal is a Flask web app divided into modules by responsibility, as in the Criterion C structure chart: views.py only handles pages; rules.py, waitlist.py and reports.py hold the logic; scheduler.py runs timed jobs; db.py alone contains SQL. This lets every rule be unit-tested without a browser, and a database change affects one file. A single app.py would be quicker to write but hard to test.

Technique 1: No double bookings (SC4)

32def make_booking(con, student, room, start, loud):
33    end = start + SLOT                     # every slot is 30 minutes
34    reason = check_rules(con, student, room, start, end, loud)
35    if reason:                              # weekly limit or noise rule
36        return None, reason
37    try:
38        # BEGIN IMMEDIATE takes the write lock BEFORE the final check,1
39        # so two requests for one slot are handled one after the other.
40        # (Idea from a ChatGPT explanation of 'database is locked';
41        # code written by me.)
42        con.execute('BEGIN IMMEDIATE')
43        if db.count_overlaps(con, room, start, end) > 0:   # Q1, rechecked inside the lock2
44            con.rollback()
45            return None, 'Slot just taken'
46        booking_id = db.insert_booking(con, student, room, start, end, loud)
47        con.commit()
48        return booking_id, None
49    except sqlite3.IntegrityError:          # UNIQUE(room_code, start_time): second safeguard3
50        con.rollback()
51        return None, 'Slot just taken'

T5  touching booking ...... PASSED
T6  same slot x200 ........ PASSED 5
    200 runs, 0 duplicates
T14 30 bookings at once ... PASSED
    slowest 0.19 s

make_booking() runs when a student taps Book. BEGIN IMMEDIATE takes the write lock (1); only then does count_overlaps() check for an overlap, using the Criterion C rule (2). A free slot is saved; otherwise the student sees “Slot just taken” (4).

Checking and then inserting let two simultaneous requests both pass. Locking before the final check makes them one step, with a UNIQUE constraint as a second safeguard (3). A Python lock is simpler but fails with several processes. I tried one first: it passed with a single server worker but let duplicates through with two, which confirmed the choice. The cost, saving bookings one at a time, is acceptable when each takes under 0.2 seconds.

T5 (extreme) accepts 12:30 next to 12:00. T6 (abnormal) books one slot twice at once, 200 times: 6 duplicates before the lock, none after (5). T14 (efficiency): 30 simultaneous bookings each took under 0.2 seconds. Together they cover correctness, reliability under load and efficiency.

Technique 2: The weekly limit (SC5)

40def count_week_bookings(con, student, start):
41    """Bookings the student already has in the ISO week (Mon-Sun) of start."""
42    year, week, _ = start.isocalendar()    # ISO week, not US week (fix after T7)1
43    row = con.execute(
44        "SELECT COUNT(*) FROM booking WHERE student_id = ? "
45        "AND iso_year = ? AND iso_week = ? "2
46        "AND state IN ('confirmed', 'checked_in')",   # no-shows and cancellations do not count3
47        (student, year, week)).fetchone()
48    return row[0]

count_week_bookings() counts the student's bookings in the ISO week of the slot. isocalendar() gives the year and week, Monday to Sunday (1); one query counts the matching rows (2), ignoring cancellations and no-shows (3).

Storing each booking's ISO year and week makes this one indexed query. Querying a Monday-to-Monday date range needs no extra columns, but repeats date arithmetic wherever weeks are used. Not counting no-shows was Ms Okafor's decision. The cost is two extra columns that must stay in step with the start time, so only insert_booking() sets them and nothing edits them later.

T7 (extreme) books a third, then a fourth slot in one week, including a Sunday. It first failed: US week numbers start on Sunday, so Sunday bookings counted towards the next week. ISO weeks fixed it, and a unit test now checks the last days of December, when the ISO year can differ from the calendar year.

Technique 3: The noise rule (SC6)

71def load_neighbours(con):
72    """Room graph as a dictionary: room code -> set of rooms sharing a wall."""
73    graph = {code: set() for code in db.room_codes(con)}
74    for a, b in db.neighbour_pairs(con):   # NEIGHBOUR table, one row per wall1
75        graph[a].add(b)                    # undirected: add both directions
76        graph[b].add(a)
77    return graph
78
79def noise_clash(con, graph, room, start, end):
80    """Return the first neighbour with an overlapping loud booking, or None."""
81    for n in sorted(graph[room]):          # only this room's neighbours2
82        if db.count_loud_overlaps(con, n, start, end) > 0:   # Q3
83            return n
84    return None

load_neighbours() turns the NEIGHBOUR table into a dictionary mapping each room to the set of rooms sharing a wall, adding each wall both ways (1). For a loud booking, noise_clash() checks only that room's neighbours (2) and the message names the clash (3).

Walls are data, not if-statements: if P2 becomes a drum room, the head of music edits a table, not the code. A set gives fast membership tests without duplicates. Refusing any loud overlap in the building is simpler but blocks distant rooms. Corridor noise is not modelled. The weakness is that the table must be right: a missing wall silently allows a clash, so the admin screen lists each room's neighbours for Ms Okafor to check.

T8: a loud booking next to a loud one is refused (abnormal); a quiet one is accepted (normal). Unit tests add extreme cases: a room with no neighbours, and bookings that only touch. A loop also checks that every wall in the table works both ways.

Technique 4: The waiting list (SC7)

18def offer_next(con, room, slot):
19    """Offer a freed slot to the student at the FRONT of its waiting list (FIFO)."""
20    entry = db.first_waiting(con, room, slot)   # ORDER BY joined_at LIMIT 11
21    if entry is None:
22        return None                             # nobody waiting: slot is just free
23    db.set_entry_state(con, entry.id, 'offered', offered_at=now())2
24    mail_offer(entry.student_id, room, slot, expires=now() + OFFER_TIME)   # 15 minutes3
25    return entry.student_id

When a booking is freed, offer_next() reads the waiting list in joined_at order and takes the oldest entry (1), so the table acts as a first-in, first-out queue. The entry becomes “offered” (2) and the student is emailed a 15-minute deadline (3).

A queue matches the rule agreed with Ms Okafor: first to join, first offered. An in-memory collections.deque would be lost on every server restart; the database keeps the queue and shows the head of music who is waiting, for one query per offer. A priority queue was considered, but nothing in the requirements ranks students, and a rule students can predict matters more here than flexibility.

T9 (normal): three students join and the first is offered the freed slot. Extreme cases accept at 14:59 and expire at 15:00, passing the offer on. Joining twice (abnormal) is refused. A fake clock avoided real waiting, and T9 was repeated after a server restart to show the queue survives.

Technique 5: Releasing no-shows (SC8)

12def release_no_shows(con):
13    """Run every 5 minutes by APScheduler (set up in app.py)."""
14    cutoff = now() - timedelta(minutes=GRACE)          # GRACE = 101
15    late = db.unchecked_bookings(con, before=cutoff)   # Q6: confirmed, no check-in
16    for b in late:
17        db.set_booking_state(con, b.id, 'no_show')     # kept for the usage report2
18        waitlist.offer_next(con, b.room_code, b.start_time)   # technique 43
19    con.commit()
20    log.info('released %d no-shows', len(late))

12:35:00  released 0 no-shows
12:40:00  released 0 no-shows
12:45:00  released 1 no-shows   P5 12:30 4
12:45:00  offer sent: P5 12:30 -> student 1043

APScheduler runs release_no_shows() every five minutes. It finds confirmed bookings that started more than ten minutes ago (1) without a check-in, marks each as a no-show rather than deleting it (2), and offers the slot to the waiting list (3).

A fixed-interval job keeps no state, so it survives restarts. A timer per booking would be exact, but every timer would be lost when the server restarts each night. The cost is precision: a booking may be released up to five minutes late. In hindsight five minutes was too generous for 30-minute lunchtime slots; a one-minute interval would cost little and is evaluated under SC8.

T10 (extreme): a booking without a check-in must be kept at +9 minutes and released after +10. It was released at +12 (4), revealing the delay. Running the job twice (abnormal) never records a no-show twice. The log showed each run takes under 50 ms, so a shorter interval is feasible.

Technique 6: The usage report (SC10)

9def usage_report(con, year, week, path):
10    """Write one CSV row per room for an ISO week (SC10)."""
11    rows = db.week_usage(con, year, week)   # GROUP BY room: bookings, minutes, no-shows1
12    with open(path, 'w', newline='') as f:
13        out = csv.writer(f)                 # quotes any field containing a comma
14        out.writerow(['room', 'bookings', 'used_%', 'no_shows'])
15        for r in rows:
16            avail = db.available_minutes(con, r.room, year, week)   # 3,000 minus blocked time
17            used = 100 * r.minutes / avail if avail else 0   # room blocked all week2
18            out.writerow([r.room, r.bookings, round(used, 1), r.no_shows])
19    return path

room  bookings  used_%  no_shows
P1    41        68.3    3
P2    37        61.7    1
P3    0         0       0    3
P5    52        86.7    4

usage_report() writes one CSV row per room for an ISO week. One grouped query returns each room's bookings, minutes and no-shows (1); the percentage used is minutes booked divided by minutes available, 3,000 less any blocked time (2).

Grouping in SQL lets the database count, so Python handles one row per room. The csv module, rather than joined strings, stops a comma in a room name breaking the file. CSV opens in Excel, which Ms Okafor uses; a PDF looks better but cannot be filtered. The grouped query relies on SQLite's date functions, so another database would need it rewritten; for one school that is an acceptable risk.

T12 uses a busy week (normal) and an empty week (extreme), checked against hand counts. A room blocked all week caused a division by zero; the guard now reports 0% (3). Totals were checked against the bookings table so that no booking is counted twice.