src/infrastructure/database/models/base.py

"""
infrastructure/database/models/base.py
───────────────────────────────────────
Shared SQLAlchemy declarative base — Supabase / PostgreSQL ready.

All ORM models inherit from ``Base``, which automatically provides:
  • ``id``         — native UUID primary key (PostgreSQL UUID type, auto-generated)
  • ``created_at`` — UTC timestamp set by the DB on INSERT
  • ``updated_at`` — UTC timestamp updated by the DB on every UPDATE

DRY: defined once here, inherited by every model.

Supabase note:
  PostgreSQL's native UUID type is used instead of VARCHAR(36) for better
  index performance and storage efficiency. The ``server_default`` for UUIDs
  uses gen_random_uuid() which is natively available on PostgreSQL ≥ 13
  (Supabase uses PostgreSQL 15).
"""
from __future__ import annotations

from datetime import datetime

from sqlalchemy import DateTime, func, text
from sqlalchemy.dialects.postgresql import UUID
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column


class Base(DeclarativeBase):
    """
    Declarative base for all SQLAlchemy ORM models.

    Shared columns (auto-injected into every subclass):
        id:         UUID primary key — generated by PostgreSQL gen_random_uuid().
        created_at: TIMESTAMP WITH TIME ZONE — set by the DB on INSERT.
        updated_at: TIMESTAMP WITH TIME ZONE — updated by the DB on every UPDATE.
    """

    id: Mapped[str] = mapped_column(
        UUID(as_uuid=False),          # Store as str; no Python uuid.UUID coercion needed
        primary_key=True,
        server_default=text("gen_random_uuid()"),
        comment="UUID primary key — generated by PostgreSQL",
    )

    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True),
        server_default=func.now(),
        nullable=False,
        comment="UTC timestamp of record creation (set by DB)",
    )

    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True),
        server_default=func.now(),
        onupdate=func.now(),
        nullable=False,
        comment="UTC timestamp of last update (maintained by DB)",
    )