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.
Maintainer(s):
jrosskopf
Installing and Loading
INSTALL anofox_similarity FROM community;
LOAD anofox_similarity;
Example
-- 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');
About anofox_similarity
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_jaccardranks materials by BOM component overlap.wl_kernel_similarity/find_similar_materials_wl_kerneluse a Weisfeiler-Lehman graph kernel to capture BOM topology, not just which components are shared but how they're structurally arranged.infer_predecessorsdetects 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_analogsfinds established products to seed a forecast for a brand-new material with no history at all.compute_fused_embeddingscombines structural, transactional, and text embedding signals into one fusable vector, indexable with DuckDB'svssextension 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.
Added Functions
| function_name | function_type | description | comment | examples |
|---|---|---|---|---|
| aggregate_material_components | table_macro | NULL | NULL | |
| bom_common_components | table_macro | NULL | NULL | |
| bom_dfs_neighborhood | table_macro | NULL | NULL | |
| bom_explosion_1level | table_macro | NULL | NULL | |
| bom_explosion_multilevel | table_macro | NULL | NULL | |
| bom_to_items | table_macro | NULL | NULL | |
| bom_where_used | table_macro | NULL | NULL | |
| check_anofox_forecast_available | macro | NULL | NULL | |
| check_duckpgq_available | macro | NULL | NULL | |
| 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();] |
| 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);] |
| compute_domain_specific_statistics | table_macro | NULL | NULL | |
| compute_fused_embeddings | table_macro | NULL | NULL | |
| 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 | NULL | NULL | |
| compute_transactional_embeddings | table | Computes 128-dimensional transactional embeddings from goods movement data and upserts them into 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);] |
| create_bom_property_graph | table_macro | NULL | NULL | |
| dynamics365_to_bom_component | table_macro | NULL | NULL | |
| dynamics365_to_bom_header | table_macro | NULL | NULL | |
| dynamics365_to_materials | table_macro | NULL | NULL | |
| embed_text | macro | NULL | NULL | |
| 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', 'gemini-api', '{"api_key": "…"}');] |
| extract_material_descriptions | table_macro | NULL | NULL | |
| extract_ts_features | table_macro | NULL | NULL | |
| filter_recent_movements | table_macro | NULL | NULL | |
| 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. | NULL | [SELECT * FROM find_similar_materials_jaccard('MAT-001', 10);, SELECT * FROM find_similar_materials_jaccard('MAT-001', 20, min_similarity := 0.3);] |
| 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);] |
| 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 | NULL | NULL | |
| 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] |
| pragma_hnsw_index_info | table | NULL | NULL | |
| recompute_embedding_statistics | table_macro | NULL | NULL | |
| sap_to_bom_items | table_macro | NULL | NULL | |
| sap_to_materials | table_macro | NULL | NULL | |
| sap_to_materials_with_desc | table_macro | NULL | NULL | |
| vss_join | table_macro | NULL | NULL | |
| vss_match | table_macro | NULL | NULL | |
| wl_fingerprint | table_macro | NULL | NULL | |
| wl_kernel_similarity | macro | NULL | NULL |
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 |
|---|---|---|---|---|
| anofox_telemetry_enabled | Enable or disable anonymous usage telemetry | BOOLEAN | GLOBAL | [] |
| anofox_telemetry_key | PostHog API key for telemetry | VARCHAR | GLOBAL | [] |
| hnsw_ef_search | experimental: override the ef_search parameter when scanning HNSW indexes | BIGINT | GLOBAL | [] |
| hnsw_enable_experimental_persistence | experimental: enable creating HNSW indexes in persistent databases | BOOLEAN | GLOBAL | [] |