Skip to content

Speakr is a personal, self-hosted web application designed for transcribing audio recordings

Python
29.4 KB
Raw Blame
"""
Recording and TranscriptChunk database models.

This module defines models for audio recordings and their chunked transcriptions.
"""

import logging
import os
from datetime import datetime
from sqlalchemy import func
from sqlalchemy.orm import validates
from src.database import db
from src.utils import md_to_html

logger = logging.getLogger(__name__)


class Recording(db.Model):
    """Main recording model storing audio files and their metadata."""

    # Add user_id foreign key to associate recordings with users
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=True)
    id = db.Column(db.Integer, primary_key=True)
    # Title will now often be AI-generated, maybe start with filename?
    title = db.Column(db.String(200), nullable=True)  # Allow Null initially
    # Where the title came from (#412): 'user' (typed in a form, edit or API),
    # 'auto' (title step: AI, naming template or filename). NULL for recordings
    # from before #412 and for placeholders; see utils.titles.title_is_user_chosen.
    title_source = db.Column(db.String(16), nullable=True)
    participants = db.Column(db.String(500))
    notes = db.Column(db.Text)
    transcription = db.Column(db.Text, nullable=True)
    # Language the transcription service reported for THIS recording, as an
    # ISO 639-1 code. Recorded at transcription time rather than read from the
    # user's preference, which is a live setting and would describe today's
    # configuration rather than the audio. None when the backend did not
    # report one, or reported something that is not a language code.
    transcription_language = db.Column(db.String(20), nullable=True)
    summary = db.Column(db.Text, nullable=True)
    status = db.Column(db.String(50), default='PENDING')  # PENDING, PROCESSING, SUMMARIZING, COMPLETED, FAILED
    audio_path = db.Column(db.String(500))
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    meeting_date = db.Column(db.DateTime, nullable=True)
    file_size = db.Column(db.Integer)  # Store file size in bytes
    original_filename = db.Column(db.String(500), nullable=True)  # Store the original uploaded filename
    is_inbox = db.Column(db.Boolean, default=True)  # New recordings are marked as inbox by default
    is_highlighted = db.Column(db.Boolean, default=False)  # Recordings can be highlighted by the user
    is_archived = db.Column(db.Boolean, default=False)  # Hidden from the main list, nothing deleted (#394)
    mime_type = db.Column(db.String(100), nullable=True)
    audio_duration_seconds = db.Column(db.Float, nullable=True)  # Cached audio duration to avoid materializing remote storage during serialization
    completed_at = db.Column(db.DateTime, nullable=True)
    # Last change a client can see (mailr spec G2); set by the before_flush
    # listener in src/services/recording_changes.py, never by hand.
    updated_at = db.Column(db.DateTime, nullable=True, index=True)
    # API upload retries (mailr spec G8): a repeat with the same key by the same
    # user within 24 hours returns this recording. Unique per user
    # (ux_recording_user_idem, #476); an expired key is cleared before reuse.
    upload_idempotency_key = db.Column(db.String(100), nullable=True)
    processing_time_seconds = db.Column(db.Integer, nullable=True)
    transcription_duration_seconds = db.Column(db.Integer, nullable=True)  # Time taken for transcription
    # Cached audio duration in seconds, populated at transcription
    # completion. Lets to_dict() / API list views avoid an ffprobe
    # subprocess per row. Falls back to live ffprobe in
    # get_audio_duration() only when the column is empty.
    audio_duration_seconds = db.Column(db.Float, nullable=True)
    summarization_duration_seconds = db.Column(db.Integer, nullable=True)  # Time taken for summarization
    processing_source = db.Column(db.String(50), default='upload')  # upload, auto_process, recording
    error_message = db.Column(db.Text, nullable=True)  # Store detailed error messages
    file_hash = db.Column(db.String(64), nullable=True)  # SHA-256 hash for duplicate detection

    # Per-upload override for VIDEO_RETENTION. When True, the processing
    # pipeline extracts audio and discards the video stream even if the
    # server's VIDEO_RETENTION is on. Also signals that the upload was
    # allowed to exceed max_file_size_mb because only the extracted audio
    # has to fit that limit. See docs/admin-guide/configuration.md.
    keep_audio_only = db.Column(db.Boolean, default=False, nullable=False)

    # Auto-deletion and archival fields
    audio_deleted_at = db.Column(db.DateTime, nullable=True)  # When audio file was deleted (null = not deleted)
    deletion_exempt = db.Column(db.Boolean, default=False)  # Manual exemption from auto-deletion

    # Speaker embeddings from diarization (JSON dict mapping speaker IDs to 256-dimensional vectors)
    speaker_embeddings = db.Column(db.JSON, nullable=True)
    # Voice embedding space the embeddings above came from (NULL: legacy space)
    speaker_embeddings_space_id = db.Column(db.Integer, nullable=True)
    # Diarization label -> the name it was assigned, so a later rename can
    # still find the label's embedding after the transcript shows names.
    speaker_label_map = db.Column(db.JSON, nullable=True)

    # Per-recording prompt-template variables (e.g. {"agenda": "...", "attendees": "..."}).
    # Substituted into {{name}} placeholders in the resolved summary prompt at summarisation time.
    prompt_variables = db.Column(db.JSON, nullable=True)

    # Effective transcription hints actually sent to the ASR for this recording,
    # captured at transcription time after tag/folder/user and admin-default
    # resolution. Persisted so the UI can show which hotwords/initial prompt were
    # used and pre-fill them on reprocess (issue #309). Null = none were applied.
    resolved_hotwords = db.Column(db.Text, nullable=True)
    resolved_initial_prompt = db.Column(db.Text, nullable=True)

    # Rendered auto-export filename WITHOUT the .md extension (#348). Set on
    # first export and authoritative from then on: re-exports overwrite this
    # file and deletion renames it. Null = legacy "recording_{id}" naming, so
    # existing deployments keep working with their existing files.
    export_filename = db.Column(db.String(500), nullable=True)

    # Folder relationship (one-to-many: a recording belongs to at most one folder)
    folder_id = db.Column(db.Integer, db.ForeignKey('folder.id', ondelete='SET NULL'), nullable=True, index=True)

    # Relationships
    folder = db.relationship('Folder', back_populates='recordings')
    tag_associations = db.relationship('RecordingTag', back_populates='recording', cascade='all, delete-orphan', order_by='RecordingTag.order')

    @property
    def tags(self):
        """Get tags ordered by the order they were added to this recording."""
        return [assoc.tag for assoc in sorted(self.tag_associations, key=lambda x: x.order)]

    def get_visible_tags(self, viewer_user, member_group_ids=None):
        """
        Get tags that are visible to a specific user viewing this recording.

        Visibility rules:
        - Group tags: visible if viewer is a member of the tag's group
        - Personal tags: visible only to the tag creator

        Note: These rules apply to ALL users, including the recording owner.
        Personal tags are private to their creator regardless of recording ownership.

        Args:
            viewer_user: User object viewing the recording (or None for backward compatibility)
            member_group_ids: optional set of the viewer's group ids, when a list
                has read them once for its page (see group_ids_of); otherwise
                each group tag costs one membership query

        Returns:
            List of Tag objects visible to the viewer
        """
        # If no viewer specified, return all tags (backward compatibility)
        if viewer_user is None:
            return self.tags

        if not self.tags:
            return []

        # Import here to avoid circular dependencies
        from src.models.organization import GroupMembership

        visible_tags = []
        for tag in self.tags:
            # Group tags: visible if viewer is a member of the group
            if tag.group_id:
                if member_group_ids is not None:
                    membership = tag.group_id in member_group_ids
                else:
                    membership = GroupMembership.query.filter_by(
                        group_id=tag.group_id,
                        user_id=viewer_user.id
                    ).first()
                if membership:
                    visible_tags.append(tag)
            # Personal tags: visible only to tag creator
            else:
                if tag.user_id == viewer_user.id:
                    visible_tags.append(tag)

        return visible_tags

    @validates('title')
    def _clip_title(self, key, value):
        # The column holds 200 characters; PostgreSQL refuses a longer value,
        # so a long title from the API, a naming template or the AI title
        # step is shortened here, whatever path set it
        if isinstance(value, str) and len(value) > 200:
            return value[:200]
        return value

    def get_user_notes(self, user):
        """
        Get notes from user's perspective (owner or shared recipient).

        - Recording owner sees Recording.notes
        - Shared users see their personal_notes from SharedRecordingState

        Args:
            user: User object viewing the recording

        Returns:
            String notes content or None
        """
        if user is None:
            return self.notes

        if self.user_id == user.id:
            return self.notes  # Owner sees Recording.notes
        else:
            # Shared user sees their personal notes
            from src.models.sharing import SharedRecordingState
            state = SharedRecordingState.query.filter_by(
                recording_id=self.id,
                user_id=user.id
            ).first()
            return state.personal_notes if state else None

    def get_audio_duration(self, allow_probe_fallback=False):
        """
        Get cached audio duration in seconds.

        By default this avoids materializing audio from storage (especially S3)
        during normal API serialization (which would otherwise turn a list
        serialization into an ffprobe subprocess per row). Optional probe
        fallback is kept for explicit callers that need best-effort backfill.

        Args:
            allow_probe_fallback: When True, probe the stored file if cached
                duration is missing.

        Returns:
            Float duration in seconds, or None if unavailable.
        """
        if self.audio_deleted_at is not None:
            return None
        if self.audio_duration_seconds is not None:
            try:
                return float(self.audio_duration_seconds)
            except (TypeError, ValueError):
                return None

        if not allow_probe_fallback:
            return None

        if not self.audio_path:
            return None
        try:
            from src.services.storage import get_storage_service
            from src.utils.ffprobe import get_duration
            storage = get_storage_service()
            if not storage.exists(self.audio_path):
                return None

            with storage.materialize(self.audio_path) as materialized:
                # Allow longer timeout for packet scanning fallback on files without duration metadata
                duration = get_duration(materialized.local_path, timeout=30)
                if duration is not None:
                    self.audio_duration_seconds = float(duration)
                return duration
        except Exception as e:
            logger.warning(f"Failed to get duration for recording {self.id}: {e}")
            return None

    @classmethod
    def get_duplicate_info_map(cls, user_id):
        """Pre-compute duplicate-info groupings for one user in a single query.

        Returns a dict keyed by file_hash. Each value is the
        ``{'total_copies': N, 'copies': [...]}`` payload that
        ``get_duplicate_info`` returns. List endpoints pass this map
        into ``to_dict`` / ``to_list_dict`` to avoid the per-row query.
        """
        rows = (
            cls.query
            .filter(cls.user_id == user_id, cls.file_hash != None)  # noqa: E711
            .with_entities(cls.id, cls.title, cls.created_at, cls.file_hash)
            .order_by(cls.created_at)
            .all()
        )
        grouped = {}
        for rid, title, created, fh in rows:
            grouped.setdefault(fh, []).append((rid, title, created))
        out = {}
        for fh, items in grouped.items():
            if len(items) <= 1:
                continue
            out[fh] = {
                'total_copies': len(items),
                'copies': [
                    {
                        'id': rid,
                        'title': title,
                        'created_at': created.isoformat() if created else None,
                    }
                    for (rid, title, created) in items
                ],
            }
        return out

    def get_duplicate_info(self):
        """Check if other recordings share the same file_hash for this user.

        Returns:
            Dict with total copy count and list of copies, or None.
        """
        if not self.file_hash:
            return None
        dupes = Recording.query.filter(
            Recording.user_id == self.user_id,
            Recording.file_hash == self.file_hash,
        ).with_entities(
            Recording.id, Recording.title, Recording.created_at
        ).order_by(Recording.created_at).all()
        if len(dupes) > 1:
            return {
                'total_copies': len(dupes),
                'copies': [
                    {
                        'id': d.id,
                        'title': d.title or f'#{d.id}',
                        'created_at': d.created_at.isoformat() if d.created_at else None,
                        'is_self': d.id == self.id
                    }
                    for d in dupes
                ]
            }
        return None

    def _serialize_tags_and_folder(self, viewer_user, share_map=None, batch=None):
        """Build (tags, folder, folder_id) for a viewer, applying #314 rules.

        For a recording the viewer does NOT own, the share-reason (from the
        InternalShare row) is surfaced and flagged ``is_foreign: True`` so the
        UI can mark it and keep it non-filterable.  A group-tag share also
        surfaces the other tags on this recording from that same group: group
        tags are shared context for its members, unlike the owner's personal
        tags.  Tags from other groups and the owner's personal tags/folder are
        never leaked. Owners (and the no-viewer backward-compat case) see their
        tags/folder normally.

        share_map: optional {recording_id: InternalShare} to avoid an N+1 query
        in list views; falls back to a per-recording lookup when not provided.
        batch: optional RecordingListBatch with the page's visible tags and
        tag/folder counts (D#483).
        """
        if batch is not None:
            visible_tags = batch.visible_tags(self)
            tag_dict, folder_dict = batch.tag_dict, batch.folder_dict
        else:
            visible_tags = self.get_visible_tags(viewer_user)
            tag_dict, folder_dict = (lambda tag: tag.to_dict()), (lambda folder: folder.to_dict())
        is_foreign = viewer_user is not None and self.user_id != viewer_user.id

        if not is_foreign:
            tags = [tag_dict(tag) for tag in visible_tags] if visible_tags else []
            folder = folder_dict(self.folder) if self.folder else None
            return tags, folder, self.folder_id

        from src.models.sharing import InternalShare
        if share_map is not None:
            share = share_map.get(self.id)
        else:
            share = InternalShare.query.filter_by(
                recording_id=self.id, shared_with_user_id=viewer_user.id
            ).first()
        reason_tag_id = share.source_tag_id if share else None
        reason_folder_id = getattr(share, 'source_folder_id', None) if share else None

        tags = []
        reason_tag = next((tag for tag in visible_tags if tag.id == reason_tag_id), None)
        if reason_tag:
            if share and share.source_type == 'group_tag' and reason_tag.group_id:
                # Group tags are shared metadata for their own group.  Include
                # only the tags from the group that granted access, so a
                # recipient never learns the recording's tags from another
                # group they may also belong to.
                tags_to_surface = [
                    tag for tag in visible_tags if tag.group_id == reason_tag.group_id
                ]
            else:
                tags_to_surface = [reason_tag]

            for tag in tags_to_surface:
                td = tag_dict(tag)
                td['is_foreign'] = True
                tags.append(td)
        if reason_folder_id and self.folder_id == reason_folder_id and self.folder:
            folder = folder_dict(self.folder)
            folder['is_foreign'] = True
            return tags, folder, self.folder_id
        return tags, None, None

    def updated_at_z(self):
        """updated_at (created_at before the first change) as ISO 8601 with Z."""
        value = self.updated_at or self.created_at
        return value.strftime('%Y-%m-%dT%H:%M:%S.%fZ') if value else None

    def to_list_dict(self, viewer_user=None, duplicate_info_map=None, share_map=None, batch=None):
        """
        Lightweight dict for list views - excludes expensive HTML conversions.

        Args:
            viewer_user: User viewing the recording (for tag visibility filtering)
            share_map: optional {recording_id: InternalShare} for #314 share-reason
                resolution without an N+1 query.
            batch: optional RecordingListBatch (src/services/recording_list.py)
                built for viewer_user over the page: share counts, duplicates,
                visible tags and tag/folder counts come from it instead of
                per-row queries (D#483).
        """
        # Import here to avoid circular dependencies
        from src.models.sharing import InternalShare, Share

        if batch is not None:
            shared_with_count = batch.shared_with_counts.get(self.id, 0)
            public_share_count = batch.public_share_counts.get(self.id, 0)
            duplicate_info = batch.duplicate_info(self)
            if share_map is None:
                share_map = batch.share_map
        else:
            # Count internal shares for this recording
            shared_with_count = db.session.query(func.count(InternalShare.id)).filter(
                InternalShare.recording_id == self.id
            ).scalar() or 0

            # Count public shares (link shares) for this recording
            public_share_count = db.session.query(func.count(Share.id)).filter(
                Share.recording_id == self.id
            ).scalar() or 0
            duplicate_info = self._dup_from_map_or_query(duplicate_info_map)

        tags_ser, folder_ser, folder_id_ser = self._serialize_tags_and_folder(viewer_user, share_map, batch)

        return {
            'id': self.id,
            'title': self.title,
            'participants': self.participants,
            # Cheap boolean (full notes text is excluded from the list for
            # lightness) — lets the merge modal offer this recording as a notes
            # source without loading every recording's notes.
            'has_notes': bool(self.notes and self.notes.strip()),
            'status': self.status,
            'created_at': self.created_at.isoformat() if self.created_at else None,
            'completed_at': self.completed_at.isoformat() if self.completed_at else None,
            'updated_at': self.updated_at_z(),
            'meeting_date': self.meeting_date.isoformat() if self.meeting_date else None,
            'file_size': self.file_size,
            'original_filename': self.original_filename,
            'mime_type': self.mime_type,  # cheap column read; lets the sidebar mark video recordings without opening them
            'is_inbox': self.is_inbox,
            'is_highlighted': self.is_highlighted,
            'is_archived': bool(self.is_archived),
            'audio_deleted_at': self.audio_deleted_at.isoformat() if self.audio_deleted_at else None,
            'audio_available': self.audio_deleted_at is None,
            # True only when a playable audio file actually exists yet. A merge
            # (issue #323) creates the row before its audio is stitched, so the
            # UI uses this to keep the play button disabled until the file lands.
            'audio_ready': bool(self.audio_path) and self.audio_deleted_at is None,
            'deletion_exempt': self.deletion_exempt,
            'folder_id': folder_id_ser,
            'folder': folder_ser,
            'tags': tags_ser,
            'duplicate_info': duplicate_info,
            'shared_with_count': shared_with_count,
            'public_share_count': public_share_count,
            'audio_duration': self.get_audio_duration(),
            'keep_audio_only': self.keep_audio_only,
        }

    def _dup_from_map_or_query(self, duplicate_info_map):
        """Use a pre-batched duplicate-info map when provided, else fall
        back to the per-row query. The map is keyed by file_hash and is
        produced by ``get_duplicate_info_map`` for list endpoints; the
        legacy path stays correct for single-row callers (detail views,
        share endpoints, etc.)."""
        if duplicate_info_map is not None:
            if not self.file_hash:
                return None
            entry = duplicate_info_map.get(self.file_hash)
            if not entry:
                return None
            # Annotate is_self for the current row, matching the
            # legacy per-row payload shape.
            copies = []
            for c in entry['copies']:
                copies.append({**c, 'is_self': c['id'] == self.id})
            return {'total_copies': entry['total_copies'], 'copies': copies}
        return self.get_duplicate_info()

    def to_dict(self, include_html=True, viewer_user=None, duplicate_info_map=None, share_map=None, batch=None):
        """
        Full dict with optional HTML conversion for notes/summary.

        Args:
            include_html: Whether to include HTML-rendered markdown fields
            viewer_user: User viewing the recording (for tag visibility filtering)
            share_map: optional {recording_id: InternalShare} for #314 share-reason
                resolution without an N+1 query.
            batch: optional RecordingListBatch built for viewer_user over the
                list: share counts, duplicates, visible tags, tag/folder counts
                and the viewer's notes come from it instead of per-row queries.
                A duplicate_info_map, when given, still decides duplicate_info.
        """
        # Import here to avoid circular dependencies
        from src.models.sharing import InternalShare, Share

        if batch is not None:
            shared_with_count = batch.shared_with_counts.get(self.id, 0)
            public_share_count = batch.public_share_counts.get(self.id, 0)
            if share_map is None:
                share_map = batch.share_map
            user_notes = batch.user_notes(self) if viewer_user is not None else self.notes
            duplicate_info = (self._dup_from_map_or_query(duplicate_info_map) if duplicate_info_map is not None
                              else batch.duplicate_info(self))
        else:
            # Count internal shares for this recording
            shared_with_count = db.session.query(func.count(InternalShare.id)).filter(
                InternalShare.recording_id == self.id
            ).scalar() or 0

            # Count public shares (link shares) for this recording
            public_share_count = db.session.query(func.count(Share.id)).filter(
                Share.recording_id == self.id
            ).scalar() or 0

            # Get user-specific notes
            user_notes = self.get_user_notes(viewer_user)
            duplicate_info = self._dup_from_map_or_query(duplicate_info_map)

        tags_ser, folder_ser, folder_id_ser = self._serialize_tags_and_folder(viewer_user, share_map, batch)

        data = {
            'id': self.id,
            'title': self.title,
            'participants': self.participants,
            'notes': user_notes,
            'transcription': self.transcription,
            'transcription_language': self.transcription_language,
            'summary': self.summary,
            'status': self.status,
            'created_at': self.created_at.isoformat() if self.created_at else None,
            'completed_at': self.completed_at.isoformat() if self.completed_at else None,
            'updated_at': self.updated_at_z(),
            'processing_time_seconds': self.processing_time_seconds,
            'transcription_duration_seconds': self.transcription_duration_seconds,
            'summarization_duration_seconds': self.summarization_duration_seconds,
            'meeting_date': self.meeting_date.isoformat() if self.meeting_date else None,
            'file_size': self.file_size,
            'original_filename': self.original_filename,
            'user_id': self.user_id,
            'is_inbox': self.is_inbox,
            'is_highlighted': self.is_highlighted,
            'is_archived': bool(self.is_archived),
            'mime_type': self.mime_type,
            'audio_deleted_at': self.audio_deleted_at.isoformat() if self.audio_deleted_at else None,
            'audio_available': self.audio_deleted_at is None,
            'audio_duration': self.get_audio_duration(allow_probe_fallback=False),
            'deletion_exempt': self.deletion_exempt,
            'folder_id': folder_id_ser,
            'folder': folder_ser,
            'tags': tags_ser,
            'events': [event.to_dict() for event in self.events] if self.events else [],
            'prompt_variables': self.prompt_variables or {},
            'resolved_hotwords': self.resolved_hotwords,
            'resolved_initial_prompt': self.resolved_initial_prompt,
            'duplicate_info': duplicate_info,
            'shared_with_count': shared_with_count,
            'public_share_count': public_share_count,
            'keep_audio_only': self.keep_audio_only,
        }

        # Only compute expensive HTML conversions when explicitly requested
        if include_html:
            data['notes_html'] = md_to_html(user_notes) if user_notes else ""
            data['summary_html'] = md_to_html(self.summary) if self.summary else ""
        else:
            data['notes_html'] = ""
            data['summary_html'] = ""

        return data


class TranscriptChunk(db.Model):
    """Stores chunked transcription segments for efficient retrieval and embedding."""

    id = db.Column(db.Integer, primary_key=True)
    recording_id = db.Column(db.Integer, db.ForeignKey('recording.id'), nullable=False)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
    chunk_index = db.Column(db.Integer, nullable=False)  # Order within the recording
    content = db.Column(db.Text, nullable=False)  # The actual text chunk
    start_time = db.Column(db.Float, nullable=True)  # Start time in seconds (if available)
    end_time = db.Column(db.Float, nullable=True)  # End time in seconds (if available)
    speaker_name = db.Column(db.String(100), nullable=True, index=True)  # Speaker for this chunk (indexed for speaker rename operations)
    embedding = db.Column(db.LargeBinary, nullable=True)  # Stored as binary vector
    created_at = db.Column(db.DateTime, default=datetime.utcnow)

    # Composite index for efficient speaker name lookups scoped to user
    __table_args__ = (
        db.Index('idx_user_speaker_name', 'user_id', 'speaker_name'),
        # Chunks are read and deleted by recording, in order (D#441)
        db.Index('ix_transcript_chunk_recording', 'recording_id', 'chunk_index'),
    )

    # Relationships
    recording = db.relationship('Recording', backref=db.backref('chunks', lazy=True, cascade='all, delete-orphan'))
    user = db.relationship('User', backref=db.backref('transcript_chunks', lazy=True, cascade='all, delete-orphan'))

    def to_dict(self):
        """Convert model to dictionary representation."""
        return {
            'id': self.id,
            'recording_id': self.recording_id,
            'chunk_index': self.chunk_index,
            'content': self.content,
            'start_time': self.start_time,
            'end_time': self.end_time,
            'speaker_name': self.speaker_name,
            'created_at': self.created_at.isoformat() if self.created_at else None
        }


class RecordingTombstone(db.Model):
    """A recording a user can no longer see, for the changes feed (mailr spec G2).

    No foreign key to the recording: the row outlives it. Written by the
    before_flush listener in src/services/recording_changes.py; pruned after
    RECORDING_TOMBSTONE_DAYS.
    """
    __tablename__ = 'recording_tombstone'

    id = db.Column(db.Integer, primary_key=True)
    recording_id = db.Column(db.Integer, nullable=False)
    user_id = db.Column(db.Integer, db.ForeignKey('user.id', ondelete='CASCADE'), nullable=False)
    deleted_at = db.Column(db.DateTime, nullable=False, index=True)
    reason = db.Column(db.String(20), nullable=False, default='deleted')  # deleted | retention | access_revoked

    __table_args__ = (db.Index('ix_recording_tombstone_user_deleted', 'user_id', 'deleted_at'),)