> ## Documentation Index
> Fetch the complete documentation index at: https://docs.bensevern.dev/docs/llms.txt
> Use this file to discover all available pages before exploring further.
# SQL extensions
> Run GoldenMatch fuzzy matching directly inside PostgreSQL and DuckDB.
The `goldenmatch-extensions` package runs GoldenMatch directly from SQL, without leaving the database. It ships a pgrx-based PostgreSQL extension and a DuckDB UDF package.
[](https://pypi.org/project/goldenmatch-duckdb/)
[](https://pypi.org/project/goldenmatch-duckdb/)
[](https://pypi.org/project/goldenmatch-embed/)
[](https://github.com/benseverndev-oss/goldenmatch/releases?q=goldenmatch-pg)
## PostgreSQL
### Install
The fastest path is the prebuilt Docker image with the extension preinstalled:
```bash theme={null}
docker run -p 5432:5432 -e POSTGRES_PASSWORD=postgres \
ghcr.io/benseverndev-oss/goldenmatch-extensions:latest
psql -h localhost -U postgres \
-c "SELECT goldenmatch.goldenmatch_score('John', 'Jon', 'jaro_winkler');"
```
Prebuilt release tarballs (Linux x86\_64, PostgreSQL 15/16/17) are attached to
each [`goldenmatch-pg-v*` release](https://github.com/benseverndev-oss/goldenmatch/releases?q=goldenmatch-pg).
Each tarball unpacks a pgrx package tree (the `.so`, the `.control`, and the SQL
files) that you copy into your PostgreSQL install:
```bash theme={null}
# Pick the latest goldenmatch-pg-v* release and your PG major version.
VERSION=0.15.0 # latest goldenmatch-pg-v* tag
PG=16 # your PostgreSQL major (15, 16, or 17)
curl -LO "https://github.com/benseverndev-oss/goldenmatch/releases/download/goldenmatch-pg-v${VERSION}/goldenmatch_pg-${VERSION}-pg${PG}-linux-x86_64.tar.gz"
tar -xzf "goldenmatch_pg-${VERSION}-pg${PG}-linux-x86_64.tar.gz"
# The tree mirrors the PG install prefix -- copy it over pg_config's dirs.
sudo cp -r "goldenmatch_pg-pg${PG}"/* /
# The extension embeds CPython and calls goldenmatch, so install it too:
pip install goldenmatch
```
Other platforms build from source (`cargo pgrx install`) -- see
[`packages/rust/extensions/CLAUDE.md`](https://github.com/benseverndev-oss/goldenmatch/blob/main/packages/rust/extensions/CLAUDE.md).
Then enable it:
```sql theme={null}
CREATE EXTENSION goldenmatch_pg;
SELECT goldenmatch.goldenmatch_score('John Smith', 'Jon Smyth', 'jaro_winkler');
```
### Functions
```sql theme={null}
-- Score two strings
SELECT goldenmatch_score('John Smith', 'Jon Smyth', 'jaro_winkler'); -- 0.91
-- Score two JSON records against a config
SELECT goldenmatch_score_pair(
'{"name": "John Smith", "email": "j@x.com"}',
'{"name": "Jon Smyth", "email": "j@x.com"}',
'{"fuzzy": {"name": 0.85}, "exact": ["email"]}'
); -- 0.95
-- Explain a match
SELECT goldenmatch_explain(rec_a, rec_b, config);
-- Whole-table operations
SELECT goldenmatch_dedupe_table('customers', '{"exact": ["email"]}');
SELECT goldenmatch_match_tables('prospects', 'customers', '{"fuzzy": {"name": 0.85}}');
```
## DuckDB
```bash theme={null}
pip install goldenmatch-duckdb
```
```python theme={null}
import duckdb
import goldenmatch_duckdb
con = duckdb.connect()
goldenmatch_duckdb.register(con)
con.sql("SELECT goldenmatch_score('John Smith', 'Jon Smyth', 'jaro_winkler')").show()
con.sql("SELECT goldenmatch_dedupe_table('customers', '{\"exact\": [\"email\"]}')").show()
```
Registered UDFs:
| Function | Purpose |
| -------------------------------------------------- | ------------------------------------------------------------------------------------------------ |
| `goldenmatch_score(a, b, scorer)` | Score two strings. |
| `goldenmatch_score_pair(rec_a, rec_b, config)` | Score two JSON records. |
| `goldenmatch_explain(rec_a, rec_b, config)` | Explain a match. |
| `goldenmatch_dedupe_table(table, config)` | Deduplicate a table. |
| `goldenmatch_match_tables(target, ref, config)` | Match two tables (JSON output). |
| `goldenmatch_match_pairs(target, ref, config)` | Match two tables, returns `(target_id, reference_id, score)` rows (Postgres). |
| `goldenmatch_autoconfig(table, mode)` | Derive a config from a table; `mode` = `'standard'` or `'probabilistic'` (Fellegi-Sunter). |
| `goldenmatch_dedupe(json, config)` | Deduplicate JSON records directly. |
| `goldenmatch_match(target_json, ref_json, config)` | Match two JSON record sets. |
| `goldenmatch_connected_components(...)` | Group a candidate-pair graph into entities. |
| `goldenmatch_pair_dedup(...)` | Keep the best score per canonical pair. |
| `goldenmatch_embed_local(text, model_path)` | Embed text with a local in-house model. |
| `gm_embed(text)` (PostgreSQL) | Embed text with the in-house model, dir from `GOLDENEMBED_MODEL_DIR`. |
| `goldenmatch_docs()` | Return this surface's `llms.txt` — what the functions are and where the authoritative docs live. |
### Orientation from inside a session
```sql theme={null}
SELECT goldenmatch_docs();
```
A SQL connection is the surface with the least to read: no filesystem, no package
to import, just function names. `goldenmatch_docs()` returns the packaged
`llms.txt` so a tool — or an AI agent — can find the authoritative documentation
instead of inferring behaviour from call signatures.
It takes no arguments and depends on nothing else in the extension. On PostgreSQL
the text is compiled into the shared library, so the function answers even in a
backend where the Python bridge fails to initialise.
## Graph and embedding kernels
These run native-direct in pure Rust, with no CPython round-trip. They expose GoldenMatch's clustering primitives and the local embedder directly in SQL, on both backends (and as DataFusion FFI UDFs). One shared kernel backs all surfaces, so results are identical across them.
### Connected components and pair dedupe
`goldenmatch_connected_components` groups a candidate-pair graph into entities, one component per entity, with singletons included. `goldenmatch_pair_dedup` canonicalizes a candidate-pair set and keeps the best score per pair. Both take the edge columns as lists. Pass integer record ids to the bare name, or string ids to the `_str` sibling.
```sql DuckDB theme={null}
-- Components over an edge set plus the id universe
SELECT goldenmatch_connected_components(
(SELECT list(id_a) FROM edges),
(SELECT list(id_b) FROM edges),
(SELECT list(score) FROM edges),
(SELECT list(id) FROM records)
); -- [[id, ...], ...]
-- Canonical max-score pairs
SELECT goldenmatch_pair_dedup(
(SELECT list(id_a) FROM pairs),
(SELECT list(id_b) FROM pairs),
(SELECT list(score) FROM pairs)
); -- [{a, b, s}, ...]
-- String record ids use the _str variants
SELECT goldenmatch_connected_components_str(
['a', 'b'], ['b', 'c'], [0.9, 0.8], ['a', 'b', 'c', 'd']
);
```
```sql PostgreSQL theme={null}
-- Components: returns (component, member) rows
SELECT * FROM goldenmatch.goldenmatch_connected_components(
ARRAY[1, 2], ARRAY[2, 3], ARRAY[0.9, 0.8], ARRAY[1, 2, 3, 4]
);
-- Canonical max-score pairs: returns (a, b, s) rows
SELECT * FROM goldenmatch.goldenmatch_pair_dedup(
ARRAY[2, 1], ARRAY[1, 2], ARRAY[0.5, 0.9]
);
```
### Local embedding
`goldenmatch_embed_local` embeds text with a saved in-house model through the `goldenembed` ONNX runtime. No network and no API key. `model_path` is a directory holding `config.json` and `model.onnx`.
```sql DuckDB theme={null}
SELECT goldenmatch_embed_local('John Smith', '/path/to/model'); -- JSON float array
```
```sql PostgreSQL theme={null}
SELECT goldenmatch.goldenmatch_embed_local('John Smith', '/path/to/model'); -- double precision[]
```
On PostgreSQL, `gm_embed(text)` is a one-argument convenience that reads the model directory from the `GOLDENEMBED_MODEL_DIR` environment variable instead of taking it per call, and returns `real[]` (float4) to match the DataFusion `goldenmatch_embed` UDF. The model loads once per backend process and is cached. A `NULL` input embeds the empty string rather than returning `NULL`.
```sql PostgreSQL theme={null}
-- Set GOLDENEMBED_MODEL_DIR in the server environment first.
SELECT goldenmatch.gm_embed('John Smith'); -- real[]
```
The DuckDB embedding UDF needs the optional embed runtime: `pip install goldenmatch-duckdb[embed]`.
## Identity graph (stateful, PostgreSQL)
PostgreSQL can maintain a durable, event-sourced **identity graph** in-database:
stable entity ids that survive across runs, incremental absorb of new records,
steward corrections, a tamper-evident audit log, and MDM operator views. This is
Postgres-only (it needs a durable multi-connection store); DuckDB stays the
stateless dedupe surface.
Point the extension at the database it runs in with a superuser GUC (or the
`GOLDENMATCH_IDENTITY_DSN` / `GOLDENMATCH_DATABASE_URL` server env), then resolve
a table into a named dataset:
```sql theme={null}
ALTER SYSTEM SET goldenmatch.identity_dsn = 'postgresql://user:pass@localhost/mydb';
SELECT pg_reload_conf();
SELECT goldenmatch.gm_configure('idjob',
'{"matchkeys":[{"name":"email","type":"exact","fields":[{"field":"email","scorer":"exact"}]}],"identity":{"source_pk_column":"id"}}');
-- Resolve a table into the in-DB identity dataset (create / absorb / merge,
-- incremental across runs). Re-running absorbs new rows into existing ids.
SELECT goldenmatch.gm_resolve('idjob', 'customers', 'people');
```
The read functions serve that in-DB dataset when `db_path` is empty (pass a
SQLite path or a libpq DSN to read an external store instead). Every function
returns JSON identical to the MCP / REST / CLI surfaces.
| Function | Purpose |
| ------------------------------------------------------------------------------- | ------------------------------------------------------- |
| `gm_resolve(job, table, dataset)` | Resolve a table into the dataset (create/absorb/merge) |
| `goldenmatch_identity_resolve(record_id, db_path)` | Resolve a `{source}:{pk}` record to its entity |
| `goldenmatch_identity_view(entity_id, db_path)` | Full identity view + event log |
| `goldenmatch_identity_history(entity_id, db_path)` | Temporal event log for one identity |
| `goldenmatch_identity_list(dataset, status, db_path)` | List identities (empty filters = all) |
| `goldenmatch_identity_conflicts(dataset, db_path)` | Open `conflicts_with` edges |
| `gm_identity_profile(entity_id, db_path)` | MDM profile of one entity (sources, records, version) |
| `gm_identity_stats(dataset, db_path)` | Graph-level health summary |
| `gm_identity_worklist(dataset, db_path)` | Prioritized steward queue (conflicts / weak confidence) |
| `gm_identity_merge(dataset, entity_a, entity_b)` | Steward merge (keep A, absorb B) |
| `gm_identity_split(dataset, entity_id, record_id)` | Steward split a record into a fresh identity |
| `gm_identity_claim(entity_id, record_id, reason)` | Steward claim a record into an entity |
| `gm_identity_resolve_conflict(dataset, record_a, record_b, resolution, reason)` | Steward verdict (`same`/`distinct`/`defer`) |
| `gm_identity_audit(dataset, db_path)` | Append-only audit-log page |
| `gm_identity_audit_seal(dataset)` | Anchor the audit log with a new tamper-evidence seal |
| `gm_identity_audit_verify(dataset, db_path)` | Replay the seal chain and report integrity |
```sql theme={null}
-- Steward workflow: profile an entity, seal + verify the audit chain.
SELECT goldenmatch.gm_identity_profile('', ''); -- '' = the in-DB dataset
SELECT goldenmatch.gm_identity_audit_seal(''); -- '' = the global chain
SELECT goldenmatch.gm_identity_audit_verify('', ''); -- {"ok": true, ...}
```
The write functions (`gm_resolve`, `gm_identity_merge` / `_split` / `_claim`,
`gm_identity_resolve_conflict`, `gm_identity_audit_seal`) commit on the store's
own connection to the configured DSN, not the caller's SQL transaction; replay
is idempotent. They require `goldenmatch.identity_dsn` (or the env fallback) to
be set. Empty optional args (`dataset`, `reason`) mean "unset".
## Requirements
* Python 3.11+
* `goldenmatch >= 1.1.0`
* DuckDB 1.0+ (DuckDB extension)
* PostgreSQL 15, 16, or 17 (Postgres extension)
The scoring and table operations embed CPython through pyo3 and call the GoldenMatch Python API, so they match the Python package exactly. The graph and embedding kernels run native-direct in pure Rust with no CPython, sharing one kernel across DuckDB, PostgreSQL, and DataFusion.