Complete Guide to Querying Records in Centrali¶

This guide covers all the ways to query records in Centrali with comprehensive examples.

Table of Contents¶

Quick Start
HTTP API (REST)
Compute Functions (SDK)
Aggregations
Filter Reference
Common Patterns
Best Practices
Troubleshooting

Quick Start¶

Which method should I use?¶

Building a frontend? → Use HTTP API
Writing automation/workflows? → Use Compute Functions (SDK)
Need analytics/reports? → Use Aggregations

Architecture Flow¶

┌──────────────────────────────────────────────────────────────────┐
│                      QUERYING IN CENTRALI                        │
└──────────────────────────────────────────────────────────────────┘

HTTP API Flow (Frontend/External Clients):
┌─────────────┐
│   Browser   │  HTTP GET /workspace/acme/api/v1/records/customers
│  or Client  │  ?status=active&page=1&pageSize=20
└──────┬──────┘
       │
       │ HTTP/REST + JWT Token
       │
       ▼
┌─────────────────────────────┐
│    Data Service (Express)   │
│  • Routes                   │
│  • Controllers              │
│  • Services                 │
│  • Repositories             │
└──────────────┬──────────────┘
               │
               ▼
         ┌──────────┐
         │PostgreSQL│
         └──────────┘


Compute Function Flow (Automation/Workflows):
┌──────────────────────────────┐
│     Compute Function         │
│  (Your JavaScript Code)      │
│                              │
│  const result = await        │
│    api.queryRecords(...)     │
└──────────────┬───────────────┘
               │
               │ SDK Methods (api.queryRecords)
               │
               ▼
┌──────────────────────────────┐
│      Compute Service         │
│  • Creates API object        │
│  • Injects workspace/user    │
│  • Sandboxes execution       │
└──────────────┬───────────────┘
               │
               │ RPC via NATS
               │ (Internal - You don't see this)
               ▼
┌──────────────────────────────┐
│    Data Service (RPC)        │
│  • RPC Handlers              │
│  • Services                  │
│  • Repositories              │
└──────────────┬───────────────┘
               │
               ▼
         ┌──────────┐
         │PostgreSQL│
         └──────────┘


Query Processing (Both flows end here):
┌─────────────────────────────────────────────────────────────┐
│                  RecordQueryBuilder                         │
│  ┌───────────────────────────────────────────────────────┐  │
│  │  1. Normalize: Parse & validate query options        │  │
│  │     • search, searchFields, filters, sort, page      │  │
│  │  2. Validate: Check fields against structure schema  │  │
│  │  3. Build SQL: Generate Knex query with filters      │  │
│  │  4. Execute: Run query against PostgreSQL            │  │
│  │  5. Transform: Apply computed fields, format result  │  │
│  └───────────────────────────────────────────────────────┘  │
└─────────────────────────────────────────────────────────────┘

Basic Example¶

HTTP API:

GET /workspace/acme/api/v1/records/customers?status=active&page=1&pageSize=20

Compute Function:

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

HTTP API (REST)¶

Base Endpoint¶

GET /workspace/{workspaceSlug}/api/v1/records/{recordSlug}

Authentication¶

All requests require a JWT token in the Authorization header:

Authorization: Bearer YOUR_JWT_TOKEN

Query Parameters¶

Parameter	Type	Description	Example
`search`	string	Search term to match	`?search=john`
`searchFields`	string	Comma-separated fields to search (default: uses structure's `defaultSearchField`)	`?searchFields=name,email`
`page`	number	Page number (1-indexed)	`?page=2`
`pageSize`	number	Records per page (1-500, default: 50)	`?pageSize=25`
`sort`	string	Sort fields (prefix `-` for descending)	`?sort=-createdAt,name`
`fields`	string	Comma-separated fields to return	`?fields=id,name,email`
`all`	boolean	Include soft-deleted records	`?all=true`
`{field}`	any	Filter by field (exact match)	`?status=active`
`{field}[op]`	any	Filter with operator	`?age[gte]=18`

Basic Query Examples¶

1. Get All Records¶

GET /workspace/acme/api/v1/records/customers

Response:

name=__codelineno-6-1 href=#__codelineno-6-1>{ "data": [ { "id": "rec-123", "recordSlug": "customers", "data": { "name": "John Doe", "email": "john@example.com", "age": 30 }, "status": "active", "createdAt": "2024-01-15T10:00:00Z", "updatedAt": "2024-01-15T10:00:00Z" } ], "meta": { "page": 1, "pageSize": 50 } class=p>}

2. Search by Name¶

GET /workspace/acme/api/v1/records/customers?search=john&searchFields=name,email

Searches for "john" in both name and email fields (case-insensitive).

3. Filter by Status¶

GET /workspace/acme/api/v1/records/customers?status=active

Returns only customers where status equals "active".

4. Paginate Results¶

GET /workspace/acme/api/v1/records/customers?page=2&pageSize=25

Returns records 26-50 (page 2 with 25 records per page).

5. Sort Results¶

# Sort by createdAt descending
GET /workspace/acme/api/v1/records/customers?sort=-createdAt

# Multi-sort: status ascending, then createdAt descending
GET /workspace/acme/api/v1/records/customers?sort=status,-createdAt

6. Select Specific Fields¶

GET /workspace/acme/api/v1/records/customers?fields=id,name,email

Returns only the specified fields (reduces response size).

Advanced Filtering¶

Filter Operators¶

Use bracket notation field[operator] or dot notation field.operator:

# Age greater than or equal to 18
GET /workspace/acme/api/v1/records/customers?age[gte]=18

# Or with dot notation
GET /workspace/acme/api/v1/records/customers?age.gte=18

Available operators: - eq - Equal (default) - ne - Not equal - gt - Greater than - gte - Greater than or equal - lt - Less than - lte - Less than or equal - in - In list (comma-separated) - nin - Not in list - contains - Contains substring - startswith - Starts with - endswith - Ends with - hasAny - Array has any of (for array fields) - hasAll - Array has all of (for array fields)

Complex Filter Examples¶

Age between 18 and 65:

GET /workspace/acme/api/v1/records/customers?age[gte]=18&age[lte]=65

Status is active OR pending:

GET /workspace/acme/api/v1/records/customers?status[in]=active,pending

Email contains "@gmail.com":

GET /workspace/acme/api/v1/records/customers?email[contains]=@gmail.com

Name starts with "John":

GET /workspace/acme/api/v1/records/customers?name[startswith]=John

Tags has any of ["vip", "enterprise"]:

GET /workspace/acme/api/v1/records/customers?tags[hasAny]=vip,enterprise

Created after January 1, 2024:

GET /workspace/acme/api/v1/records/customers?createdAt[gte]=2024-01-01T00:00:00Z

Combining Everything¶

GET /workspace/acme/api/v1/records/customers?\
  search=john&\
  searchFields=name,email&\
  status[in]=active,pending&\
  age[gte]=18&\
  age[lte]=65&\
  createdAt[gte]=2024-01-01&\
  sort=-createdAt&\
  page=1&\
  pageSize=25&\
  fields=id,name,email,age,status

This query: - Searches for "john" in name and email - Filters for active or pending status - Filters for age between 18-65 - Filters for records created after Jan 1, 2024 - Sorts by creation date (newest first) - Returns page 1 with 25 results - Returns only id, name, email, age, and status fields

JavaScript/TypeScript Example¶

async function fetchCustomers() {
  const params = new URLSearchParams({
    search: 'john',
    searchFields: 'name,email',
    status: 'active',
    'age[gte]': '18',
    page: '1',
    pageSize: '25',
    sort: '-createdAt'
  });

  const response = await fetch(
    `/workspace/acme/api/v1/records/customers?${params}`,
    {
      headers: {
        'Authorization': `Bearer ${token}`,
        'Content-Type': 'application/json'
      }
    }
  );

  const result = await response.json();
  return result.data;
}

Compute Functions (SDK)¶

When to Use Compute Functions¶

Use compute functions for: - Automated workflows - Scheduled tasks - Webhooks and event-driven logic - Complex business logic - Batch processing - Data transformations

Basic Structure¶

async function run() {
  // The `api` object is automatically available as a global
  // The `triggerParams` and `executionParams` are also available as globals

  // Available API methods:
  // - api.queryRecords() - Query records
  // - api.aggregateRecords() - Run aggregations
  // - api.fetchRecord() - Get single record by ID
  // - api.fetchRecordByUniqueField() - Get by unique field
  // - api.createRecord() - Create new record
  // - api.updateRecord() - Update record
  // - api.deleteRecord() - Delete record
  // - api.incrementField() - Atomic increment
  // - api.decrementField() - Atomic decrement
  // - api.log() - Log messages
  // - api.formatDate() - Format dates (uses dayjs)
  // - api.uuid() - Generate UUID
  // - api.lodash - Limited lodash functions (chunk, flatten, uniq, merge)
  // - api.math - Math operations (evaluate, add, subtract, multiply, divide, random)
  // - api.httpGet/Post/Put/Delete() - HTTP calls (domain-restricted)

  // Your code here
}

Important Constraints: - No direct Node.js imports (fs, path, etc.) - No console.log - use api.log() instead - HTTP calls are domain-restricted (domains must be whitelisted) - No access to file system or environment variables - All dates use ISO 8601 strings

Query Method¶

api.queryRecords(recordSlug, options)

Parameters:

recordSlug (string): The slug of the record structure (e.g., "customers")
options (object): Query options

Options:

{
  filter?: {
    // Field filters using operators
    [fieldName]: value | { operator: value }
  },
  search?: string,              // Search term
  searchFields?: string[],      // Fields to search in
  page?: number,                // Page number (default: 1)
  pageSize?: number,            // Records per page (default: 50, max: 500)
  sort?: Array<{                // Sort options
    field: string,
    direction: 'asc' | 'desc'
  }>,
  includeDeleted?: boolean,     // Include soft-deleted records
  includeTotal?: boolean,       // Include total count in response
  dateWindow?: {                // Date range filter
    field: string,              // Usually 'createdAt' or 'updatedAt'
    from?: string,              // ISO date string
    to?: string                 // ISO date string
  }
}

Returns:

{
  items: Array<Record>,    // Array of matching records
  total?: number,          // Total count (if includeTotal: true)
  page: number,            // Current page
  pageSize: number         // Page size
}

Basic Examples¶

1. Get All Records¶

async function run() {
  const result = await api.queryRecords('customers');

  api.log({
    message: `Found ${result.items.length} customers`,
    count: result.items.length
  });

  return result.items;
}

2. Search by Name¶

async function run() {
  const result = await api.queryRecords('customers', {
    search: 'john',
    searchFields: ['name', 'email']
  });

  return result.items;
}

3. Filter by Status¶

async function run() {
  const result = await api.queryRecords('customers', {
    filter: {
      status: 'active'
    }
  });

  return result.items;
}

4. Paginate Results¶

async function run() {
  const result = await api.queryRecords('customers', {
    page: 2,
    pageSize: 25
  });

  api.log(`Page ${result.page} of customers`);
  return result.items;
}

5. Sort Results¶

async function run() {
  const result = await api.queryRecords('customers', {
    sort: [
      { field: 'createdAt', direction: 'desc' }
    ],
    pageSize: 10
  });

  return result.items;
}

Advanced Filtering Examples¶

Age Range Filter¶

async function run() {
  const result = await api.queryRecords('customers', {
    filter: {
      'data.age': { gte: 18, lte: 65 }
    }
  });

  return result.items;
}

Multiple Filters¶

async function run() {
  const result = await api.queryRecords('customers', {
    filter: {
      status: 'active',
      'data.age': { gte: 18 },
      'data.verified': true
    }
  });

  return result.items;
}

IN Operator (Multiple Values)¶

async function run() {
  const result = await api.queryRecords('customers', {
    filter: {
      status: { in: ['active', 'pending', 'trial'] }
    }
  });

  return result.items;
}

String Operators¶

async function run() {
  const result = await api.queryRecords('customers', {
    filter: {
      'data.email': { contains: '@gmail.com' }
    }
  });

  return result.items;
}

Date Range Filter¶

async function run() {
  const thirtyDaysAgo = new Date();
  thirtyDaysAgo.setDate(thirtyDaysAgo.getDate() - 30);

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

  return result.items;
}

Real-World Use Cases¶

1. Process Pending Orders¶

async function run() {
  const result = await api.queryRecords('orders', {
    filter: {
      status: 'pending',
      'data.paymentReceived': true
    },
    sort: [
      { field: 'createdAt', direction: 'asc' }
    ],
    pageSize: 50  // Process in batches
  });

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

  for (const order of result.items) {
    // Generate tracking number
    const trackingNumber = `TRK-${api.uuid().substring(0, 8).toUpperCase()}`;

    // Get current timestamp
    const now = api.formatDate(new Date().toISOString(), 'YYYY-MM-DD HH:mm:ss');

    // Update order status
    await api.updateRecord(order.id, {
      status: 'processing',
      'data.processedAt': now,
      'data.trackingNumber': trackingNumber
    });

    api.log({
      message: 'Processed order',
      orderId: order.id,
      trackingNumber
    });
  }

  return {
    processed: result.items.length,
    timestamp: new Date().toISOString()
  };
}

2. Send Reminder Emails¶

async function run() {
  // Calculate date 7 days from now
  const today = new Date();
  const sevenDaysFromNow = new Date(today);
  sevenDaysFromNow.setDate(sevenDaysFromNow.getDate() + 7);

  const result = await api.queryRecords('customers', {
    filter: {
      'data.plan': 'trial',
      'data.trialEndDate': {
        gte: today.toISOString(),
        lte: sevenDaysFromNow.toISOString()
      },
      'data.reminderSent': { ne: true }
    },
    pageSize: 100
  });

  api.log({
    message: 'Found customers to remind',
    count: result.items.length
  });

  let sentCount = 0;
  let failedCount = 0;

  for (const customer of result.items) {
    try {
      // Format trial end date for display
      const trialEndFormatted = api.formatDate(
        customer.data.trialEndDate,
        'MMMM D, YYYY'
      );

      // Send reminder email via whitelisted email service
      // Note: email-service.com must be in your domain whitelist
      await api.httpPost('https://email-service.com/send', {
        to: customer.data.email,
        subject: 'Your trial is expiring soon',
        template: 'trial-expiration',
        data: {
          customerName: customer.data.name,
          trialEndDate: trialEndFormatted
        }
      });

      // Mark reminder as sent with timestamp
      await api.updateRecord(customer.id, {
        'data.reminderSent': true,
        'data.reminderSentAt': new Date().toISOString()
      });

      sentCount++;
    } catch (error) {
      api.log({
        message: 'Failed to send reminder',
        customerId: customer.id,
        error: error.message
      });
      failedCount++;
    }
  }

  return {
    total: result.items.length,
    sent: sentCount,
    failed: failedCount
  };
}

3. Data Cleanup Job¶

async function run() {
  const oneYearAgo = new Date();
  oneYearAgo.setFullYear(oneYearAgo.getFullYear() - 1);

  const result = await api.queryRecords('logs', {
    filter: {
      status: 'archived'
    },
    dateWindow: {
      field: 'createdAt',
      to: oneYearAgo.toISOString()
    },
    includeTotal: true
  });

  api.log(`Found ${result.total} old logs to delete`);

  // Process in batches
  let deletedCount = 0;
  for (const log of result.items) {
    await api.deleteRecord(log.id, true); // hard delete
    deletedCount++;
  }

  return {
    total: result.total,
    deleted: deletedCount
  };
}

4. Generate Monthly Report¶

async function run() {
  const startOfMonth = new Date();
  startOfMonth.setDate(1);
  startOfMonth.setHours(0, 0, 0, 0);
  const endOfMonth = new Date();

  api.log({
    message: 'Generating monthly report',
    period: {
      start: api.formatDate(startOfMonth.toISOString(), 'YYYY-MM-DD'),
      end: api.formatDate(endOfMonth.toISOString(), 'YYYY-MM-DD')
    }
  });

  // Get aggregated stats first (faster than fetching all records)
  const stats = await api.aggregateRecords('orders', {
    filter: {
      createdAt: { gte: startOfMonth.toISOString() }
    },
    operations: {
      totalRevenue: { sum: 'data.amount' },
      avgOrderValue: { avg: 'data.amount' },
      maxOrderValue: { max: 'data.amount' },
      orderCount: { count: '*' }
    }
  });

  // Get top 10 customers by order count
  const topCustomers = await api.aggregateRecords('orders', {
    filter: {
      createdAt: { gte: startOfMonth.toISOString() }
    },
    groupBy: ['data.customerId'],
    operations: {
      orderCount: { count: '*' },
      totalSpent: { sum: 'data.amount' }
    }
  });

  // Sort by total spent and take top 10
  const sortedCustomers = api.lodash.chunk(
    topCustomers.sort((a, b) => b.totalSpent - a.totalSpent),
    10
  )[0] || [];

  // Calculate growth compared to previous month
  const prevMonthStart = new Date(startOfMonth);
  prevMonthStart.setMonth(prevMonthStart.getMonth() - 1);

  const prevMonthStats = await api.aggregateRecords('orders', {
    filter: {
      createdAt: {
        gte: prevMonthStart.toISOString(),
        lt: startOfMonth.toISOString()
      }
    },
    operations: {
      totalRevenue: { sum: 'data.amount' },
      orderCount: { count: '*' }
    }
  });

  // Calculate growth percentages
  const revenueGrowth = prevMonthStats.totalRevenue
    ? api.math.multiply(
        api.math.divide(
          api.math.subtract(stats.totalRevenue, prevMonthStats.totalRevenue),
          prevMonthStats.totalRevenue
        ),
        100
      )
    : 0;

  const orderGrowth = prevMonthStats.orderCount
    ? api.math.multiply(
        api.math.divide(
          api.math.subtract(stats.orderCount, prevMonthStats.orderCount),
          prevMonthStats.orderCount
        ),
        100
      )
    : 0;

  api.log({
    message: 'Report generated successfully',
    revenue: stats.totalRevenue,
    orders: stats.orderCount,
    revenueGrowth: `${revenueGrowth.toFixed(1)}%`
  });

  return {
    period: {
      start: api.formatDate(startOfMonth.toISOString(), 'MMMM D, YYYY'),
      end: api.formatDate(endOfMonth.toISOString(), 'MMMM D, YYYY')
    },
    currentMonth: {
      totalRevenue: stats.totalRevenue,
      avgOrderValue: stats.avgOrderValue,
      maxOrderValue: stats.maxOrderValue,
      orderCount: stats.orderCount
    },
    growth: {
      revenue: `${revenueGrowth.toFixed(1)}%`,
      orders: `${orderGrowth.toFixed(1)}%`
    },
    topCustomers: sortedCustomers,
    generatedAt: new Date().toISOString()
  };
}

5. Batch Update Based on Criteria¶

async function run() {
  // Find customers eligible for upgrade
  const result = await api.queryRecords('customers', {
    filter: {
      'data.plan': 'basic',
      'data.revenue': { gte: 1000 },
      'data.accountAge': { gte: 90 } // days
    }
  });

  api.log(`Found ${result.items.length} customers eligible for upgrade`);

  for (const customer of result.items) {
    await api.updateRecord(customer.id, {
      'data.plan': 'premium',
      'data.upgradedAt': new Date().toISOString(),
      'data.upgradeReason': 'automatic-qualification'
    });
  }

  return { upgraded: result.items.length };
}

Pagination Handling¶

Process All Records in Batches¶

async function run() {
  let currentPage = 1;
  const pageSize = 100;
  let totalProcessed = 0;

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

    if (result.items.length === 0) {
      break; // No more records
    }

    api.log(`Processing page ${currentPage}, ${result.items.length} records`);

    // Process this batch
    for (const customer of result.items) {
      // Do something with customer
      await api.updateRecord(customer.id, {
        'data.lastProcessed': new Date().toISOString()
      });
      totalProcessed++;
    }

    // Check if we've processed all records
    if (totalProcessed >= result.total) {
      break;
    }

    currentPage++;
  }

  api.log(`Processed ${totalProcessed} customers total`);
  return { processed: totalProcessed };
}

Aggregations¶

Aggregations allow you to compute statistics (sum, avg, min, max, count) across records.

HTTP API Aggregations¶

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

{
  "filter": { "status": "completed" },
  "operations": {
    "totalRevenue": { "sum": "data.amount" },
    "avgOrderValue": { "avg": "data.amount" },
    "maxOrderValue": { "max": "data.amount" },
    "minOrderValue": { "min": "data.amount" },
    "orderCount": { "count": "*" }
  }
}

Response:

{
  "totalRevenue": 125000.50,
  "avgOrderValue": 250.00,
  "maxOrderValue": 5000.00,
  "minOrderValue": 10.00,
  "orderCount": 500
}

SDK Aggregations¶

async function run() {
  const stats = await api.aggregateRecords('orders', {
    filter: {
      status: 'completed',
      createdAt: { gte: '2024-01-01T00:00:00Z' }
    },
    operations: {
      totalRevenue: { sum: 'data.amount' },
      avgOrderValue: { avg: 'data.amount' },
      orderCount: { count: '*' }
    }
  });

  return stats;
}

Group By¶

async function run() {
  const stats = await api.aggregateRecords('orders', {
    filter: { status: 'completed' },
    groupBy: ['data.region'],
    operations: {
      totalRevenue: { sum: 'data.amount' },
      orderCount: { count: '*' }
    }
  });

  return stats;
}

Result:

[
  {
    "data.region": "US",
    "totalRevenue": 75000,
    "orderCount": 300
  },
  {
    "data.region": "EU",
    "totalRevenue": 50000,
    "orderCount": 200
  }
]

Filter Reference¶

Comparison Operators¶

Operator	Description	Example
`eq`	Equal (default)	`{ age: 18 }` or `{ age: { eq: 18 } }`
`ne`	Not equal	`{ status: { ne: 'deleted' } }`
`gt`	Greater than	`{ age: { gt: 18 } }`
`gte`	Greater than or equal	`{ age: { gte: 18 } }`
`lt`	Less than	`{ age: { lt: 65 } }`
`lte`	Less than or equal	`{ age: { lte: 65 } }`

List Operators¶

Operator	Description	Example
`in`	Value is in list	`{ status: { in: ['active', 'pending'] } }`
`nin`	Value not in list	`{ status: { nin: ['deleted', 'banned'] } }`

String Operators¶

Operator	Description	Example
`contains`	Contains substring (case-insensitive)	`{ email: { contains: '@gmail.com' } }`
`startswith`	Starts with (case-insensitive)	`{ name: { startswith: 'John' } }`
`endswith`	Ends with (case-insensitive)	`{ email: { endswith: '.com' } }`

Array Operators¶

Operator	Description	Example
`hasAny`	Array contains any of the values	`{ tags: { hasAny: ['vip', 'premium'] } }`
`hasAll`	Array contains all of the values	`{ permissions: { hasAll: ['read', 'write'] } }`

Field Naming¶

Top-level fields (access directly): - id - recordSlug - status - workspaceSlug - createdAt - updatedAt

JSONB data fields (use data. prefix): - data.name - data.email - data.age - data.address.city (nested)

Common Patterns¶

1. Recent Records¶

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

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

2. Active and Verified Users¶

const result = await api.queryRecords('users', {
  filter: {
    status: 'active',
    'data.emailVerified': true,
    'data.phoneVerified': true
  }
});

3. High-Value Customers¶

const result = await api.queryRecords('customers', {
  filter: {
    'data.lifetimeValue': { gte: 10000 },
    'data.plan': { in: ['premium', 'enterprise'] }
  },
  sort: [
    { field: 'data.lifetimeValue', direction: 'desc' }
  ]
});

4. Stale Records¶

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

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

5. Complex Business Logic¶

// Find customers who:
// - Are active
// - Have made at least 3 purchases
// - Have lifetime value > $500
// - Haven't been contacted in 30 days

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

const result = await api.queryRecords('customers', {
  filter: {
    status: 'active',
    'data.purchaseCount': { gte: 3 },
    'data.lifetimeValue': { gt: 500 },
    'data.lastContactDate': { lt: thirtyDaysAgo.toISOString() }
  },
  sort: [
    { field: 'data.lifetimeValue', direction: 'desc' }
  ]
});

Best Practices¶

1. Use Pagination for Large Datasets¶

// DON'T: Try to fetch all records at once
const result = await api.queryRecords('customers', {
  pageSize: 10000 // ❌ May timeout or run out of memory
});

// DO: Process in smaller batches
let page = 1;
while (true) {
  const result = await api.queryRecords('customers', {
    page: page,
    pageSize: 100
  });

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

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

  page++;
}

2. Use Filters Instead of Post-Processing¶

// DON'T: Fetch all and filter in code
const result = await api.queryRecords('customers');
const activeCustomers = result.items.filter(c => c.status === 'active'); // ❌ Wasteful

// DO: Filter at the database level
const result = await api.queryRecords('customers', {
  filter: { status: 'active' } // ✅ Efficient
});

3. Request Only Fields You Need¶

# DON'T: Fetch all fields
GET /workspace/acme/api/v1/records/customers

# DO: Select specific fields
GET /workspace/acme/api/v1/records/customers?fields=id,name,email

4. Use includeTotal Sparingly¶

// Only request total count when you actually need it
const result = await api.queryRecords('customers', {
  filter: { status: 'active' },
  includeTotal: true // Only if you need the total count for UI/reporting
});

5. Leverage Indexes¶

Structure your filters to take advantage of database indexes: - Filter on top-level fields (id, status, createdAt, updatedAt) when possible - These are automatically indexed - JSONB fields (data.*) may not be indexed

6. Use Date Windows for Time-Based Queries¶

// More efficient than filtering large date ranges
const result = await api.queryRecords('orders', {
  dateWindow: {
    field: 'createdAt',
    from: '2024-01-01T00:00:00Z',
    to: '2024-12-31T23:59:59Z'
  }
});

7. Handle Errors Gracefully¶

async function run() {
  try {
    const result = await api.queryRecords('customers', {
      filter: { status: 'active' }
    });

    return result.items;
  } catch (error) {
    api.log(`Query failed: ${error.message}`);
    return []; // Return empty array instead of throwing
  }
}

Troubleshooting¶

Empty Results¶

Issue: Query returns no results but you expect records.

Solutions: 1. Check field names (remember data. prefix for JSONB fields) 2. Verify filter values match exactly (status: "active" vs "Active") 3. Check if records exist: remove all filters and search 4. Verify you're querying the correct recordSlug

// Debug: Remove all filters first
const all = await api.queryRecords('customers'); // Get everything
api.log(`Total records: ${all.items.length}`);

// Then add filters one by one
const filtered = await api.queryRecords('customers', {
  filter: { status: 'active' }
});
api.log(`After status filter: ${filtered.items.length}`);

Query Timeout¶

Issue: Query takes too long or times out.

Solutions: 1. Reduce pageSize 2. Add more specific filters 3. Use pagination instead of fetching all at once 4. Consider using aggregations for large datasets

Invalid Field Error¶

Issue: "Property 'x' does not exist in structure"

Solutions: 1. Use data. prefix for JSONB fields: data.name not name 2. Check field name spelling 3. Verify field exists in the structure schema

Case Sensitivity¶

Issue: String filters not matching as expected.

Solution: String operators (contains, startswith, endswith) are case-insensitive. Use exact match if case matters:

// Case-insensitive (contains)
filter: { 'data.email': { contains: 'GMAIL' } } // Matches gmail, Gmail, GMAIL

// Case-sensitive (exact match)
filter: { 'data.status': 'Active' } // Only matches "Active", not "active"

Additional Resources¶

API Reference: Full REST API documentation
Structure Schema: Define your record structures
Compute Functions: Advanced workflow automation
Webhooks: Event-driven integrations

Support¶

Need help? Contact support@centrali.com or check our documentation at docs.centrali.com.