thrilltrack-explorer/docs/versioning/MIGRATION.md

Migration Guide

Migrating from JSONB entity_versions to relational version tables

Status

✅ Migration Complete - The relational versioning system is now fully active. Legacy system has been removed.

Overview

The old system stored versions in a single entity_versions table using JSONB:

• ❌ Not queryable
• ❌ No type safety
• ❌ Poor performance
• ❌ Complex RLS

The new system uses dedicated relational tables:

• ✅ Fully queryable
• ✅ Type-safe with foreign keys
• ✅ Indexed and performant
• ✅ Standard RLS policies

Migration Timeline

1. Phase 1: Create relational tables ✅ Complete
2. Phase 2: Enable triggers ✅ Complete
3. Phase 3: Dual-write period ✅ Complete
4. Phase 4: Archive legacy data ✅ Complete
5. Phase 5: Drop legacy tables & functions ✅ Complete
6. Phase 6: Update documentation & code ✅ Complete

Current State

• ✅ All versions written to relational tables
• ✅ Triggers active on all entity tables
• ✅ Automated cleanup scheduled via pg_cron
• ✅ Old entity_versions table archived to entity_versions_archive
• ✅ Legacy JSONB tables and functions removed
• ✅ src/lib/versioningHelpers.ts removed
• ✅ All code updated to use relational system

Completed Migration Phases

✅ Phase 1: New System Deployed

• Relational version tables created (park_versions, ride_versions, etc.)
• Triggers enabled on all entity tables
• RLS policies active and tested
• Frontend integrated with new hooks
• Complete documentation suite created

✅ Phase 2: Parallel Operation

• Both old and new systems existed side-by-side
• New triggers created versions in relational tables
• Old JSONB table received no new data
• Monitored for issues and edge cases

✅ Phase 3: Archive Legacy Data

• Archived old entity_versions to entity_versions_archive
• Verified data integrity and counts matched
• Archive table retained for audit purposes

✅ Phase 4: Drop Legacy System

• Dropped old entity_versions table
• Dropped old RPC functions (create_entity_version, compare_versions, etc.)
• Dropped auto_create_entity_version() trigger function
• Removed src/lib/versioningHelpers.ts file
• Updated all code to use relational system only
• Updated documentation

🔄 Phase 5: Ongoing Optimization

• Automated cleanup via pg_cron runs monthly
• Performance monitoring and index tuning as needed
• Documentation updates based on usage patterns
• Version retention policy: 50 versions per entity (configurable)

Backfill Script (Optional)

If you need to migrate historical JSONB versions to relational:

-- Backfill park versions
INSERT INTO park_versions (
  version_id, park_id, version_number, created_at, created_by,
  change_type, submission_id, is_current,
  name, slug, description, park_type, status,
  opening_date, opening_date_precision, closing_date, closing_date_precision,
  location_id, operator_id, property_owner_id,
  website_url, phone, email,
  banner_image_url, banner_image_id, card_image_url, card_image_id
)
SELECT
  ev.id, ev.entity_id, ev.version_number, ev.changed_at, ev.changed_by,
  ev.change_type, ev.submission_id, ev.is_current,
  ev.version_data->>'name',
  ev.version_data->>'slug',
  ev.version_data->>'description',
  ev.version_data->>'park_type',
  ev.version_data->>'status',
  (ev.version_data->>'opening_date')::date,
  ev.version_data->>'opening_date_precision',
  (ev.version_data->>'closing_date')::date,
  ev.version_data->>'closing_date_precision',
  (ev.version_data->>'location_id')::uuid,
  (ev.version_data->>'operator_id')::uuid,
  (ev.version_data->>'property_owner_id')::uuid,
  ev.version_data->>'website_url',
  ev.version_data->>'phone',
  ev.version_data->>'email',
  ev.version_data->>'banner_image_url',
  ev.version_data->>'banner_image_id',
  ev.version_data->>'card_image_url',
  ev.version_data->>'card_image_id'
FROM entity_versions ev
WHERE ev.entity_type = 'park'
ON CONFLICT DO NOTHING;

What Was Removed

The following tables, functions, and files have been permanently removed:

Tables:

• entity_versions (main JSONB table)
• entity_field_history (field-level change tracking)
• entity_relationships_history (relationship tracking)
• version_diffs (pre-computed diffs)

Functions:

• create_entity_version() - Created JSONB versions
• compare_versions() - Compared JSONB versions
• create_field_history_entries() - Created field history
• auto_create_entity_version() - Automatic versioning trigger

Files:

• src/lib/versioningHelpers.ts - Old helper functions

Archived:

• entity_versions_archive - Contains historical JSONB data for audit purposes

Field-Level History

Field-level history tracking (entity_field_history table) has been removed. To view field changes:

• Use the Version Comparison feature in the Version History tab
• Compare any two versions to see all field-level changes
• The FieldHistoryDialog component now shows a helpful message directing users to version comparison