Skip to content

Port person search to PostgreSQL full-text indexing #151

Description

@atomantic

Replace SQLite FTS5 person search with PostgreSQL full-text search while preserving the search API's observable behavior.

Depends on #148
Depends on #149
Depends on #150

Scope:

  • Maintain a tsvector-backed person search document covering display name, birth name, aliases, biography, and occupations, with a GIN index.
  • Port search queries, result counts, ranking, pagination, filters, and stable alphabetical/default ordering to PostgreSQL.
  • Decide and document how prefix/fuzzy name matching is represented; use pg_trgm only where parity fixtures prove tsvector alone is insufficient.
  • Remove the PostgreSQL path's dependency on person_fts and the SQLite 003_rebuild_fts migration.
  • Add parity fixtures for names, aliases, punctuation, prefixes, empty queries, ranking, filters, and pagination.

Acceptance criteria:

  • Existing search API tests pass against PostgreSQL with equivalent result membership and deterministic ordering.
  • Search documents update when a person or searchable claim changes.
  • Query text is parameterized and cannot alter SQL syntax.
  • Performance-sensitive search plans use the intended index on representative fixtures.

Files/areas: PostgreSQL schema/search triggers or writer, server/src/services/search.service.ts, relevant quick-search routes, legacy FTS helpers/migration, integration tests.

Part of #120

After this issue ships, check its box in #120. If it was the last open child, comment on #120 with a summary of the delivered children and close #120 as completed.

Metadata

Metadata

Assignees

No one assigned

    Labels

    effort:highHigh reasoning effort recommendedmodel:heavyBest suited to the strongest available coding modelplanClaimable backlog item

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions