Expressions

Expressions are used within attributes and other constructs to define conditions, default values, etc.

Expressions support logical and comparison operations:

Operators: ==, !=, >, <, >=, <=, in, !, &&, ||, =, +, -, *, /
Aggregate functions: SUM, COUNT, AVG, MIN, MAX, MEDIAN and conditional variants (see list below)
Scalar functions: IF, date extraction, date arithmetic, and DIFF (see list below)
Values: Literals (true, false, null, numbers, strings), identifiers, arrays.
Parentheses: Used for grouping, e.g., (a == b) && (c > d)

All operators, aggregate functions, and scalar functions can be used in @where, @set, @permission, @computed, and @validate expressions.

Arithmetic Operators

Arithmetic operators (+, -, *, /) can be used for calculations in any expression context.

Using in `@set`

update calculateTotal(id) {
    @set(order.total = order.subtotal + order.shipping)
}
 
update applyDiscount(id) {
    @set(order.finalPrice = order.total * 0.9)
}

Using in `@where`

list listHighValueProducts() {
    @where(product.price * product.quantity > 1000)
}
 
list listDiscountedProducts() {
    @where(product.basePrice - product.discount < 50)
}

Using in `@permission`

list listBudgetsWithRemainingBalance() {
    @permission(expression: budget.limit - budget.spent > 100)
}

Using in `@computed`

model Order {
  fields {
    subtotal Decimal
    shipping Decimal
    total Decimal @computed(order.subtotal + order.shipping)
    vat Decimal @computed(order.total * 0.2)
  }
}

Division and zero divisors

Division guards its divisor. When the divisor evaluates to zero, the division yields null rather than raising an error, and the null then propagates through the rest of the expression. This holds wherever an expression is used: @computed, @set, @where, @permission, and @validate.

The consequence to keep in mind is that null is not true. In a @where or @permission expression a predicate that evaluates to null does not match, so the row is left out of the results rather than the read failing. In a model-level @validate rule only a value the database can judge to be false counts as a violation, so a rule that evaluates to null does not block the write. On a non-nullable computed field, the null cannot be stored and the write is refused by the field's not-null constraint.

Use IF when you want a zero divisor to produce a particular value instead:

model Order {
  fields {
    total Decimal
    itemCount Number
    averageItemPrice Decimal @computed(IF(order.itemCount > 0, order.total / order.itemCount, 0))
  }
}

Aggregate Functions

Aggregate functions perform calculations across a collection of related records. They are commonly used with one-to-many relationships and can be used in @computed, @set, @where, @permission, and @validate expressions.

`SUM`

Adds up all numeric values in a collection.

Syntax: SUM(items)

Example usages:

// In @computed
totalValue Decimal @computed(SUM(customer.orders.total))
 
// In @set
update calculateTotalOrderValue(id) {
    @set(customer.totalOrderValue = SUM(customer.orders.total))
}
 
// In @where
list listHighValueCustomers() {
    @where(SUM(customer.orders.total) > 500)
}

`COUNT`

Counts the number of items in a collection.

Syntax: COUNT(items)

Example usages:

// In @computed
orderCount Number @computed(COUNT(customer.orders.id))
 
// In @set
update calculateOrderCount(id) {
    @set(customer.orderCount = COUNT(customer.orders.id))
}
 
// In @where
list listCustomersWithMultipleOrders() {
    @where(COUNT(customer.orders.id) >= 2)
}
 
// In @permission
list listProjectsWithTasks() {
    @permission(expression: COUNT(project.tasks.id) > 0)
}

`AVG`

Computes the arithmetic average of numeric values in a collection.

Syntax: AVG(items)

Example usages:

// In @computed
averageOrderValue Decimal @computed(AVG(customer.orders.total))
 
// In @set
update calculateAverageOrderValue(id) {
    @set(customer.averageOrderValue = AVG(customer.orders.total))
}
 
// In @where
list listCustomersWithHighAverageOrders() {
    @where(AVG(customer.orders.total) > 100)
}

`MEDIAN`

Computes the arithmetic median of the numeric values in a collection.

Syntax: MEDIAN(items)

Example usages:

// In @computed
medianOrderValue Decimal @computed(MEDIAN(customer.orders.total))
 
// In @where
list listCustomersWithHighMedianOrders() {
    @where(MEDIAN(customer.orders.total) > 75)
}

`MIN`

Finds the smallest numeric value in a collection.

Syntax: MIN(items)

Example usages:

// In @computed
minOrderValue Decimal @computed(MIN(customer.orders.total))
 
// In @set
update calculateMinOrderValue(id) {
    @set(customer.minOrderValue = MIN(customer.orders.total))
}

`MAX`

Finds the largest numeric value in a collection.

Syntax: MAX(items)

Example usages:

// In @computed
maxOrderValue Decimal @computed(MAX(customer.orders.total))
 
// In @set
update calculateMaxOrderValue(id) {
    @set(customer.maxOrderValue = MAX(customer.orders.total))
}

`SUMIF`

Adds up numeric values that satisfy a given condition.

Syntax: SUMIF(items, condition)

Example usages:

// In @computed
activeOrderTotal Decimal @computed(SUMIF(customer.orders.total, customer.orders.isActive))
 
// In @set
update calculateActiveOrderTotal(id) {
    @set(customer.activeOrderTotal = SUMIF(customer.orders.total, customer.orders.isActive))
}
 
// In @where
list listCustomersWithHighActiveOrderValue() {
    @where(SUMIF(customer.orders.total, customer.orders.isActive) > 200)
}

`COUNTIF`

Counts items that satisfy a given condition.

Syntax: COUNTIF(items, condition)

Example usages:

// In @computed
activeOrderCount Number @computed(COUNTIF(customer.orders.id, customer.orders.isActive))
 
// In @set
update calculateActiveOrderCount(id) {
    @set(customer.activeOrderCount = COUNTIF(customer.orders.id, customer.orders.isActive))
}
 
// In @where
list listCustomersWithActiveOrders() {
    @where(COUNTIF(customer.orders.id, customer.orders.isActive) >= 1)
}

`AVGIF`

Computes the average of numeric values that satisfy a given condition.

Syntax: AVGIF(items, condition)

Example usages:

// In @computed
activeOrderAvg Decimal @computed(AVGIF(customer.orders.total, customer.orders.isActive))
 
// In @set
update calculateActiveOrderAvg(id) {
    @set(customer.activeOrderAvg = AVGIF(customer.orders.total, customer.orders.isActive))
}

`MEDIANIF`

Finds the median of numeric values that satisfy a given condition.

Syntax: MEDIANIF(items, condition)

Example usages:

// In @computed
medianActiveOrder Decimal @computed(MEDIANIF(customer.orders.total, customer.orders.isActive))
 
// In @where
list listCustomersWithHighActiveMedian() {
    @where(MEDIANIF(customer.orders.total, customer.orders.isActive) > 100)
}

`MINIF`

Finds the smallest numeric value that satisfies a given condition.

Syntax: MINIF(items, condition)

Example usages:

// In @computed
minActiveOrder Decimal @computed(MINIF(customer.orders.total, customer.orders.isActive))
 
// In @set
update calculateActiveOrderMin(id) {
    @set(customer.activeOrderMin = MINIF(customer.orders.total, customer.orders.isActive))
}

`MAXIF`

Finds the largest numeric value that satisfies a given condition.

Syntax: MAXIF(items, condition)

Example usages:

// In @computed
maxActiveOrder Decimal @computed(MAXIF(customer.orders.total, customer.orders.isActive))
 
// In @set
update calculateActiveOrderMax(id) {
    @set(customer.activeOrderMax = MAXIF(customer.orders.total, customer.orders.isActive))
}

Quantifier Functions

Quantifier functions test conditions across collections in has-many relationships. They return boolean values and can be used in @computed, @set, @where, @permission, and @validate expressions.

The condition must read a to-many relationship, and every field reference inside one quantifier must share the same relationship path. That rules out four shapes, each of which is a build error.

A path that is not to-many reports which path is at fault. This covers a repeated scalar field, such as ANY(invoice.tags == "priority") where tags is Text[], and a to-one relationship, such as ANY(order.customer.name == "Ada"):

ANY requires a to-many relationship, but 'invoice.tags' is not to-many
ANY requires a to-many relationship, but 'order.customer' is not to-many

A predicate with no single shared path reports that instead. This covers two different relationship paths inside one quantifier, such as ALL(customer.orders.total > customer.reviews.rating), and a path rooted at ctx rather than at a model, such as ANY(ctx.identity.email != ""):

ALL predicate field references must share a to-many relationship path

Combine two quantifiers with && rather than putting two paths inside one.

`ANY`

Returns true if at least one item in the collection satisfies the condition.

Syntax: ANY(condition)

Example usages:

// In @where - find customers with at least one active order
list listCustomersWithActiveOrders() {
    @where(ANY(customer.orders.status == OrderStatus.Active))
}
 
// In @where - find customers with at least one high-value order
list listHighValueCustomers() {
    @where(ANY(customer.orders.total > 1000))
}
 
// In @permission - allow access if the project has any member for this identity
list listProjects() {
    @permission(expression: ANY(project.members.identity == ctx.identity))
}
 
// Multiple conditions in ANY
list listCustomersWithRecentActiveOrders() {
    @where(ANY(customer.orders.status == OrderStatus.Active && customer.orders.createdAt > ctx.now - 30d))
}
 
// In @computed - check if customer has any active orders
model Customer {
    fields {
        orders Order[]
        hasActiveOrders Boolean @computed(ANY(customer.orders.status == OrderStatus.Active))
    }
}
 
// In @set - set flag based on order status
update updateCustomerStatus(id) {
    @set(customer.hasActiveOrders = ANY(customer.orders.status == OrderStatus.Active))
}

`ALL`

Returns true if every item in the collection satisfies the condition.

Syntax: ALL(condition)

Example usages:

// In @where - find customers where all orders are delivered
list listCustomersAllDelivered() {
    @where(ALL(customer.orders.status == OrderStatus.Delivered))
}
 
// In @where - find projects where all tasks are completed
list listCompletedProjects() {
    @where(ALL(project.tasks.isCompleted))
}
 
// In @permission - require every one of the record's grants to be approved
list listSensitiveData() {
    @permission(expression: ALL(report.grants.isApproved))
}
 
// Combined with other conditions
list listVerifiedProducts() {
    @where(product.isActive && ALL(product.reviews.isVerified))
}
 
// In @computed - check if all orders are delivered
model Customer {
    fields {
        orders Order[]
        allOrdersDelivered Boolean @computed(ALL(customer.orders.status == OrderStatus.Delivered))
    }
}
 
// In @set - set flag when all tasks are complete
update markProjectComplete(id) {
    @set(project.isComplete = ALL(project.tasks.isCompleted))
}

Combining ANY and ALL

You can combine ANY and ALL with other expressions for complex filtering:

// Find customers with all delivered orders AND at least one high-value order
list listPremiumCustomers() {
    @where(
        ALL(customer.orders.status == OrderStatus.Delivered) &&
        ANY(customer.orders.total > 500)
    )
}
 
// Find projects where all tasks are assigned and any task is high priority
list listActiveProjects() {
    @where(
        ALL(project.tasks.assigneeId != null) &&
        ANY(project.tasks.priority == Priority.High)
    )
}

Scalar Functions

Scalar functions operate on individual values and return a single result. They can be used in @computed, @set, @where, @permission, and @validate expressions.

`IF`

Returns one value if a condition is true, and another if false. Useful for conditional logic in expressions.

Syntax: IF(condition, thenValue, elseValue)

The two branches do not have to be the same type as long as the types are compatible, so IF(product.isActive, product.price, 0) is valid where price is a Decimal. The result takes the type of the then branch. Mixing types from different families, such as a Decimal with a Text, is rejected.

Example usages:

// In @computed - conditional text based on boolean field
importanceLabel Text @computed(IF(appointment.isImportant, "High Priority", "Normal"))
 
// In @computed - conditional pricing
finalPrice Decimal @computed(IF(product.isActive, product.price * 0.9, product.price))
 
// In @set - apply discount based on condition
update applyActiveDiscount(id) {
    @set(product.discountedPrice = IF(product.isActive, product.price * 0.9, product.price))
}
 
// In @set - set status text
update setStatus(id) {
    @set(product.status = IF(product.isActive, "Available", "Discontinued"))
}
 
// Nested IF for multiple conditions
update setTier(id) {
    @set(product.tier = IF(product.isPremium, "Premium", IF(product.isBulk, "Bulk", "Standard")))
}
 
// In @where
list listActiveOrExpensiveProducts(minPrice: Decimal) {
    @where(IF(product.isActive, product.price, product.price * 2) > minPrice)
}

Date Extraction Functions

These functions extract specific components from Date or Timestamp fields.

`YEAR`

Extracts the year from a date or timestamp.

Syntax: YEAR(dateOrTimestamp)

Example usages:

// In @computed
scheduledYear Number @computed(YEAR(appointment.scheduledDate))
 
// In @set
update extractYear(id) {
    @set(event.extractedYear = YEAR(event.eventDate))
}
 
// In @where
list listEventsInYear(year: Number) {
    @where(YEAR(event.eventDate) == year)
}

`MONTH`

Extracts the month (1-12) from a date or timestamp.

Syntax: MONTH(dateOrTimestamp)

Example usages:

// In @computed
scheduledMonth Number @computed(MONTH(appointment.scheduledDate))
 
// In @where
list listEventsInMonth(month: Number) {
    @where(MONTH(event.eventDate) == month)
}

`DAY`

Extracts the day of the month (1-31) from a date or timestamp.

Syntax: DAY(dateOrTimestamp)

Example usages:

// In @computed
scheduledDay Number @computed(DAY(appointment.scheduledDate))
 
// In @where
list listEventsOnDay(day: Number) {
    @where(DAY(event.eventDate) == day)
}

`HOUR`

Extracts the hour (0-23) from a timestamp.

Syntax: HOUR(timestamp)

Example usages:

// In @computed
scheduledHour Number @computed(HOUR(appointment.scheduledTimestamp))
 
// In @where
list listEventsAtHour(hour: Number) {
    @where(HOUR(event.eventTimestamp) == hour)
}

`MINUTE`

Extracts the minute (0-59) from a timestamp.

Syntax: MINUTE(timestamp)

Example usage:

// In @computed
scheduledMinute Number @computed(MINUTE(appointment.scheduledTimestamp))

`SECOND`

Extracts the second (0-59) from a timestamp.

Syntax: SECOND(timestamp)

Example usage:

// In @set
update extractSecond(id) {
    @set(event.extractedSecond = SECOND(event.eventTimestamp))
}

Date Arithmetic Functions

These functions add or subtract time periods from Date or Timestamp fields.

`ADDDAYS`

Adds or subtracts days from a date or timestamp.

Syntax: ADDDAYS(dateOrTimestamp, numberOfDays)

Example usages:

// In @set - add days
update addDaysToDate(id) {
    @set(event.newDate = ADDDAYS(event.eventDate, 10))
}
 
// In @set - subtract days (use negative number)
update subtractDaysFromDate(id) {
    @set(event.newDate = ADDDAYS(event.eventDate, -7))
}
 
// Combined with IF
update adjustDeadlineByPriority(id) {
    @set(task.adjustedDeadline = IF(task.isHighPriority, ADDDAYS(task.deadline, -3), ADDDAYS(task.deadline, 7)))
}

`ADDMONTHS`

Adds or subtracts months from a date or timestamp.

Syntax: ADDMONTHS(dateOrTimestamp, numberOfMonths)

Example usages:

// In @set - add months
update addMonthsToDate(id) {
    @set(event.newDate = ADDMONTHS(event.eventDate, 3))
}
 
// In @set - subtract months
update subtractMonthsFromDate(id) {
    @set(event.newDate = ADDMONTHS(event.eventDate, -1))
}

`ADDYEARS`

Adds or subtracts years from a date or timestamp.

Syntax: ADDYEARS(dateOrTimestamp, numberOfYears)

Example usages:

// In @set - add years
update addYearsToDate(id) {
    @set(event.newDate = ADDYEARS(event.eventDate, 1))
}
 
// In @set - subtract years
update subtractYearsFromDate(id) {
    @set(event.newDate = ADDYEARS(event.eventDate, -5))
}

`DIFF`

Calculates the difference in days between two dates or timestamps. Returns a positive number if the first date is after the second, or negative if before.

Syntax: DIFF(dateOrTimestamp1, dateOrTimestamp2)

Example usages:

// In @computed
durationDays Number @computed(DIFF(appointment.endDate, appointment.scheduledDate))
 
// In @set
update calculateDaysDiff(id) {
    @set(event.daysDifference = DIFF(event.dueDate, event.eventDate))
}
 
// In @where - find tasks due within a certain number of days
list listTasksDueSoon(maxDays: Number) {
    @where(DIFF(task.deadline, task.startedAt) <= maxDays)
}
 
// In @where - find overdue events
list listOverdueEvents() {
    @where(DIFF(event.dueDate, event.eventDate) < 0)
}

Schema Reference Filtering

Expressions

Arithmetic Operators

Using in @set

Using in @where

Using in @permission

Using in @computed

Division and zero divisors

Aggregate Functions

SUM

COUNT

AVG

MEDIAN

MIN

MAX

SUMIF

COUNTIF

AVGIF

MEDIANIF

MINIF

MAXIF

Quantifier Functions

ANY

ALL

Combining ANY and ALL

Scalar Functions

IF

Date Extraction Functions

YEAR

MONTH

DAY

HOUR

MINUTE

SECOND

Date Arithmetic Functions

ADDDAYS

ADDMONTHS

ADDYEARS

DIFF

Using in `@set`

Using in `@where`

Using in `@permission`

Using in `@computed`

`SUM`

`COUNT`

`AVG`

`MEDIAN`

`MIN`

`MAX`

`SUMIF`

`COUNTIF`

`AVGIF`

`MEDIANIF`

`MINIF`

`MAXIF`

`ANY`

`ALL`

`IF`

`YEAR`

`MONTH`

`DAY`

`HOUR`

`MINUTE`

`SECOND`

`ADDDAYS`

`ADDMONTHS`

`ADDYEARS`

`DIFF`