CarrierServiceMapping Database Schema

Database schema documentation for the carrier service mapping tables introduced in Phase 37.

Overview

The carrier service mapping feature uses three tables:

Table	Purpose
`cat_carrier_service_mapping`	Maps ServiceTemplates to carrier PPCs
`cat_carrier_bundle`	Defines carrier-specific service bundles
`cat_carrier_bundle_member`	Junction table linking bundles to mappings

cat_carrier_service_mapping

Maps vendor-agnostic ServiceTemplates to carrier-specific Product Plan Codes (PPCs).

DDL

CREATE TABLE cat_carrier_service_mapping (
    -- Primary key
    id BIGINT PRIMARY KEY,
    version INT,

    -- BusinessEntity columns
    code VARCHAR(255) NOT NULL,
    description VARCHAR(255),

    -- ServiceTemplate link
    service_template_id BIGINT NOT NULL,

    -- Carrier identification
    carrier_code VARCHAR(50) NOT NULL,
    product_plan_code VARCHAR(100) NOT NULL,
    product_type VARCHAR(50),
    usage_id VARCHAR(100),

    -- Optus-specific hierarchy
    pass_code INT,
    is_parent_ppc BOOLEAN NOT NULL DEFAULT FALSE,
    parent_ppc_code VARCHAR(100),

    -- Network configuration (JSON)
    network_config TEXT,

    -- Audit columns
    creator VARCHAR(100),
    created TIMESTAMP,
    updater VARCHAR(100),
    updated TIMESTAMP,

    -- Constraints
    CONSTRAINT cat_carrier_service_mapping_pkey PRIMARY KEY (id),
    CONSTRAINT uk_csm_code UNIQUE (code),
    CONSTRAINT uk_csm_carrier_ppc UNIQUE (carrier_code, product_plan_code),
    CONSTRAINT fk_csm_service_template
        FOREIGN KEY (service_template_id)
        REFERENCES cat_service_template(id)
        ON DELETE CASCADE
);

-- Sequence
CREATE SEQUENCE cat_carrier_service_mapping_seq START 1 INCREMENT 1;

Indexes

-- Fast lookup by service template
CREATE INDEX idx_csm_service_template
    ON cat_carrier_service_mapping(service_template_id);

-- Filter by carrier
CREATE INDEX idx_csm_carrier_code
    ON cat_carrier_service_mapping(carrier_code);

-- Lookup by PPC
CREATE INDEX idx_csm_product_plan_code
    ON cat_carrier_service_mapping(product_plan_code);

-- Find child PPCs by parent
CREATE INDEX idx_csm_parent_ppc
    ON cat_carrier_service_mapping(parent_ppc_code);

-- Find parent PPCs for carrier (Optus hierarchy)
CREATE INDEX idx_csm_carrier_parent
    ON cat_carrier_service_mapping(carrier_code, is_parent_ppc);

Column Details

Column	Type	Nullable	Description
`id`	BIGINT	No	Auto-generated primary key
`version`	INT	Yes	Optimistic locking version
`code`	VARCHAR(255)	No	Unique business identifier
`description`	VARCHAR(255)	Yes	Human-readable description
`service_template_id`	BIGINT	No	FK to cat_service_template
`carrier_code`	VARCHAR(50)	No	Carrier identifier (optus, telstra, etc.)
`product_plan_code`	VARCHAR(100)	No	Carrier-specific PPC
`product_type`	VARCHAR(50)	Yes	Product classification
`usage_id`	VARCHAR(100)	Yes	CDR usage identifier
`pass_code`	INT	Yes	Optus hierarchy: 0/1/2
`is_parent_ppc`	BOOLEAN	No	Default FALSE
`parent_ppc_code`	VARCHAR(100)	Yes	Parent PPC reference
`network_config`	TEXT	Yes	JSON configuration

cat_carrier_bundle

Defines carrier-specific service bundles.

DDL

CREATE TABLE cat_carrier_bundle (
    -- Primary key
    id BIGINT PRIMARY KEY,
    version INT,

    -- BusinessEntity columns
    code VARCHAR(255) NOT NULL,
    description VARCHAR(255),

    -- Bundle identification
    bundle_code VARCHAR(50) NOT NULL,
    carrier_code VARCHAR(50) NOT NULL,

    -- Primary PPC for bundle
    primary_ppc_code VARCHAR(100),

    -- Service classification
    service_type VARCHAR(32),
    billing_type VARCHAR(32),

    -- Audit columns
    creator VARCHAR(100),
    created TIMESTAMP,
    updater VARCHAR(100),
    updated TIMESTAMP,

    -- Constraints
    CONSTRAINT cat_carrier_bundle_pkey PRIMARY KEY (id),
    CONSTRAINT uk_cb_code UNIQUE (code),
    CONSTRAINT uk_cb_bundle_code UNIQUE (bundle_code)
);

-- Sequence
CREATE SEQUENCE cat_carrier_bundle_seq START 1 INCREMENT 1;

Indexes

CREATE INDEX idx_cb_carrier ON cat_carrier_bundle(carrier_code);
CREATE INDEX idx_cb_primary_ppc ON cat_carrier_bundle(primary_ppc_code);
CREATE INDEX idx_cb_service_type ON cat_carrier_bundle(service_type);

cat_carrier_bundle_member

Junction table linking carrier bundles to carrier service mappings.

DDL

CREATE TABLE cat_carrier_bundle_member (
    -- Foreign keys (composite primary key)
    carrier_bundle_id BIGINT NOT NULL,
    carrier_service_mapping_id BIGINT NOT NULL,

    -- Optus-specific pass_code
    pass_code INT,

    -- Ordering and requirements
    sort_order INT NOT NULL DEFAULT 0,
    is_mandatory BOOLEAN NOT NULL DEFAULT TRUE,

    -- Composite primary key
    CONSTRAINT cat_carrier_bundle_member_pkey
        PRIMARY KEY (carrier_bundle_id, carrier_service_mapping_id),

    -- Foreign keys
    CONSTRAINT fk_cbm_carrier_bundle
        FOREIGN KEY (carrier_bundle_id)
        REFERENCES cat_carrier_bundle(id)
        ON DELETE CASCADE,

    CONSTRAINT fk_cbm_carrier_service_mapping
        FOREIGN KEY (carrier_service_mapping_id)
        REFERENCES cat_carrier_service_mapping(id)
        ON DELETE CASCADE
);

Indexes

CREATE INDEX idx_cbm_bundle ON cat_carrier_bundle_member(carrier_bundle_id);
CREATE INDEX idx_cbm_mapping ON cat_carrier_bundle_member(carrier_service_mapping_id);
CREATE INDEX idx_cbm_sort_order ON cat_carrier_bundle_member(carrier_bundle_id, sort_order);

Migration

Liquibase Changeset

The migration is defined in: commerce/opencell-core/opencell-model/src/main/db_resources/changelog/staqr/structure-carrier-service-mapping.xml

Changesets:

ID	Description
`staqr-carrier-service-mapping-001`	Create sequence
`staqr-carrier-service-mapping-002`	Create mapping table
`staqr-carrier-service-mapping-003`	Add foreign key
`staqr-carrier-service-mapping-004`	Create indexes
`staqr-carrier-service-mapping-005`	Create bundle sequence
`staqr-carrier-service-mapping-006`	Create bundle table
`staqr-carrier-service-mapping-007`	Create bundle indexes
`staqr-carrier-service-mapping-008`	Create member table
`staqr-carrier-service-mapping-009`	Add member foreign keys
`staqr-carrier-service-mapping-010`	Create member indexes

Running the Migration

# Liquibase update (Commerce container)
docker exec -it opencell-wildfly \
  /opt/jboss/wildfly/bin/jboss-cli.sh -c \
  --command="/subsystem=datasources/data-source=MEVEO:test-connection-in-pool"

# Verify migration applied
docker exec -it opencell-postgres \
  psql -U opencell -d opencell \
  -c "SELECT * FROM databasechangelog WHERE id LIKE 'staqr-carrier-service-mapping%';"

Rollback

Drop Tables (Reverse Order)

-- 1. Drop junction table first (no dependencies)
DROP TABLE IF EXISTS cat_carrier_bundle_member CASCADE;

-- 2. Drop bundle table
DROP TABLE IF EXISTS cat_carrier_bundle CASCADE;
DROP SEQUENCE IF EXISTS cat_carrier_bundle_seq;

-- 3. Drop mapping table
DROP TABLE IF EXISTS cat_carrier_service_mapping CASCADE;
DROP SEQUENCE IF EXISTS cat_carrier_service_mapping_seq;

Liquibase Rollback

# Rollback last 10 changesets
docker exec -it opencell-wildfly \
  /opt/jboss/wildfly/bin/liquibase.sh \
  --changeLogFile=db/changelog/db.changelog-master.xml \
  rollbackCount 10

Monitoring Queries

Health Check

-- Count mappings by carrier
SELECT carrier_code, COUNT(*) as mapping_count
FROM cat_carrier_service_mapping
GROUP BY carrier_code
ORDER BY carrier_code;

-- Find orphaned mappings (no service template)
SELECT csm.*
FROM cat_carrier_service_mapping csm
LEFT JOIN cat_service_template st ON csm.service_template_id = st.id
WHERE st.id IS NULL;

-- Find parent PPCs for Optus
SELECT code, product_plan_code, product_type
FROM cat_carrier_service_mapping
WHERE carrier_code = 'optus' AND is_parent_ppc = true
ORDER BY product_plan_code;

-- Find child PPCs missing parent reference
SELECT code, product_plan_code, pass_code, parent_ppc_code
FROM cat_carrier_service_mapping
WHERE pass_code = 2 AND parent_ppc_code IS NULL;

Bundle Composition

-- List bundle members with PPC details
SELECT
    cb.bundle_code,
    cb.carrier_code,
    cbm.sort_order,
    cbm.is_mandatory,
    csm.product_plan_code,
    csm.product_type
FROM cat_carrier_bundle cb
JOIN cat_carrier_bundle_member cbm ON cb.id = cbm.carrier_bundle_id
JOIN cat_carrier_service_mapping csm ON cbm.carrier_service_mapping_id = csm.id
ORDER BY cb.bundle_code, cbm.sort_order;

Performance Considerations

Index Usage: All foreign keys and common filter columns are indexed
CASCADE Delete: Deleting ServiceTemplate removes all mappings automatically
JSON Column: network_config is TEXT not JSONB for compatibility; parse in application
Sequence Performance: Sequences use INCREMENT 1 for predictable ordering

Carrier Service Mapping API - Developer API reference
Carrier Mappings Admin Guide - Administrator guide
Database Architecture - Overall database design (see install/architecture)

Overview​

cat_carrier_service_mapping​

DDL​

Indexes​

Column Details​

cat_carrier_bundle​

DDL​

Indexes​

cat_carrier_bundle_member​

DDL​

Indexes​

Migration​

Liquibase Changeset​

Running the Migration​

Rollback​

Drop Tables (Reverse Order)​

Liquibase Rollback​

Monitoring Queries​

Health Check​

Bundle Composition​

Performance Considerations​

Related Documentation​

Overview

cat_carrier_service_mapping

DDL

Indexes

Column Details

cat_carrier_bundle

DDL

Indexes

cat_carrier_bundle_member

DDL

Indexes

Migration

Liquibase Changeset

Running the Migration

Rollback

Drop Tables (Reverse Order)

Liquibase Rollback

Monitoring Queries

Health Check

Bundle Composition

Performance Considerations

Related Documentation