SynapCores SQL Reference

SynapCores is an AI-native SQL database with first-class support for vector embeddings, AutoML, Cypher graph queries, and LLM functions. This page is the public reference for the SQL surface available in the shipped engine (v1.8.0-ce and forward). Use only features documented here — anything labelled "coming in v1.X" is not yet runnable.

If you want the engine's runtime-discoverable manual (the one MCP clients like Claude Code, Cursor, and OpenClaw query at runtime), it's exposed as the sql_manual tool over MCP and lives in AIDB_SQL_MANUAL.md in the engine repo. Anything on this page should match that doc.

Quick reference — SynapCores extensions at a glance

The features below distinguish SynapCores from generic SQL.

Data Types

Scalar

BOOLEAN, SMALLINT, INTEGER, BIGINT, REAL, DOUBLE, DECIMAL(p, s), TEXT, VARCHAR(n), CHAR(n), BYTEA, JSON, JSONB, UUID, TIMESTAMP, DATE, TIME.

AI-native

• VECTOR(N) — N is the embedding dimension. Must match the configured embedding model (default all-minilm:latest is 384).

Multimedia

• AUDIO(format) — MP3, WAV, FLAC, AAC, OGG
• VIDEO(format) — MP4, AVI, MKV, WEBM, MOV
• IMAGE(format) — JPEG, PNG, WEBP, GIF, BMP
• PDF

Generic IMAGE / AUDIO / VIDEO without a format are not supported — always specify, e.g. IMAGE(JPEG).

Column constraints

PRIMARY KEY, UNIQUE, NOT NULL, CHECK (expr), DEFAULT expr, REFERENCES other_table(other_col).

Data Definition Language (DDL)

Databases

CREATE DATABASE [IF NOT EXISTS] db_name;
DROP DATABASE [IF EXISTS] db_name [CASCADE];
USE db_name;
SHOW DATABASES [LIKE 'pattern'];

Tables

CREATE TABLE [IF NOT EXISTS] table_name (
    column_name data_type [column_constraint],
    ...
    [table_constraint]
);

Worked example — products table with a vector column for semantic search:

CREATE TABLE products (
    id              BIGINT PRIMARY KEY,
    name            TEXT NOT NULL,
    category        TEXT,
    price           DECIMAL(10, 2),
    description     TEXT,
    description_vec VECTOR(384)
);

Append-only tables

CREATE IMMUTABLE TABLE audit_log (
    id          BIGINT PRIMARY KEY,
    actor       TEXT NOT NULL,
    action      TEXT NOT NULL,
    payload     JSON,
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE IMMUTABLE TABLE produces a hash-chained, append-only ledger. INSERT works as normal; UPDATE and DELETE are refused. Useful for compliance audit trails.

Alter, drop, index

ALTER TABLE t ADD COLUMN c data_type [constraint];
ALTER TABLE t DROP COLUMN c;
ALTER TABLE t RENAME COLUMN old TO new;
ALTER TABLE t ALTER COLUMN c TYPE new_type;

DROP TABLE [IF EXISTS] t [CASCADE];

CREATE [UNIQUE] INDEX [IF NOT EXISTS] idx_name ON t (col [ASC|DESC], ...);
DROP   INDEX [IF EXISTS] idx_name;

Partitioning

-- Range
CREATE TABLE sales (...) PARTITION BY RANGE (date);
CREATE TABLE sales_q1 PARTITION OF sales FOR VALUES FROM ('2026-01-01') TO ('2026-04-01');

-- List
CREATE TABLE customers (...) PARTITION BY LIST (country);
CREATE TABLE customers_usa PARTITION OF customers FOR VALUES IN ('USA', 'US');

-- Hash
CREATE TABLE activity (...) PARTITION BY HASH (user_id);
CREATE TABLE activity_0 PARTITION OF activity FOR VALUES WITH (MODULUS 4, REMAINDER 0);

Partition pruning is automatic when WHERE clauses constrain the partition key.

Views

CREATE [OR REPLACE] VIEW v AS SELECT ...;
CREATE MATERIALIZED VIEW mv AS SELECT ...;
REFRESH MATERIALIZED VIEW mv;
DROP VIEW v;

Introspection — INFORMATION_SCHEMA

Standard SQL system tables for catalog inspection:

SELECT * FROM INFORMATION_SCHEMA.TABLES;
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'products';
SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE;

Data Manipulation Language (DML)

INSERT INTO t [(c1, c2, ...)] VALUES (v1, v2, ...), ...;
INSERT OR REPLACE INTO t (...) VALUES (...);
INSERT INTO t (...) VALUES (...) ON CONFLICT IGNORE;

UPDATE t SET c1 = v1, c2 = v2 [WHERE condition];

DELETE FROM t [WHERE condition];

Worked example — populate a vector column from text using EMBED:

UPDATE products
   SET description_vec = EMBED(description)
 WHERE description_vec IS NULL;

Query Language

SELECT

SELECT [ALL | DISTINCT] expr [AS alias], ...
  FROM table_name
 [WHERE condition]
 [GROUP BY expr, ...]
 [HAVING condition]
 [ORDER BY expr [ASC | DESC], ...]
 [LIMIT n] [OFFSET k];

ORDER BY can reference projection aliases directly:

SELECT id,
       COSINE_SIMILARITY(description_vec, EMBED('wireless headphones')) AS similarity
  FROM products
 ORDER BY similarity DESC
 LIMIT 10;

Joins

SELECT o.id, o.total, c.name
  FROM orders o
  JOIN customers c ON o.customer_id = c.id
 WHERE o.created_at >= NOW() - INTERVAL '30 days';

INNER (default), LEFT, RIGHT, and FULL joins are supported. Use explicit JOIN…ON, not comma-separated FROM clauses.

CTEs and recursive queries

WITH recent_orders AS (
    SELECT * FROM orders WHERE created_at >= NOW() - INTERVAL '30 days'
)
SELECT customer_id, COUNT(*) AS n_orders
  FROM recent_orders
 GROUP BY customer_id;

-- Recursive CTE — walk an org hierarchy
WITH RECURSIVE managers AS (
    SELECT id, manager_id, name, 0 AS level FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.manager_id, e.name, m.level + 1
      FROM employees e
      JOIN managers m ON e.manager_id = m.id
)
SELECT * FROM managers ORDER BY level, name;

Window functions

SELECT id, category, price,
       SUM(price) OVER (PARTITION BY category ORDER BY id) AS running_total,
       AVG(price) OVER (PARTITION BY category)            AS category_avg
  FROM products;

Transactions

BEGIN [TRANSACTION];
-- statements
COMMIT;        -- or ROLLBACK;

Built-in Functions

Aggregate

COUNT, SUM, AVG, MIN, MAX, STDDEV, VARIANCE, PERCENTILE_CONT(p) WITHIN GROUP (ORDER BY col).

Math

ABS, CEIL/CEILING, FLOOR, ROUND, MOD, POWER/POW, SQRT, EXP, LOG/LN, LOG10, SIGN, TRUNCATE/TRUNC, PI, RAND/RANDOM, SIN, COS, TAN, ASIN, ACOS, ATAN, DEGREES, RADIANS.

String

UPPER, LOWER, LENGTH, SUBSTRING, CONCAT, TRIM, LTRIM, RTRIM, REPLACE, LEFT, RIGHT, LPAD, RPAD, REPEAT, REVERSE, INSTR/POSITION, ASCII, CHAR/CHR, INITCAP, MD5, SHA1, SHA256.

Date / time

NOW/CURRENT_TIMESTAMP, CURRENT_DATE, CURRENT_TIME, YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, UNIX_TIMESTAMP, DATE_FORMAT(date, fmt), STR_TO_DATE(s, fmt), DATE_ADD(date, n, unit), DATE_SUB(date, n, unit), DATEDIFF(d1, d2), LAST_DAY, DAYNAME, MONTHNAME, QUARTER, WEEK/WEEKOFYEAR, DAYOFWEEK, DAYOFYEAR.

Format examples:

DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s')   -- '2026-06-09 14:30:45'
DATE_ADD('2026-01-15', 30, 'DAY')          -- 2026-02-14
DATEDIFF('2026-12-31', NOW())              -- days until year end

Conditional / null

GREATEST(a, b, ...), LEAST(a, b, ...), IF(cond, then, else)/IIF, IFNULL(expr, alt)/ISNULL, COALESCE(...), NULLIF(a, b), CASE WHEN ... THEN ... ELSE ... END.

Vector Operations

Distance operators

embedding <=> query_vector  -- Cosine distance (1 - cosine similarity)
embedding <-> query_vector  -- Euclidean distance
embedding <#> query_vector  -- Inner product

Vector helper functions

VECTOR_ADD(vec1, vec2)         -- elementwise add
VECTOR_SUBTRACT(vec1, vec2)
VECTOR_MULTIPLY(vec, scalar)   -- scalar multiply
VECTOR_NORMALIZE(vec)          -- unit-length normalize
VECTOR_MAGNITUDE(vec)          -- L2 norm
VECTOR_DOT(vec1, vec2)

Top-K semantic search

SELECT id, name,
       COSINE_SIMILARITY(description_vec, EMBED('running shoes')) AS similarity
  FROM products
 ORDER BY similarity DESC
 LIMIT 10;

AI Functions

EMBED(text)

Computes an embedding for the given text using the configured embedding model.

• Argument: any TEXT expression.
• Returns: VECTOR(N) matching the configured model dimension.
• The column you store the result in must use the matching dimension.

SELECT EMBED('wireless noise cancelling headphones');

UPDATE products SET description_vec = EMBED(description);

COSINE_SIMILARITY(vec_a, vec_b) / EUCLIDEAN_DISTANCE(vec_a, vec_b)

SELECT id, name,
       COSINE_SIMILARITY(description_vec, EMBED('running shoes')) AS similarity,
       EUCLIDEAN_DISTANCE(description_vec, EMBED('running shoes')) AS dist
  FROM products
 ORDER BY similarity DESC
 LIMIT 10;

GENERATE(prompt [, options])

Calls the configured completion model and returns the generated text.

• prompt (TEXT, required).
• options (JSON, optional, v1.8.7+) — sampling + output-shape knobs. Build with json_object(). Recognized keys: max_tokens (default 4096 in v1.8.7+, was 200 prior), temperature, top_p, top_k, repeat_penalty, seed (reproducible sampling), system (system prompt override), grammar (GBNF), grammar_triggers (lazy-activation array), response_format: "json" (engine applies a built-in JSON grammar).
• Returns: TEXT. Cached on identical (prompt, options) tuple within a session.
• Local LLMs are slow per-row — use GENERATE for small result sets, not full-table scans.

-- Pre-v1.8.7 form — still works.
SELECT id,
       GENERATE('Summarize this review in one sentence: ' || review_text) AS summary
  FROM reviews
 WHERE rating <= 2
 LIMIT 50;

-- v1.8.7+ — deterministic JSON output with full sampling control.
SELECT GENERATE(
  'Extract product, sentiment, reason as JSON: ' || review_text,
  json_object(
    'max_tokens', 1024,
    'temperature', 0.2,
    'seed', 42,
    'response_format', 'json'
  )
) AS analysis
  FROM reviews LIMIT 10;

json_object(key1, value1, key2, value2, ...) (v1.8.7+)

Builds a JSON object literal from an even-length alternating list of (key, value) arguments. Designed for the options bag passed to GENERATE and other AI functions, but usable anywhere a JSON value is accepted. Keys must be TEXT; values can be any scalar SQL type. Returns JSON. Errors on odd-length argument lists or non-text keys.

SELECT GENERATE('Answer in one word: capital of France?',
                json_object('max_tokens', 8, 'temperature', 0.0)) AS answer;

NLP helpers

• SUMMARIZE(text, max_length) — abstractive summary
• SENTIMENT_ANALYSIS(text) — sentiment label/score
• EXTRACT_ENTITIES(text) — named entities
• EXTRACT_KEYWORDS(text, count) — keyword extraction
• CLASSIFY(text, categories) — zero-shot classification
• TRANSLATE(text, target_lang) — translation

SEMANTIC_MATCH, MULTI_MODAL_SIMILARITY, CROSS_MODAL_SEARCH

Higher-level helpers used inside SEMANTIC JOIN and multi-modal queries — see the multi-modal section below.

Agentic SQL — AGENT_RUN (v1.6.6.9+)

AGENT_RUN(persona, task) runs a complete in-database AI agent loop (ReAct: reason → call tool → observe → repeat) and returns the agent's final answer as TEXT. The agent has access to the same per-tenant database as the calling session — it can run SQL (execute_query), list/describe tables (list_tables, describe_table), and do semantic search (rag_search) inside one transaction.

-- Aggregation + reasoning over real rows
SELECT AGENT_RUN(
  'aidb-assistant',
  'Execute SELECT category, SUM(price*stock) AS v FROM products GROUP BY category.
   Tell me which category has the highest v based on the actual returned data.'
) AS reply;

-- Document Q&A via semantic search
SELECT AGENT_RUN(
  'aidb-assistant',
  'Use rag_search on knowledge_docs to answer: what is the return policy?
   Quote the most relevant passage verbatim.'
) AS reply;

-- Composed in a CTE — one agent call per row
WITH triaged AS (
  SELECT order_id,
         AGENT_RUN('returns-triage',
                   'Process return for order_id ' || CAST(order_id AS TEXT)) AS recommendation
  FROM pending_returns
)
SELECT * FROM triaged;

Configuration. AGENT_RUN requires a tool-capable LLM in [query.ai_service]. Recommended in v1.8+: qwen2.5-coder:7b via the embedded local provider (provider = "local" — no external daemon; the gateway pulls the model from registry.ollama.ai on first run). The same model also works via provider = "ollama", or you can point at any OpenAI / Anthropic / Gemini endpoint. The agent's tool surface is limited to the AIDB tools above — generic file/shell/http tools are not exposed for security reasons (v1.6.6.9 hardening).

Returns NULL only when no AI service is wired. Otherwise returns the agent's final TEXT response. See the recipes under Agentic AI Recipes for full working examples.

Native-inference model lifecycle (v1.8.0+)

v1.8.0-ce ships an in-process OCI v2 model registry: the gateway can pull GGUF models from registry.ollama.ai (or any Docker Distribution v2 registry) and serve them via the embedded local provider — no external Ollama daemon. The three functions below expose that registry as SQL, alongside the equivalent synapcores pull / synapcores models list CLI commands.

The functions are active when the gateway is running with [query.ai_service].provider = "local" (the v1.8 default — set automatically when [query.ai_service] is omitted from gateway.toml). The model store lives under data_dir/models/ with sha256-addressed blobs and JSON manifest sidecars.

PULL_MODEL(name)

Fetches a model into the local store.

• Argument: TEXT — model reference. Accepts name, name:tag, namespace/name[:tag], or registry/namespace/name[:tag]. Defaults: registry=registry.ollama.ai, namespace=library, tag=latest.
• Returns: TEXT — the resolved manifest digest.
• Idempotent: a second pull short-circuits when the local manifest digest matches the registry's current digest.
• Resume: interrupted pulls leave a .partial file; re-running PULL_MODEL resumes from the byte offset on disk.

SELECT PULL_MODEL('qwen2.5-coder:7b');
SELECT PULL_MODEL('library/all-minilm:latest');
SELECT PULL_MODEL('bartowski/Llama-3.2-3B-Instruct-GGUF:Q4_K_M');

Name-form table:

LIST_MODELS()

Inventories the local model store.

• Arguments: none.
• Returns table: (name TEXT, architecture TEXT, size_bytes BIGINT, digest TEXT, pulled_at TIMESTAMP, last_used_at TIMESTAMP).
• Reads on-disk manifest sidecars — never touches the network.

SELECT * FROM LIST_MODELS();
SELECT name, architecture, size_bytes
  FROM LIST_MODELS()
 WHERE architecture IN ('qwen2','llama','mistral','gemma2');
SELECT SUM(size_bytes) AS total_bytes FROM LIST_MODELS();

DELETE_MODEL(name)

Removes a model from the local store.

• Argument: TEXT — model reference (same name forms as PULL_MODEL).
• Returns: TEXT — the digest of the removed manifest.
• Reference-counts content-addressed blobs: a blob shared with another tag is kept on disk until the last reference is dropped.
• Errors if the model is currently loaded in the LRU; unload it first by pointing [query.ai_service].model elsewhere, or restart the gateway.

SELECT DELETE_MODEL('library/qwen2.5-coder:0.5b');

AutoML

AutoML trains real models from a SQL SELECT and exposes the trained model as an in-SQL function AUTOML.PREDICT. Training and prediction are first-class SQL — no separate Python pipeline required.

CREATE EXPERIMENT — train a model

CREATE EXPERIMENT model_name AS
  SELECT feature_1, feature_2, ..., label_column AS target
    FROM training_table
   [WHERE ...]
WITH (
    task_type        = 'binary_classification' | 'multi_classification' | 'regression'
                       | 'clustering' | 'time_series',
    target_column    = 'target',
    [optimization_metric = 'auc' | 'accuracy' | 'f1' | 'rmse' | 'mae' | ...,]
    [max_trials      = 50,]
    [algorithms      = ['logistic_regression', 'random_forest', 'gradient_boosting']]
);

• The target column from the SELECT becomes the label. By convention, for binary classification target = 1 is the positive class.
• Without algorithms, AutoML runs in Auto mode and explores a sensible default set.
• Validation predictions are calibrated with isotonic regression for binary tasks — AUTOML.PREDICT returns a well-calibrated P(class=1).
• CREATE EXPERIMENT ASYNC name AS ... schedules training in the background; poll with SHOW MODELS / DESCRIBE MODEL.

Algorithm options:

Worked example — train a churn model:

CREATE EXPERIMENT churn_model_v1 AS
  SELECT tenure_months,
         monthly_charges,
         total_charges,
         visits_30d,
         churned AS target
    FROM customers
WITH (
    task_type        = 'binary_classification',
    target_column    = 'target',
    optimization_metric = 'auc',
    max_trials       = 30,
    algorithms       = ['logistic_regression', 'random_forest', 'gradient_boosting']
);

AUTOML.PREDICT(...) — predict with a trained model

SELECT pass_through_col_1, ...,
       AUTOML.PREDICT('model_name', feature_1, feature_2, ...) [AS alias]
  FROM scoring_table
 [WHERE ...]
 [ORDER BY alias DESC|ASC]
 [LIMIT n];

• First argument is the model name as a quoted string.
• Remaining arguments are the feature columns, in any order — matched by name to the model's feature schema.
• Returns:
  • Binary classification: calibrated P(target = 1) as DOUBLE.
  • Multiclass: probability of the predicted top class.
  • Regression: the raw numeric prediction.
• Default alias is prediction if AS alias is omitted.
• You may sort or filter on the alias (ORDER BY alias DESC, WHERE alias > 0.8).

SELECT id, name, tier,
       AUTOML.PREDICT('churn_model_v1',
                      tenure_months, monthly_charges, total_charges, visits_30d) AS risk
  FROM customers
 WHERE tier = 'Gold'
 ORDER BY risk DESC
 LIMIT 50;

A feature column that also appears in the pass-through projection is dedupd automatically — don't list it twice.

Model lifecycle

SHOW MODELS;                       -- list all models in current tenant
DESCRIBE MODEL churn_model_v1;     -- schema, algorithm, metrics, training time
DROP MODEL churn_model_v1;         -- delete model artifacts

SHOW EXPERIMENTS;                  -- list (legacy) experiments
DESCRIBE EXPERIMENT name;

Limitations

• Model artifacts live under <data_dir>/models/ and are not portable across binaries.
• Models are tenant-prefixed; a tenant cannot use another tenant's models.
• AUTOML.PREDICT is supported in the SELECT projection. Wrapping it in a FROM (...) AS sub subquery works for ORDER BY alias / LIMIT but not for arbitrary outer projection rewrites.
• The anomaly_detection task_type and ANOMALY_SCORE() function are coming in v1.8.x — not yet runnable.

Cypher Graph Queries

SynapCores ships a per-tenant property graph engine with a Cypher subset. Cypher statements route automatically through /v1/query/execute — no separate endpoint required.

Read patterns

-- Find all nodes with a given label
MATCH (n:Person) RETURN n LIMIT 100;

-- Filter on properties
MATCH (n:Person) WHERE n.age >= 18 RETURN n.name, n.age;

-- Traverse a relationship
MATCH (a:Person {name: 'Alice'})-[:KNOWS]->(b:Person)
RETURN b.name;

-- Variable-length pattern + filter on the path
MATCH (a:Account)-[:TRANSFERRED*1..3]->(b:Account)
 WHERE a.owner = 'alice@example.com'
RETURN a.id, b.id;

Write patterns

-- Create a node
CREATE (p:Person {name: 'Bob', age: 30});

-- Create a relationship between two existing nodes
MATCH (a:Person {name: 'Alice'}), (b:Person {name: 'Bob'})
CREATE (a)-[:KNOWS {since: 2026}]->(b);

-- MERGE = match-or-create (good for ingest pipelines)
MERGE (p:Patient {mrn: 'MRN-101'})
MERGE (drug:Drug {name: 'Warfarin'})
MERGE (p)-[:PRESCRIBED]->(drug);

-- Delete a node and ALL its relationships
MATCH (n:Person {name: 'Charlie'}) DETACH DELETE n;

-- UNWIND a list to bulk-create
UNWIND [{name: 'Alice', age: 30}, {name: 'Bob', age: 25}] AS row
CREATE (:Person {name: row.name, age: row.age});

When to use Cypher vs SQL JOIN

• Use a SQL JOIN for tabular, fixed-depth relationships you already model in tables.
• Use Cypher when you need multi-hop traversals, variable-length paths, or to express "find everyone reachable from X via these edge types" succinctly. Cypher beats N-way SQL self-joins on graph-shaped data.

Discovery

SHOW PROPERTY GRAPHS;     -- list graphs in this tenant
CALL db.labels();         -- list all node labels in the active graph

Multi-modal SQL

For images, audio, video, and PDF stored in IMAGE/AUDIO/VIDEO/PDF columns:

-- Embed any modality and search across modalities
SELECT id,
       MULTI_MODAL_SIMILARITY(text  := description,
                              image := cover_image,
                              weights := '{"text":0.6,"image":0.4}') AS score
  FROM products
 ORDER BY score DESC LIMIT 10;

-- Semantic JOIN: match rows by semantic similarity instead of equality
SELECT a.id, b.id
  FROM articles a
SEMANTIC JOIN reference_docs b
    ON SEMANTIC_MATCH(a.body, b.text, threshold := 0.75);

MULTI_MODAL_SIMILARITY(...), CROSS_MODAL_SEARCH(...), and SEMANTIC_MATCH(...) accept named arguments using the name := value syntax.

Multimedia processing functions

TRANSCRIBE(audio, 'whisper-base')   -- audio → text transcript
EXTRACT_TEXT(image)                 -- OCR
EXTRACT_FRAMES(video, interval)     -- video → frame samples
EXTRACT_AUDIO(video)                -- video → audio track
EXTRACT_METADATA(content)           -- format metadata
DETECT_FORMAT(content)              -- format sniffing
RESIZE_IMAGE(image, width, height)
PROCESS_MULTIMEDIA(data)

Worked example — index PDFs + audio in one query:

CREATE TABLE media (
    id      BIGINT PRIMARY KEY,
    audio   AUDIO(MP3),
    video   VIDEO(MP4),
    image   IMAGE(JPEG),
    doc     PDF
);

SELECT id,
       TRANSCRIBE(audio, 'whisper-base') AS transcript,
       EXTRACT_TEXT(image)               AS ocr_text
  FROM media;

Triggers and Procedures (PL/pgSQL)

SynapCores supports PostgreSQL-style stored procedures and triggers.

Procedures

CREATE [OR REPLACE] PROCEDURE proc_name(args) AS $$
DECLARE
    var_name data_type [:= default];
BEGIN
    -- procedure body
    SET var_name = expr;
    IF condition THEN
        ...
    ELSIF condition THEN
        ...
    ELSE
        ...
    END IF;

    WHILE condition LOOP
        ...
    END LOOP;

    -- Bounded LOOP with explicit exit
    LOOP
        ...
        IF done THEN LEAVE; END IF;
    END LOOP;

    -- Raise an error
    RAISE EXCEPTION 'message %', value;

    -- Catch errors
    EXCEPTION WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
$$ LANGUAGE plpgsql;

DROP PROCEDURE [IF EXISTS] proc_name;
CALL proc_name(args);

SHOW PROCEDURES [LIKE 'pattern'];

OUT parameters return values to the caller; INOUT parameters can be read and written. RETURN exits the procedure early.

Triggers

CREATE [OR REPLACE] TRIGGER trg_name
  {BEFORE | AFTER} {INSERT | UPDATE | DELETE [OR INSERT OR UPDATE]} ON table_name
  [FOR EACH ROW]
  [WHEN (condition)]
  EXECUTE PROCEDURE proc_name(args);

DROP TRIGGER [IF EXISTS] trg_name ON table_name;
SHOW TRIGGERS [FROM table_name] [LIKE 'pattern'];

• BEFORE triggers can mutate NEW (the row being inserted/updated) — the mutated value is what gets stored.
• AFTER triggers fire post-write and are useful for cascading updates / audit log emission.
• WHEN (condition) skips the trigger body when the condition is false.
• The recursion cap prevents runaway trigger chains.

Worked example — audit-log trigger:

CREATE PROCEDURE log_change() AS $$
BEGIN
    INSERT INTO audit_log (table_name, op, payload, created_at)
    VALUES ('orders', 'UPDATE', row_to_json(NEW), CURRENT_TIMESTAMP);
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER orders_audit
  AFTER UPDATE ON orders
  FOR EACH ROW
  EXECUTE PROCEDURE log_change();

Natural Language

ASK '<natural language question>';            -- run a natural language query
EXPLAIN NATURAL '<natural language question>'; -- show the generated SQL plan
ASK 'sales by region' WITH CONTEXT (tables=['sales','regions']);

The engine resolves the NL question against the current tenant's schema and runs the generated SQL.

Backup, Restore, Clone

-- Backup the whole database (compressed, optionally encrypted)
BACKUP DATABASE TO 'path' WITH (
    TYPE        = 'FULL' | 'INCREMENTAL',
    COMPRESSION = 'ZSTD',
    ENCRYPTION  = TRUE
);

-- Backup specific tables only
BACKUP TABLES table_a, table_b TO 'path';

-- Restore
RESTORE DATABASE FROM 'path' WITH (OVERWRITE = TRUE, VERIFY_CHECKSUMS = TRUE);

-- Clone (copy structure + optionally filtered data)
CLONE DATABASE source TO target;
CLONE TABLE sales TO sales_backup WHERE date > '2026-01-01';

Backup encryption keys are tenant-specific.

Performance notes

• Vector operations scale to 10M+ vectors with HNSW indexing.
• Partition pruning kicks in automatically when WHERE clauses constrain the partition key.
• Natural language (ASK) uses schema context for best results — list the relevant tables explicitly when the auto-selected context misses.
• Multimedia columns stream large payloads.
• GENERATE and AGENT_RUN are LLM calls — assume per-call latency in the seconds, not milliseconds. Use them on small result sets or rely on session-level caching for repeated prompts.

Critical do's and don'ts

DO prefer the AI-native extension when the intent matches:

• Text/image similarity? → EMBED + COSINE_SIMILARITY (or MULTI_MODAL_SIMILARITY).
• Trained model? → CREATE EXPERIMENT … WITH (…) then AUTOML.PREDICT(…).
• Risk-ranked output? → ORDER BY <prediction_alias> DESC LIMIT N directly.
• Multi-hop relationships? → Cypher MATCH, not N-way self-joins.
• LLM-generated text per row? → GENERATE(prompt) in the SELECT.
• In-database agent loop? → AGENT_RUN(persona, task) — model-side reasoning with the AIDB tool surface.

DON'T:

• Don't invent functions or syntax not in this reference.
• Don't list a feature column twice when it's also in the pass-through projection — AUTOML.PREDICT dedupes.
• Don't use placeholder comments like -- your SQL here or [bracket placeholders] — every recipe must run.
• Don't store an embedding in a column whose declared dimension differs from the model's output dim — runtime error.
• Don't include DROP TABLE/DROP INDEX cleanup steps in shared recipes (they delete user data).

Document version: 2.0 (audited against engine v1.8.0-ce) Last updated: 2026-06-09 Engine reference: AIDB_SQL_MANUAL.md Website: https://synapcores.com