chore: update schema dump with authenticated feed trigger changes and new functions

1 files changed, 144 insertions, 5 deletions

diff --git a/supabase/schema.sql b/supabase/schema.sql
index 517b69f..0d183e5 100644
--- a/supabase/schema.sql
+++ b/supabase/schema.sql
@@ -1,5 +1,5 @@
 -- asa.news database schema
--- dumped 2026-02-07 from live supabase instance (kbugptrwjnenmgkhdofn)
+-- dumped 2026-02-08 from live supabase instance (kbugptrwjnenmgkhdofn)
 -- this file is for reference only — migrations are applied via supabase mcp
 
 -- =============================================================================
@@ -166,7 +166,7 @@ CREATE TABLE public.api_keys (
   id uuid NOT NULL DEFAULT gen_random_uuid(),
   user_id uuid NOT NULL,
   key_hash text NOT NULL,
-  key_prefix character NOT NULL,
+  key_prefix character(8) NOT NULL,
   label text,
   created_at timestamp with time zone NOT NULL DEFAULT now(),
   last_used_at timestamp with time zone,
@@ -201,6 +201,28 @@ ALTER TABLE public.subscriptions ADD CONSTRAINT subscriptions_user_id_feed_id_ke
 ALTER TABLE public.user_profiles ADD CONSTRAINT user_profiles_stripe_customer_identifier_key UNIQUE (stripe_customer_identifier);
 
 -- =============================================================================
+-- foreign keys
+-- =============================================================================
+
+ALTER TABLE public.api_keys ADD CONSTRAINT api_keys_user_id_fkey FOREIGN KEY (user_id) REFERENCES auth.users(id);
+ALTER TABLE public.custom_feeds ADD CONSTRAINT custom_feeds_user_id_fkey FOREIGN KEY (user_id) REFERENCES auth.users(id);
+ALTER TABLE public.custom_feeds ADD CONSTRAINT custom_feeds_source_folder_id_fkey FOREIGN KEY (source_folder_id) REFERENCES public.folders(id);
+ALTER TABLE public.entries ADD CONSTRAINT entries_feed_id_fkey FOREIGN KEY (feed_id) REFERENCES public.feeds(id);
+ALTER TABLE public.entries ADD CONSTRAINT entries_owner_id_fkey FOREIGN KEY (owner_id) REFERENCES auth.users(id);
+ALTER TABLE public.folders ADD CONSTRAINT folders_user_id_fkey FOREIGN KEY (user_id) REFERENCES auth.users(id);
+ALTER TABLE public.muted_keywords ADD CONSTRAINT muted_keywords_user_id_fkey FOREIGN KEY (user_id) REFERENCES auth.users(id);
+ALTER TABLE public.shared_entries ADD CONSTRAINT shared_entries_user_id_fkey FOREIGN KEY (user_id) REFERENCES auth.users(id);
+ALTER TABLE public.shared_entries ADD CONSTRAINT shared_entries_entry_id_fkey FOREIGN KEY (entry_id) REFERENCES public.entries(id);
+ALTER TABLE public.subscriptions ADD CONSTRAINT subscriptions_user_id_fkey FOREIGN KEY (user_id) REFERENCES auth.users(id);
+ALTER TABLE public.subscriptions ADD CONSTRAINT subscriptions_feed_id_fkey FOREIGN KEY (feed_id) REFERENCES public.feeds(id);
+ALTER TABLE public.subscriptions ADD CONSTRAINT subscriptions_folder_id_fkey FOREIGN KEY (folder_id) REFERENCES public.folders(id);
+ALTER TABLE public.user_entry_states ADD CONSTRAINT user_entry_states_user_id_fkey FOREIGN KEY (user_id) REFERENCES auth.users(id);
+ALTER TABLE public.user_entry_states ADD CONSTRAINT user_entry_states_entry_id_fkey FOREIGN KEY (entry_id) REFERENCES public.entries(id);
+ALTER TABLE public.user_highlights ADD CONSTRAINT user_highlights_user_id_fkey FOREIGN KEY (user_id) REFERENCES auth.users(id);
+ALTER TABLE public.user_highlights ADD CONSTRAINT user_highlights_entry_id_fkey FOREIGN KEY (entry_id) REFERENCES public.entries(id);
+ALTER TABLE public.user_profiles ADD CONSTRAINT user_profiles_id_fkey FOREIGN KEY (id) REFERENCES auth.users(id);
+
+-- =============================================================================
 -- indexes
 -- =============================================================================
 
@@ -402,6 +424,81 @@ begin
 end;
 $function$;
 
+CREATE OR REPLACE FUNCTION public.subscribe_to_feed(feed_url text, target_folder_id uuid DEFAULT NULL::uuid, feed_custom_title text DEFAULT NULL::text, feed_credential text DEFAULT NULL::text, feed_auth_type text DEFAULT 'bearer'::text)
+ RETURNS uuid
+ LANGUAGE plpgsql
+ SECURITY DEFINER
+ SET search_path TO ''
+AS $function$
+declare
+  resolved_feed_id uuid;
+  new_subscription_id uuid;
+  resolved_vault_id uuid;
+  resolved_visibility public.feed_visibility;
+begin
+  if feed_credential is not null and feed_auth_type not in ('bearer', 'basic', 'query_param') then
+    raise exception 'Invalid authentication type: %. Must be bearer, basic, or query_param', feed_auth_type;
+  end if;
+
+  if feed_credential is not null then
+    resolved_visibility := 'authenticated';
+  else
+    resolved_visibility := 'public';
+  end if;
+
+  if resolved_visibility = 'public' then
+    select id into resolved_feed_id
+    from public.feeds
+    where url = feed_url and visibility = 'public';
+
+    if resolved_feed_id is null then
+      insert into public.feeds (url, visibility)
+      values (feed_url, 'public')
+      returning id into resolved_feed_id;
+    end if;
+  else
+    insert into public.feeds (url, visibility, fetch_interval_seconds)
+    values (feed_url, 'authenticated', 300)
+    returning id into resolved_feed_id;
+
+    insert into vault.secrets (name, description, secret)
+    values (
+      'feed_credential_' || resolved_feed_id::text,
+      'Credential for authenticated feed',
+      jsonb_build_object(
+        'authenticationType', feed_auth_type,
+        'authenticationValue', feed_credential
+      )::text
+    )
+    returning id into resolved_vault_id;
+  end if;
+
+  insert into public.subscriptions (user_id, feed_id, folder_id, custom_title, vault_secret_id)
+  values (auth.uid(), resolved_feed_id, target_folder_id, feed_custom_title, resolved_vault_id)
+  returning id into new_subscription_id;
+
+  return new_subscription_id;
+end;
+$function$;
+
+CREATE OR REPLACE FUNCTION public.update_feed_url(target_feed_id uuid, new_url text)
+ RETURNS void
+ LANGUAGE plpgsql
+ SECURITY DEFINER
+AS $function$
+BEGIN
+  IF NOT EXISTS (
+    SELECT 1 FROM subscriptions
+    WHERE feed_id = target_feed_id
+    AND user_id = auth.uid()
+  ) THEN
+    RAISE EXCEPTION 'not subscribed to this feed';
+  END IF;
+
+  UPDATE feeds SET url = new_url WHERE id = target_feed_id;
+END;
+$function$;
+
 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)
  LANGUAGE sql
@@ -451,6 +548,36 @@ AS $function$
   LIMIT result_limit;
 $function$;
 
+CREATE OR REPLACE FUNCTION public.get_custom_feed(target_custom_feed_id uuid, result_limit integer DEFAULT 50, pagination_cursor timestamp with time zone DEFAULT NULL::timestamp with time zone)
+ RETURNS TABLE(entry_id uuid, feed_id uuid, feed_title text, entry_title text, entry_url text, author text, summary text, published_at timestamp with time zone, is_read boolean, is_saved boolean)
+ LANGUAGE sql
+ STABLE SECURITY DEFINER
+ SET search_path TO ''
+AS $function$
+  select
+    e.id, e.feed_id, f.title, e.title, e.url, e.author, e.summary,
+    e.published_at,
+    coalesce(ues.read, false),
+    coalesce(ues.saved, false)
+  from public.entries e
+  inner join public.subscriptions s
+    on s.feed_id = e.feed_id and s.user_id = auth.uid()
+  inner join public.feeds f on f.id = e.feed_id
+  inner join public.custom_feeds cf on cf.id = target_custom_feed_id and cf.user_id = auth.uid()
+  left join public.user_entry_states ues
+    on ues.entry_id = e.id and ues.user_id = auth.uid()
+  where
+    (e.owner_id is null or e.owner_id = auth.uid())
+    and (pagination_cursor is null or e.published_at < pagination_cursor)
+    and (
+      e.title ilike '%' || cf.query || '%'
+      or e.summary ilike '%' || cf.query || '%'
+      or e.content_text ilike '%' || cf.query || '%'
+    )
+  order by e.published_at desc nulls last
+  limit result_limit;
+$function$;
+
 CREATE OR REPLACE FUNCTION public.get_custom_feed_timeline(p_custom_feed_id uuid, p_result_limit integer DEFAULT 50, p_pagination_cursor timestamp with time zone DEFAULT NULL::timestamp with time zone)
  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 plpgsql
@@ -681,7 +808,7 @@ begin
 
   update public.feeds
   set subscriber_count = subscriber_count + 1
-  where id = new.feed_id and visibility = 'public';
+  where id = new.feed_id;
 
   return new;
 end;
@@ -794,6 +921,8 @@ CREATE OR REPLACE FUNCTION public.decrement_subscription_count()
  SECURITY DEFINER
  SET search_path TO ''
 AS $function$
+declare
+  feed_vis public.feed_visibility;
 begin
   update public.user_profiles
   set feed_count = greatest(feed_count - 1, 0)
@@ -801,7 +930,17 @@ begin
 
   update public.feeds
   set subscriber_count = greatest(subscriber_count - 1, 0)
-  where id = old.feed_id and visibility = 'public';
+  where id = old.feed_id;
+
+  select visibility into feed_vis from public.feeds where id = old.feed_id;
+
+  if feed_vis = 'authenticated' then
+    delete from public.entries where feed_id = old.feed_id and owner_id = old.user_id;
+    if old.vault_secret_id is not null then
+      delete from vault.secrets where id = old.vault_secret_id;
+    end if;
+    delete from public.feeds where id = old.feed_id;
+  end if;
 
   return old;
 end;
@@ -953,7 +1092,7 @@ CREATE TRIGGER check_highlight_limit_trigger BEFORE INSERT ON public.user_highli
 CREATE TRIGGER decrement_highlight_count_trigger AFTER DELETE ON public.user_highlights FOR EACH ROW EXECUTE FUNCTION decrement_highlight_count();
 
 -- =============================================================================
--- triggers (auth schema — creates user profile on signup)
+-- triggers (auth schema -- creates user profile on signup)
 -- =============================================================================
 
 CREATE TRIGGER on_auth_user_created AFTER INSERT ON auth.users FOR EACH ROW EXECUTE FUNCTION handle_new_user();