A tenancy contract looks like a simple record: a tenant, a unit, a start date, an end date, a rent amount. You could build the table in five minutes.

And that's the trap. Because behind that simple record is a legal agreement, real money and real people's homes and businesses. A wrong date isn't a cosmetic bug. It can mean a wrong invoice, a unit shown as free when it isn't, or a dispute nobody can settle because nobody knows what the record said last month.

Kate PMS, the property management system I lead the development of, now holds more than 2,000 tenancy contracts across a multi-property portfolio, with staff, tenants and e-signing all touching them. Building the contract module taught me more about data integrity than any tutorial. Here are the lessons I'd pass on to anyone building something similar, whether it's contracts, bookings, subscriptions or orders.

Lesson 1: validation in the form is a suggestion

Form validation is for the user's benefit: clear messages, fast feedback. It is not a guarantee. Data arrives through APIs, mobile apps, imports, queued jobs, admin tools and the occasional script someone runs in a console.

So the rules that must never be broken belong as close to the data as possible:

• In the database: NOT NULL, foreign keys, unique constraints, sensible column types (a DATE for dates, a DECIMAL or integer minor units for money, never a float).
• In the domain code: one place that creates and changes contracts, used by every entry point. Not five controllers each doing it slightly differently.
• In the form: friendly versions of the same rules, for the human in front of the screen.

If a rule only lives in the form, it doesn't really exist.

Lesson 2: a status column is a state machine, whether you admit it or not

A contract moves through a life: draft, waiting for signature, active, then expired or terminated. The dangerous bugs aren't in the states. They're in the transitions nobody thought about. Can a terminated contract become active again? Can a draft skip signing? If the code allows it, eventually someone will do it, usually by accident.

Make the allowed transitions explicit, in one place:

public function canTransitionTo(self $next): bool
{
    $allowed = match ($this) {
        self::Draft => [self::PendingSignature, self::Cancelled],
        self::PendingSignature => [self::Active, self::Draft, self::Cancelled],
        self::Active => [self::Expired, self::Terminated],
        self::Expired, self::Terminated, self::Cancelled => [],
    };

    return in_array($next, $allowed, true);
}

This lives on the status enum, and every status change goes through a method that checks it and throws if the move isn't allowed. A simplified example, but the principle scales: the lifecycle is written down in code, not spread across if statements in the UI.

Lesson 3: overlapping contracts need a lock, not just a query

One unit should never have two active contracts for the same dates. The check itself is the classic date-overlap condition: two ranges overlap when each one starts before the other ends.

The catch is concurrency. Two staff members create contracts for the same unit at the same moment. Both run the check, both see no overlap, both insert. A plain "check, then insert" has a gap in the middle. Locking the unit row for the duration of the transaction closes it:

return DB::transaction(function () use ($data) {
    Unit::whereKey($data['unit_id'])->lockForUpdate()->firstOrFail();

    $overlaps = Contract::where('unit_id', $data['unit_id'])
        ->whereIn('status', [ContractStatus::PendingSignature, ContractStatus::Active])
        ->where('start_date', '<=', $data['end_date'])
        ->where('end_date', '>=', $data['start_date'])
        ->exists();

    if ($overlaps) {
        throw ValidationException::withMessages([
            'start_date' => 'This unit already has a contract for these dates.',
        ]);
    }

    return Contract::create($data);
});

The second request now waits for the first to commit, then sees its contract and fails cleanly. PostgreSQL can enforce this with an exclusion constraint on a date range. MySQL can't, so the lock is the next best thing.

Lesson 4: signed means frozen

Once a contract is signed, its terms are a historical fact. Editing the rent on a signed contract doesn't change what was agreed; it just makes your database lie about it.

The safer model is to treat signed contracts as immutable and represent change as new records: a renewal is a new contract linked to the previous one, and an amendment is its own signed document. Your history then answers "what was agreed, and when?" without anyone having to remember.

The same thinking applies to anything derived from a contract, like invoices. Generate them from the contract's terms, store what was generated, and don't silently recalculate the past when someone edits the present.

Lesson 5: if it isn't audited, it didn't happen (or nobody can prove it did)

Sooner or later, someone asks "who changed this end date, and what was it before?". If your answer is a shrug, you have a trust problem, not a technical one.

An audit trail doesn't need to be fancy. For every meaningful change, record:

• who did it (the user, or "system" for scheduled jobs),
• what changed, with the old and new values,
• when, and ideally why (a reason field for sensitive actions like termination).

Whether you use a package or a simple append-only table, the important parts are that it's written in the same transaction as the change and that nobody can edit it afterwards. Combined with role-based access, it also makes conversations with staff easier: the history speaks for itself.

A checklist for any "simple" business record

• Correct column types: real dates, exact money, foreign keys.
• One code path for creating and changing the record.
• Explicit, tested state transitions.
• Concurrency handled with locks or constraints, not hope.
• Past agreements immutable; change recorded as new records.
• An audit trail written in the same transaction.

None of this is glamorous. All of it is what lets people trust the numbers on the screen, and that trust is the whole point of the system.

What's the business record in your system that looked simple and turned out not to be? I'd bet it has a status column.