Uploaded image for project: 'Subscription Watch'
  1. Subscription Watch
  2. SWATCH-4042

Move billable usage migration scripts from swatch-database-migrations to swatch-billable-usage

XMLWordPrintable

    • Icon: Task Task
    • Resolution: Unresolved
    • Icon: Critical Critical
    • None
    • None
    • swatch-billable-usage
    • None

      Move the tables that are owned by the swatch-billable-usage service from swatch-database-migrations to swatch-billable-usage. The only tables are:

      • billable_usage_remittance

      This table is created by the following scripts:

      Date File Changesets
      2022-05-23 202205231334-create-billable-usage-tracking-table.xml 202205231334
      2022-06-02 202206021315-add-version-to-billable-usage-tracking-table.xml 202206021315
      2022-09-30 202209301230-migrate-org-id-to-billable-usage-table.xml 202209301230-1
      2022-11-18 202211181230-update-primary-key-migrate-org-id-to-billable-usage-table.xml 202211181230-1, 202211181230-2, 202211181230-3, 202211181230-4
      2022-12-01 202212011334-add-billing-factor-column-to-remittance-table.xml 202212011334-1
      2023-05-23 202305231003-update-remittance-table-for-history.xml 202305231003-1
      2023-05-26 202305261600-rollback-update-remittance-table-for-history.xml 202305261600-1
      2023-07-13 202307131456-remove-granularity-from-billable-usage-remittance.xml 202307131456-1
      2024-01-15 202401151414-add-retry_after-column-to-billable_usage_remittance-table.xml 202401151414-01
      2024-02-05 202402051200-add-id-and-hardware-columns-to-billable_usage_remittance-table.xml 202401151414-01
      2024-03-13 202403131500-change-pk-from-billable-usage-remittance.xml 202403131500-1, 202403131500-2, 202403131500-3, 202403131500-4, 202403131500-5
      2024-10-02 202410021300-update_usages_to_unknown.xml 202410021300-01
      2024-10-21 202410211200-retry-after-billable-usage-index.xml 202410211200-01
      2024-10-24 202410241408-clear-retryable.xml 202410241408-01
      2024-12-09 202412091327-drop-hardware-measurement-type-from-billable-usage-remittance.xml 202412091327-01
      2025-04-29 202504291554-add-updated-at-column-to-billable-usage-remittance.xml 202504291554-01

      We need to consolidate all the changes from the above scripts into only one:

      <?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.20.xsd">
      
        <!-- 
          CONSOLIDATED BILLABLE_USAGE_REMITTANCE TABLE CREATION SCRIPT
          
          This script consolidates all changes made to the billable_usage_remittance table
          from the original creation (2022-05-23) through the latest changes (2025-04-29).
          
          Running this single script will create the table in its final state, equivalent 
          to running all the individual migration scripts in chronological order.
      
        <changeSet id="XXX-001" author="AUTHOR">
          <comment>Create billable_usage_remittance table with all consolidated changes</comment>
          <createTable tableName="billable_usage_remittance">
            <column name="account_number" type="VARCHAR(32)">
              <constraints nullable="true"/>
            </column>
            <column name="org_id" type="VARCHAR(32)">
              <constraints nullable="false"/>
            </column>
            <column name="product_id" type="VARCHAR(32)">
              <constraints nullable="false"/>
            </column>
            <column name="metric_id" type="VARCHAR(255)">
              <constraints nullable="false"/>
            </column>
            <column name="accumulation_period" type="VARCHAR(255)">
              <constraints nullable="false"/>
            </column>
            <column name="sla" type="VARCHAR(255)">
              <constraints nullable="false"/>
            </column>
            <column name="usage" type="VARCHAR(255)">
              <constraints nullable="false"/>
            </column>
            <column name="billing_provider" type="VARCHAR(255)">
              <constraints nullable="false"/>
            </column>
            <column name="billing_account_id" type="VARCHAR(255)">
              <constraints nullable="false"/>
            </column>
            
            <column name="remitted_value" type="double precision">
              <constraints nullable="false"/>
            </column>
            <column name="remittance_date" type="TIMESTAMP WITH TIME ZONE">
              <constraints nullable="false"/>
            </column>
            
            <column name="billing_factor" type="double precision" defaultValueNumeric="1.0">
              <constraints nullable="false"/>
            </column>
            
            <column name="version" type="INT" defaultValueNumeric="1">
              <constraints nullable="false"/>
            </column>
            
            <column name="retry_after" type="TIMESTAMP WITH TIME ZONE">
              <constraints nullable="true"/>
            </column>
            
            <column name="tally_id" type="UUID">
              <constraints nullable="true"/>
            </column>
            
            <column name="uuid" type="UUID">
              <constraints nullable="false"/>
            </column>
            
            <column name="billed_on" type="TIMESTAMP WITH TIME ZONE">
              <constraints nullable="true"/>
            </column>
            <column name="error_code" type="VARCHAR(255)">
              <constraints nullable="true"/>
            </column>
            <column name="status" type="VARCHAR(255)">
              <constraints nullable="true"/>
            </column>
            
            <column name="updated_at" type="timestamp">
              <constraints nullable="true"/>
            </column>
          </createTable>
          
          <addPrimaryKey constraintName="billable_usage_remittance_pkey"
                         tableName="billable_usage_remittance"
                         columnNames="uuid"/>
          
          <addUniqueConstraint columnNames="org_id, product_id, metric_id, accumulation_period, sla, usage, billing_provider, billing_account_id, remittance_date"
                               constraintName="billable_usage_remittance_unique" 
                               tableName="billable_usage_remittance"/>
          
          <createIndex tableName="billable_usage_remittance" indexName="billable_usage_retry_after_idx">
            <column name="retry_after"/>
          </createIndex>
          
        </changeSet>
      </databaseChangeLog>
      

      Note that the above script is a proposal and untested.

      We need to have this script in swatch-billable-usage and configure the Quarkus liquibase extension to run it (same solution than swatch-contracts). The liquibase configuration for swatch-billable-usage should be:

      • database change log table name: databasechangelog_swatch_billable_usage
      • database change log lock table name: databasechangeloglock_swatch_billable_usage

      Acceptance Criteria

      • Add the new script into the swatch-billable-usage and configure the liquibase Quarkus extension in swatch-billable-usage to perform the migrations
        • How to test it:
          • (1) run the old migrations using the swatch-database,
          • (2) next start the swatch-billable-usage service to run the new migrations.
          • Then, the swatch-billable-usage service should start ok, and no migrations at (2) should be done. Also, the new liquibase table "databasechangelog_swatch_billable_usage" should be created with new migration applied.
      • Update the core liquibase migrations listed in the above table to not perform the migrations again.
        • How to test it:
          • (1) run the new migrations starting the swatch-billable-usage service,
          • (2) next run the swatch-database migrations.
          • Then, the swatch-tally service should start ok, and no migrations at (2) should be done.
      • Remove the init-container to perform the migrations in clowder for the swatch-billable-usage service.
      • swatch-db-changelog-cleanup job available for swatch-billable-usage to release stuck Liquibase locks

      Useful links

              jcarvaja@redhat.com Jose Carvajal Hilario
              jcarvaja@redhat.com Jose Carvajal Hilario
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

                Created:
                Updated: