---
warning: DO NOT CHANGE THIS MANUALLY, THIS IS GENERATED BY https://github.com/duckdb/community-extensions repository, check README there
title: anofox_similarity
excerpt: |
DuckDB Community Extensions
Multi-modal product similarity search for manufacturing supply chain planning. Find similar materials by BOM component overlap (Jaccard), graph-structural similarity (Weisfeiler-Lehman kernel), text embeddings, and transactional patterns, with predecessor detection and cold-start analog matching for demand forecasting. Requirements coverage matching: direction-aware value checks with units (EN/DE), property trees from JSON, a traced coverage kernel with gap lists, catalogue classification with your own vectors, vocabulary review, evaluation, and MinHash/LSH recall.
extension:
name: anofox_similarity
version: 2026.10.31
description: "Multi-modal product similarity search for manufacturing supply chain planning. Find similar materials by BOM component overlap (Jaccard), graph-structural similarity (Weisfeiler-Lehman kernel), text embeddings, and transactional patterns, with predecessor detection and cold-start analog matching for demand forecasting. Requirements coverage matching: direction-aware value checks with units (EN/DE), property trees from JSON, a traced coverage kernel with gap lists, catalogue classification with your own vectors, vocabulary review, evaluation, and MinHash/LSH recall."
language: C++
build: cmake
license: BSL 1.1
requires_toolchains: "cmake, openssl"
maintainers:
- jrosskopf
repo:
github: DataZooDE/anofox-similarity
ref: 1f34228d15d7e17bd92d61c65dc16b52cc402a37
variegata: 1f34228d15d7e17bd92d61c65dc16b52cc402a37
docs:
hello_world: |
-- Components (BOM) for each product
CREATE TABLE bom_items (parent_id VARCHAR, child_id VARCHAR);
INSERT INTO bom_items VALUES
('PUMP-A', 'SEAL-001'), ('PUMP-A', 'BEARING-X'), ('PUMP-A', 'MOTOR-M1'),
('PUMP-B', 'SEAL-001'), ('PUMP-B', 'BEARING-X'), ('PUMP-B', 'MOTOR-M1'), ('PUMP-B', 'GASKET-Y'),
('PUMP-C', 'FILTER-F1'), ('PUMP-C', 'VALVE-V2');
-- Find the 3 materials most similar to PUMP-A by shared components
SELECT material_id, similarity, shared_components, total_components
FROM find_similar_materials_jaccard('PUMP-A', 3, bom_table := 'bom_items')
ORDER BY similarity DESC;
-- PUMP-B 0.75 3 4 (high overlap)
-- PUMP-C 0.0 0 5 (disjoint)
-- Structural similarity that also weighs BOM topology, not just component sets
SELECT wl_kernel_similarity('PUMP-A', 'PUMP-B', iterations := 3, bom_table := 'bom_items');
extended_description: |
anofox_similarity helps manufacturers answer "which existing product is this
new one most like?" directly against ERP master data (SAP, Microsoft
Dynamics 365, or a plain `bom_items` table), which is the key input for
cold-start demand forecasting, predecessor detection, and analog-based
planning for products with no sales history yet.
Similarity signals compose across several table functions:
- `find_similar_materials_jaccard` ranks materials by BOM component overlap.
- `wl_kernel_similarity` / `find_similar_materials_wl_kernel` use a
Weisfeiler-Lehman graph kernel to capture BOM topology, not just which
components are shared but how they're structurally arranged.
- `infer_predecessors` detects predecessor/successor relationships from
structural similarity plus temporal anti-correlation in goods-movement
history (an old part's usage declining as a new part's usage rises).
- `cold_start_analogs` finds established products to seed a forecast for a
brand-new material with no history at all.
- `compute_fused_embeddings` combines structural, transactional, and text
embedding signals into one fusable vector, indexable with DuckDB's `vss`
extension via the bundled HNSW index helpers.
SAP (MARA/MAKT/MAST/STKO/STPO) and Dynamics 365 transformation macros
(`sap_to_bom_items`, `sap_to_materials_with_desc`, ...) normalize common
ERP exports into the BOM/material schema the rest of the extension expects,
so integration is mostly `SELECT * FROM sap_to_bom_items(...)`.
All table functions accept a fully qualified read-only DuckDB database and
degrade gracefully when optional companion extensions (`anofox_forecast`
for time-series features, `duckpgq` for property-graph BOM queries) are
not loaded.
The extension collects anonymous usage telemetry (an envelope-only
`extension_loaded` event; never table names, keys, or SQL). Disable with
`SET anofox_telemetry_enabled = false` or `DATAZOO_DISABLE_TELEMETRY=1`;
see TELEMETRY.md in the repository.
extension_star_count: 4
extension_star_count_pretty: 4
extension_download_count: 888
extension_download_count_pretty: 888
image: '/images/community_extensions/social_preview/preview_community_extension_anofox_similarity.png'
layout: community_extension_doc
---
### Compatibility Matrix
This table covers all DuckDB releases that have not reached [end-of-life](https://duckdb.org/release_calendar), including LTS releases, and shows the 2 most recent patch releases of each. Older patch releases are not listed, but the extension may be available for them as well.
| Platform | 1.4.4 | 1.4.5 | 1.5.5 | 1.5.6 | 2.0.0 |
| :--- | :---: | :---: | :---: | :---: | :---: |
| Linux AMD64 | ❌ | ❌ | ✅ | ✅ | ✅ |
| Linux ARM64 | ❌ | ❌ | ✅ | ✅ | ✅ |
| macOS AMD64 | ❌ | ❌ | ✅ | ✅ | ✅ |
| macOS ARM64 | ❌ | ❌ | ✅ | ✅ | ✅ |
| Windows AMD64 | ❌ | ❌ | ✅ | ✅ | ✅ |
| Windows ARM64 | ❌ | ❌ | ❌ | ❌ | ❌ |
| WebAssembly | ❌ | ❌ | ✅ | ✅ | ✅ |
### Installing and Loading
```sql
INSTALL {{ page.extension.name }} FROM community;
LOAD {{ page.extension.name }};
```
{% if page.docs.hello_world %}
### Example
```sql
{{ page.docs.hello_world }}```
{% endif %}
{% if page.docs.extended_description %}
### About {{ page.extension.name }}
{{ page.docs.extended_description }}
{% endif %}
### Added Functions
| function_name | function_type | description | comment | examples |
|------------------------------------|---------------|---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|---------|-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| aggregate_material_components | table_macro | Groups a BOM edge list into one row per parent material with its child components as a sorted list — the shape the Jaccard and cold-start functions expect. 'material_filter' restricts the result to the listed parents; NULL means all of them. | NULL | [SELECT * FROM aggregate_material_components(bom_table := 'bom_items')] |
| apply_vocabulary_review | table | Applies a review (wording, class_id[, source, reviewed_by, reviewed_at]) to the overrides (default 'vocabulary_overrides' when that table exists; NULL for none): one entry per wording and source, the review wins. Returns the new overrides table; table functions cannot write, so store it: CREATE OR REPLACE TABLE vocabulary_overrides AS SELECT * FROM apply_vocabulary_review('review'). | NULL | [CREATE OR REPLACE TABLE vocabulary_overrides AS SELECT * FROM apply_vocabulary_review('review');] |
| bhattacharyya | scalar | Bhattacharyya coefficient of two class distributions MAP(class, probability): Σ √(p_k q_k). 1 for identical distributions, 0 for disjoint ones; the soft label agreement L(q, c) of the coverage kernel. Distributions are used as given (tree_coverage normalises them). Negative values are an error; NULL gives NULL. | NULL | [SELECT bhattacharyya(MAP {'heating': 0.6, 'ventilation': 0.4}, MAP {'heating': 0.5, 'ventilation': 0.5}); -- 0.9949] |
| bom_common_components | table_macro | Components that appear in the BOMs of both given materials -- the overlap used to judge how interchangeable two assemblies are. | NULL | [SELECT * FROM bom_common_components(material_1 := 'PUMP-A', material_2 := 'PUMP-B')] |
| bom_dfs_neighborhood | table_macro | All descendant components of a root material within a depth limit, as a reusable depth-first BOM traversal over a (parent_id, child_id) edge table. | NULL | [SELECT * FROM bom_dfs_neighborhood(root_material_id := 'PUMP-A', max_depth := 3)] |
| bom_explosion_1level | table_macro | Direct children of one parent material: returns (parent_material_id, child_material_id, quantity_per) for the immediate level only. 'header_table' and 'component_table' name the tables to read, so the macro works on any schema matching the universal BOM shape. | NULL | [SELECT * FROM bom_explosion_1level(query_material_id := 'PUMP-A')] |
| bom_explosion_multilevel | table_macro | Full multi-level BOM explosion under one parent, to 'max_depth' levels. Uses UNION rather than UNION ALL so a shared subassembly reached by many paths is expanded once -- no exponential blow-up on diamond BOMs -- and the depth gate is clamped to a hard ceiling of 64 so a cyclic BOM cannot iterate unbounded. | NULL | [SELECT * FROM bom_explosion_multilevel(query_material_id := 'PUMP-A', max_depth := 5)] |
| bom_to_items | table_macro | Flattens the universal bom_header + bom_component pair into the flat (parent_id, child_id, quantity) bom_items shape that the similarity algorithms expect. | NULL | [SELECT * FROM bom_to_items(header_table := 'bom_header', component_table := 'bom_component')] |
| bom_where_used | table_macro | Reverse BOM lookup: every parent material that consumes a given child, with the quantity each one uses. | NULL | [SELECT * FROM bom_where_used(child_material_id := 'SEAL-001')] |
| bootstrap_ci | table | Cluster bootstrap intervals: resamples the clusters (cluster_col) with replacement n times (default 1000, seeded) and reports per metric the pooled mean (estimate) and the percentile interval lo/hi at level (default 0.95), with n_boot and n_clusters. metrics defaults to every other numeric column. | NULL | [SELECT * FROM bootstrap_ci('per_request', 'maker', metrics := ['hit', 'regret']);] |
| calibrated_cosine | scalar | Cosine rescaled above a corpus floor: clip((cos - floor) / (1 - floor), 0, 1). Small sentence encoders give unrelated texts a cosine around 0.85; estimate the floor with cosine_floor. floor must be in [0, 1); NULL gives NULL. | NULL | [SELECT calibrated_cosine([1.0, 0.0], [0.8, 0.6], 0.5); -- 0.6] |
| check_anofox_forecast_available | macro | Returns true when the anofox_forecast extension is loaded, so a caller can choose the richer time-series feature path over the built-in one. anofox_forecast is an optional soft dependency and is never auto-installed. | NULL | [SELECT check_anofox_forecast_available()] |
| check_duckpgq_available | macro | Returns true when the DuckPGQ extension is loaded, so a caller can choose the graph-based BOM traversal path over the SQL fallback. DuckPGQ is an optional soft dependency and is never auto-installed. | NULL | [SELECT check_duckpgq_available()] |
| check_statistics_freshness | table | Returns a single-row summary of the transactional_embedding_statistics table including stat_count, max_samples, current_version, last_updated, and is_fresh. is_fresh is TRUE when >= 30 statistics rows exist and last_updated is within 7 days. | NULL | [SELECT * FROM check_statistics_freshness();] |
| classify_to_catalogue | table | Pivot classifier: maps every item onto the catalogue as a distribution p(class). Steps: per-source mean-centring of the item vectors (center_by_source, centroid mode; sources with fewer than min_source_items items, default 10, use the batch mean); cosine to the class vector, or maxsim(tokens, exemplar_tokens) in mode 'late_interaction'; z-score across the catalogue; plus attr_bonus x the attribute-template match (mean over the item's attributes of the best same-dimension template, name similarity by calibrated cosine of name_vectors, or 1 without vectors); softmax with temperature; margin = p1 - p2. Reviewed wordings (overrides_table: wording, source, class_id; matched on the item's label) and per-item decisions (answers_table: tree_id, node_id, class_id) give a one-hot p and overridden = true. items_table: node_id, vec[, tree_id, source, label, tokens]; catalogue_table: class_id, vec[, exemplar_tokens]; attrs_table: [tree_id,] node_id, name, dimension; templates_table: class_id, dimension[, name, name_en, name_de]. Returns tree_id, node_id, source, class_id, p, rank, margin, overridden; tree_id, node_id, class_id and p make it a label_table for tree_coverage. | NULL | [SELECT * FROM classify_to_catalogue(items_table := 'request_items', catalogue_table := 'catalogue', top := 3);, SELECT * FROM classify_to_catalogue(items_table := 'unseen', catalogue_table := 'catalogue', mode := 'late_interaction', top := 1);] |
| cluster_matches | table | Entity clustering: a C++ union-find over the pairs whose score (score_col, default 'score') is at or above threshold (default 0.8). Returns entity_id, cluster_id (the smallest member id) and cluster_size for every entity in the pairs table (left_col/right_col, default 'a'/'b'); entities without a pair above the threshold form their own cluster. For a graph view, see the DuckPGQ recipe in the extension documentation (LSH and clustering). | NULL | [SELECT * FROM cluster_matches('customer_pairs', threshold := 0.85);] |
| cold_start_analogs | table | Finds structural analogs for a cold-start material (one with little or no demand history). Returns established materials that are structurally similar and have at least min_history_months of goods movement data, enabling surrogate forecasting. | NULL | [SELECT * FROM cold_start_analogs('NEW-PART', 5);, SELECT * FROM cold_start_analogs('NEW-PART', 10, min_history_months := 12, min_similarity := 0.4);] |
| compat_score | macro | Compatibility of a provided category with a required one for an ordinal or categorical dimension (protection class, certification level, qualifier), looked up in a curated rules table (dimension, required, provided, score). Without a rule: 1 if equal, else 0. Text similarity is never used. | NULL | [SELECT compat_score('protection_class', 'IP54', 'IP65'), SELECT compat_score('cert_level', 'silver', 'gold', rules_table := 'my_rules')] |
| compute_domain_specific_statistics | table_macro | Computes the advanced domain-specific ERP features — movement type mix, day-of-week pattern and lifecycle position — used alongside the core time-series features. | NULL | [SELECT * FROM compute_domain_specific_statistics(movements_table := 'goods_movements')] |
| compute_fused_embeddings | table_macro | Combines the structural, textual and transactional embedding of every material in material_embeddings into one fused vector, returning (material_id, combined_embedding, fusion_weights). Weights are normalised to sum to 1, matching what fuse_embeddings applies internally, and a modality's vector is only required when its weight is above zero. | NULL | [SELECT * FROM compute_fused_embeddings(weights_structural := 0.6, weights_textual := 0.4)] |
| compute_jaccard_embeddings | table | Computes and RETURNS 128-dimensional MinHash embeddings (one row per material/seed) from BOM component sets; it does not write to material_embeddings — aggregate and upsert the result yourself. Each material's embedding is derived from its set of direct child components. Requires bom_table with columns parent_id and child_id. | NULL | [SELECT * FROM compute_jaccard_embeddings();, SELECT * FROM compute_jaccard_embeddings(bom_table := 'sap_stpo');] |
| compute_textual_embeddings | table_macro | Generates an embedding per material from its description text, returning (material_id, textual_embedding). In the released build every provider returns a deterministic hash PLACEHOLDER, not a semantic embedding. Reads descriptions from 'makt_table' in the given 'language' and embeds them with 'provider'; 'provider_config' passes provider-specific settings through to the backend. | NULL | [SELECT * FROM compute_textual_embeddings(makt_table := 'sap_makt', language := 'EN')] |
| compute_transactional_embeddings | table | Computes 128-dimensional transactional embeddings from goods movement data and returns them (it does not write to material_embeddings). Features include demand velocity, seasonality, frequency, and quantity statistics normalized against transactional_embedding_statistics. Supports batched computation via batch_size and batch_offset. | NULL | [SELECT * FROM compute_transactional_embeddings();, SELECT * FROM compute_transactional_embeddings(movements_table := 'sap_mseg', time_window_days := 365);, SELECT * FROM compute_transactional_embeddings(batch_size := 1000, batch_offset := 0);] |
| connected_components | scalar | Union-find over a LIST of pairs STRUCT(a, b): returns LIST(STRUCT(entity_id, cluster_id)), one entry per entity sorted by id; cluster_id is the smallest entity id of its connected component. NULL ids are ignored. The building block of cluster_matches. | NULL | [SELECT connected_components([{'a': 'x', 'b': 'y'}, {'a': 'y', 'b': 'z'}]);] |
| containment | scalar | Share of the query set contained in the candidate set: \|q∩c\| / \|q\|. Directional: a richer candidate is not penalised. Duplicates collapse. NULL in gives NULL; an empty query gives NULL. | NULL | [SELECT containment(['a','b'], ['a','b','c','d']); -- 1.0, SELECT containment(['a','b','c'], ['b','c','d']); -- 0.6667] |
| cosine_floor | table | Estimates the cosine floor b of a vector corpus: the median cosine of pairs of distinct vectors. With at most `sample` pairs (default 10 000) all pairs count; otherwise `sample` pairs are drawn deterministically from `seed` (default 7). Returns floor and n_pairs. Use the floor with calibrated_cosine or as name_floor. | NULL | [SET VARIABLE name_floor = (SELECT floor FROM cosine_floor('name_vectors', 'vec'));] |
| coverage_kernel | scalar | The stage-2 coverage kernel over two packed trees (LIST of STRUCT(node_id, parent_id, node_kind, edge_type, class_id, reviewed, count, probs MAP, attrs LIST(STRUCT(attr_id, name, dimension, unit, lo, hi, bound, category, default_bound, scale, name_vec)))) with an options STRUCT (labels, assignment, label_weight, mu, partition_gate, count_gate, name_floor, tol, soft_band, trace, rules). Returns STRUCT(coverage, n_requirements, n_gaps, reviewed, assignment_used, trace). tree_coverage and tree_coverage_trace build its inputs; use it directly for custom pipelines. | NULL | [SELECT coverage_kernel(q.nodes, c.nodes, struct_pack(labels := 'hard')).coverage FROM q, c;] |
| coverage_recall | table | Stage-1 recall over class multisets: the top-k candidate trees per query with score, containment, jaccard, reviewed, mode_used and rank. mode 'auto' uses containment when every candidate tree is reviewed and weighted jaccard otherwise. query_tree_id is a tree id or a LIST of ids. Trees come from nodes_table/attrs_table (default 'tree_nodes'/'tree_attributes'); candidates from candidate_nodes_table/candidate_attrs_table (default: the same tables). Naming a nodes table without its attributes table is an error (pass NULL for none). Classes resolve class_id -> overrides_table (reviewed vocabulary) -> label_table (classifier). Stage 1 ranks candidates by containment (+ tie_break x jaccard) when every candidate tree is reviewed, else by weighted jaccard (mode 'auto'). candidate_multiset_table: a precomputed index of the candidate class multisets (tree_class_multiset output: entity_id, feature_id, weight[, reviewed]) makes stage 1 skip resolving every candidate tree. | NULL | [SELECT * FROM coverage_recall('R01', k := 20, candidate_nodes_table := 'programme_nodes');] |
| create_bom_property_graph | table_macro | Summarises the BOM graph for one source system as (graph_name, source, num_nodes, num_edges), counting distinct parent materials in bom_header and the components beneath them. | NULL | [SELECT * FROM create_bom_property_graph(source_system := 'SAP')] |
| dynamics365_to_bom_component | table_macro | Maps Dynamics 365 BOM lines into the universal bom_component shape (singular) — note this is not the flat bom_items form. Reads BOMId, LineNum, ItemId, Quantity and ScrapPercent. | NULL | [SELECT * FROM dynamics365_to_bom_component(bom_lines := 'BOM')] |
| dynamics365_to_bom_header | table_macro | Maps Dynamics 365 BOMTable joined to BOMVersion into the universal bom_header shape, keeping rows with Status = 0. Reads BOMTable.(BOMId, ItemId, Status) and BOMVersion.(BOMId, VersionId, ActivationDate, ApprovedStatus). | NULL | [SELECT * FROM dynamics365_to_bom_header(bom_table := 'BOMTable', bom_version := 'BOMVersion')] |
| dynamics365_to_materials | table_macro | Maps a Dynamics 365 InventTable to the universal materials shape. Reads ItemId, ItemName and ItemType (0/1/2) and returns the full materials column set. Projects a shape; it does not write an output table. | NULL | [SELECT * FROM dynamics365_to_materials(invent_table := 'InventTable')] |
| embed_text | macro | Embeds a single piece of text with the default local backend — shorthand for embedding_backend(description, 'gemma-local', ''). | NULL | [SELECT embed_text('centrifugal pump seal kit')] |
| embedding_backend | scalar | Generates a 384-dimensional text embedding (FLOAT[384]) for the given text. provider selects the backend and provider_config is a JSON string of provider-specific options (api_key, endpoint). NOTE: unless the extension is built with a local OpenVINO model or a real API backend is configured, all providers return a deterministic, hash-based PLACEHOLDER embedding (useful for wiring and tests, not semantic search). | NULL | [SELECT embedding_backend('diesel pump', 'gemma-local', '{}');, SELECT embedding_backend('valve seat', 'openai', '{"api_key": "..."}');] |
| eval_calibration | table | Calibration of the predicted coverage (score_col, default 'coverage') against gold true_coverage over the pairs in both tables: n_pairs, mae, bias (predicted - true), pearson, spearman (average ranks for ties); correlations are NULL for a constant input. | NULL | [SELECT * FROM eval_calibration('results', 'gold');] |
| eval_ranking | table | Ranking quality against gold coverage. results: query_id, candidate_id and a rank (rank_col, default 'rank') or a score (score_col, default 'coverage', highest first); gold: query_id, candidate_id, true_coverage. The best candidates of a query are all gold candidates within 1e-9 of its gold best; a ranked candidate without gold counts as 0. Per k (default 1, 5, 10, 20): n_queries, top_k_accuracy (share of queries with a best candidate in the top k, BRD 'recall@k of the best candidate'), recall_at_k (share of each query's best candidates in the top k), mrr (cut at k), mean_regret and share_regret_le_005 of the top pick. | NULL | [SELECT * FROM eval_ranking('results', 'gold', k := [1, 20]);] |
| extract_material_descriptions | table_macro | Extracts material descriptions from a SAP MAKT table for one language, returning (material_id, full_text): the description (MAKTX) and the search text (MAKTG) joined by a space, one row per material. | NULL | [SELECT * FROM extract_material_descriptions(makt_table := 'sap_makt', language := 'EN')] |
| extract_ts_features | table_macro | Shared time-series feature extraction over a goods-movement table, computed once as a CTE so callers do not each re-run the expensive per-material feature computation. 'batch_size' and 'batch_offset' page through large material sets; NULL batch_size means all of them. | NULL | [SELECT * FROM extract_ts_features(movements_table := 'goods_movements', time_window_days := 365)] |
| filter_recent_movements | table_macro | Filters a goods-movement table to a recent window and a minimum quantity. The window is anchored on the most recent movement date IN THE DATA rather than wall-clock today, which works without the ICU extension, is deterministic for tests, and is what planners want on historical ERP extracts where 'today' may be years after the last movement. | NULL | [SELECT * FROM filter_recent_movements(movements_table := 'goods_movements', time_window_days := 180)] |
| find_covering_entities | table | Ranks the entities of an (entity, feature, weight) table by how well they cover a query entity of the same table: weighted containment Σmin/Σq by default, so a richer candidate is not penalised, with weighted Jaccard as tie-break so the leanest full cover comes first. Returns entity_id, score, containment, jaccard, shared_weight, query_weight, candidate_weight, surplus_weight. Named parameters: mode ('containment' \| 'jaccard' \| 'tversky'), alpha, beta (Tversky weights, used in mode 'tversky' only), weighted (use the weight column, default true), tie_break (weight of Jaccard added to the score in modes 'containment' and 'tversky', default 0.01), min_score (threshold on the measure itself), feature_table (default 'feature_multiset'), entity_col, feature_col, weight_col (NULL for unit weights). | NULL | [SELECT * FROM find_covering_entities('P-100', 10);, SELECT * FROM find_covering_entities('F-100', 5, feature_table := 'frame_bom', entity_col := 'frame_no', feature_col := 'part_no', weight_col := 'quantity_per');] |
| find_covering_entities_for | table | Like find_covering_entities, for a query that is not in the table: query is a MAP(feature, weight), e.g. the class multiset of a new specification. The argument must be constant (a literal or getvariable(...)). Named parameters: mode ('containment' \| 'jaccard' \| 'tversky'), alpha, beta (Tversky weights, used in mode 'tversky' only), weighted (use the weight column, default true), tie_break (weight of Jaccard added to the score in modes 'containment' and 'tversky', default 0.01), min_score (threshold on the measure itself), feature_table (default 'feature_multiset'), entity_col, feature_col, weight_col (NULL for unit weights). | NULL | [SELECT * FROM find_covering_entities_for(MAP {'seat_heating': 2, 'massage': 4}, 10);, SELECT * FROM find_covering_entities_for(getvariable('request'), 20, mode := 'jaccard');] |
| find_covering_entities_lsh | table | Sub-linear candidate search over a minhash_index (LSH-Ensemble style). queries_table holds (query_id, feature_id[, weight]) multisets. Per query and size partition, the containment threshold (default 0.5) is turned into the Jaccard threshold for the partition's largest set, and the rows per band are chosen so that the LSH S-curve passes below it; candidates share a band. Ranked by the estimate: containment = J (\|q\| + \|c\|) / ((1 + J) \|q\|) (capped at 1) or J (mode 'jaccard'). Returns query_id, entity_id, estimate, jaccard_estimate, rank (top k, default 20) and n_candidates (scored per query). An approximate accelerator: exact find_covering_entities stays the reference. Without bands_table the index's band hashes are unnested on every call (linear in the index size); store minhash_bands(index) once and pass it as bands_table for a hash-join (sub-linear) search. | NULL | [SELECT * FROM find_covering_entities_lsh('request_multisets', 'programme_index', k := 20);] |
| find_similar_materials_jaccard | table | Finds the k most structurally similar materials to a query material using exact Jaccard similarity on BOM component sets. Returns material_id, similarity, shared_components, and total_components. Filtered by min_similarity threshold. mode := 'containment' ranks by the share of the query's components each material contains instead (default 'jaccard'). | NULL | [SELECT * FROM find_similar_materials_jaccard('MAT-001', 10);, SELECT * FROM find_similar_materials_jaccard('MAT-001', 20, min_similarity := 0.3);, SELECT * FROM find_similar_materials_jaccard('MAT-001', 10, mode := 'containment');] |
| find_similar_materials_wl_kernel | table | Finds the k most structurally similar materials using the Weisfeiler-Lehman graph kernel, which captures multi-hop BOM structure beyond direct component overlap. iterations controls the number of WL refinement rounds (default 3). | NULL | [SELECT * FROM find_similar_materials_wl_kernel('MAT-001', 10);, SELECT * FROM find_similar_materials_wl_kernel('MAT-001', 10, iterations := 3, min_similarity := 0.2);] |
| fit_coverage_weights | table | Fits the kernel weights on reviewed matches: for every grid point of label_weights (lambda, default 0, 0.1, 0.2, 0.3, 0.5) x mus (default 0, 0.1, 0.2, 0.3), the mean absolute error of the kernel's coverage against reviewed_matches (query_id, candidate_id, true_coverage), with n_pairs, n_unresolved (reviewed pairs whose trees are missing) and rank (best first). At most 20 values per axis. The trees and options are given as for tree_coverage. The classifier's beta and tau are tuned with classify_to_catalogue and eval_ranking instead. | NULL | [SELECT * FROM fit_coverage_weights('reviewed_matches', candidate_nodes_table := 'programme_nodes', candidate_attrs_table := 'programme_attributes') LIMIT 3;] |
| fuse_embeddings | scalar | Fuses structural (FLOAT[256]), textual (FLOAT[384]), and transactional (FLOAT[128]) embeddings into a single 768-dimensional combined embedding using weighted concatenation. Each component is scaled by sqrt(weight). Weights do not need to sum to 1.0. | NULL | [SELECT fuse_embeddings(se, te, xe, {'structural':0.5,'textual':0.5,'transactional':0.0}) FROM material_embeddings;] |
| hnsw_compact_index | pragma | NULL | NULL | |
| hnsw_index_scan | table | NULL | NULL | |
| infer_predecessors | table | Identifies predecessor (superseded) materials for a given successor by combining BOM structural similarity with anti-correlated consumption patterns. Returns predecessor candidates ranked by confidence score along with correlation, similarity, and temporal overlap data. Useful for lifecycle transition planning and inventory run-down. | NULL | [SELECT * FROM infer_predecessors('NEW-PART');, SELECT * FROM infer_predecessors('NEW-PART', lookback_months := 24, min_similarity := 0.5, min_confidence := 0.6, lag_weeks := 4);] |
| items_to_bom | table_macro | The inverse of bom_to_items: expands a flat bom_items edge list back into the universal bom_header and bom_component shapes, tagging rows with 'source_system'. | NULL | [SELECT * FROM items_to_bom(items_table := 'bom_items', source_system := 'MIGRATED')] |
| jaccard_similarity | scalar | Computes the Jaccard similarity coefficient between two lists treated as sets. Returns a value in [0.0, 1.0] equal to \|intersection\| / \|union\|. Duplicates within each list are deduplicated before comparison. Returns NULL if either argument is NULL; returns 0.0 if both lists are empty. | NULL | [SELECT jaccard_similarity(['A','B','C'], ['B','C','D']); -- 0.5, SELECT jaccard_similarity([1,2,3,4], [3,4,5,6]); -- 0.3333] |
| lsh_bands | scalar | LSH band hashes of a MinHash signature: one hash per band of len(signature) / bands rows. Two multisets with Jaccard J share at least one band with probability 1 - (1 - J^rows)^bands. | NULL | [SELECT lsh_bands(weighted_minhash(MAP {'a': 1.0}, 64), 16);] |
| maxsim | scalar | Late-interaction score (ColBERT-style MaxSim): each query token takes its maximum cosine over the document tokens, and the maxima are averaged. Asymmetric. Token vectors must share one dimension; a zero token has cosine 0. NULL or an empty list gives NULL. | NULL | [SELECT maxsim([[1.0, 0.0], [0.0, 1.0]], [[1.0, 0.0], [0.6, 0.8]]); -- 0.9] |
| minhash_bands | table | The band hashes of a minhash_index in long form (entity_id, part, r, j, h, num_hashes, seed): one row per entity, rows per band (1, 2, 4, 8) and band. Store it and pass it to find_covering_entities_lsh as bands_table, so the search joins on the band hash instead of unnesting the index. | NULL | [CREATE TABLE programme_bands AS SELECT * FROM minhash_bands('programme_index');] |
| minhash_index | table | Builds a weighted-MinHash index of a feature multiset (feature_table, default 'feature_multiset'; entity_col/feature_col/weight_col as in find_covering_entities; weight_col := NULL for unit weights, where duplicate rows count once): one row per entity with total_weight, its size partition (partitions quantiles, default 4), signature (num_hashes, a multiple of 8, default 128; seed default 7), band_hashes for 1, 2, 4 and 8 rows per band (concatenated), num_hashes and seed. Store it as a table; find_covering_entities_lsh searches it. | NULL | [CREATE TABLE programme_index AS SELECT * FROM minhash_index(feature_table := 'programme_class_multiset');] |
| minhash_similarity | scalar | Share of equal positions of two MinHash signatures of the same length: the estimate of their (weighted) Jaccard similarity. | NULL | [SELECT minhash_similarity(weighted_minhash(MAP {'a': 1.0}, 64), weighted_minhash(MAP {'a': 1.0, 'b': 1.0}, 64)); -- about 0.5] |
| normalize_quantity | scalar | Converts a value in a unit to the base unit of its dimension: STRUCT(value, unit, dimension). An unknown unit is never guessed: value and unit come back unchanged with dimension NULL. Currencies keep their value (same-currency comparison only). unit_table extends the seed as in parse_quantity. | NULL | [SELECT normalize_quantity(2500, 'mA'); -- 2.5 A] |
| normalize_quantity | scalar | Converts a value in a unit to the base unit of its dimension: STRUCT(value, unit, dimension). An unknown unit is never guessed: value and unit come back unchanged with dimension NULL. Currencies keep their value (same-currency comparison only). unit_table extends the seed as in parse_quantity. | NULL | [SELECT normalize_quantity(3, 'lanes', (SELECT list(u) FROM my_units u));] |
| parse_quantity | scalar | Parses a quantity written in English or German ('max. 38 dB(A)', 'mindestens 24 cm', '6–18 V', 'zwischen 15 und 25 mm/s', '220 N ± 2 %', 'IP54 or better') into STRUCT(lo, hi, unit, dimension, bound, raw, source_unit, category). lo/hi are in the base unit of the dimension (unit); bound is at_least, at_most, within, equals or NULL when the text states none. lang ('en', 'de' or a list, first entry decides ambiguous separators such as '2.500') defaults to ['en', 'de']. unit_table is a LIST of STRUCT(symbol, alias, dimension, factor, offset) that extends or overrides the seed (see quantity_units()). A space between groups of three digits is a thousands separator ('1 000 N'). A bare number is a count; an unknown unit keeps the number and has dimension NULL. Pass lang before unit_table (DuckDB 1.5.6 binds by position). | NULL | [SELECT parse_quantity('2.500 mA', ['de', 'en']);] |
| parse_quantity | scalar | Parses a quantity written in English or German ('max. 38 dB(A)', 'mindestens 24 cm', '6–18 V', 'zwischen 15 und 25 mm/s', '220 N ± 2 %', 'IP54 or better') into STRUCT(lo, hi, unit, dimension, bound, raw, source_unit, category). lo/hi are in the base unit of the dimension (unit); bound is at_least, at_most, within, equals or NULL when the text states none. lang ('en', 'de' or a list, first entry decides ambiguous separators such as '2.500') defaults to ['en', 'de']. unit_table is a LIST of STRUCT(symbol, alias, dimension, factor, offset) that extends or overrides the seed (see quantity_units()). A space between groups of three digits is a thousands separator ('1 000 N'). A bare number is a count; an unknown unit keeps the number and has dimension NULL. Pass lang before unit_table (DuckDB 1.5.6 binds by position). | NULL | [SELECT parse_quantity('3 Stk.', 'de', (SELECT list(u) FROM my_units u));] |
| parse_quantity | scalar | Parses a quantity written in English or German ('max. 38 dB(A)', 'mindestens 24 cm', '6–18 V', 'zwischen 15 und 25 mm/s', '220 N ± 2 %', 'IP54 or better') into STRUCT(lo, hi, unit, dimension, bound, raw, source_unit, category). lo/hi are in the base unit of the dimension (unit); bound is at_least, at_most, within, equals or NULL when the text states none. lang ('en', 'de' or a list, first entry decides ambiguous separators such as '2.500') defaults to ['en', 'de']. unit_table is a LIST of STRUCT(symbol, alias, dimension, factor, offset) that extends or overrides the seed (see quantity_units()). A space between groups of three digits is a thousands separator ('1 000 N'). A bare number is a count; an unknown unit keeps the number and has dimension NULL. Pass lang before unit_table (DuckDB 1.5.6 binds by position). | NULL | [SELECT parse_quantity('3 Stk.', ['de'], (SELECT list(u) FROM my_units u));] |
| parse_quantity | scalar | Parses a quantity written in English or German ('max. 38 dB(A)', 'mindestens 24 cm', '6–18 V', 'zwischen 15 und 25 mm/s', '220 N ± 2 %', 'IP54 or better') into STRUCT(lo, hi, unit, dimension, bound, raw, source_unit, category). lo/hi are in the base unit of the dimension (unit); bound is at_least, at_most, within, equals or NULL when the text states none. lang ('en', 'de' or a list, first entry decides ambiguous separators such as '2.500') defaults to ['en', 'de']. unit_table is a LIST of STRUCT(symbol, alias, dimension, factor, offset) that extends or overrides the seed (see quantity_units()). A space between groups of three digits is a thousands separator ('1 000 N'). A bare number is a count; an unknown unit keeps the number and has dimension NULL. Pass lang before unit_table (DuckDB 1.5.6 binds by position). | NULL | [SELECT parse_quantity('65.000 Zyklen', 'de');] |
| parse_quantity | scalar | Parses a quantity written in English or German ('max. 38 dB(A)', 'mindestens 24 cm', '6–18 V', 'zwischen 15 und 25 mm/s', '220 N ± 2 %', 'IP54 or better') into STRUCT(lo, hi, unit, dimension, bound, raw, source_unit, category). lo/hi are in the base unit of the dimension (unit); bound is at_least, at_most, within, equals or NULL when the text states none. lang ('en', 'de' or a list, first entry decides ambiguous separators such as '2.500') defaults to ['en', 'de']. unit_table is a LIST of STRUCT(symbol, alias, dimension, factor, offset) that extends or overrides the seed (see quantity_units()). A space between groups of three digits is a thousands separator ('1 000 N'). A bare number is a count; an unknown unit keeps the number and has dimension NULL. Pass lang before unit_table (DuckDB 1.5.6 binds by position). | NULL | [SELECT parse_quantity('max. 38 dB(A)');] |
| pragma_hnsw_index_info | table | NULL | NULL | |
| quantity_units | table | The built-in, read-only units seed of the quantity layer: symbol, alias (list), dimension, factor, offset (base = value * factor + offset) and base_unit. Currencies have factor NULL and are never converted. Copy it into a table to extend it and pass (SELECT list(u) FROM my_units u) as unit_table. | NULL | [SELECT * FROM quantity_units() WHERE dimension = 'length';, CREATE TABLE my_units AS SELECT symbol, alias, dimension, factor, "offset" FROM quantity_units();] |
| recompute_embedding_statistics | table_macro | Recomputes the transactional embedding statistics from current movement data, returning the per-feature normalisation statistics the embedding pipeline uses. 'time_window_days' is the historical window and 'min_observations' the minimum data points a material needs to qualify. | NULL | [SELECT * FROM recompute_embedding_statistics(time_window_days := 365, min_observations := 3)] |
| sap_to_bom_items | table_macro | Converts the SAP MAST/STKO/STPO trio into the universal BOM shape. 'bom_alternative' picks the alternative BOM, 'reference_date' selects the version valid on that date, and 'bom_usage' is accepted but not applied yet (every usage is returned; filter on MAST.STLAN yourself). Quantity passes through the source MENGE column's type, which is DECIMAL in SAP -- cast if you need DOUBLE. | NULL | [SELECT * FROM sap_to_bom_items('MAST', 'STKO', 'STPO', bom_alternative := '01')] |
| sap_to_materials | table_macro | Extracts materials from a SAP MARA table into the universal materials shape. Requires MARA columns matnr, mtart, matkl, ersda and lvorm; rows flagged deleted via lvorm are excluded. The description column is always empty here -- use sap_to_materials_with_desc to populate it from MAKT; the makt_table and language parameters are accepted for compatibility and ignored. | NULL | [SELECT * FROM sap_to_materials('MARA')] |
| sap_to_materials_with_desc | table_macro | Like sap_to_materials, but joins MAKT to populate the description column in the requested 'language' (SAP single-character language key, e.g. 'E'). | NULL | [SELECT * FROM sap_to_materials_with_desc('MARA', 'MAKT', language := 'E')] |
| threshold_at_precision | table | The auto-accept threshold: over the top-1 picks, the lowest score threshold whose precision (share of picks with true coverage >= good, default 0.9) reaches target_precision (default 0.95; 'target' is a reserved word). Returns threshold (NULL when unreachable), precision, accepted_share, n_queries and the precision curve (threshold, precision, share). | NULL | [SELECT threshold, precision, accepted_share FROM threshold_at_precision('results', 'gold', target_precision := 0.95);] |
| tree_anchors | table | Flattens property trees: one row per item node (containers are transparent) with its resolved class, depth, anchor_path (ancestor ids from the root), variant_path (the nodes with an OR edge on the way, i.e. the alternatives chosen), effective_count (product of the counts along the path) and n_attributes. Works at any depth. | NULL | [SELECT * FROM tree_anchors('tree_nodes', 'tree_attributes');, SELECT * FROM tree_anchors('tree_nodes', tree_ids := ['M1']);] |
| tree_class_multiset | table | The class multiset per tree for stage-1 recall: entity_id (tree), feature_id (class), weight (summed counts, maximum across OR alternatives), reviewed (every item's class came from class_id or the reviewed vocabulary). Classes resolve in the order class_id, overrides_table (wording, source, class_id), label_table (classifier arg-max). | NULL | [SELECT * FROM tree_class_multiset('tree_nodes');, SELECT * FROM tree_class_multiset('tree_nodes', overrides_table := 'vocabulary_overrides');] |
| tree_coverage | table | Stage-2 coverage: ranks candidate trees by the share of the request they cover. Per request item q and candidate item c: score = label_weight*L + (1-label_weight)*L*a, with L the label agreement (Bhattacharyya of soft labels, or 0/1) and a the mean over q's attributes of the best (mu*name_sim + 1-mu)*value_satisfies; counts are summed over compatible items (never across OR alternatives). assignment 'partition' gives each candidate item one requirement block (no substitution), 'best_match' lets requirements share, 'auto' uses partition when both the query and the candidate trees are reviewed. Returns query_id, candidate_id, coverage, stage1_score, rank_score (coverage + stage1_weight x stage1_score), rank, n_requirements, n_gaps, reviewed, assignment_used. default_bounds := 'catalogue' gives unqualified values the bound of their template (templates_table). tol and soft_band (default 0.02, 0.05) are passed to value_satisfies; use 0 and 0 for hard limits. query_tree_id is a tree id or a LIST of ids. Trees come from nodes_table/attrs_table (default 'tree_nodes'/'tree_attributes'); candidates from candidate_nodes_table/candidate_attrs_table (default: the same tables). Naming a nodes table without its attributes table is an error (pass NULL for none). Classes resolve class_id -> overrides_table (reviewed vocabulary) -> label_table (classifier). Stage 1 ranks candidates by containment (+ tie_break x jaccard) when every candidate tree is reviewed, else by weighted jaccard (mode 'auto'). candidate_multiset_table: a precomputed index of the candidate class multisets (tree_class_multiset output: entity_id, feature_id, weight[, reviewed]) makes stage 1 skip resolving every candidate tree. | NULL | [SELECT * FROM tree_coverage('R01', k := 20, candidate_nodes_table := 'programme_nodes', candidate_attrs_table := 'programme_attributes');, SELECT * FROM tree_coverage(['R01', 'R02'], candidates := ['P001', 'P002']);] |
| tree_coverage_trace | table | The explanation of tree_coverage: one row per request item and required attribute with the matched candidate item and path, label_sim, req_value, prov_value, gate, name_sim, value_score, distance, rule, attr_score, count_needed, count_available, req_score and is_gap (no counterpart, a count shortfall or a value below 1). Same parameters as tree_coverage. query_tree_id is a tree id or a LIST of ids. Trees come from nodes_table/attrs_table (default 'tree_nodes'/'tree_attributes'); candidates from candidate_nodes_table/candidate_attrs_table (default: the same tables). Naming a nodes table without its attributes table is an error (pass NULL for none). Classes resolve class_id -> overrides_table (reviewed vocabulary) -> label_table (classifier). Stage 1 ranks candidates by containment (+ tie_break x jaccard) when every candidate tree is reviewed, else by weighted jaccard (mode 'auto'). candidate_multiset_table: a precomputed index of the candidate class multisets (tree_class_multiset output: entity_id, feature_id, weight[, reviewed]) makes stage 1 skip resolving every candidate tree. | NULL | [SELECT * FROM tree_coverage_trace('R01', candidates := ['P001']) WHERE is_gap;] |
| tree_from_json | scalar | Turns a tree from upstream extraction (JSON: {tree_id, source, lang, root: {node_id, kind, edge, label, context, class_id, count, attributes: [{attr_id, name, text, lo, hi, unit, bound, category}], children: [...]}}) into a LIST of node STRUCTs with normalised attributes (base units, dimension; text-only values are parsed with parse_quantity in the document's lang, or lang if given; unit_table extends the units seed as in parse_quantity). Attribute STRUCTs carry tree_id and node_id. Unnest it into tree_nodes and tree_attributes. A non-positive count, or a key close to a known one (childs, txt), is an error naming the node; other keys are ignored. Schema: docs/requirements-matching/tree.schema.json. | NULL | [SELECT unnest(n) FROM (SELECT unnest(tree_from_json(content)) AS n FROM read_text('requests/*.json'));] |
| tree_multiset | scalar | The class multiset of one tree given as a LIST of STRUCT(node_id, parent_id, node_kind, edge_type, class_id, count): counts multiply down the tree, add across AND siblings and take the elementwise maximum across OR alternatives. The building block of tree_class_multiset. | NULL | [SELECT tree_multiset(list(struct_pack(node_id, parent_id, node_kind, edge_type, class_id, count))) FROM tree_nodes GROUP BY tree_id;] |
| tversky_similarity | scalar | Asymmetric Tversky similarity of two lists treated as sets: \|q∩c\| / (\|q∩c\| + alpha*\|q\c\| + beta*\|c\q\|). alpha = beta = 1 is Jaccard, alpha = 1 and beta = 0 is containment. Pass both alpha and beta, alpha first: DuckDB 1.5.6 binds scalar named arguments by position, so (..., beta := x, alpha := y) silently swaps them there. NULL in gives NULL; two empty lists give 0.0. | NULL | [SELECT tversky_similarity(['a','b','c'], ['b','c','d']); -- 0.5] |
| tversky_similarity | scalar | Asymmetric Tversky similarity of two lists treated as sets: \|q∩c\| / (\|q∩c\| + alpha*\|q\c\| + beta*\|c\q\|). alpha = beta = 1 is Jaccard, alpha = 1 and beta = 0 is containment. Pass both alpha and beta, alpha first: DuckDB 1.5.6 binds scalar named arguments by position, so (..., beta := x, alpha := y) silently swaps them there. NULL in gives NULL; two empty lists give 0.0. | NULL | [SELECT tversky_similarity(['a','b'], ['a','b','c','d'], 1.0, 0.25); -- 0.8] |
| uncertain_decisions | table | The external re-rank hook: the low-margin items of a classify_to_catalogue result (margin < threshold, default 0.2, or the lowest-margin share of the items), overridden items excluded, lowest margin first. request is JSON text for an LLM or a human: tree_id, node_id, text (texts_table: [tree_id,] node_id, text), margin and the top candidates with p, label and definition (catalogue_table: class_id[, label_en\|label, definition]). Write the answers back as an answers_table for classify_to_catalogue, or as vocabulary_overrides per wording. | NULL | [SELECT request FROM uncertain_decisions(decisions := 'decided', share := 0.15, texts_table := 'texts', catalogue_table := 'catalogue_classes');] |
| validate_trees | table | Checks property trees and returns one row per issue (tree_id, node_id, attr_id, issue, severity, detail). Errors: duplicate_node, null_key, bad_node_kind, bad_edge_type, bad_count, orphan, no_root, multiple_roots, cycle, and with an attribute table duplicate_attribute, attribute_on_container, orphan_attribute, bad_interval, bad_bound. Warnings: mixed_edge_types, unknown_unit, category_without_dimension, mixed_dimensions (one attribute name with several dimensions within a source). | NULL | [SELECT * FROM validate_trees('tree_nodes', 'tree_attributes');] |
| value_satisfies | scalar | Direction-aware comparison of a requirement with a provided value (both STRUCTs with lo, hi, dimension; the requirement also bound, either may carry unit). Returns STRUCT(score, distance, rule). at_least uses the high end of a provided range, at_most the low end, within the share of the required range covered, equals the relative difference; an unqualified value is a point widened by ±0.01 decades unless default_bound gives it a direction. score is 1 within tol (decades on log scale, relative on linear scale), falling to 0 across soft_band; distance is relative (absolute in K for temperature, which uses a 1 K tolerance and 2.5 K band). Different dimensions, or different currencies, give rule 'gate' and score 0. scale defaults per dimension (linear for time, currency, percent, sound level). Pass the options in order: scale, tol, soft_band, default_bound. | NULL | [SELECT value_satisfies(parse_quantity('24 cm'), parse_quantity('26 cm'), NULL, 0.02, 0.05, 'at_least');] |
| value_satisfies | scalar | Direction-aware comparison of a requirement with a provided value (both STRUCTs with lo, hi, dimension; the requirement also bound, either may carry unit). Returns STRUCT(score, distance, rule). at_least uses the high end of a provided range, at_most the low end, within the share of the required range covered, equals the relative difference; an unqualified value is a point widened by ±0.01 decades unless default_bound gives it a direction. score is 1 within tol (decades on log scale, relative on linear scale), falling to 0 across soft_band; distance is relative (absolute in K for temperature, which uses a 1 K tolerance and 2.5 K band). Different dimensions, or different currencies, give rule 'gate' and score 0. scale defaults per dimension (linear for time, currency, percent, sound level). Pass the options in order: scale, tol, soft_band, default_bound. | NULL | [SELECT value_satisfies(parse_quantity('at least 24 cm'), parse_quantity('0.235 m'), 'linear');] |
| value_satisfies | scalar | Direction-aware comparison of a requirement with a provided value (both STRUCTs with lo, hi, dimension; the requirement also bound, either may carry unit). Returns STRUCT(score, distance, rule). at_least uses the high end of a provided range, at_most the low end, within the share of the required range covered, equals the relative difference; an unqualified value is a point widened by ±0.01 decades unless default_bound gives it a direction. score is 1 within tol (decades on log scale, relative on linear scale), falling to 0 across soft_band; distance is relative (absolute in K for temperature, which uses a 1 K tolerance and 2.5 K band). Different dimensions, or different currencies, give rule 'gate' and score 0. scale defaults per dimension (linear for time, currency, percent, sound level). Pass the options in order: scale, tol, soft_band, default_bound. | NULL | [SELECT value_satisfies(parse_quantity('at least 24 cm'), parse_quantity('23 cm'), NULL, 0.05, 0.1);] |
| value_satisfies | scalar | Direction-aware comparison of a requirement with a provided value (both STRUCTs with lo, hi, dimension; the requirement also bound, either may carry unit). Returns STRUCT(score, distance, rule). at_least uses the high end of a provided range, at_most the low end, within the share of the required range covered, equals the relative difference; an unqualified value is a point widened by ±0.01 decades unless default_bound gives it a direction. score is 1 within tol (decades on log scale, relative on linear scale), falling to 0 across soft_band; distance is relative (absolute in K for temperature, which uses a 1 K tolerance and 2.5 K band). Different dimensions, or different currencies, give rule 'gate' and score 0. scale defaults per dimension (linear for time, currency, percent, sound level). Pass the options in order: scale, tol, soft_band, default_bound. | NULL | [SELECT value_satisfies(parse_quantity('max. 38 dB(A)'), parse_quantity('35 dB(A)'));] |
| vocabulary_review_queue | table | The vocabulary review queue: one row per distinct wording and source of the item nodes in trees_table (default 'tree_nodes'), with n_occurrences, n_trees_affected, the classifier's top1/p1 and top2/p2, margin and impact = n_trees_affected x (1 - margin), highest impact first. decisions is a classify_to_catalogue result: per wording (tree_id NULL, node_id = wording) or per node (averaged over the wording's occurrences). Wordings in overrides_table are reviewed and leave the queue; sources restricts the document families. | NULL | [SELECT * FROM vocabulary_review_queue(trees_table := 'programme_nodes', decisions := 'wording_decisions', overrides_table := 'vocabulary_overrides') LIMIT 10;] |
| vss_join | table_macro | NULL | NULL | |
| vss_match | table_macro | NULL | NULL | |
| weighted_containment | scalar | Share of a MAP(key, weight) query multiset covered by the candidate: Σ min(q_k, c_k) / Σ q_k. Counts matter: 4 requested zones are half covered by 2. Weights must be non-negative; a NULL weight counts as 1. An empty query gives NULL. | NULL | [SELECT weighted_containment(MAP {'a': 3, 'b': 1}, MAP {'a': 2, 'b': 5}); -- 0.75] |
| weighted_jaccard | scalar | Weighted Jaccard similarity of two MAP(key, weight) multisets: Σ min(q_k, c_k) / Σ max(q_k, c_k). Weights must be non-negative; a NULL weight counts as 1. Two empty maps give 0.0. | NULL | [SELECT weighted_jaccard(MAP {'heating': 2, 'massage': 4}, MAP {'heating': 4, 'ventilation': 2}); -- 0.2] |
| weighted_minhash | scalar | Weighted MinHash signature of a MAP(key, weight) multiset by improved consistent weighted sampling (ICWS, Ioffe 2010): the share of equal positions of two signatures estimates their weighted Jaccard. num_hashes positions (1..4096), seeded (default 7); hashing is our own (FNV-1a, SplitMix64), independent of DuckDB's hash. A NULL weight counts as 1, zero weights are absent, negative weights are an error; NULL or an empty multiset gives NULL. | NULL | [SELECT weighted_minhash(MAP {'a': 1.0, 'b': 2.0}, 128);] |
| weighted_minhash | scalar | Weighted MinHash signature of a MAP(key, weight) multiset by improved consistent weighted sampling (ICWS, Ioffe 2010): the share of equal positions of two signatures estimates their weighted Jaccard. num_hashes positions (1..4096), seeded (default 7); hashing is our own (FNV-1a, SplitMix64), independent of DuckDB's hash. A NULL weight counts as 1, zero weights are absent, negative weights are an error; NULL or an empty multiset gives NULL. | NULL | [SELECT weighted_minhash(MAP {'a': 1.0, 'b': 2.0}, 128, 7);] |
| weighted_tversky | scalar | Weighted Tversky similarity of two MAP(key, weight) multisets: Σmin / (Σmin + alpha*Σ(q-c)+ + beta*Σ(c-q)+). alpha = beta = 1 (the two-argument default) is weighted Jaccard, alpha = 1 and beta = 0 is weighted containment. Pass both alpha and beta, alpha first: DuckDB 1.5.6 binds scalar named arguments by position, so (..., beta := x, alpha := y) silently swaps them there. | NULL | [SELECT weighted_tversky(MAP {'a': 3, 'b': 1}, MAP {'a': 2, 'c': 2}); -- 0.3333] |
| weighted_tversky | scalar | Weighted Tversky similarity of two MAP(key, weight) multisets: Σmin / (Σmin + alpha*Σ(q-c)+ + beta*Σ(c-q)+). alpha = beta = 1 (the two-argument default) is weighted Jaccard, alpha = 1 and beta = 0 is weighted containment. Pass both alpha and beta, alpha first: DuckDB 1.5.6 binds scalar named arguments by position, so (..., beta := x, alpha := y) silently swaps them there. | NULL | [SELECT weighted_tversky(MAP {'a': 3, 'b': 1}, MAP {'a': 2, 'c': 2}, 0.5, 0.5); -- 0.5] |
| wl_fingerprint | table_macro | Depth-expanded component fingerprint of one material, as component -> number of distinct depths at which it is reachable. Written as a non-recursive bounded expansion on purpose: DuckDB cannot decorrelate a recursive CTE inside a correlated scalar subquery, which is exactly how wl_kernel_similarity uses it. Iterations 1-5 are exact; higher values are capped at depth 5. | NULL | [SELECT * FROM wl_fingerprint('PUMP-A', 3, 'bom_items')] |
| wl_kernel_similarity | macro | Weisfeiler-Lehman kernel similarity between two materials' BOM structures, comparing their depth-expanded component fingerprints. Returns a score in [0, 1] where 1 means structurally identical. Iterations 1-5 are exact; higher values are capped at depth 5. | NULL | [SELECT wl_kernel_similarity('PUMP-A', 'PUMP-B')] |
### Overloaded Functions
This extension does not add any function overloads.
### Added Types
This extension does not add any types.
### Added Settings
| name | description | input_type | scope | aliases | typed_value |
|--------------------------------------|-----------------------------------------------------------------------------------------------------------------------------|------------|--------|---------|-------------|
| anofox_telemetry_enabled | Enable or disable anonymous usage telemetry | BOOLEAN | GLOBAL | [] | true |
| anofox_telemetry_key | PostHog API key for telemetry | VARCHAR | GLOBAL | [] | |
| datazoo_banner | Show the DataZoo feedback banner when an extension is loaded in an interactive terminal (at most once a day per extension). | BOOLEAN | GLOBAL | [] | true |
| hnsw_ef_search | experimental: override the ef_search parameter when scanning HNSW indexes | BIGINT | GLOBAL | [] | NULL |
| hnsw_enable_experimental_persistence | experimental: enable creating HNSW indexes in persistent databases | BOOLEAN | GLOBAL | [] | false |
| hnsw_prefilter | experimental: apply static table filters before searching HNSW indexes | BOOLEAN | GLOBAL | [] | true |
| temp_directory | Set the directory to which temporary files are written. | VARCHAR | GLOBAL | [] | post.db.tmp |