Data Model

Data Model

The schema is created by Flyway migrations in backend/src/main/resources/db/migration (V1__schema.sql, V2__seed.sql).

erDiagram
    USERS ||--o{ BOOKINGS : makes
    FLOORS ||--o{ CELLS : contains
    FLOORS ||--o{ BOOKINGS : "on"
    CELLS ||--o{ BOOKINGS : "booked as desk"

    USERS {
        bigint id PK
        text username UK
        text password_hash
        text display_name
    }
    FLOORS {
        bigint id PK
        text name
        int rows
        int cols
        text neighbour_mode "ORTHOGONAL or ALL"
    }
    CELLS {
        bigint id PK
        bigint floor_id FK
        int row
        int col
        text type "DESK, WALKWAY, WALL, ROOM"
        text label
        bigint last_seq "latest seq for this desk"
    }
    BOOKINGS {
        bigint id PK
        bigint desk_id FK
        bigint floor_id FK
        bigint user_id FK
        date date
        timestamptz created_at
    }

Loading

Tables

Table	Purpose	Constraints
users	Employees who can sign in	unique username
floors	A floor's grid size and neighbour mode	neighbour_mode in (ORTHOGONAL, ALL)
cells	Every cell of a floor's grid, desk or not	unique (floor_id, row, col); row and col within the floor's size
bookings	One desk booked by one user for one day	unique (desk_id, date); unique (user_id, date); desk_id must point to a DESK cell

Notes

• Cancelling deletes the booking row. That keeps both unique constraints simple, with no need for a "cancelled" status.
• floor_id on bookings is kept alongside desk_id so the spacing check and the snapshot can query one floor and date directly.
• Versioning: a Postgres sequence, desk_status_seq, gives every desk status change (book or cancel) an increasing number called seq. The desk rows are locked while they change, so each desk's seq values always increase in commit order. Clients use seq to drop stale updates. See Real-Time Updates.
• cells.last_seq holds the desk's latest seq. It's written in the same locked transaction as every book or cancel, and the snapshot returns it, so clients can safely drop updates they already reflect.
• Seed data (V2__seed.sql): five users (alice, bob, carol, dave, erin, password password, bcrypt-hashed); Floor 3, 8 × 12, ORTHOGONAL, with column 6 a walkway and desks 3-A1 to 3-H12; and Floor 4, 4 × 6, ALL, desks 4-A1 to 4-D6.
• Indexes: bookings(floor_id, date) for snapshots and spacing checks. The unique constraints cover desk and user lookups by date.