b63f49241d
POST /reprocess?job=summarize&tone=<voice> persists the voice on the job row (queue carries only ids, so a sweep re-enqueue keeps it), the LLM prompt appends 'write every field in a <tone> tone — the tone colors the wording, never the facts', and the resulting summary records its tone (SummaryOut.tone). Plain re-summarize clears a previous tone. Migration tone0000000001 (summaries.tone, processing_jobs.tone). 78 passed, 1 skipped; ruff clean.
466 lines
18 KiB
Python
466 lines
18 KiB
Python
"""All persistent models.
|
|
|
|
Design rules:
|
|
- Every client-visible identifier is a UUID (``PublicIdMixin.id``).
|
|
- Filesystem/storage paths are NEVER exposed to clients.
|
|
- Original uploads are immutable; processed audio lives in separate
|
|
``Asset`` rows and never replaces an original.
|
|
"""
|
|
|
|
from __future__ import annotations
|
|
|
|
import enum
|
|
import uuid
|
|
from datetime import datetime
|
|
|
|
# JSON that renders JSONB on PostgreSQL and plain JSON on SQLite (the
|
|
# desktop bundled-lite engine runs on SQLite; JSONB does not compile there).
|
|
from sqlalchemy import JSON as _JSON
|
|
from sqlalchemy import (
|
|
BigInteger,
|
|
Boolean,
|
|
Enum,
|
|
Float,
|
|
ForeignKey,
|
|
Index,
|
|
String,
|
|
Text,
|
|
UniqueConstraint,
|
|
text,
|
|
)
|
|
from sqlalchemy.dialects.postgresql import JSONB
|
|
from sqlalchemy.orm import Mapped, mapped_column, relationship
|
|
|
|
from shonar.db.base import UTCDT, Base, PublicIdMixin, utcnow
|
|
|
|
JSONType = _JSON().with_variant(JSONB(), "postgresql")
|
|
|
|
|
|
# ---------------------------------------------------------------------------
|
|
# Users & auth
|
|
# ---------------------------------------------------------------------------
|
|
|
|
|
|
class User(Base, PublicIdMixin):
|
|
__tablename__ = "users"
|
|
|
|
email: Mapped[str] = mapped_column(String(320), unique=True, index=True, nullable=False)
|
|
password_hash: Mapped[str] = mapped_column(String(255), nullable=False)
|
|
display_name: Mapped[str | None] = mapped_column(String(120))
|
|
is_active: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False)
|
|
# Feature switches the user controls (mirror of app settings, server truth
|
|
# for e.g. whether location metadata may be stored at all).
|
|
location_storage_enabled: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)
|
|
deleted_at: Mapped[datetime | None] = mapped_column(UTCDT())
|
|
|
|
devices: Mapped[list[Device]] = relationship(back_populates="user")
|
|
recordings: Mapped[list[Recording]] = relationship(back_populates="user")
|
|
|
|
|
|
class Device(Base, PublicIdMixin):
|
|
__tablename__ = "devices"
|
|
|
|
user_id: Mapped[uuid.UUID] = mapped_column(
|
|
ForeignKey("users.id", ondelete="CASCADE"), index=True, nullable=False
|
|
)
|
|
name: Mapped[str] = mapped_column(String(120), nullable=False)
|
|
platform: Mapped[str] = mapped_column(String(40), default="android", nullable=False)
|
|
last_seen_at: Mapped[datetime] = mapped_column(UTCDT(), default=utcnow)
|
|
revoked_at: Mapped[datetime | None] = mapped_column(UTCDT())
|
|
|
|
user: Mapped[User] = relationship(back_populates="devices")
|
|
|
|
|
|
class RefreshToken(Base, PublicIdMixin):
|
|
"""Rotating refresh tokens, stored hashed, grouped into families for
|
|
reuse detection. A refresh consumes one row and issues its replacement
|
|
with the same ``family``."""
|
|
|
|
__tablename__ = "refresh_tokens"
|
|
|
|
user_id: Mapped[uuid.UUID] = mapped_column(
|
|
ForeignKey("users.id", ondelete="CASCADE"), index=True, nullable=False
|
|
)
|
|
token_hash: Mapped[str] = mapped_column(String(128), unique=True, nullable=False)
|
|
family: Mapped[uuid.UUID] = mapped_column(index=True, nullable=False)
|
|
device_id: Mapped[uuid.UUID | None] = mapped_column(
|
|
ForeignKey("devices.id", ondelete="SET NULL")
|
|
)
|
|
expires_at: Mapped[datetime] = mapped_column(UTCDT(), nullable=False)
|
|
revoked_at: Mapped[datetime | None] = mapped_column(UTCDT())
|
|
replaced_by: Mapped[uuid.UUID | None] = mapped_column(
|
|
ForeignKey("refresh_tokens.id", ondelete="SET NULL")
|
|
)
|
|
|
|
|
|
# ---------------------------------------------------------------------------
|
|
# Recordings, assets, uploads
|
|
# ---------------------------------------------------------------------------
|
|
|
|
|
|
class ProcessingStatus(enum.StrEnum):
|
|
pending_upload = "pending_upload"
|
|
uploaded = "uploaded"
|
|
queued = "queued"
|
|
processing = "processing"
|
|
completed = "completed"
|
|
failed = "failed"
|
|
# No AI configured / requested — audio-only recording, fully usable.
|
|
ai_disabled = "ai_disabled"
|
|
|
|
|
|
class Recording(Base, PublicIdMixin):
|
|
__tablename__ = "recordings"
|
|
|
|
user_id: Mapped[uuid.UUID] = mapped_column(
|
|
ForeignKey("users.id", ondelete="CASCADE"), index=True, nullable=False
|
|
)
|
|
device_id: Mapped[uuid.UUID | None] = mapped_column(
|
|
ForeignKey("devices.id", ondelete="SET NULL")
|
|
)
|
|
# Client-generated idempotency id so retried uploads update, not duplicate.
|
|
client_recording_id: Mapped[str | None] = mapped_column(String(64), index=True)
|
|
|
|
title: Mapped[str] = mapped_column(String(300), nullable=False, default="Untitled recording")
|
|
recorded_at: Mapped[datetime] = mapped_column(UTCDT(), nullable=False)
|
|
duration_seconds: Mapped[float] = mapped_column(Float, default=0.0, nullable=False)
|
|
notes: Mapped[str | None] = mapped_column(Text)
|
|
# Location is stored ONLY when the user has explicitly enabled it.
|
|
latitude: Mapped[float | None] = mapped_column(Float)
|
|
longitude: Mapped[float | None] = mapped_column(Float)
|
|
location_accuracy_m: Mapped[float | None] = mapped_column(Float)
|
|
|
|
processing_status: Mapped[ProcessingStatus] = mapped_column(
|
|
Enum(
|
|
ProcessingStatus,
|
|
name="processing_status",
|
|
values_callable=lambda e: [m.value for m in e],
|
|
),
|
|
default=ProcessingStatus.pending_upload,
|
|
nullable=False,
|
|
index=True,
|
|
)
|
|
processing_error: Mapped[str | None] = mapped_column(Text)
|
|
# Effective faster-whisper model for this recording (e.g. "base").
|
|
# Set at finalize time from the per-recording override or the global
|
|
# default; NULL means "server default at processing time" (pre-model
|
|
# rows). Never rewritten: changing the default affects future rows only.
|
|
transcription_model: Mapped[str | None] = mapped_column(String(32))
|
|
# The original audio is the Asset row with kind=original for this
|
|
# recording (at most one, enforced by a partial unique index). Keeping
|
|
# the pointer one-directional avoids a recordings<->assets FK cycle.
|
|
deleted_at: Mapped[datetime | None] = mapped_column(UTCDT())
|
|
|
|
user: Mapped[User] = relationship(back_populates="recordings")
|
|
assets: Mapped[list[Asset]] = relationship(
|
|
back_populates="recording", cascade="all, delete-orphan"
|
|
)
|
|
transcripts: Mapped[list[Transcript]] = relationship(
|
|
back_populates="recording", cascade="all, delete-orphan"
|
|
)
|
|
summaries: Mapped[list[Summary]] = relationship(
|
|
back_populates="recording", cascade="all, delete-orphan"
|
|
)
|
|
tags: Mapped[list[Tag]] = relationship(secondary="recording_tags", back_populates="recordings")
|
|
|
|
__table_args__ = (
|
|
Index("ix_recordings_user_recorded", "user_id", "recorded_at"),
|
|
Index(
|
|
"ix_recordings_user_client_id",
|
|
"user_id",
|
|
"client_recording_id",
|
|
unique=True,
|
|
postgresql_where="client_recording_id IS NOT NULL",
|
|
sqlite_where=text("client_recording_id IS NOT NULL"),
|
|
),
|
|
)
|
|
|
|
|
|
class AssetKind(enum.StrEnum):
|
|
original = "original" # user's uploaded file — NEVER modified
|
|
normalized = "normalized" # derivative for processing (ffmpeg)
|
|
export = "export" # generated export bundle
|
|
|
|
|
|
class Asset(Base, PublicIdMixin):
|
|
"""A stored binary object. ``storage_key`` is server-internal only."""
|
|
|
|
__tablename__ = "assets"
|
|
|
|
recording_id: Mapped[uuid.UUID | None] = mapped_column(
|
|
ForeignKey("recordings.id", ondelete="CASCADE"), index=True
|
|
)
|
|
user_id: Mapped[uuid.UUID] = mapped_column(
|
|
ForeignKey("users.id", ondelete="CASCADE"), index=True, nullable=False
|
|
)
|
|
kind: Mapped[AssetKind] = mapped_column(
|
|
Enum(AssetKind, name="asset_kind", values_callable=lambda e: [m.value for m in e]),
|
|
nullable=False,
|
|
)
|
|
storage_key: Mapped[str] = mapped_column(String(500), nullable=False)
|
|
mime_type: Mapped[str] = mapped_column(String(100), nullable=False)
|
|
size_bytes: Mapped[int] = mapped_column(BigInteger, nullable=False, default=0)
|
|
checksum_sha256: Mapped[str] = mapped_column(String(64), nullable=False)
|
|
|
|
recording: Mapped[Recording | None] = relationship(back_populates="assets")
|
|
|
|
__table_args__ = (
|
|
# At most one immutable "original" per recording.
|
|
# sqlite_where is required as well as postgresql_where: without it
|
|
# SQLite gets a FULL unique index on recording_id (the WHERE clause
|
|
# is not carried over), which wrongly blocks >1 export asset/recording.
|
|
Index(
|
|
"uq_assets_one_original",
|
|
"recording_id",
|
|
unique=True,
|
|
postgresql_where="kind = 'original'",
|
|
sqlite_where=text("kind = 'original'"),
|
|
),
|
|
)
|
|
|
|
|
|
class UploadSessionStatus(enum.StrEnum):
|
|
open = "open"
|
|
finalizing = "finalizing"
|
|
completed = "completed"
|
|
aborted = "aborted"
|
|
expired = "expired"
|
|
|
|
|
|
class UploadSession(Base, PublicIdMixin):
|
|
"""Chunked, resumable upload session."""
|
|
|
|
__tablename__ = "upload_sessions"
|
|
|
|
user_id: Mapped[uuid.UUID] = mapped_column(
|
|
ForeignKey("users.id", ondelete="CASCADE"), index=True, nullable=False
|
|
)
|
|
recording_id: Mapped[uuid.UUID | None] = mapped_column(
|
|
ForeignKey("recordings.id", ondelete="CASCADE")
|
|
)
|
|
client_recording_id: Mapped[str | None] = mapped_column(String(64))
|
|
title: Mapped[str | None] = mapped_column(String(300))
|
|
# Optional per-recording transcription model override chosen on the
|
|
# upload screen. Validated at finalize time; the finalize body wins when
|
|
# both specify one.
|
|
transcription_model: Mapped[str | None] = mapped_column(String(32))
|
|
declared_mime_type: Mapped[str] = mapped_column(String(100), nullable=False)
|
|
declared_size_bytes: Mapped[int] = mapped_column(BigInteger, nullable=False)
|
|
chunk_size_bytes: Mapped[int] = mapped_column(
|
|
BigInteger, nullable=False, default=8 * 1024 * 1024
|
|
)
|
|
status: Mapped[UploadSessionStatus] = mapped_column(
|
|
Enum(
|
|
UploadSessionStatus,
|
|
name="upload_session_status",
|
|
values_callable=lambda e: [m.value for m in e],
|
|
),
|
|
default=UploadSessionStatus.open,
|
|
nullable=False,
|
|
)
|
|
expires_at: Mapped[datetime] = mapped_column(UTCDT(), nullable=False)
|
|
completed_asset_id: Mapped[uuid.UUID | None] = mapped_column(
|
|
ForeignKey("assets.id", ondelete="SET NULL")
|
|
)
|
|
|
|
chunks: Mapped[list[UploadChunk]] = relationship(
|
|
back_populates="session", cascade="all, delete-orphan"
|
|
)
|
|
|
|
|
|
class UploadChunk(Base):
|
|
__tablename__ = "upload_chunks"
|
|
|
|
id: Mapped[int] = mapped_column(primary_key=True)
|
|
session_id: Mapped[uuid.UUID] = mapped_column(
|
|
ForeignKey("upload_sessions.id", ondelete="CASCADE"), index=True, nullable=False
|
|
)
|
|
chunk_index: Mapped[int] = mapped_column(nullable=False)
|
|
size_bytes: Mapped[int] = mapped_column(BigInteger, nullable=False)
|
|
checksum_sha256: Mapped[str] = mapped_column(String(64), nullable=False)
|
|
storage_key: Mapped[str] = mapped_column(String(500), nullable=False)
|
|
created_at: Mapped[datetime] = mapped_column(UTCDT(), default=utcnow)
|
|
|
|
session: Mapped[UploadSession] = relationship(back_populates="chunks")
|
|
|
|
__table_args__ = (UniqueConstraint("session_id", "chunk_index"),)
|
|
|
|
|
|
# ---------------------------------------------------------------------------
|
|
# Transcript / summary / tags
|
|
# ---------------------------------------------------------------------------
|
|
|
|
|
|
class Transcript(Base, PublicIdMixin):
|
|
__tablename__ = "transcripts"
|
|
|
|
recording_id: Mapped[uuid.UUID] = mapped_column(
|
|
ForeignKey("recordings.id", ondelete="CASCADE"), index=True, nullable=False
|
|
)
|
|
# Versioned: regenerated transcripts supersede older rows; the newest
|
|
# non-superseded row is authoritative. ``edited_by_user`` rows win.
|
|
version: Mapped[int] = mapped_column(default=1, nullable=False)
|
|
superseded_at: Mapped[datetime | None] = mapped_column(UTCDT())
|
|
language: Mapped[str | None] = mapped_column(String(16))
|
|
provider: Mapped[str] = mapped_column(String(80), nullable=False, default="manual")
|
|
model: Mapped[str | None] = mapped_column(String(120))
|
|
# Full text, plus segments: [{"start": 0.0, "end": 2.5, "text": "...",
|
|
# "speaker": "S1"|null}, ...]
|
|
text: Mapped[str] = mapped_column(Text, nullable=False, default="")
|
|
segments: Mapped[list | None] = mapped_column(JSONType)
|
|
edited_by_user: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)
|
|
|
|
recording: Mapped[Recording] = relationship(back_populates="transcripts")
|
|
|
|
__table_args__ = (Index("ix_transcripts_recording_version", "recording_id", "version"),)
|
|
|
|
|
|
class Summary(Base, PublicIdMixin):
|
|
"""Structured AI summary — user-editable.
|
|
|
|
JSON shape of ``content``:
|
|
{
|
|
"short": str,
|
|
"detailed": str,
|
|
"key_points": [str],
|
|
"decisions": [str],
|
|
"action_items": [str],
|
|
"questions": [str]
|
|
}
|
|
"""
|
|
|
|
__tablename__ = "summaries"
|
|
|
|
recording_id: Mapped[uuid.UUID] = mapped_column(
|
|
ForeignKey("recordings.id", ondelete="CASCADE"), index=True, nullable=False
|
|
)
|
|
version: Mapped[int] = mapped_column(default=1, nullable=False)
|
|
superseded_at: Mapped[datetime | None] = mapped_column(UTCDT())
|
|
provider: Mapped[str] = mapped_column(String(80), nullable=False, default="manual")
|
|
model: Mapped[str | None] = mapped_column(String(120))
|
|
# Voice the LLM was asked to summarize in (e.g. "sarcastic"). NULL =
|
|
# neutral house voice. Display-only: the content JSON shape is unchanged.
|
|
tone: Mapped[str | None] = mapped_column(String(64))
|
|
content: Mapped[dict] = mapped_column(JSONType, nullable=False, default=dict)
|
|
edited_by_user: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)
|
|
|
|
recording: Mapped[Recording] = relationship(back_populates="summaries")
|
|
|
|
|
|
class Tag(Base, PublicIdMixin):
|
|
__tablename__ = "tags"
|
|
|
|
user_id: Mapped[uuid.UUID] = mapped_column(
|
|
ForeignKey("users.id", ondelete="CASCADE"), index=True, nullable=False
|
|
)
|
|
name: Mapped[str] = mapped_column(String(80), nullable=False)
|
|
|
|
recordings: Mapped[list[Recording]] = relationship(
|
|
secondary="recording_tags", back_populates="tags"
|
|
)
|
|
|
|
__table_args__ = (UniqueConstraint("user_id", "name"),)
|
|
|
|
|
|
class RecordingTag(Base):
|
|
__tablename__ = "recording_tags"
|
|
|
|
recording_id: Mapped[uuid.UUID] = mapped_column(
|
|
ForeignKey("recordings.id", ondelete="CASCADE"), primary_key=True
|
|
)
|
|
tag_id: Mapped[uuid.UUID] = mapped_column(
|
|
ForeignKey("tags.id", ondelete="CASCADE"), primary_key=True
|
|
)
|
|
|
|
|
|
# ---------------------------------------------------------------------------
|
|
# Processing jobs
|
|
# ---------------------------------------------------------------------------
|
|
|
|
|
|
class JobStatus(enum.StrEnum):
|
|
queued = "queued"
|
|
running = "running"
|
|
succeeded = "succeeded"
|
|
failed = "failed"
|
|
skipped = "skipped" # e.g. AI disabled, or user-edited output protected
|
|
|
|
|
|
class JobType(enum.StrEnum):
|
|
normalize_audio = "normalize_audio"
|
|
transcribe = "transcribe"
|
|
summarize = "summarize"
|
|
|
|
|
|
class ProcessingJob(Base, PublicIdMixin):
|
|
"""One pipeline step for one recording. Idempotent: reruns overwrite
|
|
derived outputs (unless user-edited) and never touch originals."""
|
|
|
|
__tablename__ = "processing_jobs"
|
|
|
|
recording_id: Mapped[uuid.UUID] = mapped_column(
|
|
ForeignKey("recordings.id", ondelete="CASCADE"), index=True, nullable=False
|
|
)
|
|
job_type: Mapped[JobType] = mapped_column(
|
|
Enum(JobType, name="job_type", values_callable=lambda e: [m.value for m in e]),
|
|
nullable=False,
|
|
)
|
|
status: Mapped[JobStatus] = mapped_column(
|
|
Enum(JobStatus, name="job_status", values_callable=lambda e: [m.value for m in e]),
|
|
default=JobStatus.queued,
|
|
nullable=False,
|
|
index=True,
|
|
)
|
|
attempt: Mapped[int] = mapped_column(default=0, nullable=False)
|
|
max_attempts: Mapped[int] = mapped_column(default=3, nullable=False)
|
|
error: Mapped[str | None] = mapped_column(Text)
|
|
# Fine-grained phase for progress display (e.g. transcribe jobs report
|
|
# "loading-model" then "transcribing"). Nullable: older rows predate it.
|
|
# Never parsed by pipeline logic — display only.
|
|
stage: Mapped[str | None] = mapped_column(String(32))
|
|
# 0-100 work estimate within the current stage, when known.
|
|
progress: Mapped[int | None] = mapped_column()
|
|
# Summarize jobs only: the voice the LLM was asked for (persisted so a
|
|
# sweep re-enqueue, which carries only (job_type, recording_id), reruns
|
|
# the same request). NULL = neutral.
|
|
tone: Mapped[str | None] = mapped_column(String(64))
|
|
started_at: Mapped[datetime | None] = mapped_column(UTCDT())
|
|
finished_at: Mapped[datetime | None] = mapped_column(UTCDT())
|
|
# Opaque arq task handle for observability.
|
|
task_handle: Mapped[str | None] = mapped_column(String(120))
|
|
|
|
__table_args__ = (Index("ix_processing_jobs_recording_type", "recording_id", "job_type"),)
|
|
|
|
|
|
class ExportJob(Base, PublicIdMixin):
|
|
__tablename__ = "export_jobs"
|
|
|
|
user_id: Mapped[uuid.UUID] = mapped_column(
|
|
ForeignKey("users.id", ondelete="CASCADE"), index=True, nullable=False
|
|
)
|
|
recording_id: Mapped[uuid.UUID] = mapped_column(
|
|
ForeignKey("recordings.id", ondelete="CASCADE"), index=True, nullable=False
|
|
)
|
|
# "audio", "transcript_txt", "notes_md", "bundle_zip"
|
|
export_type: Mapped[str] = mapped_column(String(40), nullable=False)
|
|
status: Mapped[JobStatus] = mapped_column(
|
|
Enum(JobStatus, name="export_job_status", values_callable=lambda e: [m.value for m in e]),
|
|
default=JobStatus.queued,
|
|
nullable=False,
|
|
)
|
|
asset_id: Mapped[uuid.UUID | None] = mapped_column(ForeignKey("assets.id", ondelete="SET NULL"))
|
|
error: Mapped[str | None] = mapped_column(Text)
|
|
|
|
|
|
# Ensure full-text search columns exist on Postgres (added via migration as
|
|
# tsvector generated columns; see migrations/versions/*_fts.py).
|
|
|
|
|
|
class AppSetting(Base, PublicIdMixin):
|
|
"""Server-wide settings editable at runtime (global transcription model
|
|
default, …). Single row per key; the desktop Settings page writes here."""
|
|
|
|
__tablename__ = "app_settings"
|
|
|
|
key: Mapped[str] = mapped_column(String(120), nullable=False, unique=True)
|
|
value: Mapped[dict] = mapped_column(JSONType, nullable=False, default=dict)
|