Complete Guide to Querying Records in Centrali¶

// Using filter object (recommended for compute functions)
const customers = await api.queryRecords('customers', {
  filter: {
    'data.status': 'active'
  },
  page: 1,
  pageSize: 20
});

// Alternative: top-level bracket notation (also works)
const customers = await api.queryRecords('customers', {
  'data.status': 'active',
  page: 1,
  pageSize: 20
});

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
}

api.queryRecords(recordSlug, options)

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

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

  return result.data;
}

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

  return result.data;
}

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

  return result.data;
}

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

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

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

  return result.data;
}

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

  return result.data;
}

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

  return result.data;
}

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

  return result.data;
}

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

  return result.data;
}

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.data;
}

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.data.length
  });

  for (const order of result.data) {
    // 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.data.length,
    timestamp: new Date().toISOString()
  };
}

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.data.length
  });

  let sentCount = 0;
  let failedCount = 0;

  for (const customer of result.data) {
    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.data.length,
    sent: sentCount,
    failed: failedCount
  };
}

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.meta.total} old logs to delete`);

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

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

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()
  };
}

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.data.length} customers eligible for upgrade`);

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

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

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.data.length === 0) {
      break; // No more records
    }

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

    // Process this batch
    for (const customer of result.data) {
      // 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.meta.total) {
      break;
    }

    currentPage++;
  }

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

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;
}

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

  return stats;
}

// 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()
  }
});

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

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

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

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

// 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' }
  ]
});

// 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.data.length === 0) break;

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

  page++;
}

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

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

// 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
});

// 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'
  }
});

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

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

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

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

// Case-insensitive (contains) - use bracket notation at top level
{ 'data.email[contains]': 'GMAIL' } // Matches gmail, Gmail, GMAIL

// Case-sensitive (exact match) - simple equality at top level
{ 'data.status': 'Active' } // Only matches "Active", not "active"