Stage 7 · Master
Phase 3 — User Service
Search
Add name-and-email search to the directory using pg_trgm, and treat organization_id as a mandatory filter rather than an optional convenience.
Every Search Endpoint Is a Potential Data Leak
A resident directory search box feels like the simplest feature in this module — type a name, get matches. But 'search users' and 'search users within Maple Ridge HOA' are different endpoints with different blast radii. A board member at Maple Ridge who searches for 'smith' must never see a Smith who lives in Cedar Grove. The moment organization scope becomes optional in a search query, it becomes a bug waiting for the first pentest.
If organization_id can be omitted from a search query and the handler silently searches everyone, that code path is a cross-tenant data leak the day someone forgets to pass it — including you, six months from now, adding a debug endpoint. Make the scope a required argument at the type level, not a query parameter with a fallback.
Trigram Indexes for 'Contains', Not Just 'Starts With'
A B-tree index accelerates equality and prefix (LIKE 'smith%') lookups but cannot help a query like '%mith%' or a misspelled 'smyth'. pg_trgm decomposes text into overlapping three-character sequences and indexes those, which is exactly the shape of similarity a resident directory search needs: someone typing part of a name from memory, not a database key.
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX users_name_trgm_idx
ON users USING gin ((first_name || ' ' || last_name) gin_trgm_ops);
CREATE INDEX users_email_trgm_idx
ON users USING gin (email gin_trgm_ops);
Two separate GIN indexes rather than one combined index: name search and email search have different query patterns, and keeping them independent lets Postgres's planner choose the cheaper one per query instead of always scanning a wider combined index.
DROP INDEX users_email_trgm_idx;
DROP INDEX users_name_trgm_idx;
DROP EXTENSION IF EXISTS pg_trgm;
Neither index uses CONCURRENTLY here, so both statements are free to share a single up/down file pair — that constraint only bites once a migration must run without holding a table lock, which Part 18 hits directly.
A Repository Method That Cannot Forget the Scope
The fix for 'someone forgot the organization filter' is not a code review checklist — it is a function signature that has no way to omit the scope. The search method below joins through memberships specifically so a bare users table scan is never even reachable from this code path.
package user
import (
"context"
"github.com/google/uuid"
"github.com/jackc/pgx/v5/pgxpool"
)
type SearchRepository struct {
pool *pgxpool.Pool
}
func NewSearchRepository(pool *pgxpool.Pool) *SearchRepository {
return &SearchRepository{pool: pool}
}
// Search requires organizationID as a positional argument, not an optional
// filter, so no caller can accidentally construct a global, cross-tenant
// search. Results are scoped through memberships — a users row that has no
// membership in this organization can never appear.
func (r *SearchRepository) Search(ctx context.Context, organizationID uuid.UUID, query string, limit int) ([]User, error) {
rows, err := r.pool.Query(ctx, `
SELECT u.id, u.email, u.first_name, u.last_name, u.status, u.created_at
FROM users u
JOIN memberships m ON m.user_id = u.id
WHERE m.organization_id = $1
AND (u.first_name || ' ' || u.last_name) % $2
ORDER BY similarity(u.first_name || ' ' || u.last_name, $2) DESC
LIMIT $3`,
organizationID, query, limit,
)
if err != nil {
return nil, err
}
defer rows.Close()
var results []User
for rows.Next() {
var u User
if err := rows.Scan(&u.ID, &u.Email, &u.FirstName, &u.LastName, &u.Status, &u.CreatedAt); err != nil {
return nil, err
}
results = append(results, u)
}
return results, rows.Err()
}The % operator is pg_trgm's similarity threshold match (controlled by pg_trgm.similarity_threshold, default 0.3) — it returns 'close enough' matches ranked by similarity(), which is what a human typing a partial or slightly misspelled name actually needs.
The Query That Should Never Ship
| Version | Query shape | Consequence |
|---|---|---|
| Unscoped (rejected in review) | SELECT * FROM users WHERE name % $1 | Returns matches from every organization on the platform — a direct tenant data leak. |
| Scoped (this lesson) | JOIN memberships ... WHERE m.organization_id = $1 AND name % $2 | Structurally cannot return a user outside the caller's organization. |
Applied exercise
Close the leak in a code review
A pull request adds GET /v1/users/search?q=smith with no organization_id parameter, reasoning that 'admins should be able to search everyone.'
- Write the review comment you would leave, explaining the specific risk in one paragraph.
- Propose an endpoint shape that supports a genuine platform-admin global search without weakening the tenant-scoped endpoint.
- State which role (from the Relationships lesson's role list) should be allowed to call the global variant, if it exists at all.
Deliverable
A code review comment plus a one-paragraph proposal for the platform-admin case.
Completion checks
- The review comment names the specific leak scenario (cross-tenant name/email exposure), not just 'this needs auth'.
- The proposal keeps the tenant-scoped endpoint's signature unchanged.
Why does Search take organizationID as a required positional parameter instead of an optional query filter?
Mark this lesson complete to store local progress and unlock a cleaner resume path the next time you visit.