The column that isn't there

Add a column called Line Total, type quantity * unit_price, and it appears in the grid filled in for every row.

Nothing was added to your database. There is no line_total column, no trigger keeping it current, no migration to run and none to undo when you change the expression. The formula travels with the query.

How it works

The expression is validated, its result type is inferred, and it is carried into the SELECT clause of every query for that view. On the generated entity the field is @Transient — Hibernate is told to ignore it for persistence, and the value is projected in at query time.

That has one large consequence in each direction. Nothing can go stale, because nothing is stored: change unit_price and the total is already right, with no recomputation step to forget. And nothing can be indexed, because there is no column to index. A formula is computed per row, per query, every time.

What you can write

Fifteen functions: SUM, AVG, COUNT, MIN, MAX, CONCAT, UPPER, LOWER, ROUND, ABS, COALESCE, LENGTH, TRIM, NOW, and CASE. Arithmetic, comparisons, string literals, and other columns of the same view by name.

The result type is inferred rather than declared — arithmetic gives you a numeric column with a decimal widget, a comparison gives you a boolean — so a formula arrives formatted sensibly instead of as text you then have to configure.

The validator is a blocklist, and that's worth saying

Formulas become SQL, so they are checked before they get anywhere near a database. SELECT, FROM, WHERE, INSERT, UPDATE, DELETE, DROP, ALTER, CREATE, TRUNCATE are rejected, along with ;, --, /* */ and square brackets.

The interesting part is what it does first: string literals are masked before the blocklist runs. So CONCAT(name, ' -- archived') is fine, because the -- is inside a quoted value and the checker knows the difference between data and syntax. An unterminated literal is itself rejected, since that's how you'd otherwise smuggle syntax past the mask.

It is still a blocklist and not a parser, and that's the honest framing. A blocklist is a list of things someone thought of. A grammar is a definition of what's allowed. The second is stronger, and this is the first — which is why the MCP schema tools require full access and why formulas are validated on the way in rather than trusted because they came from a logged-in user.

Aggregates across a relationship

A formula can aggregate the far side of a relationship — the total value of a customer's orders, say. That needs one extra step: bare column names in the expression have to be qualified against the target table rather than the current one, so SUM(total) becomes a sum of the target's total and not something ambiguous.

The rewriting is careful about what it leaves alone. Function names aren't qualified. Numbers and string literals aren't. Keywords inside a condition aren't. Escaped quotes inside a literal survive intact, and so do things that look like blocklisted words when they appear inside quotes — a DATE_FORMAT pattern being the case that caught this out. Eight tests exist purely for those distinctions, because every one of them is a way to corrupt an expression while appearing to succeed.

Every sync used to delete them

Worth admitting, because it was fixed two days ago and it lasted as long as the feature had existed.

A formula column exists only in metadata — there is no database column behind it. The schema hash knew to ignore computed columns. The drift check knew to ignore them. The diff — the code that decides what to delete — did not. So every schema sync saw a column that was "missing from the database" and removed it, taking its validation rules with it. Our own documentation promised they were preserved.

Three code paths, one rule, two of them following it. The fix is a filter and a test; the lesson is that the third path was never compared against the other two.

What it doesn't do (yet)

• Not indexable, not sortable at speed. Sorting a large view by a formula means computing it for every row first. There's no materialised option.
• No cross-view references. A formula can use columns from its own view and aggregate a related table; it cannot reach an arbitrary other view.
• Filtering and sorting a formula both work, but pay for it every time. The REST API accepts a formula field in filter and sort — it is added to the valid-field set explicitly — which means the database evaluates the expression for every candidate row before it can compare or order them. Correct, and not cheap.
• The blocklist will reject valid SQL it hasn't been told about. That is the intended direction of failure, but it means an exotic-but-harmless expression can be refused with no way to override.
• No IF. Use CASE WHEN, which is supported.
• Errors surface at query time, not at save. An expression that validates but fails in the database — a type mismatch, say — breaks the view rather than the editor.
• Aggregates are one hop. Summing across two relationships is not expressible.

The formula columns guide lists the functions with examples, and computed columns in the app covers the editor. If what you want is the related row's label rather than a calculation, that's a relationship column instead.