Models¶

This page documents the core database models in Sapari. All models use SQLAlchemy with async support and follow a consistent pattern with UUIDs, timestamps, and soft deletion.

Core Entities¶

Sapari's video-editing workflow is built on the canvas-media data model, which replaced the legacy Clip / ClipFile + Asset / AssetFile model (the legacy tables were dropped in migration 124f9581c598). The main entities:

erDiagram
    User ||--o{ Project : owns
    User ||--o{ MediaItem : owns
    User ||--o{ MediaItemGroup : creates
    Project ||--o{ Canvas : "sequences"
    Project ||--o{ Edit : has
    Project ||--o{ Draft : saves
    Project ||--o{ Export : renders

    MediaItem }o--|| MediaFile : references
    MediaItem }o--o{ MediaItemGroup : "belongs to (many-to-many)"

    Canvas ||--o{ Edit : "scopes"
    Edit }o--o| MediaItem : "places"

    Draft }o--o{ Edit : overrides
    Export }o--o| Draft : "based on"

Two ideas drive the model:

• Content-addressable storage. A MediaFile is a unique file (deduplicated by sha256_hash for uploads or youtube_video_id for imports). A MediaItem is a per-user reference to one MediaFile — many items (across users and projects) can share a single file.
• Composition over source-time. A project's timeline is a sequence of Canvas tiles. Each tile covers a span of source-time and declares how that span is composed (single clip, split-screen, PiP, insert). Edit rows attach to a canvas to place media and cut/mute regions.

User¶

class User:
    id: int
    name: str                      # 2-30 chars
    username: str                  # 2-20 chars, lowercase alphanumeric + underscores (between chars), unique
    email: str                     # unique
    hashed_password: str
    profile_image_url: str | None
    tier_id: int | None            # FK to Tier
    is_superuser: bool
    email_verified: bool           # Must be True for password users to login
    google_id: str | None          # Google OAuth user ID (unique)
    github_id: str | None          # GitHub OAuth user ID (unique)
    oauth_provider: str | None     # "google" or "github"
    stripe_customer_id: str | None # Stripe customer reference
    storage_used_bytes: int        # Cached counter of upload storage used (default 0)
    onboarding_seen: dict | None   # Tour keys dismissed, e.g. {"desktop_pipeline": true} (JSON, nullable)
    locale: str                    # UI locale for API errors + emails (default "en"); distinct from AnalysisRun.language

Auth flow: Password-based users start with email_verified=False. A verification email is sent on signup. Login returns 403 until verified. OAuth users (Google/GitHub) get email_verified set from the provider and skip the verification step.

Storage quota: storage_used_bytes is a cached counter — incremented on confirm (media upload), decremented on delete (non-YouTube only, last reference). Daily reconciliation cron (reconcile_storage_usage, 3 AM) corrects drift. Quota checked at presign-time; exceeding tier quota returns 422.

Project¶

A project is the top-level container for a video editing session. Users create projects, upload clips to them, and export edited videos.

class Project:
    uuid: UUID
    user_id: int
    name: str
    status: ProjectStatus  # created, analyzing, analyzed, rendering, complete, failed
    settings: dict         # pacing_level, silence_threshold_ms, language, etc.
    transcript: str | None # Full transcript text (copy from active run)
    active_run_id: UUID | None  # Currently active AnalysisRun
    error_message: str | None

Status Flow:

stateDiagram-v2
    [*] --> CREATED
    CREATED --> ANALYZING: Trigger analysis
    ANALYZING --> ANALYZED: Analysis complete
    ANALYZING --> FAILED: Analysis error
    ANALYZED --> RENDERING: Trigger render
    RENDERING --> COMPLETE: Render complete
    RENDERING --> FAILED: Render error
    COMPLETE --> [*]
    FAILED --> ANALYZING: Retry analysis
    FAILED --> RENDERING: Retry render

MediaFile¶

MediaFile is content-addressable storage metadata for a unique media file — the row that unified the legacy ClipFile + AssetFile. Identity is the backing content: exactly one of sha256_hash (uploads) or youtube_video_id (imports) is set per row, both UNIQUE, so re-uploading the same bytes (or re-importing the same video) reuses the existing row. Postgres allows many NULLs in a unique index, so uploads (NULL youtube_video_id) and imports (NULL sha256_hash) coexist without collision.

class MediaFile:
    uuid: UUID
    sha256_hash: str | None       # UNIQUE; identity for uploaded bytes
    youtube_video_id: str | None  # UNIQUE; identity for imports
    storage_key: str | None       # R2 path to original file
    original_filename: str
    content_type: str
    size_bytes: int | None
    duration_ms: int | None       # Video/audio duration
    width: int | None             # Source pixel width (video/image; NULL for audio)
    height: int | None            # Source pixel height (video/image; NULL for audio)
    has_audio: bool               # default True; False when the source has no audio stream
    status: MediaFileStatus       # pending, multipart_initiated, downloading, processing, uploaded, failed
    error_message: str | None
    metadata_json: dict | None    # Additional metadata (title, uploader, web_compatible, etc.)
    proxy_key: str | None         # R2 path to web-compatible 480p proxy
    audio_key: str | None         # R2 path to extracted audio
    sprite_key: str | None        # R2 path to timeline scrub sprite (10x20 grid)
    sprite_seconds_per_tile: int | None  # Density chosen at sprite generation time
    thumbnail_key: str | None     # R2 path to thumbnail
    waveform_json: list | None    # Peak amplitudes for timeline visualization

Artifact keys (proxy/audio/sprite/thumbnail/waveform) are populated lazily by the media pipeline worker; once every applicable artifact is present the row reaches MediaFileStatus.UPLOADED.

MediaFileStatus.UPLOADED universally means "all applicable artifacts present, file usable". The in-storage-but-not-yet-usable window is PROCESSING for every source — single-PUT/multipart confirm and external-download completion both land in PROCESSING, then emerge as UPLOADED once artifacts are generated.

has_audio is set by the media pipeline after probing the input with ffprobe. If the source carries no audio track (e.g. iOS ReplayKit screen recordings made without a microphone) the worker synthesizes a silent WAV at audio_key so downstream consumers stay shape-stable, and stores has_audio=False. The analysis pipeline reads this flag to route Whisper / silence / false-start around silent sources, and the frontend disables audio-dependent SettingsPanel controls when every source in the project is silent.

width / height are populated by the media pipeline probe (get_video_dimensions, in workers/download/media_pipeline/) for video and image content; they remain NULL for audio. The per-asset crop renderer reads these dims and falls back to letterbox when they are missing — see Edit.asset_crop_* below.

MediaItem¶

MediaItem is a user's reference to a MediaFile, carrying display metadata and per-item behavior defaults. scope encodes the library-vs-project distinction in a single table.

class MediaItem:
    uuid: UUID
    user_id: int                  # FK to User (CASCADE)
    media_file_id: UUID           # FK to MediaFile (RESTRICT — file can't be deleted while referenced)
    display_name: str
    scope: MediaItemScope         # library, project
    tags: list[str] | None
    scope_project_id: UUID | None # FK to Project (CASCADE); non-NULL iff scope=project
    behavior_defaults: dict | None  # Placement/audio config (folds the old AssetBehaviorConfig)

A LIBRARY item is reusable across projects and persists across project deletes (scope_project_id is NULL); a PROJECT item is bound to one project and cascade-deletes with it (scope_project_id is set). The CHECK constraint chk_media_item_scope_consistency keeps those two states the only valid ones:

(scope = 'LIBRARY' AND scope_project_id IS NULL)
OR (scope = 'PROJECT' AND scope_project_id IS NOT NULL)

behavior_defaults holds the placement/audio configuration formerly stored in a separate per-asset behavior-config row. It uses none_as_null so an unconfigured item is SQL NULL rather than JSON null — the library-filter and pipeline .is_(None) / .isnot(None) queries only match it that way.

Multiple MediaItems (across users and projects) can point at one MediaFile, which is what makes dedup by sha256_hash / youtube_video_id transparent to callers.

flowchart TB
    A[Upload file / Import YouTube URL] --> B{sha256 or youtube_video_id already stored?}
    B -- yes --> C[Reuse existing MediaFile]
    B -- no --> D[Create new MediaFile]
    C --> E[Create MediaItem referencing MediaFile]
    D --> E

Canvas¶

A Canvas is a composition tile covering a span of a project's source-time. The ordered sequence of canvases is the project's timeline; each tile declares how its span is composed.

class Canvas:
    uuid: UUID
    project_id: UUID              # FK to Project (CASCADE)
    type: CanvasType             # single, split, talking_head, insert, grid
    start_ms: int
    end_ms: int
    sequence_position: int        # UNIQUE(project_id, sequence_position) DEFERRABLE INITIALLY DEFERRED
    aspect_ratio_override: str | None
    letterbox_color: str | None
    crop_region: dict | None
    params: dict | None           # type-specific (split_ratio, pip_*, ...)
    no_trim: bool                 # Skip silence removal for this canvas
    no_subtitles: bool            # Skip subtitle generation
    no_assets: bool               # Skip asset overlay

CanvasType values: SINGLE (one clip fills the canvas — today's default), SPLIT (two sources side-by-side or top-bottom), TALKING_HEAD (asset fills the canvas, the clip becomes a PiP bubble), INSERT (full-frame asset inserted between canvases, zero source-time width), GRID (reserved placeholder for a future multi-cell layout).

sequence_position is unique within a project across all canvas types and gives one total order over the sequence. Its uniqueness is DEFERRABLE INITIALLY DEFERRED so re-densification (renumbering several rows in one transaction) can pass transiently-colliding positions and only be validated at commit — an immediate check would reject a swap mid-transaction.

The time-span CHECK chk_canvas_time_span allows start_ms == end_ms only for INSERT canvases, which are zero-width points between adjacent non-INSERT canvases:

start_ms < end_ms OR (start_ms = end_ms AND type = 'INSERT')

Canvas is read/written by CanvasService (CRUD plus the source-time mutation workflows — create-layout, reorder/swap, split-at-playhead, type-specific delete, SINGLE merge), exposed at /api/v1/projects/{uuid}/canvases. The canvas spine is the source of truth for a project's total timeline duration (get_project_total_duration_ms reads MAX(Canvas.end_ms) over non-INSERT canvases).

Edit¶

An edit represents a region to modify in the video - either a silence, false start, or profanity detected by analysis, a manual edit created by the user, or a keep region marking content to preserve.

class Edit:
    uuid: UUID
    project_id: UUID
    type: EditType       # silence, false_start, profanity, manual, asset, keep
    action: EditAction   # cut (remove video+audio), mute (silence audio), insert (asset), keep (preserve region)
    start_ms: int        # Edit start time (ms into combined project timeline)
    end_ms: int          # Edit end time (enforced end_ms <= project timeline duration at service layer)
    active: bool         # Whether to apply this edit
    confidence: float    # Detection confidence (0-1)
    reason: str          # Full explanation (for debugging/logs)
    reason_tag: str      # Short tag — raw English written by the detection pipeline; frontend localizes at render time

    # Canvas-media fields
    canvas_id: UUID | None           # FK to Canvas (CASCADE); which composition tile this edit belongs to
    role: EditRole | None            # canvas_fill, panel_a/b, pip_source, overlay, cut, mute, background_audio, watermark
    media_item_id: UUID | None       # FK to MediaItem (SET NULL); the source this edit places
    media_offset_ms: int | None      # Start offset into the media source (ms)
    media_offset_end_ms: int | None  # End offset into the media source (ms)
    output_duration_ms: int | None   # How long the placed source runs on the output timeline

    # Asset fields (only when type='asset')
    insert_source: InsertSource      # fixed, ai_directed, manual
    fixed_position: FixedPosition    # intro, outro, watermark, background_audio
    visual_mode: VisualMode          # replace, overlay, insert, none
    audio_mode: AudioMode            # original_only, asset_only, mix, none
    overlay_position: OverlayPosition  # 9-position grid (top_left, center, etc.)
    overlay_size_percent: int        # Overlay size as % of video (1-100)
    overlay_opacity_percent: int     # Overlay opacity (10-100)
    overlay_x: float                 # Custom X position (0-1, center-anchored)
    overlay_y: float                 # Custom Y position (0-1, center-anchored)
    overlay_flip_h: bool             # Flip overlay horizontally
    overlay_flip_v: bool             # Flip overlay vertically
    overlay_rotation_deg: int        # Rotation in degrees (0-360)
    audio_volume_percent: int        # Audio volume (0-100)
    audio_duck_main: bool            # Duck main audio during asset
    asset_offset_ms: int             # Offset into asset source (ms)

    # Inside-insert anchor (issue #234) — set together, both NULL when not anchored
    inside_insert_edit_id: UUID | None    # FK → edit.uuid (anchored INSERT target)
    insert_offset_ms: int | None          # Offset into that INSERT (ms, ≥ 0)

    # Per-INSERT override map: {insert_edit_uuid: 'merge'|'split'|'anchor'}
    insert_overlap_modes: dict[str, str] | None

    # Per-asset crop reframe (issue #235) — only consulted for REPLACE/INSERT video/image
    asset_crop_enabled: bool         # Default False; True opts into cover-crop (no letterbox)
    asset_crop_zoom: float           # ∈ [1.0, 10.0], default 1.0
    asset_crop_pan_x: float          # ∈ [-1.0, 1.0], default 0.0 (center)
    asset_crop_pan_y: float          # ∈ [-1.0, 1.0], default 0.0 (center)

Canvas-media fields. canvas_id scopes an edit to a composition tile (project-spanning edits like background audio / watermark leave it NULL). role (EditRole) says what the edit does within its canvas — CANVAS_FILL is the source that fills a SINGLE canvas (or an INSERT's full frame); PANEL_A / PANEL_B are the two panels of a SPLIT; PIP_SOURCE is the picture-in-picture bubble; OVERLAY is an asset overlaid on the canvas; CUT / MUTE are removed / muted spans; BACKGROUND_AUDIO / WATERMARK are project-spanning. media_item_id points at the MediaItem a content-bearing edit places (SET NULL on media deletion), and media_offset_ms / media_offset_end_ms window the source, while output_duration_ms defines how long it runs on the output timeline. Asset / b-roll edits carry media_item_id — the legacy asset_file_id column was dropped in migration 124f9581c598.

Inside-insert anchor. When a non-INSERT asset edit is placed inside an existing INSERT region, inside_insert_edit_id + insert_offset_ms are written as a pair (schema-level "both-or-neither" validator). The renderer's apply_inside_insert_anchors reads them and schedules the asset at <anchored insert's output_start_ms> + insert_offset_ms, bypassing the main-time shift. The FK has ON DELETE SET NULL as a safety net, but EditService.delete runs _repoint_anchored_children as the primary path when an INSERT is deleted — anchored children are moved to the splice point (visible duration preserved), the anchor fields are cleared, and the deleted insert's UUID is stripped from each child's insert_overlap_modes.

Per-asset crop reframe (issue #235). REPLACE / INSERT video and image asset edits default to letterbox when their source aspect differs from the project's target. Setting asset_crop_enabled=true opts that edit into a cover-crop reframe; the four columns persist UI-state (zoom + pan), not a frozen render-state crop rect — same convention as the main-video crop, so changing project aspect after persisting auto-adapts. The renderer's _maybe_crop_chain (workers/render/ffmpeg/concat/segments.py) builds the FFmpeg scale,crop,scale,setsar chain off _compute_crop_region (workers/render/ffmpeg/video_filters.py, Python port of frontend/shared/lib/cropUtils.ts:computeCropRegion). It bails to None when crop is disabled, target dims are unknown, or MediaFile.width/height are missing (e.g. audio sources) — the renderer falls back to today's letterbox path, so sources without probed dimensions round-trip without error.

Insert overlap modes. Per-INSERT override of how this asset behaves where its bar crosses or overlaps a specific INSERT: - 'merge' (default, also "missing entry"): asset plays through the insert region. - 'split': asset is split into pre/post pieces; insert region is skipped. - 'anchor': asset is clipped to play only inside the host insert (renderer caps end at insert.output_end_ms). Combined with the anchor fields above.

EditAction determines how the edit is applied:

The action field is set by the backend during analysis - the frontend just uses it without needing to know the business logic. Profanity edits use MUTE, all others use CUT.

The reason field contains the full LLM explanation and is useful for debugging. The reason_tag is a short raw-English identifier written by the detection pipeline (e.g. "Word gap", "refinement · multi_attempt", "adjusted"). The API returns it unchanged; the frontend maps known values to the analysis.edit_reason.* i18n catalog at render time via REASON_TAG_KEY_BY_RAW in features/analysis/hooks/useTransformedEdits.ts. Unknown values fall through verbatim.

Users can toggle active to include/exclude specific edits before rendering. Each edit belongs to the AnalysisRun that created it (including manual cuts, which belong to the active run at creation time).

AnalysisRun¶

Tracks a single analysis pipeline execution. Each re-analysis creates a new run — old runs' edits/captions/transcript are preserved.

class AnalysisRun:
    uuid: UUID
    project_id: UUID
    user_id: int
    status: AnalysisRunStatus  # pending, running, completed, failed
    pacing_level: int          # Settings used for this run
    false_start_sensitivity: int
    language: str | None
    transcript: str | None     # Owned by this run, copied to project when active
    transcript_words: list[dict] | None
    edit_count: int            # Snapshot counts from analysis time
    silence_count: int
    false_start_count: int
    credits_charged: int
    duration_ms: int | None

Run switching: project.active_run_id points to the current run. Edit, caption, and draft queries filter by this. POST /analysis-runs/{run_uuid}/activate switches runs — copies transcript to project, invalidates frontend caches.

Draft¶

A draft saves a specific configuration of edits for a project. Think of it as a "save state" that users can restore later.

class Draft:
    uuid: UUID
    project_id: UUID
    name: str
    edit_overrides: dict[str, EditOverride]  # Per-edit adjustments
    export_settings: dict                     # Resolution, aspect ratio, etc.
    is_default: bool                          # Auto-load this draft

The edit_overrides field stores adjustments to individual edits without modifying the original Edit records:

flowchart LR
    subgraph Original["Original Edit Records"]
        E1["Edit 1: 1000-2500ms, active"]
        E2["Edit 2: 5000-6200ms, active"]
    end

    subgraph Draft["Draft Overrides"]
        O1["Edit 1: start_ms=1200"]
        O2["Edit 2: active=false"]
    end

    subgraph Result["Applied State"]
        R1["Edit 1: 1200-2500ms, active"]
        R2["Edit 2: 5000-6200ms, inactive"]
    end

    E1 --> O1 --> R1
    E2 --> O2 --> R2

class EditOverride:
    active: bool         # Override active state
    start_ms: int | None # Override start time
    end_ms: int | None   # Override end time

Export¶

An export is a rendered video. Each export captures a snapshot of the edits at render time.

class Export:
    uuid: UUID
    project_id: UUID
    draft_id: UUID | None         # Source draft (optional)
    name: str
    edit_snapshot: dict           # Frozen copy of active edits
    settings_snapshot: dict       # Frozen export settings
    storage_key: str | None       # R2 path to rendered video
    status: ExportStatus          # pending, processing, complete, failed
    duration_ms: int | None       # Final video duration
    file_size_bytes: int | None

The snapshots mean you can keep editing while a render is in progress - it uses the frozen state.

Supporting Entities¶

MediaItemGroup¶

Organizational folders for library-scoped media items, with optional AI instructions describing when/how to use their members:

class MediaItemGroup:
    uuid: UUID
    user_id: int                      # FK to User (CASCADE)
    name: str
    description: str | None
    default_instructions: str | None  # AI hint for when to use members
    is_default: bool                   # Auto-include in projects
    is_pinned: bool                    # Show at top of list
    display_order: int

MediaItemGroupMembership¶

Junction table for the many-to-many MediaItem–MediaItemGroup relationship:

class MediaItemGroupMembership:
    media_item_id: UUID   # FK to MediaItem (CASCADE delete), part of composite PK
    group_id: UUID        # FK to MediaItemGroup (CASCADE delete), part of composite PK
    added_at: datetime

This lets a media item belong to multiple groups simultaneously. "Copy to group" creates a membership link rather than duplicating the item.

flowchart TB
    A[MediaItem: Logo.png] --> M1[Membership]
    A --> M2[Membership]
    M1 --> G1[Brand Assets]
    M2 --> G2[Intro Templates]

Canvas-media across the pipeline¶

The canvas-media model is read and written end to end:

• Upload + artifacts. MediaService handles presign/confirm/multipart/delete for MediaFile + MediaItem; the media pipeline worker (workers/download/media_pipeline/) does content-addressable sha256 dedup, artifact generation, and proxy encoding.
• Structure. CanvasService owns Canvas and the Edit canvas columns — CRUD plus source-time mutation workflows — at /api/v1/projects/{uuid}/canvases.
• Grouping. MediaItemGroup / MediaItemGroupMembership have backend CRUD + membership (MediaService grouping methods + /media-groups endpoints).
• Analysis. The analysis pipeline reads Canvas — it builds its source-time spine from the canvas sequence and emits run-scoped, source-time edits (cuts/mutes, asset placements, captions) carrying no canvas_id; it never creates or mutates canvas rows (structure is the editor's job).
• Render. The render pipeline reads the Canvas SINGLE spine for its main-video source (load_canvas_spine_for_render: SINGLE canvases in sequence order → their CANVAS_FILL Edit → MediaItem → MediaFile) and resolves b-roll from MediaItem (load_media_items_for_render), both against STORAGE_BUCKET_MEDIA. Full canvas-walking composition (SPLIT / TALKING_HEAD dual-panel / PIP) is still deferred — only the single-canvas main-video spine plus media b-roll are live in render today.

CaptionLine¶

Editable caption/subtitle lines generated from transcript:

class CaptionLine:
    uuid: UUID
    project_id: UUID              # FK to Project
    sequence: int                 # Order in transcript
    text: str                     # Editable caption text
    original_text: str            # Original from transcript
    start_ms: int                 # Start time
    end_ms: int                   # End time

Caption lines are generated from transcript words with configurable max_words per line (3-5 for vertical video, 7-10 for horizontal). Users can edit text while original_text preserves the original for comparison.

AnalysisPreset¶

Saved analysis settings that users can reuse across projects:

class AnalysisPreset:
    uuid: UUID
    user_id: int
    name: str
    is_default: bool              # Auto-apply this preset
    pacing_level: int             # 0-100, silence removal aggressiveness
    false_start_sensitivity: int  # 0-100, false start detection
    language: str | None          # ISO code (en, pt-BR) or null for auto
    audio_clean: bool             # Enable noise reduction + LUFS normalization
    audio_censorship: str         # 'none', 'mute', 'bleep' - profanity handling
    caption_censorship: bool      # Replace profanity with asterisks in captions
    director_notes: str | None    # Free-text AI instructions

Audio Clean Processing:

When audio_clean is enabled, exports include two audio processing stages:

Noise reduction runs first to avoid amplifying background noise during normalization.

Audio Censorship Options:

Users can have up to 5 presets. One can be marked as default.

PreviewPreset¶

Saved preview/export styling settings:

class PreviewPreset:
    uuid: UUID
    user_id: int
    name: str
    is_default: bool

    # Format settings
    format: str | None            # "W:H" aspect ratio (e.g. "16:9", "9:16", "1:1", "4:3", "5:4"). None = original.
    background: str               # Letterbox color (#000000)

    # Caption styling
    caption_style: CaptionStyle   # default, minimal, bold
    caption_font: str             # sans, serif, mono (generic family ids)
    caption_size: int             # Font size in px (12-144)
    caption_position: CaptionPosition  # top, center, bottom
    caption_color: str            # Text color (#FFFFFF)
    caption_length: CaptionLength # short, medium, long (words per line)

    # Main video transforms
    video_flip_h: bool            # Flip main video horizontally
    video_flip_v: bool            # Flip main video vertically

Allows users to save and quickly switch between different export configurations.

format field semantics. Stored as a "W:H" string (e.g. "16:9", "9:16", "1:1", "4:3", "5:4") or None meaning "Original" (keep the source video's native ratio). Validated at the API boundary against FORMAT_RATIO_PATTERN = r"^[1-9]\d{0,1}:[1-9]\d{0,1}$" in modules/preview_preset/constants.py. The pattern rejects zero sides (prevents a 0:0 reaching any future downstream consumer that would divide by it) and caps each side at 99 so stored values stay ≤ 5 characters (no log pollution) and within the range of real video aspect ratios.

Was previously a 3-value enum (FormatRatio = {LANDSCAPE, PORTRAIT, SQUARE}). Widened because the mobile FormatPanel lets users pick custom ratios, and saving a preset on a custom ratio would 422. The enum and its PG type formatratio have been removed.

Security boundary. This field is UI metadata only — it round-trips between the DB and React state to restore previewAspectRatio on preset load. It does NOT reach FFmpeg arguments, shell commands, filesystem paths, or any other external consumer. If a future change adds such a consumer, the FORMAT_RATIO_PATTERN validation must be re-evaluated for that context.

Subscriber¶

Newsletter subscriber record. Independent of User — newsletter signups can predate or replace account creation, so the same email may appear in both tables.

class Subscriber:
    uuid: UUID
    email: str                              # unique
    locale: str                             # 'en' | 'pt' | 'es' (defaults to DEFAULT_LOCALE; seeded from Accept-Language at signup)
    status: SubscriberStatus                # pending | confirmed | unsubscribed
    confirmation_token: str | None          # double-opt-in token
    confirmation_token_generated_at: datetime | None
    confirmation_email_send_attempts: int   # cron-bumped on each re-enqueue
    confirmed_at: datetime | None
    unsubscribed_at: datetime | None

Locale flow. POST /newsletter/subscribe reads Accept-Language (via get_request_locale) and stores it on the row. The confirmation email task and the confirm/unsubscribe success redirects (/{locale}/newsletter/{confirmed,unsubscribed} on the landing site, English unprefixed at root per Astro's prefixDefaultLocale: false) both consume subscriber.locale — so a signup in Portuguese sees Portuguese email copy and lands on /pt/newsletter/confirmed.

Lifecycle invariants. Pending rows whose confirmation_token_generated_at is older than UNCONFIRMED_SUBSCRIBER_TTL_DAYS = 30 are hard-deleted by _cleanup_unconfirmed_subscribers (GDPR Art. 5 storage limitation — abandoned signup is PII without a current legal basis). Pending rows past STUCK_NEWSLETTER_PENDING_MINUTES = 30 with confirmation_email_send_attempts < MAX_NEWSLETTER_CONFIRMATION_ATTEMPTS = 3 are re-enqueued by _recover_pending_newsletter_subscribers; each successful enqueue bumps the attempts counter and resets confirmation_token_generated_at (gives the user a fresh 24h confirm window and prevents immediate re-match next sweep).

Billing & Entitlements¶

Payment¶

Tracks Stripe Checkout Sessions and payment records.

UserEntitlement¶

Flexible access control — grants credits, tier access, or features to users.

EntitlementTransaction¶

Authoritative append-only ledger for all credit events. quantity_used on UserEntitlement is a denormalized cache rebuildable from this table via rebuild_user_balance().

Common Patterns¶

UUIDs Everywhere¶

All entities use UUIDs as public identifiers. Internal integer IDs exist but are never exposed via the API:

# Good - use UUID in API responses
{"uuid": "550e8400-e29b-41d4-a716-446655440000", ...}

# Bad - never expose internal IDs
{"id": 42, ...}

Soft Deletion¶

Entities aren't truly deleted - they're marked with deleted_at and is_deleted:

class PersistentDeletion:
    deleted_at: datetime | None
    is_deleted: bool = False

Queries filter out deleted records by default.

Timestamps¶

All entities track creation and update times:

class TimestampSchema:
    created_at: datetime
    updated_at: datetime | None

Key Files¶

← Architecture Overview API Endpoints →