# 07_msip_metadata — MSIP Label Forensics (Children docs)

This topic focuses **only** on `children` documents where:

- `list_contains(children.doc_set, 'META_MSIP')` is true.

These are the PDFs in your corpus that contain **Microsoft Information Protection (MSIP)** label metadata (as extracted into `children.metadata.msip.*`).

The goal is to produce **quantifiable, audit-friendly evidence** about:

1. **When MSIP labeling appears** in the corpus (first/last label set-times).
2. **Cross‑jurisdiction re‑use** of identical MSIP identifiers (same `msip_site_id` / `msip_action_id` / label token) across:
   - District court criminal files (`case_type = 'CR'`)
   - Court of Appeals (`case_type = 'MN-COA'`)
   - Supreme Court (`case_type = 'MN-SC'`)
   - Any other case types present in your dataset.
3. **Digital signature integrity** within the MSIP cohort:
   - Which docs have **non‑valid signature statuses** (pdfsig output).
   - Which docs have missing/invalid **MCRO Watermark** verification (the download-time validation anchor).
4. **Focal case emphasis**:
   - All MSIP docs in/connected to cluster **1570 (MATTHEW GUERTIN)** are exported as a dedicated subset.

---

## Inputs used

From each `children` row:

### MSIP metadata
- `children.metadata.msip.msip_site_id_primary`
- `children.metadata.msip.msip_action_id_primary`
- `children.metadata.msip.msip_label_name_primary`
- `children.metadata.msip.msip_label_token_primary`
- `children.metadata.msip.msip_set_date_primary` *(UTC `...Z` timestamp)*
- plus the `msip_all_*` rollups (useful for multi-label docs)

### Signature status signals

This pack uses **two signature channels**, because they answer different questions:

1. **MCRO watermark** (download-time authenticity anchor):
   - `children.signatures.mcro_authentication.sig_crypto_valid_flag`
   - `children.signatures.mcro_authentication.sig_crypto_status`
   - `children.signatures.mcro_authentication.sign_time_cst_cdt`

2. **pdfsig per-signature status** (document internal signatures):
   - `children.signatures.pdfsig.sig1_crypto_status` (and sig2/sig3)
   - plus signer names + sign times

The `pdfsig` fields are treated as **status strings**.
This topic uses a conservative heuristic:
- status containing `"valid"` => treated as “valid-like”
- anything else => treated as “non-valid-like” and exported in anomalies

---

## How to run

From the DuckDB CLI (run from your project root so paths resolve):

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

.read duckdb/sql/topics/07_msip_metadata/07_msip_metadata__views.sql
.read duckdb/sql/topics/07_msip_metadata/07_msip_metadata__exports.sql
```

Create output folder if needed:

```bash
mkdir -p reports/07_msip_metadata
```

---

## Outputs (CSV)

All outputs land in:

`reports/07_msip_metadata/`

### 1) `07_msip_docs__occurrences.csv`
**One row per MSIP-labeled PDF** (doc-level).

Includes:
- case identifiers (case_id, case_type, cluster_id/name)
- filing metadata (filing_date/type, page count, size)
- MSIP fields (site/action/token/label + set date)
- XMP provenance (author/creator/toolkit/producer/IDs)
- signature summaries:
  - MCRO watermark validity + sign time
  - pdfsig slot summaries for sig1/sig2/sig3
- computed flags:
  - `any_sig_nonvalid_flag`
  - `watermark_missing_flag`
  - `watermark_invalid_flag`

### 2) `07_msip_group_stats__by_site_action_label_case_type.csv`
Aggregates MSIP docs by:
- `msip_site_id`, `msip_action_id`, `msip_label_name`, and `case_type`.

This is the **core cross‑jurisdiction re‑use table**.

Key columns:
- `n_docs`, `n_cases`
- first/last MSIP set times
- first/last filing dates
- how many docs in the group have signature anomalies

### 3) `07_msip_timeline__by_site_id.csv`
Summarizes each `msip_site_id`:
- how many docs/cases/jurisdictions use the same site ID
- first/last MSIP set date (UTC)
- first/last filing date

This is the **timeline proof** of when a site_id enters the corpus and how long it persists.

### 4) `07_msip_site_id__jurisdiction_matrix.csv`
A wide pivot of doc counts by `msip_site_id` across case types.

Use this to quickly show:
- “This same MSIP site_id appears in CR + MN-COA + MN-SC”
- or “This site_id is confined to one jurisdiction”

### 5) `07_msip_signature_anomalies__docs.csv`
A focused table of only the docs where:
- any pdfsig slot is **non-valid-like**, OR
- the MCRO watermark is missing/invalid.

This is your **signature integrity investigation queue**.

### 6) `07_msip_signer_validity__summary.csv`
Signer-level aggregation showing:
- signers who have **both** valid-like and non-valid-like outcomes in the MSIP cohort.

This supports the “not a system-wide failure” argument:
- the same signer can validate in some docs but not others.

### 7) `07_msip_focal_cluster_1570__docs.csv`
All MSIP docs tied to the focal cluster:
- `cluster_id = 1570` OR `doc_set` contains `GUERTIN`.

---

## Notes on interpretation

- **MSIP identifiers (site_id/action_id/token)** are the strongest “shared origin” signals because they are highly specific GUID/token values.
- The MCRO watermark is treated as a **download-time authentication anchor**.
  It helps you separate:
  - “this file was stamped/served by MCRO at download time”
  from
  - “the internal signature chain is inconsistent / invalid / suspicious”
- If you later want deeper crypto verification beyond the status strings, we can add:
  - signature byte-range parsing, signer chain extraction, and
  - explicit comparisons to your `MCRO-Digital-Signature-Report-with-Embedded-Files.pdf`.