11. Settings and data management

The dashboard now needs one final page: Settings. This is where the user can check their Spotify connection, take a fresh snapshot, export the SQLite database, disconnect from Spotify, and clear saved data when they choose to start over.

This page is not complicated, but it handles actions that matter. Downloading the database is harmless. Disconnecting Spotify changes the session. Clearing the database is destructive. Each action gets a clear route, a clear button, and a clear message after it runs.

What the Settings page needs

The page has four jobs:

show whether the user is connected to Spotify
show simple database statistics
let the user take a fresh snapshot or export the database
protect dangerous actions such as disconnecting and clearing data

The main /settings route renders the page. The action routes do the work and redirect back with a flash message. Actions that change data use POST. The database export uses GET because it only downloads a file.

Add the Settings routes

Add these routes to app.py. The helper function reads the current database state, and the route handlers turn each button on the Settings page into a small Flask action.

app.py

# app.py -- Settings routes.
import os
import shutil
import subprocess
import sys
import tempfile

from flask import after_this_request, send_file


def _get_database_status():
    """Read row counts for the Ch16 tables plus file size on disk."""
    status = {
        'is_connected': 'access_token' in session,
        'tracks_count': 0,
        'snapshots_count': 0,
        'last_snapshot': None,
        'db_size_mb': 0,
    }

    if os.path.exists(DATABASE_PATH):
        status['db_size_mb'] = round(
            os.path.getsize(DATABASE_PATH) / (1024 * 1024), 2
        )

    conn = get_db_connection()
    if not conn:
        return status
    try:
        cursor = conn.cursor()
        for row_key, table in (
            ('tracks_count', 'tracks'),
            ('snapshots_count', 'snapshots'),
        ):
            try:
                cursor.execute(f"SELECT COUNT(*) FROM {table}")
                status[row_key] = cursor.fetchone()[0]
            except sqlite3.Error:
                # Table doesn't exist yet -- leave the count at 0.
                pass

        try:
            cursor.execute("SELECT MAX(snapshot_date) FROM snapshots")
            row = cursor.fetchone()
            status['last_snapshot'] = row[0] if row else None
        except sqlite3.Error:
            pass
    finally:
        conn.close()

    return status


@app.route('/settings')
@require_auth
def settings():
    """Display settings: connection status, db stats, and action buttons."""
    return render_template('settings.html', status=_get_database_status())


@app.route('/settings/sync', methods=['POST'])
@require_auth
def manual_sync():
    """Trigger Chapter 16's monthly_snapshots.py to take a fresh snapshot."""
    if 'access_token' not in session:
        flash('Please connect to Spotify first.', 'error')
        return redirect(url_for('settings'))

    try:
        # Chapter 16's snapshot script is subprocess-friendly thanks to
        # its __main__ guard. If you'd rather call the function directly,
        # import create_monthly_snapshot and build the Spotify client and
        # connection here -- the boundary is still the SQLite file either way.
        result = subprocess.run(
            [sys.executable, 'monthly_snapshots.py'],
            capture_output=True,
            text=True,
            timeout=60,
        )
        if result.returncode != 0:
            flash(f'Snapshot failed: {result.stderr.strip()}', 'error')
        else:
            flash('Snapshot complete. New rows added to snapshots.', 'success')
    except subprocess.TimeoutExpired:
        flash('Snapshot timed out after 60 seconds.', 'error')
    except Exception as e:
        flash(f'Sync failed: {e}', 'error')

    return redirect(url_for('settings'))


@app.route('/settings/export')
@require_auth
def export_database():
    """Send a copy of music_time_machine.db as a downloadable file."""
    if not os.path.exists(DATABASE_PATH):
        flash('No database to export yet.', 'warning')
        return redirect(url_for('settings'))

    try:
        # Copy first so the live database doesn't get locked during the
        # download. Use the OS temp directory rather than hard-coded /tmp
        # so this works on Windows too.
        timestamp = datetime.now().strftime('%Y%m%d_%H%M%S')
        export_filename = f'music_time_machine_{timestamp}.db'
        export_path = os.path.join(tempfile.gettempdir(), export_filename)
        shutil.copy2(DATABASE_PATH, export_path)

        @after_this_request
        def cleanup_export(response):
            try:
                os.remove(export_path)
            except OSError:
                pass
            return response

        return send_file(
            export_path,
            as_attachment=True,
            download_name=export_filename,
            mimetype='application/x-sqlite3',
        )
    except Exception as e:
        flash(f'Export failed: {e}', 'error')
        return redirect(url_for('settings'))


@app.route('/settings/disconnect', methods=['POST'])
@require_auth
def disconnect_spotify():
    """Clear every Spotify-related session key the OAuth flow set."""
    for key in ('access_token', 'refresh_token', 'token_expires_at',
                'oauth_state', 'next_url'):
        session.pop(key, None)
    flash(
        'Disconnected from Spotify. Your snapshot data is still in the database.',
        'success',
    )
    return redirect(url_for('home'))


@app.route('/settings/clear', methods=['POST'])
@require_auth
def clear_all_data():
    """Delete every row from tracks and snapshots."""
    if request.form.get('confirmed') != 'true':
        flash('Please confirm deletion using the checkbox before proceeding.',
              'warning')
        return redirect(url_for('settings'))

    try:
        # Context manager makes the two DELETEs atomic -- either both
        # commit, or any error rolls all of them back.
        with sqlite3.connect(DATABASE_PATH, timeout=10) as conn:
            cursor = conn.cursor()
            deleted = 0
            for table in ('snapshots', 'tracks'):
                cursor.execute(f'DELETE FROM {table}')
                deleted += cursor.rowcount

        flash(f'All data cleared. Deleted {deleted} rows.', 'success')
    except sqlite3.Error as e:
        flash(f'Error clearing data: {e}', 'error')

    return redirect(url_for('settings'))

Notice the two extra precautions in the destructive routes. Disconnect clears every Spotify-related session key, not just the access token. Clear data checks for an explicit confirmation value before it deletes any rows.

The clear route also wraps the delete statements in a SQLite context manager. That means the two deletes succeed together, or the transaction rolls back if SQLite raises an error.

Add the Settings template

Create templates/settings.html. The template displays connection status, safe actions, database statistics, and a danger zone for actions that need extra care.

templates/settings.html

{% extends "base.html" %}

{% block title %}Settings - Music Time Machine{% endblock %}

{% block content %}
<div class="page-header">
  <h1>Settings & Data Management</h1>
  <p class="page-subtitle">Manage your connection and data</p>
</div>

{% if status %}

<!-- Connection Status -->
<div class="settings-section">
  <h2>Connection Status</h2>
  <div class="status-card">
    <div class="status-indicator {% if status.is_connected %}status-connected{% else %}status-disconnected{% endif %}">
      {% if status.is_connected %}
        <span class="status-icon">✓</span> Connected to Spotify
      {% else %}
        <span class="status-icon">✗</span> Not Connected
      {% endif %}
    </div>
    {% if status.is_connected %}
      <p class="status-detail">Your account is linked and ready to sync</p>
    {% else %}
      <p class="status-detail">
        <a href="{{ url_for('login') }}">Connect your Spotify account</a> to enable features
      </p>
    {% endif %}
  </div>
</div>

<!-- Data Management -->
<div class="settings-section">
  <h2>Data management</h2>
  <div class="action-grid">
    <!-- Take a snapshot -->
    <div class="action-card">
      <h3>Take a snapshot</h3>
      <p>Run Chapter 16's monthly_snapshots.py and refresh the database</p>
      <form method="POST" action="{{ url_for('manual_sync') }}">
        <input type="hidden" name="csrf_token" value="{{ csrf_token() }}"/>
        <button type="submit" class="action-button" {% if not status.is_connected %}disabled{% endif %}>
          Take snapshot
        </button>
      </form>
    </div>
    <!-- Export Database -->
    <div class="action-card">
      <h3>Export database</h3>
      <p>Download music_time_machine.db</p>
      <a href="{{ url_for('export_database') }}" class="action-button">
        Download database
      </a>
    </div>
  </div>
</div>

<!-- Diagnostic Information -->
<div class="settings-section">
  <h2>Database statistics</h2>
  <div class="diagnostic-grid">
    <div class="diagnostic-item">
      <span class="diagnostic-label">Tracks</span>
      <span class="diagnostic-value">{{ status.tracks_count | default(0) }}</span>
    </div>
    <div class="diagnostic-item">
      <span class="diagnostic-label">Snapshot rows</span>
      <span class="diagnostic-value">{{ status.snapshots_count | default(0) }}</span>
    </div>
    <div class="diagnostic-item">
      <span class="diagnostic-label">Database size</span>
      <span class="diagnostic-value">{{ status.db_size_mb }} MB</span>
    </div>
    <div class="diagnostic-item">
      <span class="diagnostic-label">Last snapshot</span>
      <span class="diagnostic-value">
        {% if status.last_snapshot %}
          {{ status.last_snapshot[:10] }}
        {% else %}
          Never
        {% endif %}
      </span>
    </div>
  </div>
</div>

<!-- Danger Zone -->
<div class="settings-section danger-zone">
  <h2>Danger Zone</h2>
  <p class="danger-warning">These actions cannot be undone</p>
  <div class="action-grid">
    <!-- Disconnect Spotify -->
    <div class="action-card danger-card">
      <h3>Disconnect Spotify</h3>
      <p>Remove OAuth connection (data remains)</p>
      <form method="POST" action="{{ url_for('disconnect_spotify') }}"
            onsubmit="return confirm('Are you sure you want to disconnect from Spotify? You will need to log in again to use dashboard features.')">
        <input type="hidden" name="csrf_token" value="{{ csrf_token() }}"/>
        <button type="submit" class="danger-button" {% if not status.is_connected %}disabled{% endif %}>
          Disconnect
        </button>
      </form>
    </div>
    <!-- Clear All Data -->
    <div class="action-card danger-card">
      <h3>Clear all data</h3>
      <p>Delete all snapshots and tracks permanently</p>
      <form method="POST" action="{{ url_for('clear_all_data') }}" id="clearDataForm">
        <input type="hidden" name="csrf_token" value="{{ csrf_token() }}"/>
        <label class="checkbox-label">
          <input type="checkbox" name="confirmed" value="true" required>
          I understand this cannot be undone
        </label>
        <button type="submit" class="danger-button">
          Delete Everything
        </button>
      </form>
    </div>
  </div>
</div>

{% else %}
<div class="error-state">
  <p>Unable to load settings. Please try refreshing the page.</p>
</div>
{% endif %}

{% endblock %}

The template disables actions that do not make sense in the current state. For example, the snapshot button is disabled when the user is not connected to Spotify. The clear data form also requires a checkbox before the backend accepts the request.

Add the Settings styles

Add these styles to static/css/dashboard.css.

static/css/dashboard.css

/* Settings Page Styles */

.settings-section {
  background: #181818;
  border-radius: 8px;
  padding: 2rem;
  margin-bottom: 2rem;
}

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

/* Connection Status */

.status-card {
  background: #282828;
  border-radius: 8px;
  padding: 1.5rem;
}

.status-indicator {
  display: flex;
  align-items: center;
  gap: 0.75rem;
  font-size: 1.125rem;
  font-weight: 600;
  margin-bottom: 0.5rem;
}

.status-connected {
  color: #1DB954;
}

.status-disconnected {
  color: #FF4444;
}

.status-icon {
  display: inline-flex;
  align-items: center;
  justify-content: center;
  width: 28px;
  height: 28px;
  border-radius: 50%;
  background: currentColor;
  color: #fff;
  font-size: 1rem;
}

.status-detail {
  color: #B3B3B3;
  margin: 0;
}

.status-detail a {
  color: #1DB954;
  text-decoration: none;
}

.status-detail a:hover {
  text-decoration: underline;
}

/* Action Grid */

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

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

.action-card h3 {
  color: #fff;
  margin-bottom: 0.5rem;
  font-size: 1.125rem;
}

.action-card p {
  color: #B3B3B3;
  font-size: 0.875rem;
  margin-bottom: 1rem;
}

.action-button {
  display: inline-block;
  width: 100%;
  padding: 0.75rem;
  background: #1DB954;
  color: #fff;
  text-align: center;
  text-decoration: none;
  border: none;
  border-radius: 4px;
  font-weight: 600;
  cursor: pointer;
  transition: background 0.2s;
}

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

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

/* Diagnostic Grid */

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

.diagnostic-item {
  background: #282828;
  border-radius: 8px;
  padding: 1rem;
  display: flex;
  flex-direction: column;
  gap: 0.5rem;
}

.diagnostic-label {
  color: #B3B3B3;
  font-size: 0.875rem;
}

.diagnostic-value {
  color: #fff;
  font-size: 1.5rem;
  font-weight: 600;
}

/* Danger Zone */

.danger-zone {
  border: 2px solid #FF4444;
  background: rgba(255, 68, 68, 0.05);
}

.danger-zone h2 {
  color: #FF4444;
}

.danger-warning {
  color: #FF4444;
  font-weight: 600;
  margin-bottom: 1.5rem;
}

.danger-card {
  border-left-color: #FF4444;
  background: rgba(255, 68, 68, 0.1);
}

.danger-button {
  background: #FF4444;
}

.danger-button:hover {
  background: #FF6666;
}

.checkbox-label {
  display: flex;
  align-items: center;
  gap: 0.5rem;
  color: #B3B3B3;
  font-size: 0.875rem;
  margin-bottom: 1rem;
  cursor: pointer;
}

.checkbox-label input[type="checkbox"] {
  width: 18px;
  height: 18px;
  cursor: pointer;
}

Test the Settings page

Run the app and open /settings. Check the page in both states: connected to Spotify and not connected.

Take a snapshot and confirm the database counts update.
Export the database and confirm the downloaded file opens in a SQLite browser.
Disconnect Spotify and confirm protected pages ask you to connect again.
Try clearing data without the checkbox and confirm the route refuses the action.
Check the box, clear the data, and confirm the row counts drop.

What to remember

Settings pages are control panels. They should be clear, careful, and predictable. Safe actions can be simple. Destructive actions need confirmation, server-side checks, and useful feedback after the request finishes.