# Topic 02 — Cluster Alignment
## Pack: 02_top50_clusters_dump

### What this produces

This pack generates a **single, wide CSV “dump”** for the **Top 50 clusters** ranked by **distinct case count** (distinct `case_id`), with a strong preference for clusters that actually have `children` documents.

The output is designed to be a “one file to rule them all” artifact: a doc-level table that includes event alignment keys, signature and metadata fields, and per-event sharing stats.

---

## Ranking logic

Clusters are ranked using `parents_q`:

- `n_cases` = `COUNT(DISTINCT case_id)` per `(cluster_id, cluster_name)`

Then the ranking is ordered by:

1. `has_children_docs` (clusters with at least one `children_q` row are ranked first)
2. `n_cases` (descending)
3. `n_children_docs` (descending)
4. `cluster_id` (ascending tie-breaker)

The selected set is the **Top 50** by this ordering.

---

## What rows represent

Each row in the final output represents **one children document** (from `children_q`) within one of the Top 50 clusters **that has a valid event date** (same constraint used by your Topic 02 event alignment pack).

---

## Output file

Written by the exports script to:

`reports/02_cluster_alignment/02_top50_clusters__unified_dump.csv`

This will likely be large.

---

## Column groups

### Event-sharing stats (within Top-50 selection)
These are computed per `event_match_key` using only the Top-50 slice:

- `n_clusters_in_top50`
- `n_docs_in_top50`
- `n_cases_in_top50`
- `first_filing_date_in_top50`
- `last_filing_date_in_top50`

### Cluster ranking context
- `cluster_rank`
- `cluster_n_cases`
- `cluster_n_children_docs`

### Doc identity + event alignment
- `case_id`, `case_type`, `cluster_id`, `cluster_name`, `is_mcro_case_flag`
- event fields: `event_date`, `event_name_pretty`, `event_name_raw`, `docket_index`, `event_index`
- doc fields: `filing_type`, `filing_date`, `pdf_filename`, `json_filename`, `doc_sha256`, `size_bytes`, `pdf_page_count`
- keys: `cluster_event_key`, `event_match_key`

### Metadata fields (XMP / MS-derived high signal)
- `metadata_xmp_create_date`
- `metadata_xmp_metadata_date`
- `metadata_instance_id`
- `metadata_document_id`
- `metadata_xmp_author`
- `metadata_xmp_creator`
- `metadata_xmp_title`
- `metadata_xmp_creator_tool`
- `metadata_xmp_producer`
- `metadata_xmp_toolkit`

### Signature fields
- `has_signature_flag`
- `mcro_authentication`
- `sig_rev_count` (from `signatures.full_report[0].rev_count`)
- `sig_field_count` (# of signature fields in `full_report`)
- signature slots 1–6: `sigN_name`, `sigN_sign_time`, `sigN_signed_ranges`

---

## Notes / quirks

- All signature/metadata extraction is done using `json_extract*` against `children_q` to avoid binder errors when fields are missing in a subset of documents.
- The dump is constrained to docs with non-null event dates to keep the output aligned with your existing Topic 02 event alignment concepts (`event_match_key`).

---

## Run

From DuckDB CLI:

```sql
.read duckdb/sql/core/bootstrap.sql
.read duckdb/sql/core/starter_views__v3_provenance.sql

.read sql/topics/02_cluster_alignment/02_top50_clusters_dump__views.sql
.read sql/topics/02_cluster_alignment/02_top50_clusters_dump__exports.sql
```


## Judicial officer objects (added)
This dump now includes the 32 wide columns for the first two `Judicial Officer Signature` objects and first two `Judicial Officer Timestamp` objects, pivoted from `children_objects` by `evidence_uid` (columns `judicial_sig_1_*`, `judicial_time_1_*`, `judicial_sig_2_*`, `judicial_time_2_*`).


## Judicial Officer Objects
This pack now includes the wide pivot columns from `02_cluster_alignment__judicial_objects__views.sql` (sig/timestamp ranks 1–2), joined by `evidence_uid` and exposed in the unified CSV.