Technical Note: Freeform Reservations with Reservation Index (Bucket Index)
Purpose
This design keeps the booking experience freeform (users choose any start/end time) while adding a derived Reservation Index that converts each approved reservation into standardized time buckets. The index enables fast conflict checks, availability views, and reporting without forcing users into a slot-based UI.
This is a hybrid approach:
Freeform reservation = source of truth
Bucket index = performance and usability accelerator
Goals
Preserve the existing, proven freeform start/end workflow.
Support 100 rooms across 10 communities with community-scoped moderation.
Make conflict detection and “what’s available” queries fast and predictable.
Keep the system simple, Drupal-native, and ECA-friendly.
Non-goals (for now):
Real-time transactional locking for high-contention instant booking.
Complex pricing engines, yield management, or multi-rate policy frameworks.
High-level Architecture
Canonical Entity: Reservation
A reservation represents the user’s requested time range and is the authoritative record.
Entity type
Content type (initially) or custom entity (later if needed).
Core fields
field_room (Entity reference → Room)
field_time_range (Datetime range or Smart Date range)
moderation_state (Draft / Needs Review / Approved / Denied / Cancelled)
uid / requester (standard author, plus optional explicit requester field)
Optional: field_community (stored for convenience; always derivable from Room)
Key rule
Reservations remain freeform forever. The index must never become the “truth.”
Derived Entity: Reservation Index (Bucket Index)
A lightweight entity/table that records which standard time buckets are occupied by an approved reservation.
Entity/table name
reservation_index (or reservation_bucket)
Fields
room_id (reference or integer FK to Room)
reservation_id (reference/FK to Reservation)
bucket_start (datetime, stored in UTC recommended)
bucket_end (optional; can be implied as bucket_start + interval)
Optional: community_id (denormalized for faster community filtering)
Optional: quantity (future-proofing for capacity pooling; default 1)
Index constraints
Add a database index on (room_id, bucket_start) for fast lookups.
Optional uniqueness constraint: (room_id, bucket_start, reservation_id) to prevent duplicates.
Status policy
Only index Approved reservations (recommended).
If you need “soft holds,” you can index Needs Review with a separate flag, but that increases complexity.
Bucket Model
Bucket size (interval)
Choose a standard interval that matches operational reality:
Default suggestion: 30 minutes
Alternate: 15 minutes for more granularity, 60 minutes for simplicity
This interval is a system constant (config) and should not vary per community in Phase 1.
Bucketing algorithm
For a reservation [start, end):
bucket_floor_start = floor(start to interval)
bucket_ceil_end = ceil(end to interval)
Generate buckets:
for t from bucket_floor_start to < bucket_ceil_end step interval:
create index row for [t, t + interval)
Important Buckets are a cover, not a perfect representation. They may “over-block” small partial usage. That is acceptable because buckets are used for fast filtering, with optional final confirmation via true overlap check.
Conflict Detection Strategy
Authoritative conflict check (freeform overlap)
A reservation overlaps another if:
same room
both are Approved
(start < other_end) AND (end > other_start)
This check must exist regardless of the bucket index. It is the ground truth.
Bucket-assisted conflict check (fast pre-filter)
To speed moderation or availability computations:
generate the candidate’s bucket list
query reservation_index for any rows matching (room_id, bucket_start IN candidate_buckets)
if none: likely no conflicts
if some: possible conflict, confirm with authoritative freeform overlap check
Why both
Bucket check is fast and index-friendly.
Freeform overlap check prevents false positives caused by coarse buckets.
Workflow and Automation (ECA)
Trigger points
Reservation Created
Set requester fields
Set moderation state:
If user is a community moderator/manager: optional auto-approve
Else: Needs Review
Reservation Approved
Run authoritative freeform overlap check
If conflict: revert to Needs Review (or Denied) and message moderator
If no conflict: rebuild index buckets for this reservation
Reservation Updated (time range or room changed)
If currently Approved:
Option A (strict): revert to Needs Review and delete index rows
Option B (lenient): rerun conflict check then rebuild index rows
Reservation Cancelled / Denied
Delete index rows for this reservation
Index rebuild logic (Approved only)
Delete existing index rows for reservation_id
Generate bucket rows and insert
If insert fails (rare): log and alert (index can be rebuilt later)
Community Scoping and Moderation
Community boundary
Each Room belongs to exactly one Community.
Enforcement points:
Reservation can only reference a Room in its community.
Moderators can only approve/deny reservations within their community.
Implementation options:
Use Group module for per-community roles (cleanest)
Or taxonomy + custom access rules (lighter, more custom code)
Recommended for simplicity + correctness:
Group-based communities + roles, with Views filtered by group membership.
Query Patterns Enabled by the Index
“What’s booked for Room X on date D?”
Query reservation_index:
room_id = X
bucket_start between day_start and day_end Return bucket rows (and join reservation if needed)
“Find availability windows”
Generate all buckets for the window (e.g., a day)
Subtract occupied buckets from reservation_index
Remaining buckets represent candidate openings
Optional: merge adjacent free buckets into longer windows
Optional: confirm with freeform overlap if precision is required
Reporting
Count booked buckets per room/community (utilization proxy)
Identify peak hours via bucket histograms
Consistency and Repair
Source of truth
If the index is missing or stale, the system must still function using freeform overlap queries.
Repair tool
Provide a Drush command or admin action:
“Rebuild Reservation Index”
for a date range and/or community
deletes and regenerates index from Approved reservations
This makes the index safely “disposable” and keeps ops stress low.
Performance Notes
The index grows with: Approved reservations × buckets per reservation
Typical scale is manageable for 100 rooms with moderate volume, especially with (room_id, bucket_start) indexing.
Write amplification occurs on approval/update, not on read. That’s the trade: faster reads, slightly heavier writes.
Risks and Mitigations
Risk: Bucket false positives (over-blocking)
Mitigation: Always confirm conflicts using authoritative overlap logic before rejecting.
Risk: Index drift
Mitigation: Rebuild on key transitions + provide repair command.
Risk: Timezone confusion
Mitigation: Store bucket_start in UTC and normalize display in user/community timezone.
Risk: Edits to Approved reservations
Mitigation: enforce “edit triggers re-review” or rebuild index + recheck conflicts.
Summary
This design preserves your proven freeform reservation model while gaining slot-like operational benefits through a derived Reservation Index. It stays simple, scales across communities, and fits naturally with ECA-driven moderation. Buckets accelerate queries and reporting, but never replace the freeform reservation as the canonical truth.
