Skip to main content

Evidence

Evidence builds reports from Markdown and SQL. Source queries run when you build, and their results are stored as Parquet files next to the site; pages then query those files in the browser. With the DuckDB source, the source queries read your Send2Bucket bucket directly, so there is no warehouse to run.

You haveUse
A bucket destinationA DuckDB source that reads the objects
Views in MotherDuckA MotherDuck source
Another warehouseThat warehouse's Evidence source, with the SQL from its querying page
note

We ran the DuckDB source against an S3-compatible bucket: npm run sources and npm run build. The MotherDuck source follows Evidence's documentation and was not run by us.

DuckDB source​

Start from Evidence's project template, or add @evidence-dev/evidence, @evidence-dev/core-components and @evidence-dev/duckdb to a Node project. In evidence.config.yaml, list the DuckDB plugin:

plugins:
components:
"@evidence-dev/core-components": {}
datasources:
"@evidence-dev/duckdb": {}

Create a source folder, sources/s2b/, with three kinds of file.

connection.yaml uses an in-memory DuckDB database:

name: s2b
type: duckdb
options:
filename: ":memory:"

initialize.sql runs before every source query. It creates the secret and the records view from DuckDB:

INSTALL httpfs; LOAD httpfs;
CREATE SECRET s2b_bucket (TYPE s3, PROVIDER credential_chain, CHAIN 'env', REGION '<region>');
CREATE VIEW records AS
SELECT * FROM read_json(
's3://<bucket>/<prefix>/s2b/stream=*/date=*/hour=*/*.ndjson.gz',
format = 'newline_delimited', hive_partitioning = true,
columns = {v: 'INTEGER', delivery_batch_id: 'VARCHAR', batch_id: 'VARCHAR',
received_at: 'TIMESTAMPTZ', signal: 'VARCHAR',
resource: 'JSON', scope: 'JSON', data: 'JSON'});

CHAIN 'env' reads AWS_ACCESS_KEY_ID and AWS_SECRET_ACCESS_KEY from the environment, so no key is written in the project. For S3-compatible storage, add ENDPOINT '<endpoint>' and URL_STYLE 'path' to the secret.

Each other .sql file is one source query, and its file name is the table name. events.sql:

SELECT date_trunc('hour', received_at) AS hour,
data->>'$.event' AS event,
count(*) AS events,
sum((data->>'$.cents')::INTEGER) / 100 AS revenue_usd
FROM records
WHERE signal = 'event' AND date >= current_date - 30
GROUP BY ALL

slow_spans.sql:

SELECT data->>'$.name' AS span,
data->>'$.traceId' AS trace_id,
((data->>'$.endTimeUnixNano')::HUGEINT - (data->>'$.startTimeUnixNano')::HUGEINT) / 1e6 AS duration_ms
FROM records
WHERE signal = 'span'
ORDER BY duration_ms DESC
LIMIT 100

Aggregate in the source query. Everything a source query returns is copied into the site, so a query that returns raw records makes a large site and a slow build. The date filter keeps DuckDB from listing and reading older folders.

Run the source queries with credentials in the environment:

AWS_ACCESS_KEY_ID=<access_key_id> AWS_SECRET_ACCESS_KEY=<secret_access_key> npm run sources

Pages​

A page queries the source tables as s2b.<file name>, in DuckDB SQL, and passes the result to components. pages/index.md:

---
title: Product events
---

```sql events_by_name
select event, sum(events) as events, sum(revenue_usd) as revenue_usd
from s2b.events
group by event
order by events desc
```

<BarChart data={events_by_name} x=event y=events />

```sql slowest
select * from s2b.slow_spans order by duration_ms desc limit 5
```

<DataTable data={slowest} />

On the sample data, events_by_name returns page.view 177, order.paid 53 with revenue 4161, email.sent 47 and signup 23.

MotherDuck source​

If your views live in MotherDuck, add @evidence-dev/motherduck and a source with:

name: s2b
type: motherduck
options:
database: s2b

Supply the token as an environment variable, EVIDENCE_SOURCE__s2b__token, or through the source settings page of npm run dev, which stores it in connection.options.yaml. Use a read-scoped token. Source queries then select from the views, with the SQL from DuckDB: Example queries.

Notes​

  • Data is as fresh as the last npm run sources. Run it and npm run build on a schedule, for example in CI, and publish the build folder.
  • Evidence ignores errors in initialize.sql. If a source query fails with "Table with name records does not exist", run the statements in initialize.sql in the DuckDB CLI to see the real error, which is usually a credential or path problem.
  • The DuckDB plugin bundles its own DuckDB version (1.4 in plugin 2.0). It reads bucket objects and Iceberg tables, but not DuckLake catalogs that need DuckDB 2.0. For a DuckLake destination, query it through MotherDuck or another engine.
  • If npm install fails on a TypeScript peer dependency conflict, add typescript@5 to the project's dependencies.