How to store a booking that has no end date without breaking every date query you own

Importing a client’s real occupancy spreadsheet produced a column nobody had modelled. A large share of their bookings were marked “Long Term”, which in their trade means the advert stays up until the client asks for it to come down. No end date. No agreed term. It is a normal way to sell a hoarding and about fifteen of the thirty-eight campaigns in that file were on those terms.

The obvious representation is a null end date. It is also the one that quietly breaks the four things the system does most.

What a null end date costs you

Every query that matters here is an interval comparison. Is this surface committed on a given day. Does this window overlap that window. Which bookings end in the next thirty days. Draw a bar from here to there.

A null on one side of an interval turns each of those into a special case, and the special case is not the same in every query:

• The overlap check has to treat null as “extends forever”, so the condition grows an OR.
• The calendar has to invent a right-hand edge to draw a bar to, and the edge it invents is the window boundary, so the bar redraws differently every time the user pans.
• The expiry list has to exclude nulls explicitly, or a comparison against null silently drops rows and the list looks correct while missing a class of records.
• Sorting by end date has to decide where nulls go, and the answer differs between “soonest freeing first” and “busiest first”.

Four places, four different conventions, all of them invisible until one is wrong. The failure mode is never an error. It is a row that does not appear.

What was done instead

Two things, together, and both are needed.

A boolean on the booking and on each per-surface line saying this is open-ended. That is the truth of the commercial arrangement and it is what the interface reads: the list shows “Ongoing” instead of a date, the calendar draws the bar running off the edge of the window, and the expiring-soon list excludes it because a booking with no agreed end is not expiring.

And a far-future date in the end column. Not a null. A sentinel, so that every interval query already written continues to work untouched — the overlap check, the covering-booking lookup, the availability sort, the calendar geometry. None of them learn a new rule. The record simply looks like a booking with a very long term, which is what it commercially is.

The rule that came out of this: store the value that keeps existing queries correct, and store the meaning separately for the code that has to say it out loud. A sentinel with no flag beside it is a lie waiting to be printed on a client document. A flag with a null beside it is four query rewrites and one of them will be missed.

Where I was wrong about it

My first instinct was that the sentinel was a hack and the null was the honest representation, and that the right answer was to fix the four queries properly. That reasoning holds if the four queries are all the queries. They are not. The overlap check is not a query, it is a rule with money behind it, and it is reached from booking creation, from proposal conversion, from the import path and from the availability endpoint. A convention that has to be re-implemented at each of those is a convention that will be re-implemented differently at one of them.

The second thing I had wrong is that I originally put the flag only on the booking header. The import made it obvious that it belongs on the per-surface line as well: a campaign can hold one long-term board and two that run for a fixed three months. Availability is a property of the surface, so the flag has to travel with the surface. Reading it as “either the line or its parent is open-ended” is the compatibility rule that lets both sides be populated during a migration.

Two related things the import taught

The same spreadsheet forced two more rules that had never been written down, and both are of the same family — a real-world value the model had no room for.

A booked board with no client name against it. Whatever that means administratively, the system cannot invent a counterparty, so the rule became: import the surface, do not fabricate the booking. Missing data becomes fewer rows, never guessed rows.

A long-term booking with no start date. Here a default is defensible, because the fact of the booking is certain and only its origin is unknown, so it takes the start of the current year and the record says so. The distinction between the two cases is whether the missing field is identity or metadata. You may default metadata. You may not default identity.

Before you allow a null in a date range, write down every query that reads the range and check what null does in each. If the answer differs between them, you do not want a null — you want a value your queries already understand and a flag that carries the meaning.

The honest limit: the sentinel date will eventually appear in an export or a printed cost sheet if somebody adds a surface that formats the raw column instead of asking the flag. Nothing prevents that today except the review habit of grepping for the column whenever a new document template is added.