13/5: Backend - PostgreSQL extensions, Edge Functions & backend hardening

Daily Agenda

1 ~ PostgreSQL extensions

PostgreSQL can be extended with extra functionality. Supabase exposes many of these extensions directly in the database, which means we can solve some backend problems without adding another server. We should already have moddatetime from yesterday but we will add two new extensions.

Today we care about these extension categories:

In 000_extensions.sql, we enable the extensions we need:

create extension if not exists pg_cron schema extensions;
create extension if not exists pg_net schema extensions;

2 ~ Edge Functions demo

Edge Functions are small server-side functions that run next to Supabase. We use them when logic should not run in the frontend, for example because it needs a secret key, calls an external API, sends email or performs privileged backend work. For the demo, we will create an edge function which gets called each day and sends email reminder to all users.

Create an Edge Function

Create a new function from the Supabase CLI:

supabase functions new daily-email

This creates a folder inside supabase/functions.

Shared response helpers

Put common response logic in _shared/response-helpers.ts so every function handles JSON, CORS and errors consistently:

export const corsHeaders = {
  'Access-Control-Allow-Origin': '*',
  'Access-Control-Allow-Headers': 'authorization, x-client-info, apikey, content-type',
  'Access-Control-Allow-Methods': 'GET, POST, PUT, DELETE, OPTIONS',
}

export function ok(data?: Record<string, unknown>) {
  if (data === undefined) {
    return new Response('ok', { headers: corsHeaders })
  }

  return new Response(JSON.stringify(data), {
    headers: { ...corsHeaders, "Content-Type": "application/json" },
  })
}

export function error(message: string, status: number) {
  console.error(message)

  return new Response(JSON.stringify({ error: message }), {
    status,
    headers: { ...corsHeaders, "Content-Type": "application/json" },
  })
}

Function shape

A function should usually follow this flow:

Daily email function

For the demo, we will build a function that sends daily puzzle notification emails. The point is to understand:

• How an Edge Function receives a request.
• How it reads secrets.
• How it calls an external email provider.
• How logs appear when something fails.
• How to test locally before deployment.

Our reference function is named daily-email.

import "@supabase/functions-js/edge-runtime.d.ts"
import { createClient } from "jsr:@supabase/supabase-js@2"
import { error, ok } from "../_shared/response-helpers.ts"

function renderEmailTemplate(displayName: string, date: string): string {
  return `<div>
    <h1>Calda Academy</h1>
    <p>Hi ${displayName},</p>
    <p>Today's daily puzzle is ready for <strong>${date}</strong>. Can you group all 16 words correctly without running out of attempts?</p>
  </div>`
}

Deno.serve(async (req) => {
  const cronSecret = Deno.env.get("CRON_SECRET")
  if (!cronSecret || req.headers.get("x-cron-secret") !== cronSecret) {
    return error("Unauthorized", 401)
  }

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

  const today = new Date().toISOString().split("T")[0]

  const { data: puzzle, error: puzzleError } = await supabase
    .from("puzzles")
    .select("id")
    .eq("published_at", today)
    .single()

  if (puzzleError || !puzzle) {
    return ok({ skipped: "no puzzle today" })
  }

  const { data: users, error: usersError } = await supabase
    .from("users_data")
    .select("id, email, display_name")

  if (usersError || !users) {
    return error("Failed to fetch users", 500)
  }

  const resendApiKey = Deno.env.get("RESEND_API_KEY")
  if (!resendApiKey) {
    return error("RESEND_API_KEY is not set", 500)
  }

  const formattedDate = new Date(today).toLocaleDateString("en-US", {
    weekday: "long",
    year: "numeric",
    month: "long",
    day: "numeric",
    timeZone: "UTC",
  })

  const results = await Promise.allSettled(
    users.map((user) =>
      fetch("https://api.resend.com/emails", {
        method: "POST",
        headers: {
          "Content-Type": "application/json",
          "Authorization": `Bearer ${resendApiKey}`,
        },
        body: JSON.stringify({
          from: "puzzles@calda.agency",
          to: user.email,
          subject: "Today's Puzzle is Ready",
          html: renderEmailTemplate(user.display_name, formattedDate),
        }),
      })
    )
  )

  const sent = results.filter((r) => r.status === "fulfilled").length
  const failed = results.filter((r) => r.status === "rejected").length

  return ok({ sent, failed, total: users.length })
})

Serve functions locally:

supabase functions serve

Deploy a function:

supabase functions deploy daily-email

Set secrets in Supabase:

supabase secrets set RESEND_API_KEY=re_...
supabase secrets set CRON_SECRET=...

Scheduling the daily email with pg_cron

After the function works manually, we connect it to a cron schedule that calls it through HTTP automatically.

Because we already enabled pg_cron and pg_net in 000_extensions.sql, we can schedule HTTP requests directly from the database.

Creating vault secrets

You will use some environment variables in the cronjob call. Supabase created a feature called Vault for this. You can set your vault secrets via dashboard (Integrations -> Vault -> Secrets -> Add secret) or via SQL. The queries that will be used:

select vault.create_secret('http://host.docker.internal:54321', 'SUPABASE_URL'); -- https://<supabase project id>.supabase.co for remote database
select vault.create_secret('local-dev-cron-secret', 'CRON_SECRET');

Create the cron schedule

In your database (local or cloud), schedule a job that posts to the Edge Function URL at a fixed time every day:

select cron.schedule(
  'send-daily-puzzle-email',
  '0 8 * * *',
  $$
  select net.http_post(
    url := (
      select decrypted_secret
      from vault.decrypted_secrets
      where name = 'SUPABASE_URL'
    ) || '/functions/v1/daily-email',
    headers := jsonb_build_object(
      'Content-Type', 'application/json',
      'x-cron-secret', (
        select decrypted_secret
        from vault.decrypted_secrets
        where name = 'CRON_SECRET'
      )
    ),
    body := '{}'::jsonb
  );
  $$
);

The cron expression 0 8 * * * means the job runs at 08:00 UTC every day. Adjust the hour to match when you want the daily puzzle email to go out.

Verify the schedule

List active cron jobs to confirm it was created:

select * from cron.job;

Test the cron job manually

You can trigger the job immediately for testing:

select cron.run('send-daily-puzzle-email');

Then check the Edge Function logs in the Supabase dashboard to see whether the request arrived and whether emails were sent.

Unschedule if needed

If you need to remove or change the schedule:

select cron.unschedule('send-daily-puzzle-email');

3 ~ Edge Functions workshop

Now you will build a production-style function.

AI hint function

Build an Edge Function that returns a short hint for one puzzle group.

It should support this behavior:

• Accept a group id from the request.
• Fetch the group and its words from Supabase.
• Call an AI provider with a controlled prompt.
• Return only the hint to the client.
• Handle missing input, missing data and provider errors clearly.

Our reference function is named ai-hint.

Set the required secrets before deploying:

supabase secrets set OPENAI_API_KEY=sk-...

4 ~ Tasks for work-from-home

The rest of today's work is homework. Finish these tasks before the next session.

RLS on the remaining tables

Finish RLS for the rest of your public tables.

Your policies should separate two kinds of data:

• Shared puzzle content that authenticated users can read.
• User-owned attempt data that only the owner can read or mutate.

alter table public.puzzles enable row level security;
alter table public.puzzle_groups enable row level security;
alter table public.puzzle_words enable row level security;
alter table public.puzzle_attempts enable row level security;
alter table public.puzzle_attempt_solved_groups enable row level security;

create policy "Authenticated users can select puzzles"
  on public.puzzles
  for select
  to authenticated
  using (true);

create policy "Users can select their own puzzle_attempts"
  on public.puzzle_attempts
  for select
  to authenticated
  using ((select auth.uid()) = user_id);

create policy "Users can insert their own puzzle_attempts"
  on public.puzzle_attempts
  for insert
  to authenticated
  with check ((select auth.uid()) = user_id);

create policy "Users can update their own puzzle_attempts"
  on public.puzzle_attempts
  for update
  to authenticated
  using ((select auth.uid()) = user_id)
  with check ((select auth.uid()) = user_id);

Two PostgreSQL functions

Create two database functions the frontend can call.

The first function should return the full puzzle payload for one puzzle id, including groups, words, current user's mistakes and which groups are already solved.

The second function should return the user's ranking position for a solved puzzle by counting how many users solved it before them.

Test RLS end-to-end

Test the backend as different users, not only as the service role.

You should verify:

• Anonymous requests cannot read protected data.
• User A cannot read or update User B's profile or attempts.
• Authenticated users can read shared puzzle content.
• Users can insert and update only their own attempt rows.
• Views and functions do not accidentally bypass the rules you expect.

updated_at triggers

Add updated_at triggers to tables where the app needs to know when rows were last changed.

The pattern is the same as users_data:

create trigger handle_updated_at before update on public.users_data
  for each row execute procedure extensions.moddatetime(updated_at);

Apply the same idea to any other table in your schema that has an updated_at column.

Import data

Import the puzzle data into your database.

You should check:

• The import runs without foreign key errors.
• The dates are correct.
• Every puzzle has the expected groups.
• Every group has the expected words.
• The frontend-facing queries return the imported data correctly.