Skip to content

schema.mdx

Latest commit

 

History

History
906 lines (738 loc) · 20 KB

schema.mdx

File metadata and controls

906 lines (738 loc) · 20 KB
Raw
title Schema Definition
description The complete specification for defining objects, fields, and relationships in ObjectQL

import { FileCode, Database, Layers, Package } from 'lucide-react';

Schema Definition

In ObjectStack, data structure is configuration, not code. The Schema Definition Protocol governs how you declare your data model using declarative .object.yml files.

Single Source of Truth: This schema drives database DDL, API generation, UI form layouts, permission scopes, and validation rules.

File Structure

The .object.yml Convention

Each business entity is defined in a separate file:

my-app/
├── objects/
│   ├── customer.object.yml       # Customer entity
│   ├── order.object.yml          # Order entity
│   ├── product.object.yml        # Product entity
│   └── invoice.object.yml        # Invoice entity
├── objectstack.config.ts         # Package manifest
└── package.json

Why separate files?

  • Version control: Git shows clear diffs when objects change
  • Team collaboration: Developers can work on different objects simultaneously
  • Code generation: Each file → Database table + API + UI
  • Deployment: Selective schema deployment (only changed objects)

Alternative Formats

ObjectStack supports multiple schema formats:

# YAML (Recommended for readability)
name: customer
label: Customer

# JSON (Machine-generated)
{ "name": "customer", "label": "Customer" }

# TypeScript (Programmatic)
export default defineObject({
  name: 'customer',
  label: 'Customer'
});

Object Definition

Minimal Example

name: project
label: Project

This 2-line definition creates:

  • Database table project with system fields (_id, created_at, updated_at)
  • REST API: GET/POST/PUT/DELETE /api/project
  • Admin UI: List view + Form
  • TypeScript types

Complete Example

name: project
label: Project
plural_label: Projects
description: "A business project or initiative"
icon: standard:case
color: "#4A90E2"
bucket: main
datasource: postgres_primary
enable:
  audit: true
  workflow: true
  full_text_search: true
  api: true
  offline_sync: true
fields:
  name:
    type: text
    label: Project Name
    required: true
    max_length: 255
  status:
    type: select
    label: Status
    options:
      - { value: draft, label: Draft }
      - { value: active, label: Active }
      - { value: completed, label: Completed }
    default_value: draft
  budget:
    type: currency
    label: Budget
    scale: 2
    precision: 18
  account_id:
    type: lookup
    label: Account
    reference_to: account
    required: true
validation_rules:
  - name: budget_positive
    condition: "budget < 0"
    message: "Budget must be positive"

Object Properties Reference

Property Type Required Description
name string ✅ Machine name. Must be snake_case, unique across the system.
label string ✅ Display name for UI (singular form).
plural_label string ❌ Plural form for lists. Defaults to label + "s".
description string ❌ Human-readable description for documentation.
icon string ❌ Icon identifier (e.g., standard:case, custom:project).
color string ❌ Hex color for UI theming.
bucket string ❌ Database shard/bucket key for horizontal scaling.
datasource string ❌ External datasource ID. If omitted, uses default database.
enable object ❌ Feature flags (see Capabilities).
fields object ❌ Field definitions (see Field Definition).
validation_rules array ❌ Business validation rules (see Validation).
triggers array ❌ Database triggers (before/after insert/update/delete).
indexes array ❌ Composite indexes for query optimization.

Naming Conventions

Object Names (Machine Identifiers):

  • Format: snake_case (lowercase, underscores)
  • Pattern: /^[a-z][a-z0-9_]*$/
  • Examples: ✅ customer, project_task, sales_order
  • Invalid: ❌ Customer, projectTask, 123project

Object Labels (Display Names):

  • Format: Title Case, human-readable
  • Examples: ✅ Customer, Project Task, Sales Order

Capabilities

The enable object controls which features are active:

enable:
  # Data Management
  audit: true                # Track field history (who changed what, when)
  full_text_search: true     # Index for global search
  versioning: true           # Keep record versions for rollback
  
  # API & Integration
  api: true                  # Generate REST/GraphQL endpoints
  webhooks: true             # Allow outbound webhooks
  
  # Business Logic
  workflow: true             # Enable process builder/flows
  approval: true             # Approval processes
  
  # Advanced Features
  offline_sync: true         # Mobile offline support
  realtime: true             # WebSocket subscriptions
  ai_enabled: true           # AI features (sentiment, summarization)

Performance Impact:

  • audit: true adds ~15% write overhead (additional inserts to audit table)
  • full_text_search: true adds search indexes (~10% storage overhead)
  • versioning: true stores full record copies on each update

When to enable:

  • Audit: Regulated industries (finance, healthcare), compliance requirements
  • Full-text search: User-facing search features
  • Versioning: Critical data that may need rollback (legal contracts, pricing)

Field Definition

Fields are the columns/attributes of your object. Each field has a type and configuration.

Basic Field Structure

fields:
  field_name:
    type: text              # Field type (required)
    label: Field Label      # Display label (required)
    required: false         # Validation
    default_value: null     # Default when creating records
    help_text: "Helper text for users"
    placeholder: "Enter value..."

Field Properties Reference

Property Type Applies To Description
type string All Required. Field type (see Types).
label string All Required. Display label in UI.
required boolean All Validation: Field must have a value.
unique boolean All Enforce uniqueness at database level.
index boolean All Create database index for faster queries.
default_value any All Default value when creating new records.
help_text string All Tooltip text in forms.
placeholder string Input fields Placeholder text in input fields.
read_only boolean All Field cannot be edited after creation.
deprecated boolean All Mark field as deprecated (still queryable).
max_length number text, textarea Maximum character length.
min_value number number, currency Minimum numeric value.
max_value number number, currency Maximum numeric value.
scale number number, currency Decimal places (e.g., 2 for cents).
precision number number, currency Total digits (including scale).
options array select, radio List of valid values.
multiple boolean select, lookup Allow multiple selections.
reference_to string lookup, master_detail Target object for relationships.
formula string formula Calculation expression.
summary_type string summary Aggregation type (count, sum, min, max).

System Fields

Every object automatically includes system fields:

# Auto-generated (not defined in .object.yml)
_id:
  type: id
  label: Record ID
  read_only: true

created_at:
  type: datetime
  label: Created At
  read_only: true

updated_at:
  type: datetime
  label: Updated At
  read_only: true

created_by:
  type: lookup
  reference_to: user
  label: Created By
  read_only: true

updated_by:
  type: lookup
  reference_to: user
  label: Updated By
  read_only: true

Customizing system fields:

# Override system field behavior
fields:
  created_at:
    index: true  # Add index to created_at

Field Examples by Type

Text Fields

company_name:
  type: text
  label: Company Name
  required: true
  max_length: 255
  index: true

description:
  type: textarea
  label: Description
  max_length: 5000
  rows: 10

bio:
  type: html
  label: Biography
  sanitize: true  # Remove dangerous HTML tags

Numeric Fields

quantity:
  type: number
  label: Quantity
  min_value: 0
  max_value: 9999
  default_value: 1

discount_rate:
  type: percent
  label: Discount
  scale: 2
  min_value: 0
  max_value: 100

revenue:
  type: currency
  label: Annual Revenue
  scale: 2
  precision: 18
  default_value: { amount: 0, currency: 'USD' }

Date/Time Fields

start_date:
  type: date
  label: Start Date
  required: true

due_datetime:
  type: datetime
  label: Due Date & Time
  default_value: "{{NOW() + 7}}"  # 7 days from now

Boolean Fields

is_active:
  type: boolean
  label: Active
  default_value: true

email_opt_in:
  type: checkbox
  label: Subscribe to Newsletter
  default_value: false

Selection Fields

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 }
  default_value: medium

tags:
  type: multi_select
  label: Tags
  options:
    - { value: customer, label: Customer }
    - { value: partner, label: Partner }
    - { value: vendor, label: Vendor }
  multiple: true

Relationship Fields

account_id:
  type: lookup
  label: Account
  reference_to: account
  required: true
  reference_filters:
    - ['is_active', '=', true]  # Only show active accounts

project_id:
  type: master_detail
  label: Project
  reference_to: project
  cascade_delete: true  # Delete tasks when project is deleted

Validation Rules

Validation rules enforce business logic at the data layer:

validation_rules:
  # Simple comparison
  - name: end_after_start
    condition: "end_date < start_date"
    message: "End date must be after start date"
    severity: error
  
  # Cross-field validation
  - name: discount_requires_approval
    condition: "discount > 20 AND approved_by = null"
    message: "Discounts over 20% require manager approval"
    severity: error
  
  # Conditional validation
  - name: enterprise_contract_required
    condition: "account_type = 'Enterprise' AND contract_value = null"
    message: "Enterprise accounts must have a contract value"
    severity: error
    active: true
  
  # Warning (non-blocking)
  - name: budget_threshold_warning
    condition: "budget > 1000000"
    message: "Large budget. Please verify approval."
    severity: warning

Validation Formula Syntax

Formulas use Excel-like syntax:

Operators:

  • Comparison: =, !=, >, <, >=, <=
  • Logical: AND, OR, NOT
  • Arithmetic: +, -, *, /, ^ (power)
  • String: & (concatenate)

Functions:

  • ISBLANK(field): Check if field is empty
  • LEN(field): String length
  • CONTAINS(field, "text"): Substring check
  • REGEX(field, "pattern"): Regular expression
  • TODAY(): Current date
  • NOW(): Current datetime

Examples:

# Date validation
"start_date > TODAY()"

# String validation
"LEN(email) > 0 AND CONTAINS(email, '@')"

# Null check
"NOT(ISBLANK(manager_id))"

# Complex logic
"(status = 'Closed' AND close_date != null) OR status != 'Closed'"

Indexes

Optimize query performance with indexes:

indexes:
  # Single-field index (also via field.index: true)
  - fields: [email]
    unique: true
  
  # Composite index
  - fields: [account_id, status]
    name: idx_account_status
  
  # Full-text search index
  - fields: [name, description]
    type: fulltext
  
  # Geospatial index
  - fields: [location]
    type: geo

Index Strategy:

  • Add indexes for:
    • Foreign keys (lookup fields)
    • Fields used in WHERE clauses
    • Fields used in ORDER BY
    • Unique constraints
  • Avoid indexes for:
    • Low-cardinality fields (boolean, status with 2-3 values)
    • Fields that change frequently
    • Large text fields (use full-text search instead)

Triggers

Execute code on data changes:

triggers:
  # Before insert
  - event: before_insert
    handler: set_owner
    code: |
      record.owner_id = context.user.id;
  
  # After insert
  - event: after_insert
    handler: send_welcome_email
    async: true
    code: |
      await emailService.send({
        to: record.email,
        template: 'welcome'
      });
  
  # Before update
  - event: before_update
    handler: validate_status_change
    condition: "old.status != new.status"
    code: |
      if (old.status === 'Closed' && new.status !== 'Closed') {
        throw new Error('Cannot reopen closed records');
      }

Event Types:

  • before_insert: Before creating a record
  • after_insert: After creating a record
  • before_update: Before updating a record
  • after_update: After updating a record
  • before_delete: Before deleting a record
  • after_delete: After deleting a record

Advanced Features

Inheritance (Mixins)

Reuse field definitions across objects:

# mixins/auditable.mixin.yml
fields:
  created_at:
    type: datetime
    label: Created At
    read_only: true
  updated_at:
    type: datetime
    label: Updated At
    read_only: true

# customer.object.yml
name: customer
extends: [auditable]  # Inherit fields from mixin
fields:
  company_name:
    type: text

Polymorphic References

Reference multiple object types:

# activity.object.yml
parent_id:
  type: polymorphic
  label: Related To
  reference_to: [account, contact, opportunity]

Query polymorphic fields:

const activity = await ObjectQL.findOne('activity', id);
// activity.parent_id = { _id: '123', _type: 'account' }

Computed Fields (Virtual)

Fields calculated at query time (not stored):

full_name:
  type: formula
  label: Full Name
  formula: "first_name & ' ' & last_name"
  virtual: true  # Not stored in database

Multi-Tenant Schemas

Isolate data by tenant:

name: customer
tenant_scope: true  # Automatically filter by tenant_id
fields:
  tenant_id:
    type: lookup
    reference_to: tenant
    required: true
    default_value: "{{CURRENT_TENANT()}}"

Runtime behavior:

// User in Tenant A queries customers
const customers = await ObjectQL.query({ object: 'customer' });

// ObjectQL automatically adds: WHERE tenant_id = 'tenant_a'
// User CANNOT access Tenant B's data

Schema Versioning

Migration Strategy

When schemas change, ObjectQL generates migrations:

Before:

# v1: customer.object.yml
fields:
  name:
    type: text

After:

# v2: customer.object.yml
fields:
  first_name:
    type: text
    migration:
      from: name
      transform: "SPLIT(name, ' ')[0]"
  
  last_name:
    type: text
    migration:
      from: name
      transform: "SPLIT(name, ' ')[1]"

Generated migration:

-- PostgreSQL
ALTER TABLE customer ADD COLUMN first_name TEXT;
ALTER TABLE customer ADD COLUMN last_name TEXT;

UPDATE customer SET
  first_name = SPLIT_PART(name, ' ', 1),
  last_name = SPLIT_PART(name, ' ', 2);

ALTER TABLE customer DROP COLUMN name;

Deployment Workflow

# 1. Detect schema changes
objectstack diff

# 2. Generate migration plan
objectstack migrate:plan

# 3. Review migration SQL
cat .objectstack/migrations/001_add_customer_name.sql

# 4. Apply migration
objectstack deploy --migrate

# 5. Rollback if needed
objectstack migrate:rollback

Best Practices

Naming Conventions

# ✅ Good
name: project_task
fields:
  assigned_to_id:  # Suffix _id for lookups
    type: lookup
  is_active:       # Prefix is_ for booleans
    type: boolean
  total_amount:    # Descriptive, specific
    type: currency

# ❌ Bad
name: ProjectTask  # Not snake_case
fields:
  user:            # Ambiguous (assigned? created?)
    type: lookup
  active:          # Missing is_ prefix
    type: boolean
  amount:          # Too generic
    type: currency

Field Organization

Group related fields:

fields:
  # Identity
  name:
    type: text
  description:
    type: textarea
  
  # Relationships
  account_id:
    type: lookup
  owner_id:
    type: lookup
  
  # Status & Lifecycle
  status:
    type: select
  stage:
    type: select
  
  # Financial
  budget:
    type: currency
  actual_cost:
    type: currency

Performance Optimization

# Index frequently queried fields
email:
  type: text
  index: true
  unique: true

# Use appropriate field types
status:
  type: select  # Better than text for fixed values
  options: [...]

# Avoid large text in main table
file_content:
  type: text
  store: blob_storage  # Store in separate table/S3

Examples: Real-World Schemas

CRM: Account Object

name: account
label: Account
plural_label: Accounts
icon: standard:account
enable:
  audit: true
  full_text_search: true
  api: true

fields:
  # Company Information
  company_name:
    type: text
    label: Company Name
    required: true
    max_length: 255
    index: true
  
  website:
    type: url
    label: Website
  
  industry:
    type: select
    label: Industry
    options:
      - { value: tech, label: Technology }
      - { value: finance, label: Financial Services }
      - { value: healthcare, label: Healthcare }
      - { value: retail, label: Retail }
  
  # Contact Info
  billing_address:
    type: address
    label: Billing Address
  
  phone:
    type: phone
    label: Phone
  
  # Financial
  annual_revenue:
    type: currency
    label: Annual Revenue
    scale: 2
    precision: 18
  
  # Relationships
  owner_id:
    type: lookup
    label: Account Owner
    reference_to: user
    required: true
  
  parent_account_id:
    type: lookup
    label: Parent Account
    reference_to: account
  
  # Metrics (Computed)
  total_opportunities:
    type: summary
    label: Total Opportunities
    summary_object: opportunity
    summary_type: count
  
  total_opportunity_value:
    type: summary
    label: Total Opportunity Value
    summary_object: opportunity
    summary_field: amount
    summary_type: sum

validation_rules:
  - name: enterprise_revenue_required
    condition: "industry = 'finance' AND annual_revenue = null"
    message: "Financial services accounts must have revenue"

E-Commerce: Product Object

name: product
label: Product
icon: standard:product
datasource: mongodb_catalog  # Use MongoDB for flexible schema

fields:
  sku:
    type: text
    label: SKU
    required: true
    unique: true
    index: true
  
  name:
    type: text
    label: Product Name
    required: true
    max_length: 255
  
  description:
    type: html
    label: Description
  
  category_id:
    type: lookup
    label: Category
    reference_to: category
  
  price:
    type: currency
    label: Price
    required: true
  
  inventory_qty:
    type: number
    label: Inventory Quantity
    min_value: 0
  
  attributes:
    type: json
    label: Product Attributes
    schema:
      color: string
      size: string
      weight: number
  
  is_active:
    type: boolean
    label: Active
    default_value: true

indexes:
  - fields: [category_id, is_active]
  - fields: [name, description]
    type: fulltext

Next Steps