Skip to content

Forecast-history schema compatibility

The durable forecast-history database is a versioned SQLite asset. The application treats PRAGMA user_version as the authoritative schema version and mirrors that value in metadata.schema_version for human-readable diagnostics.

The current schema version is 4. Schema version 3 adds reproducibility metadata to each forecast origin:

experiment_run_id
configuration_id
experiment_manifest_json

The complete manifest is first-write-wins with the forecast origin, so a manual rerun cannot replace the code/configuration/data identity attached to the original historical forecast. CSV and JSONL forecast exports include these fields.

Schema version 4 adds the drift_events audit table used by production drift detection. Warning and severe events store the observed evaluation origin, evaluation/run identity, signal key and kind, severity, optional model/horizon or feature identity, and the machine-readable metric payload. The unique (evaluation_origin_at, signal_key, severity) key makes repeated evaluation of the same observed state idempotent.

Migration policy

  • Schema changes are added as ordered migrations in history_migrations.py.
  • Migration numbers are contiguous and never reused.
  • Opening the database automatically upgrades every supported older version to the current schema.
  • Migrations are idempotent: reopening an already-current database does not reapply changes.
  • Applied migrations are recorded in schema_migrations from schema version 2 onward.
  • A database with a schema version newer than the running code is rejected without modification. Forward compatibility is therefore fail-safe rather than best-effort.
  • Downgrades are not performed in place. To run older code, restore the previous release asset or another backup created by that older release.

Recovery guarantees

Before changing any existing database, the migration runner creates a byte-for-byte backup named:

<database>.pre-migration-v<source-version>.bak

Migrations execute in a SQLite transaction. If a migration or post-migration validation fails, the transaction is rolled back and the original database bytes are restored from that backup before the error is re-raised. A failed clean-database migration removes the partially created database.

The production workflow also keeps forecast_history.previous.sqlite.gz, copied from the restored release asset before migration. The durable release is only replaced after the migrated database passes integrity, foreign-key, schema-version, metadata-version and migration-audit validation.

Release compatibility

The forecast-history-v1 GitHub Release tag identifies the durable datastore product, not the internal SQLite schema version. Its forecast_history.sqlite.gz asset may therefore contain an older supported schema. Production restores that asset, migrates it automatically, validates it, runs the forecast, and only then publishes the upgraded asset.

Current code supports every schema version represented by the migration registry in history_migrations.py. Removing an old migration is a breaking storage change and should only happen together with an explicit data-retention decision and release migration plan.

Experiment manifests

experiment_manifest_json stores the versioned manifest generated by experiment_manifest.py. It records the Git SHA, TimesFM model/package version, forecast configuration, deterministic seed, source/provider identity, exact candle window and a SHA-256 digest of the OHLCV input.

configuration_id is a stable fingerprint for comparing runs made with the same effective configuration. experiment_run_id identifies one execution. Production drift severity, thresholds and adaptive-confidence behavior are included in the forecast run parameters so drift-modified forecasts remain reproducible. See EXPERIMENTS.md and DRIFT_DETECTION.md for the reproduction procedures.

Diagnostics

Use:

PYTHONPATH=src python -m btc_timesfm.history.history_store --db .state/forecast_history.sqlite verify
PYTHONPATH=src python -m btc_timesfm.history.history_store --db .state/forecast_history.sqlite stats

Both commands expose the current schema version. stats also exposes durable drift-event count and the latest persisted drift severity/origin. Migration-specific diagnostics, including the ordered audit trail, are available through history_migrations.schema_diagnostics() and are validated by history_migrations.validate_database().