Database Changes

Database Schema Changes

Overview

End-to-end process for adding new columns or tables to the CMS database.

Adding a New Column

1. Create Migration

-- migrations/cms/0004_add_my_column.sql
SET search_path TO toygres_cms, public;

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns
        WHERE table_schema = 'toygres_cms'
          AND table_name = 'instances'
          AND column_name = 'my_column'
    ) THEN
        ALTER TABLE instances ADD COLUMN my_column VARCHAR(255);
    END IF;
END;
$$;

2. Run Migration

./scripts/db-migrate.sh

3. Update Backend API

In toygres-server/src/api.rs, add to SELECT query:

let row = sqlx::query(
    "SELECT ..., my_column FROM toygres_cms.instances WHERE ..."
)

Add to JSON response:

Ok(Json(serde_json::json!({
    // existing fields...
    "my_column": row.get::<Option<String>, _>("my_column")
})))

4. Update TypeScript Types

In toygres-ui/src/lib/types.ts:

export interface InstanceDetail extends Instance {
  // existing fields...
  my_column: string | null;
}

5. Update Activities (if needed)

If an activity should set this column:

sqlx::query("UPDATE toygres_cms.instances SET my_column = $2 WHERE k8s_name = $1")
    .bind(&input.k8s_name)
    .bind(&input.my_column)
    .execute(&pool)
    .await?;

Idempotency Patterns

All CMS activities must be idempotent for Duroxide replay safety:

-- Upsert pattern
INSERT INTO table (id, value) VALUES ($1, $2)
ON CONFLICT (id) DO UPDATE SET value = $2;

-- Conditional update
UPDATE table SET state = 'new_state'
WHERE id = $1 AND state = 'expected_state';