Formula Columns

Formula columns are virtual, computed columns that use SQL expressions to derive values from other columns. They work like spreadsheet formulas -- the database evaluates the expression at query time and returns the result alongside regular column values.

Key Characteristics

• No physical database column -- nothing is created or altered in your PostgreSQL database
• Always read-only -- values are calculated, never entered manually
• Evaluated by the database -- PostgreSQL computes the result, so expressions are fast and accurate
• Visible in the API -- formula columns appear in query responses just like regular columns
• Per-view -- different views of the same table can have different formula columns
• No migrations needed -- adding, updating, or removing a formula column never triggers a schema migration

Simple Formulas

A formula references column names from the same table using standard SQL expression syntax.

Arithmetic

price * quantity
unit_price * quantity * (1 - discount)
(subtotal + tax) - credit

String Functions

CONCAT(first_name, ' ', last_name)
UPPER(country_code)
TRIM(description)

Null Handling

COALESCE(nickname, first_name, 'Anonymous')
COALESCE(phone, 'Not provided')

Rounding and Math

ROUND(price * 1.15, 2)
ABS(balance)
CEIL(score / 10)

Conditional Logic

CASE WHEN is_active = true THEN 'Active' ELSE 'Inactive' END
CASE WHEN quantity > 100 THEN price * 0.9 ELSE price END

Cross-Entity Formulas

Formula columns can reference columns from related entities using relationship.column syntax. The relationship name must match a relationship defined on the entity.

customer.discount * price
COALESCE(customer.name, 'Unknown')
CONCAT(customer.company, ' - ', customer.city)

For example, if your orders table has a relationship named customer pointing to a customers table, you can reference customer.discount to pull the discount value from the related customer record.

TIP

Cross-entity formulas use Hibernate relationship navigation (JOINs), which is more efficient than subqueries. Define a relationship on your entity first, then reference it in the formula.

Available SQL Functions

Formulas support standard SQL functions that work in PostgreSQL:

Category	Functions
String	CONCAT, UPPER, LOWER, TRIM, LENGTH, SUBSTRING, REPLACE
Numeric	ABS, ROUND, CEIL, FLOOR, MOD, SQRT, POWER, GREATEST, LEAST
Null handling	COALESCE, NULLIF
Conditional	CASE WHEN ... THEN ... ELSE ... END
Type conversion	CAST(column AS type)
Date	CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, EXTRACT(YEAR FROM ...)

Creating a Formula Column

Use the standard column creation endpoint. The presence of a formula field makes it a computed column:

POST /api/columns
Content-Type: application/json

{
  "viewUuid": "your-view-uuid",
  "displayName": "Line Total",
  "formula": "price * quantity",
  "widgetType": "DECIMAL"
}

No special endpoint is needed -- the same POST /api/columns endpoint handles regular columns, relationship columns, and formula columns.

Updating a Formula

PATCH /api/columns/item/{columnUuid}
Content-Type: application/json

{
  "formula": "price * quantity * (1 - discount)",
  "displayName": "Net Total"
}

Changes take effect on the next query. No migration is triggered.

Deleting a Formula Column

DELETE /api/columns/item/{columnUuid}

Removes the formula column from the view. No database changes are made.

Security

Formula expressions are validated before saving. The following are blocked to prevent SQL injection and data exfiltration:

Blocked SQL statements:SELECT, FROM, WHERE, INSERT, UPDATE, DELETE, DROP, ALTER, CREATE, TRUNCATE, GRANT, REVOKE, UNION, EXEC, EXECUTE

Blocked syntax:

• Line comments (--)
• Block comments (/* */)
• Statement separators (;)

Only column references, arithmetic operators (+, -, *, /), and the whitelisted SQL functions listed above are permitted.

WARNING

Formulas are case-insensitive for security validation. select, SELECT, and Select are all blocked.

Limitations

Read-Only

Formula column values cannot be edited. They are always calculated from the expression.

No Aggregate Functions

Formulas operate on a single row. Aggregate functions like SUM, COUNT, and AVG across multiple rows are not supported in formula expressions. Each formula evaluates independently per row.

Valid SQL Required

The formula is embedded as a SQL expression in the database query. If the expression references a column that does not exist or contains a syntax error, queries to the view will return an error. Test formulas with simple expressions first, then build up complexity.

Filtering and Sorting

Formula columns support filtering and sorting through the API:

GET /api/data/{viewSlug}?filter[lineTotal.gte]=100&sort=lineTotal,desc

Performance

Filtering and sorting on formula columns may be slower than on indexed physical columns because the database evaluates the expression for every row. For frequently filtered values, consider whether a physical column with a database-level default or trigger would be more appropriate.

Schema Sync Behavior

Formula columns are preserved during schema sync and import operations:

• They are not deleted during re-sync (there is no database column to compare against)
• They are excluded from drift detection
• They are excluded from the schema hash, so adding or removing them does not trigger a false drift warning