C3S2-375 replication dashboard collector

This first-stage collector preserves the reporting rows from the original planning workbook while treating esgpull queries as many-to-many execution units. It reads esgpull.db in read-only mode and writes CSV and JSON snapshots that are ready to import or upsert into Grist.

Run

python3 c375_dashboard.py --refresh-sheet
python3 c375_dashboard.py --db /path/to/esgpull.db

The first command captures the five centre sheets into work/c375_requirements.csv. The second produces outputs/c375_dashboard.csv and outputs/c375_dashboard.json.

No database records are changed and no data is sent to Grist yet. The next stage will add authenticated Grist upserts after the snapshot has been checked against esgpull show and esgpull status.

Grist dry run

The Grist table must contain columns matching the CSV headers, including a unique text column named requirement_id. Preview the exact upsert payload:

python3 grist_sync.py

Publishing is explicit and uses requirement_id as the stable match key:

GRIST_API_KEY=... GRIST_DOC_ID=... python3 grist_sync.py --apply

The default table ID is C375_Replication; override it with GRIST_TABLE_ID or --table. Rows are added or updated, never deleted.

Current matching rules

• A requirement matches a query when its start date occurs in the query's sub_experiment_id selection.
• Recognized centre tags (for example dwd) must match the workbook sheet.
• The current database's test tag is explicitly mapped to CMCC and uib to the workbook's UiB/NERSC label.
• CMIP6 Plus and CMIP6Plus are normalized to the same project value.
• dcpp-a maps to dcppA-hindcast; dcpp-b maps to dcppB-forecast.
• Files linked through multiple queries are deduplicated by file SHA and then assigned to the sheet row using the start-date token in their DRS identity.

The collector intentionally keeps sheet notes and Cat-1/Cat-2 variable lists verbatim. The current reporting grain is centre plus start date, matching the source workbook. Variable-level drill-down can be added after validating the real database's dataset IDs and query conventions.

Periodic VM refresh

update_c375_report.py performs the complete refresh safely:

1. Uses SQLite's online backup API to take a transactionally consistent copy of the live database (including committed WAL content).
2. Runs PRAGMA quick_check on the copy before using it.
3. Builds the report only from that validated copy.
4. Upserts the detail, institution summary, status summary, and institution history tables in Grist.
5. Keeps the latest 14 backups by default.

Download rates and ETA

Each successful local report build appends one small snapshot per institution, plus an ALL row, to outputs/c375_institution_history.csv. History is kept for 90 days by default; change this with --history-days. No history is written to the ESGpull database.

The institution summary contains the latest daily rate (since the preceding snapshot), seven-day rate, and ETA. The ETA prefers the seven-day rate and uses the daily rate until a full week of history is available. A reduction in downloaded volume, such as after changing queries, resets the affected rate baseline instead of reporting a negative speed.

Create a Grist table with ID Institution_History before enabling publication. Its unique key is the Text column history_id. Add these additional columns to both Institution_Summary and Institution_History where applicable:

snapshot_time          DateTime
downloaded_delta_tib   Numeric
elapsed_hours          Numeric
daily_rate_tib_day     Numeric
daily_rate_mib_s       Numeric
weekly_rate_tib_day    Numeric
weekly_rate_mib_s      Numeric
eta_days               Numeric
estimated_completion   DateTime
rate_basis             Text

Institution_History additionally needs institution (Text), downloaded_tib, remaining_tib, and total_tib (Numeric). These must be data columns because the synchronization script supplies their values. The history sync prunes records older than the locally retained window.

Keep the Grist secret outside the scripts. For example, create /home/abennasser/c3s/c375-report.env, readable only by its owner:

GRIST_HOST=https://grist.numerique.gouv.fr
GRIST_DOC_ID=rhuWpdLQZ4i83edYxfQJQq
GRIST_API_KEY=replace-with-a-new-key

Then test without publishing:

cd /home/abennasser/c3s/dashboard
python3 update_c375_report.py --no-publish

Run the real refresh after loading the environment:

set -a
. /home/abennasser/c3s/c375-report.env
set +a
python3 update_c375_report.py

The included c375-report.service and c375-report.timer are example systemd user units. Copy them to ~/.config/systemd/user/, then enable the daily timer:

systemctl --user daemon-reload
systemctl --user enable --now c375-report.timer
systemctl --user list-timers c375-report.timer

The timer runs every day at 06:15 and catches up after VM downtime. Adjust OnCalendar in the timer if a different schedule is preferable.

Review plan for missing queries

Generate candidates from a validated backup:

python3 generate_missing_queries.py \
  --db /home/abennasser/c3s/backups/c375-YYYYMMDDTHHMMSSZ.db

This writes outputs/c375_missing_query_plan.csv. It clones the established facet patterns for the same centre and experiment and substitutes only the missing sub_experiment_id values. It never changes the esgpull database. The commands should first be checked with esgpull search; adding, updating, and downloading remain separate, deliberate steps.

Exact-URL manifest reconciliation

manifest_esgpull.py treats a one-URL-per-line manifest as the authoritative C375 file list. Resolution is checkpointed outside the production database. Reconciliation is a separate dry-run operation that plans missing-file insertion, replacement of failed or stale source URLs, requeuing, dashboard provenance links, and removal of queued files outside the manifest.

python3 manifest_esgpull.py --manifest manifest.txt audit

python3 manifest_esgpull.py --manifest manifest.txt resolve \
  --esgpull-root /home/abennasser/c3s \
  --database-only

python3 manifest_esgpull.py --manifest manifest.txt resolve \
  --esgpull-root /home/abennasser/c3s \
  --limit-batches 5

python3 manifest_esgpull.py reconcile \
  --esgpull-root /home/abennasser/c3s

The last command writes outputs/manifest_reconciliation.tsv without changing the ESGpull database. Once every URL is resolved, a validated backup has been taken, and the plan has been reviewed, pass --apply. The final download command passes only resolved manifest File objects to ESGpull's Python API. Downloaded files outside the manifest are retained; surplus queued database records are removed immediately during reconciliation.

When metadata cannot be recovered from the existing database or ESGF search, the download command can use the manifest URL directly:

# Smoke-test ten unresolved URLs first.
python3 manifest_esgpull.py download \
  --esgpull-root /home/abennasser/c3s \
  --limit-files 0 \
  --direct-fallback \
  --direct-limit-files 10

# Continue all resolved ESGpull downloads and all remaining direct fallbacks.
python3 manifest_esgpull.py download \
  --esgpull-root /home/abennasser/c3s \
  --batch-size 100 \
  --direct-fallback \
  --direct-concurrency 8

Direct fallback streams only the exact manifest URL into the configured ESGpull data tree, computes SHA-256 during transfer, and registers the completed file with source=manifest-direct. This checksum fingerprints the received content; unlike metadata-assisted downloads, it cannot independently verify the ESGF publisher's checksum. Invalid manifest URLs always remain a hard stop.

Manifest-based dashboard

The scheduled report now treats outputs/manifest_state.sqlite as the list of required URLs and the validated ESGpull backup as their download state. It publishes compact aggregates rather than hundreds of thousands of URL rows:

• Manifest_Status_Summary: three rows (Downloaded, To download, Failed)
• Manifest_Host_Summary: counts and completion by source hostname
• Manifest_Failure_Summary: failure counts grouped by host and error type

Create those Grist tables with the following columns before the first publish:

Manifest_Status_Summary
  status Text (unique), url_count Integer, percentage Numeric,
  known_tib Numeric, snapshot_time DateTime

Manifest_Host_Summary
  hostname Text (unique), total_urls Integer, downloaded_urls Integer,
  to_download_urls Integer, failed_urls Integer, deferred_urls Integer,
  completion Numeric, snapshot_time DateTime

Manifest_Failure_Summary
  failure_id Text (unique), hostname Text, failure_reason Text,
  example_error Text, url_count Integer, snapshot_time DateTime

Build the CSV files without publishing:

python3 update_c375_report.py --keep-backups 1 --no-publish

A deferred URL remains part of the manifest and is counted as To download. Actual download errors are counted as Failed; a Done ESGpull record takes precedence over an error left by an older attempt.