Database Tool

Overview

The Database tool enables your AI agent to interact with your databases - querying data, inserting records, updating information, and more. This powerful tool connects your agent to live data while maintaining security and access control.

Supported Databases

Configuration

Database Connection

First, create a database connection in your Credentials Vault:

jsonCopy
{
  "name": "production_db",
  "type": "postgresql",
  "config": {
    "host": "db.example.com",
    "port": 5432,
    "database": "myapp",
    "username": "agent_user",
    "password": "{{vault.db_password}}",
    "ssl": true
  }
}

Basic Database Tool

jsonCopy
{
  "name": "lookup_customer",
  "type": "database",
  "description": "Look up customer information by email or ID",
  "config": {
    "connection": "production_db",
    "operation": "select",
    "table": "customers",
    "columns": ["id", "name", "email", "plan", "created_at"],
    "where": {
      "or": [
        { "email": "{{parameters.email}}" },
        { "id": "{{parameters.customer_id}}" }
      ]
    }
  },
  "parameters": {
    "type": "object",
    "properties": {
      "email": {
        "type": "string",
        "description": "Customer email address"
      },
      "customer_id": {
        "type": "string",
        "description": "Customer ID"
      }
    }
  }
}

Operations

SELECT (Query)

jsonCopy
{
  "config": {
    "operation": "select",
    "table": "orders",
    "columns": ["id", "status", "total", "created_at"],
    "where": {
      "customer_id": "{{parameters.customer_id}}",
      "status": "{{parameters.status}}"
    },
    "order_by": [
      { "column": "created_at", "direction": "desc" }
    ],
    "limit": 10
  }
}

Advanced Filtering

jsonCopy
{
  "where": {
    "and": [
      { "status": { "in": ["pending", "processing"] } },
      { "created_at": { "gte": "{{parameters.start_date}}" } },
      { "total": { "gt": 100 } }
    ]
  }
}

Operators:

INSERT

jsonCopy
{
  "name": "create_note",
  "type": "database",
  "description": "Create a note for a customer",
  "config": {
    "operation": "insert",
    "table": "customer_notes",
    "values": {
      "customer_id": "{{parameters.customer_id}}",
      "content": "{{parameters.note}}",
      "created_by": "agent",
      "created_at": "{{now}}"
    },
    "returning": ["id", "created_at"]
  },
  "parameters": {
    "type": "object",
    "properties": {
      "customer_id": { "type": "string" },
      "note": { "type": "string" }
    },
    "required": ["customer_id", "note"]
  }
}

UPDATE

jsonCopy
{
  "name": "update_ticket_status",
  "type": "database",
  "description": "Update a support ticket's status",
  "config": {
    "operation": "update",
    "table": "tickets",
    "set": {
      "status": "{{parameters.new_status}}",
      "updated_at": "{{now}}",
      "updated_by": "agent"
    },
    "where": {
      "id": "{{parameters.ticket_id}}"
    },
    "returning": ["id", "status", "updated_at"]
  },
  "parameters": {
    "type": "object",
    "properties": {
      "ticket_id": { "type": "string" },
      "new_status": {
        "type": "string",
        "enum": ["open", "in_progress", "resolved", "closed"]
      }
    },
    "required": ["ticket_id", "new_status"]
  }
}

DELETE

jsonCopy
{
  "name": "delete_draft",
  "type": "database",
  "description": "Delete a draft document",
  "config": {
    "operation": "delete",
    "table": "documents",
    "where": {
      "id": "{{parameters.document_id}}",
      "status": "draft"
    },
    "returning": ["id"]
  },
  "parameters": {
    "type": "object",
    "properties": {
      "document_id": { "type": "string" }
    },
    "required": ["document_id"]
  }
}

Raw SQL (Advanced)

For complex queries, use raw SQL with parameterized queries:

jsonCopy
{
  "name": "sales_report",
  "type": "database",
  "description": "Generate sales report for a date range",
  "config": {
    "operation": "raw",
    "query": "SELECT DATE(created_at) as date, COUNT(*) as orders, SUM(total) as revenue FROM orders WHERE created_at BETWEEN $1 AND $2 AND status = 'completed' GROUP BY DATE(created_at) ORDER BY date DESC",
    "params": [
      "{{parameters.start_date}}",
      "{{parameters.end_date}}"
    ]
  },
  "parameters": {
    "type": "object",
    "properties": {
      "start_date": {
        "type": "string",
        "format": "date",
        "description": "Start date (YYYY-MM-DD)"
      },
      "end_date": {
        "type": "string",
        "format": "date",
        "description": "End date (YYYY-MM-DD)"
      }
    },
    "required": ["start_date", "end_date"]
  }
}

Joins and Relations

Simple Join

jsonCopy
{
  "config": {
    "operation": "select",
    "table": "orders",
    "columns": [
      "orders.id",
      "orders.total",
      "customers.name as customer_name",
      "customers.email"
    ],
    "joins": [
      {
        "table": "customers",
        "on": "orders.customer_id = customers.id"
      }
    ],
    "where": {
      "orders.status": "pending"
    }
  }
}

Multiple Joins

jsonCopy
{
  "config": {
    "operation": "select",
    "table": "order_items",
    "columns": [
      "orders.id as order_id",
      "products.name as product_name",
      "order_items.quantity",
      "order_items.price"
    ],
    "joins": [
      {
        "table": "orders",
        "on": "order_items.order_id = orders.id"
      },
      {
        "table": "products",
        "on": "order_items.product_id = products.id"
      }
    ]
  }
}

Aggregations

jsonCopy
{
  "name": "customer_stats",
  "type": "database",
  "description": "Get statistics for a customer",
  "config": {
    "operation": "select",
    "table": "orders",
    "columns": [
      { "raw": "COUNT(*)", "as": "total_orders" },
      { "raw": "SUM(total)", "as": "total_spent" },
      { "raw": "AVG(total)", "as": "average_order" },
      { "raw": "MAX(created_at)", "as": "last_order" }
    ],
    "where": {
      "customer_id": "{{parameters.customer_id}}",
      "status": "completed"
    }
  }
}

MongoDB Support

Find Documents

jsonCopy
{
  "name": "find_products",
  "type": "database",
  "config": {
    "connection": "mongodb_prod",
    "operation": "find",
    "collection": "products",
    "filter": {
      "category": "{{parameters.category}}",
      "price": { "$lte": "{{parameters.max_price}}" }
    },
    "projection": {
      "name": 1,
      "price": 1,
      "description": 1
    },
    "sort": { "price": 1 },
    "limit": 20
  }
}

Insert Document

jsonCopy
{
  "config": {
    "operation": "insertOne",
    "collection": "logs",
    "document": {
      "event": "{{parameters.event}}",
      "data": "{{parameters.data}}",
      "timestamp": { "$date": "{{now}}" }
    }
  }
}

Update Document

jsonCopy
{
  "config": {
    "operation": "updateOne",
    "collection": "users",
    "filter": { "_id": "{{parameters.user_id}}" },
    "update": {
      "$set": {
        "preferences": "{{parameters.preferences}}",
        "updated_at": { "$date": "{{now}}" }
      }
    }
  }
}

Security

Read-Only Access

Restrict to SELECT operations:

jsonCopy
{
  "security": {
    "allowed_operations": ["select"],
    "max_rows": 100
  }
}

Table Allowlist

Limit accessible tables:

jsonCopy
{
  "security": {
    "allowed_tables": [
      "customers",
      "orders",
      "products"
    ],
    "denied_tables": [
      "users",
      "api_keys",
      "payments"
    ]
  }
}

Column Masking

Hide sensitive columns:

jsonCopy
{
  "security": {
    "masked_columns": {
      "customers": ["ssn", "credit_card"],
      "users": ["password_hash", "api_key"]
    }
  }
}

Row-Level Security

Filter by tenant:

jsonCopy
{
  "security": {
    "row_filter": {
      "tenant_id": "{{context.tenant_id}}"
    }
  }
}

Best Practices

1. Use Parameterized Queries

Never concatenate user input:

jsonCopy
// Good - Parameterized
{
  "where": {
    "email": "{{parameters.email}}"
  }
}

// Bad - String concatenation
{
  "raw_query": "SELECT * FROM users WHERE email = '" + email + "'"
}

2. Limit Results

Always set limits to prevent large result sets:

jsonCopy
{
  "config": {
    "limit": 50,
    "max_limit": 100
  }
}

3. Use Indexes

Ensure WHERE clause columns are indexed:

sqlCopy
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
CREATE INDEX idx_orders_status ON orders(status);

4. Minimal Permissions

Create a dedicated database user with minimal permissions:

sqlCopy
CREATE USER agent_user WITH PASSWORD 'secure_password';
GRANT SELECT ON customers, orders, products TO agent_user;
GRANT INSERT ON customer_notes TO agent_user;
REVOKE ALL ON users, api_keys FROM agent_user;

5. Audit Logging

Enable query logging:

jsonCopy
{
  "logging": {
    "log_queries": true,
    "log_parameters": false,
    "log_results": false
  }
}

Testing

Test Query

bashCopy
curl -X POST "https://api.arcanflows.com/api/v1/agents/{agent_id}/tools/{tool_id}/test" \
  -H "X-API-Key: your_api_key" \
  -H "Content-Type: application/json" \
  -d '{
    "parameters": {
      "customer_id": "cust_123"
    },
    "dry_run": true
  }'

Dry Run Mode

Preview the query without executing:

jsonCopy
{
  "test_options": {
    "dry_run": true,
    "explain": true
  }
}

Troubleshooting

Connection Errors

• Verify database credentials
• Check network connectivity/firewall
• Confirm SSL settings

Query Timeouts

• Add appropriate indexes
• Reduce result set size
• Optimize query structure

Permission Denied

• Check user permissions
• Verify table access
• Review row-level security

Data Type Errors

• Match parameter types to column types
• Handle NULL values appropriately
• Use proper date/time formats