full-text-search

Imported from lobehub/lobehub (.agents/skills/full-text-search/SKILL.md). Install upstream with npx skills add lobehub/lobehub --skill full-text-search. Copyright stays with the author.

Product Full-Text Search

This skill covers search over LobeHub-owned product data such as agents, topics, messages, files, knowledge bases, documents, chat groups, and memories. It does not cover the agent's external web search providers under apps/server/src/services/search/ or builtin web-browsing tools.

Architecture

Product reads follow one stable path:

router/service -> createFtsSearchRepo -> FtsSearchRepo -> selected backend -> existing result schema

• apps/server/src/services/ftsSearch/ owns request-scoped provider selection, Elasticsearch configuration, its HTTP client, and backend telemetry. Routers and domain services must call createFtsSearchRepo; they must not construct providers themselves.
• packages/database/src/repositories/ftsSearch/ owns the provider-neutral contract and the concrete PostgreSQL and Elasticsearch implementations. Keep public result shapes stable in FtsSearchRepo.
• packages/types/src/ftsSearch.ts owns the shared searchable-entity list and search domain types.
• packages/database/src/repositories/ftsSearchDocument/ owns Elasticsearch document schemas, mappings, queryable fields, and source-to-document projection.
• packages/database/src/schemas/ftsSearchSyncOutbox.ts and packages/database/src/repositories/ftsSearchSyncOutbox/ own durable change capture, claims, retries, dead letters, leases, revision fences, and capture-definition validation.
• scripts/elasticsearchReindex/ owns the resumable full-backfill command and its operational runtime. Shared database source queries and document construction remain in packages/database/src/repositories/ftsSearchDocument/. apps/server/src/services/ftsSearchSync/ and scripts/elasticsearchSync/ own continuous incremental draining.
• packages/env/src/ftsSearch.ts owns generic Elasticsearch environment variables.

Provider and Permission Invariants

• FTS_SEARCH_PROVIDER is a deployment-level provider selector with current values pg_search, elasticsearch, and pg_like. It is not a feature flag or a user rollout. Add another enum value only when its provider covers all entities in FTS_SEARCH_BACKEND_ENTITIES end to end, including mode: 'candidates' when the provider enables ftsSearchCandidateEnabled.
• Provider errors, missing configuration, and unsupported candidate behavior must remain visible. Never silently retry through another provider. pg_like is an explicitly selected lightweight provider, not an implicit fallback: a missing extension or unreachable service must still fail.
• packages/database/src/repositories/ftsSearch/postgres/ owns the query, permission, pagination, and hydration layer shared by PostgreSQL providers. pgSearch/ and pgLike/ own only their provider adapter, dialect, and provider-specific candidate behavior. Neither provider directory may import the other, so either provider can be retired without owning shared functionality.
• Before selecting Elasticsearch, require coverage tests proving that it supports every entity in the provider-neutral backend contract. Do not add per-entity routing between providers.
• Preserve userId, workspaceId, and caller-agent visibility throughout every provider. Candidate retrieval must not broaden the caller's scope.
• Where Elasticsearch only supplies candidate IDs, PostgreSQL hydration and parent checks remain the authoritative permission boundary. Do not return raw Elasticsearch hits directly when the existing result path requires database authorization or hydration.
• Provider implementations return the existing hydrated response types and ordering contract. Do not make routers understand provider-specific result shapes.

Changing a Searchable Entity

Scope for pg_like

pg_like primarily serves individual users with small datasets. Review it for correct matching, permissions, and usable result ordering at that scale. For richer search quality or larger datasets, recommend self-hosted Elasticsearch or Elastic Cloud. Do not add indexes or change the database schema as part of pg_like optimization. Keep repairs bounded; distinguish new implementation bugs from shared provider limitations and deliberate lightweight-search trade-offs. Do not infer a personal-user performance problem from large shared development datasets without representative measurements.

Entity changes

Treat an entity addition or projection change as one cross-layer change. Inspect and update every applicable item:

1. FTS_SEARCH_DOCUMENT_ENTITIES and shared request/result types.
2. Search document schema, mapping, text fields, filters, fixtures, and mapping parity tests.
3. FtsSearchDocumentBuilder, including soft deletion and fanout from related source rows.
4. PostgreSQL and Elasticsearch backend behavior, permission hydration, pagination, and ranking.
5. Capture functions/triggers or fanout queries in captureInfrastructure.ts.
6. Reindex checkpoints, Outbox draining, metrics, and self-host documentation.

Schema fields, mappings, builders, and fixed fixtures must agree. Current mapping fields plus explicit FTS_SEARCH_RETAINED_SOURCE_PROPERTIES must equal the current Zod fields, without overlap. Retained source properties keep older open indexes writable during field removals; keep the builder producing them until those indexes are closed. They must not appear in current query metadata. A field outside the document schema must not appear in a mapping or query field list.

Elasticsearch multi_match query length is bounded by a shared leaf-clause budget divided by the selected query-field count. Adding a field reduces that entity's safe query length, and changing a query analyzer to emit multiple terms per Unicode code point requires revisiting the budget and its field-count regression tests.

Capture, Reindex, and Sync

• Regular database migrations own durable schema: the revision sequence, Outbox table and indexes, and schema-managed source indexes such as the Memory fanout GIN index.
• Capture functions and triggers are installed only when an operator explicitly enables the Elasticsearch path. installCaptureInfrastructure() is transactional, definition-checked, idempotent for an exact installation, and fail-closed for partial or altered definitions.
• Do not install capture for every PostgreSQL-only instance. Do not move a normal schema index into the runtime installer merely because it supports capture.
• The Outbox coalesces by (entity, document_id). A newer capture resets retry/dead-letter state and allocates a new revision only after locking the conflicting row, preserving same-document commit order.
• Sequence allocation is non-transactional. Use the existing write fences and committed revision boundary; never treat last_value alone as proof that all earlier Outbox rows are visible.
• Claims use the precise lease timestamp as a fencing token. A stale worker must not acknowledge, fail, or release work reclaimed by another worker.
• Permanent failures and exhausted retries become durable dead letters. A drain that creates or observes dead work must fail instead of continuing to publish a successful cutover signal.
• Full backfill does not replace continuous sync. For the initial PostgreSQL-to-Elasticsearch cutover, keep the application on PostgreSQL until aliases are ready and the Outbox is empty and stable, then switch explicitly. Later mapping upgrades keep searches on the existing Elasticsearch alias until promotion.

The supported operator entrypoints below run from the OSS repository root. A wrapping repository may expose different package scripts; check its package.json before invoking these or the mapping migration commands linked below.

bun run db:install-fts-search-capture
bun run fts-search:reindex -- --status
bun run fts-search:reindex -- --apply --yes
bun run fts-search:sync -- --max-steps=8 --yes
bun run scripts/pgSearchCleanup/index.ts --status
bun run scripts/pgSearchCleanup/index.ts --apply --yes

Mapping Generations

Docker startup runs fts-search-elasticsearch-reindex.cjs --startup --yes after PostgreSQL migrations when the selected provider is Elasticsearch. It blocks serving until index migration and catch-up succeed. Persist the same checkpoint volume across application and migration containers; failed namespace locks still require explicit recovery. Continuous sync remains a separate worker. Hosted and manual workflows keep their explicit migration commands.

For mapping changes, generation operations, repeat/resume behavior, or deployment integration, read Mapping migration workflow. It routes to the public command guide and adds recovery, automation, and local Docker rehearsal rules.

• Treat managed and Serverless Elasticsearch APIs as capability-constrained. Create operational metadata and control indexes with the smallest portable request, and add topology, storage, or index settings only after verifying that the target service supports them; settings accepted by a self-managed cluster may be rejected by a managed service.
• Make idempotent Elasticsearch resource creation depend on the structured error type, such as resource_already_exists_exception, not a broad HTTP status range. Preserve any other creation failure and stop before follow-up reads or mutations instead of turning a rejected request into a misleading verification error.

Elasticsearch cannot change an existing field's type or index-time analyzer in place. The code declares the target and Elasticsearch records the live state, per entity:

• FTS_SEARCH_INDEX_DEFINITIONS[entity].schemaVersion is the declared generation; the fingerprint is sha256 of the mapping plus the shared analysis. Add complete changed-entity definitions in a new ftsSearchDocument/migration/NNNN-meaningful-name/ batch, register it and update current pointers in migration/index.ts. Append expected fingerprints in __tests__/schemaSnapshots.json; preserve published batches and baselines. mappings.test.ts and migration/index.test.ts check current parity, history, version bumps and fingerprint changes. See migration/README.md for ownership. Shared analysis changes alter every entity fingerprint and classify as breaking for every entity; bump all affected versions and rebuild rather than using in-place upgrades.
• Every physical index <alias>-v<n> or same-schema rebuild <alias>-v<n>-r<runId> carries _meta.{reindex_run_id, schema_version, schema_fingerprint}; the alias marks the live generation. A rebuild suffix is physical identity, not a schema-version bump. Indexes created before fingerprints existed may omit the fingerprint, but still need a valid run ID and schema version for sync readiness. _meta.schema_version wins over the -v<n> suffix because an in-place upgrade advances _meta without renaming the index.
• The sync runtime accepts an alias that still serves an older generation (upgrade in progress) and refuses one that serves a newer generation or a different fingerprint of the declared version. It writes every change to all open <alias>-v* indexes plus the alias write index, pruning each document to the fields that index maps (every generation is dynamic: strict), so a new generation can be backfilled beside the live one; a bulk work item is acknowledged only when every existing generation accepted it (2xx or 409 conflict).
• Canonical migration checkpoints remain keyed by (namespace, schemaVersion). Explicit current-version rebuild checkpoints add reindexRunId, so repeated v1 rebuilds never reuse a completed v1 cursor. --apply groups the requested entities by declared version, treats existing aliases as an upgrade (no --fresh-run), leaves existing aliases in place, and emits promotion_pending. A completed first install creates aliases. Promoting a newer generation requires a completed checkpoint, a fingerprint match when targeting the declared version, and an idle target-entity Outbox. Rollback to an older stamped generation can use its metadata without a retained checkpoint; --retire requires in_sync and only closes old generations; explicit --purge installs exact-index templates forbidding auto-creation before deleting eligible closed generations. --in-place requires mappingChange: additive, widens the live index with PUT _mapping, pins the checkpoint to that index, and backfills with external_gte so concurrent sync writes win.
• Use --apply --rebuild-current --entity=<entity> --yes when projection or capture semantics changed without a physical mapping change and historical documents must be regenerated from PostgreSQL. Deploy runtime support first: sync must recognize -r<runId> generations before one is created. Resume with the reported --run-id=<uuid>. Promote with the exact --generation=<alias>-v<n>-r<uuid> because version-only selection is ambiguous. The old generation remains dual-written for rollback and becomes same-version retirement-eligible only after the promotion records which live run superseded it.
• Checkpoints are local files, not Drizzle migration history. Preserve ES_REINDEX_STATE_DIR across invocations. Completed runs skip backfill; incomplete runs resume from saved cursors. Mutating CLI commands share a non-expiring Elasticsearch namespace lock, independent of checkpoint location. A failed command retains its lock when its outcome may be uncertain. Before --release-lock=<owner> --yes, stop the previous process and resolve pending requests; never assume age proves it stopped. The lock does not serialize old binaries or external operator actions.
• Reconciliation is exact only on a first install; once an alias serves an entity, concurrent sync writes make a higher Elasticsearch count legitimate and only a shortfall fails.
• scripts/elasticsearchReindex/runtime/generationService.ts owns classification (missing, unmanaged, in_sync, drift, upgrade_available, rollback_required), promotion, and retirement; keep them free of Cloud-specific policy.

Read docs/self-hosting/advanced/neon-pg-search-migration.mdx or its Chinese counterpart before changing the operational sequence. When database rollout or index cost affects the design, also use the db-migrations skill and measure the relevant operation on the actual Dev database before adding manual or deferred release steps.

Observability and Product Analytics

• Server provider metrics and traces live in apps/server/src/services/ftsSearch/observability.ts. Keep labels bounded: entity, provider, operation, outcome, and coarse error type are acceptable; raw queries, user IDs, document IDs, and index contents are not.
• User-perceived search behavior lives with the open-source Command Menu in src/features/CommandMenu/analytics.ts. Product analytics may cover end-to-end duration, rendered result counts, empty results, result clicks, and abandonment without a Cloud business slot.
• Measurement or analytics failures must never alter the selected provider's result or error.
• Backend metrics explain provider cost and latency; frontend events explain what the user actually experienced. Do not substitute one for the other.

Validation

• Add or update focused tests beside every changed provider, mapping, builder, capture, sync, or analytics module.
• Preserve boundary tests proving callers use FtsSearchRepo, providers cannot leak raw result shapes, and telemetry failures do not affect search behavior.
• Test pagination after authorization drops candidates, delete/scope-change priority, concurrent Outbox claims and stale settlements, retry/dead-letter behavior, reindex resume identity, and capture-definition mismatch whenever those paths change.
• Run bun run check <changed-files...> from the repository root. For migration or database-runtime changes, follow the db-migrations and testing skills and verify against the actual Dev database.