schema-design

This is a reference guide for Butterbase's declarative schema DSL, where you describe your desired database state and the platform diffs...

butterbase-ai/butterbase-skills·0installs532starsDatabases

SKILL.md

Full skill instructions

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.id" },
        "assignee_id": { "type": "uuid", "references": "users.id" },
        "title": { "type": "text", "nullable": false },
        "description": { "type": "text" },
        "status": { "type": "text", "nullable": false, "default": "'todo'" },
        "priority": { "type": "text", "nullable": false, "default": "'medium'" },
        "due_date": { "type": "timestamptz" },
        "created_at": { "type": "timestamptz", "nullable": false, "default": "now()" },
        "updated_at": { "type": "timestamptz", "nullable": false, "default": "now()" }
      },
      "indexes": {
        "idx_tasks_project_id": { "columns": ["project_id"], "method": "btree" },
        "idx_tasks_assignee_id": { "columns": ["assignee_id"], "method": "btree" },
        "idx_tasks_status": { "columns": ["status"], "method": "btree" }
      }
    }
  }
}

D) Social App

{
  "app_id": "YOUR_APP_ID",
  "action": "apply",
  "schema": {
    "profiles": {
      "columns": {
        "id": { "type": "uuid", "primaryKey": true, "default": "gen_random_uuid()" },
        "user_id": { "type": "uuid", "nullable": false, "unique": true, "references": "users.id" },
        "username": { "type": "text", "nullable": false, "unique": true },
        "bio": { "type": "text" },
        "avatar_id": { "type": "uuid" },
        "created_at": { "type": "timestamptz", "nullable": false, "default": "now()" },
        "updated_at": { "type": "timestamptz", "nullable": false, "default": "now()" }
      },
      "indexes": {
        "idx_profiles_username": { "columns": ["username"], "method": "btree", "unique": true },
        "idx_profiles_user_id": { "columns": ["user_id"], "method": "btree", "unique": true }
      }
    },
    "posts": {
      "columns": {
        "id": { "type": "uuid", "primaryKey": true, "default": "gen_random_uuid()" },
        "author_id": { "type": "uuid", "nullable": false, "references": "users.id" },
        "content": { "type": "text", "nullable": false },
        "media": { "type": "jsonb" },
        "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_created_at": { "columns": ["created_at"], "method": "btree" }
      }
    },
    "follows": {
      "columns": {
        "id": { "type": "uuid", "primaryKey": true, "default": "gen_random_uuid()" },
        "follower_id": { "type": "uuid", "nullable": false, "references": "users.id" },
        "following_id": { "type": "uuid", "nullable": false, "references": "users.id" },
        "created_at": { "type": "timestamptz", "nullable": false, "default": "now()" }
      },
      "indexes": {
        "idx_follows_follower_following": { "columns": ["follower_id", "following_id"], "method": "btree", "unique": true },
        "idx_follows_following_id": { "columns": ["following_id"], "method": "btree" }
      }
    },
    "likes": {
      "columns": {
        "id": { "type": "uuid", "primaryKey": true, "default": "gen_random_uuid()" },
        "user_id": { "type": "uuid", "nullable": false, "references": "users.id" },
        "post_id": { "type": "uuid", "nullable": false, "references": "posts.id" },
        "created_at": { "type": "timestamptz", "nullable": false, "default": "now()" }
      },
      "indexes": {
        "idx_likes_user_post": { "columns": ["user_id", "post_id"], "method": "btree", "unique": true },
        "idx_likes_post_id": { "columns": ["post_id"], "method": "btree" }
      }
    }
  }
}

8. Anti-Patterns

Using timestamp instead of timestamptz — timestamp silently drops timezone info, causing subtle bugs for users in different time zones. Always use timestamptz.
Forgetting "default": "gen_random_uuid()" on UUID primary keys — Without a default, inserts will fail unless the caller explicitly provides an ID. Always set this default.
Omitting created_at / updated_at columns — These are essential for debugging, ordering, and audit trails. Include them on every table from the start; retrofitting them is painful.
Not adding a user_id column on tables that will need RLS — Row-Level Security policies require a user_id column to exist. Adding it later requires a migration and backfill. Plan ahead.
Over-indexing — Indexes speed up reads but add overhead to every write. Only index columns you actively query or sort by. Avoid indexing every column "just in case."
Using text for booleans or enums — Use the boolean type for true/false values. For enums, text is acceptable but consider adding a CHECK constraint or a lookup table for referential integrity.
Storing file URLs directly — URLs change (CDN migrations, domain changes). Store the object's UUID (avatar_id uuid) and resolve the URL at render time using generate_download_url. This decouples your data from your storage topology.

If a docs/butterbase/00-state.md exists in the working directory, prefer invoking via /butterbase-skills:journey-schema so the journey orchestrator stays in sync.

Install skill

Copy and run in your terminal

npx -y skills add https://github.com/butterbase-ai/butterbase-skills --skill schema-design

View on GitHub

schema-design

schema-design

SKILL.md

Schema Design Skill

1. Overview

2. Column Types Reference

3. Column Properties

Foreign keys — short or long form

Default expressions

4. Standard Base Pattern

5. Index Types

Index definition format

Composite indexes

6. Using manage_schema

Creating new tables

Adding columns to existing tables

Destructive operations

Preview before commit

Common errors

7. Complete Patterns

A) Blog / CMS

B) E-commerce

C) SaaS Multi-tenant

D) Social App

8. Anti-Patterns

6. Using `manage_schema`