The stocks public API

Lesson 6 · about 15 minutes · the seven read endpoints the app calls, and the three tricks that keep them fast and safe.

The stocks function is really two programs sharing a router: a read API for the app and an ingestion engine for cron. This lesson is the read half. Everything here answers in one request from tables that cron already filled; nothing here calls Massive. The full route table is in the stocks endpoints reference.

Auth on this function is not Supabase's auth

config.toml sets verify_jwt = false for stocks, so the platform checks nothing. Instead stocks/utils/auth.ts compares the apikey header (or a bearer) against two env vars, PUBLISHABLE_KEY and SECRET_KEY. verifyPublicAuth accepts either; verifyServiceRoleAuth accepts only the secret. The legacy anon/service_role JWTs are rejected on purpose. stocks/utils/auth.ts:61, :106

Why a custom check? Supabase's built-in JWT gate is all-or-nothing per function. Stocks needs public reads and service-only writes under one deploy, so the gate had to move into the handler. The env var names cannot start with SUPABASE_, which is why they are the odd ones out. Two exceptions use the shared JWT module instead: watchlist and benchmark call getUserId because they are per-user. stocks/utils/auth.ts:18, watchlist.ts:81

Endpoint by endpoint

Route	Reads	Notable
`details`	`Stock_Current_Quotes` joined to `Companies`, `Events`, `Earnings_Metrics`, rpc `get_ticker_watcher_count`	Four independent reads issued with `Promise.all`; only the earnings-date lookup waits on an earlier result. Ticker from query first, then POST body. stockDetails.ts:250-258
`leaderboard`	`Stock_Current_Quotes` or rpc `search_stocks`; `Events`; rpc `get_row_sparklines`	Fetches `pageSize + 1` rows to know if there is a next page without a count query. Browse path orders by the sort field then `ticker` so ties cannot repeat across pages. stockLeaderboard.ts:243-256
`price-history`	`Stock_Price_History`, one partition	`TIMEFRAME_CONFIG` maps timeframe to tier: 1d intraday 5min, 5d hourly, 1m to 2y and ytd daily. Range mode picks hourly for spans of 7 days or less. Timestamps come back in Eastern Time with offset. priceHistory.ts:5-52, :71
`logos`	`Stock_Current_Quotes`	Returns bucket URLs under the old `icon`/`logo` names, so the same `<img src>` works and image bytes leave the payload. stockLogos.ts:7-11
`news`	`Stock_News_Article_Tickers`	`limit` clamped to 1-50, default 10; garbage input falls back rather than 400s. stockNews.ts:57-66
`watchlist`	`watchlist_items` as the user; quotes and sparklines as anon	Two clients in one handler. See below.
`benchmark`	SPY closes	Same point shape as the portfolio RPC so the client draws both lines with one parser. Grains are a cross-service contract. benchmark.ts:1-20

The two-client pattern

1getUserId(req) verifies the JWT or throws, so an anonymous caller gets 401 before any query. watchlist.ts:79-85

2A user client is built with the anon key plus the caller's Authorization header forwarded. Postgres runs the query as role authenticated with the caller's sub, so RLS on watchlist_items returns only that user's rows. watchlist.ts:97-102

3An empty result means either an empty list or someone else's watchlist_id. Both return an empty feed. Nothing distinguishes them, so nothing leaks. watchlist.ts:122-128

4An anon client then reads Stock_Current_Quotes and the sparklines, because those tables only have anon SELECT policies. The two reads run in parallel. watchlist.ts:133-144

The ordering rule: user-scoped data through the user's identity, public data through anon. Never the service key on a read path. If a handler needs the service key to read, the RLS policy is wrong, not the handler.

Where the types live

types/types.ts holds Address, PriceChange, PriceHistoryBar, PriceHistory and BaseStockCard. LeaderboardStock and WatchlistStock both extend BaseStockCard and add a sparkline. types/database.ts mirrors table rows with Postgres decimals typed as string, because PostgREST serialises numeric as text. responses.ts and requests.ts are re-export barrels for other functions. This is the layout CLAUDE.md mandates: base file, per-endpoint extensions, nothing duplicated. types/leaderboardTypes.ts:12, types/database.ts:13

Check yourself

Do this in the repo

With the stack served, hit three endpoints and note which fail auth versus CORS:

H='-H "apikey: $PUBLISHABLE_KEY" -H "Origin: http://localhost:5173"'
eval curl -s $H 'http://localhost:54321/functions/v1/stocks/leaderboard?pageSize=3&sortBy=volume' | head -c 600
eval curl -s $H 'http://localhost:54321/functions/v1/stocks/price-history?ticker=AAPL&timeframe=5d&from=2026-01-01'
eval curl -s $H 'http://localhost:54321/functions/v1/stocks/news?ticker=AAPL&limit=999' | head -c 300

Read search_stocks in migrations/20260414124431_add_pagination_to_search_stocks.sql and find the tiebreak the browse path later copied.
Trace getRowSparklines in stocks/utils/leaderboardCard.ts to the SQL function it calls, and note which tier it reads for the 1d window.

Primary source

Supabase: Row Level Security, specifically the section on auth.uid() in policies, explains why the user client in step 2 works without any filter in the query.

Next: Lesson 7, stocks ingestion: the other half of this function, where the tables you just read get filled. Ask your teacher in the terminal to show any handler line by line.