Skip to content

Correcting the daily snapshot delay

Ticket #375: Correcting the daily import's UTC schedule Type: Diagnosis / Operations / Data reliability Affected Component: scripts/pipeline.crontab, tests/test_pipeline_schedule.py, operations documentation

Need

On October 31, 2026, the secure site displayed an October 29 snapshot, although the last completed session was Friday October 30. The delay had been reported for several weeks.

Investigation and confirmed cause

Read-only VPS checks established that its timezone was Etc/UTC and its import ran at 02:00 UTC Monday through Saturday. Montreal is still on the previous calendar day at that time: 22:00 in daylight time, 21:00 in standard time.

The pipeline correctly targets the previous weekday of the Montreal date. Saturday October 31's 02:00 UTC run therefore executes on Friday October 30 at 22:00 in Montreal and requests October 29. The database's latest date was indeed October 29; this was not merely a display-label error.

The exact start date was not established. August's incident report already mentions 02:00 UTC, indicating that this schedule is not recent. A check of September and October Fridays found all four September Fridays present, but October 2, 9, 16, 23 and 30 absent. The defect therefore affects historical completeness as well as snapshot freshness. Missing sessions are established from October 2, without proving when the schedule changed or how September's Fridays were imported.

Prepared correction

  • Versioned reference in scripts/pipeline.crontab: 06:00 UTC Monday through Saturday, corresponding to 02:00 EDT / 01:00 EST on the same Montreal date.
  • Existing Python previous-weekday logic, Sunday guard and Monday catch-up policy remain unchanged.
  • Replace only the pipeline entry after validation, preserving unrelated scheduled tasks.
  • No artificial price or date changes to conceal stale data.

Local validation evidence

  • Seven scenarios failed with production's 02:00 UTC schedule before the correction.
  • Six calendar scenarios pass at 06:00 UTC, covering both daylight-saving transitions and Friday retrieval.
  • One integration test verifies the API request date, SQLite snapshot date and price, and the snapshot rendered by the secure dashboard.
  • 90 focused tests passed: 74 schedule/pipeline/dashboard tests and 16 documentation configuration/wiki tests. Two expected warnings concern a corrupted CSV.
  • Focused pipeline.py code coverage: 70.93%, above the 67% threshold used for this validation. This is not repository-wide coverage.
  • Isolated local portfolio: two real pipeline imports with simulated API responses, October 29 and 30 snapshots, and no production database access.

Validation and production application

The user explicitly validated the local portfolio and first authorized the schedule change and October 30 retrieval, then recovery of the four preceding Fridays.

  • The installed crontab was backed up before modification; only the pipeline entry and its timezone comment changed. The 06:00 UTC installation was read back and the cron service is active.
  • 132 of 138 prices were retrieved for October 30 and compared with persisted values, accounting for actual production column precision.
  • Six provider prices remain unavailable; no CSV fallback was used to give them an artificial recent date. An operator alert was sent.
  • The latest database snapshot is now October 30, 2026, without duplicates. Dashboard rendering and a linked holding's history were checked using production data in a separate diagnostic process, without changing authentication in the live service.

Two strict checks required further investigation: the import added a tracked CSV holding without a current position or transaction, and exact quantity comparison encountered the historical column's two-decimal precision versus four decimals in the ledger. The new holding was confirmed to have zero snapshot shares; persisted quantities match the ledger at the column's actual precision. Import code does not update current positions or transactions. No schema change was made.

Recovery of preceding Fridays

After authorization, October 2, 9, 16 and 23 were recovered using Marketstack prices matching each exact trading date, without CSV fallback.

APILayer historical exchange-rate access returned HTTP 401. For this recovery only, the user authorized Bank of Canada daily average USD→CAD rates. The daily import provider, its cache and configuration were not changed.

Trading date Stored prices / tracked holdings Unavailable prices Dated USD→CAD rate
October 2, 2026 133 / 138 5 1.4246
October 9, 2026 133 / 138 5 1.4271
October 16, 2026 133 / 138 5 1.4154
October 23, 2026 132 / 138 6 1.3993

All 531 persisted prices and conversions were compared with provider data at actual column precision; quantities match the ledger for each date. No duplicates were created. Transactions, existing current positions and snapshots outside these four dates are preserved. The import also refreshed holding metadata and added a tracked holding with historical prices, without a current position or transaction.

The initial all-column holding integrity check was refined to distinguish normal metadata refreshes from protected financial data. October 2, already persisted during that check, was reverified without rewriting it before completing the other three dates.

Unavailable prices triggered an operator alert for each date; no current price or rate was substituted. The dashboard retains October 30 as its latest snapshot, and a holding page displays all four recovered dates in its history table. These views were verified read-only against production data, without changing the live service.

Token usage summary

Consumption tracking covers the investigation of issue #375, the schedule correction, snapshot recovery, verification and documentation.

Tracked item Summary
Working session duration (estimate) Approximately 1 hour
Copilot AI credits consumed 134.7
Total cost $1.35

Credits and cost are unavailable in the consulted session data; they are not estimated from duration.

References