title	Data Modeling Guide
description	Complete guide to designing robust data models in ObjectStack following enterprise best practices

Data Modeling Guide

Complete guide to designing robust data models in ObjectStack following enterprise best practices.

Object Schema Design
Field Types & Configuration
Relationships & Lookups
Validation Rules
Formula Fields
Field Groups
Database Indexing
Best Practices

Object Schema Design

Basic Object Structure

Every object definition follows this pattern:

import { ObjectSchema, Field } from '@objectstack/spec/data';

export const MyObject = ObjectSchema.create({
  // Metadata
  name: 'my_object',              // Machine name (snake_case)
  label: 'My Object',             // Display name
  pluralLabel: 'My Objects',      // Plural form
  icon: 'briefcase',              // Icon identifier
  description: 'Description...',   // Help text
  
  // Display configuration
  titleFormat: '{{record.field1}} - {{record.field2}}',
  highlightFields: ['field1', 'field2', 'field3'],
  
  // Fields definition
  fields: {
    // ... field definitions
  },
  
  // Performance
  indexes: [...],
  
  // Capabilities
  enable: {...},
  
  // Business rules
  validations: [...],
});

Object Metadata

Property	Type	Description	Example
`name`	string	Machine name (snake_case)	`'account'`
`label`	string	Display name	`'Account'`
`pluralLabel`	string	Plural display name	`'Accounts'`
`icon`	string	Icon identifier	`'building'`
`description`	string	Help text	`'Companies...'`
`titleFormat`	string	Record title template (`{{record.field}}` interpolation)	`'{{record.name}} - {{record.id}}'`
`highlightFields`	string[]	Most-important fields, in priority order (default columns, cards, previews, detail highlight strip; ADR-0085 — formerly `compactLayout`; the old spelling was retired and is now rejected)	`['name', 'status']`

Enable Features

Control which features are available for an object:

enable: {
  trackHistory: true,        // Track field changes over time
  searchable: true,          // Include in global search
  apiEnabled: true,          // Expose via REST/GraphQL
  apiMethods: [              // Whitelist API operations
    'get',
    'list',
    'create',
    'update',
    'delete',
    'search',
    'export'
  ],
  files: true,               // Allow file attachments
  feeds: true,               // Enable activity feed (Chatter-like)
  activities: true,          // Track tasks and events
  trash: true,               // Soft delete with recycle bin
  mru: true,                 // Track Most Recently Used
}

Global search — `searchable` + `searchableFields`

searchable: true includes an object's records in global search and the record picker. By default the search matches the name/title field plus short-text fields. To control exactly which fields are matched, set searchableFields on the object (ADR-0061):

{
  name: 'account',
  searchable: true,
  searchableFields: ['name', 'website', 'billing_city'],
}

Queries then use the $search filter operator to match across searchableFields (e.g. { filters: { $search: 'acme' } }); a view may narrow the set with $searchFields. When searchableFields is unset, search falls back to the name/title field plus short-text fields.

Field Types & Configuration

Text Fields

// Simple text field
Field.text({
  label: 'Account Name',
  required: true,
  maxLength: 255,
  searchable: true,
})

// Text area (multi-line)
Field.textarea({
  label: 'Description',
  maxLength: 5000,
  rows: 5,
})

// Rich text / Markdown
Field.markdown({
  label: 'Notes',
})

Numeric Fields

// Number
Field.number({
  label: 'Employees',
  min: 0,
  max: 1000000,
  step: 1,
})

// Currency
Field.currency({
  label: 'Annual Revenue',
  scale: 2,           // Decimal places
  min: 0,
})

// Percent
Field.percent({
  label: 'Discount',
  scale: 2,
  min: 0,
  max: 100,
})

Date & Time Fields

// Date only
Field.date({
  label: 'Close Date',
  required: true,
  // Dynamic defaults must be a CEL expression envelope; a bare string
  // like 'TODAY()' would be stored verbatim.
  defaultValue: { dialect: 'cel', source: 'today()' },
})

// Date and time
Field.datetime({
  label: 'Last Modified',
  readonly: true,
  defaultValue: { dialect: 'cel', source: 'now()' },
})

// Time only (no `Field.time` helper — use the generic form)
{ type: 'time', label: 'Business Hours Start' }

Boolean Field

Field.boolean({
  label: 'Active',
  defaultValue: true,
})

Select Fields

// Single select (picklist)
Field.select({
  label: 'Status',
  options: [
    { label: 'New', value: 'new', color: '#999999', default: true },
    { label: 'In Progress', value: 'in_progress', color: '#FFA500' },
    { label: 'Completed', value: 'completed', color: '#00AA00' },
  ],
  required: true,
})

// Multi-select
Field.select({
  label: 'Skills',
  multiple: true,
  options: [
    { label: 'JavaScript', value: 'js' },
    { label: 'Python', value: 'python' },
    { label: 'Go', value: 'go' },
  ],
})

AutoNumber Field

Generates sequential numbers automatically:

Field.autonumber({
  label: 'Account Number',
  format: 'ACC-{0000}',      // ACC-0001, ACC-0002, ...
})

// The format string supports a single zero-pad token, e.g.:
// 'INV-{000}'   // INV-001, INV-002, ...
// Date tokens like {YYYY}/{MM}/{DD} are not interpolated.

Lookup Fields

Reference other objects:

// Simple lookup
Field.lookup('account', {
  label: 'Account',
  required: true,
})

// Lookup with filters (array of filter strings)
Field.lookup('contact', {
  label: 'Primary Contact',
  referenceFilters: ['account = {account}', 'is_active = true'],
})

User Fields

Pick a person — the equivalent of Airtable's Collaborator or Salesforce's Lookup(User). A user field is a lookup specialized to the built-in sys_user object: it stores the user's id (a real foreign key), resolves to the user's name/avatar via $expand, and renders as a searchable people picker. You never reference sys_user by hand.

// Single assignee
Field.user({ label: 'Assignee' })

// Collaborators / watchers (multiple)
Field.user({ label: 'Watchers', multiple: true })

// Auto-fill the acting user on create (record owner / reporter)
Field.user({ label: 'Owner', defaultValue: 'current_user' })

defaultValue: 'current_user' stamps the authenticated user's id at insert time (the people-field counterpart to 'NOW()' for timestamps). Because a user field is stored exactly like a lookup, an existing Field.lookup('sys_user') is equivalent at the storage layer — adopt Field.user() with no data migration. Record ownership and row-level security continue to use the existing owner_id convention.

Address Field

Structured address data:

Field.address({
  label: 'Billing Address',
  addressFormat: 'international',  // or 'us', 'uk', etc.
})

// Stores:
// - street
// - city
// - state/province
// - postal_code
// - country

Location Field

Geographic coordinates:

Field.location({
  label: 'Office Location',
  displayMap: true,
  allowGeocoding: true,
})

// Stores:
// - latitude
// - longitude

URL Field

Field.url({
  label: 'Website',
})

Email Field

Field.email({
  label: 'Email Address',
  unique: true,
})

Phone Field

Field.text({
  label: 'Phone',
  format: 'phone',
})

Color Field

Field.color({
  label: 'Brand Color',
  colorFormat: 'hex',         // 'hex', 'rgb', 'hsl'
  presetColors: [
    '#FF0000',
    '#00FF00',
    '#0000FF',
  ],
})

Relationships & Lookups

Lookup (Many-to-One)

Creates a reference to another object:

fields: {
  account: Field.lookup('account', {
    label: 'Account',
    required: true,
  }),
}

Naming Convention: Use the object name directly (e.g., account, not account_id)

Filtered Lookups

Reference records that meet criteria:

contact: Field.lookup('contact', {
  label: 'Contact',
  referenceFilters: ['account = {account}', 'is_active = true'],
})

Self-Referencing Lookups

Create hierarchies:

parent_account: Field.lookup('account', {
  label: 'Parent Account',
  description: 'Parent company in hierarchy',
})

Related Lists

Child records automatically appear in related lists when a lookup points to the parent.

Example:

Account has many Contacts (Contact.account → Account)
Account detail page shows "Contacts" related list

Validation Rules

Script Validations

condition is a CEL predicate that runs against the record. When the predicate evaluates to TRUE, validation fails and the configured message is raised. Reference fields as record.<field>.

validations: [
  {
    name: 'revenue_positive',
    type: 'script',
    severity: 'error',
    message: 'Annual Revenue must be positive',
    condition: 'record.annual_revenue < 0',
  },
  {
    name: 'close_date_future',
    type: 'script',
    severity: 'warning',
    message: 'Close Date should be in the future',
    condition: 'record.close_date < today()',
  },
]

Unique Constraints

There is no unique validation type. Enforce uniqueness with a unique index or a field-level unique flag:

// Unique index
indexes: [
  { fields: ['email'], unique: true },
]

// or field-level
Field.email({ label: 'Email', unique: true })

Required Field Validations

Mark fields as required:

Field.text({
  label: 'Name',
  required: true,
})

Validation Severity

error: Prevents save
warning: Shows warning but allows save
info: Informational message

Formula Fields

Calculate values automatically:

Formula expressions are written in CEL and reference fields as record.<field>. The calculation goes in the expression property (not formula); the field type is already formula, so don't pass type.

// Simple calculation
Field.formula({
  label: 'Total Price',
  expression: 'record.subtotal - record.discount + record.tax',
  scale: 2,
})

// Conditional logic (CEL ternary)
Field.formula({
  label: 'Priority Level',
  expression: 'record.amount > 100000 ? "High" : (record.amount > 50000 ? "Medium" : "Low")',
})

// Date calculation
Field.formula({
  label: 'Days to Close',
  expression: 'record.close_date < today() ? 1 : 0',
})

// Percentage calculation
Field.formula({
  label: 'Response Rate',
  expression: 'record.num_sent > 0 ? (record.num_responses / record.num_sent) * 100 : 0',
  scale: 2,
})

Common CEL Operators & Functions

Construct	Description	Example
`cond ? a : b`	Conditional (ternary)	`record.amount > 1000 ? "High" : "Low"`
`&&`	Logical AND	`record.is_active && record.amount > 0`
`\|\|`	Logical OR	`record.status == "new" \|\| record.status == "pending"`
`!`	Logical NOT	`!record.is_deleted`
`isBlank(value)`	Check if blank	`isBlank(record.phone)`
`coalesce(a, b)`	First non-null value	`coalesce(record.nickname, record.name)`
`today()`	Current date	`today()`
`now()`	Current datetime	`now()`
`daysFromNow(n)` / `daysAgo(n)`	Date offset	`daysFromNow(30)`

Field Groups

Organize related fields into logical groups for forms, detail pages, and editors. The group protocol is intentionally minimal (MVP):

ObjectSchema.fieldGroups declares the groups; array order is the display order (no separate order property).
Field.group assigns a field to a group by referencing an ObjectFieldGroup.key. In-group display order equals the traversal order of fields.
Fields whose group is unset (or references an undeclared key) render in a default bucket after the declared groups.

import { ObjectSchema } from '@objectstack/spec/data';

export const Account = ObjectSchema.create({
  name: 'account',
  label: 'Account',

  fieldGroups: [
    { key: 'contact_info', label: 'Contact Information', icon: 'user' },
    { key: 'billing',      label: 'Billing', collapse: 'collapsed' },
    { key: 'system',       label: 'System' },
  ],

  fields: {
    name:            { type: 'text',  required: true, group: 'contact_info' },
    email:           { type: 'email',                  group: 'contact_info' },
    phone:           { type: 'phone',                  group: 'contact_info' },
    vat_id:          { type: 'text',                   group: 'billing' },
    billing_address: { type: 'address',                group: 'billing' },
    created_at:      { type: 'datetime', readonly: true, group: 'system' },
    created_by:      { type: 'lookup', reference: 'user', readonly: true, group: 'system' },
  },
});

`ObjectFieldGroup` Properties

Property	Type	Description
`key`	`string` (snake_case)	Group machine key; referenced by `Field.group`
`label`	`string`	Human-readable group header
`icon`	`string?`	Lucide/Material icon name for the group header
`description`	`string?`	Optional description under the header
`collapse`	`'none' \| 'expanded' \| 'collapsed'` (default `'none'`)	Collapse behaviour on every surface: `'none'` = always open (no toggle), `'expanded'` = collapsible and starts open, `'collapsed'` = collapsible and starts closed (ADR-0085; replaces the deprecated `defaultExpanded` flag, which is still accepted as an alias)

Supported Migrations (MVP)

✅ Add / rename / delete / reorder groups — edit the fieldGroups array. ✅ Assign an existing field to a group — set Field.group.

⏳ Deferred (future iterations): explicit per-field in-group ordering, nested groups, group-level visibility predicates. Groups render identically on forms, modals, and detail pages; when a single page needs a bespoke layout beyond groups, assign it a custom Page schema instead of adding per-surface hints here (ADR-0085).

Database Indexing

Optimize query performance with indexes:

indexes: [
  // Single field index
  { fields: ['name'], unique: false },
  
  // Unique index
  { fields: ['email'], unique: true },
  
  // Compound index
  { fields: ['type', 'is_active'], unique: false },
  
  // Lookup field index
  { fields: ['owner'], unique: false },
]

When to Add Indexes

✅ Add indexes for:

Lookup/reference fields
Fields used in filters
Fields used for sorting
Fields in WHERE clauses
High-cardinality fields

❌ Avoid indexes for:

Low-cardinality fields (e.g., boolean)
Fields rarely queried
Frequently updated fields

Best Practices

1. Naming Conventions

✅ DO:

Use snake_case for field names: first_name, account_number
Use descriptive names: annual_revenue not rev
Use boolean prefixes: is_active, has_children

❌ DON'T:

Use camelCase for field names
Use abbreviations: acc_num
Add suffixes to lookups: account_id (use account)

2. Field Design

✅ DO:

Use appropriate field types
Set reasonable max lengths
Add help text for complex fields
Use picklists instead of text when values are fixed
Mark required fields

❌ DON'T:

Store multiple values in one field
Use text fields for dates/numbers
Create too many fields (split into related objects)

3. Relationships

✅ DO:

Use lookup fields for relationships
Set up proper cascade delete rules
Use filtered lookups to improve UX
Document relationship cardinality

❌ DON'T:

Store IDs as text fields
Create circular references
Over-normalize (balance normalization vs. performance)

4. Performance

✅ DO:

Add indexes on frequently queried fields
Use formula fields for calculations
Limit related list queries
Use compound indexes for multi-field filters

❌ DON'T:

Create too many indexes (slows writes)
Put indexes on low-cardinality fields
Use SOQL in loops

5. Validation

✅ DO:

Validate at the field level when possible
Use validation rules for complex logic
Provide clear error messages
Test validation rules thoroughly

❌ DON'T:

Duplicate validations
Create overly complex rules
Block legitimate data entry

Real-World Examples

Account Object

export const Account = ObjectSchema.create({
  name: 'account',
  label: 'Account',
  pluralLabel: 'Accounts',
  icon: 'building',
  
  fields: {
    account_number: Field.autonumber({
      label: 'Account Number',
      format: 'ACC-{0000}',
    }),
    
    name: Field.text({
      label: 'Account Name',
      required: true,
      searchable: true,
      maxLength: 255,
    }),
    
    type: Field.select({
      label: 'Type',
      options: [
        { label: 'Prospect', value: 'prospect', default: true },
        { label: 'Customer', value: 'customer' },
        { label: 'Partner', value: 'partner' },
      ]
    }),
    
    annual_revenue: Field.currency({
      label: 'Annual Revenue',
      scale: 2,
      min: 0,
    }),
    
    billing_address: Field.address({
      label: 'Billing Address',
      addressFormat: 'international',
    }),
    
    owner: Field.lookup('user', {
      label: 'Account Owner',
      required: true,
    }),
  },
  
  indexes: [
    { fields: ['name'], unique: false },
    { fields: ['owner'], unique: false },
    { fields: ['type', 'is_active'], unique: false },
  ],
  
  enable: {
    trackHistory: true,
    searchable: true,
    apiEnabled: true,
    files: true,
    feeds: true,
  },
  
  validations: [
    {
      name: 'revenue_positive',
      type: 'script',
      severity: 'error',
      message: 'Revenue must be positive',
      condition: 'annual_revenue < 0',
    },
  ],
});

Next: Business Logic →

Uh oh!

FilesExpand file tree

data-modeling.mdx

Latest commit

History

data-modeling.mdx

File metadata and controls

Data Modeling Guide

Table of Contents

Object Schema Design

Basic Object Structure

Object Metadata

Enable Features

Global search — searchable + searchableFields

Field Types & Configuration

Text Fields

Numeric Fields

Date & Time Fields

Boolean Field

Select Fields

AutoNumber Field

Lookup Fields

User Fields

Address Field

Location Field

URL Field

Email Field

Phone Field

Color Field

Relationships & Lookups

Lookup (Many-to-One)

Filtered Lookups

Self-Referencing Lookups

Related Lists

Validation Rules

Script Validations

Unique Constraints

Required Field Validations

Validation Severity

Formula Fields

Common CEL Operators & Functions

Field Groups

ObjectFieldGroup Properties

Supported Migrations (MVP)

Database Indexing

When to Add Indexes

Best Practices

1. Naming Conventions

2. Field Design

3. Relationships

4. Performance

5. Validation

Real-World Examples

Account Object

Global search — `searchable` + `searchableFields`

`ObjectFieldGroup` Properties