TagStack uses MySQL 8.0+ as its backend store for the media library, playlists, broadcast logs, server configurations, clock templates, and schema version tracking. The database is named mcaster1_tagstack and is created automatically if it does not exist.
CREATE DATABASE, CREATE TABLE, INSERT, UPDATE, DELETE, and SELECT privilegestagstack.yaml (host, port, database name, user, password)TagStack applies the database schema automatically. No manual SQL execution is required.
mcaster1_tagstack database if it does not exist and runs all pending migration files in order.migration_history table. Files already applied are skipped — running Initialize Schema again is safe.Upgrading: Use Settings → Database → Migration Wizard to see which migration files are pending. The wizard shows each file name, its applied status, and lets you apply them selectively or all at once.
The schema is built by six cumulative SQL migration files stored in the sql/ folder:
| File | Version | What It Creates |
|---|---|---|
001_init.sql | v1 | schema_version, media_items (core), playlists (core), playlist_entries (core), migration_history |
002_servers.sql | v2 | servers table (server configs, credentials, poll interval) |
003_media_weight.sql | v3 | Adds weight column to media_items for rotation scheduling |
004_media_extended.sql | v4 | Adds BPM, key, MBID, ISRC, label, mood, MusicBrainz rating to media_items |
005_composer_tables.sql | v5 | categories, broadcast_logs, log_items, clock_templates, clock_slots, cart_mappings |
006_playlist_extended.sql | v6 | Adds broadcast automation rules to playlists; adds extended metadata + TV channel fields to playlist_entries |
The core music library. One row per unique audio file path.
| Column | Type | Description |
|---|---|---|
id | BIGINT PK AI | Auto-increment primary key |
file_path | VARCHAR(1024) UNIQUE | Full absolute path to the audio file |
title | VARCHAR(512) | Track title from ID3/tag |
artist | VARCHAR(512) | Artist name |
album | VARCHAR(512) | Album name |
genre | VARCHAR(256) | Genre tag |
format | VARCHAR(16) | File extension (mp3, flac, wav, etc.) |
year | INT | Release year (0 = unknown) |
duration_sec | INT | Track duration in seconds |
file_size | BIGINT | File size in bytes |
is_stream | TINYINT | 1 if this is an ICY stream URL, 0 for local file |
track_number | SMALLINT | Track number on the album |
bpm | SMALLINT | Beats per minute (0 = unknown) |
track_key | VARCHAR(12) | Musical key (e.g. "8A", "C#m") |
weight | FLOAT | Rotation scheduling weight (default 1.0) |
mbid | VARCHAR(36) | MusicBrainz Recording ID (UUID) |
isrc | VARCHAR(12) | International Standard Recording Code |
label | VARCHAR(120) | Record label from MusicBrainz |
mood | VARCHAR(60) | Mood tag from MusicBrainz folksonomy |
mb_rating | SMALLINT | MusicBrainz community rating (0–100) |
mb_lookup_done | TINYINT(1) | 1 if MusicBrainz lookup has been completed for this track |
added_at | DATETIME | Timestamp when the record was first inserted |
Named playlists with optional broadcast automation rules.
| Column | Type | Description |
|---|---|---|
id | BIGINT PK AI | Auto-increment primary key |
name | VARCHAR(512) NOT NULL | Playlist name |
description | TEXT | Optional long description or notes |
rule_artist_sep | INT DEFAULT 0 | Min seconds before same artist repeats (0 = off) |
rule_title_sep | INT DEFAULT 0 | Min seconds before same title repeats (0 = off) |
rule_genre_mix | VARCHAR(1024) | JSON: target genre percentages, e.g. {"Rock":60,"Pop":40} |
rule_bpm_min | INT DEFAULT 0 | Minimum BPM filter (0 = no minimum) |
rule_bpm_max | INT DEFAULT 0 | Maximum BPM filter (0 = no maximum) |
rule_energy_mode | TINYINT DEFAULT 0 | 0 = flat, 1 = ramp up, 2 = ramp down |
created_at | DATETIME | Creation timestamp |
modified_at | DATETIME | Last modification timestamp (auto-updated) |
Individual tracks within a playlist, ordered by sort_order. Supports audio files, audio streams, internet TV, terrestrial TV, jingles, and break markers via the item_type field.
| Column | Type | Description |
|---|---|---|
id | BIGINT PK AI | Auto-increment primary key |
playlist_id | BIGINT NOT NULL | Parent playlist ID (references playlists.id in app code) |
media_id | BIGINT | media_items.id if this is a library track (NULL for streams/TV) |
sort_order | INT NOT NULL | Position within the playlist (1-based) |
item_type | VARCHAR(32) | music | audio_stream | tv_internet | tv_terrestrial | jingle | break |
file_path | VARCHAR(1024) | Full path for local files |
title | VARCHAR(512) | Track or segment title (cached) |
artist | VARCHAR(512) | Artist name (cached) |
album | VARCHAR(512) | Album name (cached) |
genre | VARCHAR(256) | Genre (cached) |
format | VARCHAR(32) | File extension (mp3, flac, etc.) |
year | INT | Release year |
bpm | INT | Beats per minute |
track_key | VARCHAR(32) | Musical key |
weight | FLOAT | Rotation weight (copied from media_items) |
duration_sec | INT DEFAULT -1 | Duration in seconds (-1 = unknown) |
channel_url | VARCHAR(2048) | Stream or IPTV URL (for internet TV and audio streams) |
channel_number | VARCHAR(16) | RF or cable channel number (for terrestrial TV) |
channel_name | VARCHAR(256) | Display name for TV channels and internet streams |
Streaming server configurations.
| Column | Type | Description |
|---|---|---|
id | INT PK AI | Auto-increment primary key |
name | VARCHAR(255) UNIQUE | Friendly server name |
server_type | VARCHAR(64) | mcaster1dnas or icecast2 |
url | VARCHAR(512) | Server URL (e.g. https://dnas.example.com:9443) |
admin_user | VARCHAR(128) | Admin username for ICY 1.x metadata push and mount listing |
admin_password | VARCHAR(512) | Admin password |
source_password | VARCHAR(512) | Source password for ICY 2.2 PUT and live stream push |
stream_id | VARCHAR(64) | Optional stream identifier |
verify_ssl | TINYINT(1) | 1 = verify SSL certificate; 0 = skip (for self-signed certs) |
poll_interval | INT | Stats polling interval in seconds (default 5) |
created_at | TIMESTAMP | Row creation timestamp |
updated_at | TIMESTAMP | Last update timestamp (auto-updated) |
Rotation categories for ComposerPro. Each category is a named bucket with a color badge.
| Column | Type | Description |
|---|---|---|
id | INT PK AI | Auto-increment primary key |
name | VARCHAR(60) UNIQUE | Category name (e.g. "Top 40 Hot", "Gold", "Spots") |
color_hex | VARCHAR(7) | CSS hex color for the badge (default #0ea5e9) |
Broadcast log headers created in ComposerPro.
| Column | Type | Description |
|---|---|---|
id | INT PK AI | Auto-increment primary key |
name | VARCHAR(120) NOT NULL | Log name (e.g. "Morning Drive 2026-03-01") |
log_date | DATE | Scheduled broadcast date |
notes | TEXT | Free-text notes for the operator |
created_at | DATETIME | Creation timestamp |
updated_at | DATETIME | Last save timestamp (auto-updated) |
Individual items within a broadcast log, ordered by position.
| Column | Type | Description |
|---|---|---|
id | INT PK AI | Auto-increment primary key |
log_id | INT NOT NULL | Parent broadcast_logs.id |
position | SMALLINT NOT NULL | Order within the log (1-based) |
media_id | INT | media_items.id (NULL for spots, jingles without library entries) |
cart_number | VARCHAR(20) | Cart number for SAM Broadcaster / Rivendell export |
item_type | TINYINT | 0=Music, 1=Spot, 2=Jingle, 3=News, 4=Filler, 5=Voice |
start_time | TIME | Scheduled start time (HH:MM:SS) |
duration_sec | SMALLINT | Duration in seconds |
notes | VARCHAR(200) | Operator notes for this item |
Clock templates define repeating hour structures for auto-populating broadcast logs.
| Table | Column | Description |
|---|---|---|
clock_templates | id, name, notes | Template header with unique name |
clock_slots | template_id | Parent template ID |
clock_slots | slot_index | Order of this slot within the template (0-based) |
clock_slots | item_type | Same type codes as log_items (0–5) |
clock_slots | category_id | Which rotation category to pull from (categories.id) |
clock_slots | duration_sec | Target slot duration in seconds (default 210 = 3:30) |
Lookup table mapping cart numbers to media library items.
| Column | Type | Description |
|---|---|---|
cart_number | VARCHAR(20) PK | Cart number string (e.g. "C001", "SPOT-042") |
media_id | INT | media_items.id this cart maps to |
| Table | Purpose |
|---|---|
schema_version | Stores the highest applied schema version number with a description. TagStack checks this to determine the current state. |
migration_history | Tracks each migration SQL file by filename. Used to skip already-applied files on subsequent Initialize Schema calls. Prevents duplicate execution. |
.sql).mysqldump as a child process, capturing its output to the selected file.mcaster1_tagstack: media library, playlists, servers, logs, templates, and mappings.To restore from a backup, use the MySQL command line or MySQL Workbench to import the .sql file into your mcaster1_tagstack database:
mysql -u root -p mcaster1_tagstack < backup_2026-03-01.sqlAfter restore, run Initialize Schema in TagStack to ensure any pending migrations are applied (this is safe — already-applied migrations are skipped).
media_items(artist, title) for faster search filtering in ComposerPro.weight column on media_items and playlist_entries is used by ComposerPro rotation logic — keep it populated for accurate auto-scheduling.mb_lookup_done flag prevents re-querying MusicBrainz for tracks that have already been enriched. Do not clear this column unless you intentionally want to re-run lookups.