Conventions & limits

The contract every endpoint shares: base URL, auth tiers, row caps, null semantics, freshness, and error behavior. This page is the single source of truth.

Overview

This page is the contract. The auth tiers, the 1,000-row cap, the unadjusted-price rule, the no-fill rule and the FIDC regime break are written here and nowhere else; other pages link to these anchors. The machine-readable twin is POST /rpc/catalog.

Base URL

https://zcjbtpxuhdekpwcxmepn.supabase.co/rest/v1/

The live Supabase Data API (schema api). Views are GET /<view>; functions are POST /rpc/<function>.

Note: /v1/* paths belong to serve/app.py, a local Flask adapter that is not the public API.

Authentication

Send the project’s publishable key (sb_publishable_…) on the apikey header.

apikey: sb_publishable__yfFQsykAglrvc9GS6_PYw_B24ex437

The publishable key is not a JWT: do not put it in Authorization: Bearer. That header is for a user token, which moves you to the authenticated tier. Never use the Secret / service_role key.

The printed publishable key is shared. It is a project identifier, not your identity.

Sign in with GitHub at silo-bz-deloslabs.vercel.app/signin.html and send the token as Authorization: Bearer <token> alongside apikey:

	anonymous	signed in
panel ids per call	3	50
panel universe mode (no ids + a family)	no	yes
search_funds rows	25	200
option_chain rows	200	2,000
option_exercises rows	500	5,000
statement timeout	3s	8s
rows per response	1,000	1,000

• Rows per response never change. db-max-rows is server-wide; no tier raises it.
• Exceeding an id ceiling is an error, not a trim. 22023 (HTTP 400) naming the limit.
• The token lasts about an hour, and expiry is silent. No 401: you drop back to the anonymous tier, and a four-id panel starts raising 22023.

Not reachable: landing tables (cvm_*, b3_cotahist, cia_*, bacen_*) carry no grant for either role (Accept-Profile: public returns 401). Schema api is the whole public surface; the list for your server is catalog().postgrest. If api is not the default schema, add Accept-Profile: api to GET and Content-Profile: api to POST.

Two shapes of endpoint

	Views (GET)	Functions (POST /rpc)
Endpoints	quotes, equities, bdrs, units, fund_quotas, cash_securities, auctions, funds	everything else
Arguments	PostgREST query syntax	named, JSON body, all prefixed p_
Above the cap	truncate	refuse

Filter syntax

select=a,b,c · col=eq.V · gt / gte / lt / lte / neq · col=in.(A,B) · col=is.null (not.is.null) · order=col.desc (.asc, .nullslast) · limit=100&offset=100 · header Prefer: count=exact adds the total to Content-Range. The same operators work on an RPC result (POST /rpc/fund_nav?select=period,nav&order=period.desc), applied after the function’s own cap.

Function arguments

• Names are the SQL parameter names (p_ticker, p_from, p_metrics). An unknown name fails PGRST202 (functions match by argument set, so a typo looks like a missing function).
• Omitting an argument is not the same as sending null. Defaults apply only when absent. "p_from": null makes BETWEEN null AND …, which matches nothing. The one exception is p_board, where explicit null means “all boards”.
• An unrecognised panel metric is ignored, not rejected, so the panel comes back smaller and plausible. Take names from catalog().metrics.

Dates, ids, and case

• Dates are ISO YYYY-MM-DD. Ranges use BETWEEN, both ends inclusive.
• Tickers are upper-cased and trimmed server-side. CNPJs are stripped to 14 digits.
• In panel, an id with exactly 14 digits after stripping is a CNPJ; anything else is a ticker.
• Monthly grains key on the first day of the month in panel. fund_nav returns CVM’s filed period, whose convention is per family (fidc files month-end, fip files 31-December annually).

Row caps

The binding cap is 1,000 rows, and it is not in the SQL. PostgREST is deployed with db-max-rows = 1000.

• GET views truncate: cut to the first 1,000 (the oldest) rows, returned as 200. A cut series looks like a series that ended.
• POST /rpc functions refuse: every set-returning function raises 22023 rather than hand back a slice that looks whole.

The refusal says why and how: <function>: refused, this request would return more than 1000 rows., the reason, then To fix: with the remedy for that function. PostgREST also returns them separately as details (why) and hint (how). Example hint: This function has no cursor. Narrow the window with p_from/p_to, or ask explicitly for the newest N rows with p_limit (1..1000).

Note: fidc_cedentes, fidc_sacados and fidc_portfolio trimmed silently before catalog v34 and now refuse like the rest. Their p_limit is an explicit newest-first head: 1..1000 returns exactly the newest N rows; null (or anything above one page) is the whole window, served whole or refused; below 1 is 22023.

Refuse and page with p_after:

Endpoint	Cursor
panel	'<date>|<id>|<metric>|<asset_class>'
quote_history	the last row’s trade_date
fund_nav	the last row’s period; p_entity_type required to page
index_history	the last row’s trade_date
trade_consolidated_history	the last row’s trade_date

Refuse, no cursor: narrow the request:

Endpoint	How
option_history · termo_history	narrow p_from/p_to
financials	narrow the window or pin a statement
company_financials	narrow the window
anbima_classes	narrow the window or pin a category
company_events	defaults to 12 months of deliveries; narrow the window or pin p_category
macro_series · ptax	defaults to 12 months (120 for a monthly series); narrow the window
future_curve · curve	one session, about 50 contracts or 300 vertices, always fits
future_series · curve_history	defaults to 12 months; narrow p_from/p_to (about four years fit)
research_universe	no narrowing parameter; 639 pairs today, one page
portfolio_resolve · portfolio_fees · portfolio_lookthrough · portfolio_movement · portfolio_instruments · portfolio_fund_terms	no window: send at most 200 statement lines, codes or CNPJs per call; for the look-through, fewer funds or a lower p_max_depth
inflation	defaults to 36 months; pin p_series or p_family for deeper history
inflation_items	defaults to the nine groups over 36 months; pin p_item or one month for deeper levels
fidc_cedentes	narrow p_from/p_to (a p_cedente lookup spans many funds), or newest N with p_limit (1..1000)
fidc_sacados	narrow the window (25 rows per month), or p_limit (1..1000) for the newest N
fidc_portfolio	narrow the window or pin p_kind, or p_limit (1..1000) for the newest N
fidc_tranches	narrow the window or pin p_series
fidc_aging	narrow the window (21 rows per month)
fund_documents	defaults to 12 months of deliveries; narrow the window or pin p_tipo
fund_restatements	defaults to 30 days without p_cnpj; narrow the window or pin p_cnpj / p_tipo_fundo
fund_restatement_diff	needs p_cnpj or p_fnet_id; narrow the window or pin p_fnet_id
screen_* (seven)	raise the thresholds, or pin p_dormancy / p_min_nav (dormant funds) or p_driver (delinquency drivers)
screen_restatements / screen_late_filers / screen_silent_filers	raise the thresholds, or pin p_modalidade (restatements) or p_family (late and silent filers)

Fixed caps:

Endpoint	Rows
search_funds	25 anonymous, 200 signed in (p_limit clamped, not refused)
lookup	20
quote_latest	1
coverage	one per dataset, plus one funds_<family> row per family (~9)
metric_coverage	one per filed (family, metric) pair
option_chain	200 anonymous, 2,000 signed in (default 100)
option_exercises	500 anonymous, 5,000 signed in (clamped)
panel ids	3 ids anonymous / 50 signed in; universe mode signed in only

The cursor protocol

p_after	Meaning
absent / null	Whole result. Refuses above 1,000 rows.
'' (empty string)	First page.
the key from the last row	The page after that key.

A page shorter than 1,000 rows is the last. The key is '<date>\|<id>\|<metric>\|<asset_class>' for panel, and the bare date for quote_history, fund_nav, index_history and trade_consolidated_history.

• fund_nav requires a family before it will page. Its cursor is a bare period, unique only within a family (385 CNPJs file under two in the same month). Without p_after you get every family at once, each row labelled with its entity_type.
• Range paging does not work on RPC. Rows 1000–1999 of an /rpc/ call re-run the function and return the first page again. p_after is the RPC cursor; Range / limit / offset are the view cursor.

Reading Content-Range

On GET views it is the only truthful signal: 0-999/* means you hit the cap and there is more; 0-41/* means you got everything; with Prefer: count=exact you get 0-999/1906 (the true total). RPC functions raise 22023 instead, so 0-999/* means you are on a view. The SDK raises SiloTruncated.

Statement timeouts

3 seconds for anon, 8 seconds for authenticated. Exceeding it fails with SQLSTATE 57014, never a partial result: narrow the window, ids, or metrics. The first call after idle can be slow; retry once before concluding the API is down.

Nulls and gaps

Missing observations are never filled. No forward-fill, no interpolation, no last-observation-carried-forward, no synthesized month-end bar. Never a guessed price, NAV, fill, ranking, or identifier match.

• Absent row vs null. In panel, a metric with no observation for that (id, date) produces no row. In views and series functions, a column the source did not publish is JSON null (panel’s close and volume can be too).
• close_return across a gap is null, hence absent. Monthly: only when the previous row is the immediately preceding calendar month. Daily: only when the previous session is within 7 calendar days. A quotation-factor change also nulls it (a fatcot flip rescales the quote ±99.9% with no market move), and so does a split, grouping or bonus between the two prints (DESDOBRAMENTO, GRUPAMENTO, BONIFICACAO; monthly: anywhere between the two month-end prints), which changes the share count, not the value.
• Prices are unadjusted. Every quote row carries adjusted: false; a 2:1 split reports roughly −50% in the raw close (panel’s close_return adjusts the previous close across it). close is the price as published (for a paper quoted per lot, 1,000 shares); close_unit divides by quotation_factor so levels compare across papers. Neither is corporate-action adjusted. The one adjusted price is quote_history’s close_adj (refused, never served raw, where it cannot be stood behind; see Quotes), and quote_history refuses a window that starts before an instrument’s first session (the tape starts 2019-01-02).
• Not applicable is not missing. fund_nav returns eleven columns for every family but each files only some. A null outside a family’s list is by construction; inside it, a blank in that filing. Map: catalog().applicability.
• delinquency is a value, not a rate (BRL). Ask for nav in the same call and divide yourself.
• freq=day is quotes only. No fund fundamental is daily; mix asset classes at freq=month.
• panel grain is (id, asset_class, date, metric). Pass p_entity_type or keep asset_class in your pivot key.
• Identifier matches are never invented. lookup company tickers come from CVM’s published FCA registry.
• Exercises and auctions are events, not quotes. option_exercises (tpmerc 012/013) and the auctions view (017) carry one print per event. Never compute a return over either.

Regime breaks

Where a source format changed so one column means different things on either side of a date, the boundary is published, not smoothed: catalog().regime_breaks and that dataset’s coverage row (notes). Read it before differencing anything.

FIDC delinquency starts at 2013-01 and is on every row from 2020-11. It is tab VI’s filed total of overdue credits, the same field in CVM’s yearly HIST archive (through 2024-12) and the monthly informe (from 2025-01). Through 2020-10-31 a fund with no tab VI row that month is null (not zero, not clean books). From 2020-11-30 it is filed on every row (a fund with none files 0).

Never read a pre-2020-11 null as zero or fill it, and never compare a count of reporting funds across 2020-10 → 2020-11.

Default windows serve only complete periods

With no explicit to, fund metrics end at each family’s latest COMPLETE period: a month CVM has only fractionally published is not served as if it were the industry, and FIP’s early-filed year-end row waits for its year to close. An explicit to serves the window verbatim.

Freshness

Call coverage before you claim anything is current. Only the first two columns below are freshness:

Column	Means
as_of	The newest period that has landed and has actually elapsed. This is freshness.
complete_through	The newest period classified COMPLETE, which is what the default windows serve.
newest_period	The newest period key present. Can sit in the future (FIP is keyed 31-December). Never read as freshness.
landed_at	When ingest last succeeded. A later failed run never advances it.
landed_git_sha	The git commit of that same run. null when the run recorded none (before v34, or outside GitHub Actions), and never borrowed from an older run.

metric_coverage says which (family, metric) pairs are actually filed and over what span. A pair absent from it is one the family never files, not one whose data is late.

Note: landed_at is our health; complete_through is the source’s. A stale complete_through with a fresh landed_at is Brazil’s filing calendar, not an outage.

Errors and empty results

PostgREST returns a JSON array on success: no envelope, no count, no kind.

Status	Body	Meaning
200	[...]	Rows.
200	[]	Known query, no matching data. Unknown ticker, unknown CNPJ, or a real ticker with no sessions in the window.
400	42883 / PGRST codes	Malformed filter, bad argument type, out-of-range date.
401	Invalid API key / Invalid JWT	Missing or wrong apikey, or a publishable/secret key sent as Authorization: Bearer.
404	PGRST202	No function with that name and argument set. Check the p_ names.
400	22023	A ceiling, or a window over one page. The message names the limit, what you sent, and how to get past it.
401	n/a	Landing tables are not reachable, by grant, for every caller.
406	n/a	You asked for a single object and the result had zero or more than one row.
500	57014	Statement timeout, not a server fault.

An empty array does not say why it is empty. Confirm the identifier with lookup and the range with coverage.

What is live

SQL under api reaches production only when the analytical layer is applied (the nightly ingest, or an operator’s analytics-only run), so a merge and a deploy are not the same moment. The truth is POST /rpc/catalog:

• catalog().version is the contract the database is serving. The SDK warns SiloCatalogDrift on a mismatch.
• catalog().postgrest lists the functions and views that exist on that server.

A documented function answering 404 PGRST202 has not been applied yet; it was not removed. The reverse also happens, so treat catalog().postgrest as a floor and a 200 as the last word.

Caching

History whose end date is in the past is immutable: cache it aggressively (a day is reasonable). The latest session changes daily after the ingest cron; keep that short.