Case Study 1A.4: How to build Spotify Offline in Mobile

The Disconnected Edge (< 1 GB)

How apps maintain transactional guarantees in isolated, low-storage environments.

How do Spotify-like data apps work on your smartphone?

Goal: Learn how to build data apps on smartphones and portable devices

Spotify isn't just about streaming when you're online. It's about the magic of "Offline Mode"—the ability to listen on planes, in tunnels, or when you're conserving data. The question is: How do you keep a database in your pocket?

The Smallest Powerhouse: SQLite

The go-to solution for mobile apps is SQLite.

Built-in: Preinstalled on over 3 billion devices, both Android and iOS.
Serverless: Unlike cloud databases that need vast clusters, SQLite sits snugly on your device.
Transactional: Adheres to SQL's strict rules, ensuring data integrity even if your phone dies mid-update.

With SQLite, developers handle data operations seamlessly across platforms without reinventing the wheel.

Example: The Offline Schema

On your device, the schema mirrors the cloud, but with tweaks for mobile realities:

-- Songs on your device
CREATE TABLE Songs (
    id INTEGER PRIMARY KEY,
    title TEXT,
    artist TEXT,
    file_location TEXT -- Stores the path to the file on your phone's storage
);

-- Local Playlists
CREATE TABLE Playlists (
    id INTEGER PRIMARY KEY,
    name TEXT,
    created_at TIMESTAMP,
    updated_at TIMESTAMP -- Critical for knowing when to sync with the cloud
);

-- Tracks in Playlists
CREATE TABLE Playlist_tracks (
    playlist_id INTEGER,
    song_id INTEGER,
    added_at TIMESTAMP,
    PRIMARY KEY (playlist_id, song_id) -- A compound key
);

The Synchronization Challenge: Keeping in Sync

Offline, you might add a song to your "Road Trip" playlist. Once back online, the app needs to update the cloud with these changes.

Spotify leverages Common Table Expressions (CTEs) to identify recent changes. Here's how you might track updates from the last 10 minutes (600 seconds):

WITH RecentPlaylists AS (
    SELECT * FROM Playlists 
    WHERE updated_at > datetime('now', '-600 seconds')
),
RecentTracks AS (
    SELECT * FROM Playlist_tracks 
    WHERE added_at > datetime('now', '-600 seconds')
)
SELECT * FROM RecentPlaylists;

Note: datetime('now', '-600 seconds') is SQLite's way of defining the "sync window."

Takeaway: Since SQLite uses the same declarative language as your cloud databases, developers can extend complex state logic to the isolated edge (your phone) without overhauling their core data architecture.