MongoDB indexes¶

Application-managed MongoDB indexes are created only by new-client provisioning (POST /admin/api/v1/clients) or administrative reconciliation (POST /admin/api/v1/clients/indexes with apply=true). This includes classic, Atlas Search and Atlas Vector Search indexes. Skill imports, embedding regeneration, chatbot event writes and RAG synchronization never create them.

New tenant provisioning in src/admin/services/client_provisioning.py and the tenant repair endpoint both call index_reconciliation.ensure_tenant_collections_and_indexes. This shared engine converges indexes to the same catalog and event, RAG and skill definitions. Existing databases need explicit administrative reconciliation; changing the catalog or running a business workflow does not backfill indexes. PwC registrations receive the shared tenant catalog plus the compatibility score indexes described below.

Classic indexes are available as soon as MongoDB creates them. Atlas Search and Vector Search index builds are asynchronous: wait for them to become queryable before enabling the corresponding search workflows.

Client creation is repeatable: each call inspects resources again and creates only what is missing, without persisting progress. It preserves existing Config and registration values. The shared reconciler updates compatible definitions, replaces incompatible indexes and repairs failed same-type Search builds. Unresolved reconciliation errors prevent registration. common_data.ClientConfig registration happens after every collection and index command succeeds, before optional imports; Search command acceptance does not wait for queryability. The HTTP request waits for resource commands, registration and the resource confirmation email attempt, then returns HTTP 200. Only the initial import publication remains in the background, with a task ID for monitoring. With initialisation_import=false, no import task is created and its ID is null. Client creation reports each completed entity publication separately; these reports do not track consumer completion. A successful run has no duplicate final summary; failures and cancellation retain the terminal administrative report. Use initialisation_import=false when retrying only resource creation. With true, the initial entity imports run again after reconciliation succeeds.

The registration lookup and final insert-if-absent use the existing client_config_identity_unique index on (clientId, dataSource, environment). New tenant Config documents use a fixed _id and $setOnInsert, covered by MongoDB's built-in _id_ index. The existing singleton configuration read needs no additional index; collection/index inspection uses MongoDB metadata commands.

Skill embedding regeneration¶

The administrative regeneration endpoint filters incomplete documents in MongoDB when update_embeddings=true: absent/null/blank title_en, or an absent/null/ empty model vector. It streams a projection of identifiers and titles in bounded batches, without fetching existing vectors. Updates use the built-in _id index; translation-cache lookups use the existing unique Translations.title index.

The existing skill identity and Atlas indexes do not cover this maintenance predicate. No additional classic index is provisioned: the selected vector field varies by model, and empty-array/blank-title predicates still need document inspection. Indexing vector arrays would add multikey storage and write costs without covering the complete selection. MongoDB may scan the collection to evaluate the filter even though only incomplete documents reach the application. Representative query plans must be measured on the target deployment before making a claim about server-side scan reduction.

When no collection name is supplied, a single listCollections metadata lookup enumerates reference_data before processing the names sequentially. No additional document index is needed for this metadata lookup. Regeneration only updates titles and vectors; provision the configured vector index through the approved administrative workflows before searching a newly generated vector field. Writing the model vector also unsets an old persisted embeddings field when that is not the configured vector path, in the same update by _id. Imports apply the same cleanup when saving ada_002_vector by skill/client identity. These writes reuse the existing identity indexes and do not scan other records. Incremental regeneration still selects only incomplete records, so complete documents with this obsolete field require a scoped reimport or full regeneration.

Environment-scoped skill collections¶

Skills Library and Experience-to-skills query the shared reference collection skills_<environment>_smartmobility by default. The environment component is the single value resolved when FastAPI starts. An explicit custom collection is preserved, while legacy generated names beginning with sm_ resolve to the instance collection. A legacy name shaped as skills_<prefix>_<scope>_..._smartmobility becomes skills_<scope>_smartmobility; the deployment environment replaces a mutu scope. The same rule applies to skill search, Experience-to-skills, administrative imports and embedding regeneration.

Every such collection used with MongoDB must expose the Atlas Search index skills_autocomplete and the Atlas Vector Search index named by the active embedding-model configuration. Provisioning and reconciliation manage those indexes together with the classic external_id, client_id/external_id and reference_id/external_id indexes. A shared skill import only writes documents and performs scoped cleanup. Changing the generated collection name does not backfill indexes or wait for asynchronous Atlas index builds.

For skills_<scope>_smartmobility, skills_autocomplete maps only title as an autocomplete field. Single-skill lookup uses the original request text for autocomplete against title, while its English translation is used only for vector retrieval. The reference-database reconciliation described below updates an existing skills_autocomplete index whose managed definition still differs.

Experience-to-skills excludes userSkills during retrieval. Skill repository vector indexes therefore include external_id as a filter path alongside reference_id and language; skills_autocomplete explicitly maps external_id as a number for the exclusion clause. The legacy tenant collection Skills uses skillCode instead. Tenant provisioning and admin reconciliation reuse these identifier and vector-filter definitions.

Before enabling this prefilter on existing reference collections, inspect the plan with POST /admin/api/v1/clients/indexes?db_name=reference_data&apply=false, then apply it with apply=true and wait until both Atlas indexes are queryable. The plan covers every discovered skill repository whose collection name contains skills (case-insensitive), including environment and custom repositories. SkillsEmbeddings retains its dedicated title-based vector index. Review the complete plan: reference reconciliation also removes obsolete managed indexes, as described below. Omitting db_name processes configured client databases and reference_data together. Existing tenant Skills definitions are updated through the same endpoint with the tenant database selected, or through this combined run.

Operators can inventory both Search and Vector Search indexes in mongosh without modifying the database:

db.getSiblingDB("reference_data")
  .getCollection("skills_integration_smartmobility")
  .getSearchIndexes()

The result must contain skills_autocomplete plus the configured vector index, both in a queryable state. Replace the database, collection and environment with the instance values. Use getIndexes() on the same collection to inspect the three application-managed classic indexes.

Language-neutral skill autocomplete¶

skills_autocomplete uses lucene.standard with foldDiacritics=true on title and legacy skillTitle.en. Word boundaries and lowercase normalization are shared across languages, without French stemming. The existing edgeGram tokenization and 2-to-20-character gram bounds remain unchanged. Text queries preserve Unicode text and no longer enumerate French accents or shortened stems. The Search index still supplies the autocomplete access path and repository/ID prefilters; no additional classic or vector index is needed.

Deploy the updated application, then inspect the administrative plan:

POST /admin/api/v1/clients/indexes?db_name=reference_data&apply=false

The plan should include an update of skills_autocomplete for managed skill collections such as skills_prod_smartmobility. Review the complete plan because reference reconciliation also includes other managed index changes. Apply the reviewed plan with apply=true, then wait for status: READY, queryable: true and the active definition to show lucene.standard before checking search results. Existing French-analyzed indexes keep their old behavior until the new definition is built. Search requests and skill imports never initiate that update.

New clients receive this definition during provisioning. Existing tenant Skills collections use the same update policy through /clients/indexes or a repeated client-provisioning call. PostgreSQL search is unaffected by the MongoDB analyzer change.

Skill import reconciliation¶

After a successful source read and all checkpoint writes, an explicitly scoped skill import deletes documents matching client_id, reference_id, or both, whose external_id is absent from the SQL result. An empty result clears only that explicit scope; an import without either filter never deletes documents. Source-query errors are propagated, so a failed SQL read cannot clear a scope.

Reference-only imports additionally remove legacy documents with an absent or null client_id, including duplicates of IDs still present in SQL. The predicate is reference_id = requested_reference AND (external_id NOT IN source_ids OR client_id IS NULL/missing).

Client-scoped imports also remove unassigned legacy documents whose reference_id belongs to the distinct referentiel_id values in the SQL skills returned for that client. No additional SQL query is required. With both filters supplied, only the requested reference is eligible. References absent from the source result retain their unassigned documents, including when the result is empty; documents assigned to other clients remain untouched. Unfiltered imports never clean up. The source read and all checkpoint writes must succeed before cleanup begins.

The existing client_id/external_id index covers assigned-client cleanup. The non-unique reference_id_1_external_id_1 index covers reference-only cleanup and narrows client/reference cleanup before applying its client predicate. Its reference prefix also bounds legacy cleanup; the absent/null client condition is evaluated within that reference and does not justify a separate index. Client-only imports use an $or between stale assigned documents and unassigned documents within the resolved reference IDs; the two existing indexes cover those branches. This avoids scanning unrelated references for the maintenance query. An additional three-field index is not provisioned without representative plans demonstrating a benefit that justifies its write and storage cost.

src/entity/skill/indexes.py owns the reference cleanup index definition. It is shared by new-client provisioning and admin index reconciliation. Imports only inspect existing indexes: reference-filtered cleanup requires an index starting with reference_id, while client-only cleanup requires one starting with client_id and, when the SQL result supplies reference IDs, one starting with reference_id. A missing required index is logged and cleanup is skipped, including for empty SQL results. Cleanup failures are logged without undoing successful upserts or changing the import's success response.

No new MongoDB index is required.

Provision missing indexes before importing. For shared reference collections, use POST /admin/api/v1/clients/indexes?db_name=reference_data&apply=false to inspect the plan, then apply=true. Discovery includes custom collection names containing skills (case-insensitive); other arbitrary reference collection names are outside this catalog.

No representative deployment explain plan is assumed here; tests exercise the scoped predicates and preservation of unrelated records with an in-memory MongoDB implementation, plus catalog/provisioning and failure-path checks.

Index reconciliation endpoint¶

Administrators can inspect one managed database with:

POST /admin/api/v1/clients/indexes?db_name=<database>

Omit db_name to process every client database registered through clientDB in common_data.ClientConfig plus reference_data, including its managed skill collections. Each database is processed once, even if reference_data also appears in ClientConfig. This mode does not discover other unregistered databases; common_data still requires explicit selection. It reuses each database's reconciliation catalog and continues after a database-specific failure. The completed report aggregates all actions, whose database field identifies their source, and contains db_name: null.

The optional apply query parameter defaults to false, so both the single- database and cluster-wide modes initially produce a read-only plan. With apply=false, the request waits for completion and returns HTTP 200 with the JSON report: db_name, apply, error_count, counts and actions. No task or email is created, and configured email recipients are not required. MongoDB inspection runs outside the event loop. Resource-level errors remain in the report, so check error_count even when the HTTP status is 200. Unexpected errors that escape reconciliation fail the request without exposing driver details.

With apply=true, the endpoint returns HTTP 202 with a task ID and runs in the background; the completed report arrives by email with the same fields. Recipients come from the configuration template. See background operations.

For common_data, reference_data and managed tenant databases, apply=true converges every catalog-managed collection to the current definition. Client creation uses this same reconciliation engine, including on repeated calls. The report describes keep, create, update and drop operations:

• Create missing collections and indexes.
• Update Atlas Search and Vector Search definitions in place when the index type is unchanged, including failed builds. A failed update is reported as an error; it does not trigger an automatic drop/recreate fallback.
• Update single-field TTL settings with collMod when all other classic index options match. Replace other incompatible classic index definitions.
• Replace a Search index whose type changed by dropping it, waiting up to 60 seconds for its name to disappear, then creating the desired type. A shorter MONGO_PROVISIONING_TIMEOUT_SECONDS deadline still applies. A timeout is an error and dependent creation is not submitted; a later call can resume repair.
• Drop obsolete or duplicate indexes within the catalog-managed collections. The MongoDB _id_ index is always retained. Collections outside the catalog are not modified, and collections themselves are never deleted.

PwC databases use a narrower ownership policy because other applications may manage indexes in the same collections. Classic and Search/Vector indexes whose names are outside the catalog are preserved, including equivalent classic aliases. An equivalent classic index already present under another name satisfies the requirement. A conflicting external classic index is preserved and reported as an error requiring manual review; independent indexes continue. Definitions with catalog-owned names can still be updated or replaced. The same policy applies during new-client provisioning and repeated creation requests.

apply=false returns the same planned actions without writing or waiting for index deletion. error covers inspection failures, failed operations and PwC external-index conflicts requiring manual review, not ordinary correctable definition differences. Driver details remain in application logs. A definition matching Atlas's latestDefinition while building is kept, so repeating a call does not restart the same build. Command acceptance is not a queryability guarantee: wait for the active definition and queryable state before using a changed index. The report does not wait for Search/Vector build completion.

When db_name is explicit, the endpoint accepts common_data, reference_data and tenant databases registered in common_data.ClientConfig, including PwC. For a tenant, the service first reads its registrations by clientDB: if any registration has dataSource=pwc (case-insensitive), it selects the PwC catalog and preservation policy. The database name itself is not used to infer the source. reference_data always uses its dedicated catalog, even if registered with a PwC source. When db_name is omitted, configured client databases and reference_data are selected, even when no clients are registered. Both modes use the instance's connected MongoDB cluster; they do not open or discover separate PwC staging or production connections. Collections are limited to those represented by the provisioning index catalog; the endpoint does not restore missing configuration documents or business data. Atlas Search and Vector Search creation and updates remain asynchronous after MongoDB accepts a request.

Execution order and progress¶

Within each database, reconciliation first prepares any required missing collections, then submits all managed Search and Vector Search changes. It next processes classic indexes on at most three collections concurrently. A completed collection immediately frees a slot for the next one, even if another collection is still building an index. Dependent operations inside each collection, such as dropping a conflicting index before its replacement, remain sequential. Client provisioning and reconciliation use this same execution order. Configured databases themselves are processed sequentially.

The limit applies to one reconciliation call. Dry runs stay sequential and never write. Async workers share the lifespan-owned PyMongo async client and retain request/client logging context. Each worker has its own report; the final actions remain ordered by collection, with classic index actions before Search actions. No index definitions or query access paths change as part of this scheduling. Cancellation stops and drains these workers; a server-side build already accepted by MongoDB may continue after cancellation.

Progress logs show the phase and collection X/Y, and each driver inspection or mutation logs its database, collection, index, operation, PID, status and elapsed duration, retaining request/client/task context. An operation start without its matching finish identifies a driver call that is still waiting. Errors are logged immediately rather than delayed until all collections finish. An index failure is recorded and independent subsequent indexes continue, including those in the same collection. If a conflicting index cannot be dropped, its dependent replacement is skipped while other indexes continue. Unexpected collection-phase failures are also isolated; the remaining collections and the other phase still run. Cancellation and shutdown signals retain their propagation.

In apply mode, the HTTP response acknowledges the background task with 202. Its completion email includes the existing JSON report after all classic operations finish. Classic createIndexes calls wait for MongoDB build completion, while Search commands return after acceptance. Each managed index inspection or mutation has a fresh driver deadline configured by MONGO_PROVISIONING_TIMEOUT_SECONDS (default 120 seconds). Creation also bounds configuration and registration access. Existing shorter connection timeouts remain in effect. A timeout or concurrency limit does not cancel a server-side index build or remove MongoDB's own build concurrency/replication constraints; three waiting classic operations can occupy all available slots.

Classic indexes¶

The new-client catalog includes the query paths used by:

• common configuration, API-key lookup and process locking;
• users, jobs, opportunities and skill imports;
• JOM/JPM score persistence, reverse-side cleanup and latest-score lookup;
• chat history, conversations, feedback events and uploaded documents;
• RAG manifests and RAG chunks.

Identity and pair indexes are unique where the application writes one logical record per identifier. Events.createdAt retains its existing TTL policy.

Feedbacks and Notifications are no longer part of the provisioning or reconciliation catalogs. Existing collections and indexes are not dropped by this code change. Consumer-format JOM feedback continues to update user preferences and matching scores through its existing workflow.

Translation-cache writes use unordered atomic upserts keyed by title, which is covered by translations_title_unique. Concurrent entity imports therefore reuse the first stored translation instead of failing with a duplicate-key error. Translation remains a best-effort enrichment: a MongoDB cache-write failure is logged without aborting the entity standardization that already produced the translated value.

JobOffers¶

The JobOffers collection in the tenant database requires:

embeddings_index contains two vector fields, both with 1,536 dimensions and cosine similarity, plus every field used by vector-search prefilters:

{
  "fields": [
    {"type": "vector", "path": "embeddings", "numDimensions": 1536, "similarity": "cosine"},
    {"type": "vector", "path": "InferredTitle_embeddings", "numDimensions": 1536, "similarity": "cosine"},
    {"type": "filter", "path": "status"},
    {"type": "filter", "path": "jobOfferId"},
    {"type": "filter", "path": "skillsInformation.title"}
  ]
}

There is no separate InferredTitle_embeddings_index: inferred-title searches use the InferredTitle_embeddings path inside embeddings_index. An older standalone index with that name can be removed after the combined index is ready.

Without DATABASE_URL, opportunity standardization generates or refreshes both MongoDB fields together when the source title or inferred title changes:

• embeddings represents the source opportunity title;
• InferredTitle_embeddings represents the standardized inferred title.

With DATABASE_URL, targeted PostgreSQL triggers and the opportunity embedding worker store these vectors and the reconstructed-posting vector on custom_job. The standardization workflow is not used in PostgreSQL mode; the live relational projection is described in the architecture overview. PwC search continues to use the MongoDB indexes described here. Chatbot opportunity search instead uses the relational full-text and HNSW indexes in PostgreSQL mode, with the same local 50/50 weighted RRF as Atlas.

The autocomplete definition is maintained in src/common/connectors/mongodb/atlas_search_index_definitions.py and covers the fields used by opportunity hard filters and full-text search. Do not duplicate the full JSON mapping in deployment code; reuse that source definition.

Other matching indexes¶

New-client provisioning also creates vector indexes for opportunity-description embeddings, jobs, trainings (including enriched titles), skills and the shared skill repository. Their names, vector paths, dimensions and filter paths are resolved from AI configuration unless a workflow requires a fixed index.

The Users collection also receives the users_search_index Atlas Search index used by Talent Finder. Chat history, conversations, skills, trainings and RAG chunks receive their respective Search indexes during the same provisioning flow.

PwC compatibility score indexes¶

Tenants provisioned or reconciled with dataSource=pwc also receive the classic indexes used by the migrated score endpoints:

The equality prefixes serve the entity lookup; the descending score suffixes serve Skill and Motivation ordering. Overall ordering remains an application-side calculation because the weights can be supplied per request. The joins used by department, priority and opportunity-type filters reuse the unique JobOffers.jobOfferId and Users.userId indexes.

The offer index on UsersMatchingScore serves opportunity deactivation cleanup, including bounded $in deletions during status synchronization. The existing user-first score indexes cannot serve this lookup by offer ID. The counterpart OpportunityMatchingScore indexes already start with jobOfferId, so no redundant single-field index is added there. New PwC clients receive this index during provisioning; existing clients require POST /admin/api/v1/clients/indexes reconciliation. Status synchronization does not create indexes.

Use POST /admin/api/v1/clients/indexes?db_name=<pwc-database>&apply=false to review the plan for an existing PwC database, then use apply=true to apply it. Omitting db_name includes registered PwC databases in the global run. Review any reported external-index conflicts before relying on the missing indexes.

Hybrid retrieval¶

Full-text and vector queries execute as independent MongoDB aggregations. AI Hub runs them concurrently and performs weighted reciprocal-rank fusion locally. MongoDB $rankFusion is not used. All Atlas full-text and vector stage builders live in src/common/connectors/mongodb/atlas_full_text_search.py and src/common/connectors/mongodb/atlas_vector_search.py. Hybrid execution is coordinated by atlas_hybrid_search.py, while local ranking remains in search_fusion.py. This retains the provenance and individual text/vector scores used for debugging.

Technical classification eligibility (NB-9232)¶

Users and JobOffers include the managed classic indexes user_technical_classification_status_idx and job_offer_technical_classification_status_idx, both on (technicalClassification ASC, status ASC). JOM uses classification membership plus active status without sorting. The existing status-only/expiration indexes do not narrow the classification predicate; the new indexes support both matching directions, including large candidate-user collections. They are provisioned through client creation or the Admin index reconciliation endpoint, never by imports. Existing tenants require explicit reconciliation. No live index creation is performed by deploying these definitions.

User matching freshness¶

job_offer_matching_updated_idx on JobOffers.matchingUpdatedAt DESC and job_matching_updated_idx on Jobs.matchingUpdatedAt DESC support one latest source-date lookup per user/product. These lookups cover the whole tenant catalogue, including inactive targets. matchingUpdatedAt is an application-owned BSON UTC date written during imports, updates and deactivation; it is distinct from source-provided timestamp strings. Legacy targets participate in the first complete calculation, and subsequent application changes stamp the field.

Users.matchingState reads/writes reuse the existing unique userId index. Checking that at least one score exists reuses the existing score indexes starting with userId; no additional score index is needed. Reconcile the two catalogue indexes for existing tenants through the admin index endpoint. Physical target deletion invalidates that product's references with a tenant-wide update of Users; a selective index cannot avoid this operation because every user's catalogue has changed. It does not enqueue calculations. See user matching freshness for the complete-run and always-retry-empty-result rules.