Sheet Mode

28.1 The Problem Sheet Mode Solves

Every enterprise runs on spreadsheets that should be applications. The budget tracker emailed between department heads. The project plan with 47 tabs and VLOOKUP formulas that break when someone inserts a column. The compliance checklist where someone accidentally deletes a row and nobody knows who.

These spreadsheets persist because they offer something no application framework matches: zero-friction schema evolution, formula-driven computed values, and the ability for a non-developer to model a business process by adding a column and typing a formula.

They fail because they lack access control, validation, change tracking, state management, an API, and data integrity.

Sheet mode gives you both.

28.2 Activation

resources:
  Budget:
    mode: sheet

    object:
      id:       { type: uuid, primaryKey: true }
      lineItem: { type: string, required: true }
      category: { $ref: '#/enums/BudgetCategory' }
      q1:       { type: money, default: 0 }
      q2:       { type: money, default: 0 }
      q3:       { type: money, default: 0 }
      q4:       { type: money, default: 0 }
      notes:    { type: text }

    computed:
      total:
        type: money
        formula: "q1 + q2 + q3 + q4"
        stored: true
        reactive: true

    dynamicFields:
      enabled: true
      allowTypes: [string, money, integer, decimal, date, boolean]
      maxColumns: 50
      permissions:
        addColumn: [admin, editor]
        renameColumn: [admin, editor]
        deleteColumn: [admin]

    computedFields:
      overridable: true
      reactive: true
      crossRecord: true

    permissions:
      list:   [authenticated]
      get:    [authenticated]
      create: [editor, admin]
      update: [editor, admin]
      delete: [admin]

    features:
      history: true
      versioning: true
      audit: true

The mode: sheet declaration activates sheet mode on a resource. This enables dynamic columns, per-cell formula overrides, reactive computed fields, and schema-change history events — on top of all standard ADL features (validation, permissions, state machines, hooks).

28.3 Three Layers of Schema

Sheet mode introduces a layered schema model:

Layer 1: Fixed Columns

Declared in the object: block. Typed, validated, indexed, immutable structure. These columns define the resource’s identity. Changing them requires a YAML update and domain resubmission. They carry the full weight of ADL’s type system: validation, permissions, computed fields, state machines.

object:
  id:       { type: uuid, primaryKey: true }
  lineItem: { type: string, required: true }
  category: { $ref: '#/enums/BudgetCategory' }
  q1:       { type: money, default: 0 }
  q2:       { type: money, default: 0 }

Layer 2: Dynamic Columns

Added at runtime through the API by authorized users. Typed (the allowed types are declared in dynamicFields.allowTypes) but flexible. Schema changes are recorded as history events. Dynamic columns can be referenced in computed field formulas and validation rules.

POST /api/v1/{domain}/{resource}/columns
{
  "name": "q5",
  "type": "money",
  "default": 0,
  "description": "Q5 forecast"
}

Layer 3: Per-Cell Formula Overrides

Individual cells can override the column’s default formula with a custom expression. This provides Excel-grade per-cell flexibility while maintaining the column-level type system.

PATCH /api/v1/{domain}/{resource}/:id
{
  "total_formula": "q1 * 1.1 + q2 + q3 + q4"
}

The runtime evaluates the override formula instead of the column default for that specific record. All other records continue using the column formula.

28.4 Dynamic Fields

Configuration

dynamicFields:
  enabled: true
  allowTypes: [string, money, integer, decimal, float, date, boolean, text]
  maxColumns: 50
  permissions:
    addColumn: [admin, editor]
    renameColumn: [admin, editor]
    deleteColumn: [admin]

Column CRUD API

Create Column:

POST /api/v1/{domain}/{resource}/columns
{
  "name": "q5",
  "type": "money",
  "default": 0,
  "description": "Q5 forecast"
}

Rename Column:

PATCH /api/v1/{domain}/{resource}/columns/q5
{
  "name": "q5_revised",
  "description": "Q5 revised forecast"
}

Delete Column:

DELETE /api/v1/{domain}/{resource}/columns/q5_revised

Column Operations and History

Every column operation is permission-gated and produces a history event:

history:
  events:
    columnAdded:
      on: schemaChange
      change: addColumn
      capture: [columnName, columnType, addedByName]
      label: "Column '{columnName}' added by {addedByName}"
      severity: info

    columnRemoved:
      on: schemaChange
      change: removeColumn
      capture: [columnName]
      label: "Column '{columnName}' removed"
      severity: warning

The on: schemaChange trigger type is unique to sheet mode resources. It fires on column add, rename, and delete operations.

Storage Implementation

Dynamic column storage is transparent to the API consumer. The runtime uses one of two strategies:

The ALTER TABLE strategy is preferred because it enables native filtering and sorting on dynamic columns. The JSON strategy is a fallback for environments where frequent DDL changes are undesirable.

28.5 Per-Cell Formula Overrides

When computedFields.overridable: true is set, individual records can override the default formula for any computed field.

Declaring Overridable Fields

computed:
  total:
    type: money
    formula: "q1 + q2 + q3 + q4"
    stored: true
    reactive: true

computedFields:
  overridable: true

Applying an Override

PATCH /api/v1/{domain}/{resource}/:id
{
  "total_formula": "q1 * 1.1 + q2 + q3 + q4"
}

The {fieldName}_formula convention stores the override. The runtime evaluates the override formula for this specific record; all other records continue using the column default.

Override Storage

Override formulas are stored in a _formula_overrides JSON column on the record:

{
  "total": "q1 * 1.1 + q2 + q3 + q4"
}

Override Validation

Override formulas are validated at write time:

1. The expression MUST parse as valid AEL.
2. The expression MUST reference only fields that exist on the resource (fixed or dynamic).
3. The expression MUST NOT create circular dependencies.
4. The result type MUST be compatible with the computed field’s declared type.

Invalid formulas are rejected with HTTP 422.

Clearing an Override

Setting the override to null reverts to the column default:

PATCH /api/v1/{domain}/{resource}/:id
{
  "total_formula": null
}

28.6 Reactive Computed Fields in Sheet Mode

When computedFields.reactive: true is set, the runtime maintains a dependency graph and automatically recalculates dependent values when source values change.

Same-Record Reactivity

computed:
  subtotal:
    type: money
    formula: "q1 + q2 + q3 + q4"
    stored: true
    reactive: true

  overhead:
    type: money
    formula: "subtotal * lookup(OverheadRates, 'rate', code == $this.rateCode)"
    stored: true
    reactive: true

  total:
    type: money
    formula: "subtotal + overhead"
    stored: true
    reactive: true

When q1 changes: subtotal recalculates → overhead recalculates (depends on subtotal) → total recalculates (depends on both). This is Excel’s dependency graph — but with typed columns, validation, permissions, and an audit trail.

Cross-Record Reactivity

When computedFields.crossRecord: true, formulas can reference other records and resources:

computed:
  categoryTotal:
    type: money
    formula: "sum(BudgetLine, 'total', category == $this.category)"
    stored: true
    reactive: true
    recomputeOn: [BudgetLine.create, BudgetLine.update, BudgetLine.delete]

When any BudgetLine record changes, the runtime recalculates all BudgetSummary records whose category matches. This is =SUMIF() with automatic refresh.

Dependency Graph Rules

• Circular dependencies are detected at parse time and rejected.
• Maximum chain depth is configurable (default: 10 levels).
• Per-cell formula overrides participate in the dependency graph — an override that references a different field changes the graph for that record.

28.7 Formula Language

Sheet mode formulas use AEL (§36) with additional functions for spreadsheet-like computation:

Excel-to-AEL Mapping

Lua Extension

For formulas that exceed AEL’s capabilities — financial functions (PMT, IRR, NPV), statistical analysis (LINEST, FORECAST), custom business logic:

computed:
  amortization:
    type: money
    formula: "lua:calculateAmortization(principal, rate, term)"
    stored: true
    reactive: true

Together, AEL + Lua cover everything Excel’s formula language does, with cross-resource lookups, aggregation, and conditional logic that reference the full ADL domain model.

28.8 Cell-Level History

Sheet mode integrates with the history system (§21) to track individual cell changes:

history:
  events:
    cellEdited:
      on: fieldChange
      field: "*"
      label: "{fieldName} changed"
      severity: info

    budgetRevised:
      on: fieldChange
      field: total
      capture: [total, category]
      label: "{category} total revised to {total}"
      severity: warning

    thresholdExceeded:
      on: fieldChange
      field: total
      condition: "total > budgetCeiling"
      capture: [total, budgetCeiling, category]
      label: "Budget ceiling exceeded for {category}"
      severity: error

The field: "*" wildcard triggers on ANY field change — fixed or dynamic. Every cell edit is tracked, every formula recalculation is auditable, and budget threshold violations fire automatically.

28.9 Sheet Mode API

Sheet mode resources use the standard ADL CRUD API with additional endpoints:

Column Listing Response

{
  "data": {
    "fixed": [
      { "name": "lineItem", "type": "string", "required": true },
      { "name": "q1", "type": "money", "default": 0 }
    ],
    "dynamic": [
      { "name": "q5", "type": "money", "default": 0, "description": "Q5 forecast", "addedBy": "user-1", "addedAt": "2026-05-17T10:00:00Z" }
    ],
    "computed": [
      { "name": "total", "type": "money", "formula": "q1 + q2 + q3 + q4", "reactive": true, "overridable": true }
    ]
  }
}

28.10 Integration Points

End of Chapter 28.

End of Volume III — The Intelligence Layer.

Next: Volume IV — Runtime Subsystems

Chapter 29: Authentication & Identity