reDB LogoTraceMem

Example Setup Guide

This guide provides the complete setup needed to run the agent examples. It includes database schema, data product configurations, and policy definitions.

Prerequisites

  • A PostgreSQL-compatible database (PlanetScale, Supabase, or standard PostgreSQL)
  • Access to TraceMem dashboard or Admin API
  • A TraceMem tenant with appropriate permissions

Database Schema

The examples use the following database tables. Create these in your database:

Customers Table

sql
CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    email_address VARCHAR(255) NOT NULL,
    full_name VARCHAR(255) NOT NULL,
    phone_number VARCHAR(50),
    address_line1 VARCHAR(255),
    address_line2 VARCHAR(255),
    city VARCHAR(100),
    state VARCHAR(50),
    postal_code VARCHAR(20),
    country VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Sample data
INSERT INTO customers (id, email_address, full_name, phone_number, city, state, postal_code, country) VALUES
(1001, 'customer1@example.com', 'John Doe', '555-0101', 'San Francisco', 'CA', '94102', 'USA'),
(1002, 'customer2@example.com', 'Jane Smith', '555-0102', 'New York', 'NY', '10001', 'USA'),
(1003, 'customer3@example.com', 'Bob Johnson', '555-0103', 'Chicago', 'IL', '60601', 'USA');

Products Table

sql
CREATE TABLE products (
    product_id INTEGER PRIMARY KEY,
    product_name VARCHAR(255) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL,
    stock_quantity INTEGER NOT NULL DEFAULT 0,
    category VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Sample data
INSERT INTO products (product_id, product_name, description, price, stock_quantity, category) VALUES
(1, 'Widget A', 'High-quality widget', 29.99, 100, 'Electronics'),
(2, 'Widget B', 'Premium widget', 49.99, 50, 'Electronics'),
(3, 'Widget C', 'Standard widget', 19.99, 200, 'Electronics'),
(4, 'Widget D', 'Deluxe widget', 79.99, 30, 'Electronics');

Orders Table

sql
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    quantity INTEGER NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL,
    order_status VARCHAR(50) NOT NULL DEFAULT 'pending',
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    delivery_date TIMESTAMP,
    notes TEXT,
    FOREIGN KEY (customer_id) REFERENCES customers(id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

-- Sample data (optional, for testing)
INSERT INTO orders (customer_id, product_id, quantity, total_amount, order_status) VALUES
(1001, 1, 2, 59.98, 'completed'),
(1002, 2, 1, 49.99, 'pending');

Targets Table (for delete operations)

sql
CREATE TABLE targets (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(255) NOT NULL,
    description TEXT,
    status VARCHAR(50) NOT NULL DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Sample data
INSERT INTO targets (id, name, description, status) VALUES
('0cf1e19e-00ed-4a4c-8d82-ee70f590fad8', 'Test Target 1', 'Sample target for testing', 'active'),
('550e8400-e29b-41d4-a716-446655440000', 'Test Target 2', 'Another test target', 'active');

Connector Setup

Create a connector in TraceMem that connects to your database:

Using TraceMem Dashboard

  1. Navigate to Connectors in the TraceMem dashboard

  2. Click Create Connector

  3. Select PostgreSQL (or PlanetScale if using PlanetScale)

  4. Configure connection:

    • Name: example-database
    • Host: Your database host
    • Port: 5432 (or your database port)
    • Database: Your database name
    • Username: Your database username
    • Password: Your database password
    • SSL Mode: require (recommended for production)

  5. Test the connection and save

Using Admin API

bash
curl -X POST https://api.tracemem.com/v1/connectors \
  -H "Authorization: Bearer YOUR_JWT_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "name": "example-database",
    "type": "postgres",
    "config": {
      "host": "your-database-host",
      "port": 5432,
      "database": "your-database-name",
      "username": "your-username",
      "password": "your-password",
      "ssl_mode": "require"
    }
  }'

Data Product Configurations

Create the following data products in TraceMem. These match the products used in the examples.

1. Read Customer Data Product

Name: planetscale_read_customer_v1

Configuration:

json
{
  "name": "planetscale_read_customer_v1",
  "version": "v1",
  "connector_id": "your-connector-id",
  "table": "customers",
  "operation": "read",
  "allowed_purposes": ["web_order"],
  "schema": {
    "fields": [
      {
        "name": "id",
        "type": "integer",
        "is_key": true,
        "is_queryable": true
      },
      {
        "name": "email_address",
        "type": "string"
      },
      {
        "name": "full_name",
        "type": "string"
      },
      {
        "name": "phone_number",
        "type": "string"
      },
      {
        "name": "address_line1",
        "type": "string"
      },
      {
        "name": "address_line2",
        "type": "string"
      },
      {
        "name": "city",
        "type": "string"
      },
      {
        "name": "state",
        "type": "string"
      },
      {
        "name": "postal_code",
        "type": "string"
      },
      {
        "name": "country",
        "type": "string"
      },
      {
        "name": "created_at",
        "type": "timestamp"
      },
      {
        "name": "updated_at",
        "type": "timestamp"
      }
    ]
  },
  "restrictions": []
}

2. Insert Order Data Product (with Policy)

Name: planetscale_insert_order_v1

Configuration:

json
{
  "name": "planetscale_insert_order_v1",
  "version": "v1",
  "connector_id": "your-connector-id",
  "table": "orders",
  "operation": "insert",
  "allowed_purposes": ["web_order"],
  "schema": {
    "fields": [
      {
        "name": "order_id",
        "type": "integer",
        "is_key": true,
        "db_default": true
      },
      {
        "name": "customer_id",
        "type": "integer",
        "required": true
      },
      {
        "name": "product_id",
        "type": "integer",
        "required": true
      },
      {
        "name": "quantity",
        "type": "integer",
        "required": true
      },
      {
        "name": "total_amount",
        "type": "decimal",
        "required": true
      },
      {
        "name": "order_status",
        "type": "string",
        "required": true
      },
      {
        "name": "order_date",
        "type": "timestamp",
        "db_default": true
      },
      {
        "name": "delivery_date",
        "type": "timestamp",
        "nullable": true
      },
      {
        "name": "notes",
        "type": "text",
        "nullable": true
      }
    ]
  },
  "return_created": true,
  "policies": ["discount_cap_v1"],
  "restrictions": []
}

3. Insert Order Data Product (without Policy)

Name: planetscale_insert_order_no_policy_v1

Configuration:

json
{
  "name": "planetscale_insert_order_no_policy_v1",
  "version": "v1",
  "connector_id": "your-connector-id",
  "table": "orders",
  "operation": "insert",
  "allowed_purposes": ["web_order"],
  "schema": {
    "fields": [
      {
        "name": "order_id",
        "type": "integer",
        "is_key": true,
        "db_default": true
      },
      {
        "name": "customer_id",
        "type": "integer",
        "required": true
      },
      {
        "name": "product_id",
        "type": "integer",
        "required": true
      },
      {
        "name": "quantity",
        "type": "integer",
        "required": true
      },
      {
        "name": "total_amount",
        "type": "decimal",
        "required": true
      },
      {
        "name": "order_status",
        "type": "string",
        "required": true
      },
      {
        "name": "order_date",
        "type": "timestamp",
        "db_default": true
      },
      {
        "name": "delivery_date",
        "type": "timestamp",
        "nullable": true
      },
      {
        "name": "notes",
        "type": "text",
        "nullable": true
      }
    ]
  },
  "return_created": true,
  "restrictions": []
}

4. Update Product Stock Data Product

Name: planetscale_update_product_stock_v1

Configuration:

json
{
  "name": "planetscale_update_product_stock_v1",
  "version": "v1",
  "connector_id": "your-connector-id",
  "table": "products",
  "operation": "update",
  "allowed_purposes": ["web_order"],
  "schema": {
    "fields": [
      {
        "name": "product_id",
        "type": "integer",
        "is_key": true
      },
      {
        "name": "product_name",
        "type": "string",
        "is_updatable": false
      },
      {
        "name": "description",
        "type": "text",
        "is_updatable": false
      },
      {
        "name": "price",
        "type": "decimal",
        "is_updatable": false
      },
      {
        "name": "stock_quantity",
        "type": "integer",
        "is_updatable": true
      },
      {
        "name": "category",
        "type": "string",
        "is_updatable": false
      },
      {
        "name": "created_at",
        "type": "timestamp",
        "is_updatable": false
      },
      {
        "name": "updated_at",
        "type": "timestamp",
        "is_updatable": false
      }
    ]
  },
  "restrictions": []
}

5. Delete Target Data Product

Name: planetscale_delete_target_v1

Configuration:

json
{
  "name": "planetscale_delete_target_v1",
  "version": "v1",
  "connector_id": "your-connector-id",
  "table": "targets",
  "operation": "delete",
  "allowed_purposes": ["delete_target"],
  "schema": {
    "fields": [
      {
        "name": "id",
        "type": "uuid",
        "is_key": true
      },
      {
        "name": "name",
        "type": "string",
        "is_updatable": false
      },
      {
        "name": "description",
        "type": "text",
        "is_updatable": false
      },
      {
        "name": "status",
        "type": "string",
        "is_updatable": false
      },
      {
        "name": "created_at",
        "type": "timestamp",
        "is_updatable": false
      },
      {
        "name": "updated_at",
        "type": "timestamp",
        "is_updatable": false
      }
    ]
  },
  "restrictions": []
}

Policy Configuration

Discount Cap Policy

Name: discount_cap_v1

Description: Limits discounts to a maximum of 10%. Discounts exceeding this require approval.

Configuration:

json
{
  "name": "discount_cap_v1",
  "version": "v1",
  "description": "Limits discounts to 10% cap. Exceptions require approval.",
  "inputs": [
    {
      "name": "proposed_discount",
      "type": "decimal",
      "description": "The proposed discount percentage (0.0 to 1.0)"
    }
  ],
  "evaluation_logic": {
    "type": "rego",
    "code": "package tracemem.policy.discount_cap_v1\n\nimport future.keywords.if\nimport future.keywords.in\n\n# Default deny\nallow = false\n\n# Allow discounts up to 10%\nallow if {\n    input.proposed_discount <= 0.10\n}\n\n# Require exception for discounts between 10% and 25%\nrequires_exception if {\n    input.proposed_discount > 0.10\n    input.proposed_discount <= 0.25\n}\n\n# Deny discounts over 25%\nallow = false if {\n    input.proposed_discount > 0.25\n}\n\nrationale = \"Discount of {percentage}% exceeds 10% cap\" if {\n    input.proposed_discount > 0.10\n    percentage := sprintf(\"%.2f\", [input.proposed_discount * 100])\n}"
  },
  "exception_routes": [
    {
      "name": "finance_approval",
      "channel": "slack",
      "webhook_url": "https://hooks.slack.com/services/YOUR/WEBHOOK/URL",
      "title_template": "Discount Approval Required: {percentage}%",
      "message_template": "A discount of {percentage}% has been requested. This exceeds the 10% policy cap."
    }
  ]
}

Alternative Simple Policy (JavaScript-like logic):

If your TraceMem instance uses a different policy engine, here's the logic in pseudocode:

text
function evaluate(proposed_discount) {
    if (proposed_discount <= 0.10) {
        return { outcome: "allow", rationale: "Discount within policy limit" };
    } else if (proposed_discount <= 0.25) {
        return { 
            outcome: "requires_exception", 
            rationale: `Discount of ${proposed_discount * 100}% exceeds 10% cap and requires approval` 
        };
    } else {
        return { 
            outcome: "deny", 
            rationale: `Discount of ${proposed_discount * 100}% exceeds maximum allowed 25%` 
        };
    }
}

Agent Setup

Create an agent in TraceMem to obtain an API key:

Using TraceMem Dashboard

  1. Navigate to Agents in the TraceMem dashboard

  2. Click Create Agent

  3. Configure the agent:

    • Name: test-agent
    • Description: Agent for testing examples
    • Instance: test-instance (optional)

  4. Create an API key:

    • Click on the agent
    • Navigate to Credentials
    • Click Create Credential
    • Save the API key securely (it won't be shown again)

  5. Grant data product access:

    • Navigate to Data Products tab
    • Grant read access to: planetscale_read_customer_v1
    • Grant write access to:
      • planetscale_insert_order_v1
      • planetscale_insert_order_no_policy_v1
      • planetscale_update_product_stock_v1
      • planetscale_delete_target_v1

Using Admin API

bash
# Create agent
curl -X POST https://api.tracemem.com/v1/agents \
  -H "Authorization: Bearer YOUR_JWT_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "name": "test-agent",
    "description": "Agent for testing examples"
  }'

# Create credential (save the API key from response)
curl -X POST https://api.tracemem.com/v1/agents/{agent_id}/credentials \
  -H "Authorization: Bearer YOUR_JWT_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "name": "test-credential"
  }'

# Grant data product access
curl -X POST https://api.tracemem.com/v1/agents/{agent_id}/grants \
  -H "Authorization: Bearer YOUR_JWT_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "product_id": "planetscale_read_customer_v1",
    "permissions": ["read"]
  }'

Environment Variables

Set the following environment variables for running the examples:

bash
# Required
export TRACEMEM_API_KEY="your-agent-api-key-here"

# Optional (with defaults)
export MCP_AGENT_URL="http://localhost:9001"  # or https://mcp.tracemem.com for cloud
export TRACEMEM_INSTANCE="test-instance"
export TRACEMEM_ACTOR="test-agent"

# Example-specific variables
export CUSTOMER_ID="1003"           # For read agent
export PRODUCT_ID="2"               # For insert/update agents
export QUANTITY="1"                 # For insert agent
export TOTAL_AMOUNT="99.99"         # For insert agent
export ORDER_STATUS="pending"       # For insert agent
export PROPOSED_DISCOUNT="0"        # For insert agent with policy
export STOCK_QUANTITY="90"          # For update agent
export TARGET_ID="0cf1e19e-00ed-4a4c-8d82-ee70f590fad8"  # For delete agent

Verification Steps

  1. Verify Database Connection:

    sql
    SELECT COUNT(*) FROM customers;
SELECT COUNT(*) FROM products;
SELECT COUNT(*) FROM orders;
SELECT COUNT(*) FROM targets;

  2. Verify Data Products:

    • In TraceMem dashboard, navigate to Data Products
    • Verify all 5 data products are listed and published
    • Check that schema matches the configurations above

  3. Verify Policy:

    • Navigate to Policies in TraceMem dashboard
    • Verify discount_cap_v1 is published
    • Test the policy with different discount values

  4. Verify Agent Access:

    • Navigate to your agent in TraceMem dashboard
    • Verify all data products are listed under Data Products tab
    • Verify the API key is active

  5. Test MCP Connection:

    bash
    curl -X POST http://localhost:9001 \
  -H "Authorization: Agent YOUR_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{
    "jsonrpc": "2.0",
    "id": 1,
    "method": "initialize",
    "params": {
      "protocolVersion": "2024-11-05",
      "capabilities": {},
      "clientInfo": {"name": "test", "version": "1.0.0"}
    }
  }'

Troubleshooting

Database Connection Issues

  • Verify database credentials are correct
  • Check network connectivity to database
  • Ensure SSL settings match your database configuration
  • For PlanetScale, verify branch name and connection string

Data Product Issues

  • Verify table names match exactly (case-sensitive in some databases)
  • Check field names and types match schema configuration
  • Ensure connector has proper database permissions
  • Verify data product is published (not just saved as draft)

Policy Issues

  • Verify policy syntax is correct for your policy engine
  • Check that policy is published
  • Test policy evaluation directly in TraceMem dashboard
  • Verify exception routes are configured correctly

Agent Authentication Issues

  • Verify API key is correct and not expired
  • Check that agent has grants for the data products you're using
  • Ensure MCP server URL is correct
  • For cloud deployments, use https://mcp.tracemem.com

Common Errors

"Data product not found":

  • Verify data product name matches exactly (case-sensitive)
  • Check that data product is published
  • Ensure agent has access to the data product

"Policy evaluation failed":

  • Check policy syntax
  • Verify policy is published
  • Check policy inputs match what you're sending

"Database connection failed":

  • Verify connector configuration
  • Check database credentials
  • Ensure database is accessible from TraceMem servers
reDB LogoTraceMem

TraceMem is trace-native infrastructure for AI agents

reDBBlog