Memory command examples

This guide provides practical examples of using CansoMemory with Canso AI agents, including storing structured data, having memory-enhanced conversations, and utilizing the SQL Runner tool.

Prerequisites

Before trying these examples, make sure you have:

• Deployed a Vector Database

• Deployed a Canso AI Agent

• Installed the GRU CLI tool

Storing Data in Memory

1. Storing Database Table Metadata

Create a JSON file with detailed table metadata:

cat > customer_table_metadata.json << EOF
{
  "collection_name": "canso_table_metadata",
  "data": {
    "table_name": "customers",
    "schema": "CREATE TABLE customers (\n  id INT PRIMARY KEY,\n  first_name VARCHAR(50) NOT NULL,\n  last_name VARCHAR(50) NOT NULL,\n  email VARCHAR(100) UNIQUE NOT NULL,\n  phone VARCHAR(20),\n  address_line1 VARCHAR(100),\n  address_line2 VARCHAR(100),\n  city VARCHAR(50),\n  state VARCHAR(50),\n  postal_code VARCHAR(20),\n  country VARCHAR(50),\n  status VARCHAR(20) NOT NULL DEFAULT 'active',\n  customer_segment VARCHAR(20) CHECK (customer_segment IN ('standard', 'premium', 'vip', 'enterprise')),\n  acquisition_source VARCHAR(50),\n  lifetime_value DECIMAL(12,2),\n  loyalty_points INT DEFAULT 0,\n  date_of_birth DATE,\n  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,\n  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,\n  last_login_at TIMESTAMP,\n  CONSTRAINT valid_email CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\.[A-Za-z]{2,}$')\n);",
  }
}
EOF

# Insert the table metadata using GRU CLI
gru agent memory insert --agent-name sql-assistant --file customer_table_metadata.json

1. Adding Detailed Table Documentation

Create a JSON file with comprehensive table documentation:

cat > customer_documentation.json << EOF
{
  "collection_name": "canso_table_documentation",
  "data": {
    "table_name": "customers",
    "doc_type": "comprehensive",
  }
}
EOF

# Insert the comprehensive documentation
gru agent memory insert --agent-name sql-assistant --file customer_documentation.json

1. Store domain specific knowledge

Create a JSON file with domain-specific knowledge:

cat > customer_segment_knowledge.json << EOF
{
  "collection_name": "canso_domain_knowledge",
  "data": {
    "domain": "customer_segmentation",
    "tags": ["customers", "segmentation", "classification", "business-rules"],
  }
}
EOF

# Insert the domain-specific knowledge
gru agent memory insert --agent-name sql-assistant --file customer_segment_knowledge.json

1. Store column metadata information

Create a JSON file with the column metadata information:

cat > column_metatdata_info.json << EOF
{
  "collection_name": "canso_column_metadata",
  "data": {
    "table_column_composite_key": "alerts|severity",
    "table_name": "alerts",
    "column_name": "severity",
    "candidate_values": [
      "high",
      "medium",
      "low"
    ],
    "metadata": {}
  }
}
EOF

# Insert the column metadata information
gru agent memory insert --agent-name sql-assistant --file column_metatdata_info.json

Conversation with a Memory-Enhanced Agent

On the command line, you can converse with the agent by running the following command:

gru agent converse sql-assistant

Conversation ID: a1b2c3d4
User: What information do we have in our customers table?

Agent: Based on the schema I have, the customers table contains:

- id (INT PRIMARY KEY)
- first_name, last_name (VARCHAR(50))
- email (VARCHAR(100), UNIQUE)
- phone (VARCHAR(20))
- address fields (address_line1, address_line2, city, state, postal_code, country)
- status (VARCHAR(20), DEFAULT 'active')
- customer_segment (VARCHAR(20)) with values: standard, premium, vip, enterprise
- financial metrics (lifetime_value, loyalty_points)
- various timestamps (created_at, updated_at, last_login_at)

This is the central table for all customer information in our CRM system and includes personal details, contact information, segmentation data, and account status.

User: I need to write a query that shows me all premium and vip customers who have orders in the "SHP" status. What does SHP mean?

Agent: SHP stands for "Shipped" - it means the order has left our facility with a carrier. This is one of our primary order status codes in the order processing workflow.

For your query, you'll need to join the customers table with the orders table to find premium and vip customers with shipped orders. Here's the query:

    SELECT 
      c.id,
      c.first_name,
      c.last_name,
      c.email,
      c.customer_segment,
      c.lifetime_value,
      o.id AS order_id,
      o.order_date,
      o.total_amount
    FROM 
      customers c
    JOIN 
      orders o ON c.id = o.customer_id
    WHERE 
      c.customer_segment IN ('premium', 'vip')
      AND o.order_status = 'SHP'
    ORDER BY 
      c.lifetime_value DESC,
      o.order_date DESC;

Would you like me to run this query for you?

User: Can you modify it to also show how long the orders have been in shipped status?

Agent: I'll modify the query to show how long orders have been in the shipped status by calculating the difference between the current date and the date when the order status changed to "SHP".

    SELECT 
      c.id,
      c.first_name,
      c.last_name,
      c.email,
      c.customer_segment,
      c.lifetime_value,
      o.id AS order_id,
      o.order_date,
      o.total_amount,
      osh.status_change_date AS shipped_date,
      CURRENT_DATE - osh.status_change_date AS days_in_shipped_status
    FROM 
      customers c
    JOIN 
      orders o ON c.id = o.customer_id
    JOIN 
      order_status_history osh ON o.id = osh.order_id AND osh.status = 'SHP'
    WHERE 
      c.customer_segment IN ('premium', 'vip')
      AND o.order_status = 'SHP'
    ORDER BY 
      days_in_shipped_status DESC,
      c.lifetime_value DESC;

This query:

- Joins with the order_status_history table to get the date when each order changed to "SHP" status
- Calculates days_in_shipped_status by subtracting the ship date from the current date
- Orders results by days in shipped status (longest first) and then by customer lifetime value

Based on our business rules, you might want to pay special attention to orders that have been in "SHP" status for more than 10 days, as these require investigation and customer outreach.

User: Run this query and explain the business impact of the results.

Agent: I'll run the query and explain the business impact of the results.

Agent: I'll be calling the tool run_sql_query with following arguments:
query: SELECT c.id, c.first_name, c.last_name, c.email, c.customer_segment, c.lifetime_value, o.id AS order_id, o.order_date, o.total_amount, osh.status_change_date AS shipped_date, CURRENT_DATE - osh.status_change_date AS days_in_shipped_status FROM customers c JOIN orders o ON c.id = o.customer_id JOIN order_status_history osh ON o.id = osh.order_id AND osh.status = 'SHP' WHERE c.customer_segment IN ('premium', 'vip') AND o.order_status = 'SHP' ORDER BY days_in_shipped_status DESC, c.lifetime_value DESC;
Do you approve of this action? Type 'y' to continue; otherwise, explain your requested changed.

User: y

Agent: Here are the results of the query:
| id  | first_name | last_name | email                    | customer_segment | lifetime_value | order_id | order_date  | total_amount | shipped_date | days_in_shipped_status |
|-----|------------|-----------|--------------------------|------------------|----------------|----------|-------------|--------------|--------------|----------------------|
| 143 | Jennifer   | Martinez  | [e-mail]     | VIP             | $12,450        | 8834     | 2024-01-15  | $435.99      | 2024-01-16  | 14                   |
| 267 | David      | Chen      | [e-mail]        | Premium         | $8,920         | 8847     | 2024-01-17  | $389.50      | 2024-01-18  | 12                   |
| 189 | Sarah      | Williams  | [e-mail]    | VIP             | $9,850         | 8856     | 2024-01-20  | $298.75      | 2024-01-21  | 9                    |
| 312 | Michael    | Johnson   | [e-mail]     | Premium         | $5,670         | 8862     | 2024-01-22  | $425.99      | 2024-01-23  | 7                    |
| 425 | Lisa       | Taylor    | [e-mail]      | Premium         | $4,890         | 8869     | 2024-01-23  | $227.25      | 2024-01-24  | 6                    |


Delivery Exceptions: The first two orders (for Jennifer Martinez and David Chen) have been in shipped status for more than 10 days. According to our business rules, these require immediate investigation and customer outreach. These are high-value customers (especially Jennifer with a lifetime value of over $12,000) where delivery issues could significantly impact customer satisfaction.
VIP Customer Focus: Two of the five orders with extended shipping times belong to VIP customers. Given their high lifetime value, any negative delivery experience could put substantial revenue at risk. The Jennifer Martinez order should be your highest priority.
Risk Measurement: These five delayed shipments represent approximately $1,777 in order value. However, the customers they belong to represent over $41,000 in lifetime value, showing the disproportionate impact that delivery issues could have on your business.
Recommended Actions:

Immediate outreach to Jennifer Martinez and David Chen
Expedited tracking investigation for all five orders
Consider proactive compensation (e.g., partial refund, future discount) for the orders exceeding our 10-day threshold
Analyze carrier performance for these specific delivery routes

Would you like me to draft a query to identify which carriers are handling these delayed shipments?