Data model
Every PostgreSQL table, its key columns and how the tables relate.
Six tables, defined as SQLAlchemy models in backend/app/models/. Primary keys are UUID strings generated in Python.
Crow's-foot ERD with foreign keys and ON DELETE behaviour.
users
| Column | Type | Notes |
|---|---|---|
id | string (UUID) | Primary key |
email | string | Unique, indexed |
username | string | Unique, indexed |
hashed_password | string | bcrypt |
role | enum USER / ADMIN | Database default USER, indexed |
is_active | boolean | Default true; inactive users cannot sign in or refresh |
is_verified | boolean | Default false; set by email verification |
created_at, updated_at | timestamps |
videos
The central table. It holds editorial metadata, the processing state machine and storage paths.
Prop
Type
Two columns describe two different things and are easy to confuse:
statusis editorial: has an admin published it?processing_statusis technical: how far has the pipeline got?
A video can be published while still transcoding; the player shows "still processing" until manifest_url is set.
processing_status values
Include the re-queue edge (failed → queued) used for manual retries.
| Value | Progress shown | Set by |
|---|---|---|
uploading | 5% | Model default |
queued | 15% | Upload handlers |
preparing | 25% | prepare_video |
generating_storyboard | 35% | generate_storyboard |
transcoding | 50% | transcode_quality |
aggregating | 60% | on_transcode_complete |
segmenting | 70% | segment_videos |
creating_manifest | 80% | create_manifest |
uploading_to_storage | 90% | upload_to_minio |
finalizing | 95% | finalize_processing |
completed | 100% | finalize_processing |
failed | 0% | Any stage, or the pipeline's failure handler |
The progress numbers are fixed per stage (utils/video_helpers.py), not measured.
refresh_tokens
| Column | Notes |
|---|---|
user_id | → users.id, ON DELETE CASCADE |
token_hash | SHA-256 of the refresh JWT, indexed |
expires_at | |
is_revoked | Set by logout and password reset |
email_verification_tokens and password_reset_tokens
Both have the same shape: user_id (cascade delete), token, expires_at, used, created_at. Verification tokens are UUIDs valid for 24 hours; reset tokens are 6-digit codes valid for 1 hour.
tus_uploads
One row per resumable upload session.
| Column | Notes |
|---|---|
upload_id | Primary key; the tus upload ID assigned in the pre-create hook |
user_id | → users.id |
video_id | → videos.id, set when the upload finishes |
object_key | Where the file landed in MinIO |
declared_size | Bytes, from the client |
status | created, completed or terminated |
created_at, completed_at |
Migrations
Alembic migrations live in backend/alembic/versions/ as a single linear chain. They are run manually (alembic upgrade head). At startup the API also calls create_all(), which creates any table that does not exist yet but never alters existing ones.
Design notes
- Status columns are strings, not database enums. Adding a pipeline stage needs no migration; the allowed values are enforced in Python (
ProcessingStatus). - Soft delete keeps rows (and their files) when an admin deletes a video; queries filter on
deleted_at IS NULL. - Paths, not URLs, are stored for every file, so the public host can change without touching data.