""" Ukweli — Authentication & Authorization ORM Models Implements the schema from OAG_Kenya_Auth_Architecture.md Section 5. """ from __future__ import annotations import uuid from datetime import datetime from sqlalchemy import ( Boolean, DateTime, ForeignKey, Index, Integer, String, Text, func, ) from sqlalchemy.dialects.postgresql import INET, JSONB, UUID from sqlalchemy.orm import Mapped, mapped_column, relationship from app.models.database import Base from app.models.enums import APIKeyStatus, AuthEventType, AuthTier, OTPPurpose, UserStatus class User(Base): """Registered tier users (email + OTP).""" __tablename__ = "users" id: Mapped[uuid.UUID] = mapped_column( UUID(as_uuid=True), primary_key=True, default=uuid.uuid4 ) email: Mapped[str] = mapped_column(String(255), unique=True, nullable=False, index=True) email_verified: Mapped[bool] = mapped_column(Boolean, nullable=False, default=False) tier: Mapped[str] = mapped_column( String(20), nullable=False, default=AuthTier.REGISTERED.value ) status: Mapped[str] = mapped_column( String(20), nullable=False, default=UserStatus.ACTIVE.value ) display_name: Mapped[str | None] = mapped_column(String(100), nullable=True) organization: Mapped[str | None] = mapped_column(String(100), nullable=True) last_login_at: Mapped[datetime | None] = mapped_column( DateTime(timezone=True), nullable=True ) metadata_extra: Mapped[dict] = mapped_column( "metadata", JSONB, nullable=False, default=dict ) created_at: Mapped[datetime] = mapped_column( DateTime(timezone=True), server_default=func.now(), nullable=False ) updated_at: Mapped[datetime] = mapped_column( DateTime(timezone=True), server_default=func.now(), onupdate=func.now(), nullable=False, ) # Relationships api_keys: Mapped[list["ApiKey"]] = relationship( "ApiKey", back_populates="user", cascade="all, delete-orphan" ) sessions: Mapped[list["Session"]] = relationship( "Session", back_populates="user", cascade="all, delete-orphan" ) class OTP(Base): """One-time passcodes for signup/login/reset (also cached in Redis).""" __tablename__ = "otps" id: Mapped[uuid.UUID] = mapped_column( UUID(as_uuid=True), primary_key=True, default=uuid.uuid4 ) email: Mapped[str] = mapped_column(String(255), nullable=False, index=True) code_hash: Mapped[str] = mapped_column(String(64), nullable=False) purpose: Mapped[str] = mapped_column( String(20), nullable=False, default=OTPPurpose.SIGNUP.value ) expires_at: Mapped[datetime] = mapped_column( DateTime(timezone=True), nullable=False ) used_at: Mapped[datetime | None] = mapped_column( DateTime(timezone=True), nullable=True ) attempts: Mapped[int] = mapped_column(Integer, nullable=False, default=0) created_at: Mapped[datetime] = mapped_column( DateTime(timezone=True), server_default=func.now(), nullable=False ) __table_args__ = ( Index("ix_otps_email_purpose", "email", "purpose"), ) class ApiKey(Base): """Developer API keys (tier: api).""" __tablename__ = "api_keys" id: Mapped[uuid.UUID] = mapped_column( UUID(as_uuid=True), primary_key=True, default=uuid.uuid4 ) user_id: Mapped[uuid.UUID | None] = mapped_column( UUID(as_uuid=True), ForeignKey("users.id", ondelete="CASCADE"), nullable=True, index=True, ) key_id: Mapped[str] = mapped_column( String(50), unique=True, nullable=False, index=True ) secret_hash: Mapped[bytes] = mapped_column(nullable=False) name: Mapped[str] = mapped_column(String(100), nullable=False) description: Mapped[str | None] = mapped_column(Text, nullable=True) tier: Mapped[str] = mapped_column( String(20), nullable=False, default=AuthTier.API.value ) quota_per_day: Mapped[int] = mapped_column(Integer, nullable=False, default=10000) requests_today: Mapped[int] = mapped_column(Integer, nullable=False, default=0) requests_today_reset_at: Mapped[datetime] = mapped_column( DateTime(timezone=True), server_default=func.now(), nullable=False ) last_used_at: Mapped[datetime | None] = mapped_column( DateTime(timezone=True), nullable=True ) status: Mapped[str] = mapped_column( String(20), nullable=False, default=APIKeyStatus.ACTIVE.value ) expires_at: Mapped[datetime | None] = mapped_column( DateTime(timezone=True), nullable=True ) metadata_extra: Mapped[dict] = mapped_column( "metadata", JSONB, nullable=False, default=dict ) created_at: Mapped[datetime] = mapped_column( DateTime(timezone=True), server_default=func.now(), nullable=False ) # Relationships user: Mapped[User | None] = relationship("User", back_populates="api_keys") class Session(Base): """Refresh-token sessions (rotated on use).""" __tablename__ = "sessions" id: Mapped[uuid.UUID] = mapped_column( UUID(as_uuid=True), primary_key=True, default=uuid.uuid4 ) user_id: Mapped[uuid.UUID] = mapped_column( UUID(as_uuid=True), ForeignKey("users.id", ondelete="CASCADE"), nullable=False, index=True, ) refresh_token_hash: Mapped[str] = mapped_column(String(64), nullable=False, index=True) user_agent: Mapped[str | None] = mapped_column(Text, nullable=True) ip_address: Mapped[str | None] = mapped_column(String(45), nullable=True) expires_at: Mapped[datetime] = mapped_column( DateTime(timezone=True), nullable=False ) revoked_at: Mapped[datetime | None] = mapped_column( DateTime(timezone=True), nullable=True ) created_at: Mapped[datetime] = mapped_column( DateTime(timezone=True), server_default=func.now(), nullable=False ) # Relationships user: Mapped[User] = relationship("User", back_populates="sessions") class AuthAuditLog(Base): """Immutable WORM-style auth audit trail.""" __tablename__ = "auth_audit_log" id: Mapped[uuid.UUID] = mapped_column( UUID(as_uuid=True), primary_key=True, default=uuid.uuid4 ) event_type: Mapped[str] = mapped_column(String(50), nullable=False, index=True) user_id: Mapped[uuid.UUID | None] = mapped_column( UUID(as_uuid=True), ForeignKey("users.id"), nullable=True ) api_key_id: Mapped[uuid.UUID | None] = mapped_column( UUID(as_uuid=True), ForeignKey("api_keys.id"), nullable=True ) fingerprint: Mapped[str | None] = mapped_column(String(16), nullable=True, index=True) ip_address: Mapped[str | None] = mapped_column(String(45), nullable=True) user_agent: Mapped[str | None] = mapped_column(Text, nullable=True) success: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True) error_message: Mapped[str | None] = mapped_column(Text, nullable=True) metadata_extra: Mapped[dict] = mapped_column( "metadata", JSONB, nullable=False, default=dict ) created_at: Mapped[datetime] = mapped_column( DateTime(timezone=True), server_default=func.now(), nullable=False, index=True, )