# SCHEMA.md — tidy relational CSV exports

Generated 2026-07-27T19:57:08.674Z by `scripts/build-release.mjs` from the live corpus
(`src/lib/data/cases.js`, itself built from `src/lib/data/legacy-corpus.json` and
`src/lib/data/corpus-additions.json`). These are analysis-ready flat exports: one row per
observation, joined on `slug` (the case record's stable identifier, also its `/case/<slug>`
route). All 1283 case records are represented — every `recordClass`
(core, institutional-event, historical, context), not just the headline "core" tally. Filter
on `is_core` if you want only the 1028 records that passed the site's inclusion
criterion. The separate `historicalCases` register (dossiers with no per-case sources/
proceedings structure) is NOT included in these CSVs — use the JSON releases for that.

Full field definitions and coding rules: `schema/boundbyfear.schema.json` and
`schema/CODEBOOK.md` in the source repository.

## cases.csv (1283 rows — one per case record)

Join key: `slug` (primary key of this table; foreign key on every other table below).

| column | type | description |
|---|---|---|
| slug | string | Stable unique identifier; join key for all tables in this export. |
| year | int | Year of the primary documented event or earliest anchoring date. |
| place | string | Free-text place, typically "City, Region, Country". Not a normalized jurisdiction code. |
| lat | float | Latitude of `place`, decimal degrees. |
| lng | float | Longitude of `place`, decimal degrees. |
| title | string | Display title for the case. |
| tradition | string | Coarse tradition code (enum), e.g. "christian", "islam", "juju". |
| traditionDetailId | string | Fine-grained tradition subdivision, e.g. "christian-catholic". |
| type | string | Offense/event category (enum), e.g. "sexual-abuse", "trafficking". |
| recordClass | string | "core", "institutional-event", "historical", or "context". |
| evidenceStatus | string | Normalized evidentiary status, e.g. "convicted", "charged", "official-finding". |
| status | string | Legacy free-text disposition label; not authoritative, use evidenceStatus. May be blank. |
| disposition_status | string | Current record-specific disposition after verification. Blank until backfilled. |
| disposition_last_checked | date | Date the disposition was last checked against a source. Blank until backfilled. |
| figure_status | string | "public", "private", or "unknown"; unknown is the safe default. |
| spiritualNexus | string | Published sentence(s) on the spiritual doctrine/authority/pretext connected to the harm. |
| scopeNote | string | Free-text editorial note scoping what the record does/does not establish. Often blank. |
| sourceAuditStatus | string | Open-vocabulary tracker of source-review state, e.g. "source-of-record". |
| legacyRecord | bool | True for the 71 records sourced from legacy-corpus.json. |
| additionIndex | int | Insertion-order rank (not a date); 0 for all legacy records. |
| decade | int | Derived: `floor(year / 10) * 10`, e.g. 1990. |
| country | string | Derived: trailing comma-separated component of `place`. Approximation, not a normalized jurisdiction code — see COVERAGE.md's country-coverage caveat. |
| is_core | bool | Derived: `recordClass === "core"`. |
| is_convicted | bool | Derived: `evidenceStatus === "convicted"`. |

## sources.csv (2173 rows — one per cited source)

Join key: `slug` → cases.csv.slug (many sources per case).

| column | type | description |
|---|---|---|
| slug | string | Foreign key to cases.csv. |
| link | string | Live URL of the source. |
| archive | string | Wayback Machine (or similar) archive URL. May be blank. |
| citation | string | Human-readable citation string. |
| tier | string | Open vocabulary for evidentiary weight, e.g. "prosecuting-authority", "court-reporting". Not a closed enum. May be blank. |
| kind | string | Open vocabulary for document type, e.g. "sentencing-report". Not a closed enum. May be blank. |

`body` (the quoted/paraphrased source excerpt) is deliberately EXCLUDED from this CSV — it is
long prose that breaks CSV ergonomics for spreadsheet/dataframe review. It remains available in
the JSON releases (`boundbyfear-full-v1.json`).

## proceedings.csv (1402 rows — one per legal/institutional proceeding)

Join key: `slug` → cases.csv.slug (many proceedings per case).

| column | type | description |
|---|---|---|
| slug | string | Foreign key to cases.csv. |
| jurisdiction | string | Court, tribunal, or investigating body. |
| stage | string | Procedural stage, e.g. "guilty plea and federal sentence", "appeal". |
| date | string | ISO 8601 date (YYYY-MM-DD) of this proceeding stage. May be blank. |
| outcome | string | Plain-language statement of what happened at this stage. |
| key | string | Synthetic row identifier, `${slug}#${index}` (0-based position within the case's proceedings array). Not present in the source JSON; added here so each row has a stable per-table primary key distinct from the slug foreign key. |

## appellate_history.csv (356 rows — one per appellate-history entry)

Join key: `slug` → cases.csv.slug (many entries per case). Populated on a minority of cases.

Post-fix (2026-07): every `appellateHistory` entry in the source corpus is now an object
(schema/boundbyfear.schema.json's `appellateHistoryEntry`). 62 historical bare-string entries
were normalized to `{ note: "<original text, verbatim>" }` — nothing here is inferred or
back-filled; narrative-only rows simply have every column blank except `slug` and `note`.

| column | type | description |
|---|---|---|
| slug | string | Foreign key to cases.csv. |
| date | string | Date of this appellate development, free text. May be blank. |
| court | string | Court or tribunal that issued this appellate development. May be blank. |
| jurisdiction | string | Jurisdiction of the appellate body. May be blank. |
| stage | string | Procedural stage label, e.g. "appeal", "special appeal". May be blank. |
| outcome | string | Plain-language statement of what happened. Falls back to the source's `result` key on the one legacy entry that used that name instead of `outcome` (2020-nunez-v-watchtower-thompson-falls-montana-usa). May be blank. |
| note | string | Narrative prose for entries that do not decompose into the structured columns above. All 62 string-normalized entries populate only this column. May be blank. |

## Long-format link tables (one row per case × value)

These four tables normalize array-valued fields on the case record into one-row-per-value link
tables, the standard shape for GROUP BY / join analysis. Join key on every one: `slug` →
cases.csv.slug.

| file | rows | value column | source field |
|---|---|---|---|
| case_pretext_categories.csv | 2237 | pretextCategoryId | `pretextCategoryIds` — closed enum, 14 categories. Safe to GROUP BY directly. |
| case_authority_roles.csv | 1552 | authorityRole | `authorityRoles` — **free text**, 1420 distinct values in this export. |
| case_coercion_mechanisms.csv | 2681 | coercionMechanism | `coercionMechanisms` — **free text**, 2463 distinct values in this export. |
| case_organizations.csv | 1067 | organization | `organizations` — free text, one entry coined per organization as documented; not deduplicated against a canonical entity list in this export. |

**`authorityRoles` and `coercionMechanisms` are FREE TEXT, not a closed vocabulary.** Each was
written to describe the specific record rather than chosen from a fixed list, so
`case_authority_roles.csv` has 1420 distinct `authorityRole` values and
`case_coercion_mechanisms.csv` has 2463 distinct `coercionMechanism`
values across 1283 cases. **Do not GROUP BY these columns expecting clean categories**
— near-duplicate phrasings ("Catholic priest" vs "Roman Catholic priest" vs "parish priest") are
distinct strings today. Normalizing them into a closed vocabulary is future work, not something
this export attempts. `pretextCategoryIds` (case_pretext_categories.csv) IS a closed 14-value
enum and is safe to GROUP BY as-is.