chatelet/docs/data-model.md

Chatelet — Data Model

Tables live in ext_chatelet.sqlite3 (SQLite) or the chatelet Postgres schema. Defined in ../migrations.py; typed in ../models.py.

Entities

settings (1 row)
   │
rooms ─────────────< bookings
   │                    (guest reservations; amount_sat canonical)
   └──────────────< blocks
                        (manual owner-side unavailability)

settings — one row, castle/operator-wide

Field	Purpose
operator_id	LNbits account whose Nostr signer publishes on the castle's behalf (resolved via resolve_signer — LocalSigner now, NIP-46 bunker later)
relays	relays we publish to / subscribe on
default_hold_minutes	how long a held booking survives before the sweep expires it
deposit_percent	100 = full prepay; <100 = deposit now, balance later
checkin_time / checkout_time	surfaced in listing + check-in DM
cancellation_policy	free-form markdown
publish_availability	mirror blocked dates to a public NIP-52 calendar

rooms — the rentable unit (one NIP-99 kind:30402 listing each)

id (short hash) doubles as the listing's d tag, so re-publishing replaces the same addressable event. Holds price (amount/currency/ frequency map straight onto the NIP-99 price tag), capacity (max_guests, min_nights), presentation (images, amenities→t tags, location, geohash→g tag), status (active/inactive), and listing_event_id (last published event id).

bookings — a reservation (one NIP-78 kind:30078 object each)

id doubles as the reservation object's d tag. Key fields:

• amount_sat — canonical total. Computed once at hold/quote time from price × nights × FX, then propagated as-is to the invoice, the reservation event, and any receipt. Never re-derived from price_fiat downstream (FX drifts, rounding accumulates — workspace source-of-truth rule). price_fiat is a display snapshot only.
• deposit_sat — portion invoiced now (== amount_sat when deposit is 100%).
• status — lifecycle below.
• payment_hash — LNbits invoice covering the deposit.
• request_event_id / reservation_event_id — inbound request and our published reservation object.
• expires_at — hold expiry (set while held/awaiting_payment).

blocks — manual owner unavailability

Maintenance, personal use, off-season. Half-open [start_date, end_date).

Booking lifecycle

          request
             │
             ▼
   ┌───────────────────┐   invoice sent    ┌────────────────────┐
   │       held        │ ────────────────▶ │  awaiting_payment  │
   └───────────────────┘                   └────────────────────┘
        │        │                              │          │
   hold │        │ declined                paid │          │ hold
 expires│        ▼                              ▼          │ expires
        │  ┌──────────┐                  ┌────────────┐    │
        └─▶│ expired  │                  │ confirmed  │◀───┘ (paid before expiry)
           └──────────┘                  └────────────┘
                                               │
                                    check-in   │   cancelled
                                               ▼
                                        ┌────────────┐
                                        │ checked_in │──▶ completed
                                        └────────────┘

OCCUPYING_STATUSES = {held, awaiting_payment, confirmed, checked_in} — the statuses that block a room's calendar. Terminal negatives (declined, cancelled, expired) free the dates.

Availability is derived, not stored

There is no availability table. crud.is_available(room, in, out) returns true iff the room is active and no occupying booking and no block overlaps [check_in, check_out). Half-open intervals mean back-to-back stays (one guest out, next guest in, same day) don't collide.

This keeps a single source of truth: the only way to make dates unavailable is to write a booking or a block. See event-flow.md § Concurrency for the check-then-hold locking requirement.