# ITBI São Paulo — design

## Goal

A reproducible dataset of São Paulo real-estate transactions from the municipal
ITBI records (2019–2026), cleaned, with related rows aggregated into deals, with
validated coordinates, queryable from a CLI and an HTTP API, plus a map and an
analysis report.

Primary use is **R$/m² price analysis**; the same base also serves comps lookup,
market-trend work and general querying.

## Source

Secretaria Municipal da Fazenda publishes one XLSX per year:
<https://prefeitura.sp.gov.br/web/fazenda/w/acesso_a_informacao/31501>

* 2006–2026 available; **2019–2026 built by default** (the detailed 28-field
  layout starts in 2019).
* Each workbook: 12 monthly sheets plus `LEGENDA`, `EXPLICAÇÕES` (a real data
  dictionary), `Tabela de USOS`, `Tabela de PADRÕES`.
* One row = one DTI (*Declaração de Transações Imobiliárias*) actually paid.
* Buyer and seller identities are withheld for sigilo fiscal.

Source URLs are **pinned** in `sources.py` rather than scraped at run time, so a
run is reproducible; `itbi fetch --discover` reports drift against the live page.

## Defects found in the source, and what we do about them

These were all found empirically, not assumed. Each has a deterministic rule.

| Defect | Evidence | Handling |
|---|---|---|
| Header rows in arbitrary positions | `JAN-2024` has none at row 1 and one at row 12152; `FEV-2024` has them at rows 1 and 7877 | Content-based row classifier, not a fixed header row |
| **A data row poisoned with a header label** | `FEV-2024` r7877 is a real sale on AV LAVANDISCA whose SQL cell reads `N° do Cadastro (SQL)` | Row kept as data, cell nulled, `header_text_in_cell:*` flag. Dropping it would delete a real sale |
| The workbook's own header row is misaligned | labels `AA` and `AB` both `ACC (IPTU)`, omits "Descrição do padrão" | Column mapping comes from `EXPLICAÇÕES`, never from the header row |
| 1000-row blank tails | every monthly sheet | Classified `BLANK`, counted, reconciled |
| SQL stored as float | `1.01101181E8` | Cast to int, zero-pad to 11, split setor/quadra/lote/dv |
| `numero = 99999` placeholder | ~13% of rows | Normalised to null; excluded from the address key so unnumbered properties don't collide |
| `bairro` unusable | 55% filled, 2007 distinct values, polluted with condo names (`COND HOTEL FLORENCIO`) | Not used as geography; districts come from GeoSampa |
| Sheets keyed by **guia payment month**, not transaction date | `JAN-2024` contains an Oct-2023 transaction | `mes_guia`/`ano_guia` kept strictly separate from `data_transacao`; all series use the latter |
| `base_calculo` identity violations | 1,627 in 2024 | Natureza-aware: natureza 40 (excesso na integralização) uses `base = vvr_prop − valor` by design; `vvr_prop = 0` is degenerate, not inconsistent. Residual: 115 |
| Nominal-value sales | R$95–1,900 sales with `vvr_prop = 0` | `valor_transacao_nominal` flag; excluded from `is_market_sale` |
| Launch units carry the whole building's area | an `APTO 1906` of 2,185 m², an `AP 119` of 3,296 m² | `area_construida_building_level` flag; excluded from price stats |
| Byte-identical duplicate rows | 8,398 across 2019–2026 | **Ranked, not dropped** (`dup_rank`); callers filter `dup_rank = 1` |

## Architecture

Six stages, each an independent module writing a materialised artifact.

```
fetch → parse → clean → match → geocode → checks
                  ↘ combine ↗        ↘ report / map / api / cli
```

| Stage | Module | Output |
|---|---|---|
| fetch | `fetch.py` | `data/raw/itbi_YYYY.xlsx` + `manifest.json` (URL, SHA-256, size, timestamp) |
| parse | `parse.py`, `xlsx.py` | in-memory rows + per-sheet reconciliation report |
| clean | `clean.py`, `rules.py` | `data/interim/clean/itbi_YYYY.parquet` + flag counts |
| combine | `build.py` | `data/curated/transactions.parquet` (1 row = 1 DTI) |
| match | `match.py` | `data/curated/deals.parquet` |
| geocode | `geosampa.py`, `geocode.py` | `data/curated/geocode.parquet` |
| enrich | `enrich.py` | `data/curated/deals_enriched.parquet` |
| checks | `checks.py` | `validation/checks.json`, non-zero exit on failure |

The enrich stage pre-joins geography and the IPCA deflator onto the deals and flags
price outliers, so the query layer, report and map all read one flat table instead
of recomputing joins per request.

The SEF webserver returns **HTTP 502 without a browser User-Agent** — the fetch
stage sets one, and verifies the `PK` zip magic so an HTML error page never lands
as an `.xlsx`.

### All cleaning is a named rule

`rules.py` holds every transformation as an individually testable function.
`clean.py` composes them in a fixed order and counts each application. There is no
ad-hoc cleaning anywhere else, and no manual data edits: a defect found by hand
becomes a rule, and its firing count appears in the run report.

## The aggregation problem

There is no deal identifier. One row = one DTI = one SQL, so an apartment sold
with its garage space is two rows with nothing linking them.

A naive `address + date` merge is demonstrably wrong: a launch building where an
incorporadora sells many units on one day at one address would fuse into a single
fictitious mega-deal. Measured: the aggressive policy produces a 542-row "deal"
worth R$565M.

### Tiers

Deals carry the tier that produced them.

| Tier | Rule |
|---|---|
| **A** | exactly 1 main unit + ≥1 accessory, identical building, date, cartório and natureza |
| **B** | same, but dates within 5 days; refused if another main unit sits in the window |
| **C** | accessory-only group (garage spaces sold with no main unit present) |
| **SINGLE** | everything else — one row, one deal |

**Launch guard**: a group containing two or more main units is *never* merged.
`uso = 0` (terreno) is never absorbed either, since it doubles as the placeholder
for pre-individualised launch units.

Accessory uses are `{23, 24, 26, 63}` — condominium garages and storage. `62`
(garage *not* in a condominium) is a standalone asset and is deliberately excluded.

Building key prefers `(cep, logradouro, numero)`, falling back to
`(setor, quadra, cep)` when the street number is the 99999 placeholder.

### R$/m² denominator

Numerator is the sum of all member values; denominator is the **main unit's built
area only**. Adding a garage's area to the denominator would understate R$/m² on
exactly the deals we most want to measure.

### Measured results

* 38,688 tier A · 1,046 tier B · 2,062 tier C · 41,796 merged deals total.
* On affected deals the median R$/m² moves ~16%.
* On the citywide median it moves <1% — most deals are single-row. Both facts
  matter and the report states both.

## Validation

Buyer identity is withheld, so there is no ground truth. Three independent angles:

1. **Sensitivity** — rebuild under five policies (`no_merge`, `A`, `A+B`,
   `A+B+C`, `aggressive`) and measure how far the headline moves.
2. **Hand-labelled sample** — stratified groups dumped to
   `validation/labeling_sheet.csv` with everything needed to judge them; labels in
   `validation/labels.csv`; precision per tier in `validation/precision.json`.
3. **Matrícula proximity** — a corroborating signal the matcher never consults.

## Geocoding

GeoSampa's file-download portal needs an ASP.NET session; the **WFS service** is
used instead (`wfs.geosampa.prefeitura.sp.gov.br`), which returns EPSG:4326
directly. Paging requires an explicit `sortBy` — GeoServer refuses `startIndex` on
a layer without a primary key.

The join is not one-to-one: the `lote_cidadao` layer holds **lots and
condominiums**, not condominium units. A flat's SQL has no polygon; its building
appears as `cd_lote = 0000` with a `cd_condominio` index. Resolution order:

Rules run in order; the first that fires wins. Confidence is derived from the
estimated error radius rather than hard-coded per rule, because one rule's accuracy
varies with the data (a nearest-door-number match is excellent at a gap of 2 and
poor at a gap of 100).

| Rule | Est. precision |
|---|---|
| `sql_exact` — SQL matches a lot outright | 15 m |
| `sql_no_dv` — setor+quadra+lote match, check digit differs | 25 m |
| `condo_address` — same block, street and door number match | 30 m |
| `street_nearest_number` — same block and street, nearest door number | 30 m + 6 m per number of gap |
| `address_city` — citywide street+number match, spread ≤ 400 m | 60 m |
| `street_in_block` — street known in the block, no usable number | 90 m |
| `setor_street_nearest` — same street in the setor, nearest number | 120 m + gap |
| `street_near_block` — citywide street match constrained to within 2 km of the block | 150 m + gap |
| `block_centroid` — fiscal block centroid | 175 m |

### Measured coverage (1,687,527 lots, 743,138 distinct SQLs)

| Rule | SQLs | Share |
|---|---|---|
| `condo_address` | 521,087 | 70.12% |
| `sql_exact` | 179,414 | 24.14% |
| `block_centroid` | 39,719 | 5.34% |
| `address_city` | 2,539 | 0.34% |
| `sql_no_dv` | 288 | 0.04% |
| unresolved | 91 | 0.01% |

**99.996%** of SQLs resolve to a coordinate — 30 of 743,138 remain unresolved.
**95.8%** carry an assigned precision estimate of 100 m or better.

That second number is a *label*, not a measurement: it counts SQLs whose resolving
rule claims ≤100 m. The calibration below measures real error, but only for rules
replayed against SQLs that also matched a lot exactly — a subpopulation skewed
toward houses and terrenos rather than the condominium units that dominate the
data. Treat the per-rule error table as evidence about the rules, not as a
measured accuracy figure for the whole dataset.

Adding the interpolation rules cut the block-centroid fallback from 39,719 SQLs to
17,023 and unresolved from 91 to 29.

### Validation — three independent checks, not assumptions

* **Lot area cross-check.** For SQLs matched outright, GeoSampa's recorded lot area
  is compared with the lot area in the ITBI workbook — two separate publications
  of the same cadastral fact. **99.68% agree within 2%**, median absolute
  difference **0.0 m²**. This is the strongest evidence the join is right.
* **Rule agreement.** SQLs resolvable both by exact cadastral match and by address
  are compared by distance: median **0 m**, **99.96% within 100 m** (n = 163,501).
* **Block vs point districts.** Assigning the district per fiscal block rather than
  per point agrees with point-in-polygon on **98.7%** of a 20,000-row sample.
* **Rule calibration.** Every fallback rule is replayed against the 179k SQLs that
  match a lot outright — as close to ground truth as this data offers — and its real
  error measured, so `geo_precision_m` is a measurement rather than an assertion:

  | Rule | median error | p90 | within 100 m |
  |---|---|---|---|
  | `condo_address` | 0 m | 0 m | 100.0% |
  | `street_nearest_number` | 22 m | 59 m | 97.8% |
  | `street_in_block` | 32 m | 87 m | 93.1% |
  | `setor_street_nearest` | 50 m | 102 m | 89.6% |
  | `block_centroid` | 56 m | 174 m | 77.4% |

Plus: zero coordinates outside the municipal bounding box, and all 96 districts
represented.

## Inflation

R$/m² across 2019–2026 is meaningless in nominal terms. IPCA index (IBGE series
1737, variable 2266) is fetched and cached; the report shows real and nominal.
Cumulative inflation over the window exceeds the nominal price increase, so the
real median price of a São Paulo apartment **fell**.

## Headline filter

`is_market_sale` = natureza 1 (compra e venda) **and** 100% transferred **and**
`valor ≥ R$5,000` **and** `vvr_proporcional > 0` **and** `dup_rank = 1`.
Everything else stays in `transactions`, just out of the price statistics.

## Interfaces

* `itbi` CLI — `fetch, clean, combine, match, geosampa, geocode, checks, validate,
  search, agg, sql, serve, report, map, all`
* HTTP API — `/deals`, `/aggregate`, `/sql` (read-only, keyword-guarded),
  `/quality`, `/docs`
* `data/curated/itbi.duckdb` with views over the parquet files
* `reports/analysis.html`, `reports/map.html` — self-contained, no CDN

## Testing

Unit tests are anchored on real defects and real groups pulled from the published
files: the `FEV-2024` r7877 poisoned row, the R GENEBRA apartment+vaga merge, the
R ERNESTO DE CASTRO four-apartment launch that must not merge, SQL zero-padding,
Excel date conversion, the complemento tokenizer and the documented identities.


## Price outliers

`is_clean_price` is the series most analysis should use: a market sale whose R$/m2
is inside absolute bounds and not an outlier against its peers.

Outliers are found with a robust statistic — median and median absolute deviation
of *log* price — because the mean and standard deviation are themselves dragged
around by the values being hunted. Judgement is local: a R$/m2 that is ordinary in
Itaim Bibi is extreme in Grajaú, so each deal is compared against its own
(distrito, asset class, year) cell, falling back through progressively wider peer
groups when a cell is too small. 97.4% are judged against the tightest group and
**no market sale escapes the test**.

Two traps this had to survive:

* **A collapsing MAD.** Some peer cells are a bulk sale of near-identical units
  (CAMPO GRANDE 2024 comercial: 42 deals, 15 distinct prices). The raw MAD tends to
  zero and turns ordinary variation into a z-score in the thousands, so the
  dispersion is floored at 0.10 in log space.
* **Nominal vs real.** The absolute bounds must apply to deflated prices. Applied to
  nominal ones, a 2019 sale at R$115k/m2 passed a R$120k ceiling and then displayed
  as R$172k/m2 in today's money.

Result: 3.1% of market sales flagged, which moves the residential median R$/m2 by
about 1% and the mean rather more — outliers hurt the mean, which is why the
headline figures are medians.

## The interactive explorer

`reports/explorer.html` carries its dataset inside the page as base64 binary
tables — 23 bytes per building, 8 per building-year, 10 per typology — which
decode into typed arrays on load. The basemap is the one thing fetched: MapLibre
GL over OpenFreeMap vector tiles, no API key. Without a network it falls back to
the GeoSampa district geometry inlined in the page, so the file still opens.

Pins are **buildings**, not transactions — one address that sold nine times is one
pin with nine sales behind it. What a pin means depends on how far out you are:

| Zoom | Unit | Label |
|---|---|---|
| below 12.2 | distrito | name + weighted median R$/m2 |
| 12.2 to ~17 | grid cell, 300 px wide falling to 90 px | median + how many buildings |
| beyond | one building | its own median |

Bin size interpolates with zoom because a fixed cell gives either a carpet of
numbers at street level or nothing at city level. Filters (type, period, price
band, minimum sales) re-filter all 162k buildings client-side in a few
milliseconds; the card list beside the map tracks the viewport.


## What a building is, beyond its price

The workbook records what a building sold for and never what it is. Three
sources fill that in, in ascending order of cost:

**Fields already in the workbook.** `complemento` yields the parking count
("AP 43 E 2 VG") for 9.5k buildings and a storey estimate from the unit number;
grouping sales into 10 m2 bands recovers the unit mix for 23.6k. That last one
matters more than it sounds: at R. João de Lacerda Soares 31 a 50 m2 unit goes
for R$ 10,370/m2 and a 100 m2 unit for R$ 7,990 — a single median across the
building hides a 30% spread. Bands need three sales and 15 m2, or garages sold
on their own show up as floor plans.

**GeoSampa.** `estacao_metro` and `estacao_trem` give 183 operating stations,
deduplicated across interchanges; the nearest one is computed in the page rather
than stored per building. `edificacao` carries a measured height, and is *not*
used: it is a 2014 survey, so a 2023 tower returns the height of the house
demolished to build it. Wrong is worse than absent.

**Researched per address.** `data/reference/edificios.json` is authored, never
generated: names, storey and unit counts, developers, one entry per address with
the URLs behind it. `edificios.py` refuses an entry that asserts anything without
a source. Amenity lists were dropped after the pilot — portals emit near-identical
checkboxes for a 1976 walk-up and a 2012 tower.

**The workbook's area is the IPTU cadastre's, common areas included.** For a
condominium unit it is the fração ideal applied to the whole building's built
area: `area_construida / fracao_ideal` is constant across a building's rows. At
R. Canário 130 the cadastre records 675–789 m² for units the listings advertise
at 406–463 m² private — a factor of ~1.6, and it varies per building with the
share of garages and amenities. So the R$/m² here runs below the "private" R$/m²
of the portals while staying consistent within the data. The private area lives
only in listings, so it is a researched fact: `area_privativa_min`/`_max` in
edificios.json carry the advertised range of the plantas, and
`scripts/research_edificios.py` asks the agent per address and confronts each
answer with the building's own IPTU areas — an implied factor near 1 means the
agent copied the cadastre, far above 2 means it found another building. The
explorer shows both areas side by side and says why they differ; when the
smallest cadastre planta over the smallest advertised one lands inside 1.05–2.5,
it also applies that factor to the median and offers an estimated private-m²
price, labelled as the estimate it is.


## Deciding which `referencia` strings are names

`referencia` names a condominium in about a third of rows, mixed with block
letters, lot references and the odd bairro. The first rule kept values starting
with ED or COND, which dropped THERA FARIA LIMA and admitted "12 ANDAR".

Two properties of the value itself work better. A name belongs to few addresses —
BLOCO A turns up at 149 and SAO PAULO at 370 — and it contains at least one word
that is not a positional designator, which is what separates TORRE B PLAZA II
from LT 38 QD 15B. Neighbourhoods are refused by matching against the districts
and subprefeituras already in the data, not by counting.

This is measurable rather than arguable, because the city supplies an answer key.
`scripts/bench_edificios.py` takes buildings whose own sales agree on a name and
whose name appears at one address, and scores anything against them. The parser
gets 111 of 118. Calibrating the address limit on that key moved it from 3 to 6;
fixing two bugs it exposed — addresses counted per rounded coordinate, and
`any_value()` picking a blank row over the rows carrying the name — moved named
buildings from 19,597 to 25,573.

The same key scores research agents, which is the point of building it: ask for
the address alone, withhold the name, compare. gpt-5.6-luna scores 0.99 precision
and 0.805 recall over the 118. Precision is the number that matters — it abstains
on the rest, which costs a lookup, where a wrong name would ship looking exactly
as certain as a right one.

Comparing names needs care of its own. The workbook abbreviates (VL.NOVA) and
truncates at twenty characters (RES MIRANTE DE PARQU), so the comparison expands
shorthand, tolerates truncation, and lets a token of three letters or fewer match
on its initial once two words have matched in full. Every loosening was made
while looking at disagreements, which is how you end up measuring nothing, so it
is checked the other way too: across 1,416 shuffled name pairs it matches 0.07%.


## What an external review found

`docs/CODEX_REVIEW.md` is an adversarial review run by OpenAI Codex against the
built dataset. It found real defects that the in-house checks missed, all since
fixed:

| Finding | What was wrong | Fix |
|---|---|---|
| Identity hash too narrow | `transaction_id` covered 8 fields and omitted financing, so 4,168 non-identical rows were ranked away as "duplicates". SQL 09309601965 has 284 rows on one date sharing a matrícula but carrying 160 different mortgage amounts — 284 fractional buyers, not 284 copies | hash the entire raw source row; non-identical suppression is now 0 |
| Stale market flag | `is_market_sale` AND-ed the global rank with a per-workbook flag, leaving 44 canonical rows permanently non-market | `market_eligible` stored separately; the flag is derived from the global rank |
| Accessory-only denominator | R$/m² for garage-only groups divided the value of N boxes by the area of one | denominator is the sum of member areas when there is no main unit |
| Non-market value leak | 4,674 "market" deals absorbed members that were not market sales, including a garage sold at a 26.5% share whose value still entered the price | a deal is a market sale only if every member is |
| Map pins merged by coordinate | one pin could span several addresses and asset classes, so its class filter and label were wrong | group by coordinate + street + number + asset class |
| Fake real values | `coalesce(deflator, 1.0)` relabelled nominal prices as real when a month was missing | IPCA fetched from its 1993 base month; no coalesce; missing deflator flagged |
| Discarded source values | 8 rows spill a value into column `AC`; the schema guard sampled only the first 201 rows per sheet | 2 genuine `ACC` spills recovered as a named rule, the rest flagged, any other overflow column now raises |
| Overclaimed geocoding | "100% resolved" (really 99.996%) and "95.7% within 100 m" presented as measured when it counts an assigned label | both corrected here and in the checks output |

It also confirmed as sound: the launch guard, one-deal-per-row membership, the
single-main numerator/denominator, parse conservation, absence of join fan-out,
and the direction of the headline real-price finding under the robustness tests.