Manual 1 of 8

Customer Manual

Internet SQL AI 1.25 Customer Manual

Applies to: InternetSQL 1.25 AI release

Audience: database users, application developers, administrators, and support engineers

Scope: features implemented and shipped in the current source/build

1. Product overview

Internet SQL AI adds persistent vector collections and exact similarity search

to the existing InternetSQL relational database. It operates alongside normal

tables, joins, transactions, storage fields, VINSERT, and VECTOR; it does

not replace or reinterpret those features.

The current release can:

create persistent AI/vector collections;
insert application-generated numeric embeddings with IDs and metadata;
search by COSINE, L2, or DOT ranking;
return ranked results through normal embedded and TCP query results;
report collection availability and remove AI collections;
provide a standalone vector command-line utility;
provide a deterministic offline embedding API for development tests;
generate source-backed answers with structured citations through ASK AI;
expose read-only and policy-controlled MCP tools;
trace AI execution stages from every shipped shell and server.

The isolated provider-host layer implements OpenAI embeddings and Responses

generation. SQL provider DDL, SEARCH AI ... TEXT, provider-backed sync,

database-native RAG/ASK AI, and MCP are implemented. IVF/HNSW indexes are not

implemented. See InternetSQL_OpenAI_Provider_Manual.md,

InternetSQL_AI_RAG_Manual.md, and InternetSQL_MCP_Manual.md.

2. Programs with AI support

The AI SQL command path is compiled into the existing isqldll.dll and

isqllib.lib. No separate AI DLL or library is required.

The following shipped programs share the AI command and trace facilities:

| Program | Engine mode | Typical use |

|---|---|---|

| `isqlsh.exe` | Embedded or TCP client | Scripts, automation, regression, interactive commands |

| `shell.exe` | Existing DLL family | General SQL shell |

| `shelldev.exe` | Existing DLL family | Development shell |

| `locshell.exe` | Static engine | Local/embedded shell |

| `shellonlib.exe` | Static engine | Static-library shell |

| `isqlserver.exe` | TCP server | Windowed server |

| `isqlserverd.exe` | TCP server | Console/headless server |

All continue to use their established startup and connection conventions. AI

commands are ordinary commands entered wherever that shell accepts SQL.

3. Starting `isqlsh`

3.1 Embedded mode


isqlsh.exe -dbhome C:\InternetSQLData

For a script:


isqlsh.exe -q -e -dbhome C:\InternetSQLData -cmd ai-demo.sql

The database home must contain its normal db directory. AI collection files

are placed below the selected database's table directory.

3.2 TCP client mode

Start an InternetSQL server using the deployment's normal database home and

port, then connect with:


isqlsh.exe -ip 127.0.0.1 -port 1973 -u kamranga -pwd manager

For unattended production use, do not place passwords directly in shared

scripts or process listings. Use the credential mechanism appropriate to the

deployment.

AI search results use the normal query/cursor transport. An equivalent query

therefore has the same fields and values in embedded and TCP modes.

4. Five-minute example

Save this as ai-demo.sql:


SHOW AI DEBUG ON;

CREATE AI TABLE product_embeddings DIM 4 METRIC COSINE;

INSERT VECTOR INTO product_embeddings ID 1001
VALUES [0.90,0.10,0.00,0.20]
META 'category=database';

INSERT VECTOR INTO product_embeddings ID 1002
VALUES [0.82,0.18,0.02,0.25]
META 'category=database';

INSERT VECTOR INTO product_embeddings ID 1003
VALUES [0.00,0.05,0.95,0.10]
META 'category=network';

SEARCH AI product_embeddings
QUERY [0.90,0.12,0.05,0.22]
TOP 2;

SHOW AI STATUS product_embeddings;
SHOW AI TRACE LAST 30;
SHOW AI DEBUG OFF;

Run it:


isqlsh.exe -q -e -dbhome C:\InternetSQLData -cmd ai-demo.sql

Search results contain:

| Column | Meaning |

|---|---|

| `RANK` | One-based position after metric ordering |

| `VECTOR_ID` | ID supplied by `INSERT VECTOR` |

| `SOURCE_KEY` | Stable source-row key returned for relational joins |

| `SCORE` | Metric-specific distance or score |

| `METADATA` | Stored metadata when requested by the engine path |

5. AI command reference

5.1 `CREATE AI TABLE`


CREATE AI TABLE collection_name DIM dimension METRIC metric;

Related collection:


CREATE AI TABLE article_ai FOR TABLE articles KEY article_id
TEXT (title,body) DIM 384 METRIC COSINE;

The table and fields are validated and stored as relationship metadata without

altering the relational table.

Supported metrics:

COSINE: smaller distance is closer; identical direction is normally 0.
L2: smaller Euclidean distance is closer; an identical vector is 0.
DOT: larger dot-product score is better.

Examples:


CREATE AI TABLE support_articles DIM 384 METRIC COSINE;
CREATE AI TABLE image_features DIM 512 METRIC L2;
CREATE AI TABLE recommendations DIM 128 METRIC DOT;

Collection names must pass the engine's collection-name validation. Creating

an existing collection is an error. Dimension and metric are permanent for

the collection; use a new collection to migrate to another embedding model or

dimension.

5.2 `INSERT VECTOR`


INSERT VECTOR INTO collection_name ID integer
SOURCE_KEY 'stable-key'
VALUES [number, number, ...]
[META 'text'];

For an explicit multi-insert vector transaction:


BEGIN AI TRANSACTION;
INSERT VECTOR INTO support_articles ID 42 VALUES [0.1,0.2,0.3];
INSERT VECTOR INTO support_articles ID 43 VALUES [0.2,0.3,0.4];
COMMIT;

Use ROLLBACK instead of COMMIT to discard the staged vectors. Searches in

the same session can see staged vectors. Disconnecting without a commit rolls

them back. Without BEGIN AI TRANSACTION, INSERT VECTOR retains its original

autonomous behavior.

An AI transaction is limited to one collection; commit or roll back before

starting work on another collection.

Example:


INSERT VECTOR INTO support_articles ID 42
VALUES [0.10,-0.05,0.40,0.01]
META 'topic=recovery,language=en';

Rules:

The ID is an unsigned stable vector identifier and must be unique within the collection.
The number of values must exactly match the collection dimension.
Values must be valid finite floating-point input.
Metadata is currently an opaque text payload.
Reinserting an existing ID is an error; there is no SQL update/upsert command in this preview.

Collections created with FOR TABLE ... KEY ... require a non-empty stable

SOURCE_KEY on every inserted vector.

5.3 `SEARCH AI`


SEARCH AI collection_name
QUERY [number, number, ...]
TOP k
[METRIC COSINE|L2|DOT]
[WHERE metadata_predicate [AND metadata_predicate ...]]
[INTO TEMP result_table];

Examples:


SEARCH AI support_articles
QUERY [0.10,-0.05,0.40,0.01]
TOP 5;


SEARCH AI support_articles
QUERY [0.10,-0.05,0.40,0.01]
TOP 5
WHERE topic=recovery;

WHERE supports up to 16 metadata predicates joined by AND. Operators are

=, !=, <, <=, >, >=, IS NULL, and IS NOT NULL. Values that both

parse as numbers use numeric comparison; other values use text comparison.

Quoted text is supported. For compatibility, a filter containing no operator

performs the original case-sensitive metadata substring match. This is a

bounded metadata grammar, not the complete SQL expression grammar.


SEARCH AI support_articles QUERY [0.10,-0.05,0.40,0.01]
TOP 10 WHERE year>=2024 AND language='en' INTO TEMP current_english_hits;

The SQL and C API boundaries limit TOP to 256, dimensions to 4096, metadata

to 2048 bytes, stable source keys to 255 bytes, and filter text to 511 bytes.

Search is exact FLAT search. Results are deterministic and ordered according

to the selected metric. Do not compare scores produced by different embedding

models, dimensions, normalization choices, or metrics.

Hybrid join example:


SEARCH AI article_ai QUERY [0.10,-0.05,0.40,0.01]
TOP 10 INTO TEMP ai_hits;
SELECT ai_hits.RANK,ai_hits.SCORE,articles.title
FROM ai_hits,articles
WHERE ai_hits.SOURCE_KEY=articles.article_id
ORDER BY ai_hits.RANK;

The result table belongs to the current session and is removed automatically

when its query object is released or the session logs out. An application may

drop it earlier when it no longer needs the rows. A session may own at most 16

AI result tables at one time.

5.4 `SHOW AI STATUS`


SHOW AI STATUS support_articles;

This verifies that the named collection can be opened. The current preview

returns availability rather than detailed storage/index/job statistics.

5.5 `DROP AI TABLE`


DROP AI TABLE support_articles;

This removes the AI collection artifact. It does not drop or rewrite an

ordinary relational table. Back up required collection files before dropping.

5.6 Reserved `LIST AI TABLE`


LIST AI TABLE;

This syntax is recognized but intentionally returns not implemented yet in

the current release.

6. Debugging and trace support in every shell

6.1 Commands


SHOW AI DEBUG ON;
SHOW AI DEBUG STATUS;
SHOW AI TRACE;
SHOW AI TRACE LAST 50;
SHOW AI DEBUG OFF;

SHOW AI TRACE defaults to at most the last 200 events. LAST accepts 1

through 200. The trace file retains earlier events; the shell display is a

bounded tail for safe interactive inspection. Shell rows are truncated to 254

characters to fit the legacy query-result record limit; AI_TRACE.LOG retains

the complete event line.

SHOW AI DEBUG STATUS reports:

whether tracing is on or off;
the active AI_TRACE.LOG path;
that sensitive payload logging is redacted.

6.2 Trace from process startup

Set the environment variable before starting any shell or server:


set ISQL_AI_DEBUG=1
isqlsh.exe -dbhome C:\InternetSQLData

PowerShell example:


$env:ISQL_AI_DEBUG = '1'
.\isqlserverd.exe -dbhome C:\InternetSQLData -port 1973

This enables tracing before the first AI command. It is useful with windowed

shells, automated scripts, and server processes. Remove the environment

variable or issue SHOW AI DEBUG OFF when the diagnostic session ends.

6.3 Events currently traced

AI command routing;
database/vector initialization begin and success;
collection create parsing, validation, and commit;
collection drop begin and commit;
insert parsing, dimension validation, collection open, and commit;
search parsing, dimension/TOP/filter validation, collection open, completion, and emitted row count;
AI failures with bounded error text;
debug enable and disable events.

Each line includes local timestamp, process ID, thread ID, and stage name.

Example:


2026-08-16T22:30:15.120 pid=4100 tid=928 stage=create.validate collection=docs dim=3 metric=COSINE
2026-08-16T22:30:15.124 pid=4100 tid=928 stage=create.commit collection=docs
2026-08-16T22:30:15.137 pid=4100 tid=928 stage=search.complete collection=docs result_count=2

6.4 Redaction and model-internal limits

The trace does not record:

vector values;
metadata values;
document or prompt text;
passwords, API keys, authorization headers, or credential aliases' values.

When external providers are implemented, traceable information will include

provider/model configuration identity, request correlation, batch size, input

byte counts, provider-reported token usage, embedding dimension and safe

statistics/hash, latency, retries, retrieval/context counts, output-token

usage, and tool-call lifecycle.

Hosted OpenAI APIs do not expose their model weights, attention matrices,

hidden states, or internal transformer activations. No shell can display data

that the provider does not return. A future local provider may expose deeper

telemetry when its runtime supports it and an operator explicitly enables it.

7. Producing embeddings and synchronizing source tables

SQL supports numeric vectors, provider-generated text-query embeddings, and

durable source-table synchronization. External provider calls remain isolated

in isql-ai-provider-host.exe.

Operational rules:

Select one model/configuration for a collection.
Record its provider, model/version, dimension, preprocessing, and normalization.
Apply exactly the same process to stored documents and search queries.
Validate dimensions and finite values before submission.
Batch and cache external embedding requests where appropriate.
Rebuild into a new collection when changing models or dimensions.

OpenAI calls do not run inside the database engine. They run only in

isql-ai-provider-host.exe, which resolves a credential alias

from protected host configuration. Do not put an API key in SQL, metadata,

KAMRANGA.CFG, command arguments, or an AI trace.

8. Deterministic embedding API for tests

The existing AI code implements the compatibility embedding functions declared

in ivec_public.h through provider ABI version 1:


float vector[384];
unsigned int dim = 0;

if (ivec_embedding_init("deterministic") == IVEC_OK) {
    if (ivec_embedding_text_to_vector("test document",
                                      vector,
                                      384,
                                      &dim) == IVEC_OK) {
        /* Use vector only for repeatable development tests. */
    }
    ivec_embedding_shutdown();
}

debug is accepted as a compatibility alias for deterministic. The same

text and dimension produce the same values; different text normally produces

different values. These values have no semantic meaning and must not be used

as a production embedding model.

An unknown or missing provider fails explicitly with `embedding provider not

configured`. The implementation performs no network access.

Advanced embedded applications can use iaip_provider.h for provider

registration, health, batch embedding, bounded deterministic generation,

cancellation, and structured errors. ivec_search_text() embeds a query through

the explicitly selected provider and uses the normal filtered exact-search

path. See InternetSQL_AI_Provider_Reference.md.

The engine exposes durable SYNC AI TABLE, SHOW AI JOBS FOR, and

RETRY AI JOB commands using canonical SHA-256 content/chunk hashes,

pending/leased/ready/stale/failed states, bounded retries, expired-lease repair,

and idempotent stable vector IDs. See InternetSQL_AI_Synchronization_Jobs.md.

The isolated provider-host transport is also implemented. It uses an

ACL-restricted local named pipe, versioned framing, HELLO negotiation,

correlation validation, bounded deadlines, cancellation, supervised process

lifecycle, and structured errors. It supports deterministic offline fixtures

and the isolated OpenAI embeddings/Responses provider. SQL provider DDL and

SEARCH AI ... TEXT are available. See InternetSQL_AI_Provider_Host_Manual.md and

InternetSQL_OpenAI_Provider_Manual.md.

9. Relational relationship and hybrid-search pattern

Define the source relationship in the collection so InternetSQL validates the

table, key, text fields, source authorization, and stable mapping:


CREATE TABLE articles (
  article_id number(10),
  title char(120),
  body char(1000),
  embedding_state char(16)
);

CREATE AI TABLE article_ai FOR TABLE articles KEY article_id
TEXT (title,body) DIM 384 METRIC COSINE PROVIDER production_embeddings;

SYNC AI TABLE article_ai;
SHOW AI JOBS FOR article_ai;

Supported workflow:

Create a related AI collection with FOR TABLE, KEY, and TEXT fields.
Configure a protected embedding provider or use numeric application vectors.
Run SYNC AI TABLE; monitor durable work with SHOW AI JOBS FOR.
Retry bounded failed work with RETRY AI JOB after correcting its cause.
Search into a bounded session INTO TEMP table.
Join ranked SOURCE_KEY values to authorized relational source rows.

Ordinary relational files and vector collections do not claim a shared

two-phase commit. For one business operation that changes both stores, use the

documented retry/outbox pattern. Explicit BEGIN AI TRANSACTION, COMMIT, and

ROLLBACK provide atomic multi-vector changes within one collection.

10. Backup, restore, and removal

Stop writers or otherwise establish a consistent maintenance window.
Back up the normal database home and AI companion artifacts together.
Preserve file names, directory relationships, and permissions.
Test restoration into a separate database home.
Verify representative vector searches after restore.
Never delete an unknown .ivec file while the server is running.

Vector pages use checksummed WAL prepare/commit records, checkpoint/replay,

before/after images, torn-tail handling, and idempotent crash recovery.

isqlvec.exe supports verified EXPORT and atomic IMPORT workflows. These

facilities do not replace a coordinated whole-home backup: preserve relational

files, vector/catalog/WAL/job companions, configuration, policies, and audit

state together, and always perform a test restore.

11. Security guidance

Require authenticated server access and least-privilege accounts.
Restrict database-home filesystem permissions.
AI creation honors the existing createtable revocation; AI drop honors

droptable; vector insertion honors insert; searches and related

collection creation honor existing source-table and source-field select

revocations. Superuser behavior remains the existing InternetSQL behavior.

Protect TCP traffic at the deployment boundary according to organizational policy.
Treat embeddings and metadata as potentially sensitive derived data.
Do not log vector, metadata, prompt, or credential payloads.
Apply retention and secure deletion policies to AI_TRACE.LOG.
Validate application-generated SQL and never concatenate untrusted IDs or metadata.
Set resource limits appropriate to expected dimensions, result counts, and clients.

Debug trace files are operational records. They contain collection names, IDs,

dimensions, counts, process/thread IDs, timing, and error descriptions even

though payloads are redacted.

12. Troubleshooting

| Symptom | Likely cause | Action |

|---|---|---|

| `dimension mismatch` | Query/record vector length differs from collection | Verify the model and count values |

| Collection already exists | Reused name | Open/use it or select a migration name |

| Collection does not exist | Wrong database/name or missing artifact | Verify current database and backup |

| Duplicate vector ID | ID already stored | Use a unique ID; update/upsert is not available |

| No relevant results | Wrong model, metric, preprocessing, or data | Compare model configuration and evaluation queries |

| Trace unavailable | Debug has not emitted an event or path is not writable | Run `SHOW AI DEBUG ON`, an AI command, then `SHOW AI DEBUG STATUS` |

| Trace is empty after restart | New process/debug disabled | Set `ISQL_AI_DEBUG=1` before startup |

| `LIST AI TABLE is not implemented yet` | Reserved preview command | Track collection names operationally |

| `embedding provider not configured` | No usable provider is selected/configured for this operation | Create the required deterministic or OpenAI provider definition, verify its alias/model/credential configuration, and retry; use the deterministic provider only for offline tests |

Diagnostic sequence:


SHOW AI DEBUG ON;
SHOW AI STATUS collection_name;
SEARCH AI collection_name QUERY [/* correct dimension */] TOP 3;
SHOW AI TRACE LAST 50;
SHOW AI DEBUG OFF;

13. Compatibility and current limits

AI routing applies only to explicit AI command prefixes.
Legacy CREATE TABLE, INSERT, SELECT, VINSERT, and VECTOR remain on their existing paths.
Existing database homes do not require AI migration.
Numeric vector search continues to work with no embedding provider configured.
TOP is limited to 256 in the SQL adapter.
Dimension is limited to 4096 at both SQL and C API boundaries.
Metadata is limited to 2048 bytes, source keys to 255 bytes, filter text to

511 bytes, typed filters to 16 predicates, and session AI temp tables to 16.

Search is exact FLAT; IVF and HNSW are not implemented.
Metadata is opaque text with basic current filtering.
AI results support bounded session INTO TEMP tables and relational joins.
Provider-host integration, SQL provider administration, durable source

synchronization, MCP stdio/HTTP, and RAG/ASK AI are implemented and

qualified within the limits documented by their respective manuals.

For architectural roadmap and production gates, see

isql-AI\InternetSQL_MCP_OpenAI_RAG_Implementation_Plan.md and

isql-AI\InternetSQL_AI_Vector_Architecture_Completion_Plan.md in the release

workspace.

Back to contents

Manual 2 of 8

AI SQL User Manual

Internet SQL AI User Manual

Version 1.25 integrated vector-search milestone

1. What Internet SQL AI provides

Internet SQL AI adds persistent vector collections and similarity search beside the existing Internet SQL relational engine. It does not replace or reinterpret existing tables, transactions, joins, server operation, storage fields, or SQL syntax. Existing applications continue to use the same interfaces. AI commands are a separate command family and may be used in the same shell session as ordinary SQL.

This release supports:

persistent vector collections stored inside the selected database's table directory;
vector insertion with an unsigned integer ID and optional text metadata;
exact similarity search using cosine distance, Euclidean (L2) distance, or dot-product distance;
the same commands through an embedded shell or a remote Internet SQL server;
normal query-result rows for searches, so existing shell and remote result handling can display them;
a standalone isqlvec.exe utility for direct vector-store administration and diagnostics.

Provider-backed text embeddings, synchronization, and source-backed ASK AI

generation are available in embedded and TCP sessions. Numeric embeddings are

also accepted directly. Approximate HNSW/IVF indexes are not implemented; the

current search implementation is exact. See InternetSQL_AI_RAG_Manual.md for

the RAG security and result contract.

2. Start the shell

Embedded mode

The engine runs in the shell process. Replace the path with your database home:


x64bin\isqlsh.exe -dbhome C:\isqldbhome

For scripts, use quiet mode, stop on errors, and a command file:


x64bin\isqlsh.exe -q -e -dbhome C:\isqldbhome -cmd examples\ai_demo.sql

Remote mode

Start the server using its normal configuration or explicit database home and port:


x64bin\isqlserverd.exe -dbhome C:\isqldbhome -port 1973

Connect from another shell:


x64bin\isqlsh.exe -ip 192.0.2.10 -port 1973 -u kamranga -pwd manager

Use a non-default account and protected password in production. Network authentication and permissions remain those of the existing server.

3. Five-minute example

Enter the following statements. Every statement ends in a semicolon.


CREATE AI TABLE product_embeddings DIM 4 METRIC COSINE;

INSERT VECTOR INTO product_embeddings ID 1001
  VALUES [0.92, 0.10, 0.04, 0.20]
  META 'category=database;name=Internet SQL';

INSERT VECTOR INTO product_embeddings ID 1002
  VALUES [0.85, 0.18, 0.08, 0.25]
  META 'category=database;name=Vector Store';

INSERT VECTOR INTO product_embeddings ID 1003
  VALUES [0.02, 0.90, 0.70, 0.05]
  META 'category=hardware;name=Network Switch';

SEARCH AI product_embeddings QUERY [0.90, 0.12, 0.05, 0.22] TOP 2;
SHOW AI STATUS product_embeddings;
DROP AI TABLE product_embeddings;

Search returns four columns:

| Column | Meaning |

|---|---|

| `RANK` | One-based position after sorting by best match |

| `VECTOR_ID` | ID supplied when the vector was inserted |

| `SCORE` | Metric score: smaller is closer for COSINE/L2; larger is better for DOT |

| `SOURCE_KEY` | Stable key used to join a related relational row |

| `METADATA` | Optional text stored with the vector |

For cosine search, an identical vector has score 0. Interpret scores relative to other results produced with the same model, normalization, dimensions, and metric. Do not compare scores from unrelated embedding models.

4. Command reference

CREATE AI TABLE


CREATE AI TABLE collection_name DIM dimension METRIC metric;

Related collection syntax:


CREATE AI TABLE article_ai FOR TABLE articles KEY article_id
  TEXT (title,body) DIM 384 METRIC COSINE;

The source table, key, and listed text fields must already exist. InternetSQL

stores the relationship in the AI catalog without changing the source table.

collection_name identifies the persistent collection.
dimension is the exact number of floating-point values in every vector and must be greater than zero. The integrated adapter currently accepts at most 4,096 values per command.
metric is COSINE, L2, or DOT.

Examples:


CREATE AI TABLE support_articles DIM 384 METRIC COSINE;
CREATE AI TABLE image_features DIM 512 METRIC L2;
CREATE AI TABLE recommendations DIM 128 METRIC DOT;

Creating an existing collection is an error. Choose the dimension and metric to match the embedding model; changing either requires creating a new collection and reinserting vectors.

INSERT VECTOR


INSERT VECTOR INTO collection_name ID integer
  SOURCE_KEY 'stable relational key'
  VALUES [number, number, ...]
  META 'text';

To group vector inserts atomically, issue BEGIN AI TRANSACTION, perform the

inserts, and finish with the normal COMMIT or ROLLBACK command. The session

reads its own staged vectors, and an uncommitted disconnect rolls them back.

Standalone inserts remain autonomous for compatibility.

Each AI transaction is limited to one collection.

META is optional. IDs should be unique within your application. The number of values must match the collection dimension.


INSERT VECTOR INTO support_articles ID 42
  VALUES [0.12, -0.07, 0.44, 0.03]
  META 'article_id=42;language=en;area=backup';

Metadata is stored as bounded text. Semicolon-separated key=value pairs make

the fields usable by typed search predicates. Collections created with

FOR TABLE require a non-empty SOURCE_KEY on every new vector.

SEARCH AI


SEARCH AI collection_name QUERY [number, number, ...] TOP k
  [METRIC COSINE|L2|DOT]
  [WHERE text]
  [INTO TEMP table];

TOP must be from 1 through 256.
The query dimension must match the collection dimension.
Omitting METRIC uses the collection's configured metric.
WHERE accepts up to 16 metadata predicates joined by AND: =, !=,

<, <=, >, >=, IS NULL, and IS NOT NULL. Numeric-looking operands

compare numerically; other operands compare as text. A no-operator value

retains the original case-sensitive substring-filter behavior.

Put INTO TEMP after WHERE when both are used.

Examples:


SEARCH AI support_articles QUERY [0.10,-0.05,0.40,0.01] TOP 5;

SEARCH AI support_articles QUERY [0.10,-0.05,0.40,0.01]
  TOP 10 METRIC COSINE WHERE year>=2024 AND language='en';

The search is an exact scan. It favors correctness and predictable behavior over large-scale approximate-index performance.

Materialize hits and join them to the relational source:


SEARCH AI article_ai QUERY [0.10,-0.05,0.40,0.01]
  TOP 5 INTO TEMP ai_hits;
SELECT ai_hits.RANK,articles.title
  FROM ai_hits,articles
  WHERE ai_hits.SOURCE_KEY=articles.article_id
  ORDER BY ai_hits.RANK;

The named result uses the existing InternetSQL INTO TEMP table path. It is

session-scoped and is removed when its query object is released or the session

logs out. You may drop it earlier. A session can own at most 16 AI result tables.

SHOW AI STATUS


SHOW AI STATUS support_articles;

This verifies that the named collection can be opened. It does not perform a deep integrity scan or return row counts in this milestone.

DROP AI TABLE


DROP AI TABLE support_articles;

This permanently removes the collection file. Back it up before dropping it. Dropping an AI collection does not drop a relational table with the same name.

LIST AI TABLE is reserved but is not implemented in this milestone; it returns an explicit error.

AI debug trace

The interactive and scriptable shells can enable stage-by-stage diagnostics for

the current server process:


SHOW AI DEBUG ON;
SHOW AI DEBUG STATUS;
SHOW AI TRACE LAST 50;
SHOW AI DEBUG OFF;

The same commands work through all seven shipped shells and servers because the

facility is compiled into both existing engine artifacts (isqldll.dll and

isqllib.lib). Set the process environment variable ISQL_AI_DEBUG=1 before

starting any shell or server to enable tracing from its first AI command. This is

useful for GUI shells and unattended servers.

Trace events cover AI command routing, database/vector initialization, parsing,

validation, collection open/create/drop, insert commit, search execution, result

counts, result emission, and errors. The trace is stored as AI_TRACE.LOG in the

current database table directory. SHOW AI DEBUG STATUS reports the exact path.

The trace deliberately does not record vector values, metadata values, prompt or

document text, API credentials, or authorization headers. Provider integration

adds safe telemetry for provider/model configuration, batch sizes, input byte

counts, provider-reported token usage, embedding dimension/statistics/hash,

request timing, retrieval counts/scores, context sizing, generated-token usage,

and tool-call lifecycle.

Hosted model APIs do not expose model weights, attention matrices, hidden states,

or internal transformer activations. InternetSQL therefore cannot trace those

internals. A future local-model provider may emit provider-specific transformer

telemetry only when its runtime exposes it and the operator explicitly enables

that potentially sensitive, high-volume mode.

5. Using AI with relational SQL

AI collections and relational tables are deliberately parallel. Join them in your application by ID until a future table-valued search operator is available.


CREATE TABLE articles(
  article_id NUMBER(10) PRIMARY KEY,
  title CHAR(100),
  body TEXT(20000)
);

INSERT INTO articles VALUES(42,'Restoring a backup','...');
INSERT VECTOR INTO article_embeddings ID 42
  VALUES [0.12,-0.07,0.44,0.03]
  META 'language=en';

Application flow:

Convert the user's text to an embedding with the same model used for stored vectors.
issue SEARCH AI article_embeddings ... TOP 5;.
Read the returned VECTOR_ID values.
Fetch the corresponding relational rows, for example SELECT * FROM articles WHERE article_id=42;.
Optionally supply those documents to a language model to produce a grounded answer.

This keeps existing relational semantics unchanged and makes AI adoption additive.

6. Producing embeddings

Internet SQL AI currently stores and searches numeric vectors; it does not choose or run an embedding model. Your application should:

select one embedding model and record its name/version;
use the same model for documents and queries;
verify its output dimension matches DIM;
preserve vector element order and floating-point signs;
avoid mixing normalized and non-normalized vectors unless the model documentation permits it;
batch or cache embedding calls outside the database when appropriate.

Pseudocode:


vector = embedding_model.embed(document_text)
assert length(vector) == configured_dimension
sql = "INSERT VECTOR INTO docs ID " + document_id +
      " VALUES [" + comma_separated(vector) + "] META 'source=manual'"
execute(sql)

Treat generated SQL as data-bound input in application code. Validate IDs, vector counts, finite numeric values, and metadata; escape or reject quote characters according to your client API.

7. Metric selection

| Metric | Typical use | Score behavior |

|---|---|---|

| `COSINE` | Text embeddings and direction-based similarity | `0` is identical direction; smaller is closer |

| `L2` | Spatial or feature vectors where magnitude matters | `0` is identical; smaller is closer |

| `DOT` | Models explicitly trained for inner-product ranking | Larger dot product is ranked first |

Use the metric recommended by the model provider. Relevance thresholds are model- and dataset-specific; establish them with representative evaluation queries rather than copying a universal cutoff.

8. Standalone isqlvec utility

isqlvec.exe directly exercises the same storage modules and is useful for diagnostics. Display its exact syntax for the current binary:


x64bin\isqlvec.exe --help

Prefer SQL commands for normal integrated and remote operation. Do not modify the same collection concurrently through isqlvec and a running server.

For offline operations, select an explicit database home and optionally pass a

command script:


x64bin\isqlvec.exe --db C:\InternetSQL\data operations.txt

The operational commands are LIST, INSPECT, VERIFY, REPAIR, EXPORT,

and IMPORT. REPAIR opens the collection through normal WAL recovery and

reconciles its catalog definition; it does not guess or fabricate vector data.


LIST
INSPECT support_docs
VERIFY support_docs
REPAIR support_docs
EXPORT support_docs TO "D:\backups\support docs.ivecexport"
IMPORT "D:\backups\support docs.ivecexport" AS support_docs_restored
GET support_docs_restored ID 1001

Export writes a versioned, durable text interchange file containing the

collection definition, record IDs, vectors, source keys, and metadata. It

publishes only after flushing the complete temporary file and refuses to

overwrite an existing destination. Import validates bounded file and line

sizes, dimensions, metrics, metadata and numeric values, then inserts every

record in one AI transaction. Any malformed record rolls back the transaction

and removes the newly created collection. Paths containing spaces must be

double quoted. Protect exports as database data because metadata and vectors

may contain sensitive derived information.

9. Persistence, backup, and recovery

Each collection is stored as a .ivec file below the selected database's table directory. Include these files in database backups. For a consistent backup in this milestone, stop writersâ€”and preferably the serverâ€”before copying files.

The current vector store does not participate in legacy SQL COMMIT and ROLLBACK. A relational write and vector insertion are therefore not one atomic transaction. Applications that dual-write should use an ingestion state column or job queue so incomplete work can be detected and retried.

Example pattern:


1. Insert/update relational row with embedding_state='pending'.
2. Commit the relational transaction.
3. Generate and insert the vector.
4. Mark embedding_state='ready'.
5. Periodically retry rows still marked pending.

10. Errors and troubleshooting

| Symptom | Likely cause | Action |

|---|---|---|

| `collection already exists` | Name is already present | Open/search it or choose a new name |

| `dimension mismatch` | Vector length differs from `DIM` | Count values and confirm the embedding model |

| `unknown metric` | Metric is not COSINE, L2, or DOT | Correct the command |

| `file does not exist or cannot be opened` | Wrong database, missing file, or permissions | Confirm selected database and filesystem access |

| No relevant results | Wrong model/metric or unsuitable data | Verify model consistency and evaluate embeddings |

| Remote command rejected | Login, permissions, protocol, or server binary mismatch | Verify credentials and deploy matching DLL/server binaries |

| `LIST AI TABLE is not implemented yet` | Reserved future syntax | Track collections in deployment metadata for now |

Run the integrated regression tests from the source root:


powershell -NoProfile -File tests\regress_ai_sql.ps1 -Bin .\x64bin
powershell -NoProfile -File tests\regress_ai_sql.ps1 -Bin .\x64bin -Remote

11. Operational limits and security

Maximum adapter dimension: 4,096 floats.
Maximum TOP: 256.
Search is an exact scan; latency grows with collection size.
Metadata returned through integrated SQL is currently limited to 255 displayed characters.
Stored metadata is limited to 2048 bytes, stable source keys to 255 bytes,

filter text to 511 bytes, filters to 16 predicates, TOP to 256, and

dimensions to 4096.

AI create/drop/insert reuse the existing create-table/drop-table/insert

revocations. Related collection creation and search enforce existing

source-table and source-field SELECT revocations.

Collection state is process-global in the current vector core. Avoid simultaneous direct utility access and server access.
Vector pages use checksummed write-ahead logging, recovery, checkpoints, and

explicit AI transaction commit/rollback. This release does not provide

vector-file encryption at rest, per-tenant storage quotas, or a durable

approximate-nearest-neighbor index; exact scan remains authoritative.

Treat embeddings and metadata as potentially sensitive data. Apply filesystem access controls, authenticated server access, encrypted transport at the deployment boundary, backups, and retention policies appropriate to the source data.

12. Compatibility promise for this architecture

The AI implementation is additive. The legacy parser handles all prior SQL commands. Only statements beginning with the explicit AI command prefixes are routed to the AI adapter. Existing relational tables, inner and outer joins, transactions, local operation, parallel clients, remote servers, storage, and APIs remain on their established paths.

Future work should preserve that boundary: new AI capabilities should be introduced as additional commands, functions, or table-valued result sources, with compatibility regressions proving that existing behavior has not changed.

Back to contents

Manual 3 of 8

RAG Manual

InternetSQL Database-Native RAG Manual

InternetSQL 1.25 provides source-backed retrieval-augmented generation through

the existing isqldll.dll and isqllib.lib. No additional database DLL or

static library is required. The command works in embedded shells and through

the TCP server.

Complete example


CREATE TABLE support_articles
  (article_id char(20), title char(80), body char(255));

INSERT INTO support_articles (article_id,title,body)
VALUES ('KB-100','Reset a password',
        'Use the account page to request a password reset link.');

CREATE AI PROVIDER support_embed
TYPE 'openai' ENDPOINT 'default'
MODEL '<approved-embedding-model>'
CREDENTIAL 'OPENAI_PRODUCTION' TIMEOUT 60;

CREATE AI PROVIDER support_answer
TYPE 'openai' ENDPOINT 'default'
MODEL '<approved-generation-model>'
CREDENTIAL 'OPENAI_PRODUCTION' TIMEOUT 60;

CREATE AI TABLE support_ai
FOR TABLE support_articles KEY article_id TEXT (title,body)
DIM 1536 METRIC COSINE PROVIDER support_embed
CHUNK TOKENS 400 OVERLAP 40;

SYNC AI TABLE support_ai;

ASK AI USING support_ai
QUESTION 'How can I reset my password?'
TOP 5 GENERATOR support_answer WITH SOURCES;

Use models and vector dimensions approved for your deployment. Credentials are

referenced by alias and are resolved by the isolated provider host; never put an

API key in SQL, a provider definition, a command line, or a trace.

For offline installation tests, replace both provider types with

deterministic, use dimension 3, and insert or synchronize fixture data. The

deterministic provider is not intended to produce customer-facing prose.

Command contract


ASK AI USING ai_table
QUESTION 'quoted question'
TOP 1..32
GENERATOR provider_configuration
WITH SOURCES;

The AI table supplies the embedding provider and source relationship. The

named generator is an existing CREATE AI PROVIDER configuration. InternetSQL

uses the built-in, versioned grounded-v1 prompt template. Question size is

limited to 8192 bytes, selected context to 65536 bytes, and answer text to 255

bytes in this release. The answer limit matches the existing character-field

result format.

The result is a normal query table. Its columns are:

| Column | Meaning |

|---|---|

| `ROW_TYPE` | `ANSWER` for generated text or `SOURCE` for a citation |

| `ANSWER` | Generated text on the answer row |

| `REQUEST_ID` | Correlation identifier shared by all rows |

| `PROVIDER`, `MODEL` | Generator configuration and model |

| `TEMPLATE_VERSION` | Currently `grounded-v1` |

| `SOURCE_KEY`, `CHUNK_ID`, `RANK`, `SCORE` | Retrieval provenance on source rows |

| `INPUT_TOKENS`, `OUTPUT_TOKENS` | Provider telemetry on the answer row |

| `STATUS` | `COMPLETE` or `CONTEXT_TRUNCATED` |

Applications should display ANSWER and render each SOURCE row as a

clickable or auditable citation using the source key. Treat an answer as

untrusted model output even when citations are present.

Security behavior

InternetSQL checks SELECT permission on the configured source key and text

fields before embedding or generation. A denied user cannot cause source data

to be sent to a provider. Source text is marked as untrusted data, control

characters and prompt-like delimiters are neutralized, and generated text is

returned only as data. It is never executed as SQL and receives no MCP tools,

filesystem access, credentials, or policy authority.

Set ISQL_AI_DISABLE_PROVIDERS=1 (or the deployment-wide

ISQL_MCP_DISABLE_PROVIDERS=1) in the shell/server environment to disable

provider calls. Ordinary SQL and numeric SEARCH AI ... QUERY [...] remain

available. A server reads the variable from its own process environment, so

restart it after changing the setting.

Debugging and audit


SHOW AI DEBUG ON;
ASK AI USING support_ai QUESTION 'How do I reset my password?'
TOP 5 GENERATOR support_answer WITH SOURCES;
SHOW AI TRACE LAST 100;

RAG traces include routing, embedding start, generation start/completion,

request ID, byte counts, source count, model, template version, token counts,

and completion status. Questions, source bodies, generated answers,

credentials, and API keys are not written to the trace.

Common failures are explicit: missing relationship/provider, invalid TOP,

source permission denial, provider kill switch, missing generator, no matching

authorized source context, provider failure, or memory/resource limits.

Qualification

Run both supported paths from the source tree:


powershell -ExecutionPolicy Bypass -File tests\regress_ai_rag.ps1
powershell -ExecutionPolicy Bypass -File tests\regress_ai_rag.ps1 -Remote

The suite verifies deterministic answers and citations, malformed requests,

stored prompt injection non-execution, provider isolation, redacted traces,

embedded/TCP parity, and authorization before provider access.

Back to contents

Manual 4 of 8

MCP Manual

InternetSQL MCP Stdio Server Manual

For authenticated network deployment of the same tool core, see

InternetSQL_MCP_HTTP_Deployment.md. HTTP is an optional mode of the existing

isql-mcp.exe; stdio remains the default local transport.

Current scope

isql-mcp.exe is the policy-constrained Model Context Protocol adapter for InternetSQL.

It targets stable MCP revision 2025-11-25, communicates as newline-delimited

UTF-8 JSON-RPC on standard input/output, and connects to InternetSQL through

the existing isqldll.dll client API. It creates no additional DLL or static

library.

Without a named policy it retains the qualified M8 read-only behavior. With a

valid M9 policy it can further restrict reads and optionally expose one

separately classified, approval-controlled write tool.

Required environment

Run the adapter as a dedicated operating-system identity and supply its

least-privilege InternetSQL connection through the process environment:


$env:ISQL_MCP_SERVER = '127.0.0.1'
$env:ISQL_MCP_PORT = '1973'
$env:ISQL_MCP_DATABASE = 'support'
$env:ISQL_MCP_USER = 'mcp_support_reader'
$env:ISQL_MCP_PASSWORD = '<protected value>'
$env:ISQL_MCP_CLIENT_ID = 'support-assistant-production'
$env:ISQL_MCP_AUDIT = 'C:\InternetSQL\logs\mcp-audit.jsonl'
& 'C:\InternetSQL\bin\isql-mcp.exe'

Do not place the password in MCP tool arguments, an MCP client configuration

committed to source control, SQL files, trace logs, or command-line arguments.

The adapter does not support embedded owner-access mode. It requires an

authenticated TCP server session so normal InternetSQL authorization remains

authoritative.

Validate deployment configuration before starting an MCP client:


# Parses the policy, checks launch-identity bindings and reports enabled classes.
& 'C:\InternetSQL\bin\isql-mcp.exe' --check-policy

# Also opens and authenticates the configured InternetSQL TCP profile.
& 'C:\InternetSQL\bin\isql-mcp.exe' --check-config

Success prints one MCP_CONFIG_OK line and exits zero. Invalid policy,

identity mismatch, or failed database authentication prints

MCP_CONFIG_INVALID to standard error and exits 2. The report contains policy

capabilities and counts, but never the password, approval secret, provider

credential, SQL, or retrieved data. --check-policy is suitable for offline

CI validation; use --check-config on the deployed host as a readiness probe.

Available tools

isql_list_databases
isql_list_tables
isql_describe_table
isql_query
isql_vector_search
isql_text_search
isql_hybrid_search
isql_ai_status

isql_execute is absent by default. It is discovered only when a valid named

policy enables at least one write type and table and the write kill switch is

off. See [InternetSQL_MCP_Policy_Approval.md](InternetSQL_MCP_Policy_Approval.md)

for the complete format, identity binding, approval flow, and ACL guidance.

Named policy and approved write


$env:ISQL_MCP_POLICY = 'C:\InternetSQL\config\support-policy.json'
$env:ISQL_MCP_APPROVAL_SECRET = '<protected 32-byte-or-longer secret>'

The policy must match ISQL_MCP_CLIENT_ID, ISQL_MCP_DATABASE, and

ISQL_MCP_USER. Store the policy read-only for the MCP runtime identity. Give

the approval-state directory create-file permission but do not give ordinary

MCP clients permission to delete replay markers or read the approval secret.

Submit an allowed write without a token to receive its normalized digest:


{"name":"isql_execute","arguments":{"sql":"INSERT INTO ticket_feedback (ticket_id,rating) VALUES ('T-10','5')"}}

An operator or approval service running under the separate approval identity

passes that digest on standard input to the same executable:


$token = $digest | & 'C:\InternetSQL\bin\isql-mcp.exe' --issue-approval

The client resubmits the unchanged SQL with approval_token. The token is

expiring, single-use, HMAC protected, and bound to the policy, client,

database, and exact operation digest. Approval is consumed before the one

database attempt; failures are never retried as ambiguous writes.

Emergency controls:


$env:ISQL_MCP_DISABLE_WRITES = '1'
$env:ISQL_MCP_DISABLE_PROVIDERS = '1'

The provider switch leaves relational reads and offline numeric-vector search

operational. Neither switch is mutable through MCP.

isql_ai_status accepts a required collection and an optional configured

provider name. It returns collection status, provider health without a

credential alias, and the collection's bounded durable job rows.

Read-only query


{"name":"isql_query","arguments":{"sql":"SELECT ticket_id,subject FROM tickets ORDER BY ticket_id","max_rows":100}}

The adapter tokenizes and classifies exactly one SELECT statement. It rejects

stacked statements, comments, SELECT-INTO, DDL, DML, transaction, login,

provider, synchronization, file/import/export, and administrative keywords

before calling InternetSQL. Database table and row authorization is then

applied normally by the server.

Numeric and text search


{"name":"isql_vector_search","arguments":{"collection":"ticket_ai","vector":[0.1,0.2,0.3],"top":10}}


{"name":"isql_text_search","arguments":{"collection":"ticket_ai","text":"customer cannot reset password","top":10}}

Text search invokes only the provider explicitly bound to the collection.

Provider credentials remain in the isolated provider host.

Hybrid search


{
  "name":"isql_hybrid_search",
  "arguments":{
    "collection":"ticket_ai",
    "text":"customer cannot reset password",
    "top":10,
    "source_table":"tickets",
    "key_field":"ticket_id",
    "columns":["subject","status"]
  }
}

Hybrid search creates a uniquely named session temporary hit table, joins its

stable SOURCE_KEY to the requested source key, applies normal InternetSQL

SELECT authorization to the source projection, serializes the bounded result,

and drops the temporary table. All object and column names must be simple

InternetSQL identifiers.

Enforced limits

one JSON-RPC object per line;
1 MiB input message;
4096 JSON tokens and nesting depth 128;
one SQL SELECT up to 32 KiB;
256 result rows;
64 result columns;
16 KiB per cell;
1 MiB serialized database result;
4096 numeric vector dimensions;
8192 bytes of text-search input;
16 projected hybrid source columns.
one active database tool per MCP stdio parent; each tool is process-isolated.

Unknown JSON and tool fields are rejected. Duplicate keys, reused request IDs,

JSON-RPC batches, malformed UTF-8/JSON, non-finite vectors, unsafe identifiers,

and unknown methods/tools fail before database execution.

When ISQL_MCP_AUDIT is set, each tool call appends a flushed JSON-line event

containing UTC time, request correlation ID, MCP client identity, database,

tool, normalized object label, duration, row/byte counts, status, and bounded

error class. It excludes raw SQL, search text, retrieved cells, vectors,

metadata values, passwords, provider credentials, and approval tokens.

Verification

Run the offline protocol suite:


powershell -ExecutionPolicy Bypass -File internetsql_ai\mcp\test_mcp.ps1

Run the configuration/readiness diagnostic suite:


powershell -ExecutionPolicy Bypass -File internetsql_ai\mcp\test_mcp_config.ps1

Run the disposable TCP integration suite:


powershell -ExecutionPolicy Bypass -File internetsql_ai\mcp\test_mcp_database.ps1

Run the named-policy, privilege, approval, replay, and kill-switch suite:


powershell -ExecutionPolicy Bypass -File internetsql_ai\mcp\test_mcp_policy.ps1

The integration suite proves list/describe/SELECT, numeric search, provider

text search, hybrid joins, temporary cleanup, unknown-field rejection, and

that direct or stacked DDL through isql_query never executes. It also starts

a real long-running database query, cancels it with

notifications/cancelled, checks the bounded cancellation result, and proves

that the same MCP parent executes another authenticated tool afterward.

Back to contents

Manual 5 of 8

API-Compatible Provider Manual

InternetSQL OpenAI Provider Manual

Applies to: InternetSQL 1.25 source milestone M6

Audience: administrators, application developers, security reviewers, and support engineers

What is implemented

isql-ai-provider-host.exe contains an OpenAI provider for:

batched embeddings through POST /v1/embeddings;
text generation through POST /v1/responses;
configurable model identifiers and output dimensions;
strict bounded JSON/UTF-8 parsing and finite-float validation;
token usage and x-request-id telemetry returned over provider IPC;
authentication, configuration, rate-limit, transient, permanent, timeout,

cancellation, and protocol error classes;

bounded retry of retry-safe embedding calls, honoring a numeric

Retry-After value up to eight seconds;

TLS endpoint/host enforcement, explicit custom-host allowlisting, bounded

response sizes, and redacted errors.

The database engine never receives the API key and never makes the HTTP call.

It sends only a credential alias, provider/model identity, bounded content, and

operation limits over the local named pipe. The provider host resolves the

credential from its own process environment. OpenAI's current embeddings API

accepts one string or an array of strings and returns indexed float vectors;

the optional dimensions field is supported by text-embedding-3 and later

models. See the official [OpenAI embeddings API

reference](https://developers.openai.com/api/reference/resources/embeddings/methods/create).

Generation uses the Responses API and extracts output_text content from

structured message output. Model names deliberately have no compiled default.

See the official [OpenAI Responses API

reference](https://developers.openai.com/api/reference/resources/responses/methods/create).

Protected configuration

Configure the provider-host service identity, not an interactive database

session. For alias OPENAI_PRODUCTION, the host reads:


ISQL_AI_CREDENTIAL_OPENAI_PRODUCTION=<secret API key>

Alias rules are deliberately narrow: uppercase ASCII letters, digits, and

underscore. A credential value must never be placed in SQL, provider IPC,

KAMRANGA.CFG, database files, command-line arguments, trace output, test data,

or support bundles.

Validate the model name, alias syntax, and credential presence from the

provider-host service environment before enabling a provider:


isql-ai-provider-host.exe --check-openai text-embedding-3-small OPENAI_PRODUCTION

The command performs no network request. Success prints a single redacted

PROVIDER_CONFIG_OK line and exits zero. Missing/invalid configuration prints

PROVIDER_CONFIG_INVALID and exits 2. It reports only the model, alias,

presence, and implementationâ€”never the credential value. Run it under the

same operating-system identity and protected environment as the provider-host

service. SHOW AI PROVIDER <name> then verifies the complete engine-to-host

IPC configuration after the SQL provider definition is created.

Supported provider-host environment settings:

| Setting | Default | Rule |

|---|---:|---|

| `ISQL_AI_OPENAI_ENDPOINT` | `https://api.openai.com/v1` | Full API base URL |

| `ISQL_AI_OPENAI_ALLOWED_HOSTS` | empty | Comma-separated additional HTTPS hostnames |

| `ISQL_AI_OPENAI_TIMEOUT_MS` | `30000` | 1,000-300,000 ms |

| `ISQL_AI_OPENAI_MAX_RETRIES` | `2` | 0-4; embeddings only |

| `ISQL_AI_OPENAI_ALLOW_HTTP_LOOPBACK` | disabled | Test-only `127.0.0.1`, `localhost`, or `::1` HTTP mock access |

api.openai.com is accepted only over HTTPS. Additional production hosts must

be named explicitly and also use HTTPS. Plain HTTP is rejected except for a

loopback mock when the test-only switch is exactly 1.

On Windows, place these values in the protected service configuration for the

account that runs isql-ai-provider-host.exe; restrict read and service-control

permissions to administrators and that identity. Rotate a key by replacing the

protected value and restarting the provider host. Never send the old or new

value through an SQL statement.

Engine-side configuration

The host-provider adapter uses a configuration like this at the C boundary:


IAIP_HostProviderConfig cfg;
const IAIP_ProviderV1 *provider = 0;
IAIP_Error error;

memset(&cfg, 0, sizeof(cfg));
strcpy(cfg.registry_name, "support_embeddings");
strcpy(cfg.provider_name, "openai");
strcpy(cfg.model_name, "<operator-approved-embedding-model>");
strcpy(cfg.credential_alias, "OPENAI_PRODUCTION");
strcpy(cfg.transport.pipe_name, "isql-ai-support");
strcpy(cfg.transport.host_executable, "isql-ai-provider-host.exe");
cfg.transport.connect_timeout_ms = 5000;
cfg.transport.request_timeout_ms = 60000;
cfg.transport.start_if_missing = 1;

if (iaip_host_provider_create(&cfg, &provider, &error) != IVEC_OK)
    report_redacted_error(error.status, error.message);

The host process command line contains only the local pipe leaf and parent PID.

Provider, model, alias, content, and credentials are not command-line values.

SQL provider administration, SEARCH AI ... TEXT, and provider-bound

SYNC AI TABLE are available in embedded and TCP sessions. A provider

configuration can also be named by ASK AI ... GENERATOR; see

InternetSQL_AI_RAG_Manual.md for the grounded prompt and citation contract.

Runtime behavior

Embeddings

The adapter sends a JSON array in one API request, includes the requested

dimension, requests float encoding, and validates every returned index.

Returned objects may arrive in a different order; InternetSQL restores caller

order. Missing, duplicate, or out-of-range indices, wrong dimensions,

non-finite values, malformed JSON, invalid UTF-8, or oversized responses are

protocol failures and no vector is committed.

HTTP 408, 429, and 5xx embedding responses are retryable. The provider uses a

bounded attempt count and exponential delay, or a bounded numeric

Retry-After value when supplied. Other calls are not silently repeated.

Responses generation

The adapter sends optional system text and required user text as distinct input

messages, sets store to false, and applies a bounded output-token request.

It concatenates validated output_text content from message output into the

caller-owned bounded buffer. Generated text remains data; it is never executed

as SQL.

Generation is not automatically retried after an HTTP response because a

timeout can be ambiguous and a repeated call can create duplicate provider

usage. A retryable classification is returned for policy-controlled callers.

Telemetry and redaction

Successful provider responses can return:

provider-reported input and output token counts;
external x-request-id;
vector count/dimension and bounded output sizes through normal AI tracing.

The implementation does not log or return authorization headers, API keys, raw

vectors, full prompts, response bodies, or provider error bodies. A remote

failure reports its HTTP class and request ID so an administrator can correlate

it with provider support without exposing content.

Hosted APIs do not expose model weights, attention matrices, hidden states, or

internal transformer activations. InternetSQL traces observable request,

retrieval, token, dimension, latency, status, and output stages only.

Offline contract test

Run:


internetsql_ai\test\run_all_tests.bat

The test starts a loopback HTTP mock and the real isolated provider host. It

validates authorization placement, request paths and JSON, out-of-order vector

indices, response text, usage, request IDs, 429 classification, redaction,

strict JSON, invalid UTF-8, excessive nesting, and all earlier provider-host

fault isolation. It uses the literal test credential only; it makes no Internet

request.

Opt-in live smoke

Live tests are disabled unless every required setting is explicit:


set ISQL_AI_OPENAI_LIVE=1
set ISQL_AI_OPENAI_EMBED_MODEL=<approved embedding model>
set ISQL_AI_OPENAI_RESPONSE_MODEL=<approved Responses model>
set ISQL_AI_CREDENTIAL_LIVE_OPENAI=<protected disposable test key>
internetsql_ai\test\run_openai_live_smoke.bat

Use a disposable project/key with a small spend limit and no customer data.

The smoke sends one short embedding request and one short response request. It

prints only pass/fail and redacted diagnostics. Remove the credential from the

environment after the test. Normal CI must leave ISQL_AI_OPENAI_LIVE unset.

Troubleshooting

| Result | Meaning | Action |

|---|---|---|

| Configuration error | Missing alias/model or disallowed endpoint | Validate names and protected host environment |

| Authentication | HTTP 401/403 | Rotate or correct the alias value; do not paste it into logs |

| Rate limit | HTTP 429 | Observe retry/budget policy and provider limits |

| Transient | HTTP 408/5xx or transport failure | Retry only through the bounded policy |

| Timeout | WinHTTP deadline expired | Check network/TLS and adjust the bounded host timeout |

| Protocol | Malformed/oversized JSON, wrong indices/dimensions, invalid floats | Capture request ID and safe diagnostics; reject the result |

OpenAI recommends securing API access and planning production capacity and

limits; see the official [production best-practices

guide](https://developers.openai.com/api/docs/guides/production-best-practices)

and error-code guide.

Back to contents

Manual 6 of 8

Provider Host Manual

InternetSQL AI Provider Host Manual

Architecture

isql-ai-provider-host.exe is the isolated execution boundary for model

providers. Database-side client, framing, validation, and provider adapter code

remain compiled into the existing isqldll.dll and isqllib.lib; no new DLL

or static/import library is introduced.

The host is a separate executable because provider networking, credentials,

timeouts, and third-party failures must not share the ordinary SQL engine

process. The host supplies the deterministic offline fixture and an isolated

OpenAI embeddings/Responses adapter. See

InternetSQL_OpenAI_Provider_Manual.md for protected configuration and tests.

Local transport

Windows local named pipes only; remote pipe paths are rejected.
Version-1 frames have a fixed 28-byte explicitly encoded header and bounded

TLV payload.

Pipe names are restricted to letters, digits, dot, underscore, and hyphen.
The host creates one pipe instance with a protected ACL for SYSTEM,

administrators, and the object owner.

HELLO/ABI negotiation is mandatory before provider operations.
Request/response opcode and 64-bit correlation ID must match.
Responses require RESPONSE and FINAL flags, a single structured status,

valid payload length, and bounded fields.

TCP is not a provider-host transport. Network model access belongs inside the

host adapter, never between the database engine and provider host.

Process supervision

When explicitly configured with start_if_missing, the engine starts the host

hidden with only:


isql-ai-provider-host.exe --pipe <validated-local-leaf> --parent-pid <pid>

No credential, model input, prompt, vector, or authorization header is placed

in process arguments. The host monitors the parent handle and exits when the

engine disconnects or dies. A client-owned host that does not exit promptly is

terminated during bounded cleanup.

Deadlines and cancellation

Client reads and writes use overlapped I/O with an overall request deadline.

Timeout cancels the pending OS operation and marks that connection unusable.

Cancellation is also a versioned provider operation identified by correlation

ID. Provider calls are serialized per connection; concurrent/multiplexed

provider execution is reserved for adapters that require it.

After a timeout, protocol violation, correlation mismatch, or broken pipe, the

caller discards the connection and opens a fresh supervised host. Ordinary SQL

and numeric vector search do not depend on host availability.

Provider ABI adapter

iaip_host_provider_create() returns an IAIP_ProviderV1 implementation, so

database code uses the same health, batch embedding, generation, cancellation,

and shutdown API for in-process test providers and isolated providers.

The configuration stores:

registry/provider/model names;
a credential alias, never a credential value;
local pipe leaf and host executable path;
bounded connect and request deadlines;
whether supervised startup is permitted.

Embedding responses are rejected unless item count and dimension match and

every float is finite. Generation responses are rejected unless there is one

bounded output within both request and caller capacities. Usage counts,

external request IDs, and retryable status are parsed as bounded typed fields.

The OpenAI adapter additionally enforces strict JSON/UTF-8/depth checks,

response-index uniqueness, endpoint allowlists, TLS verification, response

limits, and redacted HTTP errors.

Error isolation

Automated fixtures verify rejection and cleanup for:

bad magic, version, opcode, flags, reserved fields, and truncated TLVs;
oversized frames and payload-length mismatch;
mismatched response correlation;
malformed response header;
host crash/broken pipe;
slow host/deadline expiry;
recovery by connecting to a new healthy host after all fault cases.

The OpenAI mock suite also covers request construction, credential placement,

out-of-order embedding indices, structured Responses output, usage and request

ID telemetry, strict JSON, HTTP 429 classification, and error-body redaction.

These failures produce structured status classes without exposing request

payloads. A host error does not disable embedded numeric search or legacy SQL.

Current commands and packaging

The executable is built by

internetsql_ai\provider_host\build_provider_host_x64.bat and placed in

x64bin. It is also included in build_all_x64.bat.

The executable is not intended to be launched manually. The C host-provider

adapter creates and supervises it; SQL provider DDL will expose validated

administration in M7. Do not put an API key on its command line, in SQL, in

KAMRANGA.CFG, or in the database.

Back to contents

Manual 7 of 8

Python API Manual

InternetSQL Python API Manual

Status: supported M13 binding for InternetSQL 1.25 Windows x64.

What is shipped

release_sdk/python/internetsql.py is pure Python source. It loads the existing

isqldll.dll with ctypes; it does not build or require a Python extension,

another DLL, static library, or import library. Python 3.10 or newer is

recommended.

Embedded connection


from internetsql import Connection

with Connection.embedded(
    r"C:\InternetSQLHome",
    dll_path=r"C:\InternetSQLSDK\bin\isqldll.dll",
) as db:
    result = db.execute("SELECT id,name FROM customers;")
    for row in result.rows:
        print(row["ID"], row["NAME"])

The database home must already contain a db directory. Embedded connections

use owner access by default; set owner_access=False when the application will

perform its own login/authorization workflow.

TCP connection


with Connection.tcp(
    "127.0.0.1", 1973, "appuser", "protected-password",
    dll_path=r"C:\InternetSQLSDK\bin\isqldll.dll",
    database="default",
) as db:
    print(db.execute("SHOW AI STATUS support_vectors;").status)

Do not put credentials in source control. Obtain them from the application's

protected configuration or operating-system secret facility. Login values

containing protocol delimiters are rejected.

Results and errors

execute() returns an immutable QueryResult containing:

command_code â€” the native positive command result;
columns â€” engine column names, normally uppercase;
rows â€” an immutable tuple of string mappings;
status â€” the engine's bounded status text;
scalar(default) â€” the first cell or a supplied default.

Native failures raise InternetSQLError. Invalid local arguments raise

ValueError or TypeError. Using a closed connection raises

ConnectionClosedError.

InternetSQL 1.25 does not export a native bind-parameter API. Passing

parameters raises ParameterBindingNotSupported instead of pretending that

string interpolation is parameter binding. Keep object names fixed and

allowlisted; quote_literal() is supplied for data literals and escapes single

quotes. Never use it to construct table, field, provider, or collection names.

AI/vector operations


db.execute("CREATE AI TABLE products_ai DIM 3 METRIC COSINE;")
db.insert_vector("products_ai", 1001, [1, 0, 0],
                 metadata="category=laser;year=2026")
hits = db.search_vector("products_ai", [0.9, 0.1, 0], top=5,
                        metadata_filter="year>=2025")

The convenience methods validate collection names, finite vectors, metrics,

TOP bounds, and filter length. Provider-backed text search, synchronization,

RAG, trace inspection, and MCP-related administration use the same documented

SQL through execute():


answer = db.execute(
    "ASK AI USING support_ai QUESTION 'How do I restore a backup?' "
    "TOP 5 GENERATOR support_generator WITH SOURCES;"
)
print(answer.rows)

Vector transactions


db.begin_ai()
try:
    db.insert_vector("products_ai", 1002, [0, 1, 0])
    db.insert_vector("products_ai", 1003, [0, 0, 1])
    print(db.search_vector("products_ai", [0, 1, 0], top=2).rows)
    db.commit()
except Exception:
    db.rollback()
    raise

One AI transaction is limited to one vector collection. Standalone vector

inserts remain autonomous. An uncommitted close or disconnect rolls back.

There is no claim of two-phase atomic commit between legacy relational files

and vector collection files.

Threading, memory, and limits

A Connection serializes its calls but is not a shared worker pool. Create

one connection per independent worker/session.

Results are copied into Python strings before the next native call.
max_cell_bytes defaults to 1 MiB and rejects a copied native cell beyond

that application boundary.

Text decodes as strict UTF-8 by default; supply the database's actual

encoding when using legacy data.

Always use a context manager or call close(). Embedded process-wide engine

logs/dump handles may remain owned by the loaded DLL until Python exits, so

do not delete an active database home from within that process.

Run examples and tests


python release_sdk\python\examples\embedded_ai.py C:\InternetSQLHome C:\InternetSQLSDK\bin\isqldll.dll
powershell -ExecutionPolicy Bypass -File release_sdk\python\run_tests.ps1

The executable tests cover relational rows, typed results, AI creation,

read-your-own-writes, vector commit/rollback, validation, exception behavior,

unsupported parameter binding, idempotent close, and closed-session errors.

Back to contents

Manual 8 of 8

Release Qualification

InternetSQL 1.25 AI Release Qualification and Rollback

This document defines the reproducible release gate for the InternetSQL AI,

vector, provider, MCP, hybrid-search, RAG, shell, and SDK additions. It does

not claim that InternetSQL replaces every Oracle deployment. Qualification is

limited to the capabilities, workloads, platforms, and bounds actually tested.

Release artifact policy

Build production artifacts with:


build_all_x64.bat release
powershell -ExecutionPolicy Bypass -File tests\stage_ai_sdk.ps1 -SkipBuild
powershell -ExecutionPolicy Bypass -File tests\test_ai_sdk_package.ps1

The first command builds the existing isqldll.dll, isqldll.lib, and

isqllib.lib with the release CRT and optimization settings. AI features are

compiled into those existing engine artifacts. The package gate fails if any

other DLL, static library, or import library appears in the SDK archive.

Run the complete matrix and retain a timestamped transcript with one command:


powershell -ExecutionPolicy Bypass -File tests\run_ai_release_qualification.ps1

Use -SkipBuild only when the immediately preceding recorded operation was a

successful build_all_x64.bat release. Evidence is written under

tests\evidence; do not place credentials or customer data in that directory.

Mandatory evidence matrix

| Area | Reproducible evidence |

|---|---|

| Native vectors and recovery | `internetsql_ai\test\run_all_tests.bat` |

| Embedded and TCP AI SQL | `tests\regress_ai_sql.ps1`, also with `-Remote` |

| RAG, citations, injection containment | `tests\regress_ai_rag.ps1`, also with `-Remote` |

| Transaction isolation and termination | `tests\regress_ai_transaction_isolation.ps1`, also with `-Remote` |

| MCP protocol/fuzz bounds | `internetsql_ai\mcp\test_mcp.ps1` |

| MCP database, policy, approval, audit | `test_mcp_database.ps1`, `test_mcp_policy.ps1` |

| MCP HTTP/session/rate/cancellation | `test_mcp_http.ps1`, `test_mcp_http_rate.ps1`, `test_mcp_http_database.ps1` |

| Provider alias readiness | `internetsql_ai\provider_host\test_provider_config.ps1` |

| Legacy relational/storage compatibility | `regress_groupby*.ps1`, `regress_storage.ps1`, `regress_product_identity.ps1` |

| Endurance/performance/privacy | `tests\regress_ai_release_quality.ps1` |

| Fresh archive and examples | `tests\test_ai_sdk_package.ps1` |

The native suite covers checksummed WAL truncation/replay/checkpoint,

multi-page atomicity, crash points before/after prepare/data/commit,

create/drop recovery, lock timeout, malformed provider frames and JSON,

provider crash/slow/oversize isolation, OpenAI mock contracts, retry classes,

redaction, job lease recovery, poison jobs, and concurrent claims.

Qualification workload and supported interpretation

The default bounded quality workload persists 2,000 16-dimensional vectors,

runs 200 exact top-five filtered searches, verifies the collection, rejects a

malformed import atomically, and checks diagnostic output for fixture secrets.

Its release thresholds are 90 seconds per load/search phase and 512 MiB peak

working set. On the 2026-08-17 qualification host it measured approximately

7.4 seconds load, 0.3 seconds for 200 searches, and 3.7 MiB observed peak

working set.

These numbers are a regression baseline for that small bounded fixture, not a

customer capacity promise. Search is exact. Customers must benchmark their

own dimensions, record counts, metadata filters, storage, concurrency, source

tables, network latency, and provider service-level limits before sizing a

production deployment.

Privacy gate

Release tests use unmistakable fixture credentials and fail if their exact

values appear in provider/MCP diagnostic or vector workload output. Provider

and MCP audits additionally test that raw SQL, retrieved values, prompts,

vectors, API keys, passwords, approval secrets, and approval tokens are absent.

This is defense-in-depth evidence, not a substitute for restricting log and

service-account permissions or reviewing customer data-retention obligations.

Upgrade and rollback procedure

Stop writers and provider/MCP services; confirm no isqlserverd, shell,

isqlvec, provider-host, or MCP process is using the selected database home.

Back up the complete database home, including relational files, .ivec

collections, catalogs, WAL/job journals, configuration, policies, and audit

state. Hash and test-read the backup before upgrading.

Preserve the previous signed binaries and configuration separately. Never

overwrite the only known-good package.

Install the new release into a new program directory. Run provider

--check-openai, MCP --check-policy/--check-config, isqlvec VERIFY,

relational smoke tests, numeric search, and one grounded RAG test.

If validation fails before writes resume, stop the new processes and restore

the previous binaries/configuration. The untouched database home remains

authoritative.

If writes occurred, stop every process and restore the complete pre-upgrade

database-home snapshot as one unit before starting the previous binaries.

Do not mix old relational files with new vector/catalog/WAL files and do not

delete or hand-edit WAL pages to force a downgrade.

Re-run VERIFY, relational integrity/smoke queries, user authorization,

provider/MCP diagnostics, and backup verification. Record the incident and

retain failed files for support analysis without including secrets.

AI transaction rollback protects one active collection transaction; it is not

a substitute for a whole-database pre-upgrade backup or point-in-time restore.

Release decision

Do not label a build qualified merely because it compiles. The release is

eligible only when the release build, every applicable matrix command, the

fresh archive gate, documentation review, and artifact hashes all succeed with

no unresolved critical or high-severity finding. Live OpenAI smoke testing is

optional and requires a disposable protected key; the deterministic and mock

provider gates remain mandatory and network-independent.

Back to contents

Customer Manual

Internet SQL AI 1.25 Customer Manual

1. Product overview

2. Programs with AI support

3. Starting isqlsh

3.1 Embedded mode

3.2 TCP client mode

4. Five-minute example

5. AI command reference

5.1 CREATE AI TABLE

5.2 INSERT VECTOR

5.3 SEARCH AI

5.4 SHOW AI STATUS

5.5 DROP AI TABLE

5.6 Reserved LIST AI TABLE

6. Debugging and trace support in every shell

6.1 Commands

6.2 Trace from process startup

6.3 Events currently traced

6.4 Redaction and model-internal limits

7. Producing embeddings and synchronizing source tables

8. Deterministic embedding API for tests

9. Relational relationship and hybrid-search pattern

10. Backup, restore, and removal

11. Security guidance

12. Troubleshooting

13. Compatibility and current limits

AI SQL User Manual

Internet SQL AI User Manual

1. What Internet SQL AI provides

2. Start the shell

Embedded mode

Remote mode

3. Five-minute example

4. Command reference

CREATE AI TABLE

INSERT VECTOR

SEARCH AI

SHOW AI STATUS

DROP AI TABLE

AI debug trace

5. Using AI with relational SQL

6. Producing embeddings

7. Metric selection

8. Standalone isqlvec utility

9. Persistence, backup, and recovery

10. Errors and troubleshooting

11. Operational limits and security

12. Compatibility promise for this architecture

RAG Manual

InternetSQL Database-Native RAG Manual

Complete example

Command contract

Security behavior

Debugging and audit

Qualification

MCP Manual

InternetSQL MCP Stdio Server Manual

Current scope

Required environment

Available tools

Named policy and approved write

Read-only query

Numeric and text search

Hybrid search

Enforced limits

Verification

API-Compatible Provider Manual

InternetSQL OpenAI Provider Manual

What is implemented

Protected configuration

Engine-side configuration

Runtime behavior

Embeddings

Responses generation

Telemetry and redaction

Offline contract test

Opt-in live smoke

Troubleshooting

Provider Host Manual

3. Starting `isqlsh`

5.1 `CREATE AI TABLE`

5.2 `INSERT VECTOR`

5.3 `SEARCH AI`

5.4 `SHOW AI STATUS`

5.5 `DROP AI TABLE`

5.6 Reserved `LIST AI TABLE`