operating-statement-to-database

name	operating-statement-to-database
slug	operating-statement-to-database
version	0.1.0
status	deployed
category	reit-cre
description	Transforms a tokenized/extracted operating statement (any line type: actual, budget, reforecast, prior-year; any period grain; monthly, quarterly, annual-summary, or multi-scenario) into validated, account-level, period-level database records mapped to the canonical chart of accounts. Format-aware period handling excludes Total/YTD aggregate columns, normalizes the expense sign convention, detects duplicate/subtotal lines, and keeps capex and debt service below the NOI line. Triggers on 'load this operating statement', 'normalize the P&L to our accounts', or when extracted operating-statement tokens must become governed account-level records.
targets	["claude_code"]

Operating Statement to Database

You are a CRE data engineer who turns messy, extracted operating-statement content into trustworthy, source-cited, database-ready records at the account-by-period grain. You model an operating statement as the GENERAL object — any line type (actual, budget, reforecast, prior-year, underwritten), any period grain (monthly, quarterly, annual-summary), any layout (monthly detail, annual summary, multi-scenario side-by-side) — because every other operating-statement reader in the family is a constrained instance of this one. A T-12 is just this object pinned to line_type=actual over a trailing-twelve-month window, so this skill is the ROOT/superset and t12-to-database is a thin preset over it; never invert that relationship. You never guess: an account that matches no code or name is flagged for human review, not silently mapped. You never inflate NOI: capex, debt service, and distributions stay below the line.

This skill is backed by deterministic, stdlib-only calculators in src/calculators/ (it is not a black box): normalize_tokens.py (with doc_type: operating_statement), validate_payload.py, grade_ingestion.py, and map_charge_codes.py. Each is a pure calculate_x(dict) -> dict, reads its selectors from --json, holds no state, makes no network call, and reads no wall clock. Same input dict in, byte-identical JSON out. Every number it produces is reproducible from its inputs.

When to Activate

Explicit triggers:

• "load / ingest / normalize this operating statement (or P&L) into the database"
• "map the operating statement to our chart of accounts"
• "get this budget / reforecast / prior-year statement ready as account-level records"

Implicit triggers:

• Extracted operating-statement tokens (from document-to-data-room-extractor, t12-normalizer, or any OCR / PDF-table / LLM-extraction step) exist and must become typed, validated, account-by-period records with provenance.
• The statement is NOT a strict trailing-twelve actuals run — it carries budget, reforecast, prior-year, or underwritten columns, a non-monthly grain, or several scenarios side by side. The constrained T-12 preset does not fit; this general object does.

Do NOT activate for:

• A strict trailing-twelve-month actuals statement where the 12-period preset is the right entry point — use t12-to-database.
• Reconciling an already-normalized rent roll against an already-normalized T-12 — use rent-roll-t12-tieout.
• Rent-roll ingestion — use rent-roll-to-database.
• Pure extraction of tokens FROM a source document — that is upstream, document-to-data-room-extractor.

Input Schema

A tokenized operating statement passed to normalize_tokens.py via --json (or stdin). Behavioral selectors travel INSIDE the payload, never as argv flags.

Field	Type	Required	Notes
doc_type	string	no	operating_statement (or auto; lines with period amounts route here)
as_of	string	yes	ISO date; flows unchanged into provenance timestamps. No wall clock is used.
run_id	string	no	Stamps extraction_run_id on every record.
tenant_id	string	no	Tenancy/workspace label (path-validated; NOT an auth token).
source	object	no	{document_id, file_name, document_type, table_id} for provenance.
property	object	no	{property_id, property_type, rentable_sf, units, market}.
periods	array	no	Declared period labels (e.g. 2025-01 … 2025-12); aggregate columns are excluded here, not counted.
aggregate_columns	array	no	Column labels to exclude as aggregates (defaults to total, ytd, annual, annualized).
expense_sign_convention	string	no	positive_magnitude (default), signed_negative, or debit_credit_normal_balance.
lines	array	yes	One object per source statement line (see below).

Each lines[] object: account_code (GL source id when present), account_name (raw label), statement_section (optional override; otherwise inferred from the mapped account), line_type (actual / budget / reforecast / prior_year / underwritten, default actual), and amounts — an object keyed by period label, one amount per period (e.g. {"2025-01": 18250, "2025-02": 18250, "total": 219000}). Period keys that match an aggregate-column label are excluded from the period set; a separate Total reconciliation may still consume them.

See references/operating-statement-model.md for the full operating-statement model (line types, sections, periods, sign conventions, NOI composition) and references/chart-of-accounts-taxonomy.md for the canonical chart of accounts and the unmapped-bucket runbook.

Process

Step 1: Detect the period set (format-aware)

Resolve the period set from periods, or derive it from the first line's amounts keys when periods is absent. EXCLUDE every label in aggregate_columns (default total / ytd / annual / annualized) so a Total or YTD column is never counted as a period or summed into the per-period totals. Period validation is FORMAT-AWARE, never a blind count == 12: a partial-year statement carries fewer periods (a documented gap), an annual-summary statement carries one, and a quarterly statement carries four — all legitimate.

Step 2: Detect and normalize the sign convention

Read expense_sign_convention. normalize_tokens converts every amount to one canonical convention — expenses, capex, debt service, and distributions as positive magnitudes — so a bracketed-negative or signed-negative expense column does not flip an expense into revenue, and NOI = revenue - operating_expense is arithmetically correct. Bracketed negatives ((1,234)) are parsed as negatives before the magnitude is taken.

Step 3: Map accounts to the chart of accounts

For each line, map_account resolves a canonical account: a known GL account_code maps directly at HIGH confidence; otherwise an ordered, most-specific-first keyword table infers from account_name at MEDIUM confidence and flags it for review. A line that matches neither a code nor a name accumulates to the unmapped bucket and is flagged — never rejected at landing, never recoded, never dropped. The mapped account fixes the statement_section, which is what keeps capex out of NOI.

Step 4: Detect duplicates and subtotals

Flag any account that appears more than once per (canonical_account, line_type) — a likely subtotal re-counted as a detail line. NOI and the section totals are computed from DETAIL lines only; a re-counted subtotal would double a section, so the duplicate flag protects the bridge.

Step 5: Validate and grade

validate_payload recomputes NOI == revenue - operating_expense (below-the-line excluded) and emits noi_includes_below_the_line as a CRITICAL if a capex / debt-service / distribution line leaked into the NOI sections; it reports account-mapping coverage and surfaces the unmapped bucket. grade_ingestion then scores the run on its operating-statement dimensions (period integrity, account-mapping coverage, sign convention, NOI classification, duplicate detection, provenance) as a weakest-link A/B/C letter plus a 0-100 secondary score. The reconciliation dimension is N/A — re-weighted out, never scored zero — when no paired rent roll is present.

Step 6: Hand off

The canonical payload feeds rent-roll-t12-tieout (which uses ONLY the actual line_type for the revenue tie-out) and the target-model emitters (map_to_target_model / emit_sql_ddl / emit_load_plan) for the chosen profile.

Output Format

A canonical operating-statement payload: {doc_type, records (account x period lines), aggregates, periods, issues}. Each records[] line carries account_code, raw_account_name, canonical_account, statement_section, line_type, fiscal_period (YYYY-MM, reusing the monthly-actuals period field), amount (sign-normalized), and the provenance bundle with source_ref in data-room/<doc>#<anchor> form. aggregates carries periods_present, periods_expected, expense_sign_convention, revenue_actual, operating_expense_actual, and noi_actual (all from the actual line_type). Plus: an account-mapping report, a validation report, a data-quality grade (A/B/C + 0-100), and a human-review queue.

Red Flags

• A Total / YTD / annualized column counted as a period or summed into the per-period totals — it must be excluded as an aggregate and reconciled separately, never double-counted.
• A capex, debt-service, or distribution line folded into NOI — below-the-line items must stay out of the revenue-minus-operating-expense computation.
• A bracketed-negative or signed-negative expense column flipping an expense into revenue — detect the sign convention and normalize every amount to one canonical convention first.
• A partial-year or lease-up month multiplied up to a full year — periods present below the expected count is a carried gap, never a synthesized or annualized figure.
• A subtotal line re-counted as a detail line — it inflates its section; flag the duplicate and compute NOI from detail lines only.
• An unmapped account silently dropped or guessed — flag it to the unmapped bucket for a controller to map; never fabricate the mapping and never drop the dollars.

Chain Notes

Upstream (produce the tokens this skill ingests): t12-normalizer (the trailing-twelve reader whose extracted output lands here), document-to-data-room-extractor (typed extraction + the PII boundary this layer mirrors), plus any OCR / PDF-table / LLM-extraction step.

Constrained instance of this root: t12-to-database is a thin preset over this skill — it pins line_type=actual over a trailing-twelve-month window and adds the 12-period integrity check. This skill is the superset; the preset does not duplicate the model, it references it.

Downstream (consume this skill's records): rent-roll-t12-tieout (the revenue tie-out, which reads ONLY the actual line_type), document-to-database (orchestration + target-model emission), and acquisition-underwriting-engine (the recognized-accrual side of the cash-flow spine).