fix: P0 correctness and security fixes

- Add missing 'developer' case to check_custom_feed_limit trigger (was falling through to else 1)
- Scope user_entry_states join to authenticated user in /api/v1/entries (admin client bypasses RLS)
- Replace in-memory rate limiting with Supabase-backed solution (UNLOGGED table + check_rate_limit RPC + pg_cron cleanup)

1 files changed, 45 insertions, 1 deletions

diff --git a/supabase/schema.sql b/supabase/schema.sql
index 6603c36..fb5f3ab 100644
--- a/supabase/schema.sql
+++ b/supabase/schema.sql
@@ -946,7 +946,7 @@ begin
   select tier, custom_feed_count into current_tier, current_count
   from public.user_profiles where id = new.user_id;
 
-  maximum_allowed := case current_tier when 'free' then 1 when 'pro' then 1000 else 1 end;
+  maximum_allowed := case current_tier when 'free' then 1 when 'pro' then 1000 when 'developer' then 1000 else 1 end;
 
   if current_count >= maximum_allowed then
     raise exception 'You have reached the maximum number of custom feeds for your plan (% on % tier)', maximum_allowed, current_tier;
@@ -1200,3 +1200,47 @@ SELECT cron.schedule('purge-orphaned-feeds', '30 4 * * *', $$
       and subscriber_count = 0
       and updated_at < now() - interval '7 days';
   $$);
+
+-- every 5 minutes: cleanup expired rate limit windows
+SELECT cron.schedule('cleanup-rate-limits', '*/5 * * * *', $$DELETE FROM public.rate_limits WHERE window_start < now() - interval '10 minutes'$$);
+
+-- =============================================================================
+-- rate limiting
+-- =============================================================================
+
+CREATE UNLOGGED TABLE public.rate_limits (
+  identifier text NOT NULL,
+  window_start timestamptz NOT NULL,
+  request_count integer NOT NULL DEFAULT 1,
+  PRIMARY KEY (identifier, window_start)
+);
+
+CREATE OR REPLACE FUNCTION public.check_rate_limit(
+  p_identifier text,
+  p_limit integer,
+  p_window_seconds integer
+) RETURNS jsonb
+LANGUAGE plpgsql
+SECURITY DEFINER
+SET search_path TO 'public'
+AS $function$
+DECLARE
+  v_window_start timestamptz;
+  v_current_count integer;
+BEGIN
+  v_window_start := to_timestamp(
+    floor(extract(epoch from now()) / p_window_seconds) * p_window_seconds
+  );
+
+  INSERT INTO public.rate_limits (identifier, window_start, request_count)
+  VALUES (p_identifier, v_window_start, 1)
+  ON CONFLICT (identifier, window_start)
+  DO UPDATE SET request_count = rate_limits.request_count + 1
+  RETURNING request_count INTO v_current_count;
+
+  RETURN jsonb_build_object(
+    'success', v_current_count <= p_limit,
+    'remaining', greatest(p_limit - v_current_count, 0)
+  );
+END;
+$function$;