ai-api-testing / backend /database.py
Amitkasar's picture
deploy: update
ad279fb
Raw History Blame Contribute Delete
7.39 kB
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__": "<fernet-token>"}
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()