Developer docs

For every MCP tool: which endpoint it hits, what SQL it runs, which tables it touches. Table schemas and relations for writing free-form SQL live here too.

49 MCP tools7 data modules27 tables queryable via read-only SQL

MCP

Once connected to the MCP server, the agent queries through tools; each tool maps to one REST endpoint plus one SQL shape.

REST API

The same endpoints work directly: base URL plus Authorization: Bearer with your API key.

Read-only SQL (Pro)

execute_readonly_sql runs SELECT directly; use describe_table first, and see each table card below for relations.

On this page

Companies & SEC filings Financial statements & valuation Insiders & institutional 13F Prices & options Corporate actions, calendar, transcripts & news Macro, ETF & market snapshots Composite views & free-form SQL

Companies & SEC filings

Tables

companies

Tracked US equity and ADR registry, one row per ticker, with CIK, sector classification and delisting status.

Column	Type	Notes
ticker	varchar(10) PK	Primary key, uppercase US ticker symbol.
cik	varchar(10)	SEC Central Index Key (10-digit zero-padded).
cusip	varchar(9)	9-char CUSIP, often null.
name	text	Company name.
sector	text	Sector classification.
sic_code	varchar(4)	SEC SIC industry code (4 digits).
industry	text	Industry sub-classification.
exchange	text	Listing exchange.
status	varchar(10)	'active' or 'delisted', the authoritative delisting flag (a price gap is not a delisting).
delisted_at	date	Delisting effective date, null while active.

Relations

filings.ticker → companies.ticker (FK, SET NULL)

filings

SEC EDGAR filing index (10-K, 10-Q, 20-F, 8-K, Form 4, 13F, etc.), one row per filing, with form type and filed timestamp.

Column	Type	Notes
accession	varchar(20) PK	Primary key, SEC accession number (e.g. 0000320193-24-000123).
cik	varchar(10)	Filer SEC CIK.
ticker	varchar(10) FK	FK to companies.ticker (SET NULL on company delete).
form_type	varchar(10)	Form type such as 10-K, 10-Q, 8-K, 4, 13F-HR.
filed_at	timestamptz	Filed timestamp (tz-aware), the list sort key.
period_of_report	date	Period-of-report date, often null.
primary_doc_url	text	Primary document SEC URL.
raw_storage_key	text	R2 raw-filing storage key.
status	varchar(12)	Processing status (discovered, parsed, etc.).
parsed_at	timestamptz	Parse completion time, null if unparsed.

Relations

filings.ticker → companies.ticker (FK, SET NULL)
filing_sections.accession → filings.accession (FK, CASCADE)
income_statements.source_accession → filings.accession (FK, SET NULL)

filing_sections

Parsed filing section text, each row is one item of a filing, with item_code, title and full content.

Column	Type	Notes
id	bigint PK	Primary key, auto-increment.
accession	varchar(20) FK	FK to filings.accession (CASCADE delete).
item_code	text	Section item code such as 'Item 1A' (risk factors) or 'Item 7' (MD&A).
title	text	Section title.
content	text	Full section text, returned only by get_filing_section (omitted from the TOC listing).
char_start	integer	Section start char offset in source, the TOC sort key.
char_end	integer	Section end char offset in source.

Relations

filing_sections.accession → filings.accession (FK, CASCADE)

Functions

start_here()

Orientation map for any agent new to the dataset, returning product scope, limits, tool-picking guidance and workflows. Hits no backend and is always free and safe.

REST endpoint:Direct DB access (no REST)

SQL behind it

-- static orientation payload, no SQL

Example response

{
  "product": "investor-db",
  "scope": "US equities + ADRs only. EOD or slower, never real-time. ~3-5 years of history. No analyst ratings or revenue forecasts ...",
  "first_call": "Call get_data_coverage before quoting any exact figure ...",
  "how_to_pick_a_tool": [
    "Don't know the exact ticker -> search_companies",
    "Want a fast four-lens traffic-light conclusion -> get_analysis"
  ],
  "workflows": [
    {
      "name": "quick_answer_in_chat",
      "best_for": "a fast conclusion inside the conversation",
      "steps": ["get_company (or search_companies)", "get_analysis", "get_objective_report"]
    }
  ],
  "prompts": ["analyze_stock", "analyze_stock_full", "compare_stocks", "build_stock_report", "company_profile"],
  "resources": {
    "data://dictionary": "Field-level semantics, units, codes, and lag rules.",
    "data://analysis-playbook": "Step-by-step analysis methodology.",
    "data://report-template": "The HTML report design system used by build_stock_report."
  },
  "honesty_rules": [
    "Every figure needs an as-of date (from get_data_coverage or the row itself).",
    "null means missing, never zero.",
    "Do not invent price targets or forecasts."
  ]
}

get_data_coverage()

Reports per-domain freshness and coverage, the trust anchor before quoting any exact figure. Aggregates newest data point, last successful ingest and row estimates across every domain table, with a 10-minute in-process cache.

REST endpoint:GET /api/meta/coverage

Tables touched:ingest_runscompaniesfilings

SQL behind it

-- last successful ingest per ETL stage
SELECT stage, max(finished_at) FROM ingest_runs WHERE status = 'ok' GROUP BY stage;
-- newest data point per domain (examples)
SELECT max(filed_at) FROM filings;
SELECT max(date) FROM prices_daily;
-- cheap row-count estimate (avoids count(*) on ~33M-row tables)
SELECT relname, reltuples::bigint FROM pg_class WHERE relkind = 'r';

Example response

{
  "as_of": "2026-05-01T12:00:00+00:00",
  "notes": "All data is end-of-day or slower; nothing is real-time. Coverage: US equities + ADRs.",
  "domains": [
    {
      "domain": "prices_daily",
      "description": "Daily OHLCV (Alpha Vantage TIME_SERIES_DAILY_ADJUSTED)",
      "last_data_point": "2026-04-30",
      "last_ingest_ok": "2026-05-01T10:22:35+00:00",
      "coverage": { "tickers": 18542, "rows_estimate": 15230000 },
      "date_range": { "from": "2021-05-03", "to": "2026-04-30" },
      "cadence": "Mon-Sat ~10:00 UTC scan; EOD T+1; 5-year rolling retention",
      "known_gaps": "~5% of symbols (funds/warrants/special classes) not quoted by the vendor"
    }
  ]
}

list_companies(limit?, offset?)

Lists tracked US-equity companies ordered by ticker alphabetically, with limit/offset pagination (the MCP layer defaults to 200 rows).

REST endpoint:GET /api/companies

Tables touched:companies

SQL behind it

SELECT ticker, cik, name, sector, industry, exchange, status, delisted_at, first_seen, last_updated
FROM companies
ORDER BY ticker
LIMIT :limit OFFSET :offset

Example response

[
  {
    "ticker": "AAPL",
    "cik": "0000320193",
    "cusip": "037833100",
    "name": "Apple Inc.",
    "sector": "Technology",
    "sic_code": "3571",
    "industry": "Electronic Computers",
    "exchange": "NASDAQ",
    "reporting_currency": "USD",
    "country": null,
    "is_active": true,
    "status": "active",
    "delisted_at": null,
    "first_seen": "2021-01-04",
    "last_updated": "2026-05-01T06:12:44+00:00"
  }
]

search_companies(q, limit?)

Fuzzy-searches by ticker or company name, ranking exact match first, then ticker prefix, then name prefix, then substring, with shorter tickers first, for typeahead.

REST endpoint:GET /api/companies/search

Tables touched:companies

SQL behind it

SELECT ticker, name, exchange
FROM companies
WHERE ticker ILIKE :prefix OR ticker ILIKE :contains OR name ILIKE :contains
ORDER BY
  CASE WHEN ticker = :exact THEN 0 WHEN ticker ILIKE :prefix THEN 1 WHEN name ILIKE :prefix THEN 2 ELSE 3 END,
  length(ticker), ticker
LIMIT :limit

Example response

[
  { "ticker": "AAPL", "name": "Apple Inc.", "exchange": "NASDAQ" },
  { "ticker": "AAPD", "name": "Direxion Daily AAPL Bear 1X Shares", "exchange": "NASDAQ" }
]

get_company(ticker)

Fetches a single company's full profile by exact ticker, returning 404 if the ticker is unknown.

REST endpoint:GET /api/companies/{ticker}

Tables touched:companies

SQL behind it

SELECT ticker, cik, cusip, name, sector, sic_code, industry, exchange,
       reporting_currency, status, delisted_at, first_seen, last_updated
FROM companies
WHERE ticker = :ticker

Example response

{
  "ticker": "AAPL",
  "cik": "0000320193",
  "cusip": "037833100",
  "name": "Apple Inc.",
  "sector": "Technology",
  "sic_code": "3571",
  "industry": "Electronic Computers",
  "exchange": "NASDAQ",
  "reporting_currency": "USD",
  "country": null,
  "is_active": true,
  "status": "active",
  "delisted_at": null,
  "first_seen": "2021-01-04",
  "last_updated": "2026-05-01T06:12:44+00:00"
}

list_filings(ticker, form_type?, since?, until?, limit?)

Lists a company's SEC filings newest-first by filed_at, filterable by form_type and an inclusive since/until range on filed_at.

REST endpoint:GET /api/filings

Tables touched:filings

SQL behind it

SELECT accession, ticker, cik, form_type, filed_at, period_of_report,
       primary_doc_url, raw_storage_key
FROM filings
WHERE ticker = :ticker
  -- optional: AND form_type = :form_type AND filed_at >= :since AND filed_at <= :until
ORDER BY filed_at DESC
LIMIT :limit

Example response

[
  {
    "accession": "0000320193-26-000042",
    "ticker": "AAPL",
    "cik": "0000320193",
    "form_type": "10-Q",
    "filed_at": "2026-05-01T16:30:05+00:00",
    "period_of_report": "2026-03-28",
    "primary_doc_url": "https://www.sec.gov/Archives/edgar/data/320193/000032019326000042/aapl-20260328.htm",
    "raw_storage_key": "filings/AAPL/0000320193-26-000042/primary.htm"
  }
]

get_filing(accession)

Fetches a single filing's metadata by SEC accession number, returning 404 if not found.

REST endpoint:GET /api/filings/{accession}

Tables touched:filings

SQL behind it

SELECT accession, ticker, cik, form_type, filed_at, period_of_report,
       primary_doc_url, raw_storage_key
FROM filings
WHERE accession = :accession

Example response

{
  "accession": "0000320193-26-000042",
  "ticker": "AAPL",
  "cik": "0000320193",
  "form_type": "10-Q",
  "filed_at": "2026-05-01T16:30:05+00:00",
  "period_of_report": "2026-03-28",
  "primary_doc_url": "https://www.sec.gov/Archives/edgar/data/320193/000032019326000042/aapl-20260328.htm",
  "raw_storage_key": "filings/AAPL/0000320193-26-000042/primary.htm"
}

list_filing_sections(accession)

Lists a filing's parsed section table-of-contents (id, item_code, title, char range) without body text, ordered by char offset.

REST endpoint:GET /api/filings/{accession}/sections

Tables touched:filing_sections

SQL behind it

SELECT id, accession, item_code, title, char_start, char_end
FROM filing_sections
WHERE accession = :accession
ORDER BY char_start NULLS LAST, id

Example response

[
  {
    "id": 918273,
    "accession": "0000320193-26-000042",
    "item_code": "Item 1A",
    "title": "Risk Factors",
    "char_start": 12045,
    "char_end": 48210
  },
  {
    "id": 918274,
    "accession": "0000320193-26-000042",
    "item_code": "Item 2",
    "title": "Management's Discussion and Analysis of Financial Condition and Results of Operations",
    "char_start": 48211,
    "char_end": 96530
  }
]

get_filing_section(accession, item_code)

Fetches a single section's full body text by (accession, item_code), returning 404 if not found.

REST endpoint:GET /api/filings/{accession}/sections/{item_code}

Tables touched:filing_sections

SQL behind it

SELECT id, accession, item_code, title, content, char_start, char_end
FROM filing_sections
WHERE accession = :accession AND item_code = :item_code
LIMIT 1

Example response

{
  "id": 918273,
  "accession": "0000320193-26-000042",
  "item_code": "Item 1A",
  "title": "Risk Factors",
  "content": "The Company's business, reputation, results of operations, financial condition and stock price can be affected by a number of factors, whether currently known or unknown, including those described below ...",
  "char_start": 12045,
  "char_end": 48210
}

Financial statements & valuation

Tables

income_statements

Per-period income statements normalized to USD from SEC XBRL filings. Each row is one statement for a company and fiscal period (annual FY or quarterly Q1 to Q3).

Column	Type	Notes
ticker	varchar(10) PK	US ticker symbol, part of the composite primary key.
period_end	date PK	Fiscal period end date, part of the composite primary key and the sort key.
fiscal_period	varchar(10) PK	Period code: FY for annual, Q1 to Q3 for quarterly.
fiscal_year	integer	Fiscal year, nullable.
revenue	bigint	Total revenue in USD, nullable.
gross_profit	bigint	Gross profit in USD, nullable.
operating_income	bigint	Operating income in USD, nullable.
net_income	bigint	Net income in USD, nullable.
eps_diluted	numeric(12,4)	Diluted earnings per share, nullable.
source_accession	varchar(20) FK	SEC accession of the source filing; the table also has cost_of_revenue, rd_expense, sga_expense, eps_basic, shares_basic, shares_diluted and more.

Relations

source_accession → filings.accession (FK, SET NULL)
ticker → companies.ticker (logical join, no DB FK)

balance_sheets

Per-period balance sheets normalized to USD from SEC XBRL filings. Each row is a snapshot of assets, liabilities and equity at a company fiscal period end.

Column	Type	Notes
ticker	varchar(10) PK	US ticker symbol, part of the composite primary key.
period_end	date PK	Fiscal period end date, part of the composite primary key and the sort key.
fiscal_period	varchar(10) PK	Period code: FY for annual, Q1 to Q3 for quarterly.
cash_and_equivalents	bigint	Cash and equivalents in USD, nullable.
total_current_assets	bigint	Total current assets in USD, nullable.
total_assets	bigint	Total assets in USD, nullable.
total_current_liabilities	bigint	Total current liabilities in USD, nullable.
total_liabilities	bigint	Total liabilities in USD, nullable.
total_equity	bigint	Total equity in USD, nullable.
source_accession	varchar(20) FK	SEC accession of the source filing; the table also has goodwill, intangibles, long_term_debt, inventory and more.

Relations

source_accession → filings.accession (FK, SET NULL)
ticker → companies.ticker (logical join, no DB FK)

cash_flow_statements

Per-period cash flow statements normalized to USD from SEC XBRL filings. Each row covers operating, investing and financing cash flows plus free cash flow.

Column	Type	Notes
ticker	varchar(10) PK	US ticker symbol, part of the composite primary key.
period_end	date PK	Fiscal period end date, part of the composite primary key and the sort key.
fiscal_period	varchar(10) PK	Period code: FY for annual, Q1 to Q3 for quarterly.
operating_cash_flow	bigint	Operating cash flow in USD, nullable.
capex	bigint	Capital expenditure in USD, usually negative, nullable.
free_cash_flow	bigint	Free cash flow in USD, nullable.
dividends_paid	bigint	Dividends paid in USD, nullable.
share_buybacks	bigint	Share buybacks in USD, nullable.
net_change_in_cash	bigint	Net change in cash in USD, nullable.
source_accession	varchar(20) FK	SEC accession of the source filing; the table also has financing_cash_flow, investing_cash_flow and more.

Relations

source_accession → filings.accession (FK, SET NULL)
ticker → companies.ticker (logical join, no DB FK)

company_overview

One valuation snapshot row per ticker, sourced from Alpha Vantage OVERVIEW with market cap self computed daily. Holds the PE family, beta, 52 week range, moving averages and analyst target price.

Column	Type	Notes
ticker	varchar(10) PK	US ticker symbol, primary key.
market_cap	bigint	Market cap in USD, nullable; the query layer self computes it from latest close times shares.
pe_ratio	numeric(14,4)	Price to earnings ratio, nullable.
peg_ratio	numeric(14,4)	PEG ratio, nullable.
price_to_book	numeric(14,4)	Price to book ratio, nullable.
ev_to_ebitda	numeric(14,4)	EV to EBITDA multiple, nullable.
dividend_yield	numeric(10,6)	Dividend yield as a 0 to 1 decimal, not a percentage, nullable.
return_on_equity_ttm	numeric(10,6)	Return on equity TTM as a 0 to 1 decimal, nullable.
analyst_target_price	numeric(14,4)	Analyst target price in USD, nullable.
updated_at	timestamptz	Row refresh time; the table also has forward_pe, price_to_sales_ttm, beta, week_52_high/low, ma_50, ma_200 and dozens more.

Relations

ticker → companies.ticker (logical 1:1, intentionally no DB FK)

Functions

get_income_statements(ticker, period?, limit?)

Return a company income statements newest first by period end. Pass period annual for FY, quarterly for Q1 to Q3, or omit for both; limit defaults to 20 (1 to 200). Amounts are in USD.

REST endpoint:GET /api/financials/income

Tables touched:income_statements

SQL behind it

SELECT ticker, period_end, fiscal_period, fiscal_year,
       revenue, gross_profit, operating_income, net_income, eps_diluted
FROM income_statements
WHERE ticker = :ticker
  -- period='annual'    -> AND fiscal_period = 'FY'
  -- period='quarterly' -> AND fiscal_period IN ('Q1','Q2','Q3')
ORDER BY period_end DESC
LIMIT :limit

Example response

[
  {
    "ticker": "AAPL",
    "period_end": "2026-03-28",
    "fiscal_period": "Q2",
    "fiscal_year": 2026,
    "revenue": 95359000000,
    "cost_of_revenue": 50492000000,
    "gross_profit": 44867000000,
    "operating_expenses": 15274000000,
    "rd_expense": 8550000000,
    "sga_expense": 6724000000,
    "operating_income": 29593000000,
    "interest_expense": null,
    "tax_expense": 4864000000,
    "net_income": 24780000000,
    "eps_basic": "1.66",
    "eps_diluted": "1.65",
    "shares_basic": 14928000000,
    "shares_diluted": 15022000000,
    "reporting_currency": "USD",
    "filed_via_accession": "0000320193-26-000042"
  }
]

get_balance_sheets(ticker, period?, limit?)

Return a company balance sheets newest first by period end. The period filter matches income statements (annual for FY, quarterly for Q1 to Q3); limit defaults to 20 (1 to 200). Amounts are in USD.

REST endpoint:GET /api/financials/balance

Tables touched:balance_sheets

SQL behind it

SELECT ticker, period_end, fiscal_period,
       cash_and_equivalents, total_current_assets, total_assets,
       total_liabilities, total_equity
FROM balance_sheets
WHERE ticker = :ticker
  -- period='annual'    -> AND fiscal_period = 'FY'
  -- period='quarterly' -> AND fiscal_period IN ('Q1','Q2','Q3')
ORDER BY period_end DESC
LIMIT :limit

Example response

[
  {
    "ticker": "AAPL",
    "period_end": "2026-03-28",
    "fiscal_period": "Q2",
    "cash_and_equivalents": 28160000000,
    "short_term_investments": 35230000000,
    "accounts_receivable": 21540000000,
    "inventory": 6100000000,
    "total_current_assets": 133210000000,
    "ppe_net": 45680000000,
    "goodwill": null,
    "intangibles": null,
    "total_assets": 344090000000,
    "accounts_payable": 54180000000,
    "short_term_debt": 14200000000,
    "total_current_liabilities": 145300000000,
    "long_term_debt": 85750000000,
    "total_liabilities": 277320000000,
    "total_equity": 66770000000,
    "reporting_currency": "USD",
    "filed_via_accession": "0000320193-26-000042"
  }
]

get_cash_flow_statements(ticker, period?, limit?)

Return a company cash flow statements newest first by period end. Covers operating cash flow, capex, free cash flow, dividends and buybacks; limit defaults to 20 (1 to 200). Amounts are in USD.

REST endpoint:GET /api/financials/cashflow

Tables touched:cash_flow_statements

SQL behind it

SELECT ticker, period_end, fiscal_period,
       operating_cash_flow, capex, free_cash_flow,
       dividends_paid, share_buybacks, net_change_in_cash
FROM cash_flow_statements
WHERE ticker = :ticker
  -- same period filter as income statements
ORDER BY period_end DESC
LIMIT :limit

Example response

[
  {
    "ticker": "AAPL",
    "period_end": "2026-03-28",
    "fiscal_period": "Q2",
    "operating_cash_flow": 28950000000,
    "capex": -2870000000,
    "free_cash_flow": 26080000000,
    "dividends_paid": -3820000000,
    "share_buybacks": -23500000000,
    "financing_cash_flow": -29400000000,
    "investing_cash_flow": -1200000000,
    "net_change_in_cash": -1650000000,
    "reporting_currency": "USD",
    "filed_via_accession": "0000320193-26-000042"
  }
]

get_latest_period(ticker, period?)

Return the most recent period with all three statements stitched together (income, balance and cash flow at the same period end). The period is chosen from income_statements, then the matching balance and cash flow rows are pulled; a statement absent for that period is null. Returns 404 when no financials exist.

REST endpoint:GET /api/financials/latest

Tables touched:income_statementsbalance_sheetscash_flow_statements

SQL behind it

-- 1) pick the newest (period_end, fiscal_period) from income_statements
SELECT period_end, fiscal_period
FROM income_statements
WHERE ticker = :ticker
  -- same optional period filter (FY or IN ('Q1','Q2','Q3'))
ORDER BY period_end DESC
LIMIT 1;

-- 2) fetch each statement at that fiscal_period, keep rows matching period_end
SELECT * FROM income_statements    WHERE ticker = :ticker AND fiscal_period = :fp ORDER BY period_end DESC LIMIT 1;
SELECT * FROM balance_sheets       WHERE ticker = :ticker AND fiscal_period = :fp ORDER BY period_end DESC LIMIT 1;
SELECT * FROM cash_flow_statements WHERE ticker = :ticker AND fiscal_period = :fp ORDER BY period_end DESC LIMIT 1;

Example response

{
  "ticker": "AAPL",
  "period_end": "2026-03-28",
  "fiscal_period": "Q2",
  "income": {
    "ticker": "AAPL",
    "period_end": "2026-03-28",
    "fiscal_period": "Q2",
    "fiscal_year": 2026,
    "revenue": 95359000000,
    "gross_profit": 44867000000,
    "operating_income": 29593000000,
    "net_income": 24780000000,
    "eps_basic": "1.66",
    "eps_diluted": "1.65",
    "shares_diluted": 15022000000,
    "reporting_currency": "USD",
    "filed_via_accession": "0000320193-26-000042"
  },
  "balance": {
    "ticker": "AAPL",
    "period_end": "2026-03-28",
    "fiscal_period": "Q2",
    "cash_and_equivalents": 28160000000,
    "total_current_assets": 133210000000,
    "total_assets": 344090000000,
    "total_current_liabilities": 145300000000,
    "total_liabilities": 277320000000,
    "total_equity": 66770000000,
    "reporting_currency": "USD",
    "filed_via_accession": "0000320193-26-000042"
  },
  "cash_flow": {
    "ticker": "AAPL",
    "period_end": "2026-03-28",
    "fiscal_period": "Q2",
    "operating_cash_flow": 28950000000,
    "capex": -2870000000,
    "free_cash_flow": 26080000000,
    "dividends_paid": -3820000000,
    "share_buybacks": -23500000000,
    "net_change_in_cash": -1650000000,
    "reporting_currency": "USD",
    "filed_via_accession": "0000320193-26-000042"
  }
}

get_overview(ticker)

Return the raw valuation snapshot for a company: market cap, the PE family, beta, 52 week range, moving averages and analyst target price. Sourced from Alpha Vantage OVERVIEW and refreshed daily; an uncovered ticker returns 404.

REST endpoint:GET /api/overview/{ticker}

Tables touched:company_overview

SQL behind it

SELECT ticker, market_cap, shares_outstanding, ebitda, revenue_ttm,
       pe_ratio, forward_pe, peg_ratio, price_to_book, price_to_sales_ttm,
       ev_to_ebitda, dividend_yield, return_on_equity_ttm,
       beta, week_52_high, week_52_low, ma_50, ma_200,
       analyst_target_price, latest_quarter, updated_at
FROM company_overview
WHERE ticker = :ticker

Example response

{
  "ticker": "AAPL",
  "market_cap": 3125000000000,
  "shares_outstanding": 14840000000,
  "ebitda": 138500000000,
  "revenue_ttm": 420700000000,
  "gross_profit_ttm": 190400000000,
  "pe_ratio": 32.4,
  "forward_pe": 29.1,
  "peg_ratio": 2.61,
  "price_to_book": 46.8,
  "price_to_sales_ttm": 7.43,
  "ev_to_ebitda": 23.1,
  "eps": 6.52,
  "dividend_per_share": 1.04,
  "dividend_yield": 0.0049,
  "profit_margin": 0.243,
  "return_on_equity_ttm": 1.48,
  "beta": 1.19,
  "week_52_high": 237.49,
  "week_52_low": 169.21,
  "ma_50": 211.34,
  "ma_200": 198.77,
  "analyst_target_price": 245.0,
  "latest_quarter": "2026-03-28",
  "updated_at": "2026-07-09T16:00:00Z"
}

Insiders & institutional 13F

Tables

insider_trades

SEC Form 4 insider (officer/director/major-holder) transactions. One Form 4 can report several transactions, so a synthetic id is the primary key. Insiders must file within 2 business days of the trade and the ETL ingests daily.

Column	Type	Notes
id	bigint PK	Synthetic auto-increment primary key.
ticker	varchar(10)	Stock ticker; logical join to companies.ticker (no FK constraint).
accession	varchar(20) FK	Source SEC filing accession number.
insider_name	text	Insider name, nullable.
insider_title	text	Role, e.g. CEO or Director, nullable.
transaction_date	date	Trade date, nullable.
transaction_code	varchar(2)	SEC Form 4 code, P=open-market buy, S=sell.
shares	bigint	Shares in this transaction, nullable.
price_per_share	numeric(18,4)	Price per share in USD, nullable.
shares_owned_after	bigint	Shares owned after the trade; used to derive insider ownership percent.

Relations

accession → filings.accession (FK, CASCADE)
ticker → companies.ticker (logical join, no FK)

institutional_holdings

SEC 13F-HR institutional holdings, keyed by (filer_cik, cusip, quarter_end). About 41M rows, served through a partial index (WHERE ticker IS NOT NULL). Quarterly positions with a roughly 45-day filing lag, refreshed on a daily 1/7 filer rotation.

Column	Type	Notes
filer_cik	varchar(10) PK	Filing institution CIK.
cusip	varchar(9) PK	CUSIP, the only security identifier 13F-HR always carries.
quarter_end	date PK	Quarter-end date of the position.
ticker	varchar(10) FK	Resolved from cusip; null when it cannot be mapped.
shares	bigint	Shares held, nullable.
market_value	bigint	Position market value in USD, nullable.
change_in_shares	bigint	Share change versus prior quarter (may be positive or negative).
change_type	varchar(10)	Change class: new / increase / decrease / sold_out.
source_accession	varchar(20)	Source 13F accession (no cross-table FK).

Relations

filer_cik → institutions.cik (FK)
ticker → companies.ticker (FK, SET NULL)

institutions

13F filer registry mapping CIK to institution name, used for typeahead and to resolve holding CIKs into names.

Column	Type	Notes
cik	varchar(10) PK	Institution CIK.
name	text	Institution name.
first_seen	date	Date first seen, nullable.
last_updated	timestamptz	Row last-updated timestamp.

Relations

institutional_holdings.filer_cik → institutions.cik
institution_filings.filer_cik → institutions.cik

Functions

list_insider_trades(ticker, since?, until?, limit?)

Lists Form 4 insider trades for one company, newest first. Single-ticker view; use screen_insider_buys to screen buys across the market.

REST endpoint:GET /api/insider

Tables touched:insider_trades

SQL behind it

SELECT id, ticker, accession, insider_name, insider_title,
       transaction_date, transaction_code, shares, price_per_share,
       total_value, shares_owned_after, is_direct
FROM insider_trades
WHERE ticker = :ticker
  -- optional: AND transaction_date >= :since AND transaction_date <= :until
ORDER BY transaction_date DESC NULLS LAST, id DESC
LIMIT :limit

Example response

[
  {
    "id": 4821507,
    "ticker": "AAPL",
    "accession": "0000320193-26-000075",
    "insider_name": "COOK TIMOTHY D",
    "insider_title": "Chief Executive Officer",
    "transaction_date": "2026-04-01",
    "transaction_code": "S",
    "shares": 223986,
    "price_per_share": "169.32",
    "total_value": 37925069,
    "shares_owned_after": 3280150,
    "is_direct": true
  }
]

screen_insider_buys(transaction_code?, since_days?, ticker_contains?, insider_title_contains?, market_cap_min?, market_cap_max?, limit?)

Screens insider open-market trades across the whole universe (buys by default), ordered by transaction_date DESC then usd_value DESC. market_cap is computed live via a per-ticker LATERAL index seek into prices_daily and is null when price or share count is missing.

REST endpoint:GET /api/screener/insider-buys

Tables touched:insider_tradescompaniesprices_dailyincome_statements

SQL behind it

WITH base AS (
  SELECT it.transaction_date, it.ticker, c.name AS company_name,
         (pd.close * COALESCE(c.shares_outstanding, isd.shares_diluted))::float8 AS market_cap,
         it.insider_name, it.insider_title, it.shares, it.price_per_share,
         (it.shares * it.price_per_share)::float8 AS usd_value
  FROM insider_trades it
  LEFT JOIN companies c ON c.ticker = it.ticker
  LEFT JOIN LATERAL (
    SELECT p.close FROM prices_daily p
    WHERE p.ticker = it.ticker ORDER BY p.date DESC LIMIT 1
  ) pd ON true
  LEFT JOIN LATERAL (
    SELECT i.shares_diluted FROM income_statements i
    WHERE i.ticker = it.ticker AND i.shares_diluted IS NOT NULL
    ORDER BY i.period_end DESC LIMIT 1
  ) isd ON true
  WHERE it.transaction_code = :transaction_code
    AND it.transaction_date >= CURRENT_DATE - make_interval(days => :since_days)
)
SELECT * FROM base
WHERE (:market_cap_min IS NULL OR market_cap >= :market_cap_min)
  AND (:market_cap_max IS NULL OR market_cap <= :market_cap_max)
ORDER BY transaction_date DESC, usd_value DESC NULLS LAST
LIMIT :limit

Example response

{
  "items": [
    {
      "transaction_date": "2026-06-30",
      "ticker": "OXY",
      "company_name": "Occidental Petroleum Corp",
      "market_cap": 45600000000.0,
      "insider_name": "HOLLUB VICKI A",
      "insider_title": "President and Chief Executive Officer",
      "shares": 10000,
      "price_per_share": "48.50",
      "usd_value": 485000.0
    }
  ],
  "count": 1
}

list_13f_holders(ticker, quarter_end?, limit?)

Lists every 13F institutional holder of a stock, largest market value first. When quarter_end is omitted it uses the newest quarter past the filing deadline (quarter-end plus 45 days), so a just-closed quarter with only a few early filers does not make large caps look near zero institutional ownership.

REST endpoint:GET /api/13f/holders

Tables touched:institutional_holdingsinstitutions

SQL behind it

-- quarter_end omitted -> latest settled quarter for the ticker:
SELECT MAX(quarter_end) FILTER (
         WHERE quarter_end <= CURRENT_DATE - make_interval(days => 45)
       ) AS settled,
       MAX(quarter_end) AS newest
FROM institutional_holdings WHERE ticker = :ticker;  -- use settled, fall back to newest

SELECT ih.filer_cik, i.name AS filer_name, ih.ticker, ih.cusip, ih.quarter_end,
       ih.shares, ih.market_value, ih.change_in_shares, ih.change_type
FROM institutional_holdings ih
JOIN institutions i ON ih.filer_cik = i.cik
WHERE ih.ticker = :ticker AND ih.quarter_end = :quarter_end
ORDER BY ih.market_value DESC NULLS LAST
LIMIT :limit

Example response

[
  {
    "filer_cik": "0000102909",
    "filer_name": "VANGUARD GROUP INC",
    "ticker": "AAPL",
    "cusip": "037833100",
    "quarter_end": "2026-03-31",
    "shares": 1370000000,
    "market_value": 231000000000,
    "change_in_shares": 16800000,
    "change_type": "increase"
  },
  {
    "filer_cik": "0001364742",
    "filer_name": "BLACKROCK INC",
    "ticker": "AAPL",
    "cusip": "037833100",
    "quarter_end": "2026-03-31",
    "shares": 1050000000,
    "market_value": 177000000000,
    "change_in_shares": -4700000,
    "change_type": "decrease"
  }
]

list_13f_portfolio(cik, quarter_end?, limit?)

Lists an institution's entire 13F portfolio by CIK, largest market value first. When quarter_end is omitted it takes that filer's MAX(quarter_end), since one filer reports the whole portfolio at once so any row means the quarter is complete. ticker may be null.

REST endpoint:GET /api/13f/portfolio

Tables touched:institutional_holdingsinstitutions

SQL behind it

-- quarter_end omitted -> MAX(quarter_end) WHERE filer_cik = :cik
SELECT ih.filer_cik, i.name AS filer_name, ih.ticker, ih.cusip, ih.quarter_end,
       ih.shares, ih.market_value, ih.change_in_shares, ih.change_type
FROM institutional_holdings ih
JOIN institutions i ON ih.filer_cik = i.cik
WHERE ih.filer_cik = :cik AND ih.quarter_end = :quarter_end
ORDER BY ih.market_value DESC NULLS LAST
LIMIT :limit

Example response

[
  {
    "filer_cik": "0001067983",
    "filer_name": "BERKSHIRE HATHAWAY INC",
    "ticker": "AAPL",
    "cusip": "037833100",
    "quarter_end": "2026-03-31",
    "shares": 300000000,
    "market_value": 50600000000,
    "change_in_shares": 0,
    "change_type": "no_change"
  },
  {
    "filer_cik": "0001067983",
    "filer_name": "BERKSHIRE HATHAWAY INC",
    "ticker": "BAC",
    "cusip": "060505104",
    "quarter_end": "2026-03-31",
    "shares": 631600000,
    "market_value": 25900000000,
    "change_in_shares": -48700000,
    "change_type": "decrease"
  }
]

list_13f_top_buyers(ticker, quarter_end?, limit?)

Lists the institutions that opened or added the most this quarter, filtering change_type IN ('new','increase') and ordering by COALESCE(change_in_shares, shares) descending. Omitting quarter_end uses the same settled-latest-quarter rule as holders.

REST endpoint:GET /api/13f/top-buyers

Tables touched:institutional_holdingsinstitutions

SQL behind it

SELECT ih.filer_cik, i.name AS filer_name, ih.ticker, ih.cusip, ih.quarter_end,
       ih.shares, ih.market_value, ih.change_in_shares, ih.change_type
FROM institutional_holdings ih
JOIN institutions i ON ih.filer_cik = i.cik
WHERE ih.ticker = :ticker AND ih.quarter_end = :quarter_end
  AND ih.change_type IN ('new', 'increase')
ORDER BY COALESCE(ih.change_in_shares, ih.shares) DESC NULLS LAST
LIMIT :limit

Example response

[
  {
    "filer_cik": "0000895421",
    "filer_name": "MORGAN STANLEY",
    "ticker": "AAPL",
    "cusip": "037833100",
    "quarter_end": "2026-03-31",
    "shares": 12500000,
    "market_value": 2110000000,
    "change_in_shares": 12500000,
    "change_type": "new"
  }
]

list_13f_top_sellers(ticker, quarter_end?, limit?)

Lists the institutions that cut or exited the most this quarter, filtering change_type IN ('decrease','sold_out') and ordering by change_in_shares ascending so the most negative (biggest sellers) come first. Omitting quarter_end uses the settled-latest-quarter rule.

REST endpoint:GET /api/13f/top-sellers

Tables touched:institutional_holdingsinstitutions

SQL behind it

SELECT ih.filer_cik, i.name AS filer_name, ih.ticker, ih.cusip, ih.quarter_end,
       ih.shares, ih.market_value, ih.change_in_shares, ih.change_type
FROM institutional_holdings ih
JOIN institutions i ON ih.filer_cik = i.cik
WHERE ih.ticker = :ticker AND ih.quarter_end = :quarter_end
  AND ih.change_type IN ('decrease', 'sold_out')
ORDER BY ih.change_in_shares ASC NULLS LAST
LIMIT :limit

Example response

[
  {
    "filer_cik": "0000093751",
    "filer_name": "STATE STREET CORP",
    "ticker": "AAPL",
    "cusip": "037833100",
    "quarter_end": "2026-03-31",
    "shares": 583000000,
    "market_value": 98300000000,
    "change_in_shares": -9200000,
    "change_type": "decrease"
  }
]

list_institutional_holders(ticker)

Returns a top-holders summary view for a stock's latest quarter, ordered by shares held. Each row carries pct_held = shares / shares_out and pct_change versus the prior quarter. Use list_13f_holders for the full list and quarter-over-quarter changes.

REST endpoint:GET /api/holdings/institutions

Tables touched:institutional_holdingsinstitutionscompaniesincome_statements

SQL behind it

SELECT i.name AS holder, ih.shares, ih.market_value AS value,
       ih.change_in_shares, ih.change_type, ih.quarter_end
FROM institutional_holdings ih
JOIN institutions i ON ih.filer_cik = i.cik
WHERE ih.ticker = :ticker AND ih.quarter_end = :quarter_end  -- latest settled quarter
ORDER BY ih.shares DESC NULLS LAST
LIMIT :limit
-- pct_held = shares / COALESCE(companies.shares_outstanding,
--                              latest income_statements.shares_diluted)

Example response

[
  {
    "holder": "VANGUARD GROUP INC",
    "date_reported": "2026-03-31",
    "shares": 1370000000,
    "value": 231000000000,
    "pct_held": 0.0902,
    "pct_change": 0.0123
  },
  {
    "holder": "BLACKROCK INC",
    "date_reported": "2026-03-31",
    "shares": 1050000000,
    "value": 177000000000,
    "pct_held": 0.0691,
    "pct_change": -0.0045
  }
]

get_holders_breakdown(ticker)

Returns an insider / institution / float ownership breakdown (0 to 1 decimals), all self-computed from first-party data. Institutions = LEAST(1, latest settled-quarter 13F total shares / shares_out); insiders = sum of each insider's latest Form 4 shares_owned_after / shares_out; float = GREATEST(0, 1 - institutions - insiders). All three are null when shares_out is missing.

REST endpoint:GET /api/holdings/major

Tables touched:institutional_holdingsinsider_tradescompaniesincome_statements

SQL behind it

-- shares_out = COALESCE(companies.shares_outstanding,
--                        latest income_statements.shares_diluted)

-- institutions (newest settled quarter):
SELECT COALESCE(SUM(shares), 0) AS inst_shares, COUNT(*) AS inst_count
FROM institutional_holdings
WHERE ticker = :ticker AND quarter_end = :quarter_end;

-- insiders (each insider's latest Form 4 shares_owned_after):
SELECT COALESCE(SUM(shares_owned_after), 0) AS insider_shares
FROM (
  SELECT DISTINCT ON (insider_name) shares_owned_after
  FROM insider_trades
  WHERE ticker = :ticker AND insider_name IS NOT NULL
  ORDER BY insider_name, transaction_date DESC
) s;

-- institutions_pct = LEAST(1, inst_shares / shares_out)
-- insiders_pct     = insider_shares / shares_out
-- float_pct        = GREATEST(0, 1 - institutions_pct - insiders_pct)

Example response

{
  "insiders_pct": 0.0007,
  "institutions_pct": 0.6123,
  "institutions_float_pct": 0.3870,
  "institutions_count": 5218,
  "source": "investor-db (SEC 13F + Form 4)"
}

search_institutions(q, limit?)

Fuzzy typeahead search for 13F filers by name or CIK keyword. Ranking: exact CIK first, then name prefix, then other substring hits, ties by name. Each row carries latest_quarter.

REST endpoint:GET /api/13f/institutions/search

Tables touched:institutionsinstitutional_holdings

SQL behind it

SELECT i.cik, i.name, i.first_seen, lq.latest_quarter
FROM institutions i
LEFT JOIN (
  SELECT filer_cik, MAX(quarter_end) AS latest_quarter
  FROM institutional_holdings GROUP BY filer_cik
) lq ON lq.filer_cik = i.cik
WHERE i.cik = :exact_cik OR i.name ILIKE :contains
ORDER BY CASE
           WHEN i.cik = :exact_cik THEN 0
           WHEN i.name ILIKE :prefix THEN 1
           ELSE 2
         END, i.name
LIMIT :limit

Example response

[
  {
    "cik": "0001067983",
    "name": "BERKSHIRE HATHAWAY INC",
    "first_seen": "2013-05-15",
    "latest_quarter": "2026-03-31"
  },
  {
    "cik": "0001610520",
    "name": "BERKSHIRE ASSET MANAGEMENT LLC PA",
    "first_seen": "2014-02-14",
    "latest_quarter": "2026-03-31"
  }
]

get_institution(cik)

Fetches a single 13F filer by exact CIK (name, first_seen, latest holdings quarter); returns 404 when unknown. latest_quarter comes from MAX(quarter_end) in institutional_holdings.

REST endpoint:GET /api/13f/institutions/{cik}

Tables touched:institutionsinstitutional_holdings

SQL behind it

SELECT i.cik, i.name, i.first_seen, lq.latest_quarter
FROM institutions i
LEFT JOIN (
  SELECT filer_cik, MAX(quarter_end) AS latest_quarter
  FROM institutional_holdings GROUP BY filer_cik
) lq ON lq.filer_cik = i.cik
WHERE i.cik = :cik

Example response

{
  "cik": "0001067983",
  "name": "BERKSHIRE HATHAWAY INC",
  "first_seen": "2013-05-15",
  "latest_quarter": "2026-03-31"
}

Prices & options

Tables

prices_daily

First-party daily OHLCV bars from Alpha Vantage TIME_SERIES_DAILY_ADJUSTED, about 5 years of rolling retention. Primary key is (ticker, date); the table carries an adj_close column but /api/prices/daily returns OHLCV only.

Column	Type	Notes
ticker	varchar(10) PK	Ticker symbol, part of the PK.
date	date PK	Trading day, part of the PK.
open	numeric(14,4)	Open price in USD.
high	numeric(14,4)	Session high.
low	numeric(14,4)	Session low.
close	numeric(14,4)	Close price.
volume	bigint	Share volume.
adj_close	numeric(14,4)	Split and dividend adjusted close; not returned by the daily endpoint.

Relations

ticker → companies.ticker (soft join, no DB FK)

prices_hourly

Hourly intraday OHLCV bars (dt is timestamptz, day boundary in US Eastern), about 60 days of rolling retention. Primary key is (ticker, dt) and it carries an adj_close column that the hourly endpoint returns.

Column	Type	Notes
ticker	varchar(10) PK	Ticker symbol, part of the PK.
dt	timestamptz PK	Tz-aware hourly timestamp, part of the PK.
open	numeric(18,4)	Bar open price.
high	numeric(18,4)	Bar high.
low	numeric(18,4)	Bar low.
close	numeric(18,4)	Bar close.
volume	bigint	Bar share volume.
adj_close	numeric(18,4)	Adjusted close; returned by the hourly endpoint.

Relations

ticker → companies.ticker (soft join, no DB FK)

options_eod

Option EOD snapshots from Alpha Vantage HISTORICAL_OPTIONS, one row per OCC contract per trading day with quotes plus IV and greeks. Primary key is (contract_id, date); it also has last, mark, bid, ask, bid_size, ask_size, volume, open_interest and rho columns.

Column	Type	Notes
contract_id	varchar(24) PK	OCC contract id, part of the PK.
underlying	varchar(10)	Underlying symbol (AV symbol, not guaranteed to match companies.ticker).
expiration	date	Contract expiration date.
strike	numeric(14,4)	Strike price.
option_type	varchar(4)	'call' or 'put'.
implied_volatility	numeric(12,6)	Implied volatility, may be null on illiquid contracts.
delta	numeric(12,6)	Greek delta, can be negative.
gamma	numeric(12,6)	Greek gamma.
theta	numeric(12,6)	Greek theta, usually negative.
vega	numeric(12,6)	Greek vega.

Relations

underlying → companies.ticker (soft join, AV symbol may differ)

Functions

list_daily_prices(ticker, start?, end?, limit?)

Returns first-party daily OHLC plus volume in USD, ascending by date. About 5 years of rolling history, without adj_close, so adjust across dividends or splits yourself.

REST endpoint:GET /api/prices/daily

Tables touched:prices_daily

SQL behind it

SELECT ticker, date, open, high, low, close, volume
FROM prices_daily
WHERE ticker = :ticker
  AND (CAST(:start AS date) IS NULL OR date >= :start)
  AND (CAST(:end AS date) IS NULL OR date <= :end)
ORDER BY date
LIMIT :limit

Example response

[
  {
    "ticker": "AAPL",
    "date": "2026-07-08",
    "open": "210.5000",
    "high": "212.3400",
    "low": "209.8700",
    "close": "211.6200",
    "volume": 48213500
  },
  {
    "ticker": "AAPL",
    "date": "2026-07-09",
    "open": "211.8000",
    "high": "214.1500",
    "low": "211.2000",
    "close": "213.7700",
    "volume": 52901200
  }
]

get_latest_price(ticker)

Returns the most recent trading day daily bar (OHLC plus volume in USD), the first row of prices_daily ordered by date descending. Returns 404 when no price exists.

REST endpoint:GET /api/prices/daily/latest

Tables touched:prices_daily

SQL behind it

SELECT ticker, date, open, high, low, close, volume
FROM prices_daily
WHERE ticker = :ticker
ORDER BY date DESC
LIMIT 1

Example response

{
  "ticker": "AAPL",
  "date": "2026-07-09",
  "open": "211.8000",
  "high": "214.1500",
  "low": "211.2000",
  "close": "213.7700",
  "volume": 52901200
}

list_hourly_prices(ticker, start?, end?, limit?)

Returns raw hourly OHLC plus volume bars (dt is timestamptz, ascending by dt), with start and end given as UTC ISO datetimes. Finer than daily and suited to intraday analysis, with only about 60 days of rolling retention.

REST endpoint:GET /api/prices/hourly

Tables touched:prices_hourly

SQL behind it

SELECT ticker, dt, open, high, low, close, volume, adj_close
FROM prices_hourly
WHERE ticker = :ticker
  AND (CAST(:start AS timestamptz) IS NULL OR dt >= :start)
  AND (CAST(:end AS timestamptz) IS NULL OR dt <= :end)
ORDER BY dt
LIMIT :limit

Example response

[
  {
    "ticker": "AAPL",
    "dt": "2026-07-09T14:30:00Z",
    "open": "211.8000",
    "high": "212.9500",
    "low": "211.4000",
    "close": "212.7300",
    "volume": 8123400,
    "adj_close": "212.7300"
  }
]

get_options_chain(ticker, as_of?, expiration?, option_type?, limit?)

Returns the option chain for one underlying on one trading day (EOD quotes plus IV and greeks), ordered by (expiration, strike, option_type). Only contracts expiring on or after today are returned, and as_of defaults to the latest trading day. Numeric fields can be null and should not be treated as 0.

REST endpoint:GET /api/options/chain

Tables touched:options_eod

SQL behind it

-- as_of omitted -> SELECT MAX(date) FROM options_eod WHERE underlying = :underlying
SELECT contract_id, date, underlying, expiration, strike, option_type,
       last, mark, bid, bid_size, ask, ask_size, volume, open_interest,
       implied_volatility, delta, gamma, theta, vega, rho
FROM options_eod
WHERE underlying = :underlying
  AND date = :as_of
  AND expiration >= CURRENT_DATE
  AND (CAST(:expiration AS date) IS NULL OR expiration = :expiration)
  AND (CAST(:option_type AS text) IS NULL OR option_type = :option_type)
ORDER BY expiration, strike, option_type
LIMIT :limit

Example response

[
  {
    "contract_id": "TSLA260717C00300000",
    "date": "2026-07-09",
    "underlying": "TSLA",
    "expiration": "2026-07-17",
    "strike": "300.0000",
    "option_type": "call",
    "last": "18.4500",
    "mark": "18.6000",
    "bid": "18.4000",
    "bid_size": 12,
    "ask": "18.8000",
    "ask_size": 8,
    "volume": 4521,
    "open_interest": 15230,
    "implied_volatility": "0.482100",
    "delta": "0.548300",
    "gamma": "0.011200",
    "theta": "-0.412600",
    "vega": "0.198400",
    "rho": "0.072300"
  },
  {
    "contract_id": "TSLA260717P00300000",
    "date": "2026-07-09",
    "underlying": "TSLA",
    "expiration": "2026-07-17",
    "strike": "300.0000",
    "option_type": "put",
    "last": null,
    "mark": "16.3500",
    "bid": "16.1000",
    "bid_size": 5,
    "ask": "16.6000",
    "ask_size": 3,
    "volume": 2870,
    "open_interest": 9840,
    "implied_volatility": "0.476500",
    "delta": "-0.451700",
    "gamma": "0.011000",
    "theta": "-0.398200",
    "vega": "0.196100",
    "rho": "-0.065900"
  }
]

get_option_expirations(ticker, as_of?)

Lists the available expiration dates and the contract count per expiration for one underlying on one trading day, ascending by expiration. Only future expirations are listed for picking an expiration, and as_of defaults to the latest trading day.

REST endpoint:GET /api/options/expirations

Tables touched:options_eod

SQL behind it

SELECT expiration, COUNT(*) AS contract_count
FROM options_eod
WHERE underlying = :underlying
  AND date = :as_of
  AND expiration >= CURRENT_DATE
GROUP BY expiration
ORDER BY expiration

Example response

[
  { "expiration": "2026-07-17", "contract_count": 184 },
  { "expiration": "2026-07-24", "contract_count": 162 }
]

get_option_contract_history(contract_id, start?, end?, limit?)

Returns the per-day EOD time series for a single OCC contract (quotes, IV and greeks over time), ascending by date. The contract_id is case sensitive and used verbatim in OCC form (e.g. AAPL260605C00200000).

REST endpoint:GET /api/options/contract/{contract_id}

Tables touched:options_eod

SQL behind it

SELECT contract_id, date, underlying, expiration, strike, option_type,
       last, mark, bid, bid_size, ask, ask_size, volume, open_interest,
       implied_volatility, delta, gamma, theta, vega, rho
FROM options_eod
WHERE contract_id = :contract_id
  AND (CAST(:start AS date) IS NULL OR date >= :start)
  AND (CAST(:end AS date) IS NULL OR date <= :end)
ORDER BY date
LIMIT :limit

Example response

[
  {
    "contract_id": "TSLA260717C00300000",
    "date": "2026-07-09",
    "underlying": "TSLA",
    "expiration": "2026-07-17",
    "strike": "300.0000",
    "option_type": "call",
    "last": "18.4500",
    "mark": "18.6000",
    "bid": "18.4000",
    "bid_size": 12,
    "ask": "18.8000",
    "ask_size": 8,
    "volume": 4521,
    "open_interest": 15230,
    "implied_volatility": "0.482100",
    "delta": "0.548300",
    "gamma": "0.011200",
    "theta": "-0.412600",
    "vega": "0.198400",
    "rho": "0.072300"
  }
]

Corporate actions, calendar, transcripts & news

Tables

dividends

Full cash dividend history from Alpha Vantage, one row per payout anchored on the ex-dividend date, refreshed weekly on Sunday.

Column	Type	Notes
ticker	varchar(10) PK	Ticker symbol, FK to companies.
ex_dividend_date	date PK	Ex-dividend date, the event anchor.
declaration_date	date	Declaration date, often missing, nullable.
record_date	date	Record date, often missing, nullable.
payment_date	date	Payment date, often missing, nullable.
amount	numeric(18,6)	Per-share dividend amount, usually USD, nullable.

Relations

ticker → companies.ticker (FK)

splits

Full stock split history from Alpha Vantage, one row per split, used to reconstruct historical prices, refreshed weekly on Sunday.

Column	Type	Notes
ticker	varchar(10) PK	Ticker symbol, FK to companies.
effective_date	date PK	Split effective date.
split_factor	numeric(18,8)	New shares over old, 2:1 = 2.0, reverse 1:10 = 0.1, nullable.

Relations

ticker → companies.ticker (FK)

earnings_calendar

Alpha Vantage earnings calendar, one row per upcoming report date per ticker (up to 12 months out), refreshed daily at 03:00 UTC.

Column	Type	Notes
ticker	varchar(10) PK	Ticker symbol, FK to companies.
report_date	date PK	Scheduled report date, a vendor estimate.
fiscal_date_ending	date PK	Fiscal period end.
estimate_eps	numeric(12,4)	Consensus EPS estimate, often missing for smaller caps, nullable.
currency	varchar(3)	ISO 4217 currency, often missing, nullable.
report_time	varchar(12)	pre-market, post-market, or null.

Relations

ticker → companies.ticker (FK)

earnings_call_transcripts

One row per earnings call from Alpha Vantage, segments=0 marks a checked-empty tombstone that public endpoints hide (only segments>0 is listed), available around T+1.

Column	Type	Notes
ticker	varchar(10) PK	Ticker symbol, no FK.
quarter	char(6) PK	AV calendar quarter such as 2025Q4.
segments	integer	Segment count, 0 means a checked-empty tombstone.
raw_storage_key	text	R2 raw JSON key, null for tombstones.
fetched_at	timestamptz	Fetch timestamp.

Relations

earnings_call_segments.(ticker, quarter) → earnings_call_transcripts.(ticker, quarter)

earnings_call_segments

Per-segment transcript text, one row per segment (0-based seq) with speaker and AV per-segment sentiment, composite FK to earnings_call_transcripts.

Column	Type	Notes
id	bigint PK	Surrogate primary key.
ticker	varchar(10) FK	Part of the composite FK.
quarter	char(6) FK	Part of the composite FK.
seq	integer	Segment order within the call, 0-based.
speaker	text	Speaker name, nullable.
speaker_title	text	Speaker title, nullable.
content	text	Segment text.
sentiment	numeric(4,2)	AV per-segment sentiment score (vendor metric, not computed by this platform), nullable.

Relations

(ticker, quarter) → earnings_call_transcripts.(ticker, quarter) (FK)

news_articles

Aggregated news from Alpha Vantage NEWS_SENTIMENT (vendor sourced, not first-party SEC), one row per article with url as dedupe key, retained for 12 months.

Column	Type	Notes
id	bigint PK	Surrogate primary key.
url	text UNIQUE	Article URL, the dedupe key.
title	text	Headline, nullable.
summary	text	AV summary, no full text, nullable.
source	text	Source name, nullable.
published_at	timestamptz	Publish timestamp.
overall_sentiment	numeric(6,4)	AV overall sentiment score (vendor).
overall_label	varchar(20)	AV sentiment label such as Bullish.
topics	jsonb	Topic tag array [{topic, relevance}].

Relations

news_ticker_sentiment.article_id → news_articles.id (FK, CASCADE)

news_ticker_sentiment

Article to ticker relevance and sentiment, one row per (article, ticker), ingested only when relevance >= 0.1, with denormalized published_at for fast lookups.

Column	Type	Notes
article_id	bigint PK FK	FK to news_articles, CASCADE delete.
ticker	varchar(10) PK	Ticker symbol.
published_at	timestamptz	Denormalized from the article for per-ticker ordered queries.
relevance	numeric(6,4)	Relevance to this ticker 0 to 1, ingested only if >= 0.1, nullable.
sentiment	numeric(6,4)	Sentiment toward this ticker (vendor), nullable.
label	varchar(20)	Sentiment label for this ticker, nullable.

Relations

article_id → news_articles.id (FK, CASCADE)

Functions

list_dividends(ticker, limit?, offset?)

Lists a ticker's cash dividend history newest first with paging, for gap analysis and manual adjusted-price reconstruction.

REST endpoint:GET /api/dividends/{ticker}

Tables touched:dividends

SQL behind it

SELECT ticker, ex_dividend_date, declaration_date, record_date,
       payment_date, amount
FROM dividends
WHERE ticker = :ticker
ORDER BY ex_dividend_date DESC
LIMIT :limit OFFSET :offset

Example response

[
  {
    "ticker": "AAPL",
    "ex_dividend_date": "2025-05-12",
    "declaration_date": "2025-05-01",
    "record_date": "2025-05-12",
    "payment_date": "2025-05-15",
    "amount": "0.26"
  },
  {
    "ticker": "AAPL",
    "ex_dividend_date": "2025-02-10",
    "declaration_date": null,
    "record_date": null,
    "payment_date": null,
    "amount": "0.25"
  }
]

list_splits(ticker, limit?, offset?)

Lists a ticker's stock split history newest first with paging, used to reconstruct historical price series across split points.

REST endpoint:GET /api/splits/{ticker}

Tables touched:splits

SQL behind it

SELECT ticker, effective_date, split_factor
FROM splits
WHERE ticker = :ticker
ORDER BY effective_date DESC
LIMIT :limit OFFSET :offset

Example response

[
  { "ticker": "NVDA", "effective_date": "2024-06-10", "split_factor": "10.0" },
  { "ticker": "NVDA", "effective_date": "2021-07-20", "split_factor": "4.0" }
]

list_earnings(ticker, start?, end?, limit?)

Lists a single company's upcoming report dates oldest first with optional start and end filters, to plan around earnings gaps.

REST endpoint:GET /api/earnings/{ticker}

Tables touched:earnings_calendar

SQL behind it

SELECT ticker, report_date, fiscal_date_ending, estimate_eps,
       currency, report_time
FROM earnings_calendar
WHERE ticker = :ticker
  AND (CAST(:start AS date) IS NULL OR report_date >= :start)
  AND (CAST(:end AS date) IS NULL OR report_date <= :end)
ORDER BY report_date, fiscal_date_ending
LIMIT :limit

Example response

[
  {
    "ticker": "AAPL",
    "report_date": "2025-07-31",
    "fiscal_date_ending": "2025-06-30",
    "estimate_eps": "1.42",
    "currency": "USD",
    "report_time": "post-market"
  }
]

get_earnings_calendar(start?, end?, limit?)

Scans the whole market for companies reporting within a date range across tickers, ordered by report date, to answer who reports next.

REST endpoint:GET /api/earnings/calendar

Tables touched:earnings_calendar

SQL behind it

SELECT ticker, report_date, fiscal_date_ending, estimate_eps,
       currency, report_time
FROM earnings_calendar
WHERE (CAST(:start AS date) IS NULL OR report_date >= :start)
  AND (CAST(:end AS date) IS NULL OR report_date <= :end)
ORDER BY report_date, ticker, fiscal_date_ending
LIMIT :limit

Example response

[
  {
    "ticker": "AAPL",
    "report_date": "2025-07-31",
    "fiscal_date_ending": "2025-06-30",
    "estimate_eps": "1.42",
    "currency": "USD",
    "report_time": "post-market"
  },
  {
    "ticker": "MSFT",
    "report_date": "2025-07-31",
    "fiscal_date_ending": "2025-06-30",
    "estimate_eps": "3.35",
    "currency": "USD",
    "report_time": "post-market"
  }
]

get_earnings_transcript(ticker, quarter?, offset?, limit?, speaker?)

Returns paginated segments and meta for a company's earnings call in a given quarter, defaulting to the latest quarter when omitted and optionally filtered by a speaker substring, with vendor AV sentiment scores.

REST endpoint:GET /api/companies/{ticker}/transcripts/{quarter}

Tables touched:earnings_call_transcriptsearnings_call_segments

SQL behind it

-- 1) available quarters (latest first; segments > 0 excludes tombstones)
SELECT quarter, segments, fetched_at
FROM earnings_call_transcripts
WHERE ticker = :ticker AND segments > 0
ORDER BY quarter DESC;

-- 2) paged segments for that quarter (optional speaker ILIKE filter)
SELECT seq, speaker, speaker_title, content, sentiment
FROM earnings_call_segments
WHERE ticker = :ticker AND quarter = :quarter
  AND (CAST(:speaker AS text) IS NULL
       OR speaker ILIKE '%' || :speaker || '%')
ORDER BY seq ASC
LIMIT :limit OFFSET :offset

Example response

{
  "ticker": "AAPL",
  "quarter": "2025Q2",
  "segments_total": 72,
  "fetched_at": "2025-05-02T14:30:00+00:00",
  "segments": [
    {
      "seq": 0,
      "speaker": "Operator",
      "speaker_title": null,
      "content": "Good day, and welcome to the Apple Q2 Fiscal Year 2025 Earnings Conference Call...",
      "sentiment": "0.1"
    },
    {
      "seq": 1,
      "speaker": "Tim Cook",
      "speaker_title": "Chief Executive Officer",
      "content": "Thank you. Good afternoon, everyone. Today Apple is reporting revenue of 95.4 billion dollars for the March quarter, up 5 percent from a year ago...",
      "sentiment": "0.35"
    }
  ],
  "available_quarters": ["2025Q2", "2025Q1", "2024Q4", "2024Q3"]
}

get_company_news(ticker, days?, min_relevance?, limit?)

Returns a company's news and sentiment over the last N days above a relevance threshold, newest first, from vendor-aggregated Alpha Vantage (not first-party) refreshed every 4 hours.

REST endpoint:GET /api/companies/{ticker}/news

Tables touched:news_ticker_sentimentnews_articles

SQL behind it

SELECT a.title, a.url, a.source, a.source_domain, ts.published_at,
       a.summary, a.overall_sentiment, a.overall_label,
       ts.relevance, ts.sentiment AS ticker_sentiment, ts.label AS ticker_label
FROM news_ticker_sentiment ts
JOIN news_articles a ON a.id = ts.article_id
WHERE ts.ticker = :ticker
  AND ts.published_at >= (CURRENT_DATE - make_interval(days => :days))
  AND ts.relevance >= :min_relevance
ORDER BY ts.published_at DESC
LIMIT :limit

Example response

[
  {
    "title": "Apple Unveils On-Device AI Features at Developer Conference",
    "url": "https://www.reuters.com/technology/apple-ai-features-2025",
    "source": "Reuters",
    "source_domain": "reuters.com",
    "published_at": "2025-06-10T13:45:00Z",
    "summary": "Apple announced a suite of on-device AI capabilities aimed at boosting iPhone sales heading into the second half of the year.",
    "overall_sentiment": "0.284",
    "overall_label": "Somewhat-Bullish",
    "relevance": "0.812",
    "ticker_sentiment": "0.301",
    "ticker_label": "Somewhat-Bullish"
  }
]

get_market_news(topic?, limit?)

Returns the latest market-wide news and sentiment not tied to a single ticker, optionally filtered by AV topic, from vendor-aggregated Alpha Vantage refreshed every 4 hours.

REST endpoint:GET /api/market/news

Tables touched:news_articles

SQL behind it

SELECT title, url, source, source_domain, published_at, summary,
       overall_sentiment, overall_label, topics
FROM news_articles a
WHERE (
  CAST(:topic AS text) IS NULL
  OR EXISTS (
    SELECT 1
    FROM jsonb_array_elements(a.topics) elem
    WHERE elem ->> 'topic' = :topic
  )
)
ORDER BY published_at DESC
LIMIT :limit

Example response

[
  {
    "title": "Wall Street Rallies as Inflation Data Cools",
    "url": "https://www.bloomberg.com/news/markets-rally-inflation-2025",
    "source": "Bloomberg",
    "source_domain": "bloomberg.com",
    "published_at": "2025-06-11T21:05:00Z",
    "summary": "Major indices closed higher after the latest CPI print came in below economist expectations.",
    "overall_sentiment": "0.216",
    "overall_label": "Somewhat-Bullish",
    "topics": [
      { "topic": "Financial Markets", "relevance": "0.99" },
      { "topic": "Economy - Monetary", "relevance": "0.51" }
    ]
  }
]

Macro, ETF & market snapshots

Tables

macro_series_meta

Self-describing catalog of macro, commodity, and index series. One row per series_id holding its name, unit, frequency, and category. Pick the series_id and read its unit here before fetching observations.

Column	Type	Notes
series_id	varchar(48) PK	Stable series code (e.g. CPI, WTI, INDEX_VIX), case-sensitive.
name	text	Human-readable name.
unit	text	Observation unit (percent, USD, index, and so on), nullable.
frequency	varchar(16)	Frequency (monthly, quarterly, daily, annual, and so on).
category	varchar(16)	Category: macro, commodity, or index.
updated_at	timestamptz	Last refresh time.

Relations

macro_series.series_id → macro_series_meta.series_id (logical link, no DB FK)

macro_series

Long table of macro, commodity, and index observations. One row is a single series value on a given date. The value carries no unit, so look it up in macro_series_meta.

Column	Type	Notes
series_id	varchar(48) PK	References macro_series_meta.series_id.
date	date PK	Observation date.
value	numeric(20,6)	Observation value, unit lives in macro_series_meta.unit, nullable.

Relations

series_id → macro_series_meta.series_id (logical link, no DB FK)

etf_profile

ETF-level metadata, one row per ETF holding net assets, expense ratio, dividend yield, inception date, and leverage flag. Fixed universe of about 25 large ETFs, refreshed monthly.

Column	Type	Notes
ticker	varchar(10) PK	ETF ticker.
net_assets	bigint	Net assets in USD, nullable.
net_expense_ratio	numeric(10,6)	Net expense ratio, 0 to 1 fraction, nullable.
portfolio_turnover	numeric(10,6)	Portfolio turnover, 0 to 1 fraction, nullable.
dividend_yield	numeric(10,6)	Dividend yield, 0 to 1 fraction, nullable.
inception_date	date	Inception date, nullable.
leveraged	boolean	Whether leveraged, nullable.
updated_at	timestamptz	Last refresh time (monthly).

Relations

etf_holdings.etf_ticker → etf_profile.ticker (FK)
etf_sector_weights.etf_ticker → etf_profile.ticker (FK)

etf_holdings

ETF constituent weights, one row per ETF and holding pair. weight is a 0 to 1 fraction. holding_symbol is indexed so you can reverse-lookup which ETFs hold a given symbol.

Column	Type	Notes
etf_ticker	varchar(10) PK FK	FK to etf_profile.ticker.
holding_symbol	varchar(20) PK	Holding symbol, no FK (may be cash, foreign, or uncovered).
description	text	Holding name, often missing, nullable.
weight	numeric(12,8)	Weight in the ETF, 0 to 1 fraction, nullable.

Relations

etf_ticker → etf_profile.ticker (FK)

etf_sector_weights

GICS sector exposure per ETF, one row per ETF and sector pair. weight is a 0 to 1 fraction, about 11 sectors per ETF.

Column	Type	Notes
etf_ticker	varchar(10) PK FK	FK to etf_profile.ticker.
sector	varchar(64) PK	GICS sector name.
weight	numeric(12,8)	Weight in the ETF, 0 to 1 fraction, nullable.

Relations

etf_ticker → etf_profile.ticker (FK)

market_movers

Daily post-close market leaderboards, one row per rank within a board on a trading day. category is gainer, loser, or most_active, top 20 each. ticker has no FK and often includes symbols outside the universe.

Column	Type	Notes
date	date PK	The board's trading day.
category	varchar(12) PK	Board type: gainer, loser, or most_active.
rank	integer PK	Rank 1 to 20.
ticker	varchar(10)	Symbol, no FK, often outside the universe.
price	numeric(14,4)	Price for the day in USD, nullable.
change_amount	numeric(14,4)	Price change versus the prior day in USD, nullable.
change_pct	numeric(10,4)	Percent change as a number (12.34 means 12.34%), nullable.
volume	bigint	Volume for the day, nullable.

ipo_calendar

Calendar of recent and upcoming IPOs, one row per listing. A zero price range is converted to null before load (not yet priced). symbol has no FK since pre-listing tickers are outside the equity universe.

Column	Type	Notes
symbol	varchar(10) PK	Symbol, no FK.
ipo_date	date PK	Expected listing date (vendor estimate, may change).
name	text	Company name, nullable.
price_range_low	numeric(14,4)	Price range low in USD, null means not yet priced.
price_range_high	numeric(14,4)	Price range high in USD, null means not yet priced.
exchange	text	Listing exchange, nullable.

Functions

list_macro_series(category?)

List the catalog of queryable macro, commodity, and index series, ordered by category then series_id. Pick a series_id and read its unit here, then pass it to get_macro_series for the time series.

REST endpoint:GET /api/macro/series

Tables touched:macro_series_meta

SQL behind it

SELECT series_id, name, unit, frequency, category, updated_at
FROM macro_series_meta
WHERE (CAST(:category AS text) IS NULL OR category = :category)
ORDER BY category NULLS LAST, series_id

Example response

[
  { "series_id": "CPI", "name": "Consumer Price Index for All Urban Consumers", "unit": "index", "frequency": "monthly", "category": "macro", "updated_at": "2026-07-01T04:12:33+00:00" },
  { "series_id": "INDEX_VIX", "name": "CBOE Volatility Index", "unit": "index", "frequency": "daily", "category": "index", "updated_at": "2026-07-09T22:05:11+00:00" }
]

get_macro_series(series_id, start?, end?, limit?, order?)

Fetch a series time series with inclusive bounds. Defaults to order desc (newest first) so limit=1 returns the latest value, use order asc for charting or moving averages. The value has no inline unit, look it up in the catalog.

REST endpoint:GET /api/macro/series/{series_id}

Tables touched:macro_series

SQL behind it

SELECT series_id, date, value
FROM macro_series
WHERE series_id = :series_id
  AND (CAST(:start AS date) IS NULL OR date >= :start)
  AND (CAST(:end AS date) IS NULL OR date <= :end)
ORDER BY date DESC
LIMIT :limit

Example response

[
  { "series_id": "INDEX_VIX", "date": "2026-07-09", "value": "14.62" },
  { "series_id": "INDEX_VIX", "date": "2026-07-08", "value": "15.10" }
]

get_etf_profile(ticker)

Fetch one ETF's level metadata (net assets, expense ratio, dividend yield, leverage flag, and more). Ratios are 0 to 1 fractions. Returns 404 when the ETF is unknown.

REST endpoint:GET /api/etf/{ticker}/profile

Tables touched:etf_profile

SQL behind it

SELECT ticker, net_assets, net_expense_ratio, portfolio_turnover,
       dividend_yield, inception_date, leveraged, updated_at
FROM etf_profile
WHERE ticker = :ticker

Example response

{
  "ticker": "SPY",
  "net_assets": 612000000000,
  "net_expense_ratio": "0.00095000",
  "portfolio_turnover": "0.02000000",
  "dividend_yield": "0.01230000",
  "inception_date": "1993-01-22",
  "leveraged": false,
  "updated_at": "2026-07-01T03:10:22+00:00"
}

list_etf_holdings(ticker, limit?, offset?)

List an ETF's holdings and weights, sorted by weight descending (top holdings first), paginated. weight is a 0 to 1 fraction and nulls sort last.

REST endpoint:GET /api/etf/{ticker}/holdings

Tables touched:etf_holdings

SQL behind it

SELECT etf_ticker, holding_symbol, description, weight
FROM etf_holdings
WHERE etf_ticker = :etf_ticker
ORDER BY weight DESC NULLS LAST, holding_symbol
LIMIT :limit OFFSET :offset

Example response

[
  { "etf_ticker": "SPY", "holding_symbol": "AAPL", "description": "Apple Inc", "weight": "0.07120000" },
  { "etf_ticker": "SPY", "holding_symbol": "MSFT", "description": "Microsoft Corp", "weight": "0.06850000" }
]

list_etfs_holding_ticker(ticker, limit?, offset?)

Reverse lookup of which ETFs hold a given symbol, sorted by that symbol's weight across each ETF descending, paginated. Opposite direction to list_etf_holdings, filtering etf_holdings by holding_symbol (not etf_ticker) for passive-flow analysis.

REST endpoint:GET /api/etf/holders/{ticker}

Tables touched:etf_holdings

SQL behind it

SELECT etf_ticker, holding_symbol, description, weight
FROM etf_holdings
WHERE holding_symbol = :holding_symbol
ORDER BY weight DESC NULLS LAST, etf_ticker
LIMIT :limit OFFSET :offset

Example response

[
  { "etf_ticker": "QQQ", "holding_symbol": "AAPL", "description": "Apple Inc", "weight": "0.08960000" },
  { "etf_ticker": "SPY", "holding_symbol": "AAPL", "description": "Apple Inc", "weight": "0.07120000" }
]

list_etf_sectors(ticker)

Fetch an ETF's GICS sector weights, sorted by weight descending, no pagination. weight is a 0 to 1 fraction, about 11 sectors.

REST endpoint:GET /api/etf/{ticker}/sectors

Tables touched:etf_sector_weights

SQL behind it

SELECT etf_ticker, sector, weight
FROM etf_sector_weights
WHERE etf_ticker = :etf_ticker
ORDER BY weight DESC NULLS LAST, sector

Example response

[
  { "etf_ticker": "SPY", "sector": "Information Technology", "weight": "0.37600000" },
  { "etf_ticker": "SPY", "sector": "Financials", "weight": "0.13100000" }
]

get_market_movers(date?)

Fetch a trading day's market-wide gainers, losers, and most-active boards (top 20 each). Omit date to auto-select the latest day (MAX(date) first, then that day's rows). change_pct is a percent number (5.23 means +5.23%), not a 0 to 1 fraction.

REST endpoint:GET /api/market/movers

Tables touched:market_movers

SQL behind it

-- 1) resolve latest day when date omitted
SELECT MAX(date) AS d FROM market_movers;

-- 2) fetch that day's three boards
SELECT category, rank, ticker, price, change_amount, change_pct,
       volume, updated_at
FROM market_movers
WHERE date = :d
ORDER BY category, rank

Example response

{
  "date": "2026-07-09",
  "last_updated": "2026-07-09T20:30:05+00:00",
  "gainers": [
    { "rank": 1, "ticker": "XYZ", "price": "12.44", "change_amount": "4.11", "change_pct": "49.34", "volume": 8123456 }
  ],
  "losers": [
    { "rank": 1, "ticker": "LMNO", "price": "6.71", "change_amount": "-3.28", "change_pct": "-32.83", "volume": 4200110 }
  ],
  "most_active": [
    { "rank": 1, "ticker": "TSLA", "price": "402.11", "change_amount": "6.20", "change_pct": "1.57", "volume": 128443090 }
  ]
}

get_ipo_calendar(from_date?, to_date?)

Fetch the IPO calendar of recent and upcoming listings, ordered by IPO date ascending. When both dates are omitted a today-to-90-days window applies, passing one bound constrains only that side. A null price range means not yet priced.

REST endpoint:GET /api/market/ipo-calendar

Tables touched:ipo_calendar

SQL behind it

SELECT symbol, ipo_date, name, price_range_low, price_range_high,
       currency, exchange
FROM ipo_calendar
WHERE (CAST(:from_date AS date) IS NULL OR ipo_date >= :from_date)
  AND (CAST(:to_date AS date) IS NULL OR ipo_date <= :to_date)
ORDER BY ipo_date, symbol
LIMIT :limit

Example response

[
  { "symbol": "ACME", "ipo_date": "2026-07-15", "name": "Acme Robotics Inc", "price_range_low": "18.00", "price_range_high": "20.00", "currency": "USD", "exchange": "NASDAQ" },
  { "symbol": "BETA", "ipo_date": "2026-07-22", "name": "Beta Health Corp", "price_range_low": null, "price_range_high": null, "currency": "USD", "exchange": "NYSE" }
]

Composite views & free-form SQL

Functions

get_analysis(ticker)

Four-lens traffic-light analysis: the backend distills raw warehouse data into a verdict and one-line conclusion for fundamentals, ownership, technicals and options, plus an overall summary. Lenses lacking data are marked na and dropped from the overall denominator, and it always returns 200.

REST endpoint:GET /api/analysis/{ticker}

Tables touched:income_statementscompany_overviewinsider_tradesinstitutional_holdingsprices_dailyoptions_eod

SQL behind it

-- composite: the four-lens engine runs independent queries per lens, then merges verdicts
-- fundamental -> income_statements (revenue/EPS YoY) + company_overview (valuation/ROE)
-- ownership   -> insider_trades (6-month net buys/sells) + institutional_holdings (13F changes)
-- technical   -> prices_daily (self-computed MA50/MA200 + 52-week position)
-- options     -> options_eod (latest chain put/call volume ratio)
-- representative sub-query (technical lens moving averages):
WITH r AS (
  SELECT date, close,
    AVG(close) OVER (ORDER BY date ROWS 49 PRECEDING)  AS ma50,
    AVG(close) OVER (ORDER BY date ROWS 199 PRECEDING) AS ma200
  FROM prices_daily
  WHERE ticker = :t AND date >= CURRENT_DATE - INTERVAL '400 days'
)
SELECT date, close, ma50, ma200 FROM r ORDER BY date DESC LIMIT 1;

Example response

{
  "ticker": "AAPL",
  "overall_verdict": "bullish",
  "overall_summary": "AAPL bullish. Bullish lenses: Fundamentals, Ownership/Flows, Technical. (based on 4/4 lenses)",
  "lenses_scored": 4,
  "lenses": [
    {
      "key": "fundamental",
      "name": "Fundamentals",
      "verdict": "bullish",
      "summary": "Latest quarter revenue grew +39% YoY (strong growth); forward P/E 24.5x reflects market-priced growth.",
      "as_of": "2026-03-31",
      "signals": [
        { "label": "Revenue growth (YoY)", "value": "+39%", "verdict": "bullish" },
        { "label": "Valuation (forward P/E)", "value": "24.5x", "verdict": "neutral" }
      ]
    },
    {
      "key": "chips",
      "name": "Ownership/Flows",
      "verdict": "bullish",
      "summary": "Insiders buying, institutions net adding (13F lags ~90 days).",
      "as_of": "2026-05-20",
      "signals": [
        { "label": "Insider net buy/sell (6mo)", "value": "$12.3M (buy 5 / sell 2)", "verdict": "bullish" }
      ]
    },
    {
      "key": "technical",
      "name": "Technical",
      "verdict": "bullish",
      "summary": "Moving averages in bullish alignment; price at 70% of its 52-week range.",
      "as_of": "2026-07-09",
      "signals": [
        { "label": "Moving-average alignment", "value": "bullish alignment (close 189 / MA50 182 / MA200 178)", "verdict": "bullish" }
      ]
    },
    {
      "key": "options",
      "name": "Options",
      "verdict": "neutral",
      "summary": "Put/call volume ratio 0.85, balanced positioning.",
      "as_of": "2026-07-09",
      "signals": [
        { "label": "Put/call volume ratio", "value": "0.85", "verdict": "neutral" }
      ]
    }
  ]
}

get_objective_report(ticker, sections?, statements_limit?, insider_limit?, holders_limit?, filings_limit?, recent_price_bars?)

Objective data pack for one company: bundles valuation, the last N periods of the three financial statements, insider trades, 13F holdings, a price summary, an options summary and recent filing tables of contents in a single call, all raw with no interpretation. Use sections to fetch only the blocks you need and save tokens.

REST endpoint:GET /api/report/{ticker}

Tables touched:companiescompany_overviewincome_statementsbalance_sheetscash_flow_statementsinsider_tradesinstitutional_holdingsinstitutionsprices_dailyoptions_eodfilingsfiling_sections

SQL behind it

-- composite: per-ticker objective data pack; each requested section calls its own
-- service and returns a {source, as_of, ...} envelope. Raw data, no interpretation.
-- company -> companies; overview -> company_overview
-- financials -> income_statements + balance_sheets + cash_flow_statements
-- insider -> insider_trades; institutional_13f -> institutional_holdings JOIN institutions
-- price_summary -> prices_daily (self-computed MA / 52-week); options_summary -> options_eod
-- filings -> filings + filing_sections (section titles only, no body text)
-- representative sub-query (latest-quarter 13F flow counts):
SELECT COUNT(DISTINCT filer_cik) AS total_holders,
       COUNT(*) FILTER (WHERE change_type = 'new')      AS new_pos,
       COUNT(*) FILTER (WHERE change_type = 'increase') AS increased,
       COALESCE(SUM(change_in_shares), 0)               AS net_share_change
FROM institutional_holdings
WHERE ticker = :t AND quarter_end = :q;

Example response

{
  "ticker": "AAPL",
  "generated_at": "2026-07-10T02:14:55+00:00",
  "company": { "ticker": "AAPL", "name": "Apple Inc.", "sector": "Technology", "exchange": "NASDAQ", "is_active": true },
  "overview": {
    "source": "company_overview",
    "as_of": "2026-07-09",
    "data": { "market_cap": 3450000000000, "pe_ratio": 32.1, "forward_pe": 24.5, "beta": 1.28, "week_52_high": 199.62, "week_52_low": 164.08, "analyst_target_price": 210.0 }
  },
  "financials": {
    "source": "sec_xbrl",
    "as_of": "2026-03-31",
    "income": [ { "period_end": "2026-03-31", "fiscal_period": "Q2", "revenue": 95400000000, "net_income": 25100000000 } ],
    "balance": [ { "period_end": "2026-03-31", "total_assets": 352000000000, "total_equity": 65000000000 } ],
    "cash_flow": [ { "period_end": "2026-03-31", "operating_cash_flow": 28600000000, "free_cash_flow": 26300000000 } ]
  },
  "insider": {
    "source": "sec_form4",
    "as_of": "2026-05-20",
    "trades": [ { "insider_name": "Cook Timothy D", "transaction_date": "2026-05-20", "transaction_code": "S", "shares": 511000, "total_value": 96803000 } ]
  },
  "institutional_13f": {
    "source": "sec_13f",
    "as_of": "2026-03-31",
    "counts": { "total_holders": 4820, "new_positions": 310, "increased": 1980, "decreased": 1750, "sold_out": 240, "net_share_change": 84000000 },
    "top_holders": [ { "filer_name": "Vanguard Group Inc", "shares": 1310000000, "market_value": 248900000000, "change_type": "increase" } ]
  },
  "price_summary": {
    "source": "prices_daily",
    "as_of": "2026-07-09",
    "summary": { "latest_close": 188.72, "change_pct": 1.29, "ma_50": 182.4, "ma_200": 178.1, "range_position_pct": 69.6 },
    "recent_daily": [ { "date": "2026-07-09", "open": 186.9, "high": 189.4, "low": 186.5, "close": 188.72, "volume": 48200000 } ]
  },
  "options_summary": {
    "source": "options_eod",
    "as_of": "2026-07-09",
    "summary": { "put_call_volume_ratio": 0.66, "put_call_oi_ratio": 0.73, "contract_count": 3120 },
    "expirations": [ { "expiration": "2026-07-18", "contract_count": 412 } ]
  },
  "filings": {
    "source": "sec_filing_sections",
    "as_of": "2026-05-02",
    "recent": [ { "accession": "0000320193-26-000042", "form_type": "10-Q", "filed_at": "2026-05-02T20:31:00+00:00", "section_count": 2 } ]
  }
}

execute_readonly_sql(query)Pro

Run a read-only SELECT and get JSON back (rows plus meta) for agents or analysis that need ad hoc queries. Three guardrails apply: SELECT-only parsing, a read-only DB role, a 5 second statement timeout, plus an auto-injected outer LIMIT. Requires the Pro tier.

REST endpoint:Direct DB access (no REST)

Tables touched:companiesinstitutionsinstitution_filingsfilingsfiling_sectionsincome_statementsbalance_sheetscash_flow_statementsinsider_tradesinstitutional_holdingsprices_dailyprices_hourlycompany_overviewoptions_eoddividendssplitsearnings_calendaretf_profileetf_holdingsmacro_seriesmacro_series_metamarket_moversipo_calendarearnings_call_transcriptsearnings_call_segmentsnews_articlesnews_ticker_sentiment

SQL behind it

-- three guardrails:
-- 1) SQL parsing: sqlparse admits a single SELECT / WITH ... SELECT only,
--    rejecting multi-statement input and sensitive functions (pg_sleep, copy, lo_import).
-- 2) DB role: the connection uses a SELECT-only readonly role, so DML/DDL fails
--    even if parsing were bypassed.
-- 3) statement_timeout = 5000ms per query.
-- auto LIMIT: your SQL is wrapped as SELECT * FROM (<your SQL>) _capped LIMIT n;
-- a missing top-level LIMIT gets 1000, an explicit N is capped at 10000;
-- output larger than 100KB is truncated.
-- queryable tables: see the table list on this card (also via describe_table()).

Example response

{
  "row_count": 2,
  "executed_query": "SELECT * FROM (\nSELECT ticker, name FROM companies WHERE sector = 'Technology'\n) AS _capped\nLIMIT 1000",
  "rows": [
    { "ticker": "AAPL", "name": "Apple Inc." },
    { "ticker": "MSFT", "name": "Microsoft Corporation" }
  ]
}

describe_table(table_name?)Pro

Introspect the schema for read-only SQL: with no argument it lists every queryable table, and with a table name it lists that table's columns, types and nullability so you can confirm column names before calling execute_readonly_sql. Requires the Pro tier.

REST endpoint:Direct DB access (no REST)

SQL behind it

-- direct readonly-DB introspection (bind params, public schema only):
-- no argument -> list all BASE TABLEs in the public schema:
SELECT table_name FROM information_schema.tables
WHERE table_schema = 'public' AND table_type = 'BASE TABLE' ORDER BY table_name;
-- with table_name -> list that table's columns, types and nullability:
SELECT column_name, data_type, is_nullable FROM information_schema.columns
WHERE table_schema = 'public' AND table_name = $1 ORDER BY ordinal_position;

Example response

// mode A: describe_table() -> list all queryable tables
{
  "tables": [
    "balance_sheets",
    "cash_flow_statements",
    "companies",
    "company_overview",
    "income_statements",
    "insider_trades",
    "institutional_holdings",
    "options_eod",
    "prices_daily"
  ]
}

// mode B: describe_table("companies") -> that table's columns
{
  "table": "companies",
  "columns": [
    { "column_name": "ticker", "data_type": "text", "is_nullable": "NO" },
    { "column_name": "cik", "data_type": "text", "is_nullable": "NO" },
    { "column_name": "name", "data_type": "text", "is_nullable": "YES" },
    { "column_name": "sector", "data_type": "text", "is_nullable": "YES" }
  ]
}