Ok, woah, this is really interesting.
(Couple quick notes: this wouldn’t be something to replace Prisma 'cause there’s much more to Prisma than querying postgres. Also, the Subscriptions hold-up is with Netlify Functions not supporting the AWS API Gateway for websockets, pubsub, etc. See my comment in this thread.)
What if you ran Supabase alongside the existing Redwood architecture for Subscriptions support? 
I’m thinking off the top of my head, but take a look at how Prisma (and specifically Prisma Client) is being implemented in the API across services, SDL, and the function graphql.js. db is the PrismaClient() from lib/db.js. It’s possible to add other DB connections, you’ll just need to handle the API setup accordingly.
And if the API is deployed to Netlify Functions, will Supabase Subscriptions be compatible? Or would that require a different deploy target?