supabase_voice_limits.sql

-- supabase_voice_limits.sql
-- Trigger to enforce per-minute rate limit and per-row size limit on a voice table.
--
-- Usage:
-- 1) If your voice table is named something other than public.voice, replace all occurrences of public.voice below.
-- 2) This script creates a BEFORE INSERT trigger that:
--    - Acquires a transaction-level advisory lock to avoid race conditions when counting recent rows.
--    - Counts rows inserted in the last 1 minute; if >= 60, raises an exception.
--    - Computes the serialized size of the NEW row (approximate) and raises if > 3MB.
--
-- Note: If your voice column stores base64 text (e.g. voice_base64), see the "Precise size check" section below and uncomment / adapt it for exact binary size checks.

-- OPTIONAL: ensure the table has a created_at timestamp column
ALTER TABLE IF EXISTS public.voice
  ADD COLUMN IF NOT EXISTS created_at timestamptz DEFAULT now();

-- Create the trigger function
CREATE OR REPLACE FUNCTION public.enforce_voice_limits()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
  cnt integer;
  payload_text text;
  payload_size integer;
  max_per_minute integer := 60;                -- 每分钟限制（全局）
  max_size_bytes integer := 3 * 1024 * 1024;   -- 3 MB 限制
BEGIN
  -- Acquire a transaction-level advisory lock to serialize the counting/insertion check
  PERFORM pg_advisory_xact_lock(987654321);

  -- Count rows inserted in the last 1 minute
  SELECT COUNT(*) INTO cnt
    FROM public.voice
   WHERE created_at > (now() - interval '1 minute');

  IF cnt >= max_per_minute THEN
    RAISE EXCEPTION 'rate_limit_exceeded: max % messages per minute reached', max_per_minute;
  END IF;

  -- Approximate payload size by serializing the entire NEW row to JSON
  payload_text := row_to_json(NEW)::text;
  payload_size := octet_length(payload_text);

  IF payload_size IS NULL THEN
    payload_size := 0;
  END IF;

  IF payload_size > max_size_bytes THEN
    RAISE EXCEPTION 'payload_too_large: % bytes (max % bytes)', payload_size, max_size_bytes;
  END IF;

  RETURN NEW;
END;
$$;

-- Attach the trigger to the table
DROP TRIGGER IF EXISTS trg_enforce_voice_limits ON public.voice;
CREATE TRIGGER trg_enforce_voice_limits
BEFORE INSERT ON public.voice
FOR EACH ROW
EXECUTE FUNCTION public.enforce_voice_limits();

-- ===== Precise size check (optional) =====
-- If your voice binary is stored as base64 text in a column named voice_base64,
-- you can replace the row_to_json size check with a precise check of decoded bytes.
-- Uncomment and adapt the block below as needed, then remove or comment out the row_to_json portion above.
--
--    -- Example: compute exact binary size contribution from base64 plus textual fields
--    DECLARE
--      base64_size integer := 0;
--      other_text_size integer := 0;
--    BEGIN
--      IF NEW.voice_base64 IS NOT NULL THEN
--        -- decode base64 to bytea then measure length
--        base64_size := octet_length(decode(NEW.voice_base64, 'base64'));
--      END IF;
--      -- add sizes of other textual fields you care about; example: sender_name
--      other_text_size := COALESCE(octet_length(NEW.sender_name::text), 0)
--                      + COALESCE(octet_length(NEW.sender_id::text), 0)
--                      + COALESCE(octet_length(NEW.voice_id::text), 0);
--      payload_size := base64_size + other_text_size;
--      IF payload_size > max_size_bytes THEN
--        RAISE EXCEPTION 'payload_too_large (precise): % bytes (max % bytes)', payload_size, max_size_bytes;
--      END IF;
--    END;
--
-- Note on advisory lock key:
-- The constant 987654321 is arbitrary; you may replace it with a chosen integer or a value derived
-- from the table OID if you want to ensure uniqueness per-table.

-- End of file