Centrali Query Cheat Sheet¶

Quick reference for querying records in Centrali.

Quick Links¶

Full Guide: See QUERY_GUIDE.md for detailed examples
HTTP API: Use for frontend/external clients
Compute Functions: Use for automation/workflows

HTTP API Quick Reference¶

Basic Query¶

GET /workspace/{ws}/api/v1/records/{recordSlug}?{params}

Common Parameters¶

Parameter	Example	Description
`search`	`?search=john`	Search term
`searchFields`	`?searchFields=name,email`	Fields to search in
`page`	`?page=2`	Page number (default: 1)
`pageSize`	`?pageSize=50`	Records per page (max: 500, default: 50)
`sort`	`?sort=-createdAt,name`	Sort (prefix `-` for desc)
`fields`	`?fields=id,name,email`	Select specific fields
`all`	`?all=true`	Include soft-deleted records

Sorting (HTTP API)¶

Format: ?sort=field1,-field2,field3 - Comma-separated list of fields - Prefix - for descending order (no prefix = ascending) - Supports both top-level columns and JSONB paths

Examples:

# Sort by rank ascending
GET /workspace/acme/api/v1/records/players?sort=data.rank

# Sort by rank descending (highest first)
GET /workspace/acme/api/v1/records/players?sort=-data.rank

# Multiple sort fields (rank desc, then name asc)
GET /workspace/acme/api/v1/records/players?sort=-data.rank,data.name

# Top-level column sort
GET /workspace/acme/api/v1/records/orders?sort=-createdAt

# Nested JSONB field sort
GET /workspace/acme/api/v1/records/users?sort=data.metadata.priority,-createdAt

Common Sort Fields: - Top-level: id, createdAt, updatedAt, status - JSONB data: data.rank, data.name, data.price, data.score, etc. - Nested: data.metadata.priority, data.address.city, etc.

Filter Operators¶

Operator	Syntax	Example
Equal	`field=value`	`?status=active`
Not Equal	`field[ne]=value`	`?status[ne]=deleted`
Greater Than	`field[gt]=value`	`?age[gt]=18`
Greater/Equal	`field[gte]=value`	`?age[gte]=18`
Less Than	`field[lt]=value`	`?age[lt]=65`
Less/Equal	`field[lte]=value`	`?age[lte]=65`
In List	`field[in]=a,b,c`	`?status[in]=active,pending`
Not In List	`field[nin]=a,b`	`?status[nin]=deleted,banned`
Contains	`field[contains]=text`	`?email[contains]=@gmail.com`
Starts With	`field[startswith]=text`	`?name[startswith]=John`
Ends With	`field[endswith]=text`	`?email[endswith]=.com`
Has Any	`field[hasAny]=a,b`	`?tags[hasAny]=vip,premium`
Has All	`field[hasAll]=a,b`	`?permissions[hasAll]=read,write`

Common Queries¶

# Get active users
GET /workspace/acme/api/v1/records/users?status=active

# Search with filters
GET /workspace/acme/api/v1/records/customers?search=john&status=active&age[gte]=18

# Paginate and sort
GET /workspace/acme/api/v1/records/orders?page=2&pageSize=25&sort=-createdAt

# Sort by JSONB field (rank)
GET /workspace/acme/api/v1/records/players?sort=data.rank

# Date range
GET /workspace/acme/api/v1/records/orders?createdAt[gte]=2024-01-01&createdAt[lte]=2024-12-31

# Multiple conditions
GET /workspace/acme/api/v1/records/products?status[in]=active,featured&price[gte]=10&price[lte]=100

SDK/Axios Usage¶

Using Centrali SDK:

// Sort by rank ascending
const records = await centraliClient.records.list({
  workspaceSlug: 'acme',
  recordSlug: 'players',
  params: { sort: 'data.rank' }
});

// Sort by rank descending
const records = await centraliClient.records.list({
  workspaceSlug: 'acme',
  recordSlug: 'players',
  params: { sort: '-data.rank' }
});

// Multiple sort fields
const records = await centraliClient.records.list({
  workspaceSlug: 'acme',
  recordSlug: 'players',
  params: { sort: '-data.rank,data.name' }
});

Using Axios directly:

const response = await axios.get(`/workspace/${ws}/api/v1/records/${rs}`, {
  params: {
    sort: 'data.rank',      // or '-data.rank' for descending
    page: 1,
    pageSize: 50
  }
});

Compute Functions Quick Reference¶

Function Structure¶

async function run() {
  // Your code here
  return result;
}

Query Records¶

const result = await api.queryRecords('recordSlug', {
  filter: {},          // Field filters
  search: '',          // Search term
  searchFields: [],    // Fields to search
  page: 1,             // Page number
  pageSize: 50,        // Records per page
  sort: [],            // Sort options
  includeDeleted: false,
  includeTotal: false,
  dateWindow: {}       // Date range filter
});

// Returns: { items: [], total?, page, pageSize }

Available API Methods¶

// Query
api.queryRecords(recordSlug, options)        // Query with filters
api.aggregateRecords(recordSlug, params)     // Aggregations

// CRUD
api.fetchRecord(recordId)                    // Get by ID
api.fetchRecordByUniqueField(recordSlug, field, value)
api.createRecord(recordSlug, data)           // Create
api.updateRecord(recordId, updates)          // Update
api.deleteRecord(recordId, hardDelete)       // Delete

// Atomic Operations
api.incrementField(recordId, fieldName, value)
api.decrementField(recordId, fieldName, value)

// Utilities
api.log(message)                             // Logging
api.formatDate(date, format)                 // Date formatting (dayjs)
api.uuid()                                   // Generate UUID
api.lodash.{chunk,flatten,uniq,merge}        // Lodash utils
api.math.{evaluate,add,subtract,multiply,divide,random}
api.httpGet/Post/Put/Delete(url, ...)        // HTTP (domain-restricted)

Filter Examples¶

// Equal
filter: { status: 'active' }

// Comparison operators
filter: {
  'data.age': { gte: 18, lte: 65 }
}

// In list
filter: {
  status: { in: ['active', 'pending', 'trial'] }
}

// String matching
filter: {
  'data.email': { contains: '@gmail.com' }
}

// Array operators
filter: {
  'data.tags': { hasAny: ['vip', 'premium'] }
}

// Multiple filters (AND)
filter: {
  status: 'active',
  'data.age': { gte: 18 },
  'data.verified': true
}

Sort Examples¶

// Single field ascending
sort: [{ field: 'createdAt', direction: 'asc' }]

// Single field descending
sort: [{ field: 'createdAt', direction: 'desc' }]

// Multiple fields
sort: [
  { field: 'status', direction: 'asc' },
  { field: 'createdAt', direction: 'desc' }
]

Date Window¶

// Recent records (last 7 days)
const sevenDaysAgo = new Date();
sevenDaysAgo.setDate(sevenDaysAgo.getDate() - 7);

const result = await api.queryRecords('orders', {
  dateWindow: {
    field: 'createdAt',
    from: sevenDaysAgo.toISOString()
  }
});

// Date range
dateWindow: {
  field: 'createdAt',
  from: '2024-01-01T00:00:00Z',
  to: '2024-12-31T23:59:59Z'
}

Pagination Pattern¶

// Process all records in batches
let page = 1;
const pageSize = 100;

while (true) {
  const result = await api.queryRecords('customers', {
    filter: { status: 'active' },
    page: page,
    pageSize: pageSize
  });

  if (result.items.length === 0) break;

  // Process batch
  for (const customer of result.items) {
    // Do something
  }

  page++;
}

Common Patterns¶

// Get active and verified users
const users = await api.queryRecords('users', {
  filter: {
    status: 'active',
    'data.emailVerified': true
  }
});

// Search with filters
const customers = await api.queryRecords('customers', {
  search: 'john',
  searchFields: ['data.name', 'data.email'],
  filter: { status: 'active' }
});

// Recent orders
const thirtyDaysAgo = new Date();
thirtyDaysAgo.setDate(thirtyDaysAgo.getDate() - 30);

const orders = await api.queryRecords('orders', {
  dateWindow: {
    field: 'createdAt',
    from: thirtyDaysAgo.toISOString()
  },
  sort: [{ field: 'createdAt', direction: 'desc' }]
});

// High-value customers
const vipCustomers = await api.queryRecords('customers', {
  filter: {
    'data.lifetimeValue': { gte: 10000 },
    status: 'active'
  },
  sort: [{ field: 'data.lifetimeValue', direction: 'desc' }]
});

// Stale records
const sixtyDaysAgo = new Date();
sixtyDaysAgo.setDate(sixtyDaysAgo.getDate() - 60);

const stale = await api.queryRecords('leads', {
  filter: { status: 'pending' },
  dateWindow: {
    field: 'updatedAt',
    to: sixtyDaysAgo.toISOString()
  }
});

Aggregations Quick Reference¶

HTTP API Aggregation¶

POST /workspace/{ws}/api/v1/records/{recordSlug}/aggregate
Content-Type: application/json

{
  "filter": { "status": "completed" },
  "groupBy": ["data.region"],
  "operations": {
    "totalRevenue": { "sum": "data.amount" },
    "avgOrderValue": { "avg": "data.amount" },
    "orderCount": { "count": "*" }
  }
}

Compute Function Aggregation¶

const stats = await api.aggregateRecords('orders', {
  filter: { status: 'completed' },
  groupBy: ['data.region'],
  operations: {
    totalRevenue: { sum: 'data.amount' },
    avgOrderValue: { avg: 'data.amount' },
    maxOrder: { max: 'data.amount' },
    minOrder: { min: 'data.amount' },
    orderCount: { count: '*' }
  }
});

Aggregation Operations¶

Operation	Description	Example
`sum`	Sum of values	`{ sum: 'data.amount' }`
`avg`	Average of values	`{ avg: 'data.amount' }`
`min`	Minimum value	`{ min: 'data.amount' }`
`max`	Maximum value	`{ max: 'data.amount' }`
`count`	Count records	`{ count: '*' }`

Field Naming¶

Top-Level Fields (no prefix needed)¶

id
recordSlug
status
workspaceSlug
createdAt
updatedAt

JSONB Data Fields (use `data.` prefix)¶

data.name
data.email
data.age
data.address.city (nested)

Common Mistakes & Solutions¶

❌ Mistake: Missing `data.` prefix¶

filter: { name: 'John' }  // Won't work for JSONB fields

✅ Solution: Use `data.` prefix¶

filter: { 'data.name': 'John' }  // Correct

❌ Mistake: Using console.log in compute functions¶

console.log('Processing...');  // Not available

✅ Solution: Use api.log()¶

api.log({ message: 'Processing...', count: items.length });

❌ Mistake: Fetching all records at once¶

const result = await api.queryRecords('customers', { pageSize: 10000 });

✅ Solution: Use pagination¶

let page = 1;
while (true) {
  const result = await api.queryRecords('customers', { page, pageSize: 100 });
  if (result.items.length === 0) break;
  // Process batch
  page++;
}

❌ Mistake: Post-filtering in code¶

const result = await api.queryRecords('customers');
const active = result.items.filter(c => c.status === 'active');  // Wasteful

✅ Solution: Filter at database level¶

const result = await api.queryRecords('customers', {
  filter: { status: 'active' }  // Efficient
});

Performance Tips¶

Use filters instead of fetching all and filtering in code
Paginate large datasets (pageSize: 50-100 recommended)
Select specific fields to reduce response size
Use aggregations for statistics instead of fetching all records
Use includeTotal sparingly - only when you need the total count
Index-friendly queries - filter on top-level fields when possible
Batch operations - process records in batches, not one at a time

Limits & Constraints¶

HTTP API¶

Max pageSize: 500
Default pageSize: 50
Query timeout: 30 seconds

Compute Functions¶

Execution timeout: Varies by plan
HTTP calls: Domain-restricted (whitelist required)
No file system access
No Node.js module imports
Use api.log() for logging

Date Format¶

All dates must be ISO 8601 strings
Example: 2024-01-15T10:30:00Z
Use api.formatDate() in compute functions

Need More Help?¶

Full Documentation: See QUERY_GUIDE.md
Architecture: See ARCHITECTURE_RPC_VS_SDK.md
Support: support@centrali.com
Docs: docs.centrali.com