Code Reference

Technical implementation of the semantido project.

Decorators

@semantic_table

A class decorator to enrich SQLAlchemy models with semantic metadata.

This metadata is used by the SQLAlchemySemanticBridge to build a semantic layer, helping tools and LLMs understand the purpose, context, and filtering requirements of the underlying database table.Decorator for adding semantic information to SQLAlchemy models

Parameters:

Name	Type	Description	Default
description	str	A human-readable explanation of what the table represents.	required
synonyms	Optional[list[str]]	Alternative names for the entity (e.g., ["client", "customer"]).	None
sql_filters	Optional[list[str]]	A list of SQL fragments used for default filtering or row-level security.	None
application_context	Optional[str]	The technical or functional scope within the app.	None
business_context	Optional[str]	The business domain or logic this table belongs to.	None

Examples:

@semantic_table(
    description="User information and login profile",
    synonyms=["user profile", "client"],
    application_context="Registered users on the platform"
)
class User(Base):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True)

Source code in src/semantido/decorators/semantic_table.py

def semantic_table(
    description: str,
    synonyms: Optional[list[str]] = None,
    sql_filters: Optional[list[str]] = None,
    application_context: Optional[str] = None,
    business_context: Optional[str] = None,
):
    """A class decorator to enrich SQLAlchemy models with semantic metadata.

    This metadata is used by the `SQLAlchemySemanticBridge` to build a semantic layer,
    helping tools and LLMs understand the purpose, context, and filtering requirements
    of the underlying database table.Decorator for adding semantic information to SQLAlchemy models

    Args:
        description: A human-readable explanation of what the table represents.
        synonyms: Alternative names for the entity (e.g., ["client", "customer"]).
        sql_filters: A list of SQL fragments used for default filtering or row-level security.
        application_context: The technical or functional scope within the app.
        business_context: The business domain or logic this table belongs to.

    Examples:
        ```python
        @semantic_table(
            description="User information and login profile",
            synonyms=["user profile", "client"],
            application_context="Registered users on the platform"
        )
        class User(Base):
            __tablename__ = "users"
            id = Column(Integer, primary_key=True)
        ```
    """

    def decorator(cls):
        cls.__semantic_description__ = description
        cls.__semantic_synonyms__ = synonyms
        cls.__semantic_sql_filters__ = sql_filters
        cls.__semantic_application_context__ = application_context
        cls.__semantic_business_context__ = business_context
        return cls

    return decorator

Generators

Extracts and synchronizes semantic metadata from SQLAlchemy models into a unified layer.

SQLAlchemySemanticBridge

Bridge between SQLAlchemy models and the semantic layer.

This class serves as the extraction engine that inspects SQLAlchemy's internal registry to generate a structured SemanticLayer. It handles the conversion of SQL types to normalized types, builds join conditions for relationships, and retrieves semantic metadata attached via decorators.

Source code in src/semantido/generators/semantic_bridge.py

class SQLAlchemySemanticBridge:
    """
    Bridge between SQLAlchemy models and the semantic layer.

    This class serves as the extraction engine that inspects SQLAlchemy's internal
    registry to generate a structured `SemanticLayer`. It handles the conversion of
    SQL types to normalized types, builds join conditions for relationships, and
    retrieves semantic metadata attached via decorators.
    """

    def __init__(self, base):
        self.base = base
        self.semantic_layer = SemanticLayer()
        self._model_registry: dict[str, Type] = {}

    def get_semantic_layer(self) -> SemanticLayer:
        """
        Retrieves the current semantic layer instance.

        Returns:
            SemanticLayer: The object containing extracted table and relationship metadata.
        """
        return self.semantic_layer

    def sync_from_models(self) -> SemanticLayer:
        """
        Extracts schema and semantic information from all mapped models.

        This method clears any previously cached metadata and performs a full scan
        of the SQLAlchemy registry to rebuild the semantic layer.

        Returns:
            SemanticLayer: The fully populated semantic layer.
        """
        self.semantic_layer.tables.clear()
        self.semantic_layer.relationships.clear()
        self._model_registry.clear()

        # Get all mapped classes
        for mapper in self.base.registry.mappers:
            clazz = mapper.class_
            table_name = mapper.persist_selectable.name

            # Add the current mapped table to the model registry
            self._model_registry[table_name] = clazz

            # Extract table information
            table = self._extract_table(clazz, mapper)
            self.semantic_layer.add_table(table)

            # Extract table relationships
            relationships = self._extract_relationships(clazz, mapper)
            for relationship in relationships:
                self.semantic_layer.add_relationship(relationship)

        return self.semantic_layer

    @staticmethod
    def _extract_table(clazz: Type, mapper) -> Table:
        """
        Transforms a SQLAlchemy mapped class into a semantic Table definition.

        Args:
            clazz: The Python class representing the model.
            mapper: The SQLAlchemy Mapper object containing low-level schema info.

        Returns:
            Table: A semantic representation of the table and its metadata.
        """

        table_name = mapper.persist_selectable.name

        description = getattr(clazz, "__semantic_description__", f"Table: {table_name}")
        synonyms = getattr(clazz, "__semantic_synonyms__", [])
        sql_filters = getattr(clazz, "__semantic_sql_filters__", [])
        application_context = getattr(clazz, "__semantic_application_context__", None)
        business_context = getattr(clazz, "__semantic_business_context__", None)

        primary_keys = [key.name for key in mapper.primary_key]
        primary_key = primary_keys[0] if primary_keys else None

        columns = []

        for name, prop in mapper.columns.items():
            column = SQLAlchemySemanticBridge._extract_column(clazz, name, prop)
            columns.append(column)

        return Table(
            name=table_name,
            description=description,
            columns=columns,
            primary_key=primary_key,
            synonyms=synonyms,
            sql_filters=sql_filters,
            application_context=application_context,
            business_context=business_context,
        )

    @staticmethod
    def _extract_column(clazz: Type, column_name: str, prop) -> Column:
        """
        Extracts semantic metadata and schema info for a specific column.

        Args:
            clazz: The model class where the column is defined.
            column_name: The name of the column attribute.
            prop: The SQLAlchemy column metadata or property.

        Returns:
            Column: The semantic Column object.
        """
        sql_column_meta = prop

        data_type = SQLAlchemySemanticBridge._map_sql_alchemy_type(sql_column_meta.type)
        description = getattr(
            clazz, f"{column_name}_description", f"Column: {column_name}"
        )
        privacy_level = getattr(
            clazz, f"{column_name}_privacy_level", PrivacyLevel.PUBLIC
        )
        sample_values = getattr(clazz, f"{column_name}_sample_values", None)
        synonyms = getattr(clazz, f"{column_name}_synonyms", [])
        application_rules = getattr(clazz, f"{column_name}_application_rules", [])

        # FK
        foreign_keys = len(sql_column_meta.foreign_keys) > 0
        references = None
        if foreign_keys:
            fk = list(sql_column_meta.foreign_keys)[0]
            references = f"{fk.column.table.name}.{fk.column.name}"

        return Column(
            name=column_name,
            data_type=data_type,
            description=description,
            privacy_level=privacy_level,
            sample_values=sample_values,
            synonyms=synonyms,
            is_foreign_key=foreign_keys,
            references=references,
            application_rules=application_rules,
        )

    @staticmethod
    def _extract_relationships(clazz, mapper) -> list[Relationship]:
        """Inspects a SQLAlchemy mapped class and its mapper to extract
         semantic relationship metadata.

        This method iterates through all relationships defined on the SQLAlchemy model,
        identifying the target tables, determining the cardinality (One-to-Many vs Many-to-One),
        builds the SQL join conditions, and retrieves any custom descriptions defined on the class.

        Args:
            clazz: The SQLAlchemy model class to inspect.
            mapper: The SQLAlchemy Mapper object associated with the class.

        Returns:
            list: A list of Relationship objects representing the semantic links to other tables.
        """

        relationships = []
        for relationship_name, relationship_meta in mapper.relationships.items():
            target = relationship_meta.mapper
            target_table = target.persist_selectable.name
            source_table = mapper.persist_selectable.name

            if relationship_meta.uselist:
                relationship_type = RelationshipType.ONE_TO_MANY
            else:
                relationship_type = RelationshipType.MANY_TO_ONE

            join_condition = SQLAlchemySemanticBridge._build_join_condition(
                relationship_meta
            )

            description = getattr(
                clazz,
                f"{relationship_name}_relationship_description",
                f"Relationship between {source_table} and {target_table}",
            )

            relationships.append(
                Relationship(
                    from_table=source_table,
                    to_table=target_table,
                    join_condition=join_condition,
                    relationship_type=relationship_type,
                    description=description,
                )
            )

        return relationships

    @staticmethod
    def _map_sql_alchemy_type(sql_type) -> str:
        """
        Maps SQLAlchemy types to Postgres types.

        Args:
            sql_type: The SQLAlchemy type instance

        Returns:
            str: A Postgres standardized type name (e.g., "INTEGER", "VARCHAR").
        """
        type_mapping = {
            String: "VARCHAR",
            Text: "TEXT",
            Integer: "INTEGER",
            Float: "FLOAT",
            Numeric: "DECIMAL",
            Boolean: "BOOLEAN",
            DateTime: "TIMESTAMP",
            Date: "DATE",
        }

        for sqlalchemy_type, pg_type in type_mapping.items():
            if isinstance(sql_type, sqlalchemy_type):
                return pg_type

        return str(sql_type)

    @staticmethod
    def _build_join_condition(relationship_meta) -> str:
        """Builds the join condition string for a given relationship metadata.

        Examples:
            If you have a relationship between a users table and a posts table where
            posts.user_id references users.id, the method returns:
            "users.id = posts.user_id"

            If the relationship involves multiple columns (a composite key), the method joins
            them with AND. For example, if a sales table joins a products table on both
            store_id and product_id the method returns:
            "products.store_id = sales.store_id AND products.product_id = sales.product_id"

        Args:
            relationship_meta (RelationshipMeta):
            The relationship metadata given by SQLAlchemy models.

        Returns:
            str: The join condition string for the given relationship metadata.

        """
        local_cols = []
        remote_cols = []

        for local, remote in relationship_meta.local_remote_pairs:
            local_cols.append(f"{local.table.name}.{local.name}")
            remote_cols.append(f"{remote.table.name}.{remote.name}")

        conditions = [
            f"{local} = {remote}" for local, remote in zip(local_cols, remote_cols)
        ]

        return " AND ".join(conditions)

get_semantic_layer()

Retrieves the current semantic layer instance.

Returns:

Name	Type	Description
SemanticLayer	SemanticLayer	The object containing extracted table and relationship metadata.

Source code in src/semantido/generators/semantic_bridge.py

def get_semantic_layer(self) -> SemanticLayer:
    """
    Retrieves the current semantic layer instance.

    Returns:
        SemanticLayer: The object containing extracted table and relationship metadata.
    """
    return self.semantic_layer

sync_from_models()

Extracts schema and semantic information from all mapped models.

This method clears any previously cached metadata and performs a full scan of the SQLAlchemy registry to rebuild the semantic layer.

Returns:

Name	Type	Description
SemanticLayer	SemanticLayer	The fully populated semantic layer.

Source code in src/semantido/generators/semantic_bridge.py

def sync_from_models(self) -> SemanticLayer:
    """
    Extracts schema and semantic information from all mapped models.

    This method clears any previously cached metadata and performs a full scan
    of the SQLAlchemy registry to rebuild the semantic layer.

    Returns:
        SemanticLayer: The fully populated semantic layer.
    """
    self.semantic_layer.tables.clear()
    self.semantic_layer.relationships.clear()
    self._model_registry.clear()

    # Get all mapped classes
    for mapper in self.base.registry.mappers:
        clazz = mapper.class_
        table_name = mapper.persist_selectable.name

        # Add the current mapped table to the model registry
        self._model_registry[table_name] = clazz

        # Extract table information
        table = self._extract_table(clazz, mapper)
        self.semantic_layer.add_table(table)

        # Extract table relationships
        relationships = self._extract_relationships(clazz, mapper)
        for relationship in relationships:
            self.semantic_layer.add_relationship(relationship)

    return self.semantic_layer

Defines the data structures for the semantic representation of the database schema.

Column dataclass

Represents a database column with enriched semantic metadata.

Attributes:

Name	Type	Description
name	str	The physical name of the column in the database.
data_type	str	The normalized data type (e.g., VARCHAR, INTEGER).
description	str	A human-readable explanation of the column's content.
privacy_level	PrivacyLevel	The sensitivity classification of the data.
sample_values	Optional[list[str]]	A list of example data points to help clarify the content.
synonyms	Optional[list[str]]	Alternative terms for the column name.
is_foreign_key	bool	Boolean flag indicating if this column links to another table.
references	Optional[str]	The target table and column (format: 'table.column') if a foreign key.
application_rules	Optional[list[str]]	Specific business logic or constraints applied to this column.

Source code in src/semantido/generators/semantic_layer.py

@dataclass
class Column:
    # pylint: disable=R0902
    """
    Represents a database column with enriched semantic metadata.

    Attributes:
        name: The physical name of the column in the database.
        data_type: The normalized data type (e.g., VARCHAR, INTEGER).
        description: A human-readable explanation of the column's content.
        privacy_level: The sensitivity classification of the data.
        sample_values: A list of example data points to help clarify the content.
        synonyms: Alternative terms for the column name.
        is_foreign_key: Boolean flag indicating if this column links to another table.
        references: The target table and column (format: 'table.column') if a foreign key.
        application_rules: Specific business logic or constraints applied to this column.
    """

    name: str
    data_type: str
    description: str
    privacy_level: PrivacyLevel
    sample_values: Optional[list[str]] = None
    synonyms: Optional[list[str]] = None
    is_foreign_key: bool = False
    references: Optional[str] = None  # Format: table.column
    application_rules: Optional[list[str]] = None

PrivacyLevel

Bases: Enum

Defines the data sensitivity levels for columns.

Used to inform downstream consumers (like LLMs or BI tools) about the accessibility and security requirements of specific data points.

Source code in src/semantido/generators/semantic_layer.py

class PrivacyLevel(Enum):
    """
    Defines the data sensitivity levels for columns.

    Used to inform downstream consumers (like LLMs or BI tools) about
    the accessibility and security requirements of specific data points.
    """

    PUBLIC = "public"
    RESTRICTED = "restricted"
    CONFIDENTIAL = "confidential"

Relationship dataclass

Represents a semantic link between two database tables.

Attributes:

Name	Type	Description
from_table	str	The name of the source table.
to_table	str	The name of the target table.
join_condition	str	The SQL fragment defining how the tables link.
relationship_type	RelationshipType	The cardinality of the link.
description	str	A plain-language explanation of the relationship logic.

Source code in src/semantido/generators/semantic_layer.py

@dataclass
class Relationship:
    """
    Represents a semantic link between two database tables.

    Attributes:
        from_table: The name of the source table.
        to_table: The name of the target table.
        join_condition: The SQL fragment defining how the tables link.
        relationship_type: The cardinality of the link.
        description: A plain-language explanation of the relationship logic.
    """

    from_table: str
    to_table: str
    join_condition: str
    relationship_type: (
        RelationshipType  # Example: "one-to-many", "many-to-one", "many-to-many"
    )
    description: str

RelationshipType

Bases: Enum

Specifies the cardinality of a database relationship.

Helps in determining how to construct joins and aggregate data.

Source code in src/semantido/generators/semantic_layer.py

class RelationshipType(Enum):
    """
    Specifies the cardinality of a database relationship.

    Helps in determining how to construct joins and aggregate data.
    """

    ONE_TO_MANY = "one-to-many"
    MANY_TO_ONE = "many-to-one"
    MANY_TO_MANY = "many-to-many"

SemanticLayer dataclass

The central repository for all semantic metadata extracted from the database.

This class serves as the final output of the synchronization process, containing structured information about tables, their relationships, and a global application glossary. It provides methods for serializing this metadata to JSON for use by external tools or LLMs.

Source code in src/semantido/generators/semantic_layer.py

@dataclass
class SemanticLayer:
    """
    The central repository for all semantic metadata extracted from the database.

    This class serves as the final output of the synchronization process,
    containing structured information about tables, their relationships,
    and a global application glossary. It provides methods for serializing
    this metadata to JSON for use by external tools or LLMs.
    """

    tables: dict[str, Table] = field(default_factory=dict)
    relationships: list[Relationship] = field(default_factory=list)
    application_glossary: dict[str, str] = field(default_factory=dict)

    def add_table(self, table: Table):
        """
        Registers a new table definition in the semantic layer.

        Args:
            table: The Table object containing columns and semantic metadata.
        """
        self.tables[table.name] = table

    def add_relationship(self, relationship: Relationship):
        """
        Registers a relationship between two tables in the semantic layer.

        Args:
            relationship: The Relationship object defining the join logic and cardinality.
        """
        self.relationships.append(relationship)

    def to_dict(self) -> dict:
        """
        Converts the entire semantic layer into a nested dictionary structure.

        Returns:
            dict: A dictionary representation suitable for JSON serialization.
        """
        return {
            "tables": {
                name: {
                    "name": table.name,
                    "description": table.description,
                    "primary_key": table.primary_key,
                    "synonyms": table.synonyms,
                    "sql_filters": table.sql_filters,
                    "application_context": table.application_context,
                    "business_context": table.business_context,
                    "columns": [
                        {
                            "name": column.name,
                            "data_type": column.data_type,
                            "description": column.description,
                            "privacy_level": (
                                column.privacy_level.value
                                if isinstance(column.privacy_level, Enum)
                                else column.privacy_level
                            ),
                            "sample_values": column.sample_values,
                            "synonyms": column.synonyms,
                            "is_foreign_key": column.is_foreign_key,
                            "references": column.references,
                            "application_rules": column.application_rules,
                        }
                        for column in table.columns
                    ],
                }
                for name, table in self.tables.items()
            },
            "relationships": [
                {
                    "from_table": relationship.from_table,
                    "to_table": relationship.to_table,
                    "join_condition": relationship.join_condition,
                    "relationship_type": (
                        relationship.relationship_type.value
                        if isinstance(relationship.relationship_type, Enum)
                        else relationship.relationship_type
                    ),
                    "description": relationship.description,
                }
                for relationship in self.relationships
            ],
        }

    def to_json(self) -> str:
        """
        Exports the entire semantic layer as a formatted JSON string.

        Returns:
            str: Indented JSON string representing the semantic layer.
        """
        return json.dumps(self.to_dict(), indent=4)

    def save_to_file(self, file_path: str):
        """
        Serializes and saves the semantic layer to a JSON file.

        Args:
            file_path: The filesystem path where the JSON file will be created.
        """
        with open(file_path, "w", encoding="utf-8") as f:
            json.dump(self.to_dict(), f, indent=4)

add_relationship(relationship)

Registers a relationship between two tables in the semantic layer.

Parameters:

Name	Type	Description	Default
relationship	Relationship	The Relationship object defining the join logic and cardinality.	required

Source code in src/semantido/generators/semantic_layer.py

def add_relationship(self, relationship: Relationship):
    """
    Registers a relationship between two tables in the semantic layer.

    Args:
        relationship: The Relationship object defining the join logic and cardinality.
    """
    self.relationships.append(relationship)

add_table(table)

Registers a new table definition in the semantic layer.

Parameters:

Name	Type	Description	Default
table	Table	The Table object containing columns and semantic metadata.	required

Source code in src/semantido/generators/semantic_layer.py

def add_table(self, table: Table):
    """
    Registers a new table definition in the semantic layer.

    Args:
        table: The Table object containing columns and semantic metadata.
    """
    self.tables[table.name] = table

save_to_file(file_path)

Serializes and saves the semantic layer to a JSON file.

Parameters:

Name	Type	Description	Default
file_path	str	The filesystem path where the JSON file will be created.	required

Source code in src/semantido/generators/semantic_layer.py

def save_to_file(self, file_path: str):
    """
    Serializes and saves the semantic layer to a JSON file.

    Args:
        file_path: The filesystem path where the JSON file will be created.
    """
    with open(file_path, "w", encoding="utf-8") as f:
        json.dump(self.to_dict(), f, indent=4)

to_dict()

Converts the entire semantic layer into a nested dictionary structure.

Returns:

Name	Type	Description
dict	dict	A dictionary representation suitable for JSON serialization.

Source code in src/semantido/generators/semantic_layer.py

def to_dict(self) -> dict:
    """
    Converts the entire semantic layer into a nested dictionary structure.

    Returns:
        dict: A dictionary representation suitable for JSON serialization.
    """
    return {
        "tables": {
            name: {
                "name": table.name,
                "description": table.description,
                "primary_key": table.primary_key,
                "synonyms": table.synonyms,
                "sql_filters": table.sql_filters,
                "application_context": table.application_context,
                "business_context": table.business_context,
                "columns": [
                    {
                        "name": column.name,
                        "data_type": column.data_type,
                        "description": column.description,
                        "privacy_level": (
                            column.privacy_level.value
                            if isinstance(column.privacy_level, Enum)
                            else column.privacy_level
                        ),
                        "sample_values": column.sample_values,
                        "synonyms": column.synonyms,
                        "is_foreign_key": column.is_foreign_key,
                        "references": column.references,
                        "application_rules": column.application_rules,
                    }
                    for column in table.columns
                ],
            }
            for name, table in self.tables.items()
        },
        "relationships": [
            {
                "from_table": relationship.from_table,
                "to_table": relationship.to_table,
                "join_condition": relationship.join_condition,
                "relationship_type": (
                    relationship.relationship_type.value
                    if isinstance(relationship.relationship_type, Enum)
                    else relationship.relationship_type
                ),
                "description": relationship.description,
            }
            for relationship in self.relationships
        ],
    }

to_json()

Exports the entire semantic layer as a formatted JSON string.

Returns:

Name	Type	Description
str	str	Indented JSON string representing the semantic layer.

Source code in src/semantido/generators/semantic_layer.py

def to_json(self) -> str:
    """
    Exports the entire semantic layer as a formatted JSON string.

    Returns:
        str: Indented JSON string representing the semantic layer.
    """
    return json.dumps(self.to_dict(), indent=4)

Table dataclass

Represents a database table enriched with semantic and contextual information.

By capturing application and business contexts, this class helps disambiguate entities that might have generic names but specific roles in different domains.

Attributes:

Name	Type	Description
name	str	The physical name of the table in the database.
description	str	A human-readable explanation of the table's purpose.
columns	list[Column]	A list of Column objects belonging to this table.
primary_key	str	The name of the primary key column.
synonyms	Optional[list[str]]	Alternative names for the entity represented by the table.
sql_filters	Optional[list[str]]	Default SQL fragments for filtering or security.
application_context	Optional[str]	The functional area of the application using this table.
business_context	Optional[str]	The business domain or logic this table serves.

Source code in src/semantido/generators/semantic_layer.py

@dataclass
class Table:
    # pylint: disable=R0902
    """
    Represents a database table enriched with semantic and contextual information.

    By capturing application and business contexts, this class helps disambiguate
    entities that might have generic names but specific roles in different domains.

    Attributes:
        name: The physical name of the table in the database.
        description: A human-readable explanation of the table's purpose.
        columns: A list of Column objects belonging to this table.
        primary_key: The name of the primary key column.
        synonyms: Alternative names for the entity represented by the table.
        sql_filters: Default SQL fragments for filtering or security.
        application_context: The functional area of the application using this table.
        business_context: The business domain or logic this table serves.
    """

    name: str
    description: str
    columns: list[Column]
    primary_key: str
    synonyms: Optional[list[str]] = None
    sql_filters: Optional[list[str]] = None
    application_context: Optional[str] = None
    business_context: Optional[str] = None

Models

Defines the standard and semantic-enabled SQLAlchemy declarative base classes.

Base

Bases: DeclarativeBase

Default SQLAlchemy base class for declarative models.

Source code in src/semantido/models/declarative_base.py

class Base(DeclarativeBase):
    # pylint: disable=R0903
    """Default SQLAlchemy base class for declarative models."""

SemanticDeclarativeBase

Bases: SemanticBase, DeclarativeBase

Mixin for declarative models with semantic data support.

Source code in src/semantido/models/declarative_base.py

class SemanticDeclarativeBase(SemanticBase, DeclarativeBase):
    """Mixin for declarative models with semantic data support."""