Stop wrong data: limits, checks and guards

A limit is a rule over a table's items together, which no single field can hold: a stylist is never booked twice at the same time, an event never sells more tickets than it has seats, no two companies share a tax id. You ask the assistant for a limit, and the base's builders see each one on the table's Limits page in Builder.

1. Ask the assistant for a limit in your own words, such as "A stylist can't have two appointments at the same time.", "Stop selling tickets once an event is full." or "No two companies with the same tax id in a country.", and click Apply on its card.
2. In Builder, open the table. Its Limits page lists each limit as a sentence, such as "No two leases with the same property overlap (start to end)", followed by the items it counts when it leaves some out.
3. To remove a limit, click the bin beside it. A box asks "Remove the limit property_free of Leases?"; click Remove limit. With other changes to the table not yet saved, save them first.

The three kinds of limits

• No overlaps: no two items with the same value in some fields, such as the same stylist or the same table, cover the same time. The time is a start and an end, or a start and a number of minutes held in a field, such as an appointment's duration. With a date and time, one appointment may end at 11:00 and the next start at 11:00. With dates alone both days count, so a lease that ends on 30 June and one that starts on 30 June overlap. Tick The end day is free on a limit between two dates, and one item can start on the day another ends: a guest arrives on 30 June, the day the last guest leaves that room. The limit's sentence then says so, such as "No two bookings with the same room overlap (arrival to departure, the end day free)".
• At most: the items that link to another add up to at most one of its numbers. An event's orders, counting each order's quantity, reach at most its capacity; a course's enrolments, one each, at most its capacity. An at most limit may count only the items at the same time: the rental lines of a piece of equipment for the same days, against how many of it you own. It may also count another table's items: an event's ticket requests with no order yet.
• Unique: no two items with the same values of several fields together, where a single field's Unique switch holds one. The shop's customers: no two companies with the same country and tax id, while two people may share them. Or "only one default": no two items with Is default on.

Any limit may leave some items out, such as cancelled bookings or customers who are people, which then block nothing. Each limit can carry its own sentence, such as "Another company has this tax id", which the refusal shows in place of the one Base Captain writes. The sentence of an at most limit may hold {{left}}, which shows what is left under the limit, such as "Only {{left}} places are left for this event.": the refusal then says "Only 3 places are left for this event.", and the other lines of the same order do not lower that number.

What a refused save says

A save that would break a limit is refused, whoever makes it and from wherever: the item's form, a change of many items at once, a drag on a board or a calendar, an import, a public form, an agent or the assistant. The form says why and links the item in the way: "This appointment overlaps APT-00012.", with Open APT-00012, or "This would take Summer Gala past its capacity of 20 (20 taken)."

• A unique limit names the item that already has those values.
• A limit with a sentence of its own says that instead.
• Someone who may not see that item reads only "This appointment overlaps another appointment.", and so does a public form's visitor. Without {{left}}, the visitor of a public form reads a sentence with no number.
• An import lists the row with its failed rows, and an agent whose change is refused says so in its reply.

Limits and details

• An item with its start, its end or one of the limit's fields empty blocks nothing.
• Two bookings made at the same moment give one booking and one refusal.
• A new no overlaps or unique limit, or a change to one, is refused while items already break it, with how many, such as "3 appointments already overlap another; change them first" or "3 companies already share these values; change them first".
• An at most limit is not checked against the items already there, and lowering an event's capacity below what it has sold is not refused: the next order is.
• The apps with bookings come with their limits: Hair salon, Physiotherapy clinic, Restaurant reservations, Party equipment rental, Event tickets, Language school, Property management and Guesthouse (Start from an app and add or remove modules). The example shop's customers have the unique one: try a second company with the country and tax id of another.

A check is a rule each item of a table meets, such as an order that is never placed in the future or a coupon that ends after it starts; a save that breaks the check is refused. The example shop's Coupons have one: Valid until may not come before Valid from. The base's builders add checks.

1. In Builder, open the table, then its Checks page, beside Limits, and click Add check.
2. Give it a Name: lowercase letters, digits and underscores, such as not_in_future.
3. Write its Condition in SQL, the language of the base's database, over the item's fields by their keys: what every item must meet, such as placed_at <= now(), placed at no later than now.
4. Under Shows on, pick the field its message shows under, here Placed at, and write the Message, such as "Can't be in the future".
5. Click Save table.

A save that breaks the check is refused, from wherever it comes: the item's form, a change of many items at once, an import, a public form, a script with an API token, an agent or the assistant. The item's form shows the message on its field, and on a line's field for a line's check; an import lists the row with its failed rows. To change a check, change it on the same page, or click Remove check, and save the table.

What a check's condition can read

A check's condition can read more than the item's own fields:

• Other lists, in a subquery: an order line's quantity <= (select stock from products where id = product), "More than the stock". The example shop has this one as a warning instead, which lets the order through (Warn about a value without stopping the save).
• The item as it was before this save, as old. and the field's key: an order's not (status = 'cancelled' and old.status in ('shipped', 'delivered')), "A shipped order cannot be cancelled". On a new item, old. is empty.
• A line's parent, as p. and the field's key: an order line's not (p.status in ('shipped', 'delivered')), "A shipped order's lines cannot change".
• A line's other lines, as the parent's save holds them all: "This product is already on the order" refuses an order saved with two lines of the same product.
• Today's date: date <= current_date, "Not a date in the future".

The example shop's orders and order lines have these. Base Captain sees which kind a check is; there is nothing to switch.

Limits and details

• A condition the database cannot read is refused when you save the table, with why under its box, such as "A check cannot read total: it is worked out when read" for a check on an order's total, a rollup.
• A check over the item's own fields alone, such as the coupons', is refused when you add it while items already break it, with how many: change those items first.
• A check that reads more holds from the next save on and is not checked against the items already there, nor when an item is deleted.
• Such a check reads the other lists as they are when the save happens, so two saves at the same moment can both pass it. For capacity, never more than there is, use an at most limit (Stop double bookings and overselling with limits), which holds then too.

A warning is a sentence under a field that shows while the form is filled, when something deserves a second look, and refuses nothing: "More than the stock" under an order line's quantity, or "Over the credit limit" under an order's total. In the example shop, type a quantity larger than the product's stock on an order's line: the warning shows under it as you type.

Ask the assistant for a warning in words: "Warn on an order line when its quantity is more than the product's stock". In Builder, the table's Checks page lists its warnings under Warnings, each with its sentence and the field it shows under, such as "Under Quantity, by a rule the assistant wrote". To stop a save, use a check instead (Refuse wrong values with a check (a validation rule)).

Limits and details

• Click Save and the item saves; the message that says so names the warning too.
• A script with an API token, an import, an agent and the assistant get the warnings that held in their answer, so they see them too; an import lists them by row.
• A warning reads what a check reads: the form as it is filled, the item before this save, a line's parent and its other lines, other lists and today.
• A warning on a field you may not see never shows to you.

A guard says when an item may not be changed, deleted or made, or a button may not run, with the sentence users read: an issued invoice "cannot be changed", a shipped order "cannot be cancelled". A guard holds for everyone, the base's builders included, and from wherever the change comes: the item's page, a list's cells, an import, a script with an API token, the assistant or an AI client. The base's builders add guards.

1. In Builder, open the table. The Create items, Edit items and Delete items pages each have Guards, and so does each item button on the Custom actions page.
2. Click Add guard. Write the condition in SQL over the item's fields: what must be true for it to be allowed, such as status = 'draft'. Below it, the sentence users see when it is not, such as "An issued invoice cannot be changed".
3. Click Save table. A condition the database cannot read shows its error under its box.

What each guard does

• Edit: while the guard fails, the item is read-only, its lines too. Edit shows greyed out and pointing at it reads the sentence; a save from anywhere else is refused with it. The item's own buttons and the automations still change the item: that is how Mark paid moves an issued invoice on.
• Delete: while the guard fails, Delete shows greyed out with the sentence.
• Create: a new item that fails the guard is refused with the sentence, whoever makes it, a person, an import, a webhook, a mail or an automation.
• A button's guard: while one fails, the button stays on the page, greyed out, and pointing at it reads why, such as "Only a draft is issued" on an issued invoice. Only when, by contrast, hides the button.

In the example shop, an invoice is changed and deleted only while it is a draft, Issue needs a draft with lines, and no invoice is made, changed or deleted in a closed month (Keep rates and the closed month in a settings table): "The month is closed". On an issued invoice, Mark, a salesperson, and the admin see the same greyed out Edit, Delete and Issue. A shipped order "cannot be cancelled".