Database Change Management Guide

← Back | Home

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:

  1. Impact Assessment

    • Identify affected tables and relationships

    • Determine backward compatibility requirements

    • Assess performance implications

    • Plan rollback strategies

  2. Documentation Requirements

    • Document business justification for changes

    • Create data migration plans if needed

    • Update entity relationship diagrams

    • Prepare testing strategies

  3. 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:

  1. Automated Validation: CI/CD pipeline validates syntax

  2. Peer Review: Senior developer reviews changes

  3. DBA Review: Complex changes reviewed by database administrator

  4. 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:

  1. Structured Change Process: Clear workflow for all database modifications

  2. Multi-Database Support: Consistent approach across ACS, IOTW, and QoE databases

  3. Version Control Integration: Complete tracking of database evolution

  4. Rollback Capabilities: Safe deployment with rollback strategies

  5. Performance Optimization: Guidelines for efficient schema changes

  6. 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.
← Back | Home