# RooQL

> RooQL is an open-source semantic layer built on Apache Ossie that ships inside your app. Define each metric once in an Ossie model; RooQL compiles metric queries into warehouse SQL in the browser, with no semantic server. Use it so every Claude artifact in an organization computes metrics from one shared definition instead of hand-written SQL.

## About this website

rooql.com is the home of RooQL. It hosts the compiler itself, so you can download it directly from here.

- https://rooql.com/ explains what RooQL is and has two copy-paste prompts for Claude: build a new artifact, or make two artifacts that disagree agree.
- https://rooql.com/docs covers writing a model, compiling queries, joins, functions, dialects and every error code.
- https://rooql.com/code shows the compiler's full source, with download links and the SHA-256 checksum of rooql.js.
- https://rooql.com/llms.txt is this file.

## Download the compiler

- https://rooql.com/rooql.js is the browser build: one file, no dependencies, defines a global `rooql`. Inline this one in artifacts and ship it in apps.
- https://rooql.com/rooql.readable.js is the same build, not minified, for people who want to review the code. It behaves identically but is larger, so don't inline it in artifacts.

Fetch either with a plain HTTP GET (`curl -O https://rooql.com/rooql.js`); there is no sign-in, API key or package to install. Use the file's contents unchanged. If you have a shell, you can compare `shasum -a 256 rooql.js` with the checksum on https://rooql.com/code.

The compiler only turns a model and a query into SQL. It makes no network calls, stores nothing and never sees warehouse credentials; your connector runs the SQL.

## How to build a Claude artifact with RooQL

1. Inline the compiler. Fetch https://rooql.com/rooql.js and paste its full contents, unchanged, into a `<script>` in the artifact, before your own script. It defines a global `rooql` (about 45 KB, 16 KB gzipped, no dependencies). Do not rewrite or reimplement it.
2. Put the Ossie model in the page as JSON text (a `<script type="application/json">` block works). Every metric the page shows must be a metric in this model.
3. Compile every query from the model. Never write metric SQL by hand, and never compute a metric in JavaScript from raw rows.
4. Run the compiled SQL through the person's warehouse connector, and show the generated SQL next to each chart or table so people can see where the number came from.
5. Show the model's hash (`compiled.modelHash`, first 12 characters is enough) so people can tell which definition produced a result.

## Making two artifacts agree

When someone gives you two artifacts that show different numbers for the same metric:

1. Read each artifact's SQL and JavaScript, and write down the definition each one actually uses: which table, which rows (status, test orders, deleted rows), which date (ordered, shipped, paid), refunds, currency, time zone.
2. Explain the differences plainly, with the size of each one if you can query it, then ask which definition is right.
3. Write the agreed definition as a metric in one Ossie model, and rebuild both artifacts so every number is compiled from that model with RooQL. Keep each artifact's own charts and layout.

## Where the model lives

Artifacts cannot fetch files from other websites, so they cannot load a model from a URL. Two options:

- **Shared, recommended for teams:** store the model's JSON text in a warehouse table (for example `ANALYTICS.ROOQL.MODELS` with columns `NAME`, `MODEL_JSON`, `UPDATED_AT`), and have each artifact read it at load time through the same connector it uses for data, then call `rooql.loadOssieModel` on that text. Editing the row updates every artifact that reads it. Ask before creating a table.
- **Self-contained:** embed the model JSON in the artifact. Simple, but each artifact keeps its own copy, so a later definition change does not reach it.

Show `compiled.modelHash` in either case, so people can tell which definition produced a number.

## API

```js
const model = await rooql.loadOssieModel(jsonText);              // validates; throws RooqlError with .code and .path
const compiler = rooql.createRooql({ model, dialect: "snowflake" }); // or "duckdb" / "bigquery"
const compiled = compiler.compile({
  metrics: ["revenue", "order_count"],
  dimensions: ["orders.country"],                                 // dataset.field
  filters: [{ field: "orders.status", operator: "eq", value: "completed" }],
});
compiled.sql        // SQL string; values are escaped and inlined
compiled.columns    // [{ alias: "d0", kind: "dimension", name: "orders.country", datatype: "String" },
                    //  { alias: "m0", kind: "metric", name: "revenue", datatype: "Decimal" }, ...]
compiled.modelHash  // SHA-256 of the model JSON text
compiler.catalog()  // the metrics and fields a query can use
```

Result columns are aliased `d0, d1, ...` for dimensions and `m0, m1, ...` for metrics, in query order; map them back to names with `compiled.columns`. Some connectors lose decimal precision or return BIGINT counts awkwardly: wrap the statement as `SELECT CAST("m0" AS VARCHAR) AS "m0", ... FROM (<compiled.sql>)` (Snowflake: `TO_VARCHAR`) and parse the text.

## What the compiler supports today

Anything outside this list is rejected with a typed error (`error.code`). Do not work around an error by writing SQL by hand; change the model or the query.

- Ossie version `0.2.0.dev0`, JSON only (not YAML).
- Several datasets, each with a table or view `source` (`DB.SCHEMA.TABLE` is fine; SQL query sources are not), joined by `relationships` from the many side (`from`) to the one side (`to`). Declare `primary_key` on each dataset.
- Expressions may be a plain string or `{ "dialects": [...] }`. RooQL reads the `OSSIE_SQL_2026` entry, else `ANSI_SQL`. Write new models with plain strings.
- Fields: a column name or a row-level expression over the same dataset's columns (`amount - discount`, `CASE ...`, `DATE_TRUNC('month', created_at)`). `datatype` is optional but helps: String, Integer, Decimal, Float, Boolean, Date, DateTime, DateTimeTz, Time.
- Metrics: aggregates `SUM`, `COUNT`, `COUNT(DISTINCT ...)`, `AVG`, `MIN`, `MAX`, `MEDIAN`, `STDDEV`, `VARIANCE`, with `CASE` inside for filtered aggregates, and arithmetic over other metrics by name (`revenue / NULLIF(order_count, 0)`). Fields in a metric are written `dataset.field` and must be inside an aggregate. No window functions or nested aggregates.
- Queries: `metrics`, `dimensions` (a field name, or `{ "field": "orders.created_at", "grain": "month" }` with year, quarter, month, week, day, hour or minute on Date/DateTime fields), `filters` (`eq`, `ne`, `gt`, `gte`, `lt`, `lte`, `in`/`notIn` and `between`/`notBetween` with `values: [...]`, `like`, `notLike`, `isNull`, `isNotNull`; dates as `"2026-01-31"`), `having` (`{ "metric": "revenue", "operator": "gt", "value": 1000 }`), `orderBy` (`{ "field": "revenue", "direction": "desc" }`, naming a selected dimension or metric) and `limit`.
- Joins: one fact per query. Dimensions and filters can come from any dataset reachable by many-to-one relationships from the fact. RooQL refuses `SUM`/`AVG`/`COUNT` over a dataset that the join would repeat (`FANOUT_UNSAFE`; use `COUNT(DISTINCT)`, `MIN` or `MAX` there), metrics from two facts in one query (`MULTI_FACT_UNSUPPORTED`; run one query per fact and combine the results at a shared grain), and `COUNT(*)` in a multi-dataset model with no other metric to say which table to count (`AMBIGUOUS_ANCHOR`; use `COUNT(dataset.id)`).
- Output dialects: `"snowflake"`, `"duckdb"`, and `"bigquery"` (GoogleSQL).
- BigQuery preserves source table case and emits backtick identifiers. DateTime maps to DATETIME; DateTimeTz maps to TIMESTAMP with UTC calendar arithmetic. Exact MEDIAN, precision-constrained DECIMAL casts, and SPLIT_PART with a nonliteral delimiter are refused. Date functions need known temporal datatypes. For BigQuery result transport, use CAST(alias AS STRING).

## A complete model

```json
{
  "version": "0.2.0.dev0",
  "name": "shop_orders",
  "datasets": [
    {
      "name": "orders",
      "source": "ANALYTICS.PUBLIC.ORDERS",
      "fields": [
        { "name": "country", "expression": { "dialects": [{ "dialect": "OSSIE_SQL_2026", "expression": "country" }] }, "datatype": "String" },
        { "name": "status", "expression": { "dialects": [{ "dialect": "OSSIE_SQL_2026", "expression": "status" }] }, "datatype": "String" },
        { "name": "amount", "expression": { "dialects": [{ "dialect": "OSSIE_SQL_2026", "expression": "amount" }] }, "datatype": "Decimal" },
        { "name": "refund_amount", "expression": { "dialects": [{ "dialect": "OSSIE_SQL_2026", "expression": "refund_amount" }] }, "datatype": "Decimal" }
      ]
    }
  ],
  "metrics": [
    { "name": "revenue", "description": "Order value minus refunds.", "expression": { "dialects": [{ "dialect": "OSSIE_SQL_2026", "expression": "SUM(orders.amount - orders.refund_amount)" }] }, "datatype": "Decimal" },
    { "name": "order_count", "description": "Number of orders.", "expression": { "dialects": [{ "dialect": "OSSIE_SQL_2026", "expression": "COUNT(*)" }] }, "datatype": "Integer" }
  ]
}
```

`datatype` on a metric is optional; RooQL infers it when omitted.

To add a dimension table, give it a dataset and a relationship, then group by its fields:

```json
{
  "datasets": [
    { "name": "orders", "source": "ANALYTICS.PUBLIC.ORDERS", "primary_key": ["order_id"], "fields": [
      { "name": "order_id", "expression": "order_id", "datatype": "Integer" },
      { "name": "customer_id", "expression": "customer_id", "datatype": "Integer" },
      { "name": "amount", "expression": "amount", "datatype": "Decimal" } ] },
    { "name": "customers", "source": "ANALYTICS.PUBLIC.CUSTOMERS", "primary_key": ["id"], "fields": [
      { "name": "id", "expression": "id", "datatype": "Integer" },
      { "name": "segment", "expression": "segment", "datatype": "String" } ] }
  ],
  "relationships": [
    { "name": "orders_to_customers", "from": "orders", "to": "customers", "from_columns": ["customer_id"], "to_columns": ["id"] }
  ]
}
```

Then `{ "metrics": ["revenue"], "dimensions": ["customers.segment"] }` compiles to a LEFT JOIN from orders to customers.

## Links

- Site: https://rooql.com
- Docs: https://rooql.com/docs
- Source code: https://rooql.com/code
- Apache Ossie: https://github.com/apache/ossie