The Migration Problem: When `CREATE TABLE IF NOT EXISTS` Isn't Enough

In the course of building a sophisticated GPU proving infrastructure on Vast.ai, the assistant encountered a deceptively subtle database migration problem. The message at index 1385 is a single paragraph of reasoning followed by a file edit — but it represents a critical turning point where the assistant recognized that SQLite's CREATE TABLE IF NOT EXISTS semantics would silently defeat a naive schema change, and pivoted to a correct migration strategy.

The Message

Since this is SQLite with CREATE TABLE IF NOT EXISTS, I can't just add columns to the schema — existing tables won't be altered. I'll use a migration approach: add the columns via ALTER TABLE in the NewServer function after schema creation.

This brief statement encapsulates a deep understanding of SQLite's behavior, a recognition of the production state of the system, and a deliberate architectural decision about how to evolve the database schema without data loss.

The Problem: Vanishing Instance Metadata

The context for this message begins with a user complaint at [msg 1377]: "Logs and all instance metadata disappears when an instance is killed, should be kept around." The vast-manager system tracked GPU instances rented from Vast.ai — their GPU type, location, cost, SSH commands, and other metadata. When an instance was destroyed (killed), the system lost all this information because it was sourced entirely from the live Vast API cache. Once the instance no longer appeared in vastai show instances, its vast_id became 0, and the dashboard showed empty fields.

The assistant traced the root cause at [msg 1378]: "The issue is that killed instances have vast_id=0 because they're no longer in the vast cache (destroyed), so all the vast-sourced metadata (GPU, location, SSH, etc.) is lost." The solution was clear: persist the metadata into the SQLite database when the instance is first seen, so it survives the instance's destruction.

The assistant formulated a five-point plan at [msg 1383]:

Add vast metadata columns to the instances table (vast_id, host_id, machine_id, gpu_name, num_gpus, dph_total, geolocation, cpu_name, cpu_ram_mb, gpu_ram_mb, ssh_cmd, public_ip, status_msg)
Persist the metadata in the monitor cycle when a DB instance matches a Vast instance
Use the DB metadata as fallback in the dashboard handler when the Vast cache misses
Extend killed instance retention from 24 hours to 7 days
Keep the in-memory log buffer for logs (acceptable until process restart) Step one was to add the columns. The assistant read the schema at [msg 1384] and prepared to make the change. Then came the critical realization.

The SQLite Constraint

The existing schema was defined as a Go constant using CREATE TABLE IF NOT EXISTS:

const schema = `
CREATE TABLE IF NOT EXISTS instances (
    uuid           TEXT PRIMARY KEY,
    label          TEXT NOT NULL,
    runner_id      INTEGER UNIQUE NOT NULL,
    state          TEXT NOT NULL DEFAULT 'registered',
    ...
);
`

This is a common pattern for embedded SQLite applications: define the schema once, and CREATE TABLE IF NOT EXISTS ensures it only runs on first initialization. The problem is that it only runs on first initialization. If the table already exists — which it does on the production controller at 10.1.2.104, where the vast-manager has been running for days — the CREATE TABLE IF NOT EXISTS statement is a no-op. Adding new column definitions to the schema constant would have zero effect on the existing database.

The assistant recognized this immediately. Simply adding gpu_name TEXT or dph_total REAL to the schema constant would look correct in the source code but would never materialize in the actual database. The production system would continue to operate with the old, column-deficient schema, and the metadata persistence feature would silently fail.

The Migration Strategy

The assistant's solution was to use a migration approach: add the columns via ALTER TABLE statements in the NewServer function, after the schema creation. This is the correct pattern for SQLite schema evolution:

func NewServer(dbPath string) (*Server, error) {
    db, err := sql.Open("sqlite3", dbPath+"?...")
    // ...
    if _, err := db.Exec(schema); err != nil {
        return nil, fmt.Errorf("create schema: %w", err)
    }
    
    // Migration: add columns for vast metadata persistence
    db.Exec("ALTER TABLE instances ADD COLUMN vast_id INTEGER")
    db.Exec("ALTER TABLE instances ADD COLUMN host_id INTEGER")
    // ... more columns
}

This approach has several advantages:

Idempotency for new deployments: On a fresh database, the schema creates the table with all columns, and the ALTER TABLE statements silently succeed (or fail harmlessly if the column already exists — SQLite returns an error for duplicate column addition, which must be handled).
Backward compatibility for existing deployments: On the production database, the schema is a no-op, but the ALTER TABLE statements add the missing columns.
Minimal code change: The schema constant remains the authoritative definition for new databases, while the migration handles the upgrade path. The assistant executed the edit at [msg 1385] and immediately followed up at [msg 1386] by reading the NewServer function to find the exact insertion point for the migration statements. The subsequent edit at [msg 1387] applied the migration, and the todo list was updated at [msg 1388] to mark the schema step as completed.

The LSP Error: A Red Herring

The message also reports an LSP error: ERROR [31:12] pattern ui.html: no matching files found. This is a pre-existing issue unrelated to the edit — the Go language server is configured with a build pattern that doesn't find the UI template file. The assistant correctly ignores it, as it has appeared in virtually every edit message throughout the session. This is a common occurrence in projects where Go embeds static assets; the LSP's build configuration doesn't include the full file glob.

Broader Significance

This message exemplifies a class of bugs that plague embedded database applications: the silent assumption that schema definitions are always applied. The CREATE TABLE IF NOT EXISTS pattern is so common that developers often treat the schema constant as the ground truth of the database structure, forgetting that it only executes once per database lifetime. When the application is already deployed, adding columns to the schema constant is a no-op — the code compiles, the application runs, but the feature silently doesn't work.

The assistant's reasoning shows a production-aware mindset. Rather than naively adding columns to the schema and deploying, it considered the state of the existing database and chose a migration strategy. This is the difference between code that works in development and code that works in production.

The migration approach also set the stage for the remaining steps of the plan: persisting metadata in the monitor cycle ([msg 1390]), using DB metadata as fallback in the dashboard, and extending killed instance retention. Each subsequent step depended on the columns existing in the database, making this message the foundation of the entire feature.

Conclusion

Message 1385 is a small but pivotal moment in the vast-manager's evolution. In a few lines of reasoning and a single file edit, the assistant recognized a subtle SQLite limitation, formulated a correct migration strategy, and executed it. The result was a robust persistence layer that would allow the system to retain instance metadata across destruction events — turning the vast-manager from a live-view monitoring tool into a system with historical memory. It's a reminder that in production systems, the difference between a feature that works and one that silently fails often comes down to understanding how your database actually behaves, not how you assume it behaves.

The Migration Problem: When CREATE TABLE IF NOT EXISTS Isn't Enough