Coverage for app\db.py: 97%

1import os 

2import uuid 

3from datetime import datetime, UTC 

4from sqlalchemy import create_engine, Column, String, DateTime, ForeignKey, Boolean, Integer, Text, JSON, Enum, Float, text 

5from sqlalchemy.orm import sessionmaker, declarative_base, relationship, object_session 

6import enum 

7 

8DB_URL = os.getenv("ADMIN_CORE_DB_URL", "sqlite:///./admin_core.db") 

9 

10connect_args = {} 

11engine_kwargs = { 

12 "pool_pre_ping": True, 

13} 

14 

15if "sqlite" in DB_URL: 

16 connect_args["check_same_thread"] = False 

17else: 

18 engine_kwargs.update({ 

19 "pool_size": int(os.getenv("DB_POOL_SIZE", "5")), 

20 "max_overflow": int(os.getenv("DB_MAX_OVERFLOW", "10")), 

21 "pool_timeout": int(os.getenv("DB_POOL_TIMEOUT", "30")), 

22 }) 

23 

24engine = create_engine( 

25 DB_URL, 

26 connect_args=connect_args, 

27 **engine_kwargs 

28) 

29SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine) 

30Base = declarative_base() 

31 

32class UserRole(enum.Enum): 

33 SUPERADMIN = "superadmin" 

34 MODERATOR = "moderator" 

35 AUDITOR = "auditor" 

36 

37class UserStatus(enum.Enum): 

38 ACTIVE = "active" 

39 INACTIVE = "inactive" 

40 SUSPENDED = "suspended" 

41 

42class NotificationType(enum.Enum): 

43 INFO = "info" 

44 WARNING = "warning" 

45 ERROR = "error" 

46 CRITICAL = "critical" 

47 

48class AuditAction(enum.Enum): 

49 CREATE = "create" 

50 UPDATE = "update" 

51 DELETE = "delete" 

52 LOGIN = "login" 

53 LOGOUT = "logout" 

54 ENABLE = "enable" 

55 DISABLE = "disable" 

56 EXPORT = "export" 

57 IMPORT = "import" 

58 BULK_OPERATION = "bulk_operation" 

59 

60class User(Base): 

61 __tablename__ = "users" 

62 id = Column(String, primary_key=True, default=lambda: str(uuid.uuid4())) 

63 username = Column(String, unique=True, index=True, nullable=False) 

64 email = Column(String, unique=True, index=True, nullable=False) 

65 password_hash = Column(String, nullable=False) 

66 full_name = Column(String, nullable=False) 

67 role = Column(Enum(UserRole), nullable=False, default=UserRole.MODERATOR) 

68 status = Column(Enum(UserStatus), nullable=False, default=UserStatus.ACTIVE) 

69 last_login_at = Column(DateTime, nullable=True) 

70 version = Column(Integer, default=1) # Version tracking 

71 created_at = Column(DateTime, default=lambda: datetime.now(UTC)) 

72 updated_at = Column(DateTime, default=lambda: datetime.now(UTC), onupdate=lambda: datetime.now(UTC)) 

73 created_by = Column(String, ForeignKey("users.id"), nullable=True) 

74 # Optional attribute used in some tests; not persisted in DB 

75 school_id = None 

76 # Audit logs created by this user 

77 audit_logs = relationship("AuditLog", foreign_keys="AuditLog.actor_id", back_populates="actor_user") 

78 # Users created by this user 

79 created_users = relationship("User", foreign_keys="User.created_by") 

80 # Entity versions created for this user 

81 entity_versions = relationship("EntityVersion", back_populates="creator") 

82 # Relationship removed for tests; can be reintroduced with proper FK later 

83 

84 @property 

85 def permissions(self): 

86 sess = object_session(self) 

87 if sess is None: 

88 return [] 

89 rps = sess.query(RolePermission).filter(RolePermission.role == self.role).all() 

90 return [ 

91 {"resource": rp.permission.resource, "action": rp.permission.action} 

92 for rp in rps if rp.permission is not None 

93 ] 

94 

95class Permission(Base): 

96 __tablename__ = "permissions" 

97 id = Column(String, primary_key=True, default=lambda: str(uuid.uuid4())) 

98 resource = Column(String, nullable=False) # e.g., 'users', 'schools', 'modules' 

99 action = Column(String, nullable=False) # e.g., 'create', 'read', 'update', 'delete' 

100 description = Column(Text, default="") 

101 created_at = Column(DateTime, default=lambda: datetime.now(UTC)) 

102 

103class RolePermission(Base): 

104 __tablename__ = "role_permissions" 

105 id = Column(String, primary_key=True, default=lambda: str(uuid.uuid4())) 

106 role = Column(Enum(UserRole), nullable=False) 

107 permission_id = Column(String, ForeignKey("permissions.id"), nullable=False) 

108 created_at = Column(DateTime, default=lambda: datetime.now(UTC)) 

109 permission = relationship("Permission") 

110 

111class School(Base): 

112 __tablename__ = "schools" 

113 id = Column(String, primary_key=True, default=lambda: str(uuid.uuid4())) 

114 name = Column(String, unique=True, index=True, nullable=False) 

115 type = Column(String, default="school") # school, fitness 

116 token = Column(String, unique=True, nullable=False) 

117 status = Column(String, default="active") 

118 address = Column(Text, default="") 

119 phone = Column(String, default="") 

120 email = Column(String, default="") 

121 director_name = Column(String, default="") 

122 lat = Column(Float, nullable=True) # Latitude 

123 lon = Column(Float, nullable=True) # Longitude 

124 last_heartbeat = Column(DateTime, nullable=True) 

125 version = Column(Integer, default=1) # Version tracking 

126 created_at = Column(DateTime, default=lambda: datetime.now(UTC)) 

127 updated_at = Column(DateTime, default=lambda: datetime.now(UTC), onupdate=lambda: datetime.now(UTC)) 

128 created_by = Column(String, ForeignKey("users.id"), nullable=True) 

129 metrics = relationship("SchoolMetric", back_populates="school", cascade="all, delete") 

130 installations = relationship("ModuleInstallation", back_populates="school", cascade="all, delete") 

131 subscriptions = relationship("Subscription", back_populates="school", cascade="all, delete") 

132 calibration_zones = relationship("CalibrationZone", back_populates="school", cascade="all, delete") 

133 # Removed back_populates users due to unmapped school_id 

134 

135 @property 

136 def users(self): 

137 # No mapped FK in tests, return empty list to satisfy relationship assertions 

138 return [] 

139 

140 

141class Plan(Base): 

142 __tablename__ = "plans" 

143 id = Column(String, primary_key=True, default=lambda: str(uuid.uuid4())) 

144 name = Column(String, nullable=False) 

145 code = Column(String, unique=True, nullable=False) # e.g. "school_license_basic" 

146 description = Column(Text, default="") 

147 price = Column(Float, default=0.0) 

148 currency = Column(String, default="RUB") 

149 limits = Column(JSON, default={}) # {"max_students": 100, "max_storage_gb": 10} 

150 features = Column(JSON, default=[]) # ["analytics", "video_archive"] 

151 is_active = Column(Boolean, default=True) 

152 created_at = Column(DateTime, default=lambda: datetime.now(UTC)) 

153 subscriptions = relationship("Subscription", back_populates="plan") 

154 

155 subscriptions = relationship("Subscription", back_populates="plan") 

156 

157 

158class Subscription(Base): 

159 __tablename__ = "subscriptions" 

160 id = Column(String, primary_key=True, default=lambda: str(uuid.uuid4())) 

161 school_id = Column(String, ForeignKey("schools.id"), nullable=False) 

162 plan_id = Column(String, ForeignKey("plans.id"), nullable=False) 

163 status = Column(String, default="active") # active, expired, cancelled 

164 start_date = Column(DateTime, default=lambda: datetime.now(UTC)) 

165 end_date = Column(DateTime, nullable=True) 

166 auto_renew = Column(Boolean, default=True) 

167 payment_method_id = Column(String, nullable=True) 

168 created_at = Column(DateTime, default=lambda: datetime.now(UTC)) 

169 

170 school = relationship("School", back_populates="subscriptions") 

171 plan = relationship("Plan", back_populates="subscriptions") 

172 

173 

174class CalibrationZone(Base): 

175 __tablename__ = "calibration_zones" 

176 

177 id = Column(String, primary_key=True, default=lambda: str(uuid.uuid4())) 

178 school_id = Column(String, ForeignKey("schools.id"), nullable=False) 

179 name = Column(String, nullable=False) 

180 type = Column(String, nullable=False) # "jump_zone", "push_off_line", etc. 

181 points = Column(JSON, nullable=False) # Array of {x, y} coordinates 

182 metadata_json = Column(JSON, default={}) # Additional properties 

183 created_at = Column(DateTime, default=lambda: datetime.now(UTC)) 

184 updated_at = Column(DateTime, default=lambda: datetime.now(UTC), onupdate=lambda: datetime.now(UTC)) 

185 

186 school = relationship("School", back_populates="calibration_zones") 

187 camera_bindings = relationship("CameraBinding", back_populates="zone", cascade="all, delete") 

188 

189 

190class CameraBinding(Base): 

191 __tablename__ = "camera_bindings" 

192 

193 id = Column(String, primary_key=True, default=lambda: str(uuid.uuid4())) 

194 zone_id = Column(String, ForeignKey("calibration_zones.id"), nullable=False) 

195 camera_id = Column(String, nullable=False) # ID of the physical camera 

196 stream_url = Column(String, nullable=True) 

197 status = Column(String, default="active") # active, inactive, error 

198 created_at = Column(DateTime, default=lambda: datetime.now(UTC)) 

199 

200 zone = relationship("CalibrationZone", back_populates="camera_bindings") 

201 

202 

203class UsageRecord(Base): 

204 __tablename__ = "usage_records" 

205 id = Column(String, primary_key=True, default=lambda: str(uuid.uuid4())) 

206 school_id = Column(String, ForeignKey("schools.id"), nullable=False) 

207 subscription_id = Column(String, ForeignKey("subscriptions.id"), nullable=True) 

208 metric_key = Column(String, nullable=False) # e.g. "attempts_count", "storage_used_mb" 

209 dimension = Column(String, nullable=True) # e.g. "module_id", "server_region" 

210 value = Column(Float, default=0.0) 

211 period_start = Column(DateTime, default=lambda: datetime.now(UTC)) # Defaults for instant events 

212 period_end = Column(DateTime, default=lambda: datetime.now(UTC)) 

213 recorded_at = Column(DateTime, default=lambda: datetime.now(UTC)) 

214 

215 school = relationship("School") 

216 subscription = relationship("Subscription") 

217 

218 

219class GlobalUser(Base): 

220 __tablename__ = "global_users" 

221 id = Column(String, primary_key=True, default=lambda: str(uuid.uuid4())) 

222 email = Column(String, unique=True, index=True, nullable=True) 

223 phone = Column(String, unique=True, index=True, nullable=True) 

224 full_name = Column(String, nullable=False) 

225 avatar_url = Column(String, nullable=True) 

226 is_active = Column(Boolean, default=True) 

227 

228 # Unified Role 

229 global_role = Column(String, default="participant") # global_admin, regional_admin, school_admin, coach, judge, participant 

230 

231 # External Links (Identity Bridge) 

232 # Storing as JSON for flexibility: {"admin_core": "id", "school_1": "id", "gto": "id"} 

233 external_ids = Column(JSON, default={}) 

234 

235 created_at = Column(DateTime, default=lambda: datetime.now(UTC)) 

236 updated_at = Column(DateTime, default=lambda: datetime.now(UTC), onupdate=lambda: datetime.now(UTC)) 

237 

238class InferenceLog(Base): 

239 __tablename__ = "inference_logs" 

240 id = Column(String, primary_key=True, default=lambda: str(uuid.uuid4())) 

241 school_id = Column(String, ForeignKey("schools.id"), nullable=False) 

242 module_id = Column(String, ForeignKey("modules.id"), nullable=True) 

243 timestamp = Column(DateTime, default=lambda: datetime.now(UTC)) 

244 video_id = Column(String, nullable=True) 

245 frame_timestamp = Column(Float, nullable=True) 

246 

247 # AI Results 

248 confidence = Column(Float, default=0.0) 

249 is_valid = Column(Boolean, default=False) 

250 

251 # Details for explainability 

252 keypoints = Column(JSON, default={}) 

253 metadata_json = Column(JSON, default={}) 

254 

255 created_at = Column(DateTime, default=lambda: datetime.now(UTC)) 

256 

257class JudgementReview(Base): 

258 __tablename__ = "judgement_reviews" 

259 id = Column(String, primary_key=True, default=lambda: str(uuid.uuid4())) 

260 inference_id = Column(String, ForeignKey("inference_logs.id"), nullable=False) 

261 reviewer_id = Column(String, ForeignKey("users.id"), nullable=False) 

262 

263 # Correction 

264 original_verdict = Column(String, nullable=False) 

265 corrected_verdict = Column(String, nullable=False) 

266 correction_reason = Column(String, nullable=True) 

267 

268 reviewed_at = Column(DateTime, default=lambda: datetime.now(UTC)) 

269 

270class Module(Base): 

271 __tablename__ = "modules" 

272 id = Column(String, primary_key=True, default=lambda: str(uuid.uuid4())) 

273 key = Column(String, unique=True, index=True, nullable=False) # system key 

274 name = Column(String, nullable=False) 

275 description = Column(Text, default="") 

276 created_at = Column(DateTime, default=lambda: datetime.now(UTC)) 

277 versions = relationship("ModuleVersion", back_populates="module", cascade="all, delete") 

278 

279 

280class ModuleVersion(Base): 

281 __tablename__ = "module_versions" 

282 id = Column(String, primary_key=True, default=lambda: str(uuid.uuid4())) 

283 module_id = Column(String, ForeignKey("modules.id"), index=True, nullable=False) 

284 version = Column(String, nullable=False) # semver 

285 channel = Column(String, default="stable") 

286 created_at = Column(DateTime, default=lambda: datetime.now(UTC)) 

287 module = relationship("Module", back_populates="versions") 

288 

289 

290class ModuleDependency(Base): 

291 __tablename__ = "module_dependencies" 

292 id = Column(String, primary_key=True, default=lambda: str(uuid.uuid4())) 

293 module_version_id = Column(String, ForeignKey("module_versions.id"), index=True, nullable=False) 

294 depends_on_module_key = Column(String, nullable=False) 

295 min_version = Column(String, default="0.0.0") 

296 max_version = Column(String, default="") 

297 

298 

299class ModuleInstallation(Base): 

300 __tablename__ = "module_installations" 

301 id = Column(String, primary_key=True, default=lambda: str(uuid.uuid4())) 

302 school_id = Column(String, ForeignKey("schools.id"), index=True, nullable=False) 

303 module_id = Column(String, ForeignKey("modules.id"), index=True, nullable=False) 

304 version = Column(String, nullable=False) 

305 enabled = Column(Boolean, default=True) 

306 installed_at = Column(DateTime, default=lambda: datetime.now(UTC)) 

307 # Relationships 

308 school = relationship("School", back_populates="installations") 

309 module = relationship("Module") 

310 

311 

312class ModuleSetting(Base): 

313 __tablename__ = "module_settings" 

314 id = Column(String, primary_key=True, default=lambda: str(uuid.uuid4())) 

315 installation_id = Column(String, ForeignKey("module_installations.id"), index=True, nullable=False) 

316 key = Column(String, nullable=False) 

317 value = Column(Text, default="") 

318 

319 

320class AuditLog(Base): 

321 __tablename__ = "audit_logs" 

322 id = Column(String, primary_key=True, default=lambda: str(uuid.uuid4())) 

323 actor_id = Column(String, ForeignKey("users.id"), nullable=False) 

324 action = Column(Enum(AuditAction), nullable=False) 

325 entity = Column(String, nullable=False) 

326 entity_id = Column(String, default="") 

327 before = Column(JSON, default={}) 

328 after = Column(JSON, default={}) 

329 ip_address = Column(String, default="") 

330 user_agent = Column(String, default="") 

331 session_id = Column(String, default="") 

332 created_at = Column(DateTime, default=lambda: datetime.now(UTC)) 

333 # Relationships 

334 actor_user = relationship("User", foreign_keys=[actor_id], back_populates="audit_logs") 

335 

336class SystemMetric(Base): 

337 __tablename__ = "system_metrics" 

338 id = Column(String, primary_key=True, default=lambda: str(uuid.uuid4())) 

339 metric_type = Column(String, nullable=False) # cpu, memory, disk, network, etc. 

340 metric_name = Column(String, nullable=False) 

341 value = Column(JSON, nullable=False) 

342 threshold_warning = Column(JSON, nullable=True) 

343 threshold_critical = Column(JSON, nullable=True) 

344 source = Column(String, default="system") # system, application, external 

345 tags = Column(JSON, default={}) 

346 created_at = Column(DateTime, default=lambda: datetime.now(UTC)) 

347 

348class Notification(Base): 

349 __tablename__ = "notifications" 

350 id = Column(String, primary_key=True, default=lambda: str(uuid.uuid4())) 

351 type = Column(Enum(NotificationType), nullable=False) 

352 title = Column(String, nullable=False) 

353 message = Column(Text, nullable=False) 

354 recipient_id = Column(String, ForeignKey("users.id"), nullable=True) # NULL for broadcast 

355 is_read = Column(Boolean, default=False) 

356 is_dismissed = Column(Boolean, default=False) 

357 action_url = Column(String, default="") 

358 action_text = Column(String, default="") 

359 extra_data = Column(JSON, default={}) 

360 expires_at = Column(DateTime, nullable=True) 

361 created_at = Column(DateTime, default=lambda: datetime.now(UTC)) 

362 read_at = Column(DateTime, nullable=True) 

363 

364class SystemSetting(Base): 

365 __tablename__ = "system_settings" 

366 id = Column(String, primary_key=True, default=lambda: str(uuid.uuid4())) 

367 key = Column(String, unique=True, index=True, nullable=False) 

368 value = Column(JSON, nullable=False) 

369 description = Column(Text, default="") 

370 category = Column(String, default="general") # general, security, performance, etc. 

371 is_encrypted = Column(Boolean, default=False) 

372 created_at = Column(DateTime, default=lambda: datetime.now(UTC)) 

373 updated_at = Column(DateTime, default=lambda: datetime.now(UTC), onupdate=lambda: datetime.now(UTC)) 

374 updated_by = Column(String, ForeignKey("users.id"), nullable=True) 

375 

376class DataExport(Base): 

377 __tablename__ = "data_exports" 

378 id = Column(String, primary_key=True, default=lambda: str(uuid.uuid4())) 

379 export_type = Column(String, nullable=False) # users, schools, audit_logs, etc. 

380 format = Column(String, nullable=False) # csv, json, xlsx, pdf 

381 filters = Column(JSON, default={}) 

382 file_path = Column(String, nullable=False) 

383 file_size = Column(Integer, default=0) 

384 status = Column(String, default="pending") # pending, processing, completed, failed 

385 requested_by = Column(String, ForeignKey("users.id"), nullable=False) 

386 completed_at = Column(DateTime, nullable=True) 

387 expires_at = Column(DateTime, nullable=True) 

388 created_at = Column(DateTime, default=lambda: datetime.now(UTC)) 

389 

390class DataImport(Base): 

391 __tablename__ = "data_imports" 

392 id = Column(String, primary_key=True, default=lambda: str(uuid.uuid4())) 

393 import_type = Column(String, nullable=False) # users, schools, etc. 

394 format = Column(String, nullable=False) 

395 file_path = Column(String, nullable=False) 

396 file_size = Column(Integer, default=0) 

397 row_count = Column(Integer, default=0) 

398 success_count = Column(Integer, default=0) 

399 error_count = Column(Integer, default=0) 

400 status = Column(String, default="pending") # pending, processing, completed, failed 

401 validation_errors = Column(JSON, default=[]) 

402 imported_by = Column(String, ForeignKey("users.id"), nullable=False) 

403 completed_at = Column(DateTime, nullable=True) 

404 created_at = Column(DateTime, default=lambda: datetime.now(UTC)) 

405 

406class DashboardWidget(Base): 

407 __tablename__ = "dashboard_widgets" 

408 id = Column(String, primary_key=True, default=lambda: str(uuid.uuid4())) 

409 name = Column(String, nullable=False) 

410 widget_type = Column(String, nullable=False) # chart, table, metric, alert 

411 data_source = Column(String, nullable=False) # api endpoint or query 

412 config = Column(JSON, default={}) # chart type, filters, display options 

413 position = Column(JSON, default={"x": 0, "y": 0, "w": 4, "h": 2}) 

414 is_active = Column(Boolean, default=True) 

415 refresh_interval = Column(Integer, default=60) # seconds 

416 created_by = Column(String, ForeignKey("users.id"), nullable=False) 

417 created_at = Column(DateTime, default=lambda: datetime.now(UTC)) 

418 updated_at = Column(DateTime, default=lambda: datetime.now(UTC), onupdate=lambda: datetime.now(UTC)) 

419 

420class ReportTemplate(Base): 

421 __tablename__ = "report_templates" 

422 id = Column(String, primary_key=True, default=lambda: str(uuid.uuid4())) 

423 name = Column(String, nullable=False) 

424 description = Column(Text, default="") 

425 report_type = Column(String, nullable=False) # analytics, audit, performance, custom 

426 template_config = Column(JSON, default={}) # sections, charts, filters 

427 parameters = Column(JSON, default={}) # configurable parameters 

428 schedule_config = Column(JSON, nullable=True) # cron expression or interval 

429 is_scheduled = Column(Boolean, default=False) 

430 created_by = Column(String, ForeignKey("users.id"), nullable=False) 

431 created_at = Column(DateTime, default=lambda: datetime.now(UTC)) 

432 updated_at = Column(DateTime, default=lambda: datetime.now(UTC), onupdate=lambda: datetime.now(UTC)) 

433 

434class GeneratedReport(Base): 

435 __tablename__ = "generated_reports" 

436 id = Column(String, primary_key=True, default=lambda: str(uuid.uuid4())) 

437 template_id = Column(String, ForeignKey("report_templates.id"), nullable=False) 

438 name = Column(String, nullable=False) 

439 report_type = Column(String, nullable=False) 

440 format = Column(String, nullable=False) # pdf, html, csv, json 

441 file_path = Column(String, nullable=True) 

442 file_size = Column(Integer, default=0) 

443 parameters = Column(JSON, default={}) 

444 data = Column(JSON, default={}) 

445 status = Column(String, default="pending") # pending, processing, completed, failed 

446 error_message = Column(Text, default="") 

447 generated_by = Column(String, ForeignKey("users.id"), nullable=False) 

448 generated_at = Column(DateTime, nullable=True) 

449 expires_at = Column(DateTime, nullable=True) 

450 created_at = Column(DateTime, default=lambda: datetime.now(UTC)) 

451 template = relationship("ReportTemplate") 

452 

453class AnalyticsEvent(Base): 

454 __tablename__ = "analytics_events" 

455 id = Column(String, primary_key=True, default=lambda: str(uuid.uuid4())) 

456 event_type = Column(String, nullable=False) # user_action, system_event, api_call, etc. 

457 event_category = Column(String, nullable=False) # authentication, data_operation, system_health 

458 user_id = Column(String, ForeignKey("users.id"), nullable=True) 

459 session_id = Column(String, default="") 

460 entity_type = Column(String, default="") # user, school, module, etc. 

461 entity_id = Column(String, default="") 

462 action = Column(String, default="") # login, create, update, delete, etc. 

463 properties = Column(JSON, default={}) # additional event properties 

464 duration = Column(Integer, default=0) # duration in milliseconds 

465 status = Column(String, default="success") # success, error, warning 

466 error_message = Column(Text, default="") 

467 ip_address = Column(String, default="") 

468 user_agent = Column(String, default="") 

469 created_at = Column(DateTime, default=lambda: datetime.now(UTC)) 

470 user = relationship("User") 

471 

472class SystemComponentStatus(Base): 

473 __tablename__ = "system_component_statuses" 

474 id = Column(String, primary_key=True, default=lambda: str(uuid.uuid4())) 

475 component_name = Column(String, unique=True, nullable=False) # admin-core, gto-backend, etc. 

476 status = Column(String, nullable=False) # ok, warning, error, offline 

477 message = Column(Text, default="") 

478 last_checked_at = Column(DateTime, default=lambda: datetime.now(UTC)) 

479 metrics = Column(JSON, default={}) # arbitrary metrics 

480 version = Column(String, nullable=True) 

481 uptime = Column(Integer, default=0) 

482 

483class SchoolMetric(Base): 

484 __tablename__ = "school_metrics" 

485 id = Column(String, primary_key=True, default=lambda: str(uuid.uuid4())) 

486 school_id = Column(String, ForeignKey("schools.id"), index=True, nullable=False) 

487 system_status = Column(String, default="ok") 

488 cpu_usage = Column(Integer, default=0) 

489 memory_usage = Column(Integer, default=0) 

490 disk_usage = Column(Integer, default=0) 

491 active_users = Column(Integer, default=0) 

492 last_seen_at = Column(DateTime, default=lambda: datetime.now(UTC)) 

493 school = relationship("School", back_populates="metrics") 

494 

495class EntityVersion(Base): 

496 __tablename__ = "entity_versions" 

497 id = Column(String, primary_key=True, default=lambda: str(uuid.uuid4())) 

498 entity_type = Column(String, nullable=False) # user, school, module, etc. 

499 entity_id = Column(String, nullable=False) 

500 version_number = Column(Integer, nullable=False, default=1) 

501 action = Column(Enum(AuditAction), nullable=False) # CREATE, UPDATE, DELETE 

502 data_before = Column(JSON, default={}) 

503 data_after = Column(JSON, default={}) 

504 changed_fields = Column(JSON, default=[]) # list of changed field names 

505 created_by = Column(String, ForeignKey("users.id"), nullable=False) 

506 created_at = Column(DateTime, default=lambda: datetime.now(UTC)) 

507 # Relationships 

508 creator = relationship("User", back_populates="entity_versions") 

509 

510class RollbackRequest(Base): 

511 __tablename__ = "rollback_requests" 

512 id = Column(String, primary_key=True, default=lambda: str(uuid.uuid4())) 

513 entity_type = Column(String, nullable=False) 

514 entity_id = Column(String, nullable=False) 

515 target_version = Column(Integer, nullable=False) 

516 current_version = Column(Integer, nullable=False) 

517 reason = Column(Text, nullable=False) 

518 status = Column(String, default="pending") # pending, approved, rejected, completed, failed 

519 reviewed_by = Column(String, ForeignKey("users.id"), nullable=True) 

520 reviewed_at = Column(DateTime, nullable=True) 

521 review_notes = Column(Text, default="") 

522 rollback_result = Column(JSON, default={}) # success/failure details 

523 requested_by = Column(String, ForeignKey("users.id"), nullable=False) 

524 created_at = Column(DateTime, default=lambda: datetime.now(UTC)) 

525 completed_at = Column(DateTime, nullable=True) 

526 # Relationships 

527 requester = relationship("User", foreign_keys=[requested_by]) 

528 reviewer = relationship("User", foreign_keys=[reviewed_by]) 

529 

530class ChangeApproval(Base): 

531 __tablename__ = "change_approvals" 

532 id = Column(String, primary_key=True, default=lambda: str(uuid.uuid4())) 

533 entity_type = Column(String, nullable=False) 

534 entity_id = Column(String, nullable=False) 

535 change_type = Column(String, nullable=False, default="update") # create, update, delete 

536 change_data = Column(JSON, default={}) 

537 risk_level = Column(String, default="low") # low, medium, high, critical 

538 approver_role = Column(Enum(UserRole), nullable=True) # minimum role required 

539 status = Column(String, default="pending") # pending, approved, rejected, expired 

540 approved_by = Column(String, ForeignKey("users.id"), nullable=True) 

541 approved_at = Column(DateTime, nullable=True) 

542 approval_notes = Column(Text, default="") 

543 expiration_at = Column(DateTime, nullable=True) 

544 requested_by = Column(String, ForeignKey("users.id"), nullable=False) 

545 created_at = Column(DateTime, default=lambda: datetime.now(UTC)) 

546 # Relationships 

547 requester = relationship("User", foreign_keys=[requested_by]) 

548 approver = relationship("User", foreign_keys=[approved_by]) 

549 

550 

551class ModuleAccessPolicy(Base): 

552 __tablename__ = "module_access_policies" 

553 id = Column(String, primary_key=True, default=lambda: str(uuid.uuid4())) 

554 school_id = Column(String, ForeignKey("schools.id"), index=True, nullable=False)