---
title: "KY Courts Archive Pipeline"
status: "active"
folder: "02-notes"
tags: [note, project, ky-courts, archive, web-scraping]
created: "2026-08-12"
updated: "2026-08-12"
version: "1.0.0"
---

# KY Courts Archive Pipeline

A Colab-based harvester that builds a **local, full-library archive of Kentucky Court of Appeals (CA) and Supreme Court (SC) filings** from the state's public C-Track document portal, and persists everything to Google Drive so long runs survive VM tears.

**Repo:** `git@github.com:stateofshit/ky-court-archive-pipeline.git` (private, pushed as `origin/main`)
**Deliverable:** `KY_Courts_Archive_Pipeline.ipynb` (a 12-cell Colab notebook) + `colab-cell-magic-reference.md`

---

## Why this project exists

The KY judiciary serves case documents through `appellatepublic.kycourts.net` — a public C-Track search API. There is no bulk download, only paginated search. To archive the *whole* library (roughly **4–6 hours / ~3,900 pages of API calls** across ~70 shards), you need:

1. A way to enumerate every document without blowing Colab's session limits.
2. Resume + checkpointing so a killed session doesn't restart from page 1.
3. A durable copy on Drive so a torn-down VM loses nothing.
4. A completeness check that tells you what you'd actually be *missing* if you stopped.

That's the whole job. Nothing here is a toy — it ends in a verified, resumable local archive.

---

## Source API

- **Search:** `https://appellatepublic.kycourts.net/api/api/v1/publicaccessdocuments/search`
- **Download:** `https://appellatepublic.kycourts.net/api/api/v1/publicaccessdocuments/{id}/download`
- **Auth:** none — public. Only a `User-Agent` header is set.
- **Paging:** via `X-CTrack-Paging-StartIndex` / `-MaxResults` headers (max practical page = 25).
- **Facets:** returned on page 1 per shard and used to *estimate* total documents (drives the ceiling guard and ETA).

Seen live in **cell 5** (`CTrackClient`, exponential-backoff retries, connection pooling tuned for Stage B's 10 threads).

---

## Notebook map (cells run top-to-bottom)

| # | Title | What it does |
|---|---|---|
| 1 | Install Dependencies | `pip install` + imports |
| 2 | Pipeline Configuration | `PipelineConfig` dataclass → `CONFIG` |
| 3 | Mount Drive + dirs | Drive auth, create local + Drive trees, logging |
| 4 | DB Schema + Restore | create tables, auto-restore latest Drive snapshot |
| 5 | C-Track API Client | retry/paging/download client |
| 6 | Parsing, Classification & Job Enqueue | formatting, keyword filters, `discovery_jobs` seeding |
| 7 | State Manager & Pre-flight | `HarvesterState`, per-shard ETA table |
| 8 | **Stage A** — Shard harvest loop | the long metadata harvest |
| 9 | Completeness Report | flags capped/failed/pending/under-covered shards |
| 10 | **Stage B** — PDF download | parallelized, resumable downloads |
| 11 | Status Dashboard & Export | counts + exports |

---

## Data model (sqlite, `ky_court.sqlite`)

Key tables created in **cell 4**:

- `discovery_jobs` — one row per case-prefix shard; tracks `status`, `start_index` (page cursor), `pages_done`, `extra_json` (holds the auto-retry `attempts` counter), `last_error`.
- `documents` — deduped by `document_id`; stores case number, type, filing date, classification (`current_decision`, `decision_confidence`).
- `document_discovery` — which job discovered which doc (idempotent join).
- `download_queue` / `download_attempts` / `files` / `file_duplicates` — PDF download state machine + the downloaded artifacts.
- `text_extractions` / `text_chunks` / `fts_chunks` — OCR/text layer (FTS5) for search.
- `api_queries`, `errors`, `field_probes`, `filter_decisions`, `sync_events` — audit + diagnostics.

`documents` upsert is idempotent: `INSERT ... ON CONFLICT(document_id) DO UPDATE`, so re-fetching overlapping pages never creates duplicates.

---

## Core design patterns

### 1. Shard-based harvesting
The library is split into **case-prefix shards** by year × court (e.g. `1996-CA-`, `2005-SC-`). Stage A walks each shard's pages until the API returns two consecutive empty pages.

### 2. Sub-sharding (complete-library support)
If a `year-court` shard contains > `sub_shard_threshold` (9000) docs, it's split recursively by appending case-number digits (`2020-CA-1`, `2020-CA-15`, …) up to `sub_shard_max_depth` (4). Keeps every shard small enough to paginate reliably.

### 3. 10k ceiling guard
If the facet estimate for a shard exceeds **10,000**, the C-Track API rejects deep pagination. Stage A marks such shards `ceiling` (not `done`) so the completeness report explicitly flags them as **not harvested** rather than silently skipping.

### 4. Per-page checkpoints
Every page commits `start_index` (the next cursor) and `pages_done` to `discovery_jobs` immediately. A disconnect mid-shard resumes from the last committed page — never from page 1.

### 5. Drive snapshots
`safe_snapshot()` backs up the full DB to `MyDrive/ky_court-WIP/db_snapshots/ky_court_<ts>.sqlite` every N docs / N seconds and at end of Stage A. **Cell 4 auto-restores** the latest snapshot on the next session → the run continues where it left off.

### 6. Session chunking (`stage_a_from` / `stage_a_to`)
The full sorted shard list is split into per-session index ranges so one long run can be fanned out across multiple Colab sessions (`(0,20)`, `(21,40)`, …). Indexes reference the **full sorted list**, so positions stay stable across sessions as shards complete. Default `(0,-1)` = everything.

### 7. Attempt-capped auto-retry
A failed shard is **auto-included** in the next run while its `attempts` (stored in `extra_json`) is `< max_shard_attempts` (default 2). Once it hits the cap it drops out of auto-retry permanently and is surfaced by the Completeness Report until manually reset:

```python
con.execute("UPDATE discovery_jobs SET status='queued', extra_json='{}' WHERE status='failed'")
con.commit()
```

This self-heals transient failures without letting a genuinely broken shard burn session time forever.

### 8. Failure states
- **`done`** — harvested. (But the report cross-checks: *done but 0 docs in DB* → flag.)
- **`ceiling`** — capped at 10k, docs NOT harvested → needs attention.
- **`failed`** — threw after retries; auto-retried until the attempt cap.
- **`queued` / `running` / `superseded`** — normal lifecycle / replaced by a sub-shard.

---

## Configuration knobs (`PipelineConfig`, cell 2)

| Knob | Default | Purpose |
|---|---|---|
| `dry_run` | `True` | `True` = sample first shard only; set `False` for real harvest |
| `year_start` / `year_end` | 1996 / 2026 | harvest scope |
| `courts` | `('CA','SC')` | Counties Appeals + Supreme |
| `sub_shard_threshold` | 9000 | auto sub-shard threshold |
| `sub_shard_max_depth` | 4 | sub-shard depth |
| `stage_a_from` / `stage_a_to` | 0 / -1 | session chunk range |
| `max_shard_attempts` | 2 | auto-retry cap |
| `request_delay_seconds` | 0.4 | politeness / rate-limit delay |
| `max_retries` | 4 | per-request backoff |
| `download_included` | `False` | flip `True` to start PDF downloads (Stage B) |
| `drive_wip` | `/content/drive/MyDrive/ky_court-WIP` | Drive working folder |

---

## How to run (Colab)

1. Upload `KY_Courts_Archive_Pipeline.ipynb`; use a **CPU** runtime.
2. Run cells **1→11 in order**. Cell 3 triggers Drive auth.
3. In cell 2: set `dry_run=False` when ready for a real run; set the chunk range for session-split runs.
4. For every new session, run 1–4 first (auto-restores the DB), set the chunk, then Stage A.
5. After Stage A, run the **Completeness Report** (cell 9) — the same Google account / same `ky_court-WIP` folder must be used across sessions so snapshots land in one place.

Full setup checklist is in the Colab prompt I issued from this note's project work — the key gotchas are: **same Drive account every session**, **cells in order**, and **`dry_run=False` before Stage A**.

---

## Status & session history

- **2026-08-12** — added session chunking (`stage_a_from`/`stage_a_to`) and attempt-capped auto-retry (`max_shard_attempts=2`, counter in `extra_json`, Completeness Report shows `attempts a/max`).
- Prior: full-range harvest notebook, ID parsing/classification, 10k ceiling guard, auto sub-sharding, resumable checkpoints, Drive snapshots, Stage B downloads, FTS search.

## Failure modes to keep in mind
- **A `failed`-state shard won't auto-resume on a plain re-run** after it hits the cap — reset it explicitly (pattern above) or it silently skips. This is the known gotcha the attempt-cap is designed around: it's *bounded effort by design*, not a bug.
- **Drive account mismatch across sessions** breaks the snapshot/resume trail.
- **Deep pagination >10k** is rejected by the API — that's exactly what the `ceiling` guard exists to surface.
