Skip to main content
Version: v2

Customer Look up Logic using enterpriseSettings in CCG Database

Legend: Class / Handler Method Field / Config Key Event API Endpoint DB Table / Column

When a customer cannot be resolved through direct identifiers (walletCustomerId, enterpriseId, or hsid), the system falls back to a metadata-based CCG database lookup that also leverages the enterprise settings configuration.

When Does This Run?

The CCG DB metadata lookup is the final fallback in the customer resolution chain. It runs after all direct identifier lookups have been exhausted — not as a replacement for EIMP, but as a complementary database-level search that reuses the same enterprise settings configuration.

Resolution Chain — Where CCG DB Lookup Fits​

Customer Resolution Priority Order

The customer resolution order in CustomerQueryHandler.findCustomer():

PriorityStrategyLookup TargetMethod
1stDirect IDcustomer table by walletCustomerIdfindById()
2ndEnterprise IDenterprise_customer table by enterpriseIdgetCustomerByEnterpriseId()
3rdHSIDenterprise_customer table by hsidgetCustomerByHsid()
4thMetadata (Enterprise Settings)merchant_customer_metadata tablegetCustomerByMetadata()
5thBase HSIDmerchant_customer tablegetCustomerByBaseHsid()

CCG DB Lookup Algorithm​

Step-by-Step Process

The lookup is implemented in CustomerQueryHandler.getCustomerByMetadata() and uses merchantMetadataKey (not merchantSearchKey) to bridge the request metadata to the database:

  1. Guard check — If the query has no metadata, skip directly to getCustomerByBaseHsid()

  2. Load merchant and verify enterprise settings

    • Retrieve the Merchant entity using merchantRepository.findByMerchantId()
    • Confirm orderedCustomerSearchCriteria is non-empty; if empty, skip to fallback

  3. Expand search to the entire merchant group

    • Retrieve the merchantGroupId from the requesting merchant
    • Load all merchants in that group via merchantRepository.findByMerchantGroupId()
    • This enables cross-merchant customer discovery within the same merchant group

  4. For each merchant in the group:

    • Sort OrderedCustomerSearchCriteria by outer precedence (ascending)
    • Sort each inner CustomerSearchCriteria list by inner precedence (ascending)

  5. Validate metadata completeness — isMetadataContains()

    • Extract all merchantMetadataKey values marked required:true from the criteria
    • Verify the request metadata contains all required keys
    • If no criteria are marked required, at least one configured key must be present
    • If validation fails, skip this criteria set

  6. Filter metadata to configured keys — filterMetadata()

    • From the request metadata, keep only entries whose key matches a merchantMetadataKey in the criteria
    • Uses a LinkedHashMap to preserve insertion order (respects inner precedence)
    • Example: Request {"subscriberId": "ABC", "dependentCode": "01", "phone": "555-1234"} with criteria keys ["subscriberId", "dependentCode"] → filtered to {"subscriberId": "ABC", "dependentCode": "01"}

  7. Execute SQL query against merchant_customer_metadata

    • Call merchantCustomerMetadataRepository.findByMetadataAndMerchantId(filteredMetadata, merchantId)
    • Returns Optional<UUID> (the customer_id)

  8. Load full customer — If a customer_id is found, load the Customer via customerRepository.findOptionalCustomerById()

  9. First match wins — .reduce((first, next) -> first.isPresent() ? first : next) takes the first non-empty result across all merchants in the group

  10. Final fallback — If no metadata match found, fall through to getCustomerByBaseHsid()

SQL Query Construction​

Dynamic Query Building

The SQL is built dynamically by MerchantCustomerMetadataRepository.buildMetadataQuery() using nested subqueries for multi-key intersection:

Single metadata key (e.g., {"subscriberId": "ABC123"}):

SELECT customer_id FROM merchant_customer_metadata mcm
  JOIN customer cus ON cus.id = mcm.customer_id
 WHERE mcm.metadata_key = 'subscriberId'
   AND mcm.metadata_value = 'ABC123'
   AND mcm.merchant_id = '<merchant-uuid>'
   AND (cus.inactive IS NULL OR cus.inactive = false)

Multiple metadata keys (e.g., {"subscriberId": "ABC123", "dependentCode": "01"}):

SELECT customer_id FROM merchant_customer_metadata mcm
  JOIN customer cus ON cus.id = mcm.customer_id
 WHERE mcm.metadata_key = 'subscriberId'
   AND mcm.metadata_value = 'ABC123'
   AND mcm.merchant_id = '<merchant-uuid>'
   AND (cus.inactive IS NULL OR cus.inactive = false)
   AND customer_id IN (
     SELECT customer_id FROM merchant_customer_metadata mcm
       JOIN customer cus ON cus.id = mcm.customer_id
      WHERE mcm.metadata_key = 'dependentCode'
        AND mcm.metadata_value = '01'
        AND mcm.merchant_id = '<merchant-uuid>'
        AND (cus.inactive IS NULL OR cus.inactive = false)
   )

Each additional metadata key adds another nested AND customer_id IN (...) subquery, forming an intersection — the customer must have matching rows for every filtered metadata key.

Database Tables and Schema​

Tables Involved
TableColumns UsedRole
merchantenterprise_settings (JSONB), merchant_group_idStores enterprise settings config; groups merchants
merchant_customer_metadatacustomer_id, merchant_id, metadata_key, metadata_valueKey-value pairs linking customers to merchant-specific identifiers
customerid, inactiveJOIN filter to exclude inactive customers

The merchant_customer_metadata entity:

@Entity
@Table(name = "merchant_customer_metadata")
public class MerchantCustomerMetadata {
    UUID id;
    UUID customerId;      // → customer.id
    UUID merchantId;      // → merchant.wallet_merchant_id
    String key;           // metadata_key  (e.g., "subscriberId")
    String value;         // metadata_value (e.g., "ABC123")
}

Sequence Diagram​

CCG DB Metadata Lookup Flow

merchantMetadataKey — EIMP vs CCG DB Usage​

Dual Purpose of merchantMetadataKey

The merchantMetadataKey field serves different purposes in different lookup phases:

PhaseHow merchantMetadataKey Is UsedOther Keys Used
EIMP Lookup❌ Not usedmerchantSearchKey (JSONPath extraction), enterpriseSearchKey, enterpriseValueKey
CCG DB Lookup✅ Matches request metadata keys to DB query keys—
Wallet Upgrade✅ Maps EIMP response values (via enterpriseResponseSearchPath) to metadata storage keys in merchant_customer_metadataenterpriseResponseSearchPath

During EIMP lookups, the system uses merchantSearchKey (a JSONPath) to extract values from the full customer request object. During CCG DB lookups, the system uses merchantMetadataKey to match against the flat metadata map that was passed in the request.

Example:

Enterprise Settings:
  merchantSearchKey: "$.metadata.subscriberId"   ← used by EIMP lookup (JSONPath on full request)
  merchantMetadataKey: "subscriberId"             ← used by CCG DB lookup (key match on metadata map)

Request:
  { "metadata": { "subscriberId": "ABC123" } }

EIMP: extracts "ABC123" via JSONPath "$.metadata.subscriberId"
CCG DB: filters metadata map to {"subscriberId": "ABC123"}, queries merchant_customer_metadata table

Merchant Group Cross-Lookup​

Cross-Merchant Customer Discovery

A key feature of the CCG DB lookup is that it searches across all merchants in the same merchant group, not only the requesting merchant. This enables scenarios where a customer was originally created under one merchant but needs to be discovered when accessing through another merchant in the same group.

Merchant Group "Healthcare Corp" (merchantGroupId: MG-001)
├── Merchant A (walletMerchantId: M-001) — customer created here
│   └── merchant_customer_metadata: {subscriberId: "ABC123"} → customer_id: C-001
├── Merchant B (walletMerchantId: M-002) — customer requests here
│   └── merchant_customer_metadata: (no entry for this customer)
└── Merchant C (walletMerchantId: M-003)

When a request arrives at Merchant B with metadata: {"subscriberId": "ABC123"}:

  1. System loads Merchant B → gets merchantGroupId: MG-001
  2. Loads all merchants in group: A, B, C
  3. For each merchant, runs the metadata query against merchant_customer_metadata
  4. Finds the match under Merchant A → returns customer_id: C-001

Local Customer Lookup During Payment Method Transfer​

Reuse in Wallet Merge Flow

The same getCustomerByMetadata() flow is reused by CustomerQueryHandler.findLocalCustomer(), called during the Local to Enterprise Wallet Merge process:

public Mono<MerchantCustomer> findLocalCustomer(String merchantId, Map<String, String> metadata) {
    return getCustomerByMetadata(CustomerQuery.builder()
            .walletMerchantId(merchantId)
            .metadata(metadata)
            .build())
        .flatMap(localCustomer -> merchantCustomerRepo
            .findByMerchantIdAndCustomerIdAndNotEnterpriseCustomer(merchantId, localCustomer.getId()));
}

This adds an extra filter: the found customer must not already be an enterprise customer (verified via NOT EXISTS against the enterprise_customer table). This ensures only genuinely local customers are identified for migration.