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.