"""AI Labs tables. Five of them, and the split between the first three and the last two is the privacy design rather than a normalisation choice: * ``ai_labs_conversations`` / ``ai_labs_messages`` / ``ai_labs_generations`` hold **employee content**. They are readable only by the person who owns the row, they are **hard**-deleted on request (no ``deleted_at``: a soft delete would leave the text in the database after Helix told the employee it was gone), and the cascade takes the child messages with the parent. * ``ai_labs_usage_daily`` and ``ai_labs_failures_daily`` hold **counters only** — no prompt, no reply, no object key. They are what the admin console and the capacity story read, and they are deliberately separate tables so that answering "how much is AI Labs being used?" and "what is failing?" never requires a query that could return content, and so the numbers survive an employee deleting their history. The failures table's one non-numeric column is an error *code*, normalised on write so vendor prose (which can quote a prompt back) cannot land in it. UUID convention: hyphenated ``CHAR(36)`` via :class:`app.core.sql_types.GUID`, matching ``identity_persons`` and every other domain. A bare ``Mapped[uuid.UUID]`` would resolve to SQLAlchemy's stock ``Uuid`` (32 chars, dashes stripped on MySQL/TiDB) and any cross-table string join on ``person_id`` would silently match zero rows. """ from __future__ import annotations import uuid from datetime import UTC, datetime from sqlalchemy import ( JSON, Boolean, Date, DateTime, ForeignKey, Index, Integer, String, Text, UniqueConstraint, func, ) from sqlalchemy.orm import Mapped, mapped_column, relationship from app.core.database import Base from app.core.sql_types import GUID as Uuid def _utcnow() -> datetime: """Python-side timestamp default. Needed alongside ``server_default`` because every endpoint here serialises the row it just created: a column that is only server-defaulted is expired after flush, and reading it during serialisation triggers a lazy reload outside the async greenlet (``MissingGreenlet``). """ return datetime.now(UTC) # ── conversations ────────────────────────────────────────────── class AiLabConversation(Base): """One chat thread, owned by exactly one person.""" __tablename__ = "ai_labs_conversations" __table_args__ = ( # The list query is always "my threads, newest activity first". Index("ix_alc_person_activity", "person_id", "last_message_at"), Index("ix_alc_person_created", "person_id", "created_at"), ) id: Mapped[uuid.UUID] = mapped_column( Uuid, primary_key=True, default=uuid.uuid4, ) person_id: Mapped[uuid.UUID] = mapped_column( Uuid, ForeignKey("identity_persons.id"), nullable=False, ) #: Auto-filled from the first user message, renameable by the owner. title: Mapped[str] = mapped_column(String(200), nullable=False, default="") #: Catalogue key (NOT the vendor model id) — the catalogue can re-point a #: key at a newer vendor model without rewriting history. model_key: Mapped[str] = mapped_column(String(64), nullable=False) thinking_enabled: Mapped[bool] = mapped_column( Boolean, nullable=False, default=False, server_default="0", ) pinned: Mapped[bool] = mapped_column( Boolean, nullable=False, default=False, server_default="0", ) message_count: Mapped[int] = mapped_column( Integer, nullable=False, default=0, server_default="0", ) last_message_at: Mapped[datetime | None] = mapped_column( DateTime(timezone=True), ) created_at: Mapped[datetime] = mapped_column( DateTime(timezone=True), nullable=False, default=_utcnow, server_default=func.now(), ) updated_at: Mapped[datetime] = mapped_column( DateTime(timezone=True), nullable=False, default=_utcnow, onupdate=_utcnow, server_default=func.now(), ) messages: Mapped[list[AiLabMessage]] = relationship( back_populates="conversation", cascade="all, delete-orphan", order_by="AiLabMessage.seq", ) class AiLabMessage(Base): """One turn in a thread. ``content`` is the employee's text, verbatim.""" __tablename__ = "ai_labs_messages" __table_args__ = ( # Ordering within a thread, and the guard against the *concurrent* # double-send: two submits racing for the same seq cannot both win. # It is **not** what makes turn creation idempotent — a sequential # retry reads a fresh ``message_count``, computes the next seq and # is accepted. ``uq_alm_conv_client`` is the idempotency guard. UniqueConstraint("conversation_id", "seq", name="uq_alm_conv_seq"), # One client-supplied token per thread: a retried send (lost 200, # a double tap, an axios retry) collapses onto the turn it is # retrying instead of paying the vendor twice. UniqueConstraint( "conversation_id", "client_token", name="uq_alm_conv_client", ), ) id: Mapped[uuid.UUID] = mapped_column( Uuid, primary_key=True, default=uuid.uuid4, ) conversation_id: Mapped[uuid.UUID] = mapped_column( Uuid, ForeignKey("ai_labs_conversations.id", ondelete="CASCADE"), nullable=False, ) seq: Mapped[int] = mapped_column(Integer, nullable=False) #: ``user`` | ``assistant``. No ``system`` row exists by construction — #: this domain injects no system prompt (see the package docstring). role: Mapped[str] = mapped_column(String(16), nullable=False) content: Mapped[str] = mapped_column(Text, nullable=False, default="") #: The model's own thinking, when it emitted any. Stored separately so #: the UI can collapse it and so it never merges into the answer text. reasoning: Mapped[str | None] = mapped_column(Text) #: ``[{"object_key", "content_type", "size_bytes", "filename", "kind"}]`` #: where ``kind`` is ``image`` or ``document``. Only S3 keys — never a URL. attachments: Mapped[list[dict] | None] = mapped_column( JSON(none_as_null=True), ) #: Vendor model actually called, recorded per message: an employee can #: switch models mid-thread and "which model said this" must stay true. model: Mapped[str | None] = mapped_column(String(64)) input_tokens: Mapped[int | None] = mapped_column(Integer) output_tokens: Mapped[int | None] = mapped_column(Integer) latency_ms: Mapped[int | None] = mapped_column(Integer) finish_reason: Mapped[str | None] = mapped_column(String(32)) #: Set when the turn failed. The row is still written, so the thread #: shows what happened instead of silently losing the question. error_code: Mapped[str | None] = mapped_column(String(64)) #: Client-generated per-send token, on the ``user`` row only. Nullable #: so a caller that sends none behaves exactly as before — the #: uniqueness guard is scoped to the thread, and MySQL treats NULLs as #: distinct, so unlabelled turns never collide with each other. client_token: Mapped[str | None] = mapped_column(String(128)) created_at: Mapped[datetime] = mapped_column( DateTime(timezone=True), nullable=False, default=_utcnow, server_default=func.now(), ) conversation: Mapped[AiLabConversation] = relationship( back_populates="messages", ) # ── generations ──────────────────────────────────────────────── #: Job lifecycle. ``queued`` is durable — the row exists before any vendor #: call, so a crash between "employee pressed generate" and "vendor accepted" #: leaves a row the reaper can retry rather than a lost click. GEN_QUEUED = "queued" GEN_RUNNING = "running" GEN_SUCCEEDED = "succeeded" GEN_FAILED = "failed" GEN_CANCELLED = "cancelled" GEN_TERMINAL_STATUSES = frozenset({GEN_SUCCEEDED, GEN_FAILED, GEN_CANCELLED}) class AiLabGeneration(Base): """One media-generation job: image, image edit, video, speech, transcript.""" __tablename__ = "ai_labs_generations" __table_args__ = ( Index("ix_alg_person_created", "person_id", "created_at"), Index("ix_alg_person_capability", "person_id", "capability"), # The reaper's sweep: "unfinished jobs, oldest first". Index("ix_alg_status_created", "status", "created_at"), Index("ix_alg_vendor_task", "vendor_task_id"), ) id: Mapped[uuid.UUID] = mapped_column( Uuid, primary_key=True, default=uuid.uuid4, ) person_id: Mapped[uuid.UUID] = mapped_column( Uuid, ForeignKey("identity_persons.id"), nullable=False, ) #: Catalogue capability key: ``image`` / ``image_edit`` / ``video`` / #: ``speech`` / ``transcribe``. capability: Mapped[str] = mapped_column(String(32), nullable=False) #: Vendor model id, resolved at submit time and frozen on the row. model: Mapped[str] = mapped_column(String(64), nullable=False) status: Mapped[str] = mapped_column( String(16), nullable=False, default=GEN_QUEUED, ) #: The employee's own prompt, verbatim and un-rewritten. Empty for #: capabilities that take no prompt (transcription). prompt: Mapped[str] = mapped_column(Text, nullable=False, default="") #: Validated, catalogue-bounded option values (size, duration, voice…). params: Mapped[dict | None] = mapped_column(JSON(none_as_null=True)) #: S3 keys of employee-supplied inputs (source images, audio to #: transcribe). Uploaded through the platform presign flow, so ownership #: is verifiable from the key itself. input_object_keys: Mapped[list[str] | None] = mapped_column( JSON(none_as_null=True), ) #: ``[{"object_key", "content_type", "size_bytes"}]`` after the artifact #: has been re-homed into Helix's bucket. A vendor URL is never stored. artifacts: Mapped[list[dict] | None] = mapped_column( JSON(none_as_null=True), ) #: Text output, for transcription. output_text: Mapped[str | None] = mapped_column(Text) vendor_task_id: Mapped[str | None] = mapped_column(String(128)) error_code: Mapped[str | None] = mapped_column(String(64)) error_message: Mapped[str | None] = mapped_column(String(1000)) attempts: Mapped[int] = mapped_column( Integer, nullable=False, default=0, server_default="0", ) #: Cooperative lease, so the request-path executor and the reaper cron #: cannot both drive the same job. Claiming is a conditional UPDATE on #: ``lease_expires_at``; the token proves who holds it. lease_owner: Mapped[str | None] = mapped_column(String(64)) lease_expires_at: Mapped[datetime | None] = mapped_column( DateTime(timezone=True), ) started_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True)) finished_at: Mapped[datetime | None] = mapped_column( DateTime(timezone=True), ) last_polled_at: Mapped[datetime | None] = mapped_column( DateTime(timezone=True), ) created_at: Mapped[datetime] = mapped_column( DateTime(timezone=True), nullable=False, default=_utcnow, server_default=func.now(), ) updated_at: Mapped[datetime] = mapped_column( DateTime(timezone=True), nullable=False, default=_utcnow, onupdate=_utcnow, server_default=func.now(), ) # ── usage counters (content-free) ────────────────────────────── class AiLabUsageDaily(Base): """Per-(person x day x capability x model) counters. No content, ever. Every admin-visible number in this domain comes from here. Two consequences worth stating out loud: * an employee deleting their history does not distort the platform's own usage picture, because the counters were never derived from the rows they deleted; * there is no query an admin can run that returns a prompt, because the table they are allowed to read has no column that could hold one. """ __tablename__ = "ai_labs_usage_daily" __table_args__ = ( UniqueConstraint( "person_id", "usage_date", "capability", "model", name="uq_alud_person_date_cap_model", ), Index("ix_alud_date", "usage_date"), Index("ix_alud_person_date", "person_id", "usage_date"), ) id: Mapped[uuid.UUID] = mapped_column( Uuid, primary_key=True, default=uuid.uuid4, ) person_id: Mapped[uuid.UUID] = mapped_column(Uuid, nullable=False) #: Local calendar day in the platform's reporting timezone — the quota #: window an employee experiences, not a UTC slice of it. usage_date: Mapped[datetime] = mapped_column(Date, nullable=False) capability: Mapped[str] = mapped_column(String(32), nullable=False) model: Mapped[str] = mapped_column(String(64), nullable=False) requests: Mapped[int] = mapped_column( Integer, nullable=False, default=0, server_default="0", ) failures: Mapped[int] = mapped_column( Integer, nullable=False, default=0, server_default="0", ) input_tokens: Mapped[int] = mapped_column( Integer, nullable=False, default=0, server_default="0", ) output_tokens: Mapped[int] = mapped_column( Integer, nullable=False, default=0, server_default="0", ) #: Images produced, clips produced, audio files produced — whatever the #: capability's unit of output is. Zero for chat. artifacts: Mapped[int] = mapped_column( Integer, nullable=False, default=0, server_default="0", ) #: Seconds of video produced / audio transcribed. The dimension the #: vendor actually bills video on, so the only one worth capping. media_seconds: Mapped[int] = mapped_column( Integer, nullable=False, default=0, server_default="0", ) created_at: Mapped[datetime] = mapped_column( DateTime(timezone=True), nullable=False, default=_utcnow, server_default=func.now(), ) updated_at: Mapped[datetime] = mapped_column( DateTime(timezone=True), nullable=False, default=_utcnow, onupdate=_utcnow, server_default=func.now(), ) class AiLabFailureDaily(Base): """Per-(person x day x capability x model x error code) failure counts. The reason ``failures`` on :class:`AiLabUsageDaily` was not enough: it is a bare integer, so the admin console could say "two turns failed" and nothing more. The error *codes* existed, but only on ``ai_labs_messages.error_code`` and ``ai_labs_generations.error_code`` — rows in the content tables, which an admin must never query and which an employee can hard-delete. Asking "what is failing?" therefore had no answerable form. This table is that form, and it keeps the domain's promise for the same structural reason the counters table does: the only non-numeric column it holds is an error **code**, and :func:`app.domains.ai_labs.usage.normalize_error_code` refuses to store anything that is not code-shaped. That guard is not decoration — a vendor's ``error_code`` reaches us as an arbitrary string (see the ``TASK_FAILED`` branch in ``executor._run_async``), and DashScope is documented to quote the offending prompt back inside content-policy rejections. Without the guard, the one admin-readable surface in this domain would be the place an employee's prompt could surface. Not folded into :class:`AiLabUsageDaily` as a sixth key column on purpose: that table is also the quota gate's read model, and widening its unique key would multiply the rows every quota check sums. """ __tablename__ = "ai_labs_failures_daily" __table_args__ = ( UniqueConstraint( "person_id", "usage_date", "capability", "model", "error_code", name="uq_alfd_person_date_cap_model_code", ), # "What is failing, across everyone, in this window" — the # question the admin console's failure card asks. Index("ix_alfd_date_code", "usage_date", "error_code"), # "What is failing for this person" — the per-person drill. Index("ix_alfd_person_date", "person_id", "usage_date"), ) id: Mapped[uuid.UUID] = mapped_column( Uuid, primary_key=True, default=uuid.uuid4, ) #: Deliberately no FK, matching :class:`AiLabUsageDaily`: a counter row #: outliving the person row is the correct behaviour for a spend #: statistic, and the read path left-joins the directory for a label. person_id: Mapped[uuid.UUID] = mapped_column(Uuid, nullable=False) #: Local calendar day in the reporting timezone — the same calendar #: ``ai_labs_usage_daily.usage_date`` is written in, so the two tables #: can be compared day for day. usage_date: Mapped[datetime] = mapped_column(Date, nullable=False) capability: Mapped[str] = mapped_column(String(32), nullable=False) model: Mapped[str] = mapped_column(String(64), nullable=False) #: Code-shaped only, enforced on write. Never vendor prose. error_code: Mapped[str] = mapped_column(String(64), nullable=False) failures: Mapped[int] = mapped_column( Integer, nullable=False, default=0, server_default="0", ) #: When this (person, day, capability, model, code) last happened. #: Distinguishes "broken since the deploy" from "broke once at 3am". last_seen_at: Mapped[datetime] = mapped_column( DateTime(timezone=True), nullable=False, default=_utcnow, server_default=func.now(), ) created_at: Mapped[datetime] = mapped_column( DateTime(timezone=True), nullable=False, default=_utcnow, server_default=func.now(), ) updated_at: Mapped[datetime] = mapped_column( DateTime(timezone=True), nullable=False, default=_utcnow, onupdate=_utcnow, server_default=func.now(), ) __all__ = [ "GEN_CANCELLED", "GEN_FAILED", "GEN_QUEUED", "GEN_RUNNING", "GEN_SUCCEEDED", "GEN_TERMINAL_STATUSES", "AiLabConversation", "AiLabFailureDaily", "AiLabGeneration", "AiLabMessage", "AiLabUsageDaily", ]