Get results into your database

On the page that contains the Meletia quiz, save the result of each game for the signed-in user.

1. Create the quiz_results table if it doesn't exist: id, user_id (the signed-in user), quiz_id (text), quiz_title (text), points (decimal number), max_points (decimal number), percent (integer), reason (text), created_at (date, defaults to now). Each user can only create and read their own rows.
2. In the page, listen for the "meletia:complete" JavaScript event on window: window.addEventListener("meletia:complete", (event) => { … }). It is fired by Meletia's embed.js script at the end of a game.
3. event.detail contains: quizId, quizTitle, points, maxPoints, percent, reason ("done": quiz finished, "time": time ran out, "lives": out of lives).
4. If a user is signed in, insert a row into quiz_results with their identifier and these values. Otherwise, save nothing.
5. Don't change the quiz iframe or script.

<script>
  window.addEventListener("meletia:complete", function (event) {
    var result = event.detail;
    // result.quizId, result.quizTitle, result.points, result.maxPoints,
    // result.percent, result.reason ("done", "time" ou "lives")
    console.log("Quiz terminé :", result.percent + " %");
  });
</script>

create table quiz_results (
  id bigint generated always as identity primary key,
  user_id uuid not null default auth.uid() references auth.users on delete cascade,
  quiz_id text not null,
  quiz_title text,
  points real not null,
  max_points real not null,
  percent integer not null,
  reason text,
  created_at timestamptz not null default now()
);

alter table quiz_results enable row level security;

create policy "Chacun enregistre ses résultats" on quiz_results
  for insert to authenticated with check (user_id = auth.uid());

create policy "Chacun lit ses résultats" on quiz_results
  for select to authenticated using (user_id = auth.uid());

import { supabase } from "./supabaseClient";

window.addEventListener("meletia:complete", async (event) => {
  const { data } = await supabase.auth.getUser();
  if (!data.user) return; // visiteur non connecté : rien à enregistrer

  const result = event.detail;
  await supabase.from("quiz_results").insert({
    user_id: data.user.id,
    quiz_id: result.quizId,
    quiz_title: result.quizTitle,
    points: result.points,
    max_points: result.maxPoints,
    percent: result.percent,
    reason: result.reason,
  });
});

Create an address that receives Meletia quiz results (a "webhook") and saves them in the database.

1. Create the quiz_games table: id, delivery_id (text, unique), quiz_id (text), quiz_title (text), completed (boolean), points (decimal number), max_points (decimal number), answers (JSON), received_at (date, defaults to now). Only the server writes to this table.
2. Create a server-side function (for example a Supabase Edge Function named meletia-webhook, or an API route /api/meletia-webhook), publicly reachable with POST, without user sign-in. For a Supabase Edge Function, turn off JWT verification (verify_jwt = false).
3. It reads the raw request body (text, before any JSON.parse) and checks the signature:
   - the "Meletia-Signature" header looks like t=<timestamp>,v1=<signature>;
   - compute a hexadecimal HMAC SHA-256 of "t.body" (the timestamp, a dot, then the raw body) with the secret from the MELETIA_WEBHOOK_SECRET environment variable;
   - compare it to v1 in constant time; reply 401 if it differs, or if t is more than 5 minutes old.
4. The body is JSON: { id, type: "game.finished", createdAt, test, data: { quiz: { id, title }, completed, points, maxPoints, answers } }.
5. If test is true (test delivery), reply 200 without saving anything.
6. Otherwise, insert a row into quiz_games with delivery_id = id; if that id already exists, do nothing (Meletia may resend the same delivery).
7. Reply 200 within 5 seconds.
8. Set up the MELETIA_WEBHOOK_SECRET secret environment variable: I'll give you its value. Tell me the full public address of the function.

create table quiz_games (
  id bigint generated always as identity primary key,
  delivery_id text not null unique,
  quiz_id text not null,
  quiz_title text,
  completed boolean not null,
  points real not null,
  max_points real not null,
  answers jsonb not null,
  received_at timestamptz not null default now()
);

-- Seule la fonction serveur (clé de service) écrit dans cette table.
alter table quiz_games enable row level security;

// supabase/functions/meletia-webhook/index.ts
// À déployer sans vérification JWT : supabase functions deploy meletia-webhook --no-verify-jwt
import { createClient } from "npm:@supabase/supabase-js@2";

const secret = Deno.env.get("MELETIA_WEBHOOK_SECRET")!;
const supabase = createClient(Deno.env.get("SUPABASE_URL")!, Deno.env.get("SUPABASE_SERVICE_ROLE_KEY")!);

async function hmac(payload: string) {
  const key = await crypto.subtle.importKey(
    "raw", new TextEncoder().encode(secret), { name: "HMAC", hash: "SHA-256" }, false, ["sign"],
  );
  const mac = await crypto.subtle.sign("HMAC", key, new TextEncoder().encode(payload));
  return [...new Uint8Array(mac)].map((b) => b.toString(16).padStart(2, "0")).join("");
}

function sameText(a: string, b: string) {
  if (a.length !== b.length) return false;
  let diff = 0;
  for (let i = 0; i < a.length; i++) diff |= a.charCodeAt(i) ^ b.charCodeAt(i);
  return diff === 0;
}

Deno.serve(async (req) => {
  if (req.method !== "POST") return new Response("Method not allowed", { status: 405 });

  const body = await req.text();
  const header = req.headers.get("meletia-signature") ?? "";
  const t = header.match(/t=(\d+)/)?.[1];
  const v1 = header.match(/v1=([0-9a-f]{64})/)?.[1];
  const fresh = t !== undefined && Math.abs(Date.now() / 1000 - Number(t)) <= 300;
  if (!t || !v1 || !fresh || !sameText(await hmac(`${t}.${body}`), v1)) {
    return new Response("Invalid signature", { status: 401 });
  }

  const event = JSON.parse(body);
  if (event.test) return new Response("ok"); // envoi d'essai depuis Meletia

  const game = event.data;
  const { error } = await supabase.from("quiz_games").upsert(
    {
      delivery_id: event.id,
      quiz_id: game.quiz.id,
      quiz_title: game.quiz.title,
      completed: game.completed,
      points: game.points,
      max_points: game.maxPoints,
      answers: game.answers,
    },
    { onConflict: "delivery_id", ignoreDuplicates: true },
  );
  if (error) return new Response("Database error", { status: 500 });
  return new Response("ok");
});

// app/api/meletia-webhook/route.ts
import crypto from "node:crypto";

export async function POST(request: Request) {
  const body = await request.text();
  const header = request.headers.get("meletia-signature") ?? "";
  const t = header.match(/t=(\d+)/)?.[1];
  const v1 = header.match(/v1=([0-9a-f]{64})/)?.[1];
  if (!t || !v1 || Math.abs(Date.now() / 1000 - Number(t)) > 300) {
    return new Response("Invalid signature", { status: 401 });
  }
  const expected = crypto
    .createHmac("sha256", process.env.MELETIA_WEBHOOK_SECRET!)
    .update(`${t}.${body}`)
    .digest("hex");
  if (!crypto.timingSafeEqual(Buffer.from(v1), Buffer.from(expected))) {
    return new Response("Invalid signature", { status: 401 });
  }

  const event = JSON.parse(body);
  if (!event.test) {
    // Enregistrez ici event.data dans votre base (en ignorant un event.id déjà reçu).
    await saveGame(event.id, event.data);
  }
  return new Response("ok");
}