Cron Jobs

Cron jobs allow you to schedule recurring tasks to run at specific intervals. VSKI supports both SQL-based and HTTP-based cron jobs for different use cases.

Note

Cron jobs are disabled on replicas. Only exceptions are SQL tasks in replica's own stats.db and workflows.db which are not replicated.

Creating Cron Jobs

SQL Cron Jobs

Execute SQL queries on a schedule:

await client.cron.create({
  name: "daily-cleanup",
  schedule: "0 2 * * *", // 2 AM daily
  type: "sql",
  sql: "DELETE FROM logs WHERE created < datetime('now', '-30 days')",
});

Target Database

By default, SQL jobs execute against the default database. You can specify a target database:

await client.cron.create({
  name: "cleanup-tenant-logs",
  schedule: "0 2 * * *",
  type: "sql",
  database: "tenant1", // Execute against a specific database
  sql: "DELETE FROM logs WHERE created < datetime('now', '-30 days')",
});

This is useful in multi-tenant scenarios where each tenant has their own database.

HTTP GET Cron Jobs

Make HTTP GET requests to external services:

await client.cron.create({
  name: "health-check",
  schedule: "*/5 * * * *", // Every 5 minutes
  type: "http",
  url: "https://api.example.com/health",
  method: "GET",
  headers: {
    "Authorization": "Bearer your-token",
  },
});

HTTP POST Cron Jobs

Send data to external endpoints:

await client.cron.create({
  name: "daily-report",
  schedule: "0 8 * * *", // 8 AM daily
  type: "http",
  url: "https://api.example.com/reports",
  method: "POST",
  headers: {
    "Content-Type": "application/json",
    "X-API-Key": "your-api-key",
  },
  body: JSON.stringify({ type: "daily" }),
  contentType: "json",
});

Cron Schedule Syntax

VSKI uses standard cron syntax with 5 fields:

* * * * *
│ │ │ │ │
│ │ │ │ └─ Day of week (0-6, Sunday = 0)
│ │ │ └─── Month (1-12)
│ │ └───── Day of month (1-31)
│ └─────── Hour (0-23)
└───────── Minute (0-59)

Common Schedules

// Every 5 minutes
"*/5 * * * *";

// Every hour
"0 * * * *";

// Daily at midnight
"0 0 * * *";

// Daily at 2 AM
"0 2 * * *";

// Weekly on Sunday at midnight
"0 0 * * 0";

// Monthly on the 1st at midnight
"0 0 1 * *";

// Every weekday at 9 AM
"0 9 * * 1-5";

// Every 6 hours
"0 */6 * * *";

// Twice a day (at 6 AM and 6 PM)
"0 6,18 * * *";

// Every 30 minutes during business hours (9 AM - 5 PM)
"*/30 9-17 * * *";

Managing Cron Jobs

List All Cron Jobs

const jobs = await client.cron.list();

jobs.forEach((job) => {
  console.log(`${job.name}: ${job.schedule}`);
  console.log(`Database: ${job.database}`); // Target database for SQL jobs
  console.log(`Command: ${job.command}`); // SQL query or HTTP URL with headers/body
  console.log(`Active: ${job.active}`);
});

Get Job Details

const jobs = await client.cron.list();
const job = jobs.find((j) => j.name === "daily-cleanup");

console.log(job);

Delete Cron Job

await client.cron.delete("daily-cleanup");

Cron Job Configuration

Type: sql

{
  name: string; // Unique job name
  schedule: string; // Cron expression
  type: "sql"; // Job type
  database?: string; // Target database (default: "default")
  sql: string; // SQL query to execute
}

Type: http

{
  name: string;                           // Unique job name
  schedule: string;                       // Cron expression
  type: "http";                          // Job type
  url: string;                            // Target URL
  method?: "GET" | "POST" | "PUT" | "DELETE";  // HTTP method
  headers?: Record<string, string>;         // Request headers
  body?: string;                          // Request body
  contentType?: "json" | "urlencoded";    // Content type
}

Practical Examples

Database Cleanup

// Delete old logs daily (default database)
await client.cron.create({
  name: "cleanup-old-logs",
  schedule: "0 2 * * *",
  type: "sql",
  sql: `
    DELETE FROM logs
    WHERE created < datetime('now', '-30 days')
  `,
});

// Delete old logs from a specific tenant database
await client.cron.create({
  name: "cleanup-tenant-logs",
  schedule: "0 2 * * *",
  type: "sql",
  database: "tenant_acme",
  sql: `
    DELETE FROM logs
    WHERE created < datetime('now', '-30 days')
  `,
});

// Delete soft-deleted records weekly
await client.cron.create({
  name: "purge-deleted",
  schedule: "0 3 * * 0", // 3 AM every Sunday
  type: "sql",
  sql: `
    DELETE FROM records
    WHERE deletedAt IS NOT NULL
      AND deletedAt < datetime('now', '-7 days')
  `,
});

Data Aggregation

// Calculate daily statistics
await client.cron.create({
  name: "daily-stats",
  schedule: "0 0 * * *",
  type: "sql",
  sql: `
    INSERT INTO daily_stats (date, metric, value)
    SELECT
      date(created) as date,
      'views' as metric,
      SUM(views) as value
    FROM posts
    WHERE date(created) = date('now', '-1 day')
    GROUP BY date(created)
  `,
});

External Service Integration

// Sync data to external API
await client.cron.create({
  name: "sync-to-crm",
  schedule: "0 */2 * * *", // Every 2 hours
  type: "http",
  url: "https://crm.example.com/sync",
  method: "POST",
  headers: {
    "Authorization": `Bearer ${CRM_API_KEY}`,
    "Content-Type": "application/json",
  },
  body: JSON.stringify({ source: "vski" }),
  contentType: "json",
});

// Send health check to monitoring service
await client.cron.create({
  name: "health-monitor",
  schedule: "*/5 * * * *",
  type: "http",
  url: "https://monitor.example.com/health/vski",
  method: "POST",
  headers: {
    "Content-Type": "application/json",
  },
  body: JSON.stringify({ status: "ok" }),
});

Backup Tasks

// Trigger backup via HTTP webhook
await client.cron.create({
  name: "daily-backup",
  schedule: "0 1 * * *", // 1 AM daily
  type: "http",
  url: "https://backup-service.com/trigger",
  method: "POST",
  headers: {
    "Authorization": `Bearer ${BACKUP_API_KEY}`,
  },
  body: JSON.stringify({ database: "production" }),
});

// Log backup completion
await client.cron.create({
  name: "log-backup-status",
  schedule: "0 2 * * *", // 2 AM daily
  type: "sql",
  sql: `
    INSERT INTO backup_logs (date, status, notes)
    VALUES (datetime('now'), 'completed', 'Daily backup')
  `,
});

Email Reports

// Send daily summary email
await client.cron.create({
  name: "daily-email-report",
  schedule: "0 9 * * *", // 9 AM daily
  type: "http",
  url: "https://email-service.com/send",
  method: "POST",
  headers: {
    "Authorization": `Bearer ${EMAIL_API_KEY}`,
    "Content-Type": "application/json",
  },
  body: JSON.stringify({
    template: "daily-report",
    recipients: ["admin@example.com"],
    data: { source: "vski" },
  }),
});

Cache Warming

// Refresh cache every 10 minutes
await client.cron.create({
  name: "cache-warmup",
  schedule: "*/10 * * * *",
  type: "http",
  url: "https://api.example.com/cache/warm",
  method: "POST",
  headers: {
    "Authorization": `Bearer ${API_KEY}`,
  },
  body: JSON.stringify({ keys: ["popular", "featured"] }),
});

Cron Job Execution

Cron jobs execute automatically based on their schedule. The system:

1. Checks for jobs due to run every minute
2. Executes the job (SQL query or HTTP request)
3. Logs the execution result
4. Handles errors appropriately
5. Continues on schedule even if previous runs fail

Error Handling

SQL Job Errors

// SQL errors are logged but don't stop the job from running again
await client.cron.create({
  name: "risky-sql-job",
  schedule: "0 0 * * *",
  type: "sql",
  sql: "DELETE FROM temp_table", // May fail if table doesn't exist
});

HTTP Job Errors

// HTTP job errors are logged with status code and response
await client.cron.create({
  name: "external-api-call",
  schedule: "*/30 * * * *",
  type: "http",
  url: "https://api.example.com/sync",
  method: "POST",
  headers: {
    "Authorization": "Bearer expired-token",
  },
});
// Will fail with 401, log error, and retry on next schedule

Best Practices

1. Test schedules - Test cron expressions with tools like crontab.guru
2. Use specific times - Avoid running all jobs at the same time
3. Error handling - Make jobs resilient to transient failures
4. Logging - Include logging in SQL jobs for debugging
5. Time zones - Be aware of server time zone when setting schedules
6. Resource limits - Avoid heavy operations during peak hours
7. Idempotency - Make HTTP jobs idempotent to handle retries
8. Monitoring - Monitor cron job execution and failures
9. Backup - Backup data before running destructive SQL jobs
10. Documentation - Document what each cron job does

API Endpoints

Method	Endpoint	Description
GET	/api/cron	List all cron jobs
POST	/api/cron	Create new cron job
DELETE	/api/cron/:name	Delete cron job

Testing Cron Jobs

You can test cron jobs by setting a short schedule for development:

// For testing: run every minute
await client.cron.create({
  name: "test-job",
  schedule: "* * * * *",
  type: "sql",
  sql: "INSERT INTO test_log (message) VALUES ('Cron job ran')",
});

// Verify execution after a minute
const logs = await client.collection("test_log").getList(1, 10);
console.log(logs.items);

Remember to update the schedule before deploying to production!