cadrumo.application.storage.calc_sheets.records module

Strict records describing workbook plans produced from registry snapshots.

Every record is a frozen pydantic v2 model with extra=”forbid” so that schema drift surfaces as validation failures at the moment the engine assembles the plan rather than as silent payload divergence at the renderer boundary. The records are intentionally narrow: the engine produces them, the Google apply adapter consumes them, and the pull/parity adapters compare incoming workbook cell values back against the same plan.

A1 addressing

Workbook cell addresses are expressed by the SheetCellAddress record which carries the human-readable A1 string plus the structured tab name, row index, and column index. Row and column indices are 1-based to match Sheets convention. The a1 string is recomputed from the tab + row + column at construction time, so callers never hand-roll A1 strings — they always go through this record.

See also

cadrumo.domain.calculations.registry.schema.RegistrySnapshot

Registry snapshot compiled into these records by the engine.

SheetEvidenceFacet

Evidence facet carried by the plan and rendered by both workbook export paths.

class TabName(*values)

Bases: StrEnum

The tabs the engine emits in every workbook.

The set is fixed so that the pull adapter and the parity oracle have a stable layout to anchor against. New layers (for example a future “Pagos” tab for ingreso/devolución timings) are added by extending this enumeration; freeform tab names are rejected.

ENTRADAS

CALCULOS

PROVENANCE

TARIFFS

DETALLE

EVIDENCIA

GUIDE

column_index_to_letters(column)

Translate a 1-based column index to an A1 column letter string.

Converts a positive integer column index (1-based, matching Google Sheets’ convention) to the corresponding letter sequence used in A1 notation: 1 → "A", 26 → "Z", 27 → "AA", 702 → "ZZ".

Parameters:

column (int) – 1-based column index; must be ≥ 1.

Return type:

str

Returns:

Upper-case letter string, e.g. "A", "B", "AA".

Raises:

CalcSheetsRecordError – if column is less than 1.

column_letters_to_index(letters)

Translate an A1 column letter string to a 1-based column index.

Inverse of column_index_to_letters. Accepts one to three upper-case ASCII letters: "A" → 1, "Z" → 26, "AA" → 27.

Parameters:

letters (str) – Upper-case column identifier matching ^[A-Z]{1,3}$.

Return type:

int

Returns:

1-based integer column index.

Raises:

CalcSheetsRecordError – if letters does not match the expected pattern.

class SheetCellAddress(**data)

Bases: BaseModel

A single Sheets cell address, expressed as tab + row + column.

Parameters:

data (Any)

tab: TabName

row: int

column: int

a1: str

classmethod at(tab, row, column)

Construct a SheetCellAddress from tab, row, and column indices.

Derives the a1 string automatically so callers never hand-roll A1 notation.

Parameters:

• tab (TabName) – TabName enum member identifying the workbook tab.

• row (int) – 1-based row index.

• column (int) – 1-based column index.

Returns:

A validated SheetCellAddress instance.

Return type:

SheetCellAddress

qualified()

Return the cross-tab A1 reference (‘Tab Name’!A1).

Return type:

str

class SheetValueCell(**data)

Bases: BaseModel

A literal value the engine writes verbatim to a cell.

Used for operator-input cells (with value=None indicating “blank, awaiting operator entry”), for parameter mirror cells, and for the structural section/header labels.

Parameters:

data (Any)

address: SheetCellAddress

value: Decimal | str | bool | None

note: str | None

casilla_id: CasillaId | None

parameter: ParameterId | None

role: Literal['operator_input', 'parameter_value', 'label', 'metadata']

class SheetRoundingRule(*values)

Bases: StrEnum

How a computed sheet value is rounded before it is written.

Distinct from the casilla data-type vocabulary, which also carries money and integer: that one says what a value IS, this one says how it is rounded. A ratio is a data type and never a rounding rule, and integer-ceiling is a rounding rule and never a data type.

MONEY

INTEGER

INTEGER_CEILING

NONE

SheetRoundingRuleValue

The same rule for a strict record field.

alias of Literal[MONEY, INTEGER, INTEGER_CEILING, NONE]

class SheetFormulaCell(**data)

Bases: BaseModel

A computed cell whose value comes from a Sheets formula.

The formula string is exactly what is sent to the Sheets API, minus the leading “=” sign (the apply adapter prepends it). It must already be A1-resolved.

Parameters:

data (Any)

address: SheetCellAddress

formula: str

casilla_id: CasillaId

rounding_scale: int | None

rounding_rule: SheetRoundingRuleValue

note: str | None

class SheetCellConstraint(**data)

Bases: BaseModel

A declarative value constraint surfaced to one Sheets cell.

Mirrors the registry’s CasillaConstraints record into a Sheets setDataValidation rule. The apply adapter renders this as a condition block on the target cell so an operator who types an out-of-range value sees Sheets’s own validation banner reject it in the workbook UI.

The constraint also propagates to the cell’s note so the operator sees the legal grounding (“LIRPF art. 56 — non-negative”) even before they attempt invalid input.

Parameters:

data (Any)

address: SheetCellAddress

sign: CasillaSignConstraintValue

min_value: Decimal | None

max_value: Decimal | None

legal_refs: tuple[LegalRefId, ...]

casilla_id: CasillaId

class SheetRowSetColumn(**data)

Bases: BaseModel

One column of a SheetRowSet, mapping a binding id to a header cell.

Parameters:

data (Any)

binding: BindingId

header_address: SheetCellAddress

header_label: str

legal_refs: tuple[LegalRefId, ...]

class SheetRowSet(**data)

Bases: BaseModel

A repeating-row data block in the Detalle tab.

Mirrors the registry’s row-producer binding pattern (bindings declared with aggregation = { op = “rows”, grouping = “…” }). Each row in the workbook represents one operator-supplied detail record (e.g., one perceptor on modelo 190, one VIES counterparty on modelo 349, one foreign asset on modelo 720). Columns are declared per row-producing binding sharing the same grouping.

The engine emits a header row carrying header_label for each column. Operators add row data freely below; the apply adapter leaves the data area unprotected. The pull adapter reads the operator-supplied rows back into structured RowSetEdit records keyed by binding id.

legal_refs and source_refs ground the row-set against AEAT’s diseño-de-registro authority that mandates the per-record fields.

Parameters:

data (Any)

grouping: str

tab: TabName

header_row: int

first_data_row: int

columns: tuple[SheetRowSetColumn, ...]

legal_refs: tuple[LegalRefId, ...]

source_refs: tuple[SourceRefId, ...]

class SheetProtectedRange(**data)

Bases: BaseModel

A contiguous range the apply adapter marks read-only.

Cálculos, Procedencia, and Tarifas are always fully protected. The pull adapter inspects these to decide which cells operators are allowed to mutate.

Parameters:

data (Any)

tab: TabName

start_row: int

end_row: int

start_column: int

end_column: int

description: str

class SheetNumberFormat(**data)

Bases: BaseModel

Display-format directive for a numeric workbook cell.

Parameters:

data (Any)

address: SheetCellAddress

casilla_id: CasillaId

data_type: Literal['money', 'integer', 'percentage']

pattern: str

class SheetSectionHeader(**data)

Bases: BaseModel

Section-header styling directive for a section’s first label cell.

Marks the column-A cell where a casilla section first appears so both transports render it as a bold section header — the official AEAT workbooks group casillas under bold section banners for operator orientation.

Parameters:

data (Any)

address: SheetCellAddress

text: str

class SheetAnchor(**data)

Bases: BaseModel

An explicit labelled start / final anchor on the calculation flow.

start marks the opening of the operator-input region (Entradas); final marks the filing result (resultado / cuota) on Cálculos. Rendered as a labelled cell in both transports so the inputs→resultado flow is unambiguously oriented, mirroring the published workbook layout.

Parameters:

data (Any)

address: SheetCellAddress

kind: Literal['start', 'final']

label: str

class SheetStyledRange(**data)

Bases: BaseModel

A contiguous range tagged with a presentation role.

The engine emits one styled range per role-region (the header band, each section banner, the operator-input column, the computed column, the result cell, wrapped body columns). Both transports resolve role to a concrete fill / font / alignment through the shared theme palette. Later ranges win on overlap, so a narrow accent range (e.g. result) may be emitted after the broad column range it refines.

Parameters:

data (Any)

tab: TabName

start_row: int

end_row: int

start_column: int

end_column: int

role: StyleRole

wrap: bool

class SheetColumnWidth(**data)

Bases: BaseModel

A per-tab column width in approximate character units.

The renderer converts it to a pixel size (~7 px per character) for updateDimensionProperties. Sized so concept labels and legal-ref columns read without clipping.

Parameters:

data (Any)

tab: TabName

column: int

width: int

class SheetFrozenView(**data)

Bases: BaseModel

Per-tab frozen header rows / leading columns.

Freezes the column-title row (and, where useful, the leading label columns) so the header stays visible while an operator scrolls a long modelo.

Parameters:

data (Any)

tab: TabName

frozen_rows: int

frozen_columns: int

class SheetAutoFilter(**data)

Bases: BaseModel

A per-tab basic-filter range over the header row plus its data rows.

Lets the operator sort / filter the tab by section or concept. Offline maps it to worksheet.auto_filter.ref; online to setBasicFilter.

Parameters:

data (Any)

tab: TabName

start_row: int

end_row: int

start_column: int

end_column: int

class SheetProvenanceRow(**data)

Bases: BaseModel

One row of the Procedencia audit tab.

Each computed casilla emits a row recording the formula id, the rounding rule, the legal references, and the workbook cell that holds the value. Operators reading the workbook can audit every figure back to a registry source.

Parameters:

data (Any)

casilla_id: CasillaId

display_number: str

casilla_label: str

formula_id: FormulaId | None

rounding_rule: SheetRoundingRuleValue

legal_refs: tuple[LegalRefId, ...]

source_refs: tuple[SourceRefId, ...]

target_address: SheetCellAddress

class SheetEvidenceContributorRow(**data)

Bases: BaseModel

One ledger contributor rendered into the workbook evidence surface.

Parameters:

data (Any)

casilla_id: CasillaId

transaction_id: TransactionId

amount: Decimal

currency: IsoCurrencyCode

fx_rate: Decimal | None

value_in_eur: Decimal | None

taxable_base: Decimal | None

iva_rate: Decimal | None

iva_amount: Decimal | None

counterparty: str | None

attachment_ids: tuple[str, ...]

document_link_ids: tuple[str, ...]

legal_refs: tuple[LegalRefId, ...]

source_refs: tuple[SourceRefId, ...]

class SheetEvidenceManualEntry(**data)

Bases: BaseModel

One non-ledger fact basis entry rendered into the evidence surface.

Parameters:

data (Any)

casilla_id: CasillaId

value: str

kind: str

note: str

legal_refs: tuple[LegalRefId, ...]

source_refs: tuple[SourceRefId, ...]

class SheetEvidenceFacet(**data)

Bases: BaseModel

Evidence rows attached to a workbook export plan.

Contributor rows carry ledger-derived transaction facts by casilla; manual entries carry non-ledger fact basis values. The live renderer writes this facet to the Evidencia worksheet.

Parameters:

data (Any)

snapshot_fingerprint: ContentDigest | None

contributor_rows: tuple[SheetEvidenceContributorRow, ...]

manual_entries: tuple[SheetEvidenceManualEntry, ...]

class SheetTariffTableRow(**data)

Bases: BaseModel

One row of a parameter bracket table mirrored to the Tarifas tab.

Parameters:

data (Any)

lower_bound: DecimalValue

upper_bound: DecimalValue | None

fixed_addition: DecimalValue

marginal_rate: DecimalValue

valid_from: date

valid_to: date | None

class SheetTariffTable(**data)

Bases: BaseModel

A parameter mirrored into the workbook as a lookup table.

Two flavours are supported: scalar dated values and bracket tables (bracket_table parameters). The engine emits one of the two depending on the parameter’s data_type.

Parameters:

data (Any)

parameter: ParameterId

label: str

data_type: Literal['decimal', 'money', 'integer', 'ratio', 'bracket_table']

anchor: SheetCellAddress

scalar_value: DecimalValue | None

bracket_rows: tuple[SheetTariffTableRow, ...]

class ParameterCell(**data)

Bases: BaseModel

Pointer from a parameter id to its anchor cell in the Tarifas tab.

The translator consults this mapping when an expression references a parameter, so that the emitted Sheets formula reads from the mirrored value instead of inlining a literal.

Parameters:

data (Any)

parameter: ParameterId

anchor: SheetCellAddress

class OperatorInput(**data)

Bases: BaseModel

One pre-populated operator-input value.

Operator inputs are casilla values the caller already knows (for example from the ledger or from a previous filing). They are written as literal values into the Entradas tab; the operator is free to overwrite them in the workbook.

Parameters:

data (Any)

casilla_id: CasillaId

value: Decimal | str | bool | None

class OperatorInputs(**data)

Bases: BaseModel

Caller-supplied seed values for the Entradas tab.

Parameters:

data (Any)

values: tuple[OperatorInput, ...]

by_casilla_id()

Return a CasillaId -> OperatorInput lookup mapping.

Returns:

A Mapping keyed by canonical casilla.id. Later duplicates overwrite earlier ones; the registry enforces uniqueness so duplicates are not expected in practice.

Return type:

Mapping[CasillaId, OperatorInput]

class SheetRelationProvenance(*values)

Bases: StrEnum

Where a spreadsheet relation cell’s value came from.

Deliberately coarser than ObservationSourceKind, which splits the AEAT origins three ways for filing-grade evidence. This vocabulary answers a narrower question the workbook actually renders – did the operator type it, did a local filing supply it, or did it come off AEAT – and the two share the operator_manual token without being the same set. They must not be unified: collapsing them would let a sheet claim a filing-grade AEAT origin it never established.

LOCAL_FILING

Derived from a filing this installation holds locally.

AEAT_LIVE

Observed from AEAT, without distinguishing which AEAT surface supplied it.

OPERATOR_MANUAL

Typed by the operator, carrying no external evidence of its own.

SheetRelationProvenanceValue

The same vocabulary for a strict record or CLI payload field.

A bare enum under strict validation refuses the plain token a serialised row carries, so those fields take this literal over the members above rather than restating them.

alias of Literal[LOCAL_FILING, AEAT_LIVE, OPERATOR_MANUAL]

class RelationValue(**data)

Bases: BaseModel

One pre-resolved cross-revision relation value.

Relations aggregate values from a different modelo’s filings (typically rolling quarterly filings up into an annual summary). The local side resolves them through resolve_relation_values_from_observations and supplies the result here; the engine mirrors it as a scalar cell in Tarifas so the workbook’s formulas can consume it.

provenance carries the source tier the value came from: local_filing (operator’s own prior filing in SecureObjectRepository), aeat_live (remote AEAT Sede justificante parse), operator_manual (operator entered the value into Sheets directly). The engine stamps the provenance on the workbook so the pull adapter can detect stale prefills. The source_* and ref fields mirror the registry relation so the scalar workbook value remains joinable back to its official source modelo, source casilla, and legal grounding.

Parameters:

data (Any)

relation: RelationId

value: Decimal | None

dependency_treatment: str

The registry’s declared dependency treatment for this carry, empty when the revision declares none. A factual_evidence carry is a fact to reconcile against rather than a figure that settles the return, and a consumer must be able to tell it from a direct_annual_settlement one. Carried here rather than gated here: the value is NOT withheld, because a taxpayer is entitled to a suffered retención and dropping it silently is an over-declaration. Empty means the revision declared no treatment, which is not the same as any particular one and must never be read as one.

provenance: SheetRelationProvenanceValue

source_modelo: ModeloId | None

source_filing_year: FilingYear | None

source_periods: tuple[str, ...]

source_casilla_ids: tuple[CasillaId, ...]

legal_refs: tuple[LegalRefId, ...]

source_refs: tuple[SourceRefId, ...]

resolved_at: datetime | None

note: str | None

class RelationValues(**data)

Bases: BaseModel

Caller-supplied relation aggregations for the Tarifas tab.

Parameters:

data (Any)

values: tuple[RelationValue, ...]

by_relation()

Return a RelationId → RelationValue lookup mapping.

Returns:

A Mapping keyed by relation id for fast lookup when the engine resolves cross-revision formula references.

Return type:

Mapping[RelationId, RelationValue]

class SheetGuideContent(**data)

Bases: BaseModel

Plain-text content for the Guía tab.

The guide tab is the human-readable preamble: how to use the workbook, what each tab contains, and where to find the bidirectional sync command. Strings are caller-supplied (the CLI surface resolves them through tr() so locale parity holds).

Parameters:

data (Any)

title: str

paragraphs: tuple[str, ...]

class SheetExportMetadata(**data)

Bases: BaseModel

Stamps the workbook with the registry + engine identities.

Stored both in the workbook’s developer metadata (so the pull adapter can validate compatibility before merging operator edits) and rendered as plain-text in the Guía tab so a human reader can see provenance at a glance.

Parameters:

data (Any)

modelo_id: str

revision_id: RevisionId

filing_year: FilingYear

period: Period

engine_version: str

registry_sha: str

exported_at: datetime

class SheetExportPlan(**data)

Bases: BaseModel

Complete description of the workbook every renderer will write.

The plan is the shared contract between the registry-backed engine, Google Sheets apply adapter, pull adapter, and parity harness. It includes calculation cells, protected ranges, display facets, registry metadata, relation provenance, row sets, and workbook evidence.

Parameters:

data (Any)

metadata: SheetExportMetadata

value_cells: tuple[SheetValueCell, ...]

formula_cells: tuple[SheetFormulaCell, ...]

tariffs: tuple[SheetTariffTable, ...]

provenance: tuple[SheetProvenanceRow, ...]

protected_ranges: tuple[SheetProtectedRange, ...]

number_formats: tuple[SheetNumberFormat, ...]

section_headers: tuple[SheetSectionHeader, ...]

anchors: tuple[SheetAnchor, ...]

cell_constraints: tuple[SheetCellConstraint, ...]

row_sets: tuple[SheetRowSet, ...]

relation_provenance: RelationValues | None

evidence: SheetEvidenceFacet

font_family: str

styled_ranges: tuple[SheetStyledRange, ...]

column_widths: tuple[SheetColumnWidth, ...]

frozen_views: tuple[SheetFrozenView, ...]

auto_filters: tuple[SheetAutoFilter, ...]

guide: SheetGuideContent

all_addresses()

Return every cell address referenced by value or formula cells.

Useful for collision detection and for building the full write list before sending requests to the Sheets API.

Returns:

objects from value_cells followed by those from formula_cells, in declaration order.

Return type:

tuple[SheetCellAddress, …]