Path: src/schemas/v1/db/queries/metadata.rs

• async fn create_meta_table
• async fn get_meta_value
• async fn set_meta_value
• async fn meta_table_exists
• async fn cleanup_duplicate_keys
• async fn initialize_metadata_system
• struct MetadataCache
• fn new
• async fn get
• async fn set
• async fn refresh_cache

pub async fn create_meta_table(pool: &Pool<MySql>) -> Result<()> {
    // Acquire a lock to ensure only one thread can modify the metadata table at a time
    let _lock = METADATA_MUTEX.lock().await;

Creates the metadata table in the database if it doesn't already exist. This function ensures that the system has a place to store key-value metadata. It uses a mutex to prevent multiple threads from attempting to create the table simultaneously, and includes schema optimizations like indices and uniqueness constraints.

• pool - Database connection pool for executing the query

• Ok(()) - Table was successfully created or already existed
• Err(anyhow::Error) - Failed to create the table

This function acquires the global metadata mutex to ensure thread safety. If the table already exists, it returns immediately without performing any changes.

The metadata table is created with the following schema: - id: Auto-incrementing primary key - key: Unique string identifier (indexed for fast lookups) - value: Text field storing the metadata value - created_at: Timestamp of when the record was created - updated_at: Timestamp of the last update to the record

pub async fn get_meta_value(pool: &Pool<MySql>, key: &str) -> Result<String> {
    // No need for mutex here since we're only reading
    let value = sqlx::query_scalar::<_, String>("SELECT value FROM metadata WHERE `key` = ?")
        .bind(key)
        .fetch_one(pool)
        .await
        .context(format!("Failed to fetch metadata value for key '{}'", key))?;

Retrieves a metadata value by its key. This function performs a simple lookup in the metadata table to retrieve the value associated with the provided key.

• pool - Database connection pool for executing the query
• key - The unique key whose value to retrieve

• Ok(String) - Successfully retrieved the value for the key
• Err(anyhow::Error) - Failed to fetch the value or key doesn't exist

Returns an error if the key doesn't exist in the metadata table or if a database error occurs during the query execution.

This function doesn't acquire the metadata mutex since it only performs a read operation, which doesn't risk data corruption.

pub async fn set_meta_value(pool: &Pool<MySql>, key: &str, value: &str) -> Result<()> {
    // Acquire a lock to ensure only one thread can modify a key at a time
    let _lock = METADATA_MUTEX.lock().await;

Sets a metadata value for a specific key. This function stores a key-value pair in the metadata table. If the key already exists, its value is updated. The operation is performed atomically within a transaction and is protected by a mutex to prevent concurrent modifications.

• pool - Database connection pool for executing the query
• key - The unique key to associate with the value
• value - The value to store

• Ok(()) - Successfully stored the key-value pair
• Err(anyhow::Error) - Failed to store the key-value pair

This function acquires the global metadata mutex to ensure thread safety when multiple threads attempt to modify the same key.

This function uses a database transaction that: 1. Deletes any existing entries with the same key 2. Inserts the new key-value pair If any part of this operation fails, the entire transaction is rolled back, preserving data consistency.

pub async fn meta_table_exists(pool: &Pool<MySql>) -> Result<bool> {
    // No need for mutex here since we're only reading
    meta_table_exists_internal(pool).await
}

/// Internal function to clean up duplicate metadata keys.
///
/// This function identifies and resolves duplicate keys in the metadata table
/// by keeping only the most recently updated entry for each key. It's intended
/// for internal use by functions that already hold the metadata mutex.
///
/// # Arguments
///
/// * `pool` - Database connection pool for executing the query
///
/// # Returns
///
/// * `Ok(usize)` - Number of duplicate entries that were removed
/// * `Err(anyhow::Error)` - Failed to clean up duplicate keys
///
/// # Process
    // ... function definition continues
    // ... function body
}

Checks if the metadata table exists in the database. This function provides a thread-safe way to check if the metadata table has been created in the database.

• pool - Database connection pool for executing the query

• Ok(bool) - True if the table exists, false otherwise
• Err(anyhow::Error) - Failed to check if the table exists

This function doesn't acquire the metadata mutex since it only performs a read operation, which doesn't risk data corruption.

pub async fn cleanup_duplicate_keys(pool: &Pool<MySql>) -> Result<usize> {
    let _lock = METADATA_MUTEX.lock().await;

Cleans up duplicate metadata keys in a thread-safe manner. This function provides a public, mutex-protected interface to clean up duplicate keys in the metadata table. It ensures that only one thread can perform this operation at a time.

• pool - Database connection pool for executing the query

• Ok(usize) - Number of duplicate entries that were removed
• Err(anyhow::Error) - Failed to clean up duplicate keys

This function acquires the global metadata mutex to ensure thread safety when cleaning up duplicate keys.

This function is useful for: - Periodic maintenance of the metadata table - Resolving inconsistencies that might have been introduced by bugs - Cleaning up after schema migrations or application upgrades

pub async fn initialize_metadata_system(pool: &Pool<MySql>) -> Result<()> {
    let _lock = METADATA_MUTEX.lock().await;

Initializes the metadata system, ensuring the table exists and is clean. This function provides a safe way to initialize the metadata system by: 1. Creating the metadata table if it doesn't exist 2. Cleaning up any duplicate keys that might exist It's designed to be called during application startup to ensure the metadata system is in a consistent state before it's used.

• pool - Database connection pool for executing the query

• Ok(()) - Successfully initialized the metadata system
• Err(anyhow::Error) - Failed to initialize the metadata system

This function acquires the global metadata mutex to ensure thread safety during initialization.

If duplicate keys are found and cleaned up during initialization, an error is logged as this could indicate issues with the application's use of the metadata API.

pub struct MetadataCache {
    /// Database connection pool for executing queries
    pool: Pool<MySql>,
    /// In-memory cache of metadata key-value pairs
    cache: HashMap<String, String>,
}

Cache implementation for metadata to reduce database access. This cache maintains an in-memory copy of metadata values to minimize database queries. It provides methods to get and set values, as well as to refresh the entire cache from the database.

• pool - Database connection pool for executing queries
• cache - In-memory hash map storing key-value pairs

This struct is not thread-safe on its own and should either be used from a single thread or wrapped in a synchronization primitive like Arc<Mutex<MetadataCache>>.

The cache can significantly reduce database load for frequently accessed metadata values, but it can become stale if other processes modify the metadata table directly. Use refresh_cache periodically or after expected external changes.

    pub fn new(pool: Pool<MySql>) -> Self {
        Self {
    // ... function body
}

Creates a new metadata cache with an empty cache and the provided database pool.

• pool - Database connection pool for executing queries

A new MetadataCache instance with an empty cache

    pub async fn get(&mut self, key: &str) -> Result<String> {
        // Check cache first
        if let Some(value) = self.cache.get(key) {
    // ... function body
}

Retrieves a metadata value by its key, using the cache when possible. This method first checks the in-memory cache for the requested key. If the key is not found in the cache, it queries the database and updates the cache with the result.

• key - The unique key whose value to retrieve

• Ok(String) - Successfully retrieved the value for the key
• Err(anyhow::Error) - Failed to fetch the value or key doesn't exist

This method: - Returns cached values without querying the database when possible - Automatically populates the cache with values fetched from the database - Does not refresh existing cache entries (use refresh_cache for that)

    pub async fn set(&mut self, key: &str, value: &str) -> Result<()> {
        // Update database
        set_meta_value(&self.pool, key, value).await?;

Sets a metadata value for a specific key and updates the cache. This method updates both the database and the in-memory cache with the new key-value pair. This ensures that subsequent get calls will return the updated value without requiring a database query.

• key - The unique key to associate with the value
• value - The value to store

• Ok(()) - Successfully stored the key-value pair
• Err(anyhow::Error) - Failed to store the key-value pair

If the database update fails, the cache is not updated, ensuring consistency between the cache and the database.

    pub async fn refresh_cache(&mut self) -> Result<()> {
        // Clear cache
        self.cache.clear();

Refreshes the entire cache from the database. This method clears the in-memory cache and reloads all metadata entries from the database. It's useful when the cache might be stale due to external changes to the metadata table.

• Ok(()) - Successfully refreshed the cache
• Err(anyhow::Error) - Failed to refresh the cache

This method is particularly useful in scenarios such as: - After application startup to prime the cache - After scheduled maintenance that might have modified metadata - When cache staleness is detected or suspected - Periodically in long-running applications to ensure cache freshness