"""ORM Models for Face Inference Platform""" from sqlalchemy import CHAR, Column, String, Integer, Float, DateTime, Boolean, ForeignKey, LargeBinary, Index, Enum as SQLEnum, Text from sqlalchemy.orm import relationship from sqlalchemy.dialects.postgresql import UUID as PostgresUUID from sqlalchemy.types import TypeDecorator from datetime import datetime, timezone import uuid import enum from app.core.database import Base class GUID(TypeDecorator): """Platform-independent UUID type. Uses native UUID on PostgreSQL and CHAR(36) elsewhere, including MySQL. """ impl = CHAR cache_ok = True def load_dialect_impl(self, dialect): if dialect.name == "postgresql": return dialect.type_descriptor(PostgresUUID(as_uuid=True)) return dialect.type_descriptor(CHAR(36)) def process_bind_param(self, value, dialect): if value is None: return value if dialect.name == "postgresql": return value if isinstance(value, uuid.UUID) else uuid.UUID(str(value)) return str(value if isinstance(value, uuid.UUID) else uuid.UUID(str(value))) def process_result_value(self, value, dialect): if value is None or isinstance(value, uuid.UUID): return value return uuid.UUID(str(value)) class UserRole(str, enum.Enum): """User role enumeration""" ADMIN = "admin" USER = "user" class Tenant(Base): """Tenant model - represents a customer""" __tablename__ = "tenants" id = Column(GUID(), primary_key=True, default=uuid.uuid4) name = Column(String(255), nullable=False, unique=True) plan = Column(String(50), default="free") # free, pro, enterprise api_quota = Column(Integer, default=10000) # API calls per month storage_quota = Column(Integer, default=1024) # MB is_active = Column(Boolean, default=True) created_at = Column(DateTime, default=lambda: datetime.now(timezone.utc), nullable=False) updated_at = Column(DateTime, default=lambda: datetime.now(timezone.utc), onupdate=lambda: datetime.now(timezone.utc)) # Relationships users = relationship("User", back_populates="tenant", cascade="all, delete-orphan") face_embeddings = relationship("FaceEmbedding", back_populates="tenant", cascade="all, delete-orphan") recognition_logs = relationship("RecognitionLog", back_populates="tenant", cascade="all, delete-orphan") api_keys = relationship("APIKey", back_populates="tenant", cascade="all, delete-orphan") __table_args__ = ( Index("ix_tenant_name", "name"), Index("ix_tenant_is_active", "is_active"), ) class User(Base): """User model - represents a user within a tenant""" __tablename__ = "users" id = Column(GUID(), primary_key=True, default=uuid.uuid4) tenant_id = Column(GUID(), ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False) email = Column(String(255), nullable=False) password_hash = Column(String(255), nullable=False) full_name = Column(String(255), nullable=True) role = Column(SQLEnum(UserRole), default=UserRole.USER) is_active = Column(Boolean, default=True) created_at = Column(DateTime, default=lambda: datetime.now(timezone.utc), nullable=False) updated_at = Column(DateTime, default=lambda: datetime.now(timezone.utc), onupdate=lambda: datetime.now(timezone.utc)) # Relationships tenant = relationship("Tenant", back_populates="users") face_embeddings = relationship("FaceEmbedding", back_populates="user", cascade="all, delete-orphan") recognition_logs = relationship("RecognitionLog", back_populates="user") __table_args__ = ( Index("ix_user_tenant_id_email", "tenant_id", "email"), Index("ix_user_is_active", "is_active"), ) class FaceEmbedding(Base): """Face embedding model - stores face vectors and metadata""" __tablename__ = "face_embeddings" id = Column(GUID(), primary_key=True, default=uuid.uuid4) tenant_id = Column(GUID(), ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False) user_id = Column(GUID(), ForeignKey("users.id", ondelete="CASCADE"), nullable=False) person_id = Column(String(255), nullable=False) # e.g., "EMP001", "PERSON_001" name = Column(String(255), nullable=False) # Person's name # Embedding vector (store as binary for efficiency) embedding_vector = Column(LargeBinary, nullable=False) # numpy array serialized embedding_dim = Column(Integer, nullable=False) # e.g., 512 # Image metadata image_url = Column(String(500), nullable=True) # S3 URL image_hash = Column(String(64), nullable=True) # SHA256 for duplicate detection # Quality metrics face_quality_score = Column(Float, nullable=True) # 0-1 face_angle = Column(String(50), nullable=True) # frontal, profile, etc. # Metadata metadata_json = Column("metadata", Text, nullable=True) # JSON string with additional info created_at = Column(DateTime, default=lambda: datetime.now(timezone.utc), nullable=False) updated_at = Column(DateTime, default=lambda: datetime.now(timezone.utc), onupdate=lambda: datetime.now(timezone.utc)) # Relationships tenant = relationship("Tenant", back_populates="face_embeddings") user = relationship("User", back_populates="face_embeddings") __table_args__ = ( Index("ix_face_tenant_id", "tenant_id"), Index("ix_face_user_id", "user_id"), Index("ix_face_person_id", "person_id"), Index("ix_face_tenant_person", "tenant_id", "person_id"), ) class RecognitionLog(Base): """Recognition log model - tracks recognition results""" __tablename__ = "recognition_logs" id = Column(GUID(), primary_key=True, default=uuid.uuid4) tenant_id = Column(GUID(), ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False) user_id = Column(GUID(), ForeignKey("users.id", ondelete="CASCADE"), nullable=False) recognized_person_id = Column(String(255), nullable=False) # Which person was recognized recognized_embedding_id = Column(GUID(), nullable=True) # Which embedding matched confidence = Column(Float, nullable=False) # 0-1 confidence score # Request metadata source = Column(String(50), nullable=False) # "api", "webcam", "upload" image_url = Column(String(500), nullable=True) # Reference to input image # Timestamp timestamp = Column(DateTime, default=lambda: datetime.now(timezone.utc), nullable=False) # Relationships tenant = relationship("Tenant", back_populates="recognition_logs") user = relationship("User", back_populates="recognition_logs") __table_args__ = ( Index("ix_recog_tenant_id", "tenant_id"), Index("ix_recog_user_id", "user_id"), Index("ix_recog_timestamp", "timestamp"), Index("ix_recog_tenant_timestamp", "tenant_id", "timestamp"), ) class APIKey(Base): """API Key model - for programmatic access""" __tablename__ = "api_keys" id = Column(GUID(), primary_key=True, default=uuid.uuid4) tenant_id = Column(GUID(), ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False) user_id = Column(GUID(), ForeignKey("users.id", ondelete="CASCADE"), nullable=True) # API key hash (don't store plain keys) api_key_hash = Column(String(255), nullable=False, unique=True) key_name = Column(String(255), nullable=False) # e.g., "Production API Key" is_active = Column(Boolean, default=True) last_used_at = Column(DateTime, nullable=True) created_at = Column(DateTime, default=lambda: datetime.now(timezone.utc), nullable=False) updated_at = Column(DateTime, default=lambda: datetime.now(timezone.utc), onupdate=lambda: datetime.now(timezone.utc)) # Relationships tenant = relationship("Tenant", back_populates="api_keys") user = relationship("User") __table_args__ = ( Index("ix_apikey_tenant_id", "tenant_id"), Index("ix_apikey_is_active", "is_active"), ) class WebhookEndpoint(Base): """Webhook endpoint for tenant events""" __tablename__ = "webhook_endpoints" id = Column(GUID(), primary_key=True, default=uuid.uuid4) tenant_id = Column(GUID(), ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False) url = Column(String(500), nullable=False) secret = Column(String(255), nullable=True) is_active = Column(Boolean, default=True) created_at = Column(DateTime, default=lambda: datetime.now(timezone.utc), nullable=False) updated_at = Column(DateTime, default=lambda: datetime.now(timezone.utc), onupdate=lambda: datetime.now(timezone.utc)) tenant = relationship("Tenant") __table_args__ = ( Index("ix_webhook_tenant_id", "tenant_id"), Index("ix_webhook_is_active", "is_active"), ) class WebhookEvent(Base): """Webhook delivery events with retry tracking""" __tablename__ = "webhook_events" id = Column(GUID(), primary_key=True, default=uuid.uuid4) tenant_id = Column(GUID(), ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False) endpoint_id = Column(GUID(), ForeignKey("webhook_endpoints.id", ondelete="CASCADE"), nullable=False) event_type = Column(String(100), nullable=False) payload = Column(Text, nullable=False) attempt_count = Column(Integer, default=0) last_status_code = Column(Integer, nullable=True) delivered_at = Column(DateTime, nullable=True) created_at = Column(DateTime, default=lambda: datetime.now(timezone.utc), nullable=False) updated_at = Column(DateTime, default=lambda: datetime.now(timezone.utc), onupdate=lambda: datetime.now(timezone.utc)) endpoint = relationship("WebhookEndpoint") tenant = relationship("Tenant") __table_args__ = ( Index("ix_webhook_event_tenant_id", "tenant_id"), Index("ix_webhook_event_endpoint_id", "endpoint_id"), Index("ix_webhook_event_type", "event_type"), ) class UsageMeter(Base): """Monthly usage meters for billing""" __tablename__ = "usage_meters" id = Column(GUID(), primary_key=True, default=uuid.uuid4) tenant_id = Column(GUID(), ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False) user_id = Column(GUID(), ForeignKey("users.id", ondelete="CASCADE"), nullable=True) period = Column(String(7), nullable=False) # YYYY-MM successful_recognitions = Column(Integer, default=0) api_calls = Column(Integer, default=0) created_at = Column(DateTime, default=lambda: datetime.now(timezone.utc), nullable=False) updated_at = Column(DateTime, default=lambda: datetime.now(timezone.utc), onupdate=lambda: datetime.now(timezone.utc)) tenant = relationship("Tenant") user = relationship("User") __table_args__ = ( Index("ix_usage_tenant_user_period", "tenant_id", "user_id", "period", unique=True), Index("ix_usage_tenant_id", "tenant_id"), )