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.

Attest the chain from SQL (v1.12.0+):

VERIFY TABLE audit_log;          -- is_valid, message (records/blocks verified, or the first break)
VERIFY RECORD 1 IN audit_log;    -- record_id, is_valid, message

VERIFY TABLE walks every block, recomputes each record checksum, each sealed block's checksum and merkle root, and each block-to-block link, then cross-checks the chained record count against the rows actually stored. It errors — rather than passing — when the target is not an immutable table.

Immutable tables must be created empty with an explicit column list — CREATE IMMUTABLE TABLE ... AS SELECT is not supported; populate them with INSERT / INSERT ... SELECT.

Encryption at rest (v1.12.0+):

CREATE IMMUTABLE TABLE ssn_ledger (
    id       BIGINT PRIMARY KEY,
    subject  TEXT NOT NULL,
    secret   TEXT NOT NULL
) WITH (ENCRYPTION='AES256GCM');

WITH (ENCRYPTION='AES256GCM') encrypts both the hash-chain and the row-engine copy on disk.

• Requires the AIDB_IMMUTABLE_MASTER_KEY environment variable (a hex-encoded key). If it is missing or invalid, the CREATE is rejected — never silently downgraded to a plaintext table (fail-closed).
• AES256GCM is the only supported algorithm; an unsupported or missing value is rejected.
• ENCRYPTION_KEY='name' (a named-key registry) is not supported in this release — use ENCRYPTION='AES256GCM' alone (the table gets an auto-generated per-table key wrapped by the master key).
• Encrypted immutable tables are supported only in the default database.

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.

Bounding a run — AGENT_RUN(persona, task, options) (v1.8.9+)

An optional third argument, a JSON object built with json_object(...), bounds a single call.

SELECT AGENT_RUN('aidb-assistant', 'Investigate the slowest query',
                 json_object('max_iterations', 3, 'timeout_ms', 60000)) AS reply;

Out-of-range numbers are clamped, not rejected; unknown option keys are an error. Two keys are deliberately rejected: allow_writes (the agent's tools are read-only from SQL — a per-query flag would let any caller escalate a read-only agent; write capability is declared by an operator on a durable agent in v1.9), and model (the engine resolves the model persona-then-config, so a per-call override would be silently ignored — set it in [query.ai_service] or on the persona).

Agent memory (v1.8.5+)

Four SQL primitives give any agent durable, semantically-searchable memory without a second service. The first call to a namespace auto-creates its backing table _memory_<namespace> — (id, content, embedding VECTOR(384), metadata, created_at, accessed_at). Namespaces must match ^[A-Za-z_][A-Za-z0-9_]*$; everything is per-tenant scoped.

MEMORY_STORE(namespace, content [, metadata [, options]]) — v1.8.5+

Embeds content and inserts it; returns a sortable memory id (TEXT).

SELECT MEMORY_STORE('default', 'I prefer Python over Java') AS memory_id;

This is an unconditional insert — storing the same content twice makes two rows. Since v1.8.9, pass json_object('dedup', true) as a fourth argument to route through MEMORY_UPSERT's noop_if_equal policy, returning the existing row's id instead of duplicating.

MEMORY_RECALL(namespace, query [, top_k]) — v1.8.5+, table-valued

Semantic retrieval in a FROM clause. Returns (id, content, similarity, metadata, created_at), top top_k (default 10, cap 100) ordered by similarity DESC.

SELECT id, content, similarity FROM MEMORY_RECALL('default', 'what languages do I like', 5);

MEMORY_UPSERT(namespace, content [, options | natural_key]) — v1.8.9+

Idempotent, policy-driven write. Finds the row this content should supersede, applies a conflict-resolution policy, and returns the action taken — 'ADD' | 'UPDATE' | 'DELETE' | 'NOOP'. With no key, matching is by embedding similarity (semantic keying, default threshold 0.95).

-- Revise a belief, keeping the higher-confidence one
SELECT MEMORY_UPSERT('default', 'User is pescatarian',
                     json_object('key', 'dietary',
                                 'policy', 'replace_higher_confidence',
                                 'confidence', 0.95)) AS action;   -- 'UPDATE'

-- Retract a fact the agent no longer holds
SELECT MEMORY_UPSERT('default', 'User is vegetarian',
                     json_object('key', 'dietary', 'deleted', true)) AS action;  -- 'DELETE'

Retraction via deleted: true or confidence: 0; retracting a belief never held is a NOOP, not an error. Confidence/key/version live under reserved metadata keys, so v1.8.5 namespaces keep working with no migration. Every non-NOOP change is recorded in _system_agent_memory_audit.

MEMORY_FORGET(namespace, id) — v1.8.5+

Hard-deletes one memory by id; returns BOOLEAN.

SELECT MEMORY_FORGET('default', 'mem_abc123') AS deleted;

Personas — CREATE PERSONA (v1.14.2+)

A persona is the reasoning profile every agent binds to: a system prompt, an optional pinned model, and an output shape. AGENT_RUN(persona, task) takes one by name, and so does CREATE AGENT … PERSONA '<name>'. Since v1.14.2 a persona is a first-class database object with its own DDL rather than a config-file entry — create one at runtime and it is usable immediately and survives restart. Personas are install-wide, not tenant-scoped.

Seven personas ship built in and are seeded on first boot: default, sql_developer, data_scientist, rust_developer, devops_engineer, marketing_expert, ui_ux_designer. A built-in can be edited, but it stays built-in and cannot be dropped.

CREATE PERSONA

CREATE PERSONA retention_analyst WITH (
  system_prompt = 'You are a retention analyst. Be concise and cite numbers.'
);

CREATE PERSONA compliance_reviewer WITH (
  display_name  = 'Compliance Reviewer',
  description   = 'Reviews transactions against policy',
  system_prompt = 'You are a compliance reviewer. Quote the policy clause you rely on.',
  model         = 'qwen2.5-coder:7b',
  response_type = 'json',
  tool_enabled  = TRUE
);

An unknown key is rejected at parse time, and system_prompt is required. A duplicate name errors unless you write CREATE OR REPLACE PERSONA, which is a full rewrite — keys you omit go back to their defaults, while created_at and the built-in flag are preserved. Commas inside a quoted value are safe, and a literal single quote is escaped by doubling it ('Use the user''s schema.').

A pinned model must be installed (v1.14.2). model = 'typo-not-installed:7b' is refused by the statement itself, listing what is installed and pointing at PULL_MODEL(), instead of surfacing hours later as a mid-loop agent failure. The check is skipped only when no model registry can be resolved at all.

ALTER PERSONA, DROP PERSONA

ALTER PERSONA retention_analyst SET (system_prompt = 'Answer in at most three sentences.');
ALTER PERSONA retention_analyst SET (model = 'qwen2.5-coder:7b', response_type = 'json');
ALTER PERSONA retention_analyst SET (model = '');        -- clear the pin, inherit the default
DROP PERSONA IF EXISTS retention_analyst;

ALTER changes only the keys you name (same six keys, at least one required). DROP refuses a built-in, and IF EXISTS makes it idempotent. Dropping does not rewrite agents that reference the persona: the drop succeeds and the dependent agent keeps a dangling binding, which DESCRIBE AGENT reports as persona_resolved = false. A new CREATE AGENT naming an unknown persona is refused — so repoint or drop dependent agents first.

SHOW PERSONAS, DESCRIBE PERSONA

SHOW PERSONAS;                       -- persona_name, display_name, model, tool_enabled, is_builtin
SHOW PERSONAS LIKE 'retention%';
DESCRIBE PERSONA retention_analyst;  -- (property, value) rows, including the full system_prompt

model reads NULL in SHOW PERSONAS (and (config default) in DESCRIBE PERSONA) when nothing is pinned. The LIKE pattern uses % as its wildcard and is unanchored — LIKE 'gate' matches docgate_analyst just as '%gate%' would.

Durable Agents — CREATE AGENT (v1.9.0+)

AGENT_RUN is a transient single call. Durable agents are schema objects: a persona bound to a task, one or more activations (a schedule and/or DML events), a governance envelope, and persistent memory — all stored in the database. They survive restarts, fire asynchronously off the write path, and record every run in an auditable system table. There is no external cron box, worker fleet, or queue to operate.

CREATE AGENT

CREATE AGENT incident_triage
  PERSONA 'aidb-assistant'
  TASK 'Triage the incident in the activation row: classify it, name the likely
        attack pattern, and recommend an escalation tier.'
  ON INSERT INTO incidents WHERE severity = 'critical'
  WITH (
    max_iterations       = 3,        -- ReAct loop bound (1–10)
    allow_writes         = FALSE,    -- read-only unless explicitly opted in
    timeout_seconds      = 90,       -- wall-clock bound per run
    budget_tokens_per_day = 200000,  -- 0 = unmetered (CE default)
    on_budget_exhausted  = 'pause'   -- 'pause' | 'fail'
  );

• Persona — a registered chat persona (the reasoning profile).
• Task — the instruction template the agent runs on each activation. For event activations the matching row is appended as context.
• Activation — ON SCHEDULE '<cron>' (5- or 6-field cron) and/or ON <INSERT|UPDATE|DELETE> INTO <table> [WHERE <predicate>]. Event bindings are evaluated as internal after-triggers; a non-matching write pays negligible overhead and never runs inference on the write path.
• Governance — max_iterations, allow_writes, timeout_seconds, budget_tokens_per_day, on_budget_exhausted. Agent-initiated writes never re-trigger event bindings (cascade suppression).

ALTER AGENT, DROP AGENT

ALTER AGENT incident_triage DISABLE;   -- pause (definition retained)
ALTER AGENT incident_triage ENABLE;
DROP AGENT IF EXISTS incident_triage;  -- remove definition, schedule, bindings

SHOW AGENTS, DESCRIBE AGENT

SHOW AGENTS;                    -- name, persona, bindings, state, tokens today
DESCRIBE AGENT incident_triage; -- full definition + governance envelope

EXECUTE AGENT — run once, synchronously

EXECUTE AGENT incident_triage;                 -- run the stored task now
EXECUTE AGENT incident_triage WITH ('re-check incident 42');  -- task override

WAKE AGENT — fire now, asynchronously (v1.14.2+)

WAKE AGENT stage2_summarizer;   -- returns immediately: Agent 'stage2_summarizer' woken (queued)

WAKE AGENT enqueues an immediate run on the background dispatcher and returns at once — the ReAct loop never runs inside your request. It is the async counterpart to EXECUTE AGENT: use it when one pipeline stage should hand off to the next without waiting for a cron tick. The agent must exist and be enabled (waking a disabled agent errors and tells you to ALTER AGENT … ENABLE). The queued run lands in _system_agent_runs with activation = schedule 'manual-wake' once the dispatcher drains it, and it is enqueued at cascade hop 0 — a user-issued wake starts a fresh chain rather than continuing one.

Chaining agents — ALLOW AGENT ORIGIN (v1.14.2+)

By default a write made inside an agent's run never re-fires an event binding. That blanket suppression is what stops an allow_writes = TRUE agent from looping forever on its own INSERT. ALLOW AGENT ORIGIN is a suffix on one event binding that opts it in, so agents can be chained into a pipeline:

CREATE AGENT stage2_summarizer
  PERSONA 'retention_analyst'
  TASK 'Summarize the validated row.'
  ON UPDATE ON doc_queue WHERE stage = 'VALIDATED' ALLOW AGENT ORIGIN
  WITH (max_iterations = 2, timeout_seconds = 120);

Two guards stay in force whatever you declare: a binding never fires on its own agent's write (no self-loop), and a chain is capped at 5 hops, so a mis-declared A → B → A cycle halts instead of billing forever. Ordinary user writes fire the binding at hop 0 either way. The suffix goes after the optional WHERE, and the predicate is cut at the suffix — the binding above stores stage = 'VALIDATED'.

Run history — _system_agent_runs

Every autonomous run lands in the read-only _system_agent_runs table.

SELECT agent_name, activation, status, tokens_in, tokens_out, tokens_estimated
FROM _system_agent_runs
WHERE agent_name = 'incident_triage'
ORDER BY started_at DESC;

tokens_estimated is TRUE when the token counts are a heuristic (chars/4) rather than exact provider usage — budget accounting is never presented as exact when it isn't. Run history is retained for 30 days (purged by a background sweep).

Tamper-evident decision lineage (v1.9.1+)

Every run is hash-chained into a per-tenant ledger: prev_hash + entry_hash (entry_hash = sha256(prev_hash ‖ the run's fields)). Any edit to a stored run breaks its entry_hash and every downstream prev_hash link, so the audit is tamper-evident — and the check is a single query via the computed verified column:

-- Surface any run that was modified in storage
SELECT run_id, agent_name, started_at, verified
FROM _system_agent_runs
WHERE verified = false;

verified is TRUE/FALSE for chained runs and NULL for pre-v1.9.1 runs (which predate the ledger). The lineage is on by default, self-hosted, and never leaves your box.

CE limits. Community Edition caps active agents at 10 per instance; budget_tokens_per_day defaults to 0 (unmetered). See the Autonomous Incident Triage recipe for an end-to-end walkthrough, and AGENTIC_MODE.md for the full runtime model.

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.

MySQL compatibility (v1.10.0-ce)

v1.10.0-ce adds a MySQL-compatibility layer so real MySQL schemas and dumps load with far fewer rewrites. Every feature below is exercised by the mysql_parity.py conformance gate, which asserts the actual data state (not just HTTP 200). Where a feature is an MVP, that is called out — don't assume more than is documented.

Upsert — ON DUPLICATE KEY UPDATE / ON CONFLICT DO UPDATE

Both the MySQL and PostgreSQL upsert spellings are honored on the live write path (previously they parsed but were silently dropped). The right-hand side of each assignment is an arbitrary expression and may reference the proposed row: MySQL's VALUES(col) and Postgres's EXCLUDED.col both resolve to the value that would have been inserted.

CREATE TABLE hits (id INT PRIMARY KEY, n INT);
INSERT INTO hits (id, n) VALUES (1, 10);

-- MySQL form: arbitrary RHS expression
INSERT INTO hits (id, n) VALUES (1, 10)
  ON DUPLICATE KEY UPDATE n = n + 5;            -- n -> 15

-- Postgres form: same, with a conflict target + EXCLUDED
INSERT INTO hits (id, n) VALUES (1, 100)
  ON CONFLICT (id) DO UPDATE SET n = n + 100;   -- n -> 115

-- Reference the proposed row explicitly
INSERT INTO hits (id, n) VALUES (1, 42)
  ON CONFLICT (id) DO UPDATE SET n = EXCLUDED.n;   -- Postgres: n -> 42
INSERT INTO hits (id, n) VALUES (1, 42)
  ON DUPLICATE KEY UPDATE n = VALUES(n);           -- MySQL: n -> 42

INSERT IGNORE skips a row that would violate a key constraint (leaving the existing row unchanged) instead of erroring. REPLACE INTO (and INSERT OR REPLACE INTO) replaces the conflicting row.

INSERT IGNORE INTO hits (id, n) VALUES (1, 999);   -- existing row unchanged
REPLACE INTO hits (id, n) VALUES (1, 7);           -- row replaced

Constraint enforcement is now live (behavior change)

Behavior change in v1.10.0-ce: PRIMARY KEY, UNIQUE, and CHECK (expr) constraints are now enforced on INSERT and UPDATE. In prior releases they were accepted in DDL but never checked, so duplicate keys and out-of-range values were silently written. A violating statement now errors and the row is not persisted.

CREATE TABLE accounts (
  id    INT PRIMARY KEY,
  email TEXT UNIQUE,
  status TEXT CHECK (status IN ('open', 'closed'))
);
INSERT INTO accounts (id, email, status) VALUES (1, 'a@x.com', 'open');

INSERT INTO accounts (id, email, status) VALUES (2, 'a@x.com', 'open'); -- ERROR: UNIQUE(email)
INSERT INTO accounts (id, email, status) VALUES (1, 'b@x.com', 'open'); -- ERROR: duplicate PRIMARY KEY
INSERT INTO accounts (id, email, status) VALUES (3, 'c@x.com', 'BOGUS'); -- ERROR: CHECK(status)

Enforcement is zero-cost on unconstrained tables (it only engages when a table declares a CHECK, UNIQUE, or PRIMARY KEY, or the statement carries an ON CONFLICT clause). If you relied on the old declared-but-not-enforced behavior, drop the constraint from the DDL.

JSON path operators and functions

JSON_EXTRACT(doc, '$.path') evaluates a real MySQL JSONPath: object keys ($.a.b), array indexing ($.arr[0]), and quoted keys ($."my key"). The path operators mirror MySQL: -> returns the extracted value as JSON (quoted), ->> returns it as an unquoted scalar.

SELECT JSON_EXTRACT('{"a":{"b":42}}', '$.a.b');   -- 42
SELECT JSON_EXTRACT('{"arr":[7,8,9]}', '$.arr[0]'); -- 7

CREATE TABLE docs (id INT PRIMARY KEY, doc JSON);
INSERT INTO docs (id, doc) VALUES (1, '{"name":"bob","age":7,"tags":["x","y"]}');

SELECT doc->'$.name'  FROM docs WHERE id = 1;   -- "bob"  (JSON, quoted)
SELECT doc->>'$.name' FROM docs WHERE id = 1;   -- bob    (unquoted scalar)
SELECT id FROM docs WHERE JSON_EXTRACT(doc, '$.age') = 7;   -- filter on a path

Full JSON function set:

SELECT JSON_CONTAINS('{"tags":["x","y"]}', '"x"', '$.tags');  -- 1
SELECT JSON_ARRAY(1, 2, 3);                                   -- [1,2,3]
SELECT JSON_KEYS('{"a":1,"b":2}');                            -- ["a","b"]
SELECT JSON_LENGTH('[10,20,30]');                             -- 3
SELECT JSON_VALID('{not json');                              -- 0
SELECT JSON_SET('{"a":1}', '$.a', 99);                       -- {"a":99}
SELECT JSON_UNQUOTE('"hello"');                              -- hello
SELECT JSON_TYPE('[1,2]');                                   -- ARRAY

ENUM columns

ENUM('a', 'b', …) is accepted as a column type. It is stored as VARCHAR with an auto-injected CHECK (col IN ('a', 'b', …)), so the allowed-value set is enforced via the same live constraint machinery.

CREATE TABLE tickets (id INT PRIMARY KEY, status ENUM('open', 'closed'));
INSERT INTO tickets (id, status) VALUES (1, 'open');    -- ok
INSERT INTO tickets (id, status) VALUES (2, 'BOGUS');   -- ERROR: not an allowed value

MySQL type aliases and table options

These MySQL DDL type spellings are accepted and normalized to the native type shown:

MySQL table options ENGINE=… and DEFAULT CHARSET=… are accepted (previously they caused a parse failure) and normalized away to the native default table format:

CREATE TABLE t (
  id      INT PRIMARY KEY,
  created DATETIME,
  flag    TINYINT,
  body    LONGTEXT,
  cnt     MEDIUMINT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Auto-updating timestamps

A column declared ON UPDATE CURRENT_TIMESTAMP is automatically refreshed to the current time on every UPDATE of the row, unless the same statement assigns it an explicit value (which is honored). DEFAULT CURRENT_TIMESTAMP (and DEFAULT NOW()) populate the column on INSERT.

CREATE TABLE audit (
  id      INT PRIMARY KEY,
  note    TEXT,
  updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
INSERT INTO audit (id, note) VALUES (1, 'first');   -- updated = now()
UPDATE audit SET note = 'second' WHERE id = 1;      -- updated auto-advances
UPDATE audit SET updated = '2020-01-01 00:00:00' WHERE id = 1; -- explicit value honored

Full-text search (LIKE-ranked MVP)

FULLTEXT[ INDEX] (cols) is accepted as a table constraint in CREATE TABLE, and MATCH(cols) AGAINST('query') is supported in natural-language mode. This is a LIKE-ranked MVP: there is no inverted index — internally MATCH … AGAINST is rewritten to an OR-of-LIKE predicate when used as a WHERE filter, and to a sum-of-CASE relevance score when used in ORDER BY. Boolean-mode operators (+, -, *, "…") are stripped, not interpreted (Phase 2).

CREATE TABLE docs (id INT PRIMARY KEY, body TEXT, FULLTEXT(body));
INSERT INTO docs (id, body) VALUES (1, 'hello world of databases');
INSERT INTO docs (id, body) VALUES (2, 'the quick brown fox');

-- As a WHERE predicate
SELECT id FROM docs WHERE MATCH(body) AGAINST('hello');

-- As a relevance score for ranking
SELECT id, MATCH(body) AGAINST('hello world') AS score
  FROM docs ORDER BY score DESC;

-- Multi-column
CREATE TABLE docs2 (id INT PRIMARY KEY, title TEXT, body TEXT, FULLTEXT(title, body));
SELECT id FROM docs2 WHERE MATCH(title, body) AGAINST('foo bar');

Migrating from MySQL — synapcores import --from-mysqldump

The synapcores import --from-mysqldump <file.sql> CLI replays a mysqldump file against a running gateway over HTTP. The statement splitter is quote-, comment-, and DELIMITER-aware; oversized extended (multi-row) INSERTs are split into batches; MySQL string escapes (e.g. \') are translated. Wrapper cruft (SET, LOCK/UNLOCK TABLES, USE, conditional /*! … */ comments) is skipped, and unsupported statements (e.g. CREATE TRIGGER / CREATE PROCEDURE) are reported — never silently dropped. It emits a compatibility report (executed / tables / rows / skipped-with-reason / errored).

synapcores import --from-mysqldump dump.sql \
  --url http://127.0.0.1:8100 --username admin --password "$ADMIN_PASS"

# Parse + classify + report only, execute nothing:
synapcores import --from-mysqldump dump.sql --url … --username … --password … --dry-run

# Abort at the first engine error instead of continuing:
synapcores import --from-mysqldump dump.sql --url … --username … --password … --stop-on-error

Connecting over the MySQL wire protocol

Existing MySQL clients — DBeaver, Metabase, Connector/J (JDBC), the mysql CLI, Node/PHP drivers — can connect to SynapCores directly over the MySQL wire protocol. It is off by default; enable it under [mysql_wire] in the gateway config:

[mysql_wire]
enabled     = true
bind        = "127.0.0.1"
port        = 3307          # default 3307 (NOT MySQL's 3306), so it runs alongside an
                            # existing MySQL/MariaDB on 3306; set port = 3306 for the classic port
require_tls = false         # when true, non-TLS clients are refused (the listener reuses the
                            # gateway HTTPS cert); when false, TLS is still offered if a cert exists

Authenticate with your SynapCores username and API key (supplied as the MySQL password).

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.

Operator configuration

Gateway (community.toml) settings and CLI — not SQL, but part of running SynapCores.

Request timeouts for in-process LLM ops (v1.12.0+)

Shipped defaults were raised so first-token cold starts on in-process (native GGUF) models don't trip a request timeout: [server] request_timeout = 300 (seconds, was 30) and [query] default_timeout_ms = 300000 (was 30000).

Anonymous product telemetry (v1.12.0+, opt-out)

SynapCores sends a small amount of anonymous usage telemetry to measure the install footprint. What is sent: a random installation id (a UUID with no link to you), the product version + edition, and the deployment / OS / architecture. What is never sent: SQL, database or table names, schemas, prompts, embeddings, credentials, hostnames, usernames, IP addresses, or any row / customer data. It runs on a detached background task and cannot slow down or affect the database.

[telemetry]
enabled  = true
endpoint = "https://telemetry.synapcores.com"
# heartbeat_hours = 24   # optional cadence override (default 24h)

Turn it off in any of these ways: set [telemetry] enabled = false, run synapcores telemetry disable, set DO_NOT_TRACK=1, or set SYNAPCORES_TELEMETRY=off.

synapcores telemetry status                  # enabled?, installation id, endpoint, effective state
synapcores telemetry preview                 # print the EXACT outbound heartbeat JSON (no send)
synapcores telemetry test --send             # send one event now and report the result
synapcores telemetry enable | disable        # persist an explicit choice (overrides the config flag)
synapcores telemetry reset-installation-id   # mint a fresh installation id on next boot

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