Computed Columns

Computed columns calculate values from other columns in the same row using SQL expressions. They behave like spreadsheet formulas — the value is computed at query time and never stored in the database.

How It Works

A computed column defines a SQL expression that references other columns on the same table. When you query data, the database evaluates the expression for each row and returns the result alongside the regular column values.

For example, if your table has price and quantity columns, you can create a computed column with the formula price * quantity that displays the total.

Computed columns:

• Have no physical database column — nothing is altered in your database
• Are always read-only — values are calculated, not entered
• Are evaluated by the database engine — fast and accurate
• Are visible in the API — included in query responses just like regular columns
• Are per-view — different views of the same table can have different computed columns

Creating a Computed Column

Use the standard column creation endpoint. Include a formula field to make it a computed column:

POST /api/columns

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

No separate endpoint is needed — the presence of a formula field automatically creates a computed column instead of a physical database column. This is the same POST /api/columns endpoint used for regular columns and relationship columns.

Field	Required	Description
displayName	Yes	The column header shown in the UI
formula	Yes	SQL expression referencing column names from the same table
widgetType	No	How to render the result: STRING, NUMBER, DECIMAL, BOOLEAN, etc. Defaults to STRING

Formula Types

CONCAT — String Concatenation

Combine multiple text columns into one.

{
  "displayName": "Full Name",
  "formula": "CONCAT(first_name, ' ', last_name)",

  "widgetType": "STRING"
}

ARITHMETIC — Calculations

Basic math using +, -, *, /.

{
  "displayName": "Line Total",
  "formula": "unit_price * quantity * (1 - discount)",

  "widgetType": "DECIMAL"
}

CONDITIONAL — If/Then Logic

Use SQL CASE WHEN for conditional values.

{
  "displayName": "Status Label",
  "formula": "CASE WHEN is_active = true THEN 'Active' ELSE 'Inactive' END",

  "widgetType": "STRING"
}

COALESCE — Fallback Values

Return the first non-null value.

{
  "displayName": "Display Name",
  "formula": "COALESCE(nickname, first_name, email)",

  "widgetType": "STRING"
}

Supported SQL Functions

Formulas support standard ANSI SQL functions that work across PostgreSQL and MySQL:

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

Updating a Computed Column

Use the standard column update endpoint:

PATCH /api/columns/item/{columnUuid}

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

Changes take effect on the next query — no database migration needed.

Deleting a Computed Column

Use the standard column delete endpoint:

DELETE /api/columns/item/{columnUuid}

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

Limitations

Read-Only

Computed column values cannot be edited directly. They are calculated from other columns.

No Aggregate Functions

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

Formula Must Be Valid SQL

The formula is embedded as a SQL expression in the database query. If the expression is invalid, queries to the view will fail. Test formulas with simple expressions first.

SQL Injection Protection

Formulas are validated before saving. The following are not allowed:

• DDL statements (CREATE, ALTER, DROP, TRUNCATE)
• DML statements (INSERT, UPDATE, DELETE)
• Comments (--, /* */)
• Statement separators (;)
• Execution commands (EXEC, EXECUTE)

Filtering and Sorting

Computed columns support filtering and sorting just like regular columns. The formula expression is evaluated by the database, so standard filter operators work:

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

This translates to WHERE (price * quantity) >= 100 ORDER BY (price * quantity) DESC in the generated SQL.

Performance Note

Filtering and sorting on computed columns may be slower than on indexed columns, since the database evaluates the expression for every row. For frequently filtered computed values, consider creating a database-level generated column or index.

Cross-Table Formulas

Computed columns can reference data from related tables using SQL subqueries. Hibernate's @Formula evaluates the subquery for each row in the SELECT clause.

{
  "displayName": "Customer Name",
  "formula": "(SELECT c.name FROM customers c WHERE c.id = customer_id)",

  "widgetType": "STRING"
}

TIP

For better performance on frequently accessed cross-table values, consider using relationship columns instead, which use JOINs rather than subqueries.

Schema Sync Behavior

Computed columns are preserved during schema sync. When you import or re-sync a workspace:

• Existing computed columns are not deleted (they have no database counterpart to compare against)
• Computed columns are excluded from drift detection (they can't drift — there's nothing in the database to drift from)
• Computed columns are excluded from the schema hash (so adding/removing them doesn't trigger a false "drift detected" warning)

API Response Format

Computed column values appear in query responses alongside regular columns:

{
  "id": 1,
  "firstName": "John",
  "lastName": "Doe",
  "fullName": "John Doe",
  "price": 29.99,
  "quantity": 3,
  "lineTotal": 89.97
}

The fullName and lineTotal fields are computed — they don't exist in the database but appear in every query response.