import uuid from datetime import date, datetime from sqlalchemy import BigInteger, Boolean, Date, ForeignKey, Integer, String, Text, func from sqlalchemy.dialects.postgresql import CITEXT, ENUM as PGEnum, JSONB, TIMESTAMP, UUID as PGUUID from sqlalchemy.orm import Mapped, mapped_column from app.models.base import Base, TenantScopedMixin, TimestampMixin, UUIDPKMixin tenant_status = PGEnum("active", "inactive", "suspended", name="tenant_status", create_type=False) account_status = PGEnum("active", "inactive", name="account_status", create_type=False) role_scope = PGEnum("internal", "portal", name="role_scope", create_type=False) class Tenant(UUIDPKMixin, TimestampMixin, Base): __tablename__ = "tenants" name: Mapped[str] = mapped_column(String(200), nullable=False) status: Mapped[str] = mapped_column(tenant_status, nullable=False, server_default="active") # Tenant-Admin-defined extra fields for the Public Portal registration # form — see app/schemas/identity.py's RegistrationFieldDef. registration_field_schema: Mapped[list] = mapped_column(JSONB, nullable=False, server_default="[]") # Tenant-Admin-defined look/behaviour of the Public Portal navigation menu # — app/schemas/identity.py's PortalMenuSettings. {} = all defaults. portal_menu_settings: Mapped[dict] = mapped_column(JSONB, nullable=False, server_default="{}") # Tenant-Admin-defined built-in field rules + post-registration # verification for the Public Portal registration form — # app/schemas/identity.py's RegistrationSettings. {} = all defaults. registration_settings: Mapped[dict] = mapped_column(JSONB, nullable=False, server_default="{}") # What to compute into a user's session data at every sign-in — # app/schemas/identity.py's LoginSessionSettings. {} = nothing. login_session_settings: Mapped[dict] = mapped_column(JSONB, nullable=False, server_default="{}") theme: Mapped[dict] = mapped_column(JSONB, nullable=False, server_default="{}") # Sign-in policy and SSO (app/crud/sign_in_policy.py) — never sent in TenantRead. security_settings: Mapped[dict] = mapped_column(JSONB, nullable=False, server_default="{}") # Password rules, set by the Super Admin (app/crud/password_policy.py). password_policy: Mapped[dict] = mapped_column(JSONB, nullable=False, server_default="{}") # How long Task run history is kept (app/crud/task_history.py). task_history_days: Mapped[int] = mapped_column(Integer, nullable=False, server_default="30") # Email connector and templates for its own emails (app/crud/account_emails.py). email_settings: Mapped[dict] = mapped_column(JSONB, nullable=False, server_default="{}") # The time zone portal users see dates in, and exports print them in # (app/core/timezones.py) — set by the Super Admin. timezone: Mapped[str] = mapped_column(Text, nullable=False, server_default="Asia/Calcutta") # The public website (app/routers/public_site.py): /site//, and # its landing page, default SEO and domains — app/schemas/public_site.py's # SiteSettings. {} = no site. site_slug: Mapped[str | None] = mapped_column(String(60)) site_settings: Mapped[dict] = mapped_column(JSONB, nullable=False, server_default="{}") # The license the Super Admin sets (app/crud/licensing.py). None = no limit. license_max_users: Mapped[int | None] = mapped_column(Integer) license_max_portal_users: Mapped[int | None] = mapped_column(Integer) license_starts_on: Mapped[date | None] = mapped_column(Date) license_ends_on: Mapped[date | None] = mapped_column(Date) # When it got a license; None = unlicensed, so it can't be used. license_set_at: Mapped[datetime | None] = mapped_column(TIMESTAMP(timezone=True)) @property def licensed(self) -> bool: return self.license_set_at is not None @property def license(self) -> dict: """The license as one object (app/schemas/identity.py's TenantLicense).""" return { "max_users": self.license_max_users, "max_portal_users": self.license_max_portal_users, "starts_on": self.license_starts_on, "ends_on": self.license_ends_on, } class Vendor(UUIDPKMixin, TenantScopedMixin, TimestampMixin, Base): __tablename__ = "vendors" organization_name: Mapped[str] = mapped_column(String(200), nullable=False) status: Mapped[str] = mapped_column(account_status, nullable=False, server_default="active") class User(UUIDPKMixin, TenantScopedMixin, TimestampMixin, Base): __tablename__ = "users" vendor_id: Mapped[uuid.UUID | None] = mapped_column(PGUUID(as_uuid=True), ForeignKey("vendors.id")) name: Mapped[str] = mapped_column(String(200), nullable=False) email: Mapped[str] = mapped_column(CITEXT, nullable=False) external_auth_id: Mapped[str | None] = mapped_column(String(200)) password_hash: Mapped[str | None] = mapped_column(String(255)) status: Mapped[str] = mapped_column(account_status, nullable=False, server_default="active") last_login_at: Mapped[datetime | None] = mapped_column(TIMESTAMP(timezone=True)) # Values submitted against the registering tenant's # registration_field_schema at signup time — see routers/auth.py. extra_fields: Mapped[dict] = mapped_column(JSONB, nullable=False, server_default="{}") phone: Mapped[str | None] = mapped_column(String(30)) # Account verification chosen when an admin creates the user (Users # page): "email" sends a link + one-time code and blocks sign-in until # it's used; "sms" asks for a texted code at first sign-in. NULL = none # required. Pending = method set and verified_at still NULL. The code/ # token are stored hashed only — see app/crud/user_verification.py. # "both" = email first (sign-in blocked until done), then SMS at the # next sign-in; each channel is tracked by its own *_verified_at, and # verified_at is stamped once every required channel is done. verification_method: Mapped[str | None] = mapped_column(String(10)) verified_at: Mapped[datetime | None] = mapped_column(TIMESTAMP(timezone=True)) email_verified_at: Mapped[datetime | None] = mapped_column(TIMESTAMP(timezone=True)) phone_verified_at: Mapped[datetime | None] = mapped_column(TIMESTAMP(timezone=True)) verification_code_hash: Mapped[str | None] = mapped_column(String(64)) verification_token_hash: Mapped[str | None] = mapped_column(String(64)) verification_expires_at: Mapped[datetime | None] = mapped_column(TIMESTAMP(timezone=True)) verification_sent_at: Mapped[datetime | None] = mapped_column(TIMESTAMP(timezone=True)) verification_attempts: Mapped[int] = mapped_column(Integer, nullable=False, server_default="0") # Two-factor sign-in (app/crud/mfa.py) and lockout (app/crud/sign_in_policy.py). mfa_secret_encrypted: Mapped[str | None] = mapped_column(Text) mfa_enabled_at: Mapped[datetime | None] = mapped_column(TIMESTAMP(timezone=True)) mfa_recovery_hashes: Mapped[list] = mapped_column(JSONB, nullable=False, server_default="[]") mfa_last_step: Mapped[int | None] = mapped_column(BigInteger) # Password policy (app/crud/password_policy.py): when the password last # changed, hashes of earlier ones, and a change due at the next sign-in. password_changed_at: Mapped[datetime | None] = mapped_column(TIMESTAMP(timezone=True)) password_history: Mapped[list] = mapped_column(JSONB, nullable=False, server_default="[]") password_must_change: Mapped[bool] = mapped_column(Boolean, nullable=False, server_default="false") failed_sign_ins: Mapped[int] = mapped_column(Integer, nullable=False, server_default="0") locked_until: Mapped[datetime | None] = mapped_column(TIMESTAMP(timezone=True)) # "Forgot password?" (app/crud/account_emails.py). password_reset_hash: Mapped[str | None] = mapped_column(Text) password_reset_expires_at: Mapped[datetime | None] = mapped_column(TIMESTAMP(timezone=True)) password_reset_sent_at: Mapped[datetime | None] = mapped_column(TIMESTAMP(timezone=True)) # Tokens issued before this are turned away (app/crud/sessions.py). tokens_valid_after: Mapped[datetime | None] = mapped_column(TIMESTAMP(timezone=True)) @property def needs_email_verification(self) -> bool: return self.verification_method in ("email", "both") and self.email_verified_at is None @property def needs_sms_verification(self) -> bool: return self.verification_method in ("sms", "both") and self.phone_verified_at is None @property def verification_pending(self) -> bool: return self.needs_email_verification or self.needs_sms_verification class Role(UUIDPKMixin, TenantScopedMixin, TimestampMixin, Base): __tablename__ = "roles" name: Mapped[str] = mapped_column(String(100), nullable=False) is_system_role: Mapped[bool] = mapped_column(Boolean, nullable=False, server_default="false") scope: Mapped[str] = mapped_column(role_scope, nullable=False, server_default="internal") action_matrix: Mapped[dict] = mapped_column(JSONB, nullable=False, server_default="{}") # portal-scope only — which Page a holder of this role lands on after # logging in. See app/schemas/identity.py for the "must already be # granted in action_matrix['pages']" validation. default_page_id: Mapped[uuid.UUID | None] = mapped_column(PGUUID(as_uuid=True), ForeignKey("pages.id", ondelete="SET NULL")) # Portal roles only — the role's own Portal theme (TenantTheme's fields), # over the tenant's field by field; NULL = the tenant's theme. theme: Mapped[dict | None] = mapped_column(JSONB) # Portal roles only — the Portal menu settings that differ from the # tenant's portal_menu_settings, applied over it; NULL = the tenant's menu. menu_settings: Mapped[dict | None] = mapped_column(JSONB) # Portal roles only — detailed permissions over action_matrix's grants: # the actions allowed on each granted datasource/form, and each granted # page's hidden buttons (schemas/identity.py::check_portal_permissions). # An item with no entry allows every action; NULL = none set. portal_permissions: Mapped[dict | None] = mapped_column(JSONB) @property def default_dashboard_id(self) -> uuid.UUID | None: """Portal-scope only — a Dashboard to land on instead of a Page. Kept inside action_matrix under the "default_dashboard" key (the same whole-feature-key convention as "reports") rather than a column of its own, so it needs no schema change; default_page_id can't hold it anyway since that column is a foreign key to pages. See app/schemas/identity.py for the validation.""" ids = (self.action_matrix or {}).get("default_dashboard") or [] try: return uuid.UUID(str(ids[0])) if ids else None except ValueError: return None class UserRole(Base): __tablename__ = "user_roles" user_id: Mapped[uuid.UUID] = mapped_column(PGUUID(as_uuid=True), ForeignKey("users.id", ondelete="CASCADE"), primary_key=True) role_id: Mapped[uuid.UUID] = mapped_column(PGUUID(as_uuid=True), ForeignKey("roles.id"), primary_key=True) class LicenseFile(UUIDPKMixin, TenantScopedMixin, Base): """A license file the Super Admin issued for the tenant (app/crud/license_files.py) — single-use, revocable.""" __tablename__ = "license_files" max_users: Mapped[int | None] = mapped_column(Integer) max_portal_users: Mapped[int | None] = mapped_column(Integer) starts_on: Mapped[date | None] = mapped_column(Date) ends_on: Mapped[date | None] = mapped_column(Date) issued_at: Mapped[datetime] = mapped_column(TIMESTAMP(timezone=True), nullable=False, server_default=func.now()) issued_by: Mapped[uuid.UUID | None] = mapped_column(PGUUID(as_uuid=True), ForeignKey("platform_admins.id", ondelete="SET NULL")) applied_at: Mapped[datetime | None] = mapped_column(TIMESTAMP(timezone=True)) applied_by: Mapped[uuid.UUID | None] = mapped_column(PGUUID(as_uuid=True), ForeignKey("users.id", ondelete="SET NULL")) revoked_at: Mapped[datetime | None] = mapped_column(TIMESTAMP(timezone=True)) @property def license(self) -> dict: return {"max_users": self.max_users, "max_portal_users": self.max_portal_users, "starts_on": self.starts_on, "ends_on": self.ends_on} class LicenseRequest(UUIDPKMixin, TenantScopedMixin, Base): """A tenant administrator's request for a renewal or more seats (app/crud/license_requests.py), answered by the Super Admin.""" __tablename__ = "license_requests" requested_by: Mapped[uuid.UUID | None] = mapped_column(PGUUID(as_uuid=True), ForeignKey("users.id", ondelete="SET NULL")) max_users: Mapped[int | None] = mapped_column(Integer) max_portal_users: Mapped[int | None] = mapped_column(Integer) ends_on: Mapped[date | None] = mapped_column(Date) message: Mapped[str] = mapped_column(String(2000), nullable=False, server_default="") status: Mapped[str] = mapped_column(String(20), nullable=False, server_default="open") created_at: Mapped[datetime] = mapped_column(TIMESTAMP(timezone=True), nullable=False, server_default=func.now()) resolved_at: Mapped[datetime | None] = mapped_column(TIMESTAMP(timezone=True)) resolved_by: Mapped[uuid.UUID | None] = mapped_column(PGUUID(as_uuid=True), ForeignKey("platform_admins.id", ondelete="SET NULL")) response: Mapped[str | None] = mapped_column(String(2000)) license_file_id: Mapped[uuid.UUID | None] = mapped_column(PGUUID(as_uuid=True), ForeignKey("license_files.id", ondelete="SET NULL")) class UserSession(UUIDPKMixin, TenantScopedMixin, Base): """One sign-in (app/crud/sessions.py); its token carries this id.""" __tablename__ = "user_sessions" user_id: Mapped[uuid.UUID] = mapped_column(PGUUID(as_uuid=True), ForeignKey("users.id", ondelete="CASCADE"), nullable=False) created_at: Mapped[datetime] = mapped_column(TIMESTAMP(timezone=True), nullable=False, server_default=func.now()) last_seen_at: Mapped[datetime] = mapped_column(TIMESTAMP(timezone=True), nullable=False, server_default=func.now()) expires_at: Mapped[datetime] = mapped_column(TIMESTAMP(timezone=True), nullable=False) ip: Mapped[str | None] = mapped_column(String(64)) user_agent: Mapped[str | None] = mapped_column(String(400)) amr: Mapped[list] = mapped_column(JSONB, nullable=False, server_default="[]") revoked_at: Mapped[datetime | None] = mapped_column(TIMESTAMP(timezone=True))