# Topic 09 — ESolutions Digital Signature Cohort (DuckDB)

## What this topic is
This topic isolates **all children PDFs that carry a digital signature from**:

- **Signer name:** `ESolutions Development Certificate Authority`
- **Signer DN prefix:** `CN=ESolutions Development Certificate Authority...`

These signatures appear as one of the entries inside each document’s `children.signatures.full_report[]`, and (in your dataset) many of the same documents are also pre-tagged with:

- `children.doc_set[]` contains `META_ESolutions-Signature`

The goal is to **pull the full cohort**, even if the tag is missing or incomplete, and then produce **doc-level + signature-level exports** that make it easy to quantify scope, compare jurisdictions, and correlate with the MCRO watermark signature.

---

## Inputs (where the data comes from)
- **Doc selection fields**
  - `children.doc_set[]` (tag-based)
  - `children.signatures.full_report[].signer_name` and `signer_name_raw` (signature-based)

- **Signature details**
  - `children.signatures.full_report[]` (array of signature rows, one per signature field)

- **MCRO watermark (download-time) signature**
  - `children.signatures.mcro_authentication.*`

- **Metadata**
  - `children.metadata.*` (create/metadata dates, producer/toolkit, MSIP presence, etc.)

---

## Views produced

### 1) `v09_esolutions_sigs_long`
**One row per signature** for **every** children document.

Key fields:
- `doc_sha256`, `case_id`, `case_type`, `cluster_id/cluster_name`
- Signature columns projected from `sig`:
  - `signer_name`, `signer_name_raw`
  - `sign_time_cst_cdt`, `days_after_e_file_signed`
  - `coverage_pct`, `edits_after_sig_flag`
  - `sig_crypto_valid_flag`, `sig_crypto_status`
  - `field_index`, `chrono_rank`, `field_name`

### 2) `v09_esolutions_esolutions_sigs`
Filtered subset of `v09_esolutions_sigs_long` where the signer matches the ESolutions CA.

### 3) `v09_esolutions_docs`
**Doc-level cohort**: any children document that is either:
- tagged with `META_ESolutions-Signature`, **OR**
- contains an ESolutions CA signature row in `signatures.full_report[]`.

### 4) `v09_esolutions_docs_enriched`
Doc-level cohort +:
- **counts** of signatures and ESolutions signature rows per doc
- counts of crypto-invalid / crypto-unknown / edits-after rows
- MCRO watermark summary fields
- metadata highlights (XMP author/creator, toolkit, producer, create/metadata dates)
- MSIP presence (if any)

### 5) `v09_esolutions_stats_by_case_type`
Rollup counts by jurisdiction (`case_type`), including timeline and watermark/invalid counts.

### 6) `v09_esolutions_stats_by_cluster`
Rollup counts by cluster (so you can quickly see whether cluster **1570 (GUERTIN)** is hit, and how often).

### 7) `v09_esolutions_stats_by_signer`
Signer distribution **within the ESolutions cohort** (i.e., all signatures present on those docs).

---

## Exports produced (CSV)
Running `09_esolutions_digital_sig__exports.sql` writes:

- `reports/09_esolutions_digital_sig/09_esolutions_docs_enriched.csv`
- `reports/09_esolutions_digital_sig/09_esolutions_esolutions_sigs_long.csv`
- `reports/09_esolutions_digital_sig/09_esolutions_stats_by_case_type.csv`
- `reports/09_esolutions_digital_sig/09_esolutions_stats_by_cluster.csv`
- `reports/09_esolutions_digital_sig/09_esolutions_stats_by_signer.csv`
- `reports/09_esolutions_digital_sig/09_esolutions_docs__FOCAL_1570.csv` (optional focal extract)

---

## How to run (from project root)
In DuckDB CLI:

```sql
.read duckdb/sql/core/bootstrap.sql
.read duckdb/sql/core/starter_views__v3_provenance.sql
.read sql/topics/09_esolutions_digital_sig/09_esolutions_digital_sig__views.sql
.read sql/topics/09_esolutions_digital_sig/09_esolutions_digital_sig__exports.sql
```

---

## Interpretation guardrails
- **This topic does not claim “fraud” by itself.** It produces a defensible, reproducible **inventory**:
  - which docs contain ESolutions CA signatures,
  - how those signatures validate,
  - whether the MCRO watermark is present/valid,
  - how timing/coverage/edits-after fields behave,
  - and how scope varies by jurisdiction and cluster.

This keeps the output **audit-ready** and easy to cite in later narrative reporting.