CRM Context Retrieval Skill

Name: CRM Context Retrieval Skill
Rating: 92
Author: shyamsridhar123

Metadata

yaml

id: crm-context-retrieval
version: 1.0.0
agent: Retriever (Helper)
type: retrieval

Mission

Retrieve scoped CRM context from Postgres for agent reasoning. This skill builds efficient queries and returns minimal, relevant data for the orchestrator and agents.

Inputs

Parameter	Type	Required	Description
entity_type	string	✅	Target entity: account, contact, opportunity, activity
ids	list[string]	❌	Specific entity IDs to fetch
names	list[string]	❌	Entity names for fuzzy matching
filters	object	❌	Additional filters (date range, status, etc.)
limit	int	❌	Max rows to return (default: 50)
offset	int	❌	Pagination offset (default: 0)
allowed_columns	list[string]	❌	Columns to include (default: all safe columns)
redaction_policy	string	❌	PII redaction level: none, low, medium, high
include_related	boolean	❌	Include related entities (default: false)

Example Input

json

{
  "entity_type": "opportunity",
  "filters": {
    "account_id": "abc-123",
    "status": "open",
    "close_date_range": {
      "start": "2025-12-01",
      "end": "2025-12-31"
    }
  },
  "limit": 20,
  "allowed_columns": ["id", "name", "amount", "stage", "close_date"],
  "redaction_policy": "medium"
}

Outputs

Field	Type	Description
entities	list[object]	Retrieved entity records
total_count	int	Total matching records (for pagination)
has_more	boolean	More records available
query_executed	string	SQL for audit trail
retrieval_time_ms	int	Query execution time
redaction_applied	boolean	Whether PII was redacted

Example Output

json

{
  "entities": [
    {
      "id": "opp-456",
      "name": "Enterprise Deal",
      "amount": 50000.00,
      "stage": "negotiation",
      "close_date": "2025-12-15"
    },
    {
      "id": "opp-789",
      "name": "Support Renewal",
      "amount": 12000.00,
      "stage": "proposal",
      "close_date": "2025-12-20"
    }
  ],
  "total_count": 2,
  "has_more": false,
  "query_executed": "SELECT id, name, amount, stage, close_date FROM opportunities WHERE account_id = $1 AND status = $2 AND close_date BETWEEN $3 AND $4 LIMIT 20",
  "retrieval_time_ms": 12,
  "redaction_applied": false
}

Workflow

code

1. Validate entity_type
2. Build base query from entity_type
3. Apply filters:
   - ids → WHERE id IN (...)
   - names → WHERE name ILIKE '%...%'
   - Custom filters → WHERE clauses
4. Apply column projection (allowed_columns)
5. Add LIMIT and OFFSET
6. Execute query with parameterized values
7. Apply redaction policy to results
8. Return structured response with metadata

Filter Syntax

Date Filters

json

{
  "created_at_range": { "start": "2025-01-01", "end": "2025-12-31" },
  "close_date_gte": "2025-12-01",
  "updated_at_lt": "2025-06-01"
}

Status Filters

json

{
  "status": "active",
  "status_in": ["active", "prospect"],
  "status_not": "inactive"
}

Related Entity Filters

json

{
  "account_id": "abc-123",
  "contact_id": "contact-456"
}

Guardrails

•✅ Always use parameterized queries (no SQL injection)
•✅ Enforce maximum limit (100 rows)
•✅ Apply redaction before returning PII fields
•✅ Validate entity_type against known types
•✅ Log all queries with execution time
•❌ Never allow SELECT * without column restriction

Dependencies

•scripts/fetch_context.py — Query builder implementation
•references/schema.md — Table definitions
•references/redaction.md — PII redaction rules
•src/db/connection.py — Database connection pool

Query Optimization

•Use indexes on: id, account_id, contact_id, status, created_at
•Prefer covering indexes for common column sets
•Add query hints for complex joins
•Cache frequently accessed entities (TTL: 60s)

Error Handling

Error	Response
Invalid entity_type	"Unknown entity type: {type}"
Query timeout	Retry with reduced limit
No results	Return empty entities array, not error
DB connection error	Circuit breaker, retry with backoff