Database Change Management Guide

This comprehensive guide outlines the complete process for managing database schema changes in the oneiot_web_backend project using Liquibase. It ensures version control, consistency, and traceability of all database modifications across different environments and datasources.

1. Database Architecture Overview

1.1. Multi-Datasource Environment

The oneiot_web_backend project operates with three distinct databases:

ACS Database (acs): Primary database for device management, configurations, profiles, and IoT operations.
IOTW Database (iotw): Application-specific database for UI infrastructure, user management, and system settings.
QoE Database (qoe): External ClickHouse database for Quality of Experience monitoring and analytics data.

1.2. Liquibase Integration Benefits

Version Control:

All database changes tracked in source control
Complete history of schema evolution
Rollback capabilities for failed deployments

Environment Consistency:

Identical schema across development, staging, and production
Automated deployment of database changes
Prevention of manual database drift

Team Collaboration:

Clear change attribution and documentation
Conflict resolution for concurrent schema changes
Review process for all database modifications

2. Liquibase Framework Foundation

2.1. Core Concepts

ChangeSet: Atomic unit of database change containing one or more database operations.

ChangeLog: Collection of changeSets that defines the complete database evolution.

DatabaseChangeLog: Master file that orchestrates all changeLogs for a specific database.

Change Tracking: Liquibase maintains internal tables (DATABASECHANGELOG, DATABASECHANGELOGLOCK) to track applied changes.

2.2. Project Structure

PROJECT_ROOT/
├── services/
│   └── src/
│       └── main/
│           └── resources/
│               └── db.changelog/
│                   ├── acs/
│                   │   ├── db.changelog-master.xml
│                   │   ├── db.changelog-initial-schema.xml
│                   │   ├── db.changelog-device-management.xml
│                   │   └── db.changelog-profile-system.xml
│                   ├── iotw/
│                   │   ├── db.changelog-master.xml
│                   │   ├── db.changelog-user-management.xml
│                   │   └── db.changelog-ui-customization.xml
│                   └── qoe/
│                       ├── db.changelog-master.xml
│                       └── db.changelog-metrics-tables.xml
└── ft_data/
    └── db.changelog/
        └── acs/
            └── db.changelog-group-update.xml    # Legacy location

New changeSets should be placed in services/src/main/resources/db.changelog/[DB]/ unless maintaining compatibility with existing deployment scripts.

3. Change Management Workflow

3.1. Planning Database Changes

Before Creating Changes:

Impact Assessment
- Identify affected tables and relationships
- Determine backward compatibility requirements
- Assess performance implications
- Plan rollback strategies
Documentation Requirements
- Document business justification for changes
- Create data migration plans if needed
- Update entity relationship diagrams
- Prepare testing strategies
Environment Coordination
- Coordinate with other developers working on schema
- Check for conflicting changes in progress
- Plan deployment windows for complex changes

3.2. ChangeSet Creation Process

3.2.1. Step 1: Create ChangeSet File

Naming Convention:

db.changelog-[feature-description].xml
db.changelog-[ticket-number]-[description].xml

Examples:
db.changelog-device-bulk-operations.xml
db.changelog-IOTW-1234-user-authentication.xml
db.changelog-qoe-metrics-enhancement.xml

File Creation:

# Navigate to appropriate database directory
cd services/src/main/resources/db.changelog/acs/

# Create new changeset file
touch db.changelog-device-bulk-operations.xml

3.2.2. Step 2: Write ChangeSet Content

Basic ChangeSet Structure:

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
                        http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.3.xsd">

    <changeSet id="device-bulk-operations-1" author="developer-name" context="dev,staging,prod">
        <comment>Create tables for device bulk operations feature</comment>

        <createTable tableName="bulk_operation">
            <column name="id" type="BIGINT" autoIncrement="true">
                <constraints primaryKey="true" nullable="false"/>
            </column>
            <column name="operation_type" type="VARCHAR(50)">
                <constraints nullable="false"/>
            </column>
            <column name="status" type="VARCHAR(20)">
                <constraints nullable="false"/>
            </column>
            <column name="created_date" type="TIMESTAMP" defaultValueComputed="CURRENT_TIMESTAMP">
                <constraints nullable="false"/>
            </column>
            <column name="completed_date" type="TIMESTAMP"/>
            <column name="created_by" type="VARCHAR(100)">
                <constraints nullable="false"/>
            </column>
        </createTable>

        <addForeignKeyConstraint
            baseTableName="bulk_operation"
            baseColumnNames="created_by"
            constraintName="fk_bulk_operation_user"
            referencedTableName="users"
            referencedColumnNames="username"/>
    </changeSet>

</databaseChangeLog>

Advanced ChangeSet Examples:

Table Creation with Indexes:

<changeSet id="device-indexing-1" author="developer-name">
    <comment>Add performance indexes for device queries</comment>

    <createIndex tableName="device" indexName="idx_device_manufacturer_model">
        <column name="manufacturer"/>
        <column name="model"/>
    </createIndex>

    <createIndex tableName="device" indexName="idx_device_status_last_seen">
        <column name="status"/>
        <column name="last_seen_date"/>
    </createIndex>
</changeSet>

Data Migration:

<changeSet id="device-status-migration-1" author="developer-name">
    <comment>Migrate device status values to new enum format</comment>

    <update tableName="device">
        <column name="status" value="ACTIVE"/>
        <where>status = 'online'</where>
    </update>

    <update tableName="device">
        <column name="status" value="INACTIVE"/>
        <where>status = 'offline'</where>
    </update>

    <rollback>
        <update tableName="device">
            <column name="status" value="online"/>
            <where>status = 'ACTIVE'</where>
        </update>
        <update tableName="device">
            <column name="status" value="offline"/>
            <where>status = 'INACTIVE'</where>
        </update>
    </rollback>
</changeSet>

Conditional Changes:

<changeSet id="device-column-optional-1" author="developer-name">
    <preConditions onFail="MARK_RAN">
        <not>
            <columnExists tableName="device" columnName="firmware_version"/>
        </not>
    </preConditions>

    <comment>Add firmware_version column if it doesn't exist</comment>

    <addColumn tableName="device">
        <column name="firmware_version" type="VARCHAR(50)"/>
    </addColumn>
</changeSet>

3.2.3. Step 3: Update Master ChangeLog

Master ChangeLog Structure:

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
                        http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.3.xsd">

    <!-- Initial Schema -->
    <include file="db/changelog/acs/db.changelog-initial-schema.xml"/>

    <!-- Core Features -->
    <include file="db/changelog/acs/db.changelog-device-management.xml"/>
    <include file="db/changelog/acs/db.changelog-profile-system.xml"/>

    <!-- New Features (append new includes here) -->
    <include file="db/changelog/acs/db.changelog-device-bulk-operations.xml"/>

</databaseChangeLog>

Include File Addition:

<!-- Add at the end of master changelog -->
<include file="db/changelog/acs/db.changelog-device-bulk-operations.xml"/>

4. Database-Specific Guidelines

4.1. ACS Database Changes

Common Change Types: - Device management table modifications - Configuration profile updates - Template and parameter schema changes - Monitoring and diagnostic table updates

Performance Considerations:

<changeSet id="acs-performance-1" author="developer-name">
    <comment>Add indexes for frequently queried device data</comment>

    <!-- Index for device lookup by manufacturer/model -->
    <createIndex tableName="device" indexName="idx_device_lookup">
        <column name="manufacturer"/>
        <column name="model"/>
        <column name="status"/>
    </createIndex>

    <!-- Partial index for active devices -->
    <sql>
        CREATE INDEX idx_active_devices
        ON device (last_seen_date)
        WHERE status = 'ACTIVE';
    </sql>
</changeSet>

Data Migration Patterns:

<changeSet id="acs-config-migration-1" author="developer-name">
    <comment>Migrate configuration data to new structure</comment>

    <!-- Create temporary table for data preservation -->
    <createTable tableName="device_config_backup">
        <column name="device_id" type="BIGINT"/>
        <column name="config_data" type="TEXT"/>
        <column name="created_date" type="TIMESTAMP"/>
    </createTable>

    <!-- Backup existing data -->
    <sql>
        INSERT INTO device_config_backup
        SELECT device_id, config_json, NOW()
        FROM device_configuration;
    </sql>

    <!-- Modify schema -->
    <addColumn tableName="device_configuration">
        <column name="config_version" type="VARCHAR(10)" defaultValue="1.0"/>
    </addColumn>

    <!-- Cleanup backup after successful migration -->
    <dropTable tableName="device_config_backup"/>
</changeSet>

4.2. IOTW Database Changes

User Management Changes:

<changeSet id="iotw-user-auth-1" author="developer-name">
    <comment>Enhance user authentication system</comment>

    <createTable tableName="user_session">
        <column name="session_id" type="VARCHAR(128)">
            <constraints primaryKey="true"/>
        </column>
        <column name="user_id" type="BIGINT">
            <constraints nullable="false"/>
        </column>
        <column name="created_date" type="TIMESTAMP" defaultValueComputed="CURRENT_TIMESTAMP"/>
        <column name="expires_date" type="TIMESTAMP">
            <constraints nullable="false"/>
        </column>
        <column name="ip_address" type="VARCHAR(45)"/>
        <column name="user_agent" type="TEXT"/>
    </createTable>

    <addForeignKeyConstraint
        baseTableName="user_session"
        baseColumnNames="user_id"
        constraintName="fk_session_user"
        referencedTableName="users"
        referencedColumnNames="id"/>

    <!-- Index for session cleanup -->
    <createIndex tableName="user_session" indexName="idx_session_expires">
        <column name="expires_date"/>
    </createIndex>
</changeSet>

UI Customization Changes:

<changeSet id="iotw-ui-themes-1" author="developer-name">
    <comment>Add support for user interface themes</comment>

    <createTable tableName="ui_theme">
        <column name="id" type="BIGINT" autoIncrement="true">
            <constraints primaryKey="true"/>
        </column>
        <column name="name" type="VARCHAR(50)">
            <constraints nullable="false" unique="true"/>
        </column>
        <column name="css_variables" type="JSON"/>
        <column name="is_default" type="BOOLEAN" defaultValue="false"/>
        <column name="is_active" type="BOOLEAN" defaultValue="true"/>
    </createTable>

    <addColumn tableName="user_preferences">
        <column name="theme_id" type="BIGINT"/>
    </addColumn>

    <addForeignKeyConstraint
        baseTableName="user_preferences"
        baseColumnNames="theme_id"
        constraintName="fk_user_theme"
        referencedTableName="ui_theme"
        referencedColumnNames="id"/>
</changeSet>

4.3. QoE Database Changes

Time-Series Data Optimization:

<changeSet id="qoe-metrics-optimization-1" author="developer-name">
    <comment>Optimize QoE metrics tables for time-series queries</comment>

    <!-- ClickHouse-specific table creation -->
    <sql>
        CREATE TABLE qoe_metrics_hourly
        (
            timestamp DateTime,
            device_id String,
            metric_name String,
            metric_value Float64,
            location String
        )
        ENGINE = MergeTree()
        PARTITION BY toYYYYMM(timestamp)
        ORDER BY (device_id, metric_name, timestamp)
        SETTINGS index_granularity = 8192;
    </sql>

    <!-- Create materialized view for aggregation -->
    <sql>
        CREATE MATERIALIZED VIEW qoe_metrics_daily_mv
        ENGINE = SummingMergeTree()
        PARTITION BY toYYYYMM(day)
        ORDER BY (device_id, metric_name, day)
        AS SELECT
            toDate(timestamp) as day,
            device_id,
            metric_name,
            avg(metric_value) as avg_value,
            max(metric_value) as max_value,
            min(metric_value) as min_value,
            count() as sample_count
        FROM qoe_metrics_hourly
        GROUP BY day, device_id, metric_name;
    </sql>
</changeSet>

5. Best Practices and Guidelines

5.1. ChangeSet Design Principles

Atomic Changes:

Each changeSet should be atomic (all-or-nothing)
Avoid mixing DDL and DML operations
Group related changes logically

Backward Compatibility:

New columns should be nullable or have defaults
Don’t drop columns immediately after adding new ones
Use feature toggles for structural changes

Performance Considerations:

Add indexes after data population
Use batch operations for large data changes
Consider table partitioning for time-series data

Documentation Standards:

Always include meaningful comments
Document business justification
Explain complex migration logic

5.2. Code Review Guidelines

ChangeSet Review Checklist:

ChangeSet ID is unique and descriptive
Author information is correct
Comments explain the purpose and impact
Syntax is valid and follows project standards
Rollback strategy is defined for complex changes
Performance impact has been considered
Backward compatibility is maintained
Testing strategy is documented

Review Process:

Automated Validation: CI/CD pipeline validates syntax
Peer Review: Senior developer reviews changes
DBA Review: Complex changes reviewed by database administrator
Testing Validation: QA team validates in staging environment

5.3. Migration Strategy Guidelines

Development Phase:

Create feature-specific changeSets
Test migrations frequently in local environment
Keep changeSets focused and atomic

Staging Phase:

Validate changeSets against production-like data
Performance test migrations with realistic data volumes
Practice rollback procedures

Production Phase:

Schedule migrations during maintenance windows
Monitor database performance during deployment
Validate functionality after migration
Have rollback plan ready

6. Summary

This comprehensive database change management strategy provides:

Structured Change Process: Clear workflow for all database modifications
Multi-Database Support: Consistent approach across ACS, IOTW, and QoE databases
Version Control Integration: Complete tracking of database evolution
Rollback Capabilities: Safe deployment with rollback strategies
Performance Optimization: Guidelines for efficient schema changes
Team Collaboration: Clear review and approval processes

Always test database changes in a development environment before applying to staging or production.

Never modify existing changeSets that have been applied to production. Create new changeSets to make additional modifications.