path: root/supabase/schema.sql

-- asa.news database schema
-- dumped 2026-02-08 from live supabase instance (kbugptrwjnenmgkhdofn)
-- this file is for reference only — migrations are applied via supabase mcp

-- =============================================================================
-- extensions
-- =============================================================================

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS moddatetime SCHEMA extensions;
CREATE EXTENSION IF NOT EXISTS pgmq;
CREATE EXTENSION IF NOT EXISTS pg_cron;
CREATE EXTENSION IF NOT EXISTS pgsodium;

-- =============================================================================
-- custom types
-- =============================================================================

CREATE TYPE public.feed_visibility AS ENUM ('public', 'authenticated');
CREATE TYPE public.subscription_tier AS ENUM ('free', 'pro', 'developer');

-- =============================================================================
-- tables
-- =============================================================================

CREATE TABLE public.feeds (
  id uuid NOT NULL DEFAULT gen_random_uuid(),
  url text NOT NULL,
  site_url text,
  title text,
  description text,
  language text,
  feed_type text,
  image_url text,
  visibility feed_visibility NOT NULL DEFAULT 'public'::feed_visibility,
  etag text,
  last_modified text,
  last_fetched_at timestamp with time zone,
  last_fetch_error text,
  last_fetch_error_at timestamp with time zone,
  consecutive_failures integer NOT NULL DEFAULT 0,
  next_fetch_at timestamp with time zone NOT NULL DEFAULT now(),
  fetch_interval_seconds integer NOT NULL DEFAULT 1800,
  subscriber_count integer NOT NULL DEFAULT 0,
  created_at timestamp with time zone NOT NULL DEFAULT now(),
  updated_at timestamp with time zone NOT NULL DEFAULT now()
);

CREATE TABLE public.user_profiles (
  id uuid NOT NULL,
  display_name text,
  tier subscription_tier NOT NULL DEFAULT 'free'::subscription_tier,
  feed_count integer NOT NULL DEFAULT 0,
  folder_count integer NOT NULL DEFAULT 0,
  muted_keyword_count integer NOT NULL DEFAULT 0,
  custom_feed_count integer NOT NULL DEFAULT 0,
  created_at timestamp with time zone NOT NULL DEFAULT now(),
  updated_at timestamp with time zone NOT NULL DEFAULT now(),
  stripe_customer_identifier text,
  stripe_subscription_identifier text,
  stripe_subscription_status text,
  stripe_current_period_end timestamp with time zone,
  highlight_count integer NOT NULL DEFAULT 0,
  webhook_url text,
  webhook_secret text,
  webhook_enabled boolean NOT NULL DEFAULT false,
  webhook_consecutive_failures integer NOT NULL DEFAULT 0
);

CREATE TABLE public.folders (
  id uuid NOT NULL DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL,
  name text NOT NULL,
  position integer NOT NULL DEFAULT 0,
  created_at timestamp with time zone NOT NULL DEFAULT now(),
  updated_at timestamp with time zone NOT NULL DEFAULT now(),
  icon_url text
);

CREATE TABLE public.subscriptions (
  id uuid NOT NULL DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL,
  feed_id uuid NOT NULL,
  folder_id uuid,
  custom_title text,
  position integer NOT NULL DEFAULT 0,
  vault_secret_id uuid,
  created_at timestamp with time zone NOT NULL DEFAULT now(),
  updated_at timestamp with time zone NOT NULL DEFAULT now()
);

CREATE TABLE public.entries (
  id uuid NOT NULL DEFAULT gen_random_uuid(),
  feed_id uuid NOT NULL,
  owner_id uuid,
  guid text NOT NULL,
  url text,
  title text,
  author text,
  summary text,
  content_html text,
  content_text text,
  image_url text,
  published_at timestamp with time zone,
  word_count integer,
  fetched_at timestamp with time zone NOT NULL DEFAULT now(),
  created_at timestamp with time zone NOT NULL DEFAULT now(),
  enclosure_url text,
  enclosure_type text,
  enclosure_length bigint
);

CREATE TABLE public.user_entry_states (
  user_id uuid NOT NULL,
  entry_id uuid NOT NULL,
  read boolean NOT NULL DEFAULT false,
  saved boolean NOT NULL DEFAULT false,
  read_at timestamp with time zone,
  saved_at timestamp with time zone
);

CREATE TABLE public.muted_keywords (
  id uuid NOT NULL DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL,
  keyword text NOT NULL,
  created_at timestamp with time zone NOT NULL DEFAULT now()
);

CREATE TABLE public.custom_feeds (
  id uuid NOT NULL DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL,
  name text NOT NULL,
  query text NOT NULL,
  position integer NOT NULL DEFAULT 0,
  created_at timestamp with time zone NOT NULL DEFAULT now(),
  updated_at timestamp with time zone NOT NULL DEFAULT now(),
  source_folder_id uuid,
  match_mode text NOT NULL DEFAULT 'or'::text,
  icon_url text
);

CREATE TABLE public.shared_entries (
  id uuid NOT NULL DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL,
  entry_id uuid NOT NULL,
  share_token text NOT NULL,
  created_at timestamp with time zone NOT NULL DEFAULT now(),
  expires_at timestamp with time zone,
  note text,
  highlighted_text text,
  highlight_text_offset integer,
  highlight_text_length integer,
  highlight_text_prefix text DEFAULT ''::text,
  highlight_text_suffix text DEFAULT ''::text
);

CREATE TABLE public.user_highlights (
  id uuid NOT NULL DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL,
  entry_id uuid NOT NULL,
  highlighted_text text NOT NULL,
  note text,
  text_offset integer NOT NULL,
  text_length integer NOT NULL,
  text_prefix text NOT NULL DEFAULT ''::text,
  text_suffix text NOT NULL DEFAULT ''::text,
  color text NOT NULL DEFAULT 'yellow'::text,
  created_at timestamp with time zone NOT NULL DEFAULT now()
);

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(8) NOT NULL,
  label text,
  created_at timestamp with time zone NOT NULL DEFAULT now(),
  last_used_at timestamp with time zone,
  revoked_at timestamp with time zone
);

-- =============================================================================
-- primary keys
-- =============================================================================

ALTER TABLE public.feeds ADD CONSTRAINT feeds_pkey PRIMARY KEY (id);
ALTER TABLE public.user_profiles ADD CONSTRAINT user_profiles_pkey PRIMARY KEY (id);
ALTER TABLE public.folders ADD CONSTRAINT folders_pkey PRIMARY KEY (id);
ALTER TABLE public.subscriptions ADD CONSTRAINT subscriptions_pkey PRIMARY KEY (id);
ALTER TABLE public.entries ADD CONSTRAINT entries_pkey PRIMARY KEY (id);
ALTER TABLE public.user_entry_states ADD CONSTRAINT user_entry_states_pkey PRIMARY KEY (user_id, entry_id);
ALTER TABLE public.muted_keywords ADD CONSTRAINT muted_keywords_pkey PRIMARY KEY (id);
ALTER TABLE public.custom_feeds ADD CONSTRAINT custom_feeds_pkey PRIMARY KEY (id);
ALTER TABLE public.shared_entries ADD CONSTRAINT shared_entries_pkey PRIMARY KEY (id);
ALTER TABLE public.user_highlights ADD CONSTRAINT user_highlights_pkey PRIMARY KEY (id);
ALTER TABLE public.api_keys ADD CONSTRAINT api_keys_pkey PRIMARY KEY (id);

-- =============================================================================
-- unique constraints
-- =============================================================================

ALTER TABLE public.api_keys ADD CONSTRAINT api_keys_key_hash_key UNIQUE (key_hash);
ALTER TABLE public.folders ADD CONSTRAINT folders_user_id_name_key UNIQUE (user_id, name);
ALTER TABLE public.shared_entries ADD CONSTRAINT shared_entries_share_token_key UNIQUE (share_token);
ALTER TABLE public.shared_entries ADD CONSTRAINT shared_entries_user_id_entry_id_key UNIQUE (user_id, entry_id);
ALTER TABLE public.subscriptions ADD CONSTRAINT subscriptions_user_id_feed_id_key UNIQUE (user_id, feed_id);
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
-- =============================================================================

-- feeds
CREATE UNIQUE INDEX idx_feeds_url_public ON public.feeds USING btree (url) WHERE (visibility = 'public'::feed_visibility);
CREATE INDEX idx_feeds_next_fetch ON public.feeds USING btree (next_fetch_at) WHERE (consecutive_failures < 10);

-- entries
CREATE UNIQUE INDEX idx_entries_dedup_public ON public.entries USING btree (feed_id, guid) WHERE (owner_id IS NULL);
CREATE UNIQUE INDEX idx_entries_dedup_authenticated ON public.entries USING btree (feed_id, owner_id, guid) WHERE (owner_id IS NOT NULL);
CREATE INDEX idx_entries_feed_published ON public.entries USING btree (feed_id, published_at DESC NULLS LAST);
CREATE INDEX idx_entries_published ON public.entries USING btree (published_at DESC NULLS LAST);
CREATE INDEX idx_entries_owner ON public.entries USING btree (owner_id, published_at DESC NULLS LAST) WHERE (owner_id IS NOT NULL);
CREATE INDEX idx_entries_title_trigram ON public.entries USING gin (title gin_trgm_ops);
CREATE INDEX idx_entries_content_trigram ON public.entries USING gin (content_text gin_trgm_ops);

-- subscriptions
CREATE INDEX idx_subscriptions_user ON public.subscriptions USING btree (user_id);
CREATE INDEX idx_subscriptions_feed ON public.subscriptions USING btree (feed_id);
CREATE INDEX idx_subscriptions_folder ON public.subscriptions USING btree (folder_id);

-- user_entry_states
CREATE INDEX idx_user_entry_states_entry ON public.user_entry_states USING btree (entry_id);
CREATE INDEX idx_user_entry_states_read ON public.user_entry_states USING btree (user_id, entry_id) WHERE (read = true);
CREATE INDEX idx_user_entry_states_saved ON public.user_entry_states USING btree (user_id, saved_at DESC) WHERE (saved = true);

-- folders
CREATE INDEX idx_folders_user ON public.folders USING btree (user_id);

-- muted_keywords
CREATE INDEX idx_muted_keywords_user ON public.muted_keywords USING btree (user_id);
CREATE UNIQUE INDEX idx_muted_keywords_user_keyword ON public.muted_keywords USING btree (user_id, lower(keyword));

-- custom_feeds
CREATE INDEX idx_custom_feeds_user ON public.custom_feeds USING btree (user_id);
CREATE INDEX idx_custom_feeds_source_folder ON public.custom_feeds USING btree (source_folder_id);

-- shared_entries
CREATE INDEX idx_shared_entries_entry ON public.shared_entries USING btree (entry_id);

-- user_highlights
CREATE INDEX user_highlights_user_entry_idx ON public.user_highlights USING btree (user_id, entry_id);
CREATE INDEX user_highlights_user_created_idx ON public.user_highlights USING btree (user_id, created_at DESC);
CREATE INDEX idx_user_highlights_entry ON public.user_highlights USING btree (entry_id);

-- api_keys
CREATE INDEX idx_api_keys_user_id ON public.api_keys USING btree (user_id);
CREATE INDEX idx_api_keys_key_hash ON public.api_keys USING btree (key_hash) WHERE (revoked_at IS NULL);

-- user_profiles
CREATE INDEX idx_user_profiles_stripe_customer ON public.user_profiles USING btree (stripe_customer_identifier) WHERE (stripe_customer_identifier IS NOT NULL);

-- =============================================================================
-- row level security
-- =============================================================================

ALTER TABLE public.api_keys ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.custom_feeds ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.entries ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.feeds ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.folders ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.muted_keywords ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.shared_entries ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.subscriptions ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.user_entry_states ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.user_highlights ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.user_profiles ENABLE ROW LEVEL SECURITY;

-- =============================================================================
-- rls policies
-- =============================================================================

-- api_keys
CREATE POLICY "Users can view own API keys" ON public.api_keys AS PERMISSIVE FOR SELECT USING ((auth.uid() = user_id));
CREATE POLICY "Users can insert own API keys" ON public.api_keys AS PERMISSIVE FOR INSERT WITH CHECK ((auth.uid() = user_id));
CREATE POLICY "Users can update own API keys" ON public.api_keys AS PERMISSIVE FOR UPDATE USING ((auth.uid() = user_id));
CREATE POLICY "Users can delete own API keys" ON public.api_keys AS PERMISSIVE FOR DELETE USING ((auth.uid() = user_id));

-- custom_feeds
CREATE POLICY "users can view own custom feeds" ON public.custom_feeds AS PERMISSIVE FOR SELECT USING ((user_id = (SELECT auth.uid())));
CREATE POLICY "users can insert own custom feeds" ON public.custom_feeds AS PERMISSIVE FOR INSERT WITH CHECK ((user_id = (SELECT auth.uid())));
CREATE POLICY "users can update own custom feeds" ON public.custom_feeds AS PERMISSIVE FOR UPDATE USING ((user_id = (SELECT auth.uid()))) WITH CHECK ((user_id = (SELECT auth.uid())));
CREATE POLICY "users can delete own custom feeds" ON public.custom_feeds AS PERMISSIVE FOR DELETE USING ((user_id = (SELECT auth.uid())));

-- entries
CREATE POLICY "users can view entries from subscribed public feeds" ON public.entries AS PERMISSIVE FOR SELECT
  USING ((
    (owner_id IS NULL AND EXISTS (SELECT 1 FROM subscriptions s WHERE s.feed_id = entries.feed_id AND s.user_id = (SELECT auth.uid())))
    OR (owner_id = (SELECT auth.uid()))
  ));

-- feeds
CREATE POLICY "users can view accessible feeds" ON public.feeds AS PERMISSIVE FOR SELECT
  USING ((
    (visibility = 'public'::feed_visibility AND (SELECT auth.role()) = 'authenticated')
    OR (visibility = 'authenticated'::feed_visibility AND EXISTS (SELECT 1 FROM subscriptions s WHERE s.feed_id = feeds.id AND s.user_id = (SELECT auth.uid())))
  ));

-- folders
CREATE POLICY "users can view own folders" ON public.folders AS PERMISSIVE FOR SELECT USING ((user_id = (SELECT auth.uid())));
CREATE POLICY "users can insert own folders" ON public.folders AS PERMISSIVE FOR INSERT WITH CHECK ((user_id = (SELECT auth.uid())));
CREATE POLICY "users can update own folders" ON public.folders AS PERMISSIVE FOR UPDATE USING ((user_id = (SELECT auth.uid()))) WITH CHECK ((user_id = (SELECT auth.uid())));
CREATE POLICY "users can delete own folders" ON public.folders AS PERMISSIVE FOR DELETE USING ((user_id = (SELECT auth.uid())));

-- muted_keywords
CREATE POLICY "users can view own muted keywords" ON public.muted_keywords AS PERMISSIVE FOR SELECT USING ((user_id = (SELECT auth.uid())));
CREATE POLICY "users can insert own muted keywords" ON public.muted_keywords AS PERMISSIVE FOR INSERT WITH CHECK ((user_id = (SELECT auth.uid())));
CREATE POLICY "users can delete own muted keywords" ON public.muted_keywords AS PERMISSIVE FOR DELETE USING ((user_id = (SELECT auth.uid())));

-- shared_entries
CREATE POLICY "authenticated users can view own shared entries" ON public.shared_entries AS PERMISSIVE FOR SELECT TO authenticated USING ((user_id = (SELECT auth.uid())));
CREATE POLICY "owners can insert own shared entries" ON public.shared_entries AS PERMISSIVE FOR INSERT WITH CHECK ((user_id = (SELECT auth.uid())));
CREATE POLICY "owners can update own shared entries" ON public.shared_entries AS PERMISSIVE FOR UPDATE USING ((user_id = (SELECT auth.uid()))) WITH CHECK ((user_id = (SELECT auth.uid())));
CREATE POLICY "owners can delete own shared entries" ON public.shared_entries AS PERMISSIVE FOR DELETE USING ((user_id = (SELECT auth.uid())));

-- subscriptions
CREATE POLICY "users can view own subscriptions" ON public.subscriptions AS PERMISSIVE FOR SELECT USING ((user_id = (SELECT auth.uid())));
CREATE POLICY "users can insert own subscriptions" ON public.subscriptions AS PERMISSIVE FOR INSERT WITH CHECK ((user_id = (SELECT auth.uid())));
CREATE POLICY "users can update own subscriptions" ON public.subscriptions AS PERMISSIVE FOR UPDATE USING ((user_id = (SELECT auth.uid()))) WITH CHECK ((user_id = (SELECT auth.uid())));
CREATE POLICY "users can delete own subscriptions" ON public.subscriptions AS PERMISSIVE FOR DELETE USING ((user_id = (SELECT auth.uid())));

-- user_entry_states
CREATE POLICY "users can manage own entry states" ON public.user_entry_states AS PERMISSIVE FOR ALL USING ((user_id = (SELECT auth.uid()))) WITH CHECK ((user_id = (SELECT auth.uid())));

-- user_highlights
CREATE POLICY "users can view own highlights" ON public.user_highlights AS PERMISSIVE FOR SELECT USING ((user_id = (SELECT auth.uid())));
CREATE POLICY "users can insert own highlights" ON public.user_highlights AS PERMISSIVE FOR INSERT WITH CHECK ((user_id = (SELECT auth.uid())));
CREATE POLICY "users can update own highlights" ON public.user_highlights AS PERMISSIVE FOR UPDATE USING ((user_id = (SELECT auth.uid()))) WITH CHECK ((user_id = (SELECT auth.uid())));
CREATE POLICY "users can delete own highlights" ON public.user_highlights AS PERMISSIVE FOR DELETE USING ((user_id = (SELECT auth.uid())));

-- user_profiles
CREATE POLICY "users can view own profile" ON public.user_profiles AS PERMISSIVE FOR SELECT USING ((id = (SELECT auth.uid())));
CREATE POLICY "users can update own profile" ON public.user_profiles AS PERMISSIVE FOR UPDATE USING ((id = (SELECT auth.uid()))) WITH CHECK ((id = (SELECT auth.uid())));

-- =============================================================================
-- functions
-- =============================================================================

CREATE OR REPLACE FUNCTION public.handle_new_user()
 RETURNS trigger
 LANGUAGE plpgsql
 SECURITY DEFINER
 SET search_path TO ''
AS $function$
begin
  insert into public.user_profiles (id, display_name)
  values (new.id, coalesce(new.raw_user_meta_data ->> 'display_name', split_part(new.email, '@', 1)));

  return new;
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;
  existing_subscription_id uuid;
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;

    select id into existing_subscription_id
    from public.subscriptions
    where user_id = auth.uid() and feed_id = resolved_feed_id;

    if existing_subscription_id is not null then
      raise exception 'You are already subscribed to this feed';
    end if;
  else
    insert into public.feeds (url, visibility, fetch_interval_seconds)
    values (feed_url, 'authenticated', 300)
    returning id into resolved_feed_id;

    resolved_vault_id := vault.create_secret(
      jsonb_build_object(
        'authenticationType', feed_auth_type,
        'authenticationValue', feed_credential
      )::text,
      'feed_credential_' || resolved_feed_id::text,
      'Credential for authenticated feed'
    );
  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_credentials(p_feed_id uuid, p_auth_type text, p_credential text)
 RETURNS void
 LANGUAGE plpgsql
 SECURITY DEFINER
 SET search_path TO ''
AS $function$
declare
  v_vault_secret_id uuid;
begin
  if p_auth_type not in ('bearer', 'basic', 'query_param') then
    raise exception 'Invalid authentication type: %. Must be bearer, basic, or query_param', p_auth_type;
  end if;

  select s.vault_secret_id into v_vault_secret_id
  from public.subscriptions s
  where s.feed_id = p_feed_id and s.user_id = auth.uid();

  if v_vault_secret_id is null then
    raise exception 'No authenticated subscription found for this feed';
  end if;

  perform vault.update_secret(
    v_vault_secret_id,
    jsonb_build_object(
      'authenticationType', p_auth_type,
      'authenticationValue', p_credential
    )::text
  );
end;
$function$;

CREATE OR REPLACE FUNCTION public.add_feed_credentials(p_subscription_id uuid, p_auth_type text, p_credential text)
 RETURNS void
 LANGUAGE plpgsql
 SECURITY DEFINER
 SET search_path TO ''
AS $function$
declare
  v_old_feed_id uuid;
  v_feed_url text;
  v_new_feed_id uuid;
  v_vault_secret_id uuid;
begin
  if p_auth_type not in ('bearer', 'basic', 'query_param') then
    raise exception 'Invalid authentication type: %. Must be bearer, basic, or query_param', p_auth_type;
  end if;

  select s.feed_id into v_old_feed_id
  from public.subscriptions s
  where s.id = p_subscription_id and s.user_id = auth.uid();

  if v_old_feed_id is null then
    raise exception 'Subscription not found';
  end if;

  select f.url into v_feed_url
  from public.feeds f
  where f.id = v_old_feed_id;

  insert into public.feeds (url, visibility, fetch_interval_seconds)
  values (v_feed_url, 'authenticated', 300)
  returning id into v_new_feed_id;

  v_vault_secret_id := vault.create_secret(
    jsonb_build_object(
      'authenticationType', p_auth_type,
      'authenticationValue', p_credential
    )::text,
    'feed_credential_' || v_new_feed_id::text,
    'Credential for authenticated feed'
  );

  update public.subscriptions
  set feed_id = v_new_feed_id, vault_secret_id = v_vault_secret_id
  where id = p_subscription_id;

  update public.feeds
  set subscriber_count = subscriber_count + 1
  where id = v_new_feed_id;

  update public.feeds
  set subscriber_count = greatest(subscriber_count - 1, 0)
  where id = v_old_feed_id;
end;
$function$;

CREATE OR REPLACE FUNCTION public.update_feed_url(target_feed_id uuid, new_url text)
 RETURNS void
 LANGUAGE plpgsql
 SECURITY DEFINER
 SET search_path TO ''
AS $function$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM public.subscriptions
    WHERE feed_id = target_feed_id
    AND user_id = auth.uid()
  ) THEN
    RAISE EXCEPTION 'not subscribed to this feed';
  END IF;

  UPDATE public.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
 STABLE SECURITY DEFINER
 SET search_path TO ''
AS $function$
  SELECT
    e.id AS entry_id,
    e.feed_id,
    f.title AS feed_title,
    s.custom_title,
    e.title AS entry_title,
    e.url AS entry_url,
    e.author,
    e.summary,
    e.image_url,
    e.published_at,
    COALESCE(ues.read, false) AS is_read,
    COALESCE(ues.saved, false) AS is_saved,
    e.enclosure_url,
    e.enclosure_type
  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
  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 (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 (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
      WHERE mk.user_id = auth.uid()
      AND (
        e.title ILIKE '%' || mk.keyword || '%'
        OR e.summary ILIKE '%' || mk.keyword || '%'
      )
    )
    AND (
      (SELECT tier FROM public.user_profiles WHERE id = auth.uid()) = 'pro'
      OR e.published_at >= now() - interval '14 days'
    )
  ORDER BY e.published_at DESC NULLS LAST
  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
 STABLE SECURITY DEFINER
 SET search_path TO ''
AS $function$
DECLARE
  v_query TEXT;
  v_match_mode TEXT;
  v_source_folder_id UUID;
  v_keywords TEXT[];
  v_user_id UUID := auth.uid();
  v_user_tier public.subscription_tier;
BEGIN
  SELECT cf.query, cf.match_mode, cf.source_folder_id
  INTO v_query, v_match_mode, v_source_folder_id
  FROM public.custom_feeds cf
  WHERE cf.id = p_custom_feed_id AND cf.user_id = v_user_id;

  IF NOT FOUND THEN
    RETURN;
  END IF;

  SELECT tier INTO v_user_tier FROM public.user_profiles WHERE id = v_user_id;

  v_keywords := string_to_array(lower(v_query), ' ');

  RETURN QUERY
  SELECT
    e.id AS entry_id,
    e.feed_id,
    f.title AS feed_title,
    s.custom_title,
    e.title AS entry_title,
    e.url AS entry_url,
    e.author,
    e.summary,
    e.image_url,
    e.published_at,
    COALESCE(ues.read, false) AS is_read,
    COALESCE(ues.saved, false) AS is_saved,
    e.enclosure_url,
    e.enclosure_type
  FROM public.entries e
  INNER JOIN public.subscriptions s
    ON s.feed_id = e.feed_id AND s.user_id = v_user_id
  INNER JOIN public.feeds f ON f.id = e.feed_id
  LEFT JOIN public.user_entry_states ues
    ON ues.entry_id = e.id AND ues.user_id = v_user_id
  WHERE
    (e.owner_id IS NULL OR e.owner_id = v_user_id)
    AND (v_source_folder_id IS NULL OR s.folder_id = v_source_folder_id)
    AND (p_pagination_cursor IS NULL OR e.published_at < p_pagination_cursor)
    AND (
      CASE WHEN v_match_mode = 'and' THEN
        (SELECT bool_and(
          lower(COALESCE(e.title, '') || ' ' || COALESCE(e.summary, ''))
            LIKE '%' || replace(replace(replace(kw, '\', '\\'), '%', '\%'), '_', '\_') || '%' ESCAPE '\'
        ) FROM unnest(v_keywords) AS kw)
      ELSE
        (SELECT bool_or(
          lower(COALESCE(e.title, '') || ' ' || COALESCE(e.summary, ''))
            LIKE '%' || replace(replace(replace(kw, '\', '\\'), '%', '\%'), '_', '\_') || '%' ESCAPE '\'
        ) FROM unnest(v_keywords) AS kw)
      END
    )
    AND NOT EXISTS (
      SELECT 1 FROM public.muted_keywords mk
      WHERE mk.user_id = v_user_id
      AND (
        e.title ILIKE '%' || replace(replace(replace(mk.keyword, '\', '\\'), '%', '\%'), '_', '\_') || '%' ESCAPE '\'
        OR e.summary ILIKE '%' || replace(replace(replace(mk.keyword, '\', '\\'), '%', '\%'), '_', '\_') || '%' ESCAPE '\'
      )
    )
    AND (
      v_user_tier IN ('pro', 'developer')
      OR e.published_at >= now() - interval '14 days'
    )
  ORDER BY e.published_at DESC NULLS LAST
  LIMIT p_result_limit;
END;
$function$;

CREATE OR REPLACE FUNCTION public.get_unread_counts()
 RETURNS TABLE(feed_id uuid, unread_count bigint)
 LANGUAGE sql
 STABLE SECURITY DEFINER
 SET search_path TO 'public'
AS $function$
  SELECT e.feed_id, COUNT(*) AS unread_count
  FROM entries e
  INNER JOIN subscriptions s ON s.feed_id = e.feed_id AND s.user_id = auth.uid()
  WHERE NOT EXISTS (
    SELECT 1 FROM user_entry_states ues
    WHERE ues.entry_id = e.id
      AND ues.user_id = auth.uid()
      AND ues.read = true
  )
  GROUP BY e.feed_id;
$function$;

CREATE OR REPLACE FUNCTION public.mark_all_as_read(p_feed_id uuid DEFAULT NULL::uuid, p_folder_id uuid DEFAULT NULL::uuid, p_read_state boolean DEFAULT true)
 RETURNS bigint
 LANGUAGE plpgsql
 SECURITY DEFINER
 SET search_path TO 'public'
AS $function$
DECLARE
  affected_count bigint;
BEGIN
  IF p_read_state THEN
    INSERT INTO user_entry_states (user_id, entry_id, read, saved)
    SELECT auth.uid(), e.id, true, false
    FROM entries e
    INNER JOIN subscriptions s ON s.feed_id = e.feed_id AND s.user_id = auth.uid()
    LEFT JOIN user_entry_states ues ON ues.entry_id = e.id AND ues.user_id = auth.uid()
    WHERE (ues.read IS NULL OR ues.read = false)
      AND (p_feed_id IS NULL OR e.feed_id = p_feed_id)
      AND (p_folder_id IS NULL OR s.folder_id = p_folder_id)
    ON CONFLICT (user_id, entry_id)
    DO UPDATE SET read = true;
  ELSE
    UPDATE user_entry_states
    SET read = false
    WHERE user_id = auth.uid()
      AND read = true
      AND entry_id IN (
        SELECT e.id
        FROM entries e
        INNER JOIN subscriptions s ON s.feed_id = e.feed_id AND s.user_id = auth.uid()
        WHERE (p_feed_id IS NULL OR e.feed_id = p_feed_id)
          AND (p_folder_id IS NULL OR s.folder_id = p_folder_id)
      );
  END IF;

  GET DIAGNOSTICS affected_count = ROW_COUNT;
  RETURN affected_count;
END;
$function$;

CREATE OR REPLACE FUNCTION public.search_entries(p_query text, p_result_limit integer DEFAULT 30)
 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)
 LANGUAGE sql
 STABLE SECURITY DEFINER
 SET search_path TO ''
AS $function$
  SELECT
    e.id AS entry_id,
    e.feed_id,
    COALESCE(f.title, '') AS feed_title,
    s.custom_title,
    COALESCE(e.title, '') AS entry_title,
    COALESCE(e.url, '') AS entry_url,
    e.author,
    e.summary,
    e.image_url,
    e.published_at,
    COALESCE(ues.read, false) AS is_read,
    COALESCE(ues.saved, false) AS is_saved
  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
  LEFT JOIN public.user_entry_states ues ON ues.entry_id = e.id AND ues.user_id = auth.uid()
  WHERE (
    e.title ILIKE '%' || replace(replace(replace(p_query, '\', '\\'), '%', '\%'), '_', '\_') || '%' ESCAPE '\'
    OR e.summary ILIKE '%' || replace(replace(replace(p_query, '\', '\\'), '%', '\%'), '_', '\_') || '%' ESCAPE '\'
    OR e.author ILIKE '%' || replace(replace(replace(p_query, '\', '\\'), '%', '\%'), '_', '\_') || '%' ESCAPE '\'
  )
  AND (
    (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
  LIMIT p_result_limit;
$function$;

CREATE OR REPLACE FUNCTION public.request_feed_refresh(target_subscription_id uuid)
 RETURNS void
 LANGUAGE plpgsql
 SECURITY DEFINER
 SET search_path TO ''
AS $function$
declare
  resolved_feed_id uuid;
  resolved_tier public.subscription_tier;
begin
  select s.feed_id into resolved_feed_id
  from public.subscriptions s
  where s.id = target_subscription_id and s.user_id = auth.uid();

  if resolved_feed_id is null then
    raise exception 'Subscription not found';
  end if;

  select tier into resolved_tier from public.user_profiles where id = auth.uid();

  if resolved_tier = 'free' then
    raise exception 'Manual feed refresh requires a Pro subscription';
  end if;

  perform pgmq.send(
    'feed_refresh',
    jsonb_build_object(
      'feed_id', resolved_feed_id,
      'user_id', auth.uid(),
      'requested_at', now()
    )
  );
end;
$function$;

CREATE OR REPLACE FUNCTION public.check_subscription_limit()
 RETURNS trigger
 LANGUAGE plpgsql
 SECURITY DEFINER
 SET search_path TO ''
AS $function$
declare
  current_tier public.subscription_tier;
  current_count int;
  maximum_allowed int;
  feed_visibility public.feed_visibility;
begin
  select tier, feed_count into current_tier, current_count
  from public.user_profiles where id = new.user_id;

  maximum_allowed := case current_tier when 'free' then 10 when 'pro' then 200 when 'developer' then 500 else 10 end;

  if current_count >= maximum_allowed then
    raise exception 'You have reached the maximum number of feeds for your plan (% on % tier)', maximum_allowed, current_tier;
  end if;

  select visibility into feed_visibility from public.feeds where id = new.feed_id;

  if current_tier = 'free' and feed_visibility = 'authenticated' then
    raise exception 'Authenticated feeds require a Pro subscription';
  end if;

  update public.user_profiles set feed_count = feed_count + 1 where id = new.user_id;

  update public.feeds
  set subscriber_count = subscriber_count + 1
  where id = new.feed_id;

  return new;
end;
$function$;

CREATE OR REPLACE FUNCTION public.check_folder_limit()
 RETURNS trigger
 LANGUAGE plpgsql
 SECURITY DEFINER
 SET search_path TO ''
AS $function$
declare
  current_tier public.subscription_tier;
  current_count int;
  maximum_allowed int;
begin
  select tier, folder_count into current_tier, current_count
  from public.user_profiles where id = new.user_id;

  maximum_allowed := case current_tier when 'free' then 3 when 'pro' then 10000 when 'developer' then 10000 else 3 end;

  if current_count >= maximum_allowed then
    raise exception 'You have reached the maximum number of folders for your plan (% on % tier)', maximum_allowed, current_tier;
  end if;

  update public.user_profiles set folder_count = folder_count + 1 where id = new.user_id;

  return new;
end;
$function$;

CREATE OR REPLACE FUNCTION public.check_muted_keyword_limit()
 RETURNS trigger
 LANGUAGE plpgsql
 SECURITY DEFINER
 SET search_path TO ''
AS $function$
declare
  current_tier public.subscription_tier;
  current_count int;
  maximum_allowed int;
begin
  select tier, muted_keyword_count into current_tier, current_count
  from public.user_profiles where id = new.user_id;

  maximum_allowed := case current_tier when 'free' then 5 when 'pro' then 10000 when 'developer' then 10000 else 5 end;

  if current_count >= maximum_allowed then
    raise exception 'You have reached the maximum number of muted keywords for your plan (% on % tier)', maximum_allowed, current_tier;
  end if;

  update public.user_profiles set muted_keyword_count = muted_keyword_count + 1 where id = new.user_id;

  return new;
end;
$function$;

CREATE OR REPLACE FUNCTION public.check_custom_feed_limit()
 RETURNS trigger
 LANGUAGE plpgsql
 SECURITY DEFINER
 SET search_path TO ''
AS $function$
declare
  current_tier public.subscription_tier;
  current_count int;
  maximum_allowed int;
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;

  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;
  end if;

  update public.user_profiles set custom_feed_count = custom_feed_count + 1 where id = new.user_id;

  return new;
end;
$function$;

CREATE OR REPLACE FUNCTION public.check_highlight_limit()
 RETURNS trigger
 LANGUAGE plpgsql
 SECURITY DEFINER
AS $function$
declare
  current_tier public.subscription_tier;
  current_count int;
  maximum_allowed int;
begin
  select tier, highlight_count into current_tier, current_count
  from public.user_profiles where id = new.user_id;

  maximum_allowed := case current_tier when 'free' then 50 when 'pro' then 100000 when 'developer' then 100000 else 50 end;

  if current_count >= maximum_allowed then
    raise exception 'You have reached the maximum number of highlights for your plan (% on % tier)', maximum_allowed, current_tier;
  end if;

  update public.user_profiles set highlight_count = highlight_count + 1 where id = new.user_id;

  return new;
end;
$function$;

CREATE OR REPLACE FUNCTION public.decrement_subscription_count()
 RETURNS trigger
 LANGUAGE plpgsql
 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)
  where id = old.user_id;

  update public.feeds
  set subscriber_count = greatest(subscriber_count - 1, 0)
  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;
$function$;

CREATE OR REPLACE FUNCTION public.decrement_folder_count()
 RETURNS trigger
 LANGUAGE plpgsql
 SECURITY DEFINER
 SET search_path TO ''
AS $function$
begin
  update public.user_profiles
  set folder_count = greatest(folder_count - 1, 0)
  where id = old.user_id;

  return old;
end;
$function$;

CREATE OR REPLACE FUNCTION public.decrement_muted_keyword_count()
 RETURNS trigger
 LANGUAGE plpgsql
 SECURITY DEFINER
 SET search_path TO ''
AS $function$
begin
  update public.user_profiles
  set muted_keyword_count = greatest(muted_keyword_count - 1, 0)
  where id = old.user_id;

  return old;
end;
$function$;

CREATE OR REPLACE FUNCTION public.decrement_custom_feed_count()
 RETURNS trigger
 LANGUAGE plpgsql
 SECURITY DEFINER
 SET search_path TO ''
AS $function$
begin
  update public.user_profiles
  set custom_feed_count = greatest(custom_feed_count - 1, 0)
  where id = old.user_id;

  return old;
end;
$function$;

CREATE OR REPLACE FUNCTION public.decrement_highlight_count()
 RETURNS trigger
 LANGUAGE plpgsql
 SECURITY DEFINER
 SET search_path TO ''
AS $function$
begin
  update public.user_profiles
  set highlight_count = greatest(highlight_count - 1, 0)
  where id = old.user_id;

  return old;
end;
$function$;

CREATE OR REPLACE FUNCTION public.enqueue_feed_refresh_on_subscribe()
 RETURNS trigger
 LANGUAGE plpgsql
 SECURITY DEFINER
AS $function$
BEGIN
  PERFORM pgmq.send('feed_refresh', jsonb_build_object('feed_id', NEW.feed_id));
  RETURN NEW;
END;
$function$;

CREATE OR REPLACE FUNCTION public.prevent_billing_column_modification()
 RETURNS trigger
 LANGUAGE plpgsql
 SET search_path TO 'public'
AS $function$
BEGIN
  IF coalesce(auth.role(), '') != 'service_role' THEN
    IF NEW.tier IS DISTINCT FROM OLD.tier
      OR NEW.stripe_customer_identifier IS DISTINCT FROM OLD.stripe_customer_identifier
      OR NEW.stripe_subscription_identifier IS DISTINCT FROM OLD.stripe_subscription_identifier
      OR NEW.stripe_subscription_status IS DISTINCT FROM OLD.stripe_subscription_status
      OR NEW.stripe_current_period_end IS DISTINCT FROM OLD.stripe_current_period_end
    THEN
      RAISE EXCEPTION 'Billing columns cannot be modified directly';
    END IF;
  END IF;
  RETURN NEW;
END;
$function$;

CREATE OR REPLACE FUNCTION public.cleanup_stale_entries()
 RETURNS integer
 LANGUAGE plpgsql
 SECURITY DEFINER
 SET search_path TO 'public'
AS $function$
DECLARE
  deleted_count integer;
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
    );
  GET DIAGNOSTICS deleted_count = ROW_COUNT;
  RETURN deleted_count;
END;
$function$;

-- =============================================================================
-- triggers (public tables)
-- =============================================================================

-- feeds
CREATE TRIGGER feeds_updated_at BEFORE UPDATE ON public.feeds FOR EACH ROW EXECUTE FUNCTION moddatetime('updated_at');

-- user_profiles
CREATE TRIGGER prevent_billing_modification BEFORE UPDATE ON public.user_profiles FOR EACH ROW EXECUTE FUNCTION prevent_billing_column_modification();
CREATE TRIGGER user_profiles_updated_at BEFORE UPDATE ON public.user_profiles FOR EACH ROW EXECUTE FUNCTION moddatetime('updated_at');

-- folders
CREATE TRIGGER check_folder_limit_trigger BEFORE INSERT ON public.folders FOR EACH ROW EXECUTE FUNCTION check_folder_limit();
CREATE TRIGGER decrement_folder_count_trigger AFTER DELETE ON public.folders FOR EACH ROW EXECUTE FUNCTION decrement_folder_count();
CREATE TRIGGER folders_updated_at BEFORE UPDATE ON public.folders FOR EACH ROW EXECUTE FUNCTION moddatetime('updated_at');

-- subscriptions
CREATE TRIGGER check_subscription_limit_trigger BEFORE INSERT ON public.subscriptions FOR EACH ROW EXECUTE FUNCTION check_subscription_limit();
CREATE TRIGGER decrement_subscription_count_trigger AFTER DELETE ON public.subscriptions FOR EACH ROW EXECUTE FUNCTION decrement_subscription_count();
CREATE TRIGGER subscriptions_updated_at BEFORE UPDATE ON public.subscriptions FOR EACH ROW EXECUTE FUNCTION moddatetime('updated_at');
CREATE TRIGGER trigger_enqueue_feed_refresh_after_subscribe AFTER INSERT ON public.subscriptions FOR EACH ROW EXECUTE FUNCTION enqueue_feed_refresh_on_subscribe();

-- muted_keywords
CREATE TRIGGER check_muted_keyword_limit_trigger BEFORE INSERT ON public.muted_keywords FOR EACH ROW EXECUTE FUNCTION check_muted_keyword_limit();
CREATE TRIGGER decrement_muted_keyword_count_trigger AFTER DELETE ON public.muted_keywords FOR EACH ROW EXECUTE FUNCTION decrement_muted_keyword_count();

-- custom_feeds
CREATE TRIGGER check_custom_feed_limit_trigger BEFORE INSERT ON public.custom_feeds FOR EACH ROW EXECUTE FUNCTION check_custom_feed_limit();
CREATE TRIGGER decrement_custom_feed_count_trigger AFTER DELETE ON public.custom_feeds FOR EACH ROW EXECUTE FUNCTION decrement_custom_feed_count();
CREATE TRIGGER custom_feeds_updated_at BEFORE UPDATE ON public.custom_feeds FOR EACH ROW EXECUTE FUNCTION moddatetime('updated_at');

-- user_highlights
CREATE TRIGGER check_highlight_limit_trigger BEFORE INSERT ON public.user_highlights FOR EACH ROW EXECUTE FUNCTION check_highlight_limit();
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)
-- =============================================================================

CREATE TRIGGER on_auth_user_created AFTER INSERT ON auth.users FOR EACH ROW EXECUTE FUNCTION handle_new_user();

-- =============================================================================
-- pg_cron scheduled jobs
-- =============================================================================

-- daily at 3:00 AM UTC: purge old private entries for free-tier users (>14 days)
SELECT cron.schedule('purge-old-private-entries', '0 3 * * *', $$
    delete from public.entries e
    where e.owner_id is not null
      and exists (
        select 1 from public.user_profiles p
        where p.id = e.owner_id
        and p.tier = 'free'
      )
      and e.published_at < now() - interval '14 days';
  $$);

-- daily at 3:00 AM UTC: cleanup stale entries (>90 days, not saved)
SELECT cron.schedule('cleanup-stale-entries', '0 3 * * *', $$SELECT cleanup_stale_entries()$$);

-- daily at 4:00 AM UTC: purge orphaned entries (no subscribers)
SELECT cron.schedule('purge-orphaned-entries', '0 4 * * *', $$
    delete from public.entries e
    where e.owner_id is null
      and not exists (
        select 1 from public.subscriptions s where s.feed_id = e.feed_id
      );
  $$);

-- daily at 4:30 AM UTC: purge orphaned public feeds (0 subscribers, stale >7 days)
SELECT cron.schedule('purge-orphaned-feeds', '30 4 * * *', $$
    delete from public.feeds
    where visibility = 'public'
      and subscriber_count = 0
      and updated_at < now() - interval '7 days';
  $$);