LCFS - Add PostgreSQL full-text search to the Comment Log endpoint
- Dominant language
- Python
- Stars
- 6
- Forks
- 6
- Avg merge
- 2d 7h
- Merged PRs (30d)
- 87
Description
**Description:**
Add keyword search to `GET /organizations/{id}/comments` via PostgreSQL full-text search over the `comment_search_vector` column. Apply `websearch_to_tsquery('english', :search)` when the `search` parameter is non-empty.
Category-specific search expansion:
- `category=Organization details` → also match against organization name fields.
- `category=Person` → also match against person-related fields (name, email).
**Wireframe:** https://preview.uxpin.com/7264e34749e7af420559b8cfc0ca8001c76db3ec#/pages/223328163/simulate/no-panels
**Purpose and benefit to user:**
Users can find specific comments by keyword across an organization's entire history, which is impractical via per-entity browsing.
**Acceptance Criteria:**
- [ ] Given I pass `search=transfer`, When the endpoint responds, Then only comments whose `comment_search_vector` matches the term are returned.
- [ ] Given I pass `search` with special characters or Unicode, When the endpoint responds, Then no `500` error occurs and results are sensible.
- [ ] Given `category=Organization details` and a `search` term, When the endpoint responds, Then results also include comments linked to organizations whose name matches the term.
- [ ] Given `search` is empty or omitted, When the endpoint responds, Then all comments matching other filters are returned.
- [ ] Given a `search` query against a populated dataset, When `EXPLAIN ANALYZE` is run, Then the GIN index is used.
**Development Checklist:**
- [ ] Add `search` parameter to the query schema
- [ ] Apply `websearch_to_tsquery` in the repository
- [ ] Implement category-specific expansion for `Organization details` and `Person`
- [ ] Sanitize `search` input at the API boundary
- [ ] Tests for keyword match, empty search, special characters, and Unicode
- [ ] Confirm GIN index usage with `EXPLAIN ANALYZE`
**Notes:**
- No semantic/embedding search in this ticket; full-text search is sufficient for the acceptance criteria.
Contributor guide
Assessment
This issue has not been assessed yet.