How to Sync Supabase and Algolia

Let's break it down into 3 steps:

Create updated_at timestamps on every Supabase table.
Handle row creations and updates.
Handle deletions.

Create the updated_at columns

Following this article, here are the steps:

Enable the moddatetime extension in your Supabase console.
In the Table Editor, create a new column called updated_at on every table you need to sync. Set non-nullable, choose timestamptz type and use now() as default value.
Head over to the SQL Editor and create triggers for every updated_at columns created.

-- This is used to create the computed `updated_at` column on all these tables
-- Take a look at Database/Triggers to see these triggers

create trigger handle_updated_at before update on YOUR_TABLE_NAME for each row
execute procedure moddatetime (updated_at);

-- duplicate above snippet for every table

Handle row changes and creations

We will now create a serverless function that will then be triggered every 5 minutes to fetch all the newly created and updated rows within this timeframe (you can use any kind of service that can be triggered by CRON here).

I'm choosing Cloudflare Workers as platform and not Supabase Edge Functions because I find DX to be pretty bad when developing in a monorepo with the Supabase CLI for EFs, and also Deno is nice but I'm using TypeScript in the monorepo so Deno VSCode extension messes up everything. I could fix all of these issues, but for an MVP I don't want to spend much time here.

The serverless function

I'm gonna use Nitro over Hono for this because of its first-class Cloudflare Cron Trigger integration and because I love the Nuxt ecosystem 😅.

# Init a new Nitro app
pnpm dlx giget@latest nitro nitro-app --install

# Generate typings
cd nitro-app
pnpm dev

Now let's config Nitro a little bit.

export default defineNitroConfig({
  compatibilityDate: "2024-12-19",
  srcDir: "server",

  // https://nitro.build/deploy/providers/cloudflare#cloudflare-module-workers
  preset: "cloudflare_module",

  // https://nitro.build/guide/tasks#scheduled-tasks
  experimental: {
    tasks: true,
  },
  scheduledTasks: {
    // Run `algolia:ingest` task every 5 minutes
    // Make sure to have the same values in wrangler config
    "*/5 * * * *": ["algolia:ingest"],
  },
});

name = "nitro-app"
compatibility_date = "2024-12-19"
main = "./.output/server/index.mjs"
assets = { directory = "./.output/public/", binding = "ASSETS" }

[triggers]
# Make sure to have the same values in nitro config
crons = ["*/5 * * * *"]

[observability.logs]
enabled = true

Optionally, you can setup CI/CD very easily.

We're now finally ready to code.

export default defineTask({
  meta: {
    name: "algolia:ingest",
    description: "Ingest data from Supabase to Algolia",
  },
  async run() {
    const supabase = useSupabase();
    // TODO: maybe use time given by payload, so we can retry on particular time ranges
    const ts = new Date().getTime();
    const date = new Date(ts - (5 * 1000 * 60 + 10000)).toISOString();

    const updatedRecords = await supabase
      .from("YOUR_TABLE")
      .select("*")
      .gte("updated_at", date);

    // Allowing us to get notified and retry on failure
    if (updatedRecords.error) {
      throw new Error(updatedRecords.error.message);
    }

    if (!updatedRecords?.data?.length) {
      return { result: "No records to ingest" };
    }

    const transformedRecords = updatedRecords.data.map((record) => {
      return {
        objectID: record.uid,
        ...record,
      };
    });

    const algolia = useAlgolia();
    await algolia.saveObjects({
      indexName: "YOUR_INDEX",
      objects: transformedRecords,
    });

    return { result: "Success" };
  },
});

If you're ingesting data for the first time replace date with new Date(0) so every item will be ingested, but be aware of Algolia and Supabase limits. In my case I don't have to worry about limits yet since I'm dealing with a relatively small database.

By the way if you have a complex data structure you need to index in Algolia, think about Postgres Views. In my case, I did this and combined the most recent updated_at in my view.

Handle deletions

Here we have multiple solutions:

Make an Algolia API call everytime you delete a user on your backend. Sometimes this is the best solution, but it can also become overwhelming to have such behavior replicated multiple times in your database and also easy to forget about.
Use Database Webhooks to trigger external code that will then use the Algolia API to delete the record.
If you have too many deletions/seconds you might not consider option 2 and will need to keep track of the IDs delete and buffer those in a separate table or queue. Then do a Cron job to delete all elements in the queue.

I will explore solution 2.

Code

This time we need a proper route, not a task in Nitro.

import { Database } from "YOUR_TYPINGS";

type DeletePayload = {
  type: "DELETE";
  table: string;
  schema: string;
  record: null;
  old_record: Database["public"]["Tables"]["YOUR_TABLE"]["Row"];
};

export default eventHandler(async (event) => {
  const privateKey = getHeader(event, "x-api-key");

  if (privateKey !== process.env.SUPABASE_ALGOLIA_WEBHOOK_PRIVATE_KEY) {
    throw createError({ statusCode: 401, statusMessage: "Unauthorized" });
  }

  const body: DeletePayload = await readBody(event);

  if (body.type !== "DELETE" || body.table !== "YOUR_TABLE") {
    throw createError({ statusCode: 400, statusMessage: "Bad Request" });
  }

  const algolia = useAlgolia();
  await algolia.deleteObject({
    indexName: "YOUR_INDEX",
    objectID: body.old_record.uid,
  });

  return "OK";
});

Don't forget that you can generate typings using the Supabase CLI.

Steps

Enable webhook integration in the dashboard.
Deploy your function, copy its URL.
Create a new webhook (docs), pointing to https://yourfunction.url/webhook/algolia and don't forget the x-api-key header.

Conclusion

Congrats, you've now set up your fully-functioning and serverless Supabase to Algolia integration.

I hope this article has been helpful to you, don't hesitate to leave a star on GitHub if that's the case!