PostgreSQL

spider new bookings --pg
cd bookings
docker compose up -d db

PG_HOST=localhost
PG_PORT=5562
PG_USER=postgres
PG_PASSWORD=postgres
PG_DB=bookings_development

pub fn main(init: std.process.Init) !void {
    const allocator = init.arena.allocator();
    const io = init.io;

    try db.init(allocator, io, .{});
    defer db.deinit();

    // Applies the migrations that have not run yet: a new database, or a new
    // container, is ready when the server starts. `spider migrate`
    // (SPIDER_MIGRATE=1) only migrates.
    const applied = try migrate(allocator);
    if (spider.migrating()) {
        std.log.info("{d} migration(s) applied", .{applied});
        return;
    }

    try serve(allocator, io);
}

/// Applies the migrations that have not run yet, in order, each in its own
/// transaction with its row in schema_migrations, under a lock: several
/// copies of the app starting at once apply each one once. Returns how many
/// ran. A migration edited after it ran stops it (error.MigrationChanged):
/// add a new one instead. main() calls it, and so can the tests, on a test
/// database.
pub fn migrate(allocator: std.mem.Allocator) !usize {
    var arena = std.heap.ArenaAllocator.init(allocator);
    defer arena.deinit();
    var migrator = spider.app(.{});
    defer migrator.deinit();
    return migrator.migrations(migrations).migrate(arena.allocator());
}

-- migrate:up
-- Needed by the rule below: it lets one constraint compare a plain column
-- (the room) and a range (the time) together.
CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE bookings (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    room_id BIGINT NOT NULL REFERENCES rooms (id) ON DELETE CASCADE,
    day DATE NOT NULL,
    starts TIME NOT NULL,
    ends TIME NOT NULL,
    title TEXT NOT NULL CHECK (char_length(title) BETWEEN 1 AND 80),
    note TEXT CHECK (char_length(note) <= 500),
    booked_by TEXT NOT NULL,
    -- held: waiting to be confirmed, until held_until. confirmed: kept.
    -- cancelled and expired: over; the time is free again.
    state TEXT NOT NULL DEFAULT 'held' CHECK (state IN ('held', 'confirmed', 'cancelled', 'expired')),
    held_until TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),

    -- The day runs from 08:00 to 20:00, in half hours.
    CONSTRAINT bookings_within_the_day CHECK (starts >= '08:00' AND ends <= '20:00' AND starts < ends),
    CONSTRAINT bookings_on_the_half_hour CHECK (
        extract(minute FROM starts) IN (0, 30) AND extract(minute FROM ends) IN (0, 30)
        AND extract(second FROM starts) = 0 AND extract(second FROM ends) = 0
    ),

    -- The rule of the whole app: a room cannot have two bookings at the same
    -- time. Only bookings that hold the room count (held or confirmed), and
    -- two that touch (one ends at 10:00, the next starts at 10:00) do not
    -- overlap. The database refuses the second one whoever asks and however
    -- close together the two requests arrive.
    CONSTRAINT bookings_do_not_overlap EXCLUDE USING gist (
        room_id WITH =,
        tsrange(day + starts, day + ends) WITH &&
    ) WHERE (state IN ('held', 'confirmed'))
);

CREATE INDEX bookings_by_day ON bookings (day, room_id);

-- migrate:down
DROP TABLE IF EXISTS bookings;

/// Where a booking is in its life.
pub const State = enum {
    /// Made, and waiting for its owner to confirm it. It holds the room
    /// until `held_until`.
    held,
    /// Kept: the room is taken.
    confirmed,
    /// Given up by its owner. The time is free again.
    cancelled,
    /// Never confirmed. The time is free again.
    expired,
};

/// One booking, as the pages show it.
pub const Booking = struct {
    id: i64,
    room_id: i64,
    /// The room's name.
    room: []const u8,
    /// "2026-10-12"
    day: []const u8,
    /// "09:00"
    starts: []const u8,
    /// "10:30"
    ends: []const u8,
    title: []const u8,
    note: ?[]const u8 = null,
    booked_by: []const u8,
    /// The account that made it; null for one made before accounts existed.
    user_id: ?i64 = null,
    state: State,
    /// For a held booking: whole minutes until the hold ends, 0 once it
    /// has. null in every other state.
    minutes_left: ?i32 = null,
    /// Made in the last few seconds: the day view points it out.
    fresh: bool = false,
};

const columns =
    \\b.id, b.room_id, r.name AS room,
    \\b.day::text AS day,
    \\to_char(b.starts, 'HH24:MI') AS starts,
    \\to_char(b.ends, 'HH24:MI') AS ends,
    \\b.title, b.note, b.booked_by, b.user_id, b.state,
    \\CASE WHEN b.state = 'held'
    \\     THEN greatest(0, ceil(extract(epoch FROM b.held_until - now()) / 60))::integer
    \\END AS minutes_left,
    \\b.state IN ('held', 'confirmed') AND now() - b.created_at < interval '8 seconds' AS fresh
;
const from = " FROM bookings b JOIN rooms r ON r.id = b.room_id ";

/// The bookings that take a room on `day` ("2026-10-12"): held and
/// confirmed ones, by room and by time.
pub fn forDay(arena: std.mem.Allocator, day: []const u8) ![]model.Booking {
    const sql = "SELECT " ++ columns ++ from ++
        "WHERE b.day = $1::date AND b.state IN ('held', 'confirmed') ORDER BY r.name, b.starts";
    return db.query(model.Booking, arena, sql, .{day});
}

const from = " FROM bookings b JOIN rooms r ON r.id = b.room_id ";

/// The bookings that take a room on `day` ("2026-10-12"): held and
/// confirmed ones, by room and by time.
pub fn forDay(arena: std.mem.Allocator, day: []const u8) ![]model.Booking {
    const sql = "SELECT " ++ columns ++ from ++
        "WHERE b.day = $1::date AND b.state IN ('held', 'confirmed') ORDER BY r.name, b.starts";
    return db.query(model.Booking, arena, sql, .{day});
}

/// A room people book.
pub const Room = struct {
    id: i64,
    name: []const u8,
    /// How many people fit.
    capacity: i32,
    /// What the room has: "projector", "whiteboard". Empty for a room with
    /// nothing.
    features: []const []const u8,
};

const columns = "id, name, capacity, features";

/// The rooms that have every one of `wanted` ("projector", "whiteboard").
/// An empty list is every room.
pub fn withFeatures(arena: std.mem.Allocator, wanted: []const []const u8) ![]model.Room {
    // `@>` is "contains": the room's list has all of the wanted ones. The
    // list goes in as ONE parameter, whatever its length.
    const sql = "SELECT " ++ columns ++ " FROM rooms WHERE features @> $1 ORDER BY name";
    return db.query(model.Room, arena, sql, .{db.array([]const u8, wanted)});
}

/// Why a booking could not be made.
pub const CreateError = error{
    /// The room already has a booking in that time.
    SlotTaken,
    /// Outside 08:00-20:00, not on the half hour, the end before the
    /// start, or a title that is empty or too long.
    InvalidBooking,
    /// No room with that id.
    NoSuchRoom,
};

/// Makes a booking, held for `model.hold_minutes`. error.SlotTaken when the
/// room is not free then: the database decides, so two requests for the
/// same time cannot both get it.
pub fn create(arena: std.mem.Allocator, input: model.Input) !model.Booking {
    const Work = struct { arena: std.mem.Allocator, input: model.Input };
    // Two statements that must both happen or neither: a hold that ran out
    // is released, then the new booking takes the time. One transaction,
    // on one connection.
    const id = db.transaction(i64, Work{ .arena = arena, .input = input }, struct {
        fn run(tx: *db.Transaction, work: Work) !i64 {
            const in = work.input;
            try tx.query(void, work.arena,
                \\UPDATE bookings SET state = 'expired'
                \\WHERE room_id = $1 AND day = $2::date AND state = 'held' AND held_until < now()
            , .{ in.room_id, in.day });
            return tx.query(i64, work.arena,
                \\INSERT INTO bookings (room_id, day, starts, ends, title, note, booked_by, user_id, held_until)
                \\VALUES ($1, $2::date, $3::time, $4::time, $5, $6, $7, $8, now() + make_interval(mins => $9))
                \\RETURNING id
            , .{ in.room_id, in.day, in.starts, in.ends, in.title, in.note, in.booked_by, in.user_id, @as(i32, model.hold_minutes) });
        }
    }.run) catch |err| switch (err) {
        // What the database said, in the app's words. The names are the
        // rules written in the migration: the EXCLUDE constraint, the
        // CHECKs, the reference to the room.
        error.ExclusionViolation => return error.SlotTaken,
        error.CheckViolation, error.InvalidDatetimeFormat, error.DatetimeFieldOverflow => return error.InvalidBooking,
        error.ForeignKeyViolation => return error.NoSuchRoom,
        else => return err,
    };
    return (try find(arena, id)).?;
}

/// Makes the booking. Refused, the form comes back as it was typed, with
/// what is wrong beside the field it is about.
pub fn create(c: *spider.Ctx) !Response {
    const sent = try c.parseForm(Form);
    const form: Form = .{
        .room = sent.room,
        .date = sent.date,
        .start = sent.start,
        .end = sent.end,
        .title = std.mem.trim(u8, sent.title, " \t\r\n"),
        .note = std.mem.trim(u8, sent.note, " \t\r\n"),
    };

    var errors: Errors = .{};
    if (form.title.len == 0) errors.title = "Give the booking a title.";
    if (form.title.len > 80) errors.title = "The title can have up to 80 characters.";
    if (form.note.len > 500) errors.note = "The note can have up to 500 characters.";
    const start = grid.slotOf(form.start);
    const end = grid.slotOf(form.end);
    if (start == null or end == null) {
        errors.time = "Pick a start and an end between 08:00 and 20:00.";
    } else if (end.? <= start.?) {
        errors.time = "The end has to be after the start.";
    }
    if (errors.any()) return formPage(c, form, errors, .unprocessable_entity);

    const made = repository.create(c.arena, .{
        .room_id = form.room,
        .day = form.date,
        .starts = form.start,
        .ends = form.end,
        .title = form.title,
        .note = if (form.note.len > 0) form.note else null,
        .booked_by = c.params.get("_auth_name") orelse "Someone",
        .user_id = viewerId(c),
    }) catch |err| switch (err) {
        error.SlotTaken => {
            errors.conflict = true;
            errors.time = try conflictMessage(c, form);
            return formPage(c, form, errors, .unprocessable_entity);
        },
        error.InvalidBooking => {
            errors.time = "That day and time cannot be booked.";
            return formPage(c, form, errors, .unprocessable_entity);
        },
        error.NoSuchRoom => {
            errors.time = "Pick a room.";
            return formPage(c, form, errors, .unprocessable_entity);
        },
        else => return err,
    };
    day_view.announce(c, made.day);
    // A form htmx posted gets the address in a header, any other a redirect.
    return c.htmxRedirect(try std.fmt.allocPrint(c.arena, "/bookings/{d}", .{made.id}));
}

test "twenty requests for the same time: exactly one gets it" {
    _ = try app_test.running();
    var arena_state: std.heap.ArenaAllocator = .init(std.testing.allocator);
    defer arena_state.deinit();
    const arena = arena_state.allocator();
    const birch = try roomId(arena, "Birch");

    const Race = struct {
        room: i64,
        won: std.atomic.Value(u32) = .init(0),
        refused: std.atomic.Value(u32) = .init(0),
        other: std.atomic.Value(u32) = .init(0),

        fn ask(race: *@This()) void {
            var mine: std.heap.ArenaAllocator = .init(std.heap.smp_allocator);
            defer mine.deinit();
            if (bookings.create(mine.allocator(), booking(race.room, "2031-03-17", "09:00", "10:00"))) |_| {
                _ = race.won.fetchAdd(1, .seq_cst);
            } else |err| {
                _ = (if (err == error.SlotTaken) &race.refused else &race.other).fetchAdd(1, .seq_cst);
            }
        }
    };
    var race: Race = .{ .room = birch };
    var threads: [20]std.Thread = undefined;
    for (&threads) |*t| t.* = try std.Thread.spawn(.{}, Race.ask, .{&race});
    for (threads) |t| t.join();

    try std.testing.expectEqual(@as(u32, 1), race.won.load(.seq_cst));
    try std.testing.expectEqual(@as(u32, 19), race.refused.load(.seq_cst));
    try std.testing.expectEqual(@as(u32, 0), race.other.load(.seq_cst));
    try std.testing.expectEqual(@as(usize, 1), (try bookings.forDay(arena, "2031-03-17")).len);
}

# A PostgreSQL for the tests only: it keeps its data in memory and is thrown
# away after each run. `./test.sh` starts it, runs the tests and removes it.
# TEST_PG_PORT picks another port on this machine (5563 unless it is set).
services:
  db-test:
    image: postgres:16-alpine
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=postgres
      - POSTGRES_DB=bookings_test
    ports:
      - "${TEST_PG_PORT:-5563}:5432"
    tmpfs:
      - /var/lib/postgresql/data
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U postgres -d bookings_test"]
      interval: 1s
      timeout: 3s
      retries: 30

#!/usr/bin/env bash
# Runs the tests against a PostgreSQL that exists only for this run.
#
#     ./test.sh            every test
#     ./test.sh --summary all   (anything after it goes to `zig build test`)
#
# The tests write to the database they are given, so it is never the
# development one: a container is started with its data in memory, and
# removed when the tests end, whether they pass or not. Needs Docker.
#
# TEST_PG_PORT (5563) and TEST_PROJECT (bookings-test) change the port and
# the name of the containers, when something else uses them.
set -euo pipefail
cd "$(dirname "$0")"

export TEST_PG_PORT="${TEST_PG_PORT:-5563}"
compose() { docker compose -f docker-compose.test.yml -p "${TEST_PROJECT:-bookings-test}" "$@"; }
trap 'compose down --volumes >/dev/null 2>&1' EXIT

compose up -d --wait >/dev/null

PG_HOST=127.0.0.1 PG_PORT="$TEST_PG_PORT" PG_USER=postgres PG_PASSWORD=postgres PG_DB=bookings_test \
    zig build test "$@"

/// What main() does to serve, on the database the tests are given.
fn run() !void {
    const gpa = std.heap.smp_allocator;
    var threaded: std.Io.Threaded = .init(gpa, .{});
    const io = threaded.io();

    // The tests write to the database, so it is never the development one.
    // `./test.sh` starts a PostgreSQL that lives for one run and points the
    // PG_* variables at it; the tables are made here, as main() makes them.
    try spider.pg.init(gpa, io, .{ .database = "bookings_test" });
    _ = try main.migrate(gpa);

    try main.serve(gpa, io);
}

/// `day` ("2026-10-12") as the pages talk about it, or today when it is
/// null or not a date. PostgreSQL does the calendar: the weekday, the day
/// before, the day after, and what time it is where the office is.
pub fn dayInfo(arena: std.mem.Allocator, day: ?[]const u8) !model.Day {
    const sql =
        \\SELECT d::text AS day,
        \\       to_char(d, 'FMDay, FMDD FMMonth') AS label,
        \\       (d - 1)::text AS prev, (d + 1)::text AS next,
        \\       d = t AS is_today, d < t AS is_past,
        \\       to_char(n, 'HH24:MI') AS now,
        \\       (extract(hour FROM n) * 60 + extract(minute FROM n))::integer AS now_minutes
        \\FROM (SELECT coalesce($1::date, (now() AT TIME ZONE $2)::date) AS d,
        \\             (now() AT TIME ZONE $2)::date AS t,
        \\             now() AT TIME ZONE $2 AS n) AS clock
    ;
    const asked = db.queryOne(model.Day, arena, sql, .{ day, timeZone() }) catch |err| switch (err) {
        // Not a date: the page shows today.
        error.InvalidDatetimeFormat, error.DatetimeFieldOverflow => null,
        else => return err,
    };
    return asked orelse (try db.queryOne(model.Day, arena, sql, .{ @as(?[]const u8, null), timeZone() })).?;
}