# Topic 09 — ESolutions Digital Signature  
## Run 02: Duplication / "Cloned per Page" Methodology

This run focuses on the *mechanics* of the ESolutions signature behavior using:

- `children.signatures.full_report[]`

The goal is to quantify and demonstrate a specific pattern you described:

> The ESolutions signature appears as a **non-visible widget-like field** and is effectively **duplicated** across the PDF,
> where the **same signature container / byte pattern** repeats across multiple `field_index` entries.

At the same time, these documents typically contain a **visible judicial signature** *before* the ESolutions entries,
which can mislead a normal user into thinking the file is fully "officially signed".

---

## Cohort selection (two independent methods)

A document is included if **either** of the following is true:

1. `children.doc_set[]` contains: `META_ESolutions-Signature`  
2. Any element of `children.signatures.full_report[]` matches:
   - `signer_name == "ESolutions Development Certificate Authority"`
   - OR `signer_name_raw ILIKE "CN=ESolutions Development Certificate Authority%"`

This ensures we don’t miss cases where the tag is absent or inconsistent.

---

## What Run 02 Measures

### A) Per-document duplication signal

`v09r2_esolutions_duplication__per_doc` reports:

- `n_eso_rows`: total ESolutions full_report rows in the PDF  
- `n_eso_byte_pattern_distinct`: count of distinct signature *byte-pattern proxies* built from:
  - `v_obj`, ByteRange offsets (`br_o1/br_l1/br_o2/br_l2`)
  - `total_sig_bytes`
  - `sign_time_cst_cdt`
  - `field_name`
  - `sig_subfilter`

If:

- `n_eso_rows > n_eso_byte_pattern_distinct`

then:

- `has_cloned_eso_byte_pattern_flag = true`

This is the strongest quantitative indicator that the ESolutions entries are “the same thing repeated”.

### B) Stack-order / “preceded by visible judicial sig”

The same per-doc view also reports:

- `eso_first_rank` (first chrono rank where ESolutions appears)
- `eso_not_first_flag` (true if `eso_first_rank > 1`)
- `has_non_eso_before_eso` (true if any non-ESolutions signature exists before the first ESolutions entry)
- rank-1 signer snapshot (`signer_rank_1`, `coverage_rank_1`) for fast human review
- whether MCRO Watermark appears anywhere in the stack

### C) Cross-document template reuse

`v09r2_esolutions_signature_templates` groups ESolutions rows by a strict key:

- `v_obj`
- ByteRange offsets
- `total_sig_bytes`
- `sig_subfilter`
- `sign_time_cst_cdt`
- `field_name`

This highlights whether the exact same signature structure repeats not only within a PDF, but across multiple PDFs.

### D) “Hidden widget” heuristic

`v09r2_esolutions_hidden_widget__rollup` reports:

- how many ESolutions entries use field names matching `tyler_sig_%`
- whether the doc shows “cloned pattern” behavior

This doesn’t prove invisibility directly, but it tracks the very specific field naming pattern
you’ve observed in practice.

---

## Views

- `v09r2_esolutions_docs`  
  Cohort of ESolutions-signed children docs (with selection audit flags).

- `v09r2_esolutions_full_report__long`  
  Full flatten of `signatures.full_report[]` for all cohort docs (includes judicial + MCRO + ESolutions).

- `v09r2_esolutions_duplication__per_doc`  
  Per-document duplication + stack-order analysis.

- `v09r2_esolutions_signature_templates`  
  Cross-document template reuse groups.

- `v09r2_esolutions_hidden_widget__rollup`  
  Widget-name heuristic + clone flag rollup.

---

## Exports

All exports are written to:

`reports/09_esolutions_digital_sig/`

- `09r2_esolutions_docs.csv`
- `09r2_esolutions_full_report__long.csv`
- `09r2_esolutions_duplication__per_doc.csv`
- `09r2_esolutions_signature_templates.csv`
- `09r2_esolutions_hidden_widget__rollup.csv`

---

## How to run

```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__run02_duplication__views.sql
.read sql/topics/09_esolutions_digital_sig/09_esolutions_digital_sig__run02_duplication__exports.sql
```

If you hit a binder error, it usually means the exact column names in your `full_report[]` schema differ
(e.g., `sign_time_cst_cdt` vs `sign_time_local`). Paste the error and we’ll patch quickly.