SynapCores

AI-Native Database SQL Reference

By , Founder & CTO, SynapCores · Published

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.

Feature Form Min version
Vector column type col VECTOR(N) where N is the embedding dimension 1.0
Text embedding EMBED(text_expr) → VECTOR(N) 1.0
Cosine similarity COSINE_SIMILARITY(vec_a, vec_b) → DOUBLE in [-1, 1] 1.0
Euclidean distance EUCLIDEAN_DISTANCE(vec_a, vec_b) → DOUBLE ≥ 0 1.0
LLM text generation GENERATE(prompt [, options_json]) → TEXT 1.0 (1.8.7 added options)
Decision model (typed, 0-token) DECIDE(state, question_json [, options_json]) → JSON answer 2.5.1
Decision — choice DECIDE_CHOICE(state, instructions, criteria_obj [, options_json]) → TEXT 2.5.1
Decision — yes/no DECIDE_YESNO(state, instructions [, options_json]) → DOUBLE P(yes) 2.5.1
Decision — score rubric DECIDE_SCORE(state, instructions, levels_array [, options_json]) → DOUBLE 2.5.1
Decision — multi-question SELECT … FROM DECIDE_ALL(state, questions_obj [, options_json]) 2.5.1
NLI pair classification NLI_CLASSIFY(model, premise, hypothesis) → JSON 2.1.2
NLI statement verification NLI_VERIFY(model, statement, anchors_json [, options_json]) → JSON 2.1.2
NLI evidence aggregate NLI_ANCHORS(text) aggregate → JSON array 2.1.2
JSON object literal builder json_object(key, value, ...) → JSON 1.8.7
In-database agent AGENT_RUN(persona, task [, options]) → TEXT 1.6.6.9 (options 1.8.9)
Durable agent CREATE AGENT name PERSONA … TASK … ON INSERT INTO t … 1.9.0
Learning-loop goal (objective only) CREATE GOAL name WITH (objective = '…') — plan steps via REST 2.3.0
Persona as a database object CREATE PERSONA name WITH (system_prompt = '…') 1.14.2
Edit / drop a persona ALTER PERSONA name SET (…), DROP PERSONA [IF EXISTS] name 1.14.2
Inspect personas SHOW PERSONAS [LIKE '…'], DESCRIBE PERSONA name 1.14.2
Fire a durable agent asynchronously WAKE AGENT name 1.14.2
Agent-to-agent chaining … ON UPDATE ON t WHERE … ALLOW AGENT ORIGIN 1.14.2
Store agent memory MEMORY_STORE(ns, content [, metadata [, options]]) → TEXT 1.8.5
Recall agent memory MEMORY_RECALL(ns, query [, top_k]) → table 1.8.5
Upsert agent memory MEMORY_UPSERT(ns, content [, options]) → ADD/UPDATE/DELETE/NOOP 1.8.9
Forget agent memory MEMORY_FORGET(ns, id) → BOOLEAN 1.8.5
Persistent agent memory CREATE MEMORY name IDENTITY user_id 1.14.3
Write a memory REMEMBER mem FOR user_id = 123 '<text>' → envelope 1.14.3
Read assembled context RECALL mem FOR user_id = 123 ABOUT '<query>' → envelope 1.14.3
Authoritative present value CURRENT mem FOR user_id = 123 ATTRIBUTE attr → envelope 1.14.3
Explain a remembered value TRACE mem FOR user_id = 123 ATTRIBUTE attr → envelope 1.14.3
Authorized removal FORGET mem FOR user_id = 123 ABOUT '<scope>' → envelope 1.14.3
Train AutoML model CREATE EXPERIMENT name AS SELECT ... WITH (task_type=..., ...) 1.5
Predict with AutoML SELECT AUTOML.PREDICT('model', col1, col2, ...) FROM t 1.5
Native model pull PULL_MODEL('qwen2.5-coder:7b') → TEXT 1.8.0
Native model list LIST_MODELS() → table 1.8.0
Native model drop DELETE_MODEL('name') → TEXT 1.8.0
Cypher graph query MATCH (n:Label) RETURN n 1.6
Cypher graph write CREATE, MERGE, DETACH DELETE 1.6
PL procedure CREATE PROCEDURE name(args) AS $$ … $$ LANGUAGE plpgsql 1.6.6
Trigger CREATE TRIGGER [IF NOT EXISTS] trg BEFORE/AFTER INSERT/UPDATE/DELETE ON t EXECUTE PROCEDURE p() 1.6.6
Natural language SQL ASK '…' 1.0
Append-only audit ledger CREATE IMMUTABLE TABLE 1.5
Chain attestation VERIFY TABLE t, VERIFY RECORD id IN t 1.12.0
Query Parquet on object storage CREATE EXTERNAL TABLE t STORED AS PARQUET LOCATION 's3://…' 1.16.0
Re-read a lake schema REFRESH EXTERNAL TABLE t 1.16.0
List lake tables SHOW EXTERNAL TABLES 1.16.0

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).

Table-level [CONSTRAINT name] UNIQUE (...), FOREIGN KEY (...) REFERENCES ... and CHECK (expr) may also be written after the column list (see Table constraints — out-of-line and ALTER). PRIMARY KEY, UNIQUE and CHECK are enforced on write — see Constraint enforcement is now live.


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;
ALTER TABLE t ADD CONSTRAINT name UNIQUE (col, ...);
ALTER TABLE t ADD CONSTRAINT name FOREIGN KEY (col, ...) REFERENCES parent (col, ...);
ALTER TABLE t DROP CONSTRAINT [IF EXISTS] name;

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;

Table constraints — out-of-line and ALTER (v2.2.0-ce)

Constraints can be declared out-of-line (table-level, with an optional name) in CREATE TABLE, and added to or removed from an existing table with ALTER TABLE. This complements the inline column constraints above.

-- Out-of-line, named constraints in CREATE TABLE (incl. composite keys)
CREATE TABLE pair_parent (
  a INT, b INT,
  CONSTRAINT pair_key UNIQUE (a, b)
);
CREATE TABLE pair_child (
  id INT PRIMARY KEY, a INT, b INT,
  CONSTRAINT pair_fk FOREIGN KEY (a, b) REFERENCES pair_parent (a, b)
);

-- Add / drop constraints on an existing table
ALTER TABLE accounts ADD CONSTRAINT uq_email UNIQUE (email);
ALTER TABLE orders   ADD CONSTRAINT fk_cust  FOREIGN KEY (customer_id) REFERENCES customers (id);
ALTER TABLE orders   DROP CONSTRAINT fk_cust;
ALTER TABLE orders   DROP CONSTRAINT IF EXISTS maybe_missing;

ALTER TABLE ... ADD CONSTRAINT validates the existing rows first: if the current data already violates the constraint (a duplicate for UNIQUE, an orphan row for a FOREIGN KEY), the statement fails and nothing is attached — there is no partial state to clean up. A FOREIGN KEY requires the parent's matching PRIMARY KEY/UNIQUE key and compatible column types, and uses MATCH SIMPLE (a composite key with any NULL component is skipped). DROP CONSTRAINT removes the backing enforcement; a key that is still referenced by a foreign key cannot be dropped.

!!! note "Supported today" ALTER TABLE ... ADD CONSTRAINT ... UNIQUE (...) and ... FOREIGN KEY (...) REFERENCES ..., and ALTER TABLE ... DROP CONSTRAINT [IF EXISTS] name, on native row tables. ALTER ADD CHECK, ALTER ADD PRIMARY KEY, and deferred constraints are not yet supported and fail explicitly — declare CHECK and PRIMARY KEY inline or table-level in CREATE TABLE instead. On columnar tables, PRIMARY KEY/UNIQUE are hints unless the table is declared ENFORCED (see Columnar tables).

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;

Columnar tables (ENGINE = SYNAPCORES_COLUMNAR)

Built for analytical scans over large tables: a query reads only the columns it projects. Storage is Parquet parts with RocksDB metadata, and writes are copy-on-write — an affected part is rewritten rather than edited in place.

CREATE TABLE events (
  id      INT PRIMARY KEY,
  ts      TIMESTAMP,
  region  TEXT,
  amount  DOUBLE
) ENGINE = SYNAPCORES_COLUMNAR;

ENGINE = SYNAPCORES_COLUMNAR is the whole syntax — it takes no further options. Adding any (, COMPRESSION = …, , BLOCK_SIZE = …) is a parse error, and a WITH ( … ) clause on CREATE TABLE must not be used at all: it silently retyped every column to TEXT before v1.15.0-ce.

Columnar tables support the full INSERT / UPDATE / DELETE surface.

!!! warning "PRIMARY KEY and UNIQUE are not enforced on columnar tables" They are accepted as a clustering and metadata hint. Duplicate keys are accepted — the insert succeeds and both rows are stored.

This follows the analytical-store convention: Snowflake, BigQuery, Amazon
Redshift, ClickHouse, Apache Iceberg and Delta Lake all allow the
declaration without enforcing it, because a uniqueness probe on every
insert removes the bulk-ingest advantage that is the reason to use
columnar storage at all. BigQuery makes you write `NOT ENFORCED`
explicitly.

**A row-storage table in this engine DOES enforce it**, so the same
statement behaves differently depending on `ENGINE`. `CREATE TABLE` emits
a warning when a columnar table declares one.

If you need uniqueness: de-duplicate at ingest, or use a row table for the
keyed data and a columnar table for the analytical copy.

COPY query results to lake

COPY (SELECT id, metro, amount FROM permits WHERE amount > 0)
TO LAKE 'matches/'
WITH (CONNECTION = 'werkor_lake', PARTITION_BY = (metro),
      MODE = 'overwrite_partitions', COMPRESSION = 'zstd',
      ROW_GROUP_SIZE = 122880, FILE_TARGET_MB = 128);

CREATE EXTERNAL TABLE permit_matches STORED AS PARQUET
LOCATION 'matches/' PARTITIONED BY (metro TEXT)
WITH (CONNECTION = 'werkor_lake');
SELECT COUNT(*) FROM permit_matches WHERE metro = 'nyc';

Materializes a one-shot SELECT/WITH read query as plain foreign Parquet. The source streams directly to the writer, without the REST SELECT response cap. The result returns job_id, run_id, rows_written, files_written, bytes_written and partitions_touched.

CONNECTION is required and PARTITION_BY is optional. Path columns become Hive directories and are omitted from the data files. Output fields are OPTIONAL. Exact decimals and supported temporal types remain typed Parquet. Writers emit row-group statistics, page indexes and high-cardinality blooms.

Permissions: configure an explicit destination write_allowlist. Empty, including on existing upgraded destinations, disables COPY writes. Grants are relative to the destination root: matches/ permits that child prefix; / explicitly permits every non-managed child location. A read allowlist is separate and is required to read back the output. COPY requires a fresh authenticated principal with effective full data-write/source-database rights. Read-only or table-restricted credentials and active user transactions are refused. Agent operating privileges alone do not grant COPY authority. SQL cannot introduce another bucket, absolute path or managed-lake ancestry.

Modes: overwrite_partitions is the default and replaces only partitions actually produced; an empty source deletes nothing. append adds files without deleting old data. Repeating a successful append duplicates the rows; it is not an exactly-once retry interface. Compression supports zstd, snappy, gzip, lz4, none. Row groups default to 122880 rows (maximum 1048576). File targets default to 128 MiB (1..1024); rolling happens at row-group boundaries.

Publication: complete files are staged out of reader visibility, then published with recovery records before old files are removed. Plain object-store listing has no multi-file transaction: readers may temporarily see a mixture of complete old/new files during replacement. Publication across partitions is not a table-wide transaction. Reruns reconcile interrupted publication; unresolved ownership from a different engine is never stolen merely because a timer expired.

COPY shares the lake export concurrency cap and uses bounded batch/partition writers. The source still obeys normal query memory, spill and timeout limits. List/poll metadata at /v1/lake/copy/jobs and /v1/lake/copy/jobs/<job_id>, and inspect/cancel runs through /v1/lake/runs. A disconnected SQL request may still complete a publication already in progress; inspect the durable run before retrying. No SQL text or credentials are stored in COPY job records.

The full contract, recovery limits and operating procedure are in docs/sql/copy_to_lake.md. External tables remain read-only for row mutations; COPY is a bulk export.

External tables — querying Parquet on object storage (v1.16.0-ce)

An external table is a name in the catalog pointing at a prefix in object storage. Nothing is copied into the database and no storage is allocated: the data stays where it was written, and creating the table costs one Parquet footer read.

CREATE EXTERNAL TABLE [IF NOT EXISTS] [db.]name [(col type [NOT NULL], …)]
  STORED AS PARQUET
  LOCATION '<uri>'
  [PARTITIONED BY (col type, …)]
  [WITH (CONNECTION = '<destination name>')];

DROP EXTERNAL TABLE [IF EXISTS] [db.]name;
REFRESH EXTERNAL TABLE [db.]name;
SHOW EXTERNAL TABLES [FROM <database> | IN <database>] [LIKE '<pattern>'];

The reason to care is what the planner does with it: an external table is an ordinary relation, so it joins, filters and aggregates against live tables in a single statement. A year of history sits in cheap object storage and still answers a question alongside today's rows — no ETL step back into the database, and no second query engine whose results you stitch together in application code.

CREATE EXTERNAL TABLE pings
  STORED AS PARQUET
  LOCATION 's3://acme-lake/raw/pings/'
  PARTITIONED BY (dt DATE)
  WITH (CONNECTION = 'lake_s3');
-- External table 'pings' registered over PARQUET at s3://acme-lake/raw/pings/
-- (25 column(s), 1 partition column(s))

SELECT c.name, COUNT(*) AS calls
  FROM pings p
  JOIN customers c ON c.id = p.customer_id
 WHERE p.dt BETWEEN '2026-09-01' AND '2026-09-07'
 GROUP BY c.name
 ORDER BY calls DESC;

customers is an ordinary table. pings is Parquet on S3. One statement, one result set.

External tables are read-only. INSERT, UPDATE and DELETE are refused with the route to take instead:

INSERT is not allowed on 'pings': it is an external table and is read-only.
Its data is PARQUET at s3://acme-lake/raw/pings/. Write to the lake, then run
REFRESH EXTERNAL TABLE pings.

Partition layout. PARTITIONED BY declares the columns carried by the directory name rather than by the files — the Hive-style layout the export job writes:

s3://acme-lake/raw/pings/dt=2026-09-18/part-0000.parquet
s3://acme-lake/raw/_manifests/pings/dt=2026-09-18.json

Only dt=YYYY-MM-DD and snapshot_dt=YYYY-MM-DD count as partition directories; anything else is skipped rather than half-read. A partition column is never repeated in the column list — declaring it twice is a parse error, since the relation would carry two columns of the same name. Pruning whole partitions from their directory names, before an object is opened, is what makes WHERE dt BETWEEN … cheap on a table holding a year.

!!! warning "Only partitions with a verified manifest are visible" The export writes a partition's manifest last, after every data file is durable. Object stores have no rename, so that write order is the atomicity: a partition with no manifest, or an unverified one, does not exist as far as a reader is concerned.

A half-written export is therefore invisible rather than partially
readable. That is the behaviour to want — a partial partition read as if it
were complete is a wrong answer that looks exactly like a right one. If a
query returns fewer rows than expected, check the export job's run history
before suspecting the query.

Schema: inferred, or declared and checked. Omit the column list and the schema comes from the Parquet footer. Declare one and it is checked against the data, and a mismatch fails at CREATE:

CREATE EXTERNAL TABLE orders: the declared schema does not match the data:
column 'amount' is declared DOUBLE PRECISION but the data holds BIGINT;
column 'region' is present in the data but not declared.
Omit the column list to take the schema from the data.

Failing there rather than at query time is the point: a table that registers cleanly and breaks on first use usually breaks in front of a BI tool that cannot show the error.

WITH (CONNECTION = '…') names the destination supplying credentials and path confinement. The grammar marks it optional; in practice it is required, so always write it. Without it the read path has no destination and therefore no credentials, and CREATE fails:

external table 'pings' has no CONNECTION; the lake cannot know which
destination — and therefore which credentials — to read it with

It is the only supported option, and an unknown one is rejected rather than ignored — quietly dropping CONNECTION would build a table reading through credentials nobody chose.

LOCATION is resolved relative to the destination's prefix and checked against that destination's read allowlist; a location outside it is refused, naming what was compared. Anyone who can write SQL could otherwise read every object those credentials reach, so the allowlist on the destination is the security boundary — not the SQL statement.

REFRESH EXTERNAL TABLE re-reads the column list. The schema is stored so DESCRIBE and BI introspection answer without a network round trip, and that is what goes stale when an export adds or retypes a column:

REFRESH EXTERNAL TABLE pings;
-- External table 'pings' refreshed: 26 column(s) (was 25)

New partitions do not need it. Partitions are discovered per scan and cached for partition_cache_ttl_secs (default 300 seconds), so yesterday's export becomes visible on its own inside that window. If a day is missing, wait out the TTL or check that the export finished — refreshing re-resolves the schema, not the partition cache.

!!! warning "DROP EXTERNAL TABLE never deletes your data" sql DROP EXTERNAL TABLE pings; -- External table 'pings' unregistered. No objects were deleted from storage.

The registration goes; not one object under `LOCATION` is touched, and
re-running `CREATE EXTERNAL TABLE` over the same prefix brings the table
back. This is the opposite of `DROP TABLE` on an ordinary table, which does
destroy the rows.

Deleting lake objects is a lifecycle-policy job for the object store, and
deliberately not something SQL can do here: a `DROP` that silently emptied
an S3 prefix would be an unrecoverable mistake one autocomplete away.

SHOW EXTERNAL TABLES returns one row per registered table — table_name, format, location, connection, partition_columns, columns (the count, not the names) and created_at. It answers "what is this database reading out of object storage, and with whose credentials", which is worth reading before granting access: an external table's reach is the reach of its connection.

SHOW EXTERNAL TABLES;
SHOW EXTERNAL TABLES FROM lake LIKE 'ping%';

Two further refusals worth knowing. If the build has no lake provider installed, a query against an external table is an error ("external tables are not available in this build"), never an empty result — an empty answer that should have been an error is the worst thing a database can return. And a name already taken by a regular table is refused even under IF NOT EXISTS, which means "leave the external table I already registered alone", not "accept that this name belongs to something else".

Indexes

-- Both clause orders are accepted (v1.15.0-ce): PostgreSQL leading, MySQL trailing.
CREATE [UNIQUE] INDEX [IF NOT EXISTS] idx ON t (col [ASC|DESC], ...) [USING BTREE|HASH];
CREATE [UNIQUE] INDEX [IF NOT EXISTS] idx ON t USING BTREE|HASH (col [ASC|DESC], ...);
CREATE INDEX [IF NOT EXISTS] idx ON t USING HNSW (vector_col)
  [WITH (m = 16, ef_construction = 200, ef_search = 100, metric = 'cosine'|'euclidean'|'dot')];
DROP   INDEX [IF EXISTS] idx;

BTREE (the default) and HASH are the scalar index types; HNSW indexes a VECTOR(N) column (see below). The trailing form was a parse error before v1.15.0-ce.

CREATE INDEX builds the index from the rows already in the table and reports what it did:

Index idx_zip created on table zip_crosswalk (B-tree); 189361 rows indexed

CREATE UNIQUE INDEX over data that already contains a duplicate fails and the index is not created — a half-populated unique index would be worse than none.

Vector indexes — USING HNSW. An HNSW index on a VECTOR(N) column is real SQL DDL. Checked on 2.4.3-ce, using the table from the semantic search recipe:

CREATE INDEX recipe_sem_articles_hnsw ON recipe_sem_articles
USING HNSW (embedding)
WITH (m = 16, ef_construction = 200, ef_search = 100, metric = 'cosine');
-- Index recipe_sem_articles_hnsw created on table recipe_sem_articles (HNSW); 4 rows indexed, 0 NULL skipped
  • The column goes in bare. The metric is an option, metric = 'cosine' (the default), 'euclidean' or 'dot'; USING HNSW (embedding COSINE) and pgvector's vector_cosine_ops are parse errors.
  • ef_search is set on the index; there is no per-query ef clause.
  • An unknown option fails at create time: Unknown index option 'bogus_param'.
  • SHOW INDEXES FROM t lists it with Index_type = HNSW (COSINE).
  • No special query syntax: ORDER BY COSINE_SIMILARITY(embedding, EMBED('…')) DESC LIMIT k, or a bare alias of it (… AS s … ORDER BY s DESC LIMIT k), makes EXPLAIN show HNSW IndexScan in place of the full scan.

USING IVFFLAT is rejected ("IVFFLAT SQL indexes are not supported; use USING HNSW"). An index on a VECTOR(N) column with no USING clause is rejected too, because a B-tree over an embedding accelerates nothing while leaving a table that looks indexed.

!!! warning "Before v1.15.0-ce" CREATE INDEX returned success without building anything, so no secondary index ever accelerated a query, and index data was not rebuilt at startup — which meant a restart silently stopped PRIMARY KEY enforcement. Indexes created on an older build are registered but empty; CREATE INDEX ... IF NOT EXISTS on this version registers and backfills the missing index.

Data Manipulation Language (DML)

INSERT INTO t [(c1, c2, ...)] VALUES (v1, v2, ...), ...;
INSERT INTO t [(c1, c2, ...)] SELECT ...;
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];

rows_affected is the number of rows actually written. An INSERT ... SELECT whose SELECT matches nothing reports rows_affected = 0, so a client can detect a no-op — before v1.14.3 one entry point reported 1 for that case because it counted the result envelope rather than the write. The same rule holds for UPDATE and DELETE: a statement whose WHERE matches no row reports 0, which is what makes a conditional UPDATE usable as an atomic claim.

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];

Since v2.1.0, comparisons between a numeric value and TEXT compare numerically when the text parses as a number: int_col = '06' matches 6, '0123' joins 123, and int_col < '30' orders numerically. Text that does not parse equals no number (= is false, <> is true). Index lookups and join keys follow the same domain, so results do not depend on the chosen plan. Text-to-text comparison remains exact: '06' <> '6'.

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

As of v2.0-ce transactions are atomic, isolated and crash-durable. Writes are held until COMMIT, other connections cannot see them, and a COMMIT that returns success has already reached the write-ahead log — so an acknowledged commit survives a hard kill, and an unfinished transaction leaves nothing behind.

BEGIN [TRANSACTION];
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  SAVEPOINT after_debit;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
  ROLLBACK TO SAVEPOINT after_debit;   -- keeps the debit, drops the credit
  RELEASE SAVEPOINT after_debit;
COMMIT;                                 -- or ROLLBACK

!!! warning "Where you can run this" A bare BEGIN sent to POST /v1/query/execute is refused with HTTP 400 transaction_session_required — that endpoint has no session to attach a transaction to, and silently autocommitting is the pre-v2.0 bug. The two supported paths are:

- **A MySQL wire connection**, where `BEGIN` / `COMMIT` / `ROLLBACK` / `SAVEPOINT` work
  as your client expects. Disconnecting rolls back an open transaction, and
  `SET autocommit = 0` starts one on the next statement.
- **REST**: `POST /v1/transactions` returns an id; pass it as `transaction_id` on each
  statement, then `POST /v1/transactions/{id}/commit`. Savepoints are
  `POST /v1/transactions/{id}/savepoint` with `{"name": "..."}`, and rolling back to one
  is `POST /v1/transactions/{id}/rollback` with `{"savepoint": "..."}`.

Isolation levels. READ COMMITTED (the SQL and REST default), REPEATABLE READ (the MySQL default) and SERIALIZABLE are supported; REPEATABLE READ is write-skew-safe (SQLite / InnoDB-RR parity). READ UNCOMMITTED is rejected, not quietly downgraded. All see your own pending writes, including through scans.

Conflicts are retryable, and conservative. Concurrent writers can receive MySQL error 1213 / SQLSTATE 40001, or HTTP 409 with retryable: true — on real contention: the same row, or a REPEATABLE READ / SERIALIZABLE transaction whose read set a concurrent commit invalidated. Retry the whole transaction from a fresh BEGIN — retrying only the COMMIT will not work. Two writes to different rows of the same table no longer falsely conflict, including concurrent inserts into a table that carries a FOREIGN KEY or a secondary UNIQUE constraint.

Ordinary DML also runs in an implicit transaction, so a statement that fails partway — including its cascades — rolls itself back.

!!! note "What does not participate" Columnar tables, immutable tables, external/lake Parquet reads, AI and memory side effects, enabled triggers and agent event bindings. Inside an explicit transaction a write to a table carrying a trigger or a durable-agent event binding is refused with HTTP 501; outside one it works normally, with the hook firing after commit. So a trigger's side effect is not part of the same commit — full ACID covers your rows and constraints, not trigger side effects. DDL inside an open transaction, cross-database foreign keys, and adding a foreign key to an existing table are all rejected.

Limits: 4,096 savepoint frames per transaction, and timeouts from 1 to 300 seconds. AIDB_SQL_TRANSACTION_BACKEND=legacy returns to the pre-v2.0 non-durable path; any other value fails startup rather than falling back silently.

Referential integrity — FOREIGN KEY (enforced as of v2.0-ce)

CREATE TABLE orders (
    id          INT PRIMARY KEY,
    customer_id INT REFERENCES customers(id),
    total       DECIMAL(10,2)
);

CREATE TABLE order_items (
    id       INT PRIMARY KEY,
    order_id INT,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
);

An INSERT or UPDATE naming a parent row that does not exist is rejected with Constraint violation: Foreign key violation, and the whole statement rolls back. Deleting a parent that still has children is rejected unless you declared an action.

ON DELETE / ON UPDATE accept CASCADE, SET NULL, SET DEFAULT, RESTRICT and NO ACTION, applied within the same statement. The referenced column must be a declared PRIMARY KEY or UNIQUE key of matching type. Composite keys use MATCH SIMPLE: a NULL in any part exempts the whole reference.

Declare foreign keys in CREATE TABLE — adding one to an existing table is rejected, as are foreign keys across databases.

!!! warning "Upgrading" Before v2.0 the syntax was accepted and nothing checked it, so existing data may violate constraints you declared. v2.0 rejects new violations but does not clean up old ones:

```sql
SELECT COUNT(*) FROM orders o
 WHERE o.customer_id IS NOT NULL
   AND NOT EXISTS (SELECT 1 FROM customers c WHERE c.id = o.customer_id);
```

Qualified wildcards

SELECT t.* is supported as of v1.15.0-ce, including the fully qualified form (analytics.orders.*). Where a relation has an alias, either the alias or the table name resolves it — neither silently guesses. An unknown qualifier is an error naming what is in scope.

SELECT o.*, c.name
FROM orders o JOIN customers c ON c.id = o.customer_id;

Earlier versions rejected t.* with Complex projections not supported. COUNT(t.*) is rejected: it is not the same aggregate as COUNT(*) once an outer join is involved, so it is refused rather than guessed at.

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.
  • Input longer than the model's input window (512 tokens for the default all-MiniLM) is truncated to the window and embedded by default (v2.4.8-ce+); the server logs each truncation, so it is never silent. Set [query.ai_service] embed_truncate = false (or AIDB_EMBED_OVERSIZE=reject) to reject over-length input with prompt exceeds model input budget instead. For best retrieval quality, chunk long text to roughly 500 tokens rather than relying on truncation.
SELECT EMBED('wireless noise cancelling headphones');

UPDATE products SET description_vec = EMBED(description);

ARRAY_LENGTH(array_or_vector [, dimension]) · CARDINALITY(array_or_vector)

Returns the number of elements in an array or vector — most often the dimensionality of an EMBED() result. CARDINALITY is an alias.

  • Accepts a one-dimensional array or vector. A JSON-array string works too.
  • The optional second argument is the dimension and must be 1; any other dimension returns NULL, matching PostgreSQL.
-- How wide is the configured embedding model?
SELECT ARRAY_LENGTH(EMBED('hello')) AS dims;

SELECT CARDINALITY(tags) FROM articles;

Renaming a table

Both spellings rename one ordinary row table within its database (v1.17.0-ce). Rows, constraints, defaults and index identities are preserved across the rename and across a restart:

CREATE TABLE orders (id INT PRIMARY KEY, total DECIMAL(10,2));
INSERT INTO orders VALUES (1, 19.99), (2, 5.00);

ALTER TABLE orders RENAME TO archived_orders;
SELECT COUNT(*) FROM archived_orders;        -- 2

RENAME TABLE archived_orders TO orders2;
SELECT SUM(total) FROM orders2;              -- 24.99

The destination must not already exist, and the source must:

RENAME TABLE orders2 TO occupied;   -- error: Table 'occupied' already exists
RENAME TABLE nosuch TO whatever;    -- error: Table 'nosuch' does not exist

Row keys are rewritten, so the cost and the temporary write space grow with the size of the table. SQL against that store is briefly gated while the catalogs change, and a durable journal finishes any interrupted rename before later statements run. Re-prepare any prepared statements that referenced the old name.

Not supported, and refused before anything is written: cross-database moves, columnar, external, partitioned and immutable tables, tables inside an active transaction, auto-increment columns, names beginning with _, foreign keys and dependent views. A tenant holding triggers, stored procedures or durable agents must remove those first — their stored SQL cannot yet be rewritten safely.

Numeric casts

NUMERIC, DECIMAL, BIGNUMERIC and BIGDECIMAL are accepted as cast targets, so ::NUMERIC both plans and executes rather than erroring.

SELECT (revenue_text)::NUMERIC AS revenue FROM imports;

SIGNED, UNSIGNED, SIGNED INTEGER and UNSIGNED INTEGER are accepted for MySQL compatibility (v1.16.0-ce) and map to BIGINT, so a query copied from MySQL — or emitted by a BI tool against the MySQL wire port — parses unchanged. UNSIGNED is a spelling only; the result is a signed 64-bit integer.

SELECT CAST('42' AS SIGNED) AS v;                  -- 42
SELECT CAST(total AS UNSIGNED INTEGER) FROM orders;

Casting to a scaled DECIMAL(p, s) is accepted from any numeric or text source, but the precision and scale you write are not applied:

SELECT CAST(12.5 AS DECIMAL(10,2));     -- 12.5, exact, still scale 1
SELECT CAST('12.5' AS DECIMAL(10,2));   -- 12.5, but APPROXIMATE (a DOUBLE)
SELECT CAST(12 AS DECIMAL(10,2));       -- 12.0, approximate

A DECIMAL source passes through unchanged at its own scale; a TEXT or integer source becomes an approximate DOUBLE. A cast is therefore not the way to rescale money. Declare the column at the scale you want and let INSERT or UPDATE coerce to it — as of v1.16.0-ce both do, and the declared scale is honoured:

CREATE TABLE invoices (id INT PRIMARY KEY, amount DECIMAL(10,2));
INSERT INTO invoices VALUES (1, 29.99);
SELECT amount FROM invoices;            -- 29.99
UPDATE invoices SET amount = 7.7 WHERE id = 1;
SELECT amount FROM invoices;            -- 7.70

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.


Decision model — the DECIDE family (v2.5.1-ce)

Runs the native decision model (d1 / System One) over a state and one or more typed questions, returning a calibrated answer in a single forward pass with zero output tokens — this is the SQL form of POST /v1/systemone. It never calls the text-generation model: model resolution is options.model → [query.ai_service].decision_model → 'd1:latest'. Requires an engine with native inference (provider = "local").

Each question is one of three types:

  • choice — pick one option. criteria is an object of option → description (capped at 26 options).
  • noul — yes/no; the answer is P(yes). criteria is optional and ignored (supply it or omit it).
  • score — an ordered rubric. criteria is an array of 2–10 level labels, lowest first; the score is the probability-weighted (fractional) level index.

DECIDE(state, question_json [, options_json]) → JSON

The full form: returns the complete answer object — {type, choice|noul|score, probabilities, confidence} — as JSON.

SELECT DECIDE(
  body,
  json_object('type','choice','instructions','Which team should handle this?',
              'criteria', json_object('billing','payments/refunds','shipping','delivery','technical','bugs'))
) AS routing
  FROM tickets WHERE status = 'open' LIMIT 20;

Typed convenience scalars

Thin shells over the same decoder that return a single scalar — ideal in WHERE, ORDER BY, or a CREATE AGENT condition.

  • DECIDE_CHOICE(state, instructions, criteria_obj [, options_json]) → TEXT — the chosen option.
  • DECIDE_YESNO(state, instructions [, options_json]) → DOUBLE — P(yes) in [0,1].
  • DECIDE_SCORE(state, instructions, levels_array [, options_json]) → DOUBLE — the expected (0-based, fractional) level index.
-- Route, flag, and score — all without generating a token.
SELECT id,
       DECIDE_CHOICE(body, 'Which team?',
         json_object('billing','payments/refunds','shipping','delivery','technical','bugs')) AS route,
       DECIDE_YESNO(body, 'Is the customer angry?')                                           AS angry_p,
       DECIDE_SCORE(body, 'How urgent?', '["can wait","this week","today","right now"]')      AS urgency
  FROM tickets
 WHERE status = 'open'
 ORDER BY angry_p DESC;

DECIDE_ALL(state, questions_obj [, options_json]) — table-valued

Answers several questions at once (goes in FROM, like MEMORY_RECALL). questions_obj maps name → {type, instructions, criteria?}, mixing types; all are decided in one batched set of forward passes over the resident model (the state prefix is rendered once). Returns one row per question: question TEXT, answer_type TEXT, answer JSON, confidence DOUBLE.

SELECT question, answer_type, confidence, answer
  FROM DECIDE_ALL(
    'Customer charged twice, no shipment, wants it TODAY',
    json_object(
      'route',   json_object('type','choice','instructions','Which team?',
                   'criteria', json_object('billing','payments','shipping','delivery')),
      'angry',   json_object('type','noul','instructions','Is the customer angry?'),
      'urgency', json_object('type','score','instructions','How urgent?',
                   'criteria', '["can wait","this week","today","right now"]')
    )
  );

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.
-- 'returns-triage' is not a built-in: create it first (see Personas below) or
-- this errors with "AGENT_RUN: unknown persona 'returns-triage'" (v1.14.3+).
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.

Option Type Default Meaning
model TEXT persona / config Override the completion model for this call (v1.8.10+). Outranks the persona and the [query.ai_service] default; a model that is not installed errors.
max_iterations INT 5 ReAct tool-call cap. Clamped to 1..10.
timeout_ms INT 600000 Wall-clock budget. Clamped to 1000..600000.
allow_unknown_persona BOOL FALSE v1.14.3+. Run on the built-in default persona when persona does not exist, instead of erroring.
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. One key is deliberately rejected: allow_writes — the agent's tools are read-only from SQL, since 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.

The persona must exist — breaking change in v1.14.3. AGENT_RUN('does-not-exist', …) now fails with AGENT_RUN: unknown persona '<name>'. Available: …, the same rule CREATE AGENT has enforced since v1.14.2. Earlier builds logged a warning and silently ran the task on the built-in default persona — which is conversational, so an unattended agent answered in prose instead of calling its tools and returned plausible-looking wrong output. Create the persona with CREATE PERSONA, name a built-in, or pass json_object('allow_unknown_persona', true) to opt back into the old fallback deliberately.


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).

Policy Behavior
replace Overwrite. ADD when absent, UPDATE when present.
replace_higher_confidence Write only if the new confidence ≥ the stored one; else NOOP. A guess never clobbers a fact.
merge_max_confidence Always write, keeping MAX(new, stored) confidence.
append_history Never overwrite — insert a superseding row with _version = prev + 1.
noop_if_equal NOOP when the content is byte-identical; otherwise replace.
-- 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;

Persistent agent memory — CREATE MEMORY (v1.14.3+)

The four primitives above are a flat semantic store: one namespace, one similarity search. CREATE MEMORY is the other thing — one logical object that coordinates episodic events, durable facts, temporal validity, entity relationships, provenance, and an authoritative current state, and that maintains itself.

The rule that shapes it: the engine owns the lifecycle. An agent expresses intent; classification, extraction, deduplication, temporal reconciliation, relationship creation, provenance, indexing, consolidation and retention are the database's job. You never call CONSOLIDATE to keep memory correct.

CREATE MEMORY customer_memory
FOR AGENT support_agent
IDENTITY user_id
WITH (
    episodic = true, semantic = true, temporal = true,
    relationships = true, provenance = true,
    consolidation = 'AUTO',
    consolidate_on_session_end = true,
    consolidate_after_episodes = 50,
    retain_raw_episodes = true
);

Every option has a default, so CREATE MEMORY customer_memory IDENTITY user_id; is a complete object. IDENTITY names the field that scopes every read and write — memory never crosses identities.

Nothing is hidden: the object materializes five ordinary tables you can SELECT from.

Table Holds
_mem_<name>_episodes raw statements — the source everything derives from
_mem_<name>_facts durable facts and attribute claims, with validity intervals
_mem_<name>_state the authoritative current value per (identity, attribute)
_mem_<name>_prov derivation lineage
_mem_<name>_rels typed relationships (also mirrored into the tenant graph)

The five verbs you actually use

REMEMBER · RECALL · CURRENT · FORGET · TRACE. Everything else — CONSOLIDATE, RECONCILE, SUPERSEDE, HISTORY, SEARCH, RELATE, EXPLAIN MEMORY — is optional control, never a prerequisite.

REMEMBER — v1.14.3+

REMEMBER customer_memory
FOR user_id = 123
'We completed the production migration from Azure to AWS yesterday because the engineering team wants Bedrock integration.';

One statement persists the raw episode and then derives, with no further call: the semantic facts, the current-state change, the closed validity interval on the old value, the relationships, and the provenance linking each derived record back to this sentence. The raw episode is committed before any derivation runs, so a model failure never loses it.

Works with no model configured. A deterministic extractor always runs; the LLM pass is additive and degrades to the rule result on failure. When you want certainty, state the change outright:

REMEMBER customer_memory FOR user_id = 123 'Now on AWS'
  WITH (attribute = 'cloud_provider', value = 'AWS');

Options: source_type, source_id, confidence, event_time, session_id, session_end, attribute, value, extract.

source_type sets the claim's authority, the first dimension of conflict resolution: system_of_record > verified_user_statement > user_statement > tool_result > prior_user_statement > agent_derivation > agent_inference.

RECALL — v1.14.3+

RECALL customer_memory FOR user_id = 123 ABOUT 'customer deployment environment';

Returns current state separately from facts, episodes, relationships, temporal transitions and provenance, so a model never has to guess which similar-looking string is authoritative — plus context.prompt_ready, a compact projection you can paste straight into a prompt.

Two properties worth relying on: authority beats similarity ("what cloud do we use now?" returns AWS ahead of the semantically similar older Azure episode, while "what did we use before AWS?" routes to temporal history), and confidence and relevance are different fields — confidence is evidence strength, relevance is retrieval score.

Options: current, history, episodes, facts, relationships, provenance, temporal, token_budget, prompt_ready, as_of, min_confidence, explain. When the token budget is exceeded, lower-priority context is truncated and context.truncated is set — but current state is preserved.

CURRENT — v1.14.3+

CURRENT customer_memory FOR user_id = 123 ATTRIBUTE subscription_plan;

Deterministic state lookup, never a semantic search. Three outcomes: status = "ok" with one accepted value; status = "not_found" with value: null; or status = "conflict" with both claims in data.competing_claims when authority, temporal validity and confidence all fail to separate them. The engine does not pick — resolve with RECONCILE or a higher-authority REMEMBER.

TRACE and FORGET — v1.14.3+

TRACE  customer_memory FOR user_id = 123 ATTRIBUTE cloud_provider;
FORGET customer_memory FOR user_id = 123 ABOUT 'home address';

TRACE returns the source episode and its verbatim content, when it was recorded versus when the event happened, the extraction method, the confidence, and what it superseded. FORGET is explicit removal only — expiry, deduplication and compaction happen automatically without it — and propagates to derived facts, embeddings, relationships, state and provenance, reporting the count per category.

Consolidation is the engine's job

The engine consolidates on its own schedule — episode-count threshold, session boundary, elapsed age, idle window — with no LLM prompt, agent tool call, or application callback. A cycle retries failed extractions, collapses duplicate facts, re-checks contradictions, applies retention, and advances a per-identity watermark. It is idempotent: running it twice over an unchanged set produces no new facts.

SELECT memory_name, identity, pending_episodes, last_trigger, next_eligible
FROM _system_memory_consolidation;

You may force a cycle with CONSOLIDATE customer_memory FOR user_id = 123; before a report or in a test — but you never have to. If you need it to make an answer correct, that is a bug worth reporting.

Giving a durable agent memory

CREATE AGENT support_bot
PERSONA 'support_agent'
TASK 'Answer the customer''s question.'
WITH (memory_object = 'customer_memory', memory_identity = '123');

The runtime then performs a RECALL before inference — so memory is available while the model reasons, not after it has answered — and a REMEMBER after the turn. Neither is a tool the model can forget to call, and the identity is fixed in the definition, so the model cannot address another user's memory.

The same contract is available over REST at /v1/memory/*. Both surfaces return the identical response envelope with stable error codes.


NLI functions — NLI_CLASSIFY, NLI_VERIFY, NLI_ANCHORS (v2.1.2-ce+)

Natural-language-inference on an installed local ONNX cross-encoder (the same model registry the /v1/ai/nli/* REST endpoints use — SQL shares the loaded sessions, there is no HTTP loopback). Requires NLI enabled (AIDB_NLI_ENABLED=1, model alias via AIDB_NLI_MODEL, default nli-default) and the model installed first: synapcores --config <config> pull nli-default, then restart the gateway. When NLI is disabled or the model is missing the functions error loudly — they never fall back to a generated answer. NLI results are never served from the SQL result cache.

NLI_CLASSIFY(model, premise, hypothesis) → JSON. Probabilities for one pair; each text is capped at 16,384 UTF-8 bytes.

SELECT NLI_CLASSIFY('nli-default',
                    'The deploy shipped Friday afternoon.',
                    'A deployment happened on Friday.') AS scores;
-- {"model":"nli-default","bundle_sha256":"3d809ec2…",
--  "scores":{"contradiction":0.0002,"entailment":0.9966,"neutral":0.0033}}

NLI_VERIFY(model, statement, anchors_json [, options_json]) → JSON. Verifies a statement against up to 32 truth-anchor sentences. Build the array inline with JSON_ARRAY(...) or aggregate rows with NLI_ANCHORS.

SELECT NLI_VERIFY('nli-default',
                  'A deployment happened on Friday.',
                  JSON_ARRAY('The deploy shipped Friday afternoon touching the checkout service.')
       )->>'verdict' AS verdict;
-- TRUTH

The result carries verdict, reason, confidence, violated_anchor, matched_anchor and per-anchor all_scores (contradiction, entailment, neutral — in the supplied anchor order). verdict is one of:

  • TRUTH — an anchor crossed entailment_threshold and none crossed contradiction_threshold; matched_anchor names it.
  • LIE — an anchor crossed contradiction_threshold (violated_anchor names it), or closed_world is true and nothing supported the statement. Contradiction always takes precedence over entailment.
  • UNVERIFIED_PLAUSIBLE — neither threshold crossed under the default open-world policy.

options_json keys (any other key is rejected): closed_world (default false), contradiction_threshold (default 0.6), entailment_threshold (default 0.6). The verdict is a policy outcome over the anchors you supplied, not a certification of factual truth — curate the anchor set. Mixing unrelated timeline facts can trip contradiction precedence (e.g. a "Saturday rollback…" anchor scores 0.99 contradiction against a statement about Friday).

NLI_ANCHORS(text) → JSON array (aggregate). Collects grouped rows into the anchors array. Bounded: 32 anchors per group, 16,384 bytes per anchor — exceeding either errors the statement. Exactly one text expression; DISTINCT, FILTER, ORDER BY, OVER and NULL-treatment modifiers are rejected (anchor order is deterministic). Works in GROUP BY, CTEs and INSERT ... SELECT.

CREATE TABLE claim_evidence (claim_id INT, anchor_text TEXT);
INSERT INTO claim_evidence VALUES
  (1, 'The deploy shipped Friday afternoon touching the checkout service.'),
  (1, 'Release notes dated Friday list the checkout service deployment.');

SELECT claim_id,
       NLI_VERIFY('nli-default',
                  'A deployment happened on Friday.',
                  NLI_ANCHORS(anchor_text))->>'verdict' AS verdict
  FROM claim_evidence
 GROUP BY claim_id;
-- 1 | TRUTH

Like every AI function, NLI functions cannot sit directly in ORDER BY — alias the value and sort by the alias.

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
);
Key Type Default Meaning
system_prompt TEXT required The persona's instructions.
display_name TEXT the persona name Human-facing label.
description TEXT '' Free-text note.
model TEXT none Pin a model. Must already be installed; omit to inherit [query.ai_service].
response_type TEXT 'text' 'text', 'json' or 'code'.
tool_enabled BOOL TRUE Whether runs on this persona may call tools.

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

The bindings column (and the Bindings row of DESCRIBE AGENT) renders each event binding the way it was declared — <op> ON <table> [WHERE <expr>] [ALLOW AGENT ORIGIN] — so the agent-to-agent cascade opt-in is auditable on readback, not write-only (v1.14.2.1+). SHOW AGENTS keeps the summary compact and omits the WHERE; DESCRIBE AGENT shows the whole binding.

EXECUTE AGENT — run once, synchronously

EXECUTE AGENT incident_triage;                 -- run the stored task now
EXECUTE AGENT incident_triage TASK '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'. On v1.14.2.1+ you can audit the opt-in: DESCRIBE AGENT stage2_summarizer reports Bindings = UPDATE ON doc_queue WHERE stage = 'VALIDATED' ALLOW AGENT ORIGIN, and SHOW AGENTS shows UPDATE ON doc_queue ALLOW AGENT ORIGIN. A binding without the opt-in prints no suffix.

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.


Learning-loop goals — CREATE GOAL (v2.3.0+)

CREATE GOAL registers a durable learning-loop goal — its objective only. The statement defines what the goal is (its objective text, owning persona, priority, budget, guardrails and success criterion); it says nothing about how the goal is carried out, and a goal starts with no plan.

CREATE GOAL weekly_retention WITH (
  objective = 'Reduce weekly churn among enterprise accounts',
  persona   = 'retention-analyst',
  priority  = 1,
  budget    = '{"max_steps": 5, "wall_ms": 60000}',   -- JSON STRING literal
  guardrails = '{"allowed_actions": ["sql"]}'          -- JSON STRING literal
);

Inspect and control goals with SHOW AGENT GOALS, PAUSE GOAL <name>, RESUME GOAL <name>, EXECUTE GOAL <name>, and DROP GOAL <name>.

Plan steps are authored via REST, not SQL

CREATE GOAL does not author plan steps or alternatives. Plan steps are authored through the REST learning-loop API:

  • POST /v1/<tenant>/goals with a steps array — create a goal together with its plan in one call.
  • POST /v1/<tenant>/goals/:id/plan — attach or replace the plan of an existing goal.
// POST /v1/<tenant>/goals/<id>/plan
{
  "steps": [
    { "action": "sql", "description": "Pull at-risk accounts" },
    { "action": "generate", "description": "Draft a retention assessment" }
  ]
}

WITH option values must be JSON string literals

A structured option — budget, guardrails, context, steps, actions, definition — must be passed as a single-quoted JSON string literal, e.g. budget = '{"max_steps": 5}'. Function calls such as JSON_OBJECT(...) / JSON_ARRAY(...) and unquoted JSON are rejected by the parser; scalar options accept a quoted string, a number, or a boolean.

-- Rejected: a function call is not a literal here
CREATE GOAL g WITH (budget = JSON_OBJECT('max_steps', 5));
-- Correct: a single-quoted JSON string
CREATE GOAL g WITH (budget = '{"max_steps": 5}');

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:

Form Example Resolves to
name qwen2.5:0.5b registry.ollama.ai/library/qwen2.5:0.5b
name:tag qwen2.5-coder:7b registry.ollama.ai/library/qwen2.5-coder:7b
namespace/name[:tag] library/all-minilm:latest registry.ollama.ai/library/all-minilm:latest
registry/namespace/name[:tag] registry.example.com/user/model:tag fully-qualified

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        = 'regression' | 'classification' | 'binary_classification'
                       | 'multi_classification' | 'anomaly_detection',
    target_column    = 'target',
    [optimization_metric   = 'r2' | 'rmse' | 'mae' | 'accuracy' | 'auc' | 'f1',]
    [max_trials            = 20,]
    [algorithms            = ['logistic_regression', 'random_forest', 'gradient_boosting'],]
    [validation_strategy   = 'holdout' | 'k_fold',]
    [n_folds               = 5,]
    [hyperparameter_strategy = 'fixed' | 'random' | 'bayesian',]
    [max_training_rows     = 500000,]
    [<per-algorithm hyperparameters>]
);
  • Runs synchronously and returns when training finishes, within a 10-minute budget. For queued training use AUTOML.TRAIN.
  • Every option above is honoured, and an unrecognised option is refused rather than ignored (v2.0-ce). Before v2.0, nine of thirteen options were silently discarded — notably max_trials, which ran 2 trials when you asked for 6.
  • The result is one TEXT column of JSON carrying status, best_model_id, best_score, total_trials, trials_attempted, failed_trials, score_basis, n_folds and refit_on_full_data. Trials that fail are reported instead of silently dropped.
  • The target column from the SELECT becomes the label. By convention, for binary classification target = 1 is the positive class.
  • Without algorithms, AutoML explores a sensible default set.
  • hyperparameter_strategy = 'bayesian' runs a real Tree-structured Parzen Estimator. Its first max_trials / 4 trials (minimum 3) are a random warm-up, so give it 15 or more trials.
  • validation_strategy = 'k_fold' reports the cross-validated fold mean and refits the served model on all rows (v2.0-ce).
  • Training above 500,000 rows is refused — raise it with max_training_rows or use AUTOML.TRAIN.
  • DECIMAL feature columns are read as exact values (v2.0-ce). Before v2.0 they reached the model as NULL and were replaced by the column mean, so money columns were invisible to training.

!!! warning "Not supported in this build — each refused explicitly, not silently ignored" - algorithms = ['xgboost'] or ['catboost'] — refused before training starts; neither has an implementation. - task_type = 'clustering' or 'time_series' — no algorithm implements either. - WITH (async = true) and WITH (schedule = '...') — nothing in this build runs them. - CREATE EXPERIMENT ASYNC name AS ... — not available; it was never implemented. Use AUTOML.TRAIN for queued training.

DROP EXPERIMENT — remove an experiment and its model (v2.4.6-ce)

DROP EXPERIMENT [IF EXISTS] experiment_name;

Removes a completed experiment and everything it alone produced — the experiment record, its trial history, and the best model that experiment trained. Afterwards the experiment and that model no longer appear in SHOW EXPERIMENTS, SHOW MODELS, or the automl_experiments / automl_models / automl_trials views, and the removal survives a restart.

  • No CASCADE. If a separately deployed model still names this experiment as its source (for example one you DEPLOYed under a different name), the drop is refused and tells you which model to DROP MODEL first. The experiment's own trained model is removed with it and never blocks the drop.
  • IF EXISTS makes dropping an absent name a quiet success; without it, an absent name is an error.
  • Scoped to the current tenant and database. DROP AUTOML EXPERIMENT is an accepted synonym.
DROP EXPERIMENT churn_model_v1;            -- experiment + its trained model
DROP EXPERIMENT IF EXISTS churn_model_v1;  -- no error if already gone

AUTOML.TRAIN(...) — queued training and automatic retraining (v2.0-ce)

SELECT AUTOML.TRAIN('model_name', '<data query>', '<target column>')
  [WITH ( retrain_after_rows = 10000, retrain_on_drift = true, drift_baseline = 2.0 )];

Queues the work instead of blocking, and is how you retrain a deployed model. Requests run one at a time through a global queue, so a large job cannot starve the database of the CPU it needs to serve queries. Returns queued, pending_rows or pending_drift.

retrain_on_drift needs no labels — it compares live feature values against the scaler stored with the model, so it works on production traffic where outcomes are not yet known.

Algorithm options:

Algorithm Best for Speed Accuracy
logistic_regression Binary classification, interpretable Fast Good
linear_regression Simple regression, interpretable Fast Good for linear
random_forest General purpose, robust Medium High
gradient_boosting High accuracy Slow Very High
neural_network Complex patterns, large data Slow High
knn Local patterns Fast Medium
svm Binary classification, kernels Medium High
naive_bayes Text classification Very fast Medium

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.
  • task_type = 'anomaly_detection' (isolation forest, no target_column) and the ANOMALY_SCORE('model', col1, col2, …) scalar function are available from v2.2.0-ce. ANOMALY_SCORE works in the projection, ORDER BY and WHERE; pass feature columns (column identifiers, not literals) in the order the model was trained on — a different order is refused with a feature mismatch error. To aggregate scores (e.g. MAX per account), compute the score in a subquery and aggregate outside it. See the fraud-ring recipe.

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

Agent RBAC — roles & per-database privileges (v2.3.2+)

Database-scoped role-based access control for autonomous agent operations. These statements are SQL-only (there is no REST equivalent), tenant-local, and limited to a tenant administrator. They govern who may monitor, operate, approve and configure the learning loop / durable agents on a given database — separately from ordinary data permissions (ReadOnly / FullAccess / table grants).

The four database agent privileges

Privilege Grants
AGENT MONITOR read goals, plans, steps, episodes, policy, config/metrics and the database audit chain
AGENT OPERATE create / revise / execute / pause / resume / drop goals and plans
AGENT APPROVE approve, reject and reconcile steps (independent of OPERATE)
AGENT CONFIGURE learning config, schedules, enable/disable autonomy, consolidate / roll back policy

Predefined, immutable role templates (cannot be altered or dropped): agent_monitor {MONITOR} · agent_operator {MONITOR, OPERATE} · agent_approver {MONITOR, APPROVE} · agent_admin {MONITOR, OPERATE, APPROVE, CONFIGURE}.

Roles — CREATE ROLE / ALTER ROLE / DROP ROLE

CREATE ROLE gateway_svc WITH CREATEDB;   -- CREATEDB lets holders create & own databases
ALTER ROLE gateway_svc WITH NOCREATEDB;  -- ALTER requires the CREATEDB/NOCREATEDB keyword
DROP ROLE gateway_svc;
-- CREATE ROLE defaults to NOCREATEDB when the clause is omitted.

Grants — GRANT / REVOKE

The AGENT keyword is required before each privilege in a list.

-- Per-database agent privileges on a custom role:
GRANT AGENT MONITOR, AGENT APPROVE ON DATABASE sales TO ROLE sales_auditors;
REVOKE AGENT APPROVE ON DATABASE sales FROM ROLE sales_auditors;

-- Role membership — inherit predefined roles into a custom role:
GRANT agent_operator, agent_approver TO ROLE gateway_svc;

-- Role membership — bind a role to a user principal (account UUID, not display name):
GRANT agent_operator TO USER 'account-uuid' ON DATABASE sales;  -- ON DATABASE only for USER
REVOKE agent_operator FROM USER 'account-uuid';

Multi-item grants commit all-or-none: if any item is invalid (missing role, inheritance cycle) the engine applies none and returns an error.

Inspecting — SHOW ROLES / SHOW GRANTS / whoami

SHOW ROLES;                              -- all roles: CREATEDB, inherits, privileges, grants
SHOW GRANTS FOR ROLE sales_auditors;     -- a role's effective grants
SHOW GRANTS FOR USER 'account-uuid';     -- a user principal's grants
SHOW GRANTS FOR CURRENT_USER;            -- the calling principal's grants

For the caller's effective per-database rights (owner, tenant_admin, the four agent privileges, CREATEDB) use the REST endpoint GET /v1/auth/whoami?database=<db>.

Role-bound API keys. Bind roles and a database scope to an API key at creation:

POST /v1/api-keys
{ "name": "sales-gateway", "permission": "FullAccess",
  "roles": ["sales_auditors"], "databases": ["sales"], "expires_at": "2027-01-20T00:00:00Z" }

databases accepts exact names and/or the dynamic "owned" selector. Omitting roles keeps the legacy key contract; "roles": [] explicitly grants no agent role. A denied agent operation returns 403 with required_privilege and database in the error body (plus the learning_permission_denied legacy alias).


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.

Extended in v1.14.3. CHECK is now enforced on UPDATE as well as on INSERT and INSERT ... SELECT — before v1.14.3, UPDATE skipped it, so a row could be inserted legally and then updated into a state the constraint forbids. A table-level CHECK (written after the column list) is enforced too; it used to be parsed, accepted, and then dropped, so only inline column CHECKs did anything. A CHECK also survives a restart now, instead of being lost when the schema was reloaded from the on-disk cache.

CREATE TABLE scores (
  score INTEGER,
  CONSTRAINT score_non_negative CHECK (score >= 0)
);

INSERT INTO scores (score) VALUES (-5);
-- ERROR: Table CHECK constraint 'score_non_negative' violated (row 1)

UPDATE accounts SET status = 'BOGUS' WHERE id = 1;
-- ERROR: CHECK constraint violated for column 'status'

NULL follows SQL three-valued logic: a CHECK rejects a row only when the predicate evaluates to FALSE. CHECK (status IN ('open','closed')) therefore accepts a NULL status, because the predicate is UNKNOWN — add NOT NULL if you want to forbid it.

Table-level PRIMARY KEY (a, b) declared after the column list is honoured as of v1.15.0-ce, and creates both the primary key and its index. Earlier versions accepted the clause and silently discarded it, leaving the table with no primary key at all. Table-level UNIQUE (...) and FOREIGN KEY (...) REFERENCES ... are enforced as of v2.0-ce, in both single-column and composite form; earlier releases parsed and silently ignored them. If you declared constraints that way before upgrading, audit for duplicate and orphan rows first — v2.0 rejects new violations but does not retroactively clean existing data.

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:

Function Returns
JSON_EXTRACT(doc, path[, ...]) value(s) at the path(s)
JSON_SET(doc, path, val[, ...]) doc with path set (insert or replace)
JSON_INSERT(doc, path, val[, ...]) doc with path set only if absent
JSON_REPLACE(doc, path, val[, ...]) doc with path set only if present
JSON_REMOVE(doc, path[, ...]) doc with the path(s) removed
JSON_ARRAY(v1, v2, ...) a JSON array of the arguments
JSON_OBJECT(k1, v1, ...) a JSON object (also documented under AI functions as json_object)
JSON_CONTAINS(target, candidate[, path]) 1/0
JSON_KEYS(doc[, path]) JSON array of object keys
JSON_LENGTH(doc[, path]) element/key count
JSON_VALID(str) 1 if valid JSON, else 0
JSON_UNQUOTE(json_str) the unquoted scalar
JSON_TYPE(doc) OBJECT / ARRAY / STRING / …
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 type Native type Note
DATETIME TIMESTAMP
TINYINT SMALLINT full 0–255 range preserved; TINYINT(1) is not coerced to boolean
MEDIUMINT INTEGER
YEAR INTEGER
LONGTEXT / MEDIUMTEXT / TINYTEXT TEXT
LONGBLOB / MEDIUMBLOB / TINYBLOB BYTEA

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 on vector collections (POST /v1/vectors/collections). Real HNSW landed in v1.15.0-ce — before that every collection was a flat index regardless of the index_type you requested (recall@10 went 0.776 -> 0.976 when it was fixed). SQL tables can have their own HNSW index too: CREATE INDEX ... USING HNSW (embedding) WITH (m, ef_construction, ef_search, metric) (see Indexes, above). A top-k ORDER BY COSINE_SIMILARITY(...) DESC LIMIT k uses it automatically.
  • 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