Browsing tables

We expose a typed query syntax for large tabular resources. Today this covers POST /v0/visits/browse, POST /v0/procedures/browse, and POST /v0/diagnoses/browse, and we’ll move additional list endpoints onto the same contract over time. Every browse route accepts the same JSON request shape — column selection, filters, sorts, aggregates, search, and pagination — and returns a { table, aggregate, total } envelope.

Displaying a patient’s clinical data to end users? The visits, procedures, diagnoses, prescription-fills, and lab browse schemas change frequently as we expand claims coverage and add columns. Rather than marshalling these rows yourself and handling that churn, embed the read-only iframe — see Embedding the clinical-data UI. The browse endpoints remain the right tool for server-to-server querying and exports.

Each route registers its own request/response schemas in components.schemas as {Entity}BrowseRequest and {Entity}BrowseResponse (e.g. VisitBrowseRequest, ProcedureBrowseResponse), along with per-slot types ({Entity}BrowseFilter, {Entity}BrowseSort, {Entity}BrowseSelect, {Entity}BrowseRow, {Entity}BrowseAggregateRequest, {Entity}BrowseAggregate). Those schemas are the authoritative list of which columns each route accepts; this section documents the shared syntax so each per-route schema doesn’t have to repeat it.

Root request shape

{
  "select":    { },
  "where":     { },
  "orderBy":   [ ],
  "aggregate": { },
  "search":    "...",
  "take":      100,
  "skip":      0
}

Every top-level field is optional:

• Omit select to return every column for every joined entity.
• Omit where to return every row the caller is allowed to see.
• Omit orderBy to use the route’s default sort (typically created-at).
• Omit aggregate to skip roll-up computation.
• Omit search to skip the cross-column substring match.
• Omit take to return every matching row (useful for exports).
• Omit skip to start at the first row.

Entity slots and joins

A browse response is a flat table of rows, but each row is keyed by entity: visits join through to patient, site, payor, company, and project; procedures join through to visit, patient, site, and so on. Every slot of the request — select, where, orderBy, aggregate — mirrors this join structure and is keyed by entity name (camelCase). The root entity uses its own name (visit, procedure, diagnosis); joined-in entities use their own keys.

The exact set of entities and columns per route is documented by the corresponding {Entity}BrowseRequest schema — inspect it to see which keys each slot accepts.

Toggling columns with select

select is an entity-keyed object where each inner block maps column name to a boolean:

{
  "select": {
    "visit":   { "ref": true, "earliestServiceDate": true },
    "patient": { "firstName": true, "lastName": true }
  }
}

Every column defaults to false inside a select block. Omitting an entity’s block entirely returns every column for that entity; omitting the top-level select returns every column for every entity. Response rows mirror the request — each selected entity appears as a nested object keyed by entity name, with only the selected columns present. Unselected columns are absent from the JSON (not null).

Filtering with where

Each entity block under where maps a column name to a list of field filters. All field filters — across columns, across entities, and across entries within the same list — are ANDed together.

Every field filter is one of:

• Value filter — an object with a single operator key. The available operators depend on the column’s type:
  • Text (string): equals, notEquals, contains, startsWith, endsWith
  • Ordered (integer, decimal, date, datetime, double): equals, notEquals, gt, gte, lt, lte
  • Categorical (bool): equals, notEquals
• List filter — an object with in or notIn whose value is an array of candidate values. Available on every column type.
• Null check — the literal string "isNull" or "isNotNull". Available on every nullable column.

{
  "where": {
    "visit": {
      "earliestServiceDate": [
        { "gte": "2025-01-01" },
        { "lt":  "2026-01-01" }
      ]
    },
    "patient": {
      "lastName": [ { "startsWith": "Smi" } ]
    },
    "site": {
      "state":      [ { "in": ["CA", "NY", "TX"] } ],
      "systemName": [ "isNotNull" ]
    }
  }
}

The generated {Entity}BrowseFilter schema encodes the valid operator set per column, so requests with mismatched operators (e.g. contains on an integer) fail deserialization with a 400.

Sorting with orderBy

orderBy is an array, applied in order — the first entry is the primary sort, the second breaks ties, and so on. Each entry picks one entity and one column:

{
  "orderBy": [
    { "visit":   { "earliestServiceDate": "desc" } },
    { "patient": { "lastName": "asc" } }
  ]
}

Direction is "asc" or "desc". A stable per-row tiebreaker (typically the row’s id) is appended automatically, so pagination is deterministic even when the requested sort produces ties. Omit orderBy entirely to use the route’s default sort.

Aggregates

Pass an aggregate block to compute roll-ups over the filtered row set — pagination (take / skip) does not narrow the aggregated rows, so aggregates always describe the full filtered population. Every entity exposes count; entities with numeric columns also expose sum, avg, min, and max, each keyed by column name with boolean opt-ins. Those four take numeric columns only, never a date, so a visit’s service dates are not among them.

{
  "aggregate": {
    "visit": { "count": true }
  }
}

Results come back on response.aggregate in the same shape. Omit the block (or set every opt-in to false) to skip aggregation.

Full-text search

The top-level search string applies a case-insensitive substring match across every text-like column on every joined entity. It is ANDed with where. Use it to power a single-box search UX; use where for precise queries.

Pagination

skip and take both count matching rows (post-filter, post-search). Responses include total — the count of matching rows before pagination — so clients can render “page X of N” without a second request. Omit take to stream every matching row; combine that with Accept: text/csv to export.

Response

{
  "table":     [ ],
  "aggregate": { },
  "total":     4217
}

• table is the paginated rows, each mirroring the select shape.
• aggregate mirrors the request’s aggregate shape; absent when no aggregates were requested.
• total is the count of rows matching where + search, before skip / take.

CSV export

Every browse route content-negotiates on the Accept header. application/json (the default) returns the envelope above; text/csv returns a CSV of the selected columns only — aggregate and total are omitted from CSV output. Pair Accept: text/csv with an absent take to stream a full export.