[Enh]: Embedding Phase 4: Table semantic search

## Phase 4: Semantic search over Redis vector indexes

This phase adds semantic search to DAB read operations by using Redis vector search to find candidate database keys, then using DAB’s existing query pipeline to return database records. The sample issue you provided uses the same pattern of introducing a focused config surface, showing JSON shape, and describing the runtime behavior. ([GitHub][1])

### Goal

Enable callers to pass natural language into REST, GraphQL, and eventually MCP read operations.

DAB will:

1. Embed the semantic search value.
2. Query a configured Redis vector index.
3. Read database key values from Redis results.
4. Collapse duplicate keys.
5. Query the database using the original request plus key narrowing.
6. Return normal DAB results with an optional semantic distance field.

Redis population, chunking, backfill, embedding jobs, and index maintenance are out of scope.

## Entity configuration

Semantic search is configured per explicit entity.

```json
{
"entities": {
"Product": {
"source": {
"type": "table",
"object": "dbo.Product"
},
"semantic-search": {
"enabled": true,
"redis-index-name": "idx:product-semantic",
"redis-index-type": "hash",
"redis-index-multiplier": 2,
"similarity-threshold": 0.8,
"input-description": "Searches product descriptions using natural language.",
"output-description": "Semantic distance score returned by semantic search."
}
}
}
}
```

### Properties

| Property | Type | Default | Min | Max | Required |
| ------------------------ | ----------------: | -----------------------------------------------------: | ----: | ----: | ----------------------------- |
| `enabled` | boolean-or-string | `false` | n/a | n/a | no |
| `redis-index-name` | string | none | n/a | n/a | only when `enabled` is `true` |
| `redis-index-type` | string enum | `hash` | n/a | n/a | no |
| `redis-index-multiplier` | integer | `2` | `1` | `10` | no |
| `similarity-threshold` | number | `0.8` | `0.0` | `1.0` | no |
| `input-description` | string | `Natural language value used for semantic search.` | n/a | n/a | no |
| `output-description` | string | `Semantic distance score returned by semantic search.` | n/a | n/a | no |

## JSON schema addition

Add `semantic-search` to explicit `entities.*.properties`.

```json
"semantic-search": {
"type": "object",
"description": "Semantic search configuration for this entity.",
"additionalProperties": false,
"properties": {
"enabled": {
"$ref": "#/$defs/boolean-or-string",
"description": "Enables semantic search for this entity. When false or omitted, semantic search keywords are invalid for this entity.",
"default": false
},
"redis-index-name": {
"type": "string",
"description": "Name of the Redis vector index used for semantic search for this entity."
},
"redis-index-type": {
"type": "string",
"description": "Redis index storage type used by the semantic search index.",
"enum": [
"hash",
"json"
],
"default": "hash"
},
"redis-index-multiplier": {
"type": "integer",
"description": "Multiplier applied to the requested result count when querying Redis before duplicate database keys are collapsed.",
"default": 2,
"minimum": 1,
"maximum": 10
},
"similarity-threshold": {
"type": "number",
"description": "Minimum Redis similarity value required for a semantic match.",
"default": 0.8,
"minimum": 0.0,
"maximum": 1.0
},
"input-description": {
"type": "string",
"description": "Description surfaced in generated API metadata for the semantic search input.",
"default": "Natural language value used for semantic search."
},
"output-description": {
"type": "string",
"description": "Description surfaced in generated API metadata for the semantic distance output field.",
"default": "Semantic distance score returned by semantic search."
}
}
}
```

Conditional validation:

```text
semantic-search.redis-index-name is required when semantic-search.enabled is true.
```

Startup error:

```text
semantic-search.redis-index-name is required when semantic-search.enabled is true for entity 'Product'.
```

## Redis connection prerequisite

Semantic search reuses the Redis connection configured under `runtime.cache.level-2`.

Caching does not need to be enabled.

Required when any explicit entity has `semantic-search.enabled: true`:

```json
{
"runtime": {
"cache": {
"level-2": {
"provider": "redis",
"connection-string": "@env('REDIS_CONNECTION_STRING')"
}
}
}
}
```

Not required:

```json
{
"runtime": {
"cache": {
"level-2": {
"enabled": true
}
}
}
}
```

Startup error:

```text
Semantic search requires runtime.cache.level-2.provider to be 'redis' and runtime.cache.level-2.connection-string to be configured.
```

The current DAB schema already has `runtime.cache.level-2.provider` and `runtime.cache.level-2.connection-string`, with Redis currently identified through the level-2 cache provider configuration.

## CLI syntax

Proposed CLI syntax follows existing dotted-path configuration style.

Enable semantic search:

```bash
dab configure --entities.Product.semantic-search.enabled true
```

Set Redis index name:

```bash
dab configure --entities.Product.semantic-search.redis-index-name "idx:product-semantic"
```

Set Redis index type:

```bash
dab configure --entities.Product.semantic-search.redis-index-type "hash"
```

Set Redis multiplier:

```bash
dab configure --entities.Product.semantic-search.redis-index-multiplier 2
```

Set similarity threshold:

```bash
dab configure --entities.Product.semantic-search.similarity-threshold 0.8
```

Set input description:

```bash
dab configure --entities.Product.semantic-search.input-description "Searches product descriptions using natural language."
```

Set output description:

```bash
dab configure --entities.Product.semantic-search.output-description "Semantic distance score returned by semantic search."
```

Configure Redis connection:

```bash
dab configure --runtime.cache.level-2.provider redis
dab configure --runtime.cache.level-2.connection-string "@env('REDIS_CONNECTION_STRING')"
```

## API syntax

REST uses snake case.

```http
GET /api/Product?$semantic_search=toys&$semantic_threshold=0.85&$first=10
```

GraphQL uses camel case.

```graphql
query {
products(
semanticSearch: "toys"
semanticThreshold: 0.85
first: 10
) {
items {
id
description
semanticDistance
}
}
}
```

MCP may expose equivalent semantic read arguments later. The feature is an entity capability, not an endpoint-specific capability.

## Effective values

For semantic threshold:

```text
1. Request value: $semantic_threshold / semanticThreshold
2. Entity config: semantic-search.similarity-threshold
3. Property default: 0.8
```

For semantic top:

```text
1. Request value: $first / first
2. runtime.pagination.default-page-size
3. Runtime pagination property default: 100
```

For Redis top:

```text
redis_top = first * redis-index-multiplier
```

`first` is validated against normal DAB page-size rules.

```text
minimum = 1
maximum = runtime.pagination.max-page-size
```

`redis_top` can exceed `runtime.pagination.max-page-size`.

```text
redis_top max = runtime.pagination.max-page-size * redis-index-multiplier
```

## Redis document requirements

Redis may use HASH or JSON.

Returned Redis records must include the real database primary key column names exactly, including case.

Example table key:

```text
ProductId
Region
```

Redis record:

```json
{
"ProductId": 123,
"Region": "West",
"embedding": [0.012, -0.873, 0.442]
}
```

Aliases are user-facing only. DAB internal semantic operations use real database column names.

DAB resolves keys from existing entity metadata or database metadata. No new `key-fields` property is introduced.

## Runtime workflow

```mermaid
flowchart LR
A[Validate request] --> B[Authorize read]
B --> C[Embed semantic value]
C --> D[(Query Redis)]
D --> E[Read database keys]
E --> F[Collapse duplicate keys]
F --> G[Build key predicate]
G --> H[(Query SQL)]
H --> I[Attach semantic distance]
I --> J[Return response]
```

Detailed behavior:

1. Validate semantic search is enabled for the entity.
2. Validate operation is read.
3. Validate request syntax when possible.
4. Validate caller authorization.
5. Embed the semantic input value.
6. Query Redis using:

```text
top = first * redis-index-multiplier
threshold = effective semantic threshold
```
7. Redis applies the threshold.
8. DAB reads database key values from Redis results.
9. DAB collapses duplicate key sets.
10. For duplicate key sets, DAB keeps the highest Redis distance value.
11. DAB builds an internal key predicate.
12. DAB queries SQL using the original request plus the key predicate.
13. DAB attaches semantic distance to returned rows.
14. DAB returns normal REST, GraphQL, or MCP-shaped results.

Zero Redis results return an empty successful response. DAB skips SQL query and post-processing.

REST:

```json
{
"value": []
}
```

GraphQL:

```json
{
"data": {
"products": {
"items": []
}
}
}
```

## Filtering and ordering

Semantic search narrows the existing query.

Effective logic:

```text
(user filter)
AND
(Redis candidate keys)
AND
(DAB authorization and policy filters)
AND
(database security)
```

Existing filter behavior does not change.

For single-column keys, DAB may use `IN`.

```http
$filter=id gt 100 and id in (101,102,103)
```

For compound keys, DAB uses grouped OR predicates.

```http
$filter=
id gt 100
and (
(key1 eq 1 and key2 eq 2)
or (key1 eq 3 and key2 eq 4)
or (key1 eq 5 and key2 eq 6)
)
```

Ordering rules:

```text
orderBy supplied:
preserve user order

orderBy omitted:
sort returned rows by semantic distance descending in memory

orderBy includes semantic_distance / semanticDistance:
reject request
```

Error:

```text
semantic_distance cannot be used in orderBy.
```

## Pagination

Pagination is not supported when semantic search is used.

`first` is used as semantic top. It does not create a second page.

Continuation tokens are rejected.

Error:

```text
Pagination continuation tokens are not supported when semantic search is used.
```

## `semantic_distance` virtual field

When `semantic-search.enabled` is true, generated API metadata includes a virtual semantic distance field.

REST:

```text
semantic_distance
```

GraphQL:

```text
semanticDistance
```

Type:

```text
nullable float
```

OpenAPI:

```text
semantic_distance: number | null
readOnly: true
```

GraphQL:

```graphql
semanticDistance: Float
```

Description comes from:

```text
semantic-search.output-description
```

Default:

```text
Semantic distance score returned by semantic search.
```

REST projection behavior:

```http
GET /api/Product?$semantic_search=toys
```

Returns `semantic_distance`.

```http
GET /api/Product?$semantic_search=toys&$select=id,description
```

Does not return `semantic_distance`.

```http
GET /api/Product?$semantic_search=toys&$select=id,description,semantic_distance
```

Returns `semantic_distance`.

Selecting `semantic_distance` without semantic search is invalid.

```http
GET /api/Product?$select=id,semantic_distance
```

Error:

```text
semantic_distance can only be selected when semantic search is used.
```

GraphQL exposes `semanticDistance` when `semantic-search.enabled` is true. Preferred behavior is to allow selection only when `semanticSearch` is present. If GraphQL cannot enforce that cleanly, fallback behavior is to return `null` when semantic search is not used.

## Read-only behavior

`semantic_distance` is read-only.

It is invalid in:

```text
create
update
patch
GraphQL mutation input
MCP write input
```

Error:

```text
semantic_distance is read-only.
```

`semantic_distance` is a virtual field and is outside normal field include/exclude permissions.

## Supported sources and operations

Supported:

```text
table with resolvable primary key fields
view with resolvable primary key fields
root entity read/query operations
```

Not supported:

```text
stored procedures
keyless views
create/update/delete
execute
GraphQL mutations
aggregate queries
relationship expansion
nested GraphQL relationship fields
```

Startup errors:

```text
Semantic search is only supported for entities with source type 'table' or 'view'.
```

```text
Semantic search requires entity 'ProductView' to have resolvable primary key fields.
```

Request errors:

```text
Semantic search is only supported for read operations.
```

```text
Semantic search is not supported for aggregate queries.
```

```text
Semantic search is not supported with relationship expansion.
```

```text
Semantic search is only supported at the root entity query level.
```

## Authorization

Semantic search never bypasses DAB authorization.

DAB applies:

```text
role permissions
field projection
database policy filters
normal SQL query filters
database security
```

Authorization checks run before Redis when possible. If the caller cannot read the entity, DAB returns the normal authorization response and does not query Redis.

Documentation note:

Semantic search is not ideal for entities where RLS or database policies may filter returned rows. It still works, but Redis may return matching candidate keys that are later removed by DAB policy logic or database security. This can produce fewer results than `first` requested without a separate explanation to the caller.

## Reserved names

Reserved names apply only when `semantic-search.enabled` is true.

REST reserved names:

```text
semantic_search
semantic_threshold
semantic_distance
```

GraphQL reserved names:

```text
semanticSearch
semanticThreshold
semanticDistance
```

Startup error:

```text
Entity 'Product' cannot enable semantic search because field name 'semantic_distance' is reserved.
```

## Error messages

Semantic keyword on non-enabled entity:

```text
Semantic search is not enabled for entity 'Category'.
```

Missing Redis configuration:

```text
Semantic search requires runtime.cache.level-2.provider to be 'redis' and runtime.cache.level-2.connection-string to be configured.
```

Missing Redis index name:

```text
semantic-search.redis-index-name is required when semantic-search.enabled is true for entity 'Product'.
```

Invalid Redis index:

```text
Semantic search index 'idx:product-semantic' for entity 'Product' was not found or could not be queried.
```

Invalid threshold:

```text
semantic_threshold must be a decimal value between 0.0 and 1.0.
```

Invalid multiplier:

```text
semantic-search.redis-index-multiplier for entity 'Product' must be an integer between 1 and 10.
```

Continuation token:

```text
Pagination continuation tokens are not supported when semantic search is used.
```

Semantic distance selected without semantic search:

```text
semantic_distance can only be selected when semantic search is used.
```

Semantic distance in orderBy:

```text
semantic_distance cannot be used in orderBy.
```

Read-only field in write payload:

```text
semantic_distance is read-only.
```

[1]: https://github.com/Azure/data-api-builder/issues/3331 "[Enh]: Embedding Phase 3: Parameter Substitution · Issue #3331 · Azure/data-api-builder · GitHub"