# Formula Fields

> A column computed from an expression over the record’s other fields — and the display properties that decide how the result reads.

A `formula` field stores nothing a user types. Its value is computed from an expression referencing other fields in the same record, and it recomputes whenever one of those inputs changes.

```yaml
- { id: 1, name: total_price, type: formula, formula: 'price * quantity', resultType: number }
```

| Path | Kind | Values | Default | Description |
| --- | --- | --- | --- | --- |
| `id` | number |  |  | Unique identifier for a field within a table. Examples: 1, 2, 3, 100 |
| `name` | string |  |  | Internal identifier for the field: the database column name, and the key used in API payloads and formulas. Use `label` for the name end users read. |
| `label` | string |  |  | External display name shown to end users, in place of the internal `name`. Resolution order on every surface: surface-level override, then this label, then the raw `name` verbatim. |
| `description` | string |  |  | Author-written guidance rendered beside the field (under the control on a form, beside the value in a drawer) and associated with the control via aria-describedby. Unlike a placeholder it persists once the user starts typing. |
| `required` | boolean |  |  | Rejects a record whose value for this field is missing or empty, both through the API and in any generated form. |
| `unique` | boolean |  |  | Rejects a record whose value for this field is already used by another record in the same table. |
| `indexed` | boolean |  |  | Adds a database index on this field, so filtering and sorting on it stay fast as the table grows, at the cost of slightly slower writes. |
| `type` | enum | `formula` |  | Constant value 'formula' for type discrimination in discriminated unions |
| `formula` | string |  |  | Formula expression to compute the value. Supports field references, operators, and functions. |
| `resultType` | string |  |  | Expected data type of the formula result |
| `format` | string |  |  | Display format for the result (e.g., currency, percentage) |
| `currency` | string |  | USD | ISO 4217 three-letter currency code (e.g., USD, EUR, GBP) |
| `precision` | number |  | 2 | Number of decimal places (0-10, default: 2 for most currencies) |
| `symbolPosition` | enum | `before`, `after` |  | Position of currency symbol relative to the amount |
| `negativeFormat` | enum | `minus`, `parentheses` |  | Format for displaying negative amounts |
| `thousandsSeparator` | enum | `comma`, `period`, `space`, `none` |  | Character used to separate thousands |

## Money computed by a formula

A formula over currency fields is money, but it does **not** inherit the currency of the fields it references. An expression may touch several currency fields or none at all, so there is nothing to inherit from without guessing. Declare the code:

```yaml
- id: 12
  name: unit_price
  type: currency
  currency: EUR
  precision: 2
- id: 27
  name: stock_value
  type: formula
  formula: unit_price * stock_on_hand
  resultType: number
  format: currency
  currency: EUR
```

Omit `currency` and the amount renders with the `USD` default — which is how a `stock_value` column came to print `$224,430.90` beside the `€28.63` it was computed from.

The five display properties — `currency`, `precision`, `symbolPosition`, `negativeFormat`, `thousandsSeparator` — are the same ones a `currency` field accepts, and they behave identically here.

## What is checked, and what is not

`resultType` and `format` are free strings rather than closed vocabularies. The conventional values are `string`, `number`, `boolean` and `date` for the first, and `currency`, `percentage`, `decimal` and `date` for the second, but the schema accepts any string and validates none of them — so a typo passes `sovrium validate` and surfaces later as a rendering surprise rather than as an error.

The five currency-display properties are the exception. Those **are** validated, and an invalid value is refused when the configuration is decoded.

`formula` itself is checked for the field references it makes: an expression naming a column that does not exist on the table fails the decode.

## Composing a human-facing reference

`autonumber` deliberately takes no prefix or padding options. A formula is where that presentation belongs:

```yaml
- { id: 3, name: invoice_number, type: autonumber }
- id: 4
  name: invoice_reference
  type: formula
  formula: "'INV-' || LPAD(invoice_number::text, 5, '0')"
  resultType: string
```

Keeping the sequence and its presentation apart means the reference can be restyled without touching the numbers already allocated.

## Not every function reaches SQLite

A formula is translated into SQL, and SQLite — the zero-config default engine — does not provide every function PostgreSQL does. The engine handles the gap in two ways rather than one:

- **Translated.** `GREATEST` and `LEAST` become SQLite's `max` and `min`, so they work on both engines with nothing to change.
- **Refused, loudly.** A formula calling a function SQLite lacks — `to_char`, `date_trunc`, `extract`, `regexp_replace`, `array_length` and others — **stops the boot** when the DDL is generated, naming the function and the dialect. A refused boot naming `to_char` is better than a view that creates cleanly and then rejects every write.

Two things follow. The refusal fires at DDL-generation time, not at `sovrium validate`, so a formula can validate and still stop `start` on SQLite. And the refused list is the MEASURED set rather than an exhaustive one: functions nobody has measured fall through untranslated and fail later, so test a formula on the engine you will deploy on.

Intuition is a poor guide here in both directions. Bun ships SQLite's math extension, so `power`, `sqrt`, `ceil`, `floor`, `mod`, `exp`, `ln`, `log`, `sign` and `trunc` all work — while `repeat` and `strpos`, which look far more primitive, do not.

The `LPAD` example above is a case in point: it is fine today, but a Postgres cast such as `::text` beside it is Postgres syntax, so keep a formula meant to run on both engines to the portable subset.

## A computed column is not editable

`formula` is derived, like `count`, `rollup` and `lookup`. It recomputes from its inputs and is not writable through the records API or a form: a write naming it is refused rather than silently ignored, because a value that looks stored and is not is the worse of the two failures.

## Behaviour

### Array Functions

- Joins array elements with ARRAY_TO_STRING
- Gets unique array elements
- Removes empty elements from array
- Flattens nested arrays
- Slices array elements
- Counts array elements

### Core Formula Functionality

- Creates GENERATED ALWAYS AS column for arithmetic formula
- Performs text concatenation with GENERATED column
- Supports conditional expressions with CASE WHEN
- Applies mathematical functions like ROUND
- Evaluates boolean date logic for overdue detection
- Computes a base-table arithmetic formula on BOTH dialects (SQLite included)
- Computes a formula field ADDED to an existing table (ALTER TABLE ADD COLUMN path)
- Developer reads computed formula values on both dialects

### Date & Time Functions

- Compares date with CURRENT_DATE
- Adds interval to date
- Computes date difference
- Formats date with TO_CHAR
- Parses date from text
- Extracts year from date
- Extracts month from date
- Extracts day from date
- Extracts hour from timestamp
- Extracts minute from timestamp
- Extracts second from timestamp
- Gets day of week
- Gets week number
- Checks if date is weekday
- Counts calendar days between dates
- Compares dates at same precision
- Checks if date is after another
- Checks if date is before another

### Error Handling

- Rejects formula when referenced field does not exist
- Rejects circular formula dependencies
- Rejects formula with invalid syntax
- User can complete full formula workflow (regression)

### Logical Functions

- Evaluates IF with CASE WHEN expression
- Evaluates OR logical operator
- Evaluates XOR logical operator
- Evaluates SWITCH with CASE expression
- Returns TRUE boolean constant
- Returns FALSE boolean constant
- Returns NULL with BLANK expression
- Handles error with custom expression
- Detects errors with guarded expression
- Checks for blank with IS NULL
- Uses COALESCE for default values

### Mathematical Functions

- Computes absolute value with ABS function
- Computes average with inline calculation
- Rounds up with CEIL function
- Rounds to nearest even number with EVEN formula
- Computes exponential with EXP function
- Rounds down with FLOOR function
- Truncates to integer with TRUNC function
- Computes logarithm with LOG function
- Computes natural logarithm with LN function
- Finds maximum with GREATEST function
- Finds minimum with LEAST function
- Computes modulo with MOD function
- Rounds to nearest odd number with ODD formula
- Computes power with POWER function
- Rounds down with TRUNC for precision
- Rounds up with precision calculation
- Computes square root with SQRT function
- Computes sum of multiple fields
- Converts text to number with CAST
- Counts non-null values with CASE expression
- Returns first non-null value with COALESCE
- Decimal-result formula over INTEGER columns does not truncate division (GENERATED column path)
- Decimal-result formula over INTEGER columns does not truncate division (trigger / formula-chain path)

### Record Metadata Access

- Returns record ID
- Returns created timestamp
- Returns last modified timestamp

### Operators & Edge Cases

- Computes modulo with % operator
- Handles NULL in arithmetic
- Handles division by zero
- Coerces number to text
- Coerces text to number
- Handles nested function calls
- Concatenates with & operator equivalent
- Compares with = operator
- Compares with != operator
- Compares with < operator
- Compares with > operator
- Compares with <= operator
- Compares with >= operator
- Applies unary minus operator
- Respects parentheses grouping
- Mixes arithmetic and text
- Coerces boolean to text
- Coerces date to text
- Handles empty string
- Handles whitespace in formulas
- Handles case sensitivity in field names
- Handles reserved word escaping
- Handles long formula expressions
- Handles deeply nested expressions
- Supports multiple formula fields
- References another formula field
- Handles all NULL inputs

### Regex Functions

- Matches regex pattern
- Extracts regex match
- Replaces with regex

### Formula Fields That Reference Rollups

- Boots and computes a formula referencing rollup fields (`sum_heures + sum_minutes / 60`)
- Computes a formula-over-formula referencing a view-computed formula (`prepaid - total`)
- Recomputes view-computed formulas after a child row is inserted (no stale/null values)
- Computes a `GREATEST()` formula over a rollup on both dialects (clamped and pass-through)
- User can complete full formula-over-rollup workflow on both dialects (regression)

### String Functions

- Concatenates text with double-pipe operator
- Extracts left characters with LEFT function
- Extracts right characters with RIGHT function
- Extracts substring with SUBSTR function
- Computes string length with LENGTH function
- Converts to lowercase with LOWER function
- Converts to uppercase with UPPER function
- Removes whitespace with TRIM function
- Finds substring position with STRPOS function
- Returns null for not found with NULLIF pattern
- Replaces substring with OVERLAY function
- Substitutes all occurrences with REPLACE function
- Repeats text with REPEAT function
- Converts to text with CASE expression for T
- Splits text into array with STRING_TO_ARRAY
- Converts ASCII code to character with CHR
- Converts character to ASCII code with ASCII
- Encodes to base64 with ENCODE function
- Decodes from base64 with DECODE function
- URL encodes with custom expression

### Trigger-Computed Formula Fields

- A record created through the records API leaves a volatile (`CURRENT_DATE`) formula column holding a calendar day, not NULL — on both engines
- A formula reading another formula computes both links of the chain on insert — on both engines, with no date function involved

### Real-World Use Cases

- Handles complex nested expression
- Calculates invoice total
- Calculates discount pricing
- Calculates age from birthdate
- Derives status from conditions
- Formats full name
- Generates URL slug
- Tracks deadline status

### Special Fields in Formulas

- Allows formula to reference id without explicit field definition
- Allows formula to reference created_at without explicit field definition
- Allows formula to reference updated_at without explicit field definition
- Allows formula to reference deleted_at without explicit field definition
- Automatically creates deleted_at column on all tables
- Automatically creates index on deleted_at column
- User can complete full special fields workflow (regression)