thrilltrack-explorer/docs/versioning/README.md

Universal Versioning System

Complete documentation for the relational versioning system

Overview

The Universal Versioning System automatically tracks all changes to entities (parks, rides, companies, ride models) using a pure relational database structure. Every INSERT or UPDATE creates a timestamped version with full attribution and audit trail.

Key Features

✅ Automatic Version Creation - Triggers handle versioning transparently
✅ Pure Relational Structure - No JSONB, fully queryable and type-safe
✅ Full Audit Trail - User, timestamp, and submission tracking
✅ Version Comparison - Visual diff between any two versions
✅ Rollback Support - Restore to any previous version
✅ Moderation Integration - Links versions to content submissions

Why Relational Versioning?

Benefit	Description
Queryable	Filter and search across version fields efficiently with SQL
Type-safe	Foreign keys enforce referential integrity
Performant	Indexed columns enable fast queries
Secure	Row-Level Security at column level
Maintainable	Standard SQL operations, no JSONB parsing

Documentation Structure

Core Documentation

• ARCHITECTURE.md - System design and data flow
• SCHEMA.md - Database tables, triggers, and functions
• FRONTEND.md - React hooks and components

Integration Guides

• MODERATION.md - How versioning integrates with moderation flow
• MIGRATION.md - Migrating from old JSONB system

Reference

• API.md - Complete API reference for all functions and hooks
• BEST_PRACTICES.md - Guidelines and recommendations
• TROUBLESHOOTING.md - Common issues and solutions

Quick Start

For Developers

// Add version indicator to entity page
import { VersionIndicator } from '@/components/versioning/VersionIndicator';

<VersionIndicator 
  entityType="park" 
  entityId={park.id} 
  entityName={park.name}
/>

For Database Admins

-- Get all versions of a specific park
SELECT * FROM park_versions 
WHERE park_id = 'uuid-here' 
ORDER BY version_number DESC;

-- Compare two versions
SELECT * FROM get_version_diff('park', 'version-1-uuid', 'version-2-uuid');

For Moderators

When approving submissions, versions are automatically created with:

• Attribution to original submitter (not moderator)
• Link to content submission
• Full change tracking

Architecture at a Glance

graph TB
    A[User Submits Edit] --> B[Content Submission]
    B --> C[Moderator Approves]
    C --> D[Edge Function]
    D --> E[Set Session Variables]
    E --> F[UPDATE Entity Table]
    F --> G[Trigger Fires]
    G --> H[create_relational_version]
    H --> I[INSERT Version Table]
    I --> J[Version Created]

Supported Entities

Entity Type	Version Table	Main Table
park	park_versions	parks
ride	ride_versions	rides
company	company_versions	companies
ride_model	ride_model_versions	ride_models

Version Lifecycle

1. Creation - Entity INSERT triggers first version (version_number: 1)
2. Updates - Each UPDATE creates new version, increments version_number
3. Current Flag - Only latest version has is_current = true
4. Retention - Old versions retained (configurable cleanup)
5. Rollback - Any version can be restored, creates new version

Security Model

Row-Level Security Policies

• Public - Can view current versions only (is_current = true)
• Moderators - Can view all versions
• Users - Can view versions they created
• System - Can create versions via triggers

Session Variables

The system uses PostgreSQL session variables for attribution:

• app.current_user_id - Original submitter (not moderator)
• app.submission_id - Link to content_submissions record

Performance Considerations

• Indexes - All version tables have indexes on entity_id, created_at, version_number
• Cleanup - Use cleanup_old_versions() to retain only N recent versions
• Queries - Version queries are fast due to proper indexing

Next Steps

1. Read ARCHITECTURE.md for system design details
2. Review SCHEMA.md for database structure
3. Check FRONTEND.md for React integration
4. See API.md for complete function reference

Support

For issues or questions:

• Check TROUBLESHOOTING.md first
• Review BEST_PRACTICES.md for guidelines
• Consult main project documentation

---

Status: ✅ Production Ready
Last Updated: 2025-10-15
Version: 1.0.0