Supabase Edge Functions

Call OilPriceAPI from a Supabase Edge Function, cache the result in Postgres, and serve it to your app — without ever shipping your API key to a browser.

Edge Functions run Deno, so there is no package to install. fetch is built in.

Overview

This is the pattern most Supabase projects want:

1. An Edge Function calls OilPriceAPI using a key held in Supabase secrets.
2. It writes the price into a Postgres table.
3. Your app reads that table through Supabase's normal client — fast, cached, and rate-limit-free.
4. pg_cron refreshes on a schedule you choose.

Why route through an Edge Function rather than calling us from the client:

• Your API key stays server-side. A key in browser JavaScript is a public key.
• One scheduled fetch serves every user, so your quota tracks your refresh interval, not your traffic.
• You control freshness explicitly instead of inheriting whatever the client did.
• Postgres row-level security governs who sees what.

Prerequisites

1. An OilPriceAPI account and key (sign up — the free tier includes 50 requests per day)
2. A Supabase project
3. The Supabase CLI, for local development and deploys

Step 1 — Store the API key as a secret

Never put the key in the function source.

supabase secrets set OILPRICEAPI_KEY=your_api_key_here

Confirm it landed:

supabase secrets list

Step 2 — Create the table

create table public.commodity_prices (
  code            text        primary key,
  price           numeric     not null,
  currency        text        not null,
  unit            text,
  source_type     text,
  source_at       timestamptz,          -- when the SOURCE published it
  fetched_at      timestamptz not null default now()
);

alter table public.commodity_prices enable row level security;

-- Scope this to who should actually see it. `using (true)` alone would make
-- the table readable by anyone holding your anon key — which, in a browser
-- app, is everyone. That turns a private cache into a public price feed.
create policy "prices readable by signed-in users"
  on public.commodity_prices for select
  to authenticated
  using (true);

If your app genuinely is public and unauthenticated, widen that deliberately rather than by default — and read Data Provenance & Rights first. OilPriceAPI grants no rights in any of the data. Redistribution terms are set by each series' originating source, not by us.

Store source_at separately from fetched_at. They are different facts: one is when the market data was published, the other is when you collected it. Collapsing them into a single "updated" column is the most common cause of a dashboard that looks fresh while showing a stale price.

Step 3 — Write the Edge Function

supabase functions new refresh-prices

supabase/functions/refresh-prices/index.ts:

import { createClient } from "jsr:@supabase/supabase-js@2";

const API_KEY = Deno.env.get("OILPRICEAPI_KEY");
const CODES = ["BRENT_CRUDE_USD", "WTI_USD", "NATURAL_GAS_USD"];

Deno.serve(async (req) => {
  if (!API_KEY) {
    return new Response(
      JSON.stringify({ error: "OILPRICEAPI_KEY is not set" }),
      { status: 500, headers: { "Content-Type": "application/json" } },
    );
  }

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

  const rows = [];
  const failures = [];

  for (const code of CODES) {
    const res = await fetch(
      `https://api.oilpriceapi.com/v1/prices/latest?by_code=${code}`,
      {
        headers: {
          Authorization: `Token ${API_KEY}`,
          "Content-Type": "application/json",
          // Do NOT override User-Agent here. Deno already sends
          //   Deno/x.y.z (variant; SupabaseEdgeRuntime/a.b.c)
          // which is how we recognise Edge Function traffic. Replacing it
          // makes your calls indistinguishable from any other custom client.
          //
          // To label your own app too, use a separate header — it costs you
          // nothing and keeps the runtime signal intact:
          //   "X-API-Client": "my-app/1.0",
        },
      },
    );

    // A non-2xx is a real answer, not an exception. 402 means the plan quota
    // is exhausted; 429 means the rolling rate limit is. Only 429 and transient
    // 5xx are worth retrying.
    if (!res.ok) {
      failures.push({ code, status: res.status });
      continue;
    }

    const { data } = await res.json();
    rows.push({
      code: data.code,
      price: data.price,
      currency: data.currency,
      unit: data.unit ?? null,
      source_type: data.type ?? null,
      // `observed_at` is when the SOURCE observed the price. `created_at` is
      // when we recorded it — close together, but not the same fact, and only
      // the first one is safe to show a person as "as of".
      source_at: data.observed_at ?? data.source_date ?? null,
      fetched_at: new Date().toISOString(),
    });
  }

  if (rows.length) {
    const { error } = await supabase
      .from("commodity_prices")
      .upsert(rows, { onConflict: "code" });
    if (error) {
      return new Response(JSON.stringify({ error: error.message }), {
        status: 500,
        headers: { "Content-Type": "application/json" },
      });
    }
  }

  // Report partial failure honestly rather than returning 200 for a run that
  // updated nothing.
  const status = rows.length === 0 && failures.length ? 502 : 200;
  return new Response(JSON.stringify({ updated: rows.length, failures }), {
    status,
    headers: { "Content-Type": "application/json" },
  });
});

Deploy it:

supabase functions deploy refresh-prices

Step 4 — Schedule the refresh

Enable pg_cron and pg_net in your project, then:

select cron.schedule(
  'refresh-commodity-prices',
  '0 * * * *',                      -- hourly; see the quota note below
  $$
  select net.http_post(
    url     := 'https://YOUR_PROJECT_REF.supabase.co/functions/v1/refresh-prices',
    headers := jsonb_build_object(
      'Content-Type',  'application/json',
      'Authorization', 'Bearer YOUR_ANON_KEY'
    )
  );
  $$
);

-- did the function return 2xx on recent runs?
select id, status_code, created
from net._http_response
order by created desc
limit 10;

-- has the table actually moved?
select code, price, fetched_at, now() - fetched_at as age
from public.commodity_prices
order by fetched_at asc;

Choosing an interval

Each run above spends one request per code. Budget against your plan:

Paid quotas are monthly; the free tier is daily. Convert before comparing — a daily figure that looks small can still exhaust a monthly plan.

Add a fourth code and every row grows by a third. Budget the monthly figure, not the daily one.

The free tier is 50 requests per day, and it resets daily. Plan quotas are monthly: Developer 10,000, Starter 50,000, Professional 100,000, Scale 1,000,000. See Rate Limiting for the full table.

Step 5 — Read it from your app

const { data, error } = await supabase
  .from("commodity_prices")
  .select("code, price, currency, source_at, fetched_at")
  .eq("code", "BRENT_CRUDE_USD")
  .single();

Your app now reads Postgres, not us. It stays fast when we are slow, and it keeps working during a network blip — it just serves the last known price, with source_at and fetched_at there so you can decide whether that is acceptable.

Handling staleness

Serving a cached price is only safe if you can tell how old it is. Show the age, or refuse to show the number:

const ageMinutes = (Date.now() - new Date(row.fetched_at).getTime()) / 60000;
const stale = ageMinutes > 180; // your tolerance, not ours

Markets close. A weekend price that has not moved is correct, not broken — which is why source_at matters more than fetched_at for anything a person reads.

Local development

supabase functions serve refresh-prices --env-file ./supabase/.env.local

./supabase/.env.local:

OILPRICEAPI_KEY=your_api_key_here

Add that file to .gitignore. Then:

curl -i --location --request POST \
  'http://localhost:54321/functions/v1/refresh-prices' \
  --header 'Authorization: Bearer YOUR_ANON_KEY'

Troubleshooting

401 Unauthorized — the key is missing or malformed. The header is Authorization: Token YOUR_KEY, not Bearer. Confirm with supabase secrets list that OILPRICEAPI_KEY is set for the deployed project and not only locally.

402 Payment Required — the plan quota is exhausted. On the free tier this clears at the next daily reset. Lengthen the cron interval or upgrade.

429 Too Many Requests — the rolling rate limit (60 requests per 60 seconds, on every plan) was exceeded. If you loop over many codes, space the calls or batch them.

Function times out — a serial loop over many codes can exceed the Edge Function wall clock. Fetch concurrently with Promise.allSettled, and keep allSettled rather than all so one bad code cannot discard the whole batch.

Rows never update, function returns 200 — check the return value, not the status. The example above returns 502 when every code failed, precisely so a run that updated nothing cannot look like a success.

Related

• Rate Limiting — quotas, windows, and what 402 vs 429 mean
• Quick Start: JavaScript
• n8n and Make — if you would rather not write the function
• Data Provenance & Rights — what you may do with the data you cache