# Postgres Extension Integrations — Plan

This file enumerates Postgres extensions that pg_mentat **could**
integrate with, in the same opt-in / soft-dependency shape as the
already-shipped pg_tre integration. Each entry says what it adds, the
integration shape (Datalog where-fn? new value type? helper SQL? index
type on narrow tables?), license/build compatibility, effort, and
whether it should land.

This is a planning document, not a marketing list. Extensions that don't
add real Datalog value have been excluded. The file is the source of
truth for "what integrations does pg_mentat ship?" — when an integration
lands, mark it `Done` and link to the cookbook page.

## Reference: the pg_tre integration (already shipped)

Use this as the template for everything below.

- **Soft dependency.** `CREATE EXTENSION pg_mentat` does not require
  pg_tre. `mentat.has_pg_tre()` returns `true` when the extension is
  loaded; helper functions error with a specific `:db.error/*` code
  pointing at install instructions when it isn't.
- **One Datalog where-fn.** `(fuzzy-match $ :attr "pattern" k)` mirrors
  the shape of `(fulltext ...)` and slots into the same dispatch
  (`build_fuzzy_match_join` next to `build_fulltext_join`).
- **One helper SQL.** `mentat.create_tre_index('<:attr>')` builds a
  partial pg_tre index on the right narrow table.
- **Tests.** Happy path and every error path, gated on
  `mentat.has_pg_tre()` so the suite passes whether or not the test
  cluster has the extension installed.
- **Docs.** `docs/src/fuzzy-search.md` covers prerequisites, worked
  example, error table, explicit non-features.

Total cost: ~600 lines of Rust + SQL + docs + tests. One commit, one
review pass. This is the cost ceiling for a single integration.

---

## Tier 1 — high-value, well-scoped, should land

### pgvector — `:db.type/vector` + `(nearest-neighbor ...)`
**Status:** Open. **Effort:** 2 weeks. **Maintainer:** pgvector/pgvector. **License:** PostgreSQL.

The single most valuable integration in 2026. Nothing else in the
Postgres-Datalog space stores embeddings as datoms.

Integration shape:

- New value type `:db.type/vector`. Encodes dimension as part of the
  attribute schema (`:db/vector-dim 1536`); rejects insert of mismatched
  vectors with `:db.error/wrong-vector-dim`.
- New tenth narrow table `mentat.datoms_vector_new (store_id BIGINT, e
  BIGINT, a BIGINT, v vector, tx BIGINT, added BOOLEAN)`. Per-attribute
  HNSW or IVFFlat index via a new helper
  `mentat.create_vector_index('<:attr>', 'hnsw'|'ivfflat', '<opts>')`.
- Datalog where-fn `(nearest-neighbor $ :attr ?vec k)` returning the k
  nearest entities by cosine distance. Distance op is configurable per
  index (cosine / L2 / inner-product); pick from the attribute's index
  metadata.
- The `:db/valueType` enum gets a new entry: `:db.type/vector`.
  Existing bootstrap entids 70–78 are taken; assign `:db.type/vector`
  entid 79 (free).

Hard parts:

- New value type means new code paths in `transact.rs` (encoding),
  `query.rs` (the UNION subquery, the value-decode CASE, predicate
  comparison), `pull.rs` (decode), the dual-write trigger if it still
  exists by the time we land this. ~150–200 line touchpoints.
- Storing dimension in the attribute schema requires either a new
  `:db/vector-dim` meta-attribute (preferred) or schema
  `value_type` becoming `value_type | NULL` plus a new column. Pick
  the meta-attribute approach.

Done criteria:

```
SELECT mentat.t('[
  {:db/ident :doc/embedding
   :db/valueType :db.type/vector
   :db/vector-dim 1536
   :db/cardinality :db.cardinality/one}]');
SELECT mentat.create_vector_index(':doc/embedding', 'hnsw',
                                   'm=16,ef_construction=64');
SELECT mentat.q('[:find ?e ?dist
                  :where [(nearest-neighbor $ :doc/embedding
                           [0.0,0.0,...] 10) [[?e ?dist]]]]');
```

### PostGIS — `:db.type/geometry` + `(within ...)` / `(near ...)`
**Status:** Open. **Effort:** 2–3 weeks. **Maintainer:** OSGeo/postgis. **License:** GPL-2.0.

PostGIS is the most-used Postgres extension after pgcrypto. Geographic
data in EAV is a common shape (places-of-business, asset locations,
event coordinates).

Integration shape:

- New value types `:db.type/geometry` and `:db.type/geography`.
- New eleventh and twelfth narrow tables for each (or one shared with a
  type-tag column; pick whichever fits better with PostGIS's GiST index
  semantics).
- Per-attribute GiST index via `mentat.create_spatial_index('<:attr>')`.
- Datalog where-fns:
  - `(within $ :attr ?shape)` — datoms whose value is within the given
    geometry. Compiles to `ST_Within`.
  - `(near $ :attr ?point ?radius_m)` — within radius (meters), uses
    `<->` operator on the GiST index for k-NN ordering when used with
    `:order`.
  - `(distance $ :attr ?point) ?d` — bind ?d to the meters distance
    for use in predicates.

Hard parts:

- GPL-2.0 license. pg_mentat is Apache-2.0. PostGIS is a runtime
  dependency, not a derivative-work issue, so this is fine for the
  integration; document the license boundary.
- WKT vs WKB input. Decide one canonical form for EDN-encoded geometry
  literals. Probably WKT inside a `#geom "POINT(1 2)"` tagged literal,
  with a constructor function `mentat.geom('WKT')` for SQL callers.

Done criteria: a cookbook page that does point-in-polygon queries
mixing datoms and a GeoJSON polygon parameter.

### TimescaleDB — `mentat.transactions` and `datoms_instant_new` as hypertables
**Status:** Open. **Effort:** 1 week. **Maintainer:** timescale/timescaledb. **License:** Apache-2.0 (TSL parts: TSL).

The transaction log and the instant-typed datom table are both
naturally time-partitioned. Timescale-style hypertables would give:

- Automatic time-bucketed partitioning with proper chunk pruning.
- Continuous aggregates for time-series Datalog queries (count of
  retractions per hour, per attribute).
- `time_bucket` available inside generated SQL for queries that group
  by time window.

Integration shape:

- Helper SQL `mentat.enable_timescale(target text)` where `target ∈
  {'transactions', 'datoms_instant_new', 'all'}`. Calls
  `create_hypertable` with sensible chunk-time-interval defaults
  (probably one week).
- A `mentat.transaction_window(?from, ?to)` Datalog where-fn that
  generates SQL using `time_bucket` when the underlying table is a
  hypertable, falling back to a plain range scan otherwise.
- No new value types or narrow tables. Pure operational integration.

Hard parts:

- Hypertables impose constraints on referenced rows (no foreign keys
  pointing into them, etc.). Verify the dual-write VIEW on
  `mentat.datoms` still works once `datoms_instant_new` is a
  hypertable.
- TimescaleDB's TSL license vs the Apache-2.0 community edition. We
  should target the community edition to avoid licensing surprises.

Done criteria: `enable_timescale('transactions')` succeeds; a
`time_bucket('1 day', tx_instant)` query against `mentat.transactions`
runs against chunks; benchmark shows retraction-window queries scale
linearly with window size, not total tx count.

### fuzzystrmatch — `(soundex ...)`, `(levenshtein ...)`, `(metaphone ...)`
**Status:** Open. **Effort:** 1–2 days. **Maintainer:** PostgreSQL contrib. **License:** PostgreSQL.

Built-in `contrib` module. PG13+. No `shared_preload_libraries`. No
runtime cost when not used. The cheap, no-friction alternative to
pg_tre for name-search and other typo-tolerant queries on PG13–17.

Integration shape:

- Three new Datalog where-fns:
  - `[(levenshtein ?a ?b) ?d]` — bind ?d to the integer edit distance
    between two text values. Pure function, can be used in predicates
    (e.g. `[(< ?d 3)]` for "within 2 edits").
  - `[(soundex ?s) ?code]` — bind ?code to the Soundex hash for
    name matching.
  - `[(metaphone ?s ?max) ?code]` — bind ?code to the Metaphone
    phonetic encoding.
- All three compile to function calls with `extension_required` =
  `fuzzystrmatch`. Helper `mentat.has_fuzzystrmatch()` returns true if
  the extension is loaded.
- No index needed; these are scalar functions, called per row.

Hard parts: none. Smallest integration on the list.

### pg_partman — declarative partition management of retraction tombstones
**Status:** Open. **Effort:** 1 week. **Maintainer:** pgpartman/pg_partman. **License:** PostgreSQL.

Operational rather than feature work. Long-running pg_mentat
deployments accumulate retracted-datom tombstones (`added = false`
rows) that grow indefinitely. pg_partman partitions a parent table by
range/list/hash and runs maintenance jobs to drop old partitions or
move them to cheap storage.

Integration shape:

- Per-narrow-table opt-in: `mentat.enable_partman(table_name,
  partition_by, retention)`. For example:
  ```
  SELECT mentat.enable_partman('datoms_instant_new', 'tx', '90 days');
  ```
  partitions `datoms_instant_new` by tx range and drops partitions
  older than 90 days of tx history.
- The narrow tables already have `(store_id, e, a, tx)` primary keys;
  `tx` is monotonic so range-partitioning by `tx` is straightforward.
- A pg_cron job (see entry below) runs `partman.run_maintenance()` on
  a schedule.

Hard parts:

- Excision (the GDPR "forget me" path) and partition drops have to be
  coordinated; otherwise an excise call may target a partition that's
  about to be dropped, or vice versa. Document the order.
- Existing data in non-partitioned narrow tables has to be migrated
  in-place — pg_partman has tooling for this but it's not free.

Done criteria: partman maintenance on `datoms_instant_new` drops
partitions older than the configured retention; a benchmark shows
storage growth flattens after the retention window.

---

## Tier 2 — useful and reasonably scoped, should land after Tier 1

### pg_trgm — `(similar-to ...)` where-fn
**Status:** Open. **Effort:** 3–5 days. **License:** PostgreSQL (contrib).

Built-in. Trigram similarity (set-based, not edit-distance). Different
semantics from pg_tre and from fuzzystrmatch's Levenshtein — ranks by
overlap of trigrams, not by edit distance. Useful for "rows whose
attribute is *kind of like* this string" queries.

Shape:
- `[(similar-to $ :attr "needle" threshold) [[?e ?val ?score]]]`.
- Helper `mentat.create_trgm_index('<:attr>')` creates a partial
  `gin_trgm_ops` or `gist_trgm_ops` index on the appropriate narrow
  table.
- Coexists with `(fulltext ...)` and `(fuzzy-match ...)`. Document the
  distinction in `docs/src/text-search-comparison.md`.

### pg_jsonschema — `:db/jsonschema` constraint
**Status:** Open. **Effort:** 3–5 days. **Maintainer:** supabase/pg_jsonschema. **License:** Apache-2.0.

Validates JSON against a JSON Schema. pgrx-based, like pg_mentat.

Shape:
- New attribute-schema option `:db/jsonschema "<schema-text>"`.
- On `mentat_transact`, when an attribute has `:db/jsonschema` set and
  the value is a string, validate the parsed JSON against the schema
  before inserting. Reject with `:db.error/jsonschema-violation`.
- Document why this isn't `:db/predicate "<EDN expression>"`: JSON
  Schema is an industry standard with well-defined semantics, EDN
  predicates aren't.

### pg_cron — scheduled compaction and maintenance
**Status:** Open. **Effort:** 2–3 days. **Maintainer:** citusdata/pg_cron. **License:** PostgreSQL.

Schedule periodic operations: VACUUM on hot narrow tables, partition
maintenance for pg_partman, retraction GC for tombstoned datoms older
than retention, refresh of materialized monitoring views.

Shape:
- Helper SQL `mentat.schedule_maintenance(name, cron_expr, sql_body)`
  that registers a pg_cron job in the right database.
- Built-in jobs: `mentat.scheduled_vacuum`,
  `mentat.scheduled_excision_gc`,
  `mentat.scheduled_partman_run` (registers the partman job).
- Soft dep, gated on `mentat.has_pg_cron()`.

### postgres_fdw + cookbook — query datoms across PG instances
**Status:** Open. **Effort:** 2 days (mostly docs). **License:** PostgreSQL (contrib).

Built-in. No code change in pg_mentat needed; this is a cookbook page
showing how to set up a foreign server pointing at another pg_mentat
instance, import its `datoms_*_new` tables as foreign tables, and run
Datalog-shaped queries that JOIN them.

Shape:
- `docs/src/cookbook-multi-instance-fdw.md`.
- The `mentat_query` function operates on local tables only; cross-
  instance queries are written as plain SQL that JOINs local Datalog
  results with foreign datom tables.
- Optional helper `mentat.import_remote_store(server, store_name)` that
  generates the CREATE FOREIGN TABLE statements for one remote store.

### h3-pg — geographic indexing for PostGIS users
**Status:** Open. **Effort:** 2 days, only after PostGIS lands. **Maintainer:** zachasme/h3-pg. **License:** Apache-2.0.

Uber's H3 hexagonal hierarchical spatial index. Pairs with PostGIS for
fast point-in-region queries at scale. Niche but well-loved.

Shape:
- Helper `mentat.h3_index('<:attr>', resolution)` that adds an H3 cell
  index to a `:db.type/geometry` attribute.
- Datalog where-fn `(in-h3-cell $ :attr ?cell)`.

---

## Tier 3 — interesting, lower priority

### pg_search (ParadeDB) — Tantivy-backed BM25 full-text

**Status:** rejected on license grounds. pg_search is AGPL-3.0; this
is a non-starter for many commercial deployments. **Use `rum`
instead** — it's PostgreSQL-licensed and provides positional ranked
search that is close to BM25 in spirit. See `docs/src/rum.md`.

### pg_duckdb — DuckDB columnar engine inside Postgres
**Effort:** 2–3 weeks. **License:** MIT.

Real value for analytical workloads on the historical datom log.
Speculative until pg_duckdb's API stabilizes; today (mid-2026) it's
still moving fast. Re-evaluate annually.

### citus — sharded multi-store
**Effort:** 4–6 weeks. **License:** AGPL-3.0 (some pieces) / PostgreSQL.

The path to "datoms over many machines." Requires significant rework of
the narrow-table primary keys to include a shard-key column. Phase
A-grade work; not a single integration. Tracked here so it doesn't get
re-discovered every six months.

### pglogical / wal2json — CDC of the datom log
**Effort:** 1 week (smoke test only). **License:** PostgreSQL-style.

The narrow tables are append-mostly (retractions are also INSERTs with
`added = false`); they replicate cleanly via either path. The work is
operational documentation more than code. Already on the existing
roadmap as "Phase 6 CDC."

### plv8 / plpython3u — user-defined Datalog where-fns
**Effort:** 2 weeks. **License:** PostgreSQL.

Lets users define custom predicates and functions in JS or Python. The
sandboxing story is the question — `plpython3u` is `u` (untrusted) for
a reason. Probably not worth the security review unless someone is
asking.

### pg_stat_statements — query observability
**Effort:** already integrated as opt-in. **License:** PostgreSQL.

The Phase 2 benchmark harness uses pg_stat_statements when available
to capture top-10 slowest statements. Done.

### pgaudit — DDL/DML audit logging
**Effort:** 1 day (cookbook). **License:** PostgreSQL.

pg_mentat already has datom-level audit semantics built in. pgaudit
adds defense-in-depth at the SQL level (catches direct writes to
narrow tables that bypass `mentat_transact`). Cookbook page is enough.

---

## Anti-list — extensions explicitly NOT worth integrating

| Extension | Reason |
|---|---|
| `pgcrypto` | Used internally where useful; nothing to expose to Datalog. |
| `pg_uuidv7` / `pg_idkit` | `:db.type/uuid` exists; users supply their own UUIDs. |
| `zhparser` and other tsvector-language plugins | One-line config of `:db/fts-language`; not an integration. |
| `pgrouting` | Datalog rules already do recursive graph traversal. |
| `pg_squeeze` / `pg_repack` | Operational tools; not Datalog-visible. |
| `pg_anonymizer` | Datomic-style retraction + RLS already cover the use case. |
| `pgsql-http` / `pg_net` | Side-effecting from queries is anti-Datalog by design. |

---

## Process

When implementing an entry:

1. Open a tracking issue with the entry's section as the description.
2. Branch `feat/integration-<name>`.
3. Build the SQL helper file `pg_mentat/sql/<NN>_<name>_helpers.sql`
   following the pattern in `11_pg_tre_helpers.sql`.
4. Add Datalog where-fn(s) in `pg_mentat/src/functions/query.rs`
   following the pattern around `build_fuzzy_match_join`.
5. Tests in `pg_mentat/src/<name>_tests.rs`. Gate happy-path tests on
   the `mentat.has_<extension>()` check; assert error paths
   unconditionally.
6. Cookbook page at `docs/src/<name>.md`. Link from `SUMMARY.md`.
7. Update this file: change `Status:` to `Done` with the commit hash
   and link to the cookbook page.
8. PR. Squash. Push. The integration ships exactly as opt-in.

Do not bundle multiple integrations in one PR. Each integration is
independently reviewable; bundling rebuilds the same merge-conflict
problem we keep running into elsewhere.

---

## Done

- **pg_tre** — approximate-regex search via the `(fuzzy-match ...)`
  where-fn. See `docs/src/fuzzy-search.md`. Commit `9ad7650`.
- **fuzzystrmatch** — phonetic and edit-distance scalar functions
  (`levenshtein`, `soundex`, `metaphone`, `daitch-mokotoff`) as
  Datalog where-fns. See `docs/src/fuzzystrmatch.md`. PG13+, no
  preload required.
- **pg_trgm** — trigram-similarity matching via the `(similar-to ...)`
  where-fn, plus partial-GIN index helpers
  (`mentat.create_trgm_index`, `mentat.drop_trgm_index`). See
  `docs/src/pg-trgm.md`. PG13+, no preload required.
- **rum** (postgrespro/rum, PostgreSQL license) — BM25-style ranked
  fulltext via the `(rum-fulltext $ :attr "term")` where-fn, with
  partial-RUM index helpers (`mentat.create_rum_fulltext_index`,
  `mentat.drop_rum_fulltext_index`). The permissive alternative to
  ParadeDB's AGPL `pg_search`. See `docs/src/rum.md`.
- **pgvector** (partial) — K-nearest-neighbor search via the
  `(vector-near $ :attr "[...]" k [op])` where-fn, plus aux-table
  helpers (`attach_vector_attribute`, `set_vector`, `del_vector`,
  `create_hnsw_vector_index`). Side-table design; full
  `:db.type/vector` schema integration is a follow-up. See
  `docs/src/pgvector.md`.
- **(query-side fix bundled with pgvector)** — the FTS-style join
  builders (`fulltext`, `fuzzy-match`, `similar-to`, `rum-fulltext`,
  `vector-near`) now properly JOIN their entity binding to subsequent
  patterns instead of relying on DISTINCT to mask cartesian products.
  Fixes a latent bug exposed by `vector-near`'s per-row varying score.
- **postgres_fdw cookbook** — cookbook page covering cross-database
  Datalog: foreign-table the remote `mentat` schema, push filters
  down via FDW, materialized-view caches, multi-tenant fan-out, and
  the `:in` clause pattern. Pure docs (no new where-fns) since
  postgres_fdw is built-in contrib. See `docs/src/postgres-fdw.md`.
- **PgQue** (NikolayS/PgQue, Apache 2.0) — transactional event
  stream: one `mentat.tx` event per transaction, emitted from a
  deferred constraint trigger on `mentat.transactions`. Helpers:
  `mentat.pgque_emit_tx('queue')`, `mentat.pgque_disable_tx`,
  `mentat.pgque_register_consumer`. Pure-PL/pgSQL queue, no
  extensions, PG14+. See `docs/src/pgque.md`.
- **pg_infer** (codeberg.org/gregburd/pg_infer, Apache 2.0) —
  model-knowledge search via the experimental pg_infer extension
  (PG18+). Where-fns: `(infer-near $ :attr "text" k [:model])`
  for top-K KNN ranked by model knowledge using `<~>`,
  `(infer-similar a b)` for scalar similarity,
  `(infer-implies a b)` for directional implication, plus three
  set-returning verbs `(infer-walk "prompt" top)`,
  `(infer-describe "entity")`, `(infer-predict "prompt" top)`
  with relation bindings. Helpers: `mentat.has_pg_infer`,
  `mentat.create_infer_index`, `mentat.drop_infer_index`. See
  `docs/src/pg_infer.md`.
- **PostGIS** (postgis.net, GPL-2.0+) — geospatial search via
  `(geom-near $ :attr "WKT" k)` (KNN by `ST_Distance`),
  `(geom-within $ :attr "WKT" radius)` (within-distance via
  `ST_DWithin`), `(geom-contains $ :attr "WKT")`
  (`ST_Contains`), `(geom-intersects $ :attr "WKT")`
  (`ST_Intersects`). Aux-table helpers:
  `mentat.attach_geometry_attribute`, `set_geometry`, `del_geometry`,
  `create_gist_geometry_index`, `detach_geometry_attribute`. SRID
  auto-detected from `geometry_columns` so input WKT is coerced to
  the column's projection. See `docs/src/postgis.md`.
- **PG19 SQL/PGQ** (Property Graph Queries) — vertex / edge view
  helpers (`mentat.create_vertex_view`, `create_edge_view`,
  `drop_*`) that map narrow datom storage onto SQL/PGQ-compatible
  tables, plus a `mentat.create_property_graph_ddl` text generator.
  Forward-looking: PG19 ships SQL/PGQ; the integration is
  detect-only on PG13-PG18 today. See `docs/src/pg19_graph.md`.
- **TimescaleDB** (Apache 2.0 OSS) — hypertable conversion for
  `mentat.transactions(tx_instant)` and
  `mentat.datoms_instant_new(v)`, plus retention policies. Helpers:
  `mentat.has_timescaledb`, `timescale_attach_transactions`,
  `timescale_attach_instant_datoms`,
  `timescale_set_transaction_retention`. See
  `docs/src/timescaledb.md`.
- **pg_partman** (PostgreSQL license) — declarative partition
  management for `mentat.transactions` on `tx_instant`. Helpers:
  `mentat.has_pg_partman`, `partman_attach_transactions`,
  `partman_set_transaction_retention`, `partman_run_maintenance`.
  Refuses one-time table conversion to prevent silent data loss;
  manual conversion path documented. See `docs/src/pg_partman.md`.
- **pg_cron** (PostgreSQL license) — scheduled-maintenance wrapper.
  Helpers: `mentat.has_pg_cron`, `cron_schedule`, `cron_unschedule`,
  plus convenience schedulers for partman maintenance and narrow-
  datom-table VACUUM. See `docs/src/pg_cron.md`.