PostgreSQL Full-Text Search

# PostgreSQL Full-Text Search for Google Antigravity

Implement powerful search using PostgreSQL full-text search.

## Search Setup

```sql
-- Add search vector column
ALTER TABLE public.posts ADD COLUMN IF NOT EXISTS search_vector tsvector;

-- Create GIN index
CREATE INDEX IF NOT EXISTS idx_posts_search ON public.posts USING gin(search_vector);

-- Update function
CREATE OR REPLACE FUNCTION update_posts_search_vector() RETURNS TRIGGER AS $$
BEGIN
    NEW.search_vector = 
        setweight(to_tsvector('english', COALESCE(NEW.title, '')), 'A') ||
        setweight(to_tsvector('english', COALESCE(NEW.content, '')), 'B');
    RETURN NEW;
END; $$ LANGUAGE plpgsql;

-- Trigger
CREATE TRIGGER posts_search_update BEFORE INSERT OR UPDATE ON public.posts FOR EACH ROW EXECUTE FUNCTION update_posts_search_vector();

-- Search function
CREATE OR REPLACE FUNCTION search_posts(query TEXT, limit_count INT DEFAULT 20)
RETURNS TABLE (id UUID, title TEXT, content TEXT, rank REAL) AS $$
BEGIN
    RETURN QUERY SELECT p.id, p.title, p.content, ts_rank(p.search_vector, plainto_tsquery('english', query)) AS rank
    FROM public.posts p WHERE p.search_vector @@ plainto_tsquery('english', query) ORDER BY rank DESC LIMIT limit_count;
END; $$ LANGUAGE plpgsql;
```

## Search API

```typescript
// app/api/search/route.ts
import { NextRequest, NextResponse } from "next/server";
import { createClient } from "@/lib/supabase/server";

export async function GET(request: NextRequest) {
    const query = request.nextUrl.searchParams.get("q");
    const limit = parseInt(request.nextUrl.searchParams.get("limit") || "20");

    if (!query || query.length < 2) return NextResponse.json({ results: [] });

    const supabase = createClient();
    const { data: results, error } = await supabase.rpc("search_posts", { query, limit_count: limit });

    if (error) return NextResponse.json({ error: error.message }, { status: 500 });
    return NextResponse.json({ results });
}
```

## Search Component

```typescript
// components/SearchInput.tsx
"use client";

import { useState, useEffect, useRef, useCallback } from "react";
import debounce from "lodash.debounce";
import Link from "next/link";

export function SearchInput() {
    const [query, setQuery] = useState("");
    const [results, setResults] = useState<any[]>([]);
    const [loading, setLoading] = useState(false);
    const [open, setOpen] = useState(false);
    const ref = useRef<HTMLDivElement>(null);

    const search = useCallback(debounce(async (q: string) => {
        if (q.length < 2) { setResults([]); return; }
        setLoading(true);
        try {
            const response = await fetch(`/api/search?q=${encodeURIComponent(q)}`);
            const { results } = await response.json();
            setResults(results);
            setOpen(true);
        } catch (e) {
            console.error(e);
        } finally {
            setLoading(false);
        }
    }, 300), []);

    useEffect(() => { search(query); }, [query, search]);

    useEffect(() => {
        const handleClick = (e: MouseEvent) => {
            if (ref.current && !ref.current.contains(e.target as Node)) setOpen(false);
        };
        document.addEventListener("mousedown", handleClick);
        return () => document.removeEventListener("mousedown", handleClick);
    }, []);

    return (
        <div ref={ref} className="search-container">
            <input type="search" value={query} onChange={(e) => setQuery(e.target.value)} onFocus={() => results.length > 0 && setOpen(true)} placeholder="Search..." />
            {loading && <span>Searching...</span>}
            {open && results.length > 0 && (
                <ul className="search-results">
                    {results.map((r) => (
                        <li key={r.id}>
                            <Link href={`/posts/${r.id}`} onClick={() => setOpen(false)}>
                                <strong>{r.title}</strong>
                                <p>{r.content?.substring(0, 100)}...</p>
                            </Link>
                        </li>
                    ))}
                </ul>
            )}
        </div>
    );
}
```

## Search with Filters

```typescript
// lib/search.ts
import { createClient } from "@/lib/supabase/server";

export async function searchWithFilters(query: string, filters: { category?: string; dateFrom?: string }, page = 1, limit = 20) {
    const supabase = createClient();
    let builder = supabase.from("posts").select("*", { count: "exact" }).textSearch("search_vector", query, { type: "websearch" });

    if (filters.category) builder = builder.eq("category", filters.category);
    if (filters.dateFrom) builder = builder.gte("created_at", filters.dateFrom);

    const { data, count, error } = await builder.range((page - 1) * limit, page * limit - 1).order("created_at", { ascending: false });
    return { results: data, total: count, error };
}
```

## Highlight Terms

```typescript
// utils/highlight.ts
export function highlightTerms(text: string, query: string): string {
    if (!query.trim()) return text;
    const terms = query.split(/\s+/).filter(Boolean);
    let result = text;
    terms.forEach((term) => {
        result = result.replace(new RegExp(`(${term})`, "gi"), "<mark>$1</mark>");
    });
    return result;
}
```

## Best Practices

1. **Indexing**: Create GIN indexes for search
2. **Debouncing**: Debounce search input
3. **Highlighting**: Highlight matches
4. **Pagination**: Paginate large results
5. **Analytics**: Track search queries