Data Flows and Database Management

1. Database Architecture

1.1 Database System

Emby uses SQLite as the main database, with optional support for SQL Server in enterprise environments. The database is stored in the application's data directory and contains all metadata, user settings, authentication data, and usage statistics.

1.2 Main Database Structure

Emby creates and manages multiple database files:

• library.db - Main database containing:

  • Tables for users and authentication
  • Media metadata (movies, series, music)
  • Playback progress data
  • User settings and preferences

• activity.db - Stores activity data and logs

• channels.db - Data about channels and plugins

2. Data Flow in the Application

2.1 Authentication and Authorization Process

CREATE TABLE Users (
    Id INTEGER PRIMARY KEY,
    Name TEXT NOT NULL,
    Password TEXT,
    Salt TEXT,
    IsActive BOOLEAN,
    IsAdministrator BOOLEAN
);

CREATE TABLE AccessTokens (
    Id TEXT PRIMARY KEY,
    UserId INTEGER,
    DeviceId TEXT,
    DateCreated DATETIME,
    DateLastActivity DATETIME,
    AppName TEXT
);

Authentication flow:

1. Client sends login request with credentials
2. Server validates in database using password hash
3. On success, generates an access token and saves it in AccessTokens
4. Token is returned to client for future requests

2.2 Media Library Management

CREATE TABLE TypedBaseItems (
    Id INTEGER PRIMARY KEY,
    Type TEXT NOT NULL,
    Path TEXT,
    Name TEXT,
    MetadataLanguage TEXT,
    MetadataCountryCode TEXT,
    ProviderIds TEXT,
    PremiereDate DATETIME,
    EndDate DATETIME
);

CREATE TABLE MediaItems (
    Id INTEGER PRIMARY KEY,
    Type TEXT,
    MediaType TEXT,
    Container TEXT,
    TotalBitrate INTEGER,
    Path TEXT,
    Size INTEGER
);

CREATE TABLE UserData (
    Id INTEGER PRIMARY KEY,
    UserId INTEGER,
    ItemId INTEGER,
    PlayCount INTEGER,
    IsFavorite BOOLEAN,
    PlaybackPositionTicks INTEGER,
    LastPlayedDate DATETIME
);

Library scanning flow:

1. Emby monitors configured directories for changes
2. When detecting new files, extracts metadata using plugins
3. Saves metadata in TypedBaseItems and MediaItems tables
4. Updates search index

2.3 Playback Progress and User Data

CREATE TABLE PlaybackStatus (
    Id INTEGER PRIMARY KEY,
    UserId INTEGER,
    ItemId INTEGER,
    PositionTicks INTEGER,
    AudioStreamIndex INTEGER,
    SubtitleStreamIndex INTEGER,
    VolumeLevel INTEGER,
    IsPaused BOOLEAN,
    PlaybackStartTime DATETIME,
    LastPlaybackUpdate DATETIME
);

Playback tracking flow:

1. Client reports progress to server at regular intervals
2. Server updates PlaybackStatus and UserData
3. Data is used for synchronization between devices
4. Upon reconnection, client retrieves last saved position

2.4 Plugin and Extensions System

CREATE TABLE Plugins (
    Id INTEGER PRIMARY KEY,
    Name TEXT NOT NULL,
    Version TEXT,
    AssemblyPath TEXT,
    Configuration TEXT,
    Status INTEGER
);

CREATE TABLE PluginConfigurations ( PluginId INTEGER, Key TEXT, Value TEXT );

3. File and Directory Structure

programdata
├── cache
│   ├── images
│   ├── metadata
│   ├── transcode
│   └── certificates
│
├── config
│   ├── system.xml
│   ├── networking.xml
│   ├── users.db
│   ├── authentication.db
│   └── policies.json
│
├── data
│   ├── library.db
│   ├── library.db-shm
│   ├── library.db-wal
│   ├── sessiondata
│   ├── stats
│   └── scheduledtasks
│
├── logs
│   ├── embyserver.txt
│   ├── transcoding-*.txt
│   └── ffmpeg-*.log
│
├── metadata
│   ├── Movies
│   ├── TV Shows
│   ├── Music
│   └── People
│
├── notifications
│   ├── smtp.json
│   └── push.json
│
├── plugins
│   ├── <plugin_name>.dll
│   ├── <plugin_folder>
│   └── configurations.json
│
├── root
│   ├── default.xml
│   └── images
│
└── updates
    ├── server
    └── plugins

4. Useful Commands for Administration

4.1 Database Backup

cp /var/lib/emby/data/library.db /backup/emby/library_backup_$(date +%Y%m%d).db

4.2 Database Repair

sqlite3 /var/lib/emby/data/library.db "VACUUM;"

4.3 Integrity Check

sqlite3 /var/lib/emby/data/library.db "PRAGMA integrity_check;"

4.4 User Password Reset

sqlite3 /var/lib/emby/data/library.db "UPDATE Users SET Password=NULL, Salt=NULL WHERE Name='username';"

4.5 Cache Cleanup

rm -rf /var/lib/emby/data/cache/*

4.6 Database Statistics Check

sqlite3 /var/lib/emby/data/library.db ".tables"
sqlite3 /var/lib/emby/data/library.db "SELECT name FROM sqlite_master WHERE type='table';"

4.7 Active Connections Monitoring

lsof | grep emby | grep library.db

4.8 Database Optimization

sqlite3 /var/lib/emby/data/library.db "ANALYZE;"
sqlite3 /var/lib/emby/data/library.db "REINDEX;"

5. Data Flow at Content Loading

1. Media file is detected in monitored directory
2. Emby extracts metadata using configured providers
3. Poster/backdrop images are generated and stored in cache
4. Information is indexed for fast search
5. Notifications are sent to clients for UI update

6. Synchronization Between Devices

1. All user data is centralized on server
2. Clients use REST API to access data
3. WebSockets for real-time notifications
4. Local cache on client for performance
5. Conflict resolution based on timestamps