38980-vm/app-9w9pd00g5j41/supabase/migrations/00043_add_rate_limiting.sql

-- Rate Limiting Migration
-- Adds rate limiting tables and functions for API protection

-- 1. Create rate_limit_rules table
CREATE TABLE IF NOT EXISTS rate_limit_rules (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  endpoint TEXT NOT NULL UNIQUE, -- e.g., 'create_trip', 'ai_suggest', 'export_data'
  max_requests INTEGER NOT NULL DEFAULT 10,
  window_seconds INTEGER NOT NULL DEFAULT 60, -- Time window in seconds
  enabled BOOLEAN NOT NULL DEFAULT true,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- 2. Create rate_limit_logs table
CREATE TABLE IF NOT EXISTS rate_limit_logs (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
  ip_address INET,
  endpoint TEXT NOT NULL,
  request_count INTEGER NOT NULL DEFAULT 1,
  window_start TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  blocked BOOLEAN NOT NULL DEFAULT false,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- 3. Add indexes
CREATE INDEX IF NOT EXISTS idx_rate_limit_logs_user_endpoint ON rate_limit_logs(user_id, endpoint, window_start);
CREATE INDEX IF NOT EXISTS idx_rate_limit_logs_ip_endpoint ON rate_limit_logs(ip_address, endpoint, window_start);
CREATE INDEX IF NOT EXISTS idx_rate_limit_logs_created_at ON rate_limit_logs(created_at DESC);

-- 4. Insert default rate limit rules
INSERT INTO rate_limit_rules (endpoint, max_requests, window_seconds) VALUES
  ('create_trip', 10, 3600),           -- 10 trips per hour
  ('ai_suggest', 20, 3600),            -- 20 AI suggestions per hour
  ('export_data', 3, 86400),           -- 3 data exports per day
  ('delete_account', 1, 86400),        -- 1 account deletion per day
  ('send_email', 10, 3600),            -- 10 emails per hour
  ('api_general', 100, 60),            -- 100 general API calls per minute
  ('login_attempt', 5, 300),           -- 5 login attempts per 5 minutes
  ('signup', 3, 3600)                  -- 3 signups per hour per IP
ON CONFLICT (endpoint) DO NOTHING;

-- 5. Create function to check rate limit
CREATE OR REPLACE FUNCTION check_rate_limit(
  p_user_id UUID,
  p_ip_address INET,
  p_endpoint TEXT
) RETURNS JSONB AS $$
DECLARE
  v_rule RECORD;
  v_log RECORD;
  v_window_start TIMESTAMPTZ;
  v_allowed BOOLEAN := true;
  v_remaining INTEGER;
BEGIN
  -- Get rate limit rule
  SELECT * INTO v_rule
  FROM rate_limit_rules
  WHERE endpoint = p_endpoint AND enabled = true;
  
  -- If no rule exists, allow the request
  IF NOT FOUND THEN
    RETURN jsonb_build_object(
      'allowed', true,
      'remaining', 999,
      'reset_at', NULL
    );
  END IF;
  
  -- Calculate window start time
  v_window_start := NOW() - (v_rule.window_seconds || ' seconds')::INTERVAL;
  
  -- Check user-based rate limit (if user_id provided)
  IF p_user_id IS NOT NULL THEN
    SELECT 
      SUM(request_count) as total_requests
    INTO v_log
    FROM rate_limit_logs
    WHERE user_id = p_user_id
      AND endpoint = p_endpoint
      AND window_start >= v_window_start;
    
    IF v_log.total_requests >= v_rule.max_requests THEN
      v_allowed := false;
    END IF;
    
    v_remaining := GREATEST(0, v_rule.max_requests - COALESCE(v_log.total_requests, 0));
  ELSE
    -- Check IP-based rate limit
    SELECT 
      SUM(request_count) as total_requests
    INTO v_log
    FROM rate_limit_logs
    WHERE ip_address = p_ip_address
      AND endpoint = p_endpoint
      AND window_start >= v_window_start;
    
    IF v_log.total_requests >= v_rule.max_requests THEN
      v_allowed := false;
    END IF;
    
    v_remaining := GREATEST(0, v_rule.max_requests - COALESCE(v_log.total_requests, 0));
  END IF;
  
  -- Log the request
  INSERT INTO rate_limit_logs (
    user_id,
    ip_address,
    endpoint,
    request_count,
    window_start,
    blocked
  ) VALUES (
    p_user_id,
    p_ip_address,
    p_endpoint,
    1,
    NOW(),
    NOT v_allowed
  );
  
  RETURN jsonb_build_object(
    'allowed', v_allowed,
    'remaining', v_remaining,
    'reset_at', NOW() + (v_rule.window_seconds || ' seconds')::INTERVAL,
    'limit', v_rule.max_requests,
    'window_seconds', v_rule.window_seconds
  );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- 6. Create function to clean old rate limit logs (run periodically)
CREATE OR REPLACE FUNCTION cleanup_rate_limit_logs()
RETURNS INTEGER AS $$
DECLARE
  v_deleted INTEGER;
BEGIN
  -- Delete logs older than 7 days
  DELETE FROM rate_limit_logs
  WHERE created_at < NOW() - INTERVAL '7 days';
  
  GET DIAGNOSTICS v_deleted = ROW_COUNT;
  
  RETURN v_deleted;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- 7. Create RLS policies
ALTER TABLE rate_limit_rules ENABLE ROW LEVEL SECURITY;
ALTER TABLE rate_limit_logs ENABLE ROW LEVEL SECURITY;

-- Admins can view and manage rate limit rules
CREATE POLICY "Admins can view rate limit rules"
  ON rate_limit_rules FOR SELECT
  USING (
    EXISTS (
      SELECT 1 FROM profiles
      WHERE profiles.id = auth.uid()
      AND profiles.role = 'admin'
    )
  );

CREATE POLICY "Admins can manage rate limit rules"
  ON rate_limit_rules FOR ALL
  USING (
    EXISTS (
      SELECT 1 FROM profiles
      WHERE profiles.id = auth.uid()
      AND profiles.role = 'admin'
    )
  );

-- Users can view their own rate limit logs
CREATE POLICY "Users can view their own rate limit logs"
  ON rate_limit_logs FOR SELECT
  USING (auth.uid() = user_id);

-- Admins can view all rate limit logs
CREATE POLICY "Admins can view all rate limit logs"
  ON rate_limit_logs FOR SELECT
  USING (
    EXISTS (
      SELECT 1 FROM profiles
      WHERE profiles.id = auth.uid()
      AND profiles.role = 'admin'
    )
  );

-- Service role can insert rate limit logs
CREATE POLICY "Service role can insert rate limit logs"
  ON rate_limit_logs FOR INSERT
  WITH CHECK (true);