Data Model Design

This document is based on the following source files:

• internal/models/models.go -- All domain models, DTOs, validation logic, and custom serialization
• internal/models/backup.go -- Backup/restore data models
• internal/models/lyric.go -- Lyrics payload model and serialization
• internal/models/constant.go -- Pagination constants
• internal/database/sqlc/models.go -- sqlc-generated database row-mapping models
• internal/database/migrations/0001_init.sql -- Initial schema (6 tables + indexes + triggers + preset data; the plugin_storage table is in 0016)
• internal/database/migrations/0005_lyric_source_manual.sql -- Adds manual to lyric_source
• internal/database/migrations/0007_songs_year_genre.sql -- Adds year/genre columns
• internal/database/migrations/0008_songs_fingerprint.sql -- Adds fingerprint column

Table of Contents

1. Overview
2. ER Diagram
3. Song Model
4. Playlist Model
5. PlaylistSong Association Model
6. Config Model
7. AuthToken Model
8. JSPlugin Model
9. Lyrics Model
10. Backup Model
11. Request/Response DTOs
12. Model Relationship Class Diagram

---

1. Overview

Songloft's data persistence layer is based on SQLite, using a layered access stack of goose migration + sqlc fixed SQL + squirrel dynamic SQL. The domain models are defined in the internal/models/ package, separate from the sqlc auto-generated row-mapping models (internal/database/sqlc/models.go), with the Repository layer responsible for the conversion between the two.

The current schema has a total of 7 tables: songs, playlists, playlist_songs, configs, auth_tokens, js_plugins, and plugin_storage, evolved from the initial version to the current state through 25 migration files. The updated_at column of the core tables is automatically maintained by a SQLite TRIGGER.

Section sources

• internal/database/migrations/0001_init.sql:1-190 -- Complete initial schema

---

2. ER Diagram

Diagram sources

• internal/database/migrations/0001_init.sql:3-103 -- Table definitions
• internal/database/migrations/0007_songs_year_genre.sql -- year/genre
• internal/database/migrations/0008_songs_fingerprint.sql -- fingerprint

---

3. Song Model

Song is the most core model in the system, containing 37 persisted fields + 3 runtime-computed fields, carrying three business forms: local songs, network songs, and radio. The type is constrained by CHECK(type IN ('local', 'remote', 'radio')).

Constant	Value	Required Field
TypeLocal	local	file_path
TypeRemote	remote	url
TypeRadio	radio	url

3.1 Field Grouping

Basic information: id(PK), type, title, artist, album, year(0007), genre(0007), track(0020), language(0022), style(0022), duration

File and audio: file_path, url, file_size, format, bit_rate, sample_rate, is_live, is_video(0024), file_modified_at(0019), cache_path(0014)

CUE track splitting: cue_source_path(0018), cue_track_index(0018), cue_audio_path(0018)

Cover art: cover_path(json:"-"), cover_url(rewritten on serialization)

Lyrics: lyric(json:"-", LyricPayload JSON), lyric_source(json:"-"), lyric_remote_url, lyric_url(runtime)

Plugin and deduplication: plugin_entry_path, source_data(opaque JSON), dedup_key, source_url(runtime)

Identification: fingerprint(0008, Chromaprint), fingerprint_duration(0008), isrc(0009)

Timestamps: added_at, updated_at(TRIGGER)

Section sources

• internal/models/models.go:69-101 -- Song struct

3.2 Custom MarshalJSON

Song implements MarshalJSON(), which rewrites four fields on serialization so that clients need not concern themselves with internal storage details:

Field	Rewrite Logic	Example
url	PlaybackURL() unified playback endpoint	/api/v1/songs/42/play
cover_url	CoverURLPath(), empty when no cover art	/api/v1/songs/42/cover
lyric_url	LyricURLPath(), empty when no lyrics	/api/v1/songs/42/lyric
source_url	remote/radio filled with the original URL	https://stream.example.com/live.m3u8

The type songAlias Song trick avoids recursion. The original URL is kept in the database and not exposed to the client; all playback is dispatched uniformly through /api/v1/songs/{id}/play.

Section sources

• internal/models/models.go:178-189 -- MarshalJSON

3.3 PlaybackURL and HLS Rules

• Ordinary songs: /api/v1/songs/{id}/play
• HLS radio (URL suffix .m3u8/.m3u): /api/v1/songs/{id}/play.m3u8

Reason for appending the .m3u8 suffix: ExoPlayer/AVPlayer selects the MediaSource type by URL suffix, and without a suffix it falls to ProgressiveMediaSource, making HLS live streams unplayable. IsHLSStream() determines this by parsing the URL path suffix, and only takes effect for TypeRadio.

Section sources

• internal/models/models.go:108-139 -- PlaybackURL and IsHLSStream

3.4 CoverURLPath and LyricURLPath

Both methods follow the principle of "return the endpoint if present, return empty if absent," avoiding clients making requests destined for 404:

• CoverURLPath: returns /api/v1/songs/{id}/cover when either CoverPath or CoverURL is non-empty
• LyricURLPath: returns /api/v1/songs/{id}/lyric when Lyric is non-empty, or when LyricSource=="url" and LyricRemoteURL is non-empty

Section sources

• internal/models/models.go:143-168 -- URL generation logic

3.5 DedupKey Deduplication Mechanism

dedup_key is used for network song deduplication, typically in the form <platform>:<platform_id>. Together with plugin_entry_path it forms a conditional unique index (partial unique index), taking effect only for rows where dedup_key != '':

CREATE UNIQUE INDEX idx_songs_dedup_key_unique
    ON songs(plugin_entry_path, dedup_key) WHERE dedup_key != '';

Section sources

• internal/database/migrations/0001_init.sql:122 -- dedup_key index

---

4. Playlist Model

Field	Go Type	JSON	Description
ID	int64	id	Auto-increment primary key
Type	string	type	normal or radio (CHECK constraint)
Name	string	name	Globally unique (idx_playlists_name_unique)
Description	string	description	Description
CoverPath	string	- (not exposed)	Local cover art path
CoverURL	string	cover_url	Rewritten on serialization to /api/v1/playlists/{id}/cover
Labels	[]string	labels	JSON array (DB default '[]')
SongCount	int	song_count	Runtime-computed (not persisted)

The DB layer additionally has a position column (for ordering) and timestamp columns. MarshalJSON() rewrites cover_url just like Song.

CanAddSong validation: normal playlists accept only local/remote, and radio playlists accept only radio.

Labels: built_in marks built-in, non-deletable playlists (preset id=1 "Favorites", id=2 "Radio Favorites"), and auto_created marks playlists automatically created during scanning.

Section sources

• internal/models/models.go:253-303 -- Playlist struct and CanAddSong
• internal/database/migrations/0001_init.sql:163-166 -- Preset playlists

---

5. PlaylistSong Association Model

Implements the M:N many-to-many association between playlists and songs: id(PK), playlist_id(FK), song_id(FK), position, added_at.

Key constraints: UNIQUE(playlist_id, song_id) prevents duplicate additions; ON DELETE CASCADE follows playlist/song deletion; the (playlist_id, position) composite index accelerates ordering.

Section sources

• internal/models/models.go:306-326 -- PlaylistSong
• internal/database/migrations/0001_init.sql:45-55 -- Table constraints

---

6. Config Model

Key-value pattern, with key globally unique and value being a JSON string. Preset configuration items:

key	Description	Migration
music_path	Music directory and excluded directories	0001
scan_config	Automatic scanning, interval, formats	0001
jwt_secret	JWT secret (randomly generated with randomblob)	0001
source_validation / source_fallback / source_metrics	Audio source validation/fallback/metrics	0001
plugin_registries	Official plugin registry URL	0006
scan_auto_create_playlists	Whether to automatically create playlists	0012

Section sources

• internal/models/models.go:329-345 -- Config struct
• internal/database/migrations/0001_init.sql:169-178 -- Preset data

---

7. AuthToken Model

JWT dual-token authentication; the auth_tokens table records token metadata and revocation audit.

Field	Description
token_id	Unique identifier (UNIQUE)
token_type	access (short-lived) or refresh (long-lived), CHECK constraint
client_info	User-Agent tracking
expires_at	Expiration time
revoked_at	Revocation time (nullable, sql.NullTime); checking for null tells whether it has been revoked
revoked_by / revoked_reason	Revocation audit (who, why)

TokenInfo is the client-friendly version of AuthToken (with the internal ID removed), used for the token list API.

Section sources

• internal/models/models.go:417-439 -- AuthToken and TokenInfo
• internal/database/migrations/0001_init.sql:67-78 -- Table definition

---

8. JSPlugin Model

Metadata and runtime status of QuickJS sandbox plugins. Core fields:

Field	Description
entry_path	Route prefix (UNIQUE), e.g. "myplugin"
main	Entry file (default main.js)
permissions	JSON array, e.g. ["net","storage","fs:music"]
public_paths	Path prefixes not requiring JWT (added in 0010)
external_paths	Accessible external absolute paths (added in 0013)
icon	Plugin icon (added in 0011)
status	active / inactive (default) / error, CHECK constraint
zip_hash / entry_hash	SHA256 hashes, used for file fingerprint hot-reload detection
file_path	Relative path of the ZIP file

At runtime, the file system hash is compared with the database record, and a reload is automatically triggered when they mismatch.

Section sources

• internal/models/models.go:509-543 -- JSPlugin struct and status enum
• internal/database/migrations/0001_init.sql:81-103 -- js_plugins table

---

9. Lyrics Model

9.1 LyricPayload

The structured storage format of the songs.lyric column, which is also the response form of /api/v1/songs/{id}/lyric:

Field	JSON	Description
Lyric	lyric	Main lyrics (LRC text)
Tlyric	tlyric	Translated lyrics
Rlyric	rlyric	Romanized lyrics
Lxlyric	lxlyric	Word-by-word lyrics

Key methods: MarshalString() returns an empty string (not "{}") for an empty payload to preserve SQL null-check semantics; UnmarshalLyric(raw) is compatible with three historical forms -- empty string, valid JSON, and raw LRC text; ApplyLyricToSong(s, text, source) decides whether to store into Lyric or LyricRemoteURL based on the source type.

9.2 LyricSource Enum

Value	Description
file	.lrc file in the same directory
embedded	Lyrics embedded in the audio
url	Fetched on demand at runtime (LyricRemoteURL holds the address)
cached	Text cached after URL fetch
manual	Manually adjusted by the user, not overwritten by scanning (0005 migration extends the CHECK by rebuilding the table)

Section sources

• internal/models/lyric.go:13-79 -- Complete LyricPayload implementation
• internal/models/models.go:27-33 -- Source constants

---

10. Backup Model

Versioned snapshot design (currently BackupVersion = 1), exporting playlists and songs as self-describing JSON.

• BackupData: top-level container, containing version, exported_at, playlists[]
• BackupPlaylist: playlist snapshot (name/type/description/labels/songs[])
• BackupSong: a slimmed-down version of Song (16 core fields), excluding reconstructable data such as ID, timestamps, lyrics, and cover art paths
• ImportResult: import statistics -- playlists_created/playlists_merged/songs_created/songs_matched/songs_skipped

Section sources

• internal/models/backup.go:1-47 -- Backup models

---

11. Request/Response DTOs

Domain models are decoupled from the HTTP layer through DTOs.

Authentication: LoginRequest(username/password) -> LoginResponse(access_token/refresh_token/expires_in/token_type); RefreshTokenRequest(refresh_token); RevokeTokenRequest(reason)

Batch operations: BatchDeleteSongsRequest(ids/delete_files) -> BatchDeleteSongsResponse(deleted); BatchDeletePlaylistsRequest(ids) -> BatchDeletePlaylistsResponse(deleted)

Auto-create playlists: AutoCreatePlaylistsRequest(include_subdirs) -> AutoCreatePlaylistsResponse(playlists[]/total); sub-structure PlaylistInfo(playlist_id/name/song_count)

Configuration: CreateConfigRequest(key/value); UpdateConfigRequest(value); ConfigFilter(keyword/limit/offset/order_by/order)

Upgrade: RemoteVersionInfo(version/git_commit/build_time/download_url_prefix/release_notes); UpgradeProgress(status/progress/current_step/error), with state transitions idle -> downloading -> testing -> replacing -> restarting (exceptions failed/resetting)

Generic: ErrorResponse({error, detail}); SuccessResponse({message})

Section sources

• internal/models/models.go:348-507 -- DTO definitions

---

12. Model Relationship Class Diagram

Diagram sources

• internal/models/models.go -- Domain models
• internal/models/lyric.go -- Lyrics model
• internal/models/backup.go -- Backup model