sp00ky Logo
Github|...

Jobs & Usage

Job Table Schema

The job table stores outbox records and tracks their execution status. Here’s the required schema:

sql 
DEFINE TABLE job SCHEMAFULL
  PERMISSIONS
    FOR select, create, update, delete 
    WHERE $access = "account" AND assigned_to.author.id = $auth.id;

-- Link to parent record (e.g., thread, user)
DEFINE FIELD assigned_to ON TABLE job TYPE record<thread>
  PERMISSIONS
    FOR create, select WHERE true
    FOR update WHERE false;

-- API route path
DEFINE FIELD path ON TABLE job TYPE string
  PERMISSIONS
    FOR create, select WHERE true
    FOR update WHERE false;

-- JSON payload for the request
DEFINE FIELD payload ON TABLE job TYPE any
  PERMISSIONS
    FOR create, select WHERE true
    FOR update WHERE false;

-- Retry configuration
DEFINE FIELD retries ON TABLE job TYPE int DEFAULT ALWAYS 0
  PERMISSIONS
    FOR create, select WHERE true
    FOR update WHERE false;

DEFINE FIELD max_retries ON TABLE job TYPE int DEFAULT ALWAYS 3;

DEFINE FIELD retry_strategy ON TABLE job TYPE string DEFAULT ALWAYS "linear"
  ASSERT $value IN ["linear", "exponential"]
  PERMISSIONS
    FOR create, select WHERE true
    FOR update WHERE false;

-- Optional: per-job timeout override (seconds)
DEFINE FIELD timeout ON TABLE job TYPE option<int>
  PERMISSIONS
    FOR create, select WHERE true
    FOR update WHERE false;

-- Job status lifecycle
DEFINE FIELD status ON TABLE job TYPE string DEFAULT ALWAYS "pending"
  ASSERT $value IN ["pending", "processing", "success", "failed"]
  PERMISSIONS
    FOR create, select WHERE true
    FOR update WHERE false;

-- Error tracking
DEFINE FIELD errors ON TABLE job TYPE array<object> DEFAULT ALWAYS []
  PERMISSIONS
    FOR create WHERE true
    FOR select, update WHERE false;

-- Timestamps
DEFINE FIELD updated_at ON TABLE job TYPE datetime
  DEFAULT ALWAYS time::now()
  PERMISSIONS
    FOR create, select WHERE true
    FOR update WHERE false;

DEFINE FIELD created_at ON TABLE job TYPE datetime
  VALUE time::now()
  PERMISSIONS
    FOR create, select WHERE true
    FOR update WHERE false;

Key Fields

  • assigned_to: Links the job to a parent record (optional, but useful for querying jobs by entity)
  • path: The API route path from your OpenAPI spec
  • payload: JSON payload containing the request parameters
  • status: Current job state: pending, processing, success, or failed
  • retries: Number of retry attempts so far
  • max_retries: Maximum number of retry attempts before marking as failed
  • retry_strategy: Retry timing strategy (linear or exponential)
  • timeout: Optional per-job timeout override in seconds (only used if backend has timeoutOverridable: true)
  • errors: Array of error objects from failed execution attempts

Calling Backend Functions

Use the db.run() method to create a job and call your backend function:

TypeScript 
// Call a backend function
await db.run('api', '/spookify', { id: threadData.id }, { 
  assignedTo: threadData.id 
});

Method Signature

TypeScript 
db.run(
  backend: string,      // Backend name from sp00ky.yml
  path: string,          // Route path from OpenAPI spec
  payload: object,       // Request parameters
  options?: {
    assignedTo?: string,           // Link to parent record
    max_retries?: number,          // Override default max retries
    retry_strategy?: 'linear' | 'exponential',  // Override retry strategy
    timeout?: number               // Override backend timeout (seconds)
  }
)

Parameters

  • backend: The name of the backend app from your sp00ky.yml (e.g., 'api')
  • path: The API route path (e.g., '/spookify')
  • payload: An object containing the request parameters defined in your OpenAPI schema
  • options (optional):
    • assignedTo: Record ID to link this job to (useful for querying related jobs)
    • max_retries: Override the default maximum retry attempts (default: 3)
    • retry_strategy: Override the retry strategy (default: 'linear')
    • timeout: Override the backend HTTP request timeout in seconds. Only takes effect if the backend has timeoutOverridable: true in sp00ky.yml.
Warning

The db.run() method validates the payload against your OpenAPI schema. Missing required parameters will throw an error.

Querying Job Status

Jobs are stored as regular SurrealDB records, so you can query them using the query builder. The most common pattern is to use .related() to fetch jobs for a specific entity:

TypeScript 
const threadQuery = db.query('thread')
  .where({ id: 'thread:abc123' })
  .related('jobs', (q) => {
    return q
      .where({ path: '/spookify' })
      .orderBy('created_at', 'desc')
      .limit(1);
  })
  .one()
  .build();

const thread = useQuery(db, () => threadQuery);

// Access the job status
const job = thread()?.jobs?.[0];
const isProcessing = ['pending', 'processing'].includes(job?.status);

This pattern is reactive - when the job runner updates the job status in the database, your UI will automatically reflect the changes.

Complete Example

Here’s a complete example of implementing a “spookify” feature that generates AI content:

TypeScript 
import { useQuery } from '@spooky-sync/client-solid';
import { db } from './db';
import { createSignal } from 'solid-js';

function ThreadDetail() {
  const [isSubmitting, setIsSubmitting] = createSignal(false);

  // Query thread with related jobs
  const threadQuery = db.query('thread')
    .where({ id: 'thread:abc123' })
    .related('jobs', (q) => {
      return q
        .where({ path: '/spookify' })
        .orderBy('created_at', 'desc')
        .limit(1);
    })
    .one()
    .build();

  const thread = useQuery(db, () => threadQuery);

  // Check if job is in progress
  const isJobRunning = () => {
    const job = thread()?.jobs?.[0];
    return ['pending', 'processing'].includes(job?.status);
  };

  // Trigger backend function
  const handleSpookify = async () => {
    setIsSubmitting(true);
    try {
      await db.run('api', '/spookify', 
        { id: thread()?.id },
        { assignedTo: thread()?.id }
      );
    } catch (err) {
      console.error('Failed to spookify:', err);
    } finally {
      setIsSubmitting(false);
    }
  };

  return (
    <div>
      <h1>{thread()?.title}</h1>
      <p>{thread()?.content}</p>

      <button 
        onClick={handleSpookify}
        disabled={isSubmitting() || isJobRunning()}
      >
        {isJobRunning() ? 'Spookifying...' : 'Spookify Thread'}
      </button>

      {thread()?.jobs?.[0]?.status === 'failed' && (
        <div class="error">
          Job failed after {thread()?.jobs?.[0]?.retries} attempts
        </div>
      )}
    </div>
  );
}

Job Runner

The job runner is a separate service that processes outbox jobs. You can use the example job runner from the Sp00ky repository or implement your own.

The job runner:

  1. Polls the job table for records with status = 'pending'
  2. Updates status to 'processing'
  3. Makes the HTTP request to your backend
  4. Updates status to 'success' or 'failed'
  5. Implements retry logic based on retry_strategy
Note

See the packages/job-runner directory in the Sp00ky repository for a reference implementation in Rust.

Best Practices

Always link jobs to their parent entity using assignedTo. This makes it easy to query related jobs:

TypeScript 
await db.run('api', '/process-order', 
  { orderId: order.id },
  { assignedTo: order.id }  // Link job to order
);

Handle Failed Jobs in Your UI

Check the job status and provide feedback to users:

TypeScript 
const job = thread()?.jobs?.[0];

if (job?.status === 'failed') {
  return <ErrorMessage>
    Operation failed after {job.retries} attempts. 
    Please try again later.
  </ErrorMessage>;
}

Set Appropriate Retry and Timeout Policies

For idempotent operations, use higher retry counts. For non-idempotent operations, use lower retry counts or disable retries. For long-running operations like AI inference, increase the timeout:

TypeScript 
// Safe to retry multiple times
await db.run('api', '/generate-content', data, {
  max_retries: 5,
  retry_strategy: 'exponential'
});

// Should not retry
await db.run('api', '/charge-payment', data, {
  max_retries: 0
});

// Long-running AI operation (requires timeoutOverridable: true on backend)
await db.run('agent', '/chat', data, {
  timeout: 120,
  max_retries: 2,
  retry_strategy: 'exponential'
});

Use Permissions to Secure Jobs

The job table should have permissions that prevent users from seeing or modifying other users’ jobs:

sql 
DEFINE TABLE job SCHEMAFULL
  PERMISSIONS
    FOR select, create, update, delete 
    WHERE $access = "account" AND assigned_to.author.id = $auth.id;

This ensures users can only access jobs assigned to records they own.