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

"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 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 explicit. Without _drop / _dropColumns, the platform refuses with STATE_PREREQUISITE_MISSING.

{
  "schema": {
    "_drop": ["old_table_name", "another_old_table"]
  }
}

Dropping columns from a specific table:

{
  "schema": {
    "posts": {
      "_dropColumns": ["legacy_field", "unused_col"],
      "columns": { ... }
    }
  }
}

> ⚠️ Drops are irreversible. Always run action: "dry_run" first.

Preview before commit

Use the same payload with action: "dry_run" to see the SQL that apply would execute — without touching the database:

// Preview only:
manage_schema({ app_id, action: "dry_run", schema })

// Commit:
manage_schema({ app_id, action: "apply", schema, name: "add_posts_table" })

Common errors

| Code | Meaning | |------|---------| | VALIDATION_INVALID_SCHEMA | Schema format doesn't match the DSL | | STATE_PREREQUISITE_MISSING | Destructive op without _drop / _dropColumns | | QUOTA_TABLE_LIMIT | Exceeds the per-app table limit | | RESOURCE_NOT_FOUND | app_id does not exist |

---

7. Complete Patterns

A) Blog / CMS

{
  "app_id": "YOUR_APP_ID",
  "action": "apply",
  "schema": {
    "users": {
      "columns": {
        "id": { "type": "uuid", "primaryKey": true, "default": "gen_random_uuid()" },
        "email": { "type": "text", "nullable": false, "unique": true },
        "name": { "type": "text", "nullable": false },
        "avatar_url": { "type": "text" },
        "created_at": { "type": "timestamptz", "nullable": false, "default": "now()" },
        "updated_at": { "type": "timestamptz", "nullable": false, "default": "now()" }
      },
      "indexes": {
        "idx_users_email": { "columns": ["email"], "method": "btree", "unique": true }
      }
    },
    "categories": {
      "columns": {
        "id": { "type": "uuid", "primaryKey": true, "default": "gen_random_uuid()" },
        "name": { "type": "text", "nullable": false, "unique": true },
        "slug": { "type": "text", "nullable": false, "unique": true },
        "created_at": { "type": "timestamptz", "nullable": false, "default": "now()" },
        "updated_at": { "type": "timestamptz", "nullable": false, "default": "now()" }
      }
    },
    "posts": {
      "columns": {
        "id": { "type": "uuid", "primaryKey": true, "default": "gen_random_uuid()" },
        "author_id": { "type": "uuid", "nullable": false, "references": "users.id" },
        "category_id": { "type": "uuid", "references": "categories.id" },
        "title": { "type": "text", "nullable": false },
        "slug": { "type": "text", "nullable": false, "unique": true },
        "content": { "type": "text" },
        "excerpt": { "type": "text" },
        "published": { "type": "boolean", "nullable": false, "default": "false" },
        "published_at": { "type": "timestamptz" },
        "created_at": { "type": "timestamptz", "nullable": false, "default": "now()" },
        "updated_at": { "type": "timestamptz", "nullable": false, "default": "now()" }
      },
      "indexes": {
        "idx_posts_author_id": { "columns": ["author_id"], "method": "btree" },
        "idx_posts_category_id": { "columns": ["category_id"], "method": "btree" },
        "idx_posts_slug": { "columns": ["slug"], "method": "btree", "unique": true },
        "idx_posts_published": { "columns": ["published", "published_at"], "method": "btree" }
      }
    },
    "comments": {
      "columns": {
        "id": { "type": "uuid", "primaryKey": true, "default": "gen_random_uuid()" },
        "post_id": { "type": "uuid", "nullable": false, "references": "posts.id" },
        "author_id": { "type": "uuid", "nullable": false, "references": "users.id" },
        "content": { "type": "text", "nullable": false },
        "created_at": { "type": "timestamptz", "nullable": false, "default": "now()" },
        "updated_at": { "type": "timestamptz", "nullable": false, "default": "now()" }
      },
      "indexes": {
        "idx_comments_post_id": { "columns": ["post_id"], "method": "btree" },
        "idx_comments_author_id": { "columns": ["author_id"], "method": "btree" }
      }
    }
  }
}

---

B) E-commerce

{
  "app_id": "YOUR_APP_ID",
  "action": "apply",
  "schema": {
    "products": {
      "columns": {
        "id": { "type": "uuid", "primaryKey": true, "default": "gen_random_uuid()" },
        "name": { "type": "text", "nullable": false },
        "description": { "type": "text" },
        "price": { "type": "integer", "nullable": false },
        "stock": { "type": "integer", "nullable": false, "default": "0" },
        "sku": { "type": "text", "unique": true },
        "metadata": { "type": "jsonb" },
        "created_at": { "type": "timestamptz", "nullable": false, "default": "now()" },
        "updated_at": { "type": "timestamptz", "nullable": false, "default": "now()" }
      },
      "indexes": {
        "idx_products_sku": { "columns": ["sku"], "method": "btree", "unique": true },
        "idx_products_metadata": { "columns": ["metadata"], "method": "gin", "opclass": "jsonb_path_ops" }
      }
    },
    "orders": {
      "columns": {
        "id": { "type": "uuid", "primaryKey": true, "default": "gen_random_uuid()" },
        "user_id": { "type": "uuid", "nullable": false, "references": "users.id" },
        "status": { "type": "text", "nullable": false, "default": "'pending'" },
        "total": { "type": "integer", "nullable": false, "default": "0" },
        "shipping_address": { "type": "jsonb" },
        "created_at": { "type": "timestamptz", "nullable": false, "default": "now()" },
        "updated_at": { "type": "timestamptz", "nullable": false, "default": "now()" }
      },
      "indexes": {
        "idx_orders_user_id": { "columns": ["user_id"], "method": "btree" },
        "idx_orders_status": { "columns": ["status"], "method": "btree" }
      }
    },
    "order_items": {
      "columns": {
        "id": { "type": "uuid", "primaryKey": true, "default": "gen_random_uuid()" },
        "order_id": { "type": "uuid", "nullable": false, "references": "orders.id" },
        "product_id": { "type": "uuid", "nullable": false, "references": "products.id" },
        "quantity": { "type": "integer", "nullable": false },
        "price": { "type": "integer", "nullable": false },
        "created_at": { "type": "timestamptz", "nullable": false, "default": "now()" },
        "updated_at": { "type": "timestamptz", "nullable": false, "default": "now()" }
      },
      "indexes": {
        "idx_order_items_order_id": { "columns": ["order_id"], "method": "btree" },
        "idx_order_items_product_id": { "columns": ["product_id"], "method": "btree" }
      }
    },
    "reviews": {
      "columns": {
        "id": { "type": "uuid", "primaryKey": true, "default": "gen_random_uuid()" },
        "product_id": { "type": "uuid", "nullable": false, "references": "products.id" },
        "user_id": { "type": "uuid", "nullable": false, "references": "users.id" },
        "rating": { "type": "integer", "nullable": false },
        "title": { "type": "text" },
        "body": { "type": "text" },
        "created_at": { "type": "timestamptz", "nullable": false, "default": "now()" },
        "updated_at": { "type": "timestamptz", "nullable": false, "default": "now()" }
      },
      "indexes": {
        "idx_reviews_product_id": { "columns": ["product_id"], "method": "btree" },
        "idx_reviews_user_id": { "columns": ["user_id"], "method": "btree" }
      }
    }
  }
}

---

C) SaaS Multi-tenant

{
  "app_id": "YOUR_APP_ID",
  "action": "apply",
  "schema": {
    "workspaces": {
      "columns": {
        "id": { "type": "uuid", "primaryKey": true, "default": "gen_random_uuid()" },
        "name": { "type": "text", "nullable": false },
        "slug": { "type": "text", "nullable": false, "unique": true },
        "plan": { "type": "text", "nullable": false, "default": "'free'" },
        "settings": { "type": "jsonb" },
        "created_at": { "type": "timestamptz", "nullable": false, "default": "now()" },
        "updated_at": { "type": "timestamptz", "nullable": false, "default": "now()" }
      },
      "indexes": {
        "idx_workspaces_slug": { "columns": ["slug"], "method": "btree", "unique": true }
      }
    },
    "members": {
      "columns": {
        "id": { "type": "uuid", "primaryKey": true, "default": "gen_random_uuid()" },
        "workspace_id": { "type": "uuid", "nullable": false, "references": "workspaces.id" },
        "user_id": { "type": "uuid", "nullable": false, "references": "users.id" },
        "role": { "type": "text", "nullable": false, "default": "'member'" },
        "created_at": { "type": "timestamptz", "nullable": false, "default": "now()" },
        "updated_at": { "type": "timestamptz", "nullable": false, "default": "now()" }
      },
      "indexes": {
        "idx_members_workspace_user": { "columns": ["workspace_id", "user_id"], "method": "btree", "unique": true },
        "idx_members_user_id": { "columns": ["user_id"], "method": "btree" }
      }
    },
    "projects": {
      "columns": {
        "id": { "type": "uuid", "primaryKey": true, "default": "gen_random_uuid()" },
        "workspace_id": { "type": "uuid", "nullable": false, "references": "workspaces.id" },
        "name": { "type": "text", "nullable": false },
        "description": { "type": "text" },
        "archived": { "type": "boolean", "nullable": false, "default": "false" },
        "created_at": { "type": "timestamptz", "nullable": false, "default": "now()" },
        "updated_at": { "type": "timestamptz", "nullable": false, "default": "now()" }
      },
      "indexes": {
        "idx_projects_workspace_id": { "columns": ["workspace_id"], "method": "btree" }
      }
    },
    "tasks": {
      "columns": {
        "id": { "type": "uuid", "primaryKey": true, "default": "gen_random_uuid()" },
        "project_id": { "type": "uuid", "nullable": false, "references": "projects

…

## Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

- **Author:** [butterbase-ai](https://github.com/butterbase-ai)
- **Source:** [butterbase-ai/butterbase-skills](https://github.com/butterbase-ai/butterbase-skills)
- **License:** MIT

Install and usage instructions live in the source repository linked above.