exts.cjs

console.error('register extension', process.version)
const { writeFileSync, readFileSync } = require('fs')
const transpile = exports.transpile = file => {
  const src = readFileSync(file, 'utf8')
  const tx = `console.log(${JSON.stringify(src)})`
  writeFileSync(file + '.cjs', tx)
}
require.extensions['.foo'] = (module, file) => {
  transpile(file)
  module.filename = file + '.cjs'
  return require.extensions['.js'](module, file + '.cjs')
}

foo.foo

Hello from foo!

loader.mjs

import { createRequire } from 'node:module'
import {fileURLToPath} from 'node:url'
const require = createRequire(import.meta.url)
const { transpile } = require('./exts.cjs')
export const load = (url, context, defaultLoad) =>{
  console.error('load', url, context, defaultLoad)
  if (url.endsWith('.foo')) {
    transpile(fileURLToPath(url))
    url += '.cjs'
  }
  return defaultLoad(url, context)
}

With this approach, node --loader=./loader.mjs foo.foo and node -r ./exts.cjs foo.foo both work