# Get all properties in the city
properties_in_city = db.query(Property).filter(
Property.city.ilike(f"%{city}%")
).all()

# Filter out properties with overlapping bookings
available_properties = []
for property in properties_in_city:
overlapping_bookings = db.query(Booking).filter(
and_(
Booking.property_id == property.id,
Booking.start_date < e...

Booking operations are ubiquitous — we book flats, rooms, cars, appointments, and much more. Yet delivering a performant booking API can be deceptively tricky.

My first attempt was naive: I asked an AI agent (Claude Opus 4.5) to design the data model and implementation end‑to‑end, with the only constraint being Python and FastAPI. The result looked plausible at first glance, but the code hid several serious flaws. Here’s one of them:

# Get all properties in the city
properties_in_city = db.query(Property).filter(
Property.city.ilike(f"%{city}%")
).all()

# Filter out properties with overlapping bookings
available_properties = []
for property in properties_in_city:
overlapping_bookings = db.query(Booking).filter(
and_(
Booking.property_id == property.id,
Booking.start_date < end_date,
Booking.end_date > start_date
)
).first()

if not overlapping_bookings:
available_properties.append(property)

return available_properties

Two problems jump out:

• Using ILIKE '%...%' typically prevents index usage and often triggers a full table scan unless specialized indexing (e.g., trigram or full‑text) is in place.
• Worse, the availability check runs a separate query per property — a classic N+1 pattern that will be painfully slow at scale.

Start with abstractions

A better path is to describe the problem in abstract terms, separate from any domain specifics. What should a general‑purpose booking engine provide?

Core primitives:

1. Bookable entities (flats, cars, rooms, tables — any resource).
2. Booking events with at least a start and end (date/time).

Core operations:

1. Retrieve bookable entities available within a given range.
2. Create and cancel bookings (i.e., mark an entity as booked or free).

After learning that Django’s abstract models might be a fit, I fed these requirements into an LLM. Here’s the result—let’s review it step by step.

The booking event in the suggested model is represented by an abstract model class—it’s a simple container.

class AbstractBooking(models.Model):
start_time = models.DateTimeField()
end_time = models.DateTimeField()
# flexible status field
is_active = models.BooleanField(default=True)

class Meta:
abstract = True

The QuerySet implements the availability search logic. It creates a statement that can be safely used as a filter without risking the N+1 problem that ORMs are notorious for.

class BookableQuerySet(models.QuerySet):
def available(self, start, end, booking_relation_name='bookings'):
"""
Excludes objects that have an active booking overlapping
with the requested range.
"""
# Define the overlap condition
overlap_condition = Q(
**{f"{booking_relation_name}__start_time__lt": end,
f"{booking_relation_name}__end_time__gt": start,
f"{booking_relation_name}__is_active": True}
)
return self.exclude(overlap_condition)

Finally, the bookable mixin, which can make any future model bookable:

class BookableMixin(models.Model):
objects = BookableQuerySet.as_manager()

class Meta:
abstract = True

def is_available(self, start, end):
# Check specific instance availability
# Note: This requires the concrete model to define the reverse relation
return not self.bookings.filter(
start_time__lt=end,
end_time__gt=start,
is_active=True
).exists()

Looks fantastic, right? Not quite. There are a few major limitations that prevent this from being a truly foundational model.

Limitation 1 — Lack of composability

You cannot have a ForeignKey to an abstract model. Since an abstract model does not exist as a table, you cannot create a ForeignKey pointing to it. For example, if you want a separate "rating" model related to bookings, there’s no seamless way to attach it to the abstract base; you’d need GenericForeignKey (complex and slower) or duplicate foreign keys per concrete subtype.

Limitation 2 — Unable to query across child types easily

Because there is no single parent table, you cannot execute a simple query that spans all subtypes (e.g., “get all items ordered by created_at”). To list the 10 most recent items across Flat and Room, you must query each separately, merge in Python, and resort — awkward and inefficient.

Limitation 3 — Tight coupling

Without good composability, teams often drift toward the “God Object” anti‑pattern — stuffing logging, timestamps, status, validation, and more into one base class. That tight coupling makes unrelated changes risky.

Is there a better way?

What should a truly foundational model provide?

• Type‑agnostic: Don’t hard‑code date granularity; allow days, hours, or higher precision.
• Composability: Plug in other foundational concerns (ratings, comments) cleanly.
• True polymorphism: Operate uniformly over all bookable entities (Flats, Cars, Boats) where their shared features apply.
• Performance and scalability: Handle realistic loads efficiently and scale from single‑server to distributed deployments.

Fortunately, with dbzero we’re no longer limited to traditional ORMs or SQL schemas. Here’s a foundational model that might meet the functional and technical requirements.

@db0.memo
class BookingEvent:
def __init__(self, from_date, to_date, object: 'BookableResource', owns_lock: bool = False):
self.from_date = from_date
self.to_date = to_date
self.object = object
self.owns_lock = owns_lock

BookingEvent is minimal — a container that references the booked object plus a flag indicating whether the calendar currently owns the reservation lock.

@db0.memo
class BookableResource:
def __init__(self):
"""
Initialize the BookableResource with indexes for locking.
"""
# Index for start times of locked events
self.__ix_locked_from = db0.index()
# Index for end times of locked events
self.__ix_locked_to = db0.index()

def is_available(self, from_date: Any, to_date: Any) -> bool:
"""
Check if the resource is available for the given date range.

Args:
from_date: The start date of the requested period.
to_date: The end date of the requested period.

Returns:
bool: True if available, False otherwise.
"""
# Check for any overlapping bookings
overlapping = db0.find(
self.__ix_locked_from.select(None, to_date),
self.__ix_locked_to.select(from_date, None)
)
# If there are no overlapping bookings, it is available
return not overlapping

def _add_lock(self, event: 'BookingEvent') -> None:
"""
Add a lock for a booking event.
"""
self.__ix_locked_from.add(event.from_date, event)
self.__ix_locked_to.add(event.to_date, event)

def _remove_lock(self, event: 'BookingEvent') -> None:
"""
Remove a lock for a booking event.
"""
self.__ix_locked_from.remove(event.from_date, event)
self.__ix_locked_to.remove(event.to_date, event)

This class maintains two indexes per object. Per‑object indexing in dbzero favors data locality and makes availability checks fast without scanning the whole world. It lets us check a specific object’s availability or retrieve its past/future bookings efficiently — an important scalability lever.

About is_available:

def is_available(self, from_date, to_date) -> bool:
""" Check if any overlapping bookings exist for the given date range. """
overlapping = db0.find(
self.__ix_locked_from.select(None, to_date),
self.__ix_locked_to.select(from_date, None)
)
return not overlapping

It narrows results by combining two selectors — “starts before to_date” and “ends after from_date” — which is the standard overlap criterion. In dbzero, index.select(a, b) returns items keyed between a and b (inclusive by default). Combining multiple selectors in db0.find(...) typically intersects (logical AND) the candidate sets, while list composition broadens them (logical OR). For exclusions, use db0.no(...). find returns a lazily‑evaluated iterable, so simply testing its truthiness is an efficient existence check.

So we have a base object with a single public method, is_available, ready for future extensions.

Finally, we need a general container - for all our object bookings. What does it look like ?

@db0.memo
class BookingService:
def __init__(self):
"""
Initialize the BookingService with indexes for tracking booked resources.
"""
# Index for start times of booked resources
self.__ix_locked_from = db0.index()
# Index for end times of booked resources
self.__ix_locked_to = db0.index()

def find_booked(self, from_date: Any, to_date: Any) -> Iterable['BookableResource']:
"""
Find resources that are booked within the given date range.

Args:
from_date: The start date of the range.
to_date: The end date of the range.

Returns:
Iterable[BookableResource]: An iterable of booked resources.
"""
# Find resources where the booking overlaps with the requested range
return db0.find(
self.__ix_locked_from.select(None, to_date),
self.__ix_locked_to.select(from_date, None)
)

def find_available(self, from_date: Any, to_date: Any) -> Iterable['BookableResource']:
"""
Find resources that are available within the given date range.

Args:
from_date: The start date of the range.
to_date: The end date of the range.

Returns:
Iterable[BookableResource]: An iterable of available resources.
"""
# Find all BookableResources that are NOT in the list of booked resources
return db0.find(BookableResource, db0.no(self.find_booked(from_date, to_date)))

def add_booking(self, event: 'BookingEvent') -> None:
"""
Add a booking event to the calendar.

Args:
event: The booking event to add.

Raises:
AssertionError: If the booking is already added or the object is not available.
"""
assert not event.owns_lock, "Booking already added to calendar"
assert event.object.is_available(event.from_date, event.to_date), "Object is not available for the given date range"

# Add to calendar indexes
self.__ix_locked_from.add(event.from_date, event.object)
self.__ix_locked_to.add(event.to_date, event.object)

# Lock the object itself
event.object._add_lock(event)
event.owns_lock = True

def remove_booking(self, event: 'BookingEvent') -> None:
"""
Remove a booking event from the calendar.

Args:
event: The booking event to remove.

Raises:
AssertionError: If the booking is not owned by the calendar.
"""
assert event.owns_lock, "Booking not owned by calendar"

# Remove from calendar indexes
self.__ix_locked_from.remove(event.from_date, event.object)
self.__ix_locked_to.remove(event.to_date, event.object)

# Unlock the object itself
event.object._remove_lock(event)
event.owns_lock = False

It might look complex, but it mirrors the per‑object pattern at the calendar level. The interesting function is find_available:

def find_available(self, from_date, to_date):
return db0.find(BookableResource, db0.no(self.find_booked(from_date, to_date)))

It returns an iterable of all BookableResource instances available during the specified dates: “find all BookableResource AND NOT booked in the range.” This kind of composable query is a core dbzero mechanism. Note: it returns a lazy iterable that you can further compose or filter.

And that’s it — our foundational data model is ready.

Does it meet all of the technical requirements?

It meets the logical criteria, but what about the technical ones? Let’s revisit:

Type‑agnostic (pass): We deliberately avoided pinning from_date/to_date types; concrete implementations choose day/hour/ms precision.

Composability (pass): Cleanly composable with other modules (e.g., comments, ratings) without entangling foreign keys across subtypes.

Truly polymorphic (pass): As long as concrete types derive from BookableResource, you can treat them uniformly or specialize as needed.

Performant and scalable (pass): Private per‑object indexes make the model future‑proof. Start on a single server; scale with read replicas; partition across clusters for global workloads.

Why are foundation models so important?

I can give you a couple of reasons:

• AI agents benefit — clearer primitives simplify reasoning and planning.
• Don’t reinvent the wheel for every booking app when base requirements hold.
• Foundational models can be well‑tested and maintained, reducing bugs.
• Performance properties can be analyzed independently of specific domains.
• Treat it as a first‑class citizen in your project — almost like extending your data language with a few lines of code.
• Adapt to many booking domains by deriving concrete types.

In short: this is a path to truly composable applications. Build the model once; plug it into many domain‑specific solutions.

Stay tuned. In Part 2, I’ll cover concrete integrations and show how this foundation allowed LLMs to build a full‑featured booking service with real availability queries, without N+1 traps.