Porsche Chen
360533393f
Major Features: - ✅ Multi-tenant architecture (tenant isolation) - ✅ Employee CRUD with lifecycle management (onboarding/offboarding) - ✅ Department tree structure with email domain management - ✅ Company info management (single-record editing) - ✅ System functions CRUD (permission management) - ✅ Email account management (multi-account per employee) - ✅ Keycloak SSO integration (auth.lab.taipei) - ✅ Redis session storage (10.1.0.254:6379) - Solves Cookie 4KB limitation - Cross-system session sharing - Sliding expiration (8 hours) - Automatic token refresh Technical Stack: Backend: - FastAPI + SQLAlchemy - PostgreSQL 16 (10.1.0.20:5433) - Keycloak Admin API integration - Docker Mailserver integration (SSH) - Alembic migrations Frontend: - Next.js 14 (App Router) - NextAuth 4 with Keycloak Provider - Redis session storage (ioredis) - Tailwind CSS Infrastructure: - Redis 7 (10.1.0.254:6379) - Session + Cache - Keycloak 26.1.0 (auth.lab.taipei) - Docker Mailserver (10.1.0.254) Architecture Highlights: - Session管理由 Keycloak + Redis 統一控制 - 支援多系統 (HR/WebMail/Calendar/Drive/Office) 共享 session - Token 自動刷新,異質服務整合 - 未來可無縫遷移到雲端 Co-Authored-By: Claude Sonnet 4.5 <noreply@anthropic.com>
363 lines
15 KiB
Python
363 lines
15 KiB
Python
"""
|
||
Installation System Models
|
||
初始化系統資料模型
|
||
"""
|
||
from datetime import datetime
|
||
from sqlalchemy import (
|
||
Column, Integer, String, Boolean, Text, TIMESTAMP, ForeignKey, ARRAY
|
||
)
|
||
from sqlalchemy.dialects.postgresql import JSONB
|
||
from sqlalchemy.orm import relationship
|
||
from app.db.base import Base
|
||
|
||
|
||
class InstallationSession(Base):
|
||
"""安裝會話"""
|
||
__tablename__ = "installation_sessions"
|
||
|
||
id = Column(Integer, primary_key=True, index=True)
|
||
tenant_id = Column(Integer, nullable=True) # 不設外鍵，初始化時 tenants 表可能不存在
|
||
session_name = Column(String(200))
|
||
environment = Column(String(20)) # development/testing/production
|
||
|
||
# 狀態追蹤
|
||
started_at = Column(TIMESTAMP, default=datetime.now)
|
||
completed_at = Column(TIMESTAMP)
|
||
status = Column(String(20), default='in_progress') # in_progress/completed/failed/paused
|
||
|
||
# 進度統計
|
||
total_checklist_items = Column(Integer)
|
||
passed_checklist_items = Column(Integer, default=0)
|
||
failed_checklist_items = Column(Integer, default=0)
|
||
total_steps = Column(Integer)
|
||
completed_steps = Column(Integer, default=0)
|
||
failed_steps = Column(Integer, default=0)
|
||
|
||
executed_by = Column(String(100))
|
||
|
||
# 存取控制
|
||
is_locked = Column(Boolean, default=False)
|
||
locked_at = Column(TIMESTAMP)
|
||
locked_by = Column(String(100))
|
||
lock_reason = Column(String(200))
|
||
|
||
is_unlocked = Column(Boolean, default=False)
|
||
unlocked_at = Column(TIMESTAMP)
|
||
unlocked_by = Column(String(100))
|
||
unlock_reason = Column(String(200))
|
||
unlock_expires_at = Column(TIMESTAMP)
|
||
|
||
last_viewed_at = Column(TIMESTAMP)
|
||
last_viewed_by = Column(String(100))
|
||
view_count = Column(Integer, default=0)
|
||
|
||
created_at = Column(TIMESTAMP, default=datetime.now)
|
||
|
||
# Relationships
|
||
# 不定義 tenant relationship，因為沒有 FK constraint
|
||
tenant_info = relationship("InstallationTenantInfo", back_populates="session", uselist=False)
|
||
department_setups = relationship("InstallationDepartmentSetup", back_populates="session")
|
||
temporary_passwords = relationship("TemporaryPassword", back_populates="session")
|
||
access_logs = relationship("InstallationAccessLog", back_populates="session")
|
||
checklist_results = relationship("InstallationChecklistResult", back_populates="session")
|
||
installation_logs = relationship("InstallationLog", back_populates="session")
|
||
|
||
|
||
class InstallationChecklistItem(Base):
|
||
"""檢查項目定義（系統級）"""
|
||
__tablename__ = "installation_checklist_items"
|
||
|
||
id = Column(Integer, primary_key=True, index=True)
|
||
category = Column(String(50), nullable=False) # hardware/network/software/container/security
|
||
item_code = Column(String(100), unique=True, nullable=False)
|
||
item_name = Column(String(200), nullable=False)
|
||
check_type = Column(String(50), nullable=False) # command/api/config/manual
|
||
check_command = Column(Text) # 自動檢查命令
|
||
expected_value = Column(Text)
|
||
min_requirement = Column(Text)
|
||
recommended_value = Column(Text)
|
||
is_required = Column(Boolean, default=True)
|
||
sequence_order = Column(Integer, nullable=False)
|
||
description = Column(Text)
|
||
created_at = Column(TIMESTAMP, default=datetime.now)
|
||
|
||
# Relationships
|
||
results = relationship("InstallationChecklistResult", back_populates="checklist_item")
|
||
|
||
|
||
class InstallationChecklistResult(Base):
|
||
"""檢查結果（租戶級）"""
|
||
__tablename__ = "installation_checklist_results"
|
||
|
||
id = Column(Integer, primary_key=True, index=True)
|
||
tenant_id = Column(Integer, nullable=True) # 不設外鍵，初始化時 tenants 表可能不存在
|
||
session_id = Column(Integer, ForeignKey("installation_sessions.id", ondelete="CASCADE"))
|
||
checklist_item_id = Column(Integer, ForeignKey("installation_checklist_items.id", ondelete="CASCADE"), nullable=False)
|
||
status = Column(String(20), nullable=False) # pass/fail/warning/pending/skip
|
||
actual_value = Column(Text)
|
||
checked_at = Column(TIMESTAMP)
|
||
checked_by = Column(String(100))
|
||
auto_checked = Column(Boolean, default=False)
|
||
remarks = Column(Text)
|
||
created_at = Column(TIMESTAMP, default=datetime.now)
|
||
updated_at = Column(TIMESTAMP, default=datetime.now, onupdate=datetime.now)
|
||
|
||
# Relationships
|
||
# 不定義 tenant relationship，因為沒有 FK constraint
|
||
session = relationship("InstallationSession", back_populates="checklist_results")
|
||
checklist_item = relationship("InstallationChecklistItem", back_populates="results")
|
||
|
||
|
||
class InstallationStep(Base):
|
||
"""安裝步驟定義（系統級）"""
|
||
__tablename__ = "installation_steps"
|
||
|
||
id = Column(Integer, primary_key=True, index=True)
|
||
step_code = Column(String(50), unique=True, nullable=False)
|
||
step_name = Column(String(200), nullable=False)
|
||
phase = Column(String(20), nullable=False) # phase1/phase2/...
|
||
sequence_order = Column(Integer, nullable=False)
|
||
description = Column(Text)
|
||
execution_type = Column(String(50)) # auto/manual/script
|
||
execution_script = Column(Text)
|
||
depends_on_steps = Column(ARRAY(String)) # 依賴的步驟代碼
|
||
is_required = Column(Boolean, default=True)
|
||
created_at = Column(TIMESTAMP, default=datetime.now)
|
||
|
||
# Relationships
|
||
logs = relationship("InstallationLog", back_populates="step")
|
||
|
||
|
||
class InstallationLog(Base):
|
||
"""安裝執行記錄（租戶級）"""
|
||
__tablename__ = "installation_logs"
|
||
|
||
id = Column(Integer, primary_key=True, index=True)
|
||
tenant_id = Column(Integer, nullable=True) # 不設外鍵，初始化時 tenants 表可能不存在
|
||
step_id = Column(Integer, ForeignKey("installation_steps.id", ondelete="CASCADE"), nullable=False)
|
||
session_id = Column(Integer, ForeignKey("installation_sessions.id", ondelete="CASCADE"))
|
||
status = Column(String(20), nullable=False) # pending/running/success/failed/skipped
|
||
started_at = Column(TIMESTAMP)
|
||
completed_at = Column(TIMESTAMP)
|
||
executed_by = Column(String(100))
|
||
execution_method = Column(String(50)) # manual/auto/api/script
|
||
result_data = Column(JSONB)
|
||
error_message = Column(Text)
|
||
retry_count = Column(Integer, default=0)
|
||
remarks = Column(Text)
|
||
created_at = Column(TIMESTAMP, default=datetime.now)
|
||
updated_at = Column(TIMESTAMP, default=datetime.now, onupdate=datetime.now)
|
||
|
||
# Relationships
|
||
# 不定義 tenant relationship，因為沒有 FK constraint
|
||
step = relationship("InstallationStep", back_populates="logs")
|
||
session = relationship("InstallationSession", back_populates="installation_logs")
|
||
|
||
|
||
class InstallationTenantInfo(Base):
|
||
"""租戶初始化資訊"""
|
||
__tablename__ = "installation_tenant_info"
|
||
|
||
id = Column(Integer, primary_key=True, index=True)
|
||
tenant_id = Column(Integer, nullable=True, unique=True) # 不設外鍵，初始化時 tenants 表可能不存在
|
||
session_id = Column(Integer, ForeignKey("installation_sessions.id", ondelete="SET NULL"))
|
||
|
||
# 公司基本資訊
|
||
company_name = Column(String(200))
|
||
company_name_en = Column(String(200))
|
||
tenant_code = Column(String(50)) # 租戶代碼 = Keycloak Realm
|
||
tenant_prefix = Column(String(10)) # 員工編號前綴
|
||
tax_id = Column(String(50))
|
||
industry = Column(String(100))
|
||
company_size = Column(String(20)) # small/medium/large
|
||
|
||
# 聯絡資訊
|
||
tel = Column(String(20)) # 公司電話（對應 tenants.tel）
|
||
phone = Column(String(50))
|
||
fax = Column(String(50))
|
||
email = Column(String(200))
|
||
website = Column(String(200))
|
||
add = Column(Text) # 公司地址（對應 tenants.add）
|
||
address = Column(Text)
|
||
address_en = Column(Text)
|
||
|
||
# 郵件網域設定
|
||
domain_set = Column(Integer, default=2) # 1=組織網域, 2=部門網域
|
||
domain = Column(String(100)) # 組織網域（domain_set=1 時使用）
|
||
|
||
# 負責人資訊
|
||
representative_name = Column(String(100))
|
||
representative_title = Column(String(100))
|
||
representative_email = Column(String(200))
|
||
representative_phone = Column(String(50))
|
||
|
||
# 系統管理員資訊
|
||
admin_employee_id = Column(String(50))
|
||
admin_username = Column(String(100))
|
||
admin_legal_name = Column(String(100))
|
||
admin_english_name = Column(String(100))
|
||
admin_email = Column(String(200))
|
||
admin_phone = Column(String(50))
|
||
|
||
# 初始設定
|
||
default_language = Column(String(10), default='zh-TW')
|
||
timezone = Column(String(50), default='Asia/Taipei')
|
||
date_format = Column(String(20), default='YYYY-MM-DD')
|
||
currency = Column(String(10), default='TWD')
|
||
|
||
# 狀態追蹤
|
||
is_completed = Column(Boolean, default=False)
|
||
completed_at = Column(TIMESTAMP)
|
||
completed_by = Column(String(100))
|
||
|
||
created_at = Column(TIMESTAMP, default=datetime.now)
|
||
updated_at = Column(TIMESTAMP, default=datetime.now, onupdate=datetime.now)
|
||
|
||
# Relationships
|
||
# 不定義 tenant relationship，因為沒有 FK constraint
|
||
session = relationship("InstallationSession", back_populates="tenant_info")
|
||
|
||
|
||
class InstallationDepartmentSetup(Base):
|
||
"""部門架構設定"""
|
||
__tablename__ = "installation_department_setup"
|
||
|
||
id = Column(Integer, primary_key=True, index=True)
|
||
tenant_id = Column(Integer, nullable=True) # 不設外鍵，初始化時 tenants 表可能不存在
|
||
session_id = Column(Integer, ForeignKey("installation_sessions.id", ondelete="CASCADE"))
|
||
department_code = Column(String(50), nullable=False)
|
||
department_name = Column(String(200), nullable=False)
|
||
department_name_en = Column(String(200))
|
||
email_domain = Column(String(100))
|
||
parent_code = Column(String(50))
|
||
depth = Column(Integer, default=0)
|
||
manager_name = Column(String(100))
|
||
is_created = Column(Boolean, default=False)
|
||
created_at = Column(TIMESTAMP, default=datetime.now)
|
||
|
||
# Relationships
|
||
# 不定義 tenant relationship，因為沒有 FK constraint
|
||
session = relationship("InstallationSession", back_populates="department_setups")
|
||
|
||
|
||
class TemporaryPassword(Base):
|
||
"""臨時密碼"""
|
||
__tablename__ = "temporary_passwords"
|
||
|
||
id = Column(Integer, primary_key=True, index=True)
|
||
tenant_id = Column(Integer, nullable=True) # 不設外鍵，初始化時 tenants 表可能不存在
|
||
employee_id = Column(Integer, nullable=True) # 不設外鍵，初始化時 employees 表可能不存在
|
||
username = Column(String(100), nullable=False)
|
||
session_id = Column(Integer, ForeignKey("installation_sessions.id", ondelete="SET NULL"))
|
||
|
||
# 密碼資訊
|
||
password_hash = Column(String(255), nullable=False)
|
||
plain_password = Column(String(100)) # 明文密碼（僅初始化階段）
|
||
password_method = Column(String(20)) # auto/manual
|
||
is_temporary = Column(Boolean, default=True)
|
||
must_change_on_login = Column(Boolean, default=True)
|
||
|
||
# 有效期限
|
||
created_at = Column(TIMESTAMP, default=datetime.now)
|
||
expires_at = Column(TIMESTAMP)
|
||
|
||
# 使用狀態
|
||
is_used = Column(Boolean, default=False)
|
||
used_at = Column(TIMESTAMP)
|
||
first_login_at = Column(TIMESTAMP)
|
||
password_changed_at = Column(TIMESTAMP)
|
||
|
||
# 查看控制
|
||
is_viewable = Column(Boolean, default=True)
|
||
viewable_until = Column(TIMESTAMP)
|
||
view_count = Column(Integer, default=0)
|
||
last_viewed_at = Column(TIMESTAMP)
|
||
first_viewed_at = Column(TIMESTAMP)
|
||
|
||
# 明文密碼清除記錄
|
||
plain_password_cleared_at = Column(TIMESTAMP)
|
||
cleared_reason = Column(String(100))
|
||
|
||
# Relationships
|
||
# 不定義 tenant 和 employee relationship，因為沒有 FK constraint
|
||
session = relationship("InstallationSession", back_populates="temporary_passwords")
|
||
|
||
|
||
class InstallationAccessLog(Base):
|
||
"""存取審計日誌"""
|
||
__tablename__ = "installation_access_logs"
|
||
|
||
id = Column(Integer, primary_key=True, index=True)
|
||
session_id = Column(Integer, ForeignKey("installation_sessions.id", ondelete="CASCADE"), nullable=False)
|
||
action = Column(String(50), nullable=False) # lock/unlock/view/download_pdf
|
||
action_by = Column(String(100))
|
||
action_method = Column(String(50)) # database/api/system
|
||
ip_address = Column(String(50))
|
||
user_agent = Column(Text)
|
||
access_granted = Column(Boolean)
|
||
deny_reason = Column(String(200))
|
||
sensitive_data_accessed = Column(ARRAY(String))
|
||
created_at = Column(TIMESTAMP, default=datetime.now)
|
||
|
||
# Relationships
|
||
session = relationship("InstallationSession", back_populates="access_logs")
|
||
|
||
|
||
class InstallationEnvironmentConfig(Base):
|
||
"""環境配置記錄"""
|
||
__tablename__ = "installation_environment_config"
|
||
|
||
id = Column(Integer, primary_key=True, index=True)
|
||
session_id = Column(Integer, ForeignKey("installation_sessions.id", ondelete="SET NULL"))
|
||
config_key = Column(String(100), unique=True, nullable=False, index=True)
|
||
config_value = Column(Text)
|
||
config_category = Column(String(50), nullable=False, index=True) # redis/database/keycloak/mailserver/nextcloud/traefik
|
||
is_sensitive = Column(Boolean, default=False) # 是否為敏感資訊（密碼等）
|
||
is_configured = Column(Boolean, default=False)
|
||
configured_at = Column(TIMESTAMP)
|
||
configured_by = Column(String(100))
|
||
description = Column(Text)
|
||
created_at = Column(TIMESTAMP, default=datetime.now)
|
||
updated_at = Column(TIMESTAMP, default=datetime.now, onupdate=datetime.now)
|
||
|
||
# Relationships
|
||
session = relationship("InstallationSession")
|
||
|
||
|
||
class InstallationSystemStatus(Base):
|
||
"""系統狀態記錄（三階段：Initialization/Operational/Transition）"""
|
||
__tablename__ = "installation_system_status"
|
||
|
||
id = Column(Integer, primary_key=True, index=True)
|
||
current_phase = Column(String(20), nullable=False, index=True) # initialization/operational/transition
|
||
previous_phase = Column(String(20))
|
||
phase_changed_at = Column(TIMESTAMP)
|
||
phase_changed_by = Column(String(100))
|
||
phase_change_reason = Column(Text)
|
||
|
||
# Initialization 階段資訊
|
||
initialized_at = Column(TIMESTAMP)
|
||
initialized_by = Column(String(100))
|
||
initialization_completed = Column(Boolean, default=False)
|
||
|
||
# Operational 階段資訊
|
||
last_health_check_at = Column(TIMESTAMP)
|
||
health_check_status = Column(String(20)) # healthy/degraded/unhealthy
|
||
operational_since = Column(TIMESTAMP)
|
||
|
||
# Transition 階段資訊
|
||
transition_started_at = Column(TIMESTAMP)
|
||
transition_approved_by = Column(String(100))
|
||
env_db_consistent = Column(Boolean)
|
||
consistency_checked_at = Column(TIMESTAMP)
|
||
inconsistencies = Column(Text) # JSON 格式
|
||
|
||
# 系統鎖定
|
||
is_locked = Column(Boolean, default=False)
|
||
locked_at = Column(TIMESTAMP)
|
||
locked_by = Column(String(100))
|
||
lock_reason = Column(String(200))
|
||
|
||
created_at = Column(TIMESTAMP, default=datetime.now)
|
||
updated_at = Column(TIMESTAMP, default=datetime.now, onupdate=datetime.now)
|