Search

Product search runs against Azure AI Search, populated by a pull indexer reading a SQL view. The index is derived state — rebuildable, never the source of truth.

Engine Azure AI Search
Feed Pull indexer over a SQL view
Refresh Every 5 minutes
Document grain One per colourway
Index fields 15

How it works

flowchart LR
    P[("Product table")] --> V["SQL view"]
    V --> DS["Data source"]
    DS --> IX["Indexer<br/>every 5 min"]
    IX --> I[("Index")]
    I --> Q["Commerce<br/>reads only"]

    style P fill:#f5f3ff,stroke:#7c3aed,stroke-width:2px,color:#2e1065
    style V fill:#f5f3ff,stroke:#7c3aed,stroke-width:2px,color:#2e1065
    style I fill:#f5f3ff,stroke:#7c3aed,stroke-width:2px,color:#2e1065
    style DS fill:#eef2ff,stroke:#4f46e5,stroke-width:2px,color:#1e1b4b
    style IX fill:#eef2ff,stroke:#4f46e5,stroke-width:2px,color:#1e1b4b
    style Q fill:#e8f4f9,stroke:#00415a,stroke-width:2px,color:#002230

Commerce never writes to the index. The indexer pulls on a schedule; the application only reads. So a product upsert can never fail because search was down — and a correction takes up to five minutes to appear.

Browse and direct lookup read the database. A product correct in browse and missing from search is the index catching up, not a fault.

One document per colourway

The thing to understand before anything else. A document is one master product in one colour — not one SKU. A shirt in black across five sizes is one document listing five sizes.


Key	`{masterProductNo}_{colour}`, lowercased, spaces and slashes replaced with underscores
`AvailableSizes`	Every size in that colourway, as an array
`Blocked`	True if any variant is blocked — one blocked size hides the colourway
`Category`	Matching category names as an array, falling back to the product's raw category

So result counts are colourway counts, size is a filter within a document, and a product with no master product number or no colour never appears at all — the view excludes it.

Index schema

Source-controlled in docs/azure-search/products-index.json, and deliberately not generated from code.

Field	Type	Key	Search	Filter	Facet
`id`	string	●
`ProductNo`	string		●	●
`Description`	string		●
`Brand`	string		●	●	●
`BrandCode`	string			●
`Category`	collection			●	●
`Colour`	string		●	●	●
`AvailableSizes`	collection			●	●
`Gender`	string			●	●
`FabricType`	string			●	●
`SleeveLength`	string			●	●
`StyleDescription`	string
`FabricComposition`	string
`StratificationCode`	string			●
`Blocked`	boolean			●

Suggester sg — infix matching over Description, Brand, ProductNo, Colour. Powers typeahead and autocomplete.

Scoring profile Search, applied by default, weighting Description ×2.

Facets offered: Brand, Category, Colour, Gender. AvailableSizes is filterable but deliberately not faceted — a blank search mixes clothing, footwear and workwear scales, where "10" is both a dress size and a shoe size.

Every query is filtered by Blocked eq false, plus a brand-entitlement clause built per request. Entitlement is applied inside the query so paging and facet counts stay correct.

Configuring it manually

Three objects, created in this order — the indexer references the other two, so it comes last.

Before you start


Database migrated	The view `vw_CatalogueSearch_ProductSearchDocuments` is created by a migration. Without it the data source has nothing to read
Network path	The search service must be able to reach SQL. If the database is only reachable over a private endpoint, the service needs a shared private link first — otherwise the indexer fails on its first run with a connection error
Names	Decide the three names up front and include the environment. They sit side by side in one service

In the portal: your search service → Search management in the left navigation. Data sources, Indexes and Indexers each have their own blade there.

1. Data source

Data sources → + Add data source.

Setting	Value
Name	Your chosen data source name
Type	Azure SQL Database
Connection	The Commerce database. Prefer the search service's managed identity over a username and password
Table or view	`Catalogue.vw_CatalogueSearch_ProductSearchDocuments`
Change tracking / high water mark column	`ModifiedAt`

ModifiedAt is what makes each run incremental — the indexer picks up only rows changed since the last run. Leave it set.

Note

Do not enable a soft-delete policy. There is none by design, which means a row leaving the view does not remove its document — a product that stops qualifying stays searchable until the index is rebuilt.

2. Index

Indexes → + Add index. Name it, then add the fifteen fields with the flags from the table above.

Faster and less error-prone: paste the JSON instead. Substitute {{INDEX_NAME}} in products-index.json and PUT it:

curl -X PUT "https://{service}.search.windows.net/indexes/{index-name}?api-version={api-version}" \
  -H "Content-Type: application/json" \
  -H "api-key: {admin-key}" \
  --data-binary @products-index.json

Doing it by hand means setting fifteen fields' flags correctly and the suggester and the scoring profile. The JSON already has all three.

PUT is create-or-update. Adding a field to an existing index works; changing an existing field's type or flags does not, and needs the index dropped and rebuilt.

3. Indexer

Indexers → + Add indexer.

Setting	Value
Name	Your chosen indexer name
Data source	The one from step 1
Index	The one from step 2
Schedule	Every 5 minutes

Important

Set the schedule yourself. The committed products-indexer.json has "schedule": null, so an indexer created from that file runs once and then appears broken.

Five minutes is what the running environments use and also the shortest interval Azure AI Search allows — asking for less is rejected. In JSON: "schedule": { "interval": "PT5M" }.

The file is not a faithful record of the deployed object. Do not "correct" it to null.

Creating the indexer runs it immediately.

4. Permissions

Two identities doing two different things. Conflating them is the usual cause of a half-working setup.

Identity	Needs	For
The search service	Read access on the Commerce database	The indexer pulling from the view
The Commerce application	Read-only data role on the search service	The application querying the index

The application never writes to the index, so query-only access is enough. It authenticates with its managed identity — no key.

5. Point Commerce at it

Configuration section Commerce.CatalogueDomain.Search:

Setting
`Endpoint`	The search service URL
`IndexName`	Which index to query — this is what lets environments share one service
`ApiKey`	Leave empty, so managed identity is used

Warning

Leave ApiKey empty in every environment. It is a long-lived shared secret that has to be stored and rotated, and managed identity has none of those problems. A key that reaches a commit must be treated as compromised and rotated.

6. Verify

In order — each check rules out the one before it.

Check	Expected
Indexer status	Succeeded, non-zero items processed
Index document count	Roughly master products × colourways. Not the SKU count
Search from the storefront	Results, with brand, category, colour and gender facets populated
A product you know is blocked	Absent
A brand the test customer is not entitled to	Absent for them, present for an unrestricted caller

The indexer's status blade is where to start when documents are missing — it reports the last run, item counts, and per-item errors with the offending key.

Succeeded with zero items means the high-water mark is already past everything in the view: expected on a re-run, suspicious on a first run.

Four things that will catch you


A field can exist everywhere and still be empty	The view, the index and the code field mappings must all agree, and nothing checks them. `FabricType` and `SleeveLength` are index fields the view never projects, so every document carries an empty value. `IsDefaultColour` and `SizeFit` are the reverse — emitted by the view, no index field, silently dropped
Adding a field does not backfill	Existing documents keep their old shape because their `ModifiedAt` has not moved. Reset the indexer or touch the rows
Any category edit re-indexes everything	`ModifiedAt` takes a global maximum over the category table, not just matching categories. Category matching is dynamic, so a matching-only maximum would skip exactly the products whose membership just changed. Bluntness bought correctness
Deletions do not propagate	No deletion detection policy. Removing a row from the view leaves its document behind

Adding a field, in the right order

Project it in the view — skip this and the field arrives empty with no warning
Add it to the index with the flags it needs; filterable and facetable are not defaults
Rerun the indexer, resetting it if existing documents need the value
Register it in code — field mapping to filter on it, facet list to facet on it, selected fields to return it
Confirm documents carry a value before exposing it

Table of Contents