RecipeV01 Specification

This document is the normative RecipeV01 contract shared by TidyBank and tidycell. The key words MUST, MUST NOT, SHOULD, and MAY are to be interpreted as normative requirements.

1. Scope & versioning

RecipeV01 describes how cells from one workbook sheet become one or more tidy output tables. A conforming RecipeV01 document has version: "0.1"; consumers MUST reject any other version unless they implement that version’s separate contract.

The checked-in contract is TidyBank’s runtime-independent source of truth. A semantic change to this specification MUST arrive through a new contract import (or a future documented exchange process) and a version bump, as required by DESIGN.md §2. Existing version semantics MUST NOT change silently.

2. Addressing

R1C1 is the canonical address notation. A cell is R<row>C<col> with one-based decimal row and column numbers, for example R3C4. A range is two cells separated by a colon, for example R3C4:R8C7; its start MUST be above and to the left of, or equal to, its end. Parsers trim surrounding whitespace and normalize case before parsing.

A1 cells and ranges, such as d3 and d3:g8, are accepted case-insensitively by the A1 parsing entry points and normalize to the same row/column model. Recipe selector validation uses canonical R1C1 strings; A1 input therefore MUST be converted before it is stored in a RecipeV01 document.

Coordinates MUST respect the Excel worksheet limits:

MAX_ROW = 1_048_576
MAX_COL = 16_384
MAX_EXPANDED_RANGE_CELLS = 2_000_000 for any single range materialized by a recipe selector

Address failures use these codes:

EMPTY_ADDRESS: the input is not a non-empty string, or a bounding range is requested for an empty address list.
INVALID_CELL: a single cell does not match the selected notation.
INVALID_RANGE: range syntax is malformed, an endpoint is missing, or the start follows the end on either axis.
OUT_OF_BOUNDS: a row or column exceeds MAX_ROW or MAX_COL.

A syntactically valid range that exceeds MAX_EXPANDED_RANGE_CELLS is rejected by recipe validation before expansion.

3. Cell data types

Every parsed cell has exactly one data_type from:

A cell is treated as blank during selection and execution when its type is blank or its value is null or undefined.

4. Recipe document shape

The document shape is:

{
  version: "0.1",
  sheet: non-empty string,
  tables: non-empty Table[],
  options?: Options
}

A table is { name, values, headers, options? }. name is non-empty, values is { name, cells }, and headers is an array of header declarations. A values declaration’s name becomes its output column and cells is a cell selector.

All recipe, table, values, header, selector-object, predicate, and options objects are strict: unknown keys are rejected. Before validation, however, every object is traversed recursively (including objects inside arrays) and every property whose key begins with . is removed. Such diagnostic properties MAY contain arbitrary data and do not participate in execution.

Table names MUST be unique within a recipe. Header names MUST be unique within each table.

Output column names MUST NOT overwrite one another or provenance fields:

A values or header name MUST NOT be _source.
A header name MUST NOT equal the values name or another header name.
A name ending in _source MUST NOT have a prefix equal to a header name when that would collide with the generated <header.name>_source column.
A header name ending in _source MUST NOT have a prefix equal to the values name.

More concretely, values name period_source conflicts with header period; header period_source conflicts with header period or values name period.

5. Cell selectors

A cell selector has three input forms:

A string. A string containing : normalizes to { range: string }; any other string normalizes to a one-element address array.
An array of R1C1 cell addresses.
An object { range?, cells?, where? }, which MUST provide at least one of range or cells.

For an object selector, range addresses are expanded first and explicit cells are appended. Candidates are deduplicated and sorted in row-major order before predicates are applied. Resolved output addresses are also row-major.

The optional where predicate is strict and supports:

data_type: a non-empty array of allowed cell data types.
non_blank: true selects non-blank cells; false selects blank cells.
has_formula: matches whether a formula is present.
has_comment: matches whether a comment is present.
style_id: a non-empty array of non-empty style identifiers; a cell matches when the stable hash of its normalized style equals one of these identifiers.

A missing workbook cell behaves as a blank cell for predicate evaluation. Selector resolution records warnings for addresses outside the sheet’s used row/column range, predicates that filter every candidate, and selectors that resolve to no cells.

6. Headers

A header declaration is:

{
  name: non-empty string,
  direction: "N" | "W" | "NNW" | "WNW",
  direction_overrides?: { [R1C1 address]: direction },
  cells: CellSelector,
  fill?: "right" | "down",
  required?: boolean
}

For a value at (value.row, value.col), candidates are eligible as follows:

N: the header is in the same column and strictly above the value.
W: the header is in the same row and strictly left of the value.
NNW: the header is strictly above, header.col ≤ value.col, and header.spanEndCol ≥ value.col.
WNW: the header is strictly left, header.row ≤ value.row, and header.spanEndRow ≥ value.row.

Fill and spans

Explicit fill: "right" extends a header horizontally; fill: "down" extends it vertically. Without an explicit value, NNW implicitly fills right, WNW implicitly fills down, and N/W do not fill.

For right fill, a header’s span ends one column before the next header cell in the same direction group and row, or at the maximum selected value column when no next header exists. For down fill, it ends one row before the next header in the same direction group and column, or at the maximum selected value row. A non-filled span ends at the header’s own coordinate.

direction_overrides assigns listed resolved header cells to another direction group. The header’s explicit fill applies only to the group using its base direction. Override groups receive their own direction’s implicit fill (NNW → right, WNW → down) when applicable.

Candidate order and tie-breaking

Within one direction group, eligible candidates are sorted by descending score:

N: row
W: col
NNW: row * 1_000_000 + col
WNW: col * 1_000_000 + row

Thus N chooses the nearest row above, W the nearest column left, and NNW the nearest row above then greatest eligible column. WNW is ordered by the numeric score exactly as written: because MAX_ROW is 1_048_576, the row term can outweigh a one-column advantage, so it is not a strict nearest-column-then-row ordering. Equal scores retain insertion order.

Direction groups are created in the order their first resolved header address appears. Candidate lists are flattened in that group order and receive a monotonically increasing insertion rank. If overrides create more than one group, the flattened candidates are re-sorted by ascending Manhattan distance abs(value.row - header.row) + abs(value.col - header.col), then by insertion rank. With only one group, the direction score order is retained. The first candidate is selected.

7. Execution semantics

Selectors resolve before table execution. Per-table options merge from lowest to highest precedence as:

defaults ← recipe.options ← table.options

Option	Default	Effect
`include_blank_values`	`false`	Emit rows for selected blank value cells.
`preserve_source_address`	`true`	Include the value cell provenance object in `_source`.
`preserve_header_source_address`	`true`	Include `<header.name>_source` with the selected address or `null`.
`preserve_formatted_value`	`false`	Use a present formatted string instead of the raw cell value.
`preserve_non_table_cells`	`true`	Include unreferenced cells in the top-level result.
`include_blank_non_table_cells`	`false`	Include blank cells in `non_table_cells`.

For each resolved value address, execution obtains the workbook cell or a synthetic blank cell. It skips that cell when blank unless include_blank_values is true. An emitted row has this logical shape:

{
  [values.name]: raw-or-formatted value,
  _source?: { sheet, address, row, col },
  [header.name]: raw-or-formatted header value | null,
  [header.name + "_source"]?: R1C1 address | null
}

Header attachment is evaluated independently for each header declaration using section 6. A missing match emits null. Source-address fields are omitted only when their corresponding preservation option is false. When preserve_formatted_value is true, a present, non-empty formatted string replaces the raw value for both value and header columns.

A header attachment is ambiguous when at least one lower-ranked matching candidate has a raw cell value that is not strictly equal (!==) to the selected candidate’s value. Multiple candidates carrying equal values are not ambiguous.

All resolved value and header addresses count as referenced even when a blank value row is skipped. A value address selected by more than one table remains executable but produces an overlap warning.

preserve_non_table_cells and include_blank_non_table_cells govern a recipe-wide result, so their effective global values are read from defaults merged with recipe.options; table-level values do not alter that global list.

8. Warnings taxonomy

Execution warnings use these codes and trigger conditions:

SELECTOR_WARNING: wraps each selector-resolution warning. The underlying conditions are an address outside the sheet’s used range (OUT_OF_USED_RANGE), a predicate removing every candidate (PREDICATE_FILTERED_ALL), or any selector resolving to no addresses (EMPTY_SELECTION).
EMPTY_VALUE_SELECTION: a table’s resolved values selector contains no addresses.
OVERLAPPING_VALUE_CELL: a value address is selected by a table after a different table has already claimed it.
EMPTY_HEADER_SELECTION: a header’s resolved selector contains no addresses.
MISSING_REQUIRED_HEADER: an emitted value row has no selected candidate for a header with required: true.
AMBIGUOUS_HEADER: an emitted value row has a lower-ranked candidate whose raw value differs from the selected candidate’s raw value.
UNUSED_HEADER: a resolved header address was never selected for any emitted value row in its table.

Warnings do not by themselves abort execution.

9. Non-table cells

When preserve_non_table_cells is true, the result includes sheet cells not referenced by any resolved value or header selector. By default blank cells are omitted; include_blank_non_table_cells: true retains them.

Each non-table cell has:

{
  sheet,
  address,
  row,
  col,
  value,
  data_type,
  formatted?,
  formula?,
  comment?,
  style_id?,
  reason: "not_referenced_by_recipe"
}

formatted is emitted only when present and different from String(value); formula and comment are emitted when present. style_id is an optional contract field, although the reference executor does not currently populate it. The reason is always "not_referenced_by_recipe".

10. Sheet matching

A recipe’s sheet is matched against workbook sheets in two passes:

Return the first exact, case-sensitive name match.
If no exact match exists, lowercase both names, remove all whitespace, and return the first normalized match.

If neither pass finds a sheet, matching returns no sheet and the caller MUST report that the recipe sheet is unavailable.