SQL Reference

Auto-synced from source

This page is auto-synced from the SynapCores AIDB engine repository on every release tag. The canonical source is AIDB_SQL_MANUAL.md in the engine repo — do not edit this page directly; your change will be overwritten on the next release.

Last synced from: v2.4.3-ce on 2026-10-30

AIDB is an AI-native SQL database with first-class support for vector embeddings, AutoML, Cypher graph queries, and LLM functions. This manual is the authoritative reference for AIDB SQL features (v1.6.0 through v1.6.5.1). Use ONLY features documented here.

---

Quick reference — AIDB extensions at a glance

The features below are AIDB-specific extensions that distinguish AIDB SQL from generic SQL. Recipes for AI / analytics intents should prefer these over hand-rolled equivalents.

Feature	Form
Vector column type	col_name VECTOR(N) where N is the embedding dimension
Text embedding	EMBED(text_expr) -> VECTOR
Cosine similarity	COSINE_SIMILARITY(vec_a, vec_b) -> float in [-1, 1]
Euclidean distance	EUCLIDEAN_DISTANCE(vec_a, vec_b) -> float >= 0
Train AutoML model	CREATE EXPERIMENT name AS SELECT ... WITH (task_type=..., ...)
Queue / retrain a model	SELECT AUTOML.TRAIN('model', '<data query>', 'target') (v2.0-ce)
Retrain only when inputs drift	... AUTOML.TRAIN(...) WITH (retrain_on_drift = 2.0)
Predict with AutoML model	SELECT AUTOML.PREDICT('model', col1, col2, ...) AS risk FROM t
List/describe models	SHOW MODELS, DESCRIBE MODEL name
LLM text generation	GENERATE(prompt_text [, options_json]) -> TEXT
NLI pair classification	NLI_CLASSIFY(model, premise, hypothesis) -> JSON (v2.1.2-ce+)
NLI statement verification	NLI_VERIFY(model, statement, anchors_json [, options]) -> JSON (v2.1.2-ce+)
NLI evidence aggregate	NLI_ANCHORS(text) in GROUP BY -> JSON array (v2.1.2-ce+)
JSON object literal builder	json_object(key, val, ...) -> JSON (v1.8.7+)
Native-inference model pull	PULL_MODEL('qwen2.5-coder:7b') -> TEXT (v1.8.0+)
Native-inference model list	LIST_MODELS() -> table (v1.8.0+)
Native-inference model drop	DELETE_MODEL('model_name') -> TEXT (v1.8.0+)
Cypher graph pattern	MATCH (n:Label) RETURN n (per-tenant graph)
Cypher graph write	CREATE (n:Label {prop: value}), MERGE, DETACH DELETE n
Persona as a database object	CREATE PERSONA name WITH (system_prompt = '...') (v1.14.2+)
Persona inspection	SHOW PERSONAS [LIKE '...'], DESCRIBE PERSONA name (v1.14.2+)
Fire a durable agent async	WAKE AGENT name (v1.14.2+)
Agent-to-agent chaining	... ON INSERT INTO t ALLOW AGENT ORIGIN (v1.14.2+)
Persistent agent memory	CREATE MEMORY name IDENTITY user_id (v1.14.3+)
Write a memory	REMEMBER mem FOR user_id = 123 '<text>' (v1.14.3+)
Read assembled context	RECALL mem FOR user_id = 123 ABOUT '<query>' (v1.14.3+)
Authoritative present value	CURRENT mem FOR user_id = 123 ATTRIBUTE attr (v1.14.3+)
Explain a remembered value	TRACE mem FOR user_id = 123 ATTRIBUTE attr (v1.14.3+)
Authorized removal	FORGET mem FOR user_id = 123 ABOUT '<scope>' (v1.14.3+)
Query Parquet on object storage	CREATE EXTERNAL TABLE t STORED AS PARQUET LOCATION 's3://...' (v1.16.0-ce+)
Write a query result to Parquet	COPY (SELECT ...) TO LAKE 'path/' WITH (CONNECTION='destination')
Re-read a lake schema	REFRESH EXTERNAL TABLE t (v1.16.0-ce+)
List lake tables	SHOW EXTERNAL TABLES (v1.16.0-ce+)
Durable transaction	BEGIN; ... COMMIT; over MySQL, or POST /v1/transactions (v2.0-ce)
Partial rollback	SAVEPOINT n; ... ROLLBACK TO SAVEPOINT n (v2.0-ce)
Enforced referential integrity	col INT REFERENCES parent(id) ON DELETE CASCADE (v2.0-ce)

---

Data Definition Language (DDL)

CREATE DATABASE / DROP DATABASE / USE / SHOW DATABASES

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

CREATE TABLE

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

Data Types (full list):

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) where N is the embedding dimension (must match the configured embedding model — default MiniLM is 384). Multimedia: AUDIO, VIDEO, IMAGE, PDF.

Casts. CAST(expr AS type) and the ::type shorthand both work. SIGNED, UNSIGNED, SIGNED INTEGER and UNSIGNED INTEGER are accepted as cast targets 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.

Casting to DECIMAL(p,s) returns an exact decimal, applies the requested scale with half-away-from-zero rounding, and rejects values that exceed the precision after rounding. Precision must be 1–38 and scale must be 0–precision. DECIMAL(p) uses scale 0; bare DECIMAL retains the default (19,4).

SELECT CAST(12.5 AS DECIMAL(10,2));     -- 12.50, exact
SELECT CAST('12.5' AS DECIMAL(10,2));   -- 12.50, exact
SELECT CAST(12 AS DECIMAL(10,2));       -- 12.00, exact
SELECT CAST(-12.345 AS DECIMAL(10,2)); -- -12.35
SELECT CAST(999.995 AS DECIMAL(5,2));  -- error: 1000.00 exceeds precision 5

Numeric text and integers are converted without passing through DOUBLE. NULL remains NULL; malformed text, nonfinite floats and overflow return errors. Columns also retain their declared scale on INSERT and UPDATE:

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, padded to the declared scale

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

Table constraints: PRIMARY KEY (a, b) — including the composite form — is honoured as of v1.15.0-ce, and creates both the primary key and its index. Out-of-line 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, so if you declared constraints that way before upgrading, check for duplicate and orphan rows you may already have — v2.0 will reject new violations but does not retroactively clean existing data.

Before v1.15.0-ce

A table-level PRIMARY KEY (a, b) was accepted and then silently discarded: SHOW INDEXES returned nothing and the table had no primary key at all. If you have tables declared that way, re-running their CREATE TABLE ... IF NOT EXISTS on this version registers and backfills the missing index.

Constraint enforcement. PRIMARY KEY, UNIQUE and CHECK (expr) are enforced on write — a violating statement errors and no row is persisted. Since v1.14.3:

• CHECK is enforced on UPDATE as well as 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:

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)

(before v1.14.3 a table-level CHECK parsed, was accepted, and then enforced nothing — only inline column CHECKs did anything); * a CHECK survives a restart; it used to be dropped when the schema was reloaded from the on-disk cache.

NULL follows SQL three-valued logic: a CHECK rejects a row only when the predicate evaluates to FALSE. CHECK (grade IN ('A','B')) therefore accepts a NULL grade, because the predicate is UNKNOWN. Add NOT NULL if you want to forbid that.

Out-of-line PRIMARY KEY, including the composite form, is honoured since v1.15.0-ce.

CREATE TABLE zip_fips_crosswalk (
    zip_code VARCHAR NOT NULL,
    tract    VARCHAR NOT NULL,
    v        VARCHAR,
    PRIMARY KEY (zip_code, tract)
);

SHOW INDEXES FROM zip_fips_crosswalk;
-- PRIMARY                (zip_code, tract)
-- zip_fips_crosswalk_pkey (zip_code, tract)

INSERT INTO zip_fips_crosswalk VALUES ('10001', 'A', 'x');
INSERT INTO zip_fips_crosswalk VALUES ('10001', 'A', 'y');
-- ERROR: Duplicate key: row violates a UNIQUE or PRIMARY KEY constraint

Before v1.15.0-ce that CREATE TABLE returned ok and then produced no primary key, no index and no error — SHOW INDEXES came back empty. A single-column out-of-line PRIMARY KEY (a) behaves exactly like the inline a ... PRIMARY KEY spelling.

Storage note: a table with a single-column primary key stores each row under its primary-key value, so a lookup by that key is a direct fetch. A table with a composite primary key keeps the document store's own row id, and the composite key is enforced through the primary key's own index instead.

Out-of-line UNIQUE (...) and FOREIGN KEY (...) are enforced as of v2.0-ce (see Referential integrity below); before v2.0 they were parsed and ignored.

Worked example — 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)
);

Columnar tables — ENGINE = SYNAPCORES_COLUMNAR (v1.15.0-ce+)

Stores the table as Parquet parts with RocksDB metadata instead of row storage. Built for analytical scans over large tables: a query reads only the columns it names, and row-group statistics skip parts that cannot match.

CREATE TABLE events (
    id      INTEGER PRIMARY KEY,
    sensor  TEXT,
    reading DOUBLE,
    ts      TIMESTAMP
) ENGINE = SYNAPCORES_COLUMNAR;

Both ENGINE = SYNAPCORES_COLUMNAR and WITH (ENGINE='SYNAPCORES_COLUMNAR') preserve declared types and constraints. Unknown engine options are rejected.

Columnar PRIMARY KEY and UNIQUE declarations remain metadata hints by default: duplicate rows are accepted. Opt in to exact write-time enforcement with ENFORCED:

CREATE TABLE keyed_events (
    id BIGINT PRIMARY KEY ENFORCED,
    source TEXT UNIQUE,
    reading DOUBLE
) WITH (ENGINE='SYNAPCORES_COLUMNAR');

ENFORCED selects enforcement for all declared primary and unique keys on the table, including composite keys. Inserts reject duplicates both within the batch and against durable keys; updates and deletes publish row and key changes together. A primary key rejects NULL; unique keys allow NULL. Default hint-only tables skip the key-maintenance path. The additional work for enforced tables must be measured separately from unconstrained bulk ingestion. Columnar foreign keys and secondary indexes are unsupported. NOT ENFORCED syntax is unsupported; omit ENFORCED for hints.

Columnar tables support the full INSERT / UPDATE / DELETE surface. Writes are copy-on-write — an affected part is rewritten rather than edited in place, which is why an UPDATE or DELETE touching a large part costs more than the same statement against a row table. Prefer batched writes and analytical reads.

Before v1.15.0-ce

ENGINE = SYNAPCORES_COLUMNAR parsed and was then normalised away, so the table was silently created as a row table. Tables created that way are row tables and stay row tables; recreate them to move to columnar storage.

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

Foreign keys are persisted and enforced. Before v2.0 the syntax was accepted and nothing checked it, so orphan rows could be inserted freely.

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

-- table level, with a referential action
CREATE TABLE order_items (
    id       INT PRIMARY KEY,
    order_id INT,
    qty      INT,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
);

What is enforced. 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 too, unless you declared an action that says otherwise.

Referential actions. ON DELETE / ON UPDATE accept CASCADE, SET NULL, SET DEFAULT, RESTRICT and NO ACTION, all applied within the same statement. With no action declared the behaviour is restrictive.

Requirements and limits:

• The referenced column must be a declared PRIMARY KEY or UNIQUE key, of a matching type.
• Composite keys use MATCH SIMPLE: a NULL in any part exempts the whole reference from checking.
• An UPDATE may not move a referenced key onto another row's pre-statement key — that ambiguous swap is rejected rather than cascading a child twice.
• Declare foreign keys in CREATE TABLE, or use validating ALTER TABLE ... ADD CONSTRAINT ... FOREIGN KEY .... ALTER checks existing rows before attaching the constraint. New cross-database declarations remain unsupported; a supported table move preserves and enforces its existing references in their correct databases.
• Requires the native transaction backend, which is the default. Under AIDB_SQL_TRANSACTION_BACKEND=legacy, foreign-key creation is not available.
• FK validation scans the connected tables. It is a correctness guarantee, not a throughput-optimised path — bulk loads into deeply referenced tables will feel it.

Upgrading: v2.0 rejects new violations but does not retroactively clean existing data. Audit for orphans before relying on the constraint:

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

---

ALTER TABLE / DROP TABLE / CREATE INDEX / 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 old_name RENAME TO new_name;
RENAME TABLE old_name TO new_name;
ALTER TABLE t ALTER COLUMN c TYPE new_type;

DROP TABLE [IF EXISTS] t [CASCADE];

-- Both clause orders are accepted (v1.15.0-ce): the MySQL trailing order and
-- the PostgreSQL leading order.
CREATE [UNIQUE] INDEX [IF NOT EXISTS] idx_name ON t (col [ASC|DESC], ...) [USING BTREE|HASH];
CREATE [UNIQUE] INDEX [IF NOT EXISTS] idx_name ON t USING BTREE|HASH (col [ASC|DESC], ...);
DROP   INDEX [IF EXISTS] idx_name;

Table rename and cross-database move. Both spellings move one ordinary row table within the same tenant, including RENAME TABLE source.orders TO archive.orders. The destination must not exist. Rows, constraints, index identities and parsed static references in views, triggers, procedures and durable SQL agents are preserved. Foreign-key targets retain their database identity, including cross-database cascades. Opaque, dynamic or ambiguous stored references are refused with the object name; they are never rewritten by substring replacement.

The operation requires the tenant's dedicated SQL row store and no active transaction. Auto-increment, non-row or partitioned tables, system names beginning with _, shared storage and cross-tenant moves remain unsupported. Row keys are rewritten, so work and temporary write space grow with table size. An exclusive catalog gate blocks SQL during publication. A durable intent resumes interrupted work before subsequent SQL is admitted; this is recoverable publication across stores. Reprepare existing statements afterward. Index names remain unchanged; a new table with the old name receives separate keys.

Index types. BTREE (default), HASH, FULLTEXT and HNSW are supported. Both leading and trailing USING clause orders are accepted. IVFFLAT and a scalar index on VECTOR(N) are rejected. Search indexes require ordinary row tables.

CREATE INDEX docs_words ON docs(body) USING FULLTEXT
WITH (stopwords='a,the,and', stemmer='english');
CREATE INDEX product_vectors ON products(description_vec) USING HNSW
WITH (metric='cosine', m=16, ef_construction=200, ef_search=200);

FULLTEXT uses Unicode alphanumeric words, lowercase normalization, a configurable comma-separated stop list, and optional English light suffix stemming (none by default). It ranks by BM25 (k1=1.2, b=0.75) with deterministic row-id ties. Supported queries are natural-language terms; boolean mode, phrases, wildcard and query expansion fail explicitly. MATCH(body) AGAINST('quick fox') uses the inverted index for matching columns; without one it retains the documented substring LIKE scan fallback. Token matching differs from substring matching and stop words can yield no hits. EXPLAIN reports the selected path. Use a positive MATCH predicate to exclude zero-score rows when ranking.

Search-index definitions and real postings/native graph snapshots persist. Cold catalog replay restores incomplete definitions without loading, validating or importing snapshot payloads. The first indexed use rebuilds from committed authoritative rows before marking the index ready; startup does not scan every table or provide snapshot-only serving. Build failure never publishes a partial index. INSERT, UPDATE, DELETE, REINDEX and DROP INDEX maintain or invalidate the scoped index so stale data is never authoritative.

Validating constraints. Row tables support inline and table-level primary, unique and foreign keys. ALTER TABLE t ADD CONSTRAINT name UNIQUE(a,b) validates existing rows before publication; dirty data fails without attaching the constraint. Foreign keys require native optimistic row transactions and matching explicit parent primary or unique keys. DROP CONSTRAINT [IF EXISTS] name removes the named definition and backing enforcement; referenced parent keys cannot be dropped. Deferred constraints and ALTER ADD CHECK / ALTER ADD PRIMARY KEY are unsupported and rejected.

CREATE INDEX builds the index from the rows already in the table. It therefore takes time proportional to table size and reports how much it did:

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

Rows holding NULL in an indexed column are skipped and counted separately — they can never satisfy an equality lookup. If the build fails, the statement fails and the index is not created; a half-populated index would be worse than none, because a lookup that misses it would silently return no row.

CREATE UNIQUE INDEX over data that already contains a duplicate fails, naming the column and the repeated value.

Before v1.15.0-ce: two ways an index ended up empty

CREATE INDEX did not read the table — it relied entirely on INSERT-time maintenance. So an index created over a table that already held rows was left empty, and a lookup on it silently fell back to a full table scan (correct answers, no acceleration, no error).

Separately, INSERT-time index maintenance was gated on the table's constraint list rather than its index catalog, so on a table with no PRIMARY KEY and no UNIQUE column even rows inserted after the index existed never reached it.

An index on a table that had a PRIMARY KEY or UNIQUE column and was created before the rows were loaded worked correctly on earlier versions. Measured on v1.14.5-ce, 20k rows, same box:

scenario	v1.14.5-ce
PK table, index created before the load	1.0 ms
PK table, index created after the load	66.6 ms
no-PK table, index created before the load	66.3 ms
unindexed column (control)	72.1 ms

Indexes created on an older build are registered but empty. Upgrading repopulates them at startup -- measured: a lookup that cost 66.6 ms on v1.14.5-ce serves in 0.9 ms after opening the same data directory with v1.15.0-ce, with no CREATE INDEX re-run.

Indexes belong to the database that created them. An index created in database analytics is used only by queries running against analytics.

Supported index types: BTREE (default) and HASH.

CREATE INDEX builds the index from the rows already in the table (v1.15.0-ce). The statement scans the table, populates the index and reports how many rows it indexed:

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

It therefore takes time proportional to the table size — a large table is not instantaneous, and that is the point. Before v1.15.0-ce the statement returned in milliseconds no matter how big the table was and left the index empty: the planner then fell back to a full table scan on every lookup while SHOW INDEXES listed the index and the definition survived restarts. Nothing reported an error.

Rows whose indexed column is NULL are counted separately as skipped — such a row can never satisfy col = <value>, so leaving it out changes no answer. If the build fails, the statement fails and the index is not created: a partially built index would make lookups return fewer rows than the table actually holds.

CREATE UNIQUE INDEX over data that already contains duplicates fails (v1.15.0-ce), naming the column and the repeated value:

CREATE UNIQUE INDEX idx_email ON users (email);
-- ERROR: cannot create UNIQUE index 'idx_email' on table 'users': the table
--        already contains a DUPLICATE VALUE in the indexed column (email).
--        The row with email = 'dupe@x.com' repeats a value an earlier row
--        already has. De-duplicate the table first, or create a non-unique
--        index. The index was NOT created.

The UNIQUE flag is also now recorded on every code path; one lowering discarded it and quietly produced a plain index, so the declared guarantee was present on one route and absent on the other.

CREATE UNIQUE INDEX also rejects duplicates written AFTER it exists (v1.15.0-ce). A UNIQUE index is a constraint, not a hint: an INSERT whose value already appears in the index is rejected on every path.

CREATE UNIQUE INDEX idx_email ON users (email);
INSERT INTO users (id, email) VALUES (2, 'a@x.com');   -- value already present
-- ERROR: Duplicate key: row violates a UNIQUE or PRIMARY KEY constraint on
--        table 'users'

Before v1.15.0-ce the write-time check consulted only the constraints declared in CREATE TABLE, so an index created by CREATE UNIQUE INDEX — which the table schema never learns about — enforced nothing at all. The duplicate was written, the index layer's own rejection was logged and discarded, and the only symptom was duplicated data.

Indexes are rebuilt on first use after a restart (v1.15.0-ce). Index definitions are durable; the index data structures live in memory and are rebuilt from the table the first time a statement touches it, not at startup — so startup time does not grow with the size of the database, and the first statement against a large table pays a one-off scan (about half a second per 10,000 rows per table in a debug build, less in a release build). Until an index is rebuilt it is never treated as authoritative: lookups fall back to a table scan and uniqueness is decided by a direct check, so results are correct throughout.

This is the fix for a defect present through v1.14.5: after a restart every index was registered but empty, and the emptiness was read as an answer. A duplicate INSERT was accepted — and because a single-column PRIMARY KEY is the row's storage id, it overwrote the existing row with no error. The first write after a restart also made the empty index non-empty, after which an indexed lookup returned only the rows written since the restart while COUNT(*) still counted them all.

If an index cannot be rebuilt — most often a UNIQUE index over rows that already contain duplicates — the engine logs the table, the index, the column and the offending value, leaves the index empty and unused, and keeps answering from table scans. REINDEX <index> is the recovery once the data is fixed.

An index belongs to the database that created it (v1.15.0-ce). Each database keeps its own index catalog. CREATE INDEX issued inside USE demo — or with "database": "demo" on the REST call — creates an index that only demo's queries use and that only demo's rows go into.

Through v1.14.5 the planner looked indexes up under the bare table name while every writer filed them under the database, so outside main no index was ever chosen: CREATE INDEX reported success, SHOW INDEXES listed the index, and every lookup still paid for a full table scan. Where main happened to hold a table of the same name, the planner picked main's index for the other database's query. Both are fixed: an index is matched to its own database, and a plan naming an index from a different database is discarded in favour of a table scan rather than used. A 60,000-row lookup on an indexed non-key column in a non-main database went from 214 ms to 0.4 ms, matching main.

A composite index answers a leftmost-prefix lookup (v1.15.0-ce). With CREATE INDEX idx ON t (a, b), WHERE a = ? uses the index. Before v1.15.0-ce only the full key WHERE a = ? AND b = ? was served and any partial match returned zero rows — creating a composite index silently removed rows from query results. A predicate the index cannot serve (a non-leading column, or a partial key on a HASH index) falls back to a table scan and returns correct rows.

SQL HNSW similarity search. A single VECTOR(N) column can use the production HNSW graph. Metrics are cosine, euclidean, dot and manhattan; the query metric must match the index. Use a finite top-k limit with cosine descending or Euclidean distance ascending. A literal, bound vector parameter or request-constant EMBED probe is evaluated once. Residual vector prefilters, unsupported shapes and queries without a usable limit retain the scan path. Approximate results require a recall check against exact scan results for the chosen dataset and ef_search.

CREATE INDEX product_vectors ON products(description_vec) USING HNSW
WITH (metric='cosine', m=16, ef_construction=200, ef_search=200);
SELECT id, COSINE_SIMILARITY(description_vec, EMBED('audio gear')) AS score
FROM products ORDER BY score DESC LIMIT 10;
EXPLAIN SELECT id, COSINE_SIMILARITY(description_vec, EMBED('audio gear')) AS score
FROM products ORDER BY score DESC LIMIT 10;

The index admits at most 50 million non-NULL vectors in Community Edition. CREATE INDEX reports indexed and NULL-skipped counts. Vector dimension, non-finite values, unsupported options and mismatched metrics are validated. The separate vector collection REST API retains its own supported index types and behavior; SQL HNSW does not change that API's capabilities.

Immutable (append-only) tables and chain attestation

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

INSERT INTO audit_log (id, actor, action) VALUES (1, 'alice', 'LOGIN');

-- UPDATE and DELETE are refused on an immutable table.

-- -> is_valid BOOL, message TEXT, status TEXT,
--    records_verified INT, blocks_verified INT   (v1.12.0+; status/counts v1.14.2.1+)
VERIFY TABLE audit_log;
VERIFY RECORD 1 IN audit_log;    -- -> record_id, is_valid, message    (v1.12.0+)

CREATE IMMUTABLE TABLE writes every row into a hash-chained, append-only block store in addition to the table itself.

VERIFY TABLE <t> attests that chain: it 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 number of chained records against the rows actually stored. message reports the number of records/blocks verified, or the first break found. VERIFY TABLE on a table that is not immutable returns an error rather than a pass.

Read the counts from records_verified / blocks_verified rather than parsing message, and branch on status, not on is_valid alone:

status	is_valid	meaning
verified	true	the chain was walked and every record in it is attested (records_verified > 0)
empty	true	the chain holds zero records — the check is vacuous, nothing was attested. An empty chain is internally consistent, so is_valid stays true; it is not evidence of tampering, it usually means nothing was ever written
failed	false	at least one integrity problem was found; message names the first one

A pipeline that claims "N/N runs verified" must therefore require status = 'verified' and records_verified > 0.

VERIFY RECORD <id> IN <t> attests a single record: it recomputes that record's checksum and proves its membership in its block's merkle tree. <id> is the row's SQL id (matched against the chained payload) or the chain's internal record id.

Immutable tables require explicit columns. They can be populated at creation:

CREATE IMMUTABLE TABLE audit_snapshot (id BIGINT PRIMARY KEY, actor TEXT)
AS SELECT id, actor FROM source_events ORDER BY id;
VERIFY TABLE audit_snapshot;

CTAS uses the normal insert and chain-attestation path. It preserves a top-level ORDER BY; otherwise it sorts canonical typed row bytes for deterministic chain order. The SELECT is materialized under the query memory budget. A durable rollback intent removes the schema, rows, chain and wrapped key if creation fails or is interrupted. Recovery completes before SQL admission; dedicated durable row storage is required.

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. Requirements and behavior:

• Requires the AIDB_IMMUTABLE_MASTER_KEY environment variable (a hex-encoded key). If it is missing or invalid, the CREATE is rejected — never downgraded to a plaintext table (fail-closed).
• AES256GCM is the only supported algorithm. An unsupported or missing algorithm (e.g. bare WITH (ENCRYPTION) or ENCRYPTION='AES128') is rejected.
• ENCRYPTION_KEY='audit' selects AIDB_IMMUTABLE_MASTER_KEY_AUDIT. Names use ASCII letters, digits and underscores and are normalized to uppercase for lookup. The selected alias is persisted; the key material is not. Missing or invalid named keys and wrong keys fail closed without falling back to the default master key. Without an alias, existing AIDB_IMMUTABLE_MASTER_KEY behavior is retained. Supply at least 32 bytes encoded as hexadecimal using the same key validation.
• Encrypted immutable tables are supported only in the default database; creating one under another database is rejected.

---

Columnar tables — ENGINE = SYNAPCORES_COLUMNAR (v1.15.0-ce+)

CREATE TABLE events (
    id      INTEGER PRIMARY KEY,
    sensor  TEXT,
    reading DOUBLE,
    ts      TIMESTAMP
) ENGINE = SYNAPCORES_COLUMNAR;

Stores the table as Parquet parts with RocksDB metadata instead of row storage. A query reads only the columns it names, and row-group statistics skip parts that cannot match the predicate.

Both ENGINE = SYNAPCORES_COLUMNAR and WITH (ENGINE='SYNAPCORES_COLUMNAR') preserve column types and constraints. Unsupported options are rejected.

Full INSERT / UPDATE / DELETE are supported, but writes are copy-on-write: an affected part is rewritten rather than edited in place, so a statement touching a large part costs more than the equivalent on a row table. Batch the writes; read analytically. Columnar tables have no secondary indexes — scanning is the access path, which is the point of the format.

Primary and unique keys are metadata hints by default. Add ENFORCED immediately after a primary or unique declaration to enforce all declared keys on the table, including composite keys. Persistent exact keys are maintained with row publications; updates reject duplicate replacements atomically, deletes free keys, and restart retains enforcement. Hint-only tables skip this key path. See the columnar example above.

Before v1.15.0-ce the ENGINE clause parsed and was then normalised away, so the statement returned ok and silently created an ordinary row table. A table created on an older build is a row table and stays one; recreate it to move to columnar storage.

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 — Parquet on object storage (v1.16.0-ce+)

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

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 in object storage. Registration reads footer metadata across the dataset to establish the schema; queries reuse revision-aware metadata caches.

What makes it worth having is that the planner treats it as an ordinary relation. A lake table joins, filters and aggregates against RocksDB tables in one statement, so a year of history in cheap object storage answers a question alongside today's live rows — with no ETL step back into the database and no second query engine whose results you join by hand in application code.

External tables are read-only. INSERT, UPDATE and DELETE are refused:

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.

Worked example — register a lake table and join it to a live one:

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.

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/pings/snapshot_dt=2026-09-18/part-0000.parquet
s3://acme-lake/raw/_manifests/pings/dt=2026-09-18.json
s3://acme-lake/raw/_state/pings.json

Managed exports use dt=YYYY-MM-DD or snapshot_dt=YYYY-MM-DD. Foreign Parquet also supports arbitrary Hive directories such as metro=nyc/year=2026; declare those path columns and their types in PARTITIONED BY.

A partition column is never repeated in the column list. Declaring it in both is a parse error, because the relation would then have two columns of the same name and no predicate could say which one it meant.

Pruning partitions from their directory names, before any object is opened, is what makes WHERE dt BETWEEN … on a year-wide table cheap.

Managed exports use verified-manifest visibility

The export job 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 one whose manifest is not marked verified, does not exist as far as a reader is concerned.

A half-written export is therefore invisible, not partially readable. That is the behaviour you 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 you expect, the partition is usually still exporting or its export failed — check the job's run history before suspecting the query.

Plain foreign datasets, including COPY output outside managed ancestry, are read by listing Parquet files and do not require managed manifests. A managed marker/state/manifest at an ancestor or a managed-writer footer deliberately selects the managed path instead; use separate prefixes for the two layouts.

Schema: inferred, or declared and checked

Omit the column list and the schema is read from the Parquet footer at CREATE time. Declare one and it is checked against the data, and a disagreement fails the 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 here rather than at query time is deliberate — a table that registers cleanly and breaks on first use usually breaks in front of a BI tool that cannot show the error. Names match case-insensitively and types by meaning rather than spelling; column order is not compared.

Foreign schemas union top-level fields by name, reconcile REQUIRED/OPTIONAL nullability, and supply typed NULLs for missing nullable fields. Nullable declared supersets and empty locations with declarations are supported. Type conflicts remain errors. Explicit contracts survive REFRESH; inferred registrations use REFRESH to expose newly added logical columns. Old registrations without schema provenance remain conservative until reviewed and re-registered.

WITH (CONNECTION = '…')

Names the lake destination supplying credentials and path confinement.

The grammar marks it optional; in practice it is required. With no CONNECTION the read path has no destination, and therefore no credentials, so CREATE fails:

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

Always write it. It is the only supported WITH option, and an unknown option is rejected rather than ignored — silently dropping CONNECTION would build a table reading through credentials the operator did not choose.

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:

external table 'pings' reads raw/pings/ through destination 'lake_s3', which is
outside its read allowlist (raw/approved/). Add the prefix to the destination
before querying it.

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.

STORED AS PARQUET is the only format (STORED AS ORC errors naming PARQUET), LOCATION must be a non-empty quoted URI, and PARTITIONED BY / WITH may appear in either order.

REFRESH EXTERNAL TABLE

An external table stores the column list it last resolved, so DESCRIBE and BI-tool introspection answer without a network round trip. That stored schema is what goes stale, and REFRESH is what updates it — run it after an export job 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; set it to 0 to discover on every scan), so yesterday's export becomes visible on its own inside that window. REFRESH re-resolves the schema, not the partition cache — if a day is missing, wait out the TTL or check that the export finished, rather than refreshing.

Refreshing moves no data and still shows only verified partitions: an incomplete export stays invisible afterwards, because it is still incomplete.

DROP EXTERNAL TABLE never deletes your data

DROP EXTERNAL TABLE pings;
-- External table 'pings' unregistered. No objects were deleted from storage.

The registration goes; not one object under LOCATION is touched. Re-running CREATE EXTERNAL TABLE over the same prefix brings the table straight back. This is the opposite of DROP TABLE on an ordinary table, which does destroy the rows — worth being certain which one you are typing.

Deleting lake objects is a lifecycle-policy job for the object store, and is 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

One row per registered table: table_name, format, location, connection, partition_columns (rendered as dt DATE), columns (the count of resolved columns — use DESCRIBE for their names) and created_at.

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

This is how you answer "what is this database reading out of object storage, and with whose credentials" — an external table's reach is the reach of its connection, so it is worth reading before granting anyone access. Ordinary tables are not listed here; SHOW TABLES covers both.

Other refusals worth knowing

• The statement fails if the lake provider is not installed in the build ("external tables are not available in this build"). A missing provider is an error, never an empty result — an empty answer that should have been an error is the worst thing a database can return.
• A name already used by a regular table is refused even under IF NOT EXISTS: that flag means "leave the external table I already registered alone", not "accept that this name belongs to something else".

---

Data Manipulation Language (DML)

INSERT INTO t [(c1, c2, ...)] VALUES (v1, v2, ...), ...;
INSERT INTO t [(c1, c2, ...)] SELECT ...;

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

DELETE FROM t [WHERE condition];

rows_affected is the number of rows actually written. For INSERT ... SELECT whose SELECT matches nothing, rows_affected is 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 instead of the write; the count is now consistent across every entry point.) 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;

---

MySQL compatibility (v1.10.0-ce)

The MySQL dialect that applications actually depend on runs unchanged, and UNIQUE / PRIMARY KEY constraints are enforced (index-backed), not silently accepted.

Upserts (F1) — ON DUPLICATE KEY UPDATE, INSERT IGNORE, and REPLACE:

INSERT INTO inventory (sku, qty) VALUES ('A1', 5)
ON DUPLICATE KEY UPDATE qty = qty + 5;

INSERT IGNORE INTO inventory (sku, qty) VALUES ('A1', 1);   -- no-op on conflict
REPLACE INTO inventory (sku, qty) VALUES ('A1', 9);         -- delete-then-insert

JSON (F2) — path operators -> (JSON) / ->> (text) plus JSON_EXTRACT, JSON_SET, JSON_ARRAY, JSON_OBJECT, JSON_CONTAINS:

SELECT data->>'$.email'                     AS email,
       JSON_EXTRACT(data, '$.plan')         AS plan
FROM   users
WHERE  JSON_CONTAINS(tags, '"vip"');

ENUM (F3), auto-timestamp (F4), full-text (F5), type aliases (F6):

CREATE TABLE tickets (
  id         INT PRIMARY KEY,
  status     ENUM('open','pending','closed'),          -- enforced as CHECK (status IN (...))
  note       VARCHAR(255),                              -- MySQL type aliases accepted
  updated_at TIMESTAMP ON UPDATE CURRENT_TIMESTAMP      -- refreshes on every UPDATE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE docs (id INT, body TEXT, FULLTEXT(body));
SELECT id FROM docs WHERE MATCH(body) AGAINST('quick fox');

Also mapped to the native format: DATETIME, LONGBLOB, DECIMAL, DOUBLE, BIGINT, VARCHAR(n), utf8mb4 charset clauses, ENGINE=InnoDB.

Constraint enforcement (F7) — a duplicate on a UNIQUE/PRIMARY KEY column is rejected; a DELETE frees the key so a re-insert succeeds (MySQL semantics). Enforcement is index-backed (O(log n)), so it does not slow inserts at scale.

mysqldump import (S1) — the CLI can ingest a mysqldump file directly; oversized INSERTs are chunked and MySQL escape / DELIMITER / LOCK / SET syntax is translated or skipped.

MySQL wire-protocol front-end (S3) — existing MySQL clients (DBeaver, Metabase, Connector/J, mysql CLI, Node/PHP drivers) can connect 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 is 3307 (NOT MySQL's 3306) so it can run 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 ([server] tls_cert_path / tls_key_path) and refuses
                            # to start if require_tls is true but no server cert is configured

Authenticate with your SynapCores username and API key (used as the MySQL password). When require_tls = false (default) TLS is still offered whenever a server cert exists, so clients may opt into encryption; require_tls = true makes it mandatory.

---

Query Language

SELECT

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

ORDER BY may reference projection aliases directly:

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

(If the alias is misspelled, the query returns a clear unknown column error rather than silently returning empty — fixed in v1.6.5.1.)

Qualified wildcard — t.* (v1.15.0+)

SELECT t.* selects every column of one relation. The qualifier may be a table alias, a bare table name, or schema-qualified:

SELECT emp.* FROM emp;                       -- identical to SELECT * FROM emp
SELECT e.*   FROM emp e;                     -- by alias
SELECT app.emp.* FROM app.emp;               -- schema-qualified

-- what it exists for: one relation's columns out of a join
SELECT e.*
  FROM emp e
  JOIN dept d ON e.dept_id = d.id;

-- mixes with anything else
SELECT e.*, d.dname, 1 AS lit
  FROM emp e
  JOIN dept d ON e.dept_id = d.id;

Notes:

• In a join, e.* returns exactly the columns of e that SELECT * would return for that join, with the same names. A join qualifies its right-hand columns (d.dname), so SELECT d.* returns d.id, d.dname while SELECT * FROM dept returns id, dname.
• A qualifier that names no table or alias in the FROM clause is an error that names it (Unknown qualifier 'x' in 'x.*'), never a silent expansion. In a self-join, the shared table name is ambiguous — use the aliases (SELECT a.* FROM t a JOIN t b ...).
• Not supported with aggregates / GROUP BY (list the columns instead), and COUNT(t.*) is rejected — use COUNT(*) or COUNT(t.<column>).

Qualified wildcards — SELECT t.* — are supported as of v1.15.0-ce, and are the way to take one relation's columns out of a join:

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

The qualifier may be an alias, a table name, or a database-qualified name (analytics.orders.*). Where a relation has an alias, either spelling resolves to it. t.* returns exactly the subset of SELECT * belonging to t, in the same order.

An unknown qualifier is an error naming what is in scope, and a qualifier matching two relations (a self-join) is an ambiguity error asking you to use the alias — neither silently guesses. COUNT(t.*) is rejected: it is not the same aggregate as COUNT(*) once an outer join is involved, so it is refused rather than answered with a different number.

Earlier versions rejected t.* outright with Complex projections not supported.

Joins, CTEs, subqueries

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

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;

Transaction Control

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. An acknowledged commit survives a hard kill; 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

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:

• MySQL wire connection — BEGIN, COMMIT, ROLLBACK, SAVEPOINT, ROLLBACK TO SAVEPOINT and RELEASE SAVEPOINT work as your client expects. Disconnecting rolls back an open transaction. SET autocommit = 0 starts a transaction on the next statement; setting it back to 1 commits the open one.
• REST — POST /v1/transactions returns an id; pass it as transaction_id on each statement (or use POST /v1/transactions/{id}/execute), 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": "..."} — the same route as a full rollback, which rolls everything back when sent with no body.

Isolation levels. READ COMMITTED (the SQL and REST default) and REPEATABLE READ (the MySQL default) are supported. READ UNCOMMITTED and SERIALIZABLE are rejected, not quietly downgraded. Both supported levels see your own pending writes, including through scans and ranges.

Conflicts are retryable, and conservative. Concurrent writers can receive MySQL error 1213 / SQLSTATE 40001, or HTTP 409 with code: "transaction_conflict" and retryable: true. Retry the whole transaction from a fresh BEGIN — retrying only the COMMIT will not work. Validation tracks table epochs as well as row keys, so two writes to different rows of the same table can still conflict. Design for retries.

Ordinary DML also runs in an implicit transaction, so a statement that fails partway — including its cascades — rolls itself back. Inside an explicit transaction, a failed statement rolls back only itself; earlier successful statements stay.

What does not participate. Columnar tables, immutable tables, external/lake Parquet reads, AI and memory side effects, enabled triggers and agent event bindings are not transactional; they fail explicitly rather than appearing to enrol. DDL inside an open transaction and new cross-database foreign-key declarations are rejected. Validating ALTER ADD FOREIGN KEY runs outside an open transaction. Existing references preserved by a supported cross-database table move remain enforced.

Limits. 4,096 savepoint frames per transaction, including the internal frame each statement takes. Transaction timeouts run from 1 to 300 seconds and roll back on expiry.

Set AIDB_SQL_TRANSACTION_BACKEND=legacy to return to the pre-v2.0 non-durable path. Any other value fails startup rather than falling back silently. Centralised/consolidated storage deployments must set legacy until that routing lands.

---

Built-in Functions

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-05-17 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 & AI Functions

AIDB exposes vector and LLM operations as first-class SQL functions. They compose normally with WHERE, ORDER BY, joins, and CTEs.

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 (default 384 for MiniLM).
• The column you store the result in must use the matching dimension.

SELECT EMBED('wireless noise cancelling headphones');

UPDATE products SET description_vec = EMBED(description);

COSINE_SIMILARITY(vec_a, vec_b)

Returns a DOUBLE in [-1, 1]. 1 = identical direction, 0 = orthogonal, -1 = opposite.

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

EUCLIDEAN_DISTANCE(vec_a, vec_b)

Returns a DOUBLE >= 0. Smaller = more similar.

SELECT id, EUCLIDEAN_DISTANCE(description_vec, EMBED('hiking boots')) AS dist
  FROM products
 ORDER BY dist ASC
 LIMIT 5;

GENERATE(prompt [, options])

Calls the configured completion model and returns the generated text.

• Arguments:
• prompt (TEXT, required) — the prompt sent to the model.
• options (JSON object, optional, v1.8.7+) — sampling + output-shape knobs. Build with json_object(). Recognized keys:
  • max_tokens (INT, default 4096 in v1.8.7+, was 200 prior).
  • temperature (FLOAT)
  • top_p (FLOAT 0–1)
  • top_k (INT)
  • repeat_penalty (FLOAT)
  • seed (INT) — same seed + same prompt → reproducible output
  • system (TEXT) — system prompt override
  • grammar (TEXT) — GBNF grammar to constrain sampling
  • grammar_triggers (JSON array) — when set with grammar, the grammar activates lazily once a trigger string appears
  • response_format ("json") — engine applies a built-in lazy JSON grammar so output is a valid JSON value
• 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 customer 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,
    'top_p', 0.95,
    '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 (column default, expression, INSERT). Keys must be TEXT; values can be any scalar SQL type — they're serialized to their JSON form. Errors on odd-length argument lists or non-text keys.

• Returns: JSON.

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

-- Use as a JSON column default.
CREATE TABLE prefs (
  user_id INTEGER PRIMARY KEY,
  settings JSON DEFAULT json_object('theme', 'dark', 'notifications', true)
);

SEMANTIC_MATCH, MULTI_MODAL_SIMILARITY, CROSS_MODAL_SEARCH

Higher-level helpers used inside SEMANTIC JOIN and multi-modal queries. Prefer EMBED + COSINE_SIMILARITY for explicit similarity, and use SEMANTIC_MATCH only in SEMANTIC JOIN clauses.

Other built-in AI functions

CLASSIFY(text, categories), EXTRACT_ENTITIES(text), SENTIMENT(text), SUMMARIZE(text), TRANSLATE(text, target_lang).

TRANSCRIBE(path [, language]) — Enterprise only

SELECT TRANSCRIBE('/data/media/call.wav')        AS text;
SELECT TRANSCRIBE('/data/media/call.wav', 'en')  AS text;

Speech-to-text over an audio file on the server, returning TEXT.

Not available in Community Edition

Community builds ship no speech-to-text engine. TRANSCRIBE() there returns an error stating that the Enterprise transcription feature is absent or that no Whisper model is configured under [transcription]. It fails loudly and never returns a partial or empty transcript.

Before v1.15.0-ce the function was missing from the parser's function allowlist, so it answered Unknown function: transcribe on every edition.

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

Stores text in an agent-memory namespace. On first call for a namespace the engine auto-creates the backing table _memory_<namespace> (id TEXT PK, content TEXT NOT NULL, embedding VECTOR(384) NOT NULL, metadata TEXT, created_at TIMESTAMP, accessed_at TIMESTAMP). The content is embedded via the configured embedding model (default all-minilm:latest, dim 384) and a row is INSERTed under a sortable id (mem_<base32 ts>_<6 alphanum>).

• namespace MUST match ^[A-Za-z_][A-Za-z0-9_]*$.
• metadata is optional and stored verbatim — convention: JSON-encoded with fields like importance, kind, source.
• options is an optional JSON object (build one with json_object(...)). The only key today is dedup.
• Returns: TEXT — the generated memory id.
• Per-tenant scoped via the standard storage path.

This is an unconditional insert. Storing the same content twice creates two rows. Pass json_object('dedup', true) as the fourth argument to route the write through MEMORY_UPSERT's noop_if_equal policy, which returns the existing row's id instead of duplicating it.

-- Store a user preference
SELECT MEMORY_STORE('default', 'I prefer Python over Java') AS memory_id;

-- Store with importance metadata
SELECT MEMORY_STORE('default', 'Customer renewed annual plan',
                    '{"importance": 0.9, "kind": "fact"}') AS memory_id;

-- Store without creating a duplicate row (v1.8.9+)
SELECT MEMORY_STORE('default', 'I prefer Python over Java',
                    NULL, json_object('dedup', true)) AS memory_id;

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

Idempotent, policy-driven write. Where MEMORY_STORE always inserts, MEMORY_UPSERT first looks for the row this content should supersede, then applies a conflict-resolution policy.

• Returns: TEXT — the action taken, one of 'ADD', 'UPDATE', 'DELETE', 'NOOP'.
• Matching. With a key, the row whose metadata carries the same _key. Without one, the row whose embedding is within similarity_threshold (default 0.95) cosine of the new content — semantic keying, so an agent can revise a belief without having invented a key for it.
• Third argument. Either a bare TEXT natural key, or a JSON object built with json_object(...).

Option	Type	Default	Meaning
key	TEXT	—	Natural key. Absent ⇒ semantic keying.
policy	TEXT	replace	See below.
confidence	REAL [0,1]	1.0	Confidence of the incoming assertion.
similarity_threshold	REAL [0,1]	0.95	Cosine bar for semantic keying.
metadata	TEXT | JSON	—	Merged into the stored metadata object.
deleted	BOOLEAN	false	Retract: delete the matched row.
audit	BOOLEAN	true	Emit a row into _system_agent_memory_audit.

Policies

Policy	Behavior
replace	Overwrite verbatim. ADD when absent, UPDATE when present.
replace_higher_confidence	Write only if the new confidence ≥ the stored one; otherwise NOOP. A low-confidence guess never clobbers a high-confidence fact.
merge_max_confidence	Always write, keeping MAX(new, stored) confidence.
append_history	Never overwrite — insert a superseding row with _version = prev + 1. The belief timeline is retained.
noop_if_equal	NOOP when the content is byte-identical; otherwise behaves as replace.

Retraction. deleted: true, or confidence: 0, deletes the matched row and returns 'DELETE'. Retracting a belief that was never held is a NOOP, not an error.

Reserved metadata keys. Confidence, key and version live inside the metadata JSON object under _confidence, _key and _version — so namespaces created by v1.8.5 MEMORY_STORE keep working with no migration and no schema divergence.

Audit. Every non-NOOP call (and every confidence-declined NOOP) appends a row to _system_agent_memory_audit (audit_id, namespace, memory_id, natural_key, action, policy, old_value, new_value, caller, timestamp). Audit writes are best-effort: an audit failure never fails the write you asked for. Disable per-call with audit: false.

-- 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'

-- Semantic dedup — no key needed
SELECT MEMORY_UPSERT('default', 'I prefer Python over Java') AS action;
-- 'NOOP' if a near-identical memory already exists, else 'ADD'

-- Natural key as a bare string
SELECT MEMORY_UPSERT('conv_42', 'Prefers email over phone', 'contact_pref') AS action;

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

-- Keep the full belief timeline instead of overwriting
SELECT MEMORY_UPSERT('default', 'Now on the enterprise plan',
                     json_object('key', 'plan', 'policy', 'append_history')) AS action;
-- 'ADD' (with _version bumped)

-- Audit what the agent changed its mind about
SELECT action, old_value, new_value, timestamp
  FROM _system_agent_memory_audit
 WHERE namespace = 'default'
 ORDER BY timestamp DESC;

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

Semantically retrieves the most-similar stored memories for a free-text query. Used in a FROM clause. Embeds the query via the same provider as MEMORY_STORE, scans the namespace's backing table, computes cosine similarity, and returns the top top_k rows ordered by similarity DESC. Columns: (id TEXT, content TEXT, similarity REAL in [0,1], metadata TEXT, created_at TIMESTAMP). top_k defaults to 10, capped at 100. If _memory_<namespace> doesn't exist yet, returns an empty result set (not an error). Best-effort bumps accessed_at on the rows returned.

-- Retrieve top 5 similar memories
SELECT id, content, similarity
  FROM MEMORY_RECALL('default', 'what languages do I like', 5);

-- Join recalled memories with a downstream prompt
WITH r AS (
  SELECT content, similarity
    FROM MEMORY_RECALL('default', 'return policy', 3)
)
SELECT GENERATE('Given context: ' || STRING_AGG(content, ' | ') ||
                '. Answer: what is the return policy?') AS answer
  FROM r;

MEMORY_FORGET(namespace, id) — v1.8.5+

Hard-deletes the memory identified by id. Returns BOOLEAN — true if a row was deleted, false if the id didn't exist. Same namespace validation as MEMORY_STORE. If _memory_<namespace> doesn't exist yet, returns false (no error).

-- Forget a single memory by id
SELECT MEMORY_FORGET('default', 'mem_abc123') AS deleted;

-- Age-out cold memories
SELECT MEMORY_FORGET('default', id) AS deleted
  FROM (
    SELECT id FROM MEMORY_RECALL('default', 'irrelevant', 100)
     WHERE similarity < 0.2
  ) AS cold;

AGENT_RUN(persona, task [, options]) — v1.6.6.9+ (options: v1.8.9+)

Runs a complete in-database agent loop — the ReAct pattern: reason → call tool → observe → repeat — and returns the agent's final answer as TEXT. The agent sees the same per-tenant database as the calling session, with the tools execute_query, list_tables, describe_table and rag_search.

Requires a tool-capable LLM configured in [query.ai_service] (e.g. qwen2.5-coder:7b via the embedded local provider — the v1.8 default — or Ollama / OpenAI / Anthropic / Gemini).

• persona — the agent persona; 'aidb-assistant' is the built-in default.
• task — a natural-language instruction.
• options — optional JSON object, built with json_object(...):

Option	Type	Default	Meaning
model	TEXT	persona / config	Override the model for this call (v1.8.10+). Outranks the persona and config default; an uninstalled name 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 engine's default persona when persona does not exist, instead of erroring.

Out-of-range numbers are clamped, not rejected — failing a long agentic query on a knob is worse than capping it. Unknown option keys are an error, so typos surface immediately.

The persona must exist — v1.14.3 breaking change. AGENT_RUN('does-not-exist', …) now fails with AGENT_RUN: unknown persona '<name>'. Available: …, matching what CREATE AGENT has done since v1.14.2. Earlier builds logged a warning and silently ran the request 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 (CREATE PERSONA, below), name a built-in, or pass json_object('allow_unknown_persona', true) to opt back into the fallback deliberately.

allow_writes is rejected — the agent's database tools are always read-only from SQL. A per-query flag would let any authenticated caller escalate a read-only agent into a writing one. Write capability is declared once by an operator on a durable agent (CREATE AGENT, v1.9.0), never per query. allow_unknown_persona is different: it grants no capability, so SQL may set it.

Returns NULL only when no agent service is wired (i.e. the AI service is not configured); a run that fails (the ReAct loop errors, the model times out) returns a TEXT value prefixed AGENT_RUN error: so it survives COALESCE in a procedure body while remaining greppable. A bad argument — a non-string persona or task, an unknown option key, or (v1.14.3+) a persona that does not exist — fails the statement instead: it is a mistake in the SQL, and burying it in a result cell is exactly the silent degrade v1.14.3 removed.

-- Aggregation + reasoning
SELECT AGENT_RUN('aidb-assistant',
  'Execute SELECT category, SUM(price*stock) FROM products GROUP BY category,
   then tell me which category has the highest total') AS reply;

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

-- Composed in a CTE. 'returns-triage' is NOT a built-in: create it first
-- (CREATE PERSONA, below), or this statement 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;

---

TRANSCRIBE(path [, language]) — Enterprise only

SELECT TRANSCRIBE('/data/media/call.wav')       AS text;
SELECT TRANSCRIBE('/data/media/call.wav', 'en') AS text;

Speech-to-text over an audio file on the server, returning TEXT.

Community builds have no speech-to-text engine. The call returns an explicit error saying the Enterprise transcription feature is absent, or that no Whisper model is configured under [transcription]. It fails loudly and never returns an empty or partial transcript.

Before v1.15.0-ce the function was missing from the parser's function allowlist, so it answered Unknown function: transcribe on every edition — including builds that had the engine.

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 (v1.14.2+)

A persona is the reasoning profile — system prompt, optional pinned model, output shape — that AGENT_RUN(persona, task) and CREATE AGENT ... PERSONA '<name>' bind to. Since v1.14.2 a persona is a first-class database object with its own DDL instead of a config-file entry: a persona created at runtime is usable immediately and survives restart. Personas are install-wide (not tenant-scoped).

Eight personas are built in and seeded on first boot — default, sql_developer, data_scientist, rust_developer, devops_engineer, marketing_expert, ui_ux_designer, and aidb-assistant (the one the AI-chat UI and the AGENT_RUN examples above use). A built-in may be edited, but it stays built-in and cannot be dropped. SHOW PERSONAS lists exactly what this engine has.

Any other name — including every persona in an example you copy from a blog post or a recipe — must be created first. Both surfaces reject an unknown persona with unknown persona '<name>': CREATE AGENT since v1.14.2, and AGENT_RUN since v1.14.3. Each offers the same deliberate escape hatch — WITH (allow_unknown_persona = TRUE) on CREATE AGENT, json_object('allow_unknown_persona', true) in AGENT_RUN's options — which restores the old behaviour of running on the built-in default persona.

Upgrading from v1.14.2 or earlier: an AGENT_RUN call naming a persona nobody created used to succeed with a warning in the log. It now errors. That is the point: the default persona is conversational, and substituting it for a task-shaped one produced answers that read fine and did not do the work.

CREATE PERSONA / CREATE OR REPLACE PERSONA

CREATE [OR REPLACE] PERSONA <name> WITH ( key = value [, ...] );

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', 'markdown', 'json' or 'code'.
tool_enabled	BOOL	TRUE	Whether runs on this persona may call tools.

<name> is a SQL identifier. Use a delimited (double-quoted) identifier when the name carries a character a bare identifier cannot — the quotes are not part of the stored name, and a doubled "" inside means one literal ":

CREATE PERSONA "aidb-assistant" WITH ( system_prompt = 'You are a database task executor.' );
DESCRIBE PERSONA "aidb-assistant";      -- resolves to the persona named aidb-assistant

Fixed in v1.14.2.1. Earlier builds stored the quotes verbatim, so CREATE PERSONA "x-y" created a persona literally named "x-y" that no later statement could address.

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

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

An unknown option key is rejected at parse time, and system_prompt is required — CREATE PERSONA p WITH ( display_name = 'P' ) errors with CREATE PERSONA requires system_prompt = '<string>'. Creating a name that already exists errors unless OR REPLACE is given; OR REPLACE is a full rewrite (keys you omit revert to their defaults) that preserves created_at and the built-in flag.

Commas inside a quoted value are safe, and a literal single quote is escaped by doubling it:

CREATE OR REPLACE PERSONA docs_writer WITH (
  system_prompt = 'First, do no harm. Use the user''s schema.',
  description   = 'Writes SQL, only SQL'
);

A pinned model must be installed (v1.14.2). model = 'typo-not-installed:7b' is rejected at DDL time with the list of installed models and a pointer to PULL_MODEL('...'), instead of failing later inside an agent loop. The check is skipped only when no model registry can be resolved.

ALTER PERSONA

ALTER PERSONA <name> SET ( key = value [, ...] );

Edits only the keys named (the same six keys as CREATE PERSONA; at least one is required). Setting model to the empty string clears the pin and restores "inherit the configured default".

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 = '' );          -- back to the config default
ALTER PERSONA retention_analyst SET ( tool_enabled = false );

DROP PERSONA

DROP PERSONA [IF EXISTS] <name> [CASCADE];

IF EXISTS makes the drop idempotent (Persona 'x' does not exist, skipping (IF EXISTS)). A built-in persona cannot be dropped (cannot drop built-in persona 'sql_developer').

Since v1.14.2.1 the drop is also refused while a durable agent still binds to the persona, so a DROP can no longer silently orphan an agent:

Cannot drop persona 'retention_analyst' because 2 agent(s) depend on it:
acme/churn_watcher, acme/renewal_bot. Repoint or drop those agents first, or use
DROP PERSONA retention_analyst CASCADE to drop it anyway ...

Dependents are listed as tenant/agent, and the scan covers every tenant (personas are install-wide). Repoint or drop those agents first — a new CREATE AGENT naming an unknown persona is refused too. If the agent store cannot be read at all, the check is skipped rather than blocking the drop.

CASCADE forces the drop. It does not delete the dependent agents; it only waives the refusal, and they are left with a dangling binding that DESCRIBE AGENT reports as persona_resolved = false:

DROP PERSONA retention_analyst CASCADE;
-- Persona 'retention_analyst' dropped (CASCADE) — 2 agent(s) now have a dangling
-- persona binding: acme/churn_watcher, acme/renewal_bot

SHOW PERSONAS / DESCRIBE PERSONA

SHOW PERSONAS;                         -- persona_name, display_name, model, tool_enabled, is_builtin
SHOW PERSONAS LIKE 'retention%';
SHOW PERSONAS LIKE '%gate%';           -- substring match needs wildcards on both sides
DESCRIBE PERSONA retention_analyst;    -- (property, value) rows, including the full system_prompt
DESCRIBE PERSONA "aidb-assistant";     -- delimited identifier for a hyphenated name

model comes back NULL in SHOW PERSONAS (and (config default) in DESCRIBE PERSONA) when no model is pinned.

The LIKE pattern is standard, anchored SQL LIKE (v1.14.2.1): % matches any run of characters, _ matches exactly one, every other character is a literal, and the whole persona_name must match. LIKE 'gate' therefore matches only a persona named exactly gate — use '%gate%' to find docgate_analyst. Matching is case-sensitive.

Changed in v1.14.2.1. In v1.14.2 the pattern was compiled into an unanchored regex, so LIKE 'gate' matched docgate_analyst and a pattern containing regex metacharacters (., (, [) was silently treated as a regex.

---

Persistent agent memory (v1.14.3+)

CREATE MEMORY gives an agent memory that survives the conversation: episodes, durable facts, temporal validity, relationships, provenance, and an authoritative current state — as one logical object.

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

Creating a memory object

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, working object. IDENTITY is required: it names the field that scopes every read and write, and memory never crosses identities.

The object materializes five ordinary tables, so nothing is hidden from you:

Table	Holds
_mem_<name>_episodes	raw statements — the source everything else 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)

SELECT attribute, value, valid_from FROM _mem_customer_memory_state WHERE identity = '123';

The five verbs you actually use

Verb	Purpose
REMEMBER	persist an observation; the engine derives everything from it
RECALL	assemble the context relevant to a question
CURRENT	read the authoritative present value of one attribute
FORGET	explicit, authorized removal
TRACE	explain why a value is believed

Everything else — CONSOLIDATE, RECONCILE, SUPERSEDE, HISTORY, SEARCH, RELATE, EXPLAIN MEMORY — is optional control, never a prerequisite.

REMEMBER

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, without any 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 response is a JSON envelope in the envelope column:

{
  "status": "accepted",
  "operation": "REMEMBER",
  "memory": "customer_memory",
  "identity": {"field": "user_id", "value": "123"},
  "request_id": "req_91ac",
  "data": {
    "episode_id": "ep_89232",
    "memory_ids": ["mem_1042", "mem_1043"],
    "changes": {"episodes_created": 1, "facts_created": 1, "state_changes": 1,
                "relationships_created": 1, "records_superseded": 1},
    "consolidation": {"required": true, "status": "pending"}
  },
  "warnings": [],
  "error": null
}

consolidation.status = "pending" never means the episode was lost — the raw episode is committed before any derivation runs.

Works with no model configured. A deterministic extractor always runs; the LLM pass is additive and degrades to the rule result on any 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');

Full option list: source_type, source_id, confidence, event_time, session_id, session_end, attribute, value, extract.

source_type sets the claim's authority, which is the first dimension of conflict resolution:

system_of_record > verified_user_statement > user_statement > tool_result
                 > prior_user_statement > agent_derivation > agent_inference

RECALL

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:

{
  "current_state": {"cloud_provider": {"value": "AWS", "confidence": 0.98,
                                       "valid_from": "2026-08-15 14:32:00"}},
  "semantic":  [{"memory_id": "mem_1043", "fact": "Customer prefers managed cloud services",
                 "confidence": 0.91, "relevance": 0.77}],
  "episodic":  [{"episode_id": "ep_89232", "event": "...", "relevance": 0.95}],
  "relationships": [{"subject": "Customer", "predicate": "USES", "object": "AWS"}],
  "temporal":  [{"attribute": "cloud_provider", "previous_value": "Azure",
                 "current_value": "AWS", "changed_at": "2026-08-15 14:32:00"}],
  "context":   {"prompt_ready": "Cloud provider is AWS. ...",
                "estimated_tokens": 31, "truncated": false}
}

Two properties worth relying on:

• Authority beats similarity. "What cloud do we use now?" returns AWS ahead of the semantically similar older Azure episode. "What did we use before AWS?" routes to temporal history instead.
• confidence and relevance are different fields. Confidence is evidence strength; relevance is retrieval score. A highly relevant historical statement can be non-authoritative.

Options: current, history, episodes, facts, relationships, provenance, temporal, token_budget, prompt_ready, as_of, min_confidence, explain.

RECALL customer_memory FOR user_id = 123 ABOUT 'production infrastructure'
  WITH (current = true, history = 3, provenance = true);

When the token budget is exceeded, lower-priority context is truncated and context.truncated becomes true — but current state is preserved.

CURRENT

CURRENT customer_memory FOR user_id = 123 ATTRIBUTE subscription_plan;

Deterministic state lookup, never semantic search. Three outcomes:

• status = "ok" — one accepted value, with valid_from, confidence, authority, and the source record.
• status = "not_found" — nothing recorded; data.value is null.
• status = "conflict" — competing authoritative claims that authority, temporal validity and confidence all failed to separate. Both claims are returned in data.competing_claims. The engine does not pick. Resolve with RECONCILE or a higher-authority REMEMBER.

TRACE

TRACE customer_memory FOR user_id = 123 ATTRIBUTE cloud_provider;

Returns the source episode and its verbatim content, when it was recorded versus when the event happened, the extraction method and version, the confidence, the derivation steps, and the record this value superseded.

FORGET

FORGET customer_memory FOR user_id = 123 ABOUT 'home address';

Explicit removal only — for a user request, a retention obligation, or an administrative correction. It is not the cleanup mechanism: expiry, deduplication, compaction and consolidation all happen automatically without it. Removal propagates to derived facts, embeddings, relationships, current-state values and provenance, and the response reports the count per category. WITH (mode = 'suppress') makes records inaccessible instead of deleting them.

Consolidation is the engine's job

The engine runs consolidation on its own schedule — on an episode-count threshold, a session boundary, elapsed age, or an idle window — with no LLM prompt, agent tool call, or application callback involved. A consolidation 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 of episodes produces no new facts.

Watch it work:

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

You may force a cycle — before a report, in a test, after a bulk import:

CONSOLIDATE customer_memory FOR user_id = 123;

but you never have to. If you find yourself calling 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',
    memory_auto_recall   = true,   -- default
    memory_auto_remember = true    -- default
);

With this binding the runtime 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. Exposing REMEMBER/RECALL as tools is still supported when an agent needs discretionary control; the two paths reach the same engine.

memory_object and memory_identity must be given together — a bound object with no identity has no scope to operate in.

Conflicts

Two contradictory claims coexist as claims until policy resolves them; evidence is never overwritten. Resolution considers, in order: explicit correction language, source authority, temporal validity, then a meaningful confidence margin. When none of them separates the claims the attribute is marked conflicted and both are exposed.

-- Both statements are user statements, both dated the same, and they disagree:
CURRENT customer_memory FOR user_id = 123 ATTRIBUTE cloud_provider;
-- => status "conflict", data.competing_claims = [{"value": "Azure", ...}, {"value": "AWS", ...}]

RECONCILE customer_memory FOR user_id = 123 ATTRIBUTE cloud_provider WITH (prefer = 'AWS');

Set conflict_policy = 'ALWAYS_FLAG' to require a human for every contradiction, or 'LATEST_WINS' / 'HIGHEST_CONFIDENCE' for simpler deployments.

Identity isolation

Memory reads and writes never cross identities. Over REST, a credential whose token carries a memory_identity claim is pinned: a request naming any other identity is refused with MEMORY_ACCESS_DENIED before any SQL is composed.

An ordinary interactive login is pinned to its own user automatically. A client can then send no identity at all and have it resolved from the token — it cannot address another identity because it never names one.

On a default CE install /v1/auth/register also gives each user its own tenant, and memory objects are tenant-scoped, so that is a second boundary already. The pin matters for the configuration the licensing model describes — one tenant with several users (MAX_TENANTS = 1) — where the identity is the only boundary left.

Admins and API keys stay unpinned, deliberately: an admin needs to inspect or FORGET on a user's behalf, and a service credential serving many end users supplies the identity per request (the application-server pattern). If you mint your own JWTs, set memory_identity yourself.

REST and MCP

The same contract is available over HTTP at /v1/memory/* and as the MCP tools memory_remember, memory_recall, memory_current, memory_forget, memory_trace. All three surfaces return the identical envelope, so a client never has to parse prose to learn what happened. The maintenance verbs are deliberately absent from MCP: memory correctness must not depend on an agent remembering to invoke them.

curl -sS -X POST localhost:8080/v1/memory/customer_memory/recall \
  -H 'Authorization: Bearer <token>' -H 'Content-Type: application/json' \
  -d '{"identity":"123","about":"production cloud"}'

---

Durable-agent activation control (v1.14.2+)

Durable agents (CREATE AGENT, v1.9.0+) fire on a cron schedule and/or on committed DML events. v1.14.2 adds two ways to drive them from a pipeline.

WAKE AGENT — fire now, asynchronously

WAKE AGENT <name>;

Enqueues an immediate run on the background dispatcher and returns at once (Agent 'stage2_summarizer' woken (queued)) — the LLM loop never runs inside your request. This is the async counterpart to EXECUTE AGENT, which runs the loop synchronously and returns its output. Use it to hand off between pipeline stages without waiting for a cron tick.

The agent must exist and be enabled; waking a disabled agent errors with Agent '<name>' is disabled (ALTER AGENT <name> ENABLE to wake it). The queued run shows up in _system_agent_runs with activation = schedule 'manual-wake' once the dispatcher drains it:

WAKE AGENT stage2_summarizer;

SELECT started_at, status, output
FROM _system_agent_runs
WHERE agent_name = 'stage2_summarizer'
LIMIT 5;

A user-issued wake is enqueued at cascade hop 0 — it starts a fresh chain rather than continuing one.

ALLOW AGENT ORIGIN — let one agent's write fire another

CREATE AGENT <name> PERSONA '<persona>' TASK '<task>'
  ON {INSERT|UPDATE|DELETE} {INTO|ON|FROM} <table> [WHERE <expr>] ALLOW AGENT ORIGIN
  [WITH ( ... )];

This is a suffix on an event binding, not a statement of its own. By default a write made inside an agent's run never re-fires any event binding — the cascade suppression that stops an allow_writes agent from looping on its own INSERT. ALLOW AGENT ORIGIN opts that one binding in, so agents can be chained into a pipeline:

-- Stage 2 fires when stage 1's agent marks a row VALIDATED
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 always remain in force: a binding never fires on its own agent's write (no self-loop), and a chain is capped at 5 hops, so even a mis-declared A → B → A cycle halts. Ordinary user writes fire the binding at hop 0 whether or not the suffix is present.

Place the suffix after the optional WHERE; the predicate is cut at the suffix, so the binding above stores its predicate as stage = 'VALIDATED'.

Reading the opt-in back (v1.14.2.1+). The flag is auditable, not write-only: both DESCRIBE AGENT and SHOW AGENTS print the suffix on every binding that carries it, spelled exactly as it is declared, so the readback can be pasted straight back into DDL. A binding without the opt-in prints no suffix.

DESCRIBE AGENT stage2_summarizer;
-- property   | value
-- Bindings   | UPDATE ON doc_queue WHERE stage = 'VALIDATED' ALLOW AGENT ORIGIN

SHOW AGENTS;
-- name               | persona            | bindings                              | state   | tokens_today
-- stage2_summarizer  | retention_analyst  | UPDATE ON doc_queue ALLOW AGENT ORIGIN | enabled | 0

(The SHOW AGENTS summary stays compact — it omits the WHERE predicate but still shows the cascade opt-in. Use DESCRIBE AGENT for the full binding.)

---

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, no separate process. The three functions below expose that registry as SQL, alongside the equivalent synapcores pull / synapcores models list CLI commands.

These 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) — fetch 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 with the same name short-circuits when the local manifest digest matches the registry's current digest; no blob bytes are re-fetched.
• Resume: interrupted pulls leave a .partial file; re-running PULL_MODEL resumes from the byte offset already on disk.
• Min engine version: 1.8.0.

-- Pull the default 7B chat model (the v1.8 install-script default).
SELECT PULL_MODEL('qwen2.5-coder:7b');

-- Pull an embedding model (used by EMBED + AGENT_RUN's memory layer).
SELECT PULL_MODEL('library/all-minilm:latest');

-- Pull from a third-party namespace.
SELECT PULL_MODEL('bartowski/Llama-3.2-3B-Instruct-GGUF:Q4_K_M');

LIST_MODELS() — inventory the local model store

• Arguments: none.
• Returns: a table with columns (name TEXT, architecture TEXT, size_bytes BIGINT, digest TEXT, pulled_at TIMESTAMP, last_used_at TIMESTAMP).
• Reads on-disk manifest sidecars only — never touches the network.
• Min engine version: 1.8.0.

-- Inventory installed models.
SELECT * FROM LIST_MODELS();

-- Total disk used by the model store.
SELECT SUM(size_bytes) AS total_bytes FROM LIST_MODELS();

DELETE_MODEL(name) — remove 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.
• Min engine version: 1.8.0.

-- Remove a model that's no longer needed.
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 — you do not call out to Python.

CREATE EXPERIMENT — train a model

Syntax (v2.1):

CREATE EXPERIMENT model_name AS
  SELECT feature_1, feature_2, label_column AS target FROM training_table
WITH (
    task_type = 'regression', target_column = 'target',
    algorithms = ['ridge', 'xgboost'], max_trials = 10,
    validation_strategy = 'holdout', validation_split = 0.2,
    hyperparameter_strategy = 'fixed_grid',
    optimization_metric = 'r2', random_seed = 42
);

Accepted task types are regression, classification, binary_classification, multi_classification, anomaly_detection, clustering, and time_series. Explicit unknown algorithm names, task types and options are errors. SQL, REST and queued training use the same names and configuration.

• Synchronous training defaults to a 10-minute budget. WITH (async=true) queues the complete experiment configuration and returns a durable job_id.
• WITH (schedule='0 2 * * *') queues an initial fit and retrains daily at 02:00 UTC. Schedules accept five fields (minute/hour/day/month/weekday) or six with seconds. Missed occurrences coalesce into one retrain; a running model has one job at a time. Specs, statuses and schedules survive restart under the engine's data directory. A job interrupted by a crash retries after its tenant's training bridge starts.
• Use WITH (async=true); the separate CREATE EXPERIMENT ASYNC grammar is not supported.
• Search strategies are fixed_grid/auto, random, grid, bayesian/tpe, halving/hyperband/successive_halving, and optuna. Pinned trainer parameters override suggestions. Native TPE benefits from a budget large enough for its warm-up. The inherited halving aliases currently generate a seeded random candidate pool; they do not implement resource escalation or candidate elimination.
• optuna invokes the installed upstream Python package solely for parameter search. Install optuna in a Python environment and optionally set AIDB_OPTUNA_PYTHON to that interpreter; otherwise python3 is used. Missing or failing runtimes return an error. Training data and model fitting stay in the Rust engine.
• Preprocessing is fitted on the training partition only. k_fold fits preprocessing separately in each fold; the winning regression model is refitted on all rows. Classification preserves a held-out calibration partition for the served model.
• best_score is a selection score, with score_basis, n_folds, refit_on_full_data, trials_attempted and per-trial errors in the response. Loss metrics are negated so that a larger score is better.
• The 500,000-row default ceiling applies to both synchronous and queued experiments. Raise it with max_training_rows or AIDB_AUTOML_MAX_TRAINING_ROWS; queueing does not bypass it. Some expensive algorithms have smaller explicit resource ceilings.

Algorithm options:

Algorithm	Supported tasks	Implementation
linear_regression	regression	Linear regression with optional regularization
logistic_regression	binary classification	Logistic regression
random_forest, decision_tree, extra_trees	regression, classification	Tree estimators
gradient_boosting	regression, classification	Native gradient boosting
lightgbm	regression, binary classification	Native compatible boosting
xgboost	regression, classification	Native second-order boosting with regularized split gain and leaves
catboost	regression, classification	Native categorical boosting with symmetric trees and ordered statistics
adaboost	binary classification	Boosted stumps
knn	regression, classification	Nearest neighbors
svm	binary/multiclass classification, up to ten detected classes	Support-vector classification
svr	regression	Epsilon-insensitive support-vector regression
naive_bayes	classification	Naive Bayes
neural_network	regression, classification	Neural network
isolation_forest	anomaly detection	Isolation forest
ridge, lasso, elastic_net	regression	L2, L1 and mixed penalties
gaussian_process	regression	RBF covariance with Cholesky inference
stacking, blending	regression	Out-of-fold or held-out meta-regression over heterogeneous base models
kmeans, mini_batch_kmeans	clustering	Centroid partitioning
dbscan	clustering	Density connectivity, with -1 for noise
hierarchical	clustering	Agglomerative single/complete/average/Ward linkage
gmm	clustering	Regularized diagonal-covariance Gaussian mixture EM
seasonal_naive, ets	time series	Seasonal baseline and exponential smoothing
arima, sarima	time series	Conditional-sum-of-squares ARIMA and seasonal ARIMA
prophet	time series	Native additive trend/changepoints/Fourier seasonality

xgboost, catboost, lightgbm and prophet name native compatible algorithm families. They do not load or export upstream Python/R model binaries. Ensemble selection is explicit; stacking and blending currently support regression.

Feature-only clustering: omit target_column and select only clustering features. The selection metric is silhouette (default, deterministic bounded sample) or davies_bouldin (negated). Noise reduces the silhouette selection score; a degenerate one-cluster result is a failed trial. Scores are internal validation, not labeled accuracy. DBSCAN assigns new observations by neighboring fitted core points and returns -1 when none qualifies. Hierarchical prediction uses the nearest fitted cluster representative.

CREATE EXPERIMENT customer_groups AS SELECT spend, visits FROM customers
WITH (task_type='clustering', algorithms=['kmeans'], n_clusters=4);
SELECT AUTOML.PREDICT('customer_groups', spend, visits) FROM customers;

Anomaly detection: train native Isolation Forest without a target column, using only ordered feature columns. ANOMALY_SCORE returns a FLOAT64 in [0,1], where larger values indicate more unusual observations. It uses the saved preprocessing and cached fitted model, batches projection rows, and never routes scoring to an LLM.

CREATE EXPERIMENT transaction_anomalies AS SELECT amount, velocity FROM transactions
WITH (task_type='anomaly_detection', algorithm='isolation_forest',
      n_trees=128, sub_sample_size=256, contamination=0.01, random_seed=7);
SELECT id, ANOMALY_SCORE('transaction_anomalies', amount, velocity) AS score
FROM transactions ORDER BY score DESC LIMIT 10;

algorithms=['isolation_forest'] is also supported. Features must match the saved names, order and count. Filter or impute NULL features explicitly. Missing models, incompatible tasks, invalid features and non-finite scores fail the statement. Model lookup follows the authenticated tenant and database; fitted models reload after restart. Sort using the projected alias, as shown. tail_separation is an unsupervised selection metric, not F1 or labeled accuracy. SELECT AUTOML.TRAIN('model','SELECT amount, velocity FROM transactions') WITH (task_type='anomaly_detection', algorithm='isolation_forest') queues training; wait for completed job status before scoring.

Forecasting: supply a numeric timestamp column and numeric target for one series. Training sorts by timestamp; timestamps must be unique, increasing and equally spaced. Fill missing observations explicitly. Set time_column when the query contains other columns. forecast_horizon sets the holdout window, or each walk_forward window when cross-validation is requested. Metrics are negative MAE (default), RMSE, MSE or sMAPE. The selected forecast is refitted on all observations before serving when budget permits. Predict using future timestamps on the fitted grid; in-sample and off-grid times fail.

CREATE EXPERIMENT sales_forecast AS SELECT day_index, revenue FROM daily_sales
WITH (task_type='time_series', target_column='revenue', time_column='day_index',
      algorithms=['ets','sarima'], seasonal_period=7, forecast_horizon=14,
      validation_strategy='walk_forward', n_folds=3);
SELECT AUTOML.PREDICT('sales_forecast', 120.0);

SVR and Gaussian processes default to at most 512 training rows (hard maximum 1024), hierarchy to 2000, and DBSCAN to 10000. Larger inputs fail with an explanatory resource limit; they are never silently sampled. Seeded training and saved preprocessing make reload behavior reproducible.

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

Syntax:

SELECT pass_through_col_1, pass_through_col_2, ...,
       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 — they are matched by name to the model's feature schema.
• Returns:
• Binary classification with a numeric 0/1 target: P(target = 1) as DOUBLE, calibrated when the model has fitted calibration.
• Multiclass with more than two fitted classes: probability of the predicted 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) — the planner pushes the prediction down so the alias is in scope.

These are the top-level SQL formatter's semantics. A two-class probability row returns the second fitted class's probability, including for other numeric labels or a two-class model trained with the multiclass task. The REST bridge uses the persisted model task: binary returns positive-class probability, and multiclass returns predicted-class confidence. Training normalizes two-class classification requests to binary. The separate nested query executor's scalar Predict path returns prediction labels and a confidence column; its direct BatchPredict statement path is unsupported. For churn ranking, use an explicit binary task with 0=retained, 1=churned and verify the route in use.

Worked example — rank customers by churn risk:

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;

Important — feature columns also in the projection (v1.6.5.1): If a feature column is also a pass-through column, the planner dedupes it automatically; you do NOT need to list it twice:

-- OK: tenure_months in BOTH pass-through and features — dedupe handles it.
SELECT id, tenure_months,
       AUTOML.PREDICT('churn_model_v1', tenure_months, monthly_charges) AS risk
  FROM customers
 ORDER BY risk DESC;

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

Syntax:

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

Unlike CREATE EXPERIMENT, this queues the work instead of blocking the connection, and it is how you retrain a model that is already deployed. Training 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.

It returns a status, not a model:

status	meaning
queued	accepted; the response carries the job id
pending_rows	deferred — retrain_after_rows has not been reached yet
pending_drift	deferred — the live feature distribution has not moved far enough

Drift-triggered retraining. retrain_on_drift = <positive sigma threshold> retrains only once the rows being scored have drifted that many standard deviations from the distribution the model was fitted on. The current shared configuration requires a finite positive number; true and a separate drift_baseline option are not supported.

This needs no labels and no ground truth — it compares live feature values against the scaler stored with the model, so it works on production traffic where outcomes are not yet known. It is measured only over rows actually scored since deployment, so a model nothing has called reports no measurement rather than zero drift. Observing drift costs effectively nothing at prediction time.

-- retrain when inputs shift by 2 sigma
SELECT AUTOML.TRAIN('churn_model',
                    'SELECT tenure, monthly_charges, churned FROM customers',
                    'churned')
  WITH (retrain_on_drift = 2.0);

schedule = '...' registers active UTC cron retraining. The bounded queue persists configuration and job statuses; an interrupted job retries after restart. Use the same model name to coalesce overlapping requests.

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;

Known limitations:

• Model artifacts live under <data_dir>/models/ and are not portable across binaries (the in-memory representation may change with version upgrades).
• Models are tenant-prefixed; a tenant cannot use another tenant's models.
• AUTOML.PREDICT is supported in the SELECT projection. Wrapping it inside a FROM (...) AS sub subquery requires the v1.6.5.1 outer-subquery-wrap fix and works for ORDER BY alias / LIMIT but not for arbitrary outer projection rewrites.

---

Cypher Graph Queries

AIDB ships a per-tenant property graph engine with a Cypher subset. Cypher statements are routed automatically by /v1/query/execute (and by the SQL /v2/query/execute path) — you do not need a separate endpoint.

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 graph 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 me everyone reachable from X via these edge types" succinctly. Cypher beats N-way SQL self-joins on graph-shaped data.

Discovery

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

---

Multi-modal SQL

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

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

---

Triggers and Procedures

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

DROP TRIGGER [IF EXISTS] trg_name [ON table_name];

CREATE [OR REPLACE] PROCEDURE proc_name(args) AS $$
BEGIN
    -- procedure body
END;
$$ LANGUAGE plpgsql;

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

SHOW PROCEDURES [LIKE 'pattern'];
SHOW TRIGGERS [FROM table_name] [LIKE 'pattern'];

A trigger name belongs to the current tenant and database. OR REPLACE and DROP TRIGGER affect that database's definition; DROP DATABASE removes its owned triggers and attachments to dropped tables. SHOW TRIGGERS lists the current database. An invalid target, unknown UPDATE OF column, or duplicate name fails the SQL request instead of returning a successful error cell.

An inline body may replace EXECUTE PROCEDURE, for example:

CREATE TRIGGER adjust BEFORE UPDATE OF total ON orders FOR EACH ROW
BEGIN SET NEW.total = NEW.total + 1; END;

UPDATE OF matches the columns assigned by SET, even SET total=total; updates assigning only other columns skip the trigger. WHEN false skips the body. BEFORE INSERT/UPDATE may rewrite NEW; UPDATE/DELETE may inspect OLD.

User-trigger writes require the native row backend. In autocommit, AFTER errors fail the statement and roll back native base/trigger row writes, indexes and row statistics. Columnar/nonnative triggered writes are refused before writing. Explicit native transactions still reject writes to tables with enabled triggers. Trigger SQL cannot run DDL or mutate a nonnative table within a native row statement. Completed HTTP/shell actions cannot be undone by row rollback; agent event enqueue is not a native commit outbox. FOR EACH STATEMENT currently uses row firing; the single-table UPDATE path is supported, while UPDATE FROM is not implemented.

---

Natural Language

ASK '<natural language question>';            -- run a natural language query
EXPLAIN NATURAL '<natural language question>'; -- show the SQL plan

---

Operator configuration

These are gateway (community.toml) settings and CLI, not SQL — included here because they feed docs.synapcores.com and affect how the engine runs.

Timeouts for in-process LLM operations (v1.12.0+)

The shipped config 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)

[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 can never 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, or
• run synapcores telemetry disable, or
• set the environment variable DO_NOT_TRACK=1, or
• set the environment variable SYNAPCORES_TELEMETRY=off.

Operator CLI (synapcores telemetry <sub>):

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

---

Critical "do's and don'ts" for AIDB recipes

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

• Need text/image similarity? -> EMBED + COSINE_SIMILARITY (or MULTI_MODAL_SIMILARITY).
• Need a trained model? -> CREATE EXPERIMENT ... WITH (...) then AUTOML.PREDICT(...).
• Need risk-ranked output? -> ORDER BY <prediction_alias> DESC LIMIT N directly.
• Need multi-hop relationships? -> Cypher MATCH, not N-way self-joins.
• Need LLM-generated text per row? -> GENERATE(prompt) in the SELECT.

DON'T:

• Don't invent functions or syntax not in this manual.
• 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 have actual, runnable SQL.
• Don't include DROP TABLE/DROP INDEX cleanup steps in recipes (they delete user data).
• Don't store an embedding in a column whose declared dimension differs from the model's output dim — that is a runtime error.