# SweMetrics API Guide for LLMs

> **Base URL:** `https://swemetrics.se/duckdb`
> **Interactive Docs:** https://swemetrics.se/duckdb/docs
> **OpenAPI Spec:** https://swemetrics.se/duckdb/openapi.json

A REST API providing access to 476M+ scholarly works via DuckDB. Designed for analysts, tools, and LLMs.

---

## Quick Start

```bash
# Get 5 most-cited works from 2023
curl -H "X-API-Key: YOUR_KEY" \
  "https://swemetrics.se/duckdb/api/works?filter=publication_year:2023&sort=cited_by_count:desc&limit=5"
```

---

## Authentication

Three methods (use any one):

| Method | Example |
|--------|---------|
| **Header** (preferred) | `-H "X-API-Key: YOUR_KEY"` |
| **Query param** | `?api_key=YOUR_KEY` |
| **Basic auth** | `-u apikey:YOUR_KEY` |

**Roles:** `reader` (REST CRUD reads only), `analyst` (REST reads + raw SQL + MCP tools), `editor` (CRUD on tables), `admin` (+ execute + export)

> **MCP note:** All MCP tools issue SQL and require `QUERY` permission. The `reader` role does **not** have this — use `analyst` or higher for any MCP client (including claude.ai remote MCP integration).

Query parameter auth works for **all endpoints including `/duckdb/mcp`**, making it suitable for clients that cannot set custom headers (e.g. claude.ai remote MCP integration — register `https://swemetrics.se/duckdb/mcp?api_key=YOUR_KEY` as a remote server).

---

## MCP Access (recommended for LLMs)

The `/duckdb/mcp` endpoint implements the [Model Context Protocol](https://modelcontextprotocol.io) via streamable-HTTP. It is the most ergonomic interface for LLMs: tools are self-describing, SQL queries run directly against the local parquet snapshot, and the `export` tool avoids flooding context windows with row data.

### Client configuration

To connect from **Claude Code** or any MCP client, add this server entry to `~/.claude/mcp.json` (replace key):

```json
{
  "mcpServers": {
    "swemetrics": {
      "type": "http",
      "url": "https://swemetrics.se/duckdb/mcp",
      "headers": {
        "X-API-Key": "YOUR_KEY"
      }
    }
  }
}
```

Or from the command line:

```bash
claude mcp add --transport http swemetrics https://swemetrics.se/duckdb/mcp \
  --header "X-API-Key: YOUR_KEY"
```

### Raw JSON-RPC handshake

```json
POST https://swemetrics.se/duckdb/mcp
X-API-Key: YOUR_KEY
Content-Type: application/json

{"jsonrpc":"2.0","id":1,"method":"initialize",
 "params":{"protocolVersion":"2024-11-05","capabilities":{},
           "clientInfo":{"name":"my-agent","version":"0.1"}}}
```

### Standard Tools

| Tool | Permission | Description |
|------|-----------|-------------|
| `database_info` | query | Tables, schemas, macros, extensions; includes `estimated_row_count` per table and `export_base_url` when public exports are configured |
| `list_tables` | query | Tables (and optionally views) with column counts |
| `describe` | query | Column names and types for a table or view |
| `schema` | query | All tables + columns — use `compact=true` for ~70% fewer tokens, `table_pattern` to filter |
| `summarize` | query | Per-column stats (min/max/avg/null%/approx_unique) — accepts `table` or `sql` param (use `sql` with SAMPLE for large tables) |
| `value_counts` | query | Top-N distinct values with count, relative frequency (%), and cumulative frequency |
| `help` | query | DuckDB documentation browser — no args for TOC, topic keyword for section content |
| `sample` | query | Unbiased reservoir sample — better than LIMIT for exploration |
| `column_search` | query | Find which tables contain a column matching a name/LIKE pattern |
| `row_counts` | query | Row count for one or all tables (fast on parquet-backed views) |
| `sample_by_id_range` | query | Reservoir sample filtered by integer ID range — efficient parquet row-group skipping |
| `explain` | query | EXPLAIN or EXPLAIN ANALYZE a SQL statement — shows the query plan |
| `view_definition` | query | Return the SQL definition of a view |
| `relationships` | query | Discover likely join keys by finding column names shared across tables |
| `time_range` | query | Min/max/count and span for all date/timestamp columns in a table |
| `query` | query | Read-only SQL — result capped at `max_rows` (default 200) |
| `export` | query | Run SQL, write to file, return URL — **use for large result sets**. Set `public=true` for an auth-free URL suitable for cross-domain imports |
| `server_status` | query | Memory usage, spill-to-disk status, and key DuckDB settings |
| `import_remote` | query | Download a remote .parquet/.csv/.json file, store locally, register as `FROM alias()` table macro — requires `DUCKDB_IMPORTS_DIR` on server |
| `list_imports` | query | List active remote imports with alias, source URL, row count, columns, and expiry time |
| `drop_import` | query | Drop a named import's macro and delete its local file immediately |
| `execute` | execute | Write SQL: INSERT, UPDATE, DELETE, CREATE TABLE AS SELECT, etc. (requires `execute` permission — admin role by default) |

### Custom OpenAlex Tools

These tools call the live OpenAlex API and are ideal for resolving names to numeric IDs before querying the local snapshot.

**Entity lookup (autocomplete):**

| Tool | Parameter | Returns |
|------|-----------|---------|
| `api_search_works` | `searchterm` | work_id, doi, display_name, cited_by_count |
| `api_search_authors` | `searchterm` | author_id, orcid, display_name, works_count |
| `api_search_institutions` | `searchterm` | institution_id, ror, display_name, works_count |
| `api_search_sources` | `searchterm` | source_id, display_name, works_count |
| `api_search_topics` | `searchterm` | topic_id, display_name, wikipedia_url |
| `api_search_funders` | `searchterm` | funder_id, ror, display_name |
| `api_search_publishers` | `searchterm` | publisher_id, display_name |
| `api_search_doi` | `doi` | work_id, doi, display_name |
| `api_search_orcid` | `orcid` | author_id, orcid, display_name |
| `api_search_id` | `searchterm` | Generic OpenAlex autocomplete (any entity type) |

**Full search (returns rich result objects from OpenAlex API):**

| Tool | Parameter | Description |
|------|-----------|-------------|
| `api_works_search` | `searchterms` | Full-text search for works via OpenAlex works API |
| `api_authors_search` | `searchterms` | Filter-based author search via OpenAlex authors API |

**Text analysis:**

| Tool | Parameter | Description |
|------|-----------|-------------|
| `api_aboutness_keywords` | `title` | Extract keywords from a title via OpenAlex text API |
| `api_aboutness_topics` | `title` | Extract topic, subfield, and field assignments from a title |

**Vector search (QBOT):**

| Tool | Parameters | Description |
|------|-----------|-------------|
| `api_find_qbot` | `search_query`, `result_limit` | Semantic search via lance vector index — returns works with fulltext PDF/XML URLs |
| `api_works_qbot` | — | All QBOT-indexed works with fulltext URLs |

**Documentation readers:**

| Tool | Parameters | Description |
|------|-----------|-------------|
| `read_api_fields` | `entity_name`, `entity_url` | Parse field definitions from OpenAlex documentation pages |
| `read_docs` / `docs` | `entity_url` | Read structured content from any URL |

### MCP Resources

Reference documents available as MCP resources. Fetch before writing complex DuckDB SQL — the syntax differs significantly from standard SQL.

> **Note:** Most MCP clients (including Claude Code) do not auto-fetch resources — request them explicitly with `resources/read`. Use `resources/list` to discover what is available.

Four resources are always available (built into the binary):

| URI | Content |
|-----|---------|
| `duckdb://docs/sql-syntax` | FROM-first, GROUP BY ALL, SELECT * EXCLUDE/REPLACE, PIVOT/UNPIVOT, ASOF/LATERAL joins, lambdas, direct parquet queries |
| `duckdb://docs/visualization` | SQL templates for time series, bar charts, scatter plots, heatmaps, and ASCII text plots via the `textplot` extension |
| `duckdb://docs/functions` | LIST/STRUCT/MAP nested type reference, regexp (RE2), JSON operators, unnest patterns, QUALIFY clause |
| `duckdb://docs/datadomain-meta` | COMMENT ON syntax, tags MAP, `macro_descriptions` table pattern, all `duckdb_*()` system catalog functions with example queries |

Deployment-specific resources (mounted via `DUCKDB_MCP_DOCS_DIR`):

| URI | Content |
|-----|---------|
| `duckdb://docs/suhf-bibliometrics` | SUHF 2025 guidance for responsible evaluative bibliometrics in Sweden — 12 recommendations, indicator choice, swemetrics column mapping |

```json
{"jsonrpc":"2.0","id":1,"method":"resources/list","params":{}}
{"jsonrpc":"2.0","id":2,"method":"resources/read",
 "params":{"uri":"duckdb://docs/sql-syntax"}}
```

### Tool: `help` — DuckDB documentation browser

Call with no arguments for a table of contents; call with a section ID or keyword for section content:

```json
{"jsonrpc":"2.0","id":1,"method":"tools/call","params":{"name":"help","arguments":{}}}
{"jsonrpc":"2.0","id":2,"method":"tools/call","params":{"name":"help","arguments":{"topic":"group-by-all"}}}
```

### Recommended workflow for bibliometric queries

The recommended pattern is: **resolve → explore → query → export**.

1. **Resolve names to IDs** — use `api_search_authors`, `api_search_institutions`, `api_search_topics` etc. to get numeric IDs from the live OpenAlex API
2. **Explore the local snapshot** — use `schema(compact=true)`, `row_counts`, `value_counts` to understand the data before writing joins
3. **Write targeted SQL** — use `query` for small results, `export` for large ones; follow the CTE-first pattern below for multi-table joins
4. **Fetch syntax docs if needed** — call `help("from-first")` or fetch `duckdb://docs/sql-syntax` before writing complex queries

**Example:**
```
api_search_institutions("KTH")   → institution_id: 86987016
query("SELECT ... FROM works_authorships WHERE institution_id = 86987016 ...")
export(sql="SELECT ...", format="parquet")  → download URL
```

### Token-saving tips

| Situation | Tool | Why |
|-----------|------|-----|
| Get all table structures | `schema(table_pattern='works%', compact=true)` | **Always filter** — 57 tables; unfiltered compact schema is very large |
| Find a column across tables | `column_search("work_id")` | One call, no SQL |
| Understand a categorical column | `value_counts(table, column)` | Returns count + pct + cumulative_pct |
| Large result set | `export` | Returns URL (~15 tokens), not row data |
| Cross-domain join needed | `database_info` on both servers → `export(public=true)` on the smaller → `import_remote` on the larger → `query` → `drop_import` | 4 tool calls replaces manual S3 workflow; `estimated_row_count` guides which direction to push data |
| Unfamiliar DuckDB syntax | `help` or fetch `duckdb://docs/sql-syntax` | Fetch once, write correctly |

---

## Cross-Domain Queries (MCP)

When you have MCP connections to two or more caddy-duckdb servers (e.g. swemetrics + kthworks-mcp), you can join their data without S3 or manual file transfer. The workflow is four MCP tool calls:

**Step 1 — discover** what each server has and where exports land:
```json
{"method":"tools/call","params":{"name":"database_info","arguments":{}}}
```
The response includes `estimated_row_count` per table (use this to decide which server exports — push data from the smaller/filtered source to the larger one) and `export_base_url` (only present when the server supports public exports).

**Step 2 — export** from the smaller/filtered server with `public=true`:
```json
{"method":"tools/call","params":{"name":"export","arguments":{
  "sql": "SELECT orcid, display_name FROM diva_authors WHERE orcid IS NOT NULL",
  "format": "parquet",
  "public": true
}}}
```
Returns `{"url": "https://kthworks.vm-app.cloud.cbh.kth.se/duckdb/public-exports/<uuid>.parquet", "rows": 6934, ...}`.

**Step 3 — import** on swemetrics (the server with the large OpenAlex table):
```json
{"method":"tools/call","params":{"name":"import_remote","arguments":{
  "url": "https://kthworks.vm-app.cloud.cbh.kth.se/duckdb/public-exports/<uuid>.parquet",
  "alias": "diva_authors",
  "ttl_minutes": 60
}}}
```
Returns `{"row_count": 6934, "columns": ["orcid","display_name"], ...}`.

**Step 4 — join** on swemetrics using `query`:
```json
{"method":"tools/call","params":{"name":"query","arguments":{
  "sql": "SELECT d.display_name, count(*) AS works, avg(w.fwci) AS mean_fwci FROM diva_authors() d JOIN authors a ON d.orcid = a.orcid JOIN works_authorships wa USING (author_id) JOIN works w USING (work_id) GROUP BY d.display_name ORDER BY works DESC LIMIT 20"
}}}
```

**Step 5 — clean up:**
```json
{"method":"tools/call","params":{"name":"drop_import","arguments":{"alias":"diva_authors"}}}
```

### Cross-domain notes

- `import_remote` only accepts `https://` URLs (SSRF protection).
- The alias becomes a table macro in DuckDB's `:memory:` catalog — use `FROM alias()` (with parentheses) in all subsequent queries.
- Re-importing the same alias replaces the previous import — idempotent.
- Call `list_imports` to see active imports, their sizes, and time until expiry.
- `import_remote` requires `DUCKDB_IMPORTS_DIR` on the *receiving* server; `export(public=true)` requires `DUCKDB_PUBLIC_EXPORTS_DIR` on the *exporting* server.

---

## Efficient Multi-Table Queries on Large Parquet Databases

Several swemetrics tables exceed 1 billion rows. Naive joins will time out. Use the CTE-first pattern:

**Rules:**
1. **Start with the smallest, most selective table** — narrow down the key IDs first
2. **Never use `SELECT *`** — project only the columns you actually need
3. **Use CTEs to stage results** — each CTE should be smaller than the previous
4. **Filter before joining** — use `WHERE x IN (FROM cte SELECT id)` to push filters down

**Pattern — find works by a specific author at a specific institution:**

```sql
WITH author_work_ids AS (
    FROM works_authorships wa
    JOIN authors a USING (author_id)
    SELECT DISTINCT wa.work_id
    WHERE a.display_name ILIKE '%Maguire%'
      AND a.last_known_institution_display_name ILIKE '%KTH%'
),
citation_counts AS (
    FROM works_referenced_works
    SELECT referenced_work_id AS work_id, COUNT(*) AS citations_in_graph
    WHERE referenced_work_id IN (FROM author_work_ids SELECT work_id)
    GROUP BY referenced_work_id
)
FROM author_work_ids aw
JOIN works w USING (work_id)
LEFT JOIN citation_counts cc USING (work_id)
SELECT w.work_id, w.doi, w.display_name, w.publication_year,
       w.cited_by_count, COALESCE(cc.citations_in_graph, 0) AS citations_in_graph
ORDER BY w.cited_by_count DESC
```

**What NOT to do (will time out):**

```sql
-- BAD: joins two billion-row tables without narrowing first
SELECT w.*, pl.source_is_core
FROM works_authorships wa
JOIN works_primary_location pl ON wa.work_id = pl.work_id
WHERE wa.institution_id = 86987016
```

**Tables to be especially careful with:** `works_referenced_works` (3B rows), `works_keywords` (2.9B), `works_related_works` (2.5B), `works_topics` (906M) — always filter with a CTE before joining.

---

## Endpoints

| Endpoint | Method | Description |
|----------|--------|-------------|
| `/api` | GET | List all accessible tables and views with type info |
| `/api/{table}` | GET | Query table/view with filters, pagination, sorting |
| `/api/{table}` | POST | Insert records (editor+, tables only) |
| `/api/{table}` | PUT | Update records (editor+, tables only) |
| `/api/{table}` | DELETE | Delete records (editor+, tables only) |
| `/api/{table}/columns` | GET | Get table/view schema and optional statistics |
| `/view/{name}` | GET | Query api_-prefixed views |
| `/view/{name}/columns` | GET | Get view schema and optional statistics |
| `/export` | POST | Run SQL, write result to file, return URL |
| `/exports/{filename}` | GET | Download exported file (no auth — UUID is capability token) |
| `/public-exports/{filename}` | GET | Download a public export (no auth — only when `DUCKDB_PUBLIC_EXPORTS_DIR` is configured) |
| `/query` | POST | Execute raw SQL (analyst+) |
| `/mcp` | POST | MCP streamable-HTTP endpoint |
| `/openapi.json` | GET | OpenAPI 3.0 spec (no auth) |
| `/health` | GET | Health check (no auth) |

---

## Query Parameters

| Parameter | Example | Description |
|-----------|---------|-------------|
| `filter` | `publication_year:2023,type:article` | Filter conditions (comma = AND) |
| `sort` | `cited_by_count:desc,title:asc` | Sort order |
| `limit` | `100` | Results per page (default: 20, max: 1000) |
| `page` | `2` | Page number (offset pagination) |
| `cursor` | `*` or `eyJ...` | Cursor pagination (use `*` for first page) |
| `select` | `work_id,title,cited_by_count` | Return only these columns |
| `group_by` | `publication_year` | Aggregate counts by column |

---

## Filter Syntax

**Basic equality:**
```
?filter=publication_year:2023
```

**Multiple conditions (AND):**
```
?filter=publication_year:2023,type:article,language:en
```

**Comparison operators:**
```
?filter=cited_by_count:>100        # greater than
?filter=cited_by_count:<50         # less than
?filter=cited_by_count:>=100       # greater or equal
?filter=publication_year:!2020     # not equal
```

**Standard operator syntax (alternative):**
```
?filter=cited_by_count:gt:100
?filter=cited_by_count:gte:100
?filter=cited_by_count:lt:50
?filter=cited_by_count:lte:50
?filter=type:ne:dataset
?filter=type:like:article%
?filter=type:in:article,book,review
```

---

## Response Formats

### Standard List Response
```json
{
  "data": [
    {"work_id": 123, "title": "...", "cited_by_count": 500}
  ],
  "pagination": {
    "page": 1,
    "limit": 20,
    "total_rows": 476951866,
    "total_pages": 23847594
  }
}
```

### Cursor Pagination Response
```json
{
  "data": [...],
  "meta": {
    "count": 100,
    "per_page": 100,
    "next_cursor": "eyJzIjpbImNpdGVkX2J5X2NvdW50Il0..."
  }
}
```

### Group By Response
```json
{
  "group_by": [
    {"key": "article", "key_display_name": "article", "count": 256373696},
    {"key": "book", "key_display_name": "book", "count": 7819905}
  ]
}
```

### Output Formats

Set via `Accept` header:

| Format | Accept Header | Use Case |
|--------|---------------|----------|
| JSON | `application/json` (default) | General use |
| CSV | `text/csv` | Spreadsheets |
| Parquet | `application/parquet` | Data analysis (5-10x smaller) |
| Arrow | `application/vnd.apache.arrow.stream` | Zero-copy streaming |

---

## Common Query Patterns

### 1. Get top cited works
```bash
curl -H "X-API-Key: KEY" \
  "https://swemetrics.se/duckdb/api/works?sort=cited_by_count:desc&limit=10&select=work_id,display_name,cited_by_count"
```

### 2. Filter by year and type
```bash
curl -H "X-API-Key: KEY" \
  "https://swemetrics.se/duckdb/api/works?filter=publication_year:2023,type:article&limit=50"
```

### 3. Aggregate by publication year
```bash
curl -H "X-API-Key: KEY" \
  "https://swemetrics.se/duckdb/api/works?group_by=publication_year&filter=type:article"
```

### 4. Aggregate by work type
```bash
curl -H "X-API-Key: KEY" \
  "https://swemetrics.se/duckdb/api/works?group_by=type"
```

### 5. Cursor pagination for large exports
```bash
# First page
curl -H "X-API-Key: KEY" \
  "https://swemetrics.se/duckdb/api/works?cursor=*&limit=1000&sort=work_id:asc"

# Next page (use next_cursor from response)
curl -H "X-API-Key: KEY" \
  "https://swemetrics.se/duckdb/api/works?cursor=eyJzIjpbIndvcmtfaWQiXS...&limit=1000&sort=work_id:asc"
```

### 6. Sparse fieldset (reduce payload)
```bash
curl -H "X-API-Key: KEY" \
  "https://swemetrics.se/duckdb/api/works?select=work_id,display_name,doi,publication_year&limit=100"
```

### 7. Export as Parquet
```bash
curl -H "X-API-Key: KEY" -H "Accept: application/parquet" \
  "https://swemetrics.se/duckdb/api/works?filter=publication_year:2023&limit=10000" \
  -o works_2023.parquet
```

---

## Discovering Tables and Views

Use `GET /api` to list all accessible tables and views:

```bash
curl -H "X-API-Key: KEY" "https://swemetrics.se/duckdb/api"
```

Response:
```json
{
  "tables": [
    {"name": "works", "type": "view", "read_only": true},
    {"name": "authors", "type": "view", "read_only": true},
    {"name": "institutions", "type": "view", "read_only": true}
  ]
}
```

Each entry includes:
- `type`: `"table"` (supports full CRUD) or `"view"` (read-only queries only)
- `read_only`: `true` for views (POST/PUT/DELETE will fail), omitted for tables

Use `GET /api/{name}/columns` to inspect the schema of any table or view.

### Key Tables/Views

| Name | Type | Description | Example Filter |
|------|------|-------------|----------------|
| `works` | view | 476M+ scholarly works | `type:article,publication_year:>2020` |
| `authors` | view | Author profiles and metrics | `cited_by_count:>1000` |
| `institutions` | view | Research institutions | — |
| `works_authorships` | view | Work-author relationships | — |
| `works_topics` | view | Work-topic assignments | — |
| `topics` | view | Topic taxonomy | — |
| `sources` | view | Journals and repositories | — |
| `funders` | view | Funding organizations | — |

*All data in the current deployment is served as read-only views over S3-hosted Parquet files. Use `GET /api` for the full current list.*

---

## Works Schema (Key Columns)

*Use `GET /api/works/columns` for the full schema. Key columns:*

| Column | Type | Description |
|--------|------|-------------|
| `work_id` | integer | Unique identifier |
| `display_name` | string | Title of the work |
| `title` | string | Title (alias) |
| `doi` | string | Digital Object Identifier |
| `publication_year` | integer | Year published |
| `publication_date` | date | Full publication date |
| `type` | string | article, book, dataset, etc. |
| `language` | string | ISO 639-1 code (en, ja, de...) |
| `cited_by_count` | integer | Number of citations |
| `authors_count` | integer | Number of authors |
| `is_oa` | boolean | Open access status |
| `is_retracted` | boolean | Retraction status |
| `has_fulltext` | boolean | Full text available |
| `fwci` | float | Field-Weighted Citation Impact |

> **Citation metrics are power-law distributed.** Median `cited_by_count` = 0 even though mean ≈ 6. The same applies to `fwci` and `referenced_works_count`. Use percentile columns (`q25`, `q50`, `q75` from `summarize`) rather than averages to describe typical works — averages are dominated by a small number of highly-cited outliers.

> **Note on `api_works_food`:** this table contains duplicate columns (`doi_1`, `title_1`, `doi_2`, `title_2`) that are join artifacts in its macro definition. It has a subset of works only relevant for the food industry, focusing on sustainability. Prefer `works` for general queries.

---

## Best Practices for LLMs

### Do
- Start with `GET /api` to discover available tables/views and check `read_only` before attempting writes
- Use `GET /api/{table}/columns` to inspect schema before querying
- Use `select` to request only needed columns (reduces tokens)
- Use `cursor` pagination for iterating large datasets
- Use `group_by` for aggregations instead of fetching all rows
- Set reasonable `limit` values (100-1000)
- Use Parquet format for data analysis pipelines
- Use `schema(table_pattern=..., compact=true)` — always pass a pattern; 57 tables unfiltered produces a very large response
- Use `row_counts()` to understand table sizes before joining — several tables exceed 1 billion rows

### Don't
- Don't use random `page` numbers for sampling
- Don't fetch all columns when you only need a few
- Don't paginate beyond page 100 with offset pagination (use cursor instead)
- Don't request unlimited results without filters
- **Don't join `works_referenced_works` (3B rows), `works_keywords` (2.9B), `works_related_works` (2.5B), or `works_topics` (906M) without a filtering CTE first** — naive joins will time out
- **Don't treat citation averages as typical** — `cited_by_count`, `fwci`, and `referenced_works_count` are heavily right-skewed (median = 0 even though mean ≈ 6); use percentiles (`q50`, `q75`) rather than averages to characterise "typical" works

---

## Responsible Bibliometrics (SUHF 2025)

Swedish higher education institutions are guided by the SUHF working group's *Råd för
utvärderande bibliometri i Sverige* (August 2025), which complements DORA, CoARA/ARRA,
and the Leiden Manifesto. When helping with bibliometric analyses, apply the following:

### Indicator choice

| Task | Use | Avoid |
|------|-----|-------|
| Compare citation impact across fields | `fwci`, `citation_normalized_percentile` | Raw `cited_by_count`, JIF, h-index |
| Describe a typical work | `q50`, `q75` (from `summarize`) | `AVG(cited_by_count)` — median = 0, mean ≈ 6 |
| Compare institutions or research groups | Field-normalized percentiles | Unnormalized totals |
| Trend analysis | Year-by-year series from `works_counts_by_year` | Single-point snapshots |

> **Never use Journal Impact Factor (JIF) or h-index to compare research across different
> fields** (SUHF Rec 9, Leiden Manifesto principle 6). These indicators do not account for
> field-specific publication and citation norms.

### Sample size

- Analyses based on **fewer than 50 publications** are unreliable — state this explicitly and
  interpret with caution (SUHF Rec 6, 10).
- Always include `COUNT(*) AS n_works` alongside citation indicators so the reader can judge
  reliability.
- Use `row_counts()` before building analyses to understand the scale of each table.

### Reporting

- Report **medians and percentiles** (`q25`, `q50`, `q75`) rather than means for all citation
  metrics — the distribution is heavily right-skewed (SUHF Rec 6).
- State the **time window and data source** for every analysis (SUHF Rec 7, 10).
- Avoid false precision: round indicators to 2 significant figures unless exact values are
  needed; note that `fwci` and percentile values depend on the OpenAlex snapshot date
  (SUHF Rec 11).
- Do not directly compare indicator values from different analyses, time points, or data
  snapshots without noting the incomparability (SUHF Rec 12).

### Context

- Bibliometric data describes *some* aspects of research output — it cannot replace expert
  judgement or peer review (SUHF Rec 4, Leiden Manifesto principle 1).
- Publication and citation norms differ strongly by field. Swedish social science and
  humanities have lower citation counts than medicine or physics — use the `topics` and
  `primary_topic` columns to identify field context before interpreting raw numbers.
- Be aware that evaluation models based on bibliometrics can be gamed. If asked to help
  design such a model, note this risk (SUHF Rec 1).

### swemetrics columns for responsible reporting

| Column | Table | Use for |
|--------|-------|---------|
| `fwci` | `works` | Field-weighted citation impact (normalized) |
| `citation_normalized_percentile` | `works_citation_normalized_percentile` | Percentile rank within field+year |
| `cited_by_percentile_year` | `works_cited_by_percentile_year` | Percentile within publication year |
| `counts_by_year` | `works_counts_by_year` | Citation trends over time |
| `primary_topic` | `works_primary_topic` | Field classification for context |

---

## Error Handling

| Code | Meaning | Action |
|------|---------|--------|
| 400 | Bad request | Check query syntax |
| 401 | Unauthorized | Provide valid API key |
| 403 | Forbidden | Insufficient permissions for operation |
| 404 | Not found | Table doesn't exist |
| 429 | Rate limited | Implement exponential backoff |
| 500 | Server error | Retry with backoff |
| 503 | Service unavailable | Export directory not configured on server |

**Recommended retry strategy:** 1s, 2s, 4s, 8s delays, max 5 attempts.

**MCP tool errors** are returned as text content prefixed with `"Error: "` rather than HTTP error codes — always check the content string, not just the HTTP status.

---

## Python Example

```python
import requests

API_KEY = "your_api_key"
BASE_URL = "https://swemetrics.se/duckdb"

def query_works(filters=None, sort=None, limit=100, select=None):
    params = {"limit": limit}
    if filters:
        params["filter"] = filters
    if sort:
        params["sort"] = sort
    if select:
        params["select"] = select

    response = requests.get(
        f"{BASE_URL}/api/works",
        headers={"X-API-Key": API_KEY},
        params=params
    )
    return response.json()

# Get top 10 cited articles from 2023
results = query_works(
    filters="publication_year:2023,type:article",
    sort="cited_by_count:desc",
    limit=10,
    select="work_id,display_name,cited_by_count"
)

for work in results["data"]:
    print(f"{work['display_name']}: {work['cited_by_count']} citations")
```

---

## SQL Access (Analyst+)

Analysts and admins can execute arbitrary DuckDB SQL:

```bash
curl -X POST -H "X-API-Key: ADMIN_KEY" \
  -H "Content-Type: application/json" \
  -d '{"sql": "SELECT type, COUNT(*) as n FROM works GROUP BY type ORDER BY n DESC LIMIT 5"}' \
  "https://swemetrics.se/duckdb/query"
```

---

## Comparison with OpenAlex API

This API is intentionally compatible with [OpenAlex](https://docs.openalex.org) query patterns:

| Feature | OpenAlex | SweMetrics |
|---------|----------|------------|
| Filter syntax | `filter=year:2023` | `filter=publication_year:2023` |
| Comparison ops | `cited_by_count:>100` | `cited_by_count:>100` |
| Pagination | cursor only | cursor + offset |
| Group by | `group_by=year` | `group_by=publication_year` |
| Select fields | `select=id,title` | `select=work_id,title` |
| Output formats | JSON, CSV | JSON, CSV, Parquet, Arrow |
| Raw SQL | Not available | Available (admin) |

---

## Rate Limits

Contact administrator for rate limit details. Implement client-side rate limiting for bulk operations.

---

## Support

- **OpenAPI Spec:** https://swemetrics.se/duckdb/openapi.json
- **Interactive Docs:** https://swemetrics.se/duckdb/docs
- **Health Check:** https://swemetrics.se/duckdb/health