Database Management

Database Architecture

Tag-AI uses SQLite, a powerful file-based database system, to store all photo metadata and tags efficiently.

The database is designed for fast tag-based searches while maintaining data integrity with a normalized schema.

Database Location

The Tag-AI database is stored at:

Windows

%LOCALAPPDATA%\Tag-AI\photos.db

macOS

~/Library/Application Support/Tag-AI/photos.db

Linux

~/.local/share/Tag-AI/photos.db

Database Structure

The Tag-AI database consists of three main tables and a virtual table for full-text search:

Photos Table

Stores information about each processed photo:

• photo_id (TEXT PRIMARY KEY): Unique identifier for the photo
• file_path (TEXT): Path to the photo file on disk
• date_taken (TEXT): Date and time when the photo was taken
• file_hash (TEXT UNIQUE): SHA-256 hash of the file contents

Tags Table

Stores unique tags:

• id (INTEGER PRIMARY KEY): Unique ID for the tag
• tag (TEXT UNIQUE): The actual tag text

Photo_Tags Table

Junction table that associates photos with tags:

• photo_id (TEXT): Reference to the photos table
• tag_id (INTEGER): Reference to the tags table
• PRIMARY KEY (photo_id, tag_id): Composite primary key

Full-Text Search

The database uses a virtual FTS5 table for advanced search capabilities:

• photos_fts: Virtual table for full-text search over photo metadata and tags

Database Maintenance

Database Integrity

Tag-AI maintains database integrity through several mechanisms:

• Foreign key constraints to ensure data consistency
• Transaction-based operations for atomic updates
• Unique constraints to prevent duplicates
• File hash validation to identify already processed files

Optimization

To maintain optimal performance:

• Tag-AI automatically enables WAL (Write-Ahead Logging) mode for better concurrency
• Indexes are created on frequently queried columns
• The FTS5 virtual table is periodically optimized

Database Size

The database size grows based on the number of photos and tags:

• 1,000 photos: ~20MB
• 10,000 photos: ~200MB
• 100,000 photos: ~2GB

Backup and Restore

Database Backup

To back up your Tag-AI database:

1. Close Tag-AI to ensure no active database connections
2. Copy the photos.db file to a safe location
3. You may also want to back up the photos.db-wal and photos.db-shm files if they exist

Database Restore

To restore from a backup:

1. Close Tag-AI
2. Replace the current photos.db file with your backup
3. Delete any existing photos.db-wal and photos.db-shm files
4. Restart Tag-AI

Moving to a New Computer

To transfer your Tag-AI setup to a new computer:

1. Install Tag-AI on the new computer
2. Copy your database backup to the appropriate location on the new computer
3. Update watch folder paths if your file organization has changed

Troubleshooting

Database Corruption

Signs of database corruption:

• Error messages about "malformed" or "corrupt" database
• Unexpected application crashes when searching or scanning
• Missing tags or photos that should be in the database

To address database corruption:

1. Close Tag-AI
2. Restore from a recent backup
3. If no backup is available, you may need to rebuild the database by rescanning your photos

Database Locked Errors

If you encounter "database is locked" errors:

1. Ensure Tag-AI isn't running multiple instances
2. Check if other applications might be accessing the database
3. Restart your computer if the issue persists
4. If problems continue, try closing Tag-AI, waiting a minute, then restarting it

Missing Tags or Photos

If tags or photos appear to be missing from search results:

1. Verify the photos were successfully processed during scanning
2. Check if the search terms match the expected tags
3. Ensure the photos are still in the locations where they were scanned
4. Try running the Purge utility to clean up any missing file references

Advanced Database Operations

Logging and Diagnostics

Tag-AI logs database operations to help with troubleshooting:

• Windows: %LOCALAPPDATA%\Tag-AI\Logs\
• macOS: ~/Library/Logs/Tag-AI/
• Linux: ~/.local/state/Tag-AI/logs/

Manual Database Access

Advanced users can directly examine the database using the SQLite command-line tool or DB Browser for SQLite:

1. Close Tag-AI to avoid concurrent access issues
2. Open the database file with your SQLite tool
3. Use standard SQL queries to examine or modify the data

Database Reset

To completely reset your database and start fresh:

1. Close Tag-AI
2. Delete or rename the photos.db file
3. Restart Tag-AI
4. The application will create a new, empty database
5. Rescan your photos to rebuild the database

Database Migration

If you need to migrate to a different database location:

1. Back up your current database
2. Close Tag-AI
3. Move the database file to the new location
4. Create a symbolic link from the original location to the new location

# Windows (Admin Command Prompt)
mklink %LOCALAPPDATA%\Tag-AI\photos.db D:\NewLocation\photos.db

# macOS/Linux
ln -s /new/location/photos.db ~/Library/Application\ Support/Tag-AI/photos.db