query builder quickfix

Added to test lists need to get list_weights_by_user tested

.env

@@ -75,3 +75,4 @@ TTS_HIFIGAN_SUPERRES_VOCODER_MODEL_FILENAME=hifigan_superres_twilight_33000.pt
 
 # Development key. Permissible to check in.
 NEWRELIC_API_KEY=c402be1c4c4216ec83d0cfc0851520cfFFFFNRAL
+STORYTELLER_ROOT="/home/tensor/code/storyteller/"

.gitattributes

@@ -1,5 +1,6 @@
 # Ignore certain code for Github language statistics
 # https://github.com/github/linguist/blob/master/docs/overrides.md
 old_code/trumpet-trumped.com/dictionary/cmudict-0.7b linguist-vendored
-old_code/voder-vocodes-1.0/frontends/admin/node_modules linguist-vendored
-windows/sqlite linguist-vendored
+old_code/voder-vocodes-1.0/frontends/admin/node_modules/** linguist-vendored
+test_data/html_scraping/** linguist-vendored
+windows/sqlite/** linguist-vendored

.github/dependabot.yml

@@ -0,0 +1,11 @@
+# To get started with Dependabot version updates, you'll need to specify which
+# package ecosystems to update and where the package manifests are located.
+# Please see the documentation for all configuration options:
+# https://docs.github.com/github/administering-a-repository/configuration-options-for-dependency-updates
+
+version: 2
+updates:
+  - package-ecosystem: "cargo" # See documentation for possible values
+    directory: "/" # Location of package manifests
+    schedule:
+      interval: "weekly"

.github/workflows/docker-publish-cpu.yml

@@ -9,6 +9,7 @@ on:
       # NB: Default-branch doesn't work, despite Github's documentation.
       #- $default-branch
       - master
+      - speedup-experiments
 env:
   IMAGE_NAME: storyteller-rust
 

.github/workflows/docker-publish-gpu.yml

@@ -9,6 +9,7 @@ on:
       # NB: Default-branch doesn't work, despite Github's documentation.
       #- $default-branch
       - master
+      - speedup-experiments
 env:
   IMAGE_NAME: storyteller-rust-gpu
 

.gitignore

@@ -2,18 +2,20 @@
 *secret*sh
 .env
 .env-aichatbot-secrets
+.env-elasticsearch-secrets
 .env-secrets
+_develop/tools/.secrets
+_develop/tools/db_secrets.toml
+crates/service/web/storyteller_web/config/storyteller-web.development-secrets.env
 db.secrets
 db_secrets.toml
+env-secrets
 production_database_secret_connect.sh
 production_database_secret_query.sh
 production_database_voice_clone_report_download.sh
 secrets.toml
 secrets.yaml
-_develop/tools/.secrets
-_develop/tools/db_secrets.toml
 twitch_secrets.toml
-crates/service/web/storyteller_web/config/storyteller-web.development-secrets.env
 
 # New env secrets (per-app)
 *-secrets.env
@@ -28,7 +30,7 @@ crates/lib/sqlite_queries/target/
 *.swp
 *~
 
-# IntelliJ/CLion noise
+# IntelliJ/CLion/RustRover noise
 .idea/
 
 # Python noise

.idea/dictionaries/bt.xml

@@ -3,6 +3,8 @@
     <words>
       <w>arpabet</w>
       <w>backfill</w>
+      <w>canonicalized</w>
+      <w>canonicalizing</w>
       <w>cashapp</w>
       <w>cheermote</w>
       <w>cidrs</w>
@@ -11,7 +13,9 @@
       <w>dalle</w>
       <w>demuxer</w>
       <w>dereferencing</w>
+      <w>downloaders</w>
       <w>downvote</w>
+      <w>encodec</w>
       <w>fakeyou</w>
       <w>ffprobe</w>
       <w>greenlight</w>
@@ -22,11 +26,14 @@
       <w>hparams</w>
       <w>huggingface</w>
       <w>lipsync</w>
+      <w>mochi</w>
+      <w>nestedness</w>
       <w>pathbuf</w>
       <w>replayable</w>
       <w>retryably</w>
       <w>scrollback</w>
       <w>softvc</w>
+      <w>spammy</w>
       <w>sql's</w>
       <w>symphonia</w>
       <w>tacotron</w>

Cargo.toml

@@ -1,4 +1,5 @@
 [workspace]
+resolver = "2"
 
 members = [
   # Types: common strongly-typed tokens, ids, payloads, etc.
@@ -11,42 +12,55 @@ members = [
   # CLI tools for operations, etc.
   "crates/cli/database_backup",
   "crates/cli/database_cleanup",
+  "crates/cli/dev_database_seed",
+  "crates/cli/dev_upload_media_file",
+  "crates/cli/elasticsearch-cli",
+  "crates/cli/kubectl-pod-cleanup",
+  "crates/cli/lru-delete-models",
   "crates/cli/scrape_test",
 
   # Shared libraries
   "crates/lib/actix_helpers",
   "crates/lib/api_clients/cloud_storage",
-  #"crates/lib/api_clients/fakeyou_client",
   "crates/lib/api_clients/google_drive_common",
   "crates/lib/api_clients/twitch_common",
   "crates/lib/bootstrap",
   "crates/lib/caching/memory_caching",
   "crates/lib/caching/redis_caching",
   "crates/lib/concurrency",
+  "crates/lib/cookies",
   "crates/lib/crockford",
   "crates/lib/datetimes",
   "crates/lib/deprecated/config", # TODO(bt, 2023-02-13): Deprecate or repurpose
   "crates/lib/deprecated/container_common", # TODO(bt, 2023-02-13): Pull apart and rehome pieces
   "crates/lib/deprecated/http_server_common", # TODO(bt, 2023-03-20): Move useful pieces to actix_helpers
-  "crates/lib/envvar",
+  "crates/lib/easyenv", # NB: Vendored version of https://github.com/echelon/easyenv.rs that we'll improve.
+  "crates/lib/elasticsearch_schema",
+  "crates/lib/email_sender",
   "crates/lib/errors",
   "crates/lib/files/archives",
   "crates/lib/files/filesys",
   "crates/lib/files/hashing",
+  "crates/lib/files/images",
   "crates/lib/files/media",
   "crates/lib/files/mimetypes",
   "crates/lib/idempotency",
   "crates/lib/jobs_common",
+  "crates/lib/logging",
   "crates/lib/mysql_queries",
+  "crates/lib/password",
   "crates/lib/redis_common",
   "crates/lib/server_environment",
-  #"crates/lib/sqlite_queries",
   "crates/lib/sqlx_mysql_helpers",
+  "crates/lib/storyteller_root",
   "crates/lib/subprocess_common",
+  "crates/lib/testing",
   "crates/lib/tts_common",
   "crates/lib/url_config",
   "crates/lib/user_input_common",
   "crates/lib/web_scrapers",
+  #"crates/lib/api_clients/fakeyou_client",
+  #"crates/lib/sqlite_queries",
 
   # Programs: Desktop apps
   #"crates/programs/aichatbot-sidecar",
@@ -54,6 +68,7 @@ members = [
   # Services: API servers, jobs, workers, etc.
   "crates/service/job/analytics_job",
   "crates/service/job/download_job",
+  "crates/service/job/email_sender_job",
   "crates/service/job/inference_job",
   "crates/service/job/tts_download_job",
   "crates/service/job/tts_inference_job",
@@ -71,4 +86,7 @@ members = [
 
   # Vendored code
   "crates/vendor/newrelic-telemetry-sdk-rust",
+
+  # Vendored code
+  "my-workspace-hack",
 ]

Cranky.toml

@@ -39,6 +39,9 @@ deny = [
   "clippy::redundant_field_names", # Lint: Duplicate names is a burden when reading: you wonder why they're dupes.
   "clippy::redundant_static_lifetimes", # Lint: feels more modern to elide static lifetimes
 
+  # Rust
+  "unused_imports", # These are easy to automatically cull
+
   # Possible:
   # "clippy::ptr_arg",
   # "clippy::too_many_lines" (note: this is configurable)

_develop/docs/local_development/general_setup.md

@@ -9,6 +9,7 @@ platforms:
 
 - Mac OS (Apple M2 silicon)
 - Ubuntu 22.04
+- Windows Subsystem for Linux (Ubuntu 22.04 distro) aka "WSL"
 
 The applications (binary targets) you might be interested in running include, but 
 are not limited to:
@@ -25,15 +26,25 @@ Setup Instructions
 Install Rust [using this guide](https://www.rust-lang.org/learn/get-started). If it asks, 
 you'll want "stable" Rust, not "nightly" Rust. If it doesn't ask, it defaults to "stable".
 
+For Windows Subsystem for Linux, you'll want the WSL version, not the `.exe` file!
+
 ### (2) Install the necessary libraries
 
-Mac: (Lost the list, but it should match Linux. Install Homebrew.)
+#### Mac
 
-Ubuntu 22.04:
+On an up-to-date M1/M2 Mac, there should be no libraries you need to install! Any exceptions 
+to this assumption should be documented here.
+
+#### Ubuntu 22.04 and WSL:
+
+If you're on Ubuntu or WSL, you'll need to install some libraries to build the Rust code: 
 
 ```bash
-sudo apt install \ 
-  jq \
+# If you haven't run apt before, you'll need to fetch the package list:
+sudo apt update
+
+# Install the dependencies
+sudo apt install jq \
   libmysqlclient-dev \
   libsqlite3-dev \
   pkgconf
@@ -43,35 +54,66 @@ sudo apt install \
 
 ### (3) Install MySQL server
 
-Mac:
+#### Mac
 
 ```bash
+# Install MySQL
 brew install mysql
+
+# Try to connect
+mysql -uroot
 ```
 
-If `mysql -uroot` fails, reboot the machine:
+In the event of failure to connect, try the following steps:
 
 ```bash
-sudo reboot now
+# If connection fails, start service
+brew services start mysql
+
+# Try to connect again 
+# If this fails, reboot the machine, eg. using `sudo reboot now`
+mysql -uroot
 ```
 
-Ubuntu 22.04:
+#### Ubuntu 22.04 and WSL
 
 ```bash
+# Install MySQL
 sudo apt install mysql-server
-```
 
+# Try to connect 
+# The default password is typically "root".
+sudo mysql -u root -p
+````
+
+In the event of failure to connect, try the following steps:
+
+```bash
+# If it failed, you may need to start MySQL (typically only on WSL, not on native Ubuntu)
+sudo service mysql start
+
+# You may also have to change some socket file permissions (again, typically only on WSL)
+sudo chmod g+rx /var/run/mysqld
+sudo usermod -aG mysql $USER
+newgrp mysql
+
+# Try to connect again
+# The default password is typically "root".
+# If this fails, try rebooting. If it still fails, read some of the suggestions at the bottom 
+# of this README.
+sudo mysql -u root -p
+```
 
 ### (4) Install a `storyteller` user and table in MySQL
 
-Connect to mysql:
+First, connect to MySQL:
 
 ```bash
 # If the following command asks for a password, the password is typically "root"
 sudo mysql -u root -p
 ```
 
-Once in MySQL, run the following:
+Once in MySQL, run the following to set up the database and user accounts:
 
 ```mysql
 use mysql;
@@ -84,47 +126,71 @@ Then verify access with `./dev_mysql_connect.sh`
 
 ### (5) Install Diesel CLI (for MySQL migrations):
 
+In order to run MySQL database migrations, you'll need to install the migration tool we use:
+
 ```bash
 cargo install diesel_cli \
   --no-default-features \
   --features mysql,sqlite
 ```
 
-As of this writing, Mac [has some issues with Diesel CLI](https://github.com/diesel-rs/diesel/issues/2605)
-and requires a few extra dependencies to be installed:
+In older Macs, there [have been some issues with Diesel CLI](https://github.com/diesel-rs/diesel/issues/2605)
+that require a few extra dependencies to be installed:
 
 ```bash
+# If you're on Mac and the above command didn't work, run the following and then retry:
 brew install libpq
 ```
 
 ### (6) Run the pending database migrations:
 
+You're ready to run the migrations:
+
 ```bash
 diesel migration run
 ```
 
-You might get a scary message about `"Encountered unknown type for Mysql: enum"` -- you can ignore this 
-error (see below).
+You might get a scary message about `"Encountered unknown type for Mysql: enum"` -- you can safely ignore this 
+error if you see it in isolation. It doesn't impact the migrations whatsoever. (See notes at the bottom of 
+the README for details.)
 
 ### (7) (optional) Install the SQLx CLI (if doing database development)
 
-```bash
-cargo install sqlx-cli --no-default-features --features rustls,mysql,sqlite
-```
+(You can probably skip this step unless you're a backend engineer.) 
+
+If (and only if) you're doing Rust service development that changes the MySQL queries, then you should install 
+SQLx utilities. This tool is used to cache the column types and query plans for CI, as SQLx statically verifies 
+raw MySQL queries against the schema and Rust types.
 
 We'll be using diesel to manage the migrations, but sqlx within the app to actually perform queries.
 Diesel is an immature ORM, which isn't a good tech bet, so we use sqlx as at-compile-time
-typesafe SQL.
+typesafe SQL. 
+
+(TODO: Reevaluate this assessment for 2023-2024 and beyond. Maybe we do want Diesel now.)
+
+```bash
+# NB(bt,2023-10-04): "--no-default-features" not working
+# cargo install sqlx-cli --no-default-features --features rustls,mysql,sqlite
+cargo install sqlx-cli --features rustls,mysql,sqlite
+```
 
 ### (8) Install hosts file:
 
-(This should be the same on Linux and Mac, but might differ for Windows.)
+(This is the same for Linux, Mac, and WSL.)
 
 If you're developing against the frontend, it'll target development domains (eg. `dev.fakeyou.com`) instead 
 of `127.0.0.1` or `localhost`. You can make your machine route domains to localhost by editing your hosts 
 file (located at `/etc/hosts`) to include the following configuration lines:
 
 ```
+# 1) Edit hosts file with your editor of choice
+#
+#  - sudo vi /etc/hosts
+#  - sudo nano /etc/hosts
+#
+# 2) Then paste:
+#
+
 127.0.0.1    dev.fakeyou.com
 127.0.0.1    api.dev.fakeyou.com
 127.0.0.1    devproxy.fakeyou.com
@@ -134,32 +200,136 @@ file (located at `/etc/hosts`) to include the following configuration lines:
 127.0.0.1    devproxy.storyteller.ai
 ```
 
-### (9a) Install Redis
+### (9) Install Redis
+
+#### Mac:
+
+```bash
+# Install Redis
+brew install redis
+
+# Start the service:
+brew services start redis
+```
+
+#### Ubuntu 22.04 and WSL
 
 ```
+# Install Redis
 sudo apt install redis
+
+# Start the service (typically only needed on WSL):
+sudo service redis-server start
 ```
 
-### (9b) Run one or more applications:
-DO NOT CHECK THESE TWO FILES INTO THE REPO
-Ask Brandon for the .env-secrets and .storteller-web.development-secrets.env and place it in the root of the folder.
+### (10) Install Elasticsearch (optional)
+
+(You can most likely skip this step.)
+
+Mac: (TODO - haven't installed yet)
+
+WSL: (TODO - haven't installed yet)
+
+Ubuntu 22.04:
+
+Install:
+
+```bash
+curl -fsSL https://artifacts.elastic.co/GPG-KEY-elasticsearch | sudo gpg --dearmor -o /usr/share/keyrings/elastic.gpg
+
+echo "deb [signed-by=/usr/share/keyrings/elastic.gpg] https://artifacts.elastic.co/packages/7.x/apt stable main" | sudo tee -a /etc/apt/sources.list.d/elastic-7.x.list
+
+sudo apt update
+sudo apt install elasticsearch
+```
+
+Configure:
+
+```text
+# Edit /etc/elasticsearch/elasticsearch.yml
+# Set the following:
+network.host: localhost
+```
+
+Run:
+
+```bash
+sudo systemctl start elasticsearch
+sudo systemctl enable elasticsearch
+```
+
+Verify:
+
+```bash
+curl -X GET 'http://localhost:9200'
+```
+
+Install Kibana (prototyping UI):
+
+```bash
+# Install
+sudo apt-get install kibana
+
+# Run
+sudo /bin/systemctl daemon-reload
+sudo /bin/systemctl enable kibana.service
+sudo systemctl start kibana.service
+
+# Access the Kibana Status Page:
+# http://localhost:5601/status
+
+# Access the query console UI:
+# Click Sidebar > Management > Dev Tools > Console
+# http://localhost:5601/
+```
+
+Extra reading on setting up Elasticsearch: 
+
+- [Guide 1](https://www.digitalocean.com/community/tutorials/how-to-install-and-configure-elasticsearch-on-ubuntu-22-04)
+- [Guide 2](https://www.elastic.co/guide/en/elasticsearch/reference/current/deb.html)
+- [Kibana](https://www.elastic.co/guide/en/kibana/current/deb.html)
+
+### (11) Run one or more applications:
 
 To start the HTTP API server,
 
 ```bash
 cargo run --bin storyteller-web
 ```
+
 Note that this compiles and runs the "development" binary. It's faster and easier to debug than the
 optimized "production" build. To build a fully optimized production release,
 run `cargo build --release --bin storyteller-web` . Note that this will take much longer.
 
-DO NOT CHECK THESE TWO FILES INTO THE REPO
+You may notice that this might not work right off the bat. You may need to specify some environment variables 
+and supply some development secrets.
 
-Ask Brandon for the .download-job.development-secrets.env and place it in
-crates/service/job/download_job/config
+Secrets: 
 
-Ask Brandon for the .inference-job.development-secrets.env and place it in
-crates/service/job/inference_job/config
+- Global secrets (ask Brandon for these): 
+  - Put `.env-secrets` in the root `storyteller-rust` directory.
+  
+- Per app secrets (ask Brandon for these):
+  - Put `storteller-web.development-secrets.env` in `crates/service/web/storyteller-web/config`
+  - Put `inference-job.development-secrets.env` in `crates/service/job/inference_job/config`
+  - Put `donwload-job.development-secrets.env` in `crates/service/job/download_job/config`
+
+**DO NOT CHECK THESE SECRET FILES INTO THE REPO!**
+
+Environment config: 
+
+You may need to set local development paths for your environment. You can set these in `.env-secrets` too,
+so that they won't be updated for other engineers.
+
+```
+# this should be the directory containing all of the "storyteller" projects:
+STORYTELLER_ROOT = "/home/tensor/code/storyteller" # change this
+
+# The following are entirely optional, but if you want to fine tune where each project lives, you may do so:
+STORYTELLER_FRONTEND = "/somewhere/else/storyteller-frontend"
+STORYTELLER_ML = "storyteller-ml"
+STORYTELLER_RUST = "/Users/bt/Development/storyteller-rust"
+```
 
 To download some ML models, run:
 
@@ -184,6 +354,11 @@ cargo run --bin tts-inference-job
 Again, note that the configurations pointing to the ML monorepo must be set up for each application.
 Additionally, some development secrets may be needed (ask the team to share).
 
+Seeding the Database with Some Values
+-------------------------------------
+For voice designer you might want some pre-seeded audio that will hit the google cloud bucket, it will require env-secrets ask Brandon.
+cargo run --bin dev-database-seed -- --bucket
+
 Solutions to Common Setup Problems 
 -----------------------------------
 

_elasticsearch/index_definitions/tts_models_v1.json

@@ -0,0 +1,24 @@
+{
+  "settings" : {
+    "index.number_of_shards" : 3,
+    "index.number_of_replicas" : 2
+  },
+  "mappings" : {
+    "properties" : {
+      "token" : { "type" : "keyword" },
+
+      "title" : { "type" : "search_as_you_type" },
+
+      "ietf_language_tag" : { "type" : "keyword" },
+      "ietf_primary_language_subtag" : { "type" : "keyword" },
+
+      "creator_username" : { "type" : "keyword" },
+      "creator_display_name" : { "type" : "keyword" },
+      "creator_user_token" : { "type" : "keyword" },
+      "creator_set_visibility" : { "type" : "keyword" },
+
+      "created_at" : { "type" : "date" },
+      "updated_at" : { "type" : "date" }
+    }
+  }
+}

_sql/actions/generic_inference_jobs.sql

@@ -10,4 +10,9 @@ select count(*) from generic_inference_jobs where status = 'pending';
 
 update generic_inference_jobs
 set status = 'dead'
-where status IN ('pending', 'started', 'attempt_failed');
+where status IN ('pending', 'started', 'attempt_failed');
+
+update generic_inference_jobs
+set status = 'dead'
+where status IN ('pending', 'started', 'attempt_failed')
+and maybe_model_type IN ('sad_talker', 'so_vits_svc');

_sql/actions/media_files_face_animator.sql

@@ -0,0 +1,13 @@
+-- noinspection SqlDialectInspectionForFile
+-- noinspection SqlNoDataSourceInspectionForFile
+-- noinspection SqlResolveForFile
+
+-- Select recent videos
+select m.token,
+       u.username
+from media_files as m
+left outer join users as u
+on u.token = m.maybe_creator_user_token
+where m.origin_product_category = 'face_animator'
+order by m.id desc
+limit 50;

_sql/actions/truncate.sql

@@ -0,0 +1,11 @@
+-- noinspection SqlDialectInspectionForFile
+-- noinspection SqlNoDataSourceInspectionForFile
+-- noinspection SqlResolveForFile
+
+truncate zs_voices;
+truncate zs_voice_datasets;
+truncate zs_voice_dataset_samples;
+truncate media_files;
+truncate media_uploads;
+truncate generic_inference_jobs;
+

_sql/actions/voice_conversion_models.sql

@@ -302,7 +302,46 @@ where token IN (
     'vcm_p7m9jw1krrkr',
     'vcm_x7v3m9mcvten',
     'vcm_6r057m7594bv',
-    'vcm_wkm9e42d3spm'
+    'vcm_wkm9e42d3spm',
+    'vcm_cvz78weydh57',
+    'vcm_7g5q89m75n1p',
+    'vcm_fg3175bxy7kj',
+    'vcm_a7cejk4ng5nh',
+    'vcm_dywsh786nmp7',
+    'vcm_8q3xkanjt79v',
+    'vcm_vkgardaf2she',
+    'vcm_900cj3s8f073',
+    'vcm_ah1ffqbrzzrj',
+    'vcm_81x4c144phe8',
+    'vcm_89ghs14h2gpr',
+    'vcm_30jfz8hqdq05',
+    'vcm_hrxhg4d13n6j',
+    'vcm_58spen1nr492',
+    'vcm_6qzhqded58bq',
+    'vcm_wyfd0ft3b1dk',
+    'vcm_rhhz8sgrxg4w',
+    'vcm_9amtxj95fwpe',
+    'vcm_5thjhwepk2rs',
+    'vcm_p4xp9e34ypka',
+    'vcm_1bknrg4gqm18',
+    'vcm_4rxa4qp1ek7y',
+    'vcm_71pgq78k1y3b',
+    'vcm_7h3p6vn9ts4n',
+    'vcm_7w2akqddh758',
+    'vcm_9ytxkwqk00se',
+    'vcm_avyzqxctreb8',
+    'vcm_bxz11j5g503k',
+    'vcm_byxtz4gs6m25',
+    'vcm_ch8xjth7wm5a',
+    'vcm_dg57xngqxdp0',
+    'vcm_eb6htakr2y44',
+    'vcm_k5hkg05p5f19',
+    'vcm_kmhs1a6n97ez',
+    'vcm_krvgbm1wc2xv',
+    'vcm_m3sjae12xw66',
+    'vcm_vn1qqvy0t9bn',
+    'vcm_w6hz0tbmmmd3',
+    'vcm_zmxv2k1q9jd8'
 );
 
 

_sql/analytics/generic_inference/generic_inference_jobs.sql

@@ -2,6 +2,14 @@
 -- noinspection SqlNoDataSourceInspectionForFile
 -- noinspection SqlResolveForFile
 
+-- See how many jobs of each time were created in the last period
+select
+    distinct maybe_model_type as model_type,
+             count(*) as created
+from generic_inference_jobs
+where created_at > (CURDATE() - INTERVAL 60 MINUTE)
+group by maybe_model_type;
+
 -- See how long recent jobs are taking
 select
     id,
@@ -15,6 +23,19 @@ and success_execution_millis IS NOT NULL
 order by id desc
 limit 50;
 
+-- See who is uploading SadTalker jobs
+select
+    maybe_creator_user_token,
+    users.username as maybe_creator_username,
+    creator_ip_address,
+    count(*) as attempts
+from generic_inference_jobs
+left outer join users
+on users.token = generic_inference_jobs.maybe_creator_user_token
+where maybe_model_type = 'sad_talker'
+and status IN ('pending', 'started', 'complete_failure', 'attempt_failed')
+group by maybe_creator_user_token, maybe_creator_username, creator_ip_address
+order by attempts desc;
 
 -- Detailed report on most recent jobs, ordered by worst performing.
 -- TODO: Determine if the problem is in downloading models. Make sure the job timer doesn't include
@@ -49,7 +70,8 @@ from (
                  m.token = jobs.maybe_input_source_token
          where
              jobs.status != 'pending'
-        AND jobs.created_at > NOW() - INTERVAL 20 MINUTE
+         AND jobs.created_at > NOW() - INTERVAL 20 MINUTE
+         AND jobs.maybe_model_type IN ('so_vits_svc')
          order by id desc
              limit 200
      ) as t

_sql/analytics/tts/tts_inference_jobs.sql

@@ -35,7 +35,7 @@ order by attempts desc
 select *
 from tts_inference_jobs
 where status = 'pending'
-and creator_ip_address = '186.188.28.73'
+and creator_ip_address = '191.102.120.182'
 limit 1;
 
 -- Top voices in requests

_sql/analytics/users/password_resets.sql

@@ -0,0 +1,18 @@
+-- noinspection SqlDialectInspectionForFile
+-- noinspection SqlNoDataSourceInspectionForFile
+-- noinspection SqlResolveForFile
+
+-- A look into the most recent password resets.
+-- User account age, password change version, etc.
+SELECT
+    e.destination_email_address AS email,
+    u.username,
+    u.created_at,
+    u.updated_at,
+    u.password_version,
+    u.version
+FROM email_sender_jobs AS e
+JOIN users AS u
+    on u.token = e.maybe_destination_user_token
+order by e.id desc
+limit 100;

_sql/analytics/voice_conversion/voice_conversion_models.sql

@@ -24,10 +24,9 @@ select
     vc.id,
     vc.token,
     vc.model_type,
-    vc.title,
+    SUBSTRING(vc.title, 1, 35) as title,
     u.username,
-    vc.original_download_url,
-    vc.created_at,
+    SUBSTRING(vc.original_download_url, 1, 35) as url,
     vc.updated_at
 from voice_conversion_models as vc
 left join users AS u on

_sql/migrations/2023-05-23-090105_password_resets/up.sql

@@ -2,6 +2,20 @@
 -- noinspection SqlNoDataSourceInspectionForFile
 -- noinspection SqlResolveForFile
 
+-- Per-user password resets.
+-- Password resets are valid until any of the following are true:
+--
+--   1) database NOW() > password_rests.expires_at
+--   2) password_rests.is_redeemed == TRUE
+--   3) users.password_version > password_resets.current_password_version
+--
+-- When creating a new password reset, send the user an email with the `secret_key` in the URL.
+-- The user will have to supply their username and/or email address, plus the secret key to
+-- redeem the password reset.
+--
+-- Also when creating the password hash, be sure to set `expires_at` to be "NOW() + 3 hours", or
+-- some other small, reasonable time frame.
+--
 CREATE TABLE password_resets (
   -- Not used for anything except replication.
   id BIGINT(20) NOT NULL AUTO_INCREMENT,
@@ -22,7 +36,7 @@ CREATE TABLE password_resets (
   -- reset is no longer valid.
   current_password_version INT NOT NULL DEFAULT 0,
 
-  -- Username is a lookup key; display_name allows the user to add custom case.
+  -- Whether the password reset has been consumed.
   is_redeemed BOOLEAN NOT NULL DEFAULT false,
 
   -- For abuse tracking.

_sql/migrations/2023-09-26-070239_zs_voices/down.sql

@@ -0,0 +1,5 @@
+-- noinspection SqlDialectInspectionForFile
+-- noinspection SqlNoDataSourceInspectionForFile
+-- noinspection SqlResolveForFile
+
+drop table zs_voices;

_sql/migrations/2023-09-26-070239_zs_voices/up.sql

@@ -0,0 +1,154 @@
+-- noinspection SqlDialectInspectionForFile
+-- noinspection SqlNoDataSourceInspectionForFile
+-- noinspection SqlResolveForFile
+
+-- This table contains both zero shot TTS voices (speaker embeddings) and zero shot V2V voices (speaker embeddings).
+-- These are the zero shot voices that are usable by our users.
+CREATE TABLE zs_voices (
+  -- Not used for anything except replication.
+  id BIGINT(20) NOT NULL AUTO_INCREMENT,
+
+  -- Effective "primary key" for joins, API, etc. (PUBLIC)
+  token VARCHAR(32) NOT NULL,
+
+  -- NB: No idempotency token.
+  -- Store it in another table to prevent duplicate record creation from rapid user inputs.
+
+  -- ========== ZERO SHOT FOREIGN KEYS AND STATE==========
+
+  -- The zero shot dataset that was used to create this voice.
+  -- The dataset will only be accessible to the original author of the voice.
+  -- The original author can create multiple voices from the same dataset.
+  dataset_token VARCHAR(32) NOT NULL,
+
+  -- When datasets are edited, we bump their version.
+  -- This tells us which version of the dataset created this voice (not that we should preserve
+  -- record of which samples are in which version.)
+  -- We can surface this to the users as well.
+  dataset_version INT NOT NULL DEFAULT 0,
+
+  -- ========== MODEL DETAILS ==========
+
+  -- The class or category of model/algorithm this voice is intended for.
+  --   * 'tts' for zero shot TTS
+  --   * 'vc' for zero shot voice conversion
+  --   * 'pitch' for pitch correction models (hypothetical)
+  --   * (Since this isn't an enum, the list of values can be expanded.)
+  model_category VARCHAR(16) NOT NULL,
+
+  -- The model used for the voice:
+  --   * 'vall-e-x' for Vall-E-X
+  --   * 'ns2vc' for NaturalSpeech2 voice conversion (hypothetical)
+  --   * (Since this isn't an enum, the list of values can be expanded.)
+  model_type VARCHAR(16) NOT NULL,
+
+  -- The version of the model.
+  -- If we deploy new model weights for the model, we may need to regenerate
+  -- all of the corresponding speaker embeddings. This lets us know which voices
+  -- are the old version.
+  -- In some cases, we may need to preserve, hide, or roll back to older versions.
+  model_version INTEGER UNSIGNED NOT NULL DEFAULT 0,
+
+  -- What kind of features these are: spectrogram, encodec, etc.
+  -- Technically we shouldn't need this, as the 2-tuple (model_type, model_version) implicitly knows
+  -- what its features should be, but this is probably useful for our own analysis and may be easier
+  -- to deal with.
+  --
+  -- Example values:
+  --   * 'spectrogram'
+  --   * 'encodec'
+  --   * (Since this isn't an enum, the list of values can be expanded.)
+  encoding_type VARCHAR(16) NOT NULL,
+
+  -- ========== VOICE DETAILS ==========
+
+  -- The "title" of the voice. This can be the name and additional information as supplied by the user.
+  title VARCHAR(255) NOT NULL,
+
+  -- The full IETF BCP47 language tag (eg. en, en-US, es-419, ja-JP, pt, etc.)
+  -- The wide varchar space is because these language tags can become cumbersome, eg "hy-Latn-IT-arevela"
+  ietf_language_tag VARCHAR(64) NOT NULL DEFAULT 'en',
+
+  -- The IETF BCP47 language tag's primary language subtag (eg. "es-419" becomes "es" and "en-US" becomes "en")
+  ietf_primary_language_subtag VARCHAR(12) NOT NULL DEFAULT 'en',
+
+  -- TODO: misc speaker metadata
+  -- We'll want rich metadata for search
+  -- series_name VARCHAR(255) DEFAULT NULL,
+  -- speaker_name VARCHAR(255) DEFAULT NULL,
+  -- speaker_gender VARCHAR(255) DEFAULT NULL,
+  -- speaker_age VARCHAR(255) DEFAULT NULL,
+  -- speaker_voice_style VARCHAR(255) DEFAULT NULL,
+
+  -- ========== STORAGE DETAILS ==========
+
+  -- The hash for the bucket directory that contains the original upload
+  -- eg. /speaker_vectors/{bucket_hash}/{voice_model_version}.bin
+  bucket_hash  VARCHAR(32) NOT NULL,
+
+  -- ========== CREATOR DETAILS AND PREFERENCES ==========
+
+  -- Foreign key to user
+  maybe_creator_user_token VARCHAR(32) DEFAULT NULL,
+
+  -- Based on a cookie sent by the frontend.
+  -- We'll save this even if the user is logged in.
+  maybe_creator_anonymous_visitor_token VARCHAR(32) DEFAULT NULL,
+
+  -- For abuse tracking.
+  -- Wide enough for IPv4/6
+  creator_ip_address VARCHAR(40) NOT NULL,
+
+  -- The creator can set a desired visibility for their data.
+  -- This does not always apply to every upload type.
+  -- Additionally, some upload types may require moderator approval prior
+  -- to being publicly listed, and this field has no bearing on that.
+  -- NB: DO NOT CHANGE THE ORDER OF THE ENUM VALUES DURING SCHEMA MIGRATIONS.
+  creator_set_visibility ENUM(
+    'public',
+    'hidden',
+    'private'
+  ) NOT NULL DEFAULT 'public',
+
+  -- The synthetic id associated with this result.
+  -- These ids are incremented on a per-user basis to help users
+  -- sequence their own work. They serve no other purpose.
+  maybe_creator_synthetic_id BIGINT(20) DEFAULT NULL,
+
+  -- ========== MODERATION DETAILS ==========
+
+  -- The last moderator that made changes last.
+  -- Fine details of moderator actions are kept in a separate `audit_logs` table.
+  maybe_mod_user_token VARCHAR(32) DEFAULT NULL,
+
+  -- ========== TIMESTAMPS ==========
+
+  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
+  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+
+  -- If this is removed by a mod or the creator.
+  user_deleted_at TIMESTAMP NULL,
+  mod_deleted_at TIMESTAMP NULL,
+
+  -- ========== VECTOR CLOCK ==========
+
+  -- Incremented with every update.
+  version INT NOT NULL DEFAULT 0,
+
+  -- INDICES --
+  PRIMARY KEY (id),
+  UNIQUE KEY (token),
+
+  -- TODO: These are not correct. Fix these before landing / running migrations.
+  KEY index_model_category (model_category),
+  KEY index_model_type (model_type),
+  KEY index_model_type_and_version (model_type, model_version),
+  KEY index_encoding_type (encoding_type),
+  KEY index_ietf_primary_language_subtag (ietf_primary_language_subtag),
+  KEY fk_maybe_creator_user_token (maybe_creator_user_token),
+  KEY fk_maybe_mod_user_token (maybe_mod_user_token),
+  KEY index_creator_ip_address (creator_ip_address),
+  KEY index_user_deleted_at (user_deleted_at),
+  KEY index_mod_deleted_at (mod_deleted_at)
+
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

_sql/migrations/2023-09-26-070244_zs_voice_datasets/down.sql

@@ -0,0 +1,5 @@
+-- noinspection SqlDialectInspectionForFile
+-- noinspection SqlNoDataSourceInspectionForFile
+-- noinspection SqlResolveForFile
+
+drop table zs_voice_datasets;

_sql/migrations/2023-09-26-070244_zs_voice_datasets/up.sql

@@ -0,0 +1,106 @@
+-- noinspection SqlDialectInspectionForFile
+-- noinspection SqlNoDataSourceInspectionForFile
+-- noinspection SqlResolveForFile
+
+-- This table describes a zero shot voice in the process of being created (or later reused / edited / enhanced)
+-- These are not usable voices until their vector embeddings have been created, and this table does not serve
+-- the production voice list.
+--
+-- Multiple voices can be created from a single "dataset" as the data set is mutable over time. Users can add or
+-- remove new samples to the data set at any time.
+CREATE TABLE zs_voice_datasets (
+  -- Not used for anything except replication.
+  id BIGINT(20) NOT NULL AUTO_INCREMENT,
+
+  -- Effective "primary key" for joins, API, etc. (PUBLIC)
+  token VARCHAR(32) NOT NULL,
+
+  -- NB: No idempotency token.
+  -- Store it in another table to prevent duplicate record creation from rapid user inputs.
+
+  -- ========== VOICE DETAILS ==========
+
+  -- The "title" of the voice. This can be the name and additional information as supplied by the user.
+  -- When the voice embeddings are created, this will be copied to the produced "voice" (embeddings) record.
+  title VARCHAR(255) NOT NULL,
+
+  -- The full IETF BCP47 language tag (eg. en, en-US, es-419, ja-JP, pt, etc.)
+  -- The wide varchar space is because these language tags can become cumbersome, eg "hy-Latn-IT-arevela"
+  ietf_language_tag VARCHAR(64) NOT NULL DEFAULT 'en',
+
+  -- The IETF BCP47 language tag's primary language subtag (eg. "es-419" becomes "es" and "en-US" becomes "en")
+  ietf_primary_language_subtag VARCHAR(12) NOT NULL DEFAULT 'en',
+
+  -- TODO: misc speaker metadata
+  -- We'll want rich metadata for search
+  -- When the voice embeddings are created, this will be copied to the produced "voice" (embeddings) record.
+  -- We don't want a hard link between the dataset and the produced voice as the voice is static once
+  -- created (with the exception of regenerating embeddings), but the dataset my drift as the user experiments,
+  -- adds/removes samples, etc.
+  -- series_name VARCHAR(255) DEFAULT NULL,
+  -- speaker_name VARCHAR(255) DEFAULT NULL,
+  -- speaker_gender VARCHAR(255) DEFAULT NULL,
+  -- speaker_age VARCHAR(255) DEFAULT NULL,
+  -- speaker_voice_style VARCHAR(255) DEFAULT NULL,
+
+  -- ========== CREATOR DETAILS AND PREFERENCES ==========
+
+  -- Foreign key to user
+  maybe_creator_user_token VARCHAR(32) DEFAULT NULL,
+
+  -- Based on a cookie sent by the frontend.
+  -- We'll save this even if the user is logged in.
+  maybe_creator_anonymous_visitor_token VARCHAR(32) DEFAULT NULL,
+
+  -- For abuse tracking.
+  -- Wide enough for IPv4/6
+  creator_ip_address VARCHAR(40) NOT NULL,
+
+  -- The creator can set a desired visibility for their data.
+  -- This does not always apply to every upload type.
+  -- Additionally, some upload types may require moderator approval prior
+  -- to being publicly listed, and this field has no bearing on that.
+  -- NB: DO NOT CHANGE THE ORDER OF THE ENUM VALUES DURING SCHEMA MIGRATIONS.
+  creator_set_visibility ENUM(
+    'public',
+    'hidden',
+    'private'
+  ) NOT NULL DEFAULT 'public',
+
+  -- The synthetic id associated with this result.
+  -- These ids are incremented on a per-user basis to help users
+  -- sequence their own work. They serve no other purpose.
+  maybe_creator_synthetic_id BIGINT(20) DEFAULT NULL,
+
+  -- ========== MODERATION DETAILS ==========
+
+  -- The last moderator that made changes last.
+  -- Fine details of moderator actions are kept in a separate `audit_logs` table.
+  maybe_mod_user_token VARCHAR(32) DEFAULT NULL,
+
+  -- ========== TIMESTAMPS ==========
+
+  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
+  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+
+  -- If this is removed by a mod or the creator.
+  user_deleted_at TIMESTAMP NULL,
+  mod_deleted_at TIMESTAMP NULL,
+
+  -- ========== VECTOR CLOCK ==========
+
+  -- Incremented with every update.
+  version INT NOT NULL DEFAULT 0,
+
+  -- INDICES --
+  PRIMARY KEY (id),
+  UNIQUE KEY (token),
+
+  KEY index_ietf_primary_language_subtag (ietf_primary_language_subtag),
+  KEY fk_maybe_creator_user_token (maybe_creator_user_token),
+  KEY fk_maybe_mod_user_token (maybe_mod_user_token),
+  KEY index_creator_ip_address (creator_ip_address),
+  KEY index_user_deleted_at (user_deleted_at),
+  KEY index_mod_deleted_at (mod_deleted_at)
+
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

_sql/migrations/2023-09-26-070252_zs_voice_dataset_samples/down.sql

@@ -0,0 +1,5 @@
+-- noinspection SqlDialectInspectionForFile
+-- noinspection SqlNoDataSourceInspectionForFile
+-- noinspection SqlResolveForFile
+
+drop table zs_voice_dataset_samples;

_sql/migrations/2023-09-26-070252_zs_voice_dataset_samples/up.sql

@@ -0,0 +1,84 @@
+-- noinspection SqlDialectInspectionForFile
+-- noinspection SqlNoDataSourceInspectionForFile
+-- noinspection SqlResolveForFile
+
+-- These are the individual files in each dataset.
+-- Files can be added and removed from a dataset at any time.
+CREATE TABLE zs_voice_dataset_samples (
+  -- Not used for anything except replication.
+  id BIGINT(20) NOT NULL AUTO_INCREMENT,
+
+  -- Effective "primary key" for joins, API, etc. (PUBLIC)
+  token VARCHAR(32) NOT NULL,
+
+  -- NB: No idempotency token.
+  -- Store it in another table to prevent duplicate record creation from rapid user inputs.
+
+  -- ========== ZERO SHOT FOREIGN KEYS AND STATE==========
+
+  -- The zero shot dataset that was used to create this voice.
+  -- The dataset will only be accessible to the original author of the voice.
+  -- The original author can create multiple voices from the same dataset.
+  dataset_token VARCHAR(32) NOT NULL,
+
+  -- Foreign key to the uploaded media file.
+  -- There was an option of handling uploads and file management separately, but by electing to store the dataset
+  -- samples in the "media_files" table, they can be reused by our other code, queries, machinery, etc.
+  -- Note that this requires that the "media_files" record must be an audio (not video) file (which our code will
+  -- enforce), and that it is given a category of "voice_zero_shot" so it isn't co-mingled with other uploads/outputs
+  media_file_token VARCHAR(32) NOT NULL,
+
+  -- Some data sets require raw transcripts for the audio.
+  -- If required, they'll live here.
+  maybe_raw_transcription TEXT DEFAULT NULL,
+
+  -- A way to order the data set samples
+  -- We don't necessarily need to reimplement file reordering.
+  -- order_id INT DEFAULT NULL,
+
+  -- ========== CREATOR DETAILS AND PREFERENCES ==========
+
+  -- Foreign key to user
+  maybe_creator_user_token VARCHAR(32) DEFAULT NULL,
+
+  -- Based on a cookie sent by the frontend.
+  -- We'll save this even if the user is logged in.
+  maybe_creator_anonymous_visitor_token VARCHAR(32) DEFAULT NULL,
+
+  -- For abuse tracking.
+  -- Wide enough for IPv4/6
+  creator_ip_address VARCHAR(40) NOT NULL,
+
+  -- ========== MODERATION DETAILS ==========
+
+  -- The last moderator that made changes last.
+  -- Fine details of moderator actions are kept in a separate `audit_logs` table.
+  maybe_mod_user_token VARCHAR(32) DEFAULT NULL,
+
+  -- ========== TIMESTAMPS ==========
+
+  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
+  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+
+  -- If this is removed by a mod or the creator.
+  user_deleted_at TIMESTAMP NULL,
+  mod_deleted_at TIMESTAMP NULL,
+
+  -- ========== VECTOR CLOCK ==========
+
+  -- Incremented with every update.
+  version INT NOT NULL DEFAULT 0,
+
+  -- INDICES --
+  PRIMARY KEY (id),
+  UNIQUE KEY (token),
+
+  KEY fk_dataset_token (dataset_token),
+  KEY fk_media_file_token (media_file_token),
+  KEY fk_maybe_creator_user_token (maybe_creator_user_token),
+  KEY fk_maybe_mod_user_token (maybe_mod_user_token),
+  KEY index_creator_ip_address (creator_ip_address),
+  KEY index_user_deleted_at (user_deleted_at),
+  KEY index_mod_deleted_at (mod_deleted_at)
+
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

_sql/migrations/2023-09-29-033922_zs_voice_included_samples/down.sql

@@ -0,0 +1,5 @@
+-- noinspection SqlDialectInspectionForFile
+-- noinspection SqlNoDataSourceInspectionForFile
+-- noinspection SqlResolveForFile
+
+drop table zs_voice_included_samples;

_sql/migrations/2023-09-29-033922_zs_voice_included_samples/up.sql

@@ -0,0 +1,26 @@
+-- noinspection SqlDialectInspectionForFile
+-- noinspection SqlNoDataSourceInspectionForFile
+-- noinspection SqlResolveForFile
+
+-- In order to rebuild any "zs_voice", we need to know which dataset samples were included.
+-- Since users can change the dataset at any time after a voice was originally created, these
+-- records are generated to retain a record of which samples fed into a given voice.
+-- These records will probably not be used for any product features, just as a means to study
+-- poor-performing voices, perform migrations, etc.
+CREATE TABLE zs_voice_included_samples (
+  -- Not used for anything except replication.
+  id BIGINT(20) NOT NULL AUTO_INCREMENT,
+
+  -- First member of composite effective "primary key"
+  -- The "zs_voice" that was created.
+  voice_token VARCHAR(32) NOT NULL,
+
+  -- Second member of composite effective "primary key"
+  -- The "zs_voice_dataset_sample" that was used.
+  dataset_sample_token VARCHAR(32) NOT NULL,
+
+  -- INDICES --
+  PRIMARY KEY (id),
+  UNIQUE KEY (voice_token, dataset_sample_token)
+
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

_sql/migrations/2023-10-01-212745_alter_media_files_add_bucket_extension/down.sql

@@ -0,0 +1,5 @@
+-- noinspection SqlDialectInspectionForFile
+-- noinspection SqlNoDataSourceInspectionForFile
+-- noinspection SqlResolveForFile
+
+ALTER TABLE media_files DROP COLUMN maybe_public_bucket_extension;

_sql/migrations/2023-10-01-212745_alter_media_files_add_bucket_extension/up.sql

@@ -0,0 +1,7 @@
+-- noinspection SqlDialectInspectionForFile
+-- noinspection SqlNoDataSourceInspectionForFile
+-- noinspection SqlResolveForFile
+
+ALTER TABLE media_files
+    ADD COLUMN maybe_public_bucket_extension VARCHAR(16) DEFAULT NULL
+    AFTER public_bucket_directory_hash;

_sql/migrations/2023-10-04-062503_alter_media_files_add_bucket_prefix/down.sql

@@ -0,0 +1,5 @@
+-- noinspection SqlDialectInspectionForFile
+-- noinspection SqlNoDataSourceInspectionForFile
+-- noinspection SqlResolveForFile
+
+ALTER TABLE media_files DROP COLUMN maybe_public_bucket_prefix;

_sql/migrations/2023-10-04-062503_alter_media_files_add_bucket_prefix/up.sql

@@ -0,0 +1,7 @@
+-- noinspection SqlDialectInspectionForFile
+-- noinspection SqlNoDataSourceInspectionForFile
+-- noinspection SqlResolveForFile
+
+ALTER TABLE media_files
+    ADD COLUMN maybe_public_bucket_prefix VARCHAR(16) DEFAULT NULL
+    AFTER public_bucket_directory_hash;

_sql/migrations/2023-10-11-235742_alter_media_files_add_origin_product_category/down.sql

@@ -0,0 +1,5 @@
+-- noinspection SqlDialectInspectionForFile
+-- noinspection SqlNoDataSourceInspectionForFile
+-- noinspection SqlResolveForFile
+
+ALTER TABLE media_files DROP COLUMN origin_product_category;

_sql/migrations/2023-10-11-235742_alter_media_files_add_origin_product_category/up.sql

@@ -0,0 +1,10 @@
+-- noinspection SqlDialectInspectionForFile
+-- noinspection SqlNoDataSourceInspectionForFile
+-- noinspection SqlResolveForFile
+
+ALTER TABLE media_files
+    ADD COLUMN origin_product_category VARCHAR(16) NOT NULL DEFAULT "unknown"
+    AFTER origin_category;
+
+ALTER TABLE media_files
+    ADD INDEX index_origin_product_category (origin_product_category);

_sql/migrations/2023-10-11-235800_alter_generic_inference_jobs_add_frontend_failure_category/down.sql

@@ -0,0 +1,5 @@
+-- noinspection SqlDialectInspectionForFile
+-- noinspection SqlNoDataSourceInspectionForFile
+-- noinspection SqlResolveForFile
+
+ALTER TABLE generic_inference_jobs DROP COLUMN frontend_failure_category;

_sql/migrations/2023-10-11-235800_alter_generic_inference_jobs_add_frontend_failure_category/up.sql

@@ -0,0 +1,7 @@
+-- noinspection SqlDialectInspectionForFile
+-- noinspection SqlNoDataSourceInspectionForFile
+-- noinspection SqlResolveForFile
+
+ALTER TABLE generic_inference_jobs
+    ADD COLUMN frontend_failure_category VARCHAR(32) DEFAULT NULL
+    AFTER internal_debugging_failure_reason;

_sql/migrations/2023-10-13-093804_alter_zs_voice_dataset_samples_add_idempotency/down.sql

@@ -0,0 +1,5 @@
+-- noinspection SqlDialectInspectionForFile
+-- noinspection SqlNoDataSourceInspectionForFile
+-- noinspection SqlResolveForFile
+
+ALTER TABLE zs_voice_dataset_samples DROP COLUMN uuid_idempotency_token;

_sql/migrations/2023-10-13-093804_alter_zs_voice_dataset_samples_add_idempotency/up.sql

@@ -0,0 +1,10 @@
+-- noinspection SqlDialectInspectionForFile
+-- noinspection SqlNoDataSourceInspectionForFile
+-- noinspection SqlResolveForFile
+
+ALTER TABLE zs_voice_dataset_samples
+    ADD COLUMN uuid_idempotency_token VARCHAR(36) NOT NULL
+    AFTER token;
+
+ALTER TABLE zs_voice_dataset_samples
+    ADD UNIQUE INDEX unique_index_uuid_idempotency_token (uuid_idempotency_token);

_sql/migrations/2023-10-18-061204_alter_password_resets_add_unique_index/down.sql

@@ -0,0 +1,5 @@
+-- noinspection SqlDialectInspectionForFile
+-- noinspection SqlNoDataSourceInspectionForFile
+-- noinspection SqlResolveForFile
+
+ALTER TABLE password_resets DROP INDEX unique_user_token_and_secret_key;

_sql/migrations/2023-10-18-061204_alter_password_resets_add_unique_index/up.sql

@@ -0,0 +1,6 @@
+-- noinspection SqlDialectInspectionForFile
+-- noinspection SqlNoDataSourceInspectionForFile
+-- noinspection SqlResolveForFile
+
+ALTER TABLE password_resets
+    ADD UNIQUE INDEX unique_user_token_and_secret_key (user_token, secret_key);

_sql/migrations/2023-10-24-020831_alter_password_resets_ip_address_redemption_nullable/down.sql

@@ -0,0 +1,6 @@
+-- noinspection SqlDialectInspectionForFile
+-- noinspection SqlNoDataSourceInspectionForFile
+-- noinspection SqlResolveForFile
+
+ALTER TABLE password_resets
+    MODIFY ip_address_redemption VARCHAR(40) NOT NULL;

_sql/migrations/2023-10-24-020831_alter_password_resets_ip_address_redemption_nullable/up.sql

@@ -0,0 +1,6 @@
+-- noinspection SqlDialectInspectionForFile
+-- noinspection SqlNoDataSourceInspectionForFile
+-- noinspection SqlResolveForFile
+
+ALTER TABLE password_resets
+    MODIFY ip_address_redemption VARCHAR(40) DEFAULT NULL;

_sql/migrations/2023-10-24-044500_alter_generic_inference_jobs_add_status_values/down.sql

@@ -0,0 +1,13 @@
+-- noinspection SqlDialectInspectionForFile
+-- noinspection SqlNoDataSourceInspectionForFile
+-- noinspection SqlResolveForFile
+
+ALTER TABLE generic_inference_jobs
+    MODIFY status ENUM(
+        'pending',
+        'started',
+        'complete_success',
+        'complete_failure',
+        'attempt_failed',
+        'dead'
+  ) NOT NULL DEFAULT 'pending';

_sql/migrations/2023-10-24-044500_alter_generic_inference_jobs_add_status_values/up.sql

@@ -0,0 +1,16 @@
+-- noinspection SqlDialectInspectionForFile
+-- noinspection SqlNoDataSourceInspectionForFile
+-- noinspection SqlResolveForFile
+
+-- Add two states to allow us to distinguish user-cancelled jobs and system-cancelled jobs.
+ALTER TABLE generic_inference_jobs
+    MODIFY status ENUM(
+        'pending',
+        'started',
+        'complete_success',
+        'complete_failure',
+        'attempt_failed',
+        'dead',
+        'cancelled_by_user',
+        'cancelled_by_system'
+) NOT NULL DEFAULT 'pending';

_sql/migrations/2023-11-12-005723_alter_password_resets_rename_table/down.sql

@@ -0,0 +1,5 @@
+-- noinspection SqlDialectInspectionForFile
+-- noinspection SqlNoDataSourceInspectionForFile
+-- noinspection SqlResolveForFile
+
+RENAME TABLE user_password_resets TO password_resets;

_sql/migrations/2023-11-12-005723_alter_password_resets_rename_table/up.sql

@@ -0,0 +1,5 @@
+-- noinspection SqlDialectInspectionForFile
+-- noinspection SqlNoDataSourceInspectionForFile
+-- noinspection SqlResolveForFile
+
+RENAME TABLE password_resets TO user_password_resets;

_sql/migrations/2023-11-12-005759_alter_password_resets_to_unsigned_ints/down.sql

@@ -0,0 +1,11 @@
+-- noinspection SqlDialectInspectionForFile
+-- noinspection SqlNoDataSourceInspectionForFile
+-- noinspection SqlResolveForFile
+
+-- revert
+ALTER TABLE user_password_resets
+    MODIFY COLUMN version INT NOT NULL DEFAULT 0;
+
+-- revert
+ALTER TABLE user_password_resets
+    MODIFY COLUMN current_password_version INT NOT NULL DEFAULT 0;

_sql/migrations/2023-11-12-005759_alter_password_resets_to_unsigned_ints/up.sql

@@ -0,0 +1,9 @@
+-- noinspection SqlDialectInspectionForFile
+-- noinspection SqlNoDataSourceInspectionForFile
+-- noinspection SqlResolveForFile
+
+ALTER TABLE user_password_resets
+    MODIFY COLUMN current_password_version INT UNSIGNED NOT NULL DEFAULT 0;
+
+ALTER TABLE user_password_resets
+    MODIFY COLUMN version INT UNSIGNED NOT NULL DEFAULT 0;

_sql/migrations/2023-11-12-005851_alter_users_to_unsigned_ints/down.sql

@@ -0,0 +1,11 @@
+-- noinspection SqlDialectInspectionForFile
+-- noinspection SqlNoDataSourceInspectionForFile
+-- noinspection SqlResolveForFile
+
+-- revert
+ALTER TABLE users
+    MODIFY COLUMN password_version INT NOT NULL DEFAULT 0;
+
+-- revert
+ALTER TABLE users
+    MODIFY COLUMN version INT NOT NULL DEFAULT 0;

_sql/migrations/2023-11-12-005851_alter_users_to_unsigned_ints/up.sql

@@ -0,0 +1,10 @@
+-- noinspection SqlDialectInspectionForFile
+-- noinspection SqlNoDataSourceInspectionForFile
+-- noinspection SqlResolveForFile
+
+ALTER TABLE users
+    MODIFY COLUMN password_version INT UNSIGNED NOT NULL DEFAULT 0;
+
+ALTER TABLE users
+    MODIFY COLUMN version INT UNSIGNED NOT NULL DEFAULT 0;
+

_sql/migrations/2023-11-12-010940_alter_password_resets_secret_field_name/down.sql

@@ -0,0 +1,6 @@
+-- noinspection SqlDialectInspectionForFile
+-- noinspection SqlNoDataSourceInspectionForFile
+-- noinspection SqlResolveForFile
+
+ALTER TABLE user_password_resets
+    RENAME COLUMN public_reset_token TO secret_key;

_sql/migrations/2023-11-12-010940_alter_password_resets_secret_field_name/up.sql

@@ -0,0 +1,6 @@
+-- noinspection SqlDialectInspectionForFile
+-- noinspection SqlNoDataSourceInspectionForFile
+-- noinspection SqlResolveForFile
+
+ALTER TABLE user_password_resets
+    RENAME COLUMN secret_key TO public_reset_token;

_sql/migrations/2023-11-12-010954_alter_password_resets_change_composite_key/down.sql

@@ -0,0 +1,12 @@
+-- noinspection SqlDialectInspectionForFile
+-- noinspection SqlNoDataSourceInspectionForFile
+-- noinspection SqlResolveForFile
+
+ALTER TABLE user_password_resets
+  DROP INDEX public_reset_token;
+
+ALTER TABLE user_password_resets
+  ADD UNIQUE INDEX unique_user_token_and_secret_key (user_token, public_reset_token);
+
+ALTER TABLE user_password_resets
+  ADD INDEX index_secret_key (public_reset_token);

_sql/migrations/2023-11-12-010954_alter_password_resets_change_composite_key/up.sql

@@ -0,0 +1,12 @@
+-- noinspection SqlDialectInspectionForFile
+-- noinspection SqlNoDataSourceInspectionForFile
+-- noinspection SqlResolveForFile
+
+ALTER TABLE user_password_resets
+  DROP INDEX unique_user_token_and_secret_key;
+
+ALTER TABLE user_password_resets
+  ADD UNIQUE INDEX public_reset_token (public_reset_token);
+
+ALTER TABLE user_password_resets
+  DROP INDEX index_secret_key;

_sql/migrations/2023-11-14-055228_model_weights/down.sql

@@ -0,0 +1,5 @@
+-- noinspection SqlDialectInspectionForFile
+-- noinspection SqlNoDataSourceInspectionForFile
+-- noinspection SqlResolveForFile
+
+drop table model_weights;

_sql/migrations/2023-11-14-055228_model_weights/up.sql

@@ -0,0 +1,247 @@
+-- noinspection SqlDialectInspectionForFile
+-- noinspection SqlNoDataSourceInspectionForFile
+-- noinspection SqlResolveForFile
+
+-- Every type of fine tuned model that users can upload.
+CREATE TABLE model_weights (
+  -- Not used for anything except replication.
+  id BIGINT(20) NOT NULL AUTO_INCREMENT,
+
+  -- Effective "primary key" (PUBLIC)
+  token VARCHAR(32) NOT NULL,
+
+  -- ========== WEIGHTS CLASSIFICATION ==========
+
+  -- The exact type of weights
+  -- Currently supported values:
+  --   * 'hifigan_tt2' - hifigan for Tacotron2; TTS vocoder
+  --   * 'rvc_v2' - RVC (v2); voice conversion
+  --   * 'sd_1.5' - Stable Diffusion 1.5; image generation
+  --   * 'sdxl' - Stable Diffusion XL; image generation
+  --   * 'so_vits_svc' - SVC; voice conversion
+  --   * 'tt2' - Tacotron2; TTS
+  weights_type VARCHAR(32) NOT NULL,
+
+  -- The broader category of the weights
+  -- Currently supported values:
+  --   * 'image_generation' (SD1.5, SDXL)
+  --   * 'text_to_speech' (TT2)
+  --   * 'vocoder' (Hifigan)
+  --   * 'voice_conversion' (RVCv2, SVC)
+  weights_category VARCHAR(32) NOT NULL,
+
+
+  -- ========== MODEL DESCRIPTION ==========
+
+  -- The "name" of the model, which might be complicated.
+  title VARCHAR(255) NOT NULL,
+
+  -- A link to a possible thumbnail (TODO: Build thumbnail support reusing media_files)
+  maybe_thumbnail_token VARCHAR(32) DEFAULT NULL,
+
+  -- The description of the model in markdown.
+  description_markdown TEXT NOT NULL,
+
+  -- Generated HTML (not user-editable).
+  description_rendered_html TEXT NOT NULL,
+
+
+  -- ========== CREATOR DETAILS ==========
+
+  -- The person that created the model.
+  creator_user_token VARCHAR(32) NOT NULL,
+
+  -- For abuse tracking.
+  -- Wide enough for IPv4/6.
+  -- IP address of creator at time of creation.
+  creator_ip_address VARCHAR(40) NOT NULL,
+
+  -- (THIS MIGHT NOT BE USED)
+  -- NB: DO NOT SORT!
+  -- THIS MUST MATCH THE RESPECTIVE JOBS TABLE.
+  creator_set_visibility ENUM(
+    'public',
+    'hidden',
+    'private'
+  ) NOT NULL DEFAULT 'public',
+
+  -- The synthetic id associated with this model.
+  -- These ids are incremented on a per-user basis to help users
+  -- sequence their own work. They serve no other purpose.
+  -- "weights" synthetic ids are incremented over all media files
+  -- creator_weights_synthetic_id BIGINT(20) DEFAULT NULL,
+
+  -- "weights_type" synthetic ids are incremented over a given weight type
+  -- creator_weights_type_synthetic_id BIGINT(20) DEFAULT NULL,
+
+  -- The last user to edit the model. This could be the creator or a moderator.
+  -- Check the future audit logs table for more info.
+  maybe_last_update_user_token VARCHAR(32) DEFAULT NULL,
+
+
+  -- ========== IMMUTABLE PROVENANCE AND METADATA ==========
+
+  -- Where the file was originally downloaded (if it was downloaded)
+  original_download_url VARCHAR(512) DEFAULT NULL,
+
+  -- The filename that was used at upload time (if available)
+  original_filename VARCHAR(255) DEFAULT NULL,
+
+  -- File characteristics
+  file_size_bytes INT(10) NOT NULL DEFAULT 0,
+
+  -- Checksum of the original weights file
+  -- SHA2 hash [SHA2 = CHAR(64), SHA1 = CHAR(40), MD5 = CHAR(32)]
+  -- Note that SHA2 is a hash family (SHA-228, SHA-256, SHA-384, SHA-512, SHA-512/224, SHA-512/256, ...)
+  -- and produces different lengths output depending on the choice of algorithm.
+  file_checksum_sha2 CHAR(64) NOT NULL,
+
+  -- ========== BUCKET STORAGE ==========
+
+  -- The hash for the bucket directory that contains the original upload
+  -- as well as any associated other files.
+  -- The bucket filename for the primary file (not including the directory path) is given by:
+  -- `[{maybe_private_bucket_prefix}]{private_bucket_hash}[{maybe_private_bucket_extension}]`
+  private_bucket_hash  VARCHAR(32) NOT NULL,
+
+  -- An optional prefix on the bucket filename.
+  -- If present, this will be prepended to the beginning of the bucket filename to access the file.
+  -- The bucket filename for the primary file (not including the directory path) is given by:
+  -- `[{maybe_private_bucket_prefix}]{private_directory_hash}[{maybe_private_bucket_extension}]`
+  maybe_private_bucket_prefix VARCHAR(16) DEFAULT NULL,
+
+  -- An optional appended extension on the bucket filename.
+  -- If present, this will be appended to the end of the bucket filename to access the file.
+  -- To allow for flexibility, this extension typically will contain a leading period if
+  -- the file needs it (eg ".mp4" rather than "mp4")!
+  -- The bucket filename for the primary file (not including the directory path) is given by:
+  -- `[{maybe_private_bucket_prefix}]{private_directory_hash}[{maybe_private_bucket_extension}]`
+  maybe_private_bucket_extension VARCHAR(16) DEFAULT NULL,
+
+
+  -- ========== MODERATION DETAILS ==========
+
+  -- Mods may have to approve of vc models for them to show up in a public index.
+  -- Models can be used by the author (or other parties that know the URL) before
+  -- they're approved, but unapproved models won't show up in public indices.
+  -- This is independent of other visibility controls (is_mod_disabled, deleted_at,
+  -- and creator_set_visibility)
+  -- is_public_listing_approved BOOLEAN DEFAULT NULL,
+
+  -- Extremely popular models may be locked from deletion or modification by users.
+  -- is_locked_from_user_modification BOOLEAN NOT NULL DEFAULT FALSE,
+
+
+  -- ========== RATING AND RANKING ==========
+
+  -- User ratings are cached on the model, but are *not* a source of truth.
+  -- Total count only includes "positive" and "negative" votes, not neutral ones.
+  cached_user_ratings_total_count INT(10) UNSIGNED NOT NULL DEFAULT 0,
+
+  -- Total positive
+  cached_user_ratings_positive_count INT(10) UNSIGNED NOT NULL DEFAULT 0,
+
+  -- Total negative
+  cached_user_ratings_negative_count INT(10) UNSIGNED NOT NULL DEFAULT 0,
+
+  -- User ratings are cached on the model, but are *not* a source of truth.
+  -- Total count only includes "positive" and "negative" votes, not neutral ones.
+  maybe_cached_user_ratings_ratio FLOAT,
+
+  -- When the cached ratings were last calculated and updated.
+  cached_user_ratings_last_updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
+
+
+  -- ========== MODEL PLACEMENT ==========
+
+  -- Whether the model features on the front of FakeYou
+  -- is_front_page_featured BOOLEAN NOT NULL DEFAULT FALSE,
+
+
+-- NB: I think this should actually move into polymorphic join tables, so that we don't create a telescopic list of
+-- columns.
+--
+--  -- ========== TTS MODEL METADATA ==========
+--
+--  -- This is for Tacotron2, specifically.
+--  -- The name of the type of pipeline to use, eg "legacy_fakeyou", "legacy_vocodes", "english_v1", etc.
+--  -- New pipelines may be added at any point in the future and the source of truth for them will mostly
+--  -- live in the `storyteller-ml` repository.
+--  -- Older models and brand new models will have this field set to null, and we'll infer which value to
+--  -- lazily backfill based on the creation date and language setting.
+--  -- tts_text_pipeline_type VARCHAR(64) DEFAULT NULL,
+--
+--  -- If true, multiply the mel outputs before being vocoded by a globally default constant.
+--  tts_use_default_mel_multiply_factor BOOLEAN NOT NULL DEFAULT FALSE,
+--
+--  -- If not null, multiply the mel outputs before being vocoded by this value.
+--  -- This is used instead of the default if `tts_use_default_mel_multiply_factor` is
+--  -- set (ie. `tts_use_default_mel_multiply_factor` is ignored and this custom value is used instead)
+--  maybe_tts_custom_mel_multiply_factor DOUBLE DEFAULT NULL,
+--
+--  -- If set, use a different pretrained vocoder.
+--  -- If not set, use the website default. (Currently HifiGan-SS)
+--  maybe_tts_default_pretrained_vocoder VARCHAR(64) DEFAULT NULL,
+--
+--  -- If set, we'll use a custom vocoder (from table vocoder_models) instead of a default vocoder.
+--  -- If not set, we'll use `maybe_default_pretrained_vocoder` (or the website default).
+--  maybe_tts_custom_vocoder_token VARCHAR(32) DEFAULT NULL,
+--
+--
+--  -- ========== VOICE CONVERSION MODEL METADATA ==========
+--
+--  -- RVC (v2) specific - whether the model has an index file associated with it
+--  -- These files improve the quality of the results.
+--  vc_has_index_file BOOLEAN NOT NULL DEFAULT FALSE,
+--
+--
+--  -- ========== VOCODER MODEL METADATA ==========
+--
+--  -- Whether the vocoder is recommended for use
+--  -- vocoder_is_staff_recommended BOOLEAN NOT NULL DEFAULT FALSE,
+--
+--
+--  -- ========== VOICE MODEL METADATA (TTS and Voice Conversion Models) ==========
+--
+--  -- The full IETF BCP47 language tag (eg. en, en-US, es-419, ja-JP, pt, etc.)
+--  -- (Not that it matters apart from categorization, since voice conversion is universal.)
+--  voice_ietf_language_tag VARCHAR(64) NOT NULL DEFAULT 'en',
+--
+--  -- The IETF BCP47 language tag's primary language subtag (eg. "es-419" becomes "es")
+--  -- (Not that it matters apart from categorization, since voice conversion is universal.)
+--  voice_ietf_primary_language_subtag VARCHAR(12) NOT NULL DEFAULT 'en',
+--
+--  -- RVC (v2) specific - whether the model has an index file associated with it
+--  -- These files improve the quality of the results.
+--  voice_has_index_file BOOLEAN NOT NULL DEFAULT FALSE,
+
+
+  -- ========== VECTOR CLOCK ==========
+
+  -- Incremented with every update.
+  version INT NOT NULL DEFAULT 0,
+
+
+  -- ========== TIMESTAMPS ==========
+
+  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
+  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+
+  -- If this is removed by a mod.
+  -- It completely disappears from the system.
+  user_deleted_at TIMESTAMP NULL,
+  mod_deleted_at TIMESTAMP NULL,
+
+  -- INDICES --
+  PRIMARY KEY (id),
+  UNIQUE KEY (token)
+  -- TODO:
+  -- KEY index_model_type (model_type),
+  -- KEY fk_maybe_vocoder_token (maybe_vocoder_token),
+  -- KEY fk_creator_user_token (creator_user_token),
+  -- KEY index_creator_set_visibility (creator_set_visibility),
+  -- KEY index_private_bucket_hash (private_bucket_hash),
+  -- KEY index_is_public_listing_approved (is_public_listing_approved),
+  -- KEY index_is_locked_from_user_modification (is_locked_from_user_modification)
+
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

_sql/migrations/2023-11-14-082433_model_weights_tts_details/down.sql

@@ -0,0 +1,5 @@
+-- noinspection SqlDialectInspectionForFile
+-- noinspection SqlNoDataSourceInspectionForFile
+-- noinspection SqlResolveForFile
+
+drop table model_weights_tts_details;

_sql/migrations/2023-11-14-082433_model_weights_tts_details/up.sql

@@ -0,0 +1,73 @@
+-- noinspection SqlDialectInspectionForFile
+-- noinspection SqlNoDataSourceInspectionForFile
+-- noinspection SqlResolveForFile
+
+-- Extra metadata on tts models
+CREATE TABLE model_weights_tts_details (
+  -- Not used for anything except replication.
+  id BIGINT(20) NOT NULL AUTO_INCREMENT,
+
+  -- Simultaneous primary and foreign key.
+  -- This should match a single record in the `model_weights` table.
+  -- This turns this table into a polymorphic extension table.
+  model_weights_token VARCHAR(32) NOT NULL,
+
+  -- ========== TTS MODEL METADATA ==========
+
+  -- The full IETF BCP47 language tag (eg. en, en-US, es-419, ja-JP, pt, etc.)
+  -- (Not that it matters apart from categorization, since voice conversion is universal.)
+  ietf_language_tag VARCHAR(64) NOT NULL DEFAULT 'en',
+
+  -- The IETF BCP47 language tag's primary language subtag (eg. "es-419" becomes "es")
+  -- (Not that it matters apart from categorization, since voice conversion is universal.)
+  ietf_primary_language_subtag VARCHAR(12) NOT NULL DEFAULT 'en',
+
+  -- This is for Tacotron2, specifically.
+  -- The name of the type of pipeline to use, eg "legacy_fakeyou", "legacy_vocodes", "english_v1", etc.
+  -- New pipelines may be added at any point in the future and the source of truth for them will mostly
+  -- live in the `storyteller-ml` repository.
+  -- Older models and brand new models will have this field set to null, and we'll infer which value to
+  -- lazily backfill based on the creation date and language setting.
+  -- tts_text_pipeline_type VARCHAR(64) DEFAULT NULL,
+
+  -- If true, multiply the mel outputs before being vocoded by a globally default constant.
+  use_default_mel_multiply_factor BOOLEAN NOT NULL DEFAULT FALSE,
+
+  -- If not null, multiply the mel outputs before being vocoded by this value.
+  -- This is used instead of the default if `tts_use_default_mel_multiply_factor` is
+  -- set (ie. `tts_use_default_mel_multiply_factor` is ignored and this custom value is used instead)
+  maybe_custom_mel_multiply_factor DOUBLE DEFAULT NULL,
+
+  -- If set, use a different pretrained vocoder.
+  -- If not set, use the website default. (Currently HifiGan-SS)
+  maybe_default_pretrained_vocoder VARCHAR(64) DEFAULT NULL,
+
+  -- If set, we'll use a custom vocoder (from table vocoder_models) instead of a default vocoder.
+  -- If not set, we'll use `maybe_default_pretrained_vocoder` (or the website default).
+  maybe_custom_vocoder_token VARCHAR(32) DEFAULT NULL,
+
+
+  -- ========== VECTOR CLOCK ==========
+
+  -- Incremented with every update.
+  version INT NOT NULL DEFAULT 0,
+
+
+  -- ========== TIMESTAMPS ==========
+
+  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
+  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+
+  -- INDICES --
+  PRIMARY KEY (id),
+  UNIQUE KEY (model_weights_token)
+  -- TODO:
+  -- KEY index_model_type (model_type),
+  -- KEY fk_maybe_vocoder_token (maybe_vocoder_token),
+  -- KEY fk_creator_user_token (creator_user_token),
+  -- KEY index_creator_set_visibility (creator_set_visibility),
+  -- KEY index_private_bucket_hash (private_bucket_hash),
+  -- KEY index_is_public_listing_approved (is_public_listing_approved),
+  -- KEY index_is_locked_from_user_modification (is_locked_from_user_modification)
+
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

_sql/migrations/2023-11-14-082446_model_weights_voice_conversion_details/down.sql

@@ -0,0 +1,5 @@
+-- noinspection SqlDialectInspectionForFile
+-- noinspection SqlNoDataSourceInspectionForFile
+-- noinspection SqlResolveForFile
+
+drop table model_weights_voice_conversion_details;

_sql/migrations/2023-11-14-082446_model_weights_voice_conversion_details/up.sql

@@ -0,0 +1,62 @@
+-- noinspection SqlDialectInspectionForFile
+-- noinspection SqlNoDataSourceInspectionForFile
+-- noinspection SqlResolveForFile
+
+-- Every type of fine tuned model that users can upload.
+CREATE TABLE model_weights_voice_conversion_details (
+  -- Not used for anything except replication.
+  id BIGINT(20) NOT NULL AUTO_INCREMENT,
+
+  -- Simultaneous primary and foreign key.
+  -- This should match a single record in the `model_weights` table.
+  -- This turns this table into a polymorphic extension table.
+  model_weights_token VARCHAR(32) NOT NULL,
+
+
+  -- ========== VOICE CONVERSION MODEL METADATA ==========
+
+  -- The full IETF BCP47 language tag (eg. en, en-US, es-419, ja-JP, pt, etc.)
+  -- (Not that it matters apart from categorization, since voice conversion is universal.)
+  ietf_language_tag VARCHAR(64) NOT NULL DEFAULT 'en',
+
+  -- The IETF BCP47 language tag's primary language subtag (eg. "es-419" becomes "es")
+  -- (Not that it matters apart from categorization, since voice conversion is universal.)
+  ietf_primary_language_subtag VARCHAR(12) NOT NULL DEFAULT 'en',
+
+  -- RVC (v2) specific - whether the model has an index file associated with it
+  -- These files improve the quality of the results.
+  has_index_file BOOLEAN NOT NULL DEFAULT FALSE,
+
+  -- Pitch algorithm to use by default
+  default_pitch_algorithm VARCHAR(64) DEFAULT NULL,
+
+
+  -- ========== VECTOR CLOCK ==========
+
+  -- Incremented with every update.
+  version INT NOT NULL DEFAULT 0,
+
+
+  -- ========== TIMESTAMPS ==========
+
+  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
+  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+
+  -- If this is removed by a mod.
+  -- It completely disappears from the system.
+  user_deleted_at TIMESTAMP NULL,
+  mod_deleted_at TIMESTAMP NULL,
+
+  -- INDICES --
+  PRIMARY KEY (id),
+  UNIQUE KEY (model_weights_token)
+  -- TODO:
+  -- KEY index_model_type (model_type),
+  -- KEY fk_maybe_vocoder_token (maybe_vocoder_token),
+  -- KEY fk_creator_user_token (creator_user_token),
+  -- KEY index_creator_set_visibility (creator_set_visibility),
+  -- KEY index_private_bucket_hash (private_bucket_hash),
+  -- KEY index_is_public_listing_approved (is_public_listing_approved),
+  -- KEY index_is_locked_from_user_modification (is_locked_from_user_modification)
+
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

_sql/migrations/2023-11-14-082452_model_weights_vocoder_details/down.sql

@@ -0,0 +1,5 @@
+-- noinspection SqlDialectInspectionForFile
+-- noinspection SqlNoDataSourceInspectionForFile
+-- noinspection SqlResolveForFile
+
+drop table model_weights_vocoder_details;

_sql/migrations/2023-11-14-082452_model_weights_vocoder_details/up.sql

@@ -0,0 +1,50 @@
+-- noinspection SqlDialectInspectionForFile
+-- noinspection SqlNoDataSourceInspectionForFile
+-- noinspection SqlResolveForFile
+
+-- Every type of fine tuned model that users can upload.
+CREATE TABLE model_weights_vocoder_details (
+  -- Not used for anything except replication.
+  id BIGINT(20) NOT NULL AUTO_INCREMENT,
+
+  -- Simultaneous primary and foreign key.
+  -- This should match a single record in the `model_weights` table.
+  -- This turns this table into a polymorphic extension table.
+  model_weights_token VARCHAR(32) NOT NULL,
+
+
+  -- ========== VOCODER MODEL METADATA ==========
+
+  -- Whether the vocoder is recommended for use
+  -- vocoder_is_staff_recommended BOOLEAN NOT NULL DEFAULT FALSE,
+
+
+  -- ========== VECTOR CLOCK ==========
+
+  -- Incremented with every update.
+  version INT NOT NULL DEFAULT 0,
+
+
+  -- ========== TIMESTAMPS ==========
+
+  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
+  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+
+  -- If this is removed by a mod.
+  -- It completely disappears from the system.
+  user_deleted_at TIMESTAMP NULL,
+  mod_deleted_at TIMESTAMP NULL,
+
+  -- INDICES --
+  PRIMARY KEY (id),
+  UNIQUE KEY (model_weights_token)
+  -- TODO:
+  -- KEY index_model_type (model_type),
+  -- KEY fk_maybe_vocoder_token (maybe_vocoder_token),
+  -- KEY fk_creator_user_token (creator_user_token),
+  -- KEY index_creator_set_visibility (creator_set_visibility),
+  -- KEY index_private_bucket_hash (private_bucket_hash),
+  -- KEY index_is_public_listing_approved (is_public_listing_approved),
+  -- KEY index_is_locked_from_user_modification (is_locked_from_user_modification)
+
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

_sql/migrations/2023-11-14-082638_model_weights_image_generation_details/down.sql

@@ -0,0 +1,5 @@
+-- noinspection SqlDialectInspectionForFile
+-- noinspection SqlNoDataSourceInspectionForFile
+-- noinspection SqlResolveForFile
+
+drop table model_weights_image_generation_details;

_sql/migrations/2023-11-14-082638_model_weights_image_generation_details/up.sql

@@ -0,0 +1,50 @@
+-- noinspection SqlDialectInspectionForFile
+-- noinspection SqlNoDataSourceInspectionForFile
+-- noinspection SqlResolveForFile
+
+-- Every type of fine tuned model that users can upload.
+CREATE TABLE model_weights_image_generation_details (
+  -- Not used for anything except replication.
+  id BIGINT(20) NOT NULL AUTO_INCREMENT,
+
+  -- Simultaneous primary and foreign key.
+  -- This should match a single record in the `model_weights` table.
+  -- This turns this table into a polymorphic extension table.
+  model_weights_token VARCHAR(32) NOT NULL,
+
+
+  -- ========== IMAGE GENERATION MODEL METADATA ==========
+
+  -- RVC (v2) specific - whether the model has an index file associated with it
+  -- These files improve the quality of the results.
+  vc_has_index_file BOOLEAN NOT NULL DEFAULT FALSE,
+
+  -- ========== VECTOR CLOCK ==========
+
+  -- Incremented with every update.
+  version INT NOT NULL DEFAULT 0,
+
+
+  -- ========== TIMESTAMPS ==========
+
+  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
+  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+
+  -- If this is removed by a mod.
+  -- It completely disappears from the system.
+  user_deleted_at TIMESTAMP NULL,
+  mod_deleted_at TIMESTAMP NULL,
+
+  -- INDICES --
+  PRIMARY KEY (id),
+  UNIQUE KEY (model_weights_token)
+  -- TODO:
+  -- KEY index_model_type (model_type),
+  -- KEY fk_maybe_vocoder_token (maybe_vocoder_token),
+  -- KEY fk_creator_user_token (creator_user_token),
+  -- KEY index_creator_set_visibility (creator_set_visibility),
+  -- KEY index_private_bucket_hash (private_bucket_hash),
+  -- KEY index_is_public_listing_approved (is_public_listing_approved),
+  -- KEY index_is_locked_from_user_modification (is_locked_from_user_modification)
+
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

_sql/migrations/2023-11-15-120921_email_sender_jobs/down.sql

@@ -0,0 +1,5 @@
+-- noinspection SqlDialectInspectionForFile
+-- noinspection SqlNoDataSourceInspectionForFile
+-- noinspection SqlResolveForFile
+
+DROP TABLE email_sender_jobs;

_sql/migrations/2023-11-15-120921_email_sender_jobs/up.sql

@@ -0,0 +1,160 @@
+-- noinspection SqlDialectInspectionForFile
+-- noinspection SqlNoDataSourceInspectionForFile
+-- noinspection SqlResolveForFile
+
+CREATE TABLE email_sender_jobs (
+
+  -- Not used for anything except replication.
+  id BIGINT(20) NOT NULL AUTO_INCREMENT,
+
+  -- Effective "primary key" (PUBLIC)
+  -- This is so the in-progress results can be looked up by the UI.
+  token VARCHAR(32) NOT NULL,
+
+  -- Idempotency token from client
+  -- This is so we don't submit duplicate jobs.
+  uuid_idempotency_token VARCHAR(36) NOT NULL,
+
+  -- ========== EMAIL TYPE AND ARGS ==========
+
+  -- Type of email (and the template) to use.
+  -- This is the most important part of the email job.
+  -- Possible values:
+  --  * password_reset
+  --  * welcome
+  email_category VARCHAR(32) NOT NULL,
+
+  -- Polymorphic arguments payload that depends on the type of email job.
+  -- TEXT = 65,535 bytes (64 KiB), ~= 4 bytes per UTF-8 character, ~ 16383 characters.
+  maybe_email_args TEXT DEFAULT NULL,
+
+  -- ========== EMAIL DESTINATION ==========
+
+  -- Email we're sending to (always authoritative over the user token
+  -- record, even if user token is set)
+  destination_email_address VARCHAR(255) DEFAULT NULL,
+
+  -- Maybe the user record this is being sent to.
+  maybe_destination_user_token  VARCHAR(32) DEFAULT NULL,
+
+  -- ========== EMAIL LANGUAGE ==========
+
+  -- Email the language should be sent in.
+  -- The full IETF BCP47 language tag (eg. en, en-US, es-419, ja-JP, pt, etc.)
+  -- The wide varchar space is because these language tags can become cumbersome, eg "hy-Latn-IT-arevela"
+  ietf_language_tag VARCHAR(64) NOT NULL DEFAULT 'en',
+
+  -- Email the language should be sent in.
+  -- The IETF BCP47 language tag's primary language subtag (eg. "es-419" becomes "es" and "en-US" becomes "en")
+  ietf_primary_language_subtag VARCHAR(12) NOT NULL DEFAULT 'en',
+
+  -- ========== CREATOR DETAILS ==========
+
+  -- Foreign key to user that triggered the job (eg. for user DMs)
+  maybe_creator_user_token VARCHAR(32) DEFAULT NULL,
+
+  -- AVT of the user that triggered the job (eg. for user DMs)
+  -- Based on a cookie sent by the frontend.
+  -- We'll save this even if the user is logged in.
+  maybe_creator_anonymous_visitor_token VARCHAR(32) DEFAULT NULL,
+
+  -- For abuse tracking.
+  -- Wide enough for IPv4/6
+  maybe_creator_ip_address VARCHAR(40) DEFAULT NULL,
+
+  -- ========== WORKLOAD SOURCE AND PRIORITY ==========
+
+  -- Priority *increases*, so a level of 2 will be higher than 1.
+  -- These are the level semantics currently:
+  --   - (N/A; all emails should be roughly the same importance for now)
+  priority_level SMALLINT UNSIGNED NOT NULL DEFAULT 0,
+
+  -- ========== DEVELOPMENT AND DEBUGGING METADATA ==========
+
+  -- If true, the request gets a "debug" flag, which may do different
+  -- things depending on the type of work. This doesn't really have any
+  -- specific meaning -- it can be used to flag for anything -- but should
+  -- typically be used for one-off debugging and not a part of the normal
+  -- product surface area. Contrast this with `maybe_routing_tag`, which
+  -- forces the job onto a specific physical server: this might be a flag
+  -- deployed to handle debugging across the entire fleet.
+  is_debug_request BOOLEAN NOT NULL DEFAULT FALSE,
+
+  -- If set, the request gets processed by a special "tagged" worker
+  -- that matches this tag. The ordinary workers will ignore this work.
+  -- The tag is typically added via a special HTTP header when the work
+  -- is enqueued. In practice, this means we can canary deploy or cordon
+  -- off special workers to handle certain jobs.
+  maybe_routing_tag VARCHAR(32) DEFAULT NULL,
+
+  -- ========== JOB SYSTEM DETAILS ==========
+
+  -- Jobs begin as "pending", then transition to other states.
+  --
+  --  * Pending = job is ready to go
+  --  * Started = job is running
+  --  * Complete_Success = job is done (success)
+  --  * Complete_Failure = job is done (permanent failure, eg. a Face Animator job without a face)
+  --  * Attempt_Failed = job failed but may retry.
+  --  * Dead = job failed permanently (ie. exhausted retries)
+  --  * Cancelled_By_User = the user canceled their own job
+  --  * Cancelled_By_System = the system terminated the job (ie. load shedding, user account deletion, etc.)
+  --
+  -- Pending -> Started -> Complete_Success
+  --                    |-> Complete_Failure
+  --                    \-> Attempt_Failed -> Started -> { Complete, Failed, Dead }
+  status ENUM(
+    'pending',
+    'started',
+    'complete_success',
+    'complete_failure',
+    'attempt_failed',
+    'dead',
+    'cancelled_by_user',
+    'cancelled_by_system'
+  ) NOT NULL DEFAULT 'pending',
+
+  -- We can track this against a "max_attempt_count"
+  attempt_count SMALLINT UNSIGNED NOT NULL DEFAULT 0,
+
+  -- Optional internal-only debugging information in the case of failure.
+  internal_debugging_failure_reason VARCHAR(512) DEFAULT NULL,
+
+  -- Worker hostname (linux hostname, k8s pod name)
+  -- Assigned when a worker picks up the job
+  -- Reassigned if the job fails and gets picked up again
+  assigned_worker VARCHAR(128) DEFAULT NULL,
+
+  -- Cluster name (k8s)
+  -- Assigned when a worker picks up the job
+  -- Reassigned if the job fails and gets picked up again
+  assigned_cluster VARCHAR(128) DEFAULT NULL,
+
+  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
+  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+
+  -- Failed jobs will set a next attempt time.
+  -- Subsequent tries can increase the timeout.
+  -- Failures because of permissions require human intervention => [retry_at=null].
+  -- Failures because of invalid files are dead => [status=dead].
+  retry_at TIMESTAMP NULL,
+
+  -- Set when the job first starts executing
+  first_started_at TIMESTAMP NULL,
+
+  -- Set when the job is successfully completed
+  successfully_completed_at TIMESTAMP NULL,
+
+  -- INDICES --
+  PRIMARY KEY (id),
+  UNIQUE KEY (token),
+  UNIQUE KEY (uuid_idempotency_token),
+  KEY index_email_category (email_category),
+  KEY fk_maybe_creator_user_token (maybe_creator_user_token),
+  KEY index_maybe_creator_ip_address (maybe_creator_ip_address),
+  KEY index_priority_level (priority_level),
+  KEY index_is_debug_request (is_debug_request),
+  KEY index_maybe_routing_tag (maybe_routing_tag),
+  KEY index_status (status)
+
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

_sql/migrations_squashed/generic_inference_jobs.sql

@@ -123,11 +123,19 @@ CREATE TABLE generic_inference_jobs (
   -- ========== DEVELOPMENT AND DEBUGGING METADATA ==========
 
   -- If true, the request gets a "debug" flag, which may do different
-  -- things depending on the type of work.
+  -- things depending on the type of work. This doesn't really have any
+  -- specific meaning -- it can be used to flag for anything -- but should
+  -- typically be used for one-off debugging and not a part of the normal
+  -- product surface area. Contrast this with `maybe_routing_tag`, which
+  -- forces the job onto a specific physical server: this might be a flag
+  -- deployed to handle debugging across the entire fleet.
   is_debug_request BOOLEAN NOT NULL DEFAULT FALSE,
 
   -- If set, the request gets processed by a special "tagged" worker
   -- that matches this tag. The ordinary workers will ignore this work.
+  -- The tag is typically added via a special HTTP header when the work
+  -- is enqueued. In practice, this means we can canary deploy or cordon
+  -- off special workers to handle certain jobs.
   maybe_routing_tag VARCHAR(32) DEFAULT NULL,
 
   -- ========== JOB SYSTEM DETAILS ==========
@@ -137,9 +145,11 @@ CREATE TABLE generic_inference_jobs (
   --  * Pending = job is ready to go
   --  * Started = job is running
   --  * Complete_Success = job is done (success)
-  --  * Complete_Failure = job is done (failure)
+  --  * Complete_Failure = job is done (permanent failure, eg. a Face Animator job without a face)
   --  * Attempt_Failed = job failed but may retry.
-  --  * Dead = job failed permanently.
+  --  * Dead = job failed permanently (ie. exhausted retries)
+  --  * Cancelled_By_User = the user canceled their own job
+  --  * Cancelled_By_System = the system terminated the job (ie. load shedding, user account deletion, etc.)
   --
   -- Pending -> Started -> Complete_Success
   --                    |-> Complete_Failure
@@ -150,17 +160,41 @@ CREATE TABLE generic_inference_jobs (
     'complete_success',
     'complete_failure',
     'attempt_failed',
-    'dead') NOT NULL DEFAULT 'pending',
+    'dead',
+    'cancelled_by_user',
+    'cancelled_by_system'
+  ) NOT NULL DEFAULT 'pending',
 
   -- We can track this against a "max_attempt_count"
   attempt_count SMALLINT UNSIGNED NOT NULL DEFAULT 0,
 
   -- If there is a failure, tell the user why.
+  -- This isn't localized or very friendly to present to the user.
   failure_reason VARCHAR(512) DEFAULT NULL,
 
   -- Optional internal-only debugging information in the case of failure.
   internal_debugging_failure_reason VARCHAR(512) DEFAULT NULL,
 
+  -- An enum-like key to present the frontend with a failure class in an
+  -- i18n-friendly way. The javascript/frontend can use these as indices
+  -- into descriptive, broadly-localized error messages.
+  --
+  --   * 'face_not_detected' for images or videos that do not have a detectable
+  --                         face (SadTalker, Wav2Lip, etc.)
+  --
+  --   * 'keep_alive_elapsed' when users walk away from their inference job and
+  --                          the workload class expects for them to remain
+  --
+  --   * 'not_yet_implemented' error for developers during feature development
+  --                           and prototyping. Should ideally never happen in
+  --                           production
+  --
+  --   * 'retryable_worker_error' for generic worker errors (full filesystem,
+  --                              etc.) that can be retried. (We don't need to
+  --                              tell the user why the job failed.)
+  --
+  frontend_failure_category VARCHAR(32) DEFAULT NULL,
+
   -- On success, we populate how long the job execution took in milliseconds.
   -- We have this because 1) the job system timestamps are second-resolution,
   -- and 2) the structure of the jobs executor may change, potentially changing

_sql/migrations_squashed/media_files.sql

@@ -19,12 +19,28 @@ CREATE TABLE media_files (
 
   -- ========== FOREIGN KEY TO ORIGIN ==========
 
-  -- Where the file came from:
+  -- Broad category for where the file came from:
   --   * 'inference' for inference output
-  --   * 'processed' for processed file
-  --   * 'upload' for direct user upload
+  --   * 'processed' for processed file (eg. mp3 encoding, stem splitting, etc.)
+  --   * 'upload' for direct user upload (from the filesystem)
+  --   * 'device_api' for direct user uploads recorded using Browser/Device APIs.
   origin_category VARCHAR(16) NOT NULL,
 
+  -- TODO: Remove default value once records backfilled and queries updated.
+  -- Product area where the media file originated.
+  -- This is not the *model* that created the thing, this is the *product*.
+  -- (The underlying models can change over time.)
+  --
+  -- This value indicates what product originally created the media file. (Not the ML model or
+  -- user upload process.) This will let us scope media files to the product that generated them
+  -- and filter them out of unrelated products if necessary (eg. a user probably doesn't want
+  -- "Voice Designer" dataset samples in a video generation flow.)
+  --
+  -- Possible values:
+  --   * 'face_animator' for uploads and outputs
+  --   * 'unknown' for legacy records without an associated product (TODO: This should be temporary)
+  origin_product_category VARCHAR(16) NOT NULL DEFAULT "unknown",
+
   -- For inference that can be tied back to a model, the type of model.
   --   * 'sad_talker', which doesn't have a value for maybe_origin_model_token (!!!)
   --   * (more tome come)
@@ -88,8 +104,24 @@ CREATE TABLE media_files (
 
   -- The hash for the bucket directory that contains the original upload
   -- as well as any transcodings, downsamplings, etc.
+  -- The bucket filename for the primary file (not including the directory path) is given by:
+  -- `[{maybe_public_bucket_prefix}]{public_bucket_directory_hash}[{maybe_public_bucket_extension}]`
   public_bucket_directory_hash  VARCHAR(32) NOT NULL,
 
+  -- An optional prefix on the bucket filename.
+  -- If present, this will be prepended to the beginning of the bucket filename to access the file.
+  -- The bucket filename for the primary file (not including the directory path) is given by:
+  -- `[{maybe_public_bucket_prefix}]{public_bucket_directory_hash}[{maybe_public_bucket_extension}]`
+  maybe_public_bucket_prefix VARCHAR(16) DEFAULT NULL,
+
+  -- An optional appended extension on the bucket filename.
+  -- If present, this will be appended to the end of the bucket filename to access the file.
+  -- To allow for flexibility, this extension typically will contain a leading period if
+  -- the file needs it (eg ".mp4" rather than "mp4")!
+  -- The bucket filename for the primary file (not including the directory path) is given by:
+  -- `[{maybe_public_bucket_prefix}]{public_bucket_directory_hash}[{maybe_public_bucket_extension}]`
+  maybe_public_bucket_extension VARCHAR(16) DEFAULT NULL,
+
   -- NB: Removed, since this can be derived.
   -- The directory this media is uploaded to will be exclusive for this file.
   -- Only this given record will live in this bucket, but the directory may include

_sql/migrations_squashed/media_uploads.sql

@@ -98,7 +98,7 @@ CREATE TABLE media_uploads (
     'public',
     'hidden',
     'private'
-    ) NOT NULL DEFAULT 'public',
+  ) NOT NULL DEFAULT 'public',
 
   -- The synthetic id associated with this result.
   -- These ids are incremented on a per-user basis to help users

_sql/migrations_squashed/tts_tables/tts_models.sql

@@ -83,9 +83,10 @@ CREATE TABLE tts_models (
   -- ========== UNSTRUCTURED METADATA ==========
 
   -- The full IETF BCP47 language tag (eg. en, en-US, es-419, ja-JP, pt, etc.)
+  -- The wide varchar space is because these language tags can become cumbersome, eg "hy-Latn-IT-arevela"
   ietf_language_tag VARCHAR(64) NOT NULL DEFAULT 'en',
 
-  -- The IETF BCP47 language tag's primary language subtag (eg. "es-419" becomes "es")
+  -- The IETF BCP47 language tag's primary language subtag (eg. "es-419" becomes "es" and "en-US" becomes "en")
   ietf_primary_language_subtag VARCHAR(12) NOT NULL DEFAULT 'en',
 
   -- The "name" of the voice model, which might be complicated.

_sql/migrations_squashed/tts_tables/vocoder_models.sql

@@ -0,0 +1,130 @@
+-- noinspection SqlDialectInspectionForFile
+-- noinspection SqlNoDataSourceInspectionForFile
+-- noinspection SqlResolveForFile
+
+CREATE TABLE vocoder_models (
+  -- Not used for anything except replication.
+  id BIGINT(20) NOT NULL AUTO_INCREMENT,
+
+  -- Effective "primary key" (PUBLIC)
+  token VARCHAR(32) NOT NULL,
+
+  -- The architecture of the vocoder.
+  -- Currently supported values:
+  --   * 'hifigan' - hifigan for Tacotron2
+  --   * 'hifigan_rocket_vc' - hifigan for SoftVC ("rocket" is a codename)
+  vocoder_type VARCHAR(32) NOT NULL,
+
+  -- ========== MUTABLE METADATA ==========
+
+  -- The "name" of the vocoder, which might be complicated.
+  title VARCHAR(255) NOT NULL,
+
+  -- The description of the model in markdown.
+  description_markdown TEXT NOT NULL,
+
+  -- Generated HTML (not user-editable).
+  description_rendered_html TEXT NOT NULL,
+
+  -- ========== MUTABLE MOD-ONLY METADATA ==========
+
+  -- Whether the vocoder is recommended for use
+  is_staff_recommended BOOLEAN NOT NULL DEFAULT FALSE,
+
+  -- ========== IMMUTABLE PROVENANCE AND METADATA ==========
+
+  -- Where the file was originally downloaded (if it was downloaded)
+  original_download_url VARCHAR(512) DEFAULT NULL,
+
+  -- The filename that was used at upload time (if available)
+  original_filename VARCHAR(255) DEFAULT NULL,
+
+  -- File characteristics
+  file_size_bytes INT(10) NOT NULL DEFAULT 0,
+
+  -- ========== CREATOR DETAILS ==========
+
+  -- The person that created the template.
+  creator_user_token VARCHAR(32) NOT NULL,
+
+  -- For abuse tracking.
+  -- Wide enough for IPv4/6
+  creator_ip_address_creation VARCHAR(40) NOT NULL,
+  creator_ip_address_last_update VARCHAR(40) NOT NULL,
+
+  -- (THIS MIGHT NOT BE USED)
+  -- NB: DO NOT SORT!
+  -- THIS MUST MATCH THE RESPECTIVE JOBS TABLE.
+  creator_set_visibility ENUM(
+    'public',
+    'hidden',
+    'private'
+  ) NOT NULL DEFAULT 'public',
+
+  -- ========== BUCKET STORAGE ==========
+
+  -- The pytorch model
+  -- For now, this will be a hash of the file contents.
+  -- NB: NOT UNIQUE! We can allow duplicate uploads.
+  private_bucket_hash CHAR(64) NOT NULL,
+
+  -- The "full url" of the bucket path, not including the hostname
+  private_bucket_object_name VARCHAR(255) NOT NULL,
+
+  -- ========== MODERATION DETAILS ==========
+
+  -- Mods have locked the ability for non-authors (ie. people who didn't upload this)
+  -- to assign the vocoder for use with their TTS models.
+  -- TTS models that already incorporate the vocoder will not be affected.
+  -- The vocoder will remain visible.
+  -- This is independent of other visibility controls (is_mod_disabled_from_author_use,
+  -- deleted_at, and creator_set_visibility)
+  is_mod_disabled_from_public_use BOOLEAN NOT NULL DEFAULT FALSE,
+
+  -- Mods have locked the ability for the author (ie. the person that uploaded this)
+  -- to assign the vocoder for use with their TTS models.
+  -- TTS models that already incorporate the vocoder will not be affected.
+  -- The vocoder will remain visible.
+  -- This is independent of other visibility controls (is_mod_disabled_from_public_use,
+  -- deleted_at, and creator_set_visibility)
+  is_mod_disabled_from_author_use BOOLEAN NOT NULL DEFAULT FALSE,
+
+  -- Extremely popular models may be locked from deletion or modification by users.
+  -- If true, only moderators can edit or delete the vocoder.
+  is_mod_author_editing_locked BOOLEAN NOT NULL DEFAULT FALSE,
+
+  -- If a moderator has comments.
+  maybe_mod_comments VARCHAR(255) DEFAULT NULL,
+
+  -- The last moderator that made changes.
+  maybe_mod_user_token VARCHAR(32) DEFAULT NULL,
+
+  -- ========== VECTOR CLOCK ==========
+
+  -- Incremented with every update.
+  version INT NOT NULL DEFAULT 0,
+
+  -- ========== TIMESTAMPS ==========
+
+  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
+  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+
+  -- If this is removed by a mod.
+  -- It completely disappears from the system.
+  user_deleted_at TIMESTAMP NULL,
+  mod_deleted_at TIMESTAMP NULL,
+
+  -- INDICES --
+  PRIMARY KEY (id),
+  UNIQUE KEY (token),
+  KEY fk_creator_user_token (creator_user_token),
+  KEY fk_maybe_mod_user_token (maybe_mod_user_token),
+  KEY index_creator_ip_address_creation (creator_ip_address_creation),
+  KEY index_creator_ip_address_last_update (creator_ip_address_last_update),
+  KEY index_creator_set_visibility (creator_set_visibility),
+  KEY index_private_bucket_hash (private_bucket_hash),
+  KEY index_is_mod_disabled_from_public_use (is_mod_disabled_from_public_use),
+  KEY index_is_mod_disabled_from_author_use (is_mod_disabled_from_author_use),
+  KEY index_is_mod_author_editing_locked (is_mod_author_editing_locked)
+
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

_sql/migrations_squashed/user_tables/user_password_resets.sql

@@ -0,0 +1,67 @@
+-- noinspection SqlDialectInspectionForFile
+-- noinspection SqlNoDataSourceInspectionForFile
+-- noinspection SqlResolveForFile
+
+-- Per-user password resets.
+-- Password resets are valid until any of the following are true:
+--
+--   1) database NOW() > password_rests.expires_at
+--   2) password_rests.is_redeemed == TRUE
+--   3) users.password_version > password_resets.current_password_version
+--
+-- When creating a new password reset, send the user an email with the `secret_key` in the URL.
+-- The user will have to supply their username and/or email address, plus the secret key to
+-- redeem the password reset.
+--
+-- Also when creating the password hash, be sure to set `expires_at` to be "NOW() + 3 hours", or
+-- some other small, reasonable time frame.
+--
+CREATE TABLE user_password_resets (
+  -- Not used for anything except replication.
+  id BIGINT(20) NOT NULL AUTO_INCREMENT,
+
+  -- Password hash "primary key"
+  -- (ie. for the future when we have a support dashboard)
+  token VARCHAR(32) NOT NULL,
+
+  -- Foreign key to user
+  user_token VARCHAR(32) NOT NULL,
+
+  -- Secret "key" for the password reset
+  -- This will be shared via URL or user text input.
+  public_reset_token VARCHAR(32) NOT NULL,
+
+  -- Copied from the user record at the time of password reset issuance.
+  -- If the user's password version is greater than this value, then this
+  -- reset is no longer valid.
+  current_password_version INT UNSIGNED NOT NULL DEFAULT 0,
+
+  -- Whether the password reset has been consumed.
+  is_redeemed BOOLEAN NOT NULL DEFAULT false,
+
+  -- For abuse tracking.
+  -- Wide enough for IPv4/6
+  ip_address_creation VARCHAR(40) NOT NULL,
+  ip_address_redemption VARCHAR(40) DEFAULT NULL,
+
+  -- Incremented with every update.
+  version INT UNSIGNED NOT NULL DEFAULT 0,
+
+  -- ========== TIMESTAMPS ==========
+
+  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
+  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+
+  -- When the password reset expires
+  expires_at TIMESTAMP NOT NULL,
+
+  -- INDICES --
+  PRIMARY KEY (id),
+  UNIQUE KEY (token),
+  UNIQUE KEY (public_reset_token),
+
+  KEY fk_user_token (user_token),
+  KEY index_secret_key (secret_key),
+  KEY index_expires_at (expires_at)
+
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

_sql/migrations_squashed/user_tables/users.sql

@@ -41,7 +41,7 @@ CREATE TABLE users (
   password_hash BINARY(60) NOT NULL,
 
   -- Incremented with every update to the password.
-  password_version INT NOT NULL DEFAULT 0,
+  password_version INT UNSIGNED NOT NULL DEFAULT 0,
 
   -- ========== ABUSE TRACKING ==========
 
@@ -145,7 +145,7 @@ CREATE TABLE users (
   -- ========== VECTOR CLOCK ==========
 
   -- Incremented with every update.
-  version INT NOT NULL DEFAULT 0,
+  version INT UNSIGNED NOT NULL DEFAULT 0,
 
   -- ========== TIMESTAMPS ==========
 

_sql/migrations_squashed/zs_voices/zs_voice_dataset_samples.sql

@@ -0,0 +1,89 @@
+-- NB: This is a manually squashed view of all the CREATE and ALTER statements,
+-- with comments attached to the fields for centralized documentation.
+
+-- noinspection SqlDialectInspectionForFile
+-- noinspection SqlNoDataSourceInspectionForFile
+-- noinspection SqlResolveForFile
+
+-- These are the individual files in each dataset.
+-- Files can be added and removed from a dataset at any time.
+CREATE TABLE zs_voice_dataset_samples (
+  -- Not used for anything except replication.
+  id BIGINT(20) NOT NULL AUTO_INCREMENT,
+
+  -- Effective "primary key" for joins, API, etc. (PUBLIC)
+  token VARCHAR(32) NOT NULL,
+
+  -- Idempotency token from client
+  -- This is so the frontend client doesn't submit duplicate uploads.
+  uuid_idempotency_token VARCHAR(36) NOT NULL,
+
+  -- ========== ZERO SHOT FOREIGN KEYS AND STATE==========
+
+  -- The zero shot dataset that was used to create this voice.
+  -- The dataset will only be accessible to the original author of the voice.
+  -- The original author can create multiple voices from the same dataset.
+  dataset_token VARCHAR(32) NOT NULL,
+
+  -- Foreign key to the uploaded media file.
+  -- There was an option of handling uploads and file management separately, but by electing to store the dataset
+  -- samples in the "media_files" table, they can be reused by our other code, queries, machinery, etc.
+  -- Note that this requires that the "media_files" record must be an audio (not video) file (which our code will
+  -- enforce), and that it is given a category of "voice_zero_shot" so it isn't co-mingled with other uploads/outputs
+  media_file_token VARCHAR(32) NOT NULL,
+
+  -- Some data sets require raw transcripts for the audio.
+  -- If required, they'll live here.
+  maybe_raw_transcription TEXT DEFAULT NULL,
+
+  -- A way to order the data set samples
+  -- We don't necessarily need to reimplement file reordering.
+  -- order_id INT DEFAULT NULL,
+
+  -- ========== CREATOR DETAILS AND PREFERENCES ==========
+
+  -- Foreign key to user
+  maybe_creator_user_token VARCHAR(32) DEFAULT NULL,
+
+  -- Based on a cookie sent by the frontend.
+  -- We'll save this even if the user is logged in.
+  maybe_creator_anonymous_visitor_token VARCHAR(32) DEFAULT NULL,
+
+  -- For abuse tracking.
+  -- Wide enough for IPv4/6
+  creator_ip_address VARCHAR(40) NOT NULL,
+
+  -- ========== MODERATION DETAILS ==========
+
+  -- The last moderator that made changes last.
+  -- Fine details of moderator actions are kept in a separate `audit_logs` table.
+  maybe_mod_user_token VARCHAR(32) DEFAULT NULL,
+
+  -- ========== TIMESTAMPS ==========
+
+  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
+  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+
+  -- If this is removed by a mod or the creator.
+  user_deleted_at TIMESTAMP NULL,
+  mod_deleted_at TIMESTAMP NULL,
+
+  -- ========== VECTOR CLOCK ==========
+
+  -- Incremented with every update.
+  version INT NOT NULL DEFAULT 0,
+
+  -- INDICES --
+  PRIMARY KEY (id),
+  UNIQUE KEY (token),
+  UNIQUE KEY (uuid_idempotency_token),
+
+  KEY fk_dataset_token (dataset_token),
+  KEY fk_media_file_token (media_file_token),
+  KEY fk_maybe_creator_user_token (maybe_creator_user_token),
+  KEY fk_maybe_mod_user_token (maybe_mod_user_token),
+  KEY index_creator_ip_address (creator_ip_address),
+  KEY index_user_deleted_at (user_deleted_at),
+  KEY index_mod_deleted_at (mod_deleted_at)
+
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

_sql/migrations_squashed/zs_voices/zs_voice_datasets.sql

@@ -0,0 +1,109 @@
+-- NB: This is a manually squashed view of all the CREATE and ALTER statements,
+-- with comments attached to the fields for centralized documentation.
+
+-- noinspection SqlDialectInspectionForFile
+-- noinspection SqlNoDataSourceInspectionForFile
+-- noinspection SqlResolveForFile
+
+-- This table describes a zero shot voice in the process of being created (or later reused / edited / enhanced)
+-- These are not usable voices until their vector embeddings have been created, and this table does not serve
+-- the production voice list.
+--
+-- Multiple voices can be created from a single "dataset" as the data set is mutable over time. Users can add or
+-- remove new samples to the data set at any time.
+CREATE TABLE zs_voice_datasets (
+  -- Not used for anything except replication.
+  id BIGINT(20) NOT NULL AUTO_INCREMENT,
+
+  -- Effective "primary key" for joins, API, etc. (PUBLIC)
+  token VARCHAR(32) NOT NULL,
+
+  -- NB: No idempotency token.
+  -- Store it in another table to prevent duplicate record creation from rapid user inputs.
+
+  -- ========== VOICE DETAILS ==========
+
+  -- The "title" of the voice. This can be the name and additional information as supplied by the user.
+  -- When the voice embeddings are created, this will be copied to the produced "voice" (embeddings) record.
+  title VARCHAR(255) NOT NULL,
+
+  -- The full IETF BCP47 language tag (eg. en, en-US, es-419, ja-JP, pt, etc.)
+  -- The wide varchar space is because these language tags can become cumbersome, eg "hy-Latn-IT-arevela"
+  ietf_language_tag VARCHAR(64) NOT NULL DEFAULT 'en',
+
+  -- The IETF BCP47 language tag's primary language subtag (eg. "es-419" becomes "es" and "en-US" becomes "en")
+  ietf_primary_language_subtag VARCHAR(12) NOT NULL DEFAULT 'en',
+
+  -- TODO: misc speaker metadata
+  -- We'll want rich metadata for search
+  -- When the voice embeddings are created, this will be copied to the produced "voice" (embeddings) record.
+  -- We don't want a hard link between the dataset and the produced voice as the voice is static once
+  -- created (with the exception of regenerating embeddings), but the dataset my drift as the user experiments,
+  -- adds/removes samples, etc.
+  -- series_name VARCHAR(255) DEFAULT NULL,
+  -- speaker_name VARCHAR(255) DEFAULT NULL,
+  -- speaker_gender VARCHAR(255) DEFAULT NULL,
+  -- speaker_age VARCHAR(255) DEFAULT NULL,
+  -- speaker_voice_style VARCHAR(255) DEFAULT NULL,
+
+  -- ========== CREATOR DETAILS AND PREFERENCES ==========
+
+  -- Foreign key to user
+  maybe_creator_user_token VARCHAR(32) DEFAULT NULL,
+
+  -- Based on a cookie sent by the frontend.
+  -- We'll save this even if the user is logged in.
+  maybe_creator_anonymous_visitor_token VARCHAR(32) DEFAULT NULL,
+
+  -- For abuse tracking.
+  -- Wide enough for IPv4/6
+  creator_ip_address VARCHAR(40) NOT NULL,
+
+  -- The creator can set a desired visibility for their data.
+  -- This does not always apply to every upload type.
+  -- Additionally, some upload types may require moderator approval prior
+  -- to being publicly listed, and this field has no bearing on that.
+  -- NB: DO NOT CHANGE THE ORDER OF THE ENUM VALUES DURING SCHEMA MIGRATIONS.
+  creator_set_visibility ENUM(
+    'public',
+    'hidden',
+    'private'
+  ) NOT NULL DEFAULT 'public',
+
+  -- The synthetic id associated with this result.
+  -- These ids are incremented on a per-user basis to help users
+  -- sequence their own work. They serve no other purpose.
+  maybe_creator_synthetic_id BIGINT(20) DEFAULT NULL,
+
+  -- ========== MODERATION DETAILS ==========
+
+  -- The last moderator that made changes last.
+  -- Fine details of moderator actions are kept in a separate `audit_logs` table.
+  maybe_mod_user_token VARCHAR(32) DEFAULT NULL,
+
+  -- ========== TIMESTAMPS ==========
+
+  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
+  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+
+  -- If this is removed by a mod or the creator.
+  user_deleted_at TIMESTAMP NULL,
+  mod_deleted_at TIMESTAMP NULL,
+
+  -- ========== VECTOR CLOCK ==========
+
+  -- Incremented with every update.
+  version INT NOT NULL DEFAULT 0,
+
+  -- INDICES --
+  PRIMARY KEY (id),
+  UNIQUE KEY (token),
+
+  KEY index_ietf_primary_language_subtag (ietf_primary_language_subtag),
+  KEY fk_maybe_creator_user_token (maybe_creator_user_token),
+  KEY fk_maybe_mod_user_token (maybe_mod_user_token),
+  KEY index_creator_ip_address (creator_ip_address),
+  KEY index_user_deleted_at (user_deleted_at),
+  KEY index_mod_deleted_at (mod_deleted_at)
+
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

_sql/migrations_squashed/zs_voices/zs_voices.sql

@@ -0,0 +1,157 @@
+-- NB: This is a manually squashed view of all the CREATE and ALTER statements,
+-- with comments attached to the fields for centralized documentation.
+
+-- noinspection SqlDialectInspectionForFile
+-- noinspection SqlNoDataSourceInspectionForFile
+-- noinspection SqlResolveForFile
+
+-- This table contains both zero shot TTS voices (speaker embeddings) and zero shot V2V voices (speaker embeddings).
+-- These are the zero shot voices that are usable by our users.
+CREATE TABLE zs_voices (
+  -- Not used for anything except replication.
+  id BIGINT(20) NOT NULL AUTO_INCREMENT,
+
+  -- Effective "primary key" for joins, API, etc. (PUBLIC)
+  token VARCHAR(32) NOT NULL,
+
+  -- NB: No idempotency token.
+  -- Store it in another table to prevent duplicate record creation from rapid user inputs.
+
+  -- ========== ZERO SHOT FOREIGN KEYS AND STATE==========
+
+  -- The zero shot dataset that was used to create this voice.
+  -- The dataset will only be accessible to the original author of the voice.
+  -- The original author can create multiple voices from the same dataset.
+  dataset_token VARCHAR(32) NOT NULL,
+
+  -- When datasets are edited, we bump their version.
+  -- This tells us which version of the dataset created this voice (not that we should preserve
+  -- record of which samples are in which version.)
+  -- We can surface this to the users as well.
+  dataset_version INT NOT NULL DEFAULT 0,
+
+  -- ========== MODEL DETAILS ==========
+
+  -- The class or category of model/algorithm this voice is intended for.
+  --   * 'tts' for zero shot TTS
+  --   * 'vc' for zero shot voice conversion
+  --   * 'pitch' for pitch correction models (hypothetical)
+  --   * (Since this isn't an enum, the list of values can be expanded.)
+  model_category VARCHAR(16) NOT NULL,
+
+  -- The model used for the voice:
+  --   * 'vall-e-x' for Vall-E-X
+  --   * 'ns2vc' for NaturalSpeech2 voice conversion (hypothetical)
+  --   * (Since this isn't an enum, the list of values can be expanded.)
+  model_type VARCHAR(16) NOT NULL,
+
+  -- The version of the model.
+  -- If we deploy new model weights for the model, we may need to regenerate
+  -- all of the corresponding speaker embeddings. This lets us know which voices
+  -- are the old version.
+  -- In some cases, we may need to preserve, hide, or roll back to older versions.
+  model_version INTEGER UNSIGNED NOT NULL DEFAULT 0,
+
+  -- What kind of features these are: spectrogram, encodec, etc.
+  -- Technically we shouldn't need this, as the 2-tuple (model_type, model_version) implicitly knows
+  -- what its features should be, but this is probably useful for our own analysis and may be easier
+  -- to deal with.
+  --
+  -- Example values:
+  --   * 'encodec'
+  --   * 'spectrogram' (for example; not yet used)
+  --   * (Since this isn't an enum, the list of values can be expanded.)
+  encoding_type VARCHAR(16) NOT NULL,
+
+  -- ========== VOICE DETAILS ==========
+
+  -- The "title" of the voice. This can be the name and additional information as supplied by the user.
+  title VARCHAR(255) NOT NULL,
+
+  -- The full IETF BCP47 language tag (eg. en, en-US, es-419, ja-JP, pt, etc.)
+  -- The wide varchar space is because these language tags can become cumbersome, eg "hy-Latn-IT-arevela"
+  ietf_language_tag VARCHAR(64) NOT NULL DEFAULT 'en',
+
+  -- The IETF BCP47 language tag's primary language subtag (eg. "es-419" becomes "es" and "en-US" becomes "en")
+  ietf_primary_language_subtag VARCHAR(12) NOT NULL DEFAULT 'en',
+
+  -- TODO: misc speaker metadata
+  -- We'll want rich metadata for search
+  -- series_name VARCHAR(255) DEFAULT NULL,
+  -- speaker_name VARCHAR(255) DEFAULT NULL,
+  -- speaker_gender VARCHAR(255) DEFAULT NULL,
+  -- speaker_age VARCHAR(255) DEFAULT NULL,
+  -- speaker_voice_style VARCHAR(255) DEFAULT NULL,
+
+  -- ========== STORAGE DETAILS ==========
+
+  -- The hash for the bucket directory that contains the original upload
+  -- eg. /speaker_vectors/{bucket_hash}/{voice_model_version}.bin
+  bucket_hash  VARCHAR(32) NOT NULL,
+
+  -- ========== CREATOR DETAILS AND PREFERENCES ==========
+
+  -- Foreign key to user
+  maybe_creator_user_token VARCHAR(32) DEFAULT NULL,
+
+  -- Based on a cookie sent by the frontend.
+  -- We'll save this even if the user is logged in.
+  maybe_creator_anonymous_visitor_token VARCHAR(32) DEFAULT NULL,
+
+  -- For abuse tracking.
+  -- Wide enough for IPv4/6
+  creator_ip_address VARCHAR(40) NOT NULL,
+
+  -- The creator can set a desired visibility for their data.
+  -- This does not always apply to every upload type.
+  -- Additionally, some upload types may require moderator approval prior
+  -- to being publicly listed, and this field has no bearing on that.
+  -- NB: DO NOT CHANGE THE ORDER OF THE ENUM VALUES DURING SCHEMA MIGRATIONS.
+  creator_set_visibility ENUM(
+    'public',
+    'hidden',
+    'private'
+  ) NOT NULL DEFAULT 'public',
+
+  -- The synthetic id associated with this result.
+  -- These ids are incremented on a per-user basis to help users
+  -- sequence their own work. They serve no other purpose.
+  maybe_creator_synthetic_id BIGINT(20) DEFAULT NULL,
+
+  -- ========== MODERATION DETAILS ==========
+
+  -- The last moderator that made changes last.
+  -- Fine details of moderator actions are kept in a separate `audit_logs` table.
+  maybe_mod_user_token VARCHAR(32) DEFAULT NULL,
+
+  -- ========== TIMESTAMPS ==========
+
+  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
+  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+
+  -- If this is removed by a mod or the creator.
+  user_deleted_at TIMESTAMP NULL,
+  mod_deleted_at TIMESTAMP NULL,
+
+  -- ========== VECTOR CLOCK ==========
+
+  -- Incremented with every update.
+  version INT NOT NULL DEFAULT 0,
+
+  -- INDICES --
+  PRIMARY KEY (id),
+  UNIQUE KEY (token),
+
+  -- TODO: These are not correct. Fix these before landing / running migrations.
+  KEY index_model_category (model_category),
+  KEY index_model_type (model_type),
+  KEY index_model_type_and_version (model_type, model_version),
+  KEY index_encoding_type (encoding_type),
+  KEY index_ietf_primary_language_subtag (ietf_primary_language_subtag),
+  KEY fk_maybe_creator_user_token (maybe_creator_user_token),
+  KEY fk_maybe_mod_user_token (maybe_mod_user_token),
+  KEY index_creator_ip_address (creator_ip_address),
+  KEY index_user_deleted_at (user_deleted_at),
+  KEY index_mod_deleted_at (mod_deleted_at)
+
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

codegen_database.sh

@@ -18,7 +18,7 @@ build_shared_mysql_database_library() {
   # NB: For now, this is our monolithic DB that serves all of our microservices (gross)
   # It's a single package so that queries can be shared (again, gross)
   pushd crates/lib/mysql_queries
-  SQLX_OFFLINE=true cargo sqlx prepare --merged
+  SQLX_OFFLINE=true cargo sqlx prepare
   popd
 }
 
@@ -35,6 +35,9 @@ build_shared_sqlite_database_library() {
 echo 'mysql prepare'
 build_shared_mysql_database_library
 
-echo 'sqlite prepare'
-build_shared_sqlite_database_library
+#echo 'sqlite prepare'
+#build_shared_sqlite_database_library
+
+echo 'done'
+
 

crates/_types/buckets/Cargo.toml

@@ -17,6 +17,7 @@ path = "src/lib.rs"
 # Internal
 crockford = { path = "../../lib/crockford" }
 errors = { path = "../../lib/errors" }
+my-workspace-hack = { version = "0.1", path = "../../../my-workspace-hack" }
 
 # External
 # (none yet)

crates/_types/buckets/src/public/media_files/directory.rs

@@ -1,6 +1,7 @@
+use crockford::crockford_entropy_lower;
+
 use crate::public::public_path::PublicPath;
 use crate::util::hashed_directory_path_long_string::hashed_directory_path_long_string;
-use crockford::crockford_entropy_lower;
 
 // TODO: Generate these from a macro.
 

crates/_types/buckets/src/public/media_files/original_file.rs

@@ -1,7 +1,9 @@
+use std::path::PathBuf;
+
+use crockford::crockford_entropy_lower;
+
 use crate::public::media_files::directory::MediaFileBucketDirectory;
 use crate::public::public_path::PublicPath;
-use crockford::crockford_entropy_lower;
-use std::path::PathBuf;
 
 const ORIGINAL_FILE_BASENAME : &str = "file.bin";
 
@@ -12,25 +14,43 @@ const ORIGINAL_FILE_BASENAME : &str = "file.bin";
 #[derive(Clone)]
 pub struct MediaFileBucketPath {
   directory: MediaFileBucketDirectory,
+
+  filename: String,
   full_object_path: String,
+
+  optional_prefix: Option<String>,
+  /// NB: Extension contains the leading period, eg. ".mp4"
+  optional_extension: Option<String>,
 }
 
 impl PublicPath for MediaFileBucketPath {}
 
 impl MediaFileBucketPath {
 
-  pub fn generate_new() -> Self {
+  pub fn generate_new(optional_prefix: Option<&str>, optional_extension: Option<&str>) -> Self {
     let entropy = crockford_entropy_lower(32);
-    Self::from_object_hash(&entropy)
+    Self::from_object_hash(&entropy, optional_prefix, optional_extension)
   }
 
-  pub fn from_object_hash(hash: &str) -> Self {
+  pub fn from_object_hash(hash: &str, optional_prefix: Option<&str>, optional_extension: Option<&str>) -> Self {
     // TODO: Path construction could be cleaner.
     let directory = MediaFileBucketDirectory::from_object_hash(hash);
-    let full_object_path = format!("{}/{}", directory.get_directory_path_str(), ORIGINAL_FILE_BASENAME);
+
+    let filename = match (optional_prefix, optional_extension) {
+      (None, None) => ORIGINAL_FILE_BASENAME.to_string(),
+      (None, Some(ext)) => format!("{}{}", hash, ext),
+      (Some(pre), None) => format!("{}{}", pre, hash),
+      (Some(pre), Some(ext)) => format!("{}{}{}", pre, hash, ext),
+    };
+
+    let full_object_path = format!("{}/{}", directory.get_directory_path_str(), filename);
+
     Self {
       directory,
+      filename,
       full_object_path,
+      optional_prefix: optional_prefix.map(|p| p.to_string()),
+      optional_extension: optional_extension.map(|e| e.to_string()),
     }
   }
 
@@ -50,52 +70,76 @@ impl MediaFileBucketPath {
     &self.directory.get_object_hash()
   }
 
-  pub fn get_basename() -> &'static str {
-    ORIGINAL_FILE_BASENAME
+  pub fn get_basename(&self) -> &str {
+    &self.filename
   }
 }
 
 #[cfg(test)]
 mod tests {
-  use crate::public::media_files::original_file::MediaFileBucketPath;
   use std::path::PathBuf;
 
-  #[test]
-  pub fn generate_new_entropy() {
-    let file = MediaFileBucketPath::generate_new();
-    assert_eq!(file.get_object_hash().len(), 32);
-    assert_eq!(file.get_directory().get_object_hash().len(), 32);
+  use crate::public::media_files::original_file::MediaFileBucketPath;
+
+  mod with_prefix_and_extension {
+    use super::*;
+
+    #[test]
+    pub fn generate_new_entropy() {
+      let file = MediaFileBucketPath::generate_new(Some("pre_"), Some(".mp4"));
+      assert_eq!(file.get_object_hash().len(), 32);
+      assert_eq!(file.get_object_hash().len(), 32);
+      assert_eq!(file.get_directory().get_object_hash().len(), 32);
+    }
+
+    #[test]
+    pub fn get_full_object_path_str() {
+      let file = MediaFileBucketPath::from_object_hash("abcdefghijk", Some("pre_"), Some(".mp4"));
+      assert_eq!(file.get_full_object_path_str(), "/media/a/b/c/d/e/abcdefghijk/pre_abcdefghijk.mp4");
+      assert_eq!(file.get_basename(), "pre_abcdefghijk.mp4");
+    }
   }
 
-  #[test]
-  pub fn get_full_object_path_str() {
-    let file = MediaFileBucketPath::from_object_hash("abcdefghijk");
-    assert_eq!(file.get_full_object_path_str(), "/media/a/b/c/d/e/abcdefghijk/file.bin");
-  }
+  mod without_prefix_and_extension {
+    use super::*;
 
-  #[test]
-  pub fn to_full_object_pathbuf() {
-    let file = MediaFileBucketPath::from_object_hash("abcdefghijk");
-    assert_eq!(file.to_full_object_pathbuf(), PathBuf::from("/media/a/b/c/d/e/abcdefghijk/file.bin"));
-  }
+    #[test]
+    pub fn generate_new_entropy() {
+      let file = MediaFileBucketPath::generate_new(None, None);
+      assert_eq!(file.get_object_hash().len(), 32);
+      assert_eq!(file.get_directory().get_object_hash().len(), 32);
+    }
 
-  #[test]
-  pub fn get_full_object_path_str_short_name() {
-    let file = MediaFileBucketPath::from_object_hash("foo");
-    assert_eq!(file.get_full_object_path_str(), "/media/f/o/foo/file.bin");
-  }
+    #[test]
+    pub fn get_full_object_path_str() {
+      let file = MediaFileBucketPath::from_object_hash("abcdefghijk", None, None);
+      assert_eq!(file.get_full_object_path_str(), "/media/a/b/c/d/e/abcdefghijk/file.bin");
+    }
 
-  #[test]
-  pub fn get_full_object_path_str_starts_with_directory() {
-    let file = MediaFileBucketPath::from_object_hash("abcdefghijk");
-    let full_path = file.get_full_object_path_str();
-    assert!(full_path.starts_with(file.get_directory().get_directory_path_str()))
-  }
+    #[test]
+    pub fn to_full_object_pathbuf() {
+      let file = MediaFileBucketPath::from_object_hash("abcdefghijk", None, None);
+      assert_eq!(file.to_full_object_pathbuf(), PathBuf::from("/media/a/b/c/d/e/abcdefghijk/file.bin"));
+    }
 
-  #[test]
-  pub fn get_object_hash() {
-    let hash = "abcdefghijk";
-    let file = MediaFileBucketPath::from_object_hash(hash);
-    assert_eq!(file.get_object_hash(), hash);
+    #[test]
+    pub fn get_full_object_path_str_short_name() {
+      let file = MediaFileBucketPath::from_object_hash("foo", None, None);
+      assert_eq!(file.get_full_object_path_str(), "/media/f/o/foo/file.bin");
+    }
+
+    #[test]
+    pub fn get_full_object_path_str_starts_with_directory() {
+      let file = MediaFileBucketPath::from_object_hash("abcdefghijk", None, None);
+      let full_path = file.get_full_object_path_str();
+      assert!(full_path.starts_with(file.get_directory().get_directory_path_str()))
+    }
+
+    #[test]
+    pub fn get_object_hash() {
+      let hash = "abcdefghijk";
+      let file = MediaFileBucketPath::from_object_hash(hash, None, None);
+      assert_eq!(file.get_object_hash(), hash);
+    }
   }
 }

crates/_types/buckets/src/public/media_uploads/directory.rs

@@ -1,6 +1,7 @@
+use crockford::crockford_entropy_lower;
+
 use crate::public::public_path::PublicPath;
 use crate::util::hashed_directory_path_long_string::hashed_directory_path_long_string;
-use crockford::crockford_entropy_lower;
 
 // TODO: Generate these from a macro.
 

crates/_types/buckets/src/public/media_uploads/original_file.rs

@@ -1,7 +1,9 @@
+use std::path::PathBuf;
+
+use crockford::crockford_entropy_lower;
+
 use crate::public::media_uploads::directory::MediaUploadDirectory;
 use crate::public::public_path::PublicPath;
-use crockford::crockford_entropy_lower;
-use std::path::PathBuf;
 
 const ORIGINAL_FILE_BASENAME : &str = "original_upload.bin";
 
@@ -57,9 +59,10 @@ impl MediaUploadOriginalFilePath {
 
 #[cfg(test)]
 mod tests {
-  use crate::public::media_uploads::original_file::MediaUploadOriginalFilePath;
   use std::path::PathBuf;
 
+  use crate::public::media_uploads::original_file::MediaUploadOriginalFilePath;
+
   #[test]
   pub fn generate_new_entropy() {
     let file = MediaUploadOriginalFilePath::generate_new();

crates/_types/buckets/src/public/voice_conversion_results/original_file.rs

@@ -1,7 +1,9 @@
+use std::path::PathBuf;
+
+use crockford::crockford_entropy_lower;
+
 use crate::public::public_path::PublicPath;
 use crate::public::voice_conversion_results::directory::VoiceConversionResultDirectory;
-use crockford::crockford_entropy_lower;
-use std::path::PathBuf;
 
 const ORIGINAL_FILE_PREFIX : &str = "fakeyou_";
 const ORIGINAL_FILE_SUFFIX : &str = ".wav";
@@ -61,9 +63,10 @@ impl VoiceConversionResultOriginalFilePath {
 
 #[cfg(test)]
 mod tests {
-  use crate::public::voice_conversion_results::original_file::VoiceConversionResultOriginalFilePath;
   use std::path::PathBuf;
 
+  use crate::public::voice_conversion_results::original_file::VoiceConversionResultOriginalFilePath;
+
   #[test]
   pub fn generate_new_entropy() {
     let file = VoiceConversionResultOriginalFilePath::generate_new();

crates/_types/buckets/src/util/hash_to_bucket_path.rs

@@ -1,6 +1,7 @@
+use std::path::PathBuf;
+
 use errors::AnyhowResult;
 use errors::bail;
-use std::path::PathBuf;
 
 #[deprecated(note = "this is a messy function and should be avoided")]
 pub (crate) fn hash_to_bucket_path(file_hash: &str,
@@ -28,9 +29,10 @@ pub (crate) fn hash_to_bucket_path(file_hash: &str,
 
 #[cfg(test)]
 mod tests {
-  use crate::util::hash_to_bucket_path::hash_to_bucket_path;
   use std::path::PathBuf;
 
+  use crate::util::hash_to_bucket_path::hash_to_bucket_path;
+
   #[test]
   fn test_bucket_without_root() {
     assert_eq!(

crates/_types/buckets/src/util/hash_to_bucket_path_string.rs

@@ -1,6 +1,7 @@
-use crate::util::hash_to_bucket_path::hash_to_bucket_path;
-use errors::AnyhowResult;
 use errors::anyhow;
+use errors::AnyhowResult;
+
+use crate::util::hash_to_bucket_path::hash_to_bucket_path;
 
 #[deprecated(note = "this is a messy function and should be avoided")]
 pub fn hash_to_bucket_path_string(file_hash: &str,

crates/_types/enums/Cargo.toml

@@ -16,8 +16,6 @@ path = "src/lib.rs"
 # Serialization
 serde = "1.0.125"
 serde_derive = "1.0.125"
-serde_json = "1.0.64"
-toml = "0.5.8"
 
 # MySql Database
 # "offline" supports typesafe builds without a live database
@@ -25,8 +23,13 @@ toml = "0.5.8"
 # https://github.com/launchbadge/sqlx/issues/1095#issuecomment-798050828
 sqlx = { version = "0.6.2", features = [ "mysql", "sqlite", "runtime-actix-native-tls", "offline", "chrono" ] }
 sqlx-core = { version = "0.6.2" }
+my-workspace-hack = { version = "0.1", path = "../../../my-workspace-hack" }
 
 [dev-dependencies]
 
 # Enum utilities
-strum = { version = "0.24", features = ["derive"] }
+strum = { version = "0.25", features = ["derive"] }
+
+# Test serialization
+serde_json = "1.0.108"
+toml = "0.5.8"

crates/_types/enums/src/by_table/audit_logs/audit_log_entity_action.rs

@@ -1,6 +1,5 @@
 use serde::Deserialize;
 use serde::Serialize;
-
 #[cfg(test)]
 use strum::EnumCount;
 #[cfg(test)]

crates/_types/enums/src/by_table/audit_logs/audit_log_entity_type.rs

@@ -1,6 +1,5 @@
 use serde::Deserialize;
 use serde::Serialize;
-
 #[cfg(test)]
 use strum::EnumCount;
 #[cfg(test)]

crates/_types/enums/src/by_table/comments/comment_entity_type.rs

@@ -1,6 +1,5 @@
 use serde::Deserialize;
 use serde::Serialize;
-
 #[cfg(test)]
 use strum::EnumCount;
 #[cfg(test)]

crates/_types/enums/src/by_table/email_sender_jobs/email_category.rs

@@ -0,0 +1,115 @@
+use std::collections::BTreeSet;
+
+#[cfg(test)]
+use strum::EnumCount;
+#[cfg(test)]
+use strum::EnumIter;
+
+/// Used in the `email_sender_jobs` table in `VARCHAR(32)` field `id_category`.
+///
+/// This denotes the type of email being sent.
+///
+/// DO NOT CHANGE VALUES WITHOUT A MIGRATION STRATEGY.
+#[cfg_attr(test, derive(EnumIter, EnumCount))]
+#[derive(Clone, Copy, Eq, PartialEq, Hash, Ord, PartialOrd, Deserialize, Serialize)]
+pub enum EmailCategory {
+  /// User is recently registered
+  #[serde(rename = "welcome")]
+  Welcome,
+
+  /// User is resetting their password
+  #[serde(rename = "password_reset")]
+  PasswordReset,
+}
+
+// TODO(bt, 2022-12-21): This desperately needs MySQL integration tests!
+impl_enum_display_and_debug_using_to_str!(EmailCategory);
+impl_mysql_enum_coders!(EmailCategory);
+
+/// NB: Legacy API for older code.
+impl EmailCategory {
+  pub fn to_str(&self) -> &'static str {
+    match self {
+      Self::Welcome => "welcome",
+      Self::PasswordReset => "password_reset",
+    }
+  }
+
+  pub fn from_str(value: &str) -> Result<Self, String> {
+    match value {
+      "welcome" => Ok(Self::Welcome),
+      "password_reset" => Ok(Self::PasswordReset),
+      _ => Err(format!("invalid value: {:?}", value)),
+    }
+  }
+
+  pub fn all_variants() -> BTreeSet<Self> {
+    // NB: BTreeSet is sorted
+    // NB: BTreeSet::from() isn't const, but not worth using LazyStatic, etc.
+    BTreeSet::from([
+      Self::Welcome,
+      Self::PasswordReset,
+    ])
+  }
+}
+
+#[cfg(test)]
+mod tests {
+  use crate::by_table::email_sender_jobs::email_category::EmailCategory;
+  use crate::test_helpers::assert_serialization;
+
+  mod explicit_checks {
+    use super::*;
+
+    #[test]
+    fn test_serialization() {
+      assert_serialization(EmailCategory::Welcome, "welcome");
+      assert_serialization(EmailCategory::PasswordReset, "password_reset");
+    }
+
+    #[test]
+    fn to_str() {
+      assert_eq!(EmailCategory::Welcome.to_str(), "welcome");
+      assert_eq!(EmailCategory::PasswordReset.to_str(), "password_reset");
+    }
+
+    #[test]
+    fn from_str() {
+      assert_eq!(EmailCategory::from_str("welcome").unwrap(), EmailCategory::Welcome);
+      assert_eq!(EmailCategory::from_str("password_reset").unwrap(), EmailCategory::PasswordReset);
+    }
+
+    #[test]
+    fn all_variants() {
+      // Static check
+      let mut variants = EmailCategory::all_variants();
+      assert_eq!(variants.len(), 2);
+      assert_eq!(variants.pop_first(), Some(EmailCategory::Welcome));
+      assert_eq!(variants.pop_first(), Some(EmailCategory::PasswordReset));
+      assert_eq!(variants.pop_first(), None);
+
+      // Generated check
+      use strum::IntoEnumIterator;
+      assert_eq!(EmailCategory::all_variants().len(), EmailCategory::iter().len());
+    }
+  }
+
+  mod mechanical_checks {
+    use super::*;
+
+    #[test]
+    fn variant_length() {
+      use strum::IntoEnumIterator;
+      assert_eq!(EmailCategory::all_variants().len(), EmailCategory::iter().len());
+    }
+
+    #[test]
+    fn round_trip() {
+      for variant in EmailCategory::all_variants() {
+        assert_eq!(variant, EmailCategory::from_str(variant.to_str()).unwrap());
+        assert_eq!(variant, EmailCategory::from_str(&format!("{}", variant)).unwrap());
+        assert_eq!(variant, EmailCategory::from_str(&format!("{:?}", variant)).unwrap());
+      }
+    }
+  }
+}

crates/_types/enums/src/by_table/email_sender_jobs/mod.rs

@@ -0,0 +1 @@
+pub mod email_category;