A registry of local news outlets for the Local News Impact Consortium: a curated database, an admin interface for editing it, and an embeddable public directory for localnewsimpact.org.
It succeeds the mwe400/LocalNewsDatabase
Streamlit prototype — 2,103 outlets and 8,561 coverage records. Every feature
of that prototype is preserved; see MIGRATION.md for the parity
inventory and the data problems that have to be fixed on the way.
The Source Directory is one of four repositories that share a database
instance, a contracts package and one set of rules about how a change
reaches main.
| Repository | What it is | What it publishes |
|---|---|---|
MizzouNewsCrawler |
discovery, extraction, cleaning, classification and enrichment; GKE + Argo Workflows | rows in the crawler database; analytics tables in BigQuery |
datadesk |
the newsroom console and review queue; Django on Cloud Run | decisions written back to the crawler database; published visuals |
NewsSourceDirectory |
the outlet directory and its review queue; Django on Cloud Run at sources.localnewsimpact.org |
a hashed static feed on gh-pages, read by the WordPress plugin |
lnic-contracts |
the shapes one service writes and another reads, the shared CI, the coverage floor | a Python package and two tag series |
Two layers, deliberately split.
One organization ruleset, Main is reached by pull request, targets ~ALL repositories' default branch:
| Rule | Effect |
|---|---|
pull_request |
a change reaches main through a pull request; one approving review, code-owner review where a CODEOWNERS file matches |
non_fast_forward |
no force-push over main |
deletion |
main cannot be deleted |
Each repository's own ruleset carries required_status_checks and nothing else. That rule cannot move up to the organization: the contexts differ per repository — checks / integration exists only in the crawler, Data quality, Public feed, Pages payload and Image builds only in the Source Directory — and a context named in a ruleset but never reported blocks every pull request permanently.
The rules the suite works to:
- Nothing is pushed to origin except on a branch.
- Every repository has a pre-push hook that runs
make check, so what CI will say is known before it is said. - CI checks the pull request, and green is what allows a merge.
- An administrator may merge without a code review.
- Nobody pushes to main, administrators included.
Four and five look contradictory. The ruleset's bypass list resolves them, and the mode is what does it: OrganizationAdmin bypasses in pull_request mode, which permits an override while merging a pull request and none at all for a direct push.
| Bypass mode | Direct push to main | Merge against the rules |
|---|---|---|
always |
allowed | allowed |
pull_request (in use) |
refused | allowed, with --admin |
So an administrator merges with gh pr merge <n> --squash --admin, and a merge without --admin waits for a review. The GraphQL field viewerCanMergeAsAdmin reports false under this configuration and the --admin merge succeeds anyway; it describes the legacy branch-protection override, not a ruleset bypass, and is not the field to read.
delete_branch_on_merge is on in every repository.
| Layer | Catches | Where it lives |
|---|---|---|
| pre-push hook | a red commit, before it leaves the machine | scripts/setup-hooks.sh, one per repository, running that repository's make check |
| shared CI | a red pull request | lnic-contracts/.github/workflows/python-checks.yml@ci-v1 — lint, typecheck, test, integration, with a Postgres service |
conforms.yml |
a repository drifting from the pattern | lnic-contracts, called alongside the checks |
| the ruleset | a merge that skipped either | GitHub, organization and repository level |
conforms.yml fails a repository that stops calling the shared workflow, loses a make target the workflow runs, drops its pre-push hook, lets that hook run the whole suite for a branch deletion, leaves CI's push trigger unscoped so every pull request push runs twice, sets a coverage floor of its own, or stops running the suite's floor from make test.
Every stage is a make target — make lint, make test — never a bare ruff or pytest. The commands live in each repository's Makefile, which is what a person runs locally, so CI and a local run cannot mean different things. What the targets do differs: the crawler runs its tests inside a prebuilt image because its dependencies take minutes to install; the others install them on the runner because they take seconds. Both are make test.
The coverage floor is one number, 80%, in lnic_contracts.coverage_floor, run by every repository's make test and again by the shared workflow. A repository that sets its own is refused.
Data. One Cloud SQL instance serves all three applications. The crawler owns its database; the Source Directory's tables live in a directory schema alongside shared identity tables in public; datadesk has its own database and reaches the crawler's through a read-only role (infra/sql/create_crawler_readonly_role.sql, password in Secret Manager), with a separate read-write connection for the decisions the review queue writes back. Postgres enforces the read-only half; it is not a convention.
Packages. lnic-contracts is installed from a tag tarball, pinned in each consumer's requirements. NewsSourceDirectory is installed into datadesk's base image from a pinned git tag, so the directory front end datadesk serves is a released version rather than whatever main happens to be; tagging a directory release dispatches datadesk's deploy.
Versioning. lnic-contracts carries two tag series, because the cadences differ. vX.Y.Z versions the Python package — the shapes two services must agree on, where a renamed key strands data at runtime with no import error to catch it. ci-vX.Y.Z versions the workflows, and ci-v1 follows the newest of them, so a CI fix reaches all three repositories without a pull request in each. release-ci.yml runs make check before moving the major tag.
Publishing. The crawler exports to BigQuery. The Source Directory publishes a hashed static feed to gh-pages, which the WordPress plugin reads. datadesk publishes visuals.
The directory is the registry of outlets. Its front end is served from
two places: this repository's own Cloud Run service at
sources.localnewsimpact.org, and datadesk, which installs this
package into its base image at a pinned git tag -- so what datadesk
serves is a released version rather than whatever main holds. Tagging
a release here dispatches datadesk's deploy, and tests/test_release.py
refuses a merge at a version that is already tagged, because the merge
is what tags the release.
Required checks here are the widest in the suite: checks / lint,
checks / test, conforms / conforms, and four of this repository's
own -- Data quality, Public feed, Pages payload and Image builds. The feed is a published artefact with an explicit column
allowlist, so its guarantees are checked before a merge rather than
after a publish.
The project is lnic-source-directory, separate from the crawler's.
The directory database is not: it lives on the crawler's Cloud SQL
instance (mizzou-news-crawler:us-central1:mizzou-db-prod-ssd), which
datadesk also uses, so isolation is database-and-user level rather than
instance level. See infra/README.md for why that was
chosen and what it costs.
| CONTRIBUTING.md | Local environment, tests, and the branch-to-deploy workflow |
| docs/pipeline.md | Local tests → CI → deploy → publish: what each gate protects |
| docs/reviewing.md | For reviewers. Working the merge queue, split and merge, publishing |
| docs/runbook.md | Rollback, feed recovery, backup and restore, granting access |
| docs/schema-decisions.md | Why the models are shaped this way, argued from the data |
| docs/auth.md | Google sign-in, the domain restriction, and why not IAP |
| docs/crawler-etl.md | Loading the news crawler's sources into the registry |
| MIGRATION.md | Feature parity with the Streamlit prototype, and the data problems |
| DEVELOPMENT.md | The milestone plan |
| infra/README.md | What exists in GCP and why it costs what it costs |
Workspace account --allauth--> Django admin (Cloud Run) --> Cloud SQL [own project]
(hd + email_verified |
verified server-side) publish workflow |
v
GitHub Pages (gh-pages): manifest.json + hashed payloads
|
WP page <--------------------+------> crawler
[lnic_directory] (one-way, later)
| Layer | Choice | Why |
|---|---|---|
| Database | directory database on the crawler's existing Cloud SQL |
Reuse, not a second instance — saves ~$50/month |
| Admin | Django 5.x + Gunicorn on Cloud Run | Inlines make the merge review tractable — see below |
| Bulk edit | django-import-export |
Reads the source .xlsx/.csv with a dry-run diff before commit |
| Audit | django-simple-history |
Per-field history and revert, essential during remediation |
| ETL | management commands run as Cloud Run Jobs | Same image, different entrypoint; no request timeout |
| Auth | django-allauth, Google sign-in restricted to the hosted domain | IAP cannot admit accounts outside the org, which the researcher portal needs — see docs/auth.md |
| Public widget | plain JavaScript, no build step | One table does not justify a toolchain in the WordPress repo |
| Hosting | Cloud Run (admin), GitHub Pages (feed) | Scale-to-zero admin; the org's Domain Restricted Sharing policy refuses a public GCS bucket |
Running cost: ~$2–5/month. See Cost.
db-f1-micro and db-g1-small are shared-core and carry no Cloud SQL SLA;
their CPU is burstable and can be throttled, which shows up as an admin page that
occasionally stalls. They are a testing tier.
The smallest dedicated core (db-custom-1-3840, 1 vCPU / 3.75GB) is roughly
$50/month against ~$11, and gets the 99.95% single-zone SLA.
Capacity is not the reason to choose it. 2,103 outlets and 8,561 coverage records is a rounding error for Postgres — at a hundred times this size the database still would not be the constraint, and the real ceiling is concurrent admin users, which is under ten. Choose dedicated core for the SLA and predictable latency, not for headroom. Nothing in the architecture changes either way.
The admin is served at sources.localnewsimpact.org, not a run.app URL.
A Cloud Run domain mapping provides it, at no cost:
sources.localnewsimpact.org
-> CNAME (Route 53) overrides the *.localnewsimpact.org wildcard
-> Cloud Run domain mapping Google-managed certificate
-> Cloud Run service ingress: all
An earlier draft put a global external Application Load Balancer here, because
IAP on a bare Cloud Run service protects only the run.app hostname and domain
mappings do not carry IAP. Choosing allauth over IAP removed the reason for the
load balancer and the ~$18/month forwarding rule with it.
Because ingress is open, authentication is entirely the application's job — see Security notes.
DNS is Route 53, and *.localnewsimpact.org currently resolves to the WordPress
host (50.16.132.48). A record for the exact name takes precedence, so no wildcard
change is needed. Create it before the certificate is requested: a Google-managed
certificate will not issue until the hostname already resolves to the load
balancer address.
Gunicorn, WSGI, with the Cloud Run shape:
gunicorn --bind :$PORT --workers 1 --threads 8 --timeout 0 config.wsgi:application
One worker because Cloud Run bills per instance and handles concurrency itself;
threads because admin requests are I/O-bound on the database; --timeout 0
because Cloud Run enforces its own request deadline and a second one only
produces confusing 502s. Serve static files with WhiteNoise — without it the
Django admin loads unstyled on Cloud Run.
Yes. The three management commands run as Cloud Run Jobs built from the same image as the service, with a different entrypoint:
| Job | Trigger |
|---|---|
migrate directory |
on Datadesk's deploy, before traffic shifts |
import_source <gcs-uri> |
manual, or Cloud Scheduler |
rebuild_outlets |
manual, after an import |
publish |
on save, or scheduled |
Jobs are the right shape because they run to completion with no request timeout, and can be given more memory than the service — pandas reading a spreadsheet wants 1–2GB while the web service is comfortable at 512MB.
One split worth keeping: interactive spreadsheet uploads go through
django-import-export in the admin, because the editor needs the dry-run diff in
front of them. Jobs handle the batch and scheduled paths.
Not a framework preference — it is django.contrib.admin + django-import-export
django-simple-history+ the built-in permission model, which together are most of the application.
The deciding factor is the merge review. Fixing the prototype's dedupe means
opening one outlet and seeing its child coverage records — all 134 raw names
under patch.com — then splitting them. Admin inlines do exactly that out of
the box. In FastAPI + sqladmin, inlines and bulk actions are the parts you
would hand-build, and they are the parts most needed here.
The cost is a second web framework in the org. That is real and was accepted
deliberately. If the trade is revisited, the alternative is sqladmin or
starlette-admin on FastAPI — not Flask.
The public payload is 65KB gzipped for outlets, 204KB with coverage records included. The browser loads it once and does its own search, filtering, sorting and CSV export. No API service, no read replica, no query load.
No Cloud CDN in front of the feed. It requires an external Application Load
Balancer whose forwarding rule alone is ~$18/month. The feed serves from GitHub
Pages, which is already a CDN, already sends access-control-allow-origin: *,
and costs nothing. At 73KB gzipped there is nothing left for a CDN to buy.
It went to Pages rather than a GCS bucket because the organisation's Domain
Restricted Sharing policy refuses allUsers, so no bucket in this org can be
made public. If a custom domain for the feed is wanted later, it must actually
serve the feed — a name that merely resolves through the
*.localnewsimpact.org wildcard lands on the WordPress host, whose certificate
does not cover it, and the browser reports a bare network error rather than a
404.
Two tables, which is better than one — it makes the public/admin split structural rather than a per-column flag.
Outlet— curated outlet profiles. Publishes tosites.json.CoverageRecord— the source rows, verbatim, withsource_file/source_sheetprovenance. Admin only. Never edited by derivation; every Outlet field must be reproducible from it.Medium,Category,State— controlled vocabularies. Once medium is a foreign key, a URL cannot be stored in it and the header-row class of error becomes structurally impossible.Collection— a named subset, the unit handed to the crawler.
Implemented in directory/models.py, with every choice
argued from the data in docs/schema-decisions.md.
The prototype deduplicated on the bare registrable domain, which merged 1,102
distinct outlets into 222 rows — patch.com alone collapsed 134 outlets into
one. domain is kept and indexed because it is the join key to the crawler, but
it is not unique. Identity is host + first meaningful path segment, or
slug(name)|state when there is no URL. Details and caveats in
MIGRATION.md.
MizzouNewsCrawler is a separate system in a separate GCP project. Contributors here never touch the crawler's production project, and this repository needs no access to the crawler's data.
One piece of infrastructure is shared, and it is worth being exact about: the
directory database lives on the crawler's Cloud SQL instance
(mizzou-news-crawler:us-central1:mizzou-db-prod-ssd), borrowed rather than
bought again -- a dedicated instance would be roughly $50/month for a database
this size. Isolation is at the database-and-user level, not the instance level.
infra/README.md records the decision.
Eventually the crawler may be pointed at subsets of this registry. Flow is
one-way — registry upstream, crawler downstream, no write-back. A
Collection slug becomes a crawler dataset slug, and the Outlet id lands in
dataset_sources.legacy_host_id, which is uniquely constrained per dataset and
so makes re-ingest idempotent. When the crawler learns something the registry
should know — dead domain, moved URL — it surfaces as a report for a human, not
an automated write.
Both consumers read the same published export from the bucket, so the crawler needs no credential into this project's database.
The export names an explicit column allowlist rather than SELECT *, so a
future schema addition cannot silently publish something new. Operational fields
stay in the admin — in the crawler's Missouri export, status and
paused_reason ("Automatic pause after 5 consecutive cycles with no articles
discovered") are the kind of field that must never reach the public JSON.
localnewsimpact.org runs Divi. The directory mounts into the light DOM with
.lnic-dir-* prefixed classes so it inherits the site's fonts and link colours,
placed by a small shortcode plugin ([lnic-directory]) alongside the existing
lnic-form-plugin. The bucket needs CORS allowing the site origin.
Design tokens taken from the live /studies/ page:
| Token | Value |
|---|---|
| Accent | #66cef6 |
| Link | #0073aa, bold, underline on hover |
| Border | 1px solid #ddd |
| Header row | #f2f2f2, bold, #333 |
| Cell padding | 12px 8px, left aligned |
| Fonts | Montserrat (headings), Lato (body) |
The /studies/ table renders in Arial, which is that sheet plugin's default
rather than a design decision; the directory uses the site fonts instead. That
page is itself a Google Sheet rendered as <table class="google-sheet-table">
with no search, filter or export — a candidate to move onto this widget later.
mockup/index.html is a working, self-contained prototype
carrying the full dataset and every feature of the Streamlit app: metric tiles,
keyword search, all three multi-select filters, outlet cards, the coverage table
and the data explorer, with CSV export of whatever is on screen. Card/table
toggle, sortable columns, filter chips and pagination are additions.
python -m feed writes a content-addressed static feed:
feed/manifest.json small, short cache TTL, always revalidated
feed/sites.<sha8>.json immutable, cache for a year
feed/search-index.<sha8>.json immutable — optional, see below
Content hashing is what makes this work on a bare bucket with no CDN: the manifest is the only file that ever needs revalidating, everything it points at is immutable. A publish writes new hashed files and swaps the manifest, so a reader never sees a half-updated feed.
The build is deterministic — sorted keys, sorted rows, hash independent of
build time — so an unchanged dataset produces an unchanged hash and no pointless
redeploy. Projection onto PUBLIC_FIELDS happens in one place, and the publish
refuses to write when any rule errors unless --allow-errors is passed
explicitly.
python -m feed outlets.csv --coverage coverage.csv --out dist/feed
npm run build:index # optional, see belowIt is implemented (tools/build-search-index.mjs, in Node so the serialised form
always matches the library version the widget loads) but off by default,
because the measurement does not support it:
| Gzipped | Time | |
|---|---|---|
sites.json alone, index built in browser |
73KB | 16ms to index 2,103 docs |
plus prebuilt search-index.json |
205KB | 12ms to load |
Shipping the index costs 132KB gzipped to save 4ms. Build it in the browser.
Revisit if the registry grows by an order of magnitude, or if indexing time becomes visible on low-end phones — at which point the generator is already here and the decision is a flag, not a rewrite.
make setup # venv, dependencies, .env, Postgres in Docker
make check # everything CI runsNo GCP access needed — the project runs locally against fixtures. Full workflow in CONTRIBUTING.md.
main is protected: branch, push, open a PR, CI must pass, a reviewer must
approve, then merging deploys.
.github/workflows/ci.yml runs eight jobs on every branch, not only on pull
requests, so failures surface before a PR exists. docs/pipeline.md walks the whole chain from a local edit to
production.
| Job | Checks |
|---|---|
| Lint | ruff check and ruff format --check |
| Tests | unit tests over the rules, identity, feed and mockup |
| Integration | tests against a real Postgres 16 service |
| Data quality | the rules against a fixture of real prototype data |
| Public feed | feed builds, carries no admin columns, is reproducible |
| Image builds | both Docker stages build; the container starts and /_health returns 503 |
| Browser | the directory renders, search narrows, export produces a CSV |
| Pages payload | the mockup stays servable, internal doc links resolve |
The rules live in checks/rules.py as pure functions. CI runs
them against fixtures; the publish command will run the same functions against
the live export. A defect cannot reach sites.json by taking a different code
path.
python -m checks outlets.csv --coverage coverage.csv
python -m checks outlets.csv --export sites.json # before publishingERROR blocks a publish. WARN is counted and reported but does not block — a missing county is curation backlog, not corruption, and a permanently red pipeline gets ignored.
The single most important rule is export_columns_allowlisted: a column not on
the public allowlist fails the publish. That is what stops an admin field such as
paused_reason reaching the public site when someone adds it upstream.
tests/fixtures/ holds 32 outlets and 88 coverage records
sampled from the real prototype data and chosen to contain every known defect.
The data-quality job asserts the run fails and that each named rule fires. A
clean run there means detection has regressed, not that the data got better.
Against the prototype dataset as first imported, the rules reported:
| Rule | Errors |
|---|---|
merge_requires_review |
222 |
state_not_abbreviated |
73 |
no_header_artifacts |
3 |
no_url_in_medium |
2 |
no_placeholder_domain |
2 |
That 222 is the same figure the migration analysis arrived at independently, which is the point: the defect is now a test rather than a paragraph.
docs/pipeline.md walks it end to end: what runs locally, what each CI job protects against, the deploy sequence, and how the feed publishes. Deploys run from GitHub Actions via Workload Identity Federation, so no human holds production write access.
| Line item | Monthly |
|---|---|
Database — directory on the crawler's mizzou-db-prod |
$0 |
| Admin hostname — Cloud Run domain mapping, not a load balancer | $0 |
| Cloud Run (scale-to-zero) | $0–2 |
| Feed hosting — GitHub Pages | $0 |
| Artifact Registry, Secret Manager, logs | <$1 |
| Total | ~$2–5 |
An earlier draft of this design came to $70/month: a dedicated Cloud SQL core
($50) and a load balancer for the admin hostname (~$18). Both were bought
rather than borrowed. The crawler project already runs a Postgres 16 instance in
us-central1, and a Cloud Run domain mapping provides a custom hostname for
nothing — so neither charge was buying anything this project actually needed.
Ingress is open, so the application is the only thing standing in front of the admin. Three controls carry that weight:
- The hosted domain is verified server-side. Google's
hdparameter is a hint supplied by the client; the allauth adapter checks thehdclaim andemail_verifiedon every login. Trusting the parameter would make the restriction decorative. - The feed is allowlisted, not filtered.
export_columns_allowlistedfails the publish on any column not on the public list, so a new model field cannot leak by being added upstream. CI asserts this on every branch. - The database role is boxed in.
directoryowns only its own database, holds no role memberships, and cannot connect to the crawler'smizzoudatabase on the same instance.
An earlier draft of this file described verifying X-Goog-IAP-JWT-Assertion and
setting ingress to internal-and-load-balancer. Neither applies: there is no IAP
and no load balancer.
Built, deployed and serving. The admin is at
https://sources.localnewsimpact.org/admin/, and the public feed publishes to
the gh-pages branch of this repository.
The registry currently holds 2,809 outlets derived from 8,561 coverage records, against the prototype's 2,103.
- Schema — nine decisions in docs/schema-decisions.md
- CI on every branch: lint, unit, integration, data quality, feed build, Pages payload
- Django project, admin, import/export, history, and the review dashboard
-
import_source,rebuild_outlets,publish,seed_places,seed_vocabularies - GCP: project, Cloud SQL on the crawler's instance, Artifact Registry, secrets
- Deploy on merge to
mainvia Workload Identity Federation - Google sign-in restricted to the
localnewsimpact.orghosted domain - Public static feed, content-addressed, with a lazy coverage payload
- The widget and the WordPress shortcode plugin
- Work the review queue: 167 suspected bad merges, 143 outlets with no domain, 228 with no medium, 289 data-quality issues open
- Point the WordPress page at the production feed and publish it
- Load the crawler's sources — see docs/crawler-etl.md
IAP was considered and rejected in favour of application-level auth; a public
GCS bucket was rejected because the organisation's Domain Restricted Sharing
policy refuses allUsers. Both are explained in docs/auth.md and
infra/README.md.