# Flowleap Patstat

> Portfolio Analytics AND guarded SQL over the PATSTAT snapshot — structured-criteria aggregation by named applicant, CPC/IPC class, office, year, family, and grant status, with harmonized entity resolution and Data Edition provenance; plus agent-written SELECTs against the flowleap.* semantic views for any aggregate the typed commands don't cover (landscapes, grant rates, citation impact, inventor analytics). Trigger when an agent needs a named applicant's filing portfolio, structured-criteria corpus counts (not free-text search), any other PATSTAT aggregate, or any number that must carry a PATSTAT edition citation.

- Skill: `flowleap-ai/flowleap-patstat` (Agent Skill)
- Install (CLI): `npx skillmds@latest add flowleap-ai/flowleap-patstat`
- Raw SKILL.md: https://api.skillmd.com/api/skills/flowleap-ai/flowleap-patstat/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Data & Analytics
- Author: flowleap-ai (https://skillmd.com/u/flowleap-ai)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/flowleap-ai/flowleap-patstat

---


# FlowLeap Patstat (Portfolio Analytics)

Auth and global flags: see `flowleap-shared`.

PATSTAT is a **named non-facade exception**: it keeps its own surface instead of
running on the Tools facade, and needs no patent-data key. Nothing here was
touched by the provider-route retirement.

## Which engine? — the three-way routing rule

FlowLeap runs three analytics engines, split by *criteria shape*, not by
metric:

- **Topic Analytics** (`flowleap analytics`, the Google-Patents corpus
  engine) — the question's essential criterion is **free-text keywords** over
  title/abstract ("quantum computing filings over time"). Publication-level
  counts, substring name matching, per-query cost.
- **Portfolio Analytics** (`flowleap patstat`, this skill, the PATSTAT
  engine) — the question is expressible in **structured criteria**: named
  applicant (entity-resolved, harmonized names), CPC/IPC class, office, year,
  family, grant status. Family-level counting, zero marginal cost.
- **Graph Analytics** (`flowleap patstat graph …` → `flowleap-patstat-graph`)
  — the question is about **a named node and the relationships around it**:
  who cites EP3477840, the citation/family path between two patents, where a
  family has coverage, an applicant's co-applicant network. Typed nodes and
  edges, each with a confidence tag and row-level provenance.

Routing rule: free text → `flowleap analytics`; structured criteria,
especially a named company → `flowleap patstat`; a *connection* rather than a
count → `flowleap-patstat-graph`. If the answer is a table of counts it is
here; if it is who-links-to-what, it is traversal — go across. Individual
documents (one known publication or application) are none of the three — use
the search/retrieval skills (`flowleap-patent`, `flowleap-uspto`,
`flowleap-ops`).

**Keyless, but not a stand-in.** PATSTAT needs no patent-data key, so it stays
live when EPO OPS or USPTO ODP answers `provider_keys_required`. You may offer it
to keep work moving — framed for what it is: aggregate counts from a
twice-yearly snapshot, not documents and not current. It never answers "what
prior art exists for this claim", and a PATSTAT table never closes a missing-key
gap in a prior-art, FTO, or invalidity deliverable. See `flowleap-keys`.

Note that `patstat portfolio` and `graph applicant` draw entity boundaries
differently: `portfolio` groups by name-prefix aliases, `graph applicant`
takes one harmonized `psn_id`. They may disagree about where one company ends
and another begins — always say which produced a number.

## Portfolio

```bash
flowleap --json patstat portfolio "Siemens AG" --from-year 2015 --to-year 2023
```

Response shape: a quotable `summary` line first — relay it verbatim before
adding any narrative — then filings-by-year/office/grant-status aggregate
tables, then a `data_edition` provenance line.

## Ambiguous applicant (422)

An unresolved applicant name returns HTTP 422 with a candidate list. This is
an **interaction step, not a retryable error**: render every candidate to the
user in both `--json` and human output, and **never auto-pick one**. Once the
user picks, re-run with the exact candidate name and pin that exact string —
a caller that needs to repeat the query (e.g. a `recipe-custom-dashboard`
script) hard-codes the resolved name as a constant so the choice is made once,
not re-asked on every run.

## Data Edition

PATSTAT is published in discrete snapshot editions (~twice a year). Every
Portfolio Analytics answer carries its `data_edition` — treat Portfolio
Analytics as a snapshot with a name, not live data. Two answers are only
comparable within the **same** `data_edition`; always surface the edition
alongside any number quoted from this skill.

## Guarded SQL (Layer 2) — aggregates beyond the typed commands

For aggregate questions no typed command answers — technology landscapes by
CPC ("who dominates solid-state electrolytes"), grant rates, citation-impact
rankings, inventor analytics, family/jurisdiction coverage — write **one SQL
SELECT** against the `flowleap.*` semantic views and run it through the
deterministic backend gate (single-SELECT parse check, flowleap-only
allowlist, EXPLAIN cost ceiling, 5,000-row/5 MB hard caps, 20 s timeout;
budget 10 queries/min).

The mandatory workflow, in order:

1. **Examples first — don't write SQL you don't need:**

   ```bash
   flowleap patstat docs --section examples
   ```

   Verified question→SQL pairs. If one matches, reuse its SQL; if it carries
   `promoted_to`, use that typed command/endpoint instead.

2. **Fetch the schema and conventions — never work from memory:**

   ```bash
   flowleap patstat docs --section semantic-model
   ```

   The served YAML is the single authoritative source: logical views and
   columns, metric formulas, join paths, caveats, and the
   `interpretation_conventions` block (default counting units and year
   bases, the ask-when-material rule). Apply it as served — this skill
   deliberately does not restate it, so it can never drift.

3. **Run, always sending the user's question verbatim** (it feeds the
   query-review pipeline that turns good queries into verified examples):

   ```bash
   flowleap patstat query "SELECT office, COUNT(DISTINCT family_id) AS inventions FROM flowleap.applications a JOIN flowleap.applicants ap ON ap.application_id = a.application_id WHERE UPPER(ap.name) LIKE 'SIEMENS%' GROUP BY office ORDER BY inventions DESC" --question "where does Siemens hold the most inventions?"
   ```

   Schema-qualify every table as `flowleap.<view>`. No LIMIT needed — the
   backend caps rows and errors (never truncates) past the cap.

4. **On a `patstat_sql_*` error, fix ONCE, then stop.** The error message
   carries the exact parser/Postgres detail plus the recovery instruction —
   follow it, re-run with `--retry-of <code>`, and after a second failure
   report the error instead of looping. `patstat_busy` is different: back
   off a few seconds and retry the SAME SQL — it is load, not a SQL problem.

5. **Present with the interpretation stated** ("counted as DOCDB families by
   earliest filing year") and the `data_edition` named. Surface any
   `patstat_sql_expensive` warning as a heaviness note. Full step-by-step:
   `flowleap patstat docs --workflow guarded-sql`.

Entity disambiguation in guarded SQL: no 422 here — probe candidates with a
cheap `SELECT name … LIKE 'X%' GROUP BY name` query first, and apply the same
never-auto-pick rule as the portfolio flow when candidates diverge.

## patstat_unavailable

If the backend has no PATSTAT database configured, it returns a
`patstat_unavailable` error. Say so plainly ("backend has no PATSTAT dataset
configured") and stop — this is a deployment gap, not a transient failure; do
not retry.

Also available as `flowleap tools run patstat_portfolio …` once the backend
tool-registry entry lands — see `flowleap-tools`.

```bash
flowleap --json tools run patstat_portfolio applicant="<applicant name>"
```