Migration Scripts¶

The PostgreSQL Permissions Model uses numbered SQL migration scripts to manage database schema evolution. This section covers how the migration system works, script numbering, and version management.

Migration Script Organization¶

Script Numbering System¶

Scripts in the GitHub repository follow a structured numbering pattern:

postgresql-permissions-model/
├── 000_create_database.sql          # Database creation (optional)
├── 001_create_basic_structure.sql   # Core schemas and extensions
├── 002_create_version_management.sql # Version tracking system
├── 003_create_helpers.sql           # Utility functions
├── 004_create_permissions.sql       # Main permissions system (~217KB)
├── 007_update_permissions_v1.sql    # First incremental update
├── 008_update_permissions_v2.sql    # Second incremental update
├── ...                              # More incremental updates
├── 024_update_permissions_vX.sql    # Latest incremental update
└── 99_fix_permissions.sql           # Post-update permission fixes

Script Categories¶

Bootstrap Scripts (000-004): - 000: Database creation (optional for existing databases) - 001: Core schemas (auth, internal, helpers, etc.) and PostgreSQL extensions - 002: Version management system with public.__version table - 003: Utility functions (helpers schema functions) - 004: Main permissions system (largest file, ~217KB)

Incremental Updates (007-024): - Version-specific updates and enhancements - New features and schema modifications - Bug fixes and optimizations - Each numbered sequentially as features are added

Post-Processing (99): - Permission fixes and finalizations - System cleanup and optimization - Run after all other migrations

Version Management System¶

Version Tracking¶

Each migration script uses the version management system:

-- Start of every migration script
SELECT * FROM public.start_version_update(
    '1.16',                              -- Version number
    'Description of update',             -- Short description
    'Optional longer description',       -- Detailed description (optional)
    'component_name'                     -- Component name (defaults to 'main')
);

-- Migration SQL content here
CREATE TABLE example_table (...);
-- More migration content...

-- End of every migration script
SELECT * FROM public.stop_version_update('1.16', 'component_name');

Check Current Version¶

-- Get current system version
SELECT public.get_version();

-- Check specific version status
SELECT public.check_version('1.16', 'main');

-- View all applied versions
SELECT
    component,
    version,
    title,
    execution_started,
    execution_finished
FROM public.__version
ORDER BY execution_started DESC;

Find Latest Version¶

-- Get the latest applied version
SELECT public.get_version() as current_version;

-- Get version details
SELECT
    version,
    title,
    description,
    execution_finished
FROM public.__version
WHERE component = 'main'
  AND execution_finished IS NOT NULL
ORDER BY execution_started DESC
LIMIT 1;

Running Migrations¶

Manual Execution¶

Run scripts in numerical order:

# Core system setup
psql -h localhost -U postgres -d mydb -f 000_create_database.sql
psql -h localhost -U postgres -d mydb -f 001_create_basic_structure.sql
psql -h localhost -U postgres -d mydb -f 002_create_version_management.sql
psql -h localhost -U postgres -d mydb -f 003_create_helpers.sql
psql -h localhost -U postgres -d mydb -f 004_create_permissions.sql

# Incremental updates (run all in sequence)
psql -h localhost -U postgres -d mydb -f 007_update_permissions_v1.sql
psql -h localhost -U postgres -d mydb -f 008_update_permissions_v2.sql
# ... continue with all numbered scripts through 024

# Final cleanup
psql -h localhost -U postgres -d mydb -f 99_fix_permissions.sql

Automated Execution¶

# Run specific range of migrations
./debee.ps1 -Operations updateDatabase -UpdateStartNumber 007 -UpdateEndNumber 024

# Run all migrations
./debee.ps1 -Operations fullService

# Run only new migrations (from specific number to latest)
./debee.ps1 -Operations updateDatabase -UpdateStartNumber 015

Migration Safety Features¶

Idempotent Execution¶

Scripts can be run multiple times safely:

-- Each script checks if already applied
SELECT public.check_version('1.16', 'main');

-- Version management prevents duplicate execution
-- If version already exists and completed, script exits safely

Error Handling¶

-- Migration scripts include error handling
DO $$
BEGIN
    -- Check if already applied
    IF public.check_version('1.16', 'main') THEN
        RAISE NOTICE 'Version 1.16 already applied, skipping';
        RETURN;
    END IF;

    -- Start version tracking
    PERFORM public.start_version_update('1.16', 'Update description', NULL, 'main');

    -- Migration content with error handling
    -- ... migration SQL here ...

    -- Mark as completed
    PERFORM public.stop_version_update('1.16', 'main');

EXCEPTION
    WHEN OTHERS THEN
        RAISE EXCEPTION 'Migration 1.16 failed: %', SQLERRM;
END $$;

Finding Migration Information¶

Repository Structure¶

Check the GitHub repository structure:

https://github.com/your-org/postgresql-permissions-model/

Scripts are in the root directory with clear numerical prefixes.

Version History¶

-- View complete migration history
SELECT
    component,
    version,
    title,
    description,
    execution_started,
    execution_finished,
    CASE
        WHEN execution_finished IS NOT NULL
        THEN 'Completed'
        ELSE 'In Progress'
    END as status
FROM public.__version
ORDER BY execution_started;

-- Check for failed migrations
SELECT *
FROM public.__version
WHERE execution_finished IS NULL
  AND execution_started < now() - interval '1 hour';

Current System Status¶

-- Get comprehensive system status
SELECT
    'Current Version' as info,
    public.get_version() as value
UNION ALL
SELECT
    'Total Migrations Applied',
    COUNT(*)::text
FROM public.__version
WHERE execution_finished IS NOT NULL
UNION ALL
SELECT
    'Latest Migration Date',
    MAX(execution_finished)::text
FROM public.__version;

Migration Best Practices¶

Before Running Migrations¶

Backup Database: Always backup before major migrations
Check Current Version: Know your starting point
Review Changes: Understand what each script does
Test Environment: Run on staging first

During Migrations¶

Follow Order: Run scripts in numerical sequence
Monitor Progress: Watch for errors or warnings
Check Logs: Review PostgreSQL logs for issues
Verify Completion: Confirm each script completes successfully

After Migrations¶

-- Verify final state
SELECT public.get_version();

-- Check all schemas created
SELECT schema_name
FROM information_schema.schemata
WHERE schema_name IN ('auth', 'helpers', 'const', 'internal', 'unsecure', 'error');

-- Verify core functions exist
SELECT routine_name
FROM information_schema.routines
WHERE routine_schema = 'auth'
  AND routine_name IN ('has_permission', 'register_user', 'create_tenant');

Troubleshooting Migrations¶

Common Issues¶

Script Not Found:

# Ensure you're in the correct directory
ls -la *.sql | head -10

# Run from repository root directory
cd postgresql-permissions-model

Version Already Applied:

-- Check if version was already applied
SELECT * FROM public.__version WHERE version = '1.16';

-- If needed, manually mark as not applied (CAUTION)
UPDATE public.__version
SET execution_finished = NULL
WHERE version = '1.16';

Migration Stuck:

-- Check for incomplete migrations
SELECT * FROM public.__version
WHERE execution_finished IS NULL;

-- If migration failed, clean up and retry
DELETE FROM public.__version
WHERE version = '1.16' AND execution_finished IS NULL;

What's Next¶

Learn about Schema Organization
Explore User Management functions
Understand Multi-Tenancy implementation