Skip to content

refactor(history): optimize record history system with unified v2 architecture

Laxman Reddy requested to merge refactor/optimize-record-history-system into develop

Refactoring Request Template

Title (semantic)

refactor(history): optimize record history system with unified v2 architecture

Description

This merge request refactors and optimizes the Record History System by replacing the existing multi-table history architecture with a unified, scalable, and performance-oriented V2 design.

The previous implementation relied on multiple history-related tables for change tracking, field-level history, snapshots, and version management. This created storage duplication, complex joins, slower query performance, and increased maintenance complexity.

This refactor introduces a consolidated history system that improves maintainability, reduces storage overhead, simplifies history retrieval, and provides a safer long-term migration path while maintaining backward compatibility.

Key Changes Implemented

Unified History Architecture

  • Added record_history_v2 as the primary history tracking table.
  • Consolidated history and field-level changes into a single JSONB-based structure.
  • Reduced dependency on multiple joins for history retrieval.

Version Tracking Improvements

  • Added record_version_v2 for lightweight version aggregation.
  • Tracks:
    • current version
    • total changes
    • last modified user
    • edit statistics

Major Snapshot Support

  • Added record_major_snapshot to store periodic full snapshots.
  • Snapshot creation occurs only at major versions to reduce storage usage.

Archive Strategy

  • Added archive models and archive service support.
  • Supports moving older history data into archive tables.
  • Improves long-term scalability and database performance.

Migration Support

  • Added migration scripts for:
    • V2 history schema creation
    • archive table creation
    • performance indexes
  • Added migration validation utilities to verify integrity between old and new systems.
  • Supports reversible downgrade path.

Performance Optimizations

  • Added composite indexes for history lookup.
  • Added partial recent-history indexes for optimized recent activity queries.
  • Improved query efficiency for record timeline retrieval.

Service Layer Refactor

  • Added RecordHistoryV2Service.
  • Added RecordHistoryArchiveService.
  • Unified history write logic into a single flow.
  • Supports:
    • JSON diff generation
    • version increment handling
    • snapshot creation
    • transaction-safe updates

Validation & Testing

  • Added archive service tests.
  • Added V2 history service tests.
  • Total test coverage includes:
    • history creation
    • JSON diff validation
    • archive flow
    • dry-run archive mode
    • migration validation
    • batch processing logic

Backward Compatibility

  • Existing tables remain preserved.
  • Changes are additive and non-destructive.
  • Existing application flow remains compatible during migration period.

Runtime Validation

Validated through:

  • full test execution
  • migration upgrade/downgrade cycle
  • service execution checks
  • import validation
  • model registration validation

Checklist

  • Code has been refactored for clarity, maintainability, and performance.
    • No breaking changes introduced to existing functionality.
    • Existing tables preserved for backward compatibility.
    • All tests are passing.
    • Migration scripts are reversible.
    • Performance indexes added.
    • Archive strategy implemented.
    • Validation scripts added.
    • Code adheres to project coding standards.

Related Issue(s)

Closes #94

Merge request reports