# qipu data: data dictionary (v0.7.1)

**A claim says what a record states, not that it is true. Truth needs corroboration.**

Generated from the table contracts (`contracts/`) and the tables as built (`scripts/45_docs.py`). Files: `releases/v0.7.1/parquet/<table>.parquet`. Ids are stable across rebuilds. Empty columns are columns of the design the data cannot fill yet; nothing is invented to fill them.

## Tables (generated from contracts/)

Every table and column below comes from its contract (`contracts/parquet/<table>.yaml`), which the build checks on every release. Privacy class: what kind of data a column can hold; public copy: what the published file does with it. The embargo: names of private people are withheld for 75 years after the record (San Francisco) or 92 (Vancouver); a record with no year is treated as under the embargo.

## `address_resolution` (805,748 rows)

Raw "<number> <street>" texts seen in claims, resolved exactly to today's EAS address. Only exact, unique matches in v1.

| column | type | null | values / unit | privacy | public copy | meaning |
|---|---|---|---|---|---|---|
| `raw_text` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | A `<number> <street>` place text as printed in a claim. |
| `year` | INTEGER | yes |  | none | as is | Year of the claim. |
| `place_id` | VARCHAR | no | e.g. `qipu:sf/place/block/1` | none | as is | Today's EAS address it matched (see `places`). |
| `precision` | VARCHAR | yes | `address` | none | as is | `address` on every row. |
| `method` | VARCHAR | yes | `exact` | none | as is | `exact` on every row: number and street matched exactly one DataSF EAS address. |
| `confidence` | DECIMAL(2,1) | yes |  | none | as is | `1.0` on every row. |
| `run_id` | VARCHAR | no | `address-exact-v1` | none | as is | `address-exact-v1` (see `runs`). |

## `block_points` (5,102 rows)

One point per assessor block (the map's block index), for fast lookups.

| column | type | null | values / unit | privacy | public copy | meaning |
|---|---|---|---|---|---|---|
| `block` | VARCHAR | yes |  | none | as is | assessor block number |
| `lat` | DOUBLE | yes |  | none | as is | a point inside the block |
| `lon` | DOUBLE | yes |  | none | as is | a point inside the block |
| `hood_id` | VARCHAR | yes |  | none | as is | the neighbourhood place id |

## `cities` (2 rows)

City configuration (config/cities/*.json): namespace, currency, privacy embargo, address authority.

| column | type | null | values / unit | privacy | public copy | meaning |
|---|---|---|---|---|---|---|
| `city` | VARCHAR | no | `sf`, `van` | none | as is | City code used in ids: `sf`, `van`. |
| `name` | VARCHAR | yes | `San Francisco`, `Vancouver` | none | as is | City name. |
| `country` | VARCHAR | yes | `CA`, `US` | none | as is | Country code (`US`, `CA`). |
| `jurisdiction_id` | VARCHAR | no | `qipu:sf/place/city`, `qipu:van/place/city` | none | as is | The city's place id (`qipu:<city>/place/city`). |
| `currency` | VARCHAR | yes | `CAD`, `USD` | none | as is | Currency of amounts (`USD`, `CAD`). |
| `privacy_embargo_years` | INTEGER | yes |  | none | as is | Years after a record's date before private people's names are shown: 75 (SF), 92 (Vancouver). |
| `address_authority` | VARCHAR | yes | `City of Vancouver property addresses (open data)`, `DataSF Enterprise Addressing System` | none | as is | The address register places are matched to. |
| `wikidata` | VARCHAR | yes | `Q24639`, `Q62` | none | as is | Wikidata id. |
| `bbox` | DOUBLE[] | yes |  | none | as is | `[west, south, east, north]` in degrees. |

## `claim_reliability` (8,367,105 rows)

How well each claim is backed (qipu_data/reliability.py, phase e). A claim says what a record states, not that it is true; the tier says how firmly the claim is tied to its record and how well its reading was measured. verified = checked by a person against the scan, or stated by 2+ independent documents; quoted = its quote is on the cited page and its run measured at or above 0.9; unverified = everything else (the reason says which). Not the same as `claims.reliability`, which only says whether the run that read the claim was scored (`measured` / `unmeasured`). From v0.6.2 also in the DuckDB.

| column | type | null | values / unit | privacy | public copy | meaning |
|---|---|---|---|---|---|---|
| `claim_id` | VARCHAR | no | e.g. `c:c4ca4238a0b923820dcc509a6f75849b` | none | as is | The claim (see `claims`). |
| `quote_check` | VARCHAR | no | `found`, `not_found`, `no_ocr`, `no_quote` | none | as is | Is the claim's quote on the cited page? `found` (its first words in order and 80% of its words are in the page's OCR text, or the next page's), `not_found`, `no_ocr` (no page text to look in yet), `no_quote`. |
| `run_accuracy` | DOUBLE | yes | share 0-1; 0..1 | none | as is | The measured accuracy of the run that read the claim (its headline score in `field_scores`). Empty: the run is unmeasured. |
| `run_accuracy_against` | VARCHAR | yes | `human`, `model_key` | none | as is | What `run_accuracy` was measured against: `human` (a hand-checked key) or `model_key` (a key written by a stronger model, not checked by a person). Shown separately: they are not the same evidence. |
| `run_accuracy_n` | BIGINT | yes | 0.. | none | as is | How many items the score was measured on. |
| `support_count` | BIGINT | no | 1.. | none | as is | How many extracted rows read this same claim (`claim_support`); several readings of one page are not independent. |
| `independent_sources` | BIGINT | no | 1.. | none | as is | How many different documents state the same subject, property, value and date. |
| `checks_failed` | VARCHAR[] | yes |  | none | as is | Consistency checks this claim failed: `tally_count` / `tally_outcome` (the printed vote tally disagrees with the extracted votes or outcome), `tally_ocr_count`, `date_outside_volume` (the date is outside the years of the cited volume). |
| `human_verdict` | VARCHAR | yes | `right`, `partly`, `wrong` | none | as is | The hand check of this claim or its decision, when a person checked it against the scan (the worst field verdict). |
| `flag_status` | VARCHAR | no | `none`, `pending`, `corrected`, `rejected` | none | as is | A reader's flag on this claim: `pending` (waiting for review), `corrected`, `rejected` (reviewed, the claim stands). |
| `tier` | VARCHAR | no | `verified`, `quoted`, `unverified` | none | as is | verified / quoted / unverified (see the table description). Not a truth score. |
| `tier_reason` | VARCHAR | no |  | none | as is | Why the claim has its tier, in plain words. |

## `claim_support` (6,594,525 rows)

One row per staged extraction row behind a claim: the same statement read by several runs or layers is one claim with several supports.

| column | type | null | values / unit | privacy | public copy | meaning |
|---|---|---|---|---|---|---|
| `claim_id` | VARCHAR | no |  | none | as is | Stable id: `c:` + 32 hex (128 bits, hashed from the slot: subject, property, object, page, role); votes `v:<rollcall_id>:<n>`. |
| `extraction_table` | VARCHAR | yes | `bos_records`, `dateline`, `dl_documents`, `doors`, `hand_decisions`, `hood_stories`, `hood_timelines`, `map_block_items`, `map_blocks`, `media`, `resweep`, `showcase_search` ... | none | as is | Which table or file the reading came from. |
| `row_key` | VARCHAR | no |  | raw_text | names of private people withheld under the embargo | The row inside that table or file (its `seq`, else the staged claim id). |
| `run_id` | VARCHAR | no | `bos-corpus-2026-09`, `dateline-2026-10`, `dl-tags-haiku55-2026-10`, `doors-2026-10`, `hand-2026-10`, `hood-stories-2026-10`, `hood-timelines-2026-10`, `ia-fulltext-places-2026-07`, `map-blocks-2026-10`, `map-blocks-items-2026-10`, `media-registry-2026-10`, `resweep-d1960-haiku55-2026-10` ... | none | as is | The extraction run (see `runs`). |
| `confidence` | DOUBLE | yes |  | none | as is | 0-1 where the run gives one. |
| `agrees` | BOOLEAN | yes |  | none | as is | `true` on every row in v0.4. |

## `claims` (8,367,105 rows)

The core table: one small statement per row, the same shape for every source (DATA_ARCHITECTURE.md 7.4).

| column | type | null | values / unit | privacy | public copy | meaning |
|---|---|---|---|---|---|---|
| `subject_id` | VARCHAR | no |  | none | as is | What the statement is about: a decision (`qipu:sf/decision/...`), a place, a person (`qipu:sf/person/...`) or an archive item (`ia:<identifier>`). |
| `subject_label` | VARCHAR | yes |  | free_text | where property = 'voted': as is; where extraction_table IN ('showcase_search', 'media', 'dl_documents'): as is; otherwise: names of private people withheld under the embargo | Readable label of the subject. |
| `property` | VARCHAR | no | `outcome`, `action`, `voted`, `moved`, `seconded`, `sponsored`, `reported_by`, `party`, `money`, `rezoned`, `refers_to`, `acts_on` ... | none | as is | Controlled list: `outcome`, `voted`, `concerns_place`, `built`, `depicted_in`, `recorded_at`, `happened_at`, `ballot_result`. |
| `property_detail` | VARCHAR | yes |  | none | as is | The source file's own name for the statement (e.g. `sanborn_map_shows`, `voted_aye`, `timeline_moment`). |
| `object_id` | VARCHAR | yes |  | none | as is | The other entity, when there is one (the decision a supervisor voted on; the place an archive item concerns). |
| `value_num` | DOUBLE | yes |  | none | as is | Numeric value (e.g. the year built from the assessor roll). |
| `value_text` | VARCHAR | yes |  | free_text | where property IN ('party', 'owned_by', 'occupied_by'): shown for officials and organisations; a private person is withheld under the embargo; where extraction_table = 'x_enrich' AND property IN ('sold_for', 'leased', 'conveyed', 'took_position'): shown for officials and organisations; a private person is withheld under the embargo; where extraction_table = 'x_enrich' AND property IN ('held_role', 'sponsored', 'excused_from_vote'): as is; where property = 'voted': as is; where extraction_table IN ('showcase_search', 'media', 'dl_documents'): as is; otherwise: names of private people withheld under the embargo | Text value: the outcome, the vote (yes/no/absent/excused), the line as written. |
| `summary` | VARCHAR | yes |  | free_text | where property = 'voted': as is; otherwise: names of private people withheld under the embargo | For `outcome` claims: the one-line plain summary of the decision. For votes: the printed outcome phrase. |
| `unit` | VARCHAR | yes |  | none | as is | Empty in v1. |
| `currency` | VARCHAR | yes | `USD`, `CAD` | none | as is | Empty in v1. |
| `value_year` | INTEGER | yes |  | none | as is | Empty in v1 (money is not yet extracted). |
| `t_from` | VARCHAR | yes |  | none | as is | When: YYYY-MM-DD, YYYY-MM or YYYY. |
| `t_to` | JSON | yes |  | none | as is | End of an interval (empty in v1). |
| `t_precision` | VARCHAR | yes | `day`, `month`, `season`, `year`, `decade`, `century`, `unknown` | none | as is | `day`, `month` or `year`. |
| `place_id` | VARCHAR | yes | e.g. `qipu:sf/place/block/1` | none | as is | Where (a qipu place id; `qipu:sf/place/city` for citywide). |
| `place_label` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | The place as written in the source. |
| `place_precision` | VARCHAR | yes | `point`, `address`, `building`, `parcel`, `corner`, `segment`, `block`, `survey_block`, `street`, `area`, `hood`, `district` ... | none | as is | How exactly the source places it: `address`, `parcel`, `corner`, `segment`, `street`, `block`, `stated`, `named place`, `hood`, `city`. |
| `lat` | DOUBLE | yes | degrees (WGS 84); 36.0..50.0 | none | as is | Latitude where the source file gives one. |
| `lon` | DOUBLE | yes | degrees (WGS 84); SF-owned land reaches the Sierra (Hetch Hetchy, Camp Mather); -125.0..-119.0 | none | as is | Longitude where the source file gives one. |
| `page_id` | VARCHAR | yes | e.g. `journalofproceed30sanfrich:1` | none | as is | The archive.org page: `<identifier>:<leafNum>` (IA's leaf number, the jp2 / IIIF number), or `<identifier>:d<n>` where only the BookReader index is known, or `<url>:<page>` for a web document. `nopage:<seq>` where the source gives no page number (sfbos rows without one): the row is kept, page_url is NULL. |
| `page_url` | VARCHAR | yes | e.g. `https://archive.org/details/journalofproceed30sanfrich/page/n1` | none | as is | Opens the page: archive.org BookReader link built from the leaf map (audited against IA's scandata), or `<pdf url>#page=<n>` for a PDF. NULL only where page_id is `nopage:<seq>`. |
| `source_id` | VARCHAR | no |  | none | as is | The source: an archive.org identifier, or `web:<url>` for other sources. |
| `source_url` | VARCHAR | yes |  | none | as is | Link to the source page. |
| `source_label` | VARCHAR | yes |  | free_text | where extraction_table IN ('showcase_search', 'media', 'dl_documents'): as is; otherwise: names of private people withheld under the embargo | Readable source name. |
| `licence` | VARCHAR | yes | `CC-BY`, `CC-BY-2.0`, `CC-BY-2.5`, `CC-BY-3.0`, `CC-BY-NC-3.0`, `CC-BY-NC-4.0`, `CC-BY-NC-ND-2.0`, `CC-BY-NC-ND-2.5`, `CC-BY-NC-ND-3.0`, `CC-BY-NC-ND-4.0`, `CC-BY-NC-SA-1.0`, `CC-BY-NC-SA-2.5` ... | none | as is | The source's licence where the source file states it. |
| `quote` | VARCHAR | yes |  | raw_text | where extraction_table IN ('bos_records', 'resweep', 'map_blocks', 'x_decisions_v2', 'x_appraisals', 'site_decisions', 'hand_decisions', 'x_roll_calls'): left out for records under the embargo; otherwise: names of private people withheld under the embargo | Short quote of the source (<= 300 characters). See `privacy_flag`. |
| `extraction_table` | VARCHAR | yes | `assessor_roll`, `board_permits`, `bos_records`, `dateline`, `dbi_permits`, `dl_documents`, `doors`, `first_built`, `hand_decisions`, `hood_stories`, `hood_timelines`, `land_1869` ... | none | as is | Which table or file the statement came from (`bos_records`, `resweep`, `x_roll_calls`, `map_blocks`, `site_decisions`, ...). |
| `run_id` | VARCHAR | no | `bos-corpus-2026-09`, `dateline-2026-10`, `dl-tags-haiku55-2026-10`, `doors-2026-10`, `hand-2026-10`, `hood-stories-2026-10`, `hood-timelines-2026-10`, `ia-fulltext-places-2026-07`, `map-blocks-2026-10`, `map-blocks-items-2026-10`, `media-registry-2026-10`, `resweep-d1960-haiku55-2026-10` ... | none | as is | The extraction run (see `runs`). |
| `seq` | VARCHAR | yes |  | raw_text | names of private people withheld under the embargo | The row id inside the source table or file. For map_blocks and site rows it is "<block>\|<volume>/<leaf>\|<first line>" (to be replaced by an id). |
| `confidence` | DOUBLE | yes | probability 0-1; 0..1 | none | as is | 0-1 where a run gives one (votes: 0.95 when the printed counts match the names). |
| `status` | VARCHAR | yes | `extracted`, `record`, `curated`, `verified`, `corrected`, `disputed`, `retracted`, `superseded` | none | as is | `extracted` (machine-read), `record` (from an official register: assessor, DataSF), `curated` (written or checked by qipu for the map). No `verified` rows yet. |
| `inferred` | BOOLEAN | yes |  | none | as is | True when the claim is inferred from others (none in v1). |
| `claim_kind` | VARCHAR | no | `event`, `observation`, `interval` | none | as is | `event` or `observation`. |
| `topic` | VARCHAR | yes |  | none | as is | Topic label from the run. |
| `outcome` | VARCHAR | yes | `amended`, `approved by voters`, `continued`, `failed`, `passed`, `pending`, `referred`, `refused`, `rejected`, `rejected by voters`, `submitted_to_voters`, `tabled` ... | none | as is | Decision outcome where known. |
| `page_exact` | BOOLEAN | yes |  | none | as is | True when the archive.org page number was converted from the OCR leaf with the per-volume leaf map; false = may be a page or two off. |
| `privacy_flag` | VARCHAR | yes | `may_name_private_person`, `may_name_private_person_92y`, `may_name_private_person_after_1950` | none | as is | `may_name_private_person_after_1950` on Board text from 1952 on (records up to 1951 are open: the one cutoff, qipu_data/cutoff.py). The public SQLite leaves those quotes out. |
| `name_printed` | VARCHAR | yes |  | person_name | shown for officials and organisations; a private person is withheld under the embargo | Votes: the name as printed in the roll call. |
| `city` | VARCHAR | no | `sf`, `van` | none | as is | `sf` or `van`. |
| `rights_note` | VARCHAR | yes |  | catalogue | as is | What a reuser must know about the source's rights, in words (archive.org search hits, or a licence text longer than 80 characters moved here from `licence`). |
| `collection_group` | VARCHAR | yes | `Audio`, `Books and texts`, `Democracy's Library`, `Films and video`, `Live Music Archive`, `Maps and images`, `Open Library`, `Other`, `Prelinger Archives`, `Radio`, `SF Public Library scans`, `TV News Archive` ... | none | as is | For archive.org search hits (`showcase_search`): the group of the item's collection (`Books and texts`, `Films and video`, `TV News Archive`, ...). |
| `place_role` | VARCHAR | yes | `at`, `near`, `along`, `within`, `about`, `from`, `to`, `bounds` | none | as is | How the claim relates to its place: `at`, `along` (a street), `near` (see `qualifiers.distance_m`), `about` (a document about the place) or `bounds` (v0.6.1: the street only bounds or locates the work, "Hanover street between Allison and Pope streets" for Allison and Pope; kept off street pages and the map). |
| `qualifiers` | JSON | yes |  | nested | field by field (below) | Extra details as JSON: `role` (party role), `distance_m`, `payee` and `purpose` (money), `from`/`to` (rezoning, street segment), `period`/`rooms` (rent), `score` (search relevance). |
| `qualifiers.role` | | | | none | as is | the role of a party (claimant, owner, appellant ...) |
| `qualifiers.purpose` | | | | free_text | names of private people withheld under the embargo | what a payment is for, as read |
| `qualifiers.payee` | | | | person_name | shown for officials and organisations; a private person is withheld under the embargo | who is paid (an organisation, or a person: withheld under the embargo) |
| `qualifiers.distance_m` | | | | none | as is | metres from the place, for place_role = near |
| `qualifiers.to` | | | | none | as is | rezoning: the new district |
| `qualifiers.from` | | | | none | as is | rezoning: the old district |
| `qualifiers.score` | | | | none | as is | search relevance score (not a probability) |
| `qualifiers.period` | | | | none | as is | rent: the period the amount is for (month, year ...) |
| `qualifiers.rooms` | | | | none | as is | rent: rooms the amount is for |
| `qualifiers.demoted_from_block` | | | | none | as is | the block a placement was demoted from (the item text did not name it) |
| `qualifiers.demote_reason` | | | | none | as is | why the block placement was demoted (rule code) |
| `qualifiers.roll_year` | | | | none | as is | assessor roll: the roll year |
| `qualifiers.roll_address` | | | | none | as is | assessor roll: the address as on the roll |
| `qualifiers.lots` | | | | none | as is | assessor lots the claim covers |
| `qualifiers.permit_number` | | | | none | as is | building permit number |
| `qualifiers.status` | | | | none | as is | permit status |
| `qualifiers.cost_usd` | | | | none | as is | permit: estimated cost, USD as printed |
| `qualifiers.placed_how` | | | | none | as is | how the claim was placed (rule) |
| `qualifiers.estimate` | | | | none | as is | true when the value is an estimate |
| `qualifiers.printed_place` | | | | free_text | names of private people withheld under the embargo | the place as printed in the source |
| `qualifiers.evidence` | | | | free_text | names of private people withheld under the embargo | the words that support the claim, as read |
| `qualifiers.is_business` | | | | none | as is | occupied_by: true when the listing is a business, not a household |
| `qualifiers.addr_kind` | | | | none | as is | occupied_by: `residence` or `business` address |
| `qualifiers.occupation_printed` | | | | free_text | names of private people withheld under the embargo | directories: the occupation or trade as printed (`elk SPCo`, `barber`) |
| `qualifiers.address_printed` | | | | free_text | names of private people withheld under the embargo | directories: the address as printed (`642 Oak`) |
| `qualifiers.marker` | | | | none | as is | directories: the printed residence marker (`r` resides, `h` householder, `b` boards) |
| `qualifiers.spouse_note` | | | | person_name | shown for officials and organisations; a private person is withheld under the embargo | directories: the printed spouse or widow note (`wid Jas`); a name, so under the privacy rule |
| `qualifiers.block_today` | | | | none | as is | directories: the assessor block the printed address falls in today |
| `qualifiers.exact_door_today` | | | | none | as is | directories: true when the printed door number exists today |
| `qualifiers.occupation_group` | | | | none | as is | directories: the occupation group (clerks and office, building trades ...) |
| `qualifiers.business_type` | | | | none | as is | directories: the kind of business in plain words (`a cigar store`) |
| `qualifiers.classified_heading` | | | | catalogue | as is | directories: the classified-section heading the line is printed under |
| `qualifiers.households` | | | | none | as is | block portraits: households listed on the block |
| `qualifiers.residents_listed` | | | | none | as is | block portraits: people listed on the block |
| `qualifiers.occupations` | | | | none | as is | block portraits: occupation counts on the block (`{"clerk": 12}`) |
| `qualifiers.share_of_address_points` | | | | none | as is | block in tract: share of the block's address points inside the tract |
| `qualifiers.address_points` | | | | none | as is | block in tract: address points used |
| `qualifiers.pop` | | | | none | as is | tract portraits: persons counted |
| `qualifiers.median_income` | | | | none | as is | tract portraits: median household income, dollars of the year before the census |
| `qualifiers.median_income_2024usd` | | | | none | as is | tract portraits: the same in 2024 dollars |
| `qualifiers.record_id` | | | | none | as is | business locations: the DataSF record id (uniqueid) |
| `qualifiers.block` | | | | none | as is | business locations: the assessor block |
| `qualifiers.kind` | | | | none | as is | business locations: plain kind (a laundromat, a bar ...) |
| `qualifiers.date_as_printed` | | | | none | as is | v0.6.2: the date as the reading gave it when that day is not on the calendar (`1964-04-87`); `t_from` then keeps only the month (or the year) and `t_precision` says so |
| `qualifiers.date_fix` | | | | none | as is | v0.6.2: why `t_from` differs from `date_as_printed` (rule code `day_not_on_calendar`) |
| `qualifiers.group` | | | | none | as is | business locations: food, shop, service, lodging, leisure, worship |
| `qualifiers.naics` | | | | none | as is | business locations: self-reported NAICS code |
| `qualifiers.start_is_register_floor` | | | | none | as is | business locations: true when the start is the register floor (1968-10-01: read "by 1968") |
| `qualifiers.administratively_closed` | | | | none | as is | business locations: closed by the Tax Collector rather than by the business |
| `qualifiers.kinds` | | | | none | as is | permits per block: distinct permits per kind (`{"reroof": 3}`) |
| `qualifiers.first_year_by_kind` | | | | none | as is | permits per block: the first year of each kind |
| `qualifiers.payer` | | | | person_name | shown for officials and organisations; a private person is withheld under the embargo | money: who pays (an organisation, or a person: withheld under the embargo) |
| `qualifiers.counterparty` | | | | person_name | shown for officials and organisations; a private person is withheld under the embargo | land and agreements: the other party (withheld under the embargo when a person) |
| `qualifiers.holder` | | | | person_name | shown for officials and organisations; a private person is withheld under the embargo | permits: the holder (withheld under the embargo when a person) |
| `qualifiers.claimant` | | | | person_name | shown for officials and organisations; a private person is withheld under the embargo | claims: who claims (withheld under the embargo when a person) |
| `qualifiers.owner` | | | | person_name | shown for officials and organisations; a private person is withheld under the embargo | assessments: the owner (withheld under the embargo when a person) |
| `qualifiers.who` | | | | person_name | shown for officials and organisations; a private person is withheld under the embargo | stances: the speaker or group (withheld under the embargo when a person) |
| `qualifiers.counterparty_kind` | | | | none | as is | person, organisation, government or unknown |
| `qualifiers.holder_kind` | | | | none | as is | person, organisation, government or unknown |
| `qualifiers.claimant_kind` | | | | none | as is | person, organisation, government or unknown |
| `qualifiers.owner_kind` | | | | none | as is | person, organisation, government or unknown |
| `qualifiers.who_kind` | | | | none | as is | private_person, public_official or organisation |
| `qualifiers.fund` | | | | catalogue | as is | money: the fund paid from, as printed |
| `qualifiers.fiscal_year` | | | | none | as is | the fiscal year |
| `qualifiers.payment_stage` | | | | none | as is | money: progress_n, final, advance, retroactive |
| `qualifiers.program` | | | | catalogue | as is | money: the program, as printed |
| `qualifiers.number` | | | | none | as is | citations and appeals: the number as printed |
| `qualifiers.relation` | | | | none | as is | citations: how the decision relates to the cited law |
| `qualifiers.type` | | | | none | as is | the item type (land, organisation, institution, condition ...) |
| `qualifiers.measure` | | | | none | as is | quantities: what is measured |
| `qualifiers.rule` | | | | none | as is | traffic rules: the rule |
| `qualifiers.action` | | | | none | as is | the action (establish, rescind, create, abolish ...) |
| `qualifiers.permit_type` | | | | none | as is | permits: the kind of permit |
| `qualifiers.decision` | | | | none | as is | permits: granted, denied, revoked, transferred ... |
| `qualifiers.direction` | | | | none | as is | claims: city_pays or city_receives; traffic rules: the direction |
| `qualifiers.cause` | | | | none | as is | claims: the cause, as a category |
| `qualifiers.amount_claimed` | | | | none | as is | claims: the amount claimed, USD |
| `qualifiers.amount_paid` | | | | none | as is | claims: the amount paid, USD |
| `qualifiers.rate_unit` | | | | none | as is | positions: the pay period (hour, day, month ...) |
| `qualifiers.stance` | | | | none | as is | stances and board positions: the stance |
| `qualifiers.form` | | | | none | as is | stances: spoke, petition, protest, letter |
| `qualifiers.target_kind` | | | | none | as is | board positions: what the Board took a position on |
| `qualifiers.noes` | | | | none | as is | other votes: the noes |
| `qualifiers.use` | | | | free_text | names of private people withheld under the embargo | the use, as read |
| `qualifiers.quote_found` | | | | none | as is | true when the quote was found in the page text (OCR) |
| `qualifiers.repointed_from` | | | | none | as is | decisions v2: the page first cited, when the quote was found on the page before it instead (the claim now cites that page; qipu_data.repoint) |
| `privacy_class` | VARCHAR | yes | `public_official`, `public_figure`, `organisation`, `private_person`, `unknown`, `none` | none | as is | Who a name in the claim belongs to: `public_official` (votes, motions), `private_person` (owners, named private parties) or `organisation`; empty when the claim names nobody. |
| `public_after_year` | INTEGER | yes |  | none | as is | For `private_person` claims: the claim's year plus the embargo (75 years in SF, 92 in Vancouver); `9999` when the year is unknown. Until then public files show the role, not the name. |
| `claim_id` | VARCHAR | no | e.g. `c:c4ca4238a0b923820dcc509a6f75849b` | none | as is | Stable id: `c:` + 32 hex (128 bits, hashed from the slot: subject, property, object, page, role); votes `v:<rollcall_id>:<n>` (roll calls read from the page text in v0.6.1 have ids `p<hex>`). |
| `n_support` | BIGINT | yes |  | none | as is | How many extracted rows read this same claim (same `claim_id`); see `claim_support`. |
| `media_id` | VARCHAR | yes |  | none | as is | The media item the claim points to (`depicted_in`, `recorded_at`), see `media`. |
| `release_added` | VARCHAR | yes | `v0.4` | none | as is | Release that built the row: `v0.4` on every row (the build stamps the current release, not the first release that had the claim). |
| `display_n` | INTEGER | yes |  | none | as is | archive.org BookReader page index of the cited page (the n in `/page/n<k>`); `page_id` uses the OCR leaf instead. |
| `t_lo` | DATE | yes |  | none | as is | Earliest date `t_from` can mean (`1962` -> `1962-01-01`). |
| `t_hi` | DATE | yes |  | none | as is | Latest date `t_from` can mean (`1962` -> `1962-12-31`; for `YYYY/YYYY` the end year). |
| `reliability` | VARCHAR | yes | `measured`, `unmeasured` | none | as is | A property of the RUN, not of the claim: `measured` when the run that read this claim has scores in `field_scores`, else `unmeasured`. It is not the claim's reliability: how firmly a claim is backed is `claim_reliability.tier` (`verified` / `quoted` / `unverified`, with `tier_reason`). Planned rename in v0.7 (breaking): `run_measured` BOOLEAN. |
| `mention_id` | VARCHAR | yes |  | none | as is | The printed name this statement links to a person, organisation or name (votes, movers, owners), as a row of `name_mentions` (`m:<hash>`, internal: names are never published raw). Required on every such statement read from a page since v0.7 (check M3). |
| `place_mention_id` | VARCHAR | yes | e.g. `pm:c4ca4238a0b92382` | none | as is | v0.7: the place string the statement was read with, as a row of `place_mentions`; set on statements read from a page and placed finer than the city (check M4). |

Not in the public copy: `importance`.

## `corrections` (0 rows)

The human layer (empty in v0.3): edits applied on top of claims, never replacing extractions.

| column | type | null | values / unit | privacy | public copy | meaning |
|---|---|---|---|---|---|---|
| `correction_id` | VARCHAR | yes |  | none | as is | Empty in v0.4 (no corrections yet). Meant for the id of a human correction, applied on top of the claims (`claims_effective`). |
| `target_kind` | VARCHAR | yes |  | none | as is | Empty in v0.4 (no corrections yet). Meant for what kind of row is corrected. |
| `target_id` | VARCHAR | yes |  | none | as is | Empty in v0.4 (no corrections yet). Meant for the id of the corrected row. |
| `field` | VARCHAR | yes |  | none | as is | Empty in v0.4 (no corrections yet). Meant for the corrected column. |
| `new_value` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | Empty in v0.4 (no corrections yet). Meant for the corrected value. |
| `at_time` | TIMESTAMP | yes |  | none | as is | Empty in v0.4 (no corrections yet). Meant for when it was made. |
| `why` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | Empty in v0.4 (no corrections yet). Meant for the reason. |
| `review` | VARCHAR | yes |  | none | as is | Empty in v0.4 (no corrections yet). Meant for its review state. |

Not in the public copy: `who`.

## `cpi_ca` (113 rows)

Canadian consumer price index per year (and the latest month), used for 2026 dollars on Vancouver amounts: Statistics Canada BLS CPI-U U.S. city average, annual averages 1913-2025, and 2026 from the latest month; 1800-1912 Minneapolis Fed estimates linked at 1913 (quality `estimate`).

| column | type | null | values / unit | privacy | public copy | meaning |
|---|---|---|---|---|---|---|
| `year` | BIGINT | no | 1800..2030 | none | as is | The year. |
| `cpi` | DOUBLE | no | index (see `base`); 0.. | none | as is | The index value for the year. |
| `base` | VARCHAR | yes |  | none | as is | The index base (`1982-84=100`). |
| `series` | VARCHAR | yes |  | none | as is | The series the value comes from. |
| `quality` | VARCHAR | yes | `official`, `estimate`, `provisional` | none | as is | `official` (BLS annual average), `estimate` (before 1913, linked), `provisional` (the current year from its latest month). |
| `source_url` | VARCHAR | yes |  | none | as is | Where the number was taken from. |
| `note` | VARCHAR | yes |  | catalogue | as is | How the value was derived (rebasing, the month used). |

## `cpi_us` (227 rows)

Vocabulary: the US consumer price index by year (1982-84 = 100), used to state amounts in 2026 dollars (scripts/18_cpi.py). BLS CPI-U U.S. city average, annual averages 1913-2025, and 2026 from the latest month; 1800-1912 Minneapolis Fed estimates linked at 1913 (quality `estimate`).

| column | type | null | values / unit | privacy | public copy | meaning |
|---|---|---|---|---|---|---|
| `year` | BIGINT | no | 1800..2030 | none | as is | The year. |
| `cpi` | DOUBLE | no | index (see `base`); 0.. | none | as is | The index value for the year. |
| `base` | VARCHAR | yes |  | none | as is | The index base (`1982-84=100`). |
| `series` | VARCHAR | yes |  | none | as is | The series the value comes from. |
| `quality` | VARCHAR | yes | `official`, `estimate`, `provisional` | none | as is | `official` (BLS annual average), `estimate` (before 1913, linked), `provisional` (the current year from its latest month). |
| `source_url` | VARCHAR | yes |  | none | as is | Where the number was taken from. |
| `note` | VARCHAR | yes |  | catalogue | as is | How the value was derived (rebasing, the month used). |

## `decision_numbers` (88,876 rows)

Decision -> number(s) printed in its text.

| column | type | null | values / unit | privacy | public copy | meaning |
|---|---|---|---|---|---|---|
| `decision_id` | VARCHAR | no |  | none | as is | The decision whose text prints the number (see `decisions`). |
| `kind` | VARCHAR | no | `ordinance`, `resolution` | none | as is | `resolution` or `ordinance`. |
| `number` | VARCHAR | yes |  | none | as is | The number as printed at the start of the item. |
| `number_year` | INTEGER | yes |  | none | as is | Full year from a printed suffix (`No. 627-91` -> 1991). |
| `series` | VARCHAR | yes | `(New Series)`, `(Series of 1039)`, `(Series of 1933)`, `(Series of 1939)`, `(Series of 1989)`, `(Series of 1993)`, `(Series of 9139)` | none | as is | Series as printed: `(New Series)`, `(Series of 1939)`. |
| `file_no` | VARCHAR | yes |  | none | as is | Board file number as printed (`File No. ...`). |
| `number_check` | VARCHAR | yes |  | none | as is | v0.6.1 check of the number against the page: `cited` (printed only after words that cite another act: not this item's number, no link), `not_found` (printed nowhere on the page: no link), `seq_outlier` (far from the numbers around it: kept, flagged), `legistar: N-YY` (corrected from the Legistar matter), `legistar: not this number (...)` (no link); empty when nothing was found against it. |
| `year` | BIGINT | yes |  | none | as is | Year of the Journal volume. |
| `source_url` | VARCHAR | yes |  | none | as is | Link to the source page. |
| `instrument_id` | VARCHAR | no |  | none | as is | `qipu:sf/instrument/<res\|ord\|bill>/<year>-<n>` when the number prints its year, `s<year>-<n>` for "(Series of 1939)", `ns-<n>` for "(New Series)", else `y<journal year>-<n>`. |

## `decisions` (718,093 rows)

One row per thing the Board of Supervisors (SF) or City Council (Vancouver) did: an item in the Journal or minutes, a re-swept item, a hand-built decision, or a map/site row with no item behind it. Decisions v2 readings attach to the same row.

| column | type | null | values / unit | privacy | public copy | meaning |
|---|---|---|---|---|---|---|
| `decision_id` | VARCHAR | no | e.g. `qipu:sf/decision/journalofproceed30sanfrich\|1` | none | as is | `qipu:sf/decision/<item id>`: corpus items `<volume>\|<n>`, re-swept items `<volume>\|g<n>`, hand-built `hand:<slug>`, map/site-only rows `map:...`, `su:...`, `sd:...`. |
| `item_id` | VARCHAR | yes |  | none | as is | The item inside its volume: `<volume>\|<n>` (corpus), `<volume>\|g<n>` (re-sweep), or a hand/map/site key. |
| `origin` | VARCHAR | yes | `bos_corpus`, `hand`, `map_blocks`, `resweep`, `roll_call`, `site`, `v2` | none | as is | `bos_corpus`, `resweep`, `map_blocks`, `site`, `hand`. |
| `year` | BIGINT | yes |  | none | as is | The year of the meeting. |
| `date` | VARCHAR | yes |  | none | as is | The meeting date (YYYY-MM-DD) when known. |
| `date_from` | VARCHAR | yes |  | none | as is | Where the date comes from (v0.6.1): `record` (the pass's own record), `page: running head` / `page: page heading` / `page: meeting heading` (the Journal page's printed meeting date before the item), `page: heading of an earlier page` / `page: previous page heading` (the last printed meeting date before the page), `page: dated claims on the page`. |
| `rollcall_id` | VARCHAR | yes |  | none | as is | v0.6.1: the roll call of this decision's item (its last; it may be printed on the next page). |
| `decision_group` | VARCHAR | yes |  | none | as is | One id per printed item (v0.6.1): rows of different passes over the same item (v1 corpus, resweep, v2, map, site, roll call) share it; it is the decision_id of the group's first row by pass. Count distinct decision_group for "how many decisions". |
| `kind` | VARCHAR | yes |  | none | as is | resolution, ordinance, motion, bill, hearing, report ... as classified. |
| `heading` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | The item heading as printed (OCR). |
| `plain` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | The run's one-line plain summary (model-written; see summary_by). |
| `record_type` | VARCHAR | yes | `cut-in-half`, `decision`, `departmental-work`, `index-row`, `page-furniture`, `public-comment`, `too-damaged`, `vote-outcome-only` | none | as is | What the row records: decision, vote-outcome-only, report ... |
| `topic` | VARCHAR | yes |  | none | as is | Topic label. |
| `subtopic` | VARCHAR | yes |  | none | as is | Finer topic label. |
| `action` | VARCHAR | yes | `accepted`, `amended`, `appointed`, `appropriated`, `commended`, `granted`, `ordered`, `other`, `paid`, `refused`, `settled`, `voted-or-scheduled` | none | as is | The last action on the item (adopted, passed first reading, referred ...). |
| `outcome` | VARCHAR | yes | `amended`, `approved by voters`, `continued`, `passed`, `passed first reading`, `pending`, `referred`, `refused`, `rejected`, `rejected by voters`, `submitted_to_voters`, `tabled` ... | none | as is | The run's outcome (passed / refused / rejected / vetoed / pending / unknown). |
| `source_url` | VARCHAR | yes |  | none | as is | The archive.org page of the item. |
| `block` | VARCHAR | yes |  | none | as is | Assessor block the item names, when it names one. |
| `streets` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | Streets the item names ("\|"-separated). |
| `amounts` | VARCHAR | yes |  | none | as is | Dollar amounts printed in the item (text). |
| `how_placed` | VARCHAR | yes | `between two streets`, `names its own block`, `no place given`, `street address`, `street corner`, `street only` | none | as is | How the item was placed on the map (block, street, no place given ...). |
| `classified_by` | VARCHAR | yes | `form`, `fund`, `near-duplicate`, `purpose-clause`, `record`, `rule`, `title` | none | as is | What classified it: rule, form, model. |
| `hocr_leaf` | BIGINT | yes |  | none | as is | Page index in the OCR (before the leaf map). |
| `page_exact` | BOOLEAN | yes |  | none | as is | True when the page link was converted from the OCR leaf with the per-volume leaf map; false = may be a page or two off. |
| `who` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | Who the item concerns, in words (model-written). |
| `place_text` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | The place as written in the item. |
| `run_id` | VARCHAR | yes | `resweep-d1960-haiku55-2026-10`, `resweep-d1970-haiku55-2026-10`, `resweep-d1980-haiku55-2026-10`, `resweep-d1990-haiku55-2026-10`, `resweep-d2000-haiku55-2026-10`, `resweep-d2010-haiku55-2026-10`, `sf-v21-haiku-1014`, `van-v21-haiku-1014` | none | as is | The run that read it (see `runs`). |
| `address` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | The street address the item names, if any. |
| `amount` | BIGINT | yes |  | none | as is | The main dollar amount, as a number. |
| `quote` | VARCHAR | yes |  | raw_text | left out for records under the embargo | Raw OCR text of the item. Not published for records under the embargo. |
| `site_plain` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | The map's corrected one-line summary. |
| `site_outcome` | VARCHAR | yes | `approved by voters`, `passed`, `refused`, `rejected`, `rejected by voters`, `vetoed` | none | as is | The map's outcome value. |
| `site_ids` | VARCHAR | yes |  | none | as is | The map's row ids for this decision. |
| `outcome_best` | VARCHAR | yes | `amended`, `approved by voters`, `continued`, `passed`, `passed first reading`, `pending`, `referred`, `refused`, `rejected`, `rejected by voters`, `submitted_to_voters`, `tabled` ... | none | as is | passed / refused / rejected / vetoed / pending / unknown (the map's value first, then the run's). |
| `plain_best` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | The best one-line summary (the map's corrected line first). |
| `instrument_id` | VARCHAR | yes |  | none | as is | Ordinance/resolution number printed in the item (see `instruments`). |
| `outcome_from` | VARCHAR | yes |  | none | as is | Where outcome_best came from (site, run, roll call ...). |
| `same_as` | VARCHAR | yes |  | none | as is | Audit: the id this row was merged from (decisions v2 attached to v1). |
| `city` | VARCHAR | no | `sf`, `van` | none | as is | `sf` or `van`. |
| `privacy_flag` | VARCHAR | yes | `may_name_private_person_after_1950` | none | as is | `may_name_private_person_after_1950` on Board text from 1952 on (records up to 1951 are open: the one cutoff, qipu_data/cutoff.py). |
| `summary_by` | VARCHAR | yes | `a model in the 2026-09 pipeline (model-written; check the scan)`, `claude-haiku-5-5 (model-written; check the scan)`, `qipu (hand-written from the cited source)` | none | as is | Who wrote the plain line (model name, "model-written; check the scan"). |
| `status` | VARCHAR | no | `introduced`, `proposed`, `referred`, `continued`, `amended`, `hearing_held`, `first_reading`, `submitted_to_voters`, `recommended`, `claim_filed`, `demand_listed`, `reported` ... | none | as is | What the Board did with this item at this reading, from the first rule that applies (status_from); never blank: `not_recorded` when nothing says (qipu_data/status.py, usability audit 19/10). |
| `status_word` | VARCHAR | no |  | none | as is | The status in the word people see (proposed, referred, first reading, passed, adopted, vetoed, failed, withdrawn, claimed, listed for payment, recommended, reported, no status on record ...). |
| `status_plain` | VARCHAR | no |  | none | as is | The status in one plain sentence, e.g. "Sent to committee; no final vote found"; says when the matter went further later. |
| `happened` | VARCHAR | no | `decided`, `not_decided`, `failed`, `unknown` | none | as is | decided (finally passed, adopted, voters approved, veto overridden) / not_decided (proposed, referred, continued, first reading only, claim, recommendation) / failed (refused, tabled, withdrawn, vetoed) / unknown (no status on record). |
| `carried_out` | VARCHAR | no | `True`, `False`, `unknown` | none | as is | Whether what was decided was carried out (paid, built, demolished): only with its own evidence (carried_out_evidence); unknown = not confirmed. A decision to pay is not a payment. |
| `carried_out_evidence` | VARCHAR | yes |  | none | as is | The statement or record that shows it was carried out. |
| `status_from` | VARCHAR | no | `instrument_lifecycle`, `outcome_best`, `x_decisions`, `other_reading`, `roll_call`, `wording_rule`, `none` | none | as is | Where the status comes from: the instrument lifecycle stage of this reading, outcome_best, the v2 extraction, another reading of the same printed item, the printed roll call, a wording rule on the heading/summary/printed text, or none. |
| `matter_status` | VARCHAR | no |  | none | as is | The furthest status of the whole matter (instrument lifecycle; else the same printed item; else the same title decided within 120 days; else this reading's own). |
| `matter_happened` | VARCHAR | no | `decided`, `not_decided`, `failed`, `unknown` | none | as is | happened, for the whole matter. |
| `matter_status_from` | VARCHAR | no | `instrument_lifecycle`, `printed_item`, `same_title_120d`, `this_reading` | none | as is | Where matter_status comes from. |
| `plain_fix` | VARCHAR | yes |  | none | as is | Set when plain_best was rewritten because it stated an open matter as done (rule_* = wording rule, model_haiku = rewritten by Claude Haiku and checked); `plain` keeps the original. |

Not in the public copy: `importance`, `significance`, `site_importance`, `site_hidden`.

## `entity_ids` (336 rows)

v0.7: the minted-id registry of persons and organisations (ids/persons.csv, ids/orgs.csv in the code repo, append-only). An id is minted once and never re-derived from a name; `status` says whether it is still the record's id.

| column | type | null | values / unit | privacy | public copy | meaning |
|---|---|---|---|---|---|---|
| `id` | VARCHAR | no |  | none | as is | The id (`qipu:sf/person/...`, `qipu:sf/org/...`). |
| `kind` | VARCHAR | no | `person`, `org` | none | as is | What the id names. |
| `key` | VARCHAR | no |  | none | as is | What the build looks the record up by: `wd:<Q>`, `wp:<Wikipedia title>`, `name:<roster name>`, `journal:<surname>` (persons), the name key (organisations), `id:<old id>`. |
| `label` | VARCHAR | yes |  | organisation | as is | The name when the id was minted (officials and organisations only: private people never get an id). |
| `alias` | VARCHAR | yes |  | none | as is | An older id or slug that still resolves to this one. |
| `minted_in` | VARCHAR | yes |  | none | as is | The release (or `<= vX` for ids older than the registry) the id was minted in. |
| `status` | VARCHAR | no | `active`, `redirected`, `retired` | none | as is | `active`; `redirected` (see `redirect_to` and `id_redirects`); `retired` (kept, never reused). |
| `redirect_to` | VARCHAR | yes |  | none | as is | The id it now points to. |

## `events` (1 rows)

Events (v0.3: the 1906 earthquake and fire, with its burn-zone footprint).

| column | type | null | values / unit | privacy | public copy | meaning |
|---|---|---|---|---|---|---|
| `event_id` | VARCHAR | no | `qipu:sf/event/1906-earthquake-and-fire` | none | as is | `qipu:<city>/event/<slug>`. |
| `city` | VARCHAR | no | `sf` | none | as is | `sf`. |
| `label` | VARCHAR | yes | `The 1906 earthquake and fire` | none | as is | Readable name. |
| `event_type` | VARCHAR | yes | `disaster` | none | as is | `disaster`. |
| `t_from` | VARCHAR | yes | `1906-04-18` | none | as is | Start date (YYYY-MM-DD). |
| `t_to` | VARCHAR | yes | `1906-04-21` | none | as is | End date (YYYY-MM-DD). |
| `footprint_place_id` | VARCHAR | no | `qipu:sf/place/burn-zone/1906` | none | as is | The place covering the area it hit (see `places`). |
| `wikidata` | VARCHAR | yes | `Q211386` | none | as is | Wikidata id. |

## `field_scores` (36 rows)

Measured accuracy per run and field, from the benchmarks (L4_TEST.md, sfra bench, the roll-call gold). Claims from these runs say `reliability = measured`.

| column | type | null | values / unit | privacy | public copy | meaning |
|---|---|---|---|---|---|---|
| `run_id` | VARCHAR | no | `rollcall-parser-v1`, `sf-v21-haiku-1014`, `sfra-appraisals-haiku55-2026-10`, `van-v21-haiku-1014`, `bos-corpus-2026-09`, `dl-sf-facts-v1.1-haiku55-2026-10`, `andrews-sonnet55-2026-10`, `sf-enrich-v11-haiku55-2026-10`, `citydir-haiku55-2026-10` | none | as is | The run scored (see `runs`). |
| `field` | VARCHAR | yes | `votes`, `outcomes`, `places`, `dl facts`, `andrews owners`, `sfra appraisals`, `action`, `address`, `appraised_value`, `counts`, `dates`, `finds` ... | none | as is | The field or group of fields scored (`vote names`, `money`, `mover`, ...). |
| `metric` | VARCHAR | yes | `F1`, `F1 (vs Opus key)`, `Spearman`, `all exact`, `exact`, `exact where the roll is in the text`, `matched`, `normalized exact (18 pages)`, `recall` | none | as is | How it was scored (`exact`, `F1 (vs Opus key)`, `recall`, `Spearman`, ...). |
| `value` | DOUBLE | yes |  | none | as is | The score, 0-1. |
| `gold_id` | VARCHAR | no | `decisions_bench Vancouver gold (30 pages, 48 decisions: Opus-written, 21 decisions hand-checked)`, `decisions_bench/gold.jsonl (SF, 40 pages: an Opus-5.5-written key, no record of a hand check) + v2 Opus key`, `decisions_bench/gold.jsonl roll calls (Opus-written key), scripts/22_rollcall_gold.py`, `ia_records/sfra/bench (18 pages)` | none | as is | The gold set (see `runs.gold_id`). |
| `against` | VARCHAR | yes | `hand`, `human`, `opus_key`, `opus_key (21 hand-checked)`, `model_scan_check` | none | as is | What the run was compared with: `opus_key` (a key written by Claude Opus), `opus_key (21 hand-checked)`, `hand` / `human` (a person checked the rows), `model_scan_check` (a model re-read each sampled claim against the page image; a person did not). |
| `n` | INTEGER | yes |  | none | as is | Size of the gold set (pages, items or roll calls). |

## `id_redirects` (135,661 rows)

Stable-id policy: a merged or retired id keeps resolving through this table (empty in v0.1, the first release).

| column | type | null | values / unit | privacy | public copy | meaning |
|---|---|---|---|---|---|---|
| `old_id` | VARCHAR | no |  | none | as is | An id an earlier release published. |
| `new_id` | VARCHAR | yes |  | none | as is | The id it now resolves to; empty when the id was retired. |
| `reason` | VARCHAR | yes | `decisions v2 reading attached to the same v1 decision`, `retired in v0.3: no longer produced by the build (see CHANGELOG)` | none | as is | `decisions v2 reading attached to the same v1 decision`, or `retired in <release>: no longer produced by the build`. |
| `since_release` | VARCHAR | yes | `v0.3`, `v0.4` | none | as is | Release that added the redirect. |

## `instruments` (72,224 rows)

Ordinances, resolutions and bills by printed number.

| column | type | null | values / unit | privacy | public copy | meaning |
|---|---|---|---|---|---|---|
| `instrument_id` | VARCHAR | no |  | none | as is | `qipu:sf/instrument/<res\|ord\|bill>/<year>-<n>` when the number prints its year (627-91), `s<year>-<n>` for "(Series of 1939)", `ns-<n>` for "(New Series)", else `y<journal year>-<n>`. |
| `kind` | VARCHAR | no | `ordinance`, `resolution` | none | as is | `resolution` or `ordinance`. |
| `number` | VARCHAR | yes |  | none | as is | The number as printed. |
| `year` | BIGINT | yes |  | none | as is | Year printed with the number, else the earliest Journal year it appears in (see `year_basis`). |
| `year_basis` | VARCHAR | yes | `printed`, `year of the Journal (assumed)` | none | as is | printed, or the Journal year (assumed). |
| `title` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | Empty in v0.4. |
| `file_no` | VARCHAR | yes |  | none | as is | Board file number printed with it. |
| `first_page_id` | VARCHAR | no |  | none | as is | Lowest `page_id` among the `x_decisions` rows that print the number; `:` when that row has no archive.org page. |
| `n_decisions` | BIGINT | yes |  | none | as is | Decisions that print the number. |
| `note` | VARCHAR | yes | `printed at the head of many items: probably the ordinance they amend, not their own number` | none | as is | Warning on numbers printed at the head of more than 10 items (probably the ordinance they amend). |

## `media` (1,428 rows)

Every photo, film, recording, clip and document the map shows (timelines, covers, hood stories, block items, films, clips), with its credit, licence, holder and a rights note. Claims `depicted_in` / `recorded_at` tie each to its place and year.

| column | type | null | values / unit | privacy | public copy | meaning |
|---|---|---|---|---|---|---|
| `media_id` | VARCHAR | no |  | none | as is | `qipu:sf/media/<hash of its URL>`, stable. |
| `kind` | VARCHAR | no | `audio`, `clip`, `document`, `film`, `photo`, `text` | none | as is | Kind as the map records it: `photo`, `film`, `audio`, `clip`, `text`, `document`. |
| `url` | VARCHAR | yes |  | none | as is | The item's page at its holder. |
| `file_url` | VARCHAR | yes |  | none | as is | Direct file or image link (a thumbnail for films). |
| `embed_url` | VARCHAR | yes |  | none | as is | Embeddable player link (archive.org embed, with start time). |
| `title` | VARCHAR | yes |  | catalogue | as is | Title or caption. |
| `credit` | VARCHAR | yes |  | catalogue | as is | Credit line as the map shows it. |
| `licence` | VARCHAR | yes |  | none | as is | Licence as the map recorded it; text longer than 80 characters is moved to `rights_note`. |
| `holder` | VARCHAR | yes |  | catalogue | as is | Who holds the rights or the copy. |
| `rights_note` | VARCHAR | yes |  | catalogue | as is | What a reuser must know, by holder rule (e.g. WNP: ask before public use; Live Music Archive: non-commercial). |
| `year` | BIGINT | yes |  | none | as is | Year of the map moment the item illustrates, not when it was made (see `created_edtf`). |
| `used_in` | VARCHAR[] | yes |  | none | as is | Where the map uses it (`timelines.json:<street>#<n>`, `blocks/<block>.json:<id>`, ...). |
| `source_identifier` | VARCHAR | yes |  | none | as is | archive.org identifier, for archive.org items. |
| `start_s` | BIGINT | yes |  | none | as is | Films: start second in the map's player. |
| `placed_because` | VARCHAR | yes |  | catalogue | as is | Films: why the film is placed where it is (archive.org description, shotlist or qipu's frame check). |
| `distance_m` | BIGINT | yes |  | none | as is | Distance in metres recorded for the item in the map's block files (`dist`). |
| `end_s` | BIGINT | yes |  | none | as is | Films: end second in the map's player. |
| `derived_from` | VARCHAR | yes | `https://archive.org/details/0779_Seeing_Americas_Greatest_Chinatown_San_Francisco_Part_I_09_49_35_00`, `https://archive.org/details/EmbarcaderoFreewayEntry1957`, `https://archive.org/details/SanFranc1906_2`, `https://archive.org/details/TripDown1905`, `https://archive.org/details/anti-vietnam-war-march-1969-llsf-2016-20-mbps-1`, `https://archive.org/details/barbary-coast-1914-from-lost-landscapes-no-1`, `https://archive.org/details/barbary-coast-c-1930s-w-purcells-llsf-2012`, `https://archive.org/details/casfmta_000020`, `https://archive.org/details/casfmta_000024`, `https://archive.org/details/cubanc_000212`, `https://archive.org/details/cubanc_000238`, `https://archive.org/details/moonalice20180805-16th-annual-jerry-day-jerry-garcia-amphitheater` ... | none | as is | Clips: the archive.org film the clip was cut from. |
| `clip_seconds` | DOUBLE | yes |  | none | as is | Clips: length in seconds. |
| `not_cut_because` | VARCHAR | yes | `CC no-derivatives: link only`, `licence not open (Copyright status unknown. This work may be protected by the ): link only`, `licence not open (Copyright status unknown; stream only (Bancroft Library / Ca): link only`, `licence not open (Not stated (Moonalice video archive on the Internet Archive)): link only` | none | as is | Clips: why no clip was cut (licence). |
| `media_kind` | VARCHAR | no | `clip`, `document`, `film`, `photo`, `recording`, `text` | none | as is | `photo`, `film`, `recording`, `clip`, `text`, `document` (`kind` with `audio` -> `recording`). |
| `created_edtf` | VARCHAR | yes |  | none | as is | When it was made (EDTF), from a year in the title; `~` for circa; empty when the title has none. |
| `use_policy` | VARCHAR | yes | `open`, `attribution`, `noncommercial`, `stream_only`, `link_only`, `ask_holder`, `unknown` | none | as is | How it may be used: `open`, `attribution`, `noncommercial`, `stream_only`, `link_only`, `ask_holder`, `unknown`. |
| `city` | VARCHAR | no | `sf` | none | as is | `sf`. |

## `ocr_files` (11,323 rows)

The OCR files on archive.org behind each source item (the manifest the page texts were read from), so a reader can fetch and check them.

| column | type | null | values / unit | privacy | public copy | meaning |
|---|---|---|---|---|---|---|
| `source_id` | VARCHAR | no |  | none | as is | the archive.org identifier |
| `file` | VARCHAR | yes |  | none | as is | the file name and archive.org format (hOCR, DjVu text ...) |
| `md5` | VARCHAR | yes |  | none | as is | archive.org's checksums, size and modification time |
| `sha1` | VARCHAR | yes |  | none | as is | archive.org's checksums, size and modification time |
| `size` | VARCHAR | yes |  | none | as is | archive.org's checksums, size and modification time |
| `mtime` | VARCHAR | yes |  | none | as is | archive.org's checksums, size and modification time |
| `format` | VARCHAR | yes | `DjVuTXT`, `Djvu XML`, `OCR Page Index`, `OCR Search Text`, `Page Numbers JSON`, `Scandata`, `chOCR`, `hOCR` | none | as is | the file name and archive.org format (hOCR, DjVu text ...) |

## `org_merges` (0 rows)

v0.6.2 stub (empty in the release): the merge log of organisation names. One row per printed name variant folded into an organisation, with the rule that did it, so every merge can be checked and undone. Filled by the graph slice (graph_slice/, not yet in the release); organisation ids there are provisional until frozen (STABLE_IDS_AND_VERSIONING.md).

| column | type | null | values / unit | privacy | public copy | meaning |
|---|---|---|---|---|---|---|
| `variant` | VARCHAR | no |  | organisation | as is | The name as printed (`C P T & T Co`). |
| `org_id` | VARCHAR | no |  | none | as is | The organisation it was folded into: a minted `qipu:sf/org/...` id, never re-derived from the name. |
| `rule` | VARCHAR | no |  | none | as is | Which rule merged it (normaliser step, alias table, `hand`). |
| `confidence` | DOUBLE | yes | 0..1 | none | as is | The rule's score, 0-1; a prior unless `confidence_kind` is `measured`. |
| `confidence_kind` | VARCHAR | yes | `measured`, `prior` | none | as is | `measured` (from a hand-checked sample) or `prior` (a hand-set value for the rule). |
| `who` | VARCHAR | no |  | none | as is | The rule name, or the initials of the person. |
| `merged_on` | DATE | no |  | none | as is | When the merge was made. |
| `status` | VARCHAR | no | `unreviewed`, `accepted`, `rejected`, `ambiguous` | none | as is | `unreviewed` until a person checks it; `ambiguous` for generic names (`City`, `the city`) that stay name mentions. |
| `reversible` | BOOLEAN | no |  | none | as is | True when undoing it is a row change only (every rule merge); the variant's mentions keep their printed text. |

## `orgs` (1 rows)

Organisations (v1: the Board).

| column | type | null | values / unit | privacy | public copy | meaning |
|---|---|---|---|---|---|---|
| `org_id` | VARCHAR | no | `qipu:sf/org/board-of-supervisors` | none | as is | `qipu:sf/org/<slug>`. |
| `name` | VARCHAR | yes |  | organisation | as is | Name. |
| `kind` | VARCHAR | no | `agency` | none | as is | `agency`. |
| `wikidata` | VARCHAR | yes | `Q3658756` | none | as is | Wikidata id. |

## `pages` (183,888 rows)

Every archive.org page cited. We point at the scans; we never copy them.

| column | type | null | values / unit | privacy | public copy | meaning |
|---|---|---|---|---|---|---|
| `page_id` | VARCHAR | no | e.g. `journalofproceed30sanfrich:1` | none | as is | The archive.org page: `<identifier>:<leafNum>` (IA's leaf number, the jp2 / IIIF number), or `<identifier>:d<n>` where only the BookReader index is known, or `<url>:<page>` for a web document. `nopage:<seq>` where the source gives no page number (sfbos rows without one): the row is kept, page_url is NULL. |
| `page_url` | VARCHAR | yes | e.g. `https://archive.org/details/journalofproceed30sanfrich/page/n1` | none | as is | Opens the page: archive.org BookReader link built from the leaf map (audited against IA's scandata), or `<pdf url>#page=<n>` for a PDF. NULL only where page_id is `nopage:<seq>`. |
| `source_id` | VARCHAR | no |  | none | as is | archive.org identifier of the volume. |
| `printed_page` | VARCHAR | yes |  | none | as is | The page number printed on the page, from IA's page_numbers file (empty where IA has none, or where its page list does not match the OCR). |
| `meeting_date` | DATE | yes |  | none | as is | Earliest dated claim on the page. |
| `text_sha1` | VARCHAR | yes |  | none | as is | Empty in v1. |
| `ocr_engine` | VARCHAR | yes | `ABBYY FineReader 8.0`, `ABBYY FineReader 9.0`, `tesseract 5.3.0-6-g76ae` | none | as is | Empty in v1. |
| `scan_url` | VARCHAR | yes |  | none | as is | Opens the page on archive.org. |
| `iiif_url` | VARCHAR | yes |  | none | as is | The page image through IA's IIIF image service (`$<display_n + 1>`: that service counts the displayed pages from 1). v0.5 used the OCR leaf and showed the page before on about a third of pages; fixed in v0.6. |
| `leaf_num` | INTEGER | yes |  | none | as is | The page's OCR leaf (the jp2/IIIF file number), from the volume's leaf map; empty in volumes without one. |
| `display_n` | INTEGER | yes |  | none | as is | archive.org BookReader page index (the n in `/page/n<k>`). |
| `page_exact` | BOOLEAN | yes |  | none | as is | True when the archive.org page number was converted from the OCR leaf with the per-volume leaf map; false = may be a page or two off. |
| `n_claims` | BIGINT | yes |  | none | as is | Claims citing the page. |
| `ocr_file_sha1` | VARCHAR | yes |  | none | as is | SHA-1 of the volume's `_djvu.xml` OCR file (see `ocr_files`); the same for every page of a volume. |

## `person_names` (9,251 rows)

Each name as printed in roll calls, with the years seen and the person it resolves to.

| column | type | null | values / unit | privacy | public copy | meaning |
|---|---|---|---|---|---|---|
| `person_id` | VARCHAR | no |  | none | as is | The supervisor (see `persons`). |
| `name_printed` | VARCHAR | yes |  | public_official | as is | The name as printed in the roll call. |
| `from` | BIGINT | yes |  | none | as is | First year this printed form appears in a roll call. |
| `to` | BIGINT | yes |  | none | as is | Last year this printed form appears in a roll call. |
| `n` | BIGINT | yes |  | none | as is | Roll-call votes with this printed form. |
| `match` | VARCHAR | yes | `fuzzy`, `fuzzy+given`, `fuzzy_low`, `journal_only`, `surname`, `surname+given`, `surname_widened` | none | as is | How this printed form was matched (see `x_roll_call_votes.match`). |

## `persons` (294 rows)

People resolved in v1: the members of the Board of Supervisors (official roster: the Wikipedia list, with Wikidata ids), plus four supervisors printed in the Journals but missing from that list (`journal_only`).

| column | type | null | values / unit | privacy | public copy | meaning |
|---|---|---|---|---|---|---|
| `person_id` | VARCHAR | no |  | none | as is | `qipu:sf/person/<slug>`. |
| `display_name` | VARCHAR | yes |  | public_official | as is | Name as on the roster. |
| `born` | VARCHAR | yes |  | none | as is | Empty in v0.4. |
| `died` | VARCHAR | yes |  | none | as is | Empty in v0.4. |
| `wikidata` | VARCHAR | yes |  | none | as is | Wikidata id. |
| `is_public_official` | BOOLEAN | yes |  | none | as is | True on every row: all are supervisors. |
| `wikipedia` | VARCHAR | yes |  | public_official | as is | Title of the English Wikipedia article. |
| `roster_source` | VARCHAR | yes | `journal_only`, `wikipedia` | none | as is | `wikipedia` (the Wikipedia list of SF supervisors) or `journal_only` (a surname printed in 100+ roll calls that the list lacks). |
| `first_year` | BIGINT | yes |  | none | as is | First year served (`journal_only`: first year the name is printed). |
| `last_year` | BIGINT | yes |  | none | as is | Last year served (`journal_only`: last year the name is printed). |
| `aka` | VARCHAR[] | yes |  | public_official | as is | Other names on the roster. |

## `place_geometries` (459,992 rows)

Dated geometry versions of places (EPSG:4326): parcel and block polygons, address points, street lines, neighbourhoods, the 1906 burn zone, showcase features (approximate circles). `hit_geom` is the clickable footprint (points and lines buffered by the place type), `hit_area_m2` drives specificity.

| column | type | null | values / unit | privacy | public copy | meaning |
|---|---|---|---|---|---|---|
| `place_id` | VARCHAR | no |  | none | as is | The place (see `places`). |
| `geom_version` | INTEGER | yes |  | none | as is | Version of the place's shape; `1` on every row. |
| `valid_from` | DATE | yes |  | none | as is | First day the shape is valid; empty = always. |
| `valid_to` | DATE | yes |  | none | as is | Last day the shape is valid; empty = still. |
| `geom` | GEOMETRY('OGC:CRS84') | yes |  | none | as is | The shape (WGS84 lon/lat): point, line or polygon. |
| `hit_geom` | GEOMETRY('OGC:CRS84') | yes |  | none | as is | Clickable footprint: polygons as drawn; points and lines buffered by `place_types.hit_buffer_m` (at least 3 m). |
| `geom_kind` | VARCHAR | no | `line`, `point`, `polygon` | none | as is | `polygon`, `point` or `line`. |
| `bounds` | VARCHAR | yes | `approximate`, `official` | none | as is | `official` (from a register) or `approximate` (a circle around a point). |
| `source` | VARCHAR | yes | `Assessor blocks (DataSF), via the map block index`, `DataSF Analysis Neighborhoods (simplified)`, `DataSF EAS`, `DataSF ff3a-iqhv`, `DataSF parcels`, `DataSF street centerlines (CNN)`, `Wikidata point + radius set by qipu` | none | as is | Layer the shape comes from (`DataSF parcels`, `DataSF EAS`, `DataSF street centerlines (CNN)`, ...). |
| `area_m2` | DOUBLE | yes |  | none | as is | Polygon area in m²; empty for points and lines. |
| `length_m` | DOUBLE | yes |  | none | as is | Line length in metres; empty for points and polygons. |
| `hit_area_m2` | DOUBLE | yes |  | none | as is | Area of `hit_geom` in m². |
| `bbox_xmin` | DOUBLE | yes |  | none | as is | West edge of `hit_geom` (longitude). |
| `bbox_ymin` | DOUBLE | yes |  | none | as is | South edge of `hit_geom` (latitude). |
| `bbox_xmax` | DOUBLE | yes |  | none | as is | East edge of `hit_geom` (longitude). |
| `bbox_ymax` | DOUBLE | yes |  | none | as is | North edge of `hit_geom` (latitude). |

## `place_mentions` (892,915 rows)

v0.7: every place string a statement was read with, one row per page and string; `claims.place_mention_id` points here. The input to place resolution, like name_mentions for names.

| column | type | null | values / unit | privacy | public copy | meaning |
|---|---|---|---|---|---|---|
| `mention_id` | VARCHAR | no | e.g. `pm:c4ca4238a0b92382` | none | as is | `pm:` + sha1(page_id\|place_text)[:16]. |
| `city` | VARCHAR | yes |  | none | as is | The city. |
| `place_text` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | The place as given with the statement (`claims.place_label`; the resolved place id when the reading gave no text). |
| `page_id` | VARCHAR | no |  | none | as is | The page it was read on. |
| `place_id` | VARCHAR | yes |  | none | as is | The place it was resolved to (when one string was placed two ways on a page, one of them). |
| `place_precision` | VARCHAR | yes |  | none | as is | How exactly it was placed. |
| `year` | BIGINT | yes |  | none | as is | The year of the earliest statement using it. |
| `n_claims` | BIGINT | no |  | none | as is | Statements that point to it. |

## `place_names` (2,274 rows)

Names of places (official, alias). The historic names index starts here; v1 holds today's names and the search aliases.

| column | type | null | values / unit | privacy | public copy | meaning |
|---|---|---|---|---|---|---|
| `place_id` | VARCHAR | no |  | none | as is | The place (see `places`). |
| `name` | VARCHAR | yes |  | none | as is | The name. |
| `name_kind` | VARCHAR | no | `alias`, `former`, `official` | none | as is | `official` (street and neighbourhood names from DataSF EAS) or `alias` (a name matched in Internet Archive full-text search). |
| `from` | VARCHAR | yes |  | none | as is | Empty in v0.4. Meant for the first year the name was in use. |
| `to` | VARCHAR | yes |  | none | as is | Empty in v0.4. Meant for the last year the name was in use. |
| `source` | VARCHAR | yes | `DataSF EAS`, `Internet Archive full-text search alias (dl_documents)`, `qipu v0.6.1` | none | as is | Where the name comes from. |

## `place_relations` (1,284,690 rows)

How places relate, computed from geometry each build (plus declared links): an address `within` a block, a block `within` a neighbourhood, a street `crosses` a creek ...

| column | type | null | values / unit | privacy | public copy | meaning |
|---|---|---|---|---|---|---|
| `from_id` | VARCHAR | no | e.g. `qipu:sf/place/block/1` | none | as is | place ids |
| `relation` | VARCHAR | no | `contains`, `within`, `overlaps`, `touches`, `crosses`, `near`, `on_street`, `segment_of`, `at_intersection_of`, `part_of`, `replaced_by`, `split_into` ... | none | as is | `within`, `overlaps`, `crosses`, `on_street` ... |
| `to_id` | VARCHAR | no | e.g. `qipu:sf/place/block/1` | none | as is | place ids |
| `basis` | VARCHAR | yes | `declared`, `geometry` | none | as is | `geometry` or `declared` |
| `valid_from` | DATE | yes |  | none | as is | when the relation holds (historic places) |
| `valid_to` | DATE | yes |  | none | as is | when the relation holds (historic places) |
| `overlap_ratio` | DOUBLE | yes |  | none | as is | the geometric measure behind it |
| `distance_m` | DOUBLE | yes |  | none | as is | the geometric measure behind it |
| `note` | VARCHAR | yes | `DataSF EAS parcel number`, `DataSF EAS street`, `DataSF parcels zoning_code (today)`, `area vs area`, `by definition (the feature drains / encloses it)`, `configured street of the feature`, `point in polygon`, `street line intersects the area`, `renamed Cesar Chavez Street in 1995`, `block point inside the feature circle (approximate)`, `block point within 150 m of the feature circle (approximate)` | none | as is | how it was computed |

## `place_types` (82 rows)

The place type vocabulary (spec/vocab/place_types.csv): id segment, family, clickable buffer, salience, Wikidata class.

| column | type | null | values / unit | privacy | public copy | meaning |
|---|---|---|---|---|---|---|
| `type_id` | VARCHAR | no |  | none | as is | Type name, as used in `places.type_id`. |
| `id_segment` | VARCHAR | yes |  | none | as is | Segment used in place ids (`qipu:<city>/place/<id_segment>/...`); frozen once used. |
| `family` | VARCHAR | yes | `address`, `administrative`, `building`, `cadastral`, `coast`, `electoral`, `ephemeral`, `geology`, `hydro`, `informal`, `institution`, `landmark` ... | none | as is | Group for filtering (`regulatory`, `transport`, `hydro`, ...); not a hierarchy. |
| `label` | VARCHAR | yes |  | none | as is | Readable name. |
| `broader_type` | VARCHAR | yes | `address`, `street` | none | as is | The broader type (`street`, `address`). |
| `default_geom` | VARCHAR | yes | `line`, `none`, `point`, `polygon` | none | as is | Usual geometry: `polygon`, `line`, `point` or `none`. |
| `hit_buffer_m` | VARCHAR | yes | `0`, `10`, `12`, `15`, `20`, `25`, `30`, `40`, `50`, `8` | none | as is | How far in metres from a point or line a click still hits it. |
| `salience` | VARCHAR | yes | `0.05`, `0.1`, `0.3`, `0.4`, `0.5`, `0.6`, `0.7`, `0.8`, `0.9`, `1.0` | none | as is | Ranking prior (0-1) for click-anywhere, used after `specificity`. |
| `wikidata_class` | VARCHAR | yes | `Q12280`, `Q123705`, `Q131681`, `Q15243209`, `Q166620`, `Q190928`, `Q194195`, `Q22698`, `Q23397`, `Q23442`, `Q25391`, `Q28575` ... | none | as is | Wikidata class, where sure. |
| `definition` | VARCHAR | yes |  | none | as is | What the type covers. |

## `places` (470,596 rows)

Today's places with stable qipu ids.

| column | type | null | values / unit | privacy | public copy | meaning |
|---|---|---|---|---|---|---|
| `place_id` | VARCHAR | no | e.g. `qipu:sf/place/block/1` | none | as is | `qipu:sf/place/address/eas-<EAS id>`, `.../parcel/<block>-<lot>`, `.../block/<block>`, `.../street/<name-type>`, `.../street/<name>` (printed without type, `street_name`), `.../hood/<slug>`, `.../landmark/<slug>`, `.../city`. |
| `kind` | VARCHAR | no | `address`, `block`, `burn_zone`, `city`, `contaminated_site`, `creek`, `fill_area`, `freeway`, `landmark`, `neighbourhood`, `parcel`, `redevelopment_area` ... | none | as is | address, parcel, block, street, street_name, neighbourhood, landmark, city. |
| `name` | VARCHAR | yes |  | none | as is | Readable name: the address (`26 REARDON RD`), `<block>/<lot> (<addresses>)`, `Assessor block <n>`, the street, neighbourhood or feature name. |
| `parent_id` | VARCHAR | yes |  | none | as is | address -> parcel -> block -> neighbourhood -> city. |
| `apn` | VARCHAR | yes |  | none | as is | Assessor block + lot. |
| `eas_id` | VARCHAR | yes |  | none | as is | DataSF Enterprise Addressing System base id. |
| `cnn` | VARCHAR | yes |  | none | as is | Empty in v1. |
| `wikidata` | VARCHAR | yes | `Q1348537`, `Q211386`, `Q24639`, `Q4049033`, `Q6081595`, `Q62`, `Q6878535`, `Q719317`, `Q757971`, `Q7987437` | none | as is | Wikidata id, for named features. |
| `lat` | DOUBLE | yes | degrees (WGS 84); 36.0..50.0 | none | as is | Latitude: the EAS point for addresses, the mean of address points for parcels, blocks and streets, the Wikidata point for features. |
| `lon` | DOUBLE | yes | degrees (WGS 84); SF-owned land reaches the Sierra (Hetch Hetchy, Camp Mather); -125.0..-119.0 | none | as is | Longitude: the EAS point for addresses, the mean of address points for parcels, blocks and streets, the Wikidata point for features. |
| `number` | BIGINT | yes |  | none | as is | House number (addresses). |
| `street` | VARCHAR | yes |  | none | as is | Street name and type, upper case (`MISSION ST`). |
| `street_id` | VARCHAR | yes |  | none | as is | The street place (`qipu:sf/place/street/<name-type>`). |
| `block` | VARCHAR | yes |  | none | as is | Assessor block. |
| `lot` | VARCHAR | yes |  | none | as is | Assessor lot. |
| `hood` | VARCHAR | yes |  | none | as is | DataSF Analysis Neighborhood (point in polygon). |
| `valid_from` | DATE | yes |  | none | as is | Empty in v1 (the historic place index comes later). |
| `valid_to` | DATE | yes |  | none | as is | Last day the place existed; only the 1906 burn zone has one. |
| `source` | VARCHAR | yes | `DataSF Analysis Neighborhoods (j2bu-swwd)`, `DataSF EAS (3mea-di5p), PDDL`, `DataSF EAS parcel numbers (3mea-di5p), PDDL`, `DataSF Enterprise Addressing System (3mea-di5p), PDDL`, `DataSF ff3a-iqhv (1906 Fire perimeter)`, `DataSF parcels`, `DataSF parcels: zoning_code`, `Wikidata  (point, dates); radius set by qipu`, `Wikidata Q4049033 (point, dates); radius set by qipu`, `Wikidata Q6081595 (point, dates); radius set by qipu`, `Wikidata Q6878535 (point, dates); radius set by qipu`, `Wikidata Q757971 (point, dates); radius set by qipu` ... | none | as is | Where the place comes from (`DataSF Enterprise Addressing System (3mea-di5p), PDDL`, `DataSF parcels`, Wikidata, ...). |
| `radius_m` | DOUBLE | yes |  | none | as is | Radius in metres of the circle qipu draws around a feature's Wikidata point. |
| `gnis` | VARCHAR | yes | `254827` | none | as is | USGS GNIS feature id. |
| `geometry_note` | VARCHAR | yes | `approximate: Wikidata point + radius`, `approximate: a circle around the feature point, not the real boundary` | none | as is | Says when the geometry is approximate (a circle around a Wikidata point). |
| `type_id` | VARCHAR | no | `address`, `assessor_block`, `burn_zone`, `city`, `contaminated_site`, `creek`, `fill_area`, `freeway`, `landmark`, `neighbourhood`, `parcel`, `redevelopment_area` ... | none | as is | Place type (see `place_types`): `parcel`, `address`, `assessor_block`, `street`, `street_name`, `zoning_district`, `landmark`, ... |
| `city` | VARCHAR | no | `sf`, `van` | none | as is | `sf` or `van`. |
| `specificity` | DOUBLE | yes |  | none | as is | `-log10` of the clickable footprint in m² (`place_geometries.hit_area_m2`); higher = smaller, ranked first by `places_at`. |
| `rep_lat` | DOUBLE | yes | degrees (WGS 84); 36.0..50.0 | none | as is | Latitude of a point on the place's geometry (point on surface). |
| `rep_lon` | DOUBLE | yes | degrees (WGS 84); SF-owned land reaches the Sierra (Hetch Hetchy, Camp Mather); -125.0..-119.0 | none | as is | Longitude of a point on the place's geometry (point on surface). |
| `status` | VARCHAR | yes | `current`, `historic` | none | as is | `current`, or `historic` when `valid_to` is set. |
| `area_m2` | DOUBLE | yes |  | none | as is | Polygon area in m²; empty for points and lines. |

## `privacy_rules` (2 rows)

Names of private people are public after N years: sf 75, van 92.

| column | type | null | values / unit | privacy | public copy | meaning |
|---|---|---|---|---|---|---|
| `city` | VARCHAR | no | `sf`, `van` | none | as is | `sf` or `van`. |
| `applies_to` | VARCHAR | yes | `private_person` | none | as is | `private_person`. |
| `embargo_years` | INTEGER | yes |  | none | as is | Years after a record's date before a private person's name is shown: 75 (SF), 92 (Vancouver). |
| `basis` | VARCHAR | yes | `Canada: 92 years (census release rule); public officials and companies always; everything kept, flagged`, `US: names of private people shown publicly only for records older than ~75 years (census-style); public officials and companies always; everything kept, flagged` | none | as is | The rule and its reason, in words. |
| `as_of_year` | INTEGER | yes |  | none | as is | The year the embargo is counted to, pinned per release (qipu_data/cutoff.py AS_OF_YEAR), never today's date: nothing moves by itself on 1 January. |
| `last_open_year` | INTEGER | yes |  | none | as is | The last fully open year: as_of_year minus embargo_years (SF 1951, Vancouver 1934). Every qipu product (data, site, search, agent tools, map) uses this one number. |

## `properties` (85 rows)

The claim property vocabulary (spec/vocab/properties.csv).

| column | type | null | values / unit | privacy | public copy | meaning |
|---|---|---|---|---|---|---|
| `property` | VARCHAR | no |  | none | as is | Property name, as used in `claims.property`. |
| `domain` | VARCHAR | yes | `decision`, `decision \| case`, `decision \| instrument`, `decision \| instrument \| case`, `decision \| instrument \| case \| source \| media`, `event \| story \| decision`, `instrument`, `person`, `person \| name`, `person \| org`, `place`, `place (historic address)` | none | as is | What the subject is (`decision`, `place`, `person \| org`, ...). |
| `range` | VARCHAR | yes |  | none | as is | What the object or value is. |
| `range_kind` | VARCHAR | yes |  | none | as is | The range in a closed machine form: alternatives joined by `\|`, each one of `entity:<class>` (place, person, org, decision, instrument, media, source, event), `enum:<v1/v2/...>`, `name` (a printed name not resolved to an entity), `money`, `number`, `date`, `text`, `bool`. |
| `allowed_qualifiers` | VARCHAR | yes |  | none | as is | The `claims.qualifiers` keys seen on this property in the last release (every key seen, most used first), joined by `\|`; empty when its claims carry none. |
| `claim_kind` | VARCHAR | no | `event`, `interval`, `observation` | none | as is | `event`, `observation` or `interval`. |
| `definition` | VARCHAR | yes |  | none | as is | What the property means. |
| `wikidata_property` | VARCHAR | yes |  | none | as is | Closest Wikidata property (`P127`, ...). |
| `wikidata_match` | VARCHAR | yes | `close`, `close (inverse)`, `exact`, `none`, `verify` | none | as is | How close the Wikidata property is: `exact`, `close`, `close (inverse)`, `verify`, `none`. |
| `use_case` | VARCHAR | yes |  | none | as is | The question the property answers (`Who owned it`, `Follow the money`). |
| `since_spec` | VARCHAR | yes | `1.0`, `v0.1`, `v0.2` | none | as is | Spec version that added it (`v0.1`, `v0.2`, `1.0`). |
| `deprecated_by` | VARCHAR | yes | `concerns_place` | none | as is | The property that replaces it. |
| `sources` | VARCHAR | yes |  | none | as is | Sources or runs that give it. |

## `roles` (318 rows)

Supervisor terms (contiguous years served, from the roster).

| column | type | null | values / unit | privacy | public copy | meaning |
|---|---|---|---|---|---|---|
| `person_id` | VARCHAR | no |  | none | as is | The person (see `persons`). |
| `role` | VARCHAR | yes | `supervisor` | none | as is | `supervisor`. |
| `org_id` | VARCHAR | no | `qipu:sf/org/board-of-supervisors` | none | as is | The body (see `orgs`). |
| `from` | INTEGER | yes |  | none | as is | First year of an unbroken run of years served. |
| `to` | INTEGER | yes |  | none | as is | Last year of that run. |
| `source` | VARCHAR | yes | `Wikipedia: Members of the San Francisco Board of Supervisors`, `printed in the Journal roll calls only`, `votes in the Journal roll calls (v0.6.1)` | none | as is | `Wikipedia: Members of the San Francisco Board of Supervisors`, or `printed in the Journal roll calls only`. |

## `runs` (43 rows)

What made each table: rules, hand work, or a model run (model, prompt).

| column | type | null | values / unit | privacy | public copy | meaning |
|---|---|---|---|---|---|---|
| `run_id` | VARCHAR | no |  | none | as is | The run's id, as used in `run_id` elsewhere. |
| `kind` | VARCHAR | no | `hand`, `llm`, `llm+hand`, `llm+rules`, `rules`, `rules+hand`, `search` | none | as is | How the run worked: `llm`, `rules`, `llm+hand`, `llm+rules`, `rules+hand`, `hand`, `search`. |
| `model` | VARCHAR | yes | `claude-haiku-5-5`, `claude-sonnet-5-5` | none | as is | Model used (`claude-haiku-5-5`, `claude-sonnet-5-5`); empty for rule, hand and search runs. |
| `prompt_version` | VARCHAR | yes | `city_run.py`, `decisions_bench/v2/v2_prompt.py (schema v2.1)`, `ia_records/scripts/dl_tag.py`, `ia_records/scripts/sfra_extract.py`, `street_decisions PROMPT.md / UNPLACED_PROMPT.md / IV_PROMPT.md`, `street_decisions/RESWEEP_PROMPT.md` | none | as is | Prompt file or script the run used. |
| `schema_version` | VARCHAR | yes | `1`, `v2.1` | none | as is | Output schema version (`1`, `v2.1`) where set. |
| `settings` | VARCHAR | yes |  | none | as is | Empty in v0.4. |
| `started` | VARCHAR | yes | `2026-07-28`, `2026-09-02`, `2026-10`, `2026-10-01`, `2026-10-06`, `2026-10-12`, `2026-10-13`, `2026-10-14` | none | as is | When the run started (YYYY-MM-DD or YYYY-MM). |
| `pages` | INTEGER | yes |  | none | as is | Empty in v0.4. |
| `cost_usd` | DOUBLE | yes |  | none | as is | Empty in v0.4. |
| `gold_scores` | VARCHAR | yes |  | none | as is | Empty in v0.4. |
| `notes` | VARCHAR | yes | `Board items the location gate dropped, re-swept (resweep.py) and read one batch of 60 at a time [unmeasured: no gold for this run]`, `Democracy's Library (IGS) SF items: the hoods, streets and places each is about, read from metadata and the start of the OCR [unmeasured: no gold for this run]`, `Redevelopment Agency property files (Western Addition A-2), one row per parcel page; matched to EAS by sfra_build.py`, `blocks/build_index.py: decision -> assessor blocks (tiers street/corner/segment/addr/stated); accuracy.json: 88.9% exact on 3,195 checks`, `decisions v2 for Vancouver council minutes (decisions_runs/van_v21_haiku)`, `decisions v2: one call per page, previous page as context, Message Batches (decisions_runs/sf_v21_haiku)`, `open-public-data pipeline/minutes extract.py (location gate) + blocks/build_core_table.py classification (form/rule/title rules, some LLM labels)`, `scripts/11_stage_extra.py: every photo, film, recording, clip and document the map shows, with credit, licence and a rights note`, `scripts/20_rollcalls.py: printed roll calls -> votes; names matched to the Wikipedia roster`, `scripts/30_build.py: "<number> <street>" texts matched exactly to DataSF EAS addresses`, `scripts/80_showcase_fetch.py: archive.org advancedsearch (title/subject/description), Open Library search and Wayback CDX for the six SF showcase places; metadata only, no model`, `site rows (su:/sd:) of the map: plain line, outcome, importance, accuracy fixes [unmeasured: no gold for this run]` ... | none | as is | What the run made, in a line; model runs with no gold end with `[unmeasured: no gold for this run]`. |
| `gold_id` | VARCHAR | yes | `decisions_bench Vancouver gold (30 pages, 48 decisions: Opus-written, 21 decisions hand-checked)`, `decisions_bench/gold.jsonl (SF, 40 pages: an Opus-5.5-written key, no record of a hand check) + v2 Opus key`, `decisions_bench/gold.jsonl roll calls (Opus-written key), scripts/22_rollcall_gold.py`, `ia_records/sfra/bench (18 pages)` | none | as is | The gold set the run was scored against (see `field_scores`); empty when unmeasured. |
| `prompt_id` | VARCHAR | yes | `city_run.py`, `decisions_bench/v2/v2_prompt.py (schema v2.1)`, `ia_records/scripts/dl_tag.py`, `ia_records/scripts/sfra_extract.py`, `street_decisions PROMPT.md / UNPLACED_PROMPT.md / IV_PROMPT.md`, `street_decisions/RESWEEP_PROMPT.md` | none | as is | Same as `prompt_version`. |
| `extraction_table` | VARCHAR | yes |  | none | as is | Empty in v0.4. |
| `city` | VARCHAR | no | `sf`, `van` | none | as is | `sf` or `van`. |

## `sources` (6,377 rows)

Every source cited. archive.org items carry their collections and Democracy's Library membership.

| column | type | null | values / unit | privacy | public copy | meaning |
|---|---|---|---|---|---|---|
| `source_id` | VARCHAR | no |  | none | as is | The source: an archive.org identifier, or `web:<url>` for other sources. |
| `collection` | VARCHAR | yes |  | none | as is | First archive.org collection of the item. |
| `collections` | VARCHAR[] | yes |  | none | as is | All archive.org collections of the item. |
| `in_democracys_library` | BOOLEAN | yes |  | none | as is | True when one of the item's collections is in Democracy's Library scope: the DL pools (instituteofgovernmentalstudieslibrary, USGovernmentDocuments, fedlink, governmentpublications, us_census, nasa, ...) or one of the 830 child collections of `democracys-library` (DL_INVENTORY.md section 1). SF Public Library scans are not in DL. |
| `dl_rule` | VARCHAR | yes | `collection in Democracy's Library scope: USGovernmentDocuments,democracys-library,igscalocalgovdocs,igsl_annual-report-sf-public-utilities,instituteofgovernmentalstudieslibrary`, `collection in Democracy's Library scope: USGovernmentDocuments,democracys-library,igscalocalgovdocs,igsl_san-francisco-real-prop-report,instituteofgovernmentalstudieslibrary`, `collection in Democracy's Library scope: USGovernmentDocuments,democracys-library,igscalocalgovdocs,igsl_sf-bos-annual-salary-ord,instituteofgovernmentalstudieslibrary`, `collection in Democracy's Library scope: USGovernmentDocuments,democracys-library,igscalocalgovdocs,igsl_sfbcdc-minor-permits-granted,instituteofgovernmentalstudieslibrary`, `collection in Democracy's Library scope: USGovernmentDocuments,us-supreme-court`, `collection in Democracy's Library scope: democracys-library,igscalocalgovdocs,instituteofgovernmentalstudieslibrary`, `collection in Democracy's Library scope: igscalocalgovdocs,instituteofgovernmentalstudieslibrary`, `collection in Democracy's Library scope: nasa`, `no collection in DL scope (DL_INVENTORY.md section 1)` | none | as is | Which collection made it DL, or why not. |
| `kind` | VARCHAR | no | `appraisal`, `audio`, `board_journal`, `board_minutes`, `book`, `collection`, `council_minutes`, `directory`, `eir`, `election_results`, `encyclopedia`, `film` ... | none | as is | board_journal, board_minutes, council_minutes, report, plan, eir, book, film, audio, photo, open_data, encyclopedia, map, web ... |
| `title` | VARCHAR | yes |  | catalogue | as is | Title from archive.org, else a citing claim's source label. |
| `creator` | VARCHAR | yes |  | catalogue | as is | Creator as given by archive.org. |
| `publisher` | VARCHAR | yes |  | catalogue | as is | Publisher as given by archive.org. |
| `year_from` | BIGINT | yes |  | none | as is | Year of the item (archive.org date), else the earliest year of the claims citing it. |
| `year_to` | BIGINT | yes |  | none | as is | Latest year of the claims citing it, else the item's year. |
| `pages` | BIGINT | yes |  | none | as is | archive.org imagecount. |
| `ocr_quality` | VARCHAR | yes | `ABBYY FineReader 11.0`, `ABBYY FineReader 11.0 (Extended OCR)`, `ABBYY FineReader 8.0`, `ABBYY FineReader 9.0`, `tesseract 4.1.1`, `tesseract 5.0.0-1-g862e`, `tesseract 5.0.0-alpha-20201231-10-g1236`, `tesseract 5.0.0-beta-20210815`, `tesseract 5.0.0-rc1-12-g88b4`, `tesseract 5.0.0-rc2-1-gf788`, `tesseract 5.1.0-1-ge935`, `tesseract 5.2.0-1-gc42a` ... | none | as is | The OCR engine archive.org names (`tesseract 5.3.0`, `ABBYY FineReader 9.0`, ...); not a quality score. |
| `licence` | VARCHAR | yes |  | none | as is | archive.org licence URL or rights text (<= 300 characters); for web sources the citing claims' licence code. |
| `holder` | VARCHAR | yes |  | catalogue | as is | archive.org contributor: the library that gave the copy (`San Francisco Public Library`, ...). |
| `digitized` | BOOLEAN | yes |  | none | as is | True for archive.org items; empty for web sources. |
| `on_archive_org` | BOOLEAN | yes |  | none | as is | True when the source is an archive.org item. |
| `url` | VARCHAR | yes |  | none | as is | Link to the source: the archive.org item page, or the web page. |
| `n_claims` | DOUBLE | yes |  | none | as is | Claims citing it. |
| `notes` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | Empty in v0.4. |

## `withdrawn` (54,652 rows)

v0.7: record time. Every id that was in an earlier release and is gone from this one, with why and what replaced it; carried forward from release to release (append-only). Valid time is t_from / t_to / t_lo / t_hi on the claims.

| column | type | null | values / unit | privacy | public copy | meaning |
|---|---|---|---|---|---|---|
| `id` | VARCHAR | no |  | none | as is | The claim or entity id. |
| `kind` | VARCHAR | no | `claim`, `place`, `person`, `decision`, `instrument`, `org`, `media` | none | as is | What the id named. |
| `release_added` | VARCHAR | yes |  | none | as is | The release it first appeared in (claims). |
| `release_removed` | VARCHAR | no |  | none | as is | The first release without it. |
| `reason` | VARCHAR | no | `reread`, `error`, `merge`, `privacy`, `rights`, `retired` | none | as is | `reread` (a new reading of the same page replaced it), `merge` (folded into another record: a duplicate roll call, a renumbered instrument), `error` (a misreading, no replacement), `privacy`, `rights` (takedowns), `retired`. |
| `replaced_by` | VARCHAR | yes |  | none | as is | The id that replaces it in this release, if any. |
| `note` | VARCHAR | yes |  | none | as is | The reason in words. |

## `x_appraisals` (3,189 rows)

Extraction table: the SF Redevelopment Agency property files of the Western Addition A-2 project (SFPL scans on archive.org), one row per parcel page (claude-haiku-5-5, sfra_extract.py), matched to today's parcels by sfra_build.py.

| column | type | null | values / unit | privacy | public copy | meaning |
|---|---|---|---|---|---|---|
| `block` | VARCHAR | yes |  | none | as is | Assessor block of today it was matched to. |
| `lot` | VARCHAR | yes |  | none | as is | Assessor lot, when matched to an address. |
| `precision` | VARCHAR | yes | `address`, `block`, `near`, `printed block` | none | as is | `address` (door matched to an EAS address), `block` (matched by its street hundred), `near` (old door gone: nearest door of today within 60 m), `printed block`. |
| `xy` | DOUBLE[] | yes |  | none | as is | `[x, y]` in metres on the qipu map's local grid: the matched door, or the mean of the block's doors. |
| `year` | BIGINT | yes |  | none | as is | Year of the report (for a sale, the sale year when it is close). |
| `text` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | Plain line built from the fields (`Redevelopment Agency appraisal: 3-story frame structure; appraised at $...`). Person-like names are withheld for records under the 75-year privacy filter. |
| `where` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | The printed address in today's street spelling (`1866 Turk St`). Person-like words are withheld for records under the 75-year privacy filter (this also hits some street names). |
| `address_printed` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | Address as printed, read by a model from the page image and OCR. |
| `block_printed` | VARCHAR | yes |  | none | as is | Block as printed, read by a model from the page image and OCR. |
| `lot_printed` | VARCHAR | yes |  | none | as is | Lot as printed, read by a model from the page image and OCR. |
| `parcel_printed` | VARCHAR | yes |  | none | as is | Agency parcel number as printed, read by a model from the page image and OCR. |
| `kind_page` | VARCHAR | yes | `appraisal`, `folder`, `interview`, `property_summary`, `rent_schedule`, `sale` | none | as is | Kind of page: `appraisal`, `sale`, `property_summary`, `folder`, `interview`, `rent_schedule`. |
| `building` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | Building description as printed, read by a model from the page image and OCR. |
| `units` | VARCHAR | yes |  | none | as is | Units or rooms as printed, read by a model from the page image and OCR. |
| `rent_example` | VARCHAR | yes |  | none | as is | A sample rent line as printed, read by a model from the page image and OCR. |
| `rent_total` | VARCHAR | yes |  | none | as is | Total rent as printed, read by a model from the page image and OCR. |
| `appraised_value` | VARCHAR | yes |  | none | as is | Appraised value as printed, read by a model from the page image and OCR. |
| `assessed_total` | VARCHAR | yes |  | none | as is | Total assessed value as printed, read by a model from the page image and OCR. |
| `sale_price` | VARCHAR | yes |  | none | as is | Sale price as printed (sales study pages), read by a model from the page image and OCR. |
| `sale_date` | VARCHAR | yes |  | none | as is | Sale date as printed, read by a model from the page image and OCR. |
| `owner` | VARCHAR | yes |  | person_name | shown for officials and organisations; a private person is withheld under the embargo | Owner as printed (for a sale, the grantee). Under the 75-year privacy filter the public files show `a owner (name withheld until <year>)`. |
| `present_use` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | Present use as printed (`Flats`, `Apartments`), read by a model from the page image and OCR. |
| `confidence` | VARCHAR | yes | `high`, `medium` | none | as is | The model's legibility rating: `high` or `medium` (`low` rows are dropped). |
| `item` | VARCHAR | yes |  | none | as is | archive.org identifier of the property file (`SFH371-...`). |
| `page` | BIGINT | yes |  | none | as is | archive.org BookReader page index in the item. |
| `url` | VARCHAR | yes |  | none | as is | Link to the page on archive.org. |
| `run_id` | VARCHAR | no | `sfra-appraisals-haiku55-2026-10` | none | as is | `sfra-appraisals-haiku55-2026-10` (see `runs`). |
| `seq` | BIGINT | yes |  | none | as is | Line number of the row in the run's rows file. |
| `page_id` | VARCHAR | no | e.g. `journalofproceed30sanfrich:1` | none | as is | The archive.org page: `<identifier>:<leafNum>` (IA's leaf number, the jp2 / IIIF number), or `<identifier>:d<n>` where only the BookReader index is known, or `<url>:<page>` for a web document. `nopage:<seq>` where the source gives no page number (sfbos rows without one): the row is kept, page_url is NULL. |
| `page_url` | VARCHAR | yes | e.g. `https://archive.org/details/journalofproceed30sanfrich/page/n1` | none | as is | Opens the page: archive.org BookReader link built from the leaf map (audited against IA's scandata), or `<pdf url>#page=<n>` for a PDF. NULL only where page_id is `nopage:<seq>`. |
| `row_key` | VARCHAR | no |  | none | as is | `<run_id>/<page_id>/<seq>`. |

## `x_block_tract` (20,389 rows)

One assessor block in one tract map: the tract holding most of the block's EAS address points. Not from the Internet Archive (DataSF EAS address points and Census tract maps).

| column | type | null | values / unit | privacy | public copy | meaning |
|---|---|---|---|---|---|---|
| `block` | VARCHAR | yes |  | none | as is | Assessor block. |
| `boundary_vintage` | INTEGER | yes |  | none | as is | Tract map year (1990, 2000, 2010, 2020). |
| `tract` | VARCHAR | yes |  | none | as is | Six-digit tract code. |
| `share_of_address_points` | DOUBLE | yes |  | none | as is | Share of the block's address points inside that tract (below 0.8: the block straddles tracts). |
| `address_points` | INTEGER | yes |  | none | as is | Number of address points used. |

## `x_business_locations` (248,990 rows)

One registered business location (DataSF g8m3-pdis, PDDL) resolved to today's EAS address and block, with start and end years. Owner names are not carried; the business name is shown when it reads as a firm name and withheld under the privacy rule when it reads as a person's. Not from the Internet Archive (DataSF open data, PDDL).

| column | type | null | values / unit | privacy | public copy | meaning |
|---|---|---|---|---|---|---|
| `record_id` | VARCHAR | no |  | none | as is | DataSF uniqueid (record link: data.sf.gov/resource/g8m3-pdis.json?uniqueid=...). |
| `block` | VARCHAR | yes |  | none | as is | Assessor block. |
| `address_place_id` | VARCHAR | no |  | none | as is | EAS address place. |
| `address` | VARCHAR | yes |  | none | as is | Address as in EAS. |
| `address_exact` | BOOLEAN | yes |  | none | as is | True when the door number exists today. |
| `start_year` | BIGINT | yes |  | none | as is | Location start year. |
| `start_is_register_floor` | BOOLEAN | yes |  | none | as is | True when the start is the register floor date 1968-10-01 (read: by 1968). |
| `end_year` | BIGINT | yes |  | none | as is | Location end year (null: still open). |
| `administratively_closed` | BOOLEAN | yes |  | none | as is | Closed by the Tax Collector rather than by the business. |
| `naics` | VARCHAR | yes |  | none | as is | Self-reported NAICS code. |
| `naics_title` | VARCHAR | yes |  | catalogue | as is | NAICS 2022 title. |
| `kind` | VARCHAR | yes |  | none | as is | Plain kind used on the map (a laundromat, a bar ...), null when not a storefront. |
| `group` | VARCHAR | yes | `food`, `leisure`, `lodging`, `service`, `shop`, `worship` | none | as is | food, shop, service, lodging, leisure, worship. |
| `business_name` | VARCHAR | yes |  | person_name | shown for officials and organisations; a private person is withheld under the embargo | The DBA name as registered. A firm name is always shown; a name that reads as a person's is withheld for 75 years after the location start year (the privacy rule), so almost all personal DBA names are withheld. Owner names are never carried. |
| `hood` | VARCHAR | yes |  | none | as is | Analysis neighborhood (DataSF). |

## `x_census_tract` (925 rows)

One census tract in one census year (1980-2020): 100% counts of people, households and homes, plus sample/ACS foreign-born and median household income. Public domain, US Census Bureau. Not from the Internet Archive.

| column | type | null | values / unit | privacy | public copy | meaning |
|---|---|---|---|---|---|---|
| `year` | INTEGER | yes |  | none | as is | Census year. |
| `boundary_vintage` | INTEGER | yes |  | none | as is | Which tract map the code belongs to (1990 map for 1980 and 1990). |
| `tract` | VARCHAR | yes |  | none | as is | Six-digit census tract code (e.g. 016300 = tract 163). |
| `tract_name` | VARCHAR | yes |  | none | as is | Tract as printed (163, 101.01). |
| `pop` | INTEGER | yes |  | none | as is | Persons. |
| `households` | INTEGER | yes |  | none | as is | Households (occupied homes). |
| `units` | INTEGER | yes |  | none | as is | Housing units. |
| `occupied` | INTEGER | yes |  | none | as is | Occupied housing units. |
| `vacant` | INTEGER | yes |  | none | as is | Vacant housing units. |
| `owners` | INTEGER | yes |  | none | as is | Owner-occupied homes. |
| `renters` | INTEGER | yes |  | none | as is | Renter-occupied homes. |
| `under18` | INTEGER | yes |  | none | as is | Persons under 18. |
| `over65` | INTEGER | yes |  | none | as is | Persons 65 and over. |
| `hispanic` | INTEGER | yes |  | none | as is | Persons of Hispanic/Latino (1980: Spanish) origin, any race. |
| `white_nh` | INTEGER | yes |  | none | as is | White alone, not Hispanic (1990+). |
| `white_race` | INTEGER | yes |  | none | as is | White by race incl. Hispanic (1980 only). |
| `black` | INTEGER | yes |  | none | as is | Black (1980 and 1990: by race, any origin; 2000+: Black alone, not Hispanic). |
| `asian` | INTEGER | yes |  | none | as is | Asian and Pacific Islander (1980 and 1990: by race, any origin; 2000+: alone, not Hispanic). |
| `foreign_born` | INTEGER | yes |  | none | as is | Foreign-born persons (sample: STF3 / SF3 / ACS 5-year). |
| `median_income` | INTEGER | yes |  | none | as is | Median household income in the year before the census, dollars of that year (2010/2020: ACS 5-year). |
| `median_income_2024usd` | DOUBLE | yes |  | none | as is | The same in 2024 dollars (CPI-U). |
| `source_label` | VARCHAR | yes | `US Census 1980, Summary Tape Files 1A and 3A (tract)`, `US Census 1990, Summary Tape Files 1A and 3A (tract)`, `US Census 2000, Summary Files 1 and 3 (tract)`, `US Census 2010 (SF1) and ACS 2006-2010 (tract)`, `US Census 2020 (DHC) and ACS 2016-2020 (tract)` | catalogue | as is | Source tables. |
| `source_url` | VARCHAR | yes |  | catalogue | as is | Source file or data.census.gov table for the tract. |

## `x_decisions` (748,894 rows)

Extraction table: one row per Board item per run (DATA_ARCHITECTURE.md 7.2), with the columns the existing runs filled; the rest are empty until decisions v2 lands in the same shape.

| column | type | null | values / unit | privacy | public copy | meaning |
|---|---|---|---|---|---|---|
| `run_id` | VARCHAR | no | `bos-corpus-2026-09`, `resweep-d1960-haiku55-2026-10`, `resweep-d1970-haiku55-2026-10`, `resweep-d1980-haiku55-2026-10`, `resweep-d1990-haiku55-2026-10`, `resweep-d2000-haiku55-2026-10`, `resweep-d2010-haiku55-2026-10`, `sf-v21-haiku-1014`, `van-v21-haiku-1014` | none | as is | The extraction run that read the row (see `runs`). |
| `page_id` | VARCHAR | no | e.g. `journalofproceed30sanfrich:1` | none | as is | The archive.org page: `<identifier>:<leafNum>` (IA's leaf number, the jp2 / IIIF number), or `<identifier>:d<n>` where only the BookReader index is known, or `<url>:<page>` for a web document. `nopage:<seq>` where the source gives no page number (sfbos rows without one): the row is kept, page_url is NULL. |
| `page_url` | VARCHAR | yes | e.g. `https://archive.org/details/journalofproceed30sanfrich/page/n1` | none | as is | Opens the page: archive.org BookReader link built from the leaf map (audited against IA's scandata), or `<pdf url>#page=<n>` for a PDF. NULL only where page_id is `nopage:<seq>`. |
| `seq` | VARCHAR | yes |  | none | as is | Row id inside the run: the corpus item id (`<volume>\|<n>`) for v1 rows, the item's position on the page (1, 2, ...) for decisions v2 rows. |
| `decision_id` | VARCHAR | no |  | none | as is | The decision the row reads (see `decisions`); a v2 reading of a v1 item carries the v1 id. |
| `year` | BIGINT | yes |  | none | as is | Year of the Journal volume or meeting. |
| `date` | VARCHAR | yes |  | none | as is | Meeting date (YYYY-MM-DD) where known; empty on decisions v2 rows. |
| `quote` | VARCHAR | yes |  | raw_text | left out for records under the embargo | The item's text as printed (v1: first 300 characters; v2: the quote the model copied). Empty (withheld in the public files for records under the privacy embargo (75 years in SF, 92 in Vancouver)). |
| `heading_on_prev` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | v1 rows: the item's printed heading. Decisions v2 rows: `true`/`false`, whether the heading is on the previous page. Person-like names are withheld for records under the 75-year privacy filter (92 years in Vancouver). |
| `kind` | VARCHAR | yes | `appointment`, `ballot_measure`, `charter_amendment`, `claim`, `communication`, `contract`, `hearing`, `license`, `motion`, `ordinance`, `other`, `resolution` | none | as is | Kind of item: `resolution`, `ordinance`, `motion`, `contract`, `hearing`, `permit`, `appointment`, `claim`, `other`, ... |
| `number` | VARCHAR | yes |  | none | as is | Resolution or ordinance number as printed (v1: parsed from the item's opening words, see `decision_numbers`; v2: read by the model). |
| `file_no` | VARCHAR | yes |  | none | as is | Board file number as printed. |
| `stage` | VARCHAR | yes | `adopted`, `final`, `first reading`, `hearing` | none | as is | Decisions v2: stage reached, from `action_raw`: `adopted`, `final`, `first reading` or `hearing`; empty for other actions and v1 rows. |
| `outcome` | VARCHAR | yes | `amended`, `approved by voters`, `continued`, `passed`, `passed first reading`, `pending`, `referred`, `refused`, `rejected`, `rejected by voters`, `submitted_to_voters`, `tabled` ... | none | as is | Result: `passed`, `refused`, `pending`, `referred`, `continued`, `vetoed`, `unknown`, ... (v2: mapped from `action_raw`). |
| `action` | VARCHAR | yes | `accepted`, `amended`, `appointed`, `appropriated`, `commended`, `granted`, `ordered`, `other`, `paid`, `refused`, `settled`, `voted-or-scheduled` | none | as is | v1 corpus rows: what the Board did, as classified (`ordered`, `voted-or-scheduled`, `granted`, `amended`, `refused`, ...); v2 rows use `action_raw`. |
| `continued_to` | VARCHAR | yes |  | none | as is | Decisions v2: when the item was continued to, as printed (`next meeting of Council`, `two weeks`). |
| `amended` | BOOLEAN | yes |  | none | as is | Decisions v2: true when the item was amended on the pages read. |
| `ayes_n` | BIGINT | yes |  | none | as is | Ayes count as printed (v1: from the item's roll call in `x_roll_calls`; v2: read by the model). |
| `noes_n` | BIGINT | yes |  | none | as is | Noes count as printed (v1: from the item's roll call; v2: read by the model). |
| `absent_n` | BIGINT | yes |  | none | as is | Absent count as printed (v1: from the item's roll call; v2: read by the model). |
| `ayes` | VARCHAR[] | yes |  | public_official | as is | Surnames voting aye, as printed (v1: from `x_roll_call_votes`; v2: read by the model). |
| `noes` | VARCHAR[] | yes |  | public_official | as is | Surnames voting no, as printed (v1: from `x_roll_call_votes`; v2: read by the model). |
| `absent` | VARCHAR[] | yes |  | public_official | as is | Surnames absent or excused, as printed (v1: from `x_roll_call_votes`; v2: read by the model). |
| `mover` | VARCHAR | yes |  | public_official | as is | Decisions v2: surname of the member who moved the deciding motion, as printed. |
| `seconder` | VARCHAR | yes |  | public_official | as is | Decisions v2: surname of the member who seconded the deciding motion, as printed. |
| `parties_text` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | Re-swept rows: the parties involved, written by a model. Person-like names are withheld for records under the 75-year privacy filter (92 years in Vancouver). |
| `money_text` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | v1 corpus rows: amounts printed in the item, as text. |
| `places_text` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | Place as written: the re-sweep model's place text, or the v1 streets joined with ` & `. Person-like names are withheld for records under the 75-year privacy filter (92 years in Vancouver). |
| `block` | VARCHAR | yes |  | none | as is | v1 corpus rows: assessor block the 2026-09 pipeline placed the item on. |
| `topic` | VARCHAR | yes |  | none | as is | v1 rows: topic label (`running-the-city`, `admin`, `the-street`, `land-and-building`, ...); v2 rows use `topics`. |
| `subtopic` | VARCHAR | yes |  | none | as is | v1 corpus rows: finer topic (`vote-or-scheduling`, `zoning`, `city-buys-land`, ...). |
| `plain` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | One-line plain summary, written by a model (check the scan). Person-like names are withheld for records under the 75-year privacy filter (92 years in Vancouver). |
| `record_type` | VARCHAR | yes | `cut-in-half`, `decision`, `departmental-work`, `index-row`, `page-furniture`, `public-comment`, `too-damaged`, `vote-outcome-only` | none | as is | v1 corpus rows: what the text is (`decision`, `vote-outcome-only`, `public-comment`, `cut-in-half`, `page-furniture`, `index-row`, ...). |
| `classified_by` | VARCHAR | yes | `form`, `fund`, `near-duplicate`, `purpose-clause`, `record`, `rule`, `title` | none | as is | v1 corpus rows: what the 2026-09 classification went by (`form`, `rule`, `title`, `purpose-clause`, `record`, `near-duplicate`, `fund`). |
| `confidence` | DOUBLE | yes |  | none | as is | Decisions v2: 0-1 from rule checks: 1.0 quote found on the page, 0.7 found loosely, 0.4 not found; x0.8 for action `other` or noes count not matching names. |
| `scan_url` | VARCHAR | yes |  | none | as is | Link to the page: the archive.org scan or the minutes URL. |
| `page_exact` | BOOLEAN | yes |  | none | as is | True when the archive.org page number was converted from the OCR leaf with the per-volume leaf map; false = may be a page or two off. |
| `action_raw` | VARCHAR | yes | `adopted`, `amended`, `continued`, `continues_next_page`, `failed`, `hearing_held`, `ordered_submitted_to_voters`, `other`, `passed_final`, `passed_first_reading`, `referred`, `tabled` ... | none | as is | Decisions v2: the last action on the pages read, as the model chose it (`adopted`, `passed_final`, `referred`, `continues_next_page`, ...). |
| `department` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | Decisions v2: the department, board, officer or committee that reported or requested the item, as printed. |
| `parties` | STRUCT("name" VARCHAR, "role" VARCHAR)[] | yes |  | nested | field by field (below) | Decisions v2: private parties named, with their role. Empty in the public files under the privacy embargo (75 years SF, 92 Vancouver): every row in v0.4. |
| `parties.name` | | | | person_name | shown for officials and organisations; a private person is withheld under the embargo | the party as read (organisation shown; a person withheld under the embargo) |
| `parties.role` | | | | none | as is | claimant, owner, appellant, applicant, contractor ... |
| `money` | STRUCT(amount DOUBLE, currency VARCHAR, purpose VARCHAR, payee VARCHAR, direction INTEGER, value_year BIGINT, amount_2026 DOUBLE, index_used VARCHAR)[] | yes |  | nested | field by field (below) | Decisions v2: each amount with `currency` (`USD` SF, `CAD` Vancouver), `purpose` and `payee` as read by the model; `direction` is empty. `payee` is not name-filtered. |
| `money.amount` | | | | none | as is | the amount as read |
| `money.currency` | | | | none | as is | USD (SF) or CAD (Vancouver); rule sf_money_in_usd |
| `money.purpose` | | | | free_text | names of private people withheld under the embargo | what the money is for |
| `money.payee` | | | | person_name | shown for officials and organisations; a private person is withheld under the embargo | who is paid |
| `money.direction` | | | | none | as is | 1 = the city pays, -1 = the city receives |
| `money.value_year` | | | | none | as is | the year the amount is in |
| `money.amount_2026` | | | | none | as is | the amount in 2026 dollars (CPI, see cpi_us) |
| `money.index_used` | | | | none | as is | the price index used for amount_2026 |
| `places` | STRUCT("text" VARCHAR, street VARCHAR, number VARCHAR, from_street VARCHAR, to_street VARCHAR, block VARCHAR, lot VARCHAR, area VARCHAR, "precision" VARCHAR)[] | yes |  | nested | field by field (below) | Decisions v2: each place as printed, with street, number, from/to street, block, lot, area and `precision`. |
| `places.text` | | | | free_text | names of private people withheld under the embargo | the place as printed |
| `places.street` | | | | none | as is | street name |
| `places.number` | | | | none | as is | house number |
| `places.from_street` | | | | none | as is | segment start |
| `places.to_street` | | | | none | as is | segment end |
| `places.block` | | | | none | as is | assessor block |
| `places.lot` | | | | none | as is | assessor lot |
| `places.area` | | | | free_text | names of private people withheld under the embargo | a named area |
| `places.precision` | | | | none | as is | how exactly it is placed |
| `zoning` | STRUCT(from_district VARCHAR, to_district VARCHAR)[] | yes |  | nested | field by field (below) | Decisions v2: a zoning change, `from_district` and `to_district` as printed. |
| `zoning.from_district` | | | | none | as is | zoning before |
| `zoning.to_district` | | | | none | as is | zoning after |
| `related` | STRUCT(relation VARCHAR, ref_number VARCHAR)[] | yes |  | nested | field by field (below) | Decisions v2: numbers of other measures the item amends or refers to (`relation` is always `refers_to`). |
| `related.relation` | | | | none | as is | amends, repeals, refers_to ... |
| `related.ref_number` | | | | none | as is | the other instrument number |
| `dates` | STRUCT(date VARCHAR, "role" VARCHAR)[] | yes |  | nested | field by field (below) | Decisions v2: dates that set or schedule the decision, with `role` (`effective`, `continued_to`, `hearing`, `deadline`, `other`). |
| `dates.date` | | | | none | as is | a date printed in the item |
| `dates.role` | | | | none | as is | what the date is (hearing, effective ...) |
| `topics` | VARCHAR[] | yes |  | none | as is | Decisions v2: topics chosen by a model from a fixed list (`housing`, `whats-being-built`, `parks`, `transit`, ...). |
| `city` | VARCHAR | no | `sf`, `van` | none | as is | `sf` or `van`. |
| `privacy_flag` | VARCHAR | yes | `may_name_private_person_after_1950` | none | as is | `may_name_private_person_after_1950` on rows from 1952 on that have a quote. |
| `decision_group` | VARCHAR | yes |  | none | as is | One id per printed item (v0.6.1; see decisions.decision_group). |
| `row_key` | VARCHAR | no |  | none | as is | `<run_id>/<page_id>/<seq>`. |

Not in the public copy: `interest`, `significance`, `interest_why`.

## `x_directory`

Extraction table: the San Francisco city directories of 1910, 1915, 1920, 1926, 1930, 1936, 1940 and 1945 (Crocker-Langley and Polk, archive.org scans from the San Francisco Public Library), one row per printed line that resolves to a San Francisco address or block today (1,940,521 of the 2,695,104 lines read; the others give no address in the city or did not resolve): the name, occupation and addresses as printed, read by claude-haiku-5-5 and resolved to today's EAS address and block (source-review run). The names are shown: the editions are more than 75 years old (the privacy rule withholds private names only for records under 75 years; Daniel 15/10). The 1936 edition is measured against a hand check; 1910-1926 are unmeasured.

| column | type | null | values / unit | privacy | public copy | meaning |
|---|---|---|---|---|---|---|
| `run_id` | VARCHAR | no | `citydir-haiku55-2026-10` | none | as is | The run that read the pages (see `runs`). |
| `page_id` | VARCHAR | no | e.g. `polkscrockerlang1936dire:1` | none | as is | The page: `<archive.org identifier>:<leaf>`. |
| `page_url` | VARCHAR | yes | e.g. `https://archive.org/details/polkscrockerlang1936dire/page/n1` | none | as is | Opens the page on archive.org. |
| `row_key` | VARCHAR | no | e.g. `c:c4ca4238a0b923820dcc509a6f75849b` | none | as is | The line's id in the source-review run (the id of its staged claim). |
| `volume` | VARCHAR | no |  | none | as is | The archive.org identifier of the directory volume. |
| `year` | BIGINT | no | 1850..2030 | none | as is | The edition year (1945 for the 1945-46 edition). |
| `line_kind` | VARCHAR | yes | `resided`, `householder`, `boarded`, `roomed`, `business` | none | as is | `resided` (r: resides), `householder` (h: householder) or `business` (a business listing or classified line). |
| `name_printed` | VARCHAR | yes |  | person_name | shown for officials and organisations; a private person is withheld under the embargo | The name as printed (`Hunter Eliz`), or the business name. Shown for these editions (over 75 years old); a row without a year would be withheld. |
| `occupation_printed` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | The occupation as printed, with the employer abbreviation (`elk SPCo`). |
| `trade_printed` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | Business lines: the trade as printed (`barber`, `gro`). |
| `address_printed` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | The address as printed (`642 Oak`). |
| `marker` | VARCHAR | yes |  | none | as is | The printed residence marker (`r`, `h`, `b` ...). |
| `spouse_note` | VARCHAR | yes |  | person_name | shown for officials and organisations; a private person is withheld under the embargo | The printed spouse or widow note (`wid Jas`). |
| `business_type` | VARCHAR | yes |  | none | as is | Business lines: the kind of business in plain words (`a cigar store`). |
| `classified_heading` | VARCHAR | yes |  | catalogue | as is | The classified-section heading the line is printed under, when it is one. |
| `occupation_group` | VARCHAR | yes |  | none | as is | The occupation group (clerks and office, building trades ...). |
| `place_id` | VARCHAR | yes | e.g. `qipu:sf/place/address/eas-1` | none | as is | Today's EAS address (or block) the printed address resolves to. |
| `block_today` | VARCHAR | yes |  | none | as is | The assessor block the printed address falls in today. |
| `exact_door_today` | BOOLEAN | yes |  | none | as is | True when the printed door number exists today. |
| `quote` | VARCHAR | yes |  | raw_text | names of private people withheld under the embargo | The printed line as read (`Hunter Eliz elk SPCo r 642 Oak`). |

## `x_dl_facts` (150,446 rows)

Facts read from Democracy's Library documents about SF places (a model run on the pages of DL items: costs, units, dates, places), one row per fact.

| column | type | null | values / unit | privacy | public copy | meaning |
|---|---|---|---|---|---|---|
| `run_id` | VARCHAR | yes |  | none | as is | The DL facts run. |
| `page_id` | VARCHAR | yes | e.g. `journalofproceed30sanfrich:1` | none | as is | The archive.org page (`<identifier>:<leafNum>`). `nopage:<seq>` where the source gives no page number (sfbos rows without one): the row is kept, page_url is NULL. |
| `page_url` | VARCHAR | yes | e.g. `https://archive.org/details/journalofproceed30sanfrich/page/n1` | none | as is | Opens the page: archive.org BookReader link built from the leaf map (audited against IA's scandata), or `<pdf url>#page=<n>` for a PDF. NULL only where page_id is `nopage:<seq>`. |
| `seq` | BIGINT | yes |  | none | as is | Row number inside the run. |
| `row_key` | VARCHAR | yes |  | none | as is | Stable row key. |
| `item_id` | VARCHAR | yes |  | none | as is | The archive.org item. |
| `doc_type` | VARCHAR | yes |  | none | as is | Document type of the item (plan, report, EIR ...). |
| `item_title` | VARCHAR | yes |  | catalogue | as is | The item's catalogue title. |
| `item_year` | BIGINT | yes |  | none | as is | The year of the item. |
| `kind` | VARCHAR | yes |  | none | as is | Kind of fact. |
| `subject` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | What the fact is about, as read. |
| `property` | VARCHAR | yes |  | none | as is | The property read (cost, units, built ...). |
| `value_text` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | The value as read. |
| `value_num` | DOUBLE | yes |  | none | as is | The value as a number. |
| `unit` | VARCHAR | yes |  | none | as is | Unit of value_num (USD, units, acres ...). |
| `date` | VARCHAR | yes |  | none | as is | A date printed with the fact. |
| `quote` | VARCHAR | yes |  | raw_text | names of private people withheld under the embargo | Short quote of the page (raw text; names of private people withheld under the embargo). |
| `place_text` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | The place as printed. |
| `place_number` | VARCHAR | yes |  | none | as is | House number. |
| `place_street` | VARCHAR | yes |  | none | as is | Street. |
| `place_cross_street` | VARCHAR | yes |  | none | as is | Cross street. |
| `place_block` | VARCHAR | yes |  | none | as is | Assessor block. |
| `place_lot` | VARCHAR | yes |  | none | as is | Assessor lot. |
| `place_area` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | A named area. |
| `place_precision` | VARCHAR | yes |  | none | as is | How exactly the page places it. |
| `place_id` | VARCHAR | yes |  | none | as is | The qipu place it was resolved to. |
| `resolved_by` | VARCHAR | yes |  | none | as is | How it was resolved. |
| `value_year` | INTEGER | yes |  | none | as is | The year the amount is in (for amount_2026). |
| `amount_2026` | DOUBLE | yes |  | none | as is | value_num in 2026 dollars (CPI). |
| `index_used` | VARCHAR | yes |  | none | as is | The price index and years behind amount_2026. |

## `x_dl_tags` (1,131 rows)

Extraction table: Democracy's Library (IGS) items about SF, with the neighbourhoods, streets and places each is about (claude-haiku-5-5 on metadata + first OCR, dl_tag.py).

| column | type | null | values / unit | privacy | public copy | meaning |
|---|---|---|---|---|---|---|
| `about_sf` | BOOLEAN | yes |  | none | as is | True when the document is about San Francisco, chosen by a model from the catalogue record and the start of the OCR. |
| `doc_type` | VARCHAR | yes | `annual report`, `audit`, `city-owned property report`, `environmental impact report`, `minutes or proceedings`, `ordinance or legislation`, `other`, `plan`, `study or report` | none | as is | Document type (`study or report`, `plan`, `environmental impact report`, `budget`, ...), chosen by a model from the catalogue record and the start of the OCR. |
| `scope` | VARCHAR | yes | `citywide`, `neighbourhoods`, `not San Francisco`, `regional`, `site` | none | as is | `citywide`, `neighbourhoods`, `site`, `regional` or `not San Francisco`, chosen by a model from the catalogue record and the start of the OCR. |
| `hoods` | VARCHAR[] | yes |  | none | as is | Neighbourhoods (from the map's fixed list) the document is about, chosen by a model from the catalogue record and the start of the OCR. |
| `streets` | VARCHAR[] | yes |  | none | as is | Streets the document is about, chosen by a model from the catalogue record and the start of the OCR. |
| `places` | VARCHAR[] | yes |  | none | as is | Named sites or facilities the document is about (at most 4), chosen by a model from the catalogue record and the start of the OCR. |
| `evidence` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | Short quote (<= 160 characters) from the title or text showing what the document is about, picked by a model. |
| `identifier` | VARCHAR | yes |  | none | as is | archive.org identifier of the document. |
| `run_id` | VARCHAR | no | `dl-tags-haiku55-2026-10` | none | as is | `dl-tags-haiku55-2026-10` (see `runs`). |
| `seq` | BIGINT | yes |  | none | as is | Line number of the row in the run's tags file. |

## `x_enrich`

Extraction table (enrichment v1.1, `sf-enrich-v11-haiku55-2026-10`): one row per Board decision read again by claude-haiku-5-5 for what decisions v2 did not carry (project, status, reason, emergency, sponsors, stages, zoning, other votes). The lists (money, land, permits, claims ...) are in the `x_enrich_<field>` tables, keyed by `decision_id`. Leaves of absence, memorials, condolences and commendations were not sent (counted in `runs`).

| column | type | null | values / unit | privacy | public copy | meaning |
|---|---|---|---|---|---|---|
| `decision_id` | VARCHAR | no | e.g. `qipu:sf/decision/journalofproceed1917sanfrich:1/1` | none | as is | The decision read (see `decisions`). |
| `page_id` | VARCHAR | no | e.g. `journalofproceed1917sanfrich:1` | none | as is | The page: `<archive.org identifier>:<leaf>`. |
| `page_url` | VARCHAR | yes | e.g. `https://archive.org/details/journalofproceed1917sanfrich/page/n1` | none | as is | Opens the page on archive.org. |
| `source_id` | VARCHAR | no |  | none | as is | The archive.org item. |
| `year` | BIGINT | yes | 1850..2030 | none | as is | The year of the decision (the privacy rule counts from it). |
| `run_id` | VARCHAR | no | `sf-enrich-v11-haiku55-2026-10` | none | as is | The run that read the decision (see `runs`). |
| `kind` | VARCHAR | yes |  | none | as is | The decision kind from decisions v2. |
| `project_name` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | The project the decision is part of, as named. |
| `project_role` | VARCHAR | yes | `proposal`, `study_or_plan`, `funding`, `land`, `design`, `contract`, `construction`, `completion`, `operation`, `cancellation`, `other` | none | as is | What the decision does for the project (closed list `project role`). |
| `project_use` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | What the project is for. |
| `project_quote` | VARCHAR | yes |  | raw_text | left out for records under the embargo | The words that name the project; empty for records after 1951. |
| `status` | VARCHAR | yes | `proposed`, `approved`, `funded`, `under_construction`, `built`, `abandoned`, `rejected`, `not_applicable` | none | as is | The project status the decision states. |
| `reason` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | The stated reason, in plain words. |
| `reason_quote` | VARCHAR | yes |  | raw_text | left out for records under the embargo | The words that state the reason; empty for records after 1951. |
| `emergency` | BOOLEAN | yes |  | none | as is | True when the decision is declared an emergency. |
| `sponsors` | VARCHAR[] | yes |  | public_official | as is | Supervisors who introduced the item. |
| `committee` | VARCHAR | yes |  | catalogue | as is | The committee that reported it. |
| `effective_date` | VARCHAR | yes |  | none | as is | The date it takes effect, as read. |
| `stages` | VARCHAR[] | yes |  | none | as is | Legislative stages read (closed list `stage`). |
| `bounded_by` | VARCHAR[] | yes |  | none | as is | The streets that bound the area, as read. |
| `zoning_height_from` | VARCHAR | yes |  | none | as is | Zoning: height from, as read. |
| `zoning_height_to` | VARCHAR | yes |  | none | as is | Zoning: height to, as read. |
| `zoning_conditional_use` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | Zoning: conditional use, as read. |
| `zoning_appeal_of_body` | VARCHAR | yes |  | none | as is | Zoning: appeal of body, as read. |
| `zoning_appeal_of_number` | VARCHAR | yes |  | none | as is | Zoning: appeal of number, as read. |
| `zoning_appeal_of_decision` | VARCHAR | yes |  | none | as is | Zoning: appeal of decision, as read. |
| `zoning_proposed_use` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | Zoning: proposed use, as read. |
| `excused_names` | VARCHAR[] | yes |  | public_official | as is | Supervisors excused from voting. |
| `committee_dissent_names` | VARCHAR[] | yes |  | public_official | as is | Committee members who dissented. |
| `other_votes` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | Other votes on the item (JSON text: motion, result, ayes, noes). |
| `empty` | BOOLEAN | yes |  | none | as is | True when the model found nothing to add for the decision. |

## `x_enrich_agreements`

Extraction table (enrichment v1.1, `sf-enrich-v11-haiku55-2026-10`): one contract, lease, concession, franchise or other agreement, with its counterparty and amount. One row per item read from one Board decision by claude-haiku-5-5 (decisions v2 rows, 1906-2016). Unverified until checked: see `field_scores` and `claim_reliability`.

| column | type | null | values / unit | privacy | public copy | meaning |
|---|---|---|---|---|---|---|
| `decision_id` | VARCHAR | no | e.g. `qipu:sf/decision/journalofproceed1917sanfrich:1/1` | none | as is | The decision read (see `decisions`). |
| `page_id` | VARCHAR | no | e.g. `journalofproceed1917sanfrich:1` | none | as is | The page: `<archive.org identifier>:<leaf>`. |
| `page_url` | VARCHAR | yes | e.g. `https://archive.org/details/journalofproceed1917sanfrich/page/n1` | none | as is | Opens the page on archive.org. |
| `source_id` | VARCHAR | no |  | none | as is | The archive.org item. |
| `year` | BIGINT | yes | 1850..2030 | none | as is | The year of the decision (the privacy rule counts from it). |
| `run_id` | VARCHAR | no | `sf-enrich-v11-haiku55-2026-10` | none | as is | The run that read the decision (see `runs`). |
| `item_n` | BIGINT | no |  | none | as is | The item's place in the decision's list (0, 1 ...). |
| `kind` | VARCHAR | yes | `contract`, `lease`, `concession`, `franchise`, `labour_mou`, `joint_agreement`, `easement`, `grant_agreement`, `other` | none | as is | Kind (closed list `agreement kind`; an out-of-list reading is `other`, its text in `kind_raw`). |
| `counterparty` | VARCHAR | yes |  | person_name | shown for officials and organisations; a private person is withheld under the embargo | The counterparty as printed. Organisations and governments are shown; a private person is withheld for 75 years after the record (the row's `privacy_class` and `public_after_year`). |
| `counterparty_kind` | VARCHAR | yes | `person`, `organisation`, `government`, `unknown` | none | as is | Counterparty kind (closed list `party kind`; an out-of-list reading is `other`, its text in `counterparty_kind_raw`). |
| `subject` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | Subject, as read (private names of records under 75 years are withheld). |
| `term_start` | VARCHAR | yes |  | none | as is | Term start, as read. |
| `term_end` | VARCHAR | yes |  | none | as is | Term end, as read. |
| `amount` | DOUBLE | yes |  | none | as is | Amount, as a number. |
| `amount_basis` | VARCHAR | yes | `total`, `per_year`, `per_month`, `pct_gross`, `not_to_exceed` | none | as is | Amount basis (closed list `amount basis`; an out-of-list reading is `other`, its text in `amount_basis_raw`). |
| `minimum` | DOUBLE | yes |  | none | as is | Minimum, as a number. |
| `payment_stage` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | Payment stage, as read (private names of records under 75 years are withheld). |
| `kind_raw` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | The model's own word for `kind` when it was not on the list. |
| `counterparty_kind_raw` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | The model's own word for `counterparty_kind` when it was not on the list. |
| `amount_basis_raw` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | The model's own word for `amount_basis` when it was not on the list. |
| `quote` | VARCHAR | yes |  | raw_text | left out for records under the embargo | The words on the page that support the item (<= 300 characters); empty for records after 1951. |
| `privacy_class` | VARCHAR | yes | `none`, `organisation`, `public_official`, `private_person`, `unknown` | none | as is | Who the item names: `private_person`, `organisation`, `public_official`, `unknown` (a payee) or `none`. |
| `public_after_year` | DOUBLE | yes |  | none | as is | For `private_person` items: the year the name becomes public (year + 75). |
| `quote_found` | BOOLEAN | yes |  | none | as is | True when the quote was found in the page text (OCR); empty when the quote is too short to test. |

## `x_enrich_appointments`

Extraction table (enrichment v1.1, `sf-enrich-v11-haiku55-2026-10`): one appointment to a public body or office (officials, named in their role). One row per item read from one Board decision by claude-haiku-5-5 (decisions v2 rows, 1906-2016). Unverified until checked: see `field_scores` and `claim_reliability`.

| column | type | null | values / unit | privacy | public copy | meaning |
|---|---|---|---|---|---|---|
| `decision_id` | VARCHAR | no | e.g. `qipu:sf/decision/journalofproceed1917sanfrich:1/1` | none | as is | The decision read (see `decisions`). |
| `page_id` | VARCHAR | no | e.g. `journalofproceed1917sanfrich:1` | none | as is | The page: `<archive.org identifier>:<leaf>`. |
| `page_url` | VARCHAR | yes | e.g. `https://archive.org/details/journalofproceed1917sanfrich/page/n1` | none | as is | Opens the page on archive.org. |
| `source_id` | VARCHAR | no |  | none | as is | The archive.org item. |
| `year` | BIGINT | yes | 1850..2030 | none | as is | The year of the decision (the privacy rule counts from it). |
| `run_id` | VARCHAR | no | `sf-enrich-v11-haiku55-2026-10` | none | as is | The run that read the decision (see `runs`). |
| `item_n` | BIGINT | no |  | none | as is | The item's place in the decision's list (0, 1 ...). |
| `person` | VARCHAR | yes |  | public_official | as is | The person as printed: appointments to a public body or office are officials, named in the role (Daniel 14/10). |
| `body` | VARCHAR | yes |  | catalogue | as is | Body, as read. |
| `position` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | Position, as read (private names of records under 75 years are withheld). |
| `seat` | VARCHAR | yes |  | none | as is | Seat, as read. |
| `appointed_by` | VARCHAR | yes |  | public_official | as is | The appointed by as printed: appointments to a public body or office are officials, named in the role (Daniel 14/10). |
| `term_start` | VARCHAR | yes |  | none | as is | Term start, as read. |
| `term_end` | VARCHAR | yes |  | none | as is | Term end, as read. |
| `replaces` | VARCHAR | yes |  | public_official | as is | The replaces as printed: appointments to a public body or office are officials, named in the role (Daniel 14/10). |
| `vacancy_reason` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | Vacancy reason, as read (private names of records under 75 years are withheld). |
| `quote` | VARCHAR | yes |  | raw_text | left out for records under the embargo | The words on the page that support the item (<= 300 characters); empty for records after 1951. |
| `privacy_class` | VARCHAR | yes | `none`, `organisation`, `public_official`, `private_person`, `unknown` | none | as is | Who the item names: `private_person`, `organisation`, `public_official`, `unknown` (a payee) or `none`. |
| `public_after_year` | DOUBLE | yes |  | none | as is | For `private_person` items: the year the name becomes public (year + 75). |
| `quote_found` | BOOLEAN | yes |  | none | as is | True when the quote was found in the page text (OCR); empty when the quote is too short to test. |

## `x_enrich_assessments`

Extraction table (enrichment v1.1, `sf-enrich-v11-haiku55-2026-10`): one assessment levied, cancelled, refunded or reduced on a block and lot. One row per item read from one Board decision by claude-haiku-5-5 (decisions v2 rows, 1906-2016). Unverified until checked: see `field_scores` and `claim_reliability`.

| column | type | null | values / unit | privacy | public copy | meaning |
|---|---|---|---|---|---|---|
| `decision_id` | VARCHAR | no | e.g. `qipu:sf/decision/journalofproceed1917sanfrich:1/1` | none | as is | The decision read (see `decisions`). |
| `page_id` | VARCHAR | no | e.g. `journalofproceed1917sanfrich:1` | none | as is | The page: `<archive.org identifier>:<leaf>`. |
| `page_url` | VARCHAR | yes | e.g. `https://archive.org/details/journalofproceed1917sanfrich/page/n1` | none | as is | Opens the page on archive.org. |
| `source_id` | VARCHAR | no |  | none | as is | The archive.org item. |
| `year` | BIGINT | yes | 1850..2030 | none | as is | The year of the decision (the privacy rule counts from it). |
| `run_id` | VARCHAR | no | `sf-enrich-v11-haiku55-2026-10` | none | as is | The run that read the decision (see `runs`). |
| `item_n` | BIGINT | no |  | none | as is | The item's place in the decision's list (0, 1 ...). |
| `block` | VARCHAR | yes |  | catalogue | as is | Block, as read. |
| `lot` | VARCHAR | yes |  | catalogue | as is | Lot, as read. |
| `owner` | VARCHAR | yes |  | person_name | shown for officials and organisations; a private person is withheld under the embargo | The owner as printed. Organisations and governments are shown; a private person is withheld for 75 years after the record (the row's `privacy_class` and `public_after_year`). |
| `owner_kind` | VARCHAR | yes | `person`, `organisation`, `government`, `unknown` | none | as is | Owner kind (closed list `party kind`; an out-of-list reading is `other`, its text in `owner_kind_raw`). |
| `land_value` | DOUBLE | yes |  | none | as is | Land value, as a number. |
| `improvement_value` | DOUBLE | yes |  | none | as is | Improvement value, as a number. |
| `fiscal_year` | VARCHAR | yes |  | none | as is | Fiscal year, as read. |
| `action` | VARCHAR | yes | `levy`, `cancel`, `refund`, `reduce`, `confirm`, `deny` | none | as is | Action (closed list `assessment action`; an out-of-list reading is `other`, its text in `action_raw`). |
| `reason` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | Reason, as read (private names of records under 75 years are withheld). |
| `owner_kind_raw` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | The model's own word for `owner_kind` when it was not on the list. |
| `action_raw` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | The model's own word for `action` when it was not on the list. |
| `quote` | VARCHAR | yes |  | raw_text | left out for records under the embargo | The words on the page that support the item (<= 300 characters); empty for records after 1951. |
| `privacy_class` | VARCHAR | yes | `none`, `organisation`, `public_official`, `private_person`, `unknown` | none | as is | Who the item names: `private_person`, `organisation`, `public_official`, `unknown` (a payee) or `none`. |
| `public_after_year` | DOUBLE | yes |  | none | as is | For `private_person` items: the year the name becomes public (year + 75). |
| `quote_found` | BOOLEAN | yes |  | none | as is | True when the quote was found in the page text (OCR); empty when the quote is too short to test. |

## `x_enrich_board_positions`

Extraction table (enrichment v1.1, `sf-enrich-v11-haiku55-2026-10`): one position the Board took on a bill, ballot measure or other agency's action. One row per item read from one Board decision by claude-haiku-5-5 (decisions v2 rows, 1906-2016). Unverified until checked: see `field_scores` and `claim_reliability`.

| column | type | null | values / unit | privacy | public copy | meaning |
|---|---|---|---|---|---|---|
| `decision_id` | VARCHAR | no | e.g. `qipu:sf/decision/journalofproceed1917sanfrich:1/1` | none | as is | The decision read (see `decisions`). |
| `page_id` | VARCHAR | no | e.g. `journalofproceed1917sanfrich:1` | none | as is | The page: `<archive.org identifier>:<leaf>`. |
| `page_url` | VARCHAR | yes | e.g. `https://archive.org/details/journalofproceed1917sanfrich/page/n1` | none | as is | Opens the page on archive.org. |
| `source_id` | VARCHAR | no |  | none | as is | The archive.org item. |
| `year` | BIGINT | yes | 1850..2030 | none | as is | The year of the decision (the privacy rule counts from it). |
| `run_id` | VARCHAR | no | `sf-enrich-v11-haiku55-2026-10` | none | as is | The run that read the decision (see `runs`). |
| `item_n` | BIGINT | no |  | none | as is | The item's place in the decision's list (0, 1 ...). |
| `target_kind` | VARCHAR | yes | `state_bill`, `federal_bill`, `ballot_measure`, `agency_action`, `other` | none | as is | Target kind (closed list `board target`; an out-of-list reading is `other`, its text in `target_kind_raw`). |
| `target` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | Target, as read (private names of records under 75 years are withheld). |
| `stance` | VARCHAR | yes | `support`, `oppose`, `urge`, `request`, `ballot_argument_for`, `ballot_argument_against` | none | as is | Stance (closed list `board stance`; an out-of-list reading is `other`, its text in `stance_raw`). |
| `target_kind_raw` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | The model's own word for `target_kind` when it was not on the list. |
| `stance_raw` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | The model's own word for `stance` when it was not on the list. |
| `quote` | VARCHAR | yes |  | raw_text | left out for records under the embargo | The words on the page that support the item (<= 300 characters); empty for records after 1951. |
| `privacy_class` | VARCHAR | yes | `none`, `organisation`, `public_official`, `private_person`, `unknown` | none | as is | Who the item names: `private_person`, `organisation`, `public_official`, `unknown` (a payee) or `none`. |
| `public_after_year` | DOUBLE | yes |  | none | as is | For `private_person` items: the year the name becomes public (year + 75). |
| `quote_found` | BOOLEAN | yes |  | none | as is | True when the quote was found in the page text (OCR); empty when the quote is too short to test. |

## `x_enrich_citations`

Extraction table (enrichment v1.1, `sf-enrich-v11-haiku55-2026-10`): one other law, report or case the decision cites, and how it relates (amends, repeals, pursuant to ...). One row per item read from one Board decision by claude-haiku-5-5 (decisions v2 rows, 1906-2016). Unverified until checked: see `field_scores` and `claim_reliability`.

| column | type | null | values / unit | privacy | public copy | meaning |
|---|---|---|---|---|---|---|
| `decision_id` | VARCHAR | no | e.g. `qipu:sf/decision/journalofproceed1917sanfrich:1/1` | none | as is | The decision read (see `decisions`). |
| `page_id` | VARCHAR | no | e.g. `journalofproceed1917sanfrich:1` | none | as is | The page: `<archive.org identifier>:<leaf>`. |
| `page_url` | VARCHAR | yes | e.g. `https://archive.org/details/journalofproceed1917sanfrich/page/n1` | none | as is | Opens the page on archive.org. |
| `source_id` | VARCHAR | no |  | none | as is | The archive.org item. |
| `year` | VARCHAR | yes |  | none | as is | Year, as read. |
| `run_id` | VARCHAR | no | `sf-enrich-v11-haiku55-2026-10` | none | as is | The run that read the decision (see `runs`). |
| `item_n` | BIGINT | no |  | none | as is | The item's place in the decision's list (0, 1 ...). |
| `kind` | VARCHAR | yes | `resolution`, `ordinance`, `bill`, `motion`, `file`, `bond_issue`, `charter_section`, `report`, `eir`, `contract`, `proposition`, `planning_commission_resolution` ... | none | as is | Kind (closed list `citation kind`; an out-of-list reading is `other`, its text in `kind_raw`). |
| `number` | VARCHAR | yes |  | catalogue | as is | Number, as read. |
| `text` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | Text, as read (private names of records under 75 years are withheld). |
| `relation` | VARCHAR | yes | `amends`, `repeals`, `pursuant_to`, `funded_by`, `supersedes`, `rescinds`, `terminates`, `adopts_or_accepts`, `refers_to` | none | as is | Relation (closed list `relation`; an out-of-list reading is `other`, its text in `relation_raw`). |
| `kind_raw` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | The model's own word for `kind` when it was not on the list. |
| `relation_raw` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | The model's own word for `relation` when it was not on the list. |
| `quote` | VARCHAR | yes |  | raw_text | left out for records under the embargo | The words on the page that support the item (<= 300 characters); empty for records after 1951. |
| `privacy_class` | VARCHAR | yes | `none`, `organisation`, `public_official`, `private_person`, `unknown` | none | as is | Who the item names: `private_person`, `organisation`, `public_official`, `unknown` (a payee) or `none`. |
| `public_after_year` | DOUBLE | yes |  | none | as is | For `private_person` items: the year the name becomes public (year + 75). |
| `quote_found` | BOOLEAN | yes |  | none | as is | True when the quote was found in the page text (OCR); empty when the quote is too short to test. |

## `x_enrich_claims`

Extraction table (enrichment v1.1, `sf-enrich-v11-haiku55-2026-10`): one claim for or against the city (who, why as a category, amounts claimed and paid). One row per item read from one Board decision by claude-haiku-5-5 (decisions v2 rows, 1906-2016). Unverified until checked: see `field_scores` and `claim_reliability`.

| column | type | null | values / unit | privacy | public copy | meaning |
|---|---|---|---|---|---|---|
| `decision_id` | VARCHAR | no | e.g. `qipu:sf/decision/journalofproceed1917sanfrich:1/1` | none | as is | The decision read (see `decisions`). |
| `page_id` | VARCHAR | no | e.g. `journalofproceed1917sanfrich:1` | none | as is | The page: `<archive.org identifier>:<leaf>`. |
| `page_url` | VARCHAR | yes | e.g. `https://archive.org/details/journalofproceed1917sanfrich/page/n1` | none | as is | Opens the page on archive.org. |
| `source_id` | VARCHAR | no |  | none | as is | The archive.org item. |
| `year` | BIGINT | yes | 1850..2030 | none | as is | The year of the decision (the privacy rule counts from it). |
| `run_id` | VARCHAR | no | `sf-enrich-v11-haiku55-2026-10` | none | as is | The run that read the decision (see `runs`). |
| `item_n` | BIGINT | no |  | none | as is | The item's place in the decision's list (0, 1 ...). |
| `claimant` | VARCHAR | yes |  | person_name | shown for officials and organisations; a private person is withheld under the embargo | The claimant as printed. Organisations and governments are shown; a private person is withheld for 75 years after the record (the row's `privacy_class` and `public_after_year`). |
| `claimant_kind` | VARCHAR | yes | `person`, `organisation`, `government`, `unknown` | none | as is | Claimant kind (closed list `party kind`; an out-of-list reading is `other`, its text in `claimant_kind_raw`). |
| `direction` | VARCHAR | yes | `city_pays`, `city_receives` | none | as is | Direction (closed list `claim direction`; an out-of-list reading is `other`, its text in `direction_raw`). |
| `cause` | VARCHAR | yes | `sidewalk_or_street_defect`, `vehicle_collision`, `transit_accident`, `other_personal_injury`, `property_damage`, `public_works_damage`, `tax_refund`, `fee_or_deposit_refund`, `contract_dispute`, `wages_or_back_pay`, `police_or_jail`, `land_or_title` ... | none | as is | Cause (closed list `claim cause`; an out-of-list reading is `other`, its text in `cause_raw`). |
| `court` | VARCHAR | yes |  | catalogue | as is | Court, as read. |
| `case_number` | VARCHAR | yes |  | catalogue | as is | Case number, as read. |
| `incident_date` | VARCHAR | yes |  | none | as is | Incident date, as read. |
| `amount_claimed` | DOUBLE | yes |  | none | as is | Amount claimed, as a number. |
| `amount_paid` | DOUBLE | yes |  | none | as is | Amount paid, as a number. |
| `claimant_kind_raw` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | The model's own word for `claimant_kind` when it was not on the list. |
| `direction_raw` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | The model's own word for `direction` when it was not on the list. |
| `cause_raw` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | The model's own word for `cause` when it was not on the list. |
| `quote` | VARCHAR | yes |  | raw_text | left out for records under the embargo | The words on the page that support the item (<= 300 characters); empty for records after 1951. |
| `privacy_class` | VARCHAR | yes | `none`, `organisation`, `public_official`, `private_person`, `unknown` | none | as is | Who the item names: `private_person`, `organisation`, `public_official`, `unknown` (a payee) or `none`. |
| `public_after_year` | DOUBLE | yes |  | none | as is | For `private_person` items: the year the name becomes public (year + 75). |
| `quote_found` | BOOLEAN | yes |  | none | as is | True when the quote was found in the page text (OCR); empty when the quote is too short to test. |

## `x_enrich_conditions`

Extraction table (enrichment v1.1, `sf-enrich-v11-haiku55-2026-10`): one condition attached to a decision (bond, hours, time limit ...). One row per item read from one Board decision by claude-haiku-5-5 (decisions v2 rows, 1906-2016). Unverified until checked: see `field_scores` and `claim_reliability`.

| column | type | null | values / unit | privacy | public copy | meaning |
|---|---|---|---|---|---|---|
| `decision_id` | VARCHAR | no | e.g. `qipu:sf/decision/journalofproceed1917sanfrich:1/1` | none | as is | The decision read (see `decisions`). |
| `page_id` | VARCHAR | no | e.g. `journalofproceed1917sanfrich:1` | none | as is | The page: `<archive.org identifier>:<leaf>`. |
| `page_url` | VARCHAR | yes | e.g. `https://archive.org/details/journalofproceed1917sanfrich/page/n1` | none | as is | Opens the page on archive.org. |
| `source_id` | VARCHAR | no |  | none | as is | The archive.org item. |
| `year` | BIGINT | yes | 1850..2030 | none | as is | The year of the decision (the privacy rule counts from it). |
| `run_id` | VARCHAR | no | `sf-enrich-v11-haiku55-2026-10` | none | as is | The run that read the decision (see `runs`). |
| `item_n` | BIGINT | no |  | none | as is | The item's place in the decision's list (0, 1 ...). |
| `type` | VARCHAR | yes | `bond`, `hours`, `time_limit`, `supervision`, `use_restriction`, `payment`, `title`, `demolition`, `other` | none | as is | Type (closed list `condition type`; an out-of-list reading is `other`, its text in `type_raw`). |
| `text` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | Text, as read (private names of records under 75 years are withheld). |
| `amount` | DOUBLE | yes |  | none | as is | Amount, as a number. |
| `until` | VARCHAR | yes |  | none | as is | Until, as read. |
| `type_raw` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | The model's own word for `type` when it was not on the list. |
| `quote` | VARCHAR | yes |  | raw_text | left out for records under the embargo | The words on the page that support the item (<= 300 characters); empty for records after 1951. |
| `privacy_class` | VARCHAR | yes | `none`, `organisation`, `public_official`, `private_person`, `unknown` | none | as is | Who the item names: `private_person`, `organisation`, `public_official`, `unknown` (a payee) or `none`. |
| `public_after_year` | DOUBLE | yes |  | none | as is | For `private_person` items: the year the name becomes public (year + 75). |
| `quote_found` | BOOLEAN | yes |  | none | as is | True when the quote was found in the page text (OCR); empty when the quote is too short to test. |

## `x_enrich_districts`

Extraction table (enrichment v1.1, `sf-enrich-v11-haiku55-2026-10`): one district created, extended, amended or repealed (assessment, special use, underground ...). One row per item read from one Board decision by claude-haiku-5-5 (decisions v2 rows, 1906-2016). Unverified until checked: see `field_scores` and `claim_reliability`.

| column | type | null | values / unit | privacy | public copy | meaning |
|---|---|---|---|---|---|---|
| `decision_id` | VARCHAR | no | e.g. `qipu:sf/decision/journalofproceed1917sanfrich:1/1` | none | as is | The decision read (see `decisions`). |
| `page_id` | VARCHAR | no | e.g. `journalofproceed1917sanfrich:1` | none | as is | The page: `<archive.org identifier>:<leaf>`. |
| `page_url` | VARCHAR | yes | e.g. `https://archive.org/details/journalofproceed1917sanfrich/page/n1` | none | as is | Opens the page on archive.org. |
| `source_id` | VARCHAR | no |  | none | as is | The archive.org item. |
| `year` | BIGINT | yes | 1850..2030 | none | as is | The year of the decision (the privacy rule counts from it). |
| `run_id` | VARCHAR | no | `sf-enrich-v11-haiku55-2026-10` | none | as is | The run that read the decision (see `runs`). |
| `item_n` | BIGINT | no |  | none | as is | The item's place in the decision's list (0, 1 ...). |
| `kind` | VARCHAR | yes | `underground_district`, `special_use_district`, `interim_controls`, `moratorium`, `permit_parking_area`, `redevelopment_area`, `assessment_district`, `fire_limits`, `historic_district`, `zoning_district`, `business_improvement_district`, `other` | none | as is | Kind (closed list `district kind`; an out-of-list reading is `other`, its text in `kind_raw`). |
| `name_or_number` | VARCHAR | yes |  | catalogue | as is | Name or number, as read. |
| `action` | VARCHAR | yes | `create`, `extend`, `amend`, `repeal` | none | as is | Action (closed list `district action`; an out-of-list reading is `other`, its text in `action_raw`). |
| `until` | VARCHAR | yes |  | none | as is | Until, as read. |
| `kind_raw` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | The model's own word for `kind` when it was not on the list. |
| `action_raw` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | The model's own word for `action` when it was not on the list. |
| `quote` | VARCHAR | yes |  | raw_text | left out for records under the embargo | The words on the page that support the item (<= 300 characters); empty for records after 1951. |
| `privacy_class` | VARCHAR | yes | `none`, `organisation`, `public_official`, `private_person`, `unknown` | none | as is | Who the item names: `private_person`, `organisation`, `public_official`, `unknown` (a payee) or `none`. |
| `public_after_year` | DOUBLE | yes |  | none | as is | For `private_person` items: the year the name becomes public (year + 75). |
| `quote_found` | BOOLEAN | yes |  | none | as is | True when the quote was found in the page text (OCR); empty when the quote is too short to test. |

## `x_enrich_institution_events`

Extraction table (enrichment v1.1, `sf-enrich-v11-haiku55-2026-10`): one event of a school, hospital, firehouse, park, library ... (opened, closed, moved, built ...). One row per item read from one Board decision by claude-haiku-5-5 (decisions v2 rows, 1906-2016). Unverified until checked: see `field_scores` and `claim_reliability`.

| column | type | null | values / unit | privacy | public copy | meaning |
|---|---|---|---|---|---|---|
| `decision_id` | VARCHAR | no | e.g. `qipu:sf/decision/journalofproceed1917sanfrich:1/1` | none | as is | The decision read (see `decisions`). |
| `page_id` | VARCHAR | no | e.g. `journalofproceed1917sanfrich:1` | none | as is | The page: `<archive.org identifier>:<leaf>`. |
| `page_url` | VARCHAR | yes | e.g. `https://archive.org/details/journalofproceed1917sanfrich/page/n1` | none | as is | Opens the page on archive.org. |
| `source_id` | VARCHAR | no |  | none | as is | The archive.org item. |
| `year` | BIGINT | yes | 1850..2030 | none | as is | The year of the decision (the privacy rule counts from it). |
| `run_id` | VARCHAR | no | `sf-enrich-v11-haiku55-2026-10` | none | as is | The run that read the decision (see `runs`). |
| `item_n` | BIGINT | no |  | none | as is | The item's place in the decision's list (0, 1 ...). |
| `institution` | VARCHAR | yes |  | catalogue | as is | Institution, as read. |
| `type` | VARCHAR | yes | `school`, `hospital`, `firehouse`, `police_station`, `park_or_playground`, `library`, `museum_or_hall`, `jail`, `other` | none | as is | Type (closed list `institution type`; an out-of-list reading is `other`, its text in `type_raw`). |
| `event` | VARCHAR | yes | `opened`, `closed`, `moved`, `renamed`, `expanded`, `built`, `acquired`, `demolished`, `other` | none | as is | Event (closed list `institution event`; an out-of-list reading is `other`, its text in `event_raw`). |
| `place_text` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | Place text, as read (private names of records under 75 years are withheld). |
| `type_raw` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | The model's own word for `type` when it was not on the list. |
| `event_raw` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | The model's own word for `event` when it was not on the list. |
| `quote` | VARCHAR | yes |  | raw_text | left out for records under the embargo | The words on the page that support the item (<= 300 characters); empty for records after 1951. |
| `privacy_class` | VARCHAR | yes | `none`, `organisation`, `public_official`, `private_person`, `unknown` | none | as is | Who the item names: `private_person`, `organisation`, `public_official`, `unknown` (a payee) or `none`. |
| `public_after_year` | DOUBLE | yes |  | none | as is | For `private_person` items: the year the name becomes public (year + 75). |
| `quote_found` | BOOLEAN | yes |  | none | as is | True when the quote was found in the page text (OCR); empty when the quote is too short to test. |

## `x_enrich_land`

Extraction table (enrichment v1.1, `sf-enrich-v11-haiku55-2026-10`): one land transaction (purchase, sale, lease, easement, condemnation ...) with its counterparty and price. One row per item read from one Board decision by claude-haiku-5-5 (decisions v2 rows, 1906-2016). Unverified until checked: see `field_scores` and `claim_reliability`.

| column | type | null | values / unit | privacy | public copy | meaning |
|---|---|---|---|---|---|---|
| `decision_id` | VARCHAR | no | e.g. `qipu:sf/decision/journalofproceed1917sanfrich:1/1` | none | as is | The decision read (see `decisions`). |
| `page_id` | VARCHAR | no | e.g. `journalofproceed1917sanfrich:1` | none | as is | The page: `<archive.org identifier>:<leaf>`. |
| `page_url` | VARCHAR | yes | e.g. `https://archive.org/details/journalofproceed1917sanfrich/page/n1` | none | as is | Opens the page on archive.org. |
| `source_id` | VARCHAR | no |  | none | as is | The archive.org item. |
| `year` | BIGINT | yes | 1850..2030 | none | as is | The year of the decision (the privacy rule counts from it). |
| `run_id` | VARCHAR | no | `sf-enrich-v11-haiku55-2026-10` | none | as is | The run that read the decision (see `runs`). |
| `item_n` | BIGINT | no |  | none | as is | The item's place in the decision's list (0, 1 ...). |
| `type` | VARCHAR | yes | `purchase`, `sale`, `lease`, `easement`, `condemnation`, `transfer`, `gift`, `exchange`, `release`, `other` | none | as is | Type (closed list `land type`; an out-of-list reading is `other`, its text in `type_raw`). |
| `place_text` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | Place text, as read (private names of records under 75 years are withheld). |
| `block` | VARCHAR | yes |  | catalogue | as is | Block, as read. |
| `lot` | VARCHAR | yes |  | catalogue | as is | Lot, as read. |
| `counterparty` | VARCHAR | yes |  | person_name | shown for officials and organisations; a private person is withheld under the embargo | The counterparty as printed. Organisations and governments are shown; a private person is withheld for 75 years after the record (the row's `privacy_class` and `public_after_year`). |
| `counterparty_kind` | VARCHAR | yes | `person`, `organisation`, `government`, `unknown` | none | as is | Counterparty kind (closed list `party kind`; an out-of-list reading is `other`, its text in `counterparty_kind_raw`). |
| `price` | DOUBLE | yes |  | none | as is | Price, as a number. |
| `appraised_value` | DOUBLE | yes |  | none | as is | Appraised value, as a number. |
| `area` | VARCHAR | yes |  | none | as is | Area, as read. |
| `purpose` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | Purpose, as read (private names of records under 75 years are withheld). |
| `type_raw` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | The model's own word for `type` when it was not on the list. |
| `counterparty_kind_raw` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | The model's own word for `counterparty_kind` when it was not on the list. |
| `quote` | VARCHAR | yes |  | raw_text | left out for records under the embargo | The words on the page that support the item (<= 300 characters); empty for records after 1951. |
| `privacy_class` | VARCHAR | yes | `none`, `organisation`, `public_official`, `private_person`, `unknown` | none | as is | Who the item names: `private_person`, `organisation`, `public_official`, `unknown` (a payee) or `none`. |
| `public_after_year` | DOUBLE | yes |  | none | as is | For `private_person` items: the year the name becomes public (year + 75). |
| `quote_found` | BOOLEAN | yes |  | none | as is | True when the quote was found in the page text (OCR); empty when the quote is too short to test. |

## `x_enrich_money`

Extraction table (enrichment v1.1, `sf-enrich-v11-haiku55-2026-10`): one amount of money the decision names, with its role (who pays whom, what for). One row per item read from one Board decision by claude-haiku-5-5 (decisions v2 rows, 1906-2016). Unverified until checked: see `field_scores` and `claim_reliability`.

| column | type | null | values / unit | privacy | public copy | meaning |
|---|---|---|---|---|---|---|
| `decision_id` | VARCHAR | no | e.g. `qipu:sf/decision/journalofproceed1917sanfrich:1/1` | none | as is | The decision read (see `decisions`). |
| `page_id` | VARCHAR | no | e.g. `journalofproceed1917sanfrich:1` | none | as is | The page: `<archive.org identifier>:<leaf>`. |
| `page_url` | VARCHAR | yes | e.g. `https://archive.org/details/journalofproceed1917sanfrich/page/n1` | none | as is | Opens the page on archive.org. |
| `source_id` | VARCHAR | no |  | none | as is | The archive.org item. |
| `year` | BIGINT | yes | 1850..2030 | none | as is | The year of the decision (the privacy rule counts from it). |
| `run_id` | VARCHAR | no | `sf-enrich-v11-haiku55-2026-10` | none | as is | The run that read the decision (see `runs`). |
| `item_n` | BIGINT | no |  | none | as is | The item's place in the decision's list (0, 1 ...). |
| `amount` | DOUBLE | yes |  | none | as is | Amount, as a number. |
| `role` | VARCHAR | yes | `appropriation`, `reserve`, `contribution_or_gift`, `repayment_or_refund`, `land_purchase`, `land_sale`, `bond_issue`, `contract_award`, `salary`, `total_project_cost`, `fine_or_fee`, `claim_settlement` ... | none | as is | Role (closed list `money role`; an out-of-list reading is `other`, its text in `role_raw`). |
| `payer` | VARCHAR | yes |  | person_name | shown for officials and organisations; a private person is withheld under the embargo | The payer as printed. Organisations and governments are shown; a private person is withheld for 75 years after the record (the row's `privacy_class` and `public_after_year`). |
| `payee` | VARCHAR | yes |  | person_name | shown for officials and organisations; a private person is withheld under the embargo | The payee as printed. Organisations and governments are shown; a private person is withheld for 75 years after the record (the row's `privacy_class` and `public_after_year`). |
| `fund` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | Fund, as read (private names of records under 75 years are withheld). |
| `fiscal_year` | VARCHAR | yes |  | none | as is | Fiscal year, as read. |
| `payment_stage` | VARCHAR | yes | `progress_n`, `final`, `advance`, `retroactive` | none | as is | Payment stage (closed list `payment stage`; an out-of-list reading is `other`, its text in `payment_stage_raw`). |
| `program` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | Program, as read (private names of records under 75 years are withheld). |
| `role_raw` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | The model's own word for `role` when it was not on the list. |
| `payment_stage_raw` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | The model's own word for `payment_stage` when it was not on the list. |
| `quote` | VARCHAR | yes |  | raw_text | left out for records under the embargo | The words on the page that support the item (<= 300 characters); empty for records after 1951. |
| `privacy_class` | VARCHAR | yes | `none`, `organisation`, `public_official`, `private_person`, `unknown` | none | as is | Who the item names: `private_person`, `organisation`, `public_official`, `unknown` (a payee) or `none`. |
| `public_after_year` | DOUBLE | yes |  | none | as is | For `private_person` items: the year the name becomes public (year + 75). |
| `quote_found` | BOOLEAN | yes |  | none | as is | True when the quote was found in the page text (OCR); empty when the quote is too short to test. |

## `x_enrich_organisations`

Extraction table (enrichment v1.1, `sf-enrich-v11-haiku55-2026-10`): one company, union, group or public body the decision names, and its role (bidder, grantee ...). One row per item read from one Board decision by claude-haiku-5-5 (decisions v2 rows, 1906-2016). Unverified until checked: see `field_scores` and `claim_reliability`.

| column | type | null | values / unit | privacy | public copy | meaning |
|---|---|---|---|---|---|---|
| `decision_id` | VARCHAR | no | e.g. `qipu:sf/decision/journalofproceed1917sanfrich:1/1` | none | as is | The decision read (see `decisions`). |
| `page_id` | VARCHAR | no | e.g. `journalofproceed1917sanfrich:1` | none | as is | The page: `<archive.org identifier>:<leaf>`. |
| `page_url` | VARCHAR | yes | e.g. `https://archive.org/details/journalofproceed1917sanfrich/page/n1` | none | as is | Opens the page on archive.org. |
| `source_id` | VARCHAR | no |  | none | as is | The archive.org item. |
| `year` | BIGINT | yes | 1850..2030 | none | as is | The year of the decision (the privacy rule counts from it). |
| `run_id` | VARCHAR | no | `sf-enrich-v11-haiku55-2026-10` | none | as is | The run that read the decision (see `runs`). |
| `item_n` | BIGINT | no |  | none | as is | The item's place in the decision's list (0, 1 ...). |
| `name` | VARCHAR | yes |  | person_name | shown for officials and organisations; a private person is withheld under the embargo | The name as printed. Organisations and governments are shown; a private person is withheld for 75 years after the record (the row's `privacy_class` and `public_after_year`). |
| `type` | VARCHAR | yes | `company`, `contractor`, `union`, `community_group`, `nonprofit`, `church`, `other_government`, `utility`, `bank_or_title_company`, `city_body`, `other` | none | as is | Type (closed list `org type`; an out-of-list reading is `other`, its text in `type_raw`). |
| `role` | VARCHAR | yes | `bidder`, `contract_awarded`, `petitioner`, `protester`, `applicant`, `grantee`, `seller`, `buyer`, `lessee`, `lessor`, `payee`, `payer` ... | none | as is | Role (closed list `org role`; an out-of-list reading is `other`, its text in `role_raw`). |
| `bid_amount` | DOUBLE | yes |  | none | as is | Bid amount, as a number. |
| `type_raw` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | The model's own word for `type` when it was not on the list. |
| `role_raw` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | The model's own word for `role` when it was not on the list. |
| `quote` | VARCHAR | yes |  | raw_text | left out for records under the embargo | The words on the page that support the item (<= 300 characters); empty for records after 1951. |
| `privacy_class` | VARCHAR | yes | `none`, `organisation`, `public_official`, `private_person`, `unknown` | none | as is | Who the item names: `private_person`, `organisation`, `public_official`, `unknown` (a payee) or `none`. |
| `public_after_year` | DOUBLE | yes |  | none | as is | For `private_person` items: the year the name becomes public (year + 75). |
| `quote_found` | BOOLEAN | yes |  | none | as is | True when the quote was found in the page text (OCR); empty when the quote is too short to test. |

## `x_enrich_permits`

Extraction table (enrichment v1.1, `sf-enrich-v11-haiku55-2026-10`): one permit granted, denied, revoked or transferred (garage, oil tank, liquor, sign ...). One row per item read from one Board decision by claude-haiku-5-5 (decisions v2 rows, 1906-2016). Unverified until checked: see `field_scores` and `claim_reliability`.

| column | type | null | values / unit | privacy | public copy | meaning |
|---|---|---|---|---|---|---|
| `decision_id` | VARCHAR | no | e.g. `qipu:sf/decision/journalofproceed1917sanfrich:1/1` | none | as is | The decision read (see `decisions`). |
| `page_id` | VARCHAR | no | e.g. `journalofproceed1917sanfrich:1` | none | as is | The page: `<archive.org identifier>:<leaf>`. |
| `page_url` | VARCHAR | yes | e.g. `https://archive.org/details/journalofproceed1917sanfrich/page/n1` | none | as is | Opens the page on archive.org. |
| `source_id` | VARCHAR | no |  | none | as is | The archive.org item. |
| `year` | BIGINT | yes | 1850..2030 | none | as is | The year of the decision (the privacy rule counts from it). |
| `run_id` | VARCHAR | no | `sf-enrich-v11-haiku55-2026-10` | none | as is | The run that read the decision (see `runs`). |
| `item_n` | BIGINT | no |  | none | as is | The item's place in the decision's list (0, 1 ...). |
| `permit_type` | VARCHAR | yes | `oil_tank`, `boiler_or_engine`, `stable`, `garage`, `laundry`, `spur_track`, `pipe_line`, `blasting`, `parking_station`, `taxicab`, `sidewalk_occupancy`, `liquor` ... | none | as is | Permit type (closed list `permit type`; an out-of-list reading is `other`, its text in `permit_type_raw`). |
| `decision` | VARCHAR | yes | `granted`, `denied`, `revoked`, `transferred`, `renewed`, `other` | none | as is | Decision (closed list `permit decision`; an out-of-list reading is `other`, its text in `decision_raw`). |
| `holder` | VARCHAR | yes |  | person_name | shown for officials and organisations; a private person is withheld under the embargo | The holder as printed. Organisations and governments are shown; a private person is withheld for 75 years after the record (the row's `privacy_class` and `public_after_year`). |
| `holder_kind` | VARCHAR | yes | `person`, `organisation`, `government`, `unknown` | none | as is | Holder kind (closed list `party kind`; an out-of-list reading is `other`, its text in `holder_kind_raw`). |
| `business_name` | VARCHAR | yes |  | person_name | shown for officials and organisations; a private person is withheld under the embargo | The business name as printed. Organisations and governments are shown; a private person is withheld for 75 years after the record (the row's `privacy_class` and `public_after_year`). |
| `place_text` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | Place text, as read (private names of records under 75 years are withheld). |
| `use` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | Use, as read (private names of records under 75 years are withheld). |
| `capacity` | DOUBLE | yes |  | none | as is | Capacity, as a number. |
| `capacity_unit` | VARCHAR | yes |  | none | as is | Capacity unit, as read. |
| `revocable` | BOOLEAN | yes |  | none | as is | True when the permit is revocable. |
| `from_holder` | VARCHAR | yes |  | person_name | shown for officials and organisations; a private person is withheld under the embargo | The from holder as printed. Organisations and governments are shown; a private person is withheld for 75 years after the record (the row's `privacy_class` and `public_after_year`). |
| `permit_type_raw` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | The model's own word for `permit_type` when it was not on the list. |
| `decision_raw` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | The model's own word for `decision` when it was not on the list. |
| `holder_kind_raw` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | The model's own word for `holder_kind` when it was not on the list. |
| `quote` | VARCHAR | yes |  | raw_text | left out for records under the embargo | The words on the page that support the item (<= 300 characters); empty for records after 1951. |
| `privacy_class` | VARCHAR | yes | `none`, `organisation`, `public_official`, `private_person`, `unknown` | none | as is | Who the item names: `private_person`, `organisation`, `public_official`, `unknown` (a payee) or `none`. |
| `public_after_year` | DOUBLE | yes |  | none | as is | For `private_person` items: the year the name becomes public (year + 75). |
| `quote_found` | BOOLEAN | yes |  | none | as is | True when the quote was found in the page text (OCR); empty when the quote is too short to test. |

## `x_enrich_place_events`

Extraction table (enrichment v1.1, `sf-enrich-v11-haiku55-2026-10`): one street or place event (opened, graded, paved, widened, renamed, vacated, landmark designated ...). One row per item read from one Board decision by claude-haiku-5-5 (decisions v2 rows, 1906-2016). Unverified until checked: see `field_scores` and `claim_reliability`.

| column | type | null | values / unit | privacy | public copy | meaning |
|---|---|---|---|---|---|---|
| `decision_id` | VARCHAR | no | e.g. `qipu:sf/decision/journalofproceed1917sanfrich:1/1` | none | as is | The decision read (see `decisions`). |
| `page_id` | VARCHAR | no | e.g. `journalofproceed1917sanfrich:1` | none | as is | The page: `<archive.org identifier>:<leaf>`. |
| `page_url` | VARCHAR | yes | e.g. `https://archive.org/details/journalofproceed1917sanfrich/page/n1` | none | as is | Opens the page on archive.org. |
| `source_id` | VARCHAR | no |  | none | as is | The archive.org item. |
| `year` | BIGINT | yes | 1850..2030 | none | as is | The year of the decision (the privacy rule counts from it). |
| `run_id` | VARCHAR | no | `sf-enrich-v11-haiku55-2026-10` | none | as is | The run that read the decision (see `runs`). |
| `item_n` | BIGINT | no |  | none | as is | The item's place in the decision's list (0, 1 ...). |
| `event` | VARCHAR | yes | `opened`, `graded`, `grade_established`, `paved`, `widened`, `sidewalk_width_set`, `extended`, `renamed`, `closed_or_vacated`, `sewered`, `lighted`, `undergrounded` ... | none | as is | Event (closed list `place event`; an out-of-list reading is `other`, its text in `event_raw`). |
| `place_text` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | Place text, as read (private names of records under 75 years are withheld). |
| `from_street` | VARCHAR | yes |  | none | as is | From street, as read. |
| `to_street` | VARCHAR | yes |  | none | as is | To street, as read. |
| `old_name` | VARCHAR | yes |  | none | as is | Old name, as read. |
| `new_name` | VARCHAR | yes |  | none | as is | New name, as read. |
| `named_after` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | Named after, as read (private names of records under 75 years are withheld). |
| `landmark_number` | VARCHAR | yes |  | catalogue | as is | Landmark number, as read. |
| `width_ft` | DOUBLE | yes |  | none | as is | Width ft, as a number. |
| `material` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | Material, as read (private names of records under 75 years are withheld). |
| `contractor` | VARCHAR | yes |  | person_name | shown for officials and organisations; a private person is withheld under the embargo | The contractor as printed. Organisations and governments are shown; a private person is withheld for 75 years after the record (the row's `privacy_class` and `public_after_year`). |
| `event_raw` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | The model's own word for `event` when it was not on the list. |
| `quote` | VARCHAR | yes |  | raw_text | left out for records under the embargo | The words on the page that support the item (<= 300 characters); empty for records after 1951. |
| `privacy_class` | VARCHAR | yes | `none`, `organisation`, `public_official`, `private_person`, `unknown` | none | as is | Who the item names: `private_person`, `organisation`, `public_official`, `unknown` (a payee) or `none`. |
| `public_after_year` | DOUBLE | yes |  | none | as is | For `private_person` items: the year the name becomes public (year + 75). |
| `quote_found` | BOOLEAN | yes |  | none | as is | True when the quote was found in the page text (OCR); empty when the quote is too short to test. |

## `x_enrich_positions`

Extraction table (enrichment v1.1, `sf-enrich-v11-haiku55-2026-10`): one city position created, abolished or re-rated, with its pay rate. One row per item read from one Board decision by claude-haiku-5-5 (decisions v2 rows, 1906-2016). Unverified until checked: see `field_scores` and `claim_reliability`.

| column | type | null | values / unit | privacy | public copy | meaning |
|---|---|---|---|---|---|---|
| `decision_id` | VARCHAR | no | e.g. `qipu:sf/decision/journalofproceed1917sanfrich:1/1` | none | as is | The decision read (see `decisions`). |
| `page_id` | VARCHAR | no | e.g. `journalofproceed1917sanfrich:1` | none | as is | The page: `<archive.org identifier>:<leaf>`. |
| `page_url` | VARCHAR | yes | e.g. `https://archive.org/details/journalofproceed1917sanfrich/page/n1` | none | as is | Opens the page on archive.org. |
| `source_id` | VARCHAR | no |  | none | as is | The archive.org item. |
| `year` | BIGINT | yes | 1850..2030 | none | as is | The year of the decision (the privacy rule counts from it). |
| `run_id` | VARCHAR | no | `sf-enrich-v11-haiku55-2026-10` | none | as is | The run that read the decision (see `runs`). |
| `item_n` | BIGINT | no |  | none | as is | The item's place in the decision's list (0, 1 ...). |
| `title` | VARCHAR | yes |  | catalogue | as is | Title, as read. |
| `class_code` | VARCHAR | yes |  | catalogue | as is | Class code, as read. |
| `count` | DOUBLE | yes |  | none | as is | Count, as a number. |
| `rate` | DOUBLE | yes |  | none | as is | Rate, as a number. |
| `rate_unit` | VARCHAR | yes | `hour`, `day`, `week`, `month`, `year` | none | as is | Rate unit (closed list `rate unit`; an out-of-list reading is `other`, its text in `rate_unit_raw`). |
| `action` | VARCHAR | yes | `create`, `abolish`, `change`, `reclassify` | none | as is | Action (closed list `position action`; an out-of-list reading is `other`, its text in `action_raw`). |
| `department` | VARCHAR | yes |  | catalogue | as is | Department, as read. |
| `rate_unit_raw` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | The model's own word for `rate_unit` when it was not on the list. |
| `action_raw` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | The model's own word for `action` when it was not on the list. |
| `quote` | VARCHAR | yes |  | raw_text | left out for records under the embargo | The words on the page that support the item (<= 300 characters); empty for records after 1951. |
| `privacy_class` | VARCHAR | yes | `none`, `organisation`, `public_official`, `private_person`, `unknown` | none | as is | Who the item names: `private_person`, `organisation`, `public_official`, `unknown` (a payee) or `none`. |
| `public_after_year` | DOUBLE | yes |  | none | as is | For `private_person` items: the year the name becomes public (year + 75). |
| `quote_found` | BOOLEAN | yes |  | none | as is | True when the quote was found in the page text (OCR); empty when the quote is too short to test. |

## `x_enrich_quantities`

Extraction table (enrichment v1.1, `sf-enrich-v11-haiku55-2026-10`): one measured quantity the decision states (capacity, units, stories, width ...). One row per item read from one Board decision by claude-haiku-5-5 (decisions v2 rows, 1906-2016). Unverified until checked: see `field_scores` and `claim_reliability`.

| column | type | null | values / unit | privacy | public copy | meaning |
|---|---|---|---|---|---|---|
| `decision_id` | VARCHAR | no | e.g. `qipu:sf/decision/journalofproceed1917sanfrich:1/1` | none | as is | The decision read (see `decisions`). |
| `page_id` | VARCHAR | no | e.g. `journalofproceed1917sanfrich:1` | none | as is | The page: `<archive.org identifier>:<leaf>`. |
| `page_url` | VARCHAR | yes | e.g. `https://archive.org/details/journalofproceed1917sanfrich/page/n1` | none | as is | Opens the page on archive.org. |
| `source_id` | VARCHAR | no |  | none | as is | The archive.org item. |
| `year` | BIGINT | yes | 1850..2030 | none | as is | The year of the decision (the privacy rule counts from it). |
| `run_id` | VARCHAR | no | `sf-enrich-v11-haiku55-2026-10` | none | as is | The run that read the decision (see `runs`). |
| `item_n` | BIGINT | no |  | none | as is | The item's place in the decision's list (0, 1 ...). |
| `measure` | VARCHAR | yes | `capacity`, `power`, `count`, `area`, `length`, `width`, `height`, `depth`, `elevation`, `setback`, `units`, `stories` ... | none | as is | Measure (closed list `quantity measure`; an out-of-list reading is `other`, its text in `measure_raw`). |
| `value` | DOUBLE | yes |  | none | as is | Value, as a number. |
| `unit` | VARCHAR | yes |  | none | as is | Unit, as read. |
| `of` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | Of, as read (private names of records under 75 years are withheld). |
| `old_value` | DOUBLE | yes |  | none | as is | Old value, as a number. |
| `measure_raw` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | The model's own word for `measure` when it was not on the list. |
| `quote` | VARCHAR | yes |  | raw_text | left out for records under the embargo | The words on the page that support the item (<= 300 characters); empty for records after 1951. |
| `privacy_class` | VARCHAR | yes | `none`, `organisation`, `public_official`, `private_person`, `unknown` | none | as is | Who the item names: `private_person`, `organisation`, `public_official`, `unknown` (a payee) or `none`. |
| `public_after_year` | DOUBLE | yes |  | none | as is | For `private_person` items: the year the name becomes public (year + 75). |
| `quote_found` | BOOLEAN | yes |  | none | as is | True when the quote was found in the page text (OCR); empty when the quote is too short to test. |

## `x_enrich_rates`

Extraction table (enrichment v1.1, `sf-enrich-v11-haiku55-2026-10`): one rate, fare, fee or tax the decision sets. One row per item read from one Board decision by claude-haiku-5-5 (decisions v2 rows, 1906-2016). Unverified until checked: see `field_scores` and `claim_reliability`.

| column | type | null | values / unit | privacy | public copy | meaning |
|---|---|---|---|---|---|---|
| `decision_id` | VARCHAR | no | e.g. `qipu:sf/decision/journalofproceed1917sanfrich:1/1` | none | as is | The decision read (see `decisions`). |
| `page_id` | VARCHAR | no | e.g. `journalofproceed1917sanfrich:1` | none | as is | The page: `<archive.org identifier>:<leaf>`. |
| `page_url` | VARCHAR | yes | e.g. `https://archive.org/details/journalofproceed1917sanfrich/page/n1` | none | as is | Opens the page on archive.org. |
| `source_id` | VARCHAR | no |  | none | as is | The archive.org item. |
| `year` | BIGINT | yes | 1850..2030 | none | as is | The year of the decision (the privacy rule counts from it). |
| `run_id` | VARCHAR | no | `sf-enrich-v11-haiku55-2026-10` | none | as is | The run that read the decision (see `runs`). |
| `item_n` | BIGINT | no |  | none | as is | The item's place in the decision's list (0, 1 ...). |
| `kind` | VARCHAR | yes | `fare`, `fee`, `tax`, `charge`, `interest`, `pct_gross`, `other` | none | as is | Kind (closed list `rate kind`; an out-of-list reading is `other`, its text in `kind_raw`). |
| `value` | DOUBLE | yes |  | none | as is | Value, as a number. |
| `unit` | VARCHAR | yes |  | none | as is | Unit, as read. |
| `per` | VARCHAR | yes |  | none | as is | Per, as read. |
| `old_value` | DOUBLE | yes |  | none | as is | Old value, as a number. |
| `applies_to` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | Applies to, as read (private names of records under 75 years are withheld). |
| `kind_raw` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | The model's own word for `kind` when it was not on the list. |
| `quote` | VARCHAR | yes |  | raw_text | left out for records under the embargo | The words on the page that support the item (<= 300 characters); empty for records after 1951. |
| `privacy_class` | VARCHAR | yes | `none`, `organisation`, `public_official`, `private_person`, `unknown` | none | as is | Who the item names: `private_person`, `organisation`, `public_official`, `unknown` (a payee) or `none`. |
| `public_after_year` | DOUBLE | yes |  | none | as is | For `private_person` items: the year the name becomes public (year + 75). |
| `quote_found` | BOOLEAN | yes |  | none | as is | True when the quote was found in the page text (OCR); empty when the quote is too short to test. |

## `x_enrich_stances`

Extraction table (enrichment v1.1, `sf-enrich-v11-haiku55-2026-10`): one person or group who spoke, petitioned or protested, and their stance. One row per item read from one Board decision by claude-haiku-5-5 (decisions v2 rows, 1906-2016). Unverified until checked: see `field_scores` and `claim_reliability`.

| column | type | null | values / unit | privacy | public copy | meaning |
|---|---|---|---|---|---|---|
| `decision_id` | VARCHAR | no | e.g. `qipu:sf/decision/journalofproceed1917sanfrich:1/1` | none | as is | The decision read (see `decisions`). |
| `page_id` | VARCHAR | no | e.g. `journalofproceed1917sanfrich:1` | none | as is | The page: `<archive.org identifier>:<leaf>`. |
| `page_url` | VARCHAR | yes | e.g. `https://archive.org/details/journalofproceed1917sanfrich/page/n1` | none | as is | Opens the page on archive.org. |
| `source_id` | VARCHAR | no |  | none | as is | The archive.org item. |
| `year` | BIGINT | yes | 1850..2030 | none | as is | The year of the decision (the privacy rule counts from it). |
| `run_id` | VARCHAR | no | `sf-enrich-v11-haiku55-2026-10` | none | as is | The run that read the decision (see `runs`). |
| `item_n` | BIGINT | no |  | none | as is | The item's place in the decision's list (0, 1 ...). |
| `who` | VARCHAR | yes |  | person_name | shown for officials and organisations; a private person is withheld under the embargo | The who as printed. Organisations and governments are shown; a private person is withheld for 75 years after the record (the row's `privacy_class` and `public_after_year`). |
| `who_kind` | VARCHAR | yes | `private_person`, `public_official`, `organisation` | none | as is | Who kind (closed list `who kind`; an out-of-list reading is `other`, its text in `who_kind_raw`). |
| `affiliation` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | Affiliation, as read (private names of records under 75 years are withheld). |
| `stance` | VARCHAR | yes | `for`, `against`, `neutral` | none | as is | Stance (closed list `stance`; an out-of-list reading is `other`, its text in `stance_raw`). |
| `form` | VARCHAR | yes | `spoke`, `petition`, `protest`, `letter` | none | as is | Form (closed list `input form`; an out-of-list reading is `other`, its text in `form_raw`). |
| `count` | DOUBLE | yes |  | none | as is | Count, as a number. |
| `who_kind_raw` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | The model's own word for `who_kind` when it was not on the list. |
| `stance_raw` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | The model's own word for `stance` when it was not on the list. |
| `form_raw` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | The model's own word for `form` when it was not on the list. |
| `quote` | VARCHAR | yes |  | raw_text | left out for records under the embargo | The words on the page that support the item (<= 300 characters); empty for records after 1951. |
| `privacy_class` | VARCHAR | yes | `none`, `organisation`, `public_official`, `private_person`, `unknown` | none | as is | Who the item names: `private_person`, `organisation`, `public_official`, `unknown` (a payee) or `none`. |
| `public_after_year` | DOUBLE | yes |  | none | as is | For `private_person` items: the year the name becomes public (year + 75). |
| `quote_found` | BOOLEAN | yes |  | none | as is | True when the quote was found in the page text (OCR); empty when the quote is too short to test. |

## `x_enrich_traffic_rules`

Extraction table (enrichment v1.1, `sf-enrich-v11-haiku55-2026-10`): one traffic rule established or rescinded (stop sign, one way, parking limit ...). One row per item read from one Board decision by claude-haiku-5-5 (decisions v2 rows, 1906-2016). Unverified until checked: see `field_scores` and `claim_reliability`.

| column | type | null | values / unit | privacy | public copy | meaning |
|---|---|---|---|---|---|---|
| `decision_id` | VARCHAR | no | e.g. `qipu:sf/decision/journalofproceed1917sanfrich:1/1` | none | as is | The decision read (see `decisions`). |
| `page_id` | VARCHAR | no | e.g. `journalofproceed1917sanfrich:1` | none | as is | The page: `<archive.org identifier>:<leaf>`. |
| `page_url` | VARCHAR | yes | e.g. `https://archive.org/details/journalofproceed1917sanfrich/page/n1` | none | as is | Opens the page on archive.org. |
| `source_id` | VARCHAR | no |  | none | as is | The archive.org item. |
| `year` | BIGINT | yes | 1850..2030 | none | as is | The year of the decision (the privacy rule counts from it). |
| `run_id` | VARCHAR | no | `sf-enrich-v11-haiku55-2026-10` | none | as is | The run that read the decision (see `runs`). |
| `item_n` | BIGINT | no |  | none | as is | The item's place in the decision's list (0, 1 ...). |
| `rule` | VARCHAR | yes | `stop_sign`, `yield`, `one_way`, `parking_time_limit`, `no_parking`, `no_stopping`, `tow_away`, `turn_restriction`, `u_turn_prohibited`, `turn_lane`, `permit_parking_area`, `signal` ... | none | as is | Rule (closed list `traffic rule`; an out-of-list reading is `other`, its text in `rule_raw`). |
| `action` | VARCHAR | yes | `establish`, `rescind`, `trial` | none | as is | Action (closed list `traffic action`; an out-of-list reading is `other`, its text in `action_raw`). |
| `place_text` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | Place text, as read (private names of records under 75 years are withheld). |
| `from_street` | VARCHAR | yes |  | none | as is | From street, as read. |
| `to_street` | VARCHAR | yes |  | none | as is | To street, as read. |
| `side` | VARCHAR | yes |  | none | as is | Side, as read. |
| `hours` | VARCHAR | yes |  | none | as is | Hours, as read. |
| `days` | VARCHAR | yes |  | none | as is | Days, as read. |
| `limit_minutes` | DOUBLE | yes |  | none | as is | Limit minutes, as a number. |
| `direction` | VARCHAR | yes |  | none | as is | Direction, as read. |
| `rule_raw` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | The model's own word for `rule` when it was not on the list. |
| `action_raw` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | The model's own word for `action` when it was not on the list. |
| `quote` | VARCHAR | yes |  | raw_text | left out for records under the embargo | The words on the page that support the item (<= 300 characters); empty for records after 1951. |
| `privacy_class` | VARCHAR | yes | `none`, `organisation`, `public_official`, `private_person`, `unknown` | none | as is | Who the item names: `private_person`, `organisation`, `public_official`, `unknown` (a payee) or `none`. |
| `public_after_year` | DOUBLE | yes |  | none | as is | For `private_person` items: the year the name becomes public (year + 75). |
| `quote_found` | BOOLEAN | yes |  | none | as is | True when the quote was found in the page text (OCR); empty when the quote is too short to test. |

## `x_mentions` (915 rows)

Democracy's Library items and the neighbourhoods and streets they mention (one row per mention; from the DL place tags, `x_dl_tags`).

| column | type | null | values / unit | privacy | public copy | meaning |
|---|---|---|---|---|---|---|
| `run_id` | VARCHAR | no | `dl-tags-haiku55-2026-10` | none | as is | the tagging run |
| `document_id` | VARCHAR | no |  | none | as is | `ia:<identifier>` of the item |
| `seq` | BIGINT | yes |  | none | as is | the tag row |
| `mention_text` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | the neighbourhood or street as tagged |
| `mention_kind` | VARCHAR | no | `hood`, `street` | none | as is | `hood` or `street` |
| `doc_type` | VARCHAR | yes | `annual report`, `city-owned property report`, `environmental impact report`, `minutes or proceedings`, `ordinance or legislation`, `other`, `plan`, `study or report` | none | as is | the item's document type and scope (citywide, neighbourhood ...) |
| `scope` | VARCHAR | yes | `citywide`, `neighbourhoods`, `regional`, `site` | none | as is | the item's document type and scope (citywide, neighbourhood ...) |

## `x_permits_block` (28,708 rows)

One assessor block and one kind of building permit (DataSF i98e-djp9, PDDL), filed 1982-2026: distinct permit numbers and the first year of that kind. kind=all is every permit. Not from the Internet Archive (DataSF open data, PDDL).

| column | type | null | values / unit | privacy | public copy | meaning |
|---|---|---|---|---|---|---|
| `block` | VARCHAR | yes |  | none | as is | Assessor block. |
| `kind` | VARCHAR | no | `adu`, `all`, `change_use`, `fire`, `kitchen_bath`, `more_units`, `reroof`, `seismic`, `sign`, `soft_story`, `solar` | none | as is | all, reroof, kitchen_bath, solar, soft_story, seismic, adu, more_units, fire, sign, change_use (keyword and field rules in permits_blocks.py). |
| `permits` | BIGINT | yes |  | none | as is | Distinct permit numbers filed since 1982. |
| `first_year` | BIGINT | yes |  | none | as is | Year the first permit of that kind was filed. |

## `x_realprop_dir` (276,599 rows)

Extraction table: the Andrews Real Property Directory of San Francisco, 1943 and 1949 editions (archive.org scans), one row per printed address line: street, number, assessor block and lot, and the owners as printed (read by claude-sonnet-5-5 from page tiles; source-review run). The owners are shown: the editions are more than 75 years old (the privacy rule withholds names only for records under 75 years).

| column | type | null | values / unit | privacy | public copy | meaning |
|---|---|---|---|---|---|---|
| `run_id` | VARCHAR | no | `andrews-sonnet55-2026-10` | none | as is | The run that read the pages (see `runs`). |
| `page_id` | VARCHAR | no | e.g. `andrewsrealprope1943andr:1` | none | as is | The page: `<archive.org identifier>:<leaf>`. |
| `tile` | VARCHAR | yes | `x0y0`, `x0y1`, `x1y0`, `x1y1` | none | as is | Which quarter of the page image the line was read from (`x0y0` top left ...). |
| `seq` | VARCHAR | yes |  | none | as is | The line inside its tile (`<tile>/<n>`). |
| `street_header` | VARCHAR | yes |  | none | as is | The street heading the line is printed under. |
| `addr_number` | VARCHAR | yes |  | none | as is | The house number as printed. |
| `block` | VARCHAR | yes |  | none | as is | The assessor block as printed. |
| `lot` | VARCHAR | yes |  | none | as is | The assessor lot as printed. |
| `owners` | VARCHAR | yes |  | person_name | shown for officials and organisations; a private person is withheld under the embargo | The owners as printed (`Curtis, Emma R`). Shown for these editions (over 75 years old); a row without a year would be withheld. |
| `edition` | BIGINT | yes | 1850..2030 | none | as is | The edition of the directory. |
| `year` | BIGINT | yes | 1850..2030 | none | as is | The year the directory states (its edition year). |
| `row_key` | VARCHAR | no |  | none | as is | `<run_id>/<page_id>/<seq>`. |
| `page_url` | VARCHAR | yes | e.g. `https://archive.org/details/andrewsrealprope1943andr/page/n1` | none | as is | Opens the page on archive.org. |

## `x_roll_call_votes` (2,084,507 rows)

One row per name in a roll call: the vote, the name as printed, the person it was matched to and how (`surname`, `surname+given`, `fuzzy`, `fuzzy_low`, `surname_widened`, `journal_only`, or `ambiguous`/`unmatched` with no person).

| column | type | null | values / unit | privacy | public copy | meaning |
|---|---|---|---|---|---|---|
| `rollcall_id` | VARCHAR | no |  | none | as is | The roll call (see `x_roll_calls`). |
| `vote` | VARCHAR | no | `aye`, `no`, `absent`, `excused`, `not_voting` | none | as is | `aye`, `no`, `absent`, `excused` or `not_voting`. |
| `name_printed` | VARCHAR | yes |  | public_official | as is | The name as printed in the roll call. |
| `person_id` | VARCHAR | yes |  | none | as is | The supervisor the name was matched to (see `persons`); empty when `unmatched` or `ambiguous`. |
| `match` | VARCHAR | yes | `ambiguous`, `fuzzy`, `fuzzy+given`, `fuzzy_low`, `journal_only`, `surname`, `surname+given`, `surname_widened`, `unmatched` | none | as is | How the name was matched: `surname`, `surname+given`, `fuzzy`, `fuzzy+given`, `fuzzy_low`, `surname_widened`, `journal_only`, `ambiguous`, `unmatched`. |

## `x_roll_calls` (171,123 rows)

Every printed roll call ("Adopted by the following vote: Ayes: Supervisors ... Noes: ... Absent: ...") found in the Journal text, 1906-1999.

| column | type | null | values / unit | privacy | public copy | meaning |
|---|---|---|---|---|---|---|
| `rollcall_id` | VARCHAR | no |  | none | as is | Id of the roll call: hash of the item id and the roll call's position in the item. |
| `item_id` | VARCHAR | no |  | none | as is | The Board item the roll call is printed in (`<volume>\|<n>`, re-swept `<volume>\|g<n>`); its decision is `qipu:sf/decision/<item_id>`. |
| `corpus` | VARCHAR | yes | `bos_corpus`, `resweep_d1900`, `resweep_d1910`, `resweep_d1911`, `resweep_d1920`, `resweep_d1930`, `resweep_d1940`, `resweep_d1950`, `resweep_d1960`, `resweep_d1970`, `resweep_d1980`, `resweep_d1990` ... | none | as is | Where the item text came from: `bos_corpus` (the 2026-09 corpus) or `resweep_d<decade>` (re-swept items). |
| `page_id` | VARCHAR | no | e.g. `journalofproceed30sanfrich:1` | none | as is | The archive.org page: `<identifier>:<leafNum>` (IA's leaf number, the jp2 / IIIF number), or `<identifier>:d<n>` where only the BookReader index is known, or `<url>:<page>` for a web document. `nopage:<seq>` where the source gives no page number (sfbos rows without one): the row is kept, page_url is NULL. |
| `page_url` | VARCHAR | yes | e.g. `https://archive.org/details/journalofproceed30sanfrich/page/n1` | none | as is | Opens the page: archive.org BookReader link built from the leaf map (audited against IA's scandata), or `<pdf url>#page=<n>` for a PDF. NULL only where page_id is `nopage:<seq>`. |
| `source_id` | VARCHAR | no |  | none | as is | archive.org identifier of the Journal volume. |
| `leaf` | BIGINT | yes |  | none | as is | archive.org BookReader page index (the n in `/page/n<k>`). |
| `hocr_leaf` | BIGINT | yes |  | none | as is | Page index in the OCR (before the leaf map). |
| `page_exact` | BOOLEAN | yes |  | none | as is | True when the archive.org page number was converted from the OCR leaf with the per-volume leaf map; false = may be a page or two off. |
| `year` | BIGINT | yes |  | none | as is | Year of the Journal volume. |
| `date` | DATE | yes |  | none | as is | Meeting date, where the corpus gives one within a year of the volume's year. |
| `outcome_text` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | The printed outcome phrase before "by the following vote" (`Adopted`, `Finally passed`, ...), up to 120 characters. |
| `outcome` | VARCHAR | yes | `failed`, `passed`, `unknown` | none | as is | `passed`, `failed` or `unknown`, from the words in `outcome_text`. |
| `ayes_n` | BIGINT | yes |  | none | as is | Count as printed. |
| `noes_n` | BIGINT | yes |  | none | as is | Noes count as printed. |
| `absent_n` | BIGINT | yes |  | none | as is | Absent count as printed. |
| `ayes_parsed` | BIGINT | yes |  | none | as is | Names parsed. |
| `noes_parsed` | BIGINT | yes |  | none | as is | Names parsed after Noes. |
| `absent_parsed` | BIGINT | yes |  | none | as is | Names parsed after Absent or Excused. |
| `read_from` | VARCHAR | yes |  | none | as is | v0.6.1: `page_text` (re-read from the Journal page text, across page breaks, with "No:" single dissents) or `item_text` (the item-based parse kept). |
| `duplicate_of` | VARCHAR | yes |  | none | as is | v0.6.1: the rollcall_id of the same printed roll call read from another item (a corpus item and a re-swept one); its votes are not counted twice. |
| `counts_match` | BOOLEAN | yes |  | none | as is | True when every printed count equals the names parsed (94% of roll calls). |
| `numbers` | VARCHAR[][] | yes |  | none | as is | Resolution / Ordinance / File / Bill numbers printed in the item: `[kind, number, series year]`. |
| `context` | VARCHAR | yes |  | raw_text | left out for records under the embargo | Up to 300 characters of item text before the roll call. Empty (withheld in the public files for records under the privacy embargo (75 years in SF, 92 in Vancouver)). |
| `quote` | VARCHAR | yes |  | raw_text | left out for records under the embargo | The roll call as printed, from 60 characters before it (<= 600 characters). Names supervisors, who are public officials. Not published for records under the embargo: the quoted lines carry the item text ("Submitted by ..."), not only the roll. |
| `run_id` | VARCHAR | no | `rollcall-parser-v1` | none | as is | `rollcall-parser-v1` (see `runs`). |

## `x_site_rows` (62,917 rows)

The map's decision rows (citywide decades, street layer) with the item they come from (`linked_item`).

| column | type | null | values / unit | privacy | public copy | meaning |
|---|---|---|---|---|---|---|
| `site_id` | VARCHAR | no |  | none | as is | The map's row id: `su:<hash of the item id>` for Board items, `sd:...` for others. |
| `decision_id` | VARCHAR | no |  | none | as is | The decision the row shows (see `decisions`). |
| `linked_item` | VARCHAR | yes |  | none | as is | The Board item (`<volume>\|<n>`) the row was matched to; empty when none. |
| `origins` | VARCHAR[] | yes |  | none | as is | The map files the row appears in (`street_decisions/sd`, `citywide/decade-1970-routine.json`, ...). |
| `plain` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | The map's one-line summary, written by a model (claude-haiku-5-5), after accuracy fixes. Person-like names are withheld for records under the 75-year privacy filter (92 years in Vancouver). |
| `year` | BIGINT | yes |  | none | as is | Year of the decision. |
| `date` | VARCHAR | yes |  | none | as is | Date of the decision (YYYY-MM-DD) where known. |
| `outcome` | VARCHAR | yes | `approved by voters`, `passed`, `refused`, `rejected`, `rejected by voters`, `vetoed` | none | as is | `passed`, `rejected`, `refused`, `vetoed`, ... as on the map. |
| `topic` | VARCHAR | yes | `admin`, `appeals`, `budget`, `elections`, `health`, `housing`, `landmarks`, `names`, `other`, `permits`, `property`, `redevelopment` ... | none | as is | Topic as on the map (`admin`, `budget`, `property`, `housing`, ...). |
| `place` | VARCHAR | yes |  | free_text | names of private people withheld under the embargo | The place as written on the map. Person-like names are withheld for records under the 75-year privacy filter (92 years in Vancouver). |
| `src` | VARCHAR | yes |  | none | as is | Source link as the map gives it. |
| `src_mapped` | VARCHAR | yes |  | none | as is | Source link pointed at the item's exact archive.org page with the leaf map where the item is known; else `src`. |
| `blocks` | VARCHAR[] | yes |  | none | as is | Assessor blocks the map files the row under. |
| `streets` | VARCHAR[] | yes |  | none | as is | Streets the map files the row under (`NAME\|TYPE`). |
| `hoods` | VARCHAR[] | yes |  | none | as is | Neighbourhoods the map files the row under. |

Not in the public copy: `significance`, `hidden`, `importance`, `importance_why`.

## `xref` (1,320,115 rows)

Crosswalk: one row per outside id of a qipu record, so the people who use those ids can reach qipu (Planning and the Assessor by block and lot, DataSF by EAS id, the Clerk by ordinance number, Wikidata by QID, the Internet Archive by item, page and IIIF canvas, census tables by tract). Built by `qipu_data/xref.py` from the release's own tables; it adds no qipu ids. The schemes are a closed list (`legistar_file` and `legistar_matter` are reserved for a later release).

| column | type | null | values / unit | privacy | public copy | meaning |
|---|---|---|---|---|---|---|
| `qipu_id` | VARCHAR | no |  | none | as is | The qipu record: a place, person, instrument, archive.org source (its identifier) or page (`<identifier>:<leaf>`). |
| `scheme` | VARCHAR | no | `ia_item`, `ia_page`, `ia_display_n`, `iiif_canvas`, `blklot`, `eas`, `wikidata`, `enactment`, `census_tract_1990`, `census_tract_2000`, `census_tract_2010`, `census_tract_2020` ... | none | as is | `ia_item` (archive.org identifier), `ia_page` (hOCR page index, 0-based), `ia_display_n` (the n of archive.org /page/n<n>), `iiif_canvas` (IA's IIIF v3 canvas id), `blklot` (Assessor block and lot, `0847019`), `eas` (DataSF EAS base address id), `wikidata` (QID), `enactment` (`Ord. 338-75`, `Res. 273-64`; series forms `Ord. 8727 (N.S.)`, `Res. 15288 (Series of 1939)`), `census_tract_<map year>` (6-digit tract code), `analysis_nbhd` (DataSF Analysis Neighborhood), `sup_district_2022` (reserved), `lpf` (the Linked Places / World Historical Gazetteer @id). |
| `value` | VARCHAR | no |  | none | as is | The outside id. |
| `valid_from` | VARCHAR | yes |  | none | as is | When the id starts to apply, where known (the year of an ordinance or resolution). |
| `valid_to` | VARCHAR | yes |  | none | as is | When the id stops applying, where known. |
| `source` | VARCHAR | yes |  | catalogue | as is | Where the outside id comes from. |
| `method` | VARCHAR | yes |  | none | as is | How the row was made (`iiif_manifest` = read from the item's cached IIIF manifest; `bookreader_n` = the manifest's canvas id form for the BookReader page; `number printed, year assumed from the Journal volume` ...). |
| `confidence` | DOUBLE | yes | 0..1 | none | as is | 1 when the id is read or printed as is; lower when part of it is inferred (the method says which part). |

## Kept local, not published

`name_mentions`: Every name as printed in a vote, a party list or an owner field, with its privacy class. The raw name index: kept local, never published., `x_institution_gazetteer`: Extraction table (not published: an unreviewed v0.6 seed): the gazetteer of San Francisco institutions (museums, stadiums, hospitals, schools, churches, police stations ...) used to recognise institutions named in the records and to place them (27_source_review.py). Two runs: Wikidata items (`inst-gazetteer-v1-2026-10`, with a Wikidata id and a point) and the institution lists of the Polk-Crocker city directories (`inst-dirlists-v1-2026-10`, with the directory line they were read from).

## The public SQLite copy (`qipu_sf.db`)

Built from the public Parquet tables above; ready-joined tables for the explorer.

### `address_resolution` (805,748 rows) (qipu_sf.db (table))

Raw "<number> <street>" texts seen in claims, resolved exactly to today's EAS address. Only exact, unique matches in v1.

| column | privacy | meaning |
|---|---|---|
| `raw_text` | free_text | A `<number> <street>` place text as printed in a claim. |
| `year` | none | Year of the claim. |
| `place_id` | none | Today's EAS address it matched (see `places`). |
| `precision` | none | `address` on every row. |
| `method` | none | `exact` on every row: number and street matched exactly one DataSF EAS address. |
| `confidence` | none | `1.0` on every row. |
| `run_id` | none | `address-exact-v1` (see `runs`). |

### `block_points` (5,102 rows) (qipu_sf.db (table))

One point per assessor block (the map's block index), for fast lookups.

| column | privacy | meaning |
|---|---|---|
| `block` | none | assessor block number |
| `lat` | none | a point inside the block |
| `lon` | none | a point inside the block |
| `hood_id` | none | the neighbourhood place id |

### `claims` (5,657,289 rows) (qipu_sf.db (table))

The core table: one small statement per row, the same shape for every source (DATA_ARCHITECTURE.md 7.4).

| column | privacy | meaning |
|---|---|---|
| `claim_id` | none | Stable id: `c:` + 32 hex (128 bits, hashed from the slot: subject, property, object, page, role); votes `v:<rollcall_id>:<n>`. |
| `subject_id` | none | What the statement is about: a decision (`qipu:sf/decision/...`), a place, a person (`qipu:sf/person/...`) or an archive item (`ia:<identifier>`). |
| `subject_label` | free_text | Readable label of the subject. |
| `property` | none | Controlled list: `outcome`, `voted`, `concerns_place`, `built`, `depicted_in`, `recorded_at`, `happened_at`, `ballot_result`. |
| `property_detail` | none | The source file's own name for the statement (e.g. `sanborn_map_shows`, `voted_aye`, `timeline_moment`). |
| `object_id` | none | The other entity, when there is one (the decision a supervisor voted on; the place an archive item concerns). |
| `value_num` | none | Numeric value (e.g. the year built from the assessor roll). |
| `value_text` | free_text | Text value: the outcome, the vote (yes/no/absent/excused), the line as written. |
| `t_from` | none | When: YYYY-MM-DD, YYYY-MM or YYYY. |
| `t_precision` | none | `day`, `month` or `year`. |
| `place_id` | none | Where (a qipu place id; `qipu:sf/place/city` for citywide). |
| `place_label` | free_text | The place as written in the source. |
| `place_precision` | none | How exactly the source places it: `address`, `parcel`, `corner`, `segment`, `street`, `block`, `stated`, `named place`, `hood`, `city`. |
| `page_id` | none | The archive.org page: `<identifier>:<leafNum>` (IA's leaf number, the jp2 / IIIF number), or `<identifier>:d<n>` where only the BookReader index is known, or `<url>:<page>` for a web document. |
| `source_id` | none | The source: an archive.org identifier, or `web:<url>` for other sources. |
| `link` | none | Opens the source: the archive.org page for scans, else the source URL. |
| `source_label` | free_text | Readable source name. |
| `licence` | none | The source's licence where the source file states it. |
| `place_role` | none | How the claim relates to its place: `at`, `along` (a street), `near` (see `qualifiers.distance_m`) or `about` (a document about the place). |
| `quote` | raw_text | Short quote of the source (<= 300 characters). See `privacy_flag`. |
| `extraction_table` | none | Which table or file the statement came from (`bos_records`, `resweep`, `x_roll_calls`, `map_blocks`, `site_decisions`, ...). |
| `run_id` | none | The extraction run (see `runs`). |
| `confidence` | none | 0-1 where a run gives one (votes: 0.95 when the printed counts match the names). |
| `status` | none | `extracted` (machine-read), `record` (from an official register: assessor, DataSF), `curated` (written or checked by qipu for the map). No `verified` rows yet. |
| `topic` | none | Topic label from the run. |
| `outcome` | none | Decision outcome where known. |
| `rights_note` | catalogue | What a reuser must know about the source's rights, in words (archive.org search hits, or a licence text longer than 80 characters moved here from `licence`). |
| `collection_group` | none | For archive.org search hits (`showcase_search`): the group of the item's collection (`Books and texts`, `Films and video`, `TV News Archive`, ...). |

### `decisions` (718,093 rows) (qipu_sf.db (table))

One row per thing the Board of Supervisors (SF) or City Council (Vancouver) did: an item in the Journal or minutes, a re-swept item, a hand-built decision, or a map/site row with no item behind it. Decisions v2 readings attach to the same row.

| column | privacy | meaning |
|---|---|---|
| `decision_id` | none | `qipu:sf/decision/<item id>`: corpus items `<volume>\|<n>`, re-swept items `<volume>\|g<n>`, hand-built `hand:<slug>`, map/site-only rows `map:...`, `su:...`, `sd:...`. |
| `decision_group` | none | One id per printed item (v0.6.1): rows of several passes over the same item share it; count distinct decision_group for "how many decisions". |
| `origin` | none | `bos_corpus`, `resweep`, `map_blocks`, `site`, `hand`. |
| `city` | none | `sf` or `van`. |
| `same_as` | none | Audit: the id this row was merged from (decisions v2 attached to v1). |
| `outcome_from` | none | Where outcome_best came from (site, run, roll call ...). |
| `year` | none | The year of the meeting. |
| `date` | none | The meeting date (YYYY-MM-DD) when known. |
| `kind` | none | resolution, ordinance, motion, bill, hearing, report ... as classified. |
| `instrument_id` | none | Ordinance/resolution number printed in the item (see `instruments`). |
| `outcome` | none | The run's outcome (passed / refused / rejected / vetoed / pending / unknown). |
| `action` | none | The last action on the item (adopted, passed first reading, referred ...). |
| `plain` | free_text | The run's one-line plain summary (model-written; see summary_by). |
| `summary_note` | none | Who wrote the plain line; check the scan. |
| `topic` | none | Topic label. |
| `subtopic` | none | Finer topic label. |
| `block` | none | Assessor block the item names, when it names one. |
| `streets` | free_text | Streets the item names ("\|"-separated). |
| `place_text` | free_text | The place as written in the item. |
| `summary_by` | none | Who wrote the plain line (model name, "model-written; check the scan"). |
| `scan_url` | none | Opens the scanned page on archive.org. |
| `page_exact` | none | True when the page link was converted from the OCR leaf with the per-volume leaf map; false = may be a page or two off. |
| `quote` | raw_text | Raw OCR text of the item. Not published for records under the embargo. |

### `decisions_v2` (475,800 rows) (qipu_sf.db (table))

Decisions v2 (one model call per page, SF and Vancouver): votes with names, parties, money, places, zoning. Names of private people are withheld under the embargo.

| column | privacy | meaning |
|---|---|---|
| `run_id` | none | The extraction run that read the row (see `runs`). |
| `page_id` | none | The archive.org page: `<identifier>:<leafNum>` (IA's leaf number, the jp2 / IIIF number), or `<identifier>:d<n>` where only the BookReader index is known, or `<url>:<page>` for a web document. |
| `seq` | none | Row id inside the run: the corpus item id (`<volume>\|<n>`) for v1 rows, the item's position on the page (1, 2, ...) for decisions v2 rows. |
| `decision_id` | none | The decision id. |
| `city` | none | `sf` or `van`. |
| `year` | none | Year. |
| `kind` | none | The kind of record. |
| `number` | none | Resolution or ordinance number as printed (v1: parsed from the item's opening words, see `decision_numbers`; v2: read by the model). |
| `file_no` | none | Board file number as printed. |
| `action_raw` | none | Decisions v2: the last action on the pages read, as the model chose it (`adopted`, `passed_final`, `referred`, `continues_next_page`, ...). |
| `stage` | none | Decisions v2: stage reached, from `action_raw`: `adopted`, `final`, `first reading` or `hearing`; empty for other actions and v1 rows. |
| `outcome` | none | passed / refused / rejected / vetoed / pending / unknown. |
| `ayes_n` | none | Ayes count as printed (v1: from the item's roll call in `x_roll_calls`; v2: read by the model). |
| `noes_n` | none | Noes count as printed (v1: from the item's roll call; v2: read by the model). |
| `absent_n` | none | Absent count as printed (v1: from the item's roll call; v2: read by the model). |
| `ayes` | public_official | Surnames voting aye, as printed (v1: from `x_roll_call_votes`; v2: read by the model). |
| `noes` | public_official | Surnames voting no, as printed (v1: from `x_roll_call_votes`; v2: read by the model). |
| `absent` | public_official | Absent or excused. |
| `mover` | public_official | Decisions v2: surname of the member who moved the deciding motion, as printed. |
| `seconder` | public_official | Decisions v2: surname of the member who seconded the deciding motion, as printed. |
| `department` | free_text | Decisions v2: the department, board, officer or committee that reported or requested the item, as printed. |
| `parties` | nested | Parties as JSON text (names of private people withheld under the embargo). |
| `money` | nested | Amounts as JSON text (payees withheld under the embargo). |
| `places` | nested | Places as JSON text. |
| `zoning` | none | Decisions v2: a zoning change, `from_district` and `to_district` as printed. |
| `related` | none | Decisions v2: numbers of other measures the item amends or refers to (`relation` is always `refers_to`). |
| `dates` | none | Decisions v2: dates that set or schedule the decision, with `role` (`effective`, `continued_to`, `hearing`, `deadline`, `other`). |
| `topics` | none | Decisions v2: topics chosen by a model from a fixed list (`housing`, `whats-being-built`, `parks`, `transit`, ...). |
| `plain` | free_text | One-line plain summary (model-written; check the scan). |
| `confidence` | none | Decisions v2: 0-1 from rule checks: 1.0 quote found on the page, 0.7 found loosely, 0.4 not found; x0.8 for action `other` or noes count not matching names. |
| `quote` | raw_text | The item's text as printed (v1: first 300 characters; v2: the quote the model copied). Empty (withheld in the public files for records under the privacy embargo (75 years in SF, 92 in Vancouver)). |

### `hood_archive_items` (600 rows) (qipu_sf.db (table))

Ready-joined: archive.org items qipu linked to places, by neighbourhood (Democracy's Library first).

| column | privacy | meaning |
|---|---|---|
| `hood` | none | Neighbourhood. |
| `in_DL` | none | 1 when the item is in Democracy's Library. |
| `title` | catalogue | Title (catalogue title for archive items; the plain line for decisions). |
| `year` | none | Year. |
| `kind` | none | The kind of record. |
| `collection` | none | The collection group. |
| `place` | none | The place as written. |
| `excerpt` | raw_text | A short excerpt of the item (raw text; not published for embargoed records). |
| `link` | none | Opens the source: the archive.org page for scans, else the source URL. |
| `source_id` | none | The source: an archive.org identifier, or `web:<url>` for other sources. |

### `id_redirects` (135,661 rows) (qipu_sf.db (table))

Stable-id policy: a merged or retired id keeps resolving through this table (empty in v0.1, the first release).

| column | privacy | meaning |
|---|---|---|
| `old_id` | none | An id an earlier release published. |
| `new_id` | none | The id it now resolves to; empty when the id was retired. |
| `reason` | none | `decisions v2 reading attached to the same v1 decision`, or `retired in <release>: no longer produced by the build`. |
| `since_release` | none | Release that added the redirect. |

### `instruments` (72,224 rows) (qipu_sf.db (table))

Ordinances, resolutions and bills by printed number.

| column | privacy | meaning |
|---|---|---|
| `instrument_id` | none | `qipu:sf/instrument/<res\|ord\|bill>/<year>-<n>` when the number prints its year (627-91), `s<year>-<n>` for "(Series of 1939)", `ns-<n>` for "(New Series)", else `y<journal year>-<n>`. |
| `kind` | none | `resolution` or `ordinance`. |
| `number` | none | The number as printed. |
| `year` | none | Year printed with the number, else the earliest Journal year it appears in (see `year_basis`). |
| `year_basis` | none | printed, or the Journal year (assumed). |
| `file_no` | none | Board file number printed with it. |
| `first_page_id` | none | Lowest `page_id` among the `x_decisions` rows that print the number; `:` when that row has no archive.org page. |
| `first_page_link` | none | Opens the first page that prints the instrument. |
| `n_decisions` | none | Decisions that print the number. |

### `media` (1,428 rows) (qipu_sf.db (table))

Every photo, film, recording, clip and document the map shows (timelines, covers, hood stories, block items, films, clips), with its credit, licence, holder and a rights note. Claims `depicted_in` / `recorded_at` tie each to its place and year.

| column | privacy | meaning |
|---|---|---|
| `media_id` | none | `qipu:sf/media/<hash of its URL>`, stable. |
| `media_kind` | none | `photo`, `film`, `recording`, `clip`, `text`, `document` (`kind` with `audio` -> `recording`). |
| `kind` | none | Kind as the map records it: `photo`, `film`, `audio`, `clip`, `text`, `document`. |
| `title` | catalogue | Title or caption. |
| `created_edtf` | none | When it was made (EDTF), from a year in the title; `~` for circa; empty when the title has none. |
| `beat_year` | none | The year of the map beat the media is placed at (not when it was made: see created_edtf). |
| `url` | none | The item's page at its holder. |
| `file_url` | none | Direct file or image link (a thumbnail for films). |
| `embed_url` | none | Embeddable player link (archive.org embed, with start time). |
| `credit` | catalogue | Credit line as the map shows it. |
| `licence` | none | Licence as the map recorded it; text longer than 80 characters is moved to `rights_note`. |
| `holder` | catalogue | Who holds the rights or the copy. |
| `use_policy` | none | How it may be used: `open`, `attribution`, `noncommercial`, `stream_only`, `link_only`, `ask_holder`, `unknown`. |
| `rights_note` | catalogue | What a reuser must know, by holder rule (e.g. WNP: ask before public use; Live Music Archive: non-commercial). |
| `source_identifier` | none | archive.org identifier, for archive.org items. |
| `used_in` | none | Where the map uses it (`timelines.json:<street>#<n>`, `blocks/<block>.json:<id>`, ...). |

### `orgs` (1 rows) (qipu_sf.db (table))

Organisations (v1: the Board).

| column | privacy | meaning |
|---|---|---|
| `org_id` | none | `qipu:sf/org/<slug>`. |
| `name` | organisation | Name. |
| `kind` | none | `agency`. |
| `wikidata` | none | Wikidata id. |

### `pages` (183,888 rows) (qipu_sf.db (table))

Every archive.org page cited. We point at the scans; we never copy them.

| column | privacy | meaning |
|---|---|---|
| `page_id` | none | The archive.org page: `<identifier>:<leafNum>` (IA's leaf number, the jp2 / IIIF number), or `<identifier>:d<n>` where only the BookReader index is known, or `<url>:<page>` for a web document. |
| `source_id` | none | archive.org identifier of the volume. |
| `leaf_num` | none | The page's OCR leaf (the jp2/IIIF file number), from the volume's leaf map; empty in volumes without one. |
| `display_n` | none | archive.org BookReader page index (the n in `/page/n<k>`). |
| `meeting_date` | none | Earliest dated claim on the page. |
| `scan_url` | none | Opens the page on archive.org. |
| `iiif_url` | none | The page image through IA's IIIF image service (`$<display_n + 1>`: that service counts the displayed pages from 1). v0.5 used the OCR leaf and showed the page before on about a third of pages; fixed in v0.6. |
| `page_exact` | none | True when the archive.org page number was converted from the OCR leaf with the per-volume leaf map; false = may be a page or two off. |
| `n_claims` | none | Claims citing the page. |

### `person_names` (9,251 rows) (qipu_sf.db (table))

Each name as printed in roll calls, with the years seen and the person it resolves to.

| column | privacy | meaning |
|---|---|---|
| `person_id` | none | The supervisor (see `persons`). |
| `name_printed` | public_official | The name as printed in the roll call. |
| `from` | none | First year this printed form appears in a roll call. |
| `to` | none | Last year this printed form appears in a roll call. |
| `n` | none | Roll-call votes with this printed form. |
| `match` | none | How this printed form was matched (see `x_roll_call_votes.match`). |

### `persons` (294 rows) (qipu_sf.db (table))

People resolved in v1: the members of the Board of Supervisors (official roster: the Wikipedia list, with Wikidata ids), plus four supervisors printed in the Journals but missing from that list (`journal_only`).

| column | privacy | meaning |
|---|---|---|
| `person_id` | none | `qipu:sf/person/<slug>`. |
| `display_name` | public_official | Name as on the roster. |
| `wikidata` | none | Wikidata id. |
| `wikidata_url` | none | The Wikidata page. |
| `wikipedia` | public_official | Title of the English Wikipedia article. |
| `is_public_official` | none | True on every row: all are supervisors. |
| `roster_source` | none | `wikipedia` (the Wikipedia list of SF supervisors) or `journal_only` (a surname printed in 100+ roll calls that the list lacks). |
| `first_year` | none | First year served (`journal_only`: first year the name is printed). |
| `last_year` | none | Last year served (`journal_only`: last year the name is printed). |

### `place_names` (2,274 rows) (qipu_sf.db (table))

Names of places (official, alias). The historic names index starts here; v1 holds today's names and the search aliases.

| column | privacy | meaning |
|---|---|---|
| `place_id` | none | The place (see `places`). |
| `name` | none | The name. |
| `name_kind` | none | `official` (street and neighbourhood names from DataSF EAS) or `alias` (a name matched in Internet Archive full-text search). |
| `from` | none | Empty in v0.4. Meant for the first year the name was in use. |
| `to` | none | Empty in v0.4. Meant for the last year the name was in use. |
| `source` | none | Where the name comes from. |

### `place_records` (2,005,407 rows) (qipu_sf.db (table))

Ready-joined: every record about a place (decisions, claims, media), one line each, with its link.

| column | privacy | meaning |
|---|---|---|
| `place_id` | none | Where (a qipu place id; `qipu:sf/place/city` for citywide). |
| `when` | none | When (YYYY, YYYY-MM or YYYY-MM-DD). |
| `kind` | none | The kind of record. |
| `what` | free_text | What was decided or recorded, in one line (model-written for Board items; check the scan). |
| `outcome` | none | passed / refused / rejected / vetoed / pending / unknown. |
| `precision` | none | How exactly the source places it. |
| `link` | none | Opens the source: the archive.org page for scans, else the source URL. |
| `source` | free_text | The source, in words. |
| `record_id` | none | The decision, claim subject or item id. |
| `from_tables` | none | The tables the row was read from. |
| `topic` | none | Topic label from the run. |

### `place_relations` (740,856 rows) (qipu_sf.db (table))

How places relate, computed from geometry each build (plus declared links): an address `within` a block, a block `within` a neighbourhood, a street `crosses` a creek ...

| column | privacy | meaning |
|---|---|---|
| `from_id` | none | place ids |
| `relation` | none | `within`, `overlaps`, `crosses`, `on_street` ... |
| `to_id` | none | place ids |
| `basis` | none | `geometry` or `declared` |
| `valid_from` | none | when the relation holds (historic places) |
| `valid_to` | none | when the relation holds (historic places) |
| `overlap_ratio` | none | the geometric measure behind it |
| `note` | none | how it was computed |

### `places` (470,596 rows) (qipu_sf.db (table))

Today's places with stable qipu ids.

| column | privacy | meaning |
|---|---|---|
| `place_id` | none | `qipu:sf/place/address/eas-<EAS id>`, `.../parcel/<block>-<lot>`, `.../block/<block>`, `.../street/<name-type>`, `.../street/<name>` (printed without type, `street_name`), `.../hood/<slug>`, `.../landmark/<slug>`, `.../city`. |
| `type_id` | none | Place type (see `place_types`): `parcel`, `address`, `assessor_block`, `street`, `street_name`, `zoning_district`, `landmark`, ... |
| `kind` | none | address, parcel, block, street, street_name, neighbourhood, landmark, city. |
| `name` | none | Readable name: the address (`26 REARDON RD`), `<block>/<lot> (<addresses>)`, `Assessor block <n>`, the street, neighbourhood or feature name. |
| `parent_id` | none | address -> parcel -> block -> neighbourhood -> city. |
| `eas_id` | none | DataSF Enterprise Addressing System base id. |
| `wikidata` | none | Wikidata id, for named features. |
| `gnis` | none | USGS GNIS feature id. |
| `lat` | none | Latitude: the EAS point for addresses, the mean of address points for parcels, blocks and streets, the Wikidata point for features. |
| `lon` | none | Longitude: the EAS point for addresses, the mean of address points for parcels, blocks and streets, the Wikidata point for features. |
| `radius_m` | none | Radius in metres of the circle qipu draws around a feature's Wikidata point. |
| `valid_from` | none | Empty in v1 (the historic place index comes later). |
| `valid_to` | none | Last day the place existed; only the 1906 burn zone has one. |
| `specificity` | none | `-log10` of the clickable footprint in m² (`place_geometries.hit_area_m2`); higher = smaller, ranked first by `places_at`. |
| `geometry_note` | none | Says when the geometry is approximate (a circle around a Wikidata point). |
| `street_id` | none | The street place (`qipu:sf/place/street/<name-type>`). |
| `block` | none | Assessor block. |
| `lot` | none | Assessor lot. |
| `hood` | none | DataSF Analysis Neighborhood (point in polygon). |
| `source` | none | Where the place comes from (`DataSF Enterprise Addressing System (3mea-di5p), PDDL`, `DataSF parcels`, Wikidata, ...). |

### `refused` (11,664 rows) (qipu_sf.db (table))

Ready-joined: decisions refused, rejected or vetoed, with their places.

| column | privacy | meaning |
|---|---|---|
| `year` | none | Year. |
| `decade` | none | The decade (1970 = 1970-1979). |
| `date` | none | The meeting date (YYYY-MM-DD) when known. |
| `outcome` | none | passed / refused / rejected / vetoed / pending / unknown. |
| `topic` | none | Topic label. |
| `plain` | free_text | One-line plain summary (model-written; check the scan). |
| `place` | free_text | The place as written. |
| `neighbourhood` | none | DataSF Analysis Neighborhood(s) of the places it names. |
| `scan_url` | none | Opens the scanned page on archive.org. |
| `decision_id` | none | The decision id. |
| `k` | none | Internal: duplicate rank (1 = kept). |

### `roles` (318 rows) (qipu_sf.db (table))

Supervisor terms (contiguous years served, from the roster).

| column | privacy | meaning |
|---|---|---|
| `person_id` | none | The person (see `persons`). |
| `role` | none | `supervisor`. |
| `org_id` | none | The body (see `orgs`). |
| `from` | none | First year of an unbroken run of years served. |
| `to` | none | Last year of that run. |
| `source` | none | `Wikipedia: Members of the San Francisco Board of Supervisors`, or `printed in the Journal roll calls only`. |

### `roll_calls` (171,123 rows) (qipu_sf.db (table))

Printed roll calls found in the Journal text, with printed and parsed counts and what was decided.

| column | privacy | meaning |
|---|---|---|
| `rollcall_id` | none | Id of the roll call: hash of the item id and the roll call's position in the item. |
| `decision_id` | none | The decision id. |
| `year` | none | Year. |
| `date` | none | Meeting date, where the corpus gives one within a year of the volume's year. |
| `outcome` | none | passed / refused / rejected / vetoed / pending / unknown. |
| `outcome_text` | free_text | The printed outcome phrase before "by the following vote" (`Adopted`, `Finally passed`, ...), up to 120 characters. |
| `ayes_n` | none | Count as printed. |
| `noes_n` | none | Noes count as printed. |
| `absent_n` | none | Absent count as printed. |
| `ayes_parsed` | none | Names parsed. |
| `noes_parsed` | none | Names parsed after Noes. |
| `absent_parsed` | none | Names parsed after Absent or Excused. |
| `counts_match` | none | True when every printed count equals the names parsed (94% of roll calls). |
| `what` | free_text | What was decided or recorded, in one line (model-written for Board items; check the scan). |
| `instrument_id` | none | The ordinance or resolution the roll call is on (see `instruments`). |
| `page_id` | none | The archive.org page: `<identifier>:<leafNum>` (IA's leaf number, the jp2 / IIIF number), or `<identifier>:d<n>` where only the BookReader index is known, or `<url>:<page>` for a web document. |
| `scan_url` | none | Opens the scanned page on archive.org. |
| `page_exact` | none | True when the archive.org page number was converted from the OCR leaf with the per-volume leaf map; false = may be a page or two off. |
| `context` | raw_text | Up to 300 characters of item text before the roll call. Empty (withheld in the public files for records under the privacy embargo (75 years in SF, 92 in Vancouver)). |
| `quote` | raw_text | The roll call as printed, from 60 characters before it (<= 600 characters). Names supervisors, who are public officials. |

### `runs` (43 rows) (qipu_sf.db (table))

What made each table: rules, hand work, or a model run (model, prompt).

| column | privacy | meaning |
|---|---|---|
| `run_id` | none | The run's id, as used in `run_id` elsewhere. |
| `kind` | none | How the run worked: `llm`, `rules`, `llm+hand`, `llm+rules`, `rules+hand`, `hand`, `search`. |
| `model` | none | Model used (`claude-haiku-5-5`, `claude-sonnet-5-5`); empty for rule, hand and search runs. |
| `prompt_version` | none | Prompt file or script the run used. |
| `schema_version` | none | Output schema version (`1`, `v2.1`) where set. |
| `settings` | none | Empty in v0.4. |
| `started` | none | When the run started (YYYY-MM-DD or YYYY-MM). |
| `pages` | none | Empty in v0.4. |
| `cost_usd` | none | Empty in v0.4. |
| `gold_scores` | none | Empty in v0.4. |
| `notes` | none | What the run made, in a line; model runs with no gold end with `[unmeasured: no gold for this run]`. |
| `gold_id` | none | The gold set the run was scored against (see `field_scores`); empty when unmeasured. |
| `prompt_id` | none | Same as `prompt_version`. |
| `extraction_table` | none | Empty in v0.4. |
| `city` | none | `sf` or `van`. |

### `showcase_items` (18,351 rows) (qipu_sf.db (table))

Ready-joined: what the Archive holds about each showcase place, across collections.

| column | privacy | meaning |
|---|---|---|
| `showcase` | none | The showcase place (slug). |
| `year` | none | Year. |
| `kind` | none | The kind of record. |
| `title` | free_text | Title (catalogue title for archive items; the plain line for decisions). |
| `collection` | none | The collection group. |
| `link` | none | Opens the source: the archive.org page for scans, else the source URL. |
| `rights_note` | catalogue | What a reuser must know about the source's rights, in words (archive.org search hits, or a licence text longer than 80 characters moved here from `licence`). |
| `linked_via` | none | How the item is linked to the place. |
| `confidence` | none | 0-1 where a run gives one (votes: 0.95 when the printed counts match the names). |
| `from_table` | none | The table the row came from. |
| `outcome` | none | passed / refused / rejected / vetoed / pending / unknown. |
| `variants` | none | Near-identical catalogue records folded into this line. |
| `tier` | none | `named` (matched by name) or `nearby` (by location only). |

### `sources` (6,377 rows) (qipu_sf.db (table))

Every source cited. archive.org items carry their collections and Democracy's Library membership.

| column | privacy | meaning |
|---|---|---|
| `source_id` | none | The source: an archive.org identifier, or `web:<url>` for other sources. |
| `title` | catalogue | Title from archive.org, else a citing claim's source label. |
| `kind` | none | board_journal, board_minutes, council_minutes, report, plan, eir, book, film, audio, photo, open_data, encyclopedia, map, web ... |
| `collection` | none | First archive.org collection of the item. |
| `collections` | none | All archive.org collections of the item. |
| `in_democracys_library` | none | True when one of the item's collections is in Democracy's Library scope: the DL pools (instituteofgovernmentalstudieslibrary, USGovernmentDocuments, fedlink, governmentpublications, us_census, nasa, ...) or one of the 830 child collections of `democracys-library` (DL_INVENTORY.md section 1). SF Public Library scans are not in DL. |
| `dl_rule` | none | Which collection made it DL, or why not. |
| `creator` | catalogue | Creator as given by archive.org. |
| `publisher` | catalogue | Publisher as given by archive.org. |
| `year_from` | none | Year of the item (archive.org date), else the earliest year of the claims citing it. |
| `year_to` | none | Latest year of the claims citing it, else the item's year. |
| `pages` | none | archive.org imagecount. |
| `licence` | none | archive.org licence URL or rights text (<= 300 characters); for web sources the citing claims' licence code. |
| `holder` | catalogue | archive.org contributor: the library that gave the copy (`San Francisco Public Library`, ...). |
| `on_archive_org` | none | True when the source is an archive.org item. |
| `url` | none | Link to the source: the archive.org item page, or the web page. |
| `n_claims` | none | Claims citing it. |

### `street_zoning` (13,666 rows) (qipu_sf.db (table))

Ready-joined: zoning decisions by street.

| column | privacy | meaning |
|---|---|---|
| `street_id` | none | The street place id. |
| `street` | none | Street name. |
| `year` | none | Year. |
| `date` | none | The meeting date (YYYY-MM-DD) when known. |
| `outcome` | none | passed / refused / rejected / vetoed / pending / unknown. |
| `plain` | free_text | One-line plain summary (model-written; check the scan). |
| `streets` | free_text | Streets the item names ("\|"-separated). |
| `place_text` | free_text | The place as written in the item. |
| `instrument_id` | none | Ordinance/resolution number printed in the item (see `instruments`). |
| `scan_url` | none | Opens the scanned page on archive.org. |
| `decision_id` | none | The decision id. |

### `supervisor_totals` (220 rows) (qipu_sf.db (table))

Ready-joined: votes on record per supervisor (yes / no / absent).

| column | privacy | meaning |
|---|---|---|
| `person_id` | none | `qipu:sf/person/<slug>`. |
| `display_name` | public_official | The supervisor. |
| `first_year` | none | First year served (`journal_only`: first year the name is printed). |
| `last_year` | none | Last year served (`journal_only`: last year the name is printed). |
| `votes_on_record` | none | Roll-call votes found. |
| `yes` | none | Yes votes. |
| `no` | none | No votes. |
| `absent` | none | Absent or excused. |
| `pct_no` | none | Share of no among yes + no, %. |
| `first_vote_year` | none | First roll call found. |
| `last_vote_year` | none | Last roll call found. |
| `wikidata_url` | none | The Wikidata page. |

### `table_counts` (28 rows) (qipu_sf.db (table))

The tables of this file, their row counts and what one row is.

| column | privacy | meaning |
|---|---|---|
| `table_name` | none | A table of this file. |
| `rows` | none | Its row count. |
| `about` | none | What one row is. |

### `vote_claims` (qipu_sf.db (view))

A view: every vote in claim shape (one supervisor, one roll call).

| column | privacy | meaning |
|---|---|---|
| `claim_id` | none | Stable id: `c:` + 32 hex (128 bits, hashed from the slot: subject, property, object, page, role); votes `v:<rollcall_id>:<n>`. |
| `subject_id` | none | What the statement is about: a decision (`qipu:sf/decision/...`), a place, a person (`qipu:sf/person/...`) or an archive item (`ia:<identifier>`). |
| `subject_label` | public_official | Readable label of the subject. |
| `property` | none | Controlled list: `outcome`, `voted`, `concerns_place`, `built`, `depicted_in`, `recorded_at`, `happened_at`, `ballot_result`. |
| `value_text` | none | Text value: the outcome, the vote (yes/no/absent/excused), the line as written. |
| `object_id` | none | The other entity, when there is one (the decision a supervisor voted on; the place an archive item concerns). |
| `t_from` | none | When: YYYY-MM-DD, YYYY-MM or YYYY. |
| `t_precision` | none | `day`, `month` or `year`. |
| `place_id` | none | Where (a qipu place id; `qipu:sf/place/city` for citywide). |
| `page_id` | none | The archive.org page: `<identifier>:<leafNum>` (IA's leaf number, the jp2 / IIIF number), or `<identifier>:d<n>` where only the BookReader index is known, or `<url>:<page>` for a web document. |
| `link` | none | Opens the source: the archive.org page for scans, else the source URL. |
| `quote` | raw_text | Short quote of the source (<= 300 characters). See `privacy_flag`. |
| `extraction_table` | none | Which table or file the statement came from (`bos_records`, `resweep`, `x_roll_calls`, `map_blocks`, `site_decisions`, ...). |
| `run_id` | none | The extraction run (see `runs`). |
| `confidence` | none | 0-1 where a run gives one (votes: 0.95 when the printed counts match the names). |
| `status` | none | `extracted` (machine-read), `record` (from an official register: assessor, DataSF), `curated` (written or checked by qipu for the map). No `verified` rows yet. |

### `votes` (2,071,147 rows) (qipu_sf.db (table))

One row per supervisor per printed roll call (stored by person, then date).

| column | privacy | meaning |
|---|---|---|
| `person_id` | none | The supervisor the name was matched to (see `persons`); empty when `unmatched` or `ambiguous`. |
| `year` | none | Year. |
| `date` | none | The meeting date. |
| `vote` | none | `aye`, `no`, `absent`, `excused` or `not_voting`. |
| `rollcall_id` | none | The roll call (see `x_roll_calls`). |
| `name_printed` | public_official | The name as printed in the roll call. |
| `result` | none | The roll-call outcome. |
| `tally` | none | Printed yes-no counts. |
| `what` | free_text | What was decided or recorded, in one line (model-written for Board items; check the scan). |
| `instrument_id` | none | The ordinance or resolution voted on. |
| `page` | none | Opens the page on archive.org. |

### `x_appraisals` (3,189 rows) (qipu_sf.db (table))

Extraction table: the SF Redevelopment Agency property files of the Western Addition A-2 project (SFPL scans on archive.org), one row per parcel page (claude-haiku-5-5, sfra_extract.py), matched to today's parcels by sfra_build.py.

| column | privacy | meaning |
|---|---|---|
| `run_id` | none | `sfra-appraisals-haiku55-2026-10` (see `runs`). |
| `page_id` | none | The archive.org page: `<identifier>:<leafNum>` (IA's leaf number, the jp2 / IIIF number), or `<identifier>:d<n>` where only the BookReader index is known, or `<url>:<page>` for a web document. |
| `seq` | none | Line number of the row in the run's rows file. |
| `item` | none | archive.org identifier of the property file (`SFH371-...`). |
| `page` | none | archive.org BookReader page index in the item. |
| `url` | none | Link to the page on archive.org. |
| `year` | none | Year of the report (for a sale, the sale year when it is close). |
| `block` | none | Assessor block of today it was matched to. |
| `lot` | none | Assessor lot, when matched to an address. |
| `precision` | none | `address` (door matched to an EAS address), `block` (matched by its street hundred), `near` (old door gone: nearest door of today within 60 m), `printed block`. |
| `address_printed` | free_text | Address as printed, read by a model from the page image and OCR. |
| `block_printed` | none | Block as printed, read by a model from the page image and OCR. |
| `lot_printed` | none | Lot as printed, read by a model from the page image and OCR. |
| `kind_page` | none | Kind of page: `appraisal`, `sale`, `property_summary`, `folder`, `interview`, `rent_schedule`. |
| `building` | free_text | Building description as printed, read by a model from the page image and OCR. |
| `units` | none | Units or rooms as printed, read by a model from the page image and OCR. |
| `rent_example` | none | A sample rent line as printed, read by a model from the page image and OCR. |
| `rent_total` | none | Total rent as printed, read by a model from the page image and OCR. |
| `appraised_value` | none | Appraised value as printed, read by a model from the page image and OCR. |
| `assessed_total` | none | Total assessed value as printed, read by a model from the page image and OCR. |
| `sale_price` | none | Sale price as printed (sales study pages), read by a model from the page image and OCR. |
| `sale_date` | none | Sale date as printed, read by a model from the page image and OCR. |
| `present_use` | free_text | Present use as printed (`Flats`, `Apartments`), read by a model from the page image and OCR. |
| `owner` | person_name | Owner as printed (for a sale, the grantee). Under the 75-year privacy filter the public files show `a owner (name withheld until <year>)`. |
| `confidence` | none | The model's legibility rating: `high` or `medium` (`low` rows are dropped). |
| `text` | free_text | Plain line built from the fields (`Redevelopment Agency appraisal: 3-story frame structure; appraised at $...`). Person-like names are withheld for records under the 75-year privacy filter. |

### `x_dl_tags` (1,131 rows) (qipu_sf.db (table))

Extraction table: Democracy's Library (IGS) items about SF, with the neighbourhoods, streets and places each is about (claude-haiku-5-5 on metadata + first OCR, dl_tag.py).

| column | privacy | meaning |
|---|---|---|
| `run_id` | none | `dl-tags-haiku55-2026-10` (see `runs`). |
| `identifier` | none | archive.org identifier of the document. |
| `url` | none | The item on archive.org. |
| `about_sf` | none | True when the document is about San Francisco, chosen by a model from the catalogue record and the start of the OCR. |
| `doc_type` | none | Document type (`study or report`, `plan`, `environmental impact report`, `budget`, ...), chosen by a model from the catalogue record and the start of the OCR. |
| `scope` | none | `citywide`, `neighbourhoods`, `site`, `regional` or `not San Francisco`, chosen by a model from the catalogue record and the start of the OCR. |
| `hoods` | none | Neighbourhoods (from the map's fixed list) the document is about, chosen by a model from the catalogue record and the start of the OCR. |
| `streets` | none | Streets the document is about, chosen by a model from the catalogue record and the start of the OCR. |
| `places` | none | Named sites or facilities the document is about (at most 4), chosen by a model from the catalogue record and the start of the OCR. |
| `evidence` | free_text | Short quote (<= 160 characters) from the title or text showing what the document is about, picked by a model. |

## Claim reliability (`claim_reliability`)

A claim says what a record states, not that it is true. Truth needs corroboration. Every claim gets a tier from signals the build can check; the tier says how much evidence stands behind the claim, not whether it is true.

| tier | when | what it does NOT mean |
|---|---|---|
| verified | a person checked it against the scan (a hand-check verdict "right"), or 2+ independent documents (different `source_id`) state the same subject, property, value and date | that it is true: two records can repeat one error |
| quoted | the quote is in the cited page's OCR (first words in order, 80% of its words), the run measured >= 0.9, no consistency check failed | that the reading is right: the run's score is an average |
| unverified | everything else; `tier_reason` says why | that it is wrong |

Signals: `quote_check` (found, not_found, no_ocr, no_quote); `run_accuracy` with `run_accuracy_against` (human, model key or model scan check, always shown separately) and `run_accuracy_n`; `support_count` (readings of the same page, not independent); `independent_sources`; `checks_failed` (printed tally against the extracted counts or outcome, OCR tally, a date outside the volume); `human_verdict`; `flag_status` (a pending flag changes nothing; only an accepted correction downgrades a claim).

Counts in v0.7.1 (`SELECT tier, count(*) FROM claim_reliability GROUP BY 1`):

| tier | claims |
|---|---|
| unverified | 5,403,521 |
| quoted | 2,962,695 |
| verified | 889 |

| quote_check | claims |
|---|---|
| found | 5,300,057 |
| no_quote | 2,541,197 |
| not_found | 409,191 |
| no_ocr | 116,660 |

## Reliability by field

How each field was made and how well it is known. "Unmeasured" means no gold exists yet; nothing here is a guess.

| field | made by | reliability |
|---|---|---|
| x_roll_calls: counts (ayes/noes/absent) | rules (20_rollcalls.py) | Rule check on all 171,123 roll calls: printed counts equal the names parsed in 90.5% (1,402 print no counts). SF gold (decisions_bench/gold.jsonl, written by Opus 5.5, no record of a hand check): counts exact 40/46 (87%) where the roll call is in the item text; 12 of 58 gold roll calls not found (mostly outside the gold text span). |
| x_roll_call_votes.person_id (who voted) | rules: surname + years served, official roster | 99.4% of 2,084,507 printed names matched to a person (659 ambiguous, left empty). Gold names: 536/541 matched. Correctness of each match: unmeasured (no gold of names to persons yet); confidence in `claims` drops to 0.6 when the counts do not match. |
| claims voted: value_text (yes/no/absent) | rules | Same as the roll-call counts above; unmeasured separately. |
| decisions.block, concerns_place (block level) | rules (map block index) | Measured 2026-10-06 on 3,195 checks (accuracy.json): 88.9% on the exact block, 91.6% within one block face, 95.5% within 250 m. By tier: address 92.8%, corner 51.9%, segment 71.4%. Street-tier placements (a street, not a block) are not scored. |
| pages.display_n / page links | per-volume leaf map: the audited Journal map, IA scandata for the other items (qipu_data.leafmap_ia) | 179,043 of 180,775 pages are mapped OCR page -> viewer page (`page_exact`); the rest keep n = OCR page and may be a page or two off. iiif_url is $<display_n + 1> (that IIIF service counts the shown pages from 1). Gate L1 checks the numbering; L2 checks that a fixed sample of quotes is on the cited page. |
| decisions.topic / subtopic / action (bos_corpus) | rules + title classification (build_core_table.py) | unmeasured |
| decisions from the re-sweep: outcome, topic, plain, place (claude-haiku-5-5, RESWEEP_PROMPT.md) | LLM, one item at a time | unmeasured for this prompt. Indicative only (same model, page-level prompt, SF gold, L4_TEST.md): action 0.97, topics F1 0.84, places F1 0.89. |
| decisions.plain_best (the map's one-line summaries) | LLM + accuracy pass (CHECK_PROMPT.md) | unmeasured as a rate; lines the accuracy pass found wrong were rewritten or dropped. |
| instruments (numbers) | regex on the item text | unmeasured. OCR can garble numbers (1G2 for 162); those are not parsed. |
| address_resolution | exact text match to DataSF EAS | exact matches only, by construction; unmatched texts are not resolved. |
| sources.in_democracys_library | rule: the item's collections vs DL scope (DL_INVENTORY.md) | exact by definition, as of the metadata fetched 14/10/2026. |
| built (assessor year built), fire_1906, sanborn lines | official records (DataSF, Sanborn via OldInsuranceMaps) | record values, not extracted; the Sanborn line wording is a convention of the map (unmeasured). |
| dl_documents links (archive items to places) | IA full-text search + relevance score | unmeasured; `confidence` holds the search relevance score. |
| x_dl_tags (places a DL item is about) | LLM on metadata + first OCR | unmeasured (no gold of document places yet). |
| media rights_note | rules by holder, on top of the licence the map recorded | rule output; the item page is the authority. 17 items have no holder rule (rights not recorded: link only). |
| x_decisions v2 money currency (SF) | fixed in the build | the run's writer stamps CAD on every amount; SF amounts are relabelled USD in the release (run files unchanged); gate M1 fails any SF amount in CAD. |
| stories, timelines, doors, dateline | written for the map (LLM + hand review on demo streets) | unmeasured; each line cites its source. |
| x_decisions run sf-v21-haiku-1014 / van-v21-haiku-1014 (decisions v2) | LLM, one call per page | agreement with an Opus-written key (L4_TEST.md, SF gold; not hand-checked; n = 115, a Wilson 95% interval on 0.95 is about 0.89-0.98): finds 0.95, number 1.00, action 0.95, vote counts 0.99, all three 0.95, vote names 0.95, mover 0.93, money F1 0.80, places F1 0.66, parties F1 0.61, dates F1 0.55, related F1 0.17 (new fields scored against an Opus key, not hand-checked). |
| x_appraisals (SFRA property files), claude-haiku-5-5 | LLM, one call per page | measured on 18 pages (ia_records/sfra/bench/score.json): address 18/18, parcel 18/18, owner 18/18, present_use 17/18, rent 16/18, appraised_value 18/18. |

## Measured field scores (from `field_scores`)

| run | field | metric | value | gold |
|---|---|---|---|---|
| andrews-sonnet55-2026-10 | andrews owners | confirmed on the linked page image / checked (Wilson 95% 0.83-0.99); re-checked on v0.6 after the page-link fix (v0.5 links were one page off) | 0.97 | eval/model_scan_check (300 public claims re-read against the page image by claude-opus-5-5; no person checked them) |
| bos-corpus-2026-09 | outcomes | confirmed on the linked page image / checked (Wilson 95% 0.25-0.84) | 0.57 | eval/model_scan_check (300 public claims re-read against the page image by claude-opus-5-5; no person checked them) |
| bos-corpus-2026-09 | places | confirmed on the linked page image / checked (Wilson 95% 0.70-0.96) | 0.88 | eval/model_scan_check (300 public claims re-read against the page image by claude-opus-5-5; no person checked them) |
| dl-sf-facts-v1.1-haiku55-2026-10 | dl facts | confirmed on the linked page image / checked (Wilson 95% 0.79-0.96); re-checked on v0.6 after the page-link fix (v0.5 links were one page off) | 0.91 | eval/model_scan_check (300 public claims re-read against the page image by claude-opus-5-5; no person checked them) |
| rollcall-parser-v1 | counts | exact where the roll is in the text | 0.87 | decisions_bench/gold.jsonl roll calls (Opus-written key), scripts/22_rollcall_gold.py |
| rollcall-parser-v1 | names to persons | matched | 0.99 | decisions_bench/gold.jsonl roll calls (Opus-written key), scripts/22_rollcall_gold.py |
| rollcall-parser-v1 | votes | confirmed on the linked page image / checked (Wilson 95% 0.70-0.99) | 0.93 | eval/model_scan_check (300 public claims re-read against the page image by claude-opus-5-5; no person checked them) |
| sf-v21-haiku-1014 | action | exact | 0.95 | decisions_bench/gold.jsonl (SF, 40 pages: an Opus-5.5-written key, no record of a hand check) + v2 Opus key |
| sf-v21-haiku-1014 | dates | F1 (vs Opus key) | 0.55 | decisions_bench/gold.jsonl (SF, 40 pages: an Opus-5.5-written key, no record of a hand check) + v2 Opus key |
| sf-v21-haiku-1014 | finds | recall | 0.95 | decisions_bench/gold.jsonl (SF, 40 pages: an Opus-5.5-written key, no record of a hand check) + v2 Opus key |
| sf-v21-haiku-1014 | interest | Spearman | 0.78 | decisions_bench/gold.jsonl (SF, 40 pages: an Opus-5.5-written key, no record of a hand check) + v2 Opus key |
| sf-v21-haiku-1014 | money | F1 (vs Opus key) | 0.80 | decisions_bench/gold.jsonl (SF, 40 pages: an Opus-5.5-written key, no record of a hand check) + v2 Opus key |
| sf-v21-haiku-1014 | money | confirmed on the linked page image / checked (Wilson 95% 0.85-0.99) | 0.96 | eval/model_scan_check (300 public claims re-read against the page image by claude-opus-5-5; no person checked them) |
| sf-v21-haiku-1014 | mover | exact | 0.93 | decisions_bench/gold.jsonl (SF, 40 pages: an Opus-5.5-written key, no record of a hand check) + v2 Opus key |
| sf-v21-haiku-1014 | number | exact | 1.00 | decisions_bench/gold.jsonl (SF, 40 pages: an Opus-5.5-written key, no record of a hand check) + v2 Opus key |
| sf-v21-haiku-1014 | number+action+votes | all exact | 0.95 | decisions_bench/gold.jsonl (SF, 40 pages: an Opus-5.5-written key, no record of a hand check) + v2 Opus key |
| sf-v21-haiku-1014 | outcomes | confirmed on the linked page image / checked (Wilson 95% 0.73-0.94) | 0.87 | eval/model_scan_check (300 public claims re-read against the page image by claude-opus-5-5; no person checked them) |
| sf-v21-haiku-1014 | parties | F1 (vs Opus key) | 0.61 | decisions_bench/gold.jsonl (SF, 40 pages: an Opus-5.5-written key, no record of a hand check) + v2 Opus key |
| sf-v21-haiku-1014 | places | confirmed on the linked page image / checked (Wilson 95% 0.70-0.97) | 0.90 | eval/model_scan_check (300 public claims re-read against the page image by claude-opus-5-5; no person checked them) |
| sf-v21-haiku-1014 | places | F1 (vs Opus key) | 0.66 | decisions_bench/gold.jsonl (SF, 40 pages: an Opus-5.5-written key, no record of a hand check) + v2 Opus key |
| sf-v21-haiku-1014 | related | F1 (vs Opus key) | 0.17 | decisions_bench/gold.jsonl (SF, 40 pages: an Opus-5.5-written key, no record of a hand check) + v2 Opus key |
| sf-v21-haiku-1014 | topics | F1 | 0.90 | decisions_bench/gold.jsonl (SF, 40 pages: an Opus-5.5-written key, no record of a hand check) + v2 Opus key |
| sf-v21-haiku-1014 | vote names | exact | 0.95 | decisions_bench/gold.jsonl (SF, 40 pages: an Opus-5.5-written key, no record of a hand check) + v2 Opus key |
| sf-v21-haiku-1014 | votes | confirmed on the linked page image / checked (Wilson 95% 0.85-0.99) | 0.97 | eval/model_scan_check (300 public claims re-read against the page image by claude-opus-5-5; no person checked them) |
| sf-v21-haiku-1014 | votes (counts) | exact | 0.99 | decisions_bench/gold.jsonl (SF, 40 pages: an Opus-5.5-written key, no record of a hand check) + v2 Opus key |
| sfra-appraisals-haiku55-2026-10 | address | normalized exact (18 pages) | 1.00 | ia_records/sfra/bench (18 pages) |
| sfra-appraisals-haiku55-2026-10 | appraised_value | normalized exact (18 pages) | 1.00 | ia_records/sfra/bench (18 pages) |
| sfra-appraisals-haiku55-2026-10 | owner | normalized exact (18 pages) | 1.00 | ia_records/sfra/bench (18 pages) |
| sfra-appraisals-haiku55-2026-10 | parcel | normalized exact (18 pages) | 1.00 | ia_records/sfra/bench (18 pages) |
| sfra-appraisals-haiku55-2026-10 | present_use | normalized exact (18 pages) | 0.94 | ia_records/sfra/bench (18 pages) |
| sfra-appraisals-haiku55-2026-10 | rent | normalized exact (18 pages) | 0.89 | ia_records/sfra/bench (18 pages) |
| sfra-appraisals-haiku55-2026-10 | sfra appraisals | confirmed on the linked page image / checked (Wilson 95% 0.74-0.95), after 6 checker-prompt errors were corrected (see ACCURACY_v0.6.md) | 0.89 | eval/model_scan_check (300 public claims re-read against the page image by claude-opus-5-5; no person checked them) |
| van-v21-haiku-1014 | money | F1 (vs Opus key) | 0.73 | decisions_bench Vancouver gold (30 pages, 48 decisions: Opus-written, 21 decisions hand-checked) |
| van-v21-haiku-1014 | mover | exact | 1.00 | decisions_bench Vancouver gold (30 pages, 48 decisions: Opus-written, 21 decisions hand-checked) |
| van-v21-haiku-1014 | number+action+votes | all exact | 0.98 | decisions_bench Vancouver gold (30 pages, 48 decisions: Opus-written, 21 decisions hand-checked) |
| van-v21-haiku-1014 | vote names | exact | 0.98 | decisions_bench Vancouver gold (30 pages, 48 decisions: Opus-written, 21 decisions hand-checked) |

## Public SQLite copy (`qipu_sf.db`)

The same tables for browsing, plus ready-joined tables for the showcase questions (`place_records`, `street_zoning`, `refused`, `hood_archive_items`, `supervisor_totals`) and compact `votes` / `roll_calls` with the view `vote_claims` (votes in claim shape). In this copy the outcome claims of Board items live in `decisions`, and Board quotes from 1952 on are left out (privacy rule).