# Database

## Database Engine

PostgreSQL is required because booking overlap prevention needs strong transactional semantics and range/exclusion constraints.

Development runs through Docker Compose. Drizzle ORM owns schema definitions and migrations.

## Core Entities

Planned tables:

- `users`
- `roles`
- `user_roles`
- `sessions`
- `properties`
- `accommodation_types`
- `units`
- `amenities`
- `property_amenities`
- `localized_content`
- `media_assets`
- `seasons`
- `rate_plans`
- `rate_overrides`
- `stay_rules`
- `fees`
- `taxes`
- `promotions`
- `availability_blocks`
- `booking_holds`
- `inventory_locks`
- `bookings`
- `booking_guests`
- `booking_price_lines`
- `payments`
- `payment_events`
- `email_events`
- `contact_enquiries`
- `ical_feeds`
- `ical_sync_events`
- `audit_events`
- `application_settings`

This list can shrink if implementation proves a table is premature, but booking, pricing, audit, and media provenance must not be collapsed into opaque blobs.

## Date Ranges

Stays are half-open ranges:

```text
[check_in, check_out)
```

A checkout date does not block a new check-in on the same date.

## Money

All money is stored as integer minor units:

```text
EUR 125.00 -> 12500
```

No floating-point storage is allowed for pricing or payments.

## Double-Booking Protection

The booking layer must use:

- Transactions.
- Atomic availability revalidation.
- PostgreSQL date range or equivalent generated range representation.
- GiST exclusion constraints or another robust database-level overlap strategy.
- Idempotency keys for booking creation and payment callbacks.

Confirmed bookings, active holds, owner stays, and maintenance blocks must prevent overlaps for the same unit. The implementation uses a unified `inventory_locks` table so cross-table inventory claims are protected by one exclusion constraint instead of relying on UI checks or separate per-table constraints.

## Migrations

Migration files must be versioned. Rollbacks are handled by forward recovery migrations plus database backup restore instructions.