Skip to main content

max / makenotwork

45.0 KB · 1276 lines History Blame Raw
1 //! Public discovery queries: search, browse, type/tag and price facets.
2 //!
3 //! Uses `pg_trgm` trigram indexes for fuzzy text matching.
4
5 use sqlx::{FromRow, PgPool, Row};
6 use std::fmt::Write as _;
7
8 use super::enums::{AiTierFilter, DiscoverSort, ItemType};
9 use super::models::{DbDiscoverItemRow, DbDiscoverProjectRow, DbItemTypeCount};
10 use crate::error::Result;
11
12 /// Shared filter parameters for discover item queries.
13 ///
14 /// Used by both [`discover_items`] and [`count_discover_items`] to keep
15 /// their filter logic in sync. The `sort_by` field is only relevant for
16 /// `discover_items`; `count_discover_items` ignores it.
17 pub struct DiscoverFilters<'a> {
18 pub search: Option<&'a str>,
19 /// Selected item types. Empty means unfiltered; multiple values are OR'd.
20 pub item_types: &'a [ItemType],
21 /// Selected tag slugs. Empty means unfiltered; multiple values are OR'd,
22 /// and each matches its own subtree.
23 pub tags: &'a [String],
24 pub min_price: Option<i32>,
25 pub max_price: Option<i32>,
26 pub sort_by: Option<DiscoverSort>,
27 /// Single-valued by nature: the three AI-disclosure choices are nested
28 /// ranges (everything ⊇ human-led ⊇ handmade), not independent values, so
29 /// OR-ing them would only ever widen back to the looser one.
30 pub ai_tier: Option<AiTierFilter>,
31 }
32
33 impl DiscoverFilters<'_> {
34 /// Item-type slugs for the `= ANY($2)` bind.
35 fn item_type_slugs(&self) -> Vec<String> {
36 self.item_types
37 .iter()
38 .map(std::string::ToString::to_string)
39 .collect()
40 }
41
42 /// `LIKE` patterns matching each selected tag's descendants, via tagtree so
43 /// the dot-path convention and the LIKE metacharacter escaping stay in one
44 /// place. Pairs with [`Self::tags`] as the exact-slug bind.
45 fn tag_descendant_patterns(&self) -> Vec<String> {
46 self.tags
47 .iter()
48 .map(|slug| tagtree::like_descendant_pattern(slug))
49 .collect()
50 }
51 }
52
53 // Shared SQL fragments for search (lexical tsvector + trigram/ILIKE fallback).
54 // The ILIKE escapes \, %, _ in the search term to prevent LIKE metacharacter interpretation.
55
56 /// Candidate membership: any word the searcher typed, none they excluded.
57 ///
58 /// A macro rather than a `const` so `concat!` can splice it into the clauses at
59 /// compile time; written once, it cannot drift between the predicate that
60 /// decides a row matches and the expression that counts how well.
61 ///
62 /// This is deliberately not `websearch_to_tsquery`, which ANDs every term. That
63 /// put a cliff at 100% coverage: a row holding four of five typed words was
64 /// excluded from the word tier alongside rows holding none, and the UI then
65 /// told both of them they had matched on spelling only. The rule is now the one
66 /// stated at the top of migration 179 -- exclusions filter, everything else
67 /// ranks -- and the tier is a count rather than a yes/no.
68 ///
69 /// The function is IMMUTABLE and takes a constant config, so repeating it in
70 /// the same statement does not repeat the work.
71 macro_rules! any_term_query {
72 () => {
73 "mnw_any_term_query($1)"
74 };
75 }
76
77 // The description terms are written as `COALESCE(i.description, '')` to match
78 // `idx_items_desc_trgm`, which is built on that expression rather than on the
79 // bare column. Written any other way Postgres will not use the index, and this
80 // clause runs on the busiest public page.
81 const ITEM_SEARCH_CLAUSE: &str = concat!(
82 r" AND (
83 i.search_tsv @@ ",
84 any_term_query!(),
85 r"
86 OR i.title % $1
87 OR i.title ILIKE '%' || replace(replace(replace($1, '\', '\\'), '%', '\%'), '_', '\_') || '%'
88 OR COALESCE(i.description, '') % $1
89 OR COALESCE(i.description, '') ILIKE '%' || replace(replace(replace($1, '\', '\\'), '%', '\%'), '_', '\_') || '%'
90 )"
91 );
92
93 const PROJECT_SEARCH_CLAUSE: &str = concat!(
94 r" AND (
95 p.search_tsv @@ ",
96 any_term_query!(),
97 r"
98 OR p.title % $1
99 OR p.title ILIKE '%' || replace(replace(replace($1, '\', '\\'), '%', '\%'), '_', '\_') || '%'
100 )"
101 );
102
103 // ILIKE-only variants for short queries (1-2 chars) where trigram similarity is unreliable.
104
105 const ITEM_SEARCH_CLAUSE_SHORT: &str = r" AND (
106 i.title ILIKE '%' || replace(replace(replace($1, '\', '\\'), '%', '\%'), '_', '\_') || '%'
107 OR COALESCE(i.description, '') ILIKE '%' || replace(replace(replace($1, '\', '\\'), '%', '\%'), '_', '\_') || '%'
108 )";
109
110 const PROJECT_SEARCH_CLAUSE_SHORT: &str = r" AND (
111 p.title ILIKE '%' || replace(replace(replace($1, '\', '\\'), '%', '\%'), '_', '\_') || '%'
112 )";
113
114 // Tiered relevance. Full rationale in migration 179; the short version:
115 //
116 // One cut, placed at the truth condition of the heading it drives.
117 //
118 // tier 2 -- holds at least one word the searcher typed. Renders in the main
119 // block; a row with some but not all of them prints its own count.
120 // tier 1 -- holds none of them, and is here on character overlap alone. Sits
121 // under "Near matches. Matched on spelling, not on words", which is
122 // true of every row in the tier by construction.
123 //
124 // The two tiers rank by different measures and never compare across the
125 // boundary. `ts_rank` above (frequency and length normalization over words that
126 // are actually there), `mnw_word_match_score` below (per-word trigram, so a
127 // misspelling costs one word instead of poisoning the comparison). They do not
128 // share a scale, which is fine precisely because the tier is decided first, by
129 // a count rather than by either score.
130 //
131 // See `templates/partials/discover_results.html` for the rendering.
132
133 /// Holds at least one word the searcher typed. Ranked by `ts_rank`.
134 pub const MATCH_TIER_LEXICAL: i16 = 2;
135
136 /// Holds none of the typed words; character overlap only. Ranked by per-word
137 /// trigram scoring (`mnw_word_match_score`, migration 179).
138 ///
139 /// Only the long-query path assigns tiers. A 1-2 character search is
140 /// substring-only by construction, so every row would land here and the
141 /// distinction would say nothing; those rows get `NULL` instead and the UI
142 /// draws no boundary.
143 pub const MATCH_TIER_FUZZY: i16 = 1;
144
145 /// Tier 2 when the row holds any typed word, tier 1 when it holds none.
146 /// `smallint` to match [`DbDiscoverItemRow::match_tier`]'s `Option<i16>`.
147 ///
148 /// The literals here and the constants above are checked against each other by
149 /// `match_tier_literals_match_constants`.
150 macro_rules! match_tier_expr {
151 ($tsv:expr) => {
152 concat!(
153 "CASE WHEN mnw_exact_terms(",
154 $tsv,
155 ", $1) > 0 THEN 2 ELSE 1 END::smallint as match_tier"
156 )
157 };
158 }
159
160 /// How many of the typed words this row holds, and how many were typed.
161 ///
162 /// Projected so the template can print "Matched 3 of 5 words" on a row that has
163 /// some but not all. The denominator is constant per query, but it is stemmed
164 /// in Postgres, so it comes back per row rather than being recomputed in Rust
165 /// off the raw string and disagreeing with the numerator.
166 macro_rules! match_terms_expr {
167 ($tsv:expr) => {
168 concat!(
169 "mnw_exact_terms(",
170 $tsv,
171 ", $1)::int as matched_terms,
172 cardinality(mnw_query_terms($1))::int as query_terms"
173 )
174 };
175 }
176
177 /// Rank within a tier: `ts_rank` for lexical rows, per-word trigram scoring for
178 /// fuzzy ones.
179 ///
180 /// Both arms read their weighting off the `setweight` A/B split in the
181 /// generated `search_tsv` column (migration 176) rather than carrying a
182 /// constant: `ts_rank` honours lexeme weights natively, and
183 /// `mnw_word_match_score` (migration 179) applies the same halving to
184 /// description lexemes that used to be written into this expression.
185 ///
186 /// The fuzzy arm scores each query word separately and averages, instead of
187 /// comparing the whole query string against the whole title. Whole-phrase
188 /// `similarity` is worst on precisely the queries that reach this tier: a long
189 /// query against a short title scores low however well the words line up,
190 /// because the query's surplus trigrams count against it. Per-word, a
191 /// misspelling costs one word.
192 macro_rules! match_score_expr {
193 ($tsv:expr) => {
194 concat!(
195 "CASE WHEN mnw_exact_terms(",
196 $tsv,
197 ", $1) > 0 THEN ts_rank(",
198 $tsv,
199 ", ",
200 any_term_query!(),
201 ")
202 ELSE mnw_word_match_score(",
203 $tsv,
204 ", $1) END::real as match_score"
205 )
206 };
207 }
208
209 /// Maximum allowed search term length. Queries longer than this are truncated.
210 const MAX_SEARCH_LEN: usize = 200;
211
212 /// Normalize a search term: trim whitespace, truncate to [`MAX_SEARCH_LEN`],
213 /// and return `None` if the result is empty.
214 pub(crate) fn normalize_search(raw: Option<&str>) -> Option<String> {
215 let trimmed = raw?.trim();
216 if trimmed.is_empty() {
217 return None;
218 }
219 if trimmed.len() > MAX_SEARCH_LEN {
220 // Truncate at a char boundary
221 let end = trimmed
222 .char_indices()
223 .take_while(|(i, _)| *i < MAX_SEARCH_LEN)
224 .last()
225 .map_or(MAX_SEARCH_LEN, |(i, c)| i + c.len_utf8());
226 Some(trimmed[..end].to_string())
227 } else {
228 Some(trimmed.to_string())
229 }
230 }
231
232 /// Returns `true` when the search term is too short for trigram matching (1-2 chars).
233 pub(crate) fn is_short_query(term: &str) -> bool {
234 term.trim().len() <= 2
235 }
236
237 /// The item-type clause. OR within the facet: any selected type matches.
238 const ITEM_TYPE_CLAUSE: &str = " AND i.item_type = ANY($2)";
239
240 /// The tag clause. OR within the facet, and each selected tag matches its own
241 /// subtree: `$5` holds the exact slugs, `$6` the `tagtree`-built descendant
242 /// patterns. LIKE's default escape character is backslash, which is what
243 /// `tagtree::like_descendant_pattern` escapes with.
244 const TAG_CLAUSE: &str = r" AND EXISTS (
245 SELECT 1 FROM item_tags it2
246 JOIN tags t2 ON t2.id = it2.tag_id
247 WHERE it2.item_id = i.id
248 AND (t2.slug = ANY($5) OR t2.path LIKE ANY($6))
249 )";
250
251 /// Append discover-item filter clauses to a dynamic query.
252 /// Parameter positions: $1=search, $2=item_types, $3=min_price, $4=max_price,
253 /// $5=tag_slugs, $6=tag_descendant_patterns. AI tier is inlined (see below).
254 ///
255 /// When `short_query` is `true`, the ILIKE-only clause is used instead of the
256 /// full trigram + ILIKE clause (trigram matching is unreliable for 1-2 char terms).
257 fn append_item_discover_filters(
258 query: &mut String,
259 filters: &DiscoverFilters<'_>,
260 has_search: bool,
261 short_query: bool,
262 ) {
263 if has_search {
264 if short_query {
265 query.push_str(ITEM_SEARCH_CLAUSE_SHORT);
266 } else {
267 query.push_str(ITEM_SEARCH_CLAUSE);
268 }
269 }
270 if !filters.item_types.is_empty() {
271 query.push_str(ITEM_TYPE_CLAUSE);
272 }
273 if filters.min_price.is_some() {
274 query.push_str(" AND i.price_cents >= $3");
275 }
276 if filters.max_price.is_some() {
277 query.push_str(" AND i.price_cents <= $4");
278 }
279 if !filters.tags.is_empty() {
280 query.push_str(TAG_CLAUSE);
281 }
282 // AI disclosure filter: `Handmade only` narrows to handmade; `Human-led`
283 // accepts handmade ∪ assisted. Values are enum-derived constants (not user
284 // input), so inlining the literals is safe and keeps the bind-position
285 // count stable across queries that use this fragment.
286 match filters.ai_tier {
287 Some(AiTierFilter::HandmadeOnly) => query.push_str(" AND i.ai_tier = 'handmade'"),
288 Some(AiTierFilter::HumanLed) => {
289 query.push_str(" AND i.ai_tier IN ('handmade', 'assisted')");
290 }
291 None => {}
292 }
293 }
294
295 /// Bind the 6 discover-filter parameters ($1-$6) to a sqlx query.
296 /// The AI-tier filter is appended to the WHERE as a literal SQL fragment,
297 /// so it occupies no bind position.
298 ///
299 /// Array binds are always supplied even when the corresponding clause is
300 /// omitted, so every discover query has the same bind arity regardless of which
301 /// filters are active.
302 macro_rules! bind_item_discover_filters {
303 ($q:expr, $filters:expr, $search_term:expr) => {
304 $q.bind($search_term.unwrap_or(""))
305 .bind($filters.item_type_slugs())
306 .bind($filters.min_price.unwrap_or(0))
307 .bind($filters.max_price.unwrap_or(i32::MAX))
308 .bind($filters.tags.to_vec())
309 .bind($filters.tag_descendant_patterns())
310 };
311 }
312
313 /// The visibility predicate every public item query must carry.
314 ///
315 /// Kept as one constant because the facet counts and the result set drifted apart
316 /// once already: `get_tag_counts` was missing `scan_status` and `is_sandbox`
317 /// entirely, so it counted items the results would never show.
318 pub(crate) const ITEM_VISIBILITY_WHERE: &str = " WHERE i.is_public = true AND i.listed = true AND i.deleted_at IS NULL AND p.is_public = true AND i.scan_status = 'clean' AND u.is_sandbox = FALSE";
319
320 /// Which facet is being counted, and therefore which filter to omit.
321 ///
322 /// A facet's counts answer "what would I get if I picked this value", so the
323 /// facet's own current selection must not constrain them. Selecting
324 /// `item_type=audio` and then applying that filter to the item-type counts
325 /// would zero every other type and hide the 12 samples also available.
326 /// Every *other* active filter still applies.
327 #[derive(Debug, Clone, Copy, PartialEq, Eq)]
328 pub enum FacetAxis {
329 ItemType,
330 Tag,
331 Price,
332 AiTier,
333 }
334
335 /// Append facet-count filter clauses, omitting `axis`'s own dimension.
336 ///
337 /// Uses the same bind layout as [`append_item_discover_filters`]
338 /// ($1=search, $2=item_type, $3=min_price, $4=max_price, $5=tag) so every
339 /// discover query binds identically, and `ai_tier` is inlined as an
340 /// enum-derived literal exactly as the result queries do.
341 pub(crate) fn append_facet_filters(
342 query: &mut String,
343 filters: &DiscoverFilters<'_>,
344 axis: FacetAxis,
345 has_search: bool,
346 short_query: bool,
347 ) {
348 if has_search {
349 if short_query {
350 query.push_str(ITEM_SEARCH_CLAUSE_SHORT);
351 } else {
352 query.push_str(ITEM_SEARCH_CLAUSE);
353 }
354 }
355 if axis != FacetAxis::ItemType && !filters.item_types.is_empty() {
356 query.push_str(ITEM_TYPE_CLAUSE);
357 }
358 if axis != FacetAxis::Price {
359 if filters.min_price.is_some() {
360 query.push_str(" AND i.price_cents >= $3");
361 }
362 if filters.max_price.is_some() {
363 query.push_str(" AND i.price_cents <= $4");
364 }
365 }
366 if axis != FacetAxis::Tag && !filters.tags.is_empty() {
367 // Descendant match via the materialized dot-path, matching the result
368 // query. This previously used `parent_id`, which matched direct children
369 // only, so any tag with grandchildren counted differently than it filtered.
370 query.push_str(TAG_CLAUSE);
371 }
372 if axis != FacetAxis::AiTier {
373 match filters.ai_tier {
374 Some(AiTierFilter::HandmadeOnly) => query.push_str(" AND i.ai_tier = 'handmade'"),
375 Some(AiTierFilter::HumanLed) => {
376 query.push_str(" AND i.ai_tier IN ('handmade', 'assisted')");
377 }
378 None => {}
379 }
380 }
381 }
382
383 /// Search/browse public items with optional text search, item_type, tag, and price filters.
384 ///
385 /// Search is tiered (see the `match_tier` notes above the tier/score
386 /// expressions):
387 /// - `search_tsv @@ websearch_to_tsquery` is the lexical tier: stemmed,
388 /// word-order independent, honours quoted phrases and `-exclusions`
389 /// - `%` (trigram similarity) and `ILIKE` form the fuzzy tier: misspellings,
390 /// partial words, and substrings a stemmer will not reach
391 ///
392 /// When a search term is present but no explicit sort is requested, results
393 /// are ordered by relevance (`match_tier DESC, match_score DESC`) and the UI
394 /// draws the tier boundary. Otherwise, the caller can choose `most_sold`,
395 /// `price_asc`, `price_desc`, or the default `newest`.
396 #[tracing::instrument(skip_all)]
397 pub async fn discover_items(
398 pool: &PgPool,
399 filters: &DiscoverFilters<'_>,
400 limit: i64,
401 offset: i64,
402 ) -> Result<Vec<DbDiscoverItemRow>> {
403 let search_term = normalize_search(filters.search);
404 let has_search = search_term.is_some();
405 let short_query = search_term.as_deref().is_some_and(is_short_query);
406
407 // Build the base query with optional similarity score.
408 // For short queries (1-2 chars) use a constant match_score since trigram
409 // similarity is unreliable at that length.
410 // Use LEFT JOIN with pre-aggregated transaction counts to avoid N+1 subquery per row
411 // LEFT JOIN item_tags/tags for primary tag display
412 let mut query = if has_search && !short_query {
413 format!(
414 r"
415 SELECT
416 i.id,
417 i.title,
418 i.description,
419 i.price_cents,
420 i.item_type,
421 i.created_at,
422 u.username,
423 u.settlement_currency,
424 p.title as project_title,
425 i.sales_count::bigint,
426 pt.name as primary_tag_name,
427 i.pwyw_enabled,
428 i.pwyw_min_cents,
429 i.ai_tier,
430 {tier},
431 {terms},
432 {score}
433 FROM items i
434 JOIN projects p ON i.project_id = p.id
435 JOIN users u ON p.user_id = u.id
436 LEFT JOIN item_tags pit ON pit.item_id = i.id AND pit.is_primary = true
437 LEFT JOIN tags pt ON pt.id = pit.tag_id
438 WHERE i.is_public = true AND i.listed = true AND p.is_public = true AND i.scan_status = 'clean' AND u.is_sandbox = FALSE AND i.deleted_at IS NULL
439 ",
440 tier = match_tier_expr!("i.search_tsv"),
441 terms = match_terms_expr!("i.search_tsv"),
442 score = match_score_expr!("i.search_tsv"),
443 )
444 } else if has_search {
445 // Short query: constant match_score, skip trigram similarity computation
446 String::from(
447 r"
448 SELECT
449 i.id,
450 i.title,
451 i.description,
452 i.price_cents,
453 i.item_type,
454 i.created_at,
455 u.username,
456 u.settlement_currency,
457 p.title as project_title,
458 i.sales_count::bigint,
459 pt.name as primary_tag_name,
460 i.pwyw_enabled,
461 i.pwyw_min_cents,
462 i.ai_tier,
463 NULL::smallint as match_tier,
464 NULL::int as matched_terms,
465 NULL::int as query_terms,
466 1.0::real as match_score
467 FROM items i
468 JOIN projects p ON i.project_id = p.id
469 JOIN users u ON p.user_id = u.id
470 LEFT JOIN item_tags pit ON pit.item_id = i.id AND pit.is_primary = true
471 LEFT JOIN tags pt ON pt.id = pit.tag_id
472 WHERE i.is_public = true AND i.listed = true AND p.is_public = true AND i.scan_status = 'clean' AND u.is_sandbox = FALSE AND i.deleted_at IS NULL
473 ",
474 )
475 } else {
476 String::from(
477 r"
478 SELECT
479 i.id,
480 i.title,
481 i.description,
482 i.price_cents,
483 i.item_type,
484 i.created_at,
485 u.username,
486 u.settlement_currency,
487 p.title as project_title,
488 i.sales_count::bigint,
489 pt.name as primary_tag_name,
490 i.pwyw_enabled,
491 i.pwyw_min_cents,
492 i.ai_tier,
493 NULL::smallint as match_tier,
494 NULL::int as matched_terms,
495 NULL::int as query_terms,
496 NULL::real as match_score
497 FROM items i
498 JOIN projects p ON i.project_id = p.id
499 JOIN users u ON p.user_id = u.id
500 LEFT JOIN item_tags pit ON pit.item_id = i.id AND pit.is_primary = true
501 LEFT JOIN tags pt ON pt.id = pit.tag_id
502 WHERE i.is_public = true AND i.listed = true AND p.is_public = true AND i.scan_status = 'clean' AND u.is_sandbox = FALSE AND i.deleted_at IS NULL
503 ",
504 )
505 };
506
507 append_item_discover_filters(&mut query, filters, has_search, short_query);
508
509 // Determine ordering
510 let order = if has_search
511 && (filters.sort_by.is_none() || filters.sort_by == Some(DiscoverSort::Newest))
512 {
513 // Tier first: a lexical match outranks every fuzzy one, whatever their
514 // scores say. The two scores are not on one scale, so this is the only
515 // ordering that means anything.
516 "match_tier DESC NULLS LAST, match_score DESC NULLS LAST, i.created_at DESC"
517 } else {
518 match filters.sort_by {
519 Some(DiscoverSort::MostSold) => "sales_count DESC, i.created_at DESC",
520 Some(DiscoverSort::PriceAsc) => "i.price_cents ASC, i.created_at DESC",
521 Some(DiscoverSort::PriceDesc) => "i.price_cents DESC, i.created_at DESC",
522 _ => "i.created_at DESC",
523 }
524 };
525
526 write!(query, " ORDER BY {order} LIMIT $7 OFFSET $8").unwrap();
527
528 let items = bind_item_discover_filters!(
529 sqlx::query_as::<_, DbDiscoverItemRow>(&query),
530 filters,
531 search_term.as_deref()
532 )
533 .bind(limit)
534 .bind(offset)
535 .fetch_all(pool)
536 .await?;
537
538 Ok(items)
539 }
540
541 /// Count total matching items for pagination (same filters as [`discover_items`]).
542 #[tracing::instrument(skip_all)]
543 pub async fn count_discover_items(pool: &PgPool, filters: &DiscoverFilters<'_>) -> Result<i64> {
544 let search_term = normalize_search(filters.search);
545 let has_search = search_term.is_some();
546 let short_query = search_term.as_deref().is_some_and(is_short_query);
547
548 let mut query = String::from(
549 r"
550 SELECT COUNT(*)
551 FROM items i
552 JOIN projects p ON i.project_id = p.id
553 JOIN users u ON p.user_id = u.id
554 WHERE i.is_public = true AND i.listed = true AND i.deleted_at IS NULL AND p.is_public = true AND i.scan_status = 'clean' AND u.is_sandbox = FALSE
555 ",
556 );
557
558 append_item_discover_filters(&mut query, filters, has_search, short_query);
559
560 let count: i64 =
561 bind_item_discover_filters!(sqlx::query_scalar(&query), filters, search_term.as_deref())
562 .fetch_one(pool)
563 .await?;
564
565 Ok(count)
566 }
567
568 /// Search/browse public projects with optional text search and category filters.
569 ///
570 /// Same trigram + ILIKE strategy as [`discover_items`], but without price
571 /// filters. Aggregates a `item_count` via LEFT JOIN so the discover UI can
572 /// show "N items" per project without a separate query.
573 #[tracing::instrument(skip_all)]
574 pub async fn discover_projects(
575 pool: &PgPool,
576 search: Option<&str>,
577 category_slug: Option<&str>,
578 sort_by: Option<DiscoverSort>,
579 has_source_code: bool,
580 limit: i64,
581 offset: i64,
582 ) -> Result<Vec<DbDiscoverProjectRow>> {
583 let search_term = normalize_search(search);
584 let has_search = search_term.is_some();
585 let short_query = search_term.as_deref().is_some_and(is_short_query);
586
587 let mut query = if has_search && !short_query {
588 format!(
589 r"
590 SELECT
591 p.slug,
592 p.title,
593 p.description,
594 p.project_type,
595 p.created_at,
596 u.username,
597 p.item_count::bigint as item_count,
598 {tier},
599 {terms},
600 {score},
601 pc.name as category_name,
602 pc.slug as category_slug
603 FROM projects p
604 JOIN users u ON p.user_id = u.id
605 LEFT JOIN project_categories pc ON pc.id = p.category_id
606 WHERE p.is_public = true AND u.is_sandbox = FALSE
607 ",
608 tier = match_tier_expr!("p.search_tsv"),
609 terms = match_terms_expr!("p.search_tsv"),
610 score = match_score_expr!("p.search_tsv"),
611 )
612 } else if has_search {
613 // Short query: constant match_score, skip trigram similarity computation
614 String::from(
615 r"
616 SELECT
617 p.slug,
618 p.title,
619 p.description,
620 p.project_type,
621 p.created_at,
622 u.username,
623 p.item_count::bigint as item_count,
624 NULL::smallint as match_tier,
625 NULL::int as matched_terms,
626 NULL::int as query_terms,
627 1.0::real as match_score,
628 pc.name as category_name,
629 pc.slug as category_slug
630 FROM projects p
631 JOIN users u ON p.user_id = u.id
632 LEFT JOIN project_categories pc ON pc.id = p.category_id
633 WHERE p.is_public = true AND u.is_sandbox = FALSE
634 ",
635 )
636 } else {
637 String::from(
638 r"
639 SELECT
640 p.slug,
641 p.title,
642 p.description,
643 p.project_type,
644 p.created_at,
645 u.username,
646 p.item_count::bigint as item_count,
647 NULL::smallint as match_tier,
648 NULL::int as matched_terms,
649 NULL::int as query_terms,
650 NULL::real as match_score,
651 pc.name as category_name,
652 pc.slug as category_slug
653 FROM projects p
654 JOIN users u ON p.user_id = u.id
655 LEFT JOIN project_categories pc ON pc.id = p.category_id
656 WHERE p.is_public = true AND u.is_sandbox = FALSE
657 ",
658 )
659 };
660
661 if has_search {
662 if short_query {
663 query.push_str(PROJECT_SEARCH_CLAUSE_SHORT);
664 } else {
665 query.push_str(PROJECT_SEARCH_CLAUSE);
666 }
667 }
668
669 if category_slug.is_some() {
670 query.push_str(" AND pc.slug = $2");
671 }
672
673 if has_source_code {
674 query.push_str(" AND EXISTS (SELECT 1 FROM git_repos gr WHERE gr.project_id = p.id)");
675 }
676
677 // No GROUP BY: item_count is now a denormalized column on projects (maintained
678 // by a trigger), so the query has no aggregate to group (Run 11 Perf SER-3).
679
680 let order = if has_search && (sort_by.is_none() || sort_by == Some(DiscoverSort::Newest)) {
681 "match_tier DESC NULLS LAST, match_score DESC NULLS LAST, p.created_at DESC"
682 } else {
683 match sort_by {
684 Some(DiscoverSort::MostSold) => "item_count DESC, p.created_at DESC",
685 _ => "p.created_at DESC",
686 }
687 };
688
689 write!(query, " ORDER BY {order} LIMIT $3 OFFSET $4").unwrap();
690
691 let projects = sqlx::query_as::<_, DbDiscoverProjectRow>(&query)
692 .bind(search_term.as_deref().unwrap_or(""))
693 .bind(category_slug.unwrap_or(""))
694 .bind(limit)
695 .bind(offset)
696 .fetch_all(pool)
697 .await?;
698
699 Ok(projects)
700 }
701
702 /// Count total matching projects for pagination (same filters as [`discover_projects`]).
703 #[tracing::instrument(skip_all)]
704 pub async fn count_discover_projects(
705 pool: &PgPool,
706 search: Option<&str>,
707 category_slug: Option<&str>,
708 has_source_code: bool,
709 ) -> Result<i64> {
710 let search_term = normalize_search(search);
711 let has_search = search_term.is_some();
712 let short_query = search_term.as_deref().is_some_and(is_short_query);
713
714 let mut query = String::from(
715 r"
716 SELECT COUNT(*)
717 FROM projects p
718 JOIN users u ON p.user_id = u.id
719 WHERE p.is_public = true AND u.is_sandbox = FALSE
720 ",
721 );
722
723 if has_search {
724 if short_query {
725 query.push_str(PROJECT_SEARCH_CLAUSE_SHORT);
726 } else {
727 query.push_str(PROJECT_SEARCH_CLAUSE);
728 }
729 }
730
731 if category_slug.is_some() {
732 query.push_str(
733 " AND EXISTS (SELECT 1 FROM project_categories pc WHERE pc.id = p.category_id AND pc.slug = $2)",
734 );
735 }
736
737 if has_source_code {
738 query.push_str(" AND EXISTS (SELECT 1 FROM git_repos gr WHERE gr.project_id = p.id)");
739 }
740
741 let count: i64 = sqlx::query_scalar(&query)
742 .bind(search_term.as_deref().unwrap_or(""))
743 .bind(category_slug.unwrap_or(""))
744 .fetch_one(pool)
745 .await?;
746
747 Ok(count)
748 }
749
750 /// Get item type counts for discover page (items mode).
751 ///
752 /// Counts what each type *would* yield, so `filters.item_type` is omitted
753 /// (see [`FacetAxis`]). Every other active filter applies.
754 #[tracing::instrument(skip_all)]
755 pub async fn get_item_type_counts(
756 pool: &PgPool,
757 filters: &DiscoverFilters<'_>,
758 ) -> Result<Vec<DbItemTypeCount>> {
759 let search_term = normalize_search(filters.search);
760 let has_search = search_term.is_some();
761 let short_query = search_term.as_deref().is_some_and(is_short_query);
762
763 let mut query = String::from(
764 r"
765 SELECT i.item_type as category, COUNT(*) as count
766 FROM items i
767 JOIN projects p ON i.project_id = p.id
768 JOIN users u ON p.user_id = u.id
769 ",
770 );
771 query.push_str(ITEM_VISIBILITY_WHERE);
772
773 append_facet_filters(
774 &mut query,
775 filters,
776 FacetAxis::ItemType,
777 has_search,
778 short_query,
779 );
780
781 query.push_str(" GROUP BY i.item_type ORDER BY count DESC");
782
783 let counts = bind_item_discover_filters!(
784 sqlx::query_as::<_, DbItemTypeCount>(&query),
785 filters,
786 search_term.as_deref()
787 )
788 .fetch_all(pool)
789 .await?;
790
791 Ok(counts)
792 }
793
794 /// The discover price buckets: label, inclusive lower bound, inclusive upper
795 /// bound (`None` = unbounded).
796 ///
797 /// The single source of truth for both the `COUNT(*) FILTER` clauses that
798 /// produce the sidebar numbers and the `min_price`/`max_price` links those
799 /// numbers are clickable through. They were duplicated across two files and
800 /// kept in step only by a comment at each site; a bucket whose bounds drifted
801 /// from its own count would display a number its link could not reproduce.
802 pub const PRICE_BUCKETS: [(&str, i32, Option<i32>); 5] = [
803 ("Free", 0, Some(0)),
804 ("Under $25", 1, Some(2499)),
805 ("$25-50", 2500, Some(4999)),
806 ("$50-100", 5000, Some(9999)),
807 ("$100+", 10000, None),
808 ];
809
810 /// The `FILTER (WHERE ...)` predicate for one bucket.
811 fn price_bucket_predicate(min: i32, max: Option<i32>) -> String {
812 match max {
813 Some(max) => format!("i.price_cents >= {min} AND i.price_cents <= {max}"),
814 None => format!("i.price_cents >= {min}"),
815 }
816 }
817
818 /// Get price range counts for the discover page sidebar (items mode only).
819 ///
820 /// Buckets are in cents: free (0), under $25 (1..2499), $25-$50 (2500..4999),
821 /// $50-$100 (5000..9999), over $100 (10000+). Uses PostgreSQL `FILTER (WHERE ...)`
822 /// to compute all five counts in a single table scan.
823 #[tracing::instrument(skip_all)]
824 pub async fn get_price_range_counts(
825 pool: &PgPool,
826 filters: &DiscoverFilters<'_>,
827 ) -> Result<Vec<i64>> {
828 let search_term = normalize_search(filters.search);
829 let has_search = search_term.is_some();
830 let short_query = search_term.as_deref().is_some_and(is_short_query);
831
832 // One aggregate per bucket, generated from PRICE_BUCKETS so the counts and
833 // the bucket links can never describe different ranges. Bounds are integer
834 // literals from a const, not user input.
835 let selects: Vec<String> = PRICE_BUCKETS
836 .iter()
837 .enumerate()
838 .map(|(i, (_, min, max))| {
839 format!(
840 "COUNT(*) FILTER (WHERE {}) as bucket_{i}",
841 price_bucket_predicate(*min, *max)
842 )
843 })
844 .collect();
845
846 let mut query = format!(
847 r"
848 SELECT
849 {}
850 FROM items i
851 JOIN projects p ON i.project_id = p.id
852 JOIN users u ON p.user_id = u.id
853 ",
854 selects.join(",\n ")
855 );
856 query.push_str(ITEM_VISIBILITY_WHERE);
857
858 append_facet_filters(
859 &mut query,
860 filters,
861 FacetAxis::Price,
862 has_search,
863 short_query,
864 );
865
866 let row = bind_item_discover_filters!(sqlx::query(&query), filters, search_term.as_deref())
867 .fetch_one(pool)
868 .await?;
869
870 // Positional read, in PRICE_BUCKETS order.
871 let counts = (0..PRICE_BUCKETS.len())
872 .map(|i| row.try_get::<Option<i64>, _>(i).ok().flatten().unwrap_or(0))
873 .collect();
874
875 Ok(counts)
876 }
877
878 /// Get AI tier counts for the discover page sidebar (items mode only).
879 #[tracing::instrument(skip_all)]
880 pub async fn get_ai_tier_counts(
881 pool: &PgPool,
882 filters: &DiscoverFilters<'_>,
883 ) -> Result<Vec<DbItemTypeCount>> {
884 let search_term = normalize_search(filters.search);
885 let has_search = search_term.is_some();
886 let short_query = search_term.as_deref().is_some_and(is_short_query);
887
888 let mut query = String::from(
889 r"
890 SELECT i.ai_tier as category, COUNT(*) as count
891 FROM items i
892 JOIN projects p ON i.project_id = p.id
893 JOIN users u ON p.user_id = u.id
894 ",
895 );
896 query.push_str(ITEM_VISIBILITY_WHERE);
897
898 append_facet_filters(
899 &mut query,
900 filters,
901 FacetAxis::AiTier,
902 has_search,
903 short_query,
904 );
905
906 query.push_str(" GROUP BY i.ai_tier ORDER BY count DESC");
907
908 let counts = bind_item_discover_filters!(
909 sqlx::query_as::<_, DbItemTypeCount>(&query),
910 filters,
911 search_term.as_deref()
912 )
913 .fetch_all(pool)
914 .await?;
915
916 Ok(counts)
917 }
918
919 /// A search suggestion with a category label (tag, project, or creator).
920 #[derive(Debug, FromRow)]
921 pub struct DbSearchSuggestion {
922 pub label: String,
923 pub category: String,
924 pub url: String,
925 }
926
927 /// Return combined search suggestions from tags, projects, and creators.
928 /// Uses ILIKE prefix match for fast results, limited to 8 total.
929 #[tracing::instrument(skip_all)]
930 pub async fn search_suggestions(pool: &PgPool, query: &str) -> Result<Vec<DbSearchSuggestion>> {
931 let q = query.trim();
932 if q.is_empty() {
933 return Ok(vec![]);
934 }
935
936 let pattern = format!(
937 "{}%",
938 q.replace('\\', "\\\\")
939 .replace('%', "\\%")
940 .replace('_', "\\_")
941 );
942
943 let rows = sqlx::query_as::<_, DbSearchSuggestion>(
944 r"
945 (
946 SELECT name AS label, 'tag' AS category, '/discover?mode=items&tag=' || slug AS url
947 FROM tags
948 WHERE name ILIKE $1
949 ORDER BY name
950 LIMIT 3
951 )
952 UNION ALL
953 (
954 SELECT title AS label, 'project' AS category, '/p/' || slug AS url
955 FROM projects
956 WHERE is_public = true AND title ILIKE $1
957 ORDER BY title
958 LIMIT 3
959 )
960 UNION ALL
961 (
962 SELECT username AS label, 'creator' AS category, '/u/' || username AS url
963 FROM users
964 WHERE is_sandbox = false AND suspended_at IS NULL AND deactivated_at IS NULL AND username ILIKE $1
965 ORDER BY username
966 LIMIT 2
967 )
968 ",
969 )
970 .bind(&pattern)
971 .fetch_all(pool)
972 .await?;
973
974 Ok(rows)
975 }
976
977 #[cfg(test)]
978 mod tests {
979 use super::*;
980
981 #[test]
982 fn normalize_search_none() {
983 assert_eq!(normalize_search(None), None);
984 }
985
986 #[test]
987 fn normalize_search_empty() {
988 assert_eq!(normalize_search(Some("")), None);
989 }
990
991 #[test]
992 fn normalize_search_whitespace_only() {
993 assert_eq!(normalize_search(Some(" ")), None);
994 }
995
996 #[test]
997 fn normalize_search_trims() {
998 assert_eq!(
999 normalize_search(Some(" hello ")),
1000 Some("hello".to_string())
1001 );
1002 }
1003
1004 #[test]
1005 fn normalize_search_truncates_long() {
1006 let long = "a".repeat(300);
1007 let result = normalize_search(Some(&long)).unwrap();
1008 assert_eq!(result.len(), MAX_SEARCH_LEN);
1009 }
1010
1011 #[test]
1012 fn normalize_search_truncates_at_char_boundary() {
1013 // Multi-byte chars: each is 2 bytes. 150 chars = 300 bytes.
1014 let long: String = std::iter::repeat_n('\u{00E9}', 150).collect();
1015 let result = normalize_search(Some(&long)).unwrap();
1016 assert!(result.len() <= MAX_SEARCH_LEN);
1017 // Must end at a valid char boundary (no panic on indexing)
1018 assert!(result.is_char_boundary(result.len()));
1019 }
1020
1021 #[test]
1022 fn normalize_search_exact_limit() {
1023 let exact = "b".repeat(MAX_SEARCH_LEN);
1024 assert_eq!(normalize_search(Some(&exact)), Some(exact));
1025 }
1026
1027 #[test]
1028 fn is_short_query_empty() {
1029 assert!(is_short_query(""));
1030 }
1031
1032 #[test]
1033 fn is_short_query_two_chars() {
1034 assert!(is_short_query("ab"));
1035 }
1036
1037 #[test]
1038 fn is_short_query_three_chars() {
1039 assert!(!is_short_query("abc"));
1040 }
1041
1042 #[test]
1043 fn append_filters_no_filters() {
1044 let filters = DiscoverFilters {
1045 search: None,
1046 item_types: &[],
1047 tags: &[],
1048 min_price: None,
1049 max_price: None,
1050 sort_by: None,
1051 ai_tier: None,
1052 };
1053 let mut q = String::from("SELECT 1 WHERE true");
1054 append_item_discover_filters(&mut q, &filters, false, false);
1055 assert_eq!(q, "SELECT 1 WHERE true");
1056 }
1057
1058 #[test]
1059 fn append_filters_with_item_type() {
1060 let filters = DiscoverFilters {
1061 search: None,
1062 item_types: &[ItemType::Audio],
1063 tags: &[],
1064 min_price: None,
1065 max_price: None,
1066 sort_by: None,
1067 ai_tier: None,
1068 };
1069 let mut q = String::new();
1070 append_item_discover_filters(&mut q, &filters, false, false);
1071 assert!(q.contains("i.item_type = ANY($2)"));
1072 }
1073
1074 #[test]
1075 fn append_filters_tags_match_slug_or_subtree() {
1076 let tags = vec!["audio.genre".to_string()];
1077 let filters = DiscoverFilters {
1078 search: None,
1079 item_types: &[],
1080 tags: &tags,
1081 min_price: None,
1082 max_price: None,
1083 sort_by: None,
1084 ai_tier: None,
1085 };
1086 let mut q = String::new();
1087 append_item_discover_filters(&mut q, &filters, false, false);
1088 // Exact slug OR descendant path, both array-bound so the facet can
1089 // hold several selections at once.
1090 assert!(q.contains("t2.slug = ANY($5)"));
1091 assert!(q.contains("t2.path LIKE ANY($6)"));
1092 }
1093
1094 #[test]
1095 fn tag_descendant_patterns_escape_like_metacharacters() {
1096 // tagtree owns the escaping; pin that we actually route through it,
1097 // since an unescaped `_` in a slug would silently widen the match.
1098 let tags = vec!["audio.genre_x".to_string()];
1099 let filters = DiscoverFilters {
1100 search: None,
1101 item_types: &[],
1102 tags: &tags,
1103 min_price: None,
1104 max_price: None,
1105 sort_by: None,
1106 ai_tier: None,
1107 };
1108 let patterns = filters.tag_descendant_patterns();
1109 assert_eq!(patterns.len(), 1);
1110 assert!(
1111 patterns[0].contains("\\_"),
1112 "underscore must be escaped, got {:?}",
1113 patterns[0]
1114 );
1115 assert!(patterns[0].ends_with(".%"));
1116 }
1117
1118 #[test]
1119 fn append_filters_search_uses_short_clause() {
1120 let filters = DiscoverFilters {
1121 search: Some("ab"),
1122 item_types: &[],
1123 tags: &[],
1124 min_price: None,
1125 max_price: None,
1126 sort_by: None,
1127 ai_tier: None,
1128 };
1129 let mut q = String::new();
1130 append_item_discover_filters(&mut q, &filters, true, true);
1131 assert!(q.contains("ILIKE"));
1132 assert!(!q.contains("i.title % $1"));
1133 }
1134
1135 #[test]
1136 fn append_filters_search_uses_trigram_clause() {
1137 let filters = DiscoverFilters {
1138 search: Some("hello"),
1139 item_types: &[],
1140 tags: &[],
1141 min_price: None,
1142 max_price: None,
1143 sort_by: None,
1144 ai_tier: None,
1145 };
1146 let mut q = String::new();
1147 append_item_discover_filters(&mut q, &filters, true, false);
1148 assert!(q.contains("i.title % $1"));
1149 // The word branch has to be OR'd in alongside the fuzzy one, or a
1150 // stemmed-only match ("loops" for "looping") never reaches the result
1151 // set and the tier it would have been sorted into stays empty.
1152 assert!(q.contains("i.search_tsv @@ mnw_any_term_query($1)"));
1153 // Membership must be ANY typed word, never all of them. websearch_to_tsquery
1154 // ANDs its terms, which is the cliff this replaced: four of five words
1155 // present ranked with none of them, under a heading that said so falsely.
1156 assert!(!q.contains("websearch_to_tsquery"));
1157 }
1158
1159 /// The tier values live twice: as SQL literals inside `match_tier_expr!`,
1160 /// and as the constants Rust compares against. Nothing links them, so a
1161 /// change to one silently inverts the ordering rather than failing.
1162 #[test]
1163 fn match_tier_literals_match_constants() {
1164 let expr = match_tier_expr!("i.search_tsv");
1165 assert!(
1166 expr.contains(&format!(
1167 "THEN {MATCH_TIER_LEXICAL} ELSE {MATCH_TIER_FUZZY} END"
1168 )),
1169 "tier literals in the SQL drifted from the constants: {expr}"
1170 );
1171 // The cut is "holds any typed word", not "holds all of them". Anything
1172 // stricter puts partial matches under a heading that denies they matched
1173 // words at all.
1174 assert!(
1175 expr.contains("mnw_exact_terms(i.search_tsv, $1) > 0"),
1176 "tier is no longer a coverage cut: {expr}"
1177 );
1178 // Both sides are constants, so this is a compile-time check rather than
1179 // a runtime one: a bad edit fails to build instead of failing this test.
1180 const _: () = assert!(
1181 MATCH_TIER_LEXICAL > MATCH_TIER_FUZZY,
1182 "ORDER BY match_tier DESC puts lexical first only if it is the larger value"
1183 );
1184 }
1185
1186 /// The fuzzy arm calls a function defined in a migration, so a rename on
1187 /// either side only fails at runtime, against the busiest public page.
1188 /// Pin the name here and the pairing is at least stated in one place.
1189 /// The label's denominator and the tier's numerator must come from one
1190 /// place. Counting query words in Rust off the raw string would disagree
1191 /// with Postgres the moment stemming or stopword removal applied, and the
1192 /// row would read "Matched 3 of 7 words" against a 5-word tsquery.
1193 #[test]
1194 fn label_counts_come_from_the_same_stemmer_as_the_tier() {
1195 let terms = match_terms_expr!("i.search_tsv");
1196 assert!(terms.contains("mnw_exact_terms(i.search_tsv, $1)"));
1197 assert!(terms.contains("cardinality(mnw_query_terms($1))"));
1198 assert!(match_tier_expr!("i.search_tsv").contains("mnw_exact_terms"));
1199 }
1200
1201 #[test]
1202 fn fuzzy_arm_calls_the_word_match_function() {
1203 let expr = match_score_expr!("i.search_tsv");
1204 assert!(
1205 expr.contains("mnw_word_match_score(i.search_tsv, $1)"),
1206 "fuzzy ranking no longer calls mnw_word_match_score (migrations/179): {expr}"
1207 );
1208 // Whole-phrase similarity is what 179 replaced. If it comes back here,
1209 // the long-query case it fails on is back too.
1210 assert!(!expr.contains("similarity("));
1211 }
1212
1213 /// A tier is only meaningful when both kinds of match can occur. The short
1214 /// path is ILIKE-only, so it must not claim one.
1215 #[test]
1216 fn short_query_rows_are_untiered() {
1217 assert!(!ITEM_SEARCH_CLAUSE_SHORT.contains("search_tsv"));
1218 assert!(!PROJECT_SEARCH_CLAUSE_SHORT.contains("search_tsv"));
1219 }
1220
1221 #[test]
1222 fn append_filters_handmade_only_narrows_to_one_tier() {
1223 let filters = DiscoverFilters {
1224 search: None,
1225 item_types: &[],
1226 tags: &[],
1227 min_price: None,
1228 max_price: None,
1229 sort_by: None,
1230 ai_tier: Some(AiTierFilter::HandmadeOnly),
1231 };
1232 let mut q = String::new();
1233 append_item_discover_filters(&mut q, &filters, false, false);
1234 assert!(q.contains("i.ai_tier = 'handmade'"));
1235 assert!(!q.contains("assisted"));
1236 }
1237
1238 #[test]
1239 fn append_filters_human_led_includes_handmade_and_assisted() {
1240 // Locks the policy commitment that Human-led covers BOTH handmade
1241 // and assisted. A future rename of the literals or a swap to a
1242 // single-tier match would silently weaken the filter.
1243 let filters = DiscoverFilters {
1244 search: None,
1245 item_types: &[],
1246 tags: &[],
1247 min_price: None,
1248 max_price: None,
1249 sort_by: None,
1250 ai_tier: Some(AiTierFilter::HumanLed),
1251 };
1252 let mut q = String::new();
1253 append_item_discover_filters(&mut q, &filters, false, false);
1254 assert!(q.contains("i.ai_tier IN ('handmade', 'assisted')"));
1255 assert!(!q.contains("generated"));
1256 }
1257
1258 #[test]
1259 fn ai_tier_filter_round_trip() {
1260 // Parses the query-string value the route receives back into the
1261 // typed enum the SQL builder expects.
1262 assert_eq!(
1263 "handmade_only".parse::<AiTierFilter>().unwrap(),
1264 AiTierFilter::HandmadeOnly
1265 );
1266 assert_eq!(
1267 "human_led".parse::<AiTierFilter>().unwrap(),
1268 AiTierFilter::HumanLed
1269 );
1270 assert!("everything".parse::<AiTierFilter>().is_err());
1271 assert!("assisted".parse::<AiTierFilter>().is_err());
1272 assert_eq!(AiTierFilter::HumanLed.to_string(), "human_led");
1273 assert_eq!(AiTierFilter::HandmadeOnly.label(), "Handmade only");
1274 }
1275 }
1276