Relationships

The Track-centred entity-relationship model: cardinalities, foreign keys, cascade rules and how each relation shows up in the endpoints. For the Album and Artist satellites, see their own pages.

1 ER diagram

2 One-to-One · TrackAudioFeatures

Each Track has zero or one TrackAudioFeatures. The record is created after Phase 1 of the pipeline completes; before that, Track.AudioFeatures is null.

// TrackAudioFeaturesConfiguration.cs
builder.HasOne(taf => taf.Track)
       .WithOne(t => t.AudioFeatures)
       .HasForeignKey<TrackAudioFeatures>(taf => taf.TrackId)
       .OnDelete(DeleteBehavior.Cascade);

builder.HasIndex(taf => taf.TrackId).IsUnique();

• FK: track_audio_features.TrackId (UNIQUE).
• OnDelete: Cascade — deleting the Track removes the features.
• Lazy loading disabled: queries must use .Include(t => t.AudioFeatures).

3 Many-to-One · Album

Every Track belongs to exactly one Album. Even singles get an album shell (with 1 track) to keep the catalog, commercial (P-line/C-line) and cover model uniform.

4 Many-to-Many · Artist, Genre, Mood

Three independent relations via join tables. EF Core materialises the join tables with the naming convention track_artists / track_genres / track_moods.

Artist

The Track's primary artists. For credits with a specific role, use TrackContributor (next section). A Track can list several artists; an artist appears on many tracks.

Genre

Musical categorisation. Can be declared by the artist or suggested automatically (a future auto-genre pipeline based on the embedding).

Mood

Editorial emotional/contextual tags. Distinct from the mood scores in TrackAudioFeatures: here they are curated labels (their own entity), there they are continuous classifier probabilities.

TrackContributor (rich association)

One-to-Many from the Track, Many-to-One from the Artist. It carries a payload: Role (Producer, Composer, Mixer, FeaturedArtist, etc.), plus RoleDetail, Instrument, CreditedAs, IsPrimary and Order.

public class TrackContributor : BaseModel
{
    public Guid TrackId  { get; set; }
    public Guid ArtistId { get; set; }
    public ContributorRole Role { get; set; }
    public string? RoleDetail { get; set; }
    public string? Instrument { get; set; }
    public string? CreditedAs { get; set; }
    public bool IsPrimary { get; set; }
    public int Order { get; set; }
    public Track  Track  { get; set; } = null!;
    public Artist Artist { get; set; } = null!;
}

See the full ContributorRole reference for the role catalog.

5 One-to-Many · Segments & Jobs

TrackSegment

Each .ts file produced by HLS encoding is persisted as a TrackSegment. This lets the total duration be computed, partial re-encoding be handled, and the manifest be exposed via the API.

TrackJob

An audit trail of pipeline runs. Each upload creates one initial job; manual reprocesses create new jobs. State: TrackJobStatus (Pending, Processing, Done, Failed). It also stores StartedAt, CompletedAt, ProcessingDurationMs, WorkerId and AttemptCount — useful for diagnostics and ops dashboards.

6 Self-reference · CleanVersionOf

A clean track (censored words) references its explicit original via CleanVersionOfTrackId. This lets the player automatically pick the right version based on the user's preference/context.

builder.HasOne(t => t.CleanVersionOf)
       .WithMany()
       .HasForeignKey(t => t.CleanVersionOfTrackId)
       .OnDelete(DeleteBehavior.SetNull);

If the explicit track is deleted, the link becomes null automatically (the clean version survives).

7 Cascade rules summary

8 Query examples

Load a Track with every relation

var track = await db.Tracks
    .Include(t => t.Album)
    .Include(t => t.Artists)
    .Include(t => t.Contributors).ThenInclude(c => c.Artist)
    .Include(t => t.Genres)
    .Include(t => t.Moods)
    .Include(t => t.AudioFeatures)
    .Include(t => t.Segments)
    .FirstOrDefaultAsync(t => t.Id == id);

An artist's tracks with features

var tracks = await db.Tracks
    .Where(t => t.Artists.Any(a => a.Id == artistId))
    .Where(t => t.Status == TrackStatus.Published)
    .Select(t => new { t.Id, t.Title, Bpm = t.AudioFeatures!.Bpm })
    .ToListAsync();

Top similar via embedding (raw SQL)

// The endpoint uses raw SQL — EF does not map pgvector
SELECT t."Id",
       1 - (taf."EmbeddingDiscogs" <=> @seed::vector(1280)) AS similarity
FROM tracks t
JOIN track_audio_features taf ON taf."TrackId" = t."Id"
WHERE taf."EmbeddingDiscogs" IS NOT NULL
  AND t."Id" <> @seedTrackId
ORDER BY taf."EmbeddingDiscogs" <=> @seed::vector(1280)
LIMIT 50;