# MCRO Forensic Database — Live Schema Reference
*Generated live from PostgreSQL `information_schema` and `pg_catalog`*  
*Supabase project: `ibfmjtwahkwqzcmeyqii` · Region: us-west-2*  
*Queried: 2026-02-28*

---

## Database Summary

| Metric | Value |
|--------|-------|
| Total base tables | 53 |
| Total views | 11 |
| Total rows (approx) | ~2.9 million |
| Core forensic PDF corpus | 4,251 documents |
| Cases in registry | 9,146 |
| Cases with full scrape (parents) | 2,903 |
| Docket events | 155,357 |
| Text rows (hashpack_rows) | 606,037 |
| Row tags | 1,156,980 |

---

## Architecture Overview

The schema uses two parallel hierarchies rooted at `case_registry`:

- **`children` tree** — PDF forensic analysis (everything extracted from inside the PDFs)
- **`parents` tree** — MCRO case data (everything scraped from the case pages)
- **`hashpack` tree** — Content hashing (text rows, page streams, object hashes)
- **`source` tree** — Scrape provenance (session tracking, file artifacts, OTS anchoring)

```
case_registry  [9,146 cases — master hub]
│
├── children  [4,251 PDFs — evidence_uid is primary forensic key]
│   ├── children_objects              (35,326)  — PDF internal objects w/ SHA256
│   ├── children_signatures_full_report (5,425) — per-sig crypto detail
│   ├── children_tracking_groups      (1,942)   — tracking font assignments
│   ├── children_doc_strings          (181,556) — extracted text rows
│   ├── children_doc_set              (14,244)  — document set membership
│   ├── children_case_set             (25,964)  — multi-case filing map
│   └── children_address              (1,718)   — MCRO download URL provenance
│
├── hashpack  [4,251 — 1:1 mirror of children]
│   ├── hashpack_rows                 (606,037) — row-level SHA256 hashes
│   ├── hashpack_rows_tags            (1,156,980)— row classification tags
│   ├── hashpack_page_content_streams (32,485)  — per-page stream hashes
│   ├── hashpack_streams_rows         (238,087) — stream↔row mapping
│   └── hashpack_outline_lines        (2,154)   — PDF bookmarks/outline
│
├── parents  [2,903 fully scraped cases]
│   ├── parents_case_events           (155,357) — full docket log ← Rule 20 analysis
│   │   ├── parents_case_events_judicial_officers (36,001)
│   │   ├── parents_case_events_examiners         (155,357)
│   │   ├── parents_case_events_evidence_uids     (4,206) — event↔PDF links
│   │   └── parents_case_events_attorneys         (2,409)
│   ├── parents_hearings_previous     (30,858)
│   ├── parents_hearings_previous_judicial_officers (30,868)
│   ├── parents_hearings_upcoming     (863)
│   ├── parents_attorneys_defense_active    (3,415)
│   ├── parents_attorneys_defense_inactive  (5,010)
│   ├── parents_attorneys_prosecution_active (7,362)
│   ├── parents_attorneys_prosecution_inactive (1,159)
│   ├── parents_charges               (4,277)
│   ├── parents_dispositions          (5,088)
│   │   └── parents_dispositions_charges (6,955)
│   │       ├── parents_dispositions_charges_indexed           (6,955)
│   │       ├── parents_dispositions_charges_condition_adult   (21,679)
│   │       ├── parents_dispositions_charges_local_confinement (10,822)
│   │       ├── parents_dispositions_charges_monitoring_adult  (9,697)
│   │       ├── parents_dispositions_charges_service_adult     (6,425)
│   │       ├── parents_dispositions_charges_level_of_sentence (5,605)
│   │       ├── parents_dispositions_charges_diversion_program_adult (5,657)
│   │       ├── parents_dispositions_charges_concurrent_consecutive  (6,235)
│   │       └── parents_dispositions_charges_commit_commissioner_corrections (6,870)
│   ├── parents_cluster_cases         (25,996)
│   ├── parents_case_set              (6,476)
│   ├── parents_doc_set               (2,903)
│   ├── parents_interim_conditions    (7,970)
│   └── parents_related_cases         (4,858)
│
└── source  [14 scrape sessions]
    ├── source_cases          (1,263)
    ├── source_files          (644)   ← evidence_uid link to children
    ├── source_hearings       (11,645)
    └── source_file_artifacts (564)   ← SHA256 of MITMPROXY/OBS/OTS files
```

---

## Primary Keys

| Table | Primary Key |
|-------|------------|
| `case_registry` | `case_uid` |
| `children` | `evidence_uid` |
| `children_address` | `address_row_id` |
| `children_case_set` | `children_case_set_row_id` |
| `children_doc_set` | `children_doc_set_row_id` |
| `children_doc_strings` | `children_doc_strings_row_id` |
| `children_objects` | `children_objects_row_id` |
| `children_signatures_full_report` | `children_signatures_row_id` |
| `children_tracking_groups` | `children_tracking_groups_row_id` |
| `hashpack` | `evidence_uid` |
| `hashpack_outline_lines` | `hashpack_outline_lines_row_id` |
| `hashpack_page_content_streams` | `hashpack_page_content_streams_row_id` |
| `hashpack_rows` | `hashpack_rows_row_id` |
| `hashpack_rows_tags` | `hashpack_rows_tags_row_id` |
| `hashpack_streams_rows` | `hashpack_streams_rows_row_id` |
| `parents` | `case_uid` |
| `parents_attorneys_defense_active` | `(case_uid, attorney_index)` |
| `parents_attorneys_defense_inactive` | `(case_uid, attorney_index)` |
| `parents_attorneys_prosecution_active` | `(case_uid, attorney_index)` |
| `parents_attorneys_prosecution_inactive` | `(case_uid, attorney_index)` |
| `parents_case_events` | `(case_uid, event_index)` |
| `parents_case_events_attorneys` | `pce_att_row_id` |
| `parents_case_events_evidence_uids` | `(case_uid, event_index, evidence_uid)` |
| `parents_case_events_examiners` | `pce_ex_row_id` |
| `parents_case_events_judicial_officers` | `pce_jo_row_id` |
| `parents_case_set` | `parents_case_set_row_id` |
| `parents_charges` | `parents_charges_row_id` |
| `parents_cluster_cases` | `parents_cluster_cases_row_id` |
| `parents_dispositions` | `parents_dispositions_row_id` |
| `parents_dispositions_charges` | `pdc_row_id` |
| `parents_dispositions_charges_commit_commissioner_corrections` | `row_id` |
| `parents_dispositions_charges_concurrent_consecutive` | `parents_dispositions_charges_concurrent_consecutive_row_id` |
| `parents_dispositions_charges_condition_adult` | `parents_dispositions_charges_condition_adult_row_id` |
| `parents_dispositions_charges_diversion_program_adult` | `parents_dispositions_charges_diversion_program_adult_row_id` |
| `parents_dispositions_charges_indexed` | `parents_dispositions_charges_indexed_row_id` |
| `parents_dispositions_charges_level_of_sentence` | `parents_dispositions_charges_level_of_sentence_row_id` |
| `parents_dispositions_charges_local_confinement` | `parents_dispositions_charges_local_confinement_row_id` |
| `parents_dispositions_charges_monitoring_adult` | `parents_dispositions_charges_monitoring_adult_row_id` |
| `parents_dispositions_charges_service_adult` | `parents_dispositions_charges_service_adult_row_id` |
| `parents_doc_set` | `case_uid` |
| `parents_hearings_previous` | `(case_uid, hearing_index)` |
| `parents_hearings_previous_judicial_officers` | `phpjo_row_id` |
| `parents_hearings_upcoming` | `(case_uid, hearing_index)` |
| `parents_interim_conditions` | `parents_interim_conditions_row_id` |
| `parents_related_cases` | `parents_related_cases_row_id` |
| `source` | `source_set_id` |
| `source_cases` | `source_cases_row_id` |
| `source_file_artifacts` | `source_sha256` |
| `source_files` | `source_files_row_id` |
| `source_hearings` | `source_hearings_row_id` |
| `type_plan_canonical` | `(table_name, column_name)` |

---

## Key Foreign Key Relationships

| From Table | From Column(s) | → To Table | To Column(s) |
|------------|---------------|------------|-------------|
| `children` | `case_uid` | `case_registry` | `case_uid` |
| `children_*` (all) | `evidence_uid` | `children` | `evidence_uid` |
| `children_*` (all) | `case_uid` | `case_registry` | `case_uid` |
| `hashpack` | `evidence_uid` | `children` | `evidence_uid` (1:1) |
| `hashpack` | `case_uid` | `case_registry` | `case_uid` |
| `hashpack_*` (all) | `evidence_uid` | `hashpack` | `evidence_uid` |
| `parents` | `case_uid` | `case_registry` | `case_uid` |
| `parents_*` (all) | `case_uid` | `parents` | `case_uid` |
| `parents_case_events` | `case_uid` | `parents` | `case_uid` |
| `parents_case_events_*` | `(case_uid, event_index)` | `parents_case_events` | `(case_uid, event_index)` |
| `parents_case_events_evidence_uids` | `evidence_uid` | `children` | `evidence_uid` |
| `parents_hearings_previous_judicial_officers` | `(case_uid, hearing_index)` | `parents_hearings_previous` | `(case_uid, hearing_index)` |
| `parents_dispositions_charges` | `(case_uid, disposition_index)` | `parents_dispositions` | `(case_uid, disposition_index)` |
| `parents_dispositions_charges_*` | `(case_uid, disposition_index, charge_index)` | `parents_dispositions_charges` | same |
| `source_cases` | `case_uid` | `case_registry` | `case_uid` |
| `source_cases` | `source_set_id` | `source` | `source_set_id` |
| `source_files` | `source_set_id` | `source` | `source_set_id` |
| `source_files` | `source_sha256` | `source_file_artifacts` | `source_sha256` |
| `source_hearings` | `case_uid` | `case_registry` | `case_uid` |
| `source_hearings` | `source_set_id` | `source` | `source_set_id` |

---

## Key Column Reference — Core Tables

### `case_registry`
Central hub. Every case_uid in the entire database exists here.

| Column | Type | Notes |
|--------|------|-------|
| `case_uid` | text | **PK** · Format: `case:27-cr-23-1886` |
| `case_id` | text | NN · Format: `27-CR-23-1886` · indexed |
| `defendant_name` | text | |
| `cluster_id` | integer | Defendant identity cluster |
| `cluster_name` | text | Normalized defendant name for cluster |

---

### `children`
One row per PDF. Primary forensic analysis table. 4,251 rows.

| Column | Type | Notes |
|--------|------|-------|
| `evidence_uid` | text | **PK** · Format: `evidence:27-cr-23-1886:6e296082826a09d2` |
| `case_uid` | text | FK → case_registry |
| `case_id` | text | |
| `case_type` | text | |
| `doc_sha256` | text | SHA256 of the PDF file · indexed |
| `doc_type` | text | |
| `filename` | text | Original MCRO filename |
| `parent_filename` | text | |
| `me_filename` | text | |
| `filing_date` | date | |
| `filing_type` | text | e.g. "Order-Other", "Finding of Incompetency and Order" |
| `pdf_page_count` | integer | |
| `size_bytes` | integer | |
| `cluster_id` | integer | Denormalized from case_registry |
| `cluster_name` | text | |
| `case_event_uid` | text | indexed |
| `case_event__*` | various | Denormalized event context fields |
| `metadata__xmp_create_date` | timestamptz | XMP authoring date |
| `metadata__xmp_creator_tool` | text | e.g. "Acrobat PDFMaker 25 for Word" |
| `metadata__xmp_producer` | text | e.g. "Adobe PDF Library 25.1.208; modified using iText..." |
| `metadata__xmp_modify_date` | timestamptz | |
| `metadata__msip__has_msip_flag` | boolean | **MSIP label present** |
| `metadata__msip__msip_label_name_primary` | text | "High" or "Moderate" |
| `metadata__msip__msip_site_id_primary` | text | M365 tenant ID |
| `metadata__msip__msip_label_token_primary` | text | Label policy token |
| `metadata__msip__msip_set_date_primary` | timestamptz | When label was applied |
| `metadata__msip__msip_action_id_primary` | text | |
| `metadata__msip__msip_method_primary` | text | "Standard" |
| `metadata__msip__msip_enabled_primary` | text | |
| `metadata__msip__msip_content_bits_primary` | integer | |
| `signatures__has_signature` | boolean | Any digital sig present |
| `mcro_watermark__*` | various | MCRO download watermark fields |
| `ironpdf__*` | various | IronPDF detection fields |

---

### `children_signatures_full_report`
One row per signature field per PDF. 5,425 rows.

| Column | Type | Notes |
|--------|------|-------|
| `children_signatures_row_id` | text | **PK** |
| `evidence_uid` | text | FK → children · indexed |
| `case_uid` | text | FK → case_registry |
| `doc_sha256` | text | indexed |
| `filename` | text | |
| `filing_date` | date | |
| `filing_type` | text | |
| `sig_index` | integer | Order of signature in document |
| `field_name` | text | PDF field name (e.g. "Signature2", "tyler_sig_...") |
| `signer_name` | text | e.g. "Koch, William", "ESolutions Development Certificate Authority" |
| `signer_name_raw` | text | |
| `sig_crypto_status` | text | NULL = inconclusive |
| `sig_crypto_valid_flag` | boolean | NULL = inconclusive |
| `sig_subfilter` | text | "/adbe.pkcs7.detached" or "/ETSI.CAdES.detached" |
| `sign_time_cst_cdt` | timestamptz | |
| `sign_time_raw` | text | |
| `edits_after_sig_flag` | text | "true"/"false" — was document modified after this sig? |
| `rev_count` | integer | Total PDF revisions |
| `v_obj` | text | PDF object reference (e.g. "306 0 obj") |
| `v_obj_sort` | text | |
| `field_index` | integer | |
| `coverage_pct` | numeric | % of document covered by this sig |
| `total_sig_bytes` | integer | |
| `days_after_e_file_signed` | integer | |
| `judicial_officer_id` | text | |
| `judicial_officer_norm` | text | |
| `judicial_officer_raw` | text | |
| `itv_dss` | text | |
| `e_file_date` | date | |
| `doc_type` | text | |
| `chrono_rank` | integer | |
| `cluster_id` | integer | |
| `cluster_name` | text | |
| `case_id` | text | |
| `br_l1`, `br_l2`, `br_o1`, `br_o2` | text | Byte range fields |

---

### `children_objects`
One row per PDF internal object (fonts, images, sig objects, etc.). 35,326 rows.

| Column | Type | Notes |
|--------|------|-------|
| `children_objects_row_id` | text | **PK** |
| `evidence_uid` | text | FK → children · indexed |
| `case_uid` | text | FK → case_registry |
| `doc_sha256` | text | indexed |
| `object_sha256` | text | SHA256 of the embedded object · indexed |
| `object_type` | text | e.g. "Font", "XObject", "Sig" |
| `object_subtype` | text | |
| `object_size_bytes` | integer | |
| `object_index` | integer | |
| `v_obj` | text | PDF object reference |

---

### `children_tracking_groups`
Tracking font group assignment per document. 1,942 rows.

| Column | Type | Notes |
|--------|------|-------|
| `children_tracking_groups_row_id` | text | **PK** |
| `evidence_uid` | text | FK → children · indexed |
| `case_uid` | text | FK → case_registry |
| `doc_sha256` | text | indexed |
| `tracking_group` | text | e.g. "TRACKING-FONT-C1", "TRACKING-FONT-A1" |
| `font_sha256` | text | SHA256 of the font binary |
| `font_size_bytes` | integer | |
| `font_format` | text | ".ttf", ".cff" |

---

### `parents`
Case-level data from MCRO. 2,903 rows.

| Column | Type | Notes |
|--------|------|-------|
| `case_uid` | text | **PK** FK → case_registry |
| `case_id` | text | indexed |
| `cluster_id` | integer | indexed |
| `defendant_name` | text | |
| `case_status` | text | "Closed", "Open", "Dormant", "Under Court Jurisdiction" |
| `case_type` | text | |
| `case_location` | text | |
| `case_active_warrant` | boolean | |
| `rule20_event_count` | integer | Total Rule 20 events for this case |
| `rule20_dormant_flag` | boolean | |
| `days_since_filing` | integer | |
| `filing_date` | date | |
| ... | | Many additional MCRO-scraped fields |

---

### `parents_case_events`
Full docket event log. 155,357 rows. **Primary table for Rule 20 analysis.**

| Column | Type | Notes |
|--------|------|-------|
| `case_uid` | text | **PK** (composite) · FK → parents |
| `event_index` | integer | **PK** (composite) |
| `event_name` | text | e.g. "Rule 20.01 - Order for Evaluation" |
| `event_date` | date | |
| `event_name_pretty` | text | |
| `party` | text | |
| `filing_party_name` | text | |
| `docket_index` | integer | |

---

### `hashpack`
Top-level hash summary, 1:1 with children. 4,251 rows.

| Column | Type | Notes |
|--------|------|-------|
| `evidence_uid` | text | **PK** FK → children (1:1) · indexed |
| `case_uid` | text | FK → case_registry |
| `cluster_id` | integer | indexed |
| `filing_type` | text | indexed (composite with cluster_id) |
| `doc_sha256` | text | |
| `page_count` | integer | |
| `object_count` | integer | |
| `font_count` | integer | |
| `image_count` | integer | |
| `stream_count` | integer | |

---

### `hashpack_rows`
Row-level text with SHA256. 606,037 rows. Used for cross-document deduplication.

| Column | Type | Notes |
|--------|------|-------|
| `hashpack_rows_row_id` | text | **PK** |
| `evidence_uid` | text | FK → hashpack · indexed |
| `row_index` | integer | Row position in document · composite indexed |
| `row_sha256` | text | SHA256 of normalized row text · indexed |
| `row_text` | text | Extracted text content |
| `page_number` | integer | |
| `stream_index` | integer | |

---

### `source`
Scrape session registry. 14 rows.

| Column | Type | Notes |
|--------|------|-------|
| `source_set_id` | text | **PK** · indexed |
| `session_date` | date | |
| `description` | text | |
| `scrape_method` | text | |

---

### `source_file_artifacts`
SHA256 registry of raw collection artifacts. 564 rows.

| Column | Type | Notes |
|--------|------|-------|
| `source_sha256` | text | **PK** — SHA256 of the artifact file |
| `artifact_type` | text | e.g. "MITMPROXY", "OBS_VIDEO", "OTS" |
| `filename` | text | |
| `file_size_bytes` | integer | |

---

## Views

| View | Purpose |
|------|---------|
| `v_case_identity_best` | Best-resolution case identity (name, cluster, case_id) |
| `v_case_identity_with_provenance` | Identity + scrape session provenance |
| `v_case_provenance` | Case-level provenance chain |
| `v_case_provenance_rollup` | Rolled-up provenance summary per case |
| `v_case_registry_source_universe` | All cases visible across all source sets |
| `v_case_registry_with_provenance` | `case_registry` enriched with provenance |
| `v_case_with_source_case_details` | Cases joined with source scrape details |
| `v_hearings_enriched` | Hearings with full case + judicial officer context |
| `v_hearings_with_case_context` | Upcoming/past hearings with defendant metadata |
| `v_source_set_summary` | Per-source-set statistics (n_files, n_cases, date range) |
| `v_nulls` | Null value diagnostic |

---

## Key Indexes (Non-PK)

| Table | Index Columns | Unique? |
|-------|--------------|---------|
| `case_registry` | `case_id` | No |
| `children` | `case_uid`, `cluster_id`, `doc_sha256`, `evidence_uid`, `case_event_uid` | No |
| `children_objects` | `doc_sha256`, `evidence_uid`, `object_sha256` | No |
| `children_signatures_full_report` | `doc_sha256`, `evidence_uid` | No |
| `children_tracking_groups` | `doc_sha256`, `evidence_uid` | No |
| `hashpack` | `cluster_id`, `evidence_uid`, `(cluster_id, filing_type)` | No |
| `hashpack_rows` | `(evidence_uid, row_index)`, `row_sha256`, `(evidence_uid, row_sha256)` | No |
| `hashpack_rows_tags` | `(evidence_uid, row_index)`, `tag` | No |
| `parents` | `case_id`, `cluster_id`, `case_uid` | No |
| `parents_case_events` | `(case_uid, event_index)` | Yes (unique) |
| `parents_charges` | `(case_uid, charge_index)` | Yes (unique) |
| `parents_dispositions` | `(case_uid, disposition_index)` | Yes (unique) |
| `source_files` | `evidence_uid`, `source_set_id`, `source_sha256` | No |

---

## Naming Conventions

| Pattern | Meaning |
|---------|---------|
| `case_uid` | Text PK format: `case:27-cr-23-1886` |
| `evidence_uid` | Text PK format: `evidence:27-cr-23-1886:6e296082826a09d2` |
| `*_row_id` | Surrogate PK for child tables |
| `*_index` | Positional integer within parent (event_index, charge_index, etc.) |
| `__` (double underscore) | Namespace separator in denormalized columns (e.g. `metadata__xmp_create_date`) |
| `children_*` | Tables containing PDF-level data (one row per PDF or per object within a PDF) |
| `parents_*` | Tables containing case-level data from MCRO scrape |
| `hashpack_*` | Tables containing content hash data |
| `source_*` | Tables tracking scrape sessions and file provenance |
| `v_*` | Views |

---

## Quick Reference Queries

```sql
-- Get all forensic metadata for a specific PDF
SELECT * FROM children WHERE evidence_uid = 'evidence:27-cr-23-1886:6e296082826a09d2';

-- All signatures for a specific document
SELECT signer_name, sig_crypto_valid_flag, edits_after_sig_flag, sign_time_cst_cdt, v_obj
FROM children_signatures_full_report
WHERE evidence_uid = 'evidence:27-cr-23-1886:6e296082826a09d2'
ORDER BY sig_index;

-- All MSIP documents
SELECT filename, filing_date, filing_type, 
       metadata__msip__msip_label_name_primary,
       metadata__msip__msip_site_id_primary,
       metadata__msip__msip_set_date_primary
FROM children WHERE metadata__msip__has_msip_flag = true
ORDER BY filing_date;

-- Tracking font cross-document analysis
SELECT tracking_group, font_sha256, font_size_bytes,
       COUNT(DISTINCT evidence_uid) as doc_count,
       COUNT(DISTINCT case_uid) as case_count
FROM children_tracking_groups
GROUP BY tracking_group, font_sha256, font_size_bytes
ORDER BY doc_count DESC;

-- Rule 20 event count by case status
SELECT p.case_status, COUNT(*) as cases, 
       AVG(p.rule20_event_count)::numeric(6,2) as avg_r20_events
FROM parents p
GROUP BY p.case_status ORDER BY avg_r20_events DESC;

-- Documents filed into multiple cases (multi-case filing detection)
SELECT evidence_uid, COUNT(DISTINCT case_uid) as case_count
FROM children_case_set
GROUP BY evidence_uid
HAVING COUNT(DISTINCT case_uid) > 1
ORDER BY case_count DESC;

-- Judicial signature hash collisions
SELECT signer_name, v_obj, COUNT(DISTINCT evidence_uid) as doc_count
FROM children_signatures_full_report
WHERE signer_name NOT ILIKE '%MCRO%' 
  AND signer_name NOT ILIKE '%ESolutions%'
GROUP BY signer_name, v_obj
HAVING COUNT(DISTINCT evidence_uid) > 5
ORDER BY doc_count DESC;
```