Fix QueryBuilder with respect to column names and type juggling

list media files for user

crates/_types/enums/src/by_table/media_files/media_file_origin_category.rs

@@ -1,4 +1,5 @@
 use std::collections::BTreeSet;
+
 use serde::Deserialize;
 use serde::Serialize;
 #[cfg(test)]
@@ -29,6 +30,7 @@ pub enum MediaFileOriginCategory {
 // TODO(bt, 2022-12-21): This desperately needs MySQL integration tests!
 impl_enum_display_and_debug_using_to_str!(MediaFileOriginCategory);
 impl_mysql_enum_coders!(MediaFileOriginCategory);
+impl_mysql_from_row!(MediaFileOriginCategory);
 
 /// NB: Legacy API for older code.
 impl MediaFileOriginCategory {

crates/_types/enums/src/by_table/media_files/media_file_origin_model_type.rs

@@ -24,6 +24,7 @@ pub enum MediaFileOriginModelType {
   /// so-vits-svc voice conversion models
   #[serde(rename = "so_vits_svc")]
   SoVitsSvc,
+
   #[serde(rename = "vall_e_x")]
   VallEX,
 }
@@ -31,6 +32,7 @@ pub enum MediaFileOriginModelType {
 // TODO(bt, 2022-12-21): This desperately needs MySQL integration tests!
 impl_enum_display_and_debug_using_to_str!(MediaFileOriginModelType);
 impl_mysql_enum_coders!(MediaFileOriginModelType);
+impl_mysql_from_row!(MediaFileOriginModelType);
 
 /// NB: Legacy API for older code.
 impl MediaFileOriginModelType {

crates/_types/enums/src/by_table/media_files/media_file_origin_product_category.rs

@@ -39,6 +39,7 @@ pub enum MediaFileOriginProductCategory {
 // TODO(bt, 2022-12-21): This desperately needs MySQL integration tests!
 impl_enum_display_and_debug_using_to_str!(MediaFileOriginProductCategory);
 impl_mysql_enum_coders!(MediaFileOriginProductCategory);
+impl_mysql_from_row!(MediaFileOriginProductCategory);
 
 /// NB: Legacy API for older code.
 impl MediaFileOriginProductCategory {

crates/_types/enums/src/by_table/media_files/media_file_type.rs

@@ -25,6 +25,7 @@ pub enum MediaFileType {
 // TODO(bt, 2022-12-21): This desperately needs MySQL integration tests!
 impl_enum_display_and_debug_using_to_str!(MediaFileType);
 impl_mysql_enum_coders!(MediaFileType);
+impl_mysql_from_row!(MediaFileType);
 
 /// NB: Legacy API for older code.
 impl MediaFileType {

crates/_types/enums/src/common/visibility.rs

@@ -38,6 +38,7 @@ pub enum Visibility {
 
 
 impl_enum_display_and_debug_using_to_str!(Visibility);
+impl_mysql_from_row!(Visibility);
 
 // For reference, here's what the serde implementation might be if manually written.
 // This may be useful for designing composite types in the future:

crates/_types/enums/src/lib.rs

@@ -32,3 +32,4 @@ mod macros;
 
 pub mod by_table;
 pub mod common;
+pub mod traits;

crates/_types/enums/src/macros/impl_mysql_from_row.rs

@@ -0,0 +1,63 @@
+
+/// Implement `MySqlFromRow` on a type.
+macro_rules! impl_mysql_from_row {
+  ($t:ident) => {
+
+    // Try to convert a MySQL row and named field into the value type (for non-nullable fields).
+    impl crate::traits::mysql_from_row::MySqlFromRow<$t> for $t {
+      fn try_from_mysql_row(row: &sqlx_mysql::MySqlRow, field_name: &str) -> Result<$t, sqlx::Error> {
+        use sqlx::{Error, Row};
+
+        // NB(bt,2023-12-05): For now only string encodings are considered.
+        // We may want to revisit in the future if we deal with binary data.
+        let value : String = row.try_get(field_name)?;
+
+        let output_type = Self::from_str(&value)
+            .map_err(|err| {
+              Error::ColumnDecode {
+                index: format!("mysql_from_row failure on field {}: {:?}", field_name, err),
+                source: format!("mysql_from_row failure on field {}: {:?}", field_name, err).into(),
+              }
+            })?;
+
+        Ok(output_type)
+      }
+
+      // Try to convert a MySQL row and named field into the value type (for nullable fields).
+      fn try_from_mysql_row_nullable(row: &sqlx_mysql::MySqlRow, field_name: &str) -> Result<Option<$t>, sqlx::Error> {
+        use sqlx::{Error, Row};
+
+        // NB(bt,2023-12-05): For now only string encodings are considered.
+        // We may want to revisit in the future if we deal with binary data.
+        // NB(2): Nullable fields decode as Option<T>.
+        let maybe_value : Option<String> = row.try_get(field_name)?;
+
+        let value = match maybe_value {
+          Some(value) => value,
+          None => return Ok(None),
+        };
+
+        let output_type = Self::from_str(&value)
+            .map_err(|err| {
+              Error::ColumnDecode {
+                index: format!("mysql_from_row failure on field {}: {:?}", field_name, err),
+                source: format!("mysql_from_row failure on field {}: {:?}", field_name, err).into(),
+              }
+            })?;
+
+        Ok(Some(output_type))
+      }
+    }
+
+    //// TODO: Tests.
+    //#[cfg(test)]
+    //#[test]
+    //fn test_name() {
+    //  use strum::IntoEnumIterator;
+    //  for variant in $t::iter() {
+    //    assert_eq!(format!("{}", variant), variant.to_str());
+    //  }
+    //}
+
+  }
+}

crates/_types/enums/src/macros/mod.rs

@@ -1,3 +1,4 @@
 #[macro_use] pub mod impl_enum_display_and_debug_using_to_str;
 #[macro_use] pub mod impl_mysql_enum_coders;
+#[macro_use] pub mod impl_mysql_from_row;
 #[macro_use] pub mod impl_sqlite_enum_coders;

crates/_types/enums/src/traits/mod.rs

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

crates/_types/enums/src/traits/mysql_from_row.rs

@@ -0,0 +1,11 @@
+use sqlx_mysql::MySqlRow;
+
+/// This is a trait (in conjunction with the macro implementation) that makes up for sqlx's
+/// QueryBuilder not playing well with FromRow implementation or derivation of named types.
+pub trait MySqlFromRow<T> {
+
+  /// Attempt to parse a named column from a MySqlRow into the type.
+  fn try_from_mysql_row(row: &MySqlRow, field_name: &str) -> Result<T, sqlx::Error>;
+
+  fn try_from_mysql_row_nullable(row: &MySqlRow, field_name: &str) -> Result<Option<T>, sqlx::Error>;
+}

crates/lib/mysql_queries/src/queries/media_files/list_media_files_for_user.rs

@@ -0,0 +1,242 @@
+use chrono::{DateTime, Utc};
+use sqlx::{FromRow, MySql, MySqlPool, QueryBuilder, Row};
+use sqlx::mysql::MySqlRow;
+
+use enums::by_table::media_files::media_file_origin_category::MediaFileOriginCategory;
+use enums::by_table::media_files::media_file_origin_model_type::MediaFileOriginModelType;
+use enums::by_table::media_files::media_file_origin_product_category::MediaFileOriginProductCategory;
+use enums::by_table::media_files::media_file_type::MediaFileType;
+use enums::common::visibility::Visibility;
+use enums::traits::mysql_from_row::MySqlFromRow;
+use errors::AnyhowResult;
+use tokens::tokens::media_files::MediaFileToken;
+
+pub struct MediaFileListPage {
+  pub records: Vec<MediaFileListItem>,
+
+  pub sort_ascending: bool,
+
+  /// ID of the first record.
+  pub first_id: Option<i64>,
+
+  /// ID of the last record.
+  pub last_id: Option<i64>,
+}
+
+pub struct MediaFileListItem {
+  pub token: MediaFileToken,
+
+  pub origin_category: MediaFileOriginCategory,
+  pub origin_product_category: MediaFileOriginProductCategory,
+  pub maybe_origin_model_type: Option<MediaFileOriginModelType>,
+  pub maybe_origin_model_token: Option<String>,
+
+  pub media_type: MediaFileType,
+  pub public_bucket_directory_hash: String,
+  pub maybe_public_bucket_prefix: Option<String>,
+  pub maybe_public_bucket_extension: Option<String>,
+
+  pub creator_set_visibility: Visibility,
+
+  pub created_at: DateTime<Utc>,
+  pub updated_at: DateTime<Utc>,
+}
+
+#[derive(Clone, Copy)]
+pub enum ViewAs {
+  Author,
+  Moderator,
+  AnotherUser,
+}
+
+pub struct ListMediaFileForUserArgs<'a> {
+  pub username: &'a str,
+  pub limit: usize,
+  pub maybe_filter_media_type: Option<MediaFileType>,
+  pub maybe_offset: Option<usize>,
+  pub cursor_is_reversed: bool,
+  pub view_as: ViewAs,
+  pub mysql_pool: &'a MySqlPool,
+}
+
+pub async fn list_media_files_for_user(args: ListMediaFileForUserArgs<'_>) -> AnyhowResult<MediaFileListPage> {
+
+  let mut query = query_builder(
+    args.maybe_filter_media_type,
+    args.username,
+    args.limit,
+    args.maybe_offset,
+    args.cursor_is_reversed,
+    args.view_as,
+  );
+
+  let query = query.build_query_as::<MediaFileListItemInternal>();
+
+  let results = query.fetch_all(args.mysql_pool).await?;
+
+  let first_id = results.first()
+      .map(|raw_result| raw_result.id);
+
+  let last_id = results.last()
+      .map(|raw_result| raw_result.id);
+
+  let results = results.into_iter()
+      .map(|record| {
+        MediaFileListItem {
+          token: record.token,
+          origin_category: record.origin_category,
+          origin_product_category: record.origin_product_category,
+          maybe_origin_model_type: record.maybe_origin_model_type,
+          maybe_origin_model_token: record.maybe_origin_model_token,
+          media_type: record.media_type,
+          public_bucket_directory_hash: record.public_bucket_directory_hash,
+          maybe_public_bucket_prefix: record.maybe_public_bucket_prefix,
+          maybe_public_bucket_extension: record.maybe_public_bucket_extension,
+          creator_set_visibility: record.creator_set_visibility,
+          created_at: record.created_at,
+          updated_at: record.updated_at,
+        }
+      })
+      .collect::<Vec<_>>();
+
+  Ok(MediaFileListPage {
+    records: results,
+    sort_ascending: !args.cursor_is_reversed,
+    first_id,
+    last_id,
+  })
+}
+
+fn query_builder<'a>(
+  maybe_filter_media_type: Option<MediaFileType>,
+  username: &'a str,
+  limit: usize,
+  maybe_offset: Option<usize>,
+  cursor_is_reversed: bool,
+  view_as: ViewAs,
+) -> QueryBuilder<'a, MySql> {
+
+  // NB: Query cannot be statically checked by sqlx
+  let mut query_builder: QueryBuilder<MySql> = QueryBuilder::new(
+    r#"
+SELECT
+  m.id,
+  m.token,
+
+  m.origin_category,
+  m.origin_product_category,
+
+  m.maybe_origin_model_type,
+  m.maybe_origin_model_token,
+
+  m.media_type,
+
+  m.public_bucket_directory_hash,
+  m.maybe_public_bucket_prefix,
+  m.maybe_public_bucket_extension,
+
+  m.creator_set_visibility,
+  m.created_at,
+  m.updated_at
+
+FROM media_files AS m
+LEFT OUTER JOIN users AS u
+    ON m.maybe_creator_user_token = u.token
+
+WHERE m.user_deleted_at IS NULL
+  AND m.mod_deleted_at IS NULL
+    "#
+  );
+
+  query_builder.push(" AND u.username = ");
+  query_builder.push_bind(username);
+
+  if let Some(media_type) = maybe_filter_media_type {
+    // FIXME: Binding shouldn't require to_str().
+    //  Otherwise, it's calling the Display trait on the raw type which is resulting in an
+    //  incorrect binding and runtime error.
+    query_builder.push(" AND m.media_file_type = ");
+  }
+
+  match view_as {
+    ViewAs::Author => {}
+    ViewAs::Moderator => {}
+    ViewAs::AnotherUser => {
+      // FIXME: Binding shouldn't require to_str().
+      //  Otherwise, it's calling the Display trait on the raw type which is resulting in an
+      //  incorrect binding and runtime error.
+      query_builder.push(" AND m.creator_set_visibility = ");
+      query_builder.push_bind(Visibility::Public.to_str());
+    }
+  }
+
+  if let Some(offset) = maybe_offset {
+    if cursor_is_reversed {
+      query_builder.push(format!(" AND m.id < {offset} "));
+    } else {
+      query_builder.push(format!(" AND m.id > {offset} "));
+    }
+  }
+
+  if cursor_is_reversed {
+    query_builder.push(" ORDER BY m.id ASC ");
+  } else {
+    query_builder.push(" ORDER BY m.id DESC ");
+  }
+
+  query_builder.push(format!(" LIMIT {limit} "));
+
+  query_builder
+}
+
+struct MediaFileListItemInternal {
+  id: i64,
+  token: MediaFileToken,
+
+  origin_category: MediaFileOriginCategory,
+  origin_product_category: MediaFileOriginProductCategory,
+  maybe_origin_model_type: Option<MediaFileOriginModelType>,
+  maybe_origin_model_token: Option<String>,
+
+  media_type: MediaFileType,
+  public_bucket_directory_hash: String,
+  maybe_public_bucket_prefix: Option<String>,
+  maybe_public_bucket_extension: Option<String>,
+
+  creator_set_visibility: Visibility,
+
+  created_at: DateTime<Utc>,
+  updated_at: DateTime<Utc>,
+}
+
+// NB(bt,2023-12-05): There's an issue with type hinting in the `as` clauses with QueryBuilder (or
+// raw query strings) and sqlx::FromRow, regardless of whether it is derived of manually
+// implemented. Perhaps this will improve in the future, but for now manually constructed queries
+// cannot have type hints, eg. the following:
+//
+//    m.token as `token: tokens::tokens::media_files::MediaFileToken`,
+//    m.origin_category as `origin_category: enums::by_table::media_files::media_file_origin_category::MediaFileOriginCategory`,
+//    m.creator_set_visibility as `creator_set_visibility: enums::common::visibility::Visibility`,
+//
+// This results in the automatic mapping not being able to be found by name (for macro derive), and
+// in the manual case `row.try_get()` etc. won't have the correct column name (since the name is the
+// full "as" clause).
+impl FromRow<'_, MySqlRow> for MediaFileListItemInternal {
+  fn from_row(row: &MySqlRow) -> Result<Self, sqlx::Error> {
+    Ok(Self {
+      id: row.try_get("id")?,
+      token: MediaFileToken::new(row.try_get("token")?),
+      origin_category: MediaFileOriginCategory::try_from_mysql_row(row, "origin_category")?,
+      origin_product_category: MediaFileOriginProductCategory::try_from_mysql_row(row, "origin_product_category")?,
+      maybe_origin_model_type: MediaFileOriginModelType::try_from_mysql_row_nullable(row, "maybe_origin_model_type")?,
+      maybe_origin_model_token: row.try_get("maybe_origin_model_token")?,
+      media_type: MediaFileType::try_from_mysql_row(row, "media_type")?,
+      public_bucket_directory_hash: row.try_get("public_bucket_directory_hash")?,
+      maybe_public_bucket_prefix: row.try_get("maybe_public_bucket_prefix")?,
+      maybe_public_bucket_extension: row.try_get("maybe_public_bucket_extension")?,
+      creator_set_visibility: Visibility::try_from_mysql_row(row, "creator_set_visibility")?,
+      created_at: row.try_get("created_at")?,
+      updated_at: row.try_get("updated_at")?,
+    })
+  }
+}

crates/lib/mysql_queries/src/queries/media_files/mod.rs

@@ -1,7 +1,8 @@
-pub mod get_media_file;
-pub mod insert_media_file_from_face_animation;
-pub mod insert_media_file_from_zero_shot_tts;
-pub mod insert_media_file_from_cli_tool;
-pub mod insert_media_file_from_voice_conversion;
 pub mod delete_media_file;
+pub mod get_media_file;
+pub mod insert_media_file_from_cli_tool;
+pub mod insert_media_file_from_face_animation;
+pub mod insert_media_file_from_voice_conversion;
+pub mod insert_media_file_from_zero_shot_tts;
+pub mod list_media_files_for_user;
 pub mod update_media_file;

crates/service/web/storyteller_web/src/http_server/common_responses/mod.rs

@@ -1 +1,3 @@
+pub mod pagination_cursors;
+pub mod pagination_page;
 pub mod user_details_lite;

crates/service/web/storyteller_web/src/http_server/common_responses/pagination_cursors.rs

@@ -0,0 +1,53 @@
+/// Pagination by cursors
+/// These types of pagination are for "infinite scrolling", which do not reveal a number of pages.
+/// This is good so that investors and competitors cannot reveal how many database records we have.
+/// This is typically used for "discovery" type pages, not user profiles.
+///
+/// See `PaginationPage` for the other type of pagination.
+#[derive(Serialize)]
+pub struct PaginationCursors {
+
+  /// The "next" cursor.
+  /// This is an opaque (typically even encrypted) handle.
+  pub maybe_next: Option<String>,
+
+  /// The "previous" cursor.
+  /// This is an opaque (typically even encrypted) handle.
+  pub maybe_previous: Option<String>,
+
+  /// Details whether we're walking forward or backward.
+  pub cursor_is_reversed: bool,
+}
+
+#[cfg(test)]
+mod tests {
+  use crate::http_server::common_responses::pagination_cursors::PaginationCursors;
+
+  #[test]
+  fn test_serialize_empty_json() {
+    // Make sure this interface doesn't change.
+    let pagination = PaginationCursors {
+      maybe_next: None,
+      maybe_previous: None,
+      cursor_is_reversed: false,
+    };
+
+    let json = serde_json::to_string(&pagination).expect("serialization");
+
+    assert_eq!(json, "{\"maybe_next\":null,\"maybe_previous\":null,\"cursor_is_reversed\":false}")
+  }
+
+  #[test]
+  fn test_serialize_full_json() {
+    // Make sure this interface doesn't change.
+    let pagination = PaginationCursors {
+      maybe_next: Some("foo".to_string()),
+      maybe_previous: Some("bar".to_string()),
+      cursor_is_reversed: true,
+    };
+
+    let json = serde_json::to_string(&pagination).expect("serialization");
+
+    assert_eq!(json, "{\"maybe_next\":\"foo\",\"maybe_previous\":\"bar\",\"cursor_is_reversed\":true}")
+  }
+}

crates/service/web/storyteller_web/src/http_server/common_responses/pagination_page.rs

@@ -0,0 +1,33 @@
+/// Pagination by page id
+/// This type of pagination is by "page id" and "page count".
+/// This should never be used to walk the entire public database as competitors and investors
+/// can use it to glean information about the scale of our service.
+/// This is best used for user profiles and scoped down results.
+///
+/// See `PaginationCursors` for the other type of pagination.
+#[derive(Serialize)]
+pub struct PaginationPage {
+  /// The current page number
+  pub current: u64,
+
+  /// How many pages there are in total
+  pub total_page_count: u64,
+}
+
+#[cfg(test)]
+mod tests {
+  use crate::http_server::common_responses::pagination_page::PaginationPage;
+
+  #[test]
+  fn test_serialize() {
+    let pagination = PaginationPage {
+      current: 5,
+      total_page_count: 123,
+
+    };
+
+    let json = serde_json::to_string(&pagination).expect("serialization");
+
+    assert_eq!(json, "{\"current\":5,\"total_page_count\":123}")
+  }
+}

crates/service/web/storyteller_web/src/http_server/endpoints/media_files/list_media_files_for_user.rs

@@ -0,0 +1,218 @@
+use std::sync::Arc;
+
+use actix_web::{HttpMessage, HttpRequest, HttpResponse, web};
+use actix_web::error::ResponseError;
+use actix_web::http::StatusCode;
+use actix_web::web::{Path, Query};
+use chrono::{DateTime, Utc};
+use log::{info, warn};
+
+use enums::by_table::media_files::media_file_origin_category::MediaFileOriginCategory;
+use enums::by_table::media_files::media_file_origin_model_type::MediaFileOriginModelType;
+use enums::by_table::media_files::media_file_origin_product_category::MediaFileOriginProductCategory;
+use enums::by_table::media_files::media_file_type::MediaFileType;
+use enums::common::visibility::Visibility;
+use mysql_queries::queries::media_files::list_media_files_for_user::{list_media_files_for_user, ListMediaFileForUserArgs, ViewAs};
+use tokens::tokens::media_files::MediaFileToken;
+
+use crate::http_server::common_responses::pagination_cursors::PaginationCursors;
+use crate::http_server::web_utils::response_error_helpers::to_simple_json_error;
+use crate::server_state::ServerState;
+
+#[derive(Deserialize)]
+pub struct PathInfo {
+  username: String,
+}
+
+#[derive(Deserialize)]
+pub struct QueryParams {
+  pub sort_ascending: Option<bool>,
+  pub limit: Option<usize>,
+  pub cursor: Option<String>,
+  pub cursor_is_reversed: Option<bool>,
+  pub filter_media_type: Option<MediaFileType>,
+}
+
+#[derive(Serialize)]
+pub struct SuccessResponse {
+  pub success: bool,
+  pub results: Vec<MediaFileListItem>,
+  pub pagination: PaginationCursors,
+}
+
+#[derive(Serialize)]
+pub struct MediaFileListItem {
+  pub token: MediaFileToken,
+
+  pub origin_category: MediaFileOriginCategory,
+  pub origin_product_category: MediaFileOriginProductCategory,
+  pub maybe_origin_model_type: Option<MediaFileOriginModelType>,
+  pub maybe_origin_model_token: Option<String>,
+
+  pub media_type: MediaFileType,
+  pub public_bucket_directory_hash: String,
+  pub maybe_public_bucket_prefix: Option<String>,
+  pub maybe_public_bucket_extension: Option<String>,
+
+  pub creator_set_visibility: Visibility,
+
+  pub created_at: DateTime<Utc>,
+  pub updated_at: DateTime<Utc>,
+}
+
+#[derive(Debug)]
+pub enum ErrorResponse {
+  ServerError,
+}
+
+impl ResponseError for ErrorResponse {
+  fn status_code(&self) -> StatusCode {
+    match *self {
+      ErrorResponse::ServerError => StatusCode::INTERNAL_SERVER_ERROR,
+    }
+  }
+
+  fn error_response(&self) -> HttpResponse {
+    let error_reason = match self {
+      ErrorResponse::ServerError => "server error".to_string(),
+    };
+
+    to_simple_json_error(&error_reason, self.status_code())
+  }
+}
+
+impl std::fmt::Display for ErrorResponse {
+  fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
+    write!(f, "{:?}", self)
+  }
+}
+
+pub async fn list_media_files_for_user_handler(
+  http_request: HttpRequest,
+  path: Path<PathInfo>,
+  query: Query<QueryParams>,
+  server_state: web::Data<Arc<ServerState>>
+) -> Result<HttpResponse, ErrorResponse>
+{
+  info!("Fetching inference results for user: {}", &path.username);
+
+  let maybe_user_session = server_state
+      .session_checker
+      .maybe_get_user_session(&http_request, &server_state.mysql_pool)
+      .await
+      .map_err(|e| {
+        warn!("Session checker error: {:?}", e);
+        ErrorResponse::ServerError
+      })?;
+
+  let mut is_author = false;
+  let mut is_mod = false;
+
+  match maybe_user_session {
+    None => {},
+    Some(session) => {
+      is_author = session.username == path.username;
+      is_mod = session.can_ban_users;
+    },
+  };
+
+  // TODO(bt,2023-12-04): Enforce real maximums and defaults
+  let limit = query.limit.unwrap_or(25);
+
+  let sort_ascending = query.sort_ascending.unwrap_or(false);
+  let cursor_is_reversed = query.cursor_is_reversed.unwrap_or(false);
+
+  let cursor = if let Some(cursor) = query.cursor.as_deref() {
+    let cursor = server_state.sort_key_crypto.decrypt_id(cursor)
+        .map_err(|e| {
+          warn!("crypto error: {:?}", e);
+          ErrorResponse::ServerError
+        })?;
+    Some(cursor as usize)
+  } else {
+    None
+  };
+
+  let view_as = if is_author {
+    ViewAs::Author
+  } else if is_mod {
+    ViewAs::Moderator
+  } else {
+    ViewAs::AnotherUser
+  };
+
+  let query_results = list_media_files_for_user(ListMediaFileForUserArgs {
+    username: &path.username,
+    limit,
+    maybe_filter_media_type: query.filter_media_type,
+    maybe_offset: cursor,
+    cursor_is_reversed,
+    view_as,
+    mysql_pool: &server_state.mysql_pool,
+  }).await;
+
+
+  let results_page = match query_results {
+    Ok(results) => results,
+    Err(e) => {
+      warn!("Query error: {:?}", e);
+      return Err(ErrorResponse::ServerError);
+    }
+  };
+
+  let cursor_next = if let Some(id) = results_page.last_id {
+    let cursor = server_state.sort_key_crypto.encrypt_id(id as u64)
+        .map_err(|e| {
+          warn!("crypto error: {:?}", e);
+          ErrorResponse::ServerError
+        })?;
+    Some(cursor)
+  } else {
+    None
+  };
+
+  let cursor_previous = if let Some(id) = results_page.first_id {
+    let cursor = server_state.sort_key_crypto.encrypt_id(id as u64)
+        .map_err(|e| {
+          warn!("crypto error: {:?}", e);
+          ErrorResponse::ServerError
+        })?;
+    Some(cursor)
+  } else {
+    None
+  };
+
+  let results = results_page.records.into_iter()
+      .map(|record| MediaFileListItem {
+        token: record.token,
+        origin_category: record.origin_category,
+        origin_product_category: record.origin_product_category,
+        maybe_origin_model_type: record.maybe_origin_model_type,
+        maybe_origin_model_token: record.maybe_origin_model_token,
+        media_type: record.media_type,
+        public_bucket_directory_hash: record.public_bucket_directory_hash,
+        maybe_public_bucket_prefix: record.maybe_public_bucket_prefix,
+        maybe_public_bucket_extension: record.maybe_public_bucket_extension,
+        creator_set_visibility: record.creator_set_visibility,
+        created_at: record.created_at,
+        updated_at: record.updated_at,
+      })
+      .collect::<Vec<_>>();
+
+  let response = SuccessResponse {
+    success: true,
+    results,
+    pagination: PaginationCursors {
+      maybe_next: cursor_next,
+      maybe_previous: cursor_previous,
+      cursor_is_reversed,
+    }
+  };
+
+  let body = serde_json::to_string(&response)
+      .map_err(|e| ErrorResponse::ServerError)?;
+
+  Ok(HttpResponse::Ok()
+      .content_type("application/json")
+      .body(body))
+}

crates/service/web/storyteller_web/src/http_server/endpoints/media_files/mod.rs

@@ -1,3 +1,4 @@
+pub mod delete_media_file;
 pub mod drain_multipart_request;
 pub mod get_media_file;
-pub mod delete_media_file;
+pub mod list_media_files_for_user;

crates/service/web/storyteller_web/src/routes.rs

@@ -44,6 +44,7 @@ use crate::http_server::endpoints::investor_demo::enable_demo_mode_handler::enab
 use crate::http_server::endpoints::leaderboard::get_leaderboard::leaderboard_handler;
 use crate::http_server::endpoints::media_files::delete_media_file::delete_media_file_handler;
 use crate::http_server::endpoints::media_files::get_media_file::get_media_file_handler;
+use crate::http_server::endpoints::media_files::list_media_files_for_user::list_media_files_for_user_handler;
 use crate::http_server::endpoints::media_uploads::list_user_media_uploads_of_type::list_user_media_uploads_of_type_handler;
 use crate::http_server::endpoints::media_uploads::upload_audio::upload_audio_handler;
 use crate::http_server::endpoints::media_uploads::upload_image::upload_image_handler;
@@ -1006,6 +1007,10 @@ fn add_media_file_routes<T, B> (app: App<T>) -> App<T>
           .route(web::delete().to(delete_media_file_handler))
           .route(web::head().to(|| HttpResponse::Ok()))
       )
+      .service(web::resource("/user/{username}")
+          .route(web::get().to(list_media_files_for_user_handler))
+          .route(web::head().to(|| HttpResponse::Ok()))
+      )
   )
 }