Getting started with commons

The hardware and bandwidth for this mirror is donated by METANET, the Webhosting and Full Service-Cloud Provider.
If you wish to report a bug, or if you are interested in having us mirror your free-software or open-source project, please feel free to contact us at mirror[@]metanet.ch.

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.

These binaries (installable software) and packages are in development.
They may not be fully stable and should be used with caution. We make no claims about them.