Spaces:
Sleeping
Sleeping
| """ | |
| LeadAI — Database module. | |
| Async SQLAlchemy with SQLite for leads, calls, transcripts, and campaigns. | |
| """ | |
| import datetime | |
| from sqlalchemy import ( | |
| Column, Integer, String, Text, DateTime, Float, | |
| ForeignKey, Enum, Boolean, create_engine | |
| ) | |
| from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession, async_sessionmaker | |
| from sqlalchemy.orm import declarative_base, relationship | |
| from app.config import DATABASE_URL | |
| # --- Engine & Session --- | |
| engine = create_async_engine(DATABASE_URL, echo=False) | |
| async_session = async_sessionmaker(engine, class_=AsyncSession, expire_on_commit=False) | |
| Base = declarative_base() | |
| # --- Models --- | |
| class Campaign(Base): | |
| """A calling campaign with a specific purpose and AI script.""" | |
| __tablename__ = "campaigns" | |
| id = Column(Integer, primary_key=True, autoincrement=True) | |
| name = Column(String(255), nullable=False) | |
| description = Column(Text, default="") | |
| system_prompt = Column(Text, default="") | |
| voice = Column(String(100), default="en-US-GuyNeural") | |
| status = Column(String(50), default="draft") # draft, active, paused, completed | |
| created_at = Column(DateTime, default=datetime.datetime.utcnow) | |
| updated_at = Column(DateTime, default=datetime.datetime.utcnow, onupdate=datetime.datetime.utcnow) | |
| # Relationships | |
| leads = relationship("Lead", back_populates="campaign", lazy="selectin") | |
| class Lead(Base): | |
| """A lead/contact to be called.""" | |
| __tablename__ = "leads" | |
| id = Column(Integer, primary_key=True, autoincrement=True) | |
| name = Column(String(255), nullable=False) | |
| phone = Column(String(50), nullable=False) | |
| company = Column(String(255), default="") | |
| purpose = Column(Text, default="") | |
| status = Column(String(50), default="pending") # pending, calling, completed, failed, no_answer | |
| notes = Column(Text, default="") | |
| campaign_id = Column(Integer, ForeignKey("campaigns.id"), nullable=True) | |
| created_at = Column(DateTime, default=datetime.datetime.utcnow) | |
| # Relationships | |
| campaign = relationship("Campaign", back_populates="leads") | |
| calls = relationship("Call", back_populates="lead", lazy="selectin") | |
| class Call(Base): | |
| """A phone call record.""" | |
| __tablename__ = "calls" | |
| id = Column(Integer, primary_key=True, autoincrement=True) | |
| lead_id = Column(Integer, ForeignKey("leads.id"), nullable=False) | |
| twilio_call_sid = Column(String(100), default="") | |
| status = Column(String(50), default="initiated") # initiated, ringing, in_progress, completed, failed, no_answer | |
| outcome = Column(String(100), default="") # interested, not_interested, callback, voicemail, error | |
| duration_seconds = Column(Float, default=0.0) | |
| started_at = Column(DateTime, default=datetime.datetime.utcnow) | |
| ended_at = Column(DateTime, nullable=True) | |
| error_message = Column(Text, default="") | |
| # Relationships | |
| lead = relationship("Lead", back_populates="calls") | |
| transcripts = relationship("Transcript", back_populates="call", lazy="selectin") | |
| class Transcript(Base): | |
| """Individual message in a call transcript.""" | |
| __tablename__ = "transcripts" | |
| id = Column(Integer, primary_key=True, autoincrement=True) | |
| call_id = Column(Integer, ForeignKey("calls.id"), nullable=False) | |
| speaker = Column(String(20), nullable=False) # "agent" or "human" | |
| text = Column(Text, nullable=False) | |
| timestamp = Column(DateTime, default=datetime.datetime.utcnow) | |
| # Relationships | |
| call = relationship("Call", back_populates="transcripts") | |
| # --- Database Lifecycle --- | |
| async def init_db(): | |
| """Create all tables.""" | |
| async with engine.begin() as conn: | |
| await conn.run_sync(Base.metadata.create_all) | |
| async def get_session() -> AsyncSession: | |
| """Get an async database session.""" | |
| async with async_session() as session: | |
| yield session | |