Spaces:
Running
Running
| """SQLAlchemy ORM models — PostgreSQL.""" | |
| import uuid | |
| from datetime import datetime, timezone | |
| from sqlalchemy import ( | |
| Column, String, Integer, Float, Text, Boolean, DateTime, | |
| ForeignKey, JSON, BigInteger, ARRAY | |
| ) | |
| from sqlalchemy.dialects.postgresql import UUID as PUUID | |
| from sqlalchemy.dialects.postgresql import JSONB | |
| from sqlalchemy.orm import relationship | |
| from app.database import Base | |
| def utcnow(): | |
| return datetime.now(timezone.utc) | |
| class Session(Base): | |
| __tablename__ = "sessions" | |
| id = Column(PUUID(as_uuid=True), primary_key=True, default=uuid.uuid4) | |
| name = Column(String(256), nullable=True) | |
| project_id = Column(PUUID(as_uuid=True), ForeignKey("projects.id", ondelete="SET NULL"), nullable=True, index=True) | |
| codebase_version = Column(String(16), nullable=True) | |
| info = Column(JSONB, nullable=True) | |
| episode_count = Column(Integer, default=0) | |
| original_archive = Column(String(512), nullable=True) | |
| created_at = Column(DateTime(timezone=True), default=utcnow) | |
| episodes = relationship("Episode", back_populates="session", cascade="all, delete-orphan") | |
| class Episode(Base): | |
| __tablename__ = "episodes" | |
| id = Column(PUUID(as_uuid=True), primary_key=True, default=uuid.uuid4) | |
| session_id = Column(PUUID(as_uuid=True), ForeignKey("sessions.id", ondelete="SET NULL"), nullable=True, index=True) | |
| name = Column(String(256), nullable=True) | |
| task_description = Column(Text, nullable=True) | |
| task_id = Column(Integer, nullable=True) | |
| # Status: collecting | completed | reviewed (legacy) | |
| # received | processing | to_review | approved | rejected | failed (new pipeline) | |
| status = Column(String(16), default="collecting", index=True) | |
| frame_count = Column(Integer, default=0) | |
| fps = Column(Integer, default=30) | |
| camera_names = Column(JSONB, nullable=True) # ["cam_front", "cam_wrist"] | |
| meta = Column(JSONB, nullable=True) | |
| # Pipeline timestamps | |
| received_at = Column(DateTime(timezone=True), nullable=True, index=True) # 上传完成时间 | |
| processing_started_at = Column(DateTime(timezone=True), nullable=True) # 开始处理 | |
| review_ready_at = Column(DateTime(timezone=True), nullable=True) # 处理完成，待审核 | |
| approved_at = Column(DateTime(timezone=True), nullable=True) # 审核通过 | |
| rejected_at = Column(DateTime(timezone=True), nullable=True) # 审核拒绝 | |
| # Cleaning / validation report | |
| cleaning_report = Column(JSONB, nullable=True) # {passed, checks: [{name, status, detail}], summary} | |
| created_at = Column(DateTime(timezone=True), default=utcnow, index=True) | |
| updated_at = Column(DateTime(timezone=True), default=utcnow, onupdate=utcnow) | |
| deleted_at = Column(DateTime(timezone=True), nullable=True) # 软删除时间戳 | |
| session = relationship("Session", back_populates="episodes") | |
| frames = relationship("Frame", back_populates="episode", cascade="all, delete-orphan") | |
| annotations = relationship("AnnotationSegment", back_populates="episode", cascade="all, delete-orphan") | |
| class Frame(Base): | |
| __tablename__ = "frames" | |
| id = Column(BigInteger, primary_key=True, autoincrement=True) | |
| episode_id = Column(PUUID(as_uuid=True), ForeignKey("episodes.id", ondelete="CASCADE"), nullable=False, index=True) | |
| frame_index = Column(Integer, nullable=False) | |
| timestamp = Column(Float, nullable=True) | |
| observation = Column(JSONB, nullable=True) | |
| action = Column(JSONB, nullable=True) | |
| reward = Column(Float, nullable=True) | |
| is_terminal = Column(Boolean, default=False) | |
| is_truncated = Column(Boolean, default=False) | |
| image_paths = Column(JSONB, nullable=True) | |
| created_at = Column(DateTime(timezone=True), default=utcnow) | |
| episode = relationship("Episode", back_populates="frames") | |
| class ExportJob(Base): | |
| __tablename__ = "export_jobs" | |
| id = Column(PUUID(as_uuid=True), primary_key=True, default=uuid.uuid4) | |
| dataset_name = Column(String(128), nullable=False) | |
| status = Column(String(16), default="pending") # pending | running | completed | failed | |
| episode_ids = Column(ARRAY(PUUID(as_uuid=True)), nullable=True) | |
| split_ratio = Column(Float, default=0.9) | |
| progress = Column(Float, default=0.0) | |
| output_dir = Column(String(512), nullable=True) | |
| export_format = Column(String(16), default="lerobot_v3") | |
| error = Column(Text, nullable=True) | |
| created_at = Column(DateTime(timezone=True), default=utcnow) | |
| finished_at = Column(DateTime(timezone=True), nullable=True) | |
| class AnnotationSegment(Base): | |
| """Frame-precise annotation segment — a labeled range of frames within an episode.""" | |
| __tablename__ = "annotation_segments" | |
| id = Column(PUUID(as_uuid=True), primary_key=True, default=uuid.uuid4) | |
| episode_id = Column(PUUID(as_uuid=True), ForeignKey("episodes.id", ondelete="CASCADE"), nullable=False, index=True) | |
| label = Column(String(256), nullable=False) | |
| start_frame_index = Column(Integer, nullable=False) | |
| end_frame_index = Column(Integer, nullable=False) | |
| color = Column(String(7), nullable=True, default="#3B82F6") # hex color | |
| sort_order = Column(Integer, default=0) | |
| notes = Column(Text, nullable=True) | |
| created_at = Column(DateTime(timezone=True), default=utcnow) | |
| updated_at = Column(DateTime(timezone=True), default=utcnow, onupdate=utcnow) | |
| episode = relationship("Episode", back_populates="annotations") | |
| keyframes = relationship("AnnotationKeyframe", back_populates="segment", cascade="all, delete-orphan") | |
| class AnnotationKeyframe(Base): | |
| """A single keyframe marker within an annotation segment — marks a critical moment.""" | |
| __tablename__ = "annotation_keyframes" | |
| id = Column(PUUID(as_uuid=True), primary_key=True, default=uuid.uuid4) | |
| segment_id = Column(PUUID(as_uuid=True), ForeignKey("annotation_segments.id", ondelete="CASCADE"), nullable=True, index=True) | |
| episode_id = Column(PUUID(as_uuid=True), ForeignKey("episodes.id", ondelete="CASCADE"), nullable=False, index=True) | |
| frame_index = Column(Integer, nullable=False) | |
| event = Column(String(256), nullable=True) # e.g. "手指接触杯身" | |
| created_at = Column(DateTime(timezone=True), default=utcnow) | |
| segment = relationship("AnnotationSegment", back_populates="keyframes") | |
| class TaskDefinition(Base): | |
| """Pre-defined collection task with targets. Matches uploaded episodes by task_description.""" | |
| __tablename__ = "task_definitions" | |
| id = Column(PUUID(as_uuid=True), primary_key=True, default=uuid.uuid4) | |
| name = Column(String(256), nullable=False, unique=True, index=True) # matches episode.task_description | |
| description = Column(Text, nullable=True) | |
| claimer = Column(String(128), nullable=True, index=True) # who claims this task (e.g. HandTest_001) | |
| target_episodes = Column(Integer, default=0) # target number of batches (1 zip upload = 1 batch) | |
| target_frames = Column(Integer, default=0) # (deprecated, kept for compat) | |
| target_duration_sec = Column(Integer, default=0) # (deprecated, kept for compat) | |
| fps = Column(Integer, default=30) | |
| params = Column(JSONB, nullable=True) # extra params, e.g. {"object":"cylinder","hand":"right"} | |
| status = Column(String(16), default="active") # active | paused | completed | |
| created_at = Column(DateTime(timezone=True), default=utcnow) | |
| updated_at = Column(DateTime(timezone=True), default=utcnow, onupdate=utcnow) | |
| class Device(Base): | |
| """Registered data-collection device (robot / test rig / DAQ client).""" | |
| __tablename__ = "devices" | |
| id = Column(PUUID(as_uuid=True), primary_key=True, default=uuid.uuid4) | |
| name = Column(String(128), nullable=False, unique=True, index=True) | |
| meta = Column(JSONB, nullable=True) | |
| last_seen_at = Column(DateTime(timezone=True), default=utcnow) | |
| first_seen_at = Column(DateTime(timezone=True), default=utcnow) | |
| status = Column(String(16), default="online") # online | offline | |
| created_at = Column(DateTime(timezone=True), default=utcnow) | |
| class User(Base): | |
| """Platform user accounts.""" | |
| __tablename__ = "users" | |
| id = Column(PUUID(as_uuid=True), primary_key=True, default=uuid.uuid4) | |
| username = Column(String(64), unique=True, nullable=False, index=True) | |
| password_hash = Column(String(256), nullable=False) | |
| email = Column(String(128), nullable=True) | |
| role = Column(String(16), default="engineer") # admin | reviewer | engineer | |
| status = Column(String(16), default="active") # active | disabled | |
| expires_at = Column(DateTime(timezone=True), nullable=True, index=True) | |
| last_login_at = Column(DateTime(timezone=True), nullable=True) | |
| created_at = Column(DateTime(timezone=True), default=utcnow) | |
| # ── Workflow Studio ───────────────────────────────────── | |
| class Project(Base): | |
| """A collection project — binds one workflow to a device/task flow. | |
| Example: "鱼眼相机采集项目" binds the fisheye pipeline (fisheye_camera | |
| → mediapipe_hand → export) so uploads under this project automatically | |
| run its workflow. Calibration params follow the LeRobot dataset layout | |
| (calibration/<camera>.json), so the worker reads them without extra input. | |
| """ | |
| __tablename__ = "projects" | |
| id = Column(PUUID(as_uuid=True), primary_key=True, default=uuid.uuid4) | |
| name = Column(String(256), nullable=False, unique=True, index=True) | |
| description = Column(Text, nullable=True) | |
| workflow_id = Column(PUUID(as_uuid=True), ForeignKey("workflows.id", ondelete="SET NULL"), nullable=True, index=True) | |
| device_type = Column(String(64), nullable=True) # e.g. "fisheye" | "stereo" | "glove" | |
| params = Column(JSONB, nullable=True) # project-level defaults, e.g. {"position": "head"} | |
| status = Column(String(16), default="active", index=True) # active | paused | archived | |
| created_at = Column(DateTime(timezone=True), default=utcnow) | |
| updated_at = Column(DateTime(timezone=True), default=utcnow, onupdate=utcnow) | |
| workflow = relationship("Workflow") | |
| class Workflow(Base): | |
| """A saved workflow pipeline — stores the React Flow graph as JSON.""" | |
| __tablename__ = "workflows" | |
| id = Column(PUUID(as_uuid=True), primary_key=True, default=uuid.uuid4) | |
| name = Column(String(256), nullable=False) | |
| description = Column(Text, nullable=True) | |
| graph = Column(JSONB, nullable=False, default=dict) # {nodes: [...], edges: [...]} | |
| node_configs = Column(JSONB, nullable=False, default=dict) # per-node config overrides | |
| status = Column(String(16), default="draft", index=True) # draft | active | archived | |
| created_at = Column(DateTime(timezone=True), default=utcnow) | |
| updated_at = Column(DateTime(timezone=True), default=utcnow, onupdate=utcnow) | |
| runs = relationship("WorkflowRun", back_populates="workflow", cascade="all, delete-orphan") | |
| class WorkflowRun(Base): | |
| """A single execution run of a workflow.""" | |
| __tablename__ = "workflow_runs" | |
| id = Column(PUUID(as_uuid=True), primary_key=True, default=uuid.uuid4) | |
| workflow_id = Column(PUUID(as_uuid=True), ForeignKey("workflows.id", ondelete="CASCADE"), | |
| nullable=False, index=True) | |
| episode_id = Column(PUUID(as_uuid=True), ForeignKey("episodes.id", ondelete="SET NULL"), | |
| nullable=True, index=True) | |
| status = Column(String(16), default="queued", index=True) # queued | running | completed | failed | |
| started_at = Column(DateTime(timezone=True), nullable=True) | |
| finished_at = Column(DateTime(timezone=True), nullable=True) | |
| node_states = Column(JSONB, nullable=False, default=dict) # {node_id: {status, progress}} | |
| error_log = Column(Text, nullable=True) | |
| worker_id = Column(String(128), nullable=True, index=True) | |
| lease_until = Column(DateTime(timezone=True), nullable=True, index=True) | |
| heartbeat_at = Column(DateTime(timezone=True), nullable=True) | |
| attempt = Column(Integer, nullable=False, default=0) | |
| progress = Column(Float, nullable=False, default=0.0) | |
| outputs = Column(JSONB, nullable=False, default=dict) | |
| created_at = Column(DateTime(timezone=True), default=utcnow) | |
| workflow = relationship("Workflow", back_populates="runs") | |