RFC: SDTF Formula Field Builder

• Authors: Prathik Shetty(@pshettydev), Shaik Noorullah(@pnow-devsupreme)
• Date: 2025-12-04
• Status: Accepted

Abstract

This RFC proposes the implementation of a Formula Field creation capability within the SDTF (Schema-Driven Table Framework) FieldBuilderDialog. This feature enables administrators to define computed fields that derive their values from other fields in the same record using Jexl (JavaScript Expression Language) expressions. The implementation uses a client-side computation model to provide real-time updates and interactivity, similar to formula features in platforms like Airtable and Teable.

Motivation

Users and administrators need the ability to perform calculations, string manipulations, and logical operations on their data without requiring backend code changes or manual updates. Currently, derived data must be calculated manually or through backend processes, which is inefficient and lacks real-time feedback.

Key drivers for this feature include:

• Empowerment: Allowing non-technical administrators to create complex logic using a spreadsheet-like formula syntax.
• Real-time Feedback: Providing immediate computation results as data is viewed or modified in the browser.
• Flexibility: Supporting operations on text, numbers, booleans, and dates, including references to both native and custom fields.
• Performance: Offloading computation to the client to reduce backend load and latency.

Approaches

Approach 1: Client-Side Computation

In this approach, formula expressions are stored in the schema, but the actual evaluation happens in the user's browser using the Jexl library.

• Pros:
  • Zero Latency: UI updates immediately when dependent fields change.
  • Reduced Backend Load: The server simply stores the formula string; it doesn't need to compute values on read or write.
  • Interactive Experience: Users can see formula results while editing.
• Cons:
  • Data Consistency: The backend doesn't "know" the computed value, so sorting/filtering by formula fields requires database-level support or client-side processing (though this RFC focuses on the builder and display).

Approach 2: Server-Side Computation

Formulas could be computed by the backend on write (persisted computed columns) or on read (virtual columns).

• Pros:
  • Querying: Easy to sort, filter, and aggregate via standard SQL.
  • Consistency: The value is the same for all clients.
• Cons:
  • Complexity: Requires translating Jexl (or another syntax) to SQL or running a JS engine in the DB/backend.
  • Performance: Computed on read approaches can slow down queries. Persisted approaches require complex dependency tracking to trigger updates.
  • Latency: User must save and reload to see updated calculated values.

Decision: We proceed with Approach 1 (Client-Side Computation) to prioritize user experience and frontend performance, aligning with the "Introduction" and "Requirements" of the feature.

Proposal

The proposal implements a robust Formula Editor within the FieldBuilderDialog and integrates a runtime computation engine into the SDTF components.

Architecture

The solution involves three main parts:

1. Configuration (FieldBuilderDialog): A UI to define formulas with syntax highlighting, autocomplete, and validation.
2. Storage (Schema): Extending the TableField model to store formula metadata.
3. Runtime (SDTF Components): A service to evaluate formulas safely against row data.

Component Hierarchy

FieldBuilderDialog
├── TypePicker (includes FORMULA option)
├── FormulaEditor
│   ├── Monaco Editor (Jexl syntax, autocomplete)
│   ├── ReturnTypeSelector (TEXT, NUMBER, BOOLEAN, DATE, DATETIME)
│   ├── TestFormulaButton
│   └── FormulaHelpPanel

Data Model

The TableField schema will be extended to support the FORMULA type. The formula definition is stored in the formatting JSONB column.

// Extended TableField model concept
model TableField {
  // ... existing fields
  type            FieldType   // Includes 'FORMULA'
  is_custom_field Boolean     // true for formulas
 
  // formatting JSON structure for FORMULA
  formatting: {
    formula: string,       // e.g., "price * quantity"
    returnType: string     // "TEXT" | "NUMBER" | "BOOLEAN" | "DATE" | "DATETIME"
  }
}

Key Components

1. Formula Editor

A specialized component utilizing Monaco Editor to provide:

• Syntax Highlighting: For Jexl keywords and operators.
• Autocomplete: Suggests row.field_name and row.custom_fields.field_id.
• Real-time Validation: Uses jexl.compile() to catch syntax errors immediately.

2. Formula Validation Service

Responsible for ensuring formula correctness before saving:

• Syntax Checks: Validates valid Jexl structure.
• Reference Checks: Ensures all referenced fields exist.
• Circular Dependency Detection: Prevents infinite recursion by analyzing the dependency graph.

3. Formula Computation Service

A client-side service wrapping Jexl to evaluate formulas:

• Sandboxing: Jexl provides a secure environment, preventing access to globals like window or fetch.
• Context: Passes the row data as the context object.
• Transforms: Adds custom helpers like |currency, |upper, |round.
• Safety: Includes execution timeouts and error handling to prevent UI crashes.

Security

• Sandboxing: Strictly limits formula capabilities to the provided context (row data) using Jexl.
• Input Validation: Checks for malicious patterns and limits formula length.
• No Eval: Standard eval() is never used (which was being done earlier); Jexl parses and executes the expression tree.

Performance

• Caching: Computed values are cached and only invalidated when dependent fields change.
• Lazy Evaluation: Formulas are computed only when the cell is rendered (e.g., via IntersectionObserver).
• Timeouts: Long-running formulas are terminated to keep the UI responsive.