Database Schema and Models

Relevant source files

The following files were used as context for generating this wiki page:

• app/track.py
• app/database.py
• app/db.py (Inferred from imports in app/database.py and test/test_db.py)
• app/config.py
• README.md
• service/data_processor.py

The database system in the Spotify API Data Collector project is designed to provide persistent storage and efficient retrieval of music metadata. It utilizes a SQLite backend to store track information, artist details, and popularity metrics collected from the Spotify Web API. This module ensures data idempotence and supports various filtering operations for the system's API Endpoints.

Sources: README.md:9-16, app/database.py:1-5

Database Architecture

The architecture consists of a low-level connection manager (Database class), a high-level data model (Track dataclass), and a set of utility functions for CRUD operations. The system is configured to use a local SQLite file, defaulting to spotify_data.db.

Data Models

The Track Model

The primary data structure is the Track dataclass. It represents a flattened view of Spotify data, combining track-level information with enriched artist metadata.

Field	Type	Description
track_id	str	Unique Spotify ID (Primary Key/Unique)
track_name	str	Name of the track
track_popularity	int	Popularity score (0-100)
album_name	str	Name of the associated album
album_release_date	str	Release date of the album
duration_ms	int	Duration in milliseconds
year	int	Collection year (2020-2024)
artist_id	str	Unique Spotify Artist ID
artist_name	str	Name of the artist
artist_followers	int	Number of followers for the artist
artist_genres	str	Comma-separated list of genres
artist_popularity	int	Artist popularity score (0-100)

Sources: app/track.py:7-21, README.md:31-45

Schema Definition

The database consists of a single table named tracks. To optimize performance for the API's filtering requirements, several B-tree indexes are created on frequently queried columns.

Indexing Strategy

The init_db function establishes three specific indexes to ensure low-latency responses for the FastAPI endpoints:

1. idx_tracks_year: Optimizes filtering by release/collection year.
2. idx_tracks_artist_name: Facilitates searching tracks by artist.
3. idx_tracks_popularity: Enables efficient sorting and filtering by popularity scores.

Sources: app/database.py:16-43, README.md:47

Data Operations

The system implements several data access patterns, primarily handled in app/database.py.

Write Operations

The project supports both single insertions and high-performance batch operations. Both methods utilize INSERT OR IGNORE logic to maintain idempotence, preventing duplicate entries for the same track_id.

def insert_tracks_batch(db: Database, tracks: list[Track]) -> int:
    """Inserts multiple tracks in a single transaction."""
    insert_query = """
    INSERT OR IGNORE INTO tracks (...) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    """
    params_list = [...] # Parameters extracted from Track objects
    with db.get_connection() as conn:
        cursor = conn.cursor()
        cursor.executemany(insert_query, params_list)
        return cursor.rowcount

Sources: app/database.py:75-104

Read Operations

The database utility functions provide filtered views for the API:

• get_tracks_by_year(db, year): Retrieves tracks for a specific year, ordered by popularity descending.
• get_tracks_by_artist(db, artist_name): Performs a case-insensitive search using LOWER(artist_name).
• get_all_tracks(db, limit, offset): Provides paginated access to the entire dataset.

Sources: app/database.py:107-124

Configuration and Initialization

The database path and behavior are controlled via the Config class.

Option	Default Value	Description
DATABASE_URL	sqlite:///spotify_data.db	Connection string for the database
YEARS	[2020, 2021, 2022, 2023, 2024]	Years targeted for data collection

The database is initialized automatically during the API startup event or when the DataProcessor is instantiated.
Sources: app/config.py:15-20, service/data_processor.py:24-28

Summary

The Database Schema and Models provide a robust foundation for the Spotify data collector. By utilizing a structured SQLite schema with strategic indexing and a type-safe Python model (Track), the system achieves a balance between simple local storage and high-performance data retrieval for music analytics.