Two agreements on one property is a dispute waiting for a date

Contract records accumulate. A property signs an agreement, it is amended mid-term, it renews with revised pricing, a seasonal addendum is added. Each of those is a document, and each document tends to become a record.

The question that matters is which one governs the invoice being generated right now. If your system cannot answer that unambiguously, it will eventually answer it wrongly.

The failure mode

An amended agreement is entered. The original is not closed out, because closing it feels like deleting history and nobody wants to do that. Both now exist, both look active, and the software picks one.

Which one it picks depends on implementation details nobody documented. The lowest identifier. The most recently created. Whichever the query returned first. The client is billed at whatever rate that record carries, and the error surfaces months later when someone compares an invoice to the signed document.

Reconstructing what should have been charged across the intervening period is slow, and the outcome is usually a credit note plus a conversation about whether the provider is paying attention.

Enforce it where it cannot be bypassed

MapleConcierge permits exactly one active agreement per property, and the constraint lives in the database rather than in application code.

That distinction is the whole point. A check in code runs at a moment in time and can be raced, skipped by a different code path, or bypassed by a data import. A unique index is a property of the data itself. Whatever tries to write a second active agreement fails, and it fails at the moment of the write rather than at the moment of the invoice.

Superseded agreements are not deleted. They are marked terminated or expired and remain readable, so historical invoices can still be traced to the document that governed them. History is preserved. Ambiguity is not.

An implementation note worth passing on

Expressing "one active per property" in MySQL requires a small trick, and there is a sharp edge in it.

The usual approach adds a generated column that holds the property identifier when the status is active and null otherwise, then puts a unique index on it. Repeated nulls are permitted in a unique index, so any number of inactive agreements coexist while only one active row can exist.

The sharp edge: if that generated column is declared as stored, MySQL refuses to create a foreign key with cascading delete on the underlying column, and the error it produces names nothing useful. Declaring the column virtual instead solves it. That cost real debugging time and is worth knowing before you spend the same afternoon.

Amendments versus new agreements

Once the constraint exists, a policy question follows: is a mid-term change an amendment to the existing record or a new agreement superseding it?

The practical answer is that anything changing a price should be a new agreement with its own effective date, and anything changing scope without price can be an amendment. Price changes need a clean date boundary so that work performed on either side of it prices correctly, and a new record with an effective date gives you that boundary for free.

The test that this is working: pick any past invoice, and you should be able to name the single agreement record that produced every line on it.