Daily import outage and duplicate history cleanup
Ticket #326: Daily import outage and duplicate history rows
Type: Debugging / Fix / Data Integrity
Affected Component: run_pipeline.sh, scripts/catchup_prices.py, database (historique), tests/test_pipeline.py, tests/schema.sql
1. Context and Symptoms
A simple observation triggered this debugging work: for a few weeks, I had noticed that the price history in the database had stopped updating, while the application itself seemed to be running normally (containers active, no crash).
2. Investigation Process and Findings
SSH access and log review
I connected to the production VPS and inspected the scheduled task (crontab -l) and the log file /var/log/cron-pipeline.log. The cron schedule was correct (02:00 UTC, Monday to Saturday), but each execution had been failing with the same message for some time:
Finding 1 — The daily launcher had disappeared from the server
The file run_pipeline.sh, which is responsible for triggering the import inside the application container, simply no longer existed on the VPS and had never been tracked in Git. The last successful run dated back to July 2, 2026 — exactly the date when the history stopped in the database. The application, database, and Docker were all functioning normally; only the automatic trigger was missing.
Finding 2 — A manual catch-up revealed a second problem: duplicates
After restoring the launcher, a catch-up script (scripts/catchup_prices.py) was used to fill the missing market days between July 3 and August 14. Once the refreshed data appeared in the interface, my tests revealed that each asset displayed two identical rows per date (example observed: Air Canada TSE:AC, duplicated price from July 1 to August 14).
Finding 3 — Root cause of duplicates: a missing production constraint
Analysis of the real schema of the historique table in production showed the absence of the UNIQUE (titre_id, date_releve) constraint. The write logic (insert_data()) relies on ON DUPLICATE KEY UPDATE, assuming that this constraint exists — but it existed only in the test schema (tests/schema.sql), not in production. Result: each new attempt to write a date already present created an additional row instead of updating it.
Duplicate impact summary: 3,896 duplicate rows in total — 3,891 generated after July 2, 2026 (direct consequence of the outage and catch-up), and 5 pre-existing duplicates since November 2025 with slightly different values.
3. Root Causes Identified
| # | Cause | Impact | Status |
|---|---|---|---|
| 1 | run_pipeline.sh missing from the server and never versioned in Git |
Complete stop of automatic imports from July 3 to August 17, 2026 | Fixed |
| 2 | Missing UNIQUE (titre_id, date_releve) constraint in production |
Each rewrite of an existing date created a duplicate instead of an update | Fixed |
| 3 | Test schema and production schema were out of sync | The bug remained invisible in tests because the constraint existed only in the test environment | Fixed |
4. Solutions Implemented
4.1 — Restore and harden the automatic launcher
Recreated run_pipeline.sh, deployed it to the VPS, and validated a full end-to-end execution (cron → container → database). The script is now kept in the Git repository to prevent any future disappearance.
4.2 — Catch-up of missing data
Created scripts/catchup_prices.py, a reusable and idempotent tool (with --dry-run and --verifier modes) that reuses the existing Marketstack enrichment to fill missing trading days without duplicating dates already present.
4.3 — Duplicate cleanup and permanent locking
Created a backup of the table (historique_backup_20260817), removed the 3,896 duplicate rows (keeping the oldest row), and then added the UNIQUE (titre_id, date_releve) constraint directly in production. This makes the bug class structurally impossible regardless of any future application error. The cleanup script, intended for one-time use, was removed from the repository after completing its mission.
4.4 — Strengthening the test suite
Added tests that explicitly lock in what had failed:
- tc-pipe-integrity01 — the schema rejects a second price for the same asset on the same date.
- tc-pipe-integrity02 — re-importing the same date updates the existing row without creating a duplicate.
- tc-pipe-fx01 — USD → CAD conversion remains correct, without side effects from the fix.
- tc-pipe-date04, tc-pipe-catchup01, tc-pipe-catchup02 — Sunday blocking and catch-up scheduling.
The test schema (tests/schema.sql) was aligned with the constraint now enforced in production.
5. Verification and Outcome
- Full test suite: 251/251 tests passing.
- Pipeline test coverage: 75% → 86% (30/40 → 36/42).
- Overall documented coverage: 82% → 86% (90/110 → 96/112).
- Database: one row per asset and per date, history updated through August 14, 2026, with no data loss during cleanup (backup retained).
- Automatic import: daily execution restored and validated on the server.
6. Token Consumption Summary
925.67 units consumed for a cost of $9.25. This figure covers the whole debugging session — repeated SSH connections, database diagnostics, data catch-up, and test-suite hardening. Considering that it took me approximately 3 hours to investigate, repair, improve, and document this incident using AI, this token cost is insignificant compared to the cost of a developer over the same amount of work.