Data model and functional graph

The database schema and its relationships (read live from D1), and the functional graph of what runs, when, and why, with live counts of every job and ledger event.

1 Entity–relationship diagram · 2 Tables by layer · 3 Functional graph · 4 When and why · 5 Live activity

1. Entity–relationship diagram live

Generated from SQLite metadata (pragma_table_info / pragma_foreign_key_list) through /api/model. Row counts are current.

Loading…

2. Tables by layer live

3. Functional graph

flowchart TD CRON(["Hourly schedule :17 UTC"]) --> SCHED["scheduleFetches: due sources, idempotent per refresh window"] CRON --> RESEND["resendStale: re-queue lost or deferred jobs"] CRON --> BACK["backfill: upgrade old data to the current method"] CRON --> DELIV["deliverToSubscribers: signed webhooks"] DAILY(["Daily 03:17 UTC"]) --> ANCHOR["anchor: verify new rows, store head hash"] ADMIN(["Admin run / heavy lane"]) --> FETCH SCHED --> Q[["Queue: one job at a time"]] Q --> FETCH["fetch"] FETCH --> ROB{"robots.txt allows?"} ROB -- no --> SKIP["fetch_skipped (ledgered)"] ROB -- yes --> WAL["LEDGER fetch_started"] --> GET["conditional GET"] GET --> SIZE{"size ≤ edge limit?"} SIZE -- no --> HEAVY["heavy lane: GitHub Actions uploads 4 MiB parts"] --> DONE SIZE -- yes --> HASH["SHA-256 → content store"] --> DONE["LEDGER fetch_completed"] DONE --> CHG{"bytes changed?"} CHG -- no --> NOOP["no new version, no events"] CHG -- yes --> VER["document_version + change_event"] --> EXT["extract: toMarkdown / identity / OCR flag"] EXT --> PARSE["parse: deterministic parser per source"] EXT --> COMPACT["compact: reference card (legal texts)"] PARSE --> CODES["codes / provisions / tariffs"] PARSE -- "child pages" --> FETCH CODES --> MAP["map: keyword rules + chaining → proposed mappings"] CODES --> TR["translate: MT baseline + glossary LLM"] MAP --> GAP["gap: coverage, granularity, linguistic, disagreement"] TR --> GAP GAP --> EVT["change_event"] --> DELIV REVIEW(["Human review"]) --> MAP

4. When each part runs, and why

PartWhenWhy this design
Fetch schedulingEvery hour; each source has its own refresh window (e.g. 168 h for OPS pages, 720 h for EU acts and NZa files)Detects rule changes without hammering government sites; the job key includes the window, so one fetch per window at most.
Queue consumerContinuously, one message at a timePoliteness (one request in flight), no ledger contention, and each step stays inside the free plan's 10 ms CPU limit.
Child-page discoveryAfter an OPS page is parsed, spaced 8 s apartFollows the code hierarchy only within scope (5-87, depth ≤ 2).
Extract / parse / map / translate / gapOnly when the source bytes changedUnchanged documents cost nothing downstream; every step is idempotent.
BackfillEvery hour, small batchesApplies method upgrades (new model, new glossary) to existing data without deleting history.
Budget governorBefore every AI call and storage writeStops at about 80% of the free daily limits and defers work to the next day.
AnchorDaily 03:17 UTCIncremental re-verification of the hash chain plus a separate copy of the head hash.
Heavy laneWeekly (Mon 04:41 UTC) or on demandFiles larger than the edge limit are fetched by GitHub Actions and streamed in parts; the Worker stays the only ledger writer.
WebhooksEvery hourAt-least-once, signed delivery of change events to listeners.

5. Live activity live

Jobs by step and status

StepStatusnLast

Ledger events by type

EventnFirst → last

Latest 25 ledger entries

#When (UTC)EventSourceSubjectHTTP