contiai / apps /api /app /db /models.py
aliabozahra315's picture
Deploy 220ee9c
c7fa722
Raw History Blame Contribute Delete
25.7 kB
"""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_<title>``. 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()