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:

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

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:

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

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

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.