Type System

ObjectQL provides 20+ specialized field types that encode business semantics, not just data storage primitives. Each type understands its purpose and automatically configures database schemas, UI renderers, validation rules, and API serialization.

Type Philosophy

Traditional databases:

-- Just stores data
CREATE TABLE customer (
  revenue DECIMAL(18,2)  -- Is this USD? EUR? Monthly? Annual?
);

ObjectQL:

# Encodes business meaning
revenue:
  type: currency
  label: Annual Revenue
  scale: 2
  precision: 18
  # Automatically knows:
  # - Store amount + currency code
  # - UI shows currency symbol
  # - Validate numeric precision
  # - Format for display ($1,234.56)

Type Categories

1. Scalar Types

Text Types

text

Single-line text field.

company_name:
  type: text
  label: Company Name
  maxLength: 255
  required: true
  searchable: true

Database mapping:

• SQL driver: TEXT on every dialect — except when a declared index keys the column, where it is VARCHAR(maxLength) instead. Both conditions are required: the field declares a maxLength of 768 or less (the widest key part utf8mb4 allows — MySQL's 3072-byte index limit ÷ 4 bytes per character), and some declared index keys on it (field-level unique, or an entry in the object's indexes[]). So the company_name field above stays TEXT unless an index names it, and a keyed field declaring maxLength: 1024 stays TEXT too.
• MongoDB: String

UI rendering:

<input type="text" maxlength="255" required />

Use cases:

• Names, titles, identifiers
• Email addresses, phone numbers
• Short descriptions

textarea

Multi-line plain text.

description:
  type: textarea
  label: Description
  maxLength: 5000

Database mapping:

• SQL driver: TEXT — or VARCHAR(maxLength) when a declared index keys the column, on the same two conditions as the text type above.
• MongoDB: String

UI rendering:

<textarea rows="10" maxlength="5000"></textarea>

Use cases:

• Comments, notes
• Descriptions
• Plain text content

html

Rich text with HTML markup.

bio:
  type: html
  label: Biography

Database mapping:

• SQL driver: TEXT — or VARCHAR(maxLength) when a declared index keys the column, on the same two conditions as the text type above.
• MongoDB: String

UI rendering:

<!-- Rich text editor (TinyMCE, Quill, etc.) -->
<div class="rich-text-editor"></div>

Use cases:

• Blog posts, articles
• Product descriptions
• Email templates

email

Email address with validation.

email:
  type: email
  label: Email Address
  required: true
  unique: organization

Validation: a deliberately permissive, ReDoS-safe shape check — a local part, an @, and a dotted domain (invalid_email otherwise). It is not an RFC 5322 parser, the value is not lowercased, and no DNS/MX lookup is performed. Stricter rules belong in a validation rule or custom validator.

Database mapping:

• SQL driver: VARCHAR(maxLength), or VARCHAR(255) when the field declares no maxLength
• MongoDB: String

Use cases:

• User emails
• Contact information
• Notification addresses

url

URL with protocol validation.

website:
  type: url
  label: Website

Validation: accepts any scheme://… (not just http/https — libsql://, postgres://, s3://, file:// all pass), plus root-/protocol-relative refs (/path, //host/path) and data: / blob: URIs. A bare scheme-less string with no leading / is rejected (invalid_url). There is no domain-format check and no reachability check.

Use cases:

• Company websites
• Social media links
• API endpoints

phone

Phone number with international format.

phone:
  type: phone
  label: Phone Number

Storage format: the string as entered — a VARCHAR(maxLength) column, or VARCHAR(255) when the field declares no maxLength. The engine does not normalize to E.164 and does not reformat for display.

Validation: a shape check only — at least 5 characters drawn from digits and + ( ) - . and whitespace (invalid_phone otherwise). There is no country-code verification and no SMS-capability check. If you need E.164 at rest, normalize in a before-save trigger or a custom validator.

Numeric Types

number

General-purpose numeric field.

quantity:
  type: number
  label: Quantity
  min: 0
  max: 9999
  scale: 0  # Integer
  defaultValue: 1

Configuration:

• scale: Decimal places (0 = integer)
• precision: Total digits
• min/max: Range validation

Database mapping:

• SQL driver: a floating-point column (REAL on PostgreSQL/SQLite, FLOAT on MySQL). precision/scale are validation and display metadata — the DDL does not emit NUMERIC(precision, scale).
• MongoDB: Number

Use cases:

• Quantities, counts
• Ratings, scores
• General measurements

currency

Money amount with currency code.

annual_revenue:
  type: currency
  label: Annual Revenue
  currencyConfig:
    precision: 2
    currencyMode: fixed
    defaultCurrency: USD

Storage: a bare number — the same value shape as number.

1234.56

Features:

• currencyMode: fixed | dynamic and a defaultCurrency code on the field
• Codes are validated by length only (3 characters), so ISO 4217 (USD, EUR, CNY) and non-ISO codes (BTC, ETH) both pass
• precision (0–10, default 2) for decimal places

Database mapping:

• SQL driver: a floating-point column (REAL / FLOAT) — one column, no companion currency column and no JSON blob
• MongoDB: Number

percent

Percentage value (0-100).

discount_rate:
  type: percent
  label: Discount
  scale: 2
  min: 0
  max: 100

Display: 25.5% (automatically adds % symbol)

Storage: the percentage number itself — 25.5 means 25.5%, matching the min: 0 / max: 100 bounds above. It is not rescaled to a 0–1 ratio on write. Physically it is the same floating-point column as number.

Use cases:

• Discounts, margins
• Completion rates
• Tax rates

Date/Time Types

date

Calendar date (no time component).

birth_date:
  type: date
  label: Date of Birth

Storage format: a timezone-naive calendar day — the YYYY-MM-DD string 2024-01-15, never an instant (ADR-0053). A Date collapses to its UTC calendar day; a longer ISO string is truncated to its leading 10 characters. The same normalization is applied on write, on read, and to every filter comparand, so the two sides of a comparison can never disagree about what a date is.

Database mapping:

• SQL driver: DATE on PostgreSQL/MySQL, TEXT on SQLite — holding YYYY-MM-DD on every dialect
• MongoDB: no DDL (schemaless); the driver stores the value it is given

The day you read back does not depend on the app process's timezone. The SQL driver pins each dialect so a stored DATE arrives as the calendar-day string itself, never as an instant it would then have to re-derive a day from: PostgreSQL connections parse date (and date[]) as text, and MySQL connections are pinned to UTC in both directions. So an app container running TZ=Asia/Shanghai and one running TZ=UTC read the same row as the same day — running the process at UTC is not a prerequisite for correct dates.

Use cases:

• Birthdays, anniversaries
• Contract dates
• Deadlines

datetime

A UTC instant.

meeting_time:
  type: datetime
  label: Meeting Time

Storage format: the canonical, fixed-width, zone-explicit UTC instant YYYY-MM-DDTHH:MM:SS.sssZ — e.g. 2024-01-15T14:30:00.000Z. Milliseconds are always present and the zone is always Z; an offset-bearing input such as …T22:30:00+08:00 is rewritten to the equivalent …Z instant so text ordering stays chronological ordering.

Database mapping:

• PostgreSQL: timestamptz
• MySQL: DATETIME(3) — deliberately not TIMESTAMP, which is a 32-bit epoch (a 2038 ceiling on the column every list view sorts by), drops the milliseconds, and converts on read/write using the session timezone. The canonical instant is bound as a MySQL literal (YYYY-MM-DD HH:MM:SS.sss, no T/Z) because MySQL rejects ISO-8601 in a datetime literal.
• SQLite: TEXT holding the canonical string — fixed width plus UTC means lexicographic order is chronological order, so range filters use the index
• MongoDB: no DDL (schemaless)

time

Time of day (no date component).

business_hours_start:
  type: time
  label: Business Hours Start
  defaultValue: "09:00:00"

Storage format: HH:MM:SS, gaining a .fff millisecond suffix only when the milliseconds are non-zero (14:30:00, but 14:30:00.100). Input accepts HH:MM or HH:MM:SS (with an optional fractional part and Z/offset); 14:30 is completed to 14:30:00, so one wall clock can never split into several stored values. A Date, an epoch, or a full timestamp folds to its UTC time-of-day. A time is a wall-clock value, not an instant: it is validated as a time-of-day, not parsed as a date.

Database mapping:

• PostgreSQL: time
• MySQL: TIME(3) — bare TIME is zero-precision and rounds a fractional literal (14:30:00.500 → 14:30:01), which would change the stored wall clock
• SQLite: TEXT holding the canonical string
• MongoDB: no DDL (schemaless)

As with date and datetime, filter comparands are canonicalized by the same function as writes, so 09:00 <= t <= 18:00 windows compare like against like.

Use cases:

• Business hours
• Recurring event times
• Time-based triggers

defaultValue: 'NOW()' on temporal fields

NOW() is a framework convention meaning "use the database clock at insert time". The driver translates it into a dialect-native default that resolves against the UTC clock on every dialect, for date, datetime, and time alike:

opened_at:
  type: datetime
  label: Opened At
  defaultValue: "NOW()"

For date and time on PostgreSQL and MySQL the driver emits an explicit UTC expression default rather than a bare CURRENT_TIMESTAMP, which resolves the calendar day / wall clock in the server's timezone on PostgreSQL and the inserting session's timezone on MySQL — one instant producing three different stored values across the three dialects (and MySQL 8.0 rejects a bare CURRENT_TIMESTAMP default on DATE/TIME columns outright). On SQLite all three types use strftime(…, 'now') expressions that emit the canonical form directly. datetime on PostgreSQL/MySQL keeps the native now(), which is already UTC — the driver pins every MySQL connection with SET time_zone = '+00:00'.

Boolean Types

boolean

True/false value.

is_active:
  type: boolean
  label: Active
  defaultValue: true

Storage:

• SQL driver: BOOLEAN (SQLite stores 1/0; the driver coerces it back to a real JS boolean on read)
• MongoDB: Boolean

UI rendering:

<input type="checkbox" />
<!-- or -->
<select>
  <option value="true">Yes</option>
  <option value="false">No</option>
</select>

toggle

Boolean displayed as a toggle switch.

email_opt_in:
  type: toggle
  label: Subscribe to Newsletter
  defaultValue: false

Difference from boolean:

• Always renders as a toggle switch (not a dropdown)
• Typically used for consent, preferences, on/off settings

Selection Types

select

Dropdown/picklist from predefined options.

priority:
  type: select
  label: Priority
  options:
    - value: low
      label: Low
      color: green
    - value: medium
      label: Medium
      color: yellow
    - value: high
      label: High
      color: orange
    - value: critical
      label: Critical
      color: red
  defaultValue: medium
  required: true

Storage: Stores value (not label). Option values must be lowercase machine identifiers — the spec rejects New, In Progress, or Closed_Won.

Database mapping:

• SQL driver: VARCHAR(255) — the driver never emits a native ENUM, so adding an option is a metadata change, not a schema migration
• MongoDB: String

Use cases:

• Status, stage, priority
• Categories, types
• Fixed value lists

multiselect

Multiple selection from options.

tags:
  type: multiselect
  label: Tags
  options:
    - { value: customer, label: Customer }
    - { value: partner, label: Partner }
    - { value: vendor, label: Vendor }
  # multiple: true # Implied by type

Storage:

• SQL driver: a JSON column holding the serialized array — not a native TEXT[], so the same DDL works on SQLite and MySQL
• MongoDB: [String]

Example value: ['customer', 'partner']

radio

Radio button group (single selection).

contact_method:
  type: radio
  label: Preferred Contact Method
  options:
    - { value: email, label: Email }
    - { value: phone, label: Phone }
    - { value: sms, label: SMS }

Difference from select:

• All options visible (no dropdown)
• Better UX for 2-5 options

2. Relationship Types

lookup

Foreign key reference to another object.

account_id:
  type: lookup
  label: Account
  reference: account
  required: true
  lookupFilters:
    - field: is_active
      operator: eq
      value: true
  deleteBehavior: set_null  # or restrict, cascade

Storage: Stores id of referenced record

Query behavior: expand is the door for related data. A dotted fields entry ('account_id.company_name') is refused (400 INVALID_FIELD) — no driver resolves one, at the REST ingress since #7532 and on direct engine.find / engine.findOne calls since #7589. Keep the reference column itself in the projection: the relation is carried by account_id, and projecting it away leaves the expansion nothing to resolve.

// Read a column of the related account
const opportunities = await engine.find('opportunity', {
  fields: ['name', 'account_id'],
  expand: { account_id: { object: 'account', fields: ['company_name'] } },
});

On Delete Options:

• set_null: Clear the reference when the referenced record is deleted. On a single-value lookup the stored id becomes null; on a multiple: true lookup the reference is a set, so only the deleted member is removed and the remaining members are kept
• restrict: Prevent deletion if references exist
• cascade: Delete this record when referenced record is deleted

Residual shape of a multi-value reference. A multiple: true lookup emptied by member removal is written as [], never null. That is not a cascade convention: the representation binds every writer — cascade repair, form clears and API writes alike — so a reader of an array field never needs a null branch. The guarantee is pinned by FieldSchema, in the multiple doc block of packages/spec/src/data/field.zod.ts (rendered in the Field reference).

Required foreign keys. A required: true lookup cannot be nulled, so set_null escalates to restrict on such a field — deleting the parent is refused with 409 DELETE_RESTRICTED (the response carries dependentObject and dependentCount) instead of a confusing "<field> is required" validation error. To delete the children along with the parent, set deleteBehavior: cascade explicitly.

The escalation applies to any set_null on a required lookup — the default and one written out as deleteBehavior: set_null alike. The engine tests the resolved behavior, so it cannot tell the two apart: writing set_null explicitly on a required lookup does not opt out of the refusal, and it does not change the outcome in any way. cascade and restrict are the two values that are honored as written.

On a multiple: true required lookup the escalation is judged per row, after the referencing rows are known — because set_null there removes only the deleted member (see the member-removal rule above), and a required set is only violated when nothing is left. A row that still holds another member is written its remainder and the parent delete goes through; a row the removal would EMPTY keeps the refusal, since [] does not satisfy required on a multi-value field. When both kinds of row reference the record, the delete is refused and dependentCount counts only the rows that would have been emptied.

On master_detail the same reading applies from the other side: restrict is the only value that deviates from cascade. An explicit deleteBehavior: set_null on a master-detail reference is a parse-time rejection (#9689) — a detail row cannot outlive its master, so the spec refuses the declaration instead of silently cascading the children it asked to keep. Metadata that bypasses the parse (a raw registration, or a row stored before the tightening) still resolves to cascade, now with a loud engine log at the coercion site.

The refusal carries two messages, for two audiences. error is written for the person who clicked delete: it is rendered in the caller's locale from the built-in catalog and names the objects and the field by their labels, so a client may show it to an end user as-is. developerMessage is the operator's copy — English, API names, and the deleteBehavior: cascade remedy — and should not be surfaced to end users. Override any locale's sentence with a translation item under errors.delete_restricted / errors.delete_restricted_required.

Multiple lookups:

contacts:
  type: lookup
  reference: contact
  multiple: true  # Many-to-many

Database mapping:

• SQL driver: VARCHAR(255) holding the related record id. Not a native UUID column — record ids are opaque strings. A multiple: true lookup becomes a JSON column instead.
• MongoDB: String

master_detail

Strong parent-child relationship.

project_id:
  type: master_detail
  label: Project
  reference: project
  deleteBehavior: cascade # Enforced by type usually

Characteristics:

• Ownership: Child cannot exist without parent
• Cascade delete: Deleting parent deletes all children
• Sharing inheritance: Child inherits parent's permissions
• Rollup support: Parent can aggregate child data

Difference from lookup:

Example:

# Order (Master)
name: order

# Order Line Item (Detail)
name: order_line_item
fields:
  order_id:
    type: master_detail
    reference: order

tree

Hierarchical reference for parent-child trees.

parent:
  type: tree
  label: Parent
  reference: category

Storage: Stores the id of the referenced parent record.

Use cases:

• Category / folder hierarchies
• Org charts
• Any self-referential tree

3. Computed Types

formula

Calculated field based on other fields.

total_price:
  type: formula
  label: Total Price
  expression: "record.quantity * record.unit_price"

Formula expressions use CEL (Common Expression Language). Fields on the current record are referenced via the record. scope. CEL syntax differs from spreadsheet/Salesforce formula languages:

• Arithmetic: +, -, *, /, %
• Comparison: ==, !=, >, <, >=, <=
• Logical: &&, ||, !
• Conditional: ternary cond ? a : b (there is no IF() function)
• Functions: today(), daysFromNow(), daysBetween(), len(), size(), upper(), lower(), trim(), contains(), startsWith(), coalesce(), min(), max(), abs(), round(), and more (see the formula reference).

Examples:

# Simple calculation
discount_amount:
  type: formula
  expression: "record.price * (record.discount_rate / 100)"

# Conditional logic (CEL ternary)
status_label:
  type: formula
  expression: "record.is_active ? 'Active' : 'Inactive'"

# Cross-object formula (via expanded lookup)
account_revenue_tier:
  type: formula
  expression: "record.account.annual_revenue > 1000000 ? 'Enterprise' : 'SMB'"

# Date calculation
days_until_due:
  type: formula
  expression: "daysBetween(today(), record.due_date)"

summary (Rollup)

Aggregate child records in master-detail relationship.

# On Account object
total_opportunities:
  type: summary
  label: Total Opportunities
  summaryOperations:
    object: opportunity
    function: count

total_opportunity_value:
  type: summary
  label: Pipeline Value
  summaryOperations:
    object: opportunity
    field: amount
    function: sum

Summary Types:

• count: Count child records
• sum: Sum a numeric field
• min: Minimum value
• max: Maximum value
• avg: Average value

Requirements:

• Aggregates a child object that references this object (via its lookup/master_detail field)
• Set relationshipField only when the child has more than one reference back to this object

Optional filter: a where-style FilterCondition restricting which child rows are aggregated, ANDed with the parent-FK match. This is what lets several summaries roll the same child object into different totals:

total_signups:
  type: summary
  summaryOperations:
    object: engagement
    function: count
    filter: { type: signup }

Implementation: the summary is a real numeric column on the parent, recomputed by the ObjectQL engine when a child row is inserted, updated, or deleted. There is no materialized view, no MongoDB aggregation pipeline, and no batch/hybrid recalculation mode — a child moving in or out of the filter recomputes the parent on its next write like any other child update.

autonumber

Auto-incrementing unique identifier.

case_number:
  type: autonumber
  label: Case Number
  autonumberFormat: "CASE-{0000}"

Example values: CASE-0001, CASE-0002, ...

Format tokens:

• {0000}: Zero-padded counter
• {YYYY} / {MM} / {DD} / {YYYYMMDD}: date tokens (business timezone)
• {field_name}: interpolates another field's value

The counter resets per rendered prefix — AD{YYYYMMDD}{0000} therefore restarts at 1 each day.

Complex formats:

invoice_number:
  type: autonumber
  autonumberFormat: "INV-{YYYY}-{0000}"
  # Generates: INV-2024-0001, INV-2024-0002, ...

Database implementation: a VARCHAR(255) column, numbered from an atomic counter row in the driver's own _objectstack_sequences table (bootstrapped from the existing MAX on first use, and scoped per tenant when the object is tenant-scoped). The driver does not create a native PostgreSQL SEQUENCE.

4. Complex Types

json

Unstructured JSON data.

metadata:
  type: json
  label: Metadata

Storage:

• SQL driver: a JSON column (json on PostgreSQL/MySQL, TEXT on SQLite) — the driver uses json, not jsonb
• MongoDB: Native object

Query support:

// Query JSON properties
const products = await engine.find('product', {
  where: { 'metadata.color': 'red' }
});

Use cases:

• Product attributes (varying by category)
• Integration payloads
• User preferences
• Dynamic configurations

tags

Simple list of free-form string tags.

tags:
  type: tags
  label: Tags

Storage:

• SQL driver: a JSON column holding the serialized string array
• MongoDB: [String]

address

Structured address with geocoding.

billing_address:
  type: address
  label: Billing Address

Structure:

{
  "street": "123 Main St",
  "city": "San Francisco",
  "state": "CA",
  "postalCode": "94105",
  "country": "USA",
  "countryCode": "US",
  "formatted": "123 Main St, San Francisco, CA 94105"
}

Database mapping:

• SQL driver: a JSON column (not a composite type)
• MongoDB: Embedded document

location

Geographic coordinates.

office_location:
  type: location
  label: Office Location

Storage:

{
  "lat": 37.7749,
  "lng": -122.4194
}

altitude and accuracy (both in metres) are optional additional members. Note the keys are lat/lng — the { latitude, longitude } spelling was never consumed by the runtime and has been retired from the value contract.

Database mapping:

• SQL driver: a JSON column — the driver does not emit a native POINT / GEOGRAPHY column, which is the other half of why proximity search is not available
• MongoDB: an embedded document

file

File attachment reference.

avatar:
  type: file
  label: Profile Picture
  multiple: false  # set true to store an array of file references

Stored value: an opaque sys_file id string. The expanded read form is the media metadata object, whose only required member is url:

{
  "url": "https://cdn.example.com/files/abc123.jpg",
  "name": "profile.jpg",
  "size": 1024000,
  "mimeType": "image/jpeg"
}

alt and duration are the other optional members. The keys are name and mimeType — not filename / content_type.

Storage backends (configured on the file-storage connector, not the field):

• local: Server filesystem
• s3: Amazon S3
• azure_blob: Azure Blob Storage
• gcs: Google Cloud Storage

…plus dropbox, box, onedrive, google_drive, sharepoint, ftp, and custom. Upload-time processing — thumbnail generation, virus scanning — is configured there too, under contentProcessing.

image

Image file with transformations.

product_image:
  type: image
  label: Product Image

Features:

Per-field image-processing options (thumbnail generation, dimension validation, EXIF handling) are not Field-schema properties. Those capabilities are configured on the file-storage connector's contentProcessing (generateThumbnails, thumbnailSizes, …). The image field type itself stores the uploaded file reference.

Type Conversion Matrix

The column each type gets from the SQL driver, per dialect:

* The text family is VARCHAR(maxLength) rather than TEXT, on all three dialects, when both hold: the field declares a maxLength of 768 or less, and a declared index keys the column (field-level unique, or an entry in the object's indexes[]). A column no index touches stays TEXT whatever it declares, and so does a keyed column whose bound exceeds 768 characters — see the text type above for why, and for what the driver does when a keyed column cannot be bounded.

† The string family takes the field's declared maxLength verbatim, in both directions — a declared 1024 is a VARCHAR(1024) and a declared 20 is a VARCHAR(20). A field that declares no maxLength keeps VARCHAR(255), and so does one whose declaration is not a positive integer. Above 16383 characters (MySQL's utf8mb4 VARCHAR ceiling) the column is TEXT instead of being clamped, since a clamp would refuse writes the declaration permits; the bound is still enforced at write time by the record validator's max_length check.

Note the neighbouring rows that deliberately do not follow this rule: select / radio store an option's machine name, lookup / master_detail / tree store the referenced record's id, and autonumber stores a runtime-issued number — in none of those is the stored string the value the field's maxLength describes, so all of them keep VARCHAR(255).

Any field flagged multiple: true becomes a JSON column regardless of its type. Relationship columns are plain id strings with no database FOREIGN KEY constraint (see lookup above). The MongoDB driver is schemaless — it issues no DDL and stores the value it is given.

Sensitive Data: Masking & Encryption

There is no user-defined "custom type" registry. For sensitive values, use the dedicated secret field type, which encrypts on write and masks on read, and restrict reader access with field-level security (readable: false) or hidden.

# Reversible secret (API keys, DB passwords)
api_key:
  type: secret
  label: API Key

The per-field maskingRule and encryptionConfig properties were pruned from the Field schema in 2026-06 — they were declared surface with no runtime consumer. In 2026-07 the masking shapes were removed from the spec entirely (ADR-0056 D8); EncryptionConfigSchema remains in the System namespace as [EXPERIMENTAL] roadmap surface. See Security & Access Control for status.

Type Selection Guide

For text data:

• Short, single-line → text
• Multi-line → textarea
• Rich formatting → html
• Email address → email
• URL → url
• Phone → phone

For numbers:

• General purpose → number
• Money → currency
• Percentage → percent
• Unique ID → autonumber

For dates:

• Date only → date
• Date + time → datetime
• Time only → time

For selections:

• Fixed options → select
• Multiple selections → multiselect
• 2-5 visible options → radio
• Yes/No → boolean or toggle

For relationships:

• Loose reference → lookup
• Parent-child → master_detail
• Self-referential hierarchy → tree

For calculations:

• Derived value → formula
• Aggregate children → summary

For complex data:

• Flexible schema → json
• List of tags → tags (or multiple: true)
• Geographic data → location or address
• File upload → file or image

Next Steps