SKILL.md
When managing database migrations for the Cycle Navigator Dashboard, follow these mandatory steps:
- Mandatory Backup: Always back up the production database before any migration.
- Linux (Bash): pgdump -h localhost -U cycleuser cyclenavigator > backup$(date +%Y%m%d).sql - Linux (Fish): pgdump -h localhost -U cycleuser cyclenavigator > backup(date +%Y%m%d).sql - Windows (PowerShell): pgdump -h localhost -U cycleuser cyclenavigator > backup$(Get-Date -Format 'yyyyMMdd').sql - Windows (CMD): pgdump -h localhost -U cycleuser cyclenavigator > backup%date:~10,4%%date:~4,2%%date:~7,2%.sql
- Pre-Migration Checks: Run the check script to verify compatibility before applying migrations. This script checks for schema conflicts, data type mismatches, and TimescaleDB extension availability.
Prerequisites: Ensure Python 3.x is installed and dependencies are met (run pip install -r requirements.txt if needed). Command: python3 scripts/runtimescalemigrations.py --check-only (use python on Windows if aliased). If checks fail, review the output for issues and resolve before proceeding.
- Hypertable Conversion: Converting a table to a hypertable is irreversible. Verify the
chunktimeintervalbefore conversion (standard is '1 month' for FRED data due to lower update frequency, and '7 days' for crypto data due to high-frequency volatility).
Command: python3 scripts/runtimescalemigrations.py --convert-table <tablename> --chunk-interval <interval> After conversion, run SELECT * FROM timescaledbinformation.hypertables; to confirm the hypertable exists and chunk settings are correct.
- Compression Policy: Ensure new hypertables include a compression policy to optimize storage and query performance. The standard is 90 days for macro data (slower update cadence) and 30 days for crypto data (high-frequency updates).
Command: python3 scripts/runtimescalemigrations.py --compress-table <tablename> --compress-after <days> Verify with: SELECT * FROM timescaledbinformation.compressionsettings WHERE hypertablename = '<table_name>';
- Continuous Aggregates: When pre-calculating metrics, use materialized views with a refresh policy to reduce query latency. Use hourly refreshes for crypto data (high-frequency volatility) and daily refreshes for macro data (slower update cadence).
Command: python3 scripts/runtimescalemigrations.py --create-aggregate <aggregatename> --source-table <tablename> --refresh-interval <interval> Example metrics: OHLCV candles, moving averages, volatility indices. Verify with: SELECT * FROM timescaledbinformation.continuousaggregates WHERE viewname = '<aggregatename>';