GoPie Logo

Generated API Endpoints

Detailed reference for auto-generated REST endpoints and their capabilities

GoPie automatically generates comprehensive REST endpoints for every dataset. This guide provides detailed documentation for all available endpoints, parameters, and response formats.

Endpoint Structure

Core CRUD Endpoints

List Records

Retrieve paginated records with filtering and sorting capabilities.

GET /api/v1/datasets/{dataset_id}/data

Query Parameters:

ParameterTypeDescriptionDefault
pageintegerPage number (1-based)1
limitintegerRecords per page (max 1000)20
sortstringSort fields (prefix with - for DESC)-created_at
fieldsstringComma-separated fields to includeAll fields
excludestringComma-separated fields to excludeNone
filter[field][op]variousFilter operationsNone
searchstringFull-text search across text fieldsNone
cursorstringCursor for paginationNone
{
  "data": [
    {
      "id": 1,
      "name": "John Doe",
      "email": "[email protected]",
      "status": "active",
      "created_at": "2024-01-15T10:00:00Z"
    }
  ],
  "pagination": {
    "page": 1,
    "limit": 20,
    "total": 150,
    "pages": 8,
    "has_next": true,
    "has_prev": false,
    "next_cursor": "eyJpZCI6MjB9",
    "prev_cursor": null
  },
  "meta": {
    "sort": ["-created_at"],
    "filters_applied": 2,
    "search_query": null,
    "execution_time_ms": 15
  }
}
# Basic pagination
GET /api/v1/datasets/ds_customers/data?page=2&limit=50

# Sorting by multiple fields
GET /api/v1/datasets/ds_customers/data?sort=status,-created_at

# Field selection
GET /api/v1/datasets/ds_customers/data?fields=id,name,email

# Complex filtering
GET /api/v1/datasets/ds_customers/data?filter[status][eq]=active&filter[created_at][gte]=2024-01-01

# Full-text search
GET /api/v1/datasets/ds_customers/data?search=john%20smith

# Cursor-based pagination (for large datasets)
GET /api/v1/datasets/ds_customers/data?cursor=eyJpZCI6MTAwMH0&limit=50

Get Single Record

Retrieve a specific record by ID with optional related data.

GET /api/v1/datasets/{dataset_id}/data/{id}

Query Parameters:

ParameterTypeDescriptionDefault
fieldsstringComma-separated fields to includeAll fields
includestringRelated resources to includeNone
expandstringNested resources to expandNone
{
  "id": 1,
  "name": "John Doe",
  "email": "[email protected]",
  "phone": "+1234567890",
  "address": {
    "street": "123 Main St",
    "city": "New York",
    "state": "NY",
    "zip": "10001"
  },
  "status": "active",
  "created_at": "2024-01-15T10:00:00Z",
  "updated_at": "2024-01-20T15:30:00Z",
  "_links": {
    "self": "/api/v1/datasets/ds_customers/data/1",
    "orders": "/api/v1/datasets/ds_orders/data?filter[customer_id][eq]=1"
  }
}
# Get with specific fields
GET /api/v1/datasets/ds_customers/data/123?fields=id,name,email

# Include related data
GET /api/v1/datasets/ds_customers/data/123?include=orders,addresses

# Expand nested objects
GET /api/v1/datasets/ds_customers/data/123?expand=address.coordinates

Create Record

Create a new record with automatic validation.

POST /api/v1/datasets/{dataset_id}/data
Content-Type: application/json

{
  "name": "Jane Smith",
  "email": "[email protected]",
  "phone": "+1234567890",
  "address": {
    "street": "456 Oak Ave",
    "city": "Los Angeles",
    "state": "CA",
    "zip": "90001"
  },
  "status": "active"
}

Headers:

HeaderRequiredDescription
Content-TypeYesMust be application/json
X-Idempotency-KeyNoEnsure idempotent creation
X-Return-FieldsNoFields to return in response
{
  "id": 151,
  "name": "Jane Smith",
  "email": "[email protected]",
  "phone": "+1234567890",
  "address": {
    "street": "456 Oak Ave",
    "city": "Los Angeles",
    "state": "CA",
    "zip": "90001"
  },
  "status": "active",
  "created_at": "2024-01-20T14:30:00Z",
  "updated_at": "2024-01-20T14:30:00Z",
  "_links": {
    "self": "/api/v1/datasets/ds_customers/data/151"
  }
}
// Validation error response
{
  "error": {
    "code": "VALIDATION_ERROR",
    "message": "Validation failed",
    "details": [
      {
        "field": "email",
        "code": "invalid_format",
        "message": "Email format is invalid"
      },
      {
        "field": "phone",
        "code": "duplicate",
        "message": "Phone number already exists"
      }
    ]
  }
}

Update Record

Replace an entire record with validation.

PUT /api/v1/datasets/{dataset_id}/data/{id}
Content-Type: application/json

{
  "name": "John Doe Updated",
  "email": "[email protected]",
  "phone": "+1234567890",
  "address": {
    "street": "789 Pine St",
    "city": "Chicago",
    "state": "IL",
    "zip": "60601"
  },
  "status": "active"
}

Headers:

HeaderRequiredDescription
If-MatchNoETag for optimistic locking
X-Return-FieldsNoFields to return in response
{
  "id": 1,
  "name": "John Doe Updated",
  "email": "[email protected]",
  "phone": "+1234567890",
  "address": {
    "street": "789 Pine St",
    "city": "Chicago",
    "state": "IL",
    "zip": "60601"
  },
  "status": "active",
  "created_at": "2024-01-15T10:00:00Z",
  "updated_at": "2024-01-20T15:00:00Z",
  "version": 2,
  "_links": {
    "self": "/api/v1/datasets/ds_customers/data/1"
  }
}

Partial Update

Update specific fields without replacing the entire record.

PATCH /api/v1/datasets/{dataset_id}/data/{id}
Content-Type: application/json

{
  "status": "inactive",
  "address": {
    "city": "San Francisco"
  }
}

Special Operations:

// Increment numeric field
{
  "$inc": {
    "login_count": 1,
    "points": 10
  }
}

// Append to array
{
  "$push": {
    "tags": "premium"
  }
}

// Remove from array
{
  "$pull": {
    "tags": "trial"
  }
}

// Set if not exists
{
  "$setOnInsert": {
    "first_login": "2024-01-20T15:00:00Z"
  }
}
{
  "id": 1,
  "name": "John Doe Updated",
  "email": "[email protected]",
  "status": "inactive",
  "address": {
    "street": "789 Pine St",
    "city": "San Francisco",
    "state": "IL",
    "zip": "60601"
  },
  "updated_at": "2024-01-20T16:00:00Z",
  "version": 3
}

Delete Record

Delete a record with optional soft delete support.

DELETE /api/v1/datasets/{dataset_id}/data/{id}

Query Parameters:

ParameterTypeDescriptionDefault
softbooleanSoft delete (mark as deleted)true
cascadebooleanDelete related recordsfalse
// Soft delete response
{
  "id": 1,
  "deleted": true,
  "deleted_at": "2024-01-20T17:00:00Z",
  "message": "Record soft deleted successfully"
}

// Hard delete response
{
  "deleted": true,
  "message": "Record permanently deleted"
}

Advanced Query Endpoints

Custom SQL Query

Execute custom SQL queries with parameterization.

POST /api/v1/datasets/{dataset_id}/data/query
Content-Type: application/json

{
  "sql": "SELECT * FROM ${table} WHERE status = :status AND created_at > :date ORDER BY created_at DESC LIMIT :limit",
  "parameters": {
    "status": "active",
    "date": "2024-01-01",
    "limit": 100
  },
  "options": {
    "timeout": 30000,
    "cache": true,
    "cache_ttl": 300
  }
}

Request Body:

FieldTypeRequiredDescription
sqlstringYesSQL query (SELECT only)
parametersobjectNoNamed parameters
optionsobjectNoQuery execution options
{
  "data": [
    {
      "id": 1,
      "name": "John Doe",
      "status": "active",
      "created_at": "2024-01-15T10:00:00Z"
    }
  ],
  "meta": {
    "row_count": 45,
    "execution_time_ms": 23,
    "cached": false,
    "query_id": "q_1234567890"
  },
  "schema": [
    {
      "name": "id",
      "type": "integer",
      "nullable": false
    },
    {
      "name": "name",
      "type": "string",
      "nullable": false
    }
  ]
}

Only SELECT queries are allowed. The system automatically validates queries and applies row-level security policies.

// Forbidden query example
{
  "sql": "UPDATE customers SET status = 'deleted'",
  "error": {
    "code": "FORBIDDEN_OPERATION",
    "message": "Only SELECT queries are allowed"
  }
}

Aggregations

Perform aggregations with grouping and filtering.

GET /api/v1/datasets/{dataset_id}/data/aggregate

Query Parameters:

ParameterTypeDescriptionExample
group_bystringFields to group bystatus,region
aggstringAggregation functionscount,sum(amount),avg(rating)
havingstringFilter aggregated resultscount>10
filterobjectPre-aggregation filtersStandard filters
sortstringSort aggregated results-count
{
  "data": [
    {
      "status": "active",
      "region": "north",
      "count": 150,
      "sum_amount": 45678.90,
      "avg_rating": 4.5
    },
    {
      "status": "active",
      "region": "south",
      "count": 120,
      "sum_amount": 34567.80,
      "avg_rating": 4.2
    }
  ],
  "meta": {
    "groups": 8,
    "execution_time_ms": 45
  },
  "aggregations": {
    "total_count": 500,
    "total_amount": 150000.00,
    "overall_avg_rating": 4.3
  }
}
# Count by status
GET /api/v1/datasets/ds_orders/data/aggregate?group_by=status&agg=count

# Revenue by month
GET /api/v1/datasets/ds_orders/data/aggregate?group_by=date_trunc(month,created_at)&agg=sum(amount),count

# Top customers by order value
GET /api/v1/datasets/ds_orders/data/aggregate?group_by=customer_id&agg=sum(amount)&sort=-sum_amount&limit=10

# Complex aggregation with filtering
GET /api/v1/datasets/ds_orders/data/aggregate?group_by=product_category,status&agg=count,sum(amount),avg(amount)&filter[created_at][gte]=2024-01-01&having=count>5

Data Export

Export data in various formats with compression.

GET /api/v1/datasets/{dataset_id}/data/export

Query Parameters:

ParameterTypeDescriptionDefault
formatstringExport format (csv, json, parquet, excel)csv
compressbooleanEnable compressionfalse
delimiterstringCSV delimiter,
headerbooleanInclude header rowtrue
filterobjectApply filtersNone
fieldsstringFields to exportAll
Content-Type: text/csv
Content-Disposition: attachment; filename="customers_export_20240120.csv"
Content-Encoding: gzip
X-Total-Records: 1500
X-Export-Id: exp_1234567890
# Stream large exports
GET /api/v1/datasets/ds_logs/data/export?format=json&stream=true

# Response with streaming
Transfer-Encoding: chunked
Content-Type: application/x-ndjson

{"id":1,"message":"Log entry 1","timestamp":"2024-01-20T10:00:00Z"}
{"id":2,"message":"Log entry 2","timestamp":"2024-01-20T10:00:01Z"}
...

Bulk Operations

Bulk Create

Create multiple records in a single request.

POST /api/v1/datasets/{dataset_id}/data/bulk
Content-Type: application/json

{
  "operations": [
    {
      "method": "create",
      "data": {
        "name": "Customer 1",
        "email": "[email protected]"
      }
    },
    {
      "method": "create",
      "data": {
        "name": "Customer 2",
        "email": "[email protected]"
      }
    }
  ],
  "options": {
    "atomic": true,
    "continue_on_error": false,
    "return_ids": true
  }
}
{
  "succeeded": 2,
  "failed": 0,
  "results": [
    {
      "index": 0,
      "success": true,
      "id": 201,
      "data": {
        "id": 201,
        "name": "Customer 1",
        "email": "[email protected]"
      }
    },
    {
      "index": 1,
      "success": true,
      "id": 202,
      "data": {
        "id": 202,
        "name": "Customer 2",
        "email": "[email protected]"
      }
    }
  ],
  "execution_time_ms": 50
}

Bulk Update/Delete

Perform bulk updates or deletes with conditions.

POST /api/v1/datasets/{dataset_id}/data/bulk
Content-Type: application/json

{
  "operations": [
    {
      "method": "update",
      "filter": {"status": "trial"},
      "data": {"status": "expired"}
    },
    {
      "method": "update",
      "ids": [101, 102, 103],
      "data": {"category": "premium"}
    }
  ]
}
POST /api/v1/datasets/{dataset_id}/data/bulk
Content-Type: application/json

{
  "operations": [
    {
      "method": "delete",
      "filter": {"status": "deleted"},
      "soft": false
    },
    {
      "method": "delete",
      "ids": [201, 202, 203],
      "soft": true
    }
  ]
}

Real-time Endpoints

Server-Sent Events

Stream real-time updates for dataset changes.

GET /api/v1/datasets/{dataset_id}/data/stream
Accept: text/event-stream

# Response stream
event: record.created
data: {"id":301,"name":"New Customer","created_at":"2024-01-20T18:00:00Z"}

event: record.updated
data: {"id":101,"changes":{"status":"active"},"updated_at":"2024-01-20T18:00:05Z"}

event: record.deleted
data: {"id":201,"deleted_at":"2024-01-20T18:00:10Z"}

WebSocket Connection

// WebSocket for bi-directional communication
const ws = new WebSocket('wss://api.gopie.io/api/v1/datasets/ds_customers/data/ws');

ws.on('open', () => {
  // Subscribe to changes
  ws.send(JSON.stringify({
    action: 'subscribe',
    filters: { status: 'active' }
  }));
});

ws.on('message', (data) => {
  const event = JSON.parse(data);
  console.log('Received:', event);
});

Response Formats

Standard Success Response

{
  "data": { /* Resource data */ },
  "meta": {
    "version": "1.0",
    "request_id": "req_1234567890",
    "execution_time_ms": 25
  },
  "_links": {
    "self": "/api/v1/datasets/ds_customers/data/1",
    "collection": "/api/v1/datasets/ds_customers/data"
  }
}

Error Response Format

{
  "error": {
    "code": "VALIDATION_ERROR",
    "message": "Validation failed for 2 fields",
    "details": [
      {
        "field": "email",
        "code": "invalid_format",
        "message": "Email format is invalid"
      }
    ],
    "request_id": "req_1234567890",
    "documentation_url": "https://docs.gopie.io/errors/VALIDATION_ERROR"
  }
}

Pagination Metadata

{
  "pagination": {
    "page": 2,
    "limit": 20,
    "total": 150,
    "pages": 8,
    "has_next": true,
    "has_prev": true,
    "next_cursor": "eyJpZCI6NDB9",
    "prev_cursor": "eyJpZCI6MjB9",
    "first_page_url": "/api/v1/datasets/ds_customers/data?page=1&limit=20",
    "last_page_url": "/api/v1/datasets/ds_customers/data?page=8&limit=20",
    "next_page_url": "/api/v1/datasets/ds_customers/data?page=3&limit=20",
    "prev_page_url": "/api/v1/datasets/ds_customers/data?page=1&limit=20"
  }
}

Rate Limiting

All endpoints are subject to rate limiting:

Endpoint TypeRate LimitWindow
Read (GET)1000 req1 minute
Write (POST/PUT/PATCH)100 req1 minute
Delete50 req1 minute
Query100 req1 minute
Export10 req1 minute
Bulk20 req1 minute

Rate limit headers:

X-RateLimit-Limit: 1000
X-RateLimit-Remaining: 950
X-RateLimit-Reset: 1642694400
X-RateLimit-Reset-After: 45

Best Practices

  1. Use field selection to reduce payload size
  2. Implement cursor pagination for large datasets
  3. Cache responses with appropriate TTL
  4. Use bulk operations for multiple changes
  5. Monitor rate limits in response headers
  6. Handle errors gracefully with exponential backoff
  7. Use compression for large data transfers

Next Steps