"""Database models. The shape here is the whole point of the rebuild. The previous system had no server-side notion of a run: every pipeline step was a separate HTTP call and the browser carried the intermediate state, so closing a tab lost the work. Here a Run owns its StepRuns, each StepRun stores its own input and output, and a failed run resumes from the first step that did not succeed. """ from __future__ import annotations import enum import uuid from datetime import date, datetime from sqlalchemy import ( JSON, BigInteger, Boolean, Date, DateTime, Enum, Float, ForeignKey, Index, Integer, MetaData, String, Text, UniqueConstraint, Uuid, func, ) from sqlalchemy.dialects.postgresql import JSONB from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship # Postgres is the deployment target, but the test suite runs on SQLite so the # pipeline engine can be exercised without standing up a database. These two # aliases are the only places that difference shows up. JSONType = JSON().with_variant(JSONB, "postgresql") UUIDType = Uuid(as_uuid=True) # Deterministic constraint names. SQLite cannot alter a table in place, so # Alembic rewrites it - and it can only do that when every constraint it has # to recreate has a name it can refer to. NAMING = { "ix": "ix_%(table_name)s_%(column_0_N_name)s", "uq": "uq_%(table_name)s_%(column_0_N_name)s", "ck": "ck_%(table_name)s_%(constraint_name)s", "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s", "pk": "pk_%(table_name)s", } class Base(DeclarativeBase): metadata = MetaData(naming_convention=NAMING) def _uuid_pk() -> Mapped[uuid.UUID]: return mapped_column(UUIDType, primary_key=True, default=uuid.uuid4) def _created() -> Mapped[datetime]: return mapped_column(DateTime(timezone=True), server_default=func.now(), nullable=False) # --------------------------------------------------------------------- enums class UserRole(str, enum.Enum): user = "user" admin = "admin" class UserStatus(str, enum.Enum): # Signed up, waiting for an administrator to approve them and choose which # tools they get. This is the state every new account starts in. pending = "pending" active = "active" # Temporarily stopped. Keeps the account and its history. suspended = "suspended" # Turned off for good, without deleting the work they produced. disabled = "disabled" invited = "invited" class Department(str, enum.Enum): """Which part of the preparation team a person belongs to. Their department is what they say about themselves at sign-up. It is not what they are allowed to use - an administrator grants that separately, because someone in the books team may still need to build a deck. """ books = "books" bags = "bags" powerpoint = "powerpoint" interactive = "interactive" # Not a production team. The people who run the platform rather than build # on it, and who are usually the ones granted the dashboard - though the # two are still separate: the department is what someone says about # themselves, the role is what an administrator decides. management = "management" # The two companies. Sign-up offers these and «management» only; the four # team departments above stay for the accounts that already hold them. roya = "roya" contiva = "contiva" DEPARTMENT_LABELS_AR: dict[str, str] = { "books": "الكتب", "bags": "الحقائب", "powerpoint": "الباوربوينت", "interactive": "المحتوى التفاعلي", "management": "الإدارة", "roya": "رؤية", "contiva": "كونتيفا", } class RunStatus(str, enum.Enum): queued = "queued" running = "running" # Stopped at a gate step and waiting for a person. The run is healthy; it # holds here until the blueprint is approved, then finishes in one pass. awaiting_review = "awaiting_review" succeeded = "succeeded" failed = "failed" cancelled = "cancelled" class StepStatus(str, enum.Enum): pending = "pending" running = "running" # Produced its output, which a person still has to accept or edit. awaiting_review = "awaiting_review" succeeded = "succeeded" failed = "failed" skipped = "skipped" class DocStatus(str, enum.Enum): pending = "pending" processed = "processed" failed = "failed" # ------------------------------------------------------------------ identity class User(Base): """Mirrors ``auth.users``. Supabase owns credentials; this owns everything the product needs to know about a person.""" __tablename__ = "users" id: Mapped[uuid.UUID] = mapped_column(UUIDType, primary_key=True) email: Mapped[str] = mapped_column(String(320), unique=True, nullable=False) full_name: Mapped[str | None] = mapped_column(String(200)) role: Mapped[UserRole] = mapped_column( Enum(UserRole, name="user_role"), default=UserRole.user, nullable=False ) status: Mapped[UserStatus] = mapped_column( Enum(UserStatus, name="user_status"), default=UserStatus.pending, nullable=False ) # What the person said they do. Stated at sign-up, editable by an admin. department: Mapped[Department | None] = mapped_column( Enum(Department, name="department") ) # Which task types this person may use, chosen by the admin who approved # them. Empty means none - an approved account with no tools can sign in # and see that it is waiting for tools rather than a blank grid. allowed_task_types: Mapped[list] = mapped_column( JSONType, default=list, nullable=False ) # The designers the founder has opened to this account («bag», «slides»). # Only the founder sets it (admin.set_design_access); nobody else sees it. design_access: Mapped[list] = mapped_column( JSONType, default=list, nullable=False, server_default="[]" ) approved_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True)) approved_by: Mapped[uuid.UUID | None] = mapped_column(UUIDType) # Set for accounts carried over from Firebase, whose password hashes could # not come with them. Used to drive the reset-invite flow. legacy_firebase_uid: Mapped[str | None] = mapped_column(String(128), index=True) created_at: Mapped[datetime] = _created() last_seen_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True)) # passive_deletes hands the cascade to the database, which the column # already declares. Without it SQLAlchemy loads every project and nulls # its owner_id - a NOT NULL column - so deleting an account failed with an # integrity error instead of taking their work with it. projects: Mapped[list[Project]] = relationship( back_populates="owner", cascade="all, delete-orphan", passive_deletes=True ) # ---------------------------------------------------------------------- work class Project(Base): """A book, a course, or whatever a future pipeline produces. ``kind`` is a plain string, not an enum, so registering a new pipeline never needs a database migration. """ __tablename__ = "projects" id: Mapped[uuid.UUID] = _uuid_pk() owner_id: Mapped[uuid.UUID] = mapped_column( ForeignKey("users.id", ondelete="CASCADE"), nullable=False, index=True ) kind: Mapped[str] = mapped_column(String(64), nullable=False, index=True) title: Mapped[str] = mapped_column(String(500), nullable=False) description: Mapped[str | None] = mapped_column(Text) language: Mapped[str] = mapped_column(String(8), default="ar", nullable=False) meta: Mapped[dict] = mapped_column(JSONType, default=dict, nullable=False) archived: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False) created_at: Mapped[datetime] = _created() updated_at: Mapped[datetime] = mapped_column( DateTime(timezone=True), server_default=func.now(), onupdate=func.now(), nullable=False ) owner: Mapped[User] = relationship(back_populates="projects") runs: Mapped[list[Run]] = relationship(back_populates="project", cascade="all, delete-orphan") class Run(Base): __tablename__ = "runs" id: Mapped[uuid.UUID] = _uuid_pk() project_id: Mapped[uuid.UUID] = mapped_column( ForeignKey("projects.id", ondelete="CASCADE"), nullable=False, index=True ) pipeline_key: Mapped[str] = mapped_column(String(64), nullable=False) # A profile is copied into the run rather than referenced, so editing a # profile later never rewrites what a finished run was made from. profile_id: Mapped[uuid.UUID | None] = mapped_column( ForeignKey("profiles.id", ondelete="SET NULL") ) profile_snapshot: Mapped[dict | None] = mapped_column(JSONType) status: Mapped[RunStatus] = mapped_column( Enum(RunStatus, name="run_status"), default=RunStatus.queued, nullable=False, index=True ) current_step: Mapped[str | None] = mapped_column(String(128)) # Whatever the user supplied when starting the run. params: Mapped[dict] = mapped_column(JSONType, default=dict, nullable=False) error: Mapped[str | None] = mapped_column(Text) created_by: Mapped[uuid.UUID] = mapped_column( ForeignKey("users.id", ondelete="SET NULL"), nullable=True, index=True ) queued_at: Mapped[datetime] = _created() started_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True)) finished_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True)) project: Mapped[Project] = relationship(back_populates="runs") steps: Mapped[list[StepRun]] = relationship( back_populates="run", cascade="all, delete-orphan", order_by="StepRun.position" ) class StepRun(Base): """One step of one run. ``output`` is what makes resume possible: a rerun replays only the steps that have not succeeded and feeds them the stored outputs of the ones that have. """ __tablename__ = "step_runs" __table_args__ = ( UniqueConstraint("run_id", "step_key", name="uq_step_runs_run_step"), Index("ix_step_runs_run_position", "run_id", "position"), ) id: Mapped[uuid.UUID] = _uuid_pk() run_id: Mapped[uuid.UUID] = mapped_column( ForeignKey("runs.id", ondelete="CASCADE"), nullable=False ) step_key: Mapped[str] = mapped_column(String(128), nullable=False) position: Mapped[int] = mapped_column(Integer, nullable=False) status: Mapped[StepStatus] = mapped_column( Enum(StepStatus, name="step_status"), default=StepStatus.pending, nullable=False ) input: Mapped[dict | None] = mapped_column(JSONType) output: Mapped[dict | None] = mapped_column(JSONType) error: Mapped[str | None] = mapped_column(Text) attempt: Mapped[int] = mapped_column(Integer, default=0, nullable=False) # What the step finished before an attempt ended - written as each item # completes, read back by the next attempt, cleared on success. Without # it a step that stopped halfway was paid for twice: fifty written محاور # discarded when a key limit ended the step, and written again on resume. checkpoint: Mapped[dict | None] = mapped_column(JSONType) started_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True)) finished_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True)) run: Mapped[Run] = relationship(back_populates="steps") class Artifact(Base): __tablename__ = "artifacts" id: Mapped[uuid.UUID] = _uuid_pk() project_id: Mapped[uuid.UUID] = mapped_column( ForeignKey("projects.id", ondelete="CASCADE"), nullable=False, index=True ) run_id: Mapped[uuid.UUID | None] = mapped_column(ForeignKey("runs.id", ondelete="SET NULL")) kind: Mapped[str] = mapped_column(String(64), nullable=False) fmt: Mapped[str] = mapped_column("format", String(16), nullable=False) title: Mapped[str | None] = mapped_column(String(500)) # Small renders live inline; anything large goes to Supabase Storage. content: Mapped[str | None] = mapped_column(Text) storage_path: Mapped[str | None] = mapped_column(String(1000)) size_bytes: Mapped[int | None] = mapped_column(BigInteger) created_at: Mapped[datetime] = _created() # Who made it. Recorded for the designs (api/v1/design.py), which only # their maker and the founder see; empty for everything older. created_by: Mapped[uuid.UUID | None] = mapped_column(UUIDType) # ----------------------------------------------------------------- knowledge class Document(Base): """Carried over from ``documents_raw``.""" __tablename__ = "documents" id: Mapped[uuid.UUID] = _uuid_pk() project_id: Mapped[uuid.UUID | None] = mapped_column( ForeignKey("projects.id", ondelete="CASCADE"), index=True ) owner_id: Mapped[uuid.UUID | None] = mapped_column( ForeignKey("users.id", ondelete="SET NULL") ) # Free-text ids from the old system, kept so migrated rows stay traceable. legacy_user_id: Mapped[str | None] = mapped_column(String(200), index=True) legacy_book_id: Mapped[str | None] = mapped_column(String(500), index=True) source_url: Mapped[str] = mapped_column(Text, nullable=False) source_type: Mapped[str] = mapped_column(String(32), default="pdf", nullable=False) domain: Mapped[str] = mapped_column(String(255), default="", nullable=False) language: Mapped[str] = mapped_column(String(8), default="ar", nullable=False) pages_head: Mapped[list | None] = mapped_column(JSONType) extraction_method: Mapped[str] = mapped_column(String(16), default="text", nullable=False) pdf_size_bytes: Mapped[int | None] = mapped_column(BigInteger) status: Mapped[DocStatus] = mapped_column( Enum(DocStatus, name="doc_status"), default=DocStatus.pending, nullable=False, index=True ) error_reason: Mapped[str] = mapped_column(Text, default="", nullable=False) created_at: Mapped[datetime] = _created() class DocumentMetadata(Base): """Carried over from ``documents_metadata``. ``authors`` is text, not an array - that is how the source column is typed, and the migration keeps it that way rather than guessing at a delimiter. """ __tablename__ = "document_metadata" document_id: Mapped[uuid.UUID] = mapped_column( ForeignKey("documents.id", ondelete="CASCADE"), primary_key=True ) title: Mapped[str] = mapped_column(Text, nullable=False) authors: Mapped[str] = mapped_column(Text, default="", nullable=False) year: Mapped[int | None] = mapped_column(Integer) publisher_or_journal: Mapped[str] = mapped_column(Text, default="", nullable=False) normalized_source_type: Mapped[str] = mapped_column(String(64), default="pdf", nullable=False) apa7: Mapped[str] = mapped_column(Text, default="", nullable=False) meta: Mapped[dict] = mapped_column("metadata", JSONType, default=dict, nullable=False) created_at: Mapped[datetime] = _created() class Collection(Base): """Points a project at its Qdrant collection. The vectors are not moved by the migration - 149,860 of them already live in Qdrant under names like ``user_ziad__book_``. This table just records which project owns which collection. """ __tablename__ = "collections" __table_args__ = (UniqueConstraint("qdrant_collection", name="uq_collections_qdrant"),) id: Mapped[uuid.UUID] = _uuid_pk() project_id: Mapped[uuid.UUID | None] = mapped_column( ForeignKey("projects.id", ondelete="CASCADE"), index=True ) qdrant_collection: Mapped[str] = mapped_column(String(500), nullable=False) vector_count: Mapped[int] = mapped_column(Integer, default=0, nullable=False) embedding_model: Mapped[str] = mapped_column( String(200), default="sentence-transformers/paraphrase-multilingual-MiniLM-L12-v2", nullable=False, ) vector_size: Mapped[int] = mapped_column(Integer, default=384, nullable=False) synced_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True)) created_at: Mapped[datetime] = _created() # ------------------------------------------------------------- cost & audit class LlmCall(Base): """One row per LLM request, written by the LiteLLM metering callback. This is the only place spend is recorded, and no agent knows it exists - the callback sits under LiteLLM, so every call from every agent is caught whether or not the agent was written with metering in mind. """ __tablename__ = "llm_calls" __table_args__ = ( Index("ix_llm_calls_user_created", "user_id", "created_at"), Index("ix_llm_calls_run", "run_id"), ) id: Mapped[uuid.UUID] = _uuid_pk() run_id: Mapped[uuid.UUID | None] = mapped_column(ForeignKey("runs.id", ondelete="CASCADE")) step_run_id: Mapped[uuid.UUID | None] = mapped_column( ForeignKey("step_runs.id", ondelete="CASCADE") ) user_id: Mapped[uuid.UUID | None] = mapped_column( ForeignKey("users.id", ondelete="SET NULL") ) pipeline_key: Mapped[str | None] = mapped_column(String(64)) step_key: Mapped[str | None] = mapped_column(String(128)) model: Mapped[str] = mapped_column(String(200), nullable=False, index=True) prompt_tokens: Mapped[int] = mapped_column(Integer, default=0, nullable=False) completion_tokens: Mapped[int] = mapped_column(Integer, default=0, nullable=False) total_tokens: Mapped[int] = mapped_column(Integer, default=0, nullable=False) # Nullable on purpose. A model the price tables do not know used to be # recorded at zero, which is indistinguishable from a genuinely free route # - so a whole tier of a bag's calls appeared free and nothing questioned # it. NULL says "unknown", zero says "free", and only one of them is ever # a fact. `pricing_missing` makes the absence countable rather than # something a SUM silently skips. cost_usd: Mapped[float | None] = mapped_column(Float, default=0.0) pricing_missing: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False) latency_ms: Mapped[int | None] = mapped_column(Integer) success: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False) error: Mapped[str | None] = mapped_column(Text) created_at: Mapped[datetime] = _created() class UsageDaily(Base): """Rollup of ``llm_calls``. The dashboard charts read this, not the raw table, so a year of history stays cheap to query.""" __tablename__ = "usage_daily" __table_args__ = (UniqueConstraint("user_id", "day", name="uq_usage_daily_user_day"),) id: Mapped[uuid.UUID] = _uuid_pk() user_id: Mapped[uuid.UUID] = mapped_column( ForeignKey("users.id", ondelete="CASCADE"), nullable=False, index=True ) day: Mapped[date] = mapped_column(Date, nullable=False, index=True) total_tokens: Mapped[int] = mapped_column(BigInteger, default=0, nullable=False) prompt_tokens: Mapped[int] = mapped_column(BigInteger, default=0, nullable=False) completion_tokens: Mapped[int] = mapped_column(BigInteger, default=0, nullable=False) cost_usd: Mapped[float] = mapped_column(Float, default=0.0, nullable=False) runs_count: Mapped[int] = mapped_column(Integer, default=0, nullable=False) calls_count: Mapped[int] = mapped_column(Integer, default=0, nullable=False) class AuditLog(Base): __tablename__ = "audit_log" id: Mapped[uuid.UUID] = _uuid_pk() actor_id: Mapped[uuid.UUID | None] = mapped_column( ForeignKey("users.id", ondelete="SET NULL") ) action: Mapped[str] = mapped_column(String(100), nullable=False, index=True) target: Mapped[str | None] = mapped_column(String(500)) meta: Mapped[dict] = mapped_column(JSONType, default=dict, nullable=False) created_at: Mapped[datetime] = _created() class Upload(Base): """A file the user supplied as source material. The book pipeline can be seeded from existing PDFs - a technical outline, a previous edition - instead of a topic. Those files land here and the ingest step OCRs them. """ __tablename__ = "uploads" id: Mapped[uuid.UUID] = _uuid_pk() project_id: Mapped[uuid.UUID] = mapped_column( ForeignKey("projects.id", ondelete="CASCADE"), nullable=False, index=True ) owner_id: Mapped[uuid.UUID | None] = mapped_column( ForeignKey("users.id", ondelete="SET NULL") ) filename: Mapped[str] = mapped_column(String(500), nullable=False) content_type: Mapped[str] = mapped_column(String(120), default="", nullable=False) size_bytes: Mapped[int] = mapped_column(BigInteger, default=0, nullable=False) storage_path: Mapped[str] = mapped_column(String(1000), nullable=False) # Text pulled out of the file, cached so a resumed run does not pay for OCR twice. extracted_text: Mapped[str | None] = mapped_column(Text) # Read at upload time (services/sources): pending → reading → ready | failed, # the text page by page (citations name pages), and how it was read. status: Mapped[str] = mapped_column(String(16), default="pending", nullable=False) pages: Mapped[list | None] = mapped_column(JSONType) extraction: Mapped[dict | None] = mapped_column(JSONType) error: Mapped[str | None] = mapped_column(Text) created_at: Mapped[datetime] = _created() # ------------------------------------------------------------ saved context class Profile(Base): """Context a user saves once and reuses on every task. Without this, someone producing a series for the same institution retypes the audience, the register and the house rules on every single run. The shape is not fixed here: ``data`` holds whatever the profile schema asks for, so the fields can change without a migration. """ __tablename__ = "profiles" id: Mapped[uuid.UUID] = _uuid_pk() owner_id: Mapped[uuid.UUID] = mapped_column( ForeignKey("users.id", ondelete="CASCADE"), nullable=False, index=True ) name: Mapped[str] = mapped_column(String(200), nullable=False) summary: Mapped[str] = mapped_column(String(500), default="", nullable=False) data: Mapped[dict] = mapped_column(JSONType, default=dict, nullable=False) is_default: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False) archived: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False) created_at: Mapped[datetime] = _created() updated_at: Mapped[datetime] = mapped_column( DateTime(timezone=True), server_default=func.now(), onupdate=func.now(), nullable=False ) # ----------------------------------------------------------- plans & limits class TaskTypeSetting(Base): """Operational control over a task type. The pipeline itself is code. This row is the part an administrator may change at runtime: whether it is offered, in what order, to which plans, and what to tell people when it is switched off. """ __tablename__ = "task_type_settings" key: Mapped[str] = mapped_column(String(64), primary_key=True) enabled: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False) disabled_reason: Mapped[str] = mapped_column(String(500), default="", nullable=False) sort_order: Mapped[int] = mapped_column(Integer, default=0, nullable=False) cost_hint_usd: Mapped[float | None] = mapped_column(Float) estimated_seconds: Mapped[int | None] = mapped_column(Integer) updated_at: Mapped[datetime] = mapped_column( DateTime(timezone=True), server_default=func.now(), onupdate=func.now(), nullable=False ) class BrandKit(Base): """A brand saved in the design studio: logo, colours, fonts and style. «حفظ Brand Kit لاستخدامه بعدين». ``data`` holds the choices (colours, font, style, logo spot and size); the logo itself is kept inline, small by rule (the studio refuses logos over 2 MB). """ __tablename__ = "brand_kits" id: Mapped[uuid.UUID] = _uuid_pk() owner_id: Mapped[uuid.UUID] = mapped_column( ForeignKey("users.id", ondelete="CASCADE"), nullable=False, index=True ) name: Mapped[str] = mapped_column(String(120), nullable=False) data: Mapped[dict] = mapped_column(JSONType, default=dict, nullable=False) logo: Mapped[str | None] = mapped_column(Text) # base64 logo_type: Mapped[str] = mapped_column(String(40), default="", nullable=False) created_at: Mapped[datetime] = _created() updated_at: Mapped[datetime] = mapped_column( DateTime(timezone=True), server_default=func.now(), onupdate=func.now(), nullable=False ) class Announcement(Base): """A banner shown to everyone or to one segment.""" __tablename__ = "announcements" id: Mapped[uuid.UUID] = _uuid_pk() body: Mapped[str] = mapped_column(Text, nullable=False) kind: Mapped[str] = mapped_column(String(16), default="info", nullable=False) audience: Mapped[str] = mapped_column(String(32), default="all", nullable=False) starts_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True)) ends_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True)) published: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False) created_by: Mapped[uuid.UUID | None] = mapped_column( ForeignKey("users.id", ondelete="SET NULL") ) created_at: Mapped[datetime] = _created()