bcgov / bcgov/lcfs

LCFS - Add PostgreSQL full-text search to the Comment Log endpoint

Open
#4,457 0 comments 0 reactions 1 assignee Claimed by @hamed-valiollahi View on GitHub
Commenting component
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.