Skip to content

Data and Persistence

Michael Riddle edited this page Aug 21, 2026 · 8 revisions

Data and Persistence

KHost uses two persistence mechanisms:

  • SQLite via EF Core 10 — the song library, users, groups, performances, and tips. File: cache/khost.db next to the host binary.
  • JSON file cache (ICacheService → JsonFileCacheService, ./cache/*.json) — queue state, selected venue, theme choice, plugin enable/settings state. Small, frequently-mutated session state that survives restarts and keeps working if the DB is momentarily unavailable.

Database

DefaultContext is registered through AddDbContextFactory with global NoTracking query behavior; repositories create a context per operation. DatabaseInitializer runs migrations at startup and seeds a default admin user, default venue, and default media when absent. The design-time factory used by dotnet ef targets a separate khost_design.db.

Entities

Entity Key fields Notes
Media FilePath (unique), Title, Artist, Format, Status, DateAdded, SearchFolded, FileSize, SampledHash, ContentHash SearchFolded is what search matches on and what FTS5 indexes (below). The size and hashes drive import dedup (see Import deduplication); FileSize and ContentHash are indexed, neither uniquely. No navigation properties.
Venue Name (unique), NameFolded, Address, Phone, Enabled Settings is an owned VenueSettings stored as a JSON column (OwnsOne(...).ToJson()), including nested QueueRotation config.
KHostUser Name, NameFolded (unique), PasswordHash, CreatedDate Uniqueness is on the folded name, not on Name COLLATE NOCASE — NOCASE folds ASCII only, so it let two singers exist whose names differed just outside ASCII. Many-to-many with groups; one-to-many with tips (cascade delete).
KHostUserGroup Name (unique), NameFolded, IsAdmin, ExcludeFromSingerQueue, Permissions (JSON column) Two rows seeded with fixed GUIDs: Admin and Regular.
Performance SingerId, MediaId, VenueId, QueuePosition, CreatedDate One queue/history entry.
Tip UserId (FK, cascade), VenueId, AmountInCents, PaymentMethod, CreatedDate, NotesFolded Money is whole cents, never a decimal — see below.
UserGroupMembership (GroupId, UserId) composite PK Join entity for users ↔ groups; not exposed as a DbSet property — use context.Set<UserGroupMembership>().

Only three foreign keys are enforced: Tip.UserId and the two UserGroupMemberships FKs (all cascade). Performance.SingerId/MediaId/VenueId and Tip.VenueId are indexed GUID columns with no FK or navigation, so media, venues, and singers can be deleted without cascading into history — deliberately, so deleting a song does not erase the record that someone sang it.

Import deduplication

Import decides whether a file is already in the library by its bytes, not its name, so a song that was renamed, moved, or copied into a second folder is recognised instead of imported twice. Three tiers, cheapest first:

  1. Size — Media.FileSize. A file whose size matches no row is new, and is never opened during filtering.
  2. Sampled hash — Media.SampledHash, a SHA-256 over the length plus the first and last 64 KB. Only files whose size collides pay for this. It cannot see a difference confined to the middle of a file, which is why it never decides on its own.
  3. Full hash — Media.ContentHash, SHA-256 over the whole file, computed only to confirm a sampled match. A false positive would silently drop a song the user wanted, which is worse than a duplicate row.

Same-size files are commoner in a karaoke library than elsewhere: CD+G is fixed-rate at 96 bytes per 1/300 s, so .cdg sizes land on a coarse grid. The confirm step carries that weight.

Rows imported before fingerprints existed are measured (a stat, no read) at the start of the next import run; their hashes fill in lazily, only when a size collision actually needs them. Duplicates within one selection are caught too, not just against the stored library.

This catches byte-identical files only — the same song re-encoded is a different file, and recognising that would need audio fingerprinting, which KHost does not do. The path lookup (GetExistingFilePathsAsync) remains for the media browser, which flags already-imported files while listing a folder.

Tables as SQLite has them

Generated from a database built by running every migration, so this is the shape on disk rather than the shape of the model. SQLite has only five storage classes, so TEXT covers a lot of ground here: GUIDs, DateTime and TimeSpan are all stored as text, and booleans (Enabled, IsAdmin) as INTEGER. The three enforced foreign keys are visible below — Performances deliberately has none, so deleting a song does not erase the record that someone sang it.

Media

Column Type Null Key / index
Id TEXT no PK
FilePath TEXT no unique
Duration TEXT yes —
Status INTEGER no indexed
Title TEXT no indexed
Artist TEXT no indexed
Format TEXT no —
Notes TEXT no —
DateAdded TEXT no indexed
ContentHash TEXT yes indexed
FileSize INTEGER yes indexed
SampledHash TEXT yes —
SearchFolded TEXT no —

Users

Column Type Null Key / index
Id TEXT no PK
CreatedDate TEXT no indexed
Name TEXT no —
NameFolded TEXT no unique
Notes TEXT no —
PasswordHash TEXT yes —

UserGroups

Column Type Null Key / index
Id TEXT no PK
Name TEXT no unique
Description TEXT no —
IsAdmin INTEGER no —
ExcludeFromSingerQueue INTEGER no —
Permissions JSON no —
NameFolded TEXT no —

UserGroupMemberships

Column Type Null Key / index
UserId TEXT no PK, FK → Users.Id (cascade), indexed
GroupId TEXT no PK, FK → UserGroups.Id (cascade)

Venues

Column Type Null Key / index
Id TEXT no PK
Enabled INTEGER no —
Name TEXT no unique
Notes TEXT no —
Address TEXT no —
Phone TEXT no —
Settings TEXT no —
NameFolded TEXT no —

Performances

Column Type Null Key / index
Id TEXT no PK
SingerId TEXT no indexed
MediaId TEXT no indexed
VenueId TEXT yes indexed
QueuePosition INTEGER yes —
CreatedDate TEXT no indexed

Tips

Column Type Null Key / index
Id TEXT no PK
CreatedDate TEXT no indexed
UserId TEXT no FK → Users.Id (cascade), indexed
VenueId TEXT yes indexed
AmountInCents INTEGER no —
PaymentMethod INTEGER no —
Notes TEXT no —
NotesFolded TEXT no —

Money

Tips are stored as whole cents in an INTEGER, not as a decimal. SQLite has no decimal type, so EF Core stores one as TEXT — which sorts lexicographically ("9.00" above "10.00") and makes SUM coerce through a float. Presentation and entry are $00.00; nothing between the two ever holds a fraction.

media_fts and its shadow tables (media_fts_data, media_fts_idx, media_fts_docsize, media_fts_config, media_fts_content) are omitted — they belong to FTS5 and are described below.

Text folding

Every searched column has a folded companion — Media.SearchFolded, KHostUser.NameFolded, Venue.NameFolded, KHostUserGroup.NameFolded, Tip.NotesFolded — holding the text as comparisons see it. Folding transliterates to ASCII with Unidecode.NET and lowercases, so accents and non-Latin scripts both reduce to something a host on a US keyboard can type: Björk folds to bjork, Ελλάδα to ellada. Both sides of every comparison are folded the same way — matching works because both are reduced identically, not because either side is clever.

The folded value is stored rather than computed at query time because the bundled SQLite folds ASCII only: lower() and COLLATE NOCASE never match a stored ZOË against a typed zoë, and the miss is silent. Folding in .NET and indexing the result keeps matching in SQL and correct.

KHost.Domain.Orthography.StylisedSpelling resolves the spellings artists use where a symbol stands in for a letter ($→s, !→i, @→a), so Ke$ha is found by typing kesha. It is kept separate from transliteration because those characters are already ASCII — Unidecode has no reason to touch them, and which symbol reads as which letter is knowledge about names, not about Unicode. It applies to media only: a singer on the roster called P!nk is a different person from Pink. Digits are deliberately left alone — the rule that turns Deadmau5 into deadmaus also ruins Blink-182 and 50 Cent, which needs a per-artist alias list instead.

Folding happens on the write path, not in the models: DefaultContext.SaveChanges runs EntityFolding over every added or modified entity. Models stay plain data, nothing that writes through EF can forget, and the stored value stays observable — a loaded entity does not silently correct itself in memory.

Because the values are derived, they can go stale: a migration can only seed them with SQLite's own lower(), and changing the folding rule has to reach rows already stored. DatabaseInitializer.RefoldStoredTextAsync rewrites any row whose source text no longer folds to what is stored, on the next start. A collision it cannot repair — two names the old rule kept apart that now fold together — is logged and left alone rather than losing a row; FindByNameAsync tries the exact spelling before the folded one so the losing account can still sign in.

Full-text search (FTS5)

  • Virtual table media_fts — fts5(media_id UNINDEXED, text, tokenize = 'trigram'). The single indexed column holds Media.SearchFolded, so the index and the fallback below agree about what a song is findable by, and a query folded the same way matches either.
  • Notes are not indexed. They describe media the host has already found; a word buried in one should not pull up a song whose title and artist have nothing to do with the query.
  • Kept in sync by three triggers on Media (media_ai/media_au/media_ad). media_au fires on AFTER UPDATE OF "SearchFolded", so writing a fingerprint or editing a note does not re-index the row.
  • MediaRepository.SearchAsync folds the query, sanitizes it (strips FTS metacharacters, quotes tokens) and joins through media_fts ordered by bm25(). Supplying a SortDescriptor replaces the bm25 relevance ordering entirely.
  • The index is trigram, so it cannot hold a term shorter than three characters. A query containing one — or one left empty after the metacharacters are stripped — skips FTS and falls through to the substring search below. That is why typing so finds "Plain Song", and *** finds nothing rather than everything.
  • The FTS table and triggers are hand-written raw SQL inside migrations — EF cannot scaffold them. An FTS5 table's columns cannot be altered either, so changing them means rebuilding the table and reinstating the triggers. See the migration-reset procedure in Development.

Free-text search outside FTS

Everything that is not media search — users, user groups, venues, tip notes — and the media short-query fallback run a LIKE over the folded column, in SQL. BaseRepository folds the query with the same rule the column was written with and escapes LIKE's own wildcards, so a venue called "The 50% Bar" searches for itself rather than matching the whole table. MediaRepository overrides the folder so media queries also resolve stylised spellings.

Repositories

All repositories extend BaseRepository<T> (CRUD, paged ReadAllAsync, SearchAsync with sort/typed-options overloads, HasAnyAsync) and implement SortColumns and ApplySearchFilters. Pagination clamps to a default page size of 50, max 1000. Registered as singletons behind IMediaRepository, IVenuesRepository, IUsersRepository, IUserGroupsRepository, IPerformancesRepository, ITipsRepository.

JSON cache

JsonFileCacheService persists keyed JSON documents under ./cache/. Known keys: singer queue state, selected venue, theme, Plugins (enabled plugin ids + per-plugin settings).

Data locations

Everything lives under the host binary's directory (AppContext.BaseDirectory): cache/khost.db, cache/*.json, logs/. When running from source that is src/KHost.UserInterface/bin/Debug/net10.0/ — deleting bin/ deletes the library, users, and queue.

Clone this wiki locally