183 lines
9.0 KiB
Python
183 lines
9.0 KiB
Python
from datetime import datetime, timezone
|
|
import uuid
|
|
from sqlalchemy import (
|
|
Column, String, Integer, BigInteger, Boolean, DateTime,
|
|
ForeignKey, Text, Index
|
|
)
|
|
from sqlalchemy.orm import relationship
|
|
from app.core.database import Base
|
|
|
|
def utc_now():
|
|
return datetime.now(timezone.utc)
|
|
|
|
class User(Base):
|
|
__tablename__ = "users"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
email = Column(String(255), unique=True, index=True, nullable=False)
|
|
hashed_password = Column(String(255), nullable=False)
|
|
full_name = Column(String(255), nullable=True)
|
|
role = Column(String(50), default="ADMIN", nullable=False) # ADMIN, OPERATOR, VIEWER
|
|
is_active = Column(Boolean, default=True, nullable=False)
|
|
created_at = Column(DateTime(timezone=True), default=utc_now, nullable=False)
|
|
|
|
class Client(Base):
|
|
__tablename__ = "clients"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
client_code = Column(String(50), unique=True, index=True, nullable=False) # e.g., CLIENT-0001
|
|
name = Column(String(255), nullable=False)
|
|
alias = Column(String(255), nullable=True)
|
|
hostname = Column(String(255), nullable=True)
|
|
os_info = Column(String(255), nullable=True)
|
|
ip_address = Column(String(100), nullable=True)
|
|
agent_version = Column(String(50), default="1.0.0", nullable=False)
|
|
status = Column(String(50), default="OFFLINE", nullable=False) # ONLINE, OFFLINE, SYNCING, ERROR
|
|
storage_used_bytes = Column(BigInteger, default=0, nullable=False)
|
|
storage_quota_bytes = Column(BigInteger, default=100 * 1024 * 1024 * 1024, nullable=False) # 100 GB default
|
|
last_seen_at = Column(DateTime(timezone=True), nullable=True)
|
|
last_backup_at = Column(DateTime(timezone=True), nullable=True)
|
|
is_active = Column(Boolean, default=True, nullable=False)
|
|
created_at = Column(DateTime(timezone=True), default=utc_now, nullable=False)
|
|
|
|
credentials = relationship("ClientCredential", back_populates="client", cascade="all, delete-orphan")
|
|
jobs = relationship("BackupJob", back_populates="client", cascade="all, delete-orphan")
|
|
backup_files = relationship("BackupFile", back_populates="client", cascade="all, delete-orphan")
|
|
backup_sessions = relationship("BackupSession", back_populates="client", cascade="all, delete-orphan")
|
|
|
|
class ClientCredential(Base):
|
|
__tablename__ = "client_credentials"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
client_id = Column(Integer, ForeignKey("clients.id", ondelete="CASCADE"), nullable=False)
|
|
device_id = Column(String(100), unique=True, index=True, nullable=False) # Unique UUID
|
|
token_hash = Column(String(255), unique=True, index=True, nullable=False)
|
|
name = Column(String(255), default="Primary Windows Agent", nullable=False)
|
|
is_revoked = Column(Boolean, default=False, nullable=False)
|
|
created_at = Column(DateTime(timezone=True), default=utc_now, nullable=False)
|
|
last_used_at = Column(DateTime(timezone=True), nullable=True)
|
|
|
|
client = relationship("Client", back_populates="credentials")
|
|
|
|
class RegistrationCode(Base):
|
|
__tablename__ = "registration_codes"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
code = Column(String(50), unique=True, index=True, nullable=False) # e.g., OED-A1B2-C3D4
|
|
is_used = Column(Boolean, default=False, nullable=False)
|
|
client_name_hint = Column(String(255), nullable=True)
|
|
client_id = Column(Integer, ForeignKey("clients.id"), nullable=True)
|
|
expires_at = Column(DateTime(timezone=True), nullable=False)
|
|
created_at = Column(DateTime(timezone=True), default=utc_now, nullable=False)
|
|
|
|
class BackupJob(Base):
|
|
__tablename__ = "backup_jobs"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
job_code = Column(String(50), unique=True, index=True, nullable=False) # e.g., JOB-001
|
|
client_id = Column(Integer, ForeignKey("clients.id", ondelete="CASCADE"), nullable=False)
|
|
name = Column(String(255), nullable=False)
|
|
source_path = Column(String(1024), nullable=False) # e.g., C:\SQLBackups
|
|
file_patterns = Column(String(255), default="*.bak,*.mdf", nullable=False)
|
|
schedule_cron = Column(String(100), default="0 2 * * *", nullable=False) # default 02:00 AM daily
|
|
is_active = Column(Boolean, default=True, nullable=False)
|
|
|
|
# Retention policies
|
|
keep_daily = Column(Integer, default=7, nullable=False)
|
|
keep_weekly = Column(Integer, default=4, nullable=False)
|
|
keep_monthly = Column(Integer, default=12, nullable=False)
|
|
min_stable_time_seconds = Column(Integer, default=60, nullable=False)
|
|
|
|
status = Column(String(50), default="IDLE", nullable=False) # IDLE, RUNNING, ERROR, SUCCESS
|
|
last_run_at = Column(DateTime(timezone=True), nullable=True)
|
|
next_run_at = Column(DateTime(timezone=True), nullable=True)
|
|
created_at = Column(DateTime(timezone=True), default=utc_now, nullable=False)
|
|
|
|
client = relationship("Client", back_populates="jobs")
|
|
backup_files = relationship("BackupFile", back_populates="job", cascade="all, delete-orphan")
|
|
backup_sessions = relationship("BackupSession", back_populates="job", cascade="all, delete-orphan")
|
|
|
|
class BackupSession(Base):
|
|
__tablename__ = "backup_sessions"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
session_code = Column(String(100), unique=True, index=True, default=lambda: str(uuid.uuid4()), nullable=False)
|
|
client_id = Column(Integer, ForeignKey("clients.id", ondelete="CASCADE"), nullable=False)
|
|
job_id = Column(Integer, ForeignKey("backup_jobs.id", ondelete="CASCADE"), nullable=True)
|
|
|
|
filename = Column(String(512), nullable=False)
|
|
file_size = Column(BigInteger, nullable=False)
|
|
chunk_size = Column(Integer, default=4 * 1024 * 1024, nullable=False)
|
|
total_chunks = Column(Integer, nullable=False)
|
|
received_chunks_count = Column(Integer, default=0, nullable=False)
|
|
sha256_full = Column(String(64), nullable=False)
|
|
|
|
status = Column(String(50), default="PENDING", nullable=False) # PENDING, UPLOADING, ASSEMBLING, VERIFYING, SUCCESS, FAILED, CANCELLED
|
|
error_message = Column(Text, nullable=True)
|
|
started_at = Column(DateTime(timezone=True), default=utc_now, nullable=False)
|
|
completed_at = Column(DateTime(timezone=True), nullable=True)
|
|
|
|
client = relationship("Client", back_populates="backup_sessions")
|
|
job = relationship("BackupJob", back_populates="backup_sessions")
|
|
chunks = relationship("BackupChunk", back_populates="session", cascade="all, delete-orphan")
|
|
|
|
__table_args__ = (
|
|
Index("idx_session_client_status", "client_id", "status"),
|
|
)
|
|
|
|
class BackupChunk(Base):
|
|
__tablename__ = "backup_chunks"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
session_id = Column(Integer, ForeignKey("backup_sessions.id", ondelete="CASCADE"), nullable=False)
|
|
chunk_index = Column(Integer, nullable=False)
|
|
chunk_size = Column(Integer, nullable=False)
|
|
sha256 = Column(String(64), nullable=False)
|
|
is_received = Column(Boolean, default=False, nullable=False)
|
|
received_at = Column(DateTime(timezone=True), nullable=True)
|
|
|
|
session = relationship("BackupSession", back_populates="chunks")
|
|
|
|
__table_args__ = (
|
|
Index("idx_chunk_session_idx", "session_id", "chunk_index", unique=True),
|
|
)
|
|
|
|
class BackupFile(Base):
|
|
__tablename__ = "backup_files"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
client_id = Column(Integer, ForeignKey("clients.id", ondelete="CASCADE"), nullable=False)
|
|
job_id = Column(Integer, ForeignKey("backup_jobs.id", ondelete="CASCADE"), nullable=True)
|
|
session_id = Column(Integer, ForeignKey("backup_sessions.id", ondelete="SET NULL"), nullable=True)
|
|
|
|
filename = Column(String(512), nullable=False)
|
|
relative_path = Column(String(1024), nullable=False)
|
|
file_size = Column(BigInteger, nullable=False)
|
|
sha256 = Column(String(64), nullable=False)
|
|
retention_tag = Column(String(50), default="DAILY", nullable=False) # DAILY, WEEKLY, MONTHLY, MANUAL
|
|
is_active = Column(Boolean, default=True, nullable=False)
|
|
created_at = Column(DateTime(timezone=True), default=utc_now, nullable=False)
|
|
|
|
client = relationship("Client", back_populates="backup_files")
|
|
job = relationship("BackupJob", back_populates="backup_files")
|
|
|
|
class EventLog(Base):
|
|
__tablename__ = "event_logs"
|
|
|
|
id = Column(Integer, primary_key=True, index=True)
|
|
timestamp = Column(DateTime(timezone=True), default=utc_now, nullable=False, index=True)
|
|
event_type = Column(String(100), nullable=False, index=True) # LOGIN, CLIENT_REGISTERED, BACKUP_STARTED, BACKUP_COMPLETED, BACKUP_FAILED, etc.
|
|
severity = Column(String(50), default="INFO", nullable=False) # INFO, WARNING, ERROR, CRITICAL
|
|
client_id = Column(Integer, nullable=True, index=True)
|
|
job_id = Column(Integer, nullable=True, index=True)
|
|
user_email = Column(String(255), nullable=True)
|
|
ip_address = Column(String(100), nullable=True)
|
|
message = Column(Text, nullable=False)
|
|
details_json = Column(Text, nullable=True)
|
|
|
|
class SystemSetting(Base):
|
|
__tablename__ = "system_settings"
|
|
|
|
key = Column(String(100), primary_key=True, index=True)
|
|
value = Column(String(1024), nullable=False)
|