models.py

"""fast-memory :: SQLAlchemy models for the memory layer (Layer 1).

Domain-neutral. Mirror of schema/001_init.sql. Adapt fields to your domain, but
keep the shape: profile + growing facts + dialogue + cached generations + usage.
"""
from __future__ import annotations

from datetime import datetime

from sqlalchemy import (
    BigInteger, DateTime, Float, ForeignKey, Integer, Numeric, String, Text,
    UniqueConstraint, func,
)
from sqlalchemy.dialects.postgresql import JSONB
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship


class Base(DeclarativeBase):
    pass


class UserProfile(Base):
    __tablename__ = "user_profile"

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True)
    external_id: Mapped[str] = mapped_column(String, unique=True)      # channel user id
    display_name: Mapped[str | None] = mapped_column(String)
    locale: Mapped[str] = mapped_column(String, default="en")
    attributes: Mapped[dict] = mapped_column(JSONB, default=dict)      # flexible profile bag
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())

    facts: Mapped[list["UserFact"]] = relationship(back_populates="user")


class UserFact(Base):
    """One remembered fact about the user. The growing record."""
    __tablename__ = "user_fact"

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True)
    user_id: Mapped[int] = mapped_column(ForeignKey("user_profile.id", ondelete="CASCADE"))
    key: Mapped[str] = mapped_column(String)             # normalized concept key/code
    value_num: Mapped[float | None] = mapped_column(Float)
    value_text: Mapped[str | None] = mapped_column(Text)
    unit: Mapped[str | None] = mapped_column(String)
    status: Mapped[str | None] = mapped_column(String)   # computed by CODE, cached here
    source: Mapped[str | None] = mapped_column(String)
    observed_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())

    user: Mapped[UserProfile] = relationship(back_populates="facts")


class ConversationMessage(Base):
    __tablename__ = "conversation_message"

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True)
    user_id: Mapped[int] = mapped_column(ForeignKey("user_profile.id", ondelete="CASCADE"))
    role: Mapped[str] = mapped_column(String)            # user | assistant | system
    content: Mapped[str] = mapped_column(Text)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())


class GeneratedOutput(Base):
    """Cache of an expensive LLM generation. input_hash gates regeneration."""
    __tablename__ = "generated_output"
    __table_args__ = (UniqueConstraint("user_id", "kind"),)

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True)
    user_id: Mapped[int] = mapped_column(ForeignKey("user_profile.id", ondelete="CASCADE"))
    kind: Mapped[str] = mapped_column(String)            # portrait | recommendation | ...
    payload: Mapped[dict] = mapped_column(JSONB)
    model: Mapped[str | None] = mapped_column(String)
    input_hash: Mapped[str] = mapped_column(String)      # stable_hash(state) at gen time
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())


class LlmUsage(Base):
    __tablename__ = "llm_usage"

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True)
    user_id: Mapped[int | None] = mapped_column(ForeignKey("user_profile.id", ondelete="SET NULL"))
    surface: Mapped[str | None] = mapped_column(String)
    model: Mapped[str | None] = mapped_column(String)
    input_tokens: Mapped[int | None] = mapped_column(Integer)
    output_tokens: Mapped[int | None] = mapped_column(Integer)
    cache_read_tokens: Mapped[int | None] = mapped_column(Integer)
    cache_write_tokens: Mapped[int | None] = mapped_column(Integer)
    latency_ms: Mapped[int | None] = mapped_column(Integer)
    estimated_cost_usd: Mapped[float | None] = mapped_column(Numeric(10, 6))
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())