fix: preserve provisional guard in late orphan cleanup

The hidden_gems, critically_acclaimed, and forgotten_favorites section
recipes failed for viewers scoped to specific libraries:

  ERROR: column reference "content_id" is ambiguous (SQLSTATE 42702)

ListByRatingThreshold, ListUnplayedHighRated, and ListForgottenFavorites
join media_item_libraries (which also has a content_id column) when access
is library-restricted, but selected the unqualified itemColumns list,
making the leading content_id ambiguous.

Select qualifiedItemColumns("mi") instead. The SQL-building logic for each
query is extracted into a testable builder so the regression test exercises
the real production query rather than a duplicated copy in the test file.

internal/catalog/discovery_repo.go

@@ -36,10 +36,10 @@ type RatingFilter struct {
 	Filter AccessFilter
 }
 
-// ListByRatingThreshold returns media items whose rating_imdb is >= f.Min,
-// ordered by rating_imdb DESC NULLS LAST.  Items without an IMDb rating are
-// always excluded.
-func (r *DiscoveryRepository) ListByRatingThreshold(ctx context.Context, f RatingFilter) ([]*models.MediaItem, error) {
+// buildRatingThresholdQuery builds the SQL statement and bind args for ListByRatingThreshold.
+// It returns an empty query string when access rules exclude every
+// library, signalling the caller to skip the query and return no rows.
+func buildRatingThresholdQuery(f RatingFilter) (string, []any) {
 	var conditions []string
 	var args []any
 	argIdx := 1
@@ -60,7 +60,7 @@ func (r *DiscoveryRepository) ListByRatingThreshold(ctx context.Context, f Ratin
 		fromClause = "media_items mi JOIN media_item_libraries mil ON mi.content_id = mil.content_id"
 		if f.Filter.AllowedLibraryIDs != nil {
 			if len(f.Filter.AllowedLibraryIDs) == 0 {
-				return []*models.MediaItem{}, nil
+				return "", nil
 			}
 			placeholders := make([]string, len(f.Filter.AllowedLibraryIDs))
 			for i, id := range f.Filter.AllowedLibraryIDs {
@@ -85,7 +85,7 @@ func (r *DiscoveryRepository) ListByRatingThreshold(ctx context.Context, f Ratin
 
 	query := fmt.Sprintf(
 		"SELECT %s FROM %s WHERE %s ORDER BY mi.rating_imdb DESC NULLS LAST, mi.content_id ASC",
-		itemColumns,
+		qualifiedItemColumns("mi"),
 		fromClause,
 		strings.Join(conditions, " AND "),
 	)
@@ -95,6 +95,18 @@ func (r *DiscoveryRepository) ListByRatingThreshold(ctx context.Context, f Ratin
 		args = append(args, f.Limit)
 	}
 
+	return query, args
+}
+
+// ListByRatingThreshold returns media items whose rating_imdb is >= f.Min,
+// ordered by rating_imdb DESC NULLS LAST.  Items without an IMDb rating are
+// always excluded.
+func (r *DiscoveryRepository) ListByRatingThreshold(ctx context.Context, f RatingFilter) ([]*models.MediaItem, error) {
+	query, args := buildRatingThresholdQuery(f)
+	if query == "" {
+		return []*models.MediaItem{}, nil
+	}
+
 	rows, err := r.pool.Query(ctx, query, args...)
 	if err != nil {
 		return nil, fmt.Errorf("listing items by rating threshold: %w", err)
@@ -122,17 +134,10 @@ type UnplayedFilter struct {
 	Filter AccessFilter
 }
 
-// ListUnplayedHighRated returns high-rated items that the given user/profile has
-// never started watching.  "Never started" means no row exists in
-// user_watch_history for (user_id, profile_id, media_item_id), regardless of
-// completion status.  Items without an IMDb rating are excluded.
-//
-// Results are ordered by rating_imdb DESC NULLS LAST.
-func (r *DiscoveryRepository) ListUnplayedHighRated(ctx context.Context, f UnplayedFilter) ([]*models.MediaItem, error) {
-	if f.UserID <= 0 || strings.TrimSpace(f.ProfileID) == "" {
-		return nil, fmt.Errorf("ListUnplayedHighRated: UserID and ProfileID are required")
-	}
-
+// buildUnplayedHighRatedQuery builds the SQL statement and bind args for ListUnplayedHighRated.
+// It returns an empty query string when access rules exclude every
+// library, signalling the caller to skip the query and return no rows.
+func buildUnplayedHighRatedQuery(f UnplayedFilter) (string, []any) {
 	var conditions []string
 	var args []any
 	argIdx := 1
@@ -161,7 +166,7 @@ func (r *DiscoveryRepository) ListUnplayedHighRated(ctx context.Context, f Unpla
 		fromClause = "media_items mi JOIN media_item_libraries mil ON mi.content_id = mil.content_id"
 		if f.Filter.AllowedLibraryIDs != nil {
 			if len(f.Filter.AllowedLibraryIDs) == 0 {
-				return []*models.MediaItem{}, nil
+				return "", nil
 			}
 			placeholders := make([]string, len(f.Filter.AllowedLibraryIDs))
 			for i, id := range f.Filter.AllowedLibraryIDs {
@@ -186,7 +191,7 @@ func (r *DiscoveryRepository) ListUnplayedHighRated(ctx context.Context, f Unpla
 
 	query := fmt.Sprintf(
 		"SELECT %s FROM %s WHERE %s ORDER BY mi.rating_imdb DESC NULLS LAST, mi.content_id ASC",
-		itemColumns,
+		qualifiedItemColumns("mi"),
 		fromClause,
 		strings.Join(conditions, " AND "),
 	)
@@ -196,6 +201,25 @@ func (r *DiscoveryRepository) ListUnplayedHighRated(ctx context.Context, f Unpla
 		args = append(args, f.Limit)
 	}
 
+	return query, args
+}
+
+// ListUnplayedHighRated returns high-rated items that the given user/profile has
+// never started watching.  "Never started" means no row exists in
+// user_watch_history for (user_id, profile_id, media_item_id), regardless of
+// completion status.  Items without an IMDb rating are excluded.
+//
+// Results are ordered by rating_imdb DESC NULLS LAST.
+func (r *DiscoveryRepository) ListUnplayedHighRated(ctx context.Context, f UnplayedFilter) ([]*models.MediaItem, error) {
+	if f.UserID <= 0 || strings.TrimSpace(f.ProfileID) == "" {
+		return nil, fmt.Errorf("ListUnplayedHighRated: UserID and ProfileID are required")
+	}
+
+	query, args := buildUnplayedHighRatedQuery(f)
+	if query == "" {
+		return []*models.MediaItem{}, nil
+	}
+
 	rows, err := r.pool.Query(ctx, query, args...)
 	if err != nil {
 		return nil, fmt.Errorf("listing unplayed high-rated items: %w", err)
@@ -225,13 +249,10 @@ type ForgottenFavoritesFilter struct {
 	Filter AccessFilter
 }
 
-// ListForgottenFavorites returns high-rated items (rating_imdb >= 7.0) that the
-// user/profile either has never watched OR last watched more than LookbackDays
-// ago.  Results are ordered by rating_imdb DESC NULLS LAST.
-func (r *DiscoveryRepository) ListForgottenFavorites(ctx context.Context, f ForgottenFavoritesFilter) ([]*models.MediaItem, error) {
-	if f.UserID <= 0 || strings.TrimSpace(f.ProfileID) == "" {
-		return nil, fmt.Errorf("ListForgottenFavorites: UserID and ProfileID are required")
-	}
+// buildForgottenFavoritesQuery builds the SQL statement and bind args for ListForgottenFavorites.
+// It returns an empty query string when access rules exclude every
+// library, signalling the caller to skip the query and return no rows.
+func buildForgottenFavoritesQuery(f ForgottenFavoritesFilter) (string, []any) {
 	if f.LookbackDays <= 0 {
 		f.LookbackDays = 365
 	}
@@ -261,7 +282,7 @@ func (r *DiscoveryRepository) ListForgottenFavorites(ctx context.Context, f Forg
 		fromClause = "media_items mi JOIN media_item_libraries mil ON mi.content_id = mil.content_id"
 		if f.Filter.AllowedLibraryIDs != nil {
 			if len(f.Filter.AllowedLibraryIDs) == 0 {
-				return []*models.MediaItem{}, nil
+				return "", nil
 			}
 			placeholders := make([]string, len(f.Filter.AllowedLibraryIDs))
 			for i, id := range f.Filter.AllowedLibraryIDs {
@@ -286,7 +307,7 @@ func (r *DiscoveryRepository) ListForgottenFavorites(ctx context.Context, f Forg
 
 	query := fmt.Sprintf(
 		"SELECT %s FROM %s WHERE %s ORDER BY mi.rating_imdb DESC NULLS LAST, mi.content_id ASC",
-		itemColumns,
+		qualifiedItemColumns("mi"),
 		fromClause,
 		strings.Join(conditions, " AND "),
 	)
@@ -296,6 +317,21 @@ func (r *DiscoveryRepository) ListForgottenFavorites(ctx context.Context, f Forg
 		args = append(args, f.Limit)
 	}
 
+	return query, args
+}
+
+// ListForgottenFavorites returns high-rated items (rating_imdb >= 7.0) that the
+// user/profile either has never watched OR last watched more than LookbackDays
+// ago.  Results are ordered by rating_imdb DESC NULLS LAST.
+func (r *DiscoveryRepository) ListForgottenFavorites(ctx context.Context, f ForgottenFavoritesFilter) ([]*models.MediaItem, error) {
+	if f.UserID <= 0 || strings.TrimSpace(f.ProfileID) == "" {
+		return nil, fmt.Errorf("ListForgottenFavorites: UserID and ProfileID are required")
+	}
+	query, args := buildForgottenFavoritesQuery(f)
+	if query == "" {
+		return []*models.MediaItem{}, nil
+	}
+
 	rows, err := r.pool.Query(ctx, query, args...)
 	if err != nil {
 		return nil, fmt.Errorf("listing forgotten favorites: %w", err)

internal/catalog/discovery_repo_test.go

@@ -13,130 +13,6 @@ import (
 	"testing"
 )
 
-// buildRatingThresholdQuery is the testable extraction of the SQL generation
-// logic from ListByRatingThreshold so we can inspect it without a pool.
-func buildRatingThresholdQuery(f RatingFilter) (string, []any) {
-	var conditions []string
-	var args []any
-	argIdx := 1
-
-	conditions = append(conditions, "mi.rating_imdb >= $1")
-	args = append(args, f.Min)
-	argIdx++
-
-	fromClause := "media_items mi"
-	if f.LibraryID != nil {
-		fromClause = "media_items mi JOIN media_item_libraries mil ON mi.content_id = mil.content_id"
-		conditions = append(conditions, "mil.media_folder_id = $2")
-		args = append(args, *f.LibraryID)
-		argIdx++
-	} else if f.Filter.AllowedLibraryIDs != nil || len(f.Filter.DisabledLibraryIDs) > 0 {
-		fromClause = "media_items mi JOIN media_item_libraries mil ON mi.content_id = mil.content_id"
-		if f.Filter.AllowedLibraryIDs != nil {
-			if len(f.Filter.AllowedLibraryIDs) == 0 {
-				return "", nil // early empty
-			}
-			placeholders := make([]string, len(f.Filter.AllowedLibraryIDs))
-			for i, id := range f.Filter.AllowedLibraryIDs {
-				placeholders[i] = "$" + itoa(argIdx)
-				args = append(args, id)
-				argIdx++
-			}
-			conditions = append(conditions, "mil.media_folder_id IN ("+strings.Join(placeholders, ", ")+")")
-		}
-		if len(f.Filter.DisabledLibraryIDs) > 0 {
-			placeholders := make([]string, len(f.Filter.DisabledLibraryIDs))
-			for i, id := range f.Filter.DisabledLibraryIDs {
-				placeholders[i] = "$" + itoa(argIdx)
-				args = append(args, id)
-				argIdx++
-			}
-			conditions = append(conditions, "mil.media_folder_id NOT IN ("+strings.Join(placeholders, ", ")+")")
-		}
-	}
-
-	applyAccessFilter("mi", f.Filter, &conditions, &args, &argIdx)
-
-	query := "SELECT " + itemColumns + " FROM " + fromClause +
-		" WHERE " + strings.Join(conditions, " AND ") +
-		" ORDER BY mi.rating_imdb DESC NULLS LAST, mi.content_id ASC"
-
-	if f.Limit > 0 {
-		query += " LIMIT $" + itoa(argIdx)
-		args = append(args, f.Limit)
-	}
-	return query, args
-}
-
-// buildUnplayedHighRatedQuery mirrors the SQL-building portion of
-// ListUnplayedHighRated for unit testing.
-func buildUnplayedHighRatedQuery(f UnplayedFilter) (string, []any) {
-	var conditions []string
-	var args []any
-	argIdx := 1
-
-	conditions = append(conditions, "mi.rating_imdb >= $1")
-	args = append(args, f.MinRating)
-	argIdx++
-
-	conditions = append(conditions, "NOT EXISTS (\n\t\tSELECT 1\n\t\tFROM user_watch_history uwh\n\t\tWHERE uwh.user_id = $"+itoa(argIdx)+"\n\t\t  AND uwh.profile_id = $"+itoa(argIdx+1)+"\n\t\t  AND uwh.media_item_id = mi.content_id\n\t)")
-	args = append(args, f.UserID, f.ProfileID)
-	argIdx += 2
-
-	fromClause := "media_items mi"
-	if f.Filter.AllowedLibraryIDs != nil || len(f.Filter.DisabledLibraryIDs) > 0 {
-		fromClause = "media_items mi JOIN media_item_libraries mil ON mi.content_id = mil.content_id"
-		if f.Filter.AllowedLibraryIDs != nil {
-			if len(f.Filter.AllowedLibraryIDs) == 0 {
-				return "", nil // early empty
-			}
-			placeholders := make([]string, len(f.Filter.AllowedLibraryIDs))
-			for i, id := range f.Filter.AllowedLibraryIDs {
-				placeholders[i] = "$" + itoa(argIdx)
-				args = append(args, id)
-				argIdx++
-			}
-			conditions = append(conditions, "mil.media_folder_id IN ("+strings.Join(placeholders, ", ")+")")
-		}
-		if len(f.Filter.DisabledLibraryIDs) > 0 {
-			placeholders := make([]string, len(f.Filter.DisabledLibraryIDs))
-			for i, id := range f.Filter.DisabledLibraryIDs {
-				placeholders[i] = "$" + itoa(argIdx)
-				args = append(args, id)
-				argIdx++
-			}
-			conditions = append(conditions, "mil.media_folder_id NOT IN ("+strings.Join(placeholders, ", ")+")")
-		}
-	}
-
-	applyAccessFilter("mi", f.Filter, &conditions, &args, &argIdx)
-
-	query := "SELECT " + itemColumns + " FROM " + fromClause +
-		" WHERE " + strings.Join(conditions, " AND ") +
-		" ORDER BY mi.rating_imdb DESC NULLS LAST, mi.content_id ASC"
-
-	if f.Limit > 0 {
-		query += " LIMIT $" + itoa(argIdx)
-		args = append(args, f.Limit)
-	}
-	return query, args
-}
-
-// itoa is a local helper to avoid importing strconv.
-func itoa(n int) string {
-	if n == 0 {
-		return "0"
-	}
-	buf := [20]byte{}
-	pos := len(buf)
-	for n > 0 {
-		pos--
-		buf[pos] = byte('0' + n%10)
-		n /= 10
-	}
-	return string(buf[pos:])
-}
-
 // ---------------------------------------------------------------------------
 // ListByRatingThreshold SQL generation tests
 // ---------------------------------------------------------------------------
@@ -370,62 +246,6 @@ func TestUnplayedHighRated_ContentRatingFilter(t *testing.T) {
 // ListForgottenFavorites SQL generation tests
 // ---------------------------------------------------------------------------
 
-// buildForgottenFavoritesQuery is the testable extraction of the SQL
-// generation logic from ListForgottenFavorites.
-func buildForgottenFavoritesQuery(f ForgottenFavoritesFilter) (string, []any) {
-	if f.LookbackDays <= 0 {
-		f.LookbackDays = 365
-	}
-
-	var conditions []string
-	var args []any
-	argIdx := 1
-
-	conditions = append(conditions, "mi.rating_imdb >= 7.0")
-
-	conditions = append(conditions, "NOT EXISTS (\n\t\tSELECT 1\n\t\tFROM user_watch_history uwh\n\t\tWHERE uwh.user_id = $"+itoa(argIdx)+"\n\t\t  AND uwh.profile_id = $"+itoa(argIdx+1)+"\n\t\t  AND uwh.media_item_id = mi.content_id\n\t\t  AND uwh.watched_at >= NOW() - ($"+itoa(argIdx+2)+" || ' days')::interval\n\t)")
-	args = append(args, f.UserID, f.ProfileID, f.LookbackDays)
-	argIdx += 3
-
-	fromClause := "media_items mi"
-	if f.Filter.AllowedLibraryIDs != nil || len(f.Filter.DisabledLibraryIDs) > 0 {
-		fromClause = "media_items mi JOIN media_item_libraries mil ON mi.content_id = mil.content_id"
-		if f.Filter.AllowedLibraryIDs != nil {
-			if len(f.Filter.AllowedLibraryIDs) == 0 {
-				return "", nil // early empty
-			}
-			placeholders := make([]string, len(f.Filter.AllowedLibraryIDs))
-			for i, id := range f.Filter.AllowedLibraryIDs {
-				placeholders[i] = "$" + itoa(argIdx)
-				args = append(args, id)
-				argIdx++
-			}
-			conditions = append(conditions, "mil.media_folder_id IN ("+strings.Join(placeholders, ", ")+")")
-		}
-		if len(f.Filter.DisabledLibraryIDs) > 0 {
-			placeholders := make([]string, len(f.Filter.DisabledLibraryIDs))
-			for i, id := range f.Filter.DisabledLibraryIDs {
-				placeholders[i] = "$" + itoa(argIdx)
-				args = append(args, id)
-				argIdx++
-			}
-			conditions = append(conditions, "mil.media_folder_id NOT IN ("+strings.Join(placeholders, ", ")+")")
-		}
-	}
-
-	applyAccessFilter("mi", f.Filter, &conditions, &args, &argIdx)
-
-	query := "SELECT " + itemColumns + " FROM " + fromClause +
-		" WHERE " + strings.Join(conditions, " AND ") +
-		" ORDER BY mi.rating_imdb DESC NULLS LAST, mi.content_id ASC"
-
-	if f.Limit > 0 {
-		query += " LIMIT $" + itoa(argIdx)
-		args = append(args, f.Limit)
-	}
-	return query, args
-}
-
 func TestForgottenFavorites_BasicQuery(t *testing.T) {
 	query, args := buildForgottenFavoritesQuery(ForgottenFavoritesFilter{
 		LookbackDays: 365,
@@ -540,3 +360,41 @@ func TestForgottenFavorites_DefaultLookbackApplied(t *testing.T) {
 		t.Fatalf("expected lookback default 365, got %v", args[2])
 	}
 }
+
+// TestDiscoveryQueries_QualifyContentIDUnderLibraryJoin is a regression guard
+// for the "column reference \"content_id\" is ambiguous" (SQLSTATE 42702)
+// failure. When a viewer has library restrictions, each discovery query joins
+// media_item_libraries (which also has a content_id column). The SELECT list
+// must therefore qualify its columns with the mi alias; a bare column list
+// makes content_id ambiguous and the query fails at runtime.
+func TestDiscoveryQueries_QualifyContentIDUnderLibraryJoin(t *testing.T) {
+	restricted := AccessFilter{AllowedLibraryIDs: []int{1, 2}}
+
+	cases := []struct {
+		name  string
+		query string
+	}{
+		{"rating_threshold", mustQuery(buildRatingThresholdQuery(RatingFilter{Min: 7, Filter: restricted}))},
+		{"unplayed_high_rated", mustQuery(buildUnplayedHighRatedQuery(UnplayedFilter{MinRating: 7, UserID: 1, ProfileID: "p", Filter: restricted}))},
+		{"forgotten_favorites", mustQuery(buildForgottenFavoritesQuery(ForgottenFavoritesFilter{UserID: 1, ProfileID: "p", LookbackDays: 365, Filter: restricted}))},
+	}
+
+	for _, tc := range cases {
+		t.Run(tc.name, func(t *testing.T) {
+			// Precondition: the join that introduces the ambiguity is present.
+			if !strings.Contains(tc.query, "JOIN media_item_libraries mil") {
+				t.Fatalf("expected library join in query, got:\n%s", tc.query)
+			}
+			// The SELECT list must be alias-qualified.
+			if !strings.HasPrefix(tc.query, "SELECT mi.content_id,") {
+				t.Fatalf("SELECT list is not qualified with the mi alias (ambiguous content_id), got:\n%s", tc.query)
+			}
+			// And must not contain a bare, unqualified content_id column.
+			if strings.Contains(tc.query, "SELECT content_id,") {
+				t.Fatalf("SELECT list uses an unqualified content_id, got:\n%s", tc.query)
+			}
+		})
+	}
+}
+
+func mustQuery(query string, _ []any) string { return query }

internal/catalog/folder_repo.go

@@ -716,12 +716,12 @@ const collectOrphanedProvisionalIDsByContentIDSQL = `
 	SELECT mi.content_id
 	FROM public.media_items mi
 	WHERE mi.content_id = ANY($1)
-	  AND ` + orphanedMediaItemSafetyConditions
+	  AND ` + orphanedProvisionalMediaItemConditions
 
 const deleteOrphanedProvisionalIDsByContentIDSQL = `
 	DELETE FROM public.media_items mi
 	WHERE mi.content_id = ANY($1)
-	  AND ` + orphanedMediaItemSafetyConditions
+	  AND ` + orphanedProvisionalMediaItemConditions
 
 func (r *FolderRepository) deleteOrphanedItemsByContentID(ctx context.Context, contentIDs []string) (int, []string, error) {
 	if len(contentIDs) == 0 {