Astrology API in Supabase: Edge Functions, SQL and RLS

You are building an astrology feature on Supabase and you have three things to protect: the API key, the birth data of your users, and your request quota. The usual first attempt calls the API from the browser, which exposes the key, or stores birth data somewhere the vendor can see it. Neither is necessary.

This guide is the architecture you can copy. The birth profile and the consent timestamp live in your Postgres. A Supabase Edge Function holds the RoxyAPI secret key and calls the Astrology API on behalf of a signed-in user. A natal chart is cached in a table, because the same inputs always produce the same chart. Daily horoscopes can be read straight from SQL. A page with no backend at all can use a widget instead.

Every SQL and TypeScript block below was executed; the hosted deploy commands were not. What ran against a live API and what was checked against the Supabase documentation only is stated just before the FAQ.

What does the architecture look like when Supabase calls an astrology API?

The RoxyAPI secret key never leaves the server, and the profile and consent never leave your database. The browser talks only to Supabase, with the signed-in session. An Edge Function verifies that session, reads the birth profile under it, returns a cached chart when one exists, and otherwise calls the API once and stores the result. RoxyAPI receives the birth date, time and coordinates for the calculation and nothing else: no email, no user id, no name.

The adjacent question, where conversation memory and reading history should live, is covered in building a memory layer for an AI astrology companion. This post is the Supabase wiring underneath it.

Ready to build this? The Astrology API returns a full natal chart with interpretations in one call, and one key covers 258+ endpoints across 18+ domains. See pricing.

How do you keep birth data and consent in your own Postgres?

Put the birth profile in a table keyed to auth.users, enable Row Level Security, and let each person read and write only their own row. Record consent as a required timestamp, so a profile without consent cannot exist. The chart cache gets a select policy and no write policy, which means a browser holding the Supabase publishable key can read its own chart but cannot write a fake one. Deleting the account deletes both rows.

create table public.birth_profile (
  user_id      uuid primary key references auth.users on delete cascade,
  birth_date   date not null,
  birth_time   time not null,
  birth_city   text not null,
  consented_at timestamptz not null,
  updated_at   timestamptz not null default now()
);

create table public.natal_chart (
  user_id    uuid primary key references public.birth_profile on delete cascade,
  input_key  text not null,
  place      jsonb not null,
  chart      jsonb not null,
  created_at timestamptz not null default now()
);

alter table public.birth_profile enable row level security;
alter table public.natal_chart enable row level security;

create policy "own profile" on public.birth_profile
  for all to authenticated
  using ((select auth.uid()) = user_id)
  with check ((select auth.uid()) = user_id);

create policy "read own chart" on public.natal_chart
  for select to authenticated
  using ((select auth.uid()) = user_id);

The policies follow the Supabase recommendations: name the authenticated role so the check never runs for signed-out requests, and wrap auth.uid() in a select so Postgres can evaluate it once per statement instead of once per row. The profile stores the city name the person typed, not coordinates. The Edge Function resolves the city, which is the one step that needs the API key.

How does a Supabase Edge Function call the astrology API?

Set the RoxyAPI key once with supabase secrets set, read it with Deno.env.get in one shared helper, and import the typed TypeScript SDK from npm through one deno.json import map. Edge Functions run Deno, which resolves npm: specifiers natively, so there is no build step. The function below verifies the caller session, returns the cached chart when the inputs match, and otherwise resolves the city and casts the chart. The three helpers are the ones the open source Supabase starter ships, unchanged.

import { corsHeaders, preflight } from '../_shared/cors.ts';
import { errorResponse, roxyClient } from '../_shared/roxy.ts';
import { adminClient, requireProjectKey, userClient } from '../_shared/supabase.ts';

const json = (body: unknown, status = 200) =>
  new Response(JSON.stringify(body), {
    status,
    headers: { ...corsHeaders, 'Content-Type': 'application/json' },
  });

Deno.serve(async (req) => {
  const refused = preflight(req) ?? requireProjectKey(req);
  if (refused) return refused;

  const user = await userClient(req);
  if (!user) return json({ error: 'Sign in first', code: 'unauthorized' }, 401);

  const { data: profile } = await user.db
    .from('birth_profile')
    .select('birth_date, birth_time, birth_city')
    .eq('user_id', user.userId)
    .maybeSingle();
  if (!profile) return json({ error: 'Save a birth profile first', code: 'no_profile' }, 404);

  const inputKey = `${profile.birth_date}|${profile.birth_time}|${profile.birth_city}`;
  const { data: cached } = await user.db
    .from('natal_chart')
    .select('input_key, chart')
    .eq('user_id', user.userId)
    .maybeSingle();
  if (cached?.input_key === inputKey) return json(cached.chart);

  const roxy = roxyClient();
  const { data: found, error: cityError } = await roxy.location.searchCities({
    query: { q: profile.birth_city, limit: 1 },
  });
  if (cityError) return errorResponse(cityError, corsHeaders);

  const place = found.cities[0];
  if (!place) return json({ error: 'Birth city not found', code: 'city_not_found' }, 404);

  const { data: chart, error } = await roxy.astrology.generateNatalChart({
    body: {
      date: profile.birth_date,
      time: profile.birth_time,
      latitude: place.latitude,
      longitude: place.longitude,
      timezone: place.timezone,
    },
  });
  if (error) return errorResponse(error, corsHeaders);

  await adminClient()
    .from('natal_chart')
    .upsert({ user_id: user.userId, input_key: inputKey, place, chart });

  return json(chart);
});

import { createClient } from '@supabase/supabase-js';
import { corsHeaders } from './cors.ts';

/**
 * The one place the Supabase project keys are read.
 *
 * The platform injects them as `SUPABASE_PUBLISHABLE_KEYS` and `SUPABASE_SECRET_KEYS`, each a JSON map
 * by key name, and the key a project is created with is named `default`. They are not JWTs, so the
 * platform JWT check is off for every function and `requireProjectKey` does that job instead.
 */
function keys(name: 'SUPABASE_PUBLISHABLE_KEYS' | 'SUPABASE_SECRET_KEYS'): Record<string, string> {
  return JSON.parse(Deno.env.get(name) ?? '{}');
}

/**
 * Refuses a request that does not carry one of the project keys on the `apikey` header.
 *
 * The publishable key is public, so this is not a secret check. It keeps the functions answering only
 * callers that were given your project, which is what spends your quota. Returns null when the key
 * matches.
 */
export function requireProjectKey(req: Request): Response | null {
  const sent = req.headers.get('apikey');
  const known = [
    ...Object.values(keys('SUPABASE_PUBLISHABLE_KEYS')),
    ...Object.values(keys('SUPABASE_SECRET_KEYS')),
  ];
  if (sent && known.includes(sent)) return null;
  return new Response(
    JSON.stringify({ error: 'Send your project publishable key on the apikey header', code: 'unauthorized' }),
    { status: 401, headers: { ...corsHeaders, 'Content-Type': 'application/json' } },
  );
}

/** Runs as the `service_role` database role and bypasses row level security. Server side only. */
export function adminClient() {
  return createClient(Deno.env.get('SUPABASE_URL')!, keys('SUPABASE_SECRET_KEYS').default);
}

/**
 * Runs as whoever the session token names, under their row level security policies.
 *
 * Returns null unless the token verifies, so a caller without a valid session is never mistaken for
 * one with it.
 */
export async function userClient(req: Request) {
  const token = req.headers.get('Authorization')?.replace(/^Bearer /, '');
  if (!token) return null;
  const db = createClient(Deno.env.get('SUPABASE_URL')!, keys('SUPABASE_PUBLISHABLE_KEYS').default, {
    global: { headers: { Authorization: `Bearer ${token}` } },
  });
  const { data } = await db.auth.getClaims(token);
  return data?.claims.sub ? { db, userId: data.claims.sub } : null;
}

import { createRoxy } from '@roxyapi/sdk';

/**
 * The one place the API key is read.
 *
 * Set it with `supabase secrets set ROXY_API_KEY=...`. It is a project secret, so it exists inside a
 * function and nowhere a browser can reach. A function that built its own client would be a second
 * place to leak it from, which is why there is only this one.
 */
export function roxyClient() {
  const key = Deno.env.get('ROXY_API_KEY');
  if (!key) throw new Error('ROXY_API_KEY is not set. Run: supabase secrets set ROXY_API_KEY=...');
  return createRoxy(key);
}

/** The API error contract, passed through instead of flattened. */
type RoxyError = { error: string; code: string; doc_url?: string };

/**
 * Turns an SDK error into a response that keeps the upstream status.
 *
 * Collapsing everything to 500 would tell a caller that a bad birth date and an outage are the same
 * event, and they retry differently: 4xx never succeeds on a retry, 429 and 5xx do.
 */
export function errorResponse(error: unknown, headers: HeadersInit): Response {
  const e = error as Partial<RoxyError> & { status?: number };
  const status = typeof e?.status === 'number' ? e.status : 502;
  return new Response(
    JSON.stringify({
      error: e?.error ?? 'Upstream request failed',
      code: e?.code ?? 'upstream_error',
      doc_url: e?.doc_url,
    }),
    { status, headers: { ...headers, 'Content-Type': 'application/json' } },
  );
}

/**
 * Permissive CORS because the demo page is served from anywhere, including a local file.
 *
 * Narrow `Access-Control-Allow-Origin` to your own site before you ship. It does not protect the API
 * key, which is never in the browser, but it does decide who may spend your quota through you.
 */
export const corsHeaders = {
  'Access-Control-Allow-Origin': '*',
  'Access-Control-Allow-Headers': 'authorization, x-client-info, apikey, content-type',
  'Access-Control-Allow-Methods': 'POST, OPTIONS',
};

/** Answers the preflight. Returns null when the request is not one. */
export function preflight(req: Request): Response | null {
  return req.method === 'OPTIONS' ? new Response('ok', { headers: corsHeaders }) : null;
}

{
  "imports": {
    "@roxyapi/sdk": "npm:@roxyapi/sdk@1",
    "@supabase/supabase-js": "npm:@supabase/supabase-js@2"
  }
}

[functions.natal-chart]
verify_jwt = false
import_map = "./functions/deno.json"

supabase secrets set ROXY_API_KEY=sk_your_key_here
supabase functions deploy natal-chart

// In the browser, signed in through Supabase Auth
const { data: chart } = await supabase.functions.invoke('natal-chart');

The function lives at supabase/functions/natal-chart/index.ts, the three helpers in supabase/functions/_shared/, and the import map at supabase/functions/deno.json, which config.toml points the function at.

Two clients, on purpose. userClient verifies the session token with getClaims and returns null unless it verifies, so a signed-out or forged caller never reaches a query; its client carries the publishable key and that token, so Row Level Security decides which profile it sees. adminClient uses the Supabase secret key, which Postgres sees as the service_role role: it bypasses policies and is the only thing allowed to fill the cache. The platform injects both keys into every function as SUPABASE_PUBLISHABLE_KEYS and SUPABASE_SECRET_KEYS, each a JSON map by key name, and the helper reads the one named default. Neither key is a JWT, so config.toml turns off the platform JWT check and requireProjectKey checks the apikey header instead; supabase.functions.invoke sends the publishable key there and the session on Authorization. Secret names starting with SUPABASE_ are reserved, which is why the RoxyAPI key is ROXY_API_KEY. cors.ts answers the browser preflight and allows any origin, so narrow it to your site before you ship. Try the chart request itself in the natal chart API reference, live in the browser with no signup.

Why can a natal chart be cached, and what does a repeat call cost?

A natal chart depends only on the birth date, time and place, so the answer never changes and a cached copy is exactly as correct as a fresh one. Sent the same body three times, POST /astrology/natal-chart returned three responses with the same SHA-256 hash. Served from the table instead, a repeat costs no API request at all, because the function returns before the SDK is called.

Measured through the function above, for a New York birth on 1990-01-15 at 14:30:

The input_key is what keeps the cache honest. If a person corrects their birth time, the key changes, the next call misses, and the chart is recomputed and replaced. Nothing needs to expire.

Numerology from a name and birth date, a kundli or a panchang for a fixed date are cacheable the same way. A random tarot draw or a coin-cast I Ching reading is not, because the randomness is the feature.

Can you query an astrology API straight from SQL in Supabase?

Yes, for content that does not vary per person. The OpenAPI foreign data wrapper that Supabase maintains reads the RoxyAPI spec, and a foreign table then turns an endpoint into something you select from. A value in the path, such as the zodiac sign, is filled from the where clause. It needs no function and no deploy, which makes it the shortest path to a daily horoscope on a landing page.

create extension if not exists wrappers with schema extensions;

create foreign data wrapper wasm_wrapper
  handler wasm_fdw_handler
  validator wasm_fdw_validator;

select vault.create_secret('sk_your_key_here', 'roxy_api_key');

create server roxy_server
  foreign data wrapper wasm_wrapper
  options (
    fdw_package_url 'https://github.com/supabase/wrappers/releases/download/wasm_openapi_fdw_v0.2.1/openapi_fdw.wasm',
    fdw_package_name 'supabase:openapi-fdw',
    fdw_package_version '0.2.1',
    fdw_package_checksum '12c902f3089e18142a1d8d35c66b9ceb85c193224229687bd929aff6b44cddde',
    base_url 'https://roxyapi.com/api/v2',
    spec_url 'https://roxyapi.com/api/v2/openapi.json',
    api_key_id '<the id returned by vault.create_secret>',
    api_key_header 'X-API-Key',
    api_key_prefix ''
  );

create schema if not exists roxy;

create foreign table roxy.daily_horoscope (
  sign text,
  overview text,
  love text,
  career text,
  lucky_number integer,
  lucky_color text,
  attrs jsonb
)
server roxy_server
options (endpoint '/astrology/horoscope/{sign}/daily');

select sign, lucky_number, lucky_color, overview
from roxy.daily_horoscope
where sign = 'aries';

insert into daily_reading (sign, payload)
select sign, attrs from roxy.daily_horoscope where sign = 'aries'
on conflict (sign, reading_date) do nothing;

Take the current package version and checksum from the wrapper catalog before you copy them, because both move. The key sits in Vault, and the server names it by the id vault.create_secret returns rather than holding it in plain text. Column names translate across conventions, so luckyNumber reads into lucky_number, and attrs holds the whole response row. The daily_reading table is the shared cache from the starter: one row per sign per day, a public read policy, and no write policy.

Three limits decide what belongs here. Foreign tables are read-only. A body-taking endpoint gets one fixed request_body per table, so one table is one chart, not one chart per visitor. Every select on a foreign table is a live API request, which is why the third tab copies the reading of the day into a table.

Where do a publishable key and widgets fit on a Supabase site?

A page that should show a reading with no backend at all, such as a marketing page or a static site hosted next to your Supabase app, does not need an Edge Function. It uses a RoxyAPI publishable key, which starts with pk_, is safe in page source, and can be locked to your origin. It is a different key from the Supabase sb_publishable_ key. A RoxyAPI secret key must never reach the browser, whether through a client-visible environment variable or a table the browser can select from.

The fastest route is a ready-made embeddable widget: paste one snippet and a live natal chart or daily horoscope renders on any surface that accepts HTML. The widgets guide in the docs covers creating the key and choosing the widget.

The rule is short. Anything personal goes through the Edge Function, so birth data stays under your policies and the RoxyAPI secret key stays in project secrets. Anything shared and anonymous can render from a widget. If you want the rendering without the widget, the same visual layer ships as MIT web components at RoxyAPI UI, fed with the JSON your function already returns.

FAQ

How do I call an astrology API from a Supabase Edge Function?

Store the RoxyAPI key with supabase secrets set ROXY_API_KEY=..., add npm:@roxyapi/sdk@1 to the functions deno.json import map, and create the client with createRoxy, reading the key with Deno.env.get. Resolve the birth city with roxy.location.searchCities, then pass its latitude, longitude and IANA timezone to roxy.astrology.generateNatalChart. With the Supabase publishable and secret keys, set verify_jwt = false and check the apikey header and the session in the function. The RoxyAPI key never reaches the browser.

Does RoxyAPI store the birth data of my users?

No. RoxyAPI is stateless and keeps no birth profiles, readings or user records. In the Supabase architecture above, the profile, the consent timestamp and the saved chart all live in your own Postgres under Row Level Security, and the API receives only the date, time and coordinates needed for the calculation.

Can I cache natal charts in my Supabase database?

Yes. A natal chart for a fixed date, time and place never changes, so store it once in a table keyed to the user and serve every repeat read from Postgres. Keep a key built from the inputs beside it, so a corrected birth time triggers one recalculation instead of serving a stale chart.

Can I query RoxyAPI from Postgres with plain SQL?

Yes, through the Supabase OpenAPI foreign data wrapper pointed at the RoxyAPI spec. It suits daily horoscopes and reference catalogues, with the sign or other path value filled from the where clause. Foreign tables are read-only, carry one fixed request body each and call the API on every select, so per-user charts belong in an Edge Function and daily content is worth copying into a table.

Can I show a horoscope on a Supabase site without a backend?

Yes. Create a RoxyAPI publishable key, which starts with pk_, is safe in page source and can be locked to your origin, and paste a RoxyAPI widget onto the page. It renders a live natal chart or daily horoscope with no Edge Function. Use the Edge Function path for anything personal, so birth data stays under your own policies.

Conclusion

On Supabase, an astrology feature is a few tables, one Edge Function and a secret: the person and their consent stay in your Postgres, the key stays in project secrets, and a chart is computed once. Start from the Astrology API, or fork the MIT Supabase starter, which ships the same helpers, config and SQL path with a shared daily cache and saved charts, and compare plans on pricing.