Spaces:
Sleeping
Sleeping
| """ | |
| DataFlow Server — Database Models (SQLAlchemy + SQLite) | |
| """ | |
| from datetime import datetime | |
| from sqlalchemy import ( | |
| Column, String, Float, Integer, Boolean, | |
| DateTime, Text, ForeignKey, Enum as SAEnum | |
| ) | |
| from sqlalchemy.orm import relationship, declarative_base | |
| Base = declarative_base() | |
| class User(Base): | |
| __tablename__ = "users" | |
| id = Column(String, primary_key=True) | |
| full_name = Column(String, nullable=False) | |
| email = Column(String, unique=True, nullable=False) | |
| hashed_password = Column(String, nullable=False) | |
| position = Column(String, nullable=True) | |
| role = Column(String, default="developer") # manager, developer, observer | |
| two_factor_enabled = Column(Boolean, default=False) | |
| biometric_enabled = Column(Boolean, default=False) | |
| language = Column(String, default="ru") | |
| timezone = Column(String, default="Asia/Almaty") | |
| currency = Column(String, default="KZT") | |
| avatar_url = Column(String, nullable=True) | |
| created_at = Column(DateTime, default=datetime.utcnow) | |
| tasks = relationship("Task", back_populates="assignee", foreign_keys="Task.assignee_id") | |
| class Project(Base): | |
| __tablename__ = "projects" | |
| id = Column(String, primary_key=True) | |
| name = Column(String, nullable=False) | |
| description = Column(Text, default="") | |
| start_date = Column(DateTime, nullable=False) | |
| end_date = Column(DateTime, nullable=False) | |
| budget = Column(Float, default=0.0) | |
| spent = Column(Float, default=0.0) | |
| status = Column(String, default="active") # active, paused, completed, archived | |
| progress = Column(Float, default=0.0) | |
| risk_level = Column(String, default="green") # green, yellow, red | |
| owner_id = Column(String, ForeignKey("users.id"), nullable=True) | |
| created_at = Column(DateTime, default=datetime.utcnow) | |
| tasks = relationship("Task", back_populates="project", cascade="all, delete-orphan") | |
| expenses = relationship("Expense", back_populates="project", cascade="all, delete-orphan") | |
| documents = relationship("Document", back_populates="project", cascade="all, delete-orphan") | |
| members = relationship("ProjectMember", back_populates="project", cascade="all, delete-orphan") | |
| forecast = relationship("AIForecast", back_populates="project", uselist=False, cascade="all, delete-orphan") | |
| class ProjectMember(Base): | |
| __tablename__ = "project_members" | |
| id = Column(String, primary_key=True) | |
| project_id = Column(String, ForeignKey("projects.id"), nullable=False) | |
| user_id = Column(String, ForeignKey("users.id"), nullable=False) | |
| role = Column(String, default="developer") | |
| project = relationship("Project", back_populates="members") | |
| class Task(Base): | |
| __tablename__ = "tasks" | |
| id = Column(String, primary_key=True) | |
| title = Column(String, nullable=False) | |
| description = Column(Text, default="") | |
| project_id = Column(String, ForeignKey("projects.id"), nullable=False) | |
| status = Column(String, default="todo") # todo, inProgress, review, done | |
| priority = Column(String, default="medium") # low, medium, high, critical | |
| assignee_id = Column(String, ForeignKey("users.id"), nullable=True) | |
| assignee_name = Column(String, nullable=True) | |
| deadline = Column(DateTime, nullable=True) | |
| created_at = Column(DateTime, default=datetime.utcnow) | |
| tags = Column(Text, default="") # comma-separated | |
| project = relationship("Project", back_populates="tasks") | |
| assignee = relationship("User", back_populates="tasks", foreign_keys=[assignee_id]) | |
| subtasks = relationship("SubTask", back_populates="task", cascade="all, delete-orphan") | |
| comments = relationship("TaskComment", back_populates="task", cascade="all, delete-orphan") | |
| class SubTask(Base): | |
| __tablename__ = "subtasks" | |
| id = Column(String, primary_key=True) | |
| task_id = Column(String, ForeignKey("tasks.id"), nullable=False) | |
| title = Column(String, nullable=False) | |
| completed = Column(Boolean, default=False) | |
| task = relationship("Task", back_populates="subtasks") | |
| class TaskComment(Base): | |
| __tablename__ = "task_comments" | |
| id = Column(String, primary_key=True) | |
| task_id = Column(String, ForeignKey("tasks.id"), nullable=False) | |
| author_name = Column(String, nullable=False) | |
| text = Column(Text, nullable=False) | |
| created_at = Column(DateTime, default=datetime.utcnow) | |
| task = relationship("Task", back_populates="comments") | |
| class Expense(Base): | |
| __tablename__ = "expenses" | |
| id = Column(String, primary_key=True) | |
| project_id = Column(String, ForeignKey("projects.id"), nullable=False) | |
| title = Column(String, nullable=False) | |
| amount = Column(Float, nullable=False) | |
| category = Column(String, nullable=False) # personnel, infrastructure, software, ... | |
| date = Column(DateTime, default=datetime.utcnow) | |
| status = Column(String, default="pending") # pending, approved, rejected | |
| receipt_url = Column(String, nullable=True) | |
| description = Column(Text, nullable=True) | |
| approved_by = Column(String, nullable=True) | |
| created_at = Column(DateTime, default=datetime.utcnow) | |
| project = relationship("Project", back_populates="expenses") | |
| class Document(Base): | |
| __tablename__ = "documents" | |
| id = Column(String, primary_key=True) | |
| name = Column(String, nullable=False) | |
| project_id = Column(String, ForeignKey("projects.id"), nullable=False) | |
| type = Column(String, nullable=False) # pdf, docx, xlsx, image | |
| size_bytes = Column(Integer, default=0) | |
| file_path = Column(String, nullable=True) | |
| uploaded_by = Column(String, nullable=True) | |
| is_favorite = Column(Boolean, default=False) | |
| # AI OCR fields | |
| extracted_text = Column(Text, nullable=True) | |
| recognition_confidence = Column(Float, nullable=True) | |
| detected_type = Column(String, nullable=True) # invoice, receipt, contract, spec | |
| extracted_amount = Column(Float, nullable=True) | |
| extracted_date = Column(DateTime, nullable=True) | |
| extracted_supplier = Column(String, nullable=True) | |
| uploaded_at = Column(DateTime, default=datetime.utcnow) | |
| project = relationship("Project", back_populates="documents") | |
| class AIForecast(Base): | |
| __tablename__ = "ai_forecasts" | |
| id = Column(String, primary_key=True) | |
| project_id = Column(String, ForeignKey("projects.id"), unique=True, nullable=False) | |
| current_spent = Column(Float, default=0.0) | |
| planned_budget = Column(Float, default=0.0) | |
| risk_level = Column(String, default="green") | |
| overrun_probability = Column(Float, default=0.0) | |
| optimistic_forecast = Column(Float, default=0.0) | |
| realistic_forecast = Column(Float, default=0.0) | |
| pessimistic_forecast = Column(Float, default=0.0) | |
| recommendations = Column(Text, default="") # JSON list stored as text | |
| generated_at = Column(DateTime, default=datetime.utcnow) | |
| project = relationship("Project", back_populates="forecast") | |
| class Notification(Base): | |
| __tablename__ = "notifications" | |
| id = Column(String, primary_key=True) | |
| user_id = Column(String, ForeignKey("users.id"), nullable=True) | |
| title = Column(String, nullable=False) | |
| body = Column(Text, nullable=False) | |
| type = Column(String, nullable=False) # task, expense, project, ai, document | |
| is_read = Column(Boolean, default=False) | |
| action_route = Column(String, nullable=True) | |
| created_at = Column(DateTime, default=datetime.utcnow) | |