MicProtocol / app /models /auth.py
Benard John
feat: implement full-stack architecture with database models, authentication services, and web dashboard components
37b5223
Raw History Blame Contribute Delete
7.35 kB
"""
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,
)