Schema Management

Tables in Catalyzed support schema evolution. You can add columns, modify types, and track schema versions over time.

Schema Versioning

Every table maintains a history of schema changes. Each modification creates a new version:

v1: Initial schema (id, name, email)
v2: Added column (id, name, email, phone)
v3: Changed type (id, name, email, phone_number)

View Current Schema

Get current schema

cURL

curl https://api.catalyzed.ai/dataset-tables/KzaMsfA0LSw_Ld0KyaXIS/schema \
  -H "Authorization: Bearer $API_TOKEN"

TypeScript

const response = await fetch(
  "https://api.catalyzed.ai/dataset-tables/KzaMsfA0LSw_Ld0KyaXIS/schema",
  { headers: { Authorization: `Bearer ${apiToken}` } }
);
const schema = await response.json();

Python

response = requests.get(
    "https://api.catalyzed.ai/dataset-tables/KzaMsfA0LSw_Ld0KyaXIS/schema",
    headers={"Authorization": f"Bearer {api_token}"}
)
schema = response.json()

Response:

{
  "schemaVersionId": "sch_abc123",
  "versionNumber": 3,
  "arrowSchema": "...",
  "fields": [
    {"name": "id", "dataType": "string", "nullable": false},
    {"name": "name", "dataType": "string", "nullable": false},
    {"name": "email", "dataType": "string", "nullable": true},
    {"name": "phone_number", "dataType": "string", "nullable": true}
  ],
  "primaryKeyColumns": ["id"],
  "appliedAt": "2024-03-15T14:30:00Z"
}

View Schema History

List schema versions

cURL

curl https://api.catalyzed.ai/dataset-tables/KzaMsfA0LSw_Ld0KyaXIS/schema/versions \
  -H "Authorization: Bearer $API_TOKEN"

TypeScript

const response = await fetch(
  "https://api.catalyzed.ai/dataset-tables/KzaMsfA0LSw_Ld0KyaXIS/schema/versions",
  { headers: { Authorization: `Bearer ${apiToken}` } }
);
const versions = await response.json();

Python

response = requests.get(
    "https://api.catalyzed.ai/dataset-tables/KzaMsfA0LSw_Ld0KyaXIS/schema/versions",
    headers={"Authorization": f"Bearer {api_token}"}
)
versions = response.json()

Response:

{
  "versions": [
    {
      "schemaVersionId": "sch_abc123",
      "versionNumber": 3,
      "appliedAt": "2024-03-15T14:30:00Z"
    },
    {
      "schemaVersionId": "sch_abc122",
      "versionNumber": 2,
      "appliedAt": "2024-02-10T09:15:00Z"
    },
    {
      "schemaVersionId": "sch_abc121",
      "versionNumber": 1,
      "appliedAt": "2024-01-15T10:00:00Z"
    }
  ]
}

Schema Migrations

Migrations allow you to evolve your schema in a controlled way.

Supported Operations

Operation	Description
add_column	Add a new column
drop_column	Remove a column
rename_column	Rename a column
change_column_type	Change column data type
alter_column_nullability	Make column nullable or non-nullable
set_primary_key	Set primary key columns
drop_primary_key	Remove primary key

Add a Column

Add column migration

cURL

curl -X POST https://api.catalyzed.ai/dataset-tables/KzaMsfA0LSw_Ld0KyaXIS/migrations \
  -H "Authorization: Bearer $API_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "operations": [
      {
        "type": "add_column",
        "params": {
          "name": "phone",
          "dataType": "string",
          "nullable": true
        }
      }
    ]
  }'

TypeScript

await fetch("https://api.catalyzed.ai/dataset-tables/KzaMsfA0LSw_Ld0KyaXIS/migrations", {
  method: "POST",
  headers: {
    Authorization: `Bearer ${apiToken}`,
    "Content-Type": "application/json",
  },
  body: JSON.stringify({
    operations: [
      {
        type: "add_column",
        params: {
          name: "phone",
          dataType: "string",
          nullable: true,
        },
      },
    ],
  }),
});

Python

requests.post(
    "https://api.catalyzed.ai/dataset-tables/KzaMsfA0LSw_Ld0KyaXIS/migrations",
    headers={"Authorization": f"Bearer {api_token}"},
    json={
        "operations": [
            {
                "type": "add_column",
                "params": {
                    "name": "phone",
                    "dataType": "string",
                    "nullable": True
                }
            }
        ]
    }
)

Drop a Column

Caution

Dropping a column permanently removes data. This cannot be undone.

{
  "operations": [
    {
      "type": "drop_column",
      "params": {
        "name": "legacy_field"
      }
    }
  ]
}

Rename a Column

{
  "operations": [
    {
      "type": "rename_column",
      "params": {
        "oldName": "phone",
        "newName": "phone_number"
      }
    }
  ]
}

Change Column Type

{
  "operations": [
    {
      "type": "change_column_type",
      "params": {
        "name": "amount",
        "oldType": "int32",
        "newType": "float64"
      }
    }
  ]
}

Type conversion rules:

• string → any type (if values are parseable)
• int32 → int64, float32, float64, string
• float32 → float64, string
• timestamp → string, date

Alter Nullability

{
  "operations": [
    {
      "type": "alter_column_nullability",
      "params": {
        "name": "email",
        "nullable": false
      }
    }
  ]
}

Note

Making a column non-nullable will fail if the column contains NULL values.

Set Primary Key

{
  "operations": [
    {
      "type": "set_primary_key",
      "params": {
        "columns": ["id"],
        "enforced": true
      }
    }
  ]
}

Drop Primary Key

{
  "operations": [
    {
      "type": "drop_primary_key",
      "params": {}
    }
  ]
}

Multiple Operations

Apply multiple changes in a single migration:

{
  "operations": [
    {
      "type": "add_column",
      "params": {"name": "updated_at", "dataType": "timestamp", "nullable": true}
    },
    {
      "type": "rename_column",
      "params": {"oldName": "created", "newName": "created_at"}
    },
    {
      "type": "drop_column",
      "params": {"name": "deprecated_field"}
    }
  ]
}

Operations are applied in order.

Planning Migrations

Before applying a migration, validate it:

Plan migration (dry run)

cURL

curl -X POST https://api.catalyzed.ai/dataset-tables/KzaMsfA0LSw_Ld0KyaXIS/migrations/plan \
  -H "Authorization: Bearer $API_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "operations": [
      {"type": "add_column", "params": {"name": "phone", "dataType": "string", "nullable": true}}
    ]
  }'

TypeScript

const response = await fetch(
  "https://api.catalyzed.ai/dataset-tables/KzaMsfA0LSw_Ld0KyaXIS/migrations/plan",
  {
    method: "POST",
    headers: {
      Authorization: `Bearer ${apiToken}`,
      "Content-Type": "application/json",
    },
    body: JSON.stringify({
      operations: [
        { type: "add_column", params: { name: "phone", dataType: "string", nullable: true } },
      ],
    }),
  }
);
const plan = await response.json();

Python

response = requests.post(
    "https://api.catalyzed.ai/dataset-tables/KzaMsfA0LSw_Ld0KyaXIS/migrations/plan",
    headers={"Authorization": f"Bearer {api_token}"},
    json={
        "operations": [
            {"type": "add_column", "params": {"name": "phone", "dataType": "string", "nullable": True}}
        ]
    }
)
plan = response.json()

Response:

{
  "isValid": true,
  "validationErrors": [],
  "estimatedDurationMs": 1500,
  "indexImpacts": [],
  "requiresRewrite": false
}

Validation Errors

{
  "isValid": false,
  "validationErrors": [
    "Column 'email' already exists",
    "Cannot change type from 'timestamp' to 'int64'"
  ],
  "estimatedDurationMs": null,
  "indexImpacts": [],
  "requiresRewrite": false
}

Migration Status

Migrations run as background operations. List migrations for a table:

curl https://api.catalyzed.ai/dataset-tables/KzaMsfA0LSw_Ld0KyaXIS/migrations \
  -H "Authorization: Bearer $API_TOKEN"

Response:

{
  "migrations": [
    {
      "migrationId": "cWBPHwTmJ-MaDFmei1WKF",
      "status": "completed",
      "fromVersionNumber": 1,
      "toVersionNumber": 2,
      "createdAt": "2024-01-15T10:30:00Z",
      "completedAt": "2024-01-15T10:30:45Z"
    }
  ],
  "total": 1,
  "page": 1,
  "pageSize": 20
}

Best Practices

1. Always Plan First

Run the plan endpoint before applying migrations to catch errors early.

2. Add Columns as Nullable

When adding columns, make them nullable initially:

{
  "type": "add_column",
  "params": {"name": "new_field", "dataType": "string", "nullable": true}
}

Then backfill data, then make non-nullable if needed.

3. Avoid Breaking Changes

These operations may break existing queries:

• Dropping columns
• Renaming columns
• Changing column types

Coordinate with API consumers before applying.

4. Use Descriptive Column Names

Choose clear, descriptive names:

• created_at instead of created
• customer_id instead of cid
• total_amount instead of amt

5. Document Changes

Track migrations in your application’s version control alongside code changes.

Index Management After Schema Changes

Indexes may need rebuilding after schema changes. Check index status:

curl https://api.catalyzed.ai/dataset-tables/KzaMsfA0LSw_Ld0KyaXIS/indexes \
  -H "Authorization: Bearer $API_TOKEN"

To rebuild an index, drop and recreate it:

# Drop the index
curl -X DELETE https://api.catalyzed.ai/dataset-tables/KzaMsfA0LSw_Ld0KyaXIS/indexes/idx_name \
  -H "Authorization: Bearer $API_TOKEN"

# Recreate the index
curl -X POST https://api.catalyzed.ai/dataset-tables/KzaMsfA0LSw_Ld0KyaXIS/indexes \
  -H "Authorization: Bearer $API_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"indexName": "idx_name", "columnName": "column_name", "indexType": "btree"}'

Next Steps

• Tables - Full table operations reference
• Querying Data - SQL syntax and examples