Building REST APIs for AI Consumption

GET /api/v2/db/_schema/customers

{
  "name": "customers",
  "label": "Customers",
  "field": [
    {
      "name": "id",
      "type": "integer",
      "is_primary_key": true,
      "auto_increment": true
    },
    {
      "name": "email",
      "type": "string",
      "length": 255,
      "is_unique": true,
      "validation": "email"
    },
    {
      "name": "status",
      "type": "string",
      "enum": ["active", "inactive", "pending"]
    },
    {
      "name": "created_at",
      "type": "datetime",
      "default": "CURRENT_TIMESTAMP"
    }
  ],
  "related": [
    {
      "name": "orders",
      "type": "has_many",
      "ref_table": "orders",
      "ref_field": "customer_id"
    }
  ]
}

# First query: Get count and sample
GET /api/v2/db/_table/orders?limit=10&include_count=true

# Response tells AI there are 15,000 total orders
# AI can then decide how to proceed:

# Option A: Filter to reduce scope
GET /api/v2/db/_table/orders?filter=status%3Dpending&limit=100

# Option B: Paginate through results
GET /api/v2/db/_table/orders?limit=100&offset=0
GET /api/v2/db/_table/orders?limit=100&offset=100
GET /api/v2/db/_table/orders?limit=100&offset=200

# Option C: Aggregate instead of retrieve all
GET /api/v2/db/_table/orders?group=status&fields=status,COUNT(*)%20as%20count

# Get customer with their orders and addresses in one request
GET /api/v2/db/_table/customers/42?related=orders,addresses

# Get orders with customer info and line items
GET /api/v2/db/_table/orders?related=customer_by_customer_id,order_items&limit=50

{
  "resource": [
    {
      "id": 42,
      "first_name": "John",
      "last_name": "Doe",
      "email": "[email protected]",
      "orders": [
        {
          "id": 1001,
          "order_date": "2026-01-15",
          "total": 299.99,
          "status": "shipped"
        },
        {
          "id": 1024,
          "order_date": "2026-02-01",
          "total": 149.50,
          "status": "pending"
        }
      ],
      "addresses": [
        {
          "id": 201,
          "type": "billing",
          "street": "123 Main St",
          "city": "Springfield",
          "state": "IL"
        }
      ]
    }
  ],
  "meta": {
    "count": 1
  }
}

# List all available services
GET /api/v2

# List all tables in a database service
GET /api/v2/db/_table

# Get schema for all tables
GET /api/v2/db/_schema

# Get stored procedures
GET /api/v2/db/_proc

# Get the full OpenAPI spec for your API
GET /api/v2/api_docs

# Get OpenAPI spec for a specific service
GET /api/v2/db/api_docs

# ================================
# READ Operations
# ================================

# Get all records (with default limit)
GET /api/v2/db/_table/products

# Get single record by ID
GET /api/v2/db/_table/products/42

# Get multiple specific records
GET /api/v2/db/_table/products?ids=1,2,3,4,5

# Get with filtering
GET /api/v2/db/_table/products?filter=category%3Delectronics

# Get specific fields only
GET /api/v2/db/_table/products?fields=id,name,price

# Get with sorting
GET /api/v2/db/_table/products?order=price%20DESC

# Get with related data
GET /api/v2/db/_table/products?related=category_by_category_id,reviews

# ================================
# CREATE Operations
# ================================

# Create single record
POST /api/v2/db/_table/products
{
  "resource": [
    {"name": "New Product", "price": 29.99, "category_id": 5}
  ]
}

# Create multiple records (batch)
POST /api/v2/db/_table/products
{
  "resource": [
    {"name": "Product A", "price": 19.99},
    {"name": "Product B", "price": 39.99}
  ]
}

# ================================
# UPDATE Operations
# ================================

# Update single record
PUT /api/v2/db/_table/products/42
{
  "name": "Updated Name",
  "price": 34.99
}

# Partial update (PATCH)
PATCH /api/v2/db/_table/products/42
{
  "price": 29.99
}

# ================================
# DELETE Operations
# ================================

# Delete single record
DELETE /api/v2/db/_table/products/42

# Delete multiple records
DELETE /api/v2/db/_table/products?ids=1,2,3

# Include related data via foreign key
?related=customer_by_customer_id

# MCP requests are capped at 1,000 records per call
# The platform default (DB_MAX_RECORDS_RETURNED) is 100,000
?limit=1000

# AI prompt: "Show me the top 10 pending orders from January 2026
# with customer details, sorted by total amount"

GET /api/v2/db/_table/orders?
  filter=status%3Dpending%20AND%20order_date%3E%3D2026-01-01%20AND%20order_date%3C2026-02-01&
  related=customer_by_customer_id&
  fields=id,order_date,total,status,customer_by_customer_id.name,customer_by_customer_id.email&
  order=total%20DESC&
  limit=10&
  include_count=true

{
  // The actual data - always an array
  "resource": [
    { /* record 1 */ },
    { /* record 2 */ }
  ],

  // Metadata about the response (when include_count=true)
  "meta": {
    "count": 2,          // Records in this response
    "total_count": 547,  // Total matching records
    "limit": 100,        // Applied limit
    "offset": 0          // Applied offset
  }
}

{
  "id": 42,
  "first_name": "John",
  "last_name": "Doe",
  "email": "[email protected]",
  "status": "active",
  "created_at": "2026-01-15T10:30:00Z",
  "updated_at": "2026-02-01T14:22:00Z"
}

{
  "error": {
    "code": 400,
    "message": "Invalid filter syntax",
    "context": {
      "field": "filter",
      "value": "status == active",
      "hint": "Use single '=' for equality. Example: status=active"
    }
  }
}

// Another example: Invalid field name
{
  "error": {
    "code": 400,
    "message": "Field 'user_status' not found in table 'customers'",
    "context": {
      "table": "customers",
      "requested_field": "user_status",
      "available_fields": ["id", "name", "email", "status", "created_at"],
      "suggestion": "Did you mean 'status'?"
    }
  }
}

// Just ONE endpoint in a traditional API
// Multiply this by dozens of tables...

[ApiController]
[Route("api/[controller]")]
public class CustomersController : ControllerBase
{
    private readonly ICustomerRepository _repository;
    private readonly ILogger _logger;
    private readonly IMapper _mapper;
    private readonly IValidator _validator;

    [HttpGet]
    public async Task<ActionResult<PagedResult<CustomerDto>>> GetAll(
        [FromQuery] string filter,
        [FromQuery] string sort,
        [FromQuery] int page = 1,
        [FromQuery] int pageSize = 50)
    {
        // Parse filter expression
        var filterExpression = FilterParser.Parse(filter);

        // Validate filter fields
        var validationResult = _validator.ValidateFilter(filterExpression);
        if (!validationResult.IsValid)
            return BadRequest(validationResult.Errors);

        // Build query
        var query = _repository.Query()
            .ApplyFilter(filterExpression)
            .ApplySort(sort)
            .Skip((page - 1) * pageSize)
            .Take(pageSize);

        // Execute and map
        var results = await query.ToListAsync();
        var total = await _repository.CountAsync(filterExpression);
        var dtos = _mapper.Map<List<CustomerDto>>(results);

        // Return paged result
        return Ok(new PagedResult<CustomerDto>
        {
            Data = dtos,
            Page = page,
            PageSize = pageSize,
            TotalCount = total
        });
    }

    // ... hundreds more lines for POST, PUT, DELETE, related data, etc.
}

{
  "name": "enterprise_db",
  "type": "sql_server",
  "config": {
    "host": "sql.company.com",
    "database": "production",
    "username": "${env.DB_USER}",
    "password": "${env.DB_PASS}"
  }
}

// That's it. DreamFactory now provides:
// - REST endpoints for ALL tables
// - Full CRUD operations
// - Filtering, sorting, pagination
// - Related data loading
// - Schema discovery
// - OpenAPI documentation
// - Role-based access control

# Check what tables are available
GET /api/v2/db/_table

# Check schema for a specific table
GET /api/v2/db/_schema/customers

# Check available relationships
GET /api/v2/db/_schema/customers?related=true

# Test filter syntax with minimal data
GET /api/v2/db/_table/customers?filter=id=1&fields=id

# Get OpenAPI spec for reference
GET /api/v2/db/api_docs