Computed Fields

Computed fields derive their values from other data rather than being set directly. They are read-only – you cannot set their values directly. oikapi supports formula, lookup, and rollup computed fields.

Formula fields

Calculate a value from other fields on the same record.

POST /api/apps/{app}/tables/invoices/fields
{
  "name": "total",
  "type": "formula",
  "config": {
    "expression": "mul(quantity,unit_price)",
    "return_type": "decimal"
  }
}

Expressions use a function-based syntax (the same syntax as filters), not infix operators. Arguments are field names, literals, or nested function calls. String literals are quoted with single or double quotes.

Common functions:

Category	Functions
Arithmetic	add, sub, mul, div
String	concat, upper, lower, trim, length
Numeric	round, floor, ceil, abs
Comparison	eq, ne, gt, gte, lt, lte, like, contains, in, is_null
Logical	and, or, not
Date/time	now(), today(), date_add, date_sub
Other	coalesce, if

Examples:

mul(quantity,unit_price)                        # quantity × unit price
concat(first_name,' ',last_name)                # full name
if(eq(status,'active'),1,0)                      # conditional
and(lt(due_date,now()),ne(status,'closed'))     # is overdue

return_type is one of boolean, integer, decimal, text, date, or datetime.

Note

now() and today() are only available in query-time formulas. They cannot be used in stored mode because the value would be frozen at write time.

Lookup fields

Pull a value from a related record via an existing reference field.

POST /api/apps/{app}/tables/orders/fields
{
  "name": "customer_name",
  "type": "lookup",
  "config": {
    "relation": "customer",
    "field": "name"
  }
}

relation is the name of a reference field on this table; field is the field to pull from the related record.

Lookups support multi-level paths – traverse through multiple reference hops by using dot notation in relation:

{
  "relation": "order.customer",
  "field": "name"
}

Lookups are resolved with LEFT JOINs – no N+1 query problem.

Rollup fields

Aggregate values from child records that reference this table.

POST /api/apps/{app}/tables/projects/fields
{
  "name": "task_count",
  "type": "rollup",
  "config": {
    "relation": "tasks.project",
    "function": "count"
  }
}

relation uses the qualified child_table.reference_field form: the child table you aggregate over, and the name of the reference field on that child table that points back to this table. In the example above, tasks.project means “aggregate rows in the tasks table whose project reference points at this record.”

The qualified form is required to disambiguate when two different child tables carry a like-named back-reference (e.g. both software_installs.entitlement and entitlement_allocations.entitlement point back to entitlements).

Aggregation functions:

Function	Description
count	Number of related records
sum	Sum of a numeric field
avg	Average of a numeric field
min	Minimum value
max	Maximum value
string_agg	Concatenate text values

For sum, avg, min, max, and string_agg, specify the field to aggregate. You can also filter the child records and set a separator for string_agg:

{
  "relation": "invoices.customer",
  "function": "sum",
  "field": "amount",
  "filter": "eq(status,'posted')"
}

Note

A rollup filter that traverses a reference — a dotted field reference such as eq(customer.tier,'gold') — is applied only at query time. Combining such a reference-traversal filter with store: true returns a validation error, because the stored recompute path does not evaluate it and the materialized value would drift. Plain filters on the child table’s own columns (like eq(status,'posted') above) work in both modes.

Query-time vs stored

Computed fields support two execution modes, selected with store in the config:

Mode	When calculated	Pros	Cons
Query-time (default)	On every read	Always up-to-date	Slower reads on complex aggregates
Stored (store: true)	When source data changes	Fast reads	Recommended for frequently filtered/sorted aggregates

In stored mode the value is materialized into a physical column and kept current by a background recompute worker.

Note

Stored mode is currently available for rollup fields. Setting store: true on a formula or lookup field returns a validation error – those types are query-time only. Time-dependent expressions (now(), today()) are never allowed in stored mode.

Filtering and sorting

Computed fields can be filtered and sorted like regular fields. For complex rollups, oikapi wraps the query in a subquery to support this.