/schema-design

name: schema-design
description: Use when designing database schemas, creating or modifying tables, choosing column types, adding indexes, or working with the Butterbase declarative schema DSL

Schema Design Skill

Reference guide for Butterbase's declarative schema DSL. Covers column types, constraints, indexes, and common data modeling patterns.

---

1. Overview

Butterbase uses a **declarative schema DSL** — you describe the desired end state of your database, and the platform computes and applies the diff. You never write raw `ALTER TABLE` or `CREATE TABLE` SQL. Instead, call `manage_schema` with `action: "apply"` and a JSON payload describing your tables, columns, and indexes.

The single `manage_schema` tool exposes four actions:

| Action | Purpose | |--------|---------| | `"get"` | Read the current schema | | `"dry_run"` | Preview SQL that `apply` would execute, without running it | | `"apply"` | Apply a declarative schema (diffs against current, runs safe DDL) | | `"list_migrations"` | List applied migrations, most recent first |

Key principles:

• **Idempotent**: applying the same schema twice is safe — returns "Schema is up to date" if no changes needed
• **Additive by default**: new columns and tables are created automatically
• **Explicit drops**: destructive operations require opt-in via `_drop` / `_dropColumns`
• **Preview first**: use `action: "dry_run"` to see what will change before committing
• **Transactional**: each migration runs in a single transaction — all changes commit or all roll back

---

2. Column Types Reference

| Type | PostgreSQL | Use case | |------|-----------|----------| | `uuid` | UUID | Primary keys, foreign keys | | `text` | TEXT | Strings of any length | | `integer` | INTEGER | Whole numbers (-2B to 2B) | | `bigint` | BIGINT | Large whole numbers | | `boolean` | BOOLEAN | True/false flags | | `timestamptz` | TIMESTAMPTZ | Dates with timezone | | `jsonb` | JSONB | Structured/semi-structured data | | `real` | REAL | 32-bit floating point | | `double precision` | DOUBLE PRECISION | 64-bit floating point | | `vector(N)` | VECTOR(N) | Embeddings (pgvector); e.g. `vector(1536)` for OpenAI |

> **Always use `timestamptz` instead of `timestamp`.** `timestamp` silently drops timezone info and causes subtle bugs with users in different time zones.

---

3. Column Properties

Each column is an object with the following properties:

| Property | Type | Required | Default | Description | |----------|------|----------|---------|-------------| | `type` | string | ✅ yes | — | Column data type (see §2) | | `primaryKey` | boolean | no | false | Mark as primary key | | `nullable` | boolean | no | true | Allow NULL values | | `default` | string | no | — | SQL expression for default value | | `unique` | boolean | no | false | Add unique constraint | | `references` | string \| object | no | — | Foreign key target (see below) |

Foreign keys — short or long form

Short form (just the target):

"author_id": { "type": "uuid", "nullable": false, "references": "users.id" }

Long form (with cascade behavior):

"author_id": {
  "type": "uuid",
  "nullable": false,
  "references": {
    "table": "users",
    "column": "id",
    "onDelete": "CASCADE",
    "onUpdate": "NO ACTION"
  }
}

`onDelete` / `onUpdate` accept `CASCADE | SET NULL | SET DEFAULT | RESTRICT | NO ACTION` (default `NO ACTION`).

Default expressions

Pass SQL expressions as strings:

"default": "gen_random_uuid()"   // UUID primary keys
"default": "now()"               // Timestamps
"default": "false"               // Booleans
"default": "0"                   // Integers
"default": "'draft'"             // String literals (single-quoted)

---

4. Standard Base Pattern

Every table should include these base columns:

{
  "id": { "type": "uuid", "primaryKey": true, "default": "gen_random_uuid()" },
  "created_at": { "type": "timestamptz", "nullable": false, "default": "now()" },
  "updated_at": { "type": "timestamptz", "nullable": false, "default": "now()" }
}

If your app uses Row-Level Security (RLS), also add:

"user_id": { "type": "uuid", "nullable": false, "references": "users.id" }

> Tables without `user_id` cannot have per-user RLS policies applied later without a migration.

---

5. Index Types

| `method` | Use case | Example opclass | |----------|----------|----------------| | `btree` | Default, range queries, sorting | — | | `hash` | Exact-match lookups | — | | `gin` | Full-text search on JSONB, arrays | `jsonb_path_ops` | | `gist` | Geometric/spatial data | — | | `hnsw` | Vector similarity (pgvector) | `vector_cosine_ops` | | `ivfflat` | Vector similarity (large datasets) | `vector_cosine_ops` |

Index definition format

Indexes are defined per-table under the `indexes` key:

{
  "indexes": {
    "idx_posts_author": {
      "columns": ["author_id"],
      "method": "btree"
    },
    "idx_posts_embedding": {
      "columns": ["embedding"],
      "method": "hnsw",
      "opclass": "vector_cosine_ops"
    },
    "idx_posts_content_search": {
      "columns": ["content"],
      "method": "gin"
    }
  }
}

Index naming convention: `idx_{table}_{column(s)}` — e.g. `idx_orders_user_id`.

Composite indexes

"idx_members_workspace_user": {
  "columns": ["workspace_id", "user_id"],
  "method": "btree",
  "unique": true
}

---

6. Using `manage_schema`

All schema operations go through one tool with an `action` parameter:

manage_schema({ app_id, action: "get" })
manage_schema({ app_id, action: "dry_run", schema })
manage_schema({ app_id, action: "apply", schema, name })   // name is optional
manage_schema({ app_id, action: "list_migrations" })

Creating new tables

Include the table definition in your `schema` payload and call `action: "apply"`. The platform creates the table if it doesn't exist.

Adding columns to existing tables

Add the new column(s) to the ex