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