Cell metadata

Cell Metadata

Cell metadata is the layer that sits next to a cell value, carrying formatting, styling, validation, and conditional formatting rules. Metadata never changes the computed value — it changes how the value is displayed and validated.

This is distinct from type tags (A1 as currency = 100), which are part of the formula language and live in the model source. Cell metadata is set via the HTTP API at runtime, not in .grid source.

For the HTTP endpoints, see docs/api/http_api.md §Cell Metadata.

1. The `CellMetadata` Shape

Each cell can carry one optional CellMetadata record:

interface CellMetadata {
  unit?: UnitAnnotation;
  numberFormat?: NumberFormat;
  dateFormat?: DateFormat;
  style?: CellStyle;
  conditionalFormats?: ConditionalFormat[];
  visualizations?: CellVisualization[];
  validation?: ValidationRule;
}

Every field is optional. Setting one field doesn't clear the others.

2. Number Formatting (`numberFormat`)

interface NumberFormat {
  style: "decimal" | "percent" | "currency" | "scientific";
  currency?: string;          // ISO code, e.g. "USD"
  minimumFractionDigits?: number;
  maximumFractionDigits?: number;
  useGrouping?: boolean;      // thousands separator
  locale?: string;            // BCP-47, e.g. "en-US"
}

Examples:

{ "style": "currency", "currency": "USD", "minimumFractionDigits": 2 }
{ "style": "percent", "maximumFractionDigits": 1 }
{ "style": "scientific", "maximumFractionDigits": 3 }
{ "style": "decimal", "useGrouping": true, "maximumFractionDigits": 0 }

Underlying implementation uses the JavaScript Intl.NumberFormat contract.

3. Date Formatting (`dateFormat`)

interface DateFormat {
  pattern: string;
}

Patterns use Excel/Sheets-style tokens (yyyy, mm, dd, hh, mm, ss, am/pm). Example:

{ "pattern": "yyyy-mm-dd" }
{ "pattern": "mmm d, yyyy hh:mm am/pm" }

The same pattern strings are accepted by the TEXT() function in formulas.

4. Unit Annotation (`unit`)

A free-form unit label rendered alongside the value:

interface UnitAnnotation {
  label: string;
  position: "prefix" | "suffix";
}

{ "label": "$", "position": "prefix" }
{ "label": "kg", "position": "suffix" }
{ "label": "°C", "position": "suffix" }

Unit annotations are display-only — they don't affect the value or arithmetic. For semantic typing that does affect coercion and validation, use type tags (A1 as currency = 100).

5. Style (`style`)

interface CellStyle {
  bold?: boolean;
  italic?: boolean;
  textColor?: string;            // CSS color, e.g. "#FF0000"
  backgroundColor?: string;
  horizontalAlign?: "left" | "center" | "right";
  borderTop?: string;            // CSS border, e.g. "1px solid #999"
  borderRight?: string;
  borderBottom?: string;
  borderLeft?: string;
}

Style fields merge — partial PATCHes preserve existing fields:

existing: [ bold: true, textColor: "#000" ]
patch:    [ italic: true ]
result:   [ bold: true, italic: true, textColor: "#000" ]

This merge happens automatically in PATCH /api/models/:id/cells/metadata.

6. Validation (`validation`)

interface ValidationRule {
  type: "list" | "range" | "regex" | "custom";
  params: Record<string, unknown>;
  errorMessage?: string;
  allowBlank?: boolean;
}

`type`	`params` shape	What it checks
`list`	`{ "options": ["a", "b", "c"] }`	Coerced string value must be in `options`
`range`	`{ "min": number, "max": number }` (either optional)	Coerced numeric value must lie in `[min, max]`
`regex`	`{ "pattern": "..." }`	Coerced string value must match the JS regex
`custom`	(any)	Always valid; reserved for caller-side checks

If allowBlank is true, blank or empty values pass regardless of the rule.

errorMessage overrides the default message in ValidationResult.

Validation is not enforced on writes by default — it produces a ValidationResult { valid, message? } that callers (UI, API consumers) choose to surface or block on.

7. Conditional Formatting (`conditionalFormats`)

A list of conditions, each pairing a predicate with a style override:

interface ConditionalFormat {
  operator: ConditionalOperator;
  params: Record<string, unknown>;
  style: CellStyle;
}

Operators:

`operator`	`params` shape	Fires when
`greaterThan`	`{ "value": n }`	numeric value > n
`lessThan`	`{ "value": n }`	numeric value < n
`greaterThanOrEqual`	`{ "value": n }`	numeric value ≥ n
`lessThanOrEqual`	`{ "value": n }`	numeric value ≤ n
`equal`	`{ "value": v }`	numeric or string equals v
`notEqual`	`{ "value": v }`	numeric or string ≠ v
`between`	`{ "min": n, "max": n }`	numeric value ∈ [min, max]
`notBetween`	`{ "min": n, "max": n }`	numeric value ∉ [min, max]
`isEmpty`	`{}`	cell is blank
`isNotEmpty`	`{}`	cell is not blank
`textContains`	`{ "text": "..." }`	string value contains text
`textNotContains`	`{ "text": "..." }`	string value does not contain text

Multiple conditional formats may be set; the runtime applies them in order, and the last matching style wins per CSS field.

Example:

{
  "conditionalFormats": [
    { "operator": "lessThan", "params": { "value": 0 }, "style": { "textColor": "#C00000" } },
    { "operator": "greaterThanOrEqual", "params": { "value": 1000 }, "style": { "bold": true } }
  ]
}

8. Cell Visualizations (`visualizations`)

Cell visualizations are value-driven renderers. They change how a cell is drawn without changing the underlying value and without being predicate rules. They share the same metadata storage and API PATCH flow as other cell metadata.

type CellVisualization =
  | ColorScaleVisualization
  | ProgressBarVisualization
  | RelativeBarVisualization
  | BadgeVisualization
  | SparklineVisualization;

Supported visualization kinds:

`kind`	Purpose
`colorScale`	Interpolates cell background from min/mid/max colors over a range or column scope
`progressBar`	Draws an in-cell bar for direct percentage or normalized numeric values
`relativeBar`	Draws an in-cell bar relative to scoped range/column stats
`badge`	Draws compact status/risk/pass-fail labels from value predicates
`sparkline`	Draws a compact line/bar/win-loss chart from an array value or source range

Scopes are optional:

interface CellPresentationScope {
  kind: "cell" | "range" | "column";
  range?: string;  // e.g. "A1:A20" for kind: "range"
  column?: number; // zero-based column index for kind: "column"
}

Example:

{
  "visualizations": [
    {
      "kind": "relativeBar",
      "scope": { "kind": "column" },
      "fillColor": "#3b82f6",
      "negativeFillColor": "#ef4444",
      "zeroBaseline": true
    },
    {
      "kind": "badge",
      "rules": [
        { "operator": "greaterThanOrEqual", "params": { "value": 1 }, "label": "PASS" },
        { "operator": "lessThan", "params": { "value": 1 }, "label": "RISK" }
      ]
    }
  ]
}

Visualizations are presentation-only. CSV/export surfaces raw values, not rendered bars, badges, or icons. UI renderers should provide readable labels/tooltips when a visual effect conveys information not visible in plain text.

9. Comments

Comments aren't part of CellMetadata — they live in their own threaded structure that can attach to a cell, row, column, or whole sheet.

interface CommentThread {
  key: string;
  target: CommentTarget;
  comments: CommentEntry[];
}
 
type CommentTarget =
  | { kind: "sheet"; sheet?: string }
  | { kind: "row"; row: number; sheet?: string }
  | { kind: "column"; column: number; sheet?: string }
  | { kind: "cell"; symbol: string };
 
interface CommentEntry {
  id: string;
  commenter: string;
  message: string;
  createdAt: string;     // ISO-8601
}

A ModelSnapshot includes all comment threads keyed by their CommentThread.key. Add comments via POST /api/models/:id/comments. Updates are broadcast over the WebSocket as comment:update events.

10. Setting Metadata From The API

# Single-cell write (replaces all metadata for the cell)
curl -X PUT http://localhost:3000/api/models/portfolio/cells/B1/metadata \
  -H "Content-Type: application/json" \
  -d '{
    "metadata": {
      "numberFormat": { "style": "currency", "currency": "USD" },
      "style": { "bold": true, "horizontalAlign": "right" }
    }
  }'
 
# Bulk patch (merges into existing metadata)
curl -X PATCH http://localhost:3000/api/models/portfolio/cells/metadata \
  -H "Content-Type: application/json" \
  -d '{
    "metadata": {
      "A1": { "validation": { "type": "range", "params": { "min": 0 } } },
      "B1": { "conditionalFormats": [
        { "operator": "lessThan", "params": { "value": 0 }, "style": { "textColor": "#C00000" } }
      ]},
      "C1": { "visualizations": [
        { "kind": "progressBar", "min": 0, "max": 1, "fillColor": "#3b82f6", "label": "percent" }
      ]}
    }
  }'

11. Relationship To Type Tags

Type tags (in formula source) and cell metadata (set via the API) serve overlapping but distinct purposes:

Concern	Type tag	Cell metadata
Stored where	`.grid` source	Redis (next to value)
Affects computation	Yes (validation, coercion)	No
Affects display	Implicit defaults (currency tag → currency format)	Explicit fields
Versioned with model	Yes	No (per-cell, mutable at runtime)
Set by	Author of the model	UI / API consumers

The recommended pattern:

Use type tags to declare what a value is (currency, percentage, date) — durable in source.
Use cell metadata to declare how it should be displayed and validated on a particular deployment (currency code, decimal places, color thresholds) — mutable at runtime.

Many UIs use the type tag as a default and let users override via metadata.

12. Display Hooks For Domain Type Tags

Domain typeTag values give the renderer enough information to default to a sensible display without any extra metadata, but the schema reserves slots so a UI may override per-cell:

`typeTag`	Default display	Override metadata fields
`complex`	`re ± im·i` canonical (e.g. `3+4i`, `-2.5i`)	`numberFormat` for both parts
`url`	hyperlink with the host as label	`displayHint.urlLabel`
`molecule`	rendered formula (subscript digits)	`displayHint.useSubscripts`
`dna`	monospace sequence, optional 10-char grouping	`displayHint.dnaGroup`
`color`	swatch + hex	`displayHint.colorFormat` (`hex`, `rgb`, `hsl`)
`note`	scientific pitch (`A4`) + optional Hz tooltip	`displayHint.noteShowHz`
`bytes`	length + first 4 bytes hex (e.g. `4 B DEADBEEF`)	`displayHint.bytesEncoding` (`hex`, `base64`)

displayHint is an optional bag on CellMetadata; renderers that don't recognize a hint key simply fall back to the type's canonical default. The canonical defaults match formatComplexCanonical / format<Domain> in src/core/model/value.ts and src/core/types/domain/*.ts, so server-side stringification stays in lockstep with what the client renders.

13. See Also

docs/api/http_api.md — endpoints to read/write metadata.
reference.md §5.2 — type tags.
src/core/model/cellMetadata.ts — schema source of truth.
src/core/model/comments.ts — comment-thread schema.
src/core/model/validation.ts — validation evaluator.

Cell Metadata

1. The CellMetadata Shape

2. Number Formatting (numberFormat)

3. Date Formatting (dateFormat)

4. Unit Annotation (unit)

5. Style (style)

6. Validation (validation)

7. Conditional Formatting (conditionalFormats)

8. Cell Visualizations (visualizations)