Database Schema

The sd-webui-infinite-image-browsing application uses an SQLite database (iib.db by default) to store metadata, tags, and cached information for efficient image browsing and search. Below is a detailed description of its schema.

Tables

1. image

• Purpose: Stores information about individual media files (images, videos, audio) scanned by the application.
• Columns:
  • id: INTEGER, PRIMARY KEY AUTOINCREMENT
  • path: TEXT, UNIQUE - Absolute path to the media file.
  • exif: TEXT - Raw generation information (EXIF/metadata) extracted from the file.
  • size: INTEGER - File size in bytes.
  • date: TEXT - Last modified date of the file (formatted as YYYY-MM-DD HH:MM:SS).
• Indexes:
  • image_idx_path on image(path)

2. tag

• Purpose: Stores all unique tags (e.g., prompt keywords, models, samplers, custom favorites) used in the application.
• Columns:
  • id: INTEGER, PRIMARY KEY AUTOINCREMENT
  • name: TEXT - The tag's name (e.g., "masterpiece", "sdxl", "like").
  • score: INTEGER - (Currently unused in code, default 0).
  • type: TEXT - Category of the tag (e.g., "custom", "pos", "lora", "Model", "Sampler", "size", "Media Type").
  • count: INTEGER - Frequency of the tag's use.
  • color: TEXT, DEFAULT '' - Custom color for the tag (added via ALTER TABLE).
• Constraints:
  • UNIQUE(name, type) ON CONFLICT REPLACE
• Indexes:
  • tag_idx_name on tag(name)
• Initial Data: Inserts a default ("like", 0, "custom", 0) tag if not exists.

3. image_tag

• Purpose: A many-to-many relationship table linking image records to tag records.
• Columns:
  • image_id: INTEGER - Foreign key referencing image.id.
  • tag_id: INTEGER - Foreign key referencing tag.id.
  • created_at: TIMESTAMP - Timestamp when the tag was associated with the image.
• Constraints:
  • PRIMARY KEY (image_id, tag_id)
  • FOREIGN KEY (image_id) REFERENCES image(id)
  • FOREIGN KEY (tag_id) REFERENCES tag(id)

4. folders

• Purpose: Tracks directories that have been scanned for images, storing their last modified date to quickly detect if a rescan/update is needed.
• Columns:
  • id: INTEGER, PRIMARY KEY AUTOINCREMENT
  • path: TEXT - Absolute path to the folder.
  • modified_date: TEXT - Last modified date of the folder (formatted as YYYY-MM-DD HH:MM:SS).
• Indexes:
  • folders_idx_path on folders(path)

5. extra_path

• Purpose: Stores user-defined additional paths for browsing or scanning, distinguishing between different usage types (e.g., scanned, walk-mode, CLI-only access).
• Columns:
  • path: TEXT, PRIMARY KEY - Absolute path.
  • type: TEXT, NOT NULL - Comma-separated string of types (e.g., "scanned+walk").
  • alias: TEXT, DEFAULT '' - User-defined alias for the path.

6. dir_cover_cache

• Purpose: Caches information about media files within a directory, specifically the top 4 to quickly generate folder covers.
• Columns:
  • folder_path: TEXT, PRIMARY KEY
  • modified_time: TEXT - Last modified time of the folder when cache was generated (ISO format).
  • media_files: TEXT - JSON string containing a list of top media file info.

7. global_setting

• Purpose: A key-value store for various application settings and frontend-specific configurations.
• Columns:
  • name: TEXT, PRIMARY KEY - Setting name (key).
  • setting_json: TEXT - JSON string representing the setting's value.
  • created_time: TEXT - Creation timestamp (ISO format).
  • modified_time: TEXT - Last modification timestamp (ISO format).

8. image_embedding

• Purpose: Stores vector embeddings for image prompt texts, used for natural language search and topic clustering.
• Columns:
  • image_id: INTEGER, PRIMARY KEY - Foreign key referencing image.id.
  • model: TEXT, NOT NULL - The embedding model used (e.g., "text-embedding-3-small").
  • dim: INTEGER, NOT NULL - Dimensionality of the embedding vector.
  • text_hash: TEXT, NOT NULL - SHA256 hash of the normalized prompt text, used for incremental updates.
  • vec: BLOB, NOT NULL - The embedding vector stored as a float32 byte array.
  • updated_at: TEXT, NOT NULL - Last update timestamp (ISO format).
• Constraints:
  • FOREIGN KEY (image_id) REFERENCES image(id)
• Indexes:
  • image_embedding_idx_model_hash on image_embedding(model, text_hash)

9. image_embedding_fail

• Purpose: Caches information about failed embedding generation attempts for specific images and models, preventing repeated (and likely to fail) API calls.
• Columns:
  • image_id: INTEGER, NOT NULL - Foreign key referencing image.id.
  • model: TEXT, NOT NULL - The embedding model attempted.
  • text_hash: TEXT, NOT NULL - SHA256 hash of the prompt text.
  • error: TEXT, NOT NULL - Error message from the failed attempt.
  • updated_at: TEXT, NOT NULL - Last update timestamp (ISO format).
• Constraints:
  • PRIMARY KEY (image_id, model, text_hash)
• Indexes:
  • image_embedding_fail_idx_model on image_embedding_fail(model)

10. topic_title_cache

• Purpose: Caches LLM-generated titles and keywords for topic clusters, avoiding redundant LLM API calls.
• Columns:
  • cluster_hash: TEXT, PRIMARY KEY - A hash identifying the specific cluster and parameters.
  • title: TEXT, NOT NULL - The LLM-generated cluster title.
  • keywords: TEXT, NOT NULL - JSON string of LLM-generated keywords.
  • model: TEXT, NOT NULL - The LLM model used for title generation.
  • updated_at: TEXT, NOT NULL - Last update timestamp (ISO format).
• Indexes:
  • topic_title_cache_idx_model on topic_title_cache(model)

11. topic_cluster_cache

• Purpose: Persists the full result of a topic clustering run, including identified clusters and noise, to enable fast retrieval without re-running the entire process if underlying data hasn't changed.
• Columns:
  • cache_key: TEXT, PRIMARY KEY - A hash identifying the specific clustering parameters and input folders.
  • folders: TEXT, NOT NULL - JSON string of the input folders used for clustering.
  • model: TEXT, NOT NULL - The embedding model used for clustering.
  • params: TEXT, NOT NULL - JSON string of clustering parameters (e.g., threshold, min_cluster_size).
  • embeddings_count: INTEGER, NOT NULL - Number of embeddings processed for this cache.
  • embeddings_max_updated_at: TEXT, NOT NULL - Max updated_at from image_embedding records, used to detect staleness.
  • result: TEXT, NOT NULL - JSON string of the full clustering result (clusters, noise, etc.).
  • updated_at: TEXT, NOT NULL - Last update timestamp (ISO format).
• Indexes:
  • topic_cluster_cache_idx_model on topic_cluster_cache(model)