AI SQL functions

AOCore exposes six AI functions as Trino SQL UDFs. Every call routes through AOCore’s guardrails pipeline (PII / jailbreak / content-mod / secrets / regex), applies budget enforcement, resolves the model + prompt template from the catalog at call time, and emits provenance entries to the audit chain.

Requires: AOCore Trino plugin loaded in the Trino coordinator; model catalog seeded with referenced model assets.

Quick reference

All 6 functions are deterministic=false — Trino never caches identical calls. Per-item failures return SQL NULL (BigQuery / Snowflake pattern); the query continues with partial results. See Error Handling.

Architecture

Trino worker
  └─ @ScalarFunction
       └─ BatchQueue (64 rows default, 100ms timeout flush)
            └─ AOCoreGatewayClient (HttpClient 5 pool, 200 conns)
                 └─ POST /v1/batch/udf (mTLS + X-Trino-User)
                      └─ ProxyService.Execute / ExecuteEmbedding / ExecuteRerank
                           ├─ Guardrails pipeline (PII / jailbreak / content-mod / secrets / regex)
                           ├─ Budget + rate-limit enforcement
                           ├─ EvalGate check (emit CM-3 evidence, or fallback, or block)
                           ├─ Upstream provider call
                           └─ Audit chain emit

For SEMANTIC_SEARCH, the query text is embedded through the same guardrails pipeline, searched against the vector index, and recorded in the provenance chain as a single transaction.

The batch architecture prevents per-row serialization overhead. For workloads processing more than 10,000 rows, see the materialized-table pattern.

Function reference

Each function section follows the same layout: SQL signature, semantics, example, error handling, audit kinds emitted, and performance notes.

LLM_GENERATE

Signature:

LLM_GENERATE(model VARCHAR, prompt VARCHAR) → VARCHAR
LLM_GENERATE(model VARCHAR, prompt VARCHAR, params MAP<VARCHAR,VARCHAR>) → VARCHAR

Generate text via the AOCore gateway. The model argument is a catalog asset name (resolved at call time by CatalogResolver). The full guardrails pipeline applies to the prompt; spend is tracked per call. The optional params map passes temperature, max_tokens, etc. to the upstream provider (e.g. MAP(ARRAY['temperature','max_tokens'], ARRAY['0.3','500'])).

Output shape (gateway internal): {"text": "..."} → extracted as VARCHAR.

Example:

SELECT id, LLM_GENERATE('claude-sonnet-4-7', 'Summarize in one sentence: ' || content) AS summary
FROM articles
WHERE published_at > now() - INTERVAL '7' DAY;

Error handling: Per-item failure (budget exhausted, rate limited, guardrail block, eval gate block) → SQL NULL + WARNING in Trino worker log. Filter with WHERE LLM_GENERATE(...) IS NOT NULL. Materialize into a CTE first to avoid calling the function twice.

Audit kinds:

• proxy.chat — operational record (ProxyService)
• udf.eval_gate.pass — CM-3 evidence if the model has an eval gate configured

Performance: Gateway overhead ≤ 50ms p99 (steady-state warm cache); cold-start ~65ms first call per Trino worker (one-time catalog cache miss). Provider call dominates: 200ms–2s typical for chat-completion models.

See full example.

EMBED

Signature:

EMBED(model VARCHAR, text VARCHAR) → ARRAY<DOUBLE>

Embed text into a vector via ProxyService.ExecuteEmbedding. The full guardrails pipeline applies. The returned ARRAY<DOUBLE> has as many dimensions as the model produces (e.g. 1536 for text-embedding-3-small).

Output shape (gateway internal): {"vector": [1.2, 3.4, ...]} → extracted as ARRAY<DOUBLE>.

Example:

SELECT id, EMBED('text-embedding-3-small', title || ' ' || content) AS embedding
FROM documents
WHERE indexed_at IS NULL;

Error handling: Per-item failure → SQL NULL (no vector). Filter with WHERE EMBED(...) IS NOT NULL.

Audit kinds:

• proxy.embedding — operational record (ProxyService)
• udf.eval_gate.pass — CM-3 evidence if the model has an eval gate configured

Performance: Gateway overhead ≤ 50ms p99; provider call 50–200ms typical for embedding models. Batch threshold 64 rows (default); batching is especially effective for EMBED since embedding calls are low-latency and high-throughput.

See full example.

LLM_CLASSIFY

Signature:

LLM_CLASSIFY(model VARCHAR, text VARCHAR, labels ARRAY<VARCHAR>) → VARCHAR

Classify text into one of labels. The Go dispatch resolves a prompt_template catalog asset associated with model, substitutes {{labels}} with the serialized label list, and uses the result as a system message. The model must be configured with a prompt template that contains the {{labels}} placeholder.

Output shape (gateway internal): {"label": "positive"} → extracted as VARCHAR. The returned label is always one of the input labels (the model is instructed to return exactly one).

Example:

SELECT id, content,
       LLM_CLASSIFY('claude-haiku-classify', content, ARRAY['positive', 'negative', 'neutral']) AS sentiment
FROM reviews
WHERE reviewed_at > now() - INTERVAL '24' HOUR;

Error handling: Per-item failure (model not found, no prompt template, budget, guardrail) → SQL NULL. To filter and avoid calling the function twice, use a CTE:

WITH classified AS (
    SELECT id, content,
           LLM_CLASSIFY('claude-haiku-classify', content, ARRAY['urgent', 'non-urgent']) AS priority
    FROM support_tickets
    WHERE created_at > now() - INTERVAL '1' DAY
)
SELECT id, content FROM classified WHERE priority = 'urgent';

Audit kinds:

• proxy.chat — operational record (ProxyService)
• udf.eval_gate.pass — CM-3 evidence if the model has an eval gate configured

Performance: Gateway overhead ≤ 50ms p99; provider call 200ms–2s typical. Prompt template resolution adds one catalog cache lookup (60s TTL, singleflight dedup on cache miss).

See full example.

LLM_EXTRACT

Signature:

LLM_EXTRACT(model VARCHAR, text VARCHAR, schema VARCHAR) → VARCHAR

Extract structured JSON from text against a JSON schema. The schema argument is a JSON document (e.g. a JSON Schema object). The Go dispatch injects the schema into a resolved prompt template and requests JSON-mode output from the provider. The model must support JSON-mode output (check the model asset’s model_card.supports_json_mode field in the catalog).

Output shape: The extracted JSON object, serialized back to a VARCHAR. Parse with Trino’s json_extract_scalar(...) or CAST(... AS ...).

Example:

SELECT id,
       LLM_EXTRACT(
         'claude-sonnet-extract',
         description,
         '{"type":"object","properties":{"name":{"type":"string"},"age":{"type":"integer"},"email":{"type":"string"}}}'
       ) AS extracted_json
FROM contact_form_submissions
WHERE submitted_at > now() - INTERVAL '1' HOUR;

Error handling: Malformed schema (not valid JSON) → SQL NULL immediately (pre-dispatch). Other per-item failures → SQL NULL.

Audit kinds:

• proxy.chat — operational record (ProxyService)
• udf.eval_gate.pass — CM-3 evidence if the model has an eval gate configured

Performance: Gateway overhead ≤ 50ms p99; provider call 200ms–2s typical. The schema argument is bundled into the prompt; larger schemas increase token count and provider latency.

See full example.

RERANK

Signature:

RERANK(model VARCHAR, query VARCHAR, candidates ARRAY<VARCHAR>) → ARRAY<ROW(doc_id VARCHAR, score DOUBLE)>

Re-rank candidates (text strings) against query via ProxyService.ExecuteRerank (full guardrails + budget + audit). Returns an ARRAY<ROW(doc_id VARCHAR, score DOUBLE)> where doc_id is the candidate text and score is the relevance score. Expand to rows with CROSS JOIN UNNEST or UNNEST(...).

No AIBOM chain — RERANK is a single-step operation with no cross-asset orchestration.

Output shape (gateway internal): {"results":[{"doc_id":"x","score":0.9},...]} → expanded to ARRAY<ROW>.

Example:

SELECT r.doc_id, r.score
FROM UNNEST(
    RERANK('cohere-rerank-3', 'machine learning tutorials',
           ARRAY['ML 101 guide', 'Deep learning intro', 'Linear algebra basics'])
) AS r(doc_id, score)
ORDER BY r.score DESC;

Error handling: Per-item failure → SQL NULL (the entire array result is null for that row).

Audit kinds:

• proxy.rerank — operational record (ProxyService)
• udf.eval_gate.pass — CM-3 evidence if the model has an eval gate configured

Performance: Gateway overhead ≤ 50ms p99; provider call 50–200ms typical for rerank models. The candidate list is sent in a single batch item.

See full example.

SEMANTIC_SEARCH

Signature:

SEMANTIC_SEARCH(vector_table VARCHAR, query_text VARCHAR, top_k INTEGER) → ARRAY<ROW(doc_id VARCHAR, score DOUBLE)>

Vector (semantic) search against a catalog-registered Lance table. The model argument is omitted — the embedding model is resolved server-side from vector_index.default_embedding_model on the vector index asset (ensuring query and index vectors use the same model). The index must be built and index_ready = true (derived from the asset’s vector_column + dimensions fields being set).

The Go dispatch performs two steps:

1. ExecuteEmbedding — embed query_text with the catalog-pinned model (full guardrails pipeline).
2. lance.Client.SearchWithActor — ANN search in-process via the the platform Lance shim.

A 3-entry AIBOM provenance chain is emitted atomically for every call. See Provenance & Audit.

Output shape (gateway internal): {"results":[{"doc_id":"x","score":0.9},...]} → expanded to ARRAY<ROW>.

Example:

SELECT d.id, d.title, d.content, s.score
FROM documents d
CROSS JOIN UNNEST(SEMANTIC_SEARCH('kb_vectors', 'how does eval gate work?', 5)) AS s(doc_id, score)
WHERE d.id = s.doc_id
ORDER BY s.score DESC;

Error handling:

• vector_index_not_ready:<name> → SQL NULL (index not built yet; check asset readiness in catalog browser or via management API)
• Other per-item failures → SQL NULL

Audit kinds:

• proxy.embedding — operational record (ProxyService, embed step)
• udf.embed.query — AIBOM provenance entry 1 (embed step metadata)
• udf.lance.ann_search — AIBOM provenance entry 2 (ANN search metadata)
• udf.semantic_search.aibom — AIBOM tie entry (the platform join key); contains trino_query_id for query attribution
• udf.eval_gate.pass — CM-3 evidence if the embedding model has an eval gate configured

Performance: Gateway overhead ≤ 50ms p99; embed provider call 50–200ms typical; Lance ANN search in-process (<10ms for warm indexes). Total expected latency per call: 60–250ms steady-state (warm cache, warm index). AOCORE_LANCE_RETRIEVAL_ENABLED must be true; when off, returns per-item error not_implemented_yet:semantic_search.

See full example.

Performance Characteristics

Provider call latency is dominant. For large-scale workloads (> 10,000 rows), the materialized-table pattern separates SCAN cost from AI inference cost and enables predictable batching.

Error Handling

Per-item failures map to SQL NULL with a WARNING log on the Trino worker. The query completes with partial results rather than failing entirely (BigQuery / Snowflake pattern).

Common per-item error codes (logged in WARNING on the Trino worker):

Filter rows where the UDF returned NULL:

-- Materialize first to avoid calling the UDF twice:
WITH results AS (
    SELECT id, content,
           LLM_CLASSIFY('classify-model', content, ARRAY['a', 'b', 'c']) AS classification
    FROM data
    WHERE created_at > now() - INTERVAL '1' HOUR
)
SELECT id, content, classification
FROM results
WHERE classification IS NOT NULL;

Provenance & Audit

Every UDF call emits audit chain entries to audit_logs. The full taxonomy:

The udf.semantic_search.aibom entry is LOCKED (schema_version=1) for the platform provenance graph consumption. The entry schema is version-locked for provenance-graph consumption.

Look up all audit rows for a specific Trino query (requires PostgreSQL access):

SELECT action, payload->>'trino_query_id' AS trino_query, updated_values
FROM audit_logs
WHERE payload->>'trino_query_id' = '<query_id>'
   OR updated_values->>'trino_query_id' = '<query_id>'
ORDER BY sequence_number ASC;

Or, using the the platform query-level summary row:

SELECT action, updated_values->'ai_udfs_invoked' AS udfs_invoked
FROM audit_logs
WHERE action LIKE 'trino.query.%'
  AND object_id LIKE 'trino-query/<query_id>/%';

Cross-tenant behavior

A Trino query authenticated as tenant-A:principal-1 cannot call UDFs against models owned by tenant-B. Cross-tenant requests are rejected at the AOCore gateway boundary (POST /v1/batch/udf); the per-item error tenant_mismatch surfaces as SQL NULL + WARNING log on the Trino worker. The wrong-tenant gate is enforced by the platform’s identity resolver, independent of the UDF function.

To avoid cross-tenant surprises: ensure the Trino principal’s tenant owns (or has explicit access to) the model assets and vector index assets it references. Check via the catalog browser (AOCore admin UI) or via management API asset queries.

Deferred features

• LLM_GENERATE_STREAM — streaming text generation is not available via Trino SQL UDFs. Use the streaming HTTP API instead — see the developer API reference.

Examples