Creating a Customer Context Agent: Unifying Support Data Across Systems

Support agents switch between CRM, billing, product usage, and ticket history to understand a customer. Each system has different data formats, schemas, and access patterns. Context is fragmented across four or five different tools. The result: agents spend 5 minutes gathering context before they can solve a problem, first response time suffers, and customer satisfaction drops.

The problem: A customer emails support about a billing issue. The agent opens CRM to check account details, switches to billing to check payment status, opens product analytics to see usage, checks ticket history for previous issues. By the time they have full context, 5 minutes have passed. The customer is waiting. Other tickets are piling up.

The solution: A unified customer context view that joins data from CRM, billing, product analytics, and support systems into a single queryable interface. Support agents get complete customer context in seconds, not minutes. This post walks through building a repeatable architecture that transforms fragmented customer data into unified intelligence.

The Problem with Fragmented Customer Data

Customer data lives in silos. Here's what support agents face:

The Multi-System Problem

To understand a customer, agents need to check:

• CRM (HubSpot, Salesforce): Account name, plan tier, contract value, renewal date
• Billing (Stripe, billing DB): Payment status, last payment, payment method, revenue
• Product Analytics (Postgres, Snowflake): Active users, last login, feature usage, usage trends
• Support History (Zendesk, Intercom): Previous tickets, resolution times, satisfaction scores

The math: If checking each system takes 1 minute and you handle 50 tickets per day, that's 200 minutes (3.3 hours) just switching between tools—before solving any problems.

The Schema Mismatch Problem

Each system uses different schemas:

• CRM calls it account_name, billing calls it company_name
• CRM stores mrr, billing stores monthly_revenue
• Product analytics uses user_id, CRM uses customer_id
• Support uses ticket_count, CRM uses support_tickets

The impact: Agents waste time mapping between systems, making mistakes, missing critical context.

The Access Control Problem

Different systems have different access controls:

• CRM: Full access for support team
• Billing: Read-only access, some fields restricted
• Product Analytics: Requires separate credentials, different permissions
• Support: Full access to tickets, limited access to customer data

The cost: Agents request access, wait for approvals, use workarounds. Security teams spend time managing permissions across systems.

The Real-Time Problem

Data is stale:

• CRM updates hourly
• Billing updates daily
• Product analytics updates every 15 minutes
• Support history updates in real-time

The missed opportunity: Agents make decisions based on outdated data. A customer's payment status changed 2 hours ago, but the agent doesn't know.

A Unified Customer Context Architecture

The solution is a unified view architecture that normalizes data across systems, joins related information, and provides a single queryable interface.

Architecture Components

1. Data Normalization Layer

• Connects to source systems (CRM, billing, product, support)
• Maps schemas to unified format
• Handles data type conversions
• Manages connection credentials securely

2. Customer Context View

• Materialized SQL view joining all customer data
• Pre-computed joins for performance
• Calculated fields (customer tier, risk indicators)
• Filters and aggregations

3. Real-Time Update Pipeline

• Incremental refreshes from source systems
• Event-driven updates for critical changes
• Conflict resolution for conflicting data
• Audit trail for data lineage

4. Access Control Layer

• Role-based access to context view
• Row-level security (agents see only relevant customers)
• Column-level security (sensitive fields excluded)
• Audit logging for compliance

5. MCP Tool Interface

• Self-documenting tool for agent consumption
• Parameterized queries for flexibility
• Response formatting for natural language
• Error handling and fallbacks

Normalizing Data Across Systems

The first step is normalizing data from different systems into a unified schema. Each system has different field names, data types, and structures.

Schema Mapping

Map source system fields to unified schema:

CRM Fields → Unified Schema:

-- CRM (HubSpot/Salesforce) mapping
SELECT 
    account_id as customer_id,
    account_name as customer_name,
    email,
    company_name,
    account_owner,
    plan_name,
    mrr,
    contract_value,
    renewal_date,
    customer_segment,
    'crm' as source_system
FROM hubspot_accounts;

Billing Fields → Unified Schema:

-- Billing (Stripe/Billing DB) mapping
SELECT 
    customer_id,
    billing_status,
    last_payment_date,
    payment_method,
    EXTRACT(DAY FROM (CURRENT_DATE - last_payment_date)) as days_since_last_payment,
    total_revenue,
    'billing' as source_system
FROM stripe_customers;

Product Analytics Fields → Unified Schema:

-- Product Analytics (Postgres/Snowflake) mapping
SELECT 
    customer_id,
    COUNT(DISTINCT user_id) as active_users,
    MAX(last_login_date) as last_login_date,
    EXTRACT(DAY FROM (CURRENT_DATE - MAX(last_login_date))) as days_since_last_login,
    AVG(feature_adoption_score) as feature_adoption_score,
    CASE 
        WHEN AVG(daily_active_users) > LAG(AVG(daily_active_users), 7) OVER (PARTITION BY customer_id ORDER BY date) THEN 'increasing'
        WHEN AVG(daily_active_users) < LAG(AVG(daily_active_users), 7) OVER (PARTITION BY customer_id ORDER BY date) THEN 'declining'
        ELSE 'stable'
    END as usage_trend,
    'product' as source_system
FROM product_usage_events
GROUP BY customer_id;

Support History Fields → Unified Schema:

-- Support History (Zendesk/Intercom) mapping
SELECT 
    customer_id,
    COUNT(*) as total_tickets,
    COUNT(*) FILTER (WHERE status = 'open') as open_tickets,
    AVG(EXTRACT(EPOCH FROM (resolved_at - created_at)) / 3600) as avg_resolution_time_hours,
    MAX(created_at) as last_ticket_date,
    MODE() WITHIN GROUP (ORDER BY ticket_type) as last_ticket_type,
    AVG(satisfaction_score) as satisfaction_score_avg,
    COUNT(*) FILTER (WHERE ticket_type = LAG(ticket_type) OVER (PARTITION BY customer_id ORDER BY created_at)) as repeat_issue_count,
    'support' as source_system
FROM support_tickets
GROUP BY customer_id;

Building the Unified View

Join normalized data into a comprehensive customer context view:

CREATE MATERIALIZED VIEW customer_context_comprehensive AS
SELECT 
    -- Core Customer Info (from CRM)
    COALESCE(crm.customer_id, billing.customer_id, product.customer_id, support.customer_id) as customer_id,
    crm.customer_name,
    crm.email,
    crm.company_name,
    crm.account_owner,
    crm.plan_name,
    crm.mrr,
    crm.contract_value,
    crm.renewal_date,
    crm.customer_segment,
    
    -- Billing Status
    billing.billing_status,
    billing.last_payment_date,
    billing.payment_method,
    billing.days_since_last_payment,
    billing.total_revenue,
    
    -- Product Usage
    product.active_users,
    product.last_login_date,
    product.days_since_last_login,
    product.feature_adoption_score,
    product.usage_trend,
    
    -- Support History
    support.total_tickets,
    support.open_tickets,
    support.avg_resolution_time_hours,
    support.last_ticket_date,
    support.last_ticket_type,
    support.satisfaction_score_avg,
    support.repeat_issue_count,
    
    -- Calculated Fields
    CASE 
        WHEN crm.mrr > 10000 THEN 'enterprise'
        WHEN crm.mrr > 1000 THEN 'mid_market'
        ELSE 'self_serve'
    END as customer_tier,
    
    CASE 
        WHEN support.open_tickets > 5 THEN 'high_support_load'
        WHEN support.open_tickets > 2 THEN 'medium_support_load'
        ELSE 'low_support_load'
    END as support_load_category,
    
    CASE 
        WHEN billing.billing_status != 'current' THEN 'billing_issue'
        WHEN product.days_since_last_login > 30 THEN 'inactive'
        WHEN support.repeat_issue_count > 3 THEN 'repeat_issues'
        ELSE 'healthy'
    END as risk_indicators,
    
    -- Data Freshness
    GREATEST(
        crm.last_updated,
        billing.last_updated,
        product.last_updated,
        support.last_updated
    ) as context_last_updated,
    
    CURRENT_TIMESTAMP as view_refreshed_at
    
FROM (
    SELECT * FROM hubspot_accounts_normalized
) crm
FULL OUTER JOIN (
    SELECT * FROM stripe_customers_normalized
) billing ON crm.customer_id = billing.customer_id
FULL OUTER JOIN (
    SELECT * FROM product_usage_normalized
) product ON COALESCE(crm.customer_id, billing.customer_id) = product.customer_id
FULL OUTER JOIN (
    SELECT * FROM support_history_normalized
) support ON COALESCE(crm.customer_id, billing.customer_id, product.customer_id) = support.customer_id
WHERE COALESCE(crm.customer_status, 'active') = 'active';

Building the Customer Context View

The customer context view is the foundation. It joins data from all systems, normalizes schemas, and provides calculated fields for quick insights.

View Structure

The view includes:

1. Core Customer Information (from CRM):

• Customer ID, name, email, company
• Account owner, plan tier, MRR
• Contract value, renewal date, segment

2. Billing Status (from billing system):

• Payment status, last payment date
• Payment method, days since last payment
• Total revenue, billing history

3. Product Usage (from analytics):

• Active users, last login date
• Feature adoption scores
• Usage trends (increasing, declining, stable)

4. Support History (from support system):

• Total tickets, open tickets
• Average resolution time
• Last ticket date and type
• Satisfaction scores, repeat issues

5. Calculated Fields:

• Customer tier (enterprise, mid-market, self-serve)
• Support load category (high, medium, low)
• Risk indicators (billing issues, inactivity, repeat issues)

Materialization Strategy

Materialize the view for performance:

-- Create materialized view
CREATE MATERIALIZED VIEW customer_context_comprehensive AS
-- ... (view definition from above)

-- Create indexes for common queries
CREATE INDEX idx_customer_context_email ON customer_context_comprehensive(email);
CREATE INDEX idx_customer_context_tier ON customer_context_comprehensive(customer_tier);
CREATE INDEX idx_customer_context_risk ON customer_context_comprehensive(risk_indicators);

-- Refresh strategy: Incremental updates every 15 minutes
REFRESH MATERIALIZED VIEW CONCURRENTLY customer_context_comprehensive;

Refresh frequency:

• Real-time: Support history (updates immediately)
• 15 minutes: Product analytics (near real-time)
• Hourly: CRM data (frequent enough for support)
• Daily: Billing data (less frequently changing)

Real-Time Context Updates

Customer context needs to stay fresh. Implement an incremental update pipeline that refreshes data as it changes.

Update Pipeline Architecture

Incremental Refresh Logic

-- Incremental refresh for customer context
CREATE OR REPLACE FUNCTION refresh_customer_context_incremental()
RETURNS void AS $$
BEGIN
    -- Refresh only changed customers
    REFRESH MATERIALIZED VIEW CONCURRENTLY customer_context_comprehensive
    WHERE customer_id IN (
        -- Customers with recent changes in any source system
        SELECT DISTINCT customer_id
        FROM (
            SELECT customer_id FROM hubspot_accounts 
            WHERE last_updated > CURRENT_TIMESTAMP - INTERVAL '15 minutes'
            UNION
            SELECT customer_id FROM stripe_customers 
            WHERE last_updated > CURRENT_TIMESTAMP - INTERVAL '15 minutes'
            UNION
            SELECT customer_id FROM product_usage_events 
            WHERE event_timestamp > CURRENT_TIMESTAMP - INTERVAL '15 minutes'
            UNION
            SELECT customer_id FROM support_tickets 
            WHERE created_at > CURRENT_TIMESTAMP - INTERVAL '15 minutes'
        ) changed_customers
    );
END;
$$ LANGUAGE plpgsql;

-- Schedule incremental refresh every 15 minutes
SELECT cron.schedule(
    'refresh-customer-context',
    '*/15 * * * *',
    'SELECT refresh_customer_context_incremental();'
);

Event-Driven Updates

For critical changes, trigger immediate updates:

-- Trigger on support ticket creation
CREATE OR REPLACE FUNCTION on_ticket_created()
RETURNS TRIGGER AS $$
BEGIN
    -- Immediately refresh context for this customer
    REFRESH MATERIALIZED VIEW CONCURRENTLY customer_context_comprehensive
    WHERE customer_id = NEW.customer_id;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER ticket_created_trigger
AFTER INSERT ON support_tickets
FOR EACH ROW
EXECUTE FUNCTION on_ticket_created();

Agent Integration and Tool Design

Expose the customer context view through an MCP tool that agents can query. The tool should be self-documenting, flexible, and secure.

MCP Tool Definition

{
  "name": "get_customer_context",
  "description": "Retrieves comprehensive customer context including account details, billing status, product usage, and support history. Use when support agents need complete customer information to answer questions, troubleshoot issues, or understand customer situation.",
  "inputSchema": {
    "type": "object",
    "properties": {
      "customer_email": {
        "type": "string",
        "description": "Customer email address to look up"
      },
      "customer_id": {
        "type": "string",
        "description": "Customer ID (alternative to email)"
      },
      "include_history": {
        "type": "boolean",
        "description": "Include detailed support history (default: true)",
        "default": true
      }
    },
    "required": ["customer_email"]
  },
  "query": "SELECT * FROM customer_context_comprehensive WHERE (email = $1 OR customer_id = $2) LIMIT 1",
  "policies": ["authenticated", "role:support"]
}

Tool Usage Examples

Example 1: Basic Customer Lookup

Agent query: "Get context for customer@example.com"
Tool call: get_customer_context(customer_email: "customer@example.com")
Response: Complete customer context with all fields

Example 2: Customer ID Lookup

Agent query: "What's the status of customer 12345?"
Tool call: get_customer_context(customer_id: "12345")
Response: Customer context filtered by ID

Example 3: Quick Context (No History)

Agent query: "Quick check on customer@example.com"
Tool call: get_customer_context(customer_email: "customer@example.com", include_history: false)
Response: Customer context without detailed support history

Response Formatting

Format the response for natural language consumption:

{
  "customer": {
    "name": "Acme Corp",
    "email": "contact@acme.com",
    "tier": "enterprise",
    "mrr": 15000,
    "plan": "Enterprise Pro"
  },
  "billing": {
    "status": "current",
    "last_payment": "2025-01-15",
    "method": "credit_card"
  },
  "usage": {
    "active_users": 250,
    "last_login": "2025-01-20",
    "trend": "increasing"
  },
  "support": {
    "open_tickets": 2,
    "total_tickets": 15,
    "avg_resolution_time": "4.5 hours",
    "satisfaction_score": 4.2
  },
  "risk_indicators": ["healthy"],
  "context_updated": "2025-01-20T10:30:00Z"
}

Schema Overview

The customer context architecture uses a hierarchical schema that traces from source systems to unified context.

Key relationships:

• Source systems → Normalized views (schema mapping)
• Normalized views → Customer context comprehensive (joins)
• Customer context comprehensive → MCP tool (exposure)
• MCP tool → Support agent (consumption)

Traceability: Every customer context query can be traced back to source systems, normalization logic, and update timestamps.

Closing Thoughts

Building a unified customer context view transforms support operations from fragmented to cohesive. The key is treating customer data as a unified resource—not separate silos.

Key takeaways:

1. Normalization is foundational: Map different schemas to a unified format. This makes joins possible and queries consistent.

2. Materialization enables performance: Pre-compute joins and aggregations. Support agents need fast queries, not complex joins at query time.

3. Real-time updates matter: Customer context changes frequently. Implement incremental refreshes and event-driven updates to keep data fresh.

4. Tool design affects adoption: Make the MCP tool self-documenting and flexible. Support agents should understand when and how to use it.

5. Access control is critical: Customer data is sensitive. Implement row-level and column-level security. Audit all queries.

The architecture described here is repeatable. Start with one or two source systems, build the normalization layer, create the unified view, then add more sources incrementally.

Most importantly: this isn't about replacing existing systems—it's about creating a unified interface. CRM, billing, product analytics, and support systems continue to operate independently. The customer context view provides a unified lens for support agents.

This architecture pattern is implemented in Pylar, but the concepts apply to any system unifying customer data. The key is the normalization → materialization → exposure pipeline.