schema-design

# 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):

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

Long form (with cascade behavior):

```json
"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:

```json
"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:

```json
{
  "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:

```json
"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:

```json
{
  "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

```json
"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:

```js
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 existing table definition and call `action: "apply"`. Existing rows receive the column's `default` value (or NULL if no default).

### Destructive operations

Dropping tables and columns is opt-in and ex