scripts/migrations/README.md

Database & Data Migrations

This directory contains migration scripts for schema changes and data transformations.

Available Migrations

migrate_state_naming.py

Purpose: Standardize state field naming across all database tables and parquet files

What it does:

• Renames state (2-char codes) → state_code
• Renames state_name (full names) → state
• Renames state_abbr → state_code
• Adds missing state (full name) columns where needed
• Populates full state names from codes using lookup table

Usage:

# Dry run (recommended first)
python scripts/migrations/migrate_state_naming.py --dry-run --all

# Migrate database tables only
python scripts/migrations/migrate_state_naming.py --tables

# Migrate parquet files only
python scripts/migrations/migrate_state_naming.py --parquets

# Migrate everything
python scripts/migrations/migrate_state_naming.py --all

Safety:

• Always runs in dry-run mode by default if no target specified
• Creates .parquet.backup files before modifying parquet files
• Shows all changes before applying them
• Can be run multiple times safely (idempotent)

Affected Tables:

• jurisdiction
• contact
• event
• organization_nonprofit
• bills_search
• bill_map_aggregate
• jurisdictions_details_search
• zip_county_mapping
• • 8 more tables with state columns

Affected Files:

• data/gold/states/{STATE}/*.parquet (all state-specific parquets)
• data/gold/jurisdictions_details.parquet

Best Practices

Before Running Migrations

1. Backup your database:

   pg_dump -h localhost -p 5433 -U postgres open_navigator > backup_$(date +%Y%m%d).sql
   

2. Test in dry-run mode:

   python scripts/migrations/migrate_state_naming.py --dry-run --all
   

3. Review the changes:

   • Check the migration plan
   • Verify table/file identification
   • Confirm backup locations

After Running Migrations

1. Verify the migration:

   -- Check table structure
   \d+ jurisdiction
   
   -- Verify data
   SELECT state_code, state, COUNT(*) 
   FROM jurisdiction 
   GROUP BY state_code, state 
   LIMIT 10;
   

2. Test parquet files:

   import pandas as pd
   df = pd.read_parquet('data/gold/states/AL/contact_official.parquet')
   print(df.columns.tolist())
   print(df[['state_code', 'state']].drop_duplicates())
   

3. Update dependent code:

   • Search for: WHERE state =
   • Update API endpoints
   • Update frontend filters
   • Update data loading scripts

Creating New Migrations

Template

#!/usr/bin/env python3
"""
Migration: [Brief description]

Changes:
- [Change 1]
- [Change 2]

Usage:
    python scripts/migrations/migrate_xxx.py --dry-run
    python scripts/migrations/migrate_xxx.py --apply
"""
import os
import sys
from pathlib import Path
import psycopg2
from loguru import logger
from dotenv import load_dotenv

load_dotenv()

DATABASE_URL = os.getenv('NEON_DATABASE_URL_DEV', 
                         'postgresql://postgres:password@localhost:5433/open_navigator')

class XXXMigrator:
    def __init__(self, dry_run: bool = True):
        self.dry_run = dry_run
        
    def migrate(self):
        logger.info("Starting migration...")
        conn = psycopg2.connect(DATABASE_URL)
        cursor = conn.cursor()
        
        try:
            # Migration logic here
            if not self.dry_run:
                conn.commit()
            else:
                conn.rollback()
        finally:
            cursor.close()
            conn.close()

if __name__ == '__main__':
    import argparse
    parser = argparse.ArgumentParser()
    parser.add_argument('--dry-run', action='store_true', default=True)
    parser.add_argument('--apply', action='store_true')
    args = parser.parse_args()
    
    migrator = XXXMigrator(dry_run=not args.apply)
    migrator.migrate()

Naming Convention

• Use descriptive names: migrate_{what}_{when}.py
• Include date for tracking: migrate_state_naming_20260503.py
• Keep migrations in order: 001_, 002_, etc.

Migration History

Date	Script	Description	Status
2026-05-03	migrate_state_naming.py	Standardize state field naming	✅ Ready

Troubleshooting

Migration failed mid-way

1. Check the error message
2. Restore from backup:

   psql -h localhost -p 5433 -U postgres open_navigator < backup_20260503.sql
   

3. Fix the issue
4. Run migration again

Parquet file corruption

1. Backups are created as .parquet.backup
2. Restore:

   mv file.parquet file.parquet.failed
   mv file.parquet.backup file.parquet
   

Dependent code breaking

1. Search for state field references:

   grep -r "WHERE state = " api/ scripts/ neon/
   

2. Update to use state_code:

   # Before
   query = "SELECT * FROM jurisdictions WHERE state = 'AL'"
   
   # After
   query = "SELECT * FROM jurisdictions WHERE state_code = 'AL'"
   

See Also

• State Field Naming Standard
• GitHub Copilot Instructions
• Database Schema