misc/all_rpcs.md

schema_name	function_name	identity_args	result_type	security_definer	function_definition
public	get_dashboard_kpis		json	true	CREATE OR REPLACE FUNCTION public.get_dashboard_kpis()
RETURNS json
LANGUAGE sql
SECURITY DEFINER
AS $function$
SELECT json_build_object(

'pricing', json_build_object(
  'products_tracked',   (SELECT COUNT(*) FROM products),
  'price_changes_7d',   (
    SELECT COUNT(*) FROM price_history
    WHERE created_at >= NOW() - INTERVAL '7 days'
    AND price != (
      SELECT price FROM price_history ph2
      WHERE ph2.product_id = price_history.product_id
      AND ph2.created_at < price_history.created_at
      ORDER BY ph2.created_at DESC LIMIT 1
    )
  ),
  'spikes_7d',          (
    SELECT COUNT(*) FROM v_price_volatility
    WHERE ABS(pct_above_trailing_avg) >= 25
    AND created_at >= NOW() - INTERVAL '7 days'
  ),
  'last_scrape_status', (
    SELECT last_status FROM v_scrape_health
    WHERE job_type = 'PRICE_MONITOR'
    ORDER BY last_started_at DESC LIMIT 1
  ),
  'tracking_since',     (SELECT MIN(created_at) FROM products)
),
'leads', json_build_object(
  'targets_crawled',      (SELECT COUNT(*) FROM lead_targets),
  'new_leads_7d',         (SELECT COUNT(*) FROM leads WHERE created_at >= NOW() - INTERVAL '7 days'),
  'total_leads_all_time', (SELECT COUNT(*) FROM leads),
  'last_scrape_status',   (
    SELECT last_status FROM v_scrape_health
    WHERE job_type = 'LEAD_GEN'
    ORDER BY last_started_at DESC LIMIT 1
  )
),
'real_estate', json_build_object(
  'properties_tracked', (SELECT COUNT(DISTINCT property_id) FROM v_rate_volatility),
  'rate_changes_7d',    (
    SELECT COUNT(*) FROM rate_history
    WHERE created_at >= NOW() - INTERVAL '7 days'
    AND nightly_rate IS NOT NULL
    AND nightly_rate != (
      SELECT nightly_rate FROM rate_history rh2
      WHERE rh2.property_id = rate_history.property_id
      AND rh2.created_at < rate_history.created_at
      AND rh2.nightly_rate IS NOT NULL
      ORDER BY rh2.created_at DESC LIMIT 1
    )
  ),
  'spikes_7d',          (
    SELECT COUNT(*) FROM v_rate_volatility
    WHERE ABS(pct_above_trailing_avg) >= 25
    AND recorded_at >= NOW() - INTERVAL '7 days'
  ),
  'tracking_since',     (SELECT MIN(created_at) FROM properties),
  'last_scrape_status', (
    SELECT COALESCE(json_object_agg(platform, last_status), '{}'::json)
    FROM v_scrape_health
    WHERE job_type = 'REAL_ESTATE_MONITOR'
  )
)

); $function$ | | public | get_distance_km | property_a_id uuid, property_b_id uuid | TABLE(distance_km numeric) | true | CREATE OR REPLACE FUNCTION public.get_distance_km(property_a_id uuid, property_b_id uuid) RETURNS TABLE(distance_km numeric) LANGUAGE sql STABLE SECURITY DEFINER AS $function$ WITH a AS ( SELECT latitude, longitude FROM public.properties WHERE id = property_a_id ), b AS ( SELECT latitude, longitude FROM public.properties WHERE id = property_b_id ) SELECT ( 6371 * 2 * ASIN( SQRT( POWER(SIN(RADIANS(b.latitude - a.latitude) / 2), 2) + COS(RADIANS(a.latitude)) * COS(RADIANS(b.latitude)) * POWER(SIN(RADIANS(b.longitude - a.longitude) / 2), 2) ) ) )::numeric(12,4) AS distance_km FROM a, b WHERE a.latitude IS NOT NULL AND a.longitude IS NOT NULL AND b.latitude IS NOT NULL AND b.longitude IS NOT NULL; $function$ | | public | get_market_averages | market_param text | TABLE(market character varying, active_properties bigint, avg_nightly_rate numeric, min_nightly_rate numeric, max_nightly_rate numeric) | true | CREATE OR REPLACE FUNCTION public.get_market_averages(market_param text DEFAULT NULL::text) RETURNS TABLE(market character varying, active_properties bigint, avg_nightly_rate numeric, min_nightly_rate numeric, max_nightly_rate numeric) LANGUAGE sql STABLE SECURITY DEFINER AS $function$ SELECT market, COUNT(DISTINCT property_id) AS active_properties, ROUND(AVG(nightly_rate), 2) AS avg_nightly_rate, MIN(nightly_rate) AS min_nightly_rate, MAX(nightly_rate) AS max_nightly_rate FROM public.v_rate_volatility WHERE (market_param IS NULL OR LOWER(market) = LOWER(market_param)) AND is_active = true AND nightly_rate IS NOT NULL GROUP BY market; $function$ | | public | get_properties_by_filter | p_market text, p_platform text, p_available boolean, p_bedrooms integer | TABLE(property_id uuid, property_name character varying, market character varying, platform character varying, bedrooms integer, avg_rating numeric, review_count integer, nightly_rate numeric, pct_above_trailing_avg numeric, is_available boolean, recorded_at timestamp with time zone) | true | CREATE OR REPLACE FUNCTION public.get_properties_by_filter(p_market text DEFAULT NULL::text, p_platform text DEFAULT NULL::text, p_available boolean DEFAULT NULL::boolean, p_bedrooms integer DEFAULT NULL::integer) RETURNS TABLE(property_id uuid, property_name character varying, market character varying, platform character varying, bedrooms integer, avg_rating numeric, review_count integer, nightly_rate numeric, pct_above_trailing_avg numeric, is_available boolean, recorded_at timestamp with time zone) LANGUAGE sql STABLE SECURITY DEFINER AS $function$ SELECT DISTINCT ON (property_id) property_id, property_name, market, platform, bedrooms, avg_rating, review_count, nightly_rate, pct_above_trailing_avg, is_available, recorded_at FROM public.v_rate_volatility WHERE (p_market IS NULL OR LOWER(market) = LOWER(p_market)) AND (p_platform IS NULL OR LOWER(platform) = LOWER(p_platform)) AND (p_available IS NULL OR is_available = p_available) AND (p_bedrooms IS NULL OR bedrooms = p_bedrooms) ORDER BY property_id, recorded_at DESC; $function$ | | public | get_property_rate_changes | property_search text, days_param integer, compare_window_days integer | TABLE(property_id uuid, property_name character varying, market character varying, platform character varying, stay_date timestamp with time zone, nightly_rate numeric, trailing_avg_rate numeric, pct_above_trailing_avg numeric, prev_nightly_rate numeric, pct_change_vs_prev numeric) | true | CREATE OR REPLACE FUNCTION public.get_property_rate_changes(property_search text, days_param integer DEFAULT 14, compare_window_days integer DEFAULT 1) RETURNS TABLE(property_id uuid, property_name character varying, market character varying, platform character varying, stay_date timestamp with time zone, nightly_rate numeric, trailing_avg_rate numeric, pct_above_trailing_avg numeric, prev_nightly_rate numeric, pct_change_vs_prev numeric) LANGUAGE sql STABLE SECURITY DEFINER AS $function$ WITH base AS ( SELECT rh.property_id, p.name AS property_name, p.market, p.platform, rh.stay_date, rh.nightly_rate, -- compute trailing avg as avg of previous 7 days (excluding current day) AVG(rh.nightly_rate) OVER ( PARTITION BY rh.property_id ORDER BY rh.stay_date ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING ) AS trailing_avg_rate, LAG(rh.nightly_rate, compare_window_days) OVER ( PARTITION BY rh.property_id ORDER BY rh.stay_date ) AS prev_nightly_rate FROM public.v_rate_volatility rh JOIN public.properties p ON p.id = rh.property_id WHERE (rh.property_id::text = property_search OR LOWER(rh.property_name) = LOWER(property_search) OR LOWER(rh.property_name) LIKE '%' || LOWER(property_search) || '%') AND rh.recorded_at >= (NOW() - (days_param || ' days')::interval) ) SELECT property_id, property_name, market, platform, stay_date, nightly_rate, trailing_avg_rate, CASE WHEN trailing_avg_rate IS NULL OR trailing_avg_rate = 0 OR nightly_rate IS NULL THEN NULL ELSE ((nightly_rate - trailing_avg_rate) / trailing_avg_rate) * 100 END AS pct_above_trailing_avg, prev_nightly_rate, CASE WHEN prev_nightly_rate IS NULL OR prev_nightly_rate = 0 OR nightly_rate IS NULL THEN NULL ELSE ((nightly_rate - prev_nightly_rate) / prev_nightly_rate) * 100 END AS pct_change_vs_prev FROM base WHERE stay_date IS NOT NULL ORDER BY stay_date ASC; $function$ | | public | get_property_rate_history | property_search text, days_param integer | TABLE(property_id uuid, property_name character varying, market character varying, stay_date timestamp with time zone, nightly_rate numeric, trailing_avg_rate numeric, pct_above_trailing_avg numeric, is_available boolean) | true | CREATE OR REPLACE FUNCTION public.get_property_rate_history(property_search text, days_param integer DEFAULT 30) RETURNS TABLE(property_id uuid, property_name character varying, market character varying, stay_date timestamp with time zone, nightly_rate numeric, trailing_avg_rate numeric, pct_above_trailing_avg numeric, is_available boolean) LANGUAGE sql STABLE SECURITY DEFINER AS $function$ SELECT property_id, property_name, market, stay_date, nightly_rate, trailing_avg_rate, pct_above_trailing_avg, is_available FROM public.v_rate_volatility WHERE (property_id::TEXT = property_search OR LOWER(property_name) LIKE '%' || LOWER(property_search) || '%') AND stay_date >= (NOW() - (days_param || ' days')::INTERVAL) ORDER BY stay_date ASC; $function$ | | public | get_spike_alerts | threshold_param numeric, days_param integer | TABLE(property_id uuid, property_name character varying, market character varying, platform character varying, nightly_rate numeric, trailing_avg_rate numeric, pct_above_trailing_avg numeric, recorded_at timestamp with time zone) | true | CREATE OR REPLACE FUNCTION public.get_spike_alerts(threshold_param numeric DEFAULT 25.0, days_param integer DEFAULT 7) RETURNS TABLE(property_id uuid, property_name character varying, market character varying, platform character varying, nightly_rate numeric, trailing_avg_rate numeric, pct_above_trailing_avg numeric, recorded_at timestamp with time zone) LANGUAGE sql STABLE SECURITY DEFINER AS $function$ SELECT property_id, property_name, market, platform, nightly_rate, trailing_avg_rate, pct_above_trailing_avg, recorded_at FROM public.v_rate_volatility WHERE ABS(pct_above_trailing_avg) >= threshold_param AND recorded_at >= (NOW() - (days_param || ' days')::INTERVAL) ORDER BY recorded_at DESC; $function$ | | public | get_tracked_markets | p_platform text | TABLE(market character varying, active_properties bigint) | true | CREATE OR REPLACE FUNCTION public.get_tracked_markets(p_platform text DEFAULT NULL::text) RETURNS TABLE(market character varying, active_properties bigint) LANGUAGE sql STABLE SECURITY DEFINER AS $function$ SELECT p.market, COUNT(DISTINCT p.id) AS active_properties FROM public.properties p WHERE p.is_active = true AND (p_platform IS NULL OR LOWER(p.platform) = LOWER(p_platform)) AND p.market IS NOT NULL GROUP BY p.market ORDER BY active_properties DESC, market; $function$ | | public | match_re_methodology | query_embedding vector, match_threshold double precision, match_count integer | TABLE(id uuid, section_title character varying, chunk_content text, similarity double precision) | true | CREATE OR REPLACE FUNCTION public.match_re_methodology(query_embedding vector, match_threshold double precision DEFAULT 0.5, match_count integer DEFAULT 3) RETURNS TABLE(id uuid, section_title character varying, chunk_content text, similarity double precision) LANGUAGE sql STABLE SECURITY DEFINER AS $function$ SELECT kb.id, kb.section_title, kb.chunk_content, 1 - (kb.embedding <=> query_embedding) AS similarity FROM public.re_knowledge_base kb WHERE 1 - (kb.embedding <=> query_embedding) > match_threshold ORDER BY kb.embedding <=> query_embedding LIMIT match_count; $function$ | | public | rls_auto_enable | | event_trigger | true | CREATE OR REPLACE FUNCTION public.rls_auto_enable() RETURNS event_trigger LANGUAGE plpgsql SECURITY DEFINER SET search_path TO 'pg_catalog' AS $function$ DECLARE cmd record; BEGIN FOR cmd IN SELECT * FROM pg_event_trigger_ddl_commands() WHERE command_tag IN ('CREATE TABLE', 'CREATE TABLE AS', 'SELECT INTO') AND object_type IN ('table','partitioned table') LOOP IF cmd.schema_name IS NOT NULL AND cmd.schema_name IN ('public') AND cmd.schema_name NOT IN ('pg_catalog','information_schema') AND cmd.schema_name NOT LIKE 'pg_toast%' AND cmd.schema_name NOT LIKE 'pg_temp%' THEN BEGIN EXECUTE format('alter table if exists %s enable row level security', cmd.object_identity); RAISE LOG 'rls_auto_enable: enabled RLS on %', cmd.object_identity; EXCEPTION WHEN OTHERS THEN RAISE LOG 'rls_auto_enable: failed to enable RLS on %', cmd.object_identity; END; ELSE RAISE LOG 'rls_auto_enable: skip % (either system schema or not in enforced list: %.)', cmd.object_identity, cmd.schema_name; END IF; END LOOP; END; $function$ | | public | search_properties | p_search text, p_market text, p_platform text, p_bedrooms integer, p_available boolean, p_limit integer | TABLE(property_id uuid, property_name character varying, market character varying, platform character varying, bedrooms integer, avg_rating numeric, review_count integer, nightly_rate numeric, pct_above_trailing_avg numeric, is_available boolean, recorded_at timestamp with time zone) | true | CREATE OR REPLACE FUNCTION public.search_properties(p_search text DEFAULT NULL::text, p_market text DEFAULT NULL::text, p_platform text DEFAULT NULL::text, p_bedrooms integer DEFAULT NULL::integer, p_available boolean DEFAULT NULL::boolean, p_limit integer DEFAULT 50) RETURNS TABLE(property_id uuid, property_name character varying, market character varying, platform character varying, bedrooms integer, avg_rating numeric, review_count integer, nightly_rate numeric, pct_above_trailing_avg numeric, is_available boolean, recorded_at timestamp with time zone) LANGUAGE sql STABLE SECURITY DEFINER AS $function$ SELECT DISTINCT ON (rvv.property_id) rvv.property_id, rvv.property_name, rvv.market, rvv.platform, rvv.bedrooms, rvv.avg_rating, rvv.review_count, rvv.nightly_rate, rvv.pct_above_trailing_avg, rvv.is_available, rvv.recorded_at FROM public.v_rate_volatility rvv WHERE (p_market IS NULL OR LOWER(rvv.market) = LOWER(p_market)) AND (p_platform IS NULL OR LOWER(rvv.platform) = LOWER(p_platform)) AND (p_bedrooms IS NULL OR rvv.bedrooms = p_bedrooms) AND (p_available IS NULL OR rvv.is_available = p_available) AND ( p_search IS NULL OR rvv.property_name ILIKE '%' || p_search || '%' OR rvv.property_id::text = p_search ) ORDER BY rvv.property_id, rvv.recorded_at DESC LIMIT LEAST(GREATEST(p_limit, 1), 200); $function$ |