Migration Management

Relevant source files

The following files were used as context for generating this wiki page:

• prisma/migrations/20260213110000_comprehensive_schema_refactor/migration.sql
• prisma/migrations/20260212131500_mes_isolated_price_tables/migration.sql
• package.json
• README.md
• cubic.yaml
• scripts/reconcile-economic-series.ts

Migration Management in the Rabid Raccoon project encompasses the lifecycle of database schema evolution, data transformation, and architectural isolation. The project utilizes Prisma ORM as the primary engine for schema definition and migration execution, supplemented by specialized SQL migrations and reconciliation scripts to handle complex data refactoring, such as moving from Float to Decimal types or consolidating multiple domain-specific tables.

The scope of migration management covers the transition of market data (MES futures), economic indicators, and news feeds into a structured, isolated database environment. This ensures data integrity for high-frequency trading (HFT) modules and AI forecasting models.
Sources: README.md:92-111, cubic.yaml:13-20, prisma/migrations/20260213110000_comprehensive_schema_refactor/migration.sql:1-12

Core Migration Workflow

The project follows a standard Prisma-based workflow for local development and production deployments. Local migrations are managed via prisma migrate dev, while production deployments use prisma migrate deploy to apply pending changes without resetting the database.

The workflow ensures that schema changes are tracked in version control before being applied to production environments.
Sources: package.json:15-18, README.md:104-108

Key Migration Commands

Command	Script Path	Description
npm run prisma:migrate	prisma migrate dev	Creates and applies migrations in development.
npm run db:migrate	prisma migrate deploy	Applies migrations to a target database in production.
npm run prisma:generate	prisma generate	Regenerates the Prisma Client based on the latest schema.
npm run db:counts	scripts/db-counts.ts	Validates migration success by checking row counts.
Sources: package.json:15-18, README.md:143-145

Schema Refactoring and Data Integrity

Recent migrations have focused on increasing precision and isolating critical market data. A significant refactor involved converting numeric fields from Float to Decimal(18,6) or Decimal(24,8) to prevent floating-point errors in trading signals.

Isolation of MES Market Data

To prevent cross-contamination and ensure high performance for MES futures, market data was migrated from a unified mkt_futures_1h table into isolated tables like mes_prices_1h. This was enforced via database-level check constraints.

This diagram represents the isolation logic implemented to separate Micro E-mini S&P 500 data from other futures contracts.
Sources: prisma/migrations/20260212131500_mes_isolated_price_tables/migration.sql:63-95

Economic Table Consolidation

The project transitioned from 8 individual economic tables (e.g., econ_rates_1d, econ_labor_1d) to a consolidated econ_observations_1d structure using an EconCategory enum for discrimination.

Enum Category	Previous Table Source
RATES	econ_rates_1d
MONEY	econ_yields_1d, econ_money_1d
FX	econ_fx_1d
VOLATILITY	econ_vol_indices_1d
LABOR	econ_labor_1d
Sources: prisma/migrations/20260213110000_comprehensive_schema_refactor/migration.sql:102-184

Reconciliation and Validation

Post-migration, the system uses reconciliation scripts to ensure that observed data aligns with the central EconomicSeries catalog. This process identifies missing series or category mismatches and can optionally fix them.

Reconciliation Logic

1. Observe: Fetch distinct seriesId values from all domain-specific tables.
2. Compare: Check against the EconomicSeries catalog for missing entries.
3. Resolve: If --fix-category is provided, update the catalog to match observed data.
   Sources: scripts/reconcile-economic-series.ts:108-144

The reconciliation process maintains a synchronized mapping between raw ingested data and the system's metadata registry.
Sources: scripts/reconcile-economic-series.ts:130-170

Production Safety Rules

The project defines surgical rules in cubic.yaml to prevent data corruption during migrations and ingestion runs:

• Append-only Tables: Time-series tables (mkt_futures_mes_15m, 1h, 1d) are strictly append-only. DELETE or UPDATE operations are flagged as critical violations.
• Batch Limits: Prisma batch operations (e.g., createMany) must not exceed 100 rows to ensure compatibility with Prisma Accelerate.
• Deduplication: Migrations and scripts must use skipDuplicates: true and rely on rowHash or unique constraints (e.g., symbolCode, timeframe, timestamp) to prevent duplicate candles.
  Sources: cubic.yaml:126-137, cubic.yaml:184-192, prisma/migrations/20260213110000_comprehensive_schema_refactor/migration.sql:240-244

Conclusion

Migration Management in Rabid Raccoon is a multi-layered system that combines Prisma's automated schema tracking with custom SQL refactoring scripts. By enforcing strict isolation for MES market data, utilizing high-precision Decimal types, and employing post-migration reconciliation scripts, the project maintains a reliable foundation for its high-frequency trading and AI forecasting modules.
Sources: README.md:92-111, prisma/migrations/20260213110000_comprehensive_schema_refactor/migration.sql:1-12