Spotify with Alopex DB

What the service does

On a Spotify-like audio streaming service, listeners discover, manage and enjoy music, podcasts and audiobooks. The data is a catalog of tracks and artists, playlists, and each listener’s play history. Recommendations are built from that catalog and history.

Where the data lives

SQL tables: tracks, artists, albums, playlists and the order of tracks in each playlist live in tables.
Vectors: each track’s embedding sits in a vector column. “Tracks like this one” is a nearest-neighbor search through an HNSW index, with conditions on genre or release year added in the same SQL.
Columnar tables: play history is loaded in batches into a columnar table through HTTP ingest. Each listener’s favorite artists and listening by time of day are aggregated in SQL.

The app creates the track embeddings. A recommendation is then one SQL query: favorite artists from the aggregation become the filter, and vectors find the nearest tracks.

Deployment

An Alopex DB server runs alongside the app backend, which connects over HTTP or gRPC. The catalog, history and vectors are in the same database, so nothing is copied into another system to make recommendations.

Why Alopex DB fits

Recommendations combine what is similar (vectors) with what is popular (aggregation). Alopex DB handles vector search and columnar aggregation in SQL on the same server.

See it in code

The play history is aggregated into a top artist, and tracks close to that taste are recommended by vector. Just the recommendation core.

import alopex
import numpy as np

db = alopex.Database.open("spotify.alopex")

db.execute_sql("""
    CREATE TABLE IF NOT EXISTS tracks (
        id INTEGER PRIMARY KEY,
        title TEXT,
        artist TEXT,
        embedding VECTOR(4)
    )
""")
db.execute_sql("CREATE TABLE IF NOT EXISTS plays (track_id INTEGER)")

tracks = [
    (1, "Song A", "Aqua", [0.9, 0.1, 0.0, 0.0]),
    (2, "Song B", "Aqua", [0.85, 0.15, 0.0, 0.0]),
    (3, "Song C", "Lofi", [0.0, 0.0, 0.9, 0.1]),
]
for tid, title, artist, emb in tracks:
    db.execute_sql("INSERT INTO tracks (id, title, artist, embedding) VALUES (?, ?, ?, ?)", [tid, title, artist, np.array(emb, dtype=np.float32)])
for track_id in [1, 1, 2, 3]:
    db.execute_sql("INSERT INTO plays (track_id) VALUES (?)", [track_id])

# よく聴くアーティストを、再生の履歴から集計する
top = db.execute_sql("""
    SELECT t.artist, COUNT(*) AS cnt
    FROM plays AS p
    JOIN tracks AS t ON t.id = p.track_id
    GROUP BY t.artist
    ORDER BY cnt DESC
    LIMIT 1
""")[0]
print("よく聴く:", top["artist"], top["cnt"])

# その傾向（Song A）に近い曲を、ベクトルでおすすめする
query = np.array([0.9, 0.1, 0.0, 0.0], dtype=np.float32)
rows = db.execute_sql("""
    SELECT title, artist, vector_similarity(embedding, ?, 'cosine') AS score
    FROM tracks
    WHERE id <> ?
    ORDER BY score DESC
    LIMIT 1
""", [query, 1])
for row in rows:
    print("おすすめ:", round(row["score"], 3), row["title"], row["artist"])

What is played most (aggregation) and what is similar (vector) combine in the same server’s SQL.

Alopex DB serves similar-track vectors and listening trends in columnar tables from one server. Why not try it for recommendations? The quickstart is the starting point.