Migration Guide

Database Migration Guide

Adding Evaluation Metrics (f1_score, previous_precision)

This migration adds two new columns to the rule_evaluations table:

• f1_score: F1-score metric (harmonic mean of precision and recall)
• previous_precision: Previous precision value for drift detection

Running the Migration

Option 1: Using Makefile

make migrate-db

Option 2: Direct execution

poetry run python scripts/migrate_add_evaluation_metrics.py

Checking Migration Status

To verify that the migration was successful:

make check-db-schema

Or directly:

poetry run python scripts/check_db_schema.py

What the Migration Does

1. Checks existing columns: Verifies if f1_score and previous_precision already exist
2. Adds missing columns: Adds columns only if they don't exist (idempotent)
3. Supports both SQLite and PostgreSQL: Automatically detects database type and uses appropriate SQL

Migration Safety

• Idempotent: Safe to run multiple times
• Non-destructive: Only adds columns, doesn't modify existing data
• Backward compatible: Existing code continues to work (columns are nullable)

After Migration

Once migration is complete:

• New evaluations will automatically include f1_score and previous_precision
• Existing evaluations will have NULL values for these fields (which is fine)
• Drift detection will work for new evaluations

Troubleshooting

Error: "table rule_evaluations does not exist"

• Run init_db() first to create tables
• Or ensure your database is properly initialized

Error: "column already exists"

• This is normal if migration was already run
• Migration is idempotent and handles this case

PostgreSQL permission errors

• Ensure database user has ALTER TABLE permissions
• Check database connection settings

Manual Migration (if needed)

If automatic migration fails, you can run SQL manually:

SQLite:

ALTER TABLE rule_evaluations ADD COLUMN f1_score REAL;
ALTER TABLE rule_evaluations ADD COLUMN previous_precision REAL;

PostgreSQL:

ALTER TABLE rule_evaluations ADD COLUMN f1_score FLOAT;
ALTER TABLE rule_evaluations ADD COLUMN previous_precision FLOAT;