Fuzzy Matching Bug Fix Process

May contain stale references

This document was audited on 2026-06-22 and contains references that need updating: PostgreSQL-native matching functions moved/renamed. Cross-check critical details against the current codebase before relying on them.

Summary

This document outlines the comprehensive approach taken to fix fuzzy matching issues and implement organization-specific configuration for the ERP-Unlocked platform.

Problem Analysis

The original fuzzy matching system had several issues:

1. Hardcoded Configuration: All fuzzy matching thresholds and rules were hardcoded, making it impossible to customize behavior per organization.
2. Inflexible Prefix Rules: Prefix compatibility rules were static and couldn’t be adapted to different organizations’ product catalogs.
3. No Organization-Specific Tuning: Different organizations have different product naming conventions and requirements.

Solution Overview

1. Organization-Specific Configuration

Implemented a new organization_settings table to store organization-specific configurations:

interface OrganizationSettings {
  id: string;
  clerkOrganizationId: string;
  fuzzyMatchingConfig: string; // JSON
  // ... other config fields for future expansion
}

2. Fuzzy Matching Configuration Schema

Created a comprehensive Zod schema for fuzzy matching configuration:

interface FuzzyMatchingConfig {
  prefixRules: Array<{
    incompatible: [string, string];
    description: string;
    category?: string;
    severity: 'block' | 'warn' | 'allow';
  }>;
  thresholds: {
    trigram: number; // 0.0 - 1.0
    fulltext: number; // 0.0 - 1.0
    maxLevenshteinDistance: number; // 0 - 10
  };
  baseSimilarityThreshold: number; // 0.0 - 1.0
  enableDynamicValidation: boolean;
}

3. Updated Fuzzy Matching Functions

Modified the core fuzzy matching functions to use organization-specific configuration:

• findTrigramMatch() - Now accepts clerkOrganizationId and uses org-specific trigram threshold
• findFullTextMatch() - Now accepts clerkOrganizationId and uses org-specific fulltext threshold
• findFuzzyMatch() - Main orchestrator function updated to pass clerkOrganizationId to sub-functions
• validatePrefixCompatibility() - New function to validate prefix compatibility using org-specific rules

4. Service Layer

Created OrganizationSettingsService to manage organization settings:

class OrganizationSettingsService {
  async getFuzzyMatchingConfig(clerkOrganizationId: string): Promise<FuzzyMatchingConfig>;
  async updateFuzzyMatchingConfig(
    clerkOrganizationId: string,
    config: FuzzyMatchingConfig,
    userId: string
  ): Promise<FuzzyMatchingConfig>;
}

5. API Endpoints

Created REST API endpoints for managing fuzzy matching settings:

• GET /api/organizations/settings/fuzzy-matching - Retrieve configuration
• PUT /api/organizations/settings/fuzzy-matching - Update configuration

6. User Interface

Built a React component for managing fuzzy matching settings:

• FuzzyMatchingSettingsEditor - Comprehensive UI for editing all configuration options
• Astro page at /organization/settings/fuzzy-matching for admin access

Organization-Specific Configuration

Overview

The fuzzy matching system now supports organization-specific configuration through the organization_settings table. This allows each organization to customize their fuzzy matching behavior according to their specific business needs and product catalog characteristics.

How It Works

1. Configuration Storage: Each organization’s fuzzy matching configuration is stored in the organization_settings table as JSON in the fuzzy_matching_config field.

2. Default Fallback: If no organization-specific configuration exists, the system uses the default configuration defined in DEFAULT_FUZZY_MATCHING_CONFIG.

3. Dynamic Loading: The fuzzy matching functions automatically load the organization-specific configuration when processing orders.

Configuration Structure

interface FuzzyMatchingConfig {
  prefixRules: Array<{
    incompatible: [string, string];
    description: string;
    category?: string;
    severity: 'block' | 'warn' | 'allow';
  }>;
  thresholds: {
    trigram: number; // 0.0 - 1.0
    fulltext: number; // 0.0 - 1.0
    maxLevenshteinDistance: number; // 0 - 10
  };
  baseSimilarityThreshold: number; // 0.0 - 1.0
  enableDynamicValidation: boolean;
}

Default Configuration

The system includes sensible defaults for organizations that haven’t customized their settings:

const DEFAULT_FUZZY_MATCHING_CONFIG: FuzzyMatchingConfig = {
  prefixRules: [
    {
      incompatible: ['c', 'pc'],
      description: 'Brass vs Composite parts',
      category: 'material_type',
      severity: 'block',
    },
    {
      incompatible: ['p', 'pc'],
      description: 'Plastic vs Composite parts',
      category: 'material_type',
      severity: 'block',
    },
    {
      incompatible: ['b', 'br'],
      description: 'Brass vs Bronze parts',
      category: 'material_type',
      severity: 'block',
    },
    {
      incompatible: ['s', 'st'],
      description: 'Steel vs Stainless steel parts',
      category: 'material_type',
      severity: 'block',
    },
    {
      incompatible: ['c', 'e'],
      description: 'C prefix vs E prefix parts',
      category: 'product_type',
      severity: 'block',
    },
  ],
  thresholds: {
    trigram: 0.74,
    fulltext: 0.5,
    maxLevenshteinDistance: 2,
  },
  baseSimilarityThreshold: 0.6,
  enableDynamicValidation: true,
};

Implementation Details

Database Schema

CREATE TABLE "organization_settings" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
  "clerk_organization_id" text NOT NULL,
  "fuzzy_matching_config" text,
  "sync_config" text,
  "alerting_config" text,
  "feature_flags" text,
  "business_rules_config" text,
  "integration_config" text,
  "ui_config" text,
  "security_config" text,
  "last_modified_by" text,
  "version" integer DEFAULT 1,
  "created_at" timestamp DEFAULT now() NOT NULL,
  "updated_at" timestamp DEFAULT now() NOT NULL,
  CONSTRAINT "organization_settings_clerk_organization_id_unique" UNIQUE("clerk_organization_id")
);

CREATE INDEX "org_settings_clerk_org_idx" ON "organization_settings" USING btree ("clerk_organization_id");

Key Functions Updated

1. findTrigramMatch(): Now loads organization-specific trigram threshold
2. findFullTextMatch(): Now loads organization-specific fulltext threshold and Levenshtein distance
3. validatePrefixCompatibility(): New function that validates prefix compatibility using organization-specific rules
4. findFuzzyMatch(): Updated to pass clerkOrganizationId to relevant sub-functions

Error Handling

The system includes comprehensive error handling:

• Graceful fallback to default configuration if organization config is invalid
• Detailed logging for debugging configuration issues
• Validation of configuration before saving

Testing

Unit Tests

Created comprehensive unit tests for:

• OrganizationSettingsService - Service layer functionality
• API endpoints - Request/response handling
• Fuzzy matching functions - Integration with organization config

Integration Tests

• End-to-end testing of fuzzy matching with organization-specific configuration
• API endpoint testing with various configuration scenarios
• UI component testing for configuration management

Future Enhancements

Planned Features

1. Sync Schedules: Organization-specific data sync schedules
2. Alerting Configuration: Custom alerting rules per organization
3. Feature Flags: Organization-level feature toggles
4. Business Rules: Custom business logic configuration
5. Integration Settings: Organization-specific integration configurations
6. UI Customization: Organization-specific UI themes and layouts
7. Security Policies: Organization-specific security configurations

System Admin Panel

A comprehensive system admin panel is planned to manage:

• Organization settings across all customers
• System-wide configuration management
• Customer support tools
• Analytics and monitoring
• Bulk configuration updates

Migration Strategy

Database Migration

The implementation includes a database migration that:

1. Creates the organization_settings table
2. Sets up proper indexes for performance
3. Includes all necessary constraints

Backward Compatibility

The system maintains backward compatibility by:

• Using default configuration when no organization-specific config exists
• Graceful degradation if configuration loading fails
• No breaking changes to existing API contracts

Monitoring and Observability

Logging

Enhanced logging includes:

• Configuration loading events
• Threshold usage in matching functions
• Prefix compatibility validation results
• Configuration update events

Metrics

Key metrics to monitor:

• Configuration load times
• Match success rates by organization
• Threshold effectiveness
• Configuration update frequency

Security Considerations

Access Control

• Only organization admins can modify fuzzy matching settings
• All configuration changes are logged with user attribution
• Input validation prevents malicious configuration

Data Protection

• Configuration data is stored securely in the database
• No sensitive information in configuration JSON
• Proper error handling prevents information leakage

Conclusion

This implementation provides a robust, flexible foundation for organization-specific fuzzy matching configuration while maintaining system reliability and performance. The modular design allows for easy extension to other organization-specific settings in the future.