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: minimum delay (milliseconds) the job stays pending before it runs
DEFINE FIELD delay ON TABLE job TYPE int DEFAULT ALWAYS 0
  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;

-- Optional: owning SSP node id, set by the system for cluster recovery
DEFINE FIELD assignee ON TABLE job TYPE option<string>
  PERMISSIONS
    FOR select WHERE true
    FOR create, 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)
delay: Optional minimum delay in milliseconds before the job is eligible to run (default 0). While delayed the job stays pending and can still be killed.
timeout: Optional per-job timeout override in seconds (only used if backend has timeoutOverridable: true)
errors: Array of error objects from failed execution attempts
assignee: System-managed. The owning SSP node id, set automatically in cluster mode so a job is recovered if its SSP goes down. You never set this.

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)
    delay?: number                 // Stay pending at least N milliseconds before running
  }
)

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.
- delay: Minimum milliseconds the job stays pending before it is eligible to run (default 0).

Warning

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

Delaying Jobs

Set delay (milliseconds) to keep a job pending for at least that long before it runs. Use it for reminders, debounced follow-ups, or spacing out work:

TypeScript

// Run no sooner than 5 minutes from now
await db.run('api', '/send-reminder',
  { id: reminder.id },
  { delay: 5 * 60 * 1000 }  // 5 minutes, in milliseconds
);

// Delay composes with the other options
await db.run('api', '/generate-digest', data, {
  delay: 30_000,            // wait 30s before the first attempt
  max_retries: 3,
  retry_strategy: 'exponential'
});

The delay is a minimum, not an exact schedule: the job becomes eligible once the delay elapses and then runs as soon as the runner picks it up. delay requires the delay field on your job table (see the schema above).

A delayed job is just a pending job, so it stays fully visible and cancellable during its wait. Killing it before the delay elapses marks it failed and the backend is never called:

TypeScript

// Enqueue a delayed job...
await db.run('api', '/send-reminder', { id: reminder.id }, { delay: 60_000 });

// ...then cancel it before the delay elapses (CLI, or fn::job::kill).
// The job is marked "failed" and never calls the backend.
// spky jobs kill job:abc123

Note

In cluster mode, delayed jobs survive an SSP restart: the scheduler re-dispatches a delayed job (using the assignee field) if its owning SSP goes down before the delay elapses.

Example: Undo Send

A delay window is the classic “undo send”. Queue the email to leave in 30 seconds, linked to the email record so you can find the job again:

TypeScript

import { db } from './db';

// User clicked "Send". Queue the email to go out in 30 seconds, linked to the
// email record so we can find the job again during the undo window.
async function sendEmail(email) {
  await db.run('api', '/send-email',
    { to: email.to, subject: email.subject, body: email.body },
    { assignedTo: email.id, delay: 30_000 }  // 30s "undo send" window
  );
}

While the delay has not elapsed the job is still pending, so it can be killed. Killing it marks the job failed and the backend (the actual send) is never called:

TypeScript

// During the 30s window the job is still `pending`, so it can be killed. Find
// it via the email it is linked to (db.run returns void, so we query for it):
const pendingSend = db.query('job')
  .where({ assigned_to: 'email:abc123', path: '/send-email', status: 'pending' })
  .orderBy('created_at', 'desc')
  .limit(1)
  .one()
  .build();

const job = useQuery(db, () => pendingSend);
const jobId = () => job()?.id;  // e.g. "job:xyz789"

// Retract it — a killed pending job is marked `failed` and the email is never
// sent. Kill is an operator action, so run it via the CLI:
//   spky jobs kill job:xyz789

Warning

fn::job::kill / spky jobs kill are operator actions (system access) — record-access end users can’t call them, so they can’t stop each other’s jobs. For a user-facing “Undo” button, either route the kill through a trusted backend/admin action, or use the soft-cancel pattern below.

For a purely client-side undo, gate the send on a flag the user can flip during the window — no operator access required:

TypeScript

// In-app "Undo" without operator access: let the user flag the email retracted
// during the window (they own the record, so a normal update works), and have
// the /send-email route skip sending when the job finally runs.

// Client — flip the flag while the job is still delayed:
await db.update('email', 'email:abc123', { retracted: true });

// Backend /send-email handler (pseudocode) — bail out if retracted:
if (email.retracted) return { skipped: true };
await mailer.send(email);

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

Inspecting Jobs from the CLI

The spky jobs command reads the same outbox job tables straight from SurrealDB — the tables declared in sp00ky.yml (any backend app with an outbox method), falling back to job. It works against your local dev database by default, or your deployed production database with --cloud, so you can watch and manage the queue from a terminal without building any UI.

Run it without a subcommand to open a live, interactive dashboard (status counts, recent jobs, filter cycling):

Bash

# Live dashboard against your local dev database
spky jobs

# ...against the deployed (cloud) database
spky jobs --cloud

Listing Jobs

spky jobs list (alias ls) prints a static, scriptable table — handy in CI or piped through jq with --json:

Bash

# List recent jobs as a static, scriptable table (alias: ls)
spky jobs list

# Filter by status and/or a single job table
spky jobs list --status failed
spky jobs list --status pending --table job

# Raise the per-table row cap (default 200) and emit JSON for scripts
spky jobs list --limit 1000 --json

Inspecting, Killing, Retrying & Clearing

Bash

# Full payload + error history for one job
spky jobs get job:abc123

# Stop a job: cancel it if in-flight, or drop it if still queued
spky jobs kill job:abc123

# Re-run a terminal (failed or success) job
spky jobs retry job:abc123

# Delete every terminal job (status success or failed) from all job tables
spky jobs clear

Note

kill and retry mutate jobs through the fn::job::kill / fn::job::retry SurrealQL functions rather than a plain UPDATE (job pickup is gated inside the SSP). Those functions are installed by the schema deploy, so kill/retry require a deployed backend.

spky jobs clear deletes every terminal job (status success or failed) from all job tables — handy for trimming finished history. Terminal jobs aren’t in-flight, so this is a plain DELETE (no SSP coordination); pending/processing jobs are left untouched.

Dev vs. Production

By default every spky jobs command targets the local dev database from the spky dev stack (http://localhost:8000, namespace/database main). Pass --cloud to target the project’s deployed database instead: it resolves the endpoint and root credentials from your spky login session and the slug: in sp00ky.yml (the same lookup as spky project credentials), so you don’t pass --url/--password. You can also point at any database explicitly with --url/--namespace/--database/--username/--password or the matching SURREAL_URL/SURREAL_NS/SURREAL_DB/SURREAL_USER/SURREAL_PASS environment variables.

Bash

# Default: the local dev database (the "spky dev" stack at
# http://localhost:8000, namespace/database "main"). Override explicitly
# with flags or env vars:
spky jobs list --url http://localhost:8000 --namespace main --database main
SURREAL_URL=https://db.example.com SURREAL_NS=main spky jobs list

# --cloud: your deployed production database, resolved from "spky login"
# plus the slug in sp00ky.yml (same lookup as "spky project credentials").
spky jobs list --cloud
spky jobs --cloud            # dashboard against production

Note

--cloud is the same flag the other database-connecting commands accept (spky flag, spky migrate, spky query) — run them locally during development and add --cloud to point at production.

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:

Picks up job records with status = 'pending'
Honors the delay field, keeping a job pending until its delay has elapsed
Updates status to 'processing'
Makes the HTTP request to your backend
Updates status to 'success' or 'failed'
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.

Jobs & Usage

Job Table Schema

Key Fields

Calling Backend Functions

Method Signature

Parameters

Delaying Jobs

Example: Undo Send

Querying Job Status

Complete Example

Inspecting Jobs from the CLI

Listing Jobs

Inspecting, Killing, Retrying & Clearing

Dev vs. Production

Job Runner

Best Practices

Use assignedTo for Related Jobs

Handle Failed Jobs in Your UI

Set Appropriate Retry and Timeout Policies

Use Permissions to Secure Jobs