Service Hierarchy Troubleshooting Guide

Purpose: Diagnosis and resolution workflows for common service hierarchy issues.

---

Issue 1: Service Won't Activate

Symptoms

• "Activation failed" error in UI
• Service remains in PENDING status
• Error logs show validation failure

Diagnosis Tree

flowchart TD
    Start[Service Won't Activate] --> Q1{Is parent<br/>service active?}
    Q1 -->|No| A1[Activate parent first]
    Q1 -->|Yes| Q2{Does service have<br/>valid parent template?}
    Q2 -->|No| A2[Fix orphaned service<br/>Add parent template]
    Q2 -->|Yes| Q3{Are eligibility<br/>constraints met?}
    Q3 -->|No| A3[Check constraint requirements<br/>Customer tier, dependencies]
    Q3 -->|Yes| Q4{Is inventory available?<br/>For INVENTORY role only}
    Q4 -->|No| A4[Replenish inventory pool]
    Q4 -->|Yes| Q5{Check carrier integration}
    Q5 -->|Error| A5[Check carrier status<br/>Review error logs]
    Q5 -->|OK| A6[Check service config<br/>Review template settings]

    style A1 fill:#4CAF50
    style A2 fill:#FF5722
    style A3 fill:#FF9800
    style A4 fill:#2196F3
    style A5 fill:#9C27B0
    style A6 fill:#607D8B

Resolution Steps

1. Check Parent Service Status

-- Find service and parent status
SELECT
  si.id as service_instance_id,
  si.status,
  st.code as service_code,
  parent_si.status as parent_status,
  parent_st.code as parent_code
FROM billing_service_instance si
JOIN cat_service_template st ON si.service_template_id = st.id
LEFT JOIN billing_service_instance parent_si ON si.parent_service_id = parent_si.id
LEFT JOIN cat_service_template parent_st ON parent_si.service_template_id = parent_st.id
WHERE si.id = ?;

If parent is not ACTIVE: Activate parent first, then retry child activation.

2. Check Service Template Configuration

-- Check if service has parent template
SELECT
  code,
  role,
  parent_service_template_id,
  (SELECT code FROM cat_service_template WHERE id = st.parent_service_template_id) as parent_code
FROM cat_service_template st
WHERE code = ?;

If parent_service_template_id is NULL for non-PRIMARY: Service is orphaned. Fix template configuration.

3. Check Eligibility Constraints

-- List eligibility constraints for service
SELECT
  st.code,
  ec.constraint_type,
  ec.constraint_value,
  ec.required_service_code
FROM cat_service_template st
JOIN cat_eligibility_constraint ec ON st.id = ec.service_template_id
WHERE st.code = ?;

If constraints not met: Verify customer meets all requirements.

4. Check Inventory Availability (INVENTORY role)

-- Check inventory pool for service
SELECT
  ip.pool_name,
  ip.available_count,
  ip.total_count
FROM inventory_pool ip
JOIN cat_service_template st ON st.inventory_pool_id = ip.id
WHERE st.code = ?;

If available_count = 0: Replenish inventory pool before activation.

---

Issue 2: Children Not Auto-Activating

Symptoms

• Parent activates successfully
• MANDATORY children remain in PENDING
• Manual activation required for auto-services

Diagnosis Tree

flowchart TD
    Start[Children Not Auto-Activating] --> Q1{Is inclusion type<br/>MANDATORY?}
    Q1 -->|No| A1[Correct - OPTIONAL services<br/>require user selection]
    Q1 -->|Yes| Q2{Is auto_activate_children<br/>enabled on parent?}
    Q2 -->|No| A2[Enable auto_activate_children<br/>on parent template]
    Q2 -->|Yes| Q3{Are activation<br/>constraints blocking?}
    Q3 -->|Yes| A3[Review activation constraints<br/>Check timing/sequence rules]
    Q3 -->|No| Q4{Check activation order}
    Q4 -->|Wrong order| A4[Fix sort_order values]
    Q4 -->|Correct| A5[Check service processor logs]

    style A1 fill:#4CAF50
    style A2 fill:#FF5722
    style A3 fill:#FF9800
    style A4 fill:#2196F3
    style A5 fill:#607D8B

Resolution Steps

1. Verify Inclusion Type

SELECT code, inclusion_type
FROM cat_service_template
WHERE parent_service_template_id = (
  SELECT id FROM cat_service_template WHERE code = ?
);

Expected: MANDATORY services should auto-activate.

2. Check Parent Auto-Activation Setting

SELECT code, auto_activate_children
FROM cat_service_template
WHERE code = ?;

If false: Enable auto_activate_children = true on parent template.

3. Check Activation Order

SELECT code, sort_order, inclusion_type
FROM cat_service_template
WHERE parent_service_template_id = ?
ORDER BY sort_order;

Fix: Ensure sort_order follows logical sequence.

---

Issue 3: Eligibility Constraints Preventing Activation

Symptoms

• "Not eligible" message in UI
• Service greyed out in service configurator
• Customer cannot select service

Diagnosis Tree

flowchart TD
    Start[Eligibility Constraint Issue] --> Q1{Which constraint<br/>type is blocking?}
    Q1 -->|CUSTOMER_TIER| A1[Check customer tier<br/>Upgrade if needed]
    Q1 -->|REQUIRED_SERVICE| A2[Activate required service first]
    Q1 -->|EXCLUDED_SERVICE| A3[Deactivate conflicting service]
    Q1 -->|GEOGRAPHIC| A4[Check service area coverage]
    Q1 -->|TEMPORAL| A5[Check time-based restrictions]
    Q1 -->|UNKNOWN| A6[Check constraint configuration]

    style A1 fill:#2196F3
    style A2 fill:#4CAF50
    style A3 fill:#FF5722
    style A4 fill:#FF9800
    style A5 fill:#9C27B0
    style A6 fill:#607D8B

Resolution Steps

1. List All Constraints for Service

SELECT
  ec.constraint_type,
  ec.constraint_value,
  ec.required_service_code,
  ec.excluded_service_code,
  ec.min_customer_tier,
  ec.valid_from,
  ec.valid_to
FROM cat_eligibility_constraint ec
JOIN cat_service_template st ON ec.service_template_id = st.id
WHERE st.code = ?;

2. Check Customer's Current State

-- Customer tier
SELECT customer_tier FROM billing_customer WHERE id = ?;

-- Active services
SELECT st.code
FROM billing_service_instance si
JOIN cat_service_template st ON si.service_template_id = st.id
WHERE si.customer_id = ? AND si.status = 'ACTIVE';

3. Evaluate Each Constraint

Constraint Type	Check	Action
CUSTOMER_TIER	Customer tier >= required	Upgrade customer
REQUIRED_SERVICE	Required service active	Activate required first
EXCLUDED_SERVICE	Conflicting service active	Deactivate or choose alternative
GEOGRAPHIC	Service area includes customer	Check coverage map
TEMPORAL	Current time in valid range	Wait or adjust window

---

Issue 4: Lifecycle Inheritance Not Working

Symptoms

• Parent suspends but children remain active
• Parent terminates but children remain
• Manual cleanup required

Diagnosis Tree

flowchart TD
    Start[Lifecycle Inheritance Issue] --> Q1{Is inherit_parent_lifecycle<br/>enabled?}
    Q1 -->|No| A1[Enable inherit_parent_lifecycle<br/>on child template]
    Q1 -->|Yes| Q2{Check lifecycle_inheritance_type}
    Q2 -->|FULL| Q3{Is child status<br/>following parent?}
    Q2 -->|PARTIAL| A2[Review which events inherited]
    Q2 -->|NONE| A3[Change to FULL or PARTIAL]

    Q3 -->|No| Q4{Check lifecycle processor logs}
    Q3 -->|Yes| A4[Lifecycle working correctly]

    Q4 -->|Errors| A5[Fix processor configuration]
    Q4 -->|No errors| A6[Check timing/async issues]

    style A1 fill:#FF5722
    style A2 fill:#FF9800
    style A3 fill:#FF9800
    style A4 fill:#4CAF50
    style A5 fill:#9C27B0
    style A6 fill:#607D8B

Resolution Steps

1. Check Inheritance Configuration

SELECT
  code,
  inherit_parent_lifecycle,
  lifecycle_inheritance_type
FROM cat_service_template
WHERE code = ?;

Expected values:

• inherit_parent_lifecycle = true
• lifecycle_inheritance_type = 'FULL' (or 'PARTIAL' with explicit events)

2. Check Parent-Child Status Alignment

SELECT
  parent_si.status as parent_status,
  child_si.status as child_status,
  child_st.code as child_code,
  child_st.inherit_parent_lifecycle
FROM billing_service_instance parent_si
JOIN billing_service_instance child_si ON child_si.parent_service_id = parent_si.id
JOIN cat_service_template child_st ON child_si.service_template_id = child_st.id
WHERE parent_si.id = ?;

If misaligned: Manually align statuses and fix template configuration.

---

Issue 5: Internet Services Requiring NR_* Fields

Note: This issue should NOT occur after Phase 41 refactoring. If it does, the refactoring was incomplete.

Symptoms

• Internet service activation asks for NR_* fields (IPND data)
• Validation error: "Missing NR_SERVICE_TYPE"
• Internet hierarchy incorrectly includes Number Registry

Diagnosis

-- Check if Internet services have Registry children
SELECT
  parent.code as internet_service,
  child.code as unexpected_child,
  child.role
FROM cat_service_template parent
JOIN cat_service_template child ON child.parent_service_template_id = parent.id
WHERE parent.code LIKE '%NBN%' OR parent.code LIKE '%INTERNET%'
  AND child.role = 'REGISTRY';

Expected: No rows returned. Internet services should NOT have REGISTRY children.

Resolution

If Registry services are incorrectly attached to Internet:

1. Remove parent relationship from Registry services
2. Verify Registry services are only children of phone number services (MSN, VOIP_NUMBER, etc.)

---

Issue 6: Orphaned Services

Symptoms

• Service appears in catalogue but cannot be activated
• "Parent not found" error
• Service instance has no subscription

Diagnosis

-- Find orphaned service templates
SELECT code, role
FROM cat_service_template
WHERE role IN ('INVENTORY', 'ADDON', 'REGISTRY')
  AND parent_service_template_id IS NULL;

-- Find orphaned service instances
SELECT si.id, st.code, si.status
FROM billing_service_instance si
JOIN cat_service_template st ON si.service_template_id = st.id
WHERE si.subscription_id IS NULL
  AND si.parent_service_id IS NULL
  AND st.role != 'PRIMARY';

Resolution

For orphaned templates:

UPDATE cat_service_template
SET parent_service_template_id = (
  SELECT id FROM cat_service_template WHERE code = 'SVC_CORRECT_PARENT'
)
WHERE code = 'SVC_ORPHANED_CHILD';

For orphaned instances:

-- Option 1: Link to correct parent
UPDATE billing_service_instance
SET parent_service_id = ?, subscription_id = ?
WHERE id = ?;

-- Option 2: Terminate orphan
UPDATE billing_service_instance
SET status = 'TERMINATED', termination_date = NOW()
WHERE id = ?;

---

Issue 7: Wrong Parent Assigned

Symptoms

• Service appears under wrong parent in hierarchy
• Lifecycle events not propagating correctly
• Display order incorrect

Diagnosis

SELECT
  st.code,
  st.role,
  parent.code as current_parent,
  CASE
    WHEN st.code LIKE '%NBN_AVC%' THEN 'SVC_NBN_ACCESS'
    WHEN st.code LIKE '%MSN%' THEN 'SVC_MOBILE_BASE'
    WHEN st.code LIKE '%VOIP_NUMBER%' THEN 'SVC_VOIP_BASE'
    ELSE 'Check decision tree'
  END as expected_parent
FROM cat_service_template st
LEFT JOIN cat_service_template parent ON st.parent_service_template_id = parent.id
WHERE st.code = ?;

Resolution

UPDATE cat_service_template
SET parent_service_template_id = (
  SELECT id FROM cat_service_template WHERE code = 'SVC_CORRECT_PARENT'
)
WHERE code = 'SVC_WRONGLY_PARENTED';

---

Database Investigation Queries

Complete Hierarchy View

WITH RECURSIVE hierarchy AS (
  SELECT
    id,
    code,
    role,
    parent_service_template_id,
    0 as depth,
    code as path
  FROM cat_service_template
  WHERE parent_service_template_id IS NULL

  UNION ALL

  SELECT
    st.id,
    st.code,
    st.role,
    st.parent_service_template_id,
    h.depth + 1,
    h.path || ' → ' || st.code
  FROM cat_service_template st
  JOIN hierarchy h ON st.parent_service_template_id = h.id
)
SELECT
  REPEAT('  ', depth) || code as hierarchy_display,
  role,
  depth
FROM hierarchy
ORDER BY path;

Service Instance Hierarchy

WITH RECURSIVE instance_hierarchy AS (
  SELECT
    si.id,
    st.code,
    si.status,
    si.parent_service_id,
    0 as depth
  FROM billing_service_instance si
  JOIN cat_service_template st ON si.service_template_id = st.id
  WHERE si.subscription_id = ?
    AND si.parent_service_id IS NULL

  UNION ALL

  SELECT
    si.id,
    st.code,
    si.status,
    si.parent_service_id,
    h.depth + 1
  FROM billing_service_instance si
  JOIN cat_service_template st ON si.service_template_id = st.id
  JOIN instance_hierarchy h ON si.parent_service_id = h.id
)
SELECT
  REPEAT('  ', depth) || code as hierarchy_display,
  status,
  id
FROM instance_hierarchy
ORDER BY depth, code;

---

API Endpoints for Testing

Get Service Template Hierarchy

curl -X GET "https://api.staqr.com/v1/catalog/services/{code}/hierarchy" \
  -H "Authorization: Bearer $TOKEN"

Validate Service Configuration

curl -X POST "https://api.staqr.com/v1/catalog/services/{code}/validate" \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"checkParent": true, "checkConstraints": true}'

Activate Service (with debug)

curl -X POST "https://api.staqr.com/v1/services/activate" \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "serviceCode": "SVC_EXAMPLE",
    "customerId": "CUST123",
    "debug": true
  }'

---

Log File Analysis Patterns

Staqr Server Logs

# Service activation errors
grep -i "activation.*failed\|eligibility.*not met\|parent.*not found" /var/log/staqr/server.log

# Lifecycle inheritance issues
grep -i "lifecycle.*inherit\|cascade.*failed" /var/log/staqr/server.log

Commerce Logs

# Service processor errors
grep -i "ServiceProcessor\|ActivationJob" /var/log/commerce/opencell.log

# Subscription creation failures
grep -i "SubscriptionService.*error" /var/log/commerce/opencell.log

---

Document Metadata

• File: service-hierarchy-troubleshooting.md
• Related Plans: 41-D-03-PLAN.md
• Location: docs-new/docs/admin-console/catalog/
• Author: Claude Code (autonomous execution)