portfolio_lookthrough

What a set of funds holds, looked through their fund quotas. One CDA month (p_month, or by default the last complete one: the newest month whose block-2 filing count reaches 90% of the median of the 12 before it, the /holdings rule). From each root, CDA block 2 (fund quotas) is followed recursively, cycle-guarded and capped at p_max_depth levels (1..6, default 4); every fund on the way, root included, lists its own holdings from block 1 (government_bond; repo collateral served apart as repo), block 2 (fund_quota when looked through, else fund_quota_unfiled / fund_quota_depth_cap / fund_quota_cycle), block 4 (stock, debenture with issuer_code = ISIN chars 3-6, other_block4) and block 6 (private_credit with the issuer’s CNPJ when it is a PJ and the indexer as filed). weight_in_root = value / holder NAV times the weights down the path, NAV = fact_fund_monthly.vl_patrim_liq of the same month (not the CDA blocks’ total: blocks 3, 5, 7, 8 are not ingested); NULL when a NAV on the path is unknown. A fund reached by two paths appears once per path; sum weight_in_root over every row but fund_quota for the exposure. A root with no CDA that month returns one no_cda_filing row. More than 200 CNPJs or more than 1000 rows RAISES 22023 (never trimmed): send fewer funds per call or lower p_max_depth.

Row cap. A window producing more than 1000 rows raises SQLSTATE 22023 naming this function. Nothing is trimmed to fit — PostgREST would silently cut the response at 1000 and a cut-short series is indistinguishable from one that simply ends. This function does not page — narrow the window instead.

POST /rpc/portfolio_lookthrough

portfolio_lookthrough

cURL

curl --request POST \
  --url https://zcjbtpxuhdekpwcxmepn.supabase.co/rest/v1/rpc/portfolio_lookthrough \
  --header 'Content-Type: application/json' \
  --header 'apikey: <api-key>' \
  --data '
{
  "p_cnpjs": [
    "<string>"
  ],
  "p_month": null,
  "p_max_depth": 4
}
'

200

[
  {
    "root_cnpj": "<string>",
    "path": [
      "<string>"
    ],
    "depth": 123,
    "holder_cnpj": "<string>",
    "block": 123,
    "asset_kind": "<string>",
    "asset_key": "<string>",
    "asset_name": "<string>",
    "isin": "<string>",
    "issuer_cnpj": "<string>",
    "issuer_code": "<string>",
    "tp_aplic": "<string>",
    "tp_ativo": "<string>",
    "tp_titpub": "<string>",
    "indexer_code": "<string>",
    "maturity": "2023-12-25",
    "value_brl": 123,
    "weight_in_root": 123,
    "period": "2023-12-25",
    "is_cycle": true
  }
]

Authorizations

apikey · string · header · required

The publishable key (sb_publishable_…), on every request. It is NOT a JWT — sending it as Authorization: Bearer is rejected as an invalid JWT. It identifies the project, not you, and maps to the Postgres anon role.

Body

application/json

p_cnpjs · (string | null)[] | null · required

p_month · string<date> | null

Defaults to NULL::date.

p_max_depth · integer<int32> | null · default: 4

Defaults to 4.

Response

200

application/json

The result set, oldest first. An unknown id is an empty array, not a 404 and never a guessed value.

root_cnpj · string | null

path · (string | null)[] | null

depth · integer<int32> | null

holder_cnpj · string | null

block · integer<int32> | null

asset_kind · string | null

asset_key · string | null

asset_name · string | null

isin · string | null

issuer_cnpj · string | null

issuer_code · string | null

tp_aplic · string | null

tp_ativo · string | null

tp_titpub · string | null

indexer_code · string | null

maturity · string<date> | null

value_brl · number | null

weight_in_root · number | null

period · string<date> | null

is_cycle · boolean | null