acmcc/supabase/migrations/20260504233251_36965aa8-8428-4c76-aba8-9679d774ba9b.sql

-- Create vault secret for ACH encryption (idempotent)
DO $$
DECLARE v_exists boolean;
BEGIN
  SELECT EXISTS(SELECT 1 FROM vault.secrets WHERE name = 'vendor_ach_encryption_key') INTO v_exists;
  IF NOT v_exists THEN
    PERFORM vault.create_secret(encode(extensions.gen_random_bytes(32), 'hex'), 'vendor_ach_encryption_key', 'Symmetric key for vendor ACH field encryption');
  END IF;
END $$;

-- Helper to fetch the key
CREATE OR REPLACE FUNCTION public._get_vendor_ach_key()
RETURNS text
LANGUAGE sql STABLE SECURITY DEFINER SET search_path = public
AS $$
  SELECT decrypted_secret FROM vault.decrypted_secrets WHERE name = 'vendor_ach_encryption_key' LIMIT 1;
$$;
REVOKE ALL ON FUNCTION public._get_vendor_ach_key() FROM public, anon, authenticated;

-- Add encrypted (bytea) columns
ALTER TABLE public.vendors
  ADD COLUMN IF NOT EXISTS ach_bank_name_enc bytea,
  ADD COLUMN IF NOT EXISTS ach_account_holder_enc bytea,
  ADD COLUMN IF NOT EXISTS ach_routing_number_enc bytea,
  ADD COLUMN IF NOT EXISTS ach_account_number_enc bytea,
  ADD COLUMN IF NOT EXISTS ach_account_last4 text;

-- Migrate any existing plaintext (likely none yet)
UPDATE public.vendors
SET ach_bank_name_enc       = CASE WHEN ach_bank_name       IS NOT NULL THEN extensions.pgp_sym_encrypt(ach_bank_name,       public._get_vendor_ach_key()) END,
    ach_account_holder_enc  = CASE WHEN ach_account_holder  IS NOT NULL THEN extensions.pgp_sym_encrypt(ach_account_holder,  public._get_vendor_ach_key()) END,
    ach_routing_number_enc  = CASE WHEN ach_routing_number  IS NOT NULL THEN extensions.pgp_sym_encrypt(ach_routing_number,  public._get_vendor_ach_key()) END,
    ach_account_number_enc  = CASE WHEN ach_account_number  IS NOT NULL THEN extensions.pgp_sym_encrypt(ach_account_number,  public._get_vendor_ach_key()) END,
    ach_account_last4       = CASE WHEN ach_account_number  IS NOT NULL THEN RIGHT(ach_account_number, 4) END
WHERE ach_bank_name IS NOT NULL OR ach_account_holder IS NOT NULL
   OR ach_routing_number IS NOT NULL OR ach_account_number IS NOT NULL;

-- Drop plaintext columns
ALTER TABLE public.vendors
  DROP COLUMN IF EXISTS ach_bank_name,
  DROP COLUMN IF EXISTS ach_account_holder,
  DROP COLUMN IF EXISTS ach_routing_number,
  DROP COLUMN IF EXISTS ach_account_number;

-- Updated submit RPC: encrypts ACH inputs before saving
CREATE OR REPLACE FUNCTION public.submit_vendor_profile(
  p_token text,
  p_remittance_address text,
  p_tax_id text,
  p_is_1099_eligible boolean,
  p_w9_document_url text DEFAULT NULL,
  p_insurance_carrier text DEFAULT NULL,
  p_insurance_policy_number text DEFAULT NULL,
  p_insurance_expiration_date date DEFAULT NULL,
  p_insurance_document_url text DEFAULT NULL,
  p_ach_bank_name text DEFAULT NULL,
  p_ach_account_holder text DEFAULT NULL,
  p_ach_routing_number text DEFAULT NULL,
  p_ach_account_number text DEFAULT NULL,
  p_ach_account_type text DEFAULT NULL
)
RETURNS boolean
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public
AS $$
DECLARE
  v_request RECORD;
  v_key text;
BEGIN
  SELECT * INTO v_request FROM public.vendor_profile_requests
  WHERE token = p_token AND expires_at > now() AND submitted_at IS NULL
  LIMIT 1;

  IF v_request IS NULL THEN
    RETURN FALSE;
  END IF;

  v_key := public._get_vendor_ach_key();

  UPDATE public.vendors
  SET remittance_address = COALESCE(NULLIF(p_remittance_address, ''), remittance_address),
      tax_id = COALESCE(NULLIF(p_tax_id, ''), tax_id),
      is_1099_eligible = COALESCE(p_is_1099_eligible, is_1099_eligible),
      w9_document_url = COALESCE(p_w9_document_url, w9_document_url),
      insurance_carrier = COALESCE(NULLIF(p_insurance_carrier, ''), insurance_carrier),
      insurance_policy_number = COALESCE(NULLIF(p_insurance_policy_number, ''), insurance_policy_number),
      insurance_expiration_date = COALESCE(p_insurance_expiration_date, insurance_expiration_date),
      insurance_document_url = COALESCE(p_insurance_document_url, insurance_document_url),
      ach_bank_name_enc      = CASE WHEN NULLIF(p_ach_bank_name, '')      IS NOT NULL THEN extensions.pgp_sym_encrypt(p_ach_bank_name,      v_key) ELSE ach_bank_name_enc END,
      ach_account_holder_enc = CASE WHEN NULLIF(p_ach_account_holder, '') IS NOT NULL THEN extensions.pgp_sym_encrypt(p_ach_account_holder, v_key) ELSE ach_account_holder_enc END,
      ach_routing_number_enc = CASE WHEN NULLIF(p_ach_routing_number, '') IS NOT NULL THEN extensions.pgp_sym_encrypt(p_ach_routing_number, v_key) ELSE ach_routing_number_enc END,
      ach_account_number_enc = CASE WHEN NULLIF(p_ach_account_number, '') IS NOT NULL THEN extensions.pgp_sym_encrypt(p_ach_account_number, v_key) ELSE ach_account_number_enc END,
      ach_account_last4      = CASE WHEN NULLIF(p_ach_account_number, '') IS NOT NULL THEN RIGHT(p_ach_account_number, 4) ELSE ach_account_last4 END,
      ach_account_type = COALESCE(NULLIF(p_ach_account_type, ''), ach_account_type),
      profile_last_submitted_at = now(),
      updated_at = now()
  WHERE id = v_request.vendor_id;

  UPDATE public.vendor_profile_requests
  SET submitted_at = now()
  WHERE id = v_request.id;

  RETURN TRUE;
END;
$$;

GRANT EXECUTE ON FUNCTION public.submit_vendor_profile(text, text, text, boolean, text, text, text, date, text, text, text, text, text, text) TO anon, authenticated;

-- Admin/manager-only decryption RPC
CREATE OR REPLACE FUNCTION public.get_vendor_ach_details(p_vendor_id uuid)
RETURNS TABLE(
  bank_name text,
  account_holder text,
  routing_number text,
  account_number text,
  account_type text,
  account_last4 text
)
LANGUAGE plpgsql STABLE SECURITY DEFINER SET search_path = public
AS $$
DECLARE v_key text;
BEGIN
  IF NOT (has_role(auth.uid(), 'admin'::app_role) OR has_role(auth.uid(), 'manager'::app_role)) THEN
    RAISE EXCEPTION 'Forbidden';
  END IF;
  v_key := public._get_vendor_ach_key();
  RETURN QUERY
  SELECT
    CASE WHEN v.ach_bank_name_enc      IS NOT NULL THEN extensions.pgp_sym_decrypt(v.ach_bank_name_enc,      v_key) END,
    CASE WHEN v.ach_account_holder_enc IS NOT NULL THEN extensions.pgp_sym_decrypt(v.ach_account_holder_enc, v_key) END,
    CASE WHEN v.ach_routing_number_enc IS NOT NULL THEN extensions.pgp_sym_decrypt(v.ach_routing_number_enc, v_key) END,
    CASE WHEN v.ach_account_number_enc IS NOT NULL THEN extensions.pgp_sym_decrypt(v.ach_account_number_enc, v_key) END,
    v.ach_account_type,
    v.ach_account_last4
  FROM public.vendors v
  WHERE v.id = p_vendor_id;
END;
$$;
REVOKE ALL ON FUNCTION public.get_vendor_ach_details(uuid) FROM public, anon;
GRANT EXECUTE ON FUNCTION public.get_vendor_ach_details(uuid) TO authenticated;