SoftadminApi.AiSearchIndex_InsertUpdate

Inserts a new entry into the HNSW index (and full-text index, if one has been created) or updates an existing entry (see AI Search). Entries will be chunked, embedded and inserted into the index. You can both use this procedure for partial updates of the indexes when an entry has changed and for the initial batch import when you start using the AI Search and all rows in the table need to be imported.

It's never recommended to execute full reindex operations daily, Instead, update only modified records as required, in order to conserve server resources.

Expects one table with the data to insert and an optional table containing key-value pairs that will be stored. These pairs can then be used when searching to filter the results.

CREATE TABLE #SearchEntry
(
	RowId bigint NOT NULL,
	SecurityGroupId int NULL,
	Text nvarchar(MAX) NOT NULL,
	Weight int NOT NULL,
	ChunkPrefix nvarchar(300) NULL,
	TextDatetime datetime2(2) NULL
);

CREATE TABLE #SearchEntryKeyValue
(
	RowId bigint NOT NULL,
	EntryKey nvarchar(300) NOT NULL,
	EntryValue nvarchar(1000) NOT NULL
);

RowId

Your own primary key. This RowId will later be returned by SoftadminApi.AiSearchIndex_Search together with the schema and table name.

SecurityGroupId

If you use row-level permissions for search data, the id of a search group that the user must belong to (see SoftadminApi.UserSecurityGroup) to view this entry.

Leave as NULL to have everyone with permission to view the row's entity permission to view the row.

Weight

How important a match is relative to the others in the same index.

ChunkPrefix

Chunks will be prefixed with this value and a line break. Usually this will help LLMs to understand context and the embedding models to generate better embeddings.

Prefer descriptive and specific titles/sections over generic titles. For example: "Title: API Authentication, Section: Token Lifecycle" is better than "Introduction".

Leave as NULL to not prefix chunks.

TextDatetime

The datetime the text was stored or written. Only used to filter or sort search results.

EntryKey/EntryValue

Stores key-value pairs for each entry. An entry can have multiple key-value pairs. Can be used to filter search results. For example, "key: Project, value: Multisoft".