Chapter 4: SQLite Persistence & Schema

Welcome to the fourth chapter of the cc-switch tutorial!

In the previous chapter, Provider Adaptation Layer, we built a universal translator so our proxy could talk to any AI provider.

But we have a loose end. If you configure your API keys, set up your routing rules, and then restart the application... poof! Everything is gone.

In this chapter, we will give cc-switch a permanent memory. We are moving away from fragile JSON configuration files to a robust SQLite Database.

The Problem: JSON is Fragile

Many beginner projects save settings in a config.json file. This works fine for simple tools, but it has major issues for a desktop app like ours:

1. Corruption: If the app crashes while writing to the file, the file breaks.
2. Concurrency: If the frontend reads the file while the backend is writing to it, you get errors.
3. Upgrades: If you release version 2.0 of your app with new settings, the old JSON file might cause a crash.

The Solution: A Digital Filing Cabinet

We use SQLite. It allows us to store data in a single file on your disk (cc-switch.db), but interact with it using structured rules.

Think of it this way:

• JSON is like stuffing receipts into a shoebox. It's easy to start, but hard to find things later.
• SQLite is a steel filing cabinet. Every document has a specific folder (Table), a specific structure (Schema), and an index number (ID).

Key Concepts

1. The Schema (The Blueprint)

A database needs a blueprint. You can't just throw data in; you have to define Tables first.

• Table: A collection of related items (e.g., providers).
• Column: A specific piece of data (e.g., api_key, url).

2. Single Source of Truth

Instead of the Frontend having one state and the Backend having another, the Database is the ultimate authority. If it's not in the database, it didn't happen.

3. Migrations (The Time Machine)

This is the most critical concept in this chapter. When you update the cc-switch app from v1.0 to v2.0, the database structure might need to change (e.g., adding a "Cost per Token" column). Migrations are scripts that automatically upgrade the database structure without deleting your existing data.

Internal Implementation: The Startup Logic

When cc-switch launches, it doesn't just blindly open the database. It performs a "Health Check" and an "Upgrade" if necessary.

Sequence Diagram

1. Defining the Tables (create_tables)

In src-tauri/src/database/schema.rs, we define what our "Filing Cabinet" looks like. We use SQL (Structured Query Language) to create tables if they don't exist.

Here is how we define the providers table:

// src-tauri/src/database/schema.rs

// "conn" is our active connection to the database file
conn.execute(
    "CREATE TABLE IF NOT EXISTS providers (
        id TEXT NOT NULL,
        app_type TEXT NOT NULL,
        name TEXT NOT NULL,
        settings_config TEXT NOT NULL,
        PRIMARY KEY (id, app_type)
    )",
    [], // No parameters needed for creation
)?;

Explanation: This SQL command tells SQLite: "Create a drawer labeled 'providers'. It must have columns for ID, App Type, Name, and Config. If this drawer already exists, do nothing."

2. Handling Updates with Migrations

This is where the magic happens. We assign a version number to our database structure.

• v1: Just had basic provider info.
• v2: We added "Failover" features.
• v3: We added "Skills".

When the app starts, it runs apply_schema_migrations. It loops through versions until the database catches up to the code.

// src-tauri/src/database/schema.rs

// Get the version number stored inside the DB file
let mut version = Self::get_user_version(conn)?;

// Loop until the DB version matches the App version
while version < SCHEMA_VERSION {
    match version {
        1 => {
            // If DB is v1, run the upgrade logic to make it v2
            Self::migrate_v1_to_v2(conn)?;
            Self::set_user_version(conn, 2)?;
        }
        2 => {
            // If DB is v2, upgrade to v3
            Self::migrate_v2_to_v3(conn)?;
            Self::set_user_version(conn, 3)?;
        }
        // ... handle other versions
    }
    // Refresh version variable for the next loop iteration
    version = Self::get_user_version(conn)?;
}

3. A Concrete Migration Example

What does a migration look like? It's usually just adding a missing column.

Imagine we released a new feature to track costs. We need to add a cost_multiplier column to the providers table, but we don't want to lose the providers the user already saved.

// src-tauri/src/database/schema.rs

fn migrate_v1_to_v2(conn: &Connection) -> Result<(), AppError> {
    // Helper function that adds a column only if it's missing
    Self::add_column_if_missing(
        conn,
        "providers",         // Table Name
        "cost_multiplier",   // New Column Name
        "TEXT NOT NULL DEFAULT '1.0'" // Data Type & Default Value
    )?;

    // We can also create entirely new tables here
    conn.execute("CREATE TABLE IF NOT EXISTS proxy_request_logs ...", [])?;

    Ok(())
}

Beginner Note: This safely "renovates" the house while people are living in it. It adds a new room (column) without knocking down the walls (deleting the table).

Why This Matters for You

By implementing this schema system:

1. Reliability: Your cc-switch app can crash, restart, or update, and your data remains safe.
2. History: We can now store thousands of request logs (which we will use in Chapter 6) without slowing down the app, thanks to SQLite's indexing.
3. Complex Data: We can link tables together. A Provider can have many Logs. This relationship is hard to manage in JSON but native to SQLite.

Summary

In this chapter, we gave our application a robust long-term memory:

1. We replaced fragile files with SQLite.
2. We defined a Schema (blueprint) for our data.
3. We implemented Migrations so the database evolves automatically as we update the app.

Now that we have a secure place to store configuration, we can build complex features on top of it. One of the most powerful features in our roadmap involves managing external tools directly.

In the next chapter, we will learn how to manage the "Model Context Protocol" (MCP) servers.

Next Chapter: Unified MCP Management

Generated by Code IQ