For: Full Stack · Backend API

Data model

The database tables and how they fit together.

The schema is prisma/schema.prisma. Money is stored as integers in the smallest currency unit; dates of stays are YYYY-MM-DD strings.

Table Holds
Setting One row (id 1): company names, about, contact details, currency, tax %, check-in and check-out times, bookable days ahead, free cancellation days, payment switches, "taking bookings"
Destination A place (Jebel Akhdar, Salalah...): names, region, summary, description, photo, best months, order, visible
Stay A home: destination, type, names, summary, description, house rules (one per line), photos and amenities (JSON arrays), nightly, weekend and cleaning prices, minimum nights, guests, rooms, featured, visible, host
SeasonalRate A price for a stay between a first and last night, with an optional longer minimum stay
StayNight One row per taken night: stay, date, and either a booking or a block. Unique (stayId, date): this is what makes double bookings impossible
Block Nights closed by the team, with a note and who did it
Booking Code, stay, check-in, check-out, nights, guests, guest name, email, phone, country, notes, status, payment method and status, Thawani reference, a copy of every night's price, totals, door code, cancel request, internal note, source, language
BookingEvent The timeline: status, note, who, when
Review Stay, booking (one review per booking), name, country, rating, text, status (PENDING, PUBLISHED, HIDDEN), reply
Inquiry A guest's question: stay (optional), contact details, dates, guests, message, status (NEW, ANSWERED, CLOSED), team note
StaffUser Dashboard accounts: name, email, scrypt password hash, role (OWNER, ADMIN, HOST), active, hosted stays
Destination 1──* Stay 1──* SeasonalRate
                  │  1──* StayNight *──1 Booking | Block
                  │  1──* Booking 1──* BookingEvent
                  │  1──* Review *──0..1 Booking
                  │  1──* Inquiry
StaffUser 1──* Stay (as host)

Why nights are rows

Checking "is any night between check-in and check-out taken?" and then saving is not enough on its own: two requests can both check before either saves. With one StayNight row per night and a unique index, the database itself refuses the second insert. It works the same in SQLite and PostgreSQL, and it makes the calendar a simple query.

Deleting things

  • A stay with bookings cannot be deleted; hide it instead (active: false). Its bookings and their prices stay intact.
  • A destination with stays cannot be deleted.
  • Deleting a block deletes its nights. Cancelling a booking deletes its nights but keeps the booking and its timeline.
  • A dashboard account is deactivated, not deleted, so the timelines keep its name.