Embeddings and semantic search over free-text columns

Last updated 20 October 2026

Free-text columns in our lists hold useful information that keyword search over SharePoint never surfaces well.

Keyword search matches words, and incident notes rarely use the same words twice. “No hot water on floor three” and “boiler lockout, east wing” describe the same fault, and SharePoint’s search will never connect them. Free-text columns in a facilities incident list, what the engineer saw, what they tried, what fixed it, are the most useful part of the list and the hardest to search.

Embeddings written by the sync

SPSync replicates the lists, free-text columns included, into Postgres with pgvector and refreshes them on a schedule. On every sync it writes one embedding per row from the text columns you choose, so there is no separate embedding job to build or schedule. The vectors sit in the same table as the rest of the replicated data, with an HNSW index for similarity search, so one statement can filter on site or status and order by vector distance.

Inside the incidents table

The free-text columns replicate as text columns next to everything else in the list: lookups as key columns, multi-value columns as child tables, and a stable primary key per item. The text columns you configure are joined, in order, into one piece of text per row, and its embedding goes into a single vector column, sp_embedding by default. A removed incident keeps its row and vector with sp_deleted_at set.

What re-embedding costs

Delta sync identifies which rows changed since the last run, and only those are re-embedded. Embedding cost and latency therefore follow how much text changes between syncs: twenty new or edited incidents in a day cost twenty embeddings that day. Set the schedule by how often the free text changes.

To set it up, point SPSync at a PostgreSQL 15 or later database with the pgvector extension available, pick the lists, and choose which text columns to embed and which embedding model to use. Your application then queries the relational columns and the vectors together. SharePoint to pgvector covers the full setup, the model configuration and the schema. While you build and test, the desktop app is enough; a production search needs the sync running somewhere that stays on.

The article comparing a flow, a script and replication for embedding SharePoint lists sets a Power Automate flow, a LangChain or LlamaIndex script and the Azure AI Search indexer against embedding on sync, using a facilities Incidents list and a similarity query in Supabase.

spsync auth login --client-id 00000000-0000-0000-0000-000000000000
spsync export-config --output replication.yaml --pipeline-name semantic_search_embeddings --prefix semantic_search_embeddings --destination-kind postgres --connection-string-env SPSYNC_DB_CONNECTION --dataset-name semantic_search_embeddings --client-id 00000000-0000-0000-0000-000000000000 --site-url https://contoso.sharepoint.com/sites/example --all-lists
spsync doctor replication.yaml
spsync run replication.yaml

pipeline_name: semantic_search_embeddings
prefix: semantic_search_embeddings
auth:
  mode: delegated
  client_id: 00000000-0000-0000-0000-000000000000
  tenant_id: common
destination:
  kind: postgres
  connection_string_env: SPSYNC_DB_CONNECTION
  dataset_name: semantic_search_embeddings
scopes:
  - site_url: https://contoso.sharepoint.com/sites/example
    mode: entire_site
reconcile_interval_days: 7
create_reporting_views: false

# every night at 02:00
0 2 * * * cd /opt/spsync && SPSYNC_DB_CONNECTION="$(cat /etc/spsync/db.env)" uv run spsync run replication.yaml >> /var/log/spsync/run.log 2>&1

# .github/workflows/spsync-run.yml
name: SPSync
on:
  schedule:
    - cron: "0 2 * * *"
  workflow_dispatch: {}
jobs:
  run:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: astral-sh/setup-uv@v3
      - run: uv sync
      - run: uv run spsync run replication.yaml
        env:
          SPSYNC_DB_CONNECTION: ${{ secrets.SPSYNC_DB_CONNECTION }}
          SPSYNC_LICENCE_KEY: ${{ secrets.SPSYNC_LICENCE_KEY }}

# azure-pipelines.yml
schedules:
  - cron: "0 2 * * *"
    branches: { include: [main] }
    always: true
steps:
  - script: uv sync
  - script: uv run spsync run replication.yaml
    env:
      SPSYNC_DB_CONNECTION: $(SPSYNC_DB_CONNECTION)
      SPSYNC_LICENCE_KEY: $(SPSYNC_LICENCE_KEY)

# Windows Task Scheduler action (see /docs/cli/automation/)
$env:SPSYNC_DB_CONNECTION = (Get-Content C:\spsync\db.env)
uv run spsync run C:\spsync\replication.yaml
schtasks /Create /SC DAILY /ST 02:00 /TN "SPSync" /TR "powershell -File C:\spsync\run.ps1"

# ~/Library/LaunchAgents/io.spsync.run.plist, then: launchctl load ~/Library/LaunchAgents/io.spsync.run.plist