fix: prevent read entries from reverting to unread on re-fetch

Root cause: cleanup_stale_entries deleted read-but-unsaved entries from
active feeds, then the Go worker re-inserted them with new UUIDs,
orphaning the user_entry_states rows and making entries appear unread.

- cleanup_stale_entries: skip feeds with active subscribers and preserve
  entries that have been read (not just saved)
- Go parser: normalize GUIDs by trimming whitespace and stripping
  tracking query parameters from URL-based identifiers
- Go writer: preserve original published_at on upsert instead of
  overwriting, preventing old entries from jumping to timeline top
- get_unread_counts: apply same time boundary as get_timeline so
  ancient re-inserted entries don't inflate counts
- Realtime listener: ignore INSERT events for entries older than 48h
  to suppress misleading "new entries" notifications from re-inserts

1 files changed, 11 insertions, 3 deletions

diff --git a/supabase/schema.sql b/supabase/schema.sql
index 7d2fa6c..388154a 100644
--- a/supabase/schema.sql
+++ b/supabase/schema.sql
@@ -2,7 +2,7 @@
 -- PostgreSQL database dump
 --
 
--- \restrict WfBftBa2FBaIUQXqx7Cp1KF3Bwxh3O5RTj1axY30px9LdUnvAINNXEbxzkAHb3i
+-- \restrict wNSUmyvUwXt9aJkcfw8gLKUVSQuRUSZfaqWMfy8PywitG4rFxIkfWY9tkxN08cC
 
 -- Dumped from database version 17.6
 -- Dumped by pg_dump version 17.6
@@ -463,7 +463,10 @@ BEGIN
   DELETE FROM public.entries
   WHERE published_at < NOW() - INTERVAL '90 days'
     AND id NOT IN (
-      SELECT entry_id FROM public.user_entry_states WHERE saved = true
+      SELECT entry_id FROM public.user_entry_states WHERE saved = true OR read = true
+    )
+    AND feed_id NOT IN (
+      SELECT id FROM public.feeds WHERE subscriber_count > 0
     );
   GET DIAGNOSTICS deleted_count = ROW_COUNT;
   RETURN deleted_count;
@@ -824,6 +827,11 @@ CREATE OR REPLACE FUNCTION "public"."get_unread_counts"() RETURNS TABLE("feed_id
       AND ues.user_id = auth.uid()
       AND ues.read = true
   )
+  AND (e.owner_id IS NULL OR e.owner_id = auth.uid())
+  AND (
+    (SELECT tier FROM user_profiles WHERE id = auth.uid()) IN ('pro', 'developer')
+    OR e.published_at >= now() - interval '14 days'
+  )
   GROUP BY e.feed_id;
 $$;
 
@@ -3728,5 +3736,5 @@ ALTER DEFAULT PRIVILEGES FOR ROLE "postgres" IN SCHEMA "public" GRANT ALL ON TAB
 -- PostgreSQL database dump complete
 --
 
--- \unrestrict WfBftBa2FBaIUQXqx7Cp1KF3Bwxh3O5RTj1axY30px9LdUnvAINNXEbxzkAHb3i
+-- \unrestrict wNSUmyvUwXt9aJkcfw8gLKUVSQuRUSZfaqWMfy8PywitG4rFxIkfWY9tkxN08cC