---
title: "Getting started with commons"
output: rmarkdown::html_vignette
vignette: >
  %\VignetteIndexEntry{Getting started with commons}
  %\VignetteEngine{knitr::rmarkdown}
  %\VignetteEncoding{UTF-8}
---

```{r}
#| include: false
knitr::opts_chunk$set(
  collapse = TRUE,
  comment = "#>",
  eval = rlang::is_installed(c("duckdb", "yaml"))
)
```

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.

```{r}
#| message: false
library(commons)
```

## 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.

```{r}
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:

```{r}
data_source(con)$tables
```

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

```{r}
data_source(con, tables = c("orders", "reps"))$tables
```

`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](https://data-dict.tidyverse.org/) format describes what each
table's rows represent, what its columns mean, how tables join, and what your
organization's domain terms mean.

```{r}
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.

```{r}
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.

```{r}
#| eval: false
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:

```{r}
#| eval: false
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](https://posit-dev.github.io/shinychat/). Build a fresh agent for
each session so users do not share a conversation.

```{r}
#| eval: false
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:

```{r}
#| eval: false
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.

```{r}
#| include: false
DBI::dbDisconnect(con, shutdown = TRUE)
```