Community Media Archive & Archive Corps

Community Media Archive & Archive Corps — Project Notes

The strategic shift

What "Move focus from Community Media Archive operations to CMA Stewardship" means:

• Move off operations, onto the underdeveloped/unexplored aspects
• Bring the community in to understand the resource they have and what it could become
• Drop the "that video's not going to archive itself!" pressure

Why Archive Corps matters (the value proposition to participants):

• Discover a passion or interest without a scary commitment — no career change, no months of full-time retraining, no degree that might end in "I don't like this field"
• Earn peer recognition for real contributions
• Contribute at scale to something that preserves this community's work and history, and gives researchers a resource for studying community and local civic media

The integration question: how does Archive Corps map onto operating / feeding / improving / extending the beast?

Curriculum: IA interface fundamentals first

Taught with real examples, three levels:

1. GUI
2. Search → advanced search
3. CLI

Rationale: you can't understand how metadata decisions affect discovery, findability, searchability, and usability until you've worked the retrieval side. Only then does the link between what you put in and how people find it click.

Outcomes: participants can archive their own media — born-digital, whether or not it's on YouTube/Vimeo. Also a jumping-off point for physical digitization projects.

Then: operating the CMA. Worked example — default TelVue archiving module vs. exporting metadata alongside the videos.

CMA work areas

Operating the beast

• Onboarding new YouTube channels
• Reporting to operators, fleet managers, channel owners

Feeding / expanding the fleet

• New worker process for Linux/Mac/Windows that doesn't require deep Linux tooling knowledge
• Migrate existing fleet to the new Linux process

Incorporating Agendas & Minutes

• Existing corpus: ~800,000 CivicPlus documents, ~1,500 communities, run 2022–2023 covering 2018–2022, via BigLocalNews civic-scraper
• CivicPlus UI has changed; the 2022 scraper code no longer works
• Need extractors matching current UIs for the common agenda vendors in civic.db
• Need a way to track vendor changes over time

Improving the beast — metadata grooming bots

• Language attribution: mark Spanish based on description text, not naive title matching
• Clean \r and \" in descriptions
• Automated transcript local dictionaries

Infrastructure

• Captioning machines: Faster-Whisper-XXL on cards that need newer Nvidia software than 3060 generation can support

Extending the beast — volunteer systems

• Microtask distribution, volunteer direction, recognition tracking

The biggest unfinished areas

1. Volunteer infrastructure — microtasks, recognition, coordination. Nothing else in Archive Corps works without it.
2. The onboarding curriculum — IA fundamentals → CMA operation. Currently a sketch.
3. Agendas/minutes rebuild — highest-value corpus, currently broken.
4. Cross-platform worker process — the gate on fleet expansion beyond Linux-fluent people.

↑ back to contents00.0-stepping-back-from-CMA-operations/cma-notes.md

Civic Data Pipeline (Cities)

A pipeline that, for every incorporated city/town in a U.S. state, discovers its official website, its YouTube channel, and its Agendas & Minutes page (with any copyright statement). All data lives in a single SQLite database (civic.db by default) with one row per place across every state. CSV files are used only for review and hand-editing, via export and import.

Data source: the U.S. Census Bureau 2024 Places Gazetteer.

Files

Sequencing a state for the workers is documented separately in docs/CMA_ONBOARDING_RUNBOOK.md (also .docx/.pdf) — the plan → survey → review → tune → assign cycle, how to choose scoring weights, the review flags that must stop a collection being created, and how to decide when to spin up a worker on a new IP.

Core files must live in the same directory (civic.py imports the others); wiki_official_site.py, cma_onboard.py, cma_survey.py, youtube_triage.py, body_discriminator.py and check_rescrape.py are standalone and run on their own.

Requirements

• Python 3.8+ (SQLite is in the standard library)
• pip install requests beautifulsoup4

Quick start

# Stage 1 — discover official sites for WA (downloads/caches the gazetteer,
# caches every homepage it fetches). Writes into civic.db.
python3 civic.py scrape-sites --state WA \
    --gazetteer ~/civic/gazetteer.zip --cache-dir ~/civic/html_cache

# Stage 2 — YouTube channels (reuses cached homepages, no re-fetch).
python3 civic.py scrape-youtube --state WA --cache-dir ~/civic/html_cache

# Stage 3 — Agendas & Minutes + copyright.
python3 civic.py scrape-agendas --state WA --cache-dir ~/civic/html_cache

# See coverage across everything in the database.
python3 civic.py stats

# Get a CSV of the finished data.
python3 civic.py export --state WA --out wa_final.csv

Use --db path/to/other.db on any command to target a different database (default is civic.db in the current directory). Process more states by re-running scrape-sites --state XX; they all accumulate in the same DB.

The --cache-dir and --gazetteer flags above are optional — both default to ~/.civic_cache/ (the homepage cache lives in ~/.civic_cache/html/), so you can omit them entirely and caching still works across runs. Pass them only to use a custom location.

Output schema

These columns live in the places table (and in every exported CSV). scrape-sites fills the first group; scrape-youtube and scrape-agendas fill theirs. The database also keeps an updated_at timestamp per row (not exported).

A blank official_site (confidence unverified) means no site cleared the verification bar; the youtube/agenda stages skip those rows.

How detection works (brief)

• Stage 1 guesses common municipal URL patterns (cityofX.gov, Xwa.gov, X-wa.gov, ci.X.wa.us, townofX.com, …), type-aware (towns try townof… first). A fast DNS pre-check skips hosts that don't resolve. Each responding candidate is scored against the target city and state (name in title/page, state in domain/page, .gov host, wrong-state penalties) so a same-named city in another state or a domain squatter is rejected. Hard rejects (no signal can rescue them): real-estate portals (nhrealestate.com, *realty*, Zillow/Redfin/Coldwell Banker…), domain marketplaces (afternic, hugedomains, expireddomains, */buy-domain/*, */domain/*), and parked/for-sale pages — these score well on name+state signals (a listing for "Nashua, NH" has the city, state, and an address), so they are refused outright rather than penalized. A .gov host is never hard-rejected.
• A name match alone is not a match. A generic site that merely carries the city's word in its title (liberty.org for Liberty TX, commerce.gov for Commerce TX, scotland.org for Scotland SD) is rejected, not stored at low confidence. This matters: a bad match occupies the site field, so needs=site skips the row forever and the real domain is never found — a blank is strictly better. Corroboration is required: state evidence, or a governmental signal that isn't just a bare <name>.gov (federal departments live at bare <name>.gov, so that alone proves nothing). Wrong-state defenses are strict: a domain or <title> announcing a different state (adjuntas-ny.gov, "Springfield, MO" when seeking IL) is penalized heavily; street addresses are ZIP-verified against the target state (a city-hall address whose ZIP places it in another state is a strong rejection, while a ZIP-confirmed in-state address is strong acceptance evidence); and a candidate with no positive evidence of the right state can't reach "confident" on name + .gov host alone — it's capped to "low" and flagged rather than accepted. A bare ", ST" token (e.g. a sister-city link) no longer counts as state evidence on its own.
• Stage 2 scans the homepage for YouTube links — including social-icon bars, data-* attributes, entity-encoded and protocol-relative URLs. If the homepage has none, it follows the most promising internal subpages ("Watch Meetings", "Live Stream", "Connect With Us", …) and scans those (youtube_source='subpage'). Every found channel is then fetched once to (a) canonicalize it — @handle / /user/ / /c/ forms resolve to the stable /channel/UC… id the wider ecosystem joins on — and (b) verify it belongs to this place: a channel page with zero overlap with the place name and no civic vocabulary (e.g. a linked news station) is dropped rather than stored; an unreachable channel page keeps the URL as found (benefit of the doubt). Stage 2 also — and ranks real channels above shared videos. When neither the homepage nor its subpages link a channel, Stage 2 falls back to a YouTube search — the same move a human makes, typing "city of <name> <ST>" into the search bar. It queries YouTube's channel-filtered results, parses the candidates, and ranks them by name (word-boundary match), state (a channel titled "…, SD" or describing "South Dakota" is penalized/rejected for an Oregon place), civic vocabulary, and handle pattern (cityof<slug>, <slug>oregon, city_of_<slug>, … — including the auto-appended digits YouTube adds, e.g. @cityofboardmanoregon7618). Only a candidate that clears the acceptance bar is kept; places with no real channel (a search returning only same-named churches or businesses) yield nothing rather than a bad guess. The pick is then run through the same canonicalize-and-verify page fetch as homepage hits (youtube_source='search'). A wrong-entity guard rejects the three leaks that name+state scoring misses because the channel never spells out its state: (1) a different place — the "City/Town of X" in the title must resolve to this place, so "City of Oakland Park" is rejected for Oakland and "Town of Union City" for Union; (2) a non-government entity — a newspaper, church, school, business, or single department (police, public safety, attorney…) that merely names the town; (3) a different-state code in the title, e.g. "City of Mitchell IN" for Mitchell OR. Selection then prefers a state-confirmed active channel; failing that it takes the best active survivor, and failing that it stores nothing — a tiny town whose only real channel is abandoned gets no channel rather than a same-named one from another state. Finally, every found channel — homepage, subpage, or search — passes an activity gate: its /about page reports a total video count, and a channel with ≤ MIN_YOUTUBE_VIDEOS (30) videos is dropped. Small towns often have a channel they tried once and abandoned (a few clips from years ago); those aren't where meetings live. Past live streams count toward the total, so an active government-access channel (e.g. one with 2.3K videos) passes; an undeterminable count is kept rather than dropped on a parse miss. On the search path the gate is applied while verifying down the ranked list, so a dead top pick is skipped and an active same-city channel below it wins (e.g. a 3-video …oregon8825 variant falls through to the 52-video @CityofTillamook).
• Stage 3 finds the link whose text/href best matches "Agendas & Minutes" / "Minutes & Agendas" (combined phrases rank highest), fetches it, extracts a copyright statement, and identifies the agenda portal vendor (Granicus, Legistar, CivicPlus, CivicClerk, PrimeGov, Municode, …) from the URL — the routing key for downstream extraction.

Which places count: governance models and data sources

The hard part of "list every local government" isn't scraping — it's that the United States has no single, uniform tier of local government. Most states have incorporated cities and towns, but several do not, and the Census Bureau splits these different structures across different Gazetteer files. A naïve tool that only reads the "Places" file silently returns almost nothing for those states.

This pipeline handles the common structural cases with a per-state source profile, choosing the right Gazetteer file(s) automatically:

Each row records which file it came from in the source column (place / cousub / county), and Stage 1 uses source-appropriate URL patterns (e.g. Xcounty.gov and co.X.st.us for counties, townof… first for New England towns).

Concretely, this is the difference between scrape-sites --state MA returning ~350 town + city governments instead of just the ~58 cities, and --state HI returning Hawaii's 5 counties — or --state PR returning Puerto Rico's 78 municipios — instead of nothing usable.

Known limitation: township states are not yet special-cased

States with civil townships — e.g. NY, NJ, PA, OH, IN, IL, MI, WI, MN, MO, KS, ND, SD, NE — are currently treated as DEFAULT (Places file only). In some of these, townships exercise real governing authority (and have their own websites and meeting agendas); in others they are weak or nominal. Because that authority varies so much, they are intentionally left for a later refinement rather than swept in wholesale. For now, a run of one of these states covers its incorporated cities/villages but not its townships. If you need township coverage for a specific state, that state's governments live in the County Subdivisions file and could be added to the NEW_ENGLAND-style merge — ask, or edit STATE_SOURCE_PROFILE and the COUSUB_GOVERNING_SUFFIXES map in civic_common.py.

Territories

Puerto Rico (PR) and Hawaii use the COUNTY profile (see the table above). The other U.S. territories — Guam (GU), the U.S. Virgin Islands (VI), American Samoa (AS), and the Northern Mariana Islands (MP) — each have their own governance structure and are not yet profiled; running them falls through to DEFAULT and may return statistical areas rather than governments, exactly as Puerto Rico did before it was added. They can be profiled the same way once their Gazetteer structure is verified.

The county layer (opt-in)

By default the pipeline is municipal-focused: a DEFAULT state returns its incorporated cities/towns, and counties are pulled only for COUNTY- profile states (HI, PR) where the county is the primary government. Counties everywhere else are not included unless you ask for them.

To add the full county layer, use the opt-in scrape-counties command:

python3 civic.py scrape-counties --state TX      # one state's counties
python3 civic.py scrape-counties --all-states    # every state's counties

County rows carry source='county' and coexist with the municipal rows (different GEOIDs — a "Los Angeles city" place row and a "Los Angeles County" county row both live in places, distinguishable by source). All county equivalents are handled: Louisiana parishes, Alaska boroughs / census areas / municipalities, and Virginia independent cities. Because the source column separates them, you can export just cities, just counties, or both.

Two honest caveats. First, adding counties roughly doubles the row count and changes what the database represents (municipal → municipal + county). Second, county site-discovery is weaker than municipal — county domains are highly varied, so expect a higher "no site" rate than for cities even with the county-specific URL patterns. County rows with no site can be corrected via the same export → edit → import round-trip.

More broadly: "local government" is genuinely fuzzy. Special districts (school, water, fire), tribal governments, and independent authorities are out of scope entirely. This pipeline models the general-purpose municipal and county tier, which covers the vast majority of what people mean by "city/county websites," but is not a complete census of every governing body in the U.S.

Re-running just the failures

Re-running is automatic and built into the scrape commands: each one only processes rows that still need it. scrape-sites does cities with no official_site; scrape-youtube does sites with no youtube_channel; scrape-agendas does sites with no agenda_page. Run the same command again and it picks up only the gaps — already-filled rows are skipped, and manual rows are never touched.

python3 civic.py scrape-sites   --state WA   # fills only missing sites
python3 civic.py scrape-youtube --state WA   # fills only missing channels
python3 civic.py scrape-agendas --state WA   # fills only missing agendas

Each scrape writes results row-by-row inside transactions, so an interrupted run leaves the database consistent and the next run resumes from the gaps. Omit --state on scrape-youtube/scrape-agendas to process every state in the database at once.

Manual corrections — the export → edit → import round-trip

Automated discovery will miss or mis-identify some sites. You correct them by editing a CSV exported from the database, then importing it back. Manual rows are authoritative and are never overwritten by future scrapes.

Workflow:

1. Export the rows you want to review. You can target exactly the gaps:

   python3 civic.py export --state WA --needs site --out wa_fix.csv

   (Drop --needs to export everything; drop --state to export all states.)

2. Open wa_fix.csv in a spreadsheet and fix the bad rows: set official_site to the correct URL and set match_confidence to manual. The score is normalized to 9 for you. Works for both "no site" rows and wrong-URL rows. Don't change the geoid — it's the match key.

3. Import the edits back into the database:

   python3 civic.py import --in wa_fix.csv --cache-dir ~/civic/html_cache

   Each manual URL is fetched once to confirm it's reachable and to populate the HTML cache (so the YouTube/agenda stages work without re-fetching). A URL that doesn't resolve (or a manual row with no URL) is flagged manual-unreachable and listed in a warning.

Blank cells do not erase. An empty column in the CSV leaves whatever is stored in that field alone; import reports how many values it kept. This matters because an export is a snapshot: if you exported last month and the database has been backfilled or re-scraped since, every cell that was empty at export time is still empty in your file — and re-importing it would silently roll those columns back. That is not hypothetical. On 2026-08-22 an import of reviewed-YouTube CSVs exported before the 2026-08-21 population backfill wrote population='' over 35 freshly-filled Connecticut and New Jersey rows.

To genuinely blank a field, pass --clear-blanks, which is gated behind --yes and previews the damage first:

python3 civic.py import --in wa_fix.csv --clear-blanks          # dry run: lists what would be cleared
python3 civic.py import --in wa_fix.csv --clear-blanks --yes    # actually clears it

The gate only engages when there is something to lose — --clear-blanks on a CSV whose blanks match the database runs straight through. If you only meant to clear one field on one row, civic.py clear is narrower and safer than round-tripping a whole spreadsheet.

Why your edits survive: manual rows live in the same database as everything else, distinguished only by match_confidence = manual. The gazetteer upsert in scrape-sites explicitly skips manual rows, and the scrape stages only process rows still missing data — so a manual correction is never re-scraped or overwritten. There is no sidecar to manage; the database is the durable store.

Correcting YouTube channels and agenda pages

The same round-trip fixes a wrong or missing YouTube channel or agenda page, locked independently of the site (and of each other):

1. export the rows (e.g. --needs youtube or --needs agenda, or just the whole state).
2. In the CSV, set the correct value and mark its source column manual:
   • YouTube: set youtube_channel and youtube_source = manual.
   • Agenda: set agenda_page and agenda_source = manual.
3. import the CSV. These values are trusted as entered — not fetched or validated (unlike manual site URLs, which drive both later stages and so are still validated + cached).

A manual source locks that field: scrape-youtube / scrape-agendas skip it on every future run. This also lets you assert a negative — set youtube_channel to blank with youtube_source = manual to record "this city has no channel; stop looking." Each field is independent, so you can lock a channel while leaving the agenda open for the scraper, or vice versa.

Audit your corrections at any time (read-only):

python3 civic.py list-manual            # all states
python3 civic.py list-manual --state WA # one state

This prints every manual / manual-unreachable site row. The stats command shows per-field manual counts (mSITE, mYT, mAG) so you can see how many channels and agendas you've locked.

Zeroing out a contaminated row

If a row's official_site is flat wrong (e.g. a same-named city in another state), the YouTube and agenda data derived from it are wrong too. clear wipes the site and everything derived from it in one step, and (with --cache-dir) drops the now-wrong cached homepage:

# Blank site + youtube + agenda, leave the row OPEN for re-scraping:
python3 civic.py clear --geoid 7250123 --cache-dir ~/civic/cache

# Blank everything and LOCK it so the scraper won't re-fill it
# (use when you'll fix it by hand, or there is no correct site):
python3 civic.py clear --geoid 7250123 --lock

Without --lock the next scrape re-attempts the row. With --lock the site/youtube/agenda fields are set to manual so all three stages skip it; set the correct values later via the export → edit → import round-trip, or run clear again without --lock to reopen it.

Why was a verified URL flagged manual-unreachable? The importer fetches each manual site URL once; it only passes if the response is HTTP 200 with more than ~500 characters of HTML. A URL that works in your browser can still fail this if the site blocks non-browser User-Agents (a 403 from a WAF), is JavaScript-rendered (the raw HTML is a near-empty shell), or needs a scheme /www. you didn't include. The flag does not discard your URL — it's kept and still flows to later stages; only the one-time homepage caching was skipped. To see the exact reason per URL:

python3 civic.py diagnose                      # every manual-unreachable row
python3 civic.py diagnose --geoid 5377105      # one place by geoid
python3 civic.py diagnose --url https://x.gov  # any URL directly

It reports each step (scheme, DNS, HTTP status, body size), gives a verdict, and retries with a browser-like User-Agent to tell you whether that alone would fix it.

Automatic recovery from User-Agent blocks: the fetcher identifies itself honestly (Civic-Research/1.0) by default, but if a request is refused with a 401/403/429 it automatically retries that one request with a browser-like User-Agent. So sites behind a WAF that blocks non-browser agents are handled transparently — you don't need to do anything. If some sites were flagged manual-unreachable before this behavior existed, just re-import the same CSV: the rows are re-validated and flip to manual once they fetch.

Mislabeled status codes: some municipal servers and bot-mitigation layers return a full, real page under an odd non-200 status (e.g. a 415 with 400 KB of HTML — observed on Puerto Rico municipio sites). The fetcher accepts any response carrying a substantial body, so these pages are used rather than discarded on a status technicality, while genuine error pages (small bodies) stay rejected.

Non-English pages: site verification recognizes Spanish-language civic terms ("municipio de", "gobierno municipal", "alcaldía", "asamblea municipal"), so a legitimate Puerto Rico municipio page (often a bare <name>.com or <name>.pr.gov) clears the confidence bar — while an unrelated same-named .com with no civic content is still rejected.

Caching

The pipeline caches two things to avoid redundant network work.

Gazetteer (--gazetteer PATH)

The pipeline may use up to three national Gazetteer files (Places, County Subdivisions, Counties — see Governance models above). Each is downloaded at most once and cached on disk; subsequent runs (any state) load from disk.

--gazetteer accepts either a directory (the three files are stored inside it under their standard Census names) or a specific .zip (used as the Places file, back-compat; the other two are derived as siblings in the same directory).

• Default location: ~/.civic_cache/ (e.g. ~/.civic_cache/2024_Gaz_place_national.zip, …cousubs…, …counties…).
• Override with --gazetteer on scrape-sites, or set GAZETTEER_PATH.

Only the file(s) a given state needs are downloaded — a DEFAULT state fetches just Places; a New England state also fetches County Subdivisions; Hawaii and Puerto Rico fetch Counties. Only scrape-sites reads the gazetteer.

Homepage HTML (--cache-dir DIR)

scrape-sites saves each official homepage it fetched (keyed by geoid, as DIR/<geoid>.html); scrape-youtube and scrape-agendas read homepages from the same DIR instead of re-fetching — turning three homepage fetches per city into one. import also caches the homepage of each validated manual URL.

This cache is on by default: if you don't pass --cache-dir, it defaults to ~/.civic_cache/html/ (a flat html/ subdirectory alongside the gazetteer cache), so caching "just works" across runs. Files are stored flat — GEOIDs are globally unique, so no per-state subdirectories are needed. Override the location with --cache-dir, or set the CIVIC_CACHE_DIR environment variable.

Cache control flags on Stages 2 and 3:

Note: only homepages are cached. scrape-agendas still fetches each discovered agendas subpage live, since that URL isn't known until runtime.

All command-line options

Global: --db PATH (database to use, default civic.db) goes before the subcommand: python3 civic.py --db wa.db stats.

The three scrape-* commands share: --cache-dir DIR, --workers N (default 8), and a selection option for partial runs:

• --limit N — the first N items (deterministic).
• --sample N — N items chosen at random (sorted by geoid). Mutually exclusive with --limit.
• --seed S — fix the random seed for --sample (reproducible subset).

scrape-sites

--state STATE     USPS code or full name. Required.
--gazetteer PATH  Gazetteer zip location (load if present, else download+save).
--cache-dir DIR   Save fetched homepages here for the youtube/agenda stages.

export (with population filters)

--state STATE     One state (default: all).
--needs FIELD     Only rows missing site / youtube / agenda.
--min-pop N       Only rows with population >= N   (applied live, no reload).
--max-pop N       Only rows with population <= N.
--tier XL,L,...   Only these size buckets (XL/L/M/S/XS).
--sort-pop        Order biggest-first (unknown population last).
--format FMT      Field delimiter: csv (default), psv (vertical bar '|'),
                  or tsv (tab). For psv/tsv the delimiter is also stripped
                  from values so every row splits cleanly for naive tools.
--psv             Shorthand for --format psv.
--out FILE        Destination file. Required.

Population thresholds are computed at query time from the stored number, so a different cutoff is just a different --min-pop — never a reload. Volunteer triage — biggest governments still missing an agenda, largest first:

python3 civic.py export --needs agenda --min-pop 25000 --sort-pop --out gaps.csv

Pipe-delimited (PSV) output for tools that prefer it — the | character is stripped from any field value first, so a scraped description containing a pipe can't shift columns:

python3 civic.py export --psv --out all.psv          # or --format psv

CSV output is plain, greppable text

Scraped fields (agenda_description, copyright_statement, …) come from arbitrary HTML and can carry control characters — CR (^M), shift-out/in (^N/^O), NUL. Left alone these make grep report "binary file matches" and break normal text tooling. Every CSV this pipeline writes is therefore sanitized: embedded CR/LF collapse to a space (so one row is always one physical line), other control bytes are removed, and lines end with a plain Unix \n rather than the CSV-spec \r\n. Values are otherwise preserved.

Data scraped before this was added is still dirty in the DB. Scrub it in place (no re-scrape needed):

python3 check_rescrape.py --sanitize-text         # dry run: what's affected
python3 check_rescrape.py --sanitize-text --yes   # scrub

adjudicate-dupes (who does this domain actually belong to?)

--state STATE    Limit to one state.
--report FILE    Full per-claimant scoring to CSV.
--clear-losers   Blank the non-owning rows (needs --yes).
--yes            Confirm the clear.

dupe-sites shows which rows share a domain; this answers who owns it. Each duplicated domain is fetched once and scored with the current verifier against every claiming place:

• WINNER — one place verifies; the others are cleared and reopened for re-scrape.
• NO-OWNER — nobody verifies (a generic site like liberty.org, or a real-estate/domain-sale host). All claimants are cleared.
• TIE — several verify equally; left for a human.

python3 civic.py adjudicate-dupes --report adj.csv          # dry run
python3 civic.py adjudicate-dupes --clear-losers --yes      # apply
python3 civic.py scrape-sites --state TX                    # re-find the real ones

dupe-sites (find shared-domain false positives)

--state STATE  Limit to one state (default: all).
--top N        How many duplicated domains to print (default 40).
--report FILE  Write EVERY individual row behind each duplicate to CSV.

A domain used by more than one place is nearly always a false positive. The report assigns each a verdict:

• REJECT-ALL — a known non-government host (real-estate portal, domain marketplace) or a domain-sale URL. Every row using it should be cleared.
• REVIEW — a plausible municipal domain shared by several places (the "same city name, wrong state" case). One row probably owns it; the rest need clearing. The CSV includes population / pop_tier so a volunteer can see which place most plausibly owns the domain.

python3 civic.py dupe-sites                            # summary
python3 civic.py dupe-sites --report dupes.csv         # + every row, for review
# then clear the wrong ones:
python3 civic.py clear --geoid <geoid>                 # reopen for re-scrape
python3 civic.py clear --geoid <geoid> --lock          # keep empty

Progress reporting on long runs

Every stage reports [done/total], but the format adapts to the run length so a 16,000-row job does not bury its own signal:

• Short run (<= 200 items): one line per item, as before.
• Long run: a rolled-up counter line every 100 items, with rate and ETA.

[100/1200]   8% | confident=80  low=15 miss=2  unverified=3  | 85.1/s eta 12s
[600/1200]  50% | confident=535 low=35 miss=10 unverified=20 | 84.6/s eta 7s

--verbose forces per-item output on any run.

Redirected output is line-buffered. civic.py calls sys.stdout.reconfigure(line_buffering=True) at startup, so civic.py ... > run.log & shows progress in the log as it happens. Without it Python block-buffers to a pipe or file and a long run writes nothing until it exits — which reads exactly like a hung process. (Observed: seven minutes of silence from a wiki import that was working fine.) You do not need python3 -u.

Review worksheets (make_*_review.py / harvest_*_review.py)

Two markable-text workflows for decisions the automatic signals cannot settle. Both follow the same conventions as the YouTube worksheets: one URL per line, alone and last, boxes pre-marked with a recommendation, and a --check pass before anything is written.

python3 make_site_review.py                     # sites reverify scored below the floor
python3 harvest_site_review.py --check          # counts / conflicts / unmarked
python3 harvest_site_review.py --apply --yes

python3 make_dupe_review.py                     # one decision per SHARED DOMAIN
python3 harvest_dupe_review.py --check
python3 harvest_dupe_review.py --apply --yes

FIX -> <url> beats every box. If you had to look a town up to decide, record what you found instead of clearing the row and waiting for a later scrape to rediscover it. A FIX is written verbatim as match_confidence='manual', score 9 — never fetched, never validated, never second-guessed. A URL a human opened in a browser is ground truth; a fetch failure here is a fact about this scraper, not about the site.

An unmarked row does nothing. Unlike the YouTube flow (where unmarked meant import), silence never clears a site — the destructive direction requires an explicit mark.

make_dupe_review.py decides per domain, not per row, with three verdicts:

A shared domain is not a duplicate. 36 of 71 shared domains were coterminous New England pairs — one government appearing as place/city and cousub/town. Clearing the "loser" would blank one row of every New England city.

reverify --revalidate-unreachable

manual-unreachable is otherwise a terminal state — reverify skips MANUAL_STATUSES and needs=site excludes them, so nothing ever looks again. This re-tests rows that still hold a URL and promotes the responders to manual. On first run it recovered 18 of 18 live sites (Pomona, Merced, Tulare, Richland) that a single failed fetch had mislabelled months earlier. Rows with a blank site are left alone — those are deliberate "no site exists" decisions wearing the same label.

See docs/LESSONS_LEARNED.md §9-§12.

reverify (re-score existing matches)

--state STATE     Limit to one state (default: all non-manual matched rows).
--yes             Write updated confidence/scores (default: dry-run report).
--clear-failed    With --yes: clear rows now below the 'low' floor —
                  site + derived youtube/agenda fields blanked, cache entry
                  removed, row reopened for re-scrape. Manual rows are
                  never touched.
--refresh / --max-age DAYS   Homepage cache staleness controls.

Verification logic improves over time, but scrape-sites only visits rows with a blank site — old matches are never re-examined. reverify re-scores every already-matched site with the current verifier, reading homepages from the cache (live fetch only on a cache miss), and reports upgrades, downgrades, and outright failures (e.g. wrong-state false positives caught by newer checks). Typical flow after a verifier improvement:

python3 civic.py reverify --state WA              # dry run: see what changes
python3 civic.py reverify --state WA --yes --clear-failed
python3 civic.py scrape-sites --state WA          # re-discover cleared rows

load-backflush-towns (surgical township add)

--in FILE     CSV with state + city_name columns — typically the
              <report>_unmatched.csv from import-backflush.
--gazetteer P Gazetteer cache location (dir or .zip).
--yes         Add the rows (default: dry-run report).

For the township-belt states (NY/PA/MI/NJ/WI/…), a back-flush "Town of X" row often has no match because only the places layer was loaded, not the County Subdivisions (cousub) layer where townships live. Loading a whole state's townships would add thousands of mostly-unwanted rows, so this command does it surgically: it treats the unmatched list as a whitelist and adds only those specific towns, looked up in the cousub gazetteer. Townships parse as place_type='town', source='cousub'. Rows not found in the cousub file (CSV spelling variants, non-cousub places) are reported and skipped. Typical A-pass flow after an import-backflush dry run:

python3 civic.py load-backflush-towns --in bf_review_unmatched.csv         # preview
python3 civic.py load-backflush-towns --in bf_review_unmatched.csv --yes   # add towns
python3 civic.py import-backflush --in civic_backflush_2026-05-26.csv --yes # attach channels

import-backflush (CMA channel back-flush)

--in FILE   CMA back-flush CSV (dircode, state, city_name, org_name,
            video_channel, channel_platform, ia_collection_url, in_cma, …).
--yes       Apply (default: dry-run report).
--report F  Write the FULL unmatched + ambiguous + resolved lists to
            F_unmatched.csv / F_ambiguous.csv / F_resolved.csv. Unmatched rows
            carry a fuzzy 'did_you_mean' nearest-city suggestion.
--apply-disambiguated
            Also apply rows the org-name rule resolves ('City of X' picks the
            city row over a same-named town/village/cousub; place-source is the
            tiebreak). Needs --yes. Rows the rule can't resolve stay ambiguous.

Imports archive-verified video channels so onboarding volunteers don't re-derive them. Filters (case-insensitive): rejects any org_name containing Library / School / University / College; requires a municipal signal in org_name — city or town, or a consolidated-government word (consolidated / metro / metropolitan / unified / municipal) so consolidated city-counties (Augusta, Nashville, Louisville, Athens…) match. Plain counties/parishes/boroughs — not loaded outside HI — are still excluded. Matching normalizes names, so a backflush "Augusta" reaches the places-file row "Augusta-Richmond County consolidated government (balance)". Matches by (state, city_name); ambiguous city+state (more than one row) is skipped and reported. Stores video_channel + channel_platform + channel_source='cma_backflush', keeps youtube_channel in sync for YouTube rows, and records in_cma / cma_collection_url. Imported channels are locked (the crawler skips youtube_source='cma_backflush') and never clobber an existing or manually-locked channel (fill-only-where- empty). Dry-run first to see the filter/match breakdown:

python3 civic.py import-backflush --in civic_backflush_2026-05-26.csv        # preview
python3 civic.py import-backflush --in civic_backflush_2026-05-26.csv --yes  # apply

import-wiki-sites (Wikipedia official-site back-flush)

--in FILE          wiki_official_site.py output CSV (geoid-keyed).
--state XX         Limit to one state.
--yes              Apply (default: dry-run report).
--confident-only   Apply only strong 'confident' place-matches; the rest go to review.
--strict           Use the scraper's floor (confident/low only; name-only -> review).
                   Default TRUSTS the wiki link and keeps name-only matches.
--report F         Write F_applied.csv and F_review.csv.
(also --cache-dir / --workers / --limit / --sample / --seed — shared scrape args)

Back-flushes the sites wiki_official_site.py found into places that are still site-blank (fill-only; never clobbers an existing or manual value). A Wikipedia official-site link is editorially curated — far better than the scraper's URL-guessing — so the URL is trusted unless there is negative evidence: each candidate is fetched and scored with the same place-match verifier scrape-sites uses, but only a parked/real-estate page or a strong wrong-state signal is rejected. The scorer's "name-only, no state evidence" case is kept (tier namematch) rather than thrown out — that is exactly the good small-town site Wikipedia handed us. A URL civic_common can't re-fetch is still kept when the wiki tool already verified it (verified) or when it answered a bot-block code like 403/406/5xx (blocked — the host is live); only a 404 or dead connection lands in unreachable review. Applied rows are tagged match_confidence='wiki-<tier>' (fill-only) and their homepage cached for the youtube/agenda stages. Dry-run first, then apply and adjudicate duplicates:

python3 civic.py import-wiki-sites --in wiki_official_sites.csv --state WA --report /tmp/wa  # preview
python3 civic.py import-wiki-sites --in wiki_official_sites.csv --yes --report /tmp/wiki     # apply
python3 civic.py dupe-sites          # catch shared / regional URLs
python3 civic.py adjudicate-dupes

load-population (opt-in, run once)

FILES...          One or more 2020 Decennial population files (place / county
                  P1 tables; CSV or pipe/tab-delimited; prefixed GEOIDs OK).
--threshold N     Population at/above which pop_over_threshold='yes'
                  (default 10000).
--yes             Write the values (default: dry-run report).

Joins population onto existing rows by GEOID — it never adds places, so run it after your places exist. It matches any geography whose GEOID is in your DB, so to cover incorporated places and New England cousub towns and counties, load all three 2020 Decennial P1 files (Place, County Subdivision, County); the loader keys purely on GEOID, so each row is filled from whichever file contains it.

Only the raw population number and its pop_tier bucket are stored. Any population threshold is applied at retrieval time, not baked in at load — so you never reload to change a cutoff (see export --min-pop below). Get the files from data.census.gov (2020 Decennial table P1, "Total Population", for geographies Place and County), or the Census API. The loader auto-detects the GEOID and P1_001N columns and strips the 1600000US… / 0500000US… prefixes. Dry-run shows the tier distribution and how many rows clear the threshold before writing.

Volunteer-triage example — export the biggest cities still missing an agenda:

python3 civic.py load-population place_P1.csv county_P1.csv --yes
python3 civic.py export --needs agenda --out gaps.csv
#   then sort/filter gaps.csv by pop_tier / pop_over_threshold

scrape-counties (opt-in county layer)

--state STATE     One state's counties (mutually exclusive with --all-states).
--all-states      Load counties for every state.
--gazetteer PATH  Gazetteer cache location.
--cache-dir DIR   Save fetched homepages here.
(also supports --limit / --sample / --seed / --workers)

scrape-youtube / scrape-agendas

--state STATE     Limit to one state (default: every state in the DB).
--cache-dir DIR   Read homepages from cache instead of fetching.
--refresh         Re-fetch homepages live, ignoring the cache.
--max-age DAYS    Re-fetch cached homepages older than DAYS.

scrape-youtube also:
--dry-run         Detect channels but DON'T write the DB. Writes a review CSV
                  instead (see --report), one row per proposed channel with its
                  video count, resolved handle/title, and a review_flag:
                    OK-STATE-CONFIRMED   channel's page names this state
                    REVIEW-UNCONFIRMED   names no state — eyeball it
                    REVIEW-WRONG-STATE?  names another state — probably wrong
--report FILE     Where --dry-run writes the CSV (default youtube_dry_run.csv).
--include-siteless
                  Also try rows with NO official_site (default: skip them).

--include-siteless. Stage 2 has two detection paths: read the official site's homepage/subpages, and — when that finds nothing — search for the channel by name. Only the first needs a site. The two were coupled when scraping the homepage was the only method, and the search fallback, added later, inherited a precondition it never needed. The result was that 5,012 rows could never be reached by a channel search at all, including Chula Vista, Gilbert AZ, North Las Vegas and Richmond VA.

The flag drops that filter (11,407 → 16,419 candidate rows). The added rows are 96% XS with a median population of 222, so the hit rate is low — pair it with a population or tier bound rather than running it across all 5,012. On the five largest site-less rows it found two real municipal channels: Gilbert AZ ("Gilbert, Arizona", 1,900 videos) and Richmond VA ("Richmond Virginia", 995).

See docs/2026-08-22-site-coverage-analysis.md for the measurement.

The review CSV is directly importable: geoid, youtube_channel, and youtube_source are real columns, the rest are review-only. Delete the rows you reject, then import the file to apply the good ones — a dry-run first, commit second workflow that avoids a bulk back-out.

redirect-youtube (prototype)

--state STATE   Limit the SOURCE places searched to one state.
--dry-run       Required. Write a CSV of proposed redirects; never writes the DB.
--report FILE   Where the CSV is written (default youtube_redirects.csv).

A search for one place often surfaces the real, active channel of a different same-named place ("City of Merrill", WI shows up while searching Merrill, OR). Rather than only rejecting it, redirect-youtube redelivers it to the place it belongs to — a blank, unlocked, same-name row in the DB — disambiguated by one of: a distinct name (only one place in the country carries it, e.g. Oakland Park → FL), an explicit state named in the channel, or (LOW confidence) a state code in the handle. Each proposal carries a match_basis and confidence; the CSV is importable like the --dry-run one (review, delete, import). The Census geoid is what makes this safe — it turns "same name" into an exact place identity. Prototype: dry-run only, and it only redelivers government channels (a newspaper or church belongs to no civic slot); genuinely ambiguous cases (a common name with no state signal) are left out rather than guessed.

export

--state STATE     Limit to one state (default: all).
--needs FIELD     Only rows missing this field: site | youtube | agenda.
--out PATH        Output CSV path. Required.

import

--in PATH         CSV to import (must have a geoid column). Required.
--cache-dir DIR   Cache homepages of validated manual URLs here.
--workers N       Concurrency for manual-URL validation.

list-manual / stats

list-manual [--state STATE]   Print manual / manual-unreachable rows.
stats                         Per-state coverage summary.

Typical workflows

Process several states into one database, gazetteer downloaded once:

for st in WA OR CA; do
  python3 civic.py scrape-sites --state $st \
      --gazetteer ~/civic/gaz.zip --cache-dir ~/civic/cache
done
python3 civic.py scrape-youtube --cache-dir ~/civic/cache   # all states
python3 civic.py scrape-agendas --cache-dir ~/civic/cache   # all states
python3 civic.py stats

Refresh a months-old dataset cheaply (only re-fetch homepages older than 30 days; everything fresh is reused):

python3 civic.py scrape-youtube --cache-dir ~/civic/cache --max-age 30
python3 civic.py scrape-agendas --cache-dir ~/civic/cache --max-age 30

Test on a handful of cities first:

# First 10 (deterministic):
python3 civic.py scrape-sites --state WA --limit 10

# A random 10, reproducible via the seed (unbiased spot-check across the
# whole alphabet rather than just A-named cities):
python3 civic.py scrape-sites --state WA --sample 10 --seed 42

Politeness & good citizenship

• Requests are rate-limited per host and run under a worker pool, so unrelated city sites are hit concurrently but no single host is hammered.

• Set a real contact address in the USER_AGENT string in civic_common.py before large runs.

• Consider checking each site's robots.txt. These are public .gov sites, but courtesy still applies.

• Identify honestly first; escalate only when refused. Every request goes out under the plain USER_AGENT. Only after a site has explicitly refused us — a 401/403/429, or a body carrying a bot-mitigation challenge signature — do we retry with the full browser header set (BROWSER_HEADERS: Sec-Fetch-*, sec-ch-ua, a real Accept). Sites that don't block us never see anything but the honest agent.

  This matters more than it used to. Municipal sites increasingly run enterprise bot-mitigation, and a User-Agent alone does not satisfy Akamai — it answers with a ~400-byte "Access Denied" stub. Because a WAF stub and a 404 stub are both short, the old "small body = error page" heuristic threw live sites away as unreachable: measured over 92 blocked candidates, UA-only recovered 16 while the full header set recovered 44. Chula Vista went from a 412-byte stub to 296 KB of real page.

  _try_url also upgrades a failed http:// candidate to https://, and records why a fetch failed — last_fetch_failure() returns blocked | dns | dead | error. Callers previously saw only (None, None) and could not distinguish "this town has no website" from "this town's WAF refused us": opposite facts, one a finding about the world and one a blind spot in our own tooling.

Extending

civic_common.py still has one stub you can implement to raise hit rates:

• search_fallback(query) — return a best-guess official-site URL from a search API (used by Stage 1 when URL guessing fails). Returns None by default, so Stage 1 runs without API keys.

The Stage-2 youtube_search_fallback(place_name, usps, place_type) is now implemented (no API key needed): it scrapes YouTube's channel-filtered results page, parses the embedded ytInitialData, scores candidates on name / state / civic-vocabulary / municipal-handle-pattern signals, and requires government evidence before trusting a hit. detect_youtube consumes the ranked list via _youtube_ranked_channels(...) and verifies down it, preferring a channel whose page confirms the target state. On Oregon's hand-verified set it reproduces ~94% of manually-found channels (and the misses are places whose channel simply doesn't surface for the query, not bad guesses). Because it scrapes an undocumented response shape, keep it behind the homepage/subpage scans (its natural place) and expect to re-check the ytInitialData parser if YouTube changes their markup.

cma_onboard.py — onboarding new channels into the Community Media Archive

civic.py answers "which places publish?". cma_onboard.py answers the next question: which of those are not yet in the Community Media Archive, and what should their collections be called? It reads civic.db, picks the cohort, mints each place a free mnemonic collection identifier, and writes the spreadsheets the ia CLI consumes.

It creates nothing on archive.org. It only reads (?mode=ro on every database) and writes local files for you to review.

Standard library only, Python 3.8+, and it runs unchanged on Windows, macOS and Linux. The internetarchive Python library is used if you pass --use-ia-lib and it imports; otherwise availability checks go over plain HTTPS.

What "new" means

A place is a candidate when it has a video channel and is not already preserved. Four exclusions, cheapest first:

The confidence gate. By default only channels the project already trusts are onboarded: a dry-run review_flag of OK-STATE-CONFIRMED, or a youtube_source of homepage / manual. An unreviewed search hit is not auto-onboarded — that's the "never auto-onboard a low/unverified match" rule from civic-data-integration.md, enforced in code. Widen it deliberately with --include-unconfirmed, --accept-flag, or --any-flag.

The slug convention

The identifier is minted, not chosen by hand, and it obeys one contract:

4–11 characters, [A-Za-z0-9] only, the 2-character state code in the last two positions — followed by a single v when the channel is Vimeo.

That contract was derived from the 749 existing CMA collections recorded in civic.db; 96% of them already comply, and the generator reproduces 25 of 25 hand-checked real slugs (median rank: 1st candidate).

[type prefix][name mnemonic][state][v if vimeo]

cormd     City of Rockville, MD          co + r    + md
tobme     Town of Brunswick, ME          to + b    + me
coscia    City of Sioux City, IA         co + sc   + ia
cowcca    City of Walnut Creek, CA       co + wc   + ca
tosmyrtn  Town of Smyrna, TN             to + smyr + tn
clvrdca   Cloverdale, CA                      clvrd + ca
hemetca   Hemet, CA                           hemet + ca
copoflv   City of Port Orange, FL (Vimeo) co + po + fl + v

For each place the generator emits a ranked ladder of contract-valid candidates — co+initials first (the house style), then the bare name, then consonant skeletons, then digit-disambiguated variants — and takes the first one that is actually free. Preview a place's ladder any time:

python3 cma_onboard.py slug "Walnut Creek" CA --place-type city
python3 cma_onboard.py slug "Smyrna" TN --place-type town --check   # ask IA too

Proving the identifier is free

This is the part that matters: a collection identifier must not exist at the moment it is created. Two gates:

1. Local — every ia_collection in collector.db (~2,000), every cma_collection_url in civic.db (~750), anything in a --reserve file, and everything minted earlier in the same run. Free, catches the common case, and guarantees the batch is internally unique.
2. Remote — the metadata API first, then archive.org's own check_identifier service. An identifier counts as free only when both agree; anything ambiguous comes back unknown and is refused, so a single endpoint hiccup can never read as "free".

That order is a deliberate optimisation. check_identifier answers in ~0.6 s when an identifier is taken but takes **~11 s** when it is available, while the metadata API answers in ~0.4 s either way. Testing metadata first means the slow call happens roughly once per slug actually minted, rather than once per candidate tried as the ladder falls through. In practice a 12-collection cohort cost 25 archive.org calls.

Three speed/safety settings:

Results are cached to <out-dir>/.ia_identifier_cache.json, so re-planning a cohort is nearly free.

Work state by state. Checks are sequential and paced by --delay — good citizenship toward archive.org, the same discipline the scrapers follow. --state is also the natural review unit: a manifest you can actually read before consenting to it.

Budget the time — this is slow by nature, not broken. archive.org answers "is this identifier free?" in ~10-25 s when the answer is yes, and it must be asked once per slug actually minted. So:

A real VT run: 45 collections in ~11 minutes. The tool prints one line per place with elapsed time and an ETA, so you can watch it move. Ctrl-C is safe — the availability cache is flushed every 10 places and again on exit, so a re-run skips everything already checked.

Because identifiers can be claimed by anyone between planning and creation, run verify immediately before you create:

python3 cma_onboard.py verify --manifest onboard/VT/cma_onboarding_manifest.csv

It re-checks every slug, re-validates the contract, catches duplicates, and exits non-zero if anything is taken, invalid, or unverifiable.

What it writes

The collection metadata matches the existing CMA collections exactly — mediatype=collection, parents community_media / newsandpublicaffairs / movies, collectionsource=patron, subject = "Government Access Television" + the state name, and the house-style description:

This collection is an archive of the "Town of Smyrna" Youtube channel: https://www.youtube.com/@townofsmyrna<br>The intent is that it will be updated roughly weekly.

--make-start-files writes a placeholder start-file.txt per collection and points the sheet's file column at it — IA needs at least one file to bring an item into existence, which is exactly how the existing collections were created.

The slug is also the directory name

collector.db stores metadata_dir = <root>/<ia_collection> for every channel — the identifier and the on-disk directory name are the same string. Create the tree from a manifest:

python3 cma_onboard.py make-dirs --manifest onboard/VT/cma_onboarding_manifest.csv \
    --root ~/youtube-archive/metadata --dry-run

Full example

# plan one state, trusting only state-confirmed channels
python3 cma_onboard.py plan \
    --db ~/city-county-civic-scans/v10/civic.db \
    --collector-db ~/datasette-project/collector.db \
    --reports "reports/youtube_*.csv" \
    --state VT --make-start-files --out-dir onboard/VT

# review onboard/VT/cma_onboarding_manifest.csv, delete rows you reject
# re-prove the identifiers, then create
python3 cma_onboard.py verify --manifest onboard/VT/cma_onboarding_manifest.csv
ia upload --spreadsheet=onboard/VT/ia_collections_upload.csv

--reports is what makes the descriptions good: the scrape-youtube --dry-run review CSVs carry channel_title, video_count and review_flag, none of which are in places. The globs are opened read-only — the tool never writes into reports/, so it is safe to point at a sweep that is still running.

Reports enrich; they do not, by default, recruit. A dry-run CSV routinely proposes a channel for a place whose places.youtube_channel is still empty — that is the whole point of the review queue (review → delete rejects → civic.py import). Onboarding straight from those rows would create IA collections ahead of the human review, so by default such a place is skipped with reason channel-not-yet-imported, and the CSV is used only to enrich places whose channel is already in civic.db.

Pass --reports-as-source to opt in deliberately — worth doing only for states you have already reviewed. On the current data the difference is large:

Closing the loop

Once the collections exist, write back to civic.db so those places stop appearing as new — set in_cma='yes' and cma_collection_url='https://archive.org/details/<slug>' per geoid (import-backflush is the supported route). Until you do, a re-plan will propose them again — though it will mint different slugs, because the first ones are now taken.

All command-line options

plan        --db PATH                civic.db (read-only). Source of youtube_channel.
            --collector-db PATH      already-onboarded channels + known-taken identifiers
            --archive-db PATH        more known-taken identifiers (dirname)
            --reports GLOB           scrape-youtube --dry-run CSVs (repeatable, read-only)
            --reports-as-source      also onboard channels that exist only in a
                                     --reports CSV and are not yet imported
            --reserve FILE           extra identifiers to treat as taken (repeatable)
            --out-dir DIR            where the spreadsheets go (default ./onboard)
            --state ST               limit to one state
            --platform youtube|vimeo
            --limit N                cap the cohort (highest population first)
            --min-videos N           needs --reports
            --min-population N
            --accept-flag FLAG       review_flag values to accept (repeatable)
            --include-unconfirmed    also accept REVIEW-UNCONFIRMED
            --any-flag               accept everything (review the manifest!)
            --parent A,B,C           parent collections
            --metadata-root PATH     recorded in the manifest's metadata_dir
            --make-dirs ROOT         also create ROOT/<slug>/ now
            --make-start-files       placeholder file per collection
            --no-hidden              omit the hidden=true column
            --fast-check             metadata API only (much faster; verify still full)
            --offline                no network; slugs marked UNVERIFIED-OFFLINE
            --cache PATH             availability cache
verify      --manifest FILE [--db PATH] [--collector-db PATH] [--reserve FILE]
check       IDENTIFIER [IDENTIFIER …]
slug        CITY STATE [--place-type T] [--platform P] [--top N] [--check]
make-dirs   --manifest FILE --root DIR [--dry-run]

network (plan/verify/check/slug): --delay SECS (default 0.5) --timeout SECS
            --retries N --use-ia-lib --verbose

youtube_triage.py — triaging the review queue

scrape-youtube --dry-run proposes channels; a human decides. On the 48-state sweep that was 1,348 rows flagged for review — days of work, most of it repetitive. This tool doesn't decide correctness; it sorts proposals by how they can be decided, so attention goes where judgement is actually needed.

python3 youtube_triage.py --reports "reports/youtube_*.csv" --out-dir triage
python3 youtube_triage.py --reports "reports/youtube_*.csv" --apply   # strip AUTO-REJECT

Runs in 0.3 s over 2,000 proposals. Writes <ST>_triaged.csv per state plus _auto_reject.csv, _group_decide.csv, _needs_review.csv, _name_match.csv.

GROUP-DECIDE is the biggest win. One channel proposed for several places — "City of Mount Vernon" for Mount Vernon in GA, IN, MO and OH — is one decision, not four investigations. At most one place can be right.

AUTO-REJECT is deliberately tiny. Only two rules are trusted to delete unread: a wrong entity (civic_common's own reject-entity logic — a dealership, chamber, EDC, hospital, church, school) and a longer real place that contains this one: Belle ← "City of Belle Meade", Hoyt ← "City of Hoyt Lakes", Eden ← "City of Eden Prairie", Freedom ← "New Freedom Borough".

What is deliberately NOT auto-rejected

Three rules were tried and pulled back, each after finding a false positive that would have deleted correct data:

• "names a different place" → demoted to NEEDS-REVIEW. It fires on ordinary words that are also place names (Media PA, Brandon MN, Wright WY) and produced a real casualty: Kansas City, KS ← "Unified Government of Wyandotte County" — which is the correct channel, because KCK is that unified government.
• <place> + a civic word → excluded. Knox, ME ← "Knox City Council" matched Knox City, MO; Murray City Live matched Murray City, OH. Usually it's this place plus its own institution, not a different municipality.
• bare inc / llc / corporation in the entity list → removed. PEG stations are routinely incorporated nonprofits (Stoughton Media Access Corporation, Granby Community Access & Media, Inc.) and are exactly what the CMA archives. Narrower area development / development corp is used instead — false-positive rate against 1,589 known-good CMA channel names: 0.13%.

NAME-MATCH is not an import instruction. "The title names this place and the channel is proposed nowhere else" is strong but not proof — the residual risk is entity type, which reads instantly from a title. Validated against 17 ground-truth cases (channels the survey proved wrong, and rows known correct): 0 leaked into NAME-MATCH, 0 wrongly auto-rejected.

cma_survey.py — sequencing the download queue

cma_onboard.py decides who is eligible and mints their slug. cma_survey.py decides what order the workers actually do the work in, by looking at what is really on each channel before a single byte is downloaded.

python3 cma_survey.py --manifest onboard/VT/cma_onboarding_manifest.csv \
    --out-dir survey

Downloads nothing — it fetches channel listings only. Stdlib + yt-dlp, Windows/macOS/Linux. Re-running skips channels already surveyed, so a state can be resumed or a single channel redone (--redo --slug tomivt).

Why a separate program

It shares no code path with identifier minting and shouldn't: different remote host (YouTube vs archive.org), different politeness budget, different failure modes, and this one will change often as the sequencing is tuned. The manifest CSV is the contract between them — cma_onboard.py writes it, this reads it.

Four questions, all from cheap metadata

1 · Which titles collide. The pipeline turns a video title into an IA identifier, and title-derived failures are the known top cause. This models the real transform and flags two distinct problems:

• duplicate titles — two videos literally named the same thing;
• identifier collisions after truncation — strictly worse and easy to miss: IA identifiers cap at 100 characters, so two different 90-character titles can truncate to the same identifier.

Both land in needs_date_suffix, which is your "append a date to disambiguate" worklist.

The transform was derived empirically against 1,286 real (title, identifier) pairs from the codtx corpus in ~/youtube-archive/metadata/ and reproduces them 97.7% exactly. Rules: : and en/em dashes → _-_; @ → at; quotes and !? deleted; any other illegal character → _; runs of _ collapsed; ends trimmed; prefix <slug>-; truncate to 100. The residual 2.3% is human renames and titles edited on YouTube after upload, not transform error. If the real rule differs, fix derive_identifier() — everything else follows from it.

2 · Which channels have no meetings at all. Government channels that carry only promos and talk shows aren't CMA work. Titles are matched against a word-boundary vocabulary (council, selectboard, board of …, commission, planning and zoning, public hearing, work session, town meeting, …; extend with --meeting-words). A channel with zero hits is marked NO_MEETINGS and scored out of the queue rather than silently ranked low.

The vocabulary is the measurement instrument for meeting_hours, so it is derived from the existing CMA corpus (368,291 real titles in ~/youtube-archive/metadata/youtube_metadata.db) rather than guessed — and re-evaluated offline with --rescore --revocab, never by re-surveying. The method, including the closed-compound trap that once cost 945 meeting-hours, is §3.5 of the runbook.

3 · Dead vs active. Days since the most recent upload on either tab, banded ACTIVE ≤90d · SLOWING ≤365d · STALE ≤3y · DEAD beyond.

Two dates, deliberately. --flat-playlist returns no date at all unless youtubetab:approximate_date is passed, and that date is back-computed from YouTube's relative label, so it drifts badly with age — measured on real VT channels: 148 days off for Williston, 200 days for Barre. Good enough for ordering, useless for a dead/active call. So the tool also makes one cheap RSS request (feeds/videos.xml) for an exact timestamp of the latest upload. latest_upload_exact drives the banding; latest_upload_approx is kept beside it so the drift stays visible. --no-rss skips it.

4 · How big the job is. Video count split by tab, and total hours summed from the free duration field — so a worker can be handed a week of work instead of a surprise.

Both tabs

/videos and /streams are separate YouTube tabs and the bare channel URL does not reliably union them. Government channels routinely livestream meetings, so the survey fetches both and reports n_videos / n_streams separately. A channel whose meetings are all on /streams looks empty if you only ask for /videos.

Output

survey/
├── survey_summary.csv                  # every channel, all states, ranked
└── VT/
    ├── _channel_summary.csv            # this state, ranked + tiered
    ├── _channels_with_duplicate_titles.csv
    ├── tomivt_youtube_channel_flat_playlist_output.csv   # per video
    └── tomivt_duplicate_titles.csv                       # just the collisions

The per-channel CSV is one row per video: tab, position, id, title, duration, views, url, approximate date, is_meeting, plus derived_identifier, identifier_len, identifier_truncated, dup_title_count, identifier_collision_count and needs_date_suffix.

Priority, tuning, and capacity → see the runbook

priority_score ranks the queue; every input is a column beside it. The default objective is most hours of civic record preserved:

score =  2.0 × log10(meeting_hours + 1)      # the objective
       + 0.0 × log10(residents + 1)          # off: reach is a different goal
       + 0.8 × risk(activity)                # DEAD 1.0 · STALE 0.7 · ACTIVE 0
       - 0.5 × (items_needing_date / videos) # known upload-failure cause

meeting_hours is summed duration of meeting-like titles, so 40 three-hour council meetings outrank 200 ninety-second clips. status of EMPTY, UNREACHABLE, BAD_URL, NO_MEETINGS or DUPLICATE_CHANNEL scores negative and is tiered SKIP; the rest are cut into quartile tiers A–D.

Re-rank without re-fetching:

python3 cma_survey.py --manifest onboard/VT/cma_onboarding_manifest.csv \
    --out-dir survey --rescore --weight-urgency 0.4      # VT: 0.096s

Choosing weights, reviewing bad matches, sizing disk, and deciding when to spin up a worker on a new IP all live in docs/CMA_ONBOARDING_RUNBOOK.md. That is the document to read before sequencing a state — this section is only the mechanism.

Always pass --collector-db

Without it the queue will recommend re-downloading channels the fleet already has. civic.db's in_cma is partial by construction — only "City of" and "Government Access Television" collections were back-flushed into it, so PEG stations (catalogued as "Public Access Television") are archived but invisible there. And cma_onboard.py's offline check can only match a canonical /channel/UC… URL, so a /user/<name> or youtu.be/<id> URL silently passes as "new". In the VT pilot that put Brattleboro Community TV (brattvvt) and MCTV (mctvvtv) at ranks 1 and 2 — 62% of the state's ranked hours, all already preserved. The survey resolves the real channel_id, so it is the first place the question can be answered; matches are marked ALREADY_IN_CMA and excluded.

Review flags that stop a collection being created

The survey's main value is catching what onboarding's confidence gate could not. From the 45-channel VT pilot: a Kia dealership matched to Berlin (it passed OK-STATE-CONFIRMED because its name contains "Vermont"); youtube.com/privacy recorded as Londonderry's channel; three town/village slug pairs sharing one channel_id (which would create two collections fed by one channel); and the state's single most valuable channel — 5,474 meeting-hours of Brattleboro Community TV — attributed to the town of Marlboro.

So the survey emits BAD_URL, DUPLICATE_CHANNEL, NO_MEETINGS and a name_match column, resolves stray youtu.be/<id> links to their owning channel, and keeps the largest-population claimant when two slugs collide. Read name_match = no sorted by meeting-hours — regional stations legitimately don't carry their host town's name, so it is a review flag, not a rejection.

Working the GROUP-DECIDE queue

One channel claimed by several places is one decision, not N investigations. make_group_worksheet.py renders those groups as a markable text file; put an x in one box per group (KEEP beside the owning place, or REJECT ALL), then harvest_worksheet.py writes your marks into triage/_group_decisions.csv and the worksheet re-renders with your calls on top.

python3 make_group_worksheet.py     # render
$EDITOR triage/_group_decide_worksheet.txt
python3 harvest_worksheet.py        # marks -> CSV  (--dry-run to preview)

Lines carry g=<geoid> because several groups have two candidates in the same state (Waterbury CT city + town, Oakwood OH ×3) where a state-level answer cannot express the decision, and KEY= so a decision survives re-rendering. Full procedure and cautions: runbook §1.7.

--prune-onboarding — take the excluded rows back out

Surveying happens after cma_onboard.py has already written its spreadsheets, so a channel the survey excludes is still sitting in them. Removing it by hand is a trap: the slug appears in five artifacts, and the one that matters is not the one you would think to edit.

Editing only the manifest yields a clean-looking review file that still creates the collections. So prune all five together:

python3 cma_survey.py --manifest onboard/VT/cma_onboarding_manifest.csv \
    --out-dir survey --rescore \
    --collector-db ~/datasette-project/collector.db \
    --prune-onboarding onboard/VT --prune-dry-run   # preview, then drop the flag

Defaults to pruning ALREADY_IN_CMA only — the rows that would create a duplicate collection for something already preserved. That is the one case that is purely mechanical. --prune-status BAD_URL --prune-status DUPLICATE_CHANNEL extends it; NO_MEETINGS and name_match = no are deliberately not default, because they are judgment calls that belong to the human review pass.

Reversible and auditable: originals are copied to DIR/pruned_backup/ and every removed row is logged to DIR/pruned_rows.csv with its reason. The Vermont run removed 10 rows across 5 files for 2 slugs (tomavt → brattvvt, tomivt → mctvvtv), leaving 43 rows in each artifact.

NEXT_STEPS.txt is not pruned — it is generated narrative, not an input to any tool, so it goes stale rather than dangerous. Re-running cma_onboard.py plan regenerates it (and re-checks the surviving identifiers).

Failure honesty

yt-dlp exits 0 with valid JSON and an empty entry list when an extractor breaks, which is indistinguishable from an empty tab. The survey carries the same guard as the worker's enrich_missing.py and reports EXTRACTOR REGRESSION? in the channel's errors column rather than recording a zero. The RSS call likewise retries and reports throttling instead of returning a blank date that reads as "never uploaded".

Known limitations

• URL-pattern discovery can't find sites on domains that follow no convention; implement search_fallback for the long tail.
• JavaScript-rendered homepages may hide links from a plain HTTP fetch; those need a headless browser (not included).
• The HTML cache never expires on its own unless you pass --max-age (or --refresh); a redesigned site keeps serving the old cached copy.
• Township states are not yet special-cased — NY/NJ/PA/MI/WI/etc. return their incorporated cities/villages but not their civil townships. See Which places count above. New England towns and Hawaii's counties are handled via per-state source profiles.
• Scope is the general-purpose municipal + county tier. Special districts (school/water/fire), tribal governments, and independent authorities are out of scope.

↑ back to contents10.0-civic-data-pipeline(22)/civic-data-pipeline/README.md

IA Metadata XML → SQLite / Datasette

Ingest Internet Archive _meta.xml files (~3.2M items across ~2,200 collections) into a SQLite database for analysis with Datasette. Handles a weekly incremental sync of new/modified items and new collections, plus out-of-band detection of items that Internet Archive has "made dark."

Bundle layout

This bundle is organized so the documents are easy to send out for review and the code stays separate:

README.md                 ← you are here (entry point)
docs/                     markdown sources of every document
  docs/pdf/               the same documents as PDF   (for reviewers / posting)
  docs/docx/              the same documents as Word   (for academics & comment)
code/                     all runnable code, config, and fixtures
  code/conf/              skip-list, directory→collection map, secrets example
  code/fixtures/          sample caption files used by the caption design

Every document under docs/ is provided in all three formats — edit the markdown; the docx and PDF are regenerated from it by code/make_bundle.sh. (OVERVIEW is the one exception: its Word/PDF are built by a small dedicated script for a tight two-page layout.) File names below are given without their code/ or docs/ prefix for brevity.

Contents

Quick start

pip install lxml datasette

# 1. Build the directory->collection map
python3 build_map.py --root /mnt/md0/.../archive_stats \
    --out conf/directory_collection_map.psv --jobs 16 --include-identity

# 2. Initial bulk load (once)
python3 bootstrap.py --root /mnt/md0/.../archive_stats \
    --db /mnt/md0/scratch/archive.build.db --shard-dir /mnt/md0/scratch/shards \
    --map conf/directory_collection_map.psv --skip conf/skip.conf --jobs 36

# 3. Weekly incremental (cron)
python3 sync.py --root /mnt/md0/.../archive_stats --db /mnt/md0/datasette/archive.db \
    --map conf/directory_collection_map.psv --skip conf/skip.conf --auto --jobs 36

# 4. Dark reconciliation (separate cadence; needs IA keys)
set -a && . conf/ia_secrets.env && set +a
python3 dark_reconcile.py --db /mnt/md0/datasette/archive.db \
    --map conf/directory_collection_map.psv --skip conf/skip.conf --fraction 1.0

# 5. Serve
datasette -i /mnt/md0/datasette/archive.db --metadata datasette.yaml

Full procedures — including the build-then-swap publish flow and recovery scenarios — are in RUNBOOK.md. Read DESIGN.md first for the why.

Requirements

Python 3.10+, lxml. Everything else is stdlib (sqlite3, hashlib, urllib, concurrent.futures). Datasette for serving.

Security note

The dark job authenticates to Internet Archive with S3-style keys. Keep them in conf/ia_secrets.env (chmod 600), never in code or version control. If a key is ever exposed, rotate it at https://archive.org/account/s3.php.

↑ back to contents10.1-ia_metadata_xml_to_sql(26)/ia_metadata_xml_to_sqlite/README.md

Distributed Archive Worker Dashboard

A monitoring system for ~10 distributed Linux "worker" machines that download YouTube/Vimeo channels via yt-dlp (orchestrated by GNU parallel), process them through bash scripts, and upload to the Community Media collections on the Internet Archive (IA). The system tracks, per channel, how far each has progressed through the pipeline (discovered → downloaded → processed → uploaded → verified-in-archive), tracks machine health, and surfaces — in plain language for non-technical viewers — the few things that need a human.

How it fits together

  workers (agent.py)  ── POST status every 2 min ──▶  collector.py
   read-only observers                                 (always-on service)
   of the existing pipeline                            • ingest (fast write)
                                                        • rollup + alerting
  reconcile.py  ── hourly, queries IA ──▶ collector DB  • serves dashboard
   (separate process, holds IA creds)                  • SQLite (only durable state)
                                                              │
                                                              ▼
                                                        dashboard (in browser)
                                                        "is it working, and
                                                         what needs a human?"

Core principles: workers are dumb read-only counters that push; the collector owns all reconciliation, alerting, and rollup; reconciliation runs as a separate process so a slow IA query never blocks ingest; the channel (its canonical UC…/Vimeo id) is the stable join key; and everything is stateless-restartable with one SQLite file as the only durable truth.

Three numbers, three different sources (don't conflate them)

Per channel, the system tracks three counts that look similar but come from different places and answer different questions. Keeping them straight is the key to reading the dashboard correctly:

The division of labor follows the source: the agent (on each worker) talks to YouTube (remote_total) and the local filesystem (downloaded); the reconcile.py job (on the collector) talks to IA (verified). Reconciliation checks IA only — never YouTube and never the worker's disk.

Archival progress on the dashboard is verified / remote_total — an IA number over a YouTube number. It deliberately does not use downloaded, because a worker's local inventory reflects only what's on that machine now (especially on migrated or multi-generation workers), not what's actually preserved in the archive. verified is the authoritative, worker-independent measure of "is the content safe"; downloaded is a local operational detail shown as a secondary stat. (See the design doc's "Three numbers easy to conflate" for the full treatment.)

What's in this package

sql/

Project A aggregate rollups over the IA metadata DB (archive.db): per-collection / per-year / fleet trend tables, a refresh script, and example research queries. Tested against the real schema. See sql/README.md.

code/ — the programs, split by where they run

The code is organized by deployment target so it's clear what goes where (each subdirectory has its own README):

• code/worker/ → deploy to /opt/archive-agent/ on each worker. The stateless agent (agent.py + agent_platform.py), config/claims tools, and the enrich_missing.py diagnostic. None touch a database.
• code/collector/ → deploy to /opt/archive-collector/ on the one always-on collector. The service (collector.py), IA verification (reconcile.py), the DB-writing operator tools, and the SQL exports/cleanups. This side owns the only durable state (collector.db).
• code/shared/ → role-aware tools that run on either side: rebuild_missing.sh (export on collector, redownload on worker) and migrate_env.sh (OS-upgrade recovery on whichever machine you upgraded). Kept single-copy to avoid skew. | File | Role | |---|---| | agent.py | Runs on each worker from cron. Reads the existing pipeline's files (download-archive, joblogs, .info.json, markers) across the metadata/download/TEMP volumes, computes the funnel counts, and POSTs a status blob. Read-only; never touches the pipeline. | | agent_platform.py | Cross-platform helpers (time, file mtime, process counting, single-instance lock, volume/disk resolution) so agent.py runs unmodified on Linux, macOS, and WSL2. No shell-outs to date/stat/pgrep/flock/df. | | collector.py | The always-on service: ingest, rollup, alerting, the dashboard, DB migrations. Stdlib only (http.server + sqlite3). | | reconcile.py | Verifies claimed uploads against Internet Archive only (never YouTube, never local disk), matching on the youtube-id/vimeo-id metadata every existing item already carries. Path A (query by collection) with a Path B (match by video id) fallback for multi-channel collections. Produces the verified count. | | generate_config.py | Builds a worker's config.toml by parsing the existing per-directory get_new_video_metadata_backlog.sh scripts. Supports --verify (stat the paths), --append (merge into an existing config, with auto-backup and dedup), and --channel-id-map (use pre-resolved channel ids to skip network calls). | | yt_channel_names.sh | One-time pre-resolver: reads a dirs list, resolves each YouTube channel's UC… id via yt-dlp (politely, resumable), and writes a dirname → channel_id TSV map. Feed it to generate_config.py --channel-id-map so config generation is fast and network-free. Vimeo is skipped (resolved URL-side by the generator). | | set_collections.py | Populates each channel's IA collection in the collector DB from a dirname → ia_collection mapping file (pipe/tab/whitespace delimited). This is the prerequisite that lets reconciliation verify a channel against IA — without it, channels stay "upload not yet verified". Run once per worker on the collector. | | set_channel_names.py | Populates human-readable channel names in the collector DB (from the yt_channel_names.sh TSV or a channel_id\|name map), so the dashboard shows "Anchorage School District" instead of a raw UC… id. The collector also falls back to the dirname before the id. Run once per worker. | | scan_claims.py | Worker-side batch job: scans each channel's download_dir (read from config.toml) for .info.json files, extracts the video ids, and POSTs them to the collector's /claims endpoint. This populates the claims table so reconciliation can compute missing (claimed-but-not-in-archive). Heavier than the agent's per-cycle work, so run on a slow timer (e.g. daily or post-pipeline), not every cycle. Has --dry-run. | | seed_one_channel.py | Test helper: seeds one channel + collection + a few claimed video ids into the collector DB, for a live single-channel reconciliation check (Level 3 test). Not a production tool. | | rebuild_missing.sh | Re-fetch a worker's missing videos per channel (dirname). Exports each channel's missing ids to {dirname}_missing_ids.txt (from the v3 missing_video table), then either (--mode redownload, recommended) re-downloads only those ids into the channel's configured download_dir with --no-download-archive (shared archive untouched, zero blast radius), or (--mode archive) backs up the shared archive.txt timestamped, removes just those ids from it, and re-runs the dirname's backlog script. --dry-run, --only DIRNAME, --export-only/--skip-export for split collector/worker execution. Throttle-safe pacing mirrors the agent's model: yt-dlp per-video sleeps (--sleep-min/-max, default 3-8s) so a channel's downloads never burst the shared IP, plus optional channel-level GNU --parallel (--jobs, --delay stagger) — parallelism at the channel granularity, never by splitting one channel's id list. | | enrich_missing.py | Worker-side diagnostic for the missing-video failure analysis (Archive Corps). Takes a channel's missing video ids (from the missing_videos_by_worker.sql export) and enriches each from the cheapest source: local .info.json (full metadata — title, duration, tags, etc.), then optionally a yt-dlp --flat-playlist probe (one call/channel) for ids with no local file, then optionally a full per-video fetch. Tags each record with its source tier. JSONL or TSV output. The source itself is diagnostic — no .info.json means the video failed earlier in the pipeline than one that downloaded but never uploaded. | | migrate_env.sh | OS-upgrade recovery/repair tool. After a worker (or collector) is upgraded across the PEP 668 boundary (Ubuntu 22.04 → 24.04), pip --user deps become invisible to the new Python and the agent silently dies. This script idempotently re-homes the machine onto a venv, fixes yt-dlp, repoints cron at the venv interpreter, and proves the agent runs. Safe to run on any machine in any state (--dry-run, --role, --venv). | | test_reconcile.py | Offline test of the verification logic — substitutes a fake IA client and asserts verified/missing/orphan outcomes across both reconciliation paths. Run with python3 test_reconcile.py; no IA account or network needed. |

docs/

• archive-dashboard-design.md — the full design document (architecture, data model, status-blob schema, the collector, reconciliation, schema-version rollout, deployment, SQLite backup/migration, the dashboard, and design principles). The reference for why everything works the way it does.
• collector-schema.md — the database reference: every table and column, a data-access map (which program reads/writes which table), the blob-vs-DB schema version distinction, and Mermaid entity-relationship diagrams for the v1 and v2 schema. Generated from collector.py and validated against a real migrated DB.
• collector-sql-reference.md — a teaching inventory of every SQL statement the code actually runs, verbatim, grouped by pattern (upserts, INSERT OR IGNORE vs OR REPLACE, delete-then-insert, safe optional filtering) and annotated with why each is written that way. A model to learn from when writing new SQL.
• how-it-works-now.md — the pre-existing worker fleet & pipeline this monitoring system observes (scale, hardware, schedule, networking, output). Current-state context, not the dashboard design.
• metadata-enhancements.md — forward-looking research roadmap: what a researcher would ask of the ~3.2M-item corpus across all collections, and a sequenced series of metadata-enhancement projects (aggregates → transcription → entity/topic extraction → research API) to answer those questions.
• volunteer-contribution.md — a "how can I help?" guide for volunteer contributors (laypersons, community-media pros, editors, MARA/MLIS students, locals with deep community knowledge): roles mapped to the enhancement tasks, a contribution ladder, a FAQ, and the management/supervisory infrastructure to coordinate and quality-control their work.
• civic-data-integration.md — federating the civic-data-pipeline (Census-GEOID discovery of every US local government's official site, YouTube channel, and agenda/minutes portal) as the discovery source: confidence-gated channel onboarding, the civic-coverage denominator for Democracy's Library, and a sibling agendas-&-minutes extraction project (with the video↔︎agenda linking decision).
• metadata-db-integration.md — how to federate this project's collector.db ("what we sent") with the separate IA metadata database ("what's actually on IA", incl. post-upload deletions): join keys, the DARKENED failure class, reconcile-against-local-truth, and where each report draws its data.
• report-mockups.md — worked mock-ups of all three reports (operator/manager/owner) on sample data: exactly what each shows, what issues it surfaces, and the questions each answers.
• reporting-and-expansion.md — analysis + recommendations: audience-scoped reporting (operator/manager/channel-owner) with suggested cadences, and an automated capacity-first channel-expansion process that fills spare worker capacity before provisioning new machines.

runbooks/

Suggested reading / build order

1. docs/archive-dashboard-design.md — understand the target system (the why).
2. docs/collector-schema.md — skim the data model: the tables, the channel_id join key, and the three-numbers distinction in concrete columns. Useful context before you stand anything up.
3. runbooks/collector-standup-runbook.md — stand up the one always-on collector first, so there's a live endpoint for workers to post to. Note its Step 4 (host allowlist): every worker's host must be added there (and the env file regenerated + service restarted) or its posts are rejected 400 unknown host.
4. runbooks/migration-runbook.md — convert the first existing worker. This is the main per-worker procedure; follow its steps in order:
   • install (Step 1 auto-handles the 22.04 pip --user vs 24.04 venv / PEP 668 split and sets $AGENT_PY; verify yt-dlp is on the agent's PATH),
   • generate config from the on-disk scripts (Steps 2–3),
   • trial-run --no-remote and confirm the worker appears (Step 4),
   • schedule cron (Step 5), map collections (5b), set names (5c), optionally populate claims (5d), then seed remote totals so the bars appear (5e). Validate the dashboard against the old monitoring before trusting it.
5. Repeat step 4 for the remaining workers. Each is independent: add its host to the allowlist, run its config/collections/names/seed. The per-worker checklist at the end of the migration runbook is the quick reference.
6. As needed: add fresh or cross-platform machines via linux-/windows-/macos-worker-runbook.md; after any worker's OS upgrade across the 22.04→24.04 boundary, run code/migrate_env.sh (see the migration runbook's "Maintenance: OS upgrade" section).

Status of the code

All programs were tested during development (unit tests of the parsing and reconciliation logic, and end-to-end runs of the agent → collector → dashboard path against fixtures), and the system has since been deployed and debugged across a live multi-worker fleet. Two environment-specific wiring points to confirm per deployment:

• reconcile.py's IAClient needs live IA credentials (configured on the collector per the standup runbook). The real query layer is isolated in one injectable class.
• agent.py's remote-total fetch resolves yt-dlp by absolute path (env YTDLP_PATH → PATH → ~/.local/bin → /usr/local/bin → /usr/bin) and fails loudly if it can't find it, rather than silently returning null totals. Confirm sudo -u access yt-dlp --version works (symlink into /usr/local/bin if it lives in ~/.local/bin); see the migration runbook's Step 1 and the "remote_total stays null" troubleshooting.

Nothing requires changes to the existing upload pipeline before reconciliation works — it matches on the youtube-id/vimeo-id metadata the corpus already has.

Dependencies

• Workers (agent.py): Python 3.8+, psutil (live job count; optional — degrades gracefully), tomli on Python <3.11 (3.11+ has tomllib built in). The pipeline already provides yt-dlp, jq, ffmpeg, GNU parallel. On Ubuntu 22.04 install deps with pip --user; on 24.04 / Debian 12+ that's blocked by PEP 668, so use a venv — the migration runbook's Step 1 auto-detects which and sets $AGENT_PY accordingly, and code/migrate_env.sh re-homes a worker onto a venv after an OS upgrade.
• Collector (collector.py): Python 3.8+ stdlib only.
• Reconciliation (reconcile.py): the internetarchive package.
• All machines: Tailscale (already in use).

↑ back to contents10.2-archive-dashboard-project(71)/archive-dashboard-project/README.md

encoder-1: Faster-Whisper-XXL r245.4 — as-built

Status: Installed, verified, benchmarked. Outstanding items in §7. Last updated: 2026-08-25

1. Environment (verified)

The r245.4 bundle ships its own CUDA 12.x / cuDNN 8.x libs, CTranslate2 4.4.0, onnxruntime-gpu 1.18.0, and Python 3.10.11 via PyInstaller. Nothing installed system-wide. No distro upgrade — 22.04 is supported to April 2027.

2. Paths and names

Upstream reversed the executable's word order — whisper-faster-xxl → faster-whisper-xxl. Most likely thing to break existing automation.

Optional stable alias so future upgrades are a symlink repoint:

ln -s /mnt0/fwxxl-r245.4/faster-whisper-xxl ~/bin/fwxxl

Storage:

/dev/nvme0n1p2   915G  93G avail   /          ← 90% full; data + manifests only
/dev/sda1         17T 5.5T avail   /mnt0      ← ext4; binary, models, temp, output
/dev/sdb1         13T 4.1T avail   /mnt1      ← ext4, spare
//192.168.3.177   17T 9.8T avail   /mnt3,/mnt5 ← CIFS; never models or temp

3. Install — as performed

mkdir -p /mnt0/whisper-models /mnt0/whisper-tmp

cd /mnt0
wget -c https://github.com/Purfview/whisper-standalone-win/releases/download/Faster-Whisper-XXL/Faster-Whisper-XXL_r245.4_linux.7z

7z x Faster-Whisper-XXL_r245.4_linux.7z -o./Whisper-Faster-XXL-r245.4
# → 634 folders, 4393 files, 6.09 GB extracted (not ~3.5 GB)

# Archive nests everything under Faster-Whisper-XXL/ — flatten it
mv /mnt0/Whisper-Faster-XXL-r245.4/Faster-Whisper-XXL /mnt0/fwxxl-r245.4
mv /mnt0/Whisper-Faster-XXL-r245.4/license.txt /mnt0/fwxxl-r245.4/
rmdir /mnt0/Whisper-Faster-XXL-r245.4

# 7z does not preserve the execute bit
chmod +x /mnt0/fwxxl-r245.4/faster-whisper-xxl /mnt0/fwxxl-r245.4/One_Click_Transcribe.sh

env -u LD_LIBRARY_PATH /mnt0/fwxxl-r245.4/faster-whisper-xxl --help | head -20

Contents: faster-whisper-xxl (41.9 MB), _xxl_data/ (5.7 GB), One_Click_Transcribe.sh, license.txt.

4. Migration reference

Renames

Removed in r239.1

--nullify_non_speech, --no_speech_strict_lvl, --prompt_max, first option of --reprompt.

grep -rn -e 'whisper-faster-xxl' -e 'vad_alt_method' -e 'ff_mdx_kim2' -e 'mdx_device' \
         -e 'nullify_non_speech' -e 'no_speech_strict_lvl' -e 'prompt_max' \
         /home/access/Whisper-Faster-XXL /mnt0/Whisper-Faster-XXL \
         ~/bin ~/.local/bin /etc/cron.d 2>/dev/null

Behavior changes affecting output

• --sentence now affects all formats except json (r194.2; previously srt/vtt only). When diffing old vs new, compare srt only — txt and tsv differ by design.
• lrc is a new format. --output_format all emits one extra file per input.

Flags in the old production command (--model, --sentence, --beam_size, --output_format, --output_dir) all carry over unchanged.

5. Benchmarks

Denominators

Every figure below names the corpus it came from. Neither corpus is a sample of the wider archive.

Corpus A — Rio Dell City Council, 2026. 29 mp3, 88,580 s = 24.6 h. Mean 50.9 min, median 45.8 min, range 19.2-111.5 min. Chosen deliberately as a hard case: worst room audio and least orderly council conduct of the jurisdictions under test, and meetings shorter than most. Fourteen of fifteen meetings appear twice from two capture sources under the Access Humboldt joint franchise (7 places + 1 county); other jurisdictions do not share this property.

Corpus B — Humboldt County Board of Supervisors, 2026. 27 mp3, 274,802 s = 76.3 h. Mean 169.6 min, median 166.7 min, range 3.1-444.2 min. Bimodal: four "Special" sessions of 3-25 min, the rest 73-444 min. 18 files exceed 2 h, 12 exceed 3 h, 7 exceed 4 h. Mean file length is 3.3x Corpus A's.

Corpus B was added specifically because Corpus A could not speak to long-meeting behaviour. The two agree within 8%, which is the basis for treating the rates below as more than single-jurisdiction artefacts — but they remain two jurisdictions in one county, not a projection onto the 3M-item archive.

Corpus A — Rio Dell (24.6 h)

Corpus B — Humboldt County BoS (76.3 h)

Halves finished 46 s apart across a 52-minute run — the greedy longest-first split (S5) balanced to 1.3 min of audio across two 38-hour halves.

Findings

The two corpora agree within 8%. Corpus B ran at 88.2× against Corpus A's 95.4× under identical settings, despite files averaging 3.3× longer. Projecting Corpus A's rate onto Corpus B predicted 48.0 min; actual was 51.9 min. So per-file overhead is not what dominates, and the worst-audio corpus was not meaningfully harder than the long-meeting one.

Long files are safe. The 7.4 h file transcribed with no truncation (last cue 07:23:50 against 07:24:14 duration), no repetition loops (max consecutive duplicate = 2), and a well-formed 4,980-segment SRT. Cues longer than 15 s were 10 of 4,980 (0.2%), all dead-air absorption rather than collapsed speech. The model question is closed: large-v3-turbo on everything, including 7+ hour files.

The tool's reported audio-seconds/s is per-file, not per-run — it reports the last file in the batch. On the Corpus B run the two processes reported 94.86 and 48.82 audio-s/s, which are two different meetings, not two run averages. The figure is valid for the file it describes and excludes model load and inter-file overhead. For batch throughput use wall-clock over a corpus of known total duration; do not sum or average the per-file figures.

Concurrency is the second win — 1.31× over single-process. Two processes on the same GPU finished the same 29 files in 15m28s vs 20m18s. This was predicted not to work, on the basis of a pre-batching observation that two jobs simply halved each other's rate. That prediction was wrong under batched inference.

Mechanism: GPU decode is saturated, but each process spends ~24% of its wall-clock on CPU-side work (VAD, model load, output writing) during which the GPU idles. Two processes interleave and fill each other's holes. This is not more GPU throughput — it is better duty cycle. Predicted ceiling from the overhead arithmetic was ~96×; measured 95.4×.

The split was near-balanced (last outputs one minute apart), so the figure is sound for this corpus. Three-way concurrency is untested and expected to show diminishing returns, since the overhead is now largely absorbed.

Batching is the win. 2.5–2.7× at zero quality cost, purely from loading the model once per batch instead of once per file. Nothing else measured comes close.

Batch size beyond 8 does nothing. bs=8 and bs=12 landed within a minute across 29 files. Don't tune it further.

Power is not a constraint — for batched transcription. At a 150 W cap the card peaks around 132 W and rarely. A clock log (374 samples) showed mean 1,764 MHz with no meaningful throttling — SwPowerCap appears on 182 samples but at 1,906 MHz average, which is normal boost behavior, not a limit. This does not generalise to diarization (see the diarization measurement below), whose torch-based pass saturates the card. The 150 W cap was lifted to the 170 W default during concurrency testing and remains off.

7.5% of sampled time the GPU was fully idle (28 samples at 210 MHz, flag 0x1 GpuIdle) — CPU-side VAD and mp3 decode. time corroborates: real 20m18s / user 29m01s / sys 8m40s, i.e. 37+ min of CPU against 20 min wall-clock. The CPU works harder than the GPU on this pipeline.

GPU utilization % is a useless metric here — it reports whether any kernel is resident, not how much of the card is used. It read 100% at every batch size. Use wall-clock instead. Note also that nvidia-smi dmon's mem column is memory bandwidth utilization, not VRAM occupancy.

Diarization — first measurement (Corpus A, 2026-08-26)

One file, --diarize pyannote_v3.1, --batched OFF (VRAM contention), solo, via test/test_diarize.sh. Denominator: a single 1:42:10 Rio Dell meeting — not a rate.

Reading of it:

• Diarization roughly quarters throughput — ~18× vs the settled 72–95× for transcription. Diarizing all of Corpus B (76.3 h) would be ~4.2 h solo, against ~52 min for the 2-way transcription pipeline. Speaker labels are expensive.
• The 48.76 audio-s/s is unbatched and consistent with the batching finding (~48× unbatched vs ~72× batched), not a regression.
• Diarization is power-bound where transcription was not. The torch pass sat at 168 W of 170 W the whole diarization stage. So the "1.31× from 2 processes" win — which came from the GPU being idle ~24% of the time under transcription — cannot be assumed for diarized runs: two diarization passes would contend for a saturated, power-limited card. Measure before running diarization N-way.
• 11 speakers at a 12 ceiling is near the top of the range; it may over-split if raised. Rio Dell is 5 members + ~3 officials + public commenters, so 11 is plausible but wants a check. Iterate cheaply with MODE=only MAX_SPK=8 (a --diarize_only pass is ~3 min) and eyeball a roll-call vote — the known weak spot. Accuracy is still unjudged; only cost is now measured.

Model decision — SETTLED: use large-v3-turbo

large-v3-turbo is marginally faster than medium.en on this corpus (20m18.6s vs 21m12.9s), not slower. There is no speed/quality trade-off to weigh: turbo delivers a large-class model at equal-or-better throughput.

Why: the pipeline is bound by fixed CPU-side overhead, not decode. medium.en reported 91.03 audio-s/s from the tool but achieved 69.6× wall-clock — a ~300 s gap, ~24% of the run, outside the decode loop. That cost is constant regardless of model, so turbo's shallower decoder (4 layers vs 24) has little left to win. medium.en even used more CPU time (31m24s vs 29m01s).

Still worth checking transcript quality on a worst-audio meeting before locking it in — turbo was distilled with less robustness emphasis and may degrade late in long sessions:

diff <(sed 's/^[0-9:,>< -]*$//' /mnt0/whisper-tmp/medium-batched/SAMPLE.srt) \
     <(sed 's/^[0-9:,>< -]*$//' /mnt0/whisper-tmp/turbo/SAMPLE.srt) | head -60

Check proper nouns, numbers, and the tail of a 90-minute meeting.

6. Test scenarios

All assume:

export TMPDIR=/mnt0/whisper-tmp
cd /mnt0/Whisper-Faster-XXL      # audio lives here; relative input paths resolve from it
FW="/mnt0/fwxxl-r245.4/faster-whisper-xxl"

--output_dir is created on demand. TMPDIR must exist.

6.1 Current best-known production config

time env -u LD_LIBRARY_PATH "$FW" \
  /mnt0/Whisper-Faster-XXL/Rio_Dell_City_Council/2026/*.mp3 \
  --model=large-v3-turbo -l en --sentence --beam_size=1 \
  --model_dir /mnt0/whisper-models \
  --batched --batch_size 8 \
  --output_format srt json txt \
  --output_dir /mnt0/transcripts/Rio_Dell_City_Council/2026 \
  --skip

--skip passes over files already present in the output dir — makes bulk runs resumable. Omit it when benchmarking.

--output_format srt json txt emits three views of each meeting: srt (timed cues, plus [Speaker_XX] labels when diarizing), json (word-level timestamps, no speaker labels — see §7), and txt (plain reading transcript, no timestamps). txt is a sentence-path format, so a diarized run's txt carries the speaker labels; the json never does.

6.2 Isolate one variable

Change exactly one thing per run against §6.1.

# Model comparison
--model=medium.en          # old model, batched
--model=large-v2           # full large model (untested)
--model=distil-large-v3.5  # English-only, less hallucination-prone

# VAD on GPU — tests the 7.5% idle finding
--vad_method pyannote_v3

# Beam width — may be near-free on a latency-bound card
--beam_size=5

6.3 Duration-balanced split (S5) and concurrency

File counts balance badly when durations range 3 min to 7.4 h. Greedy longest-first assignment got Corpus B to 1.3 min of imbalance across two 38-hour halves; the halves then finished 46 s apart. Round-robin on a shuffle would not have.

Log each process to its own file. Merging both to one stdout makes per-process completion times unrecoverable — this was learned the hard way.

# S5: duration-balanced N-way split. Pass the audio dir; N via NBINS.
NBINS=2 python3 - /mnt0/Whisper-Faster-XXL/Humboldt_County_Board_of_Supervisors/2026 << 'SPLIT'
import subprocess, sys, os, glob
d = sys.argv[1]
N = int(os.environ.get('NBINS', '2'))
fs = []
for f in sorted(glob.glob(os.path.join(d, '*.mp3'))):
    dur = float(subprocess.check_output(
        ['ffprobe','-v','error','-show_entries','format=duration',
         '-of','csv=p=0', f]))
    fs.append((dur, f))
if not fs:
    sys.exit("no mp3 found in " + d)
fs.sort(reverse=True)                      # longest first
bins = [[] for _ in range(N)]; tot = [0.0]*N
for dur, f in fs:
    i = tot.index(min(tot)); bins[i].append(f); tot[i] += dur
for i, b in enumerate(bins):
    with open("/tmp/bin-%d.txt" % i, "w") as fh:
        fh.write("\n".join(b) + "\n")
    print("bin %d: %.2f h, %d files" % (i, tot[i]/3600, len(b)), file=sys.stderr)
SPLIT

# Run the bins concurrently, one log each
export TMPDIR=/mnt0/whisper-tmp
FW=/mnt0/fwxxl-r245.4/faster-whisper-xxl

time (
  for i in 0 1; do
    env -u LD_LIBRARY_PATH "$FW" $(cat /tmp/bin-$i.txt) \
      --model=large-v3-turbo -l en --sentence --beam_size=1 \
      --model_dir /mnt0/whisper-models --batched --batch_size 8 \
      --output_format srt json txt --output_dir /mnt0/whisper-tmp/bin-$i \
      > /mnt0/whisper-tmp/bin-$i.log 2>&1 &
  done
  wait
)

Each process loads its own model copy — watch VRAM before raising NBINS past 2. At 3.0 cores busy with the GPU as the constraint, 3-way is expected to add little.

6.3a Output verification (S1–S4)

Run these after any long-meeting batch. They catch the three failure modes that matter — silent truncation, repetition loops, malformed output — without listening to the audio.

S1 — truncation check. Compare each transcript's last cue against the source file's real duration. A transcript ending well short is turbo dropping segments, its known failure mode.

for f in /mnt0/whisper-tmp/bin-*/*.srt; do
  base=$(basename "$f" .srt)
  src=$(find /mnt0/Whisper-Faster-XXL -name "$base.mp3" | head -1)
  [ -z "$src" ] && { printf '%-64s NO SOURCE\n' "$base"; continue; }
  dur=$(ffprobe -v error -show_entries format=duration -of csv=p=0 "$src")
  last=$(grep -- '-->' "$f" | tail -1 | awk '{print $3}' | tr ',' '.')
  lastsec=$(echo "$last" | awk -F: '{print $1*3600+$2*60+$3}')
  gap=$(echo "$dur $lastsec" | awk '{printf "%.0f", $1-$2}')
  flag=""; [ "$gap" -gt 120 ] && flag="  <-- CHECK"
  printf '%-64s dur=%7.0fs last=%7.0fs gap=%5ss%s\n' \
    "$base" "$dur" "$lastsec" "$gap" "$flag"
done | sort

A gap under ~60 s is trailing silence after the gavel — normal. Two minutes or more warrants a look.

S2 — repetition loops (consecutive). The one that matters. A loop repeats the same line consecutively; a busy meeting repeats common phrases scattered across hours, which is not a defect.

for f in /mnt0/whisper-tmp/bin-*/*.srt; do
  echo "== $(basename "$f")"
  awk '!/^[0-9]+$/ && !/-->/ && NF' "$f" | uniq -c | sort -rn | head -5
done

Max consecutive run of 2–3 is normal speech — two people thanking each other a half-second apart. Any line repeating 10+ times consecutively is a loop; re-run that file with --model=large-v2 or a higher --beam_size.

S3 — frequency table (context, not a verdict). Total counts, ignoring adjacency. Useful for judging whether the vocabulary looks like a meeting.

grep -hv '^[0-9]' /mnt0/whisper-tmp/bin-*/*.srt | grep -v -- '-->' \
  | sort | uniq -c | sort -rn | head -20

Interpret against the meeting: 112 "Thank you." across 7.4 h of public comment is one every four minutes — real. A chair's script ("I will unmute you and you will have two minutes") recurring is corroboration, not repetition. S2, not S3, decides whether there is a loop.

S4 — over-long cues. Cues far longer than their neighbours usually mean VAD handed the decoder a region it could not subdivide — dead air absorbed into a short phrase. Harmless for reading; matters only for timestamp-linked search, where the link can land 10–20 s early.

for f in /mnt0/whisper-tmp/bin-*/*.srt; do
  awk -v F="$(basename "$f")" '/-->/{
    split($1,a,":"); split($3,b,":");
    s=a[1]*3600+a[2]*60+a[3]; e=b[1]*3600+b[2]*60+b[3];
    if (e-s > 15) printf "%5.0fs  %s  %s\n", e-s, F, $0 }' "$f"
done | sort -rn | head -20

Corpus B baseline: 10 of 4,980 cues (0.2%) over 15 s, longest 29 s. Whisper's encoder works in 30 s windows, so a 25–29 s cue is one window holding little transcribable content. Inspect a couple with grep -A2 '<timestamp>' file.srt — a short phrase means dead air was absorbed (fine); a full paragraph means a real segment did not get subdivided.

Segment-count sanity. grep -c '^[0-9]*$' returns roughly 2x the segment count (index lines plus blank separators). Halve it and compare against the last index in the file; a mismatch means malformed output.

6.4 Earlier round-robin approach (superseded by 6.3)

Round-robin the split so long files distribute evenly — file counts alone balance poorly when durations range 19–111 min. Log each process separately; merging both to one stdout makes the results unreadable.

ls /mnt0/Whisper-Faster-XXL/Rio_Dell_City_Council/2026/*.mp3 | shuf > /tmp/all.txt
split -n r/2 /tmp/all.txt /tmp/part-

time (
  for P in /tmp/part-*; do
    env -u LD_LIBRARY_PATH "$FW" $(cat $P) \
      --model=large-v3-turbo -l en --sentence --beam_size=1 \
      --model_dir /mnt0/whisper-models --batched --batch_size 8 \
      --output_format srt json txt --output_dir /mnt0/whisper-tmp/$(basename $P) \
      > /mnt0/whisper-tmp/$(basename $P).log 2>&1 &
  done
  wait
)

Watch VRAM: each process loads its own model copy. split -n r/3 for three-way.

6.5 Batch size sweep (already answered: use 8)

for BS in 4 8 12 16; do
  echo "=== bs=$BS ==="
  /usr/bin/time -f "%e sec" env -u LD_LIBRARY_PATH "$FW" "$F" \
    --model=large-v3-turbo -l en --sentence --beam_size=1 \
    --model_dir /mnt0/whisper-models --batched --batch_size $BS \
    --output_format srt txt --output_dir /mnt0/whisper-tmp/bs$BS
done

6.6 Corpus duration and realtime factor

for f in /mnt0/Whisper-Faster-XXL/Rio_Dell_City_Council/2026/*.mp3; do
  ffprobe -v error -show_entries format=duration -of csv=p=0 "$f"
done | awk '{s+=$1} END {printf "%.0f sec  %.2f h  n=%d  mean=%.1f min\n", s, s/3600, NR, s/NR/60}'

Track audio-seconds per wall-clock second, not raw times — it's comparable across files and models.

6.7 GPU instrumentation

# VRAM occupancy + real utilization
nvidia-smi --query-gpu=memory.used,memory.total,utilization.gpu --format=csv -l 1

# Log a full run
nvidia-smi --query-gpu=timestamp,memory.used,utilization.gpu,power.draw,clocks.sm \
  --format=csv -l 1 -f /mnt0/whisper-tmp/gpu-run.log

# Throttle check
nvidia-smi --query-gpu=clocks_throttle_reasons.active,clocks.sm,clocks.max.sm --format=csv -l 1

# Idle-sample count (flag 0x1 = GpuIdle)
awk -F, 'NR>1{gsub(/ MHz/,"",$2); if($2+0<1000) c++} END{print c+0" idle samples"}' gpu-run.log

6.8 Hotwords

Plain quoted string — no file support. Load from a file via shell substitution:

HOT=$(tr '\n' ' ' < /mnt0/hotwords/rio_dell.txt)
... --hotwords "$HOT"

Constraints: shares the 224-token prompt window with initial_prompt, so keep to a few dozen terms — a long roster overflows and terms are silently dropped. It is a bias, not a guarantee. Measured cost ≈ 90 s across 29 files. Enabling it also auto-changes --prompt_reset_on_no_end behavior. --rehot is documented as "for Dev experiments" — leave alone.

Verify hotwords actually help by grepping target names in output with and without, not just by timing.

Measured 2026-08-28 (test/test_hotwords.sh, Humboldt County BoS, two files). Real but partial, and it scales with how often the target names occur:

On the 7.4 h file, 11 of 21 terms improved (Madrone +6, Humboldt +8, Bushnell/Arroyo/Bohn +4, six place names +2, Wilson +1); none fell. The 3 min special changed nothing — too few mentions for the bias to land.

The count overstates it — read the context. The surname Bohn (homophone of "bone") shows 0 → 4 in the table, but in the transcript baseline wrote "Supervisor Bone" ×25 / "Bohn" ×0 and hot wrote "Bohn" ×3 / "Bone" ×23 — the bias flipped only 3 of ~25 mentions. Hotwords nudge; they do not reliably win a hard homophone. Never trust a per-term delta without grepping the actual lines (the whisper.cpp side, 10.4, learned the same for unique-line %). Bottom line: worth enabling on long meetings (+11% canonical forms, ~4% cost, zero regressions), pointless on short clips, and not a fix for hard homophones.

7. Diarization

Bundled — no Hugging Face token needed

Confirmed present in _xxl_data/faster_whisper/assets/:

• pyannote_vad_v3.bin / .onnx — segmentation (speech regions, overlap detection)
• pyannote_wespeaker-voxceleb-resnet34-LM.bin — speaker embedding model
• pyannote_wespeaker-voxceleb-resnet34-LM.yaml — pipeline config

Purfview ships extracted weights as plain assets, bypassing the gated HF repo. The HF-token requirement widely documented online is a WhisperX requirement, not this tool's.

reverb_v1 / reverb_v2 are not bundled and would download on demand — and are licensed for personal non-profit use only. Given this corpus's destination, stay on pyannote unless you've reviewed those terms yourself.

Backends

First run

mkdir -p /mnt0/whisper-tmp/diar

time env -u LD_LIBRARY_PATH "$FW" \
  Rio_Dell_City_Council/2026/Rio_Dell_City_Council_Meeting_of_2026-08-04.mp3 \
  --model=large-v3-turbo -l en --beam_size=1 \
  --model_dir /mnt0/whisper-models \
  --diarize pyannote_v3.1 \
  --speaker Speaker \
  --min_speakers 3 --max_speakers 12 \
  --output_format srt json txt \
  --output_dir /mnt0/whisper-tmp/diar

Scripted as test/test_diarize.sh <Jurisdiction> [year] [sample.mp3] (honours solo timing, no --sentence, --batched off, per-run log). First run measured 2026-08-26 — see §5 "Diarization — first measurement": ~18× realtime, ~55% of the wall-clock is the diarization pass, and it saturates the power cap.

Options

Behavior to expect

• --diarize auto-activates --sentence — don't pass both. Affects all output formats except json.
• JSON carries no speaker labels — confirmed 2026-08-26. A diarized run's json segment has no speaker field and unlabeled text (an observed segment: text: ' You gotta watch Victor.', speaker: None), while the srt for the same run had [Speaker_XX] on every cue. Labels are injected only into the sentence/subtitle formats (srt/vtt/tsv/lrc, and txt). For speaker data parse the SRT, or use --diarize_dump for structured turns — never the json. To get word-level text with speakers, join json word times against --diarize_dump turns by timestamp. This matters for the C3 write-back: the json alone cannot populate a speaker column.
• Labels are arbitrary per file. SPEAKER_00 in one meeting has no relation to SPEAKER_00 in the next. Diarization clusters voices within a recording; it does not identify people.
• It is a full second pass — embedding extraction plus clustering. Time one file before running a batch.
• VRAM contention. Diarization and --batched both want the card; on 12 GB they may not coexist at bs=8. Test separately, then together.
• Bounding speaker count helps. Council meetings have a few members plus rotating public commenters. Bounds prevent splitting one person across labels or merging two quiet speakers.
• Known weak spots: crosstalk, very short utterances ("Aye."), and roll-call votes where each person says one word in sequence — unfortunate, since roll calls are exactly what's most valuable to label.

Use --diarize_only to tune --min_speakers / --max_speakers without re-transcribing.

Cross-meeting speaker identity

-embeddings emits WeSpeaker ResNet34 vectors — voice fingerprints. The same council member should occupy a similar region of that space across meetings, so cosine similarity against a small hand-labeled reference set could map Speaker_00 → an actual name without any training. That's the path from per-file labels to a genuinely searchable archive. A project, not a flag.

8. Outstanding

8.1 LD_LIBRARY_PATH pollution — before pointing cron at r245.4

/home/access/Whisper-Faster-XXL/_xxl_data is on LD_LIBRARY_PATH system-wide. Evidence: /usr/bin/find emits

find: /home/access/Whisper-Faster-XXL/_xxl_data/libselinux.so.1: no version information available

System binaries are already loading r192.3.1's bundled libs. Harmless for find; not harmless for r245.4, whose CUDA 12 libs get shadowed by r192.3.1's CUDA 11 libs — surfacing as a confusing cuDNN error rather than a path problem.

echo "$LD_LIBRARY_PATH"
grep -rn 'LD_LIBRARY_PATH' ~/.bashrc ~/.profile ~/.bash_profile /etc/environment /etc/profile.d/ 2>/dev/null

Comment out the _xxl_data entry, then exec bash -l. env -u LD_LIBRARY_PATH is a per-command workaround — it won't protect cron jobs or scripts that omit it.

8.2 Move the model cache off root

du -sh /home/access/Whisper-Faster-XXL/_models
mv /home/access/Whisper-Faster-XXL/_models/* /mnt0/whisper-models/
df -h /

Leave .cache alone — bound to the old install; r245.4 builds its own.

8.3 CIFS mounts not in fstab

/mnt3 and /mnt5 are mounted from 192.168.3.177 but absent from /etc/fstab. No impact here (no reboot), but they may not return after an unexpected restart.

systemctl list-units --type=mount | grep -E 'mnt3|mnt5'
findmnt -o TARGET,SOURCE,FSTYPE,OPTIONS /mnt3 /mnt5

8.4 Separate binary from data

/home/access/Whisper-Faster-XXL holds the old binary, _xxl_data, _models, .cache, and hundreds of *_already_done / *_mp3s / *_still_to_do manifests in one directory — which is how _xxl_data ended up on LD_LIBRARY_PATH. Binary is now on /mnt0; finishing the split makes the next upgrade a directory swap.

8.5 Unmeasured

• --vad_method pyannote_v3 (tests the GPU-idle finding)
• large-v2 batched — now more interesting: if overhead dominates, large-v2 may also cost little
• --beam_size=5 (may be near-free when latency-bound)
• turbo vs medium.en transcript quality (speed settled; quality still unmeasured)
• three-way concurrency (expected to add little — GPU is the constraint)
• diarization accuracy (cost now measured — §5: ~18× realtime, power-bound; concurrency for diarized runs untested and not safe to assume)

8.6 Retire r192.3.1

Keep until a full production batch runs clean on r245.4. Rollback is invoking the old path; nothing was overwritten.

9. Method notes

Two measurement mistakes worth not repeating:

• Confounded runs. The bs=8 vs bs=12 comparison also changed hotwords. Change one variable per run.
• Wrong model assumed. Several runs were analyzed as large-v2 when they were large-v3-turbo. Record the full command line with each timing.

A third error, caught 2026-08-25: the §5 table originally compared medium.en at 103–115× (the tool's per-file reported speed) against turbo at ~73× (wall-clock over the batch). Different metrics, and the comparison inverted the actual result. Only compare wall-clock over identical inputs.

A fourth error: a duplicate-file pattern observed in this 29-file corpus was described as "roughly half your transcription compute is redundant" and projected onto the 3M-item corpus. The duplication is a known property of the Access Humboldt joint franchise, affects some jurisdictions and not others, and is not a rate that generalises. State the denominator before stating the rate.

A fifth: two concurrent processes were told to write to one stdout, making the per-process completion times unrecoverable. Redirect each to its own log.

Metrics that mislead: GPU utilization % (any kernel resident reads 100%), dmon's mem column (bandwidth, not occupancy), and the tool's per-file transcription speed — it reports only the last file of a batch and excludes model load and inter-file overhead, so it is neither a run average nor comparable across concurrent processes.

↑ back to contents10.3-whisper-asr-encoder-1/encoder-1-fwxxl-r245-upgrade.md

whisper.cpp Cross-Platform Transcription Benchmarks

Local ASR pipeline for civic meeting audio, measured across five platforms.

Start with CLAUDE.md — full session context, all benchmark results, and the findings and script bugs that are expensive to rediscover.

Layout

• CLAUDE.md — project context, auto-loaded by Claude Code
• docs/ — runbooks and analysis (.md is canonical; .docx/.pdf generated)
• scripts/ — benchmark harness, scorers, per-fleet deploy, audio conversion

Quick start

Benchmark a Linux/Vulkan box:

cd scripts
./fetch-models.sh /path/to/whisper.cpp        # ~9.4 GB, four models
./bench-wo4.sh /path/to/meeting.wav my-run

Benchmark a Mac (or any platform, fewer variants):

THREADS=4 ./run-detached-portable.sh /path/to/meeting.wav my-run

Score a transcript on its own:

./score-transcript.sh --detail transcript.txt
./score-transcript.sh --table  ~/whisper-bench/my-run/*.txt

Trying Parakeet (alternative to whisper.cpp)

NVIDIA Parakeet tops the Open ASR Leaderboard on average WER (tdt-0.6b-v2 ~6.05%) and is far faster than whisper turbo. But it is CTC/RNNT/TDT — there is no --prompt, no --carry-initial-prompt, no hotwords. The entire -mc machinery that fixed "Rio Dell" on whisper.cpp has no equivalent, so the open question is whether Parakeet's acoustic model gets the civic proper nouns right unaided. bench-parakeet.sh answers that on the same Rio Dell file with the same score-transcript.sh/terms.txt, so the result is directly comparable to the whisper turbo -mc 160 baseline (54/55). Read the NOUNS column first — Parakeet wins on speed regardless.

Two backends, one scorer (auto-detected: mlx on Apple Silicon, else cpp):

Linux / Vulkan — parakeet.cpp

git clone --recursive https://github.com/mudler/parakeet.cpp
cd parakeet.cpp
cmake -B build -DPARAKEET_GGML_VULKAN=ON && cmake --build build -j
# same Vulkan toolchain traps as whisper.cpp: render group, glslc,
# spirv-headers, and rm -rf build after fixing any of them.

# GGUF weights from HF mudler/parakeet-cpp-gguf into $HOME/parakeet-models/.
# Filenames are the SHORT model name + -f16.gguf, e.g. tdt-0.6b-v2-f16.gguf — no
# "parakeet-" prefix. Other quants: q8_0 q6_k q5_k q4_k.
mkdir -p ~/parakeet-models && cd ~/parakeet-models
for m in tdt-0.6b-v2 tdt-0.6b-v3 ctc-1.1b; do
  curl -fL -o "$m-f16.gguf" \
    "https://huggingface.co/mudler/parakeet-cpp-gguf/resolve/main/$m-f16.gguf"
done

# run the benchmark (Phase A model sweep + Phase B beam sweep on the winner)
cd -  # back to whisper-benchmarks/scripts
PARAKEET_BIN=~/parakeet.cpp/build/examples/cli/parakeet-cli \
  ./bench-parakeet.sh Rio_Dell_City_Council_Meeting_of_2026-08-18.wav p14s-parakeet

uv on the Macs (parakeet-mlx)

parakeet-mlx is the easy Apple-Silicon path — native MLX, no compile. It is a Python tool, run through uv so nothing touches the system Python.

# 1. install uv (either one)
curl -LsSf https://astral.sh/uv/install.sh | sh     # official installer
brew install uv                                     # or via Homebrew

# 2. ffmpeg is required (parakeet-mlx shells out to it to decode/resample)
brew install ffmpeg

# 3. install the CLI as a uv-managed tool — puts `parakeet-mlx` on PATH,
#    isolated in its own venv, no `pip install` into anything shared
uv tool install parakeet-mlx -U
#    later: `uv tool upgrade parakeet-mlx`  /  `uv tool uninstall parakeet-mlx`

# 4. run the benchmark — BACKEND=mlx is auto-detected on Darwin.
#    Models auto-download from HF (mlx-community/parakeet-*) on first use.
./bench-parakeet.sh Rio_Dell_City_Council_Meeting_of_2026-08-18.wav m1-parakeet

The 8 GB M1 is fine — tdt-0.6b is smaller than the whisper turbo it already runs. First run downloads the model into ~/.cache/huggingface.

Unattended (detached) runs

The Rio Dell file is 34 min and Phase A sweeps several models, so on a laptop run it detached — it survives disconnect, and on macOS caffeinate keeps the system awake. Pass the harness via BENCH; all extra env is inherited, and console.log lands with the results.

# Mac (mlx) — caffeinated, survives SSH drop
BENCH=bench-parakeet.sh BACKEND=mlx \
  ./run-detached-portable.sh Rio_Dell_City_Council_Meeting_of_2026-08-18.wav m1-parakeet

# P14s (Vulkan / parakeet.cpp)
BENCH=bench-parakeet.sh PARAKEET_BIN=~/parakeet.cpp/build/examples/cli/parakeet-cli \
  ./run-detached-portable.sh Rio_Dell_City_Council_Meeting_of_2026-08-18.wav p14s-parakeet

# then, per the wrapper's printed hints:
tail -f ~/parakeet-bench/<tag>/console.log     # watch
cat     ~/parakeet-bench/<tag>/summary.txt      # result

Compare Parakeet against the whisper baseline

./score-transcript.sh --table \
  ~/parakeet-bench/*/A-*.txt  ~/whisper-bench/<best-run>/*.txt

The number that matters: does any Parakeet variant match whisper turbo's 54/55 proper nouns, given it has no vocabulary lever to lean on?

State

Measured on five platforms — Beelink SER6, MacBook M2, Mac mini M1, Peladn WO4, and ThinkPad P14s — against the same 2056.7 s Rio Dell file. The P14s is the fastest box (~18×, -mc 160, 54/55 nouns, RDNA3 12 CU); the WO4 remains the well-characterised reference. The WO4 large-v3-turbo -mc sweep is complete (-mc 160 won, 54/55). See the Results section and "Open items" in CLAUDE.md for what's left — a multi-meeting corpus, one hand-corrected transcript for real WER, and the encoder-1 --hotwords and Parakeet comparisons.

Regenerating documents

Follow the pile-wide convention (root CLAUDE.md): rebuild the .docx from the existing one as a style reference, normalize the paragraph style pandoc tags on the first paragraph, then render the PDF.

# 1. edit docs/NAME.md, then rebuild the docx using the current one as the
#    style reference (write to a temp so pandoc isn't reading its own output)
pandoc docs/NAME.md -o docs/NAME.new.docx --reference-doc=docs/NAME.docx
mv docs/NAME.new.docx docs/NAME.docx

# 2. normalize pandoc's first-paragraph style (else it renders over-indented)
d=docs/NAME.docx; tmp=$(mktemp -d); unzip -q "$d" -d "$tmp"
sed -i 's/w:val="FirstParagraph"/w:val="BodyText"/g' "$tmp/word/document.xml"
(cd "$tmp" && zip -qr - .) > "$d"; rm -rf "$tmp"

# 3. render the PDF
soffice --headless --convert-to pdf docs/NAME.docx --outdir docs/

Do not add --toc — LibreOffice renders pandoc's TOC field as an empty heading. Keep the version table in each doc current.

↑ back to contents10.4-whisper-cpp-cross-platform-benchmarks/whisper-benchmarks/README.md

Fleet dirname → collection map — rescue copy

What this is. The mapping from each worker directory (basename(metadata_dir)) to the Internet Archive collection that directory's uploads land in. It is the bridge between the two grains the CMA is keyed on:

• dirname — one per channel/working directory; lowercase on the workers and in collector.db, UPPERCASE in archive.db.metadata.dirname.
• collection — the IA collection identifier; the grain a manager asks about.

They are equal for 1,998 directories and deliberately different for 132. Those 132 are not errors — they are the reason this file has to exist. acctvny really does feed collection ac5ny; datv really does feed datvdayton.

This is the same concept the archive-side pipeline calls conf/directory_collection_map.psv (built by build_map.py from the _meta.xml tree on megastor), but derived independently, from the worker side.

Why it was rescued

The chain of custody was /tmp on ten worker machines → one operator's home directory on ser62 → nowhere else. Three things made that untenable:

1. /tmp was the origin. A rebuilt worker cannot regenerate its rows.
2. 63 dirnames in this map have no channel row in collector.db. For those, this file is the only record that the directory maps to a collection at all.
3. The two conflicts below are unresolvable without all ten files, because each host holds only its own half.

Provenance

• Source: ser62:~/<host>_dirname_collection_map.psv, ten files.
• Copied: 2026-08-30, scp -p (mtimes preserved), by Claude, at the user's instruction. Source files were not modified, moved, or deleted.
• Verified: all ten md5sums identical to source after copy.
• File mtimes span 2026-06-26 → 2026-07-04, i.e. this is a snapshot of the fleet as it stood at the end of June 2026, not a live view.

Files here

Round-trip verified: stripping the host column from the consolidated file reproduces each raw file's well-formed lines exactly, per host. 2,133 + 1 malformed = 2,134 non-empty raw lines. Nothing was dropped or normalized silently.

Population (denominators)

Counted over all 2,133 well-formed rows — the entire map, not a sample:

The 42 matter operationally: a collection fed by four directories is only quiet when all four are quiet. whcama and lmctvnyv have four feeders each; rompeviento_tv, nwctc, hwcamma, ehmmav, bcoco have three.

Known defects — recorded, not repaired

Four dirnames out of 2,128 (0.19%). Nothing here has been corrected; the right resolution is evidence from build_map.py on megastor, which derives the same mapping independently from IA's own metadata.

Conflicts — one dirname, two collections (CONFLICTS.psv)

Both also appear in collector.db as a directory with two YouTube channels on two different hosts with different remote_totals — the same problem seen from the other side, which is mutual confirmation rather than two separate bugs.

Map vs collector.db drift

Compared against collector.db on ser62 (read ?immutable=1, 2026-08-30):

• 6 disagreeing rows of 2,075. Four are the two conflicts above. The other two are genuine drift: lpsmo (map lpsmo, db klps18mo) and vmawi (map vmawi, db cvctvwi).
• 63 dirnames in the map with no collector.db channel row.
• 7 dirnames in collector.db not in the map.

Otherwise collector.db.channels.ia_collection is a faithful copy of this map.

Malformed row (MALFORMED.psv)

ser55|130|nasa_tv?mnt4

Sorts where nasa_tv belongs, between nactv1mb and npctv. A ? sits where the | should be; mnt4 may be a volume name rather than a collection. It is not guessed at here. Corroborating evidence from collector.db:

• A channel exists with metadata_dir=/home/access/youtube-archive/metadata/nasa_tv on ser55, remote_total 6,168, status=active — and ia_collection blank (one of only 6 such channels).
• That same channel (UCLA_DiR1FfKNvjuUpBHmylQ) is attached to 155 rows in channel_collection — by far the most of any channel, consistent with a fallback firing because the collection was never resolved.

So this single malformed line is very likely the root cause of a 6,168-video channel having no collection attribution anywhere in the system. Worth a human decision, not an automated fix.

Caveats

• This is a June-2026 snapshot. It is not authoritative for the fleet today.
• It is worker-side. It records where uploads were sent, not what IA actually holds. archive.db is authoritative for the latter.
• Coverage is not the whole CMA. 2,079 collections here against roughly 2,200 in archive.db (a documented figure, not re-verified — megastor was unreachable on 2026-08-30). The remainder are largely direct-to-IA / user-supplied collections with no fleet directory.

Recommended next steps

1. Load into collector.db as dirname_collection_map(dirname, collection, host, source, first_seen, last_seen) so it enters the backup path and becomes joinable. This file stays as the provenance record.
2. Cross-check against megastor once reachable: run build_map.py --root /mnt/md0/poweredge710/data/archive_stats --include-identity and diff. Two independent derivations of the same fact settle the four defects on evidence.
3. Resolve nasa_tv — 6,168 videos with no collection attribution.
4. Stop writing this to /tmp. Whatever produces these files should write to a durable path and push to the collector, like every other worker artifact.

↑ back to contents10.5-fleet-dirname-collection-map/PROVENANCE.md

Scrape API returns another query's results — /services/search/v1/scrape

Reported by: John Hauser (jhauser@pobox.com) Date of observations: 2026-08-30, 17:2x–17:40 UTC (plus an independent sighting via the ia CLI on the evening of 2026-08-29) Client public IP: 206.124.26.30 Severity (our view): high — wrong data is returned with HTTP 200 and no error, so a client cannot detect it.

Status: submitted 2026-08-31. Filed as a GitHub issue on jjjake/internetarchive, and this document sent by email with wider project context to Jake Johnson, the project maintainer. Related to #797, which reports the same endpoint returning a stale value for every query.

Summary

For certain (count, fields) parameter combinations, GET/POST https://archive.org/services/search/v1/scrape returns a result set belonging to a different query, ignoring the q parameter entirely. The response is HTTP 200, well-formed, and carries no error, no Age, no X-Cache, and no Retry-After header.

The clearest demonstration: a query for a collection that does not exist returns 5,291 items belonging to an unrelated collection.

Three different queries issued one second apart returned byte-identical results:

# captured 2026-08-30T17:37:37Z
GET  17:37:37  q=collection:sctvct                      total=5291  first_item=2012_Voice_of_The_Voter_Debate_CT_16th_District_State_Representative
GET  17:37:38  q=collection:cobcov                      total=5291  first_item=2012_Voice_of_The_Voter_Debate_CT_16th_District_State_Representative
GET  17:37:38  q=collection:zzzznotarealcollection12345 total=5291  first_item=2012_Voice_of_The_Voter_Debate_CT_16th_District_State_Representative

Ground truth (confirmed via the internetarchive Python library at 17:37:51, same host, 13 seconds later):

LIB  17:37:51  q=collection:sctvct                      total=5291  first_item=2012_Voice_of_The_Voter_Debate_CT_16th_District_State_Representative
LIB  17:37:53  q=collection:cobcov                      total=4814  first_item=021017_TEASE
LIB  17:37:53  q=collection:zzzznotarealcollection12345 total=0     first_item=none

So cobcov has 4,814 items and the bogus collection has 0 — but the first set of requests reported 5,291 for all three, with sctvct's payload.

How to reproduce

Please read the caveat below before spending time on this — we could not reproduce it from a clean starting point, only observe it on already-affected parameter tuples.

1. The tuples that were failing for us

These two returned sctvct's payload for any q, repeatedly, from two machines, for at least 15 minutes on 2026-08-30 ~17:25–17:40 UTC:

U='https://archive.org/services/search/v1/scrape'

# Both of these should return total:0 (the collection does not exist).
curl -sS --compressed "$U?q=collection:zzzznotarealcollection12345&fields=identifier&count=100" | jq '.total'
curl -sS --compressed "$U?q=collection:zzzznotarealcollection12345&count=100" | jq '.total'

Expected 0. We observed 5291, with sctvct's items in .items.

Control — the same query with any other count was correct throughout:

curl -sS --compressed "$U?q=collection:zzzznotarealcollection12345&fields=identifier&count=101" | jq '.total'   # -> 0, correct

2. The pattern we suspect, and could not trigger deliberately

Our working theory is that a response gets bound to a (count, fields) key and then returned for later requests using that key regardless of q. The obvious test is to prime a fresh tuple and then reuse it:

U='https://archive.org/services/search/v1/scrape'
N=698; F='identifier,mediatype,language'          # a tuple never used before

curl -sS --compressed "$U?q=collection:sctvct&fields=$F&count=$N" | jq '.total'   # prime with collection A
curl -sS --compressed "$U?q=collection:cobcov&fields=$F&count=$N" | jq '.total'   # then ask for collection B

If the theory held, the second call would return 5291 (A's answer). It did not — we got the correct 4814, and a third call for the nonexistent collection correctly returned 0. So priming a fresh tuple did not reproduce it for us.

3. What this means for your investigation

The affected tuples were already in the failing state when we found them, and we never saw one transition from correct to incorrect under our own traffic. So:

• Reproducing from a clean client may not work. Inspecting the state associated with the keys (count=100, fields=identifier) and (count=100, no fields) may be more productive than re-running our commands.
• The wrong payloads we saw were sctvct (5,291 items) and, earlier, a total of 5,416,280, which we never queried for and which looks like an unfiltered result.
• Requests were plain HTTPS to archive.org, mostly anonymous, at a low rate (a few requests per second at most, a few hundred over ~20 minutes).
• If it is per-client state, our public IP was 206.124.26.30 during the window above.

4. Ground-truth values, for checking any result

collection:sctvct                       5291
collection:cobcov                       4814
collection:bouldercolorado                 0   (a Vimeo username, not an IA collection)
collection:zzzznotarealcollection12345     0
collection:community_media          3224860

Environment

Exact client versions:

ia CLI / internetarchive   5.11.1   (also reproduced on 5.4.0, before upgrading)
python                     3.12.3
  requests                 2.32.3
  urllib3                  2.4.0
curl                       8.5.0 (libcurl/8.5.0 OpenSSL/3.0.13 nghttp2/1.59.0)
jq                         1.7
OS                         Ubuntu 24.04.4 LTS, kernel 6.17.0-1030-oem

We upgraded internetarchive 5.4.0 → 5.11.1 mid-session specifically to rule the client out. Both versions behave identically, and both issue the same requests: POST …/scrape?q=<query>&count=10000&total_only=true for counts, and POST …/scrape?q=<query>&count=10000&fields=<fields> for item listing.

Response headers on an affected request (nothing indicating a cache):

HTTP/2 200
server: nginx/1.31.3
date: Sun, 30 Aug 2026 17:37:54 GMT
cache-control: no-cache

No age, vary, etag, x-cache, or via header was present.

Detailed evidence

Our original reproduction

sctvct (Simsbury Community Television) has 5,291 items; cobcov has 4,814; zzzznotarealcollection12345 does not exist and must return 0.

U='https://archive.org/services/search/v1/scrape'
for C in sctvct cobcov zzzznotarealcollection12345; do
  curl -sS --compressed "$U?q=collection:$C&fields=identifier&count=100" \
    | jq -c '{q:"'"$C"'", total, first:.items[0].identifier}'
done

Observed: all three return total: 5291 and the same first item. Expected: 5291, 4814, 0.

Parameter isolation

All rows below query the nonexistent collection, so the correct answer is always total: 0. Captured 2026-08-30T17:38:50Z–17:39:02Z:

count       other params              total returned   correct?
--------------------------------------------------------------
100         fields=identifier         5291             NO
100         (none)                    5291             NO
100         fields=identifier,title      0             yes
101         fields=identifier            0             yes
150         fields=identifier            0             yes
200         fields=identifier            0             yes
500         fields=identifier            0             yes
1000        fields=identifier            0             yes
5000        fields=identifier            0             yes
10000       fields=identifier            0             yes
100         total_only=true              0             yes
10000       total_only=true              0             yes

Only specific (count, fields) tuples are affected. count=100 — the documented minimum — was affected for two field configurations; count=101 with the same field list was correct.

The tuple appears to be the key, not count itself

Using a parameter tuple never issued before in the session (count=698&fields=identifier,mediatype,language), every query was correct, including a repeat:

# 2026-08-30T17:39:26Z
q=collection:sctvct                      total=5291  first=2012_Voice_of_The_Voter_Debate_...
q=collection:cobcov                      total=4814  first=021017_TEASE
q=collection:zzzznotarealcollection12345 total=0     first=none
q=collection:cobcov                      total=4814  first=021017_TEASE

Meanwhile count=100&fields=identifier remained wrong on every retry, from both machines, for at least 15 minutes:

# 17:40:13Z  p14s-ub   attempts 1-3: total=5291, 5291, 5291
#            ser62     attempts 1-3: total=5291, 5291, 5291   (same minute; not separately timestamped)

Earlier in the same session, the same tuple returned different wrong answers

The affected tuple did not always return 5,291. Over roughly fifteen minutes it returned, for the same query:

• a correct value (5,291 for sctvct, 4,814 for cobcov) on first use;
• total: 0 for every collection, HTTP 200;
• total: 5416280 for every collection, HTTP 200 — three consecutive probes at 17:30:50, 17:31:10, 17:31:30 (see captures/retry_probe.log);
• total: 5291 for every collection, with sctvct's payload.

5,416,280 is far larger than any collection we query and looks like the result of an unfiltered query.

What we ruled out

Each of these was tested directly and made no difference — the affected tuple stayed wrong and the unaffected tuples stayed right:

• Query encoding — literal :, %3a, %3A, and collection:"value" all identical.
• HTTP method — GET and POST both affected. (POST with parameters in the body returns 400 {"error":"Missing query"}; the library sends POST with parameters in the URL and an empty body.)
• Authentication — anonymous, S3 Authorization header, and session cookies all identical.
• Connection: close vs keep-alive.
• User-Agent — curl/8.5.0, internetarchive/5.4.0 (linux), a Chrome UA, and a custom UA all identical.
• Client library version — internetarchive 5.4.0 and 5.11.1 behave the same.
• Client-side network — no proxy env vars, no VPN interface, no local proxy; direct TLS to 207.241.224.2. Reproduced from two machines.

The API's input validation is correct and loud, which makes the contrast sharper: count=3 returns 400 {"error":"count '3' is too small (min count=100)","errorType":"RangeException"} and an absent q returns 400 {"error":"Missing query","errorType":"DomainException"}. It is only the results that are silently wrong.

Our hypothesis (offered as a lead, not a conclusion)

The behaviour is consistent with a response cache somewhere in front of the service whose key includes count and fields but not q. That would explain: first use of a tuple being correct; subsequent different queries with that tuple returning the first one's payload; a fresh tuple behaving correctly; and the wrong value changing over time as entries are replaced.

We cannot see your infrastructure, so this is inference from client-side behaviour only. cache-control: no-cache is present on the responses, which argues against a deliberate cache and may point at something unintended.

Why this matters to us

We maintain a mirror of Internet Archive metadata for the Community Media Archive (~3.2M items across ~2,200 collections; collection:community_media reported 3,224,860 items via the library today). We were about to use the Scrape API to enumerate every collection's items in order to detect new and changed items.

Under this bug that harvest would have written one collection's item list into another collection's record, with no error to catch. Because responses are HTTP 200 and well-formed, there is no client-side signal to validate against short of already knowing the right answer.

Note that the ia CLI and Python library are not immune by design — only by their current parameter choices. Counting uses count=10000&total_only=true, but item listing uses count=10000&fields=<your fields>, which is exactly the kind of (count, fields) tuple that was affected here. An ia search --field ... invocation could return another collection's items. We believe this is what we saw on the evening of 2026-08-29, when CLI responses "did not make sense".

Questions

1. Is there a caching layer in front of /services/search/v1/scrape, and does its key include q?
2. Is this per-client (IP/session) or shared across clients? If shared, results could be contaminated between unrelated users.
3. Is there a request pattern or rate that triggers it, so we can avoid it while it is investigated?
4. Is there a header we can send to bypass the affected path, or a recommended parameterisation that is known-safe for bulk enumeration?
5. Would you prefer we use a different endpoint entirely for full-collection enumeration at this scale?

Attachments

Happy to run any diagnostic you'd like from this client, and to hold off on bulk enumeration until you've had a look.

↑ back to contents10.6-ia-scrape-api-anomaly/IA_SCRAPE_API_ANOMALY.md

CMA collection freshness — which collections have stopped being updated?

Answers a manager's question — "which collections haven't been updated in a while?" — and, more usefully, distinguishes work that can be done from work that cannot.

Two tools, run in that order. Both stream output, flush per record, resume after Ctrl-C, and sample reproducibly by --seed.

yt_title_census.py exists because the equivalent local scan cannot see the problem: with the default %(title).90s template, every same-titled video writes to the same filename, so only one survives on disk. Measured on cofsc — local scan 0 duplicates, remote census ×206. The count has to come from YouTube.

P=~/.local/share/pipx/venvs/internetarchive/bin/python
$P ia_scrape_harvest.py --sample 75 --seed 1        # archive side
./yt_freshness_probe.py --all                       # YouTube side

Outputs land in ~/cma_scrape_out/ and ~/cma_probe_out/. The durable record is collection_item_index.psv (dirname|ia_collection|item_identifier), which also feeds re-seeding megastor's _meta.xml tree.

Reading the census output

yt_title_census.py prints three independent measured counts per channel — none is a window, a tunable, or scaled per channel:

[7/200] 22cvma   461 vids  dup=43  max=x25

For 22cvma, N=25 because 25 videos are all titled 22-CityView - Cambridge, MA Live Stream, plus nine more groups of 2 (43 = 25 + 9×2). N varies because publishing practice varies — that is the signal, not a setting — and it is independent of channel size (pbcgfl reaches ×7 on 1,050 videos; ccxnewsmn ×12 on 16,929).

Why max drives the flag and dup does not. dup conflates one catastrophic group with many harmless pairs. Only one video per identical title can ever occupy the derived identifier, so max_group − 1 is the floor on items lost from the worst group — which is why wvual (×423) outranks klegks (×5) on comparable dup counts.

The single tunable is --min-group (default 5), identical for every channel. It encodes the practice of tolerating small duplicate counts and splitting processing only when the number gets large, and because raw/ persists every title list it can be re-tuned offline with --summary-only — no re-fetching.

Two guards that are not optional

The IA Scrape API can return another query's payload with HTTP 200 and no error (see 10.6-ia-scrape-api-anomaly/). The harvester queries a collection that cannot exist, before starting and every 25 collections, and aborts the run if it ever returns non-zero. Without it a bulk harvest silently writes one collection's items into another's.

--flat-playlist dates are approximate — median 1 day drift, mean 39, max 342. Every comparison here is keyed on video id, never date or title. That also makes it robust to the publisher practice of renaming a stream with a date and moving it to /videos.

What building this turned up

Related sections

• 10.5-fleet-dirname-collection-map/ — the rescued dirname → collection map these tools key on. Three naming grains must be held at once: dirname, ia_collection, and the download subdir.
• 10.6-ia-scrape-api-anomaly/ — the Scrape API bug report, and why the sentinel guard exists.
• 10.8-youtube-update-uploader-v2/ — the per-channel upload script these findings are about, and the best existing version of what a portable uploader must replace.

Caveats carried by everything here

• The 75-collection sample is 3.6% of 2,079. cofsc and vernca alone are 62% of all blocked videos found — do not extrapolate the 63.5%.
• Counter-example from the same session: ser62's 3,102 missing videos contain 0 title collisions. The class is concentrated, not uniform.
• Probe windows cap at 500; BEHIND+ means the window was truncated and the count is a floor.
• archive.db was unreachable while this was built, so the archive side comes from the live IA API rather than the weekly mirror.

↑ back to contents10.7-cma-collection-freshness/README.md

youtube_update-v2 — the per-channel upload script rewrite

The v2 rewrite of the a58ma archive/upload script, plus its lessons-learned write-up. Imported 2026-08-30 from ~/Downloads; not previously in the pile. All three files are byte-identical to the copies they came from.

Ignore the .sh mtime. The current script's timestamp is when it was retrieved, not when it was written; the script itself has not been modified recently. The PRE-FIX copy's 2026-06-18 mtime is genuine.

The script matches the document — verified

An earlier revision of this README flagged the two as out of sync, because the only copy then available was the 203-line pre-fix snapshot. The complete version has since been recovered and installed. Every fix the document describes is present:

The diff is exactly what the document predicts: the current script is a strict superset of the earlier one apart from two replaced lines — the old s/|/","/g and the old s/"item"/"identifier"/ — plus one wholly new section, 8b. Validate ia_upload.csv before uploading. The validator's comment even cites the failing value verbatim (the Tennyson title whose interior quotes and commas made a 79-field row parse as 81).

This is now a working reference for the fixes, not just a description of them — which matters for the cross-platform rewrite, since U8–U11 in 10.7-cma-collection-freshness/PLANNING_INPUT_streams_and_identifiers.md are drawn from exactly these mechanics.

Why this is filed at 10.8

This is the uploader, which is distinct from 10.2 (the archive dashboard / collector / monitoring). 10.2 already made the monitoring half cross-platform (agent_platform.py, macOS and Windows worker runbooks). The download/upload half is still this: bash + jq + awk + sed + GNU rename + ia. That gap is item 4 of "the biggest unfinished areas" in 00.0-stepping-back-from-CMA-operations/cma-notes.md — "Cross-platform worker process — the gate on fleet expansion beyond Linux-fluent people."

This directory is therefore the concrete starting point for that work: the best existing version of what a portable uploader has to replace.

What this script confirms about identifiers

Two things visible here corroborate findings recorded in 10.7-cma-collection-freshness/:

1. The identifier prefix follows the dirname, not the collection.

CHAN=a58ma                                  # dirname
ia search -i collection:ccatma > itemlist   # DIFFERENT collection
sed 's/a58ma-//' itemlist > itemlist_no_prefix

a58ma → ccatma is one of the 132 documented dirname≠collection exceptions (see 10.5-fleet-dirname-collection-map/). The script correctly uses the dirname for paths and the identifier prefix, and the collection for the IA query. Any reimplementation must keep those two keys distinct — see [[cma-dirname-vs-collection-grain]].

2. /videos only — no /streams. Consistent with the fleet sweep: 38 of 4,111 backlog scripts fetch /streams at all. See 10.7-cma-collection-freshness/PLANNING_INPUT_streams_and_identifiers.md.

The lessons that generalise beyond this script

Four of the document's takeaways are not a58ma-specific and should carry into the OS-independent uploader (folded into 10.7's planning input as requirements U8–U11):

• A validator that disagrees with the real parser is worse than none — it manufactures false confidence. The naive ","-token counter failed open on a genuinely malformed row (the Tennyson title with interior quotes and commas), reporting 79 fields for a row the real parser read as 81. Measure with the same library the consumer uses.
• Don't hand-roll a CSV parser. The quote-aware awk attempt returned 5 for a row the real parser read as 7, and the error failed open.
• Fix format corruption at the last unambiguous point — the |-delimited source, where every boundary is still explicit. After the lossy transform you cannot distinguish structural from content quotes, so repair becomes guessing. This is why the repair rebuilds rather than patches.
• Design repairs around a real failing example, not a hypothesised one. The earlier \" → "" repair was a no-op for the problem it claimed to fix; the actual corruption was bare interior quotes. A single concrete failing case beat a page of reasoning.

Plus two operational gotchas worth keeping: reordering a sed step invalidates every later step that matches on intermediate text (it silently broke the item→identifier rename), and mawk reserves exp as a builtin so awk -v exp=… dies with a non-obvious message.

REQUIREMENTS FOR THE REWRITE — filename collisions (added 2026-09-02)

The cross-platform uploader must not inherit the current filename scheme. A defect measured on 2026-09-02 makes this concrete: see 10.7-cma-collection-freshness/FINDING_2026-09-02_restrict_filenames_collapses_non_ascii_titles.md.

Fleet-wide, live channels with no disambiguator: 1,734 channels, 87,025 videos would be lost. Of those, 523 videos across 60 channels have titles that reduce to _ or - entirely — the source of the stray _.mp4 files seen on the filesystem.

What the new uploader must get right

1. The identifier must be unique by construction, not by hope. Today it is derived from the filename, which is derived from the title. Two videos with the same title — or two whose titles both vanish under --restrict-filenames — produce the same identifier. Include the video id. %(upload_date)s is NOT sufficient: multilingual and live-stream channels post several videos the same day (177 of mcpsmd's 224 losses survive a date suffix).

2. Non-ASCII titles must survive as metadata even when they cannot survive as a filename. The current architecture is right here and must be preserved: IA identifiers are ASCII (0 non-ASCII fleet-wide) while IA titles keep Unicode (97,806 items fleet-wide). Only the identifier degenerates. Do not "fix" this by transliterating.

3. A skipped download must never be recorded as complete. --no-force-overwrites + a shared --download-archive means yt-dlp skips the write and still records the id, so the loss is permanent and silent. Whatever the new uploader uses for state, record only on verified success.

4. The identifier source of truth must be explicit. Today it is ._filename inside the info.json, not the file on disk — renaming a file does not change the identifier. That indirection cost real debugging time; make it obvious.

5. Enforce the 100-char cap at construction, not by truncating afterwards. prefix + title_budget + separator + 11-char id must be < 100, and the budget must be derived from the prefix length per channel.

6. Fail loudly. Every symptom here was silent: yt-dlp exited 0, the log said only "already been downloaded", and the id was recorded. The new uploader should treat "the output filename already exists for a different video id" as an error, not a skip.

A test case worth keeping

mcpsmd (Montgomery County Public Schools, MD) publishes the same parent- information series in English, Amharic, Chinese and Korean on the same day. Any scheme that survives that channel handles the general case.

이제 압니다! 성적표          -> old: '_'   new: '_-IQFVuU0WRjY'
현재您知道了! 返校倒計時      -> old: '_'   new: '_-<id>'

↑ back to contents10.8-youtube-update-uploader-v2/README.md

archive.txt surgery — remove ids so a channel can re-fetch

The problem it solves

An id in a worker's yt-dlp --download-archive means that video was already fetched and recorded. If the item then collapsed (TITLE_COLLISION) or landed under the wrong identifier, it is missing from IA and a re-run silently skips it. The id is the thing preventing recovery. Measured on a 20-channel sample, 66% of "missing" videos are in this state — they are not backlog, they are blocked.

What it refuses to do

It does not fetch anything and does not touch archive.org. Re-fetching is a separate deliberate step; the command is printed.

Resolving the archive file — never assumed

The path is resolved the way yt-dlp resolves it: the backlog script's own --download-archive wins, else the --config-locations chain is followed. 145 configs on the fleet use a relative archive.txt (per-dirname) and 191 the shared /home/access/archive.txt, so assuming the shared path would be wrong about a third of the time. The resolved path, how it was resolved, the config chain, size, line count and mtime are all reported before anything happens.

Usage

./archive_txt_surgery.py --self-test
./archive_txt_surgery.py --dirname <d> --collection <c> --ids-file <f>            # dry run
./archive_txt_surgery.py --dirname <d> --collection <c> --ids-file <f> --sandbox  # rehearse on a copy
./archive_txt_surgery.py --dirname <d> --collection <c> --ids-file <f> --yes      # apply
./archive_txt_surgery.py --dirname <d> --restore <backup-path> --yes              # revert

The ids file takes one id per line; # comments, blanks, and id|title worksheet rows are all accepted.

Safety sequence when applying

1. Resolve worker -> resolve archive path -> report both
2. Prefix check, id-presence check, leftover-media check, running-cycle check
3. Audit JSON written BEFORE any mutation (~/archive_surgery/audit_<dir>_<stamp>.json) — records host, archive path, every id, its line numbers, the expected backup path and the exact restore command
4. cp -p backup to <archive>.bak-<stamp> (refuses if one exists)
5. Rewrite to a temp file in the same directory, fsync, line-count check, then os.replace (atomic)
6. Verify: every id absent, and after == before - removed
7. Audit updated with the result; restore and re-fetch commands printed

Restore

./archive_txt_surgery.py --dirname <d> --restore <archive>.bak-<stamp> --yes

Keeps a <archive>.prerestore copy of what it replaced, and verifies the line count matches the backup. Dry-run by default here too.

Test results — 2026-09-01, all passing

16 offline self-tests (--self-test): id parsing, worker resolution including the multi-host case, all five prefix-check outcomes, and the removable/missing/blocked/duplicate-line classifier.

End-to-end against ser62, live data:

The real /home/access/archive.txt was never modified — verified after the run: 677,447 lines, mtime unchanged at 2026-08-29 16:15:55, zero stray backup or sandbox files.

Two bugs were found by the end-to-end test and fixed: the driver sent $HOME/... as a literal that the worker never expanded (every path was wrong, and it presented as "no prefix found" rather than an error); and the multi-host case originally forced --host when the supplied collection could disambiguate.

The test case — 6 stranded ActonTV items

actontv_6.ids. These were published 2020-10-25 under the wrong dirname and are the only copy — none of their YouTube ids appears in actonma. They are the missing first episodes of three series otherwise complete there: Transgender in the South ch.1–2, The Creature Teachers ep.1, Kelly's Quest ep.2. All six target identifiers (actonma-…) are currently free, so a re-fetch would not immediately re-collide.

APPLIED 2026-09-01 — the full loop is closed

independent pre-flight backup   archive.txt.PREFLIGHT-20260901T040834Z  (md5-verified identical)
tool's own backup               archive.txt.bak-20260901T040843Z
edit                            677,447 -> 677,441 lines, 6 removed, VERIFIED
independent diff vs backup      exactly the 6 target lines gone, 0 lines added
re-fetch (JH, on ser62)         all 6 downloaded and re-recorded in archive.txt
upload to archive.org           all 6 present, collection=['actonma', 'community_media', 'newsandpublicaffairs']

Verified on IA with a sentinel query (actonma-zzzznotarealitem12345 -> MISSING), per the scrape-API contamination lesson; ia CLI 5.11.1, direct metadata endpoint.

Three series in actonma are now complete. The audit JSON records the whole operation at ~/archive_surgery/audit_actvma_20260901T040843Z.json.

One title had changed upstream since 2020: krPQxSK9vEc is now "The Transgender Community and Religion - Kelly's Quest - Episode 2", not "Kelly's Quest: Episode 2 - Transgender and Religion". The minted identifier follows the CURRENT title. Do not predict identifiers from archive.db titles — they are a snapshot of what was fetched, not of what the channel shows now.

Two media-guard bugs the live run exposed (v1.1)

Both would have caused the guard to mis-report, and neither was reachable in the sandbox because the sandbox never had leftover media.

1. Extension allow-list was wrong. The guard looked for .mp4/.mkv/.webm/.m4a/.part. ActonTV produces .HD.mov, which the allow-list silently passed — the exact silent no-op the tool exists to prevent. Replaced with a sidecar deny-list: anything sharing the stem that is not a known sidecar counts as media.
2. Ownership and prefix matching. grep -l on the video id also matched channel/playlist-level dumps (ActonTV.info.json contains all 37 ids), and glob(stem + "*") is a PREFIX match, so stem ActonTV swallowed ActonTV-2015_in_Review.HD.mov. Result: all six ids reported the same media file. Now the info.json's own top-level "id" must equal the video id and carry no entries, and a candidate must be stem + "." + suffix.

Regression-tested: cobva (.mp4) still blocks correctly, and each of the six ActonTV ids now names its own .HD.mov. They block correctly today because the media is back on disk after the re-fetch — removing those ids again would be a no-op until the uploader's cleanup runs, which is exactly what the guard is for.

What this tool deliberately leaves alone

• The 6 existing actvma-… items sitting in Auburn's collection. IA identifiers are immutable, so a re-fetch creates correct new items; darking the old ones is a separate archive.org action.
• Other hosts holding the same ids. Only the current worker's archive is edited. For this case ser55 and ser5 also hold the 6 ids, but their actvma points at ACTVAuburn, so they would never re-fetch ActonTV content.

v1.2 — mtime now advances (was: preserved)

v1.0/v1.1 used shutil.copystat / copy2, which preserved the original mtime across the atomic replace. That was wrong for two independent reasons:

1. ls -lth is JH's default way of finding recently-changed files. A surgery that leaves mtime untouched is invisible to it — the file sorts as though nothing happened.
2. rsync quick-check safety. rsync -auvx transfers when size OR mtime differs. Today's edit propagated only because the byte count changed. A future size-neutral edit (remove and re-add to the same length) with a preserved mtime would be invisible to every sync job, in both directions, with no error — precisely the silent-wrong failure this tool exists to prevent.

Change: copystat -> copymode (+ best-effort chown) on the live file, and copy2 -> copy for the backup and restore. Permissions and ownership survive; mtime becomes "now" on the edited file, the .bak-<stamp>, and any restore, so all three surface at the top of ls -lth. The pre-edit mtime is recorded in the audit JSON (archive_mtime_before_edit) and printed in the report, so nothing is lost.

Verified on ser62 with a controlled file (mtime forced to 2020-01-02, mode 640):

BEFORE  mtime=2020-01-02 03:04:05  mode=640  lines=3
AFTER   mtime=2026-08-31 21:51:02  mode=640  lines=2   <- advanced, mode kept
BACKUP  mtime=2026-08-31 21:51:02  mode=640  lines=3
ls -lth  both files at the top

Full sandbox E2E re-run after the change: 677,447 -> 677,444, verified, restore verified, real archive untouched.

Which sync jobs actually carry /home/access/archive.txt

Audited across the jobs JH named as important (rsync_to_zimacube, rsync_to_encoder1, rsync_to_megastor, rsync*to_hp*) on the current workers. Check every rsync line — several scripts carry it on a later line (wo44/rsync_results_to_zimacube.sh on 1 of 9; ser56/rsync_to_encoder1.sh on 1 of 7):

All are outbound, so none can overwrite a worker's archive.txt. ser62's to_megastor / to_encoder1 / to_hp3601 do not carry it, so megastor's copy of ser62's archive.txt will not reflect surgery done there.

↑ back to contents10.9-archive-txt-surgery/README.md

Backlog URL repair — replace stale handles with channel ids

The problem

Every channel URL in the fleet's get_new_video_metadata*.sh is a handle (@somename) or a legacy /user/ name. Both change when an operator renames. Channel ids do not.

16 collections holding ~16,000 archived items were found running weekly in live cycle lists and fetching nothing because their URL no longer resolved — silent for 6 months to 7 years. Nothing surfaced it: the collections are absent from collector.db, so neither the title census nor the dormant analysis ever saw them, and a yt-dlp 404 just rolls on to the next dirname.

Replacing the handle with /channel/UC… removes the failure class rather than resetting its clock. See 10.7-cma-collection-freshness/STALE_URL_COLLECTIONS_2026-09-01.md for how the replacement ids were established and proved.

How a replacement id is proved before it is used

Not by searching for the channel name. "Canton Community Television" matches several Cantons, and name-matching is exactly what put another organisation's videos into cotmfl and nactvmb.

A video we already archived came from that collection's channel by definition. So: newest archived youtube_id -> resolve the video -> read its channel_id. Then confirm by overlap — how many ids we already hold appear on that channel. Overlap proves both that the channel is the collection's real source and that it is fetchable. mcpsmd 1,974 of 1,981; lrksprca 479 of 479.

Safety model

Dry-run is the default; --yes applies.

Usage

./repair_backlog_urls.py --self-test
./repair_backlog_urls.py --host ser6                          # dry run
./repair_backlog_urls.py --host ser6 --yes
./repair_backlog_urls.py --host ser6 --restore-stamp 20260901T172140Z --yes
./repair_backlog_urls.py --host ser6 --only-backlog           # skip the sibling script

By default it patches both get_new_video_metadata_backlog.sh (the executed one) and get_new_video_metadata.sh (its non-executed sibling, which carries the same stale URL). Leaving the sibling stale guarantees a future audit finds an inconsistency; --only-backlog opts out.

Applied 2026-09-01

wo44 — applied 2026-09-02

14 files, all verified. Revert stamp 20260902T045832Z. An independent pre-flight snapshot of all 14 originals is at wo44:~/wo44_urlrepair_PREFLIGHT-20260902T045824Z.tar.gz (14 members, 14 stale URLs captured), separate from the tool's per-file backups.

JH intends to run these individually rather than inside the weekly cycle batch, because of the volume of new files they will pull.

All 12 confirmed collections are now repaired — ~3,113 videos become collectable (wo44 2,602 + ser6/ser56 511).

Verification performed

11 offline self-tests (5 driver, 6 helper URL-parsing). Dry run on both hosts. After applying, an independent read-back: 0 stale handle/user URLs remaining, modes preserved (664 on ser6, 644 on mpactmi), 2 backups per dirname. Then every URL as written in the patched file was executed from the worker itself, read-only — all six resolve.

Three traps this hit

1. ntttx had two URL lines (/videos and /streams) in each of two files, and the streams line carries the date disambiguator in its -o template. Patching only the first line would have left half the channel dead.
2. vsuga's URL was malformed — /user/valdostastateuniv/video345s. The tab logic drops anything that is not a real channel tab, so the typo was removed rather than carried forward. Its sibling file had the clean form; the two needed different handling and both landed correctly.
3. The first verification was wrong, not the patch. ser56 reported entries=0 for both dirnames — it was yt-dlp: command not found. The binary is at ~/.local/bin/yt-dlp there and is not on PATH in a non-interactive ssh shell (ser6 has it at /usr/local/bin). Any future remote check must resolve the binary via a login shell or an absolute path, or it silently reports zero.

Files

repair_backlog_urls.py   driver (self-tests, dry-run default, restore)
_url_helper.py           runs on the worker; JSON in/out; no policy decisions
url_repair_plan.psv      dirname|host|live_list|stale_url|new_channel_id|new_url|evidence

↑ back to contents10.10-backlog-url-repair/README.md

<dirname>_clean_up.sh — analysis and proposed changes

Nothing has been modified. This is analysis and a proposal.

These scripts delete local media that has been successfully uploaded to archive.org. They decide by reconstructing the identifier from the filename — which is exactly the coupling the 2026-09-01/02 template work just changed.

What the script does

1  ls <downloaddir>/*.mpeg4 *.HD.mov | grep -v % | grep -v + | grep -v @   -> mnt7_files
2  ...same, strip dir and extension                                        -> mnt7_base
3  ia search -i collection:<coll> | sort -u                                -> itemlist
4  sed 's/<coll>-//' itemlist                                              -> itemlist_no_prefix
5  grep -Fxf itemlist_no_prefix mnt7_base | awk '{print "rm <dir>/" $1 ".mpeg4"}' > rm_already_uploaded.sh
6  sh ./rm_already_uploaded.sh                                             <- DELETES

The join is filename stem == identifier minus prefix.

Which copy actually runs

backlog_full_cycle.sh invokes sh {}_clean_up.sh from the metadata root, not {}/{}_clean_up.sh. On ser62 there are 669 root-level scripts and 300 in subdirectories, and the two copies of a channel differ (the root copy points at /mnt7, the subdir copy at /mnt4). The root copy is the one that executes. Any change must target it. Called 10+ times per cycle.

Defects, worst first

A. Deletion is gated on ia search — the API we filed a bug against

669 of 669 root scripts use ia search -i collection:X. Per 10.6-ia-scrape-api-anomaly, that API has been observed returning another query's results with HTTP 200 and no error.

• An empty response is safe: grep -Fxf on an empty pattern file matches nothing, so nothing is deleted.
• A contaminated response is not. Identifiers from an unrelated collection could match local filename stems and delete media that was never uploaded.

There is no sentinel query, and no check that the matched item actually contains a media file — ia search proves an identifier exists, not that the upload succeeded. An empty or failed item satisfies the test.

This is the only step in the pipeline that destroys the sole local copy.

B. The filename->identifier reconstruction does not mirror the awk

rv_format_jq_metadata.awk applies transformations the clean_up does not undo:

gsub(/\+/,"",item)  gsub(/%/,"",item)  gsub(/@/,"",item)
sub(/\.m4v/,"",item)  sub(/\.mov.mp4/,"",item)  sub(/\.mov/,"",item)
if (length(item) >100) { item = substr(item,1,99) }        <- TRUNCATION

The clean_up only strips the directory and the extension. So:

• Special characters. A file Foo+Bar.mpeg4 uploads as <coll>-FooBar. De-prefixed that is FooBar, which never equals the stem Foo+Bar. Rather than handle this, the scripts exclude such files from consideration entirely (grep -v \% | grep -v \+ | grep -v \@, present in 666 of 669). Consequence: those files are uploaded but can never be cleaned up. Measured on ser62: 181 of 333 remaining media files (54%) contain %, + or @. They accumulate forever.

• Long identifiers. Where prefix + title exceeded 100, the awk truncated to 99. The truncated identifier never matches the full filename stem, so the file is never deleted. This is the "long filenames aren't handled well" case.

Note the asymmetry JH identified: the special characters are irrelevant to the filename and decisive for the identifier, yet the exclusion is applied to the filename.

C. The prefix strip is unanchored

sed 's/<coll>-//' replaces the first occurrence anywhere on the line, not at the start. 1,334 unanchored strips on ser62; 0 anchored. An identifier whose title happens to contain the collection name is mangled, and the mangled value is then used as a deletion key.

D. awk '{print "rm ..." $1 ...}' splits on whitespace

$1 is the first whitespace-delimited field. Any filename containing a space yields a wrong rm target. --restrict-filenames makes this rare today, but older files predate it.

E. Shared download directories

The download path is the uploader name (/mnt7/downloaded/143tv_CCTV), not the dirname. Where two dirnames feed one uploader directory — and 42 collections are fed by more than one dirname — channel A's clean_up lists channel B's files and matches them against A's identifier list.

How the recent changes affect this

The -%(id)s suffix does NOT break the existing join

New filename Title-abc123XYZ_9.mpeg4 -> stem Title-abc123XYZ_9; the awk mints <coll>-Title-abc123XYZ_9; de-prefixed it matches the stem. The round trip still works, because the identifier is derived from the filename.

It does not fix defects A-E either

Every defect above is orthogonal to the suffix and remains.

But it makes a much better join possible — for the first time

The video id is now in the filename, and it is already in the IA item metadata (youtube_id; archive.db holds 2.7M of these). That is an exact, fixed-length key that is immune to every transformation in defect B: no character stripping, no truncation, no prefix arithmetic.

Proposed changes

1. Join on the video id, not on reconstructed strings. Take the id from the filename suffix (or the local .info.json, which is authoritative), and compare against the IA item's youtube_id. This removes defects B, C and D at once — there is nothing left to mirror.

2. Prove the upload before deleting. The test should be: an item exists, is in the expected collection, and carries a media file of plausible size. Existence of an identifier is not evidence that the media landed.

3. Do not use bare ia search as the deletion gate. At minimum include a sentinel query per run and abort if it returns anything, per ia-scrape-api-contamination. Better: verify per item, or drive the check from archive.db, which already stores youtube_id per identifier.

4. Stop excluding %, +, @ — handle them. With an id join the characters stop mattering, and the 181 stranded files on ser62 (plus their equivalents fleet-wide) become collectable garbage again.

5. Dry-run by default. Today the script writes rm_already_uploaded.sh and immediately executes it. It should require an explicit flag, and log what it deleted, with counts, to a dated file.

6. Never delete on a zero-match run. If the item list is empty or the sentinel fails, exit non-zero and delete nothing rather than proceeding.

7. Patch the ROOT copies (669 on ser62), not the subdirectory copies — and reconcile the two, since they currently disagree about the mount point.

Suggested order

The safety fixes (2, 3, 5, 6) are independent of the id join and can land first; they reduce the blast radius of the defect that can destroy a sole copy. The id join (1, 4) is the structural fix and is much easier now that the suffix exists.

What to measure before and after

• media files remaining on disk that ARE uploaded (should fall)
• media files deleted whose item lacks a media file (must be zero)
• files containing %, +, @ still stranded (should fall to zero)

↑ back to contents10.11-cleanup-script-analysis/README.md

Discover Agenda Systems via Wayback Machine

Tools and documentation for identifying which agenda-management vendor each US local government uses, by enumerating vendor tenant subdomains from the Internet Archive's Wayback CDX index.

Purpose: expand the set of known jurisdictions using one of ten agenda systems, so existing scrapers can be pointed at more targets and new extractor work can be prioritized by real client counts.

Status at packaging (2026-08-03): discovery complete — 12,362 tenants enumerated across ten vendors, up from a prior all-vendor working list of 2,414 — a 5.1x expansion. Next step is classification.

/code

All are stdlib-only except cc_columnar_hosts.py, which needs duckdb.

Quick start:

python3 code/vendor_host_harvest.py --sources wayback --sleep 1 --resume \
    --out-dir ./vendor_hosts

Read the runbook before a first run — particularly the four run modes (--resume / --append / --force / none), which govern whether existing results are protected.

/docs

Each document is provided as .md, .docx, and .pdf.

Not included

Harvested data (hosts_*.csv, raw_*.csv, all_vendor_hosts.csv, summary.txt) and scan state files are not packaged here — they are outputs, not assets, and are regenerated by running the tools.

Version History

↑ back to contents20.0-discover_agenda_systems_via_wayback_machine/README.md

Civic meeting scrapers — review bundle

2026-08-03

Start here

docs/EXECUTIVE-SUMMARY_2026-07-26 — scope, effort, what went wrong, and the decisions now needed. Everything else is detail behind it.

Each document is provided three ways: .pdf (read anywhere), .docx (comment and track changes), .md (plain text, version-controllable). The content is identical.

What is in here

docs/    seven documents, each as .pdf / .docx / .md
code/    six tools and three test suites

Documents, in reading order

Code

Three things a reviewer should know

The numbers answer different questions. The corpus counts places (cities, towns, villages). The Wayback CDX search counts vendor client sites, which include school districts, counties and special districts. They should never be added together — the executive summary explains why this matters for scope.

"Self-hosted" does not mean bespoke. It means no vendor name appeared in the web address. Councils commonly run a branded page on top of a commercial agenda system, with the join hidden in JavaScript. An earlier version of this summary mistook that label for a finding and wrote off ~4,800 sites; the correction is recorded in the summary and the tier-4 document.

Nothing here is finished collecting. The tools work and have been run against real sites, but collection is early. The summary's open-items list is the current state.

↑ back to contents20.1-civic-scrapers-bundle_2026-08-03/README.md

Municipal Agenda Scraper — Bundle Manifest

Bundle date: 2026-07-21 Purpose: Self-contained snapshot of the municipal agenda-scraper project — working code plus all project documentation — for review and for importing into other working sessions.

What this project is (30 seconds)

A toolkit for collecting agendas and minutes from municipal government websites at scale. A working scraper handles CivicPlus AgendaCenter sites today. The project has mapped the full ~2,400-site landscape across ~11 meeting-management platforms and has a decided build order for extending coverage.

If you read one file: docs/civicplus-scraper-exec-summary_2026-07-21.* (one page, non-technical).

Bundle layout

/
├── MANIFEST.md          ← you are here
├── code/
│   └── civicplus_agendas.py   (the scraper, ~1,600 lines, Python 3)
├── data/                (small representative sample — see data/README.md)
│   ├── sample_input_sites.csv        (what --preflight-csv consumes)
│   ├── sample_rejected_sites.csv     (what --summarize reads)
│   ├── sample_recovered_sites.csv    (a passing-output example)
│   └── vendor_distribution_summary.txt  (full 2,414-site platform tally)
└── docs/                (4 documents, each in 3 formats: .md .docx .pdf)
    ├── civicplus-scraper-exec-summary_2026-07-21.*
    ├── civicplus-scraper-project_2026-07-21.*
    ├── civicplus-scraper-runbook_2026-07-21.*
    └── civicplus-agenda-scraper_2026-07-20.*

Formats: every document is provided as Markdown (.md, source), Word (.docx), and PDF. Non-technical reviewers should use the .pdf or .docx; the .md is the editable source.

The documents — what each is for, and who should read it

Read order for a new person: exec-summary → project → runbook. The 07-20 doc is background.

The code

code/civicplus_agendas.py — single-file Python 3 scraper.

• Dependencies: pip install requests beautifulsoup4 lxml
• Handles: CivicPlus AgendaCenter (categories, years, agendas, minutes), PDF download into a State/City/Category/Year/Type/ tree, legacy vendor subdomain resolution, endpoint auto-detection with manual overrides.
• Batch tooling: --preflight-csv (test many sites for compatibility), --summarize (triage the rejects by platform/vendor), vendor detection.
• Quick start: python3 code/civicplus_agendas.py --help

Full operating instructions are in the runbook.

Current status at a glance

• CivicPlus AgendaCenter scraper: working (~1,300+ sites addressable).
• Full platform map: complete (2,414 sites classified).
• Build order: decided — CivicClerk first (public API, largest untapped block), then the Granicus/Legistar family. Details and rationale in the project doc.
• Not yet built: refactor into a per-platform plugin architecture; any second extractor.

Important caveats (please read before acting on the numbers)

• Vendor counts come from an imperfect upstream classifier (a separate effort). Treat site counts as approximate (±10–20%). Confirm before committing resources.
• Platform feasibility is unconfirmed for platforms beyond CivicPlus. Each new extractor should begin with a quick check that its API/site structure is accessible with plain HTTP (some may need a real browser).
• The docs candidly record several mid-project course corrections. That transparency is intentional — the lessons are part of the deliverable.

Sample data included

A small representative slice of the working data is in data/ (see data/README.md) — enough to understand the file formats and try the tooling. The full working data (complete input site lists, full preflight output, debug HTML dumps) is not bundled: it is large, environment-specific, and regenerable by the code. The docs reference these fuller files by name where relevant.

↑ back to contents30.0-agenda-scraper-bundle_2026-07-21/MANIFEST.md

CivicPlus Archive — Forensic Assessment Bundle
==============================================

  docs/   Start here.  (All findings current as of the 2026-08 full run.)
            EXECUTIVE_SUMMARY  — 3 pages, non-technical: what this collection is,
                                 what survived, and what to do next.
            PROGRESS_SUMMARY   — what was investigated and established.
            RUNBOOK            — how to re-run the tools, what every category
                                 means, and what to investigate first.
            SQL_REFERENCE      — the database schema, how the tables join, and
                                 ready-made queries for your own questions.
            LESSONS_LEARNED    — what the collection taught, and what the
                                 assessment's own mistakes taught.
          Each is provided as .md (source), .docx (Word) and .pdf.

  code/   civicplus_forensics.py    builds the index and classifies everything
          civicplus_investigate.py  named diagnostic checks, no SQL needed
          civicplus_refetch.py      probe whether source sites still answer,
                                    fingerprint who serves them now, and
                                    repair what they will serve
          civicplus_migration_test.py  for an address that has gone dark: did the
                                    jurisdiction leave, or just move?
          civicplus_blank_scan.py   find "blank" PDFs -- structurally valid
                                    (%PDF, %%EOF, one page) but no text, fonts,
                                    or images, so they render empty. Passes every
                                    other check yet carries nothing. Modes:
                                    strata (size histogram), sample (blank rate),
                                    full (exact count), breakdown (--resolve the
                                    www bucket + --rebuild-url a re-fetch
                                    worklist), fetch (re-download and verify not
                                    blank), ledger (roll the fetch log into a
                                    per-outcome/per-tenant census), check (spot a
                                    file).
          Python 3.8+, standard library only. The corpus is read-only.
          (blank_scan's optional --render uses poppler's pdftoppm if present.)

  data/   civicplus_host_lookup.csv       web address -> state/place (2,063 rows)
          civicplus_slug_lookup.csv       registry, normalized (1,550 rows)
          civicplus_script_crosswalk.csv  each batch script and its target
          sample_*                        shape of each input and output file

Quick start
-----------
  python3 code/civicplus_forensics.py \
      --root ~/civic-data-project \
      --sites civicplus_sites.csv \
      --host-lookup data/civicplus_host_lookup.csv \
      --db civicplus_index.db --out reports --stage all --verify-magic

  python3 code/civicplus_investigate.py --db civicplus_index.db --check orphans

See docs/RUNBOOK for everything else.

↑ back to contents30.1-civicplus_forensics_bundle(13)/README.txt

Municipal Meeting-Document Scrapers — Bundle

Bundle date: 2026-07-26 Purpose: complete, current snapshot of the CivicClerk/CivicWeb scraper effort — code + docs in one place, so work can resume in a fresh conversation with no context loss.

Layout

/code   — the tools (current versions only; superseded drafts excluded)
/docs   — runbook, master plan, project summary

/code — tools (run order per docs/runbook.md)

NOT YET BUILT: civicclerk_reconcile.py (Stage F audit — joins runs/ + disk tree + snapshots). Design is in runbook §7; records + snapshots are being captured now so it can run retroactively.

Primary scraper is at feature level: records emission + Ctrl-C-safe atomic records + archive-then-write eviction + progress heartbeat. (Built up over versions v1→v3; only the current consolidated file is included here.)

/docs

Key facts that took real work to learn (don't re-derive)

• civic-scraper library is BROKEN for modern CivicClerk (parses a __VIEWSTATE field React portals lack). All tools here are from-scratch against the real API.
• CivicClerk wildcards DNS — every hostname resolves. Existence must be checked via the API (/v1/Events → JSON vs empty body), never DNS.
• Pagination is mandatory (@odata.nextLink, ~15 rows/page). Files come from the single-event Events({id}) record, not the collection.
• Preflight samples events across the WHOLE date range (recent events are often empty placeholders — sampling only recent = false negatives).
• Two recurring "honest" failures: phantom tenants (@events: no rows → drop) and video-only CivicClerk (@files fail → docs are on CivicPlus, route there).
• Data root /mnt0/civicclerk_output/ is self-describing: {ST}/{Place}/{Board}/{Year}/*.pdf
  • runs/{siteid}.csv (records) + discovery/ (snapshots) + logs/.

Environment

• Ubuntu; Python 3, standard library only (no third-party deps for the API tools; civicclerk_html_archive.py uses BeautifulSoup if present, else stdlib).
• Politeness: keep --delay ≥ 0.3–0.5, modest --jobs.

↑ back to contents31.0-civicclerk_scrapers_bundle/civicclerk-scrapers-bundle_2026-07-26/MANIFEST.md

CivicClerk — Texas run: diagnostics, findings and code changes

Handoff note. This work happened inside a Legistar-focused conversation and belongs with the CivicClerk effort. Everything needed to pick it up is here.

Date: 2026-08-05/06. Scraper went from v3 (2026-07-26) to v5 (2026-08-05).

1. The run

119 clients | 74,177 downloaded | 5,785 cached | 1 failed | 32h24m | ~41/min

Command as issued:

python3 civicclerk_api_scrape-2026-07-26-v3.py \
    --portals TX_civicclerk_remaining.csv --jobs 8 --delay 1 \
    --start-date 2000-01-01 --end-date 2026-12-31 --download \
    --download-dir /mnt0/civicclerk_output

Output tree /mnt0/civicclerk_output/{ST}/{Place}/{Board}/{Year}/, records CSVs in runs/<client>.csv.

2. Findings

2.1 Zero-file meetings — 11,553, and nothing recorded them

The log carries lines like 2022-09-15 | City Council (#26) - 0 file(s). A meeting with no documents was not a match, not a failure, and not written anywhere — it existed only as a line in a multiplexed stdout log. The run summary (1518 file(s) matched; downloaded 1518, cached 0, failed 0) cannot express it.

Counted from the log:

By year, past only: 2016 302 · 2017 550 · 2018 406 · 2019 708 · 2020 845 · 2021 885 · 2022 997 · 2023 1,261 · 2024 1,378 · 2025 1,378 · 2026 665, with a thin tail back to 2006.

Three distinct causes are mixed together, and only one is benign:

1. Scheduled placeholders. Cities create a year of calendar entries in advance. Watauga's monthly City Council pairs run empty to 2026-05-25 while 2026-06-22 has agendas. The 1,464 future-dated ones are all of this kind — and they were in scope only because --end-date 2026-12-31 was five months past the run date.
2. Genuinely never published. Wimberley's third-Thursday council series (#24, #25, #26) goes empty while the first-Thursday series keeps publishing. A real property of that body, not a scrape failure.
3. The one that matters: documents the API did not report but the portal shows. This is unverified. It is exactly the failure that cost 60,781 meetings on the Legistar side, where a status field's silence was read as absence.

--section archived is a Legistar flag; CivicClerk's scraper has no equivalent. Bounding --end-date to the run date is what excludes placeholders.

2.2 The log could not be demultiplexed

With --jobs 8, eight workers write one stdout. Lines naming a file are attributable by their path; the 0 file(s) lines named no client at all. Wimberley file paths appear inside Watauga's section in the same log. So 31 hours of work produced 74,177 documents and no way to attribute any gap to a city — not by cleverness at analysis time, because the information was never written.

2.3 Duplicate place directories

Baycity/Bay_City and Bigspring/Big_Spring — an earlier run was killed, place names corrected, and the state re-run. Verified before deleting:

TX/Baycity:  762 files   TX/Bay_City:  1,123    in OLD not NEW: 0
TX/Bigspring: 308 files  TX/Big_Spring:  343    in OLD not NEW: 0

New runs were strict supersets, so the old trees were removable. Worth noting the general practice: a killed run can leave a file the re-run never reached, so the set-difference is what proves deletion is safe.

2.4 GUID-named documents — a false alarm

Bay City logs MEET-Agenda-f68bb121… where other cities log human titles. The GUID is the meeting's, not the file's — an Agenda and Minutes for one meeting share it. Checked on disk: all %PDF, sizes scattering 30 KB–410 KB, which is the signature of uploaded documents rather than generated placeholders. The name never reaches the tree, since on-disk filenames are built from date/board/type/file-id. Nothing to fix.

2.5 "General" board — 6,613 files, 106 tenants, the real defect

General was the most common board directory in Texas (93 places). It is not a body: CivicClerk defaults an uncategorized meeting's categoryName to "General", and 106 tenants leave it there.

Friendswood shows the damage — four bodies in one directory:

Friendswood | 2018-01-08 | Agenda  | City Council 01-08-2018 Regular Agenda
Friendswood | 2018-01-08 | Agenda  | P&Z 01-08-2018 Agenda
Friendswood | 2018-01-09 | Agenda  | FDEDC 01-09-2018 Agenda
Friendswood | 2018-01-09 | Agenda  | Senior Citizen 01-09-2018 Agenda

Diagnosis, in order:

• board == category for all 6,613 rows — the records CSV offers no alternative field.
• Filename-prefix extraction matches only 1,292 of 6,613 (20%), and conventions differ per city (Royse City puts the date first). A regex approach would mis-attribute more than it fixed.
• The API probe settled it. A raw event dump showed eventName carrying the body — "City Council Meeting", "Investment Committee Meeting" — while categoryName is the literal "General".

Root cause, one line:

board = cat or ev_name or "Board"      # "General" is truthy, so ev_name never ran

A generic value is not a value. Test for meaning, not for presence.

2.6 event_id was never written

_record() passed event_id="" on every row. The key tying a document to its meeting is blank in every CivicClerk records CSV produced to date, including the completed Texas run.

2.7 Flagged, not resolved

Notice_of_Possible_Quorum appears as a board directory. It is a Texas posting category, not a deliberative body — the same class as Legistar's Miscellaneous Agendas and Public Notice buckets. Decide before item identifiers are minted: keep with a note, or map to the posting body.

3. Code changes

v4 — attribution and pending records

• PendingWriter (subclasses RecordsWriter, same atomic partial-then-promote): writes runs/<client>_pending.csv, one row per zero-file meeting, with a classified reason — scheduled for future dates, no_files_reported for past ones. This is the CivicClerk equivalent of Legistar's _pending.csv, and without it the gap is uncountable.
• ClientLog — every per-meeting line prefixed [<client>], so a combined log demultiplexes with grep.
• --client-log-dir DIR — optional per-client log files alongside the combined stream.

v5 — the body name

• body_from_event(category, event_name) → (board, source). A category is used only if it names something; GENERIC_CATEGORIES covers general, uncategorized, default, other, misc, n/a and blank, so this is not a Texas-specific patch. Falls back to eventName.
• Trailing "Meeting" trimmed so City Council Meeting and City Council — both present in one tenant — are one body. _MEETING_KIND protects Special Meeting, Regular Meeting and similar from becoming Special.
• board_source column (category | event_name) in the records CSV, so a derived body name is visible and reversible rather than silently overwriting.
• event_id now actually written.

Files: civicclerk_api_scrape-2026-08-05-v5.py, civicclerk_v3_to_v5.diff (270 lines against v3).

civicclerk_regeneral.py — remediating the 6,613 already on disk

Three phases, each stopping for review; deletion is never automatic.

python3 civicclerk_regeneral.py --output-dir /mnt0/civicclerk_output plan
python3 civicclerk_regeneral.py --output-dir /mnt0/civicclerk_output rerun \
    --scraper ~/Downloads/civicclerk_api_scrape-2026-08-05-v5.py \
    --portals TX_civicclerk_remaining.csv --jobs 8 --delay 1 --apply
python3 civicclerk_regeneral.py --output-dir /mnt0/civicclerk_output verify

plan finds affected clients from the records CSVs. rerun slices the portals CSV to just those clients and re-runs them — the destination path changes (General → City_Council), so skip-if-exists cannot match the old copy and the files are re-fetched into the right place. verify set-differences each General/ tree against the rest of that city's tree and lists a directory as safe only when every filename appears elsewhere; it writes the delete list but never deletes.

Expect some directories to remain unsafe: meetings where eventName was empty too have nowhere else to go and legitimately stay General.

4. Open items

1. Regenerate the Texas inventory. A metadata-only pass (no --download) with v5 writes the pending CSVs for all 119 clients in minutes rather than 31 hours, and bounds the window to the run date:

   python3 civicclerk_api_scrape-2026-08-05-v5.py \
       --portals TX_civicclerk_remaining.csv --jobs 8 --delay 1 \
       --start-date 2000-01-01 --end-date $(date +%F) \
       --records-dir /mnt0/civicclerk_output/runs \
       --client-log-dir /mnt0/civicclerk_output/logs/clients

2. Answer the index-or-source question. Sample ten no_files_reported rows across different cities and open the portal pages by hand. Documents present means CivicClerk's API is an index like Legistar's and 10,089 meetings need a recovery path. This decides whether the Texas corpus is complete or 12% short.

3. Run the General remediation (§3) — 6,613 files across 106 tenants.

4. Decide on Notice_of_Possible_Quorum and any similar posting categories.

5. Investigate the single failed 1. One failure across 32 hours is findable now and impossible later.

6. Note that existing records CSVs lack event_id. Any join built on the Texas run's current CSVs has no meeting key until they are regenerated.

5. Carried over from the Legistar effort

Three patterns that recurred and are worth applying to CivicClerk directly:

• A field being populated is not the same as its contents being usable. On Legistar this appeared four separate times (bare filenames that broke the downloader, the pending path, a CSV validator, and provenance selection). On CivicClerk it is "General" — populated, truthy, and meaningless.
• "We did not look" must never be recorded as "it is not there." Every guard that stops trying needs its own reason code, or the gap silently becomes a coverage statistic.
• Count the thing itself. downloaded/kept conflates fetches with skips; grep -c '^\[saved\]' does not. Order-of-magnitude wrong conclusions came from the former.

The Legistar runbook's §13 is a symptom-to-cause diagnostic playbook built from these; most of it transfers with the vendor name changed.

↑ back to contents31.1-civicclerk_TX_diagnostics_and_code_changes/CIVICCLERK_TX_HANDOFF.md

LEGISTAR SCRAPER -- BUILD #1 SNAPSHOT
=====================================
Packaged: 2026-08-05

READ THIS FIRST
---------------
This archive is a point-in-time snapshot of the first working build of the
Legistar civic-meeting scraper, packaged for review. Development continued
after this was cut: a later working session may have productionalized or
superseded what is here. If you have received more than one archive, or have
access to the project's later conversations, CHECK FOR A NEWER VERSION before
building on this one. The findings log (docs/legistar-notes.md) is the
authoritative record of what was known at packaging time.

WHAT THIS PROJECT IS
--------------------
The third scraper in a family (Granicus, CivicClerk, Legistar) that collects
municipal meeting agendas and minutes into a common archive layout
(State/Place/Board/Year/DocType) with a shared record schema. This build
covers the 215 US municipalities running Legistar, of which 170 are live,
scrapeable clients. It scrapes the Legistar web API for enumeration and
falls back to the human-facing InSite meeting pages for the roughly one
tenth of documents the API links to but does not report.

CONTENTS
--------
MANIFEST.txt                          this file

code/
  legistar_api_scrape.py              the scraper (~4,000 lines). All modes in
                                      one file: scrape, download, preflight,
                                      field census, InSite survey, InSite
                                      document recovery, pending-document
                                      audit. Run with --help for usage; the
                                      design doc explains the architecture.
  test_legistar_offline.py            339 offline tests (no network needed):
                                      python3 -m unittest test_legistar_offline
  legistar_recovery_audit.py          joins a run's pending CSVs against its
                                      records CSVs to report how many
                                      API-missing documents were recovered
                                      from InSite, and what is still gone.
  legistar_action_summary_clients.py  filters an --insite-survey result down
                                      to the clients whose boards publish
                                      "Action Summary" documents, for targeted
                                      collection.

docs/  (each document in three identical-content formats: .md source,
        .docx for commenting in Word, .pdf for reading)
  legistar-executive-summary.*        ONE PAGE. Start here: status, scope,
                                      planned-vs-actual effort, the five
                                      unexpected issues, lessons, and the
                                      decisions still open.
  legistar-scraper-design.*           the original design/plan written before
                                      implementation. Kept as-written; the
                                      notes record where reality disagreed.
  legistar-notes.*                    the findings log, v1-v26: every live
                                      run, every anomaly, every bug (mine and
                                      Legistar's), in chronological order.
                                      The project's institutional memory.

SUGGESTED READING ORDER (non-technical reviewers)
-------------------------------------------------
1. legistar-executive-summary.pdf    (3 pages)
2. legistar-scraper-design.pdf       (9 pages, skim)
3. legistar-notes.pdf                (35 pages, reference -- skim the section
                                      headers, which are dated and titled by
                                      finding)

THE ONE-SENTENCE FINDING
------------------------
The Legistar API is a reliable index and an unreliable inventory: it
eliminated the structural guesswork of HTML scraping, but silently omits
documents that exist, so this build uses the API to decide what to fetch and
the human-facing meeting pages to actually fetch it.

NOT INCLUDED
------------
No scraped documents, no run outputs, no target lists with per-client tokens.
The scraper produces those; this archive is the tool and its paper trail.

↑ back to contents32.0-legistar-scraper-build1/MANIFEST.txt

legistar_client_runs

Tooling and documentation for collecting US local-government meeting agendas and minutes from Legistar, one of ten agenda-management vendors.

Packaged 2026-08-02. Scraper version 2026-08-02.6, pipeline driver 2026-07-30.1.

What this produced

418,871 documents from 170 jurisdictions across 33 states, spanning 2000–2026. Every file traces to a source URL, event id, publishing body, meeting date and document type — reconciliation is clean in both directions, with zero unexplained files and 1,918 records (0.48%) whose document could not be retrieved, each classified rather than reported as generic loss.

The documents themselves are not in this archive. This is the method: the scrapers, the verification tooling, and the record of why each decision was made.

Start here

If you have been handed a corpus to verify or upload and did not do the collection, start at runbook §12. It is self-contained: it explains the artifacts before asking you to act on them, and every check states what a good result looks like.

docs/

Each document is provided in three formats — .md (source), .docx (Word, for review and comment) and .pdf (for reading and printing). The .md files are authoritative; the other two are generated from them, so edit the markdown and regenerate rather than editing a Word file that will be overwritten.

cd docs
for f in *.md; do pandoc "$f" -o "${f%.md}.docx" --from=gfm --toc --standalone; done
soffice --headless --convert-to pdf --outdir . *.docx

• EXECUTIVE_SUMMARY.md — scope, effort estimated versus actual, the six categories of problem we did not anticipate, and the lessons that generalise to other vendors.
• IA_DECISION_BRIEF.md — eight decisions pending with the Internet Archive: item granularity, identifier scheme, delivery model, document scope, and who owns ongoing refresh. Each section ends in a question.
• LEGISTAR_RUNBOOK.md — operator procedure. Collection stages, the per-state pipeline, document format policy, the weekly refresh job, and (§12) reconciliation, remediation and upload preparation.
• legistar-notes.md — the engineering log. Every non-obvious decision with the evidence that forced it, in version order. The most transferable artifact here: it records which failure modes are real rather than theoretical.

code/

Collection

• legistar_api_scrape.py — the scraper. Preflight, survey, download, InSite recovery, per-tenant host repair, content sniffing. --version reports what it supports; check it before any long run.
• legistar_run_state.sh — pipeline driver. Walks one state through slice → preflight → survey → plan → download → audit, with pinned filenames, gates and a status ledger. -n for a dry run.
• legistar_weekly.sh — cron-schedulable refresh across every state, with audit gates and a count of what arrived.
• legistar_recovery_clients.py — splits a state's clients into the batch that needs InSite recovery and the batch that does not.

Verification

• legistar_reconcile.py — the tree against the records CSVs, both directions. Files with no provenance, and records with no file, with a per-client host census that classifies why.
• legistar_recovery_audit.py — recovery yield per pending reason, each against its own denominator. --min-pct for the weekly gate.
• legistar_minutes_gap_count.py — sizes the missing-minutes gap by body, separating bodies that publish minutes sometimes from those that never do.
• legistar_packet_scan.py — finds agenda packets misfiled as agendas, by size relative to each tenant's own norm, confirmed by page count.

Repair

• legistar_refetch.py — re-fetches documents listed in missing.csv using the scraper's host-repair ladder, for records the API no longer returns.
• legistar_unfile.py — moves documents written under a placeholder state back to the client tree they belong to.

Upload

• legistar_ia_manifest.py — builds an ia CLI upload spreadsheet from the download tree joined to the records CSVs. Configurable granularity and identifier prefix; emits neutral column names for late field mapping.

legistar_api_scrape_v34.diff is the cumulative diff of the scraper against the version that began this round of work, if you want to see what changed rather than what it does.

The one thing worth knowing before you read the code

The Legistar API is an index, not a source. It reports what it stored when a meeting was published; the meeting's own page is a superset — more document classes, current links, and the truth about what exists today. The architecture is API-to-decide-which-pages, page-to-fetch-content, and most of the complexity in this codebase exists because that was learned late rather than assumed early.

For the next vendor, the ten-minute test: fetch one record from the API and its human-facing page, and diff the document sets. If they differ, design for an index from day one.

↑ back to contents32.1-legistar_client_runs(2)/legistar_client_runs/README.md

Volunteers & Contribution — "How can I help?"

The metadata-enhancement roadmap (metadata-enhancements.md) has a large amount of work that is better done by people than by machines — and specifically by people with local knowledge, subject expertise, or archival training. This document maps volunteer contributors to that work: who can help, what they'd do, how they're supported and supervised, and answers to the questions a prospective volunteer asks.

The core insight: enrichment projects like transcription-correction, place mapping, speaker identification, and topic tagging are exactly the kind of work that scales through motivated volunteers — if the coordination and quality-control infrastructure exists to make their contributions trustworthy. This document is about building that infrastructure, not just recruiting.

1. Who can help, and what they're uniquely good at

Different contributors bring different strengths. The work is deliberately sliced so each profile has a natural, valuable lane.

The last row deserves emphasis: local knowledge is the one input that cannot be automated or outsourced. A retired city clerk who can identify every council member by voice across fifteen years of meetings is providing data that no ASR or NER pipeline can produce. Designing roles that capture that knowledge is the highest-value part of a volunteer program for this corpus.

2. What volunteers actually do — tasks mapped to the roadmap

Each task ties to a project in metadata-enhancements.md. Tasks are sized so a newcomer can do a useful unit in one sitting.

Caption & transcript correction (Project C)

The harvest tiers (on-disk YouTube captions, pre-2019 IA captions) produce usable but imperfect text; ASR on the gaps produces more. Volunteers review and correct segments — fixing misheard words, speaker turns, and punctuation. This is the largest pool of volunteer work and the most accessible: anyone literate can improve a transcript against the audio.

• Layperson / editor / senior. Editors raise consistency; seniors catch local names ("that's Commissioner Yoakum, not 'commissioner yokum'") that others can't.

Place & jurisdiction mapping (Project B)

The dirname→place map (lvnwks → Leavenworth, KS) is mostly mechanical but has exceptions and ambiguities a human resolves quickly. Volunteers confirm/complete the locality, county, and coordinates per collection.

• Community-media pro / senior / student. Locals disambiguate same-named towns; students apply geographic authority control.

Speaker & entity identification (Project E)

After NER proposes people/orgs, volunteers confirm, correct, and link them — especially recurring speakers across meetings. This is where local knowledge is decisive.

• Senior / community-media pro. The "name that voice / face" work is uniquely human and uniquely valuable.

Topic & subject tagging (Projects A/E)

Apply a controlled vocabulary of civic topics (zoning, budget, public safety, …) to items or transcript segments, so topical search and prevalence analysis work.

• Editor / student / layperson. Students design/steward the vocabulary; others apply it.

Coverage-gap flagging (Sub-project C-gap; Project G)

Volunteers spot and report gaps: a collection missing recent meetings, an item whose caption is garbage, a video that's gone dark. This mirrors the operational missing-video work, done by humans who watch the content.

• Anyone. A great first task — low skill floor, immediately useful.

Description & context writing (Project A)

Write or improve item/collection descriptions — what a meeting covered, why a collection matters. Turns thin auto-metadata into human-useful context.

• Editor / community-media pro / senior.

Metadata standards & authority control (cross-cutting)

Design the controlled vocabularies, name-authority files, and quality rubrics the other tasks depend on. This is the librarian layer that makes volunteer output consistent and trustworthy.

• MARA / MLIS students and professionals. This is their discipline; it's also the supervisory backbone (see §4).

3. Contribution levels (a ladder, not a wall)

Volunteers should be able to start trivially and grow into responsibility. A tiered ladder also is the quality-control structure (higher tiers review lower tiers):

1. Contributor — do discrete micro-tasks (correct a transcript, confirm a place, tag a topic). No commitment, no training beyond a short guide. Work is queued and reviewed before it lands.
2. Reviewer — trusted contributors who check others' work and approve it. Earned by a track record of accepted contributions. This tier is what lets the program scale without every edit hitting a paid supervisor.
3. Domain steward — owns a slice (a region, a collection group, the topic vocabulary, the speaker-authority file). Coordinates contributors, resolves disputes, maintains standards in their area. Often MARA/MLIS students/pros or deeply-engaged locals.
4. Program coordinator — the paid/lead role(s); see §4.

Progression is earned through accepted work, not tenure — which keeps quality tied to demonstrated reliability.

4. Management & supervisory infrastructure

Volunteer enrichment fails without coordination and quality control. The insight: the same "claimed vs. verified" discipline the archiving pipeline uses for videos applies to human contributions. A volunteer edit is a claim; a reviewer's approval is verification; unreviewed edits don't land in the authoritative dataset. That mental model — already proven in this project — is the backbone.

The people

• Program coordinator(s) — paid or lead volunteers who recruit, onboard, set priorities, and handle escalations. Realistically 1 coordinator per ~30–50 active contributors. This is the role that most needs to be resourced; everything else can be volunteer-run if a coordinator holds it together.
• Domain stewards (tier 3 above) — the distributed supervisory layer; each owns a region/collection-group/vocabulary and reviews or delegates review.
• A metadata/archival advisor — a MARA/MLIS professional (or faculty partner) who owns standards, vocabularies, and authority control. Part-time but essential: they keep volunteer output aligned with real archival practice, and they make the program a credential-worthy experience for students (see §5, practicum angle).

The systems (what has to exist)

• A task queue / work-assignment system — hands out micro-tasks, tracks who did what, prevents duplicate work. This is the single most important piece of infrastructure; without it, volunteers don't know what to do and effort collides.
• A review/approval workflow — every contribution enters as pending, gets reviewed (by a tier-2+ reviewer), and only then merges into the authoritative metadata. Mirrors the collector's claimed→verified model exactly.
• Provenance on every edit — who changed what, when, from which source, reviewed by whom. The metadata DB's soft-delete/history discipline extends naturally to human edits; nothing is ever silently overwritten.
• Contributor guides & rubrics — short, task-specific "how to correct a transcript," "how to tag a topic," maintained by the stewards/advisor.
• Recognition — visible credit, contribution stats, and (for students) a documentable record for their program. Recognition is the volunteer program's currency; budget for it.

Quality control model

• Redundancy for high-stakes fields — e.g. two independent confirmations before a speaker identification is authoritative; consensus resolves disagreement.
• Sampling — stewards spot-check accepted work to keep reviewers calibrated.
• Reputation — contributors whose work is consistently accepted earn lighter review; new contributors get more. This concentrates supervisory attention where it's needed and rewards reliability.
• Nothing lands unreviewed — the authoritative dataset only ever ingests verified contributions; raw edits live in a staging layer. (Same architecture as keeping archive.db authoritative and collector.db claims pending until reconciled.)

5. FAQ

Q: I'm not technical at all. Can I still help? Yes — most of the work is not technical. Correcting a transcript against the audio, confirming which town a collection belongs to, identifying a speaker you recognize, or tagging what a meeting was about needs care and knowledge, not coding. The task queue hands you a small, clear unit of work with a short guide.

Q: How much time do I need to commit? None ongoing. The work is designed as micro-tasks — do one transcript segment or one tagging task in a few minutes, or spend an afternoon. Contribute once or become a steward; both are welcome.

Q: I have deep knowledge of my town's history/government. Where's that most useful? Speaker and place identification, and historical context — the things no algorithm can know. Recognizing officials by voice across years of meetings, disambiguating local places and events, and explaining "why this meeting mattered" is the single most valuable contribution for this corpus, and it's irreplaceable.

Q: I'm a MARA/MLIS student — is there work at my level, and does it count? Yes, and this is designed as a practicum-quality experience: controlled-vocabulary design, name-authority control, provenance modeling, and supervising contributor quality are core archival practice on a real 3.2M-item corpus. With a faculty/advisor partnership it can be structured as documentable, credential-relevant work.

Q: How do I know my corrections actually matter / won't be lost? Every edit is attributed, reviewed, and versioned — nothing is silently overwritten, and you can see your accepted contributions. Reviewed work merges into the authoritative archive that researchers and the public use.

Q: What if I make a mistake? That's what the review layer is for — contributions are checked before they land, so mistakes are caught, not shipped. New contributors get more review; there's no penalty for honest errors.

Q: Can my community-media station / historical society / class participate as a group? Yes — a group can adopt a collection or region as domain stewards. Group participation with a local coordinator is one of the most effective patterns, because it concentrates local knowledge where it's most relevant.

Q: Who decides the standards (vocabularies, how to tag, name spellings)? The metadata advisor and domain stewards, following established archival practice. Standards are documented in the contributor guides so everyone works consistently; proposed changes go through the stewards.

Q: Is my work going to train AI / be sold? [A policy question for the program to answer explicitly and honestly up front — volunteers deserve a clear, truthful statement of how their contributions are used and licensed. State it plainly in the volunteer agreement.]

6. Why this fits this project specifically

• The work is already sliced. The enhancement roadmap's tasks (caption correction, place mapping, speaker ID, tagging, gap-flagging) are naturally human-sized and queueable — the volunteer program is mostly coordinating work the roadmap already defines.
• The quality model already exists. "Claimed vs. verified," soft-delete history, provenance-on-everything, coverage-gap worklists — the disciplines this project built for videos transfer directly to human contributions. The supervisory infrastructure is the same architecture applied to people.
• Local knowledge is the corpus's missing input. Civic media is inherently local; the people who understand it are distributed across exactly the communities the archive covers. A volunteer program isn't just cheaper labor — it's the only source of the local-knowledge metadata that makes the corpus truly navigable.
• It creates a virtuous loop with the reporting work. Channel-owner reports (report-mockups.md) put the archive in front of the very communities whose members are the ideal volunteers — the report that shows a town "here's your preserved public record" is also the natural on-ramp to "…and here's how you can help make it better."

This is a planning document for a future volunteer program, not an operational guide. It assumes the metadata-enhancement roadmap is underway and focuses on the human-coordination infrastructure that enrichment at scale requires.

↑ back to contents10.2-archive-dashboard-project(71)/archive-dashboard-project/docs/volunteer-contribution.md

Volunteer Contribution Platform — Design Notes

Status: design/scoping, not yet built. This document works through the infrastructure the volunteer program (volunteer-contribution.md) needs: how volunteers see and edit data, how edits round-trip safely into the canonical databases, whether SQLite still suffices, and how tasks, review, reputation, and gamification fit together. It builds on the three-database ecosystem (metadata-db-integration.md) and reuses its "claimed → verified" discipline.

The core realization: one new multi-writer store, not four converted ones

The instinct to consider Postgres comes from a real worry — many volunteers writing at once will make SQLite's single-writer lock a bottleneck. But that worry applies to the wrong databases. The three canonical stores (collector.db, archive.db, civic.db) are single-writer by design, each written by one pipeline on a cadence, and that is a feature, not a limitation: it is what makes them safe to rebuild, promote, and federate read-only.

Volunteers must never write the canonical databases directly. The moment they did, we'd lose the single-writer guarantee, the atomic build-then-promote, and the clean federation. So the multi-writer requirement doesn't belong to the canonical DBs at all — it belongs to a new, separate store that holds volunteer activity:

   VOLUNTEERS (many, concurrent)
        │  submit edits, claim tasks
        ▼
  +-------------------------------------------------+
  |  contrib store   (the ONLY multi-writer store)  |
  |  tasks · submissions · reviews · reputation     |
  +-------------------------------------------------+
        │ reads canonical DBs read-only (to build tasks + show context)
        │ writes back ONLY accepted edits, via a reviewed merge job
        ▼
  +------------+   +------------+   +------------+
  | collector  |   |  archive   |   |   civic    |   ← still single-writer,
  |    .db     |   |    .db     |   |    .db     |     still federated read-only
  +------------+   +------------+   +------------+

This keeps everything the project already relies on and localizes the concurrency problem to one component we can choose the right technology for.

Does SQLite still work? Mostly yes — with one caveat

• Canonical DBs: keep SQLite. Nothing changes. Single writer, WAL for concurrent readers, Datasette for serving. The volume and access pattern that made SQLite the right call still hold.
• Contrib store: SQLite in WAL is fine to start; Postgres if/when write volume demands it. SQLite in WAL mode allows many concurrent readers and one writer at a time; writes are serialized but each volunteer edit is tiny (milliseconds), so a single writer can absorb a surprising rate — realistically hundreds of submissions/minute, far beyond early program scale. The honest threshold: if sustained concurrent writes ever cause lock timeouts, that is the signal to move the contrib store (and only that store) to Postgres. The schema is designed to port cleanly, so this is a swap, not a redesign.

The decision rule, stated plainly: don't migrate to Postgres preemptively. Build the contrib store on SQLite/WAL, measure, and move just that one store to Postgres only if real write contention appears. The canonical three never move.

How edits round-trip (the "claimed → verified → merge" reuse)

The project already has the exact pattern this needs, proven on videos:

1. A volunteer edit is a claim. It's written to the contrib store as a submission (status submitted), never to the canonical DB. It records the target (which DB, table, row, field), the proposed value, the volunteer, and a timestamp.
2. A reviewer verifies it. A trusted volunteer (see the ladder in volunteer-contribution.md §3) approves or rejects; status becomes accepted or rejected. High-stakes fields can require two independent approvals.
3. A merge job applies accepted edits. On a cadence, a job reads all accepted submissions for a canonical DB and applies them — exactly like the existing sync/promote flow: build a new copy, apply the accepted edits with full provenance, integrity-check, atomically promote. The canonical DB thus still has exactly one writer (the merge job), preserving every guarantee.

Provenance travels with every applied edit (who proposed, who reviewed, when, from which submission), so nothing is silently overwritten and any edit can be traced or rolled back — the same soft-delete/history discipline already in the schema.

How volunteers access the data — three options, phased

Option A — CSV round-trip (today's approach). Export a slice ("give me 200 unconfirmed governments in Nebraska"), volunteers edit in a spreadsheet, re-import as submissions. Pros: zero new services, familiar tools. Cons: manual, no live validation, no concurrency awareness, easy to fumble a re-import. Verdict: fine for a handful of expert volunteers right now; it does not scale to an open program, but it's a valid Phase 0 that needs only small export/import helpers.

Option B — Mutable Datasette over the contrib store. Datasette already serves these databases read-only; a writable Datasette instance (canned write queries / the write API) pointed at the contrib store gives volunteers a web UI with no local database access, per-row editing, and built-in query views. Pros: reuses tech already in the stack, quick to stand up, good for the civic catalog's tabular editing. Cons: Datasette is read-optimized; it's a thin editing surface, not a task-queue/review system, so it covers access but not workflow. Verdict: a strong Phase 1 for the civic-catalog track specifically — tabular, low-volume, benefits most from a simple grid UI.

Option C — A purpose-built contribution web app + API. A lightweight web application whose API owns all writes to the contrib store, presents micro-tasks, captures submissions, runs the review queue, and tracks reputation. Pros: the real answer — it's where task distribution, review, reputation, and gamification live; it can present the right micro-task UI per track (a transcript editor, a "confirm this government" card, a "is this the right channel?" yes/no). Cons: the most build effort. Verdict: the Phase 2 destination; Options A and B are stepping stones that don't block it.

Phasing: A now (expert volunteers, CSV), B for the civic catalog (writable Datasette, quick win), C as the program opens up (the full platform).

The task-distribution, review, reputation & gamification system

All of this lives in the contrib store and (in Phase 2) the contribution app.

Task queue

• Task generation. A job reads the canonical DBs read-only and materializes tasks — e.g. "confirm government X" (civic rows with unconfirmed), "find the agendas page for Y" (governments with no portal URL), "correct segment Z" (items flagged needs_review), "is channel C official for station S?" A task names its track, its target row, and what "done" means.
• Leasing, not assigning. A volunteer requests a task; the queue hands out the next appropriate one and leases it (a short hold with a timeout) so two people don't do the same unit. If the lease expires unfinished, the task returns to the pool. This prevents duplicate work without locking anyone out.
• Right-sized & routed. Tasks are filtered by track and by the volunteer's tier (newcomers get well-defined, low-stakes tasks; trusted volunteers can lease review tasks). Locality-aware routing — offering a volunteer governments/channels in their own region — makes the local-knowledge work land where it's strongest.

Review workflow (states)

  submitted ──▶ in_review ──▶ accepted ──▶ (merged into canonical DB)
       │
       └──────────▶ rejected (with a reason; volunteer can learn/redo)

• Every submission enters submitted. A tier-2+ reviewer moves it to accepted or rejected (with a short reason). Only accepted submissions are eligible for the merge job. High-stakes fields (e.g. a speaker identification, a government's canonical name) can require two independent accepted votes; consensus resolves disagreement.
• Sampling. Stewards spot-check a fraction of already-accepted work to keep reviewers calibrated — quality control on the quality-controllers.

Reputation (climbing the ladder)

• Each volunteer has a reputation score driven by accepted contributions (not raw submissions — quality, not volume). Rejections don't punish beyond not earning; honest mistakes carry no penalty.
• Reputation gates the ladder from volunteer-contribution.md §3: enough accepted work in a track promotes a Contributor to Reviewer (can approve others' work), and sustained stewardship to Domain steward. This is how the program scales its own supervision — trusted volunteers review newcomers, so not every edit hits a paid coordinator.
• Trust reduces friction. Higher reputation earns lighter review (e.g. sampled rather than full review), concentrating scarce reviewer attention on newcomers and high-stakes fields.

Gamification (motivation, tied to quality)

Recognition is the volunteer program's currency; a few mechanics, all keyed to accepted work so they reward real contribution, not gaming:

• Leaderboards — per track and per region ("top contributors in Kansas this month"), plus all-time. Regional boards tap local pride and keep them human-scaled rather than one intimidating global list.
• Streaks & milestones — consecutive active weeks; badges at 10/100/1000 accepted contributions; a "first accepted edit" welcome badge.
• Track-specific badges — "Portal Historian" (traced N vendor migrations in Track 1), "Channel Scout" (onboarded N channels in Track 2), "Transcript Editor" (N corrected segments in Track 3), "Local Historian" (N speaker/place identifications). Badges map to the tracks so people see progress in the work they chose.
• Visible impact — "your confirmed governments are now searchable by 1,200 researchers," "the meeting you transcribed has been viewed 340 times." Impact beats points for sustained motivation on civic work.

A caution: gamify accepted, reviewed work only. Rewarding raw submission volume invites low-quality spam; rewarding accepted work aligns the incentive with the mission.

A sketch of the contrib store schema (illustrative)

Not final — just to show the shape. One store, multi-writer, portable to Postgres.

• task — id, track, target_db, target_table, target_key, kind, prompt, state (open/leased/done), lease_owner, lease_expires.
• submission — id, task_id, volunteer_id, target (db/table/key/field), proposed value, status (submitted/in_review/accepted/rejected), created_at.
• review — id, submission_id, reviewer_id, decision, reason, created_at.
• volunteer — id, display_name, region, tier, reputation, joined_at.
• badge / volunteer_badge — the gamification layer.
• merge_log — which accepted submissions were applied to which canonical DB when (provenance + rollback).

The canonical DBs are attached read-only for task generation and context; they are written only by their own merge job.

Open questions

1. Contrib store engine: start on SQLite/WAL (recommended) and port to Postgres only if measured write contention demands — or start on Postgres now to avoid a later migration? (Trade-off: operational simplicity now vs. a possible swap later. The schema is written to port cleanly either way.)
2. Identity & auth: how do volunteers sign in (email magic-link, OAuth via a civic/education identity, etc.), and what's the minimum PII we store?
3. Datasette-write vs. custom app for Phase 1 civic editing: is writable Datasette enough for the civic catalog to start, or do we jump to the custom app sooner?
4. Merge cadence: how often does the merge job fold accepted edits into each canonical DB — nightly, weekly (aligned to the existing sync), on a threshold?
5. Conflict handling: two accepted edits touching the same field — last-write, steward-resolves, or task-level locking to prevent it upstream?
6. The "use of contributions" policy (volunteer-contribution.md FAQ) — the licensing/AI-training answer should be settled before the platform opens.