9. Building the Playlist Manager

The Playlist Manager turns the snapshot logic from Chapter 16 into a browser form. The user picks a playlist source, optionally names the playlist, and Flask creates it in Spotify.

What this page builds

This page has one job: let the user generate a Spotify playlist without opening the terminal. The form collects a source such as Forgotten Gems, Recent Discoveries, or Current Rotation. The backend finds matching tracks in music_time_machine.db, creates a new Spotify playlist, adds the tracks, and shows a confirmation link.

If the snapshot history is still empty, the route will handle that gracefully and ask the user to run the Chapter 16 snapshot step first.

Chapter 16 already gave you the playlist logic. This page wraps that logic in a Flask route, a form, and a result screen.

Creating the playlist-generation route

The /playlists route handles both sides of the page. A GET request shows the form. A POST request validates the form, generates the playlist, and returns either a success message or an error.

app.py

# app.py -- /playlists route.
import requests

PLAYLIST_SOURCES = {
    'forgotten_gems': {
        'label': 'Forgotten Gems',
        'default_name': 'Forgotten Gems',
        'description': 'Tracks you loved months ago but have not played lately',
    },
    'recent_discoveries': {
        'label': 'Recent Discoveries',
        'default_name': 'Recent Discoveries',
        'description': 'Tracks that first appeared in your latest snapshot',
    },
    'current_rotation': {
        'label': 'Current Rotation',
        'default_name': 'Current Rotation',
        'description': 'Your latest short-term top tracks in ranked order',
    },
}


def _build_playlist_query(source):
    """Return a parameterised query for a whitelisted playlist source."""
    if source == 'forgotten_gems':
        return """
            SELECT DISTINCT t.track_id, t.name, t.artist_name,
                   MAX(old.snapshot_date) AS last_seen
            FROM tracks t
            JOIN snapshots old ON old.track_id = t.track_id
            WHERE old.time_range = 'short_term'
              AND old.snapshot_date BETWEEN date('now', '-365 days')
                                        AND date('now', '-90 days')
              AND NOT EXISTS (
                  SELECT 1 FROM snapshots recent
                  WHERE recent.track_id = t.track_id
                    AND recent.time_range = 'short_term'
                    AND recent.snapshot_date >= date('now', '-30 days')
              )
            GROUP BY t.track_id
            ORDER BY last_seen DESC
            LIMIT 30
        """, []

    if source == 'recent_discoveries':
        return """
            WITH latest AS (
                SELECT MAX(snapshot_date) AS snapshot_date
                FROM snapshots
                WHERE time_range = 'short_term'
            )
            SELECT t.track_id, t.name, t.artist_name,
                   s.snapshot_date AS last_seen
            FROM snapshots s
            JOIN latest ON latest.snapshot_date = s.snapshot_date
            JOIN tracks t ON t.track_id = s.track_id
            WHERE s.time_range = 'short_term'
              AND NOT EXISTS (
                  SELECT 1 FROM snapshots earlier
                  WHERE earlier.track_id = s.track_id
                    AND earlier.time_range = 'short_term'
                    AND earlier.snapshot_date < s.snapshot_date
              )
            ORDER BY s.rank
            LIMIT 30
        """, []

    if source == 'current_rotation':
        return """
            WITH latest AS (
                SELECT MAX(snapshot_date) AS snapshot_date
                FROM snapshots
                WHERE time_range = 'short_term'
            )
            SELECT t.track_id, t.name, t.artist_name,
                   s.snapshot_date AS last_seen
            FROM snapshots s
            JOIN latest ON latest.snapshot_date = s.snapshot_date
            JOIN tracks t ON t.track_id = s.track_id
            WHERE s.time_range = 'short_term'
            ORDER BY s.rank
            LIMIT 30
        """, []

    raise ValueError(f'Unknown playlist source: {source}')


@app.route('/playlists', methods=['GET', 'POST'])
@require_auth
def playlists():
    """Generate playlists from the saved snapshot tables."""

    if request.method == 'POST':
        # Server-side validation. Client-side checks exist for UX, but every
        # field is re-validated here because anything from the form is
        # untrusted by definition.
        source = request.form.get('source', '').strip().lower()
        playlist_name = request.form.get('playlist_name', '').strip()

        if not source:
            flash('Please select a playlist source.', 'error')
            return redirect(url_for('playlists'))

        if source not in PLAYLIST_SOURCES:
            flash(f'Invalid playlist source: {source}.', 'error')
            return redirect(url_for('playlists'))

        if playlist_name and len(playlist_name) > 100:
            flash('Playlist name must be 100 characters or less.', 'warning')
            playlist_name = playlist_name[:100]

        source_config = PLAYLIST_SOURCES[source]

        try:
            sql, params = _build_playlist_query(source)
            conn = get_db_connection()
            if not conn:
                flash('Could not open the database.', 'error')
                return redirect(url_for('playlists'))
            try:
                cursor = conn.cursor()
                cursor.execute(sql, params)
                tracks = cursor.fetchall()
            finally:
                conn.close()

            if not tracks:
                flash(
                    f"No tracks found for {source_config['label']} yet. "
                    f"Run a Chapter 16 snapshot, then try again.",
                    'warning',
                )
                return redirect(url_for('playlists'))

            track_ids = [row['track_id'] for row in tracks]

            if not playlist_name:
                timestamp = datetime.now().strftime('%B %Y')
                playlist_name = f"{source_config['default_name']} - {timestamp}"

            access_token = session.get('access_token')
            headers = {
                'Authorization': f'Bearer {access_token}',
                'Content-Type': 'application/json',
            }

            # 1. Get the reader's Spotify user ID.
            user_resp = requests.get(
                'https://api.spotify.com/v1/me',
                headers=headers,
                timeout=10,
            )
            user_resp.raise_for_status()
            user_id = user_resp.json()['id']

            # 2. Create the empty playlist.
            create_resp = requests.post(
                f'https://api.spotify.com/v1/users/{user_id}/playlists',
                headers=headers,
                json={
                    'name': playlist_name,
                    'description': source_config['description'],
                    'public': False,
                },
                timeout=10,
            )
            create_resp.raise_for_status()
            playlist = create_resp.json()
            playlist_id = playlist['id']
            playlist_url = playlist['external_urls']['spotify']

            # 3. Add the tracks.
            add_resp = requests.post(
                f'https://api.spotify.com/v1/playlists/{playlist_id}/tracks',
                headers=headers,
                json={'uris': [f'spotify:track:{tid}' for tid in track_ids]},
                timeout=10,
            )
            add_resp.raise_for_status()

            return render_template(
                'playlists.html',
                new_playlist_url=playlist_url,
                new_playlist_name=playlist_name,
                track_count=len(tracks),
            )

        except requests.exceptions.HTTPError as e:
            if e.response is not None and e.response.status_code == 401:
                flash('Spotify session expired. Please log in again.', 'error')
                return redirect(url_for('login'))
            flash(f'Spotify API error: {e}', 'error')
            return redirect(url_for('playlists'))

        except sqlite3.Error as e:
            flash(f'Database error: {e}', 'error')
            return redirect(url_for('playlists'))

        except Exception as e:
            print(f"Unexpected error in /playlists: {e}")
            flash('Something went wrong creating the playlist.', 'error')
            return redirect(url_for('playlists'))

    # GET request: render the form.
    return render_template('playlists.html')

Server-side validation strategy

The route validates every input before processing, even though the HTML form has client-side validation. This protects against attackers who bypass the form by crafting raw HTTP requests. The validation checks include:

Source whitelist validation: The source not in PLAYLIST_SOURCES check ensures only predefined playlist sources are accepted. An attacker could modify the form HTML to add a <option value="malicious"> and submit it. Without this check, the route would process invalid criteria.

Playlist name sanitisation: The route trims whitespace and enforces a 100-character maximum. This prevents database errors from oversized inputs and protects against potential injection attacks if the name gets used in SQL or API calls elsewhere.

Empty input handling: The route explicitly checks for missing or empty source values and returns helpful error messages rather than crashing with a KeyError when accessing PLAYLIST_SOURCES[source].

Why validation happens twice

The HTML form includes required attributes and maxlength limits for good user experience. They prevent accidental mistakes. But client-side validation is convenience, not security. It's trivial to bypass using browser DevTools or by sending direct HTTP requests with curl or Python's requests library.

Professional web applications validate on both sides: client-side for UX (instant feedback), server-side for security (enforceable protection). This route demonstrates production validation patterns you'll use in every form-handling application.

Building the Playlist Manager template

Create templates/playlists.html with a form for playlist-source selection, optional custom naming, and conditional success display. The form includes a CSRF token to protect against forged requests from malicious sites.

templates/playlists.html

{% extends "base.html" %}

{% block title %}Playlist Manager - Music Time Machine{% endblock %}

{% block content %}
<div class="page-header">
  <h1>Playlist Manager</h1>
  <p class="page-subtitle">Generate playlists from your saved listening history</p>
</div>

{% if new_playlist_url %}
<!-- Success card: shown after a successful POST -->
<div class="success-card">
  <div class="success-icon">✓</div>
  <h2>Playlist created</h2>
  <p class="playlist-name">{{ new_playlist_name }}</p>
  <p class="help-text">{{ track_count }} tracks added</p>
  <a href="{{ new_playlist_url }}" target="_blank" class="spotify-button">
    Open in Spotify
  </a>
  <p class="help-text">
    <a href="{{ url_for('playlists') }}">Generate another</a>
  </p>
</div>
{% else %}
<!-- Generation form -->
<div class="form-container">
  <h2>Choose a playlist source</h2>
  <form method="POST" action="{{ url_for('playlists') }}" id="playlistForm">
    <input type="hidden" name="csrf_token" value="{{ csrf_token() }}"/>

    <div class="form-group">
      <label>Playlist source</label>
      <label class="radio-option">
        <input type="radio" name="source" value="forgotten_gems" required>
        Forgotten Gems (older tracks missing from recent snapshots)
      </label>
      <label class="radio-option">
        <input type="radio" name="source" value="recent_discoveries">
        Recent Discoveries (new tracks in the latest snapshot)
      </label>
      <label class="radio-option">
        <input type="radio" name="source" value="current_rotation">
        Current Rotation (latest short-term top tracks)
      </label>
    </div>

    <div class="form-group">
      <label for="playlist_name">Playlist name (optional)</label>
      <input type="text" id="playlist_name" name="playlist_name" maxlength="100"
             placeholder="Leave blank for an auto-generated name">
      <p class="field-hint">Up to 100 characters. We'll auto-name it for you if you skip this field.</p>
    </div>

    <button type="submit" class="generate-button">Generate playlist</button>
  </form>
</div>

<div class="playlist-sources-info">
  <h3>What each playlist source selects</h3>
  <div class="profile-grid">
    <div class="profile-card">
      <strong>Forgotten Gems</strong>
      <p>Tracks that appeared 90-365 days ago but not in the last 30 days.</p>
    </div>
    <div class="profile-card">
      <strong>Recent Discoveries</strong>
      <p>Tracks whose first-ever snapshot appearance is in the latest snapshot.</p>
    </div>
    <div class="profile-card">
      <strong>Current Rotation</strong>
      <p>The latest short-term snapshot, ordered by Spotify's current ranking.</p>
    </div>
  </div>
</div>
{% endif %}

{% endblock %}

CSRF token for both HTML forms and AJAX

This page uses CSRF tokens in two places: (1) The hidden <input> field inside the form for traditional HTML submissions, and (2) The shared <meta> tag from base.html for JavaScript to access when making AJAX requests.

Flask-WTF validates tokens from either form data or the X-CSRFToken HTTP header, allowing both submission methods to work securely. The form also has id="playlistForm" so JavaScript can target it for AJAX enhancement.

Styling the Playlist Manager

Add these styles to dashboard.css for form layout and success card presentation:

static/css/dashboard.css

/* Playlist Manager Styles */

.form-container {
  max-width: 600px;
  margin: 2rem auto;
  background: #181818;
  border-radius: 8px;
  padding: 2rem;
}

.form-container h2 {
  color: #fff;
  margin-bottom: 1.5rem;
  font-size: 1.5rem;
}

.form-group {
  margin-bottom: 1.5rem;
}

.form-group label {
  display: block;
  color: #fff;
  font-weight: 600;
  margin-bottom: 0.5rem;
}

.form-group select,
.form-group input[type="text"] {
  width: 100%;
  padding: 0.75rem;
  background: #282828;
  border: 2px solid #404040;
  border-radius: 4px;
  color: #fff;
  font-size: 1rem;
  transition: border-color 0.2s;
}

.form-group select:focus,
.form-group input[type="text"]:focus {
  outline: none;
  border-color: #1DB954;
}

.field-hint {
  color: #B3B3B3;
  font-size: 0.875rem;
  margin-top: 0.25rem;
}

.generate-button {
  width: 100%;
  padding: 1rem;
  background: #1DB954;
  color: #fff;
  border: none;
  border-radius: 24px;
  font-size: 1rem;
  font-weight: 600;
  cursor: pointer;
  transition: background 0.2s;
}

.generate-button:hover {
  background: #1ED760;
}

.generate-button:disabled {
  background: #535353;
  cursor: not-allowed;
}

/* Success Card */

.success-card {
  max-width: 500px;
  margin: 2rem auto;
  background: linear-gradient(135deg, #1DB954 0%, #1ED760 100%);
  border-radius: 12px;
  padding: 2rem;
  text-align: center;
  box-shadow: 0 4px 12px rgba(29, 185, 84, 0.3);
}

.success-icon {
  font-size: 3rem;
  color: #fff;
  margin-bottom: 1rem;
}

.success-card h2 {
  color: #fff;
  margin-bottom: 0.5rem;
}

.playlist-name {
  color: #fff;
  font-size: 1.25rem;
  font-weight: 600;
  margin-bottom: 1.5rem;
}

.spotify-button {
  display: inline-block;
  padding: 0.75rem 2rem;
  background: #fff;
  color: #1DB954;
  text-decoration: none;
  border-radius: 24px;
  font-weight: 600;
  transition: transform 0.2s;
}

.spotify-button:hover {
  transform: scale(1.05);
}

.help-text {
  color: rgba(255, 255, 255, 0.8);
  font-size: 0.875rem;
  margin-top: 1rem;
}

/* Playlist sources info */

.playlist-sources-info {
  margin-top: 3rem;
  padding-top: 2rem;
  border-top: 1px solid #404040;
}

.playlist-sources-info h3 {
  color: #fff;
  margin-bottom: 1rem;
}

.profile-grid {
  display: grid;
  grid-template-columns: repeat(auto-fit, minmax(200px, 1fr));
  gap: 1rem;
}

.profile-card {
  background: #282828;
  padding: 1rem;
  border-radius: 8px;
  border-left: 3px solid #1DB954;
}

.profile-card strong {
  color: #1DB954;
  display: block;
  margin-bottom: 0.5rem;
}

.profile-card p {
  color: #B3B3B3;
  font-size: 0.875rem;
  margin: 0;
}

/* Flash Messages */

.flash-message {
  max-width: 600px;
  margin: 1rem auto;
  padding: 1rem;
  border-radius: 8px;
  text-align: center;
  font-weight: 500;
}

.flash-success {
  background: rgba(29, 185, 84, 0.1);
  border: 2px solid #1DB954;
  color: #1DB954;
}

.flash-error {
  background: rgba(255, 68, 68, 0.1);
  border: 2px solid #FF4444;
  color: #FF4444;
}

.flash-warning {
  background: rgba(255, 180, 0, 0.1);
  border: 2px solid #FFB400;
  color: #FFB400;
}

The form styles use dark backgrounds consistent with the Spotify theme. Input fields get a green border on focus to indicate activity. The success card uses a gradient background and subtle shadow to make it stand out. The playlist-source grid adapts responsively using grid-template-columns: repeat(auto-fit, minmax(200px, 1fr)), which automatically adjusts column count based on available space.

Testing the HTML form version

Test the form by selecting a playlist source and submitting. The page will refresh, display a success message at the top, and show the new playlist URL. This full-page refresh is intentional. It's the foundation. Once this works reliably, you can enhance it with AJAX.

Try invalid inputs: submit without selecting a source (browser validation should prevent this), manually modify the form HTML to submit an invalid source value (your backend whitelist validation should catch it), disconnect Spotify in another tab and try generating (error handling should display a clear message).