Getting started with commons

A commons agent combines one or more data_source() objects with a semantic_layer() of governed calculations. For each question, the agent searches the semantic layer first. If a measure matches, the agent runs a calculation defined by your data team. Otherwise, it reads the data documentation, inspects the relevant tables, and writes a SQL query.

Connecting a data source

A data source wraps a DBI connection. The agent queries the database directly, without copying its data.

The examples use a small in-process DuckDB database.

con <- DBI::dbConnect(duckdb::duckdb())

DBI::dbWriteTable(con, "orders", data.frame(
  order_id = 1:6,
  rep = c("Ada", "Ada", "Bo", "Cy", "Bo", "Ada"),
  region = c("EMEA", "Americas", "EMEA", "APAC", "Americas", "EMEA"),
  revenue = c(500, 900, 1200, 300, 2000, 750),
  refunded = c(0, 100, 0, 0, 0, 50)
))

DBI::dbWriteTable(con, "reps", data.frame(
  rep = c("Ada", "Bo", "Cy"),
  hired = as.Date(c("2021-03-01", "2023-07-15", "2024-01-20"))
))

By default, every table on the connection is listed in the system prompt:

data_source(con)$tables
#> [1] "orders" "reps"

In production, list only the relevant tables and open a read-only connection where the backend supports it:

data_source(con, tables = c("orders", "reps"))$tables
#> [1] "orders" "reps"

tables also accepts schema-qualified names like "analytics.orders" or DBI::Id() objects.

Documenting the data

Table names rarely provide enough context. A data dictionary in the data-dict.yaml format describes what each table’s rows represent, what its columns mean, how tables join, and what your organization’s domain terms mean.

dictionary <- tempfile(fileext = ".yaml")
writeLines(
  '
name: Sales
description: One row per closed order, plus the reps who closed them.
details: >
  Revenue figures are gross. Net revenue subtracts the refunded column;
  always report net revenue unless asked otherwise.
tables:
  - name: orders
    description: Closed orders, one row each.
    columns:
      - name: revenue
        type: number
        units: USD
        description: Gross revenue for the order.
      - name: refunded
        type: number
        units: USD
        description: Amount refunded against the order.
      - name: region
        description: Sales region.
        values: [EMEA, Americas, APAC]
  - name: reps
    description: One row per sales representative.
relationships:
  - join: orders.rep = reps.rep
    cardinality: many-to-one
    description: Each order is credited to exactly one rep.
glossary:
  net revenue: Gross revenue minus refunds.
',
  dictionary
)

sales <- data_source(con, dictionary = dictionary)

commons uses the dictionary in three places:

The system prompt includes data source-wide prose and the glossary.
The first tool result that touches a table includes its full entry.
search_context searches the dictionary’s prose.

Defining measures

A measure is an ordinary R function documented with roxygen comments and marked with @measure. Documented arguments are supplied by the model; undocumented arguments are hidden from it.

commons() supplies arguments omitted from arguments, and the model does not see them. An argument named after a data source receives that source’s connection, so the measure need not depend on a variable defined elsewhere.

measure_file <- tempfile(fileext = ".R")
writeLines(
  c(
    "#' Net Revenue by Region",
    "#'",
    "#' @param region `enum[EMEA, Americas, APAC]` Sales region.",
    "#' @measure",
    "net_revenue_by_region <- function(region, warehouse) {",
    "  DBI::dbGetQuery(",
    "    warehouse,",
    "    'SELECT sum(revenue - refunded) AS net_revenue FROM orders WHERE region = ?',",
    "    params = list(region)",
    "  )",
    "}"
  ),
  measure_file
)

layer <- semantic_layer(measure_file)
unlink(measure_file)

The region enum limits the model to values present in the data. warehouse does not appear in the model’s schema.

semantic_layer() reads the documented measures from the file into a layer.

Building the agent

commons() takes a chat client that supplies the provider and model. It returns an ellmer::Chat with commons’ system prompt and tools.

The data source name warehouse connects it to the measure argument of the same name.

agent <- commons(
  ellmer::chat_anthropic(),
  data_sources = list(warehouse = sales),
  semantic_layer = layer
)

agent$chat("What was net revenue in EMEA?")
#> Net revenue in EMEA was $2,400.

The agent finds net_revenue_by_region with search_measures("net revenue in EMEA"), then calls it with region = "EMEA". The measure supplies the definition of net revenue.

Without a matching measure, the agent can run SQL against the connection:

agent$chat("Which rep was hired most recently?")
#> Cy, hired 2024-01-20.

Here the agent searches the data documentation, describes the reps table, and runs a SQL query.

The agent’s tools

Every commons agent has five tools:

Tool	What it does
`search_measures`	Finds measures matching a question, with their argument schemas
`call_measure`	Runs a measure
`search_context`	Searches your data documentation
`describe_table`	Returns a table’s columns, types, and sample rows
`run_sql`	Runs a SQL query after checking its leading statement keyword

run_sql checks the query’s leading statement keyword against a denylist of common data- and schema-modifying operations before passing it to the database. The check is a keyword filter; database permissions remain the access-control boundary. Open a read-only connection where possible, with access limited to the tables the agent needs.

Deploying in Shiny

commons() returns an ellmer::Chat, so it works with shinychat. Build a fresh agent for each session so users do not share a conversation.

library(shiny)
library(shinychat)

ui <- bslib::page_fillable(chat_mod_ui("chat"))

server <- function(input, output, session) {
  agent <- commons(
    ellmer::chat_anthropic(),
    data_sources = list(warehouse = sales),
    semantic_layer = layer
  )
  chat_mod_server("chat", client = agent)
}

shinyApp(ui, server)

Customizing the system prompt

The default prompt is a markdown file shipped with commons. To customize it, copy the file into your project and interpolate the edited version:

file.copy(
  system.file("prompts/system-prompt.md", package = "commons"),
  "system-prompt.md"
)

commons(
  ellmer::chat_anthropic(),
  data_sources = list(warehouse = sales),
  semantic_layer = layer,
  system_prompt = ellmer::interpolate_file(
    "system-prompt.md",
    date = Sys.Date()
  )
)

commons appends the available tables and data dictionaries to the prompt. Omit them from the file.