Beginner Capstone: Semantic Product Search

Build a semantic product search that retrieves natural-language intent while preserving exact SKU search, availability, tenant, category, region, and price rules. The capstone integrates PostgreSQL schema, ingestion, embeddings, exact and HNSW search, full-text search, RRF, evaluation, a small API, security fixtures, observability, and cleanup. Completion requires evidence, not a polished demo alone.

What is the mission?

A user should find “waterproof shoes for a winter hike” even when products use different wording, and find BOOT-X41 exactly. The system must never return unavailable, wrong-region, or unauthorized catalog items. It must return a safe empty result when evidence is insufficient.

What are the user stories?

• Search by natural language, exact SKU, brand, and feature.
• Filter by category, availability, region, and price range.
• See title, short explanation, and stable product link.
• Receive diverse results rather than near-identical variants.
• Allow catalog updates and deletion to become visible within a defined freshness SLO.

What architecture will you build?

What is the data contract?

Products need tenant, SKU, title, description, category, brand, region, currency, price, availability, source version/checksum, updated/deleted timestamps, search vector, embedding model, and vector. Keep price numeric and filters typed. Do not embed price or authorization as the only representation of those rules.

What are the milestones?

1. Start the version-pinned Docker environment and migration history.
2. Load at least 500 realistic synthetic products across two tenants.
3. Implement idempotent change and delete ingestion.
4. Create exact SQL and a 50-query labeled baseline.
5. Add HNSW and measure recall/latency against exact search.
6. Add FTS and exact SKU branch.
7. Fuse with RRF and cap product-family duplicates.
8. Expose a parameterized API with server-derived tenant identity.
9. Add logs/metrics without raw query or vector leakage.
10. Run security, load, deletion, backup, and rollback tests.

What are the acceptance gates?

• zero cross-tenant or unavailable results;
• 100% exact-SKU success for valid fixtures;
• documented recall@10 and nDCG@10 targets on held-out queries;
• p95 latency under the target at stated concurrency;
• deletion visible within freshness SLO;
• restore drill reproduces schema and sample search;
• every result records branch contribution and model/index version.

What is the threat model?

Test SQL injection, tenant-ID tampering, extreme top-k, price overflow, malicious catalog text, cache collision, stale availability, deleted products, and model/provider failure. Catalog content is data and cannot change system or tool policy.

How do you verify it?

Publish a capstone report containing architecture, migrations, fixture generator, workload card, labels, exact and ANN results, hybrid ablation, plans, load results, security tests, deletion trace, restore time, costs, known limits, and next decisions.

What breaks in production?

• Semantic score overrides availability.
• Exact identifiers are lower-ranked than vague matches.
• One product family fills all results.
• Product updates do not trigger embeddings.
• Evaluation uses only pleasant demo queries.

AI pair-work prompt

Act as my capstone reviewer. Using this specification and my repository artifacts, review one milestone at a time. Require SQL, tests, plans, metrics, and failure evidence before approving the next milestone. Never generate credentials or mark a gate passed from screenshots alone.

Verification contract: A second person should recreate the environment and reproduce the report from a clean checkout.

Check your understanding

1. Which rules remain typed filters instead of embeddings?
2. Why include exact, lexical, and vector branches?
3. What evidence makes the capstone launchable?

Official references

• pgvector
• PostgreSQL full-text search