Picture a Friday afternoon at a brokerage. Pickup is in an hour, the carrier is on the phone, and the rate con hasn’t come back signed. Someone clicks dispatch anyway, because the screen lets them and the load has to move.
Every freight broker workflow has a few steps that must never be skipped. When we started building FreightVero’s TMS with Bronco Logistics, our design partner, the first decision was where those rules would live. We put them in the database. Here’s why, what it cost, and where the rules still have limits.
A rule on a screen only covers that screen
Most software checks rules in the interface. The dispatch button greys out until a document is attached. That works for the person clicking that button.
It doesn’t cover the next path into the data. An integration writes a status. A bulk update runs. A second screen gets built a year later and nobody copies the check. Someone with database access fixes a load by hand. Each path needs its own copy of the rule, and copies drift.
In freight, a skipped step costs real money: a carrier dispatched on an old rate, a load marked delivered with no POD, an invoice held for weeks because the paperwork isn’t there.
What we built instead
FreightVero runs on PostgreSQL. The business rules sit inside the database as constraints, triggers and functions.
A load has ten statuses: New Shipment, Alerted, Carrier Dispatched, Arrived Pickup, Freight On Board, Arrived Delivery, Delivered, Ready to Bill, Billed and Cancelled. There is exactly one way to change a status, a database function called api_transition_load(). The status column itself is protected. The app, an integration and someone typing SQL all go through the same function, and none of them can skip a checkpoint.
When the function refuses, it returns structured reasons, and the screen shows them word for word. The dispatcher sees exactly what’s missing.
The four checkpoints
| To move a load to | The database requires |
|---|---|
| Alerted | An active carrier assignment, the carrier’s compliance status set to Approved, driver and truck details, and tracking accepted (or an authorized manual tracking override with a reason) |
| Carrier Dispatched | The latest rate con version signed. If the rate changed after signature, the new version needs its own signature |
| Freight On Board | A BOL and freight photos. A BOL the brokerage generated itself doesn’t count |
| Delivered | A POD and the signer’s first and last name |
The rules around the checkpoints
The four checkpoints get the attention, but the smaller rules do a lot of the work.
- Carrier fall-offs keep history. Swapping a carrier requires a reason. The old assignment stays on the load as inactive, the old rate con is voided, and the load drops back so the new carrier goes through the checkpoints.
- Rate con versions can’t be edited or deleted. A change makes a new version.
- Load numbers never change. They’re assigned once, safely even when two people create loads at the same moment. A wrong load gets cancelled and re-created.
- Billing rules are locked in at booking. The customer’s paperwork rules are copied onto the load when it’s created, so a later change to the customer record doesn’t rewrite old loads.
- Time corrections leave a trail. Writing directly over a milestone time is rejected, and corrections record the old value, the new value and who made the change.
- The audit log is append-only. It records who, what, when, and the before and after values. The database blocks edits and deletes, whether they come from the app, an admin or anyone with a database connection.
- People and conversations aren’t erased. Users are deactivated instead of deleted, messages are append-only, and each company’s data is isolated at the database level with row-level security on every table.
The full walkthrough is on our workflow enforcement page, and rate con versioning has its own page on rate confirmations.
What it costs us
This approach is slower to build. Changing a rule means a database migration and tests, where a screen-only check is a quick code change. Every refusal needs a reason a dispatcher can act on.
It can also feel strict. You can’t edit a load number. You can’t delete a rate con version. Some brokers will find that annoying on day one. We think it’s the right trade, because the board then means what it says: a load showing Carrier Dispatched had a signed, current rate con when it got there.
Where the rules have limits
I don’t want this post to promise more than the database does, so here are the caveats.
- There is a manual tracking override. A load can reach Alerted without accepted tracking if an authorized user records a reason. The override and the reason go into the audit log. So “no load moves without proof” isn’t literally true, and we don’t claim it.
- Carrier compliance is a recorded status. The checkpoint requires Approved on the carrier record, but today someone sets that status by hand. An automated feed from RMIS is planned and isn’t live yet.
- The database checks that proof exists. It can’t tell whether the proof is accurate. A blurry photo is still a file. People still need to look at the POD.
- Tracking flags run on a schedule. Tracking that goes quiet for 45 minutes gets flagged, and the check runs on a timer, so the flag lands within the hour. More on the live tracking page.
- One brokerage shaped these rules. Bronco Logistics is our one customer today. The next brokerage will bring freight that tests them in new ways, and we expect to adjust.
Why this matters beyond our product
Whatever TMS you run, ask where its required steps are enforced. Try to break the rule during the demo: dispatch without a signed rate con, deliver without a POD, and ask whether an integration could do what the screen won’t.
If the answer is that the screen stops you but an import wouldn’t, you know where the gaps will show up. Our broker TMS buyer checklist has the full demo script.
Try to break a checkpoint in our demo
Bring a real load and try to dispatch it without a signed rate con. We’ll show you what the database says back.