Connect Riddle Quiz Maker to Supabase

To connect Riddle Quiz Maker to Supabase, you need to set up a webhook that will send quiz data to your Supabase database. Follow these steps:

1. Create a Supabase Project: If you haven't already, create a new project in Supabase.
2. Set Up Database Tables: Create the necessary tables in your Supabase database to store quiz data. For example, you might have a webhook_riddle table with columns for id, email, name, and data. Important: Make sure to configure Row Level Security (RLS) policies for your table to allow the edge function to insert and update data. You can either disable RLS for this table or create appropriate policies that allow operations from your edge function.
3. Obtain API Keys: Get your Supabase API URL and anon/public API key from the project settings.
4. Configure Webhook in Riddle: In your Riddle Quiz Maker settings, configure a webhook to send quiz data to your Supabase endpoint. You will need to include the API key in the headers for authentication. Learn how to use webhooks here
5. Handle Incoming Data: In your Supabase project, set up an edge function to handle incoming webhook requests from Riddle. This function should parse the incoming data and insert it into the appropriate database tables.
6. Test the Integration: Create a test quiz in Riddle and submit it. Check your Supabase database to ensure the data has been correctly inserted.

By following these steps, you can successfully connect Riddle Quiz Maker to Supabase and store your quiz data in a scalable database.

Example Edge Function

The following Edge Function demonstrates how to process incoming webhook data from Riddle in your Supabase database:

import "jsr:@supabase/functions-js/edge-runtime.d.ts";
import { createClient } from "jsr:@supabase/supabase-js@2";
Deno.serve(async (req) => {
  try {
    // 1. Extract incoming Riddle data from the request
    const riddleData = await req.json();
    const formArray = riddleData.data.form;
    
    // 2. Find email and name from form data
    // The function searches for fields whose customId contains "email" or "name"
    const email = formArray.find((f) =>
      f.customId.toLowerCase().includes("email")
    );
    const name = formArray.find((f) =>
      f.customId.toLowerCase().includes("name")
    );

    // 3. Initialize Supabase client
    // Uses environment variables for URL and API key
    const supabase = createClient(
      Deno.env.get("SUPABASE_URL") ?? "",
      Deno.env.get("SUPABASE_ANON_KEY") ?? "",
      {
        global: {
          headers: {
            Authorization: req.headers.get("Authorization"),
          },
        },
      }
    );

    const TABLE_NAME = "webhook_riddle";
    
    // 4. Check if an entry with this email already exists
    const { data, error } = await supabase
      .from(TABLE_NAME)
      .select("email")
      .eq("email", email.value);

    if (error) {
      console.log("error: ", error);
      throw error;
    }

    if (data.length === 0) {
      // 5a. Create new entry if email doesn't exist yet
      const { data, error } = await supabase.from(TABLE_NAME).insert([
        {
          name: name.value,
          email: email.value,
          data: JSON.stringify(riddleData), // Store all Riddle data as JSON
        },
      ]);

      if (error) {
        console.error("Insert error:", error);
      }
    } else {
      // 5b. Update existing entry if email already exists
      const updatedAt = new Date();
      const { data, error } = await supabase
        .from(TABLE_NAME)
        .update([
          {
            name: name.value,
            data: JSON.stringify(riddleData), // Store updated Riddle data
          },
        ])
        .eq("email", email.value);
      if (error) {
        console.error("Update error:", error);
      }
    }
    
    // 6. Send successful response back to Riddle
    return new Response(
      JSON.stringify({
        data: {},
      }),
      {
        headers: {
          "Content-Type": "application/json",
        },
        status: 200,
      }
    );
  } catch (err) {
    // 7. Error handling - errors are also returned with status 200
    // to prevent Riddle from retrying the webhook
    return new Response(
      JSON.stringify({
        message: err?.message ?? err,
      }),
      {
        headers: {
          "Content-Type": "application/json",
        },
        status: 200,
      }
    );
  }
});

Code Explanation in Detail:

Data Extraction (Steps 1-2):

• The function receives JSON data from Riddle via req.json()
• Email and name fields are automatically identified from the form array
• The search is performed via the customId which must contain "email" or "name"

Supabase Integration (Step 3):

• A Supabase client is initialized with environment variables
• Authorization headers are passed through for additional security

Duplicate Check (Steps 4-5):

• Before inserting, the function checks if an entry with this email already exists
• New Entry: Complete data is inserted
• Existing Entry: Only name and data are updated (email remains the same)

Response Behavior (Steps 6-7):

• Successful processing is confirmed with status 200
• Even errors are returned with status 200 to avoid retry loops
• Riddle only interprets non-200 status codes as errors and would otherwise retry the webhook

Important Notes:

• Environment Variables: SUPABASE_URL and SUPABASE_ANON_KEY must be configured in the Supabase Edge Function
• Table Schema: The webhook_riddle table should have at least the columns id, name, email, and data (JSONB)
• Row Level Security (RLS): Ensure your table has appropriate RLS policies to allow the edge function to perform INSERT and UPDATE operations, or temporarily disable RLS for this table
• Error Handling: All errors are logged, but the webhook still returns status 200
• Data Format: The complete Riddle data is stored as a JSON string in the data column