Typed operations, joins, search, and jobs

Loomup manifest version 1 can expose reviewed SQL as typed SDK methods. This is the escape hatch for joins, aggregates, transactional workflows, and other domain operations that do not fit single-resource CRUD. Clients never submit arbitrary SQL.

Declare the contract

version = 1
name = "analytics-core"

[databases.analytics]
path = "data/analytics.sqlite"
wal = true

[types.ReportInput.fields.workspace_id]
type = "uuid"
required = true

[types.ReportRow.fields.workspace_id]
type = "uuid"
required = true

[types.ReportRow.fields.event_count]
type = "integer"
required = true

[queries.workspace_report]
database = "analytics"
sql = "operations/workspace_report.sql"
access = "authenticated"
input = "ReportInput"
output = "ReportRow"
cardinality = "many"
max_rows = 1000
timeout_ms = 5000

operations/workspace_report.sql can contain ordinary joins and aggregates:

SELECT w.id AS workspace_id, COUNT(e.id) AS event_count
FROM workspaces AS w
LEFT JOIN events AS e ON e.workspace_id = w.id
WHERE w.id = :workspace_id
  AND w.owner_id = :auth_user_id
GROUP BY w.id

Only named parameters are accepted. Input fields bind as :field_name; trusted identity bindings are :auth_user_id, :auth_role, :auth_kind, and :service_key_id. Queries are read-only, bounded by max_rows and timeout_ms, and validated against their output contract.

Contract primitives are string, boolean, integer, number, decimal, uuid, datetime, date, bytes, and json. A field can also reference another declared type and can specify required, nullable, array, enum, length/numeric limits, or a regular-expression pattern.

Contract, operation, policy, job, and named-database identifiers are portable across all generated SDKs: they begin with an ASCII letter and contain only letters, digits, or _. Scalar queries select exactly one column and use an output contract containing exactly one non-array field; the generated return type is that field's type or null.

Commands and policies

[types.CreateEvent.fields.workspace_id]
type = "uuid"
required = true

[types.CreateEvent.fields.name]
type = "string"
required = true
min_length = 1
max_length = 120

[policies.workspace_member]
database = "analytics"
sql = "policies/workspace_member.sql"

[commands.create_event]
database = "analytics"
sql = "operations/create_event.sql"
access = "authenticated"
input = "CreateEvent"
output = "ReportRow"
policies = ["workspace_member"]
idempotency = "required"
batch = "atomic"
max_batch_items = 100
offline = true

A policy SQL file returns one boolean value. Commands execute in an immediate SQLite transaction. atomic batches commit every item or none; best_effort batches return an item result for each input. Offline commands must require idempotency. Reusing a key with identical input replays its stored result; changing the input returns a conflict.

Operation SQL cannot access Loomup-owned tables, load extensions, or run schema/attachment pragmas. Loomup-managed CDC and FTS triggers still run inside command transactions.

Full-text search

[searches.articles]
database = "default"
resource = "articles"
fields = ["title", "body"]
access = "public"
max_rows = 100
timeout_ms = 5000

Loomup creates and maintains the FTS5 index and applies the resource read rule to results. Search is called by name, so index details do not leak into application code.

Named-database resources support CRUD, durable history, searches, and typed operations. Realtime resource subscriptions and offline sync v1 use the default database's single ordered journal, so a resource with realtime = true must use database = "default", and sync v1 rejects named-database resources explicitly.

Durable jobs

Jobs support three v1 handlers:

external: a scoped service-key worker claims, heartbeats, completes, or fails leases.
command: Loomup invokes a named command in the background with job-id idempotency.
http: Loomup posts JSON to an HTTPS endpoint (plain HTTP is limited to loopback).

[jobs.rollup]
handler = "command"
target = "build_rollup"
schedule = "*/15 * * * *"
input = "ReportInput"
output = "ReportRow"
payload = { workspace_id = "6cb58ec0-0d78-4a7b-8275-caa7d8066d15" }
max_attempts = 8
timeout_ms = 10000

Schedules use five UTC cron fields with lists, ranges, and steps. A transactional minute ledger prevents duplicate scheduled occurrences. Failed jobs use bounded exponential retry and become dead after max_attempts; expired worker leases can be reclaimed and cannot be revived, completed, or failed by the stale worker.

A command job declares the same input and output contracts as its target command. The target command's timeout controls command execution; timeout_ms controls HTTP jobs. HTTP redirects are rejected and response bodies are limited to 1 MiB.

Create a least-privilege external-worker key locally:

loomup admin create-service-key \
  --name rollup-worker \
  --scopes jobs:rollup \
  --config loomup.toml

The secret is displayed once and only its SHA-256 digest is stored. Keys can be created, safely rotated, listed, and revoked from a managed project's Studio API Keys page, or managed locally with the corresponding admin commands. Studio and the CLI share the same portable key store. operations:* grants all named queries, commands, and searches but deliberately does not grant job-worker access; use jobs:<name>, jobs:*, or * explicitly. Resource access requires an explicit resource:<name>:read or resource:<name>:write scope for restricted integrations. project:backend is the durable project-wide credential: it includes schema deployment and current or future Resources, queries, commands, searches, and jobs. Legacy operation wildcards never expand into Resource access.

Generate and call the SDK

loomup gen sdk --language typescript --output src/loomup.operations.ts
loomup gen sdk --language swift --output Sources/App/LoomupOperations.swift
loomup gen sdk --language kotlin --output app/src/main/java/app/LoomupOperations.kt
loomup gen sdk --language dart --output lib/loomup_operations.dart

The generated project wrapper provides typed query, command, batch, search, and enqueue helpers. The core clients also expose dynamic calls:

const report = await client.query<ReportRow[], ReportInput>(
  "workspace_report",
  { workspace_id },
);

const created = await client.command<ReportRow, CreateEvent>(
  "create_event",
  { workspace_id, name: "opened" },
  { idempotencyKey: crypto.randomUUID() },
);

Use resource CRUD for ordinary records and named operations for joins or workflows. This keeps the database implementation replaceable while giving each SDK a stable domain-level API.

Resource querying and cursors

Resource lists support eq, ne, lt, lte, gt, gte, in, is_null, contains, and starts_with, field projection, and stable multi-column sorting. Responses can include a signed next_cursor; pass it alone to continue the same query without rebuilding filters. The opaque cursor also advances bounded row-policy scans, including across an empty authorized page. Malformed filter syntax, unknown operators, and ambiguous is_null values are rejected instead of being ignored.

#Typed operations, joins, search, and jobs

#Declare the contract

#Commands and policies

#Full-text search

#Durable jobs

#Generate and call the SDK