Open-source personal finance manager. Self-hosted, privacy-first.
import uuid
from datetime import datetime
from typing import TYPE_CHECKING, Optional
from sqlalchemy import JSON, DateTime, ForeignKey, Index, Integer, String, Text
from sqlalchemy.dialects.postgresql import UUID
from sqlalchemy.orm import Mapped, mapped_column, relationship
from sqlalchemy.sql import func
from app.core.database import Base
if TYPE_CHECKING:
from app.agents.models.agent import Agent
class Conversation(Base):
__tablename__ = "agent_conversations"
id: Mapped[uuid.UUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
agent_id: Mapped[uuid.UUID] = mapped_column(UUID(as_uuid=True), ForeignKey("agents.id", ondelete="CASCADE"), index=True)
user_id: Mapped[uuid.UUID] = mapped_column(UUID(as_uuid=True), ForeignKey("users.id", ondelete="CASCADE"), index=True)
workspace_id: Mapped[uuid.UUID] = mapped_column(
UUID(as_uuid=True), ForeignKey("workspaces.id", ondelete="CASCADE"), index=True
)
# Channel of origin: "web", "whatsapp", "api", etc. The runtime is
# channel-agnostic; this column lets us segment conversations later.
channel: Mapped[str] = mapped_column(String(40), default="web")
title: Mapped[Optional[str]] = mapped_column(String(200), nullable=True)
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(), onupdate=func.now())
agent: Mapped["Agent"] = relationship(back_populates="conversations")
messages: Mapped[list["Message"]] = relationship(
back_populates="conversation",
cascade="all, delete-orphan",
order_by="Message.ordinal",
)
class Message(Base):
__tablename__ = "agent_messages"
id: Mapped[uuid.UUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
conversation_id: Mapped[uuid.UUID] = mapped_column(UUID(as_uuid=True), ForeignKey("agent_conversations.id", ondelete="CASCADE"))
ordinal: Mapped[int] = mapped_column(Integer)
# role ∈ {"system","user","assistant","tool"}
role: Mapped[str] = mapped_column(String(20))
content: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
# When role="assistant", list of {id,name,arguments,thought_signature?}.
tool_calls: Mapped[Optional[list]] = mapped_column(JSON, nullable=True)
# When role="tool", payload is {tool_call_id, server, name, result, error?}.
tool_result: Mapped[Optional[dict]] = mapped_column(JSON, nullable=True)
# Citations attached to this message (RAG hits, transactions, etc.).
citations: Mapped[Optional[list]] = mapped_column(JSON, nullable=True)
input_tokens: Mapped[Optional[int]] = mapped_column(Integer, nullable=True)
output_tokens: Mapped[Optional[int]] = mapped_column(Integer, nullable=True)
created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())
conversation: Mapped["Conversation"] = relationship(back_populates="messages")
__table_args__ = (
Index("ix_agent_messages_conv_ord", "conversation_id", "ordinal"),
)