SQLAlchemy many-to-many relationships with join table

from sqlalchemy import Table, Column, ForeignKey, select
from sqlalchemy.dialects.postgresql import UUID
from sqlalchemy.orm import Mapped, mapped_column, relationship

# Join table definition:
trace_tags = Table(
    'trace_tags',
    Base.metadata,
    Column('trace_id', UUID(as_uuid=True), ForeignKey('traces.id'), primary_key=True),
    Column('tag_id', UUID(as_uuid=True), ForeignKey('tags.id'), primary_key=True),
)

class Trace(Base):
    tags: Mapped[list['Tag']] = relationship(
        'Tag', secondary='trace_tags', back_populates='traces'
    )

class Tag(Base):
    traces: Mapped[list['Trace']] = relationship(
        'Trace', secondary='trace_tags', back_populates='tags'
    )

# Get-or-create tag (idempotent):
async def get_or_create_tag(session: AsyncSession, name: str) -> Tag:
    normalized = normalize_tag(name)
    result = await session.execute(select(Tag).where(Tag.name == normalized))
    tag = result.scalar_one_or_none()
    if tag is None:
        tag = Tag(name=normalized)
        session.add(tag)
        await session.flush()  # Get ID without committing
    return tag

# Insert into join table directly (avoid lazy load issues):
from sqlalchemy import insert

async def add_trace_tags(session: AsyncSession, trace: Trace, tag_names: list[str]) -> None:
    for name in tag_names:
        if not validate_tag(normalize_tag(name)):
            continue
        tag = await get_or_create_tag(session, name)
        await session.execute(
            trace_tags.insert().values(trace_id=trace.id, tag_id=tag.id)
        )

# Query traces by tag:
stmt = select(Trace).where(Trace.tags.any(Tag.name == 'python'))