src/db.py

import os
from datetime import datetime
from sqlalchemy import (
    create_engine, Column, Integer, String, Text,
    DateTime, ForeignKey, Index
)
from sqlalchemy.orm import declarative_base, sessionmaker, relationship

#Ngecek persistent (belum aktifasi)
DB_DIR = "/data" if os.path.exists("/data") else "."
os.makedirs(DB_DIR, exist_ok=True)
DB_PATH = os.path.join(DB_DIR, "rag_chat.db")

engine = create_engine(
    f"sqlite:///{DB_PATH}",
    connect_args={"check_same_thread": False},
    pool_pre_ping=True,
)
SessionLocal = sessionmaker(bind=engine, autoflush=False, autocommit=False, expire_on_commit=False)
Base = declarative_base()

class User(Base):
    __tablename__ = "users"
    id    = Column(Integer, primary_key=True)
    email = Column(String, unique=True, index=True, nullable=False)
    name  = Column(String, nullable=True)
    role  = Column(String, nullable=True)

class Conversation(Base):
    __tablename__ = "conversations"
    id        = Column(Integer, primary_key=True)
    user_id   = Column(Integer, ForeignKey("users.id"), index=True, nullable=False)
    title     = Column(String, default="Session")
    created_at = Column(DateTime, default=datetime.utcnow)
    user      = relationship("User")

class Message(Base):
    __tablename__ = "messages"
    id              = Column(Integer, primary_key=True)
    conversation_id = Column(Integer, ForeignKey("conversations.id"), index=True, nullable=False)
    role            = Column(String) 
    content         = Column(Text)
    created_at      = Column(DateTime, default=datetime.utcnow)

Index("ix_messages_conv_time", Message.conversation_id, Message.created_at)

def init_db():
    Base.metadata.create_all(engine)
    with engine.connect() as conn:
        conn.exec_driver_sql("PRAGMA journal_mode=WAL;") #optional