fix: prioritise unread entries server-side so they appear in all-entries view

1 files changed, 28 insertions, 9 deletions

diff --git a/supabase/schema.sql b/supabase/schema.sql
index fde7232..e989af4 100644
--- a/supabase/schema.sql
+++ b/supabase/schema.sql
@@ -756,10 +756,10 @@ $$;
 ALTER FUNCTION "public"."get_custom_feed_timeline"("p_custom_feed_id" "uuid", "p_result_limit" integer, "p_pagination_cursor" timestamp with time zone) OWNER TO "postgres";
 
 --
--- Name: get_timeline("uuid", "uuid", integer, timestamp with time zone, boolean); Type: FUNCTION; Schema: public; Owner: postgres
+-- Name: get_timeline("uuid", "uuid", integer, timestamp with time zone, boolean, boolean, boolean); Type: FUNCTION; Schema: public; Owner: postgres
 --
 
-CREATE OR REPLACE FUNCTION "public"."get_timeline"("target_folder_id" "uuid" DEFAULT NULL::"uuid", "target_feed_id" "uuid" DEFAULT NULL::"uuid", "result_limit" integer DEFAULT 50, "pagination_cursor" timestamp with time zone DEFAULT NULL::timestamp with time zone, "unread_only" boolean DEFAULT false) RETURNS TABLE("entry_id" "uuid", "feed_id" "uuid", "feed_title" "text", "custom_title" "text", "entry_title" "text", "entry_url" "text", "author" "text", "summary" "text", "image_url" "text", "published_at" timestamp with time zone, "is_read" boolean, "is_saved" boolean, "enclosure_url" "text", "enclosure_type" "text")
+CREATE OR REPLACE FUNCTION "public"."get_timeline"("target_folder_id" "uuid" DEFAULT NULL::"uuid", "target_feed_id" "uuid" DEFAULT NULL::"uuid", "result_limit" integer DEFAULT 50, "pagination_cursor" timestamp with time zone DEFAULT NULL::timestamp with time zone, "unread_only" boolean DEFAULT false, "prioritise_unread" boolean DEFAULT false, "cursor_is_read" boolean DEFAULT NULL) RETURNS TABLE("entry_id" "uuid", "feed_id" "uuid", "feed_title" "text", "custom_title" "text", "entry_title" "text", "entry_url" "text", "author" "text", "summary" "text", "image_url" "text", "published_at" timestamp with time zone, "is_read" boolean, "is_saved" boolean, "enclosure_url" "text", "enclosure_type" "text")
     LANGUAGE "sql" STABLE SECURITY DEFINER
     SET "search_path" TO ''
     AS $$
@@ -789,7 +789,6 @@ CREATE OR REPLACE FUNCTION "public"."get_timeline"("target_folder_id" "uuid" DEF
     AND (target_folder_id IS NULL OR s.folder_id = target_folder_id)
     AND (target_feed_id IS NULL OR e.feed_id = target_feed_id)
     AND (target_feed_id IS NOT NULL OR s.hidden_from_timeline = false)
-    AND (pagination_cursor IS NULL OR e.published_at < pagination_cursor)
     AND (NOT unread_only OR COALESCE(ues.read, false) = false)
     AND NOT EXISTS (
       SELECT 1 FROM public.muted_keywords mk
@@ -803,12 +802,32 @@ CREATE OR REPLACE FUNCTION "public"."get_timeline"("target_folder_id" "uuid" DEF
       (SELECT tier FROM public.user_profiles WHERE id = auth.uid()) IN ('pro', 'developer')
       OR e.published_at >= now() - interval '14 days'
     )
-  ORDER BY e.published_at DESC NULLS LAST
+    AND (
+      NOT prioritise_unread
+      OR pagination_cursor IS NULL
+      OR (
+        cursor_is_read = false AND COALESCE(ues.read, false) = false AND e.published_at < pagination_cursor
+      )
+      OR (
+        cursor_is_read = true AND COALESCE(ues.read, false) = true AND e.published_at < pagination_cursor
+      )
+      OR (
+        cursor_is_read = false AND COALESCE(ues.read, false) = true
+      )
+    )
+    AND (
+      prioritise_unread
+      OR pagination_cursor IS NULL
+      OR e.published_at < pagination_cursor
+    )
+  ORDER BY
+    CASE WHEN prioritise_unread THEN COALESCE(ues.read, false)::int ELSE 0 END ASC,
+    e.published_at DESC NULLS LAST
   LIMIT result_limit;
 $$;
 
 
-ALTER FUNCTION "public"."get_timeline"("target_folder_id" "uuid", "target_feed_id" "uuid", "result_limit" integer, "pagination_cursor" timestamp with time zone, "unread_only" boolean) OWNER TO "postgres";
+ALTER FUNCTION "public"."get_timeline"("target_folder_id" "uuid", "target_feed_id" "uuid", "result_limit" integer, "pagination_cursor" timestamp with time zone, "unread_only" boolean, "prioritise_unread" boolean, "cursor_is_read" boolean) OWNER TO "postgres";
 
 --
 -- Name: get_unread_counts(); Type: FUNCTION; Schema: public; Owner: postgres
@@ -3384,12 +3403,12 @@ GRANT ALL ON FUNCTION "public"."get_custom_feed_timeline"("p_custom_feed_id" "uu
 
 
 --
--- Name: FUNCTION "get_timeline"("target_folder_id" "uuid", "target_feed_id" "uuid", "result_limit" integer, "pagination_cursor" timestamp with time zone, "unread_only" boolean); Type: ACL; Schema: public; Owner: postgres
+-- Name: FUNCTION "get_timeline"("target_folder_id" "uuid", "target_feed_id" "uuid", "result_limit" integer, "pagination_cursor" timestamp with time zone, "unread_only" boolean, "prioritise_unread" boolean, "cursor_is_read" boolean); Type: ACL; Schema: public; Owner: postgres
 --
 
-REVOKE ALL ON FUNCTION "public"."get_timeline"("target_folder_id" "uuid", "target_feed_id" "uuid", "result_limit" integer, "pagination_cursor" timestamp with time zone, "unread_only" boolean) FROM PUBLIC;
-GRANT ALL ON FUNCTION "public"."get_timeline"("target_folder_id" "uuid", "target_feed_id" "uuid", "result_limit" integer, "pagination_cursor" timestamp with time zone, "unread_only" boolean) TO "authenticated";
-GRANT ALL ON FUNCTION "public"."get_timeline"("target_folder_id" "uuid", "target_feed_id" "uuid", "result_limit" integer, "pagination_cursor" timestamp with time zone, "unread_only" boolean) TO "service_role";
+REVOKE ALL ON FUNCTION "public"."get_timeline"("target_folder_id" "uuid", "target_feed_id" "uuid", "result_limit" integer, "pagination_cursor" timestamp with time zone, "unread_only" boolean, "prioritise_unread" boolean, "cursor_is_read" boolean) FROM PUBLIC;
+GRANT ALL ON FUNCTION "public"."get_timeline"("target_folder_id" "uuid", "target_feed_id" "uuid", "result_limit" integer, "pagination_cursor" timestamp with time zone, "unread_only" boolean, "prioritise_unread" boolean, "cursor_is_read" boolean) TO "authenticated";
+GRANT ALL ON FUNCTION "public"."get_timeline"("target_folder_id" "uuid", "target_feed_id" "uuid", "result_limit" integer, "pagination_cursor" timestamp with time zone, "unread_only" boolean, "prioritise_unread" boolean, "cursor_is_read" boolean) TO "service_role";
 
 
 --