Heratio Help Center article. Category: Technical / AI & Automation.

Semantic Search - Technical Manual

Overview

The Semantic Search system provides query expansion capabilities for Heratio archives using a thesaurus-based approach. It integrates with Elasticsearch for search optimization and supports multiple data sources including WordNet, Wikidata, and local synonym definitions.

Namespace: AtomFramework\Services\SemanticSearch Database: Laravel Query Builder (Illuminate\Database\Capsule\Manager) Location: /usr/share/nginx/archive/atom-framework/src/Services/SemanticSearch/

Architecture

System Architecture

Entity Relationship Diagram (ERD)

Database Schema

Table Relationships

Database Schema DDL

Migration File

Location: /usr/share/nginx/archive/atom-framework/database/migrations/2026_01_21_semantic_search_tables.sql

-- Thesaurus Terms
CREATE TABLE IF NOT EXISTS ahg_thesaurus_term (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    term VARCHAR(255) NOT NULL,
    source VARCHAR(50) NOT NULL DEFAULT 'local',
    domain VARCHAR(100) DEFAULT 'general',
    external_id VARCHAR(255) NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY idx_term_source (term, source),
    INDEX idx_source (source),
    INDEX idx_domain (domain)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Synonyms
CREATE TABLE IF NOT EXISTS ahg_thesaurus_synonym (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    term_id BIGINT UNSIGNED NOT NULL,
    synonym VARCHAR(255) NOT NULL,
    relationship_type VARCHAR(50) DEFAULT 'exact',
    weight DECIMAL(3,2) DEFAULT 0.80,
    source VARCHAR(50) DEFAULT 'local',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (term_id) REFERENCES ahg_thesaurus_term(id) ON DELETE CASCADE,
    UNIQUE KEY idx_term_synonym (term_id, synonym),
    INDEX idx_synonym (synonym),
    INDEX idx_weight (weight)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Sync Log
CREATE TABLE IF NOT EXISTS ahg_thesaurus_sync_log (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    source VARCHAR(50) NOT NULL,
    status VARCHAR(20) DEFAULT 'running',
    terms_synced INT DEFAULT 0,
    started_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    completed_at DATETIME NULL,
    error_message TEXT NULL,
    INDEX idx_source_status (source, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Embeddings
CREATE TABLE IF NOT EXISTS ahg_thesaurus_embedding (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    term_id BIGINT UNSIGNED NOT NULL,
    model VARCHAR(100) NOT NULL,
    embedding BLOB NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (term_id) REFERENCES ahg_thesaurus_term(id) ON DELETE CASCADE,
    UNIQUE KEY idx_term_model (term_id, model)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Search Log
CREATE TABLE IF NOT EXISTS ahg_semantic_search_log (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    original_query VARCHAR(500) NOT NULL,
    expanded_query TEXT NULL,
    was_expanded TINYINT(1) DEFAULT 0,
    terms_expanded INT DEFAULT 0,
    user_id INT NULL,
    ip_address VARCHAR(45) NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_created (created_at),
    INDEX idx_query (original_query(100))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Service Classes

Class Diagram