import logging import uuid from datetime import datetime, timezone def _utcnow(): return datetime.now(timezone.utc) from sqlalchemy import Boolean, Column, DateTime, Float, ForeignKey, Integer, JSON, String, Table, Text, create_engine from sqlalchemy.orm import declarative_base, relationship, sessionmaker from backend.core.config import settings logger = logging.getLogger(__name__) DATABASE_URL = settings.DATABASE_URL if not DATABASE_URL: raise ValueError("DATABASE_URL is not set. Add it to backend/config_local.py") # Fix for common PostgreSQL connection string prefixes if DATABASE_URL.startswith("postgres://"): DATABASE_URL = DATABASE_URL.replace("postgres://", "postgresql://", 1) logger.info("Initializing DB engine: %s", DATABASE_URL.split('@')[-1]) engine = create_engine( DATABASE_URL, pool_pre_ping=True, pool_recycle=300 ) SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine) Base = declarative_base() # Many-to-Many junction table for assets and collections asset_collections = Table( "asset_collections", Base.metadata, Column("asset_id", String, ForeignKey("assets.id"), primary_key=True), Column("collection_id", String, ForeignKey("collections.id"), primary_key=True) ) class User(Base): __tablename__ = "users" id = Column(String, primary_key=True, default=lambda: str(uuid.uuid4())) username = Column(String, unique=True, nullable=False, index=True) hashed_password = Column(String, nullable=False) created_at = Column(DateTime, default=_utcnow) class SpecDocument(Base): __tablename__ = "spec_documents" id = Column(String, primary_key=True, default=lambda: str(uuid.uuid4())) user_id = Column(String, nullable=True, index=True) name = Column(String, nullable=False) raw_content = Column(Text, nullable=False) parsed_json = Column(JSON, nullable=False) base_url = Column(String, nullable=True) login_credentials = Column(JSON, nullable=True) # Per-spec stored credentials execution_plan = Column(JSON, nullable=True) # Persisted LLM execution plan scenario = Column(Text, nullable=True) # Saved test scenario text file_path = Column(String, nullable=True) # Reference to file in Supabase Storage uploaded_at = Column(DateTime, default=_utcnow) # Relationships assets = relationship("Asset", back_populates="spec") class SessionModel(Base): __tablename__ = "sessions" id = Column(String, primary_key=True, index=True) user_id = Column(String, nullable=True, index=True) spec_id = Column(String, ForeignKey("spec_documents.id"), nullable=True) spec_name = Column(String, nullable=True) base_url = Column(String) scenario = Column(Text) context_variables = Column(JSON, default={}) created_at = Column(DateTime, default=_utcnow) class ExecutionHistory(Base): __tablename__ = "execution_history" id = Column(Integer, primary_key=True, index=True) session_id = Column(String, index=True) step = Column(Integer) request_json = Column(JSON) response_json = Column(JSON) analysis_json = Column(JSON, nullable=True) step_definition_json = Column(JSON, nullable=True) # Historical step metadata timestamp = Column(DateTime, default=_utcnow) class Collection(Base): __tablename__ = "collections" id = Column(String, primary_key=True, default=lambda: str(uuid.uuid4())) name = Column(String, nullable=False) description = Column(Text, nullable=True) spec_id = Column(String, ForeignKey("spec_documents.id"), nullable=True) created_at = Column(DateTime, default=_utcnow) # Relationships assets = relationship("Asset", secondary=asset_collections, back_populates="collections") class Asset(Base): __tablename__ = "assets" id = Column(String, primary_key=True, default=lambda: str(uuid.uuid4())) name = Column(String, nullable=False) file_type = Column(String, nullable=False) file_path = Column(String, nullable=False) file_size = Column(Integer) category = Column(String, default="documents") # audio, video, documents, images description = Column(Text, nullable=True) tags = Column(JSON, default=[]) # e.g. ["aadhar", "identity"] metadata_json = Column(JSON, default={}) # thumbnails, dimensions, etc. spec_id = Column(String, ForeignKey("spec_documents.id"), nullable=True) uploaded_at = Column(DateTime, default=_utcnow) # Relationships spec = relationship("SpecDocument", back_populates="assets") collections = relationship("Collection", secondary=asset_collections, back_populates="assets") class ChatSession(Base): __tablename__ = "chat_sessions" id = Column(String, primary_key=True, default=lambda: str(uuid.uuid4())) user_id = Column(String, nullable=True, index=True) name = Column(String, nullable=False) # auto-named from first message, user can rename spec_id = Column(String, ForeignKey("spec_documents.id", ondelete="SET NULL"), nullable=True) session_id = Column(String, nullable=True) # linked API test session created_at = Column(DateTime, default=_utcnow) updated_at = Column(DateTime, default=_utcnow, onupdate=_utcnow) # Relationships messages = relationship("ChatMessage", back_populates="chat_session", cascade="all, delete-orphan", order_by="ChatMessage.id") class ChatMessage(Base): __tablename__ = "chat_messages" id = Column(Integer, primary_key=True, index=True) chat_session_id = Column(String, ForeignKey("chat_sessions.id", ondelete="CASCADE"), nullable=False) role = Column(String, nullable=False) # 'user' | 'assistant' content = Column(Text, nullable=False) # raw message text action_json = Column(JSON, nullable=True) # structured action returned by AI (execute_step, explain, etc.) timestamp = Column(DateTime, default=_utcnow) # Relationships chat_session = relationship("ChatSession", back_populates="messages") class LLMUsage(Base): """Tracks token consumption per LLM call for cost monitoring and budget enforcement.""" __tablename__ = "llm_usage" id = Column(Integer, primary_key=True, index=True) session_id = Column(String, nullable=True, index=True) user_id = Column(String, nullable=True) call_type = Column(String, nullable=False) # "plan" | "execute_step" | "analyze" | "chat" prompt_tokens = Column(Integer, default=0) completion_tokens = Column(Integer, default=0) total_tokens = Column(Integer, default=0) timestamp = Column(DateTime, default=_utcnow) class LLMProvider(Base): __tablename__ = "llm_providers" provider_id = Column(String, primary_key=True) # "mistral", "openai", etc. api_key_enc = Column(JSON, nullable=True) # {"__enc__": ""} is_active = Column(Boolean, default=False) class LLMTaskConfig(Base): __tablename__ = "llm_task_config" task_name = Column(String, primary_key=True) # "plan_generation" | "step_execution" | "response_analysis" | "chat" provider_id = Column(String, ForeignKey("llm_providers.provider_id"), nullable=True) model_name = Column(String, nullable=True) temperature = Column(Float, default=0.0) def get_db(): db = SessionLocal() try: yield db finally: db.close()