Supabase helper for better RPC function typing with jsonb fields

The problem

When you have those tables used for say vector search... you usually have a jsonb field for metadata like this:

create table
  documents (
    id text not null,
    vector extensions.vector null,
    content text not null,
     ---v--- this field
    metadata jsonb null, 
    constraint documents_pkey primary key (id)
  )

Where let's say you always have this in your jsonb metadata

 type Metadata = { 
    sourceUrl: string
    otherField: string
 }

But when you do a supabase call say supabase.rpc('match_vec_doc', args)... even if you create client supabse with Database from the typings provided... that metadata field will be Json and not of type Metadata.. which messes with your rest of your code!

So here is some help functions in addition to the type pulling you can get from

supabase gen types typescript --project-id [PROJECT_ID] > database.types.ts

The solution

Create a database_types.ts file in say you lib/supabase folder, and import from the generated types (you probably have some of this for Tables, Views):

import { type Database } from '@/database.types'

export type Tables<T extends keyof Database['public']['Tables']> =
  Database['public']['Tables'][T]['Row']
export type Views<T extends keyof Database['public']['Views']> =
  Database['public']['Views'][T]['Row']
export type Enums<T extends keyof Database['public']['Enums']> =
  Database['public']['Enums'][T]

export type Insert<T extends keyof Database['public']['Tables']> =
  Database['public']['Tables'][T]['Insert']

export type Functions = Database['public']['Functions']

Then add a MetadataField type mapping

// (... continue)
// Keys are the types for each metadata field here for document
export type MetadataField = {
  documentMetadata: { 
    sourceUrl: string
    otherField: string
  }
  defaultMetadata: unknown // this is the default for all other functions
 }

// Define a type that maps function names to keys in MetadataField
// Here I have 2 functions that both return metadata from the documents tables.. I have the k in keyof Functions to ensure we load all functions to avoid type errors
export type MetadataFieldMapping = {
  [k in keyof Functions]: string
} & {
  match_fts_doc: 'documentMetadata'
  match_vec_doc: 'documentMetadata'
  hybrid_search_doc: 'documentMetadata'
  match_fts_chunk: 'chunkMetadata'
  match_vec_chunk_text: 'chunkMetadata'
}

Then add an RPC type that you will use in your helper replacement function for rpc calls. It has a fallback

//  Define the RPC type using a generic parameter for dynamic metadata field
export type RPC = {
  [FunctionName in keyof MetadataFieldMapping]: {
    Args: Functions[FunctionName]['Args']
    Returns: Array<
      Functions[FunctionName]['Returns'][number] & {
        metadata: MetadataField[MetadataFieldMapping[FunctionName] extends keyof MetadataField
          ? MetadataFieldMapping[FunctionName]
          : 'defaultMetadata'] // Fallback to 'defaultMetadata' if the mapping does not exist
      }
    >
  }
}

The helper function

// Helper function to simplify RPC calls
export async function supabaseRPC<
  MethodName extends keyof MetadataFieldMapping
>(methodName: MethodName, args: RPC[MethodName]['Args']) {
  const supabase = createClient()
  return supabase.rpc<MethodName, RPC[MethodName]>(methodName, args)
}

How to use it

Instead of

supabase.rpc('match_vec_doc', args)

Use

supabaseRPC('match_vec_doc', args)

Example

You can see the metadata field is now properly typed. Note this example the schema is different than above.. but you get it.