Agent skill

Schema Design

by butterbase-ai in butterbase-ai/butterbase-skills

A skill your agent uses when designing database schemas, creating or modifying tables, choosing column types, adding indexes, or working with the Butterbase declarative schema DSL

MITAuto-check passedDatabases

Install Schema Design

skills CLI

$ npx skills add butterbase-ai/butterbase-skills --skill schema-design -a claude-code

Project install by default; add -g for ~/.claude/skills/.

GitHub CLI

$ gh skill install butterbase-ai/butterbase-skills schema-design --agent claude-code

Project scope by default; add --scope user for a personal install. Needs GitHub CLI 2.90.0 or later (public preview).

Manual copy

$ git clone --depth 1 https://github.com/butterbase-ai/butterbase-skills.git skills-src && mkdir -p .claude/skills && cp -r skills-src/skills/schema-design .claude/skills/schema-design && rm -rf skills-src

Use ~/.claude/skills/ instead of .claude/skills for a personal install. The folder must contain SKILL.md.

Claude Code skills documentation · loads skills from .claude/skills/

Paste into Claude Code

Install the "schema-design" agent skill from https://github.com/butterbase-ai/butterbase-skills/tree/main/skills/schema-design into .claude/skills/schema-design/ in this project. Copy the whole folder (SKILL.md and every file beside it), keep the folder name "schema-design", then confirm the skill loads.

Claude Code copies the folder itself, the same result as the manual copy. Check what it changed before you commit it.

$skill-installer

$skill-installer install https://github.com/butterbase-ai/butterbase-skills/tree/main/skills/schema-design

Type this inside Codex. $skill-installer <name> installs a curated skill from openai/skills. The installer writes to $CODEX_HOME/skills (default ~/.codex/skills). Restart Codex if the skill does not show up.

skills CLI

$ npx skills add butterbase-ai/butterbase-skills --skill schema-design -a codex

Project install goes to .agents/skills/; add -g for ~/.codex/skills/.

GitHub CLI

$ gh skill install butterbase-ai/butterbase-skills schema-design --agent codex

Project scope by default (.agents/skills/); add --scope user for a personal install.

Manual copy

$ git clone --depth 1 https://github.com/butterbase-ai/butterbase-skills.git skills-src && mkdir -p .agents/skills && cp -r skills-src/skills/schema-design .agents/skills/schema-design && rm -rf skills-src

Use ~/.agents/skills/ instead of .agents/skills for a personal install.

Codex skills documentation · loads skills from .agents/skills/

Paste into Codex

Install the "schema-design" agent skill from https://github.com/butterbase-ai/butterbase-skills/tree/main/skills/schema-design into .agents/skills/schema-design/ in this project. Copy the whole folder (SKILL.md and every file beside it), keep the folder name "schema-design", then confirm the skill loads.

Codex copies the folder itself, the same result as the manual copy. Check what it changed before you commit it.

skills CLI

$ npx skills add butterbase-ai/butterbase-skills --skill schema-design -a cursor

Project install goes to .agents/skills/; add -g for ~/.cursor/skills/.

GitHub CLI

$ gh skill install butterbase-ai/butterbase-skills schema-design --agent cursor

Project scope by default (.agents/skills/); add --scope user for a personal install.

Manual copy

$ git clone --depth 1 https://github.com/butterbase-ai/butterbase-skills.git skills-src && mkdir -p .cursor/skills && cp -r skills-src/skills/schema-design .cursor/skills/schema-design && rm -rf skills-src

Use ~/.cursor/skills/ instead of .cursor/skills for a personal install.

Cursor skills documentation · loads skills from .cursor/skills/, .agents/skills/, .claude/skills/, .codex/skills/

Paste into Cursor

Install the "schema-design" agent skill from https://github.com/butterbase-ai/butterbase-skills/tree/main/skills/schema-design into .cursor/skills/schema-design/ in this project. Copy the whole folder (SKILL.md and every file beside it), keep the folder name "schema-design", then confirm the skill loads.

Cursor copies the folder itself, the same result as the manual copy. Check what it changed before you commit it.

gemini skills install

$ gemini skills install https://github.com/butterbase-ai/butterbase-skills.git --path skills/schema-design

--scope user (default) or --scope workspace; --path is the subfolder of the repo that holds the skill; --consent skips the security confirmation prompt.

skills CLI

$ npx skills add butterbase-ai/butterbase-skills --skill schema-design -a gemini-cli

Project install goes to .agents/skills/; add -g for ~/.gemini/skills/.

GitHub CLI

$ gh skill install butterbase-ai/butterbase-skills schema-design --agent gemini-cli

Project scope by default (.agents/skills/); add --scope user for a personal install.

Manual copy

$ git clone --depth 1 https://github.com/butterbase-ai/butterbase-skills.git skills-src && mkdir -p .gemini/skills && cp -r skills-src/skills/schema-design .gemini/skills/schema-design && rm -rf skills-src

Use ~/.gemini/skills/ instead of .gemini/skills for a personal install, then run /skills reload.

Gemini CLI skills documentation · loads skills from .gemini/skills/, .agents/skills/

Paste into Gemini CLI

Install the "schema-design" agent skill from https://github.com/butterbase-ai/butterbase-skills/tree/main/skills/schema-design into .gemini/skills/schema-design/ in this project. Copy the whole folder (SKILL.md and every file beside it), keep the folder name "schema-design", then confirm the skill loads.

Gemini CLI copies the folder itself, the same result as the manual copy. Check what it changed before you commit it.

GitHub CLI

$ gh skill install butterbase-ai/butterbase-skills schema-design

Installs for Copilot at project scope by default; add --scope user for a personal install. Preview a skill first with gh skill preview. Needs GitHub CLI 2.90.0 or later (public preview).

skills CLI

$ npx skills add butterbase-ai/butterbase-skills --skill schema-design -a github-copilot

Project install goes to .agents/skills/; add -g for ~/.copilot/skills/.

Manual copy

$ git clone --depth 1 https://github.com/butterbase-ai/butterbase-skills.git skills-src && mkdir -p .github/skills && cp -r skills-src/skills/schema-design .github/skills/schema-design && rm -rf skills-src

Use ~/.copilot/skills/ instead of .github/skills for a personal install. Commit .github/skills so cloud agent and code review can use it.

GitHub Copilot skills documentation · loads skills from .github/skills/, .claude/skills/, .agents/skills/

Paste into GitHub Copilot

Install the "schema-design" agent skill from https://github.com/butterbase-ai/butterbase-skills/tree/main/skills/schema-design into .github/skills/schema-design/ in this project. Copy the whole folder (SKILL.md and every file beside it), keep the folder name "schema-design", then confirm the skill loads.

GitHub Copilot copies the folder itself, the same result as the manual copy. Check what it changed before you commit it.

skills CLI

$ npx skills add butterbase-ai/butterbase-skills --skill schema-design -a opencode

OpenCode documents no install command of its own. Project install goes to .agents/skills/; add -g for ~/.config/opencode/skills/.

GitHub CLI

$ gh skill install butterbase-ai/butterbase-skills schema-design --agent opencode

Project scope by default (.agents/skills/); add --scope user for a personal install.

Manual copy

$ git clone --depth 1 https://github.com/butterbase-ai/butterbase-skills.git skills-src && mkdir -p .opencode/skills && cp -r skills-src/skills/schema-design .opencode/skills/schema-design && rm -rf skills-src

Use ~/.config/opencode/skills/ instead of .opencode/skills for a personal install.

OpenCode skills documentation · loads skills from .opencode/skills/, .claude/skills/, .agents/skills/

Paste into OpenCode

Install the "schema-design" agent skill from https://github.com/butterbase-ai/butterbase-skills/tree/main/skills/schema-design into .opencode/skills/schema-design/ in this project. Copy the whole folder (SKILL.md and every file beside it), keep the folder name "schema-design", then confirm the skill loads.

OpenCode copies the folder itself, the same result as the manual copy. Check what it changed before you commit it.

Facts

Skill name: schema-design
GitHub stars: 534
Token cost: ~5.4k tokens
SKILL.md length: 832 words
Files: 1
Skills in repo: 39
Repo updated: Oct 5, 2026
First seen: Oct 7, 2026
Licence: MIT

At a glance

A skill your agent uses when designing database schemas, creating or modifying tables, choosing column types, adding indexes, or working with the Butterbase declarative schema DSL

Works in 8 steps: Overview → Column Types Reference → Column Properties → …
Designing database schemas
SKILL.md covers 1. Overview, 2. Column Types Reference, 3. Column Properties and 4. Standard Base Pattern, plus 4 more sections
Instructions only: no scripts, shell commands, URLs or credentials in SKILL.md

What it does

Schema Design is an agent skill from butterbase-ai/butterbase-skills. Use when designing database schemas, creating or modifying tables, choosing column types, adding indexes, or working with the Butterbase declarative schema DSL

Its SKILL.md is about 5.4k tokens, which your agent loads only when the skill is triggered. It is a single SKILL.md file with no bundled scripts.

It sits in Databases, covering Database schema design. The repository describes itself as: Plugin for Butterbase.ai. The licence is MIT.

When your agent uses it

Designing database schemas
Modifying tables
Choosing column types
Working with the Butterbase declarative schema DSL

Example prompts

“/schema-design”

Workflow steps

8 steps, taken from the step headings in SKILL.md.

Overview
Column Types Reference
Column Properties
Standard Base Pattern
Index Types
Using manage_schema
Complete Patterns
Anti-Patterns

What it can do on your machine

Read from SKILL.md and the folder at commit aa8ae69. It shows what the files ask for, not the result of running them.

Tool permissions
Pre-approves nothing: there is no allowed-tools line, so your agent's usual permission prompts apply.
From allowed-tools in the SKILL.md frontmatter.
Runs code
No scripts in the folder and no shell commands in SKILL.md (its code samples are json and javascript).
From the folder's file list and the shell code blocks in SKILL.md.
Network
No URLs in SKILL.md.
From URLs in SKILL.md, links to its own repository left out.
Credentials
Names no API keys, tokens, secrets or passwords.
From names ending in _API_KEY, _TOKEN, _SECRET, _KEY or _PASSWORD in SKILL.md.

Context cost

Schema Design loads about 5.4k tokens when it runs. Until then it costs about 43 tokens; SKILL.md has 832 words of instructions outside code blocks.

Always · name and description, kept in context so the agent knows when to use it: ~43
When it runs · the whole SKILL.md, loaded when a task matches: ~5.4k

Estimates: characters ÷ 4, the usual rule of thumb; real counts depend on the model's tokenizer. Scripts and assets cost tokens only if the agent reads them.

Safety

Auto-check passed

The automated check found no risky patterns in SKILL.md.

Automated static check — not a guarantee. Review scripts before installing. It scans the text of SKILL.md for risky patterns (piping downloads into a shell, reading credential files, hidden Unicode, destructive commands); files beside SKILL.md are not scanned.

SKILL.md

The full file from butterbase-ai/butterbase-skills at commit aa8ae69, republished under its MIT licence (© butterbase-ai). 832 words, ~5,357 tokens.

Download SKILL.mdSave it as .claude/skills/schema-design/SKILL.md (or your agent's skills folder).

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

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:

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.

Show full SKILL.md (339 more words)Show less

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.

json

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

Dropping columns from a specific table:

json

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

json

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

json

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

json

{
  "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" }
      }
    }
  }
}

json

{
  "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.

Files

Just SKILL.md in skills/schema-design of butterbase-ai/butterbase-skills.

Open the folder on GitHubat commit aa8ae69

Compare with similar skills

Schema Design next to the 5 skills that share the most tags, products or categories with it. Stars are the repository's; “used in” counts other GitHub owners with a copy.

Schema Design compared with similar skills
Skill	Stars	Used in	Tokens	Auto-check	Licence	Repo updated
Schema Design this skillbutterbase-ai/butterbase-skills	534	—	~5.4k	Automated safety check: Pass	MIT	4 days ago
SQL Optimization Patternsynulihao/AgentSkillOS	617	11 repos	~3.3k	Automated safety check: Pass	None	7 mo ago
Datamodellmnimbalyst/nimbalyst	1.9k	—	~713	Automated safety check: Pass	MIT	today
Add Mpk Taskmirage-project/mirage	2.5k	—	~4.5k	Automated safety check: Pass	Apache-2.0	yesterday
B200 Flash Attention4 Plannermirage-project/mirage	2.5k	—	~1.9k	Automated safety check: Pass	Apache-2.0	yesterday
Experiment Auditwanshuiyin/Auto-claude-code-research-in-sleep	17k	1 repos	~2.7k	Automated safety check: Notes	MIT	2 days ago

Similar skills

SQL Optimization Patterns
ynulihao/AgentSkillOS
Master SQL query optimization, indexing strategies, and EXPLAIN analysis to dramatically improve database performance and eliminate slow queries.
617 GitHub starsUsed in 11 repos~3.3k tokens
DatabasesAuto-check passed
Datamodellm
nimbalyst/nimbalyst
Create visual data models for database schemas using Nimbalyst's DataModelLM editor.
1.9k GitHub stars~713 tokensUpdated today
DatabasesAuto-check passed
Add Mpk Task
mirage-project/mirage
Step-by-step guide for adding a new task implementation to Mirage Persistent Kernel (MPK).
2.5k GitHub stars~4.5k tokensUpdated yesterday
DatabasesAuto-check passed
B200 Flash Attention4 Planner
mirage-project/mirage
A skill your agent uses when the user wants to design or extend a FlashAttention-style forward kernel on B200/Blackwell, involving the two MMAs QKᵀ and PV, online softmax, S/P/O in TMEM, warp roles…
2.5k GitHub stars~1.9k tokensUpdated yesterday
DatabasesAuto-check passed
Experiment Audit
wanshuiyin/Auto-claude-code-research-in-sleep
Audit experiment integrity before claiming results. An agent skill from wanshuiyin/Auto-claude-code-research-in-sleep.
17k GitHub starsUsed in 1 repo~2.7k tokens
DatabasesAuto-check: notes
Sqlite Schema Design
fastrepl/anarlog
Design or review schemas for crates/cloudsync using SQLite Sync constraints, not generic SQLite advice.
9.5k GitHub stars~1.9k tokensUpdated today
DatabasesAuto-check passed

More from butterbase-ai/butterbase-skills

All 39 skills in this repo

AI
butterbase-ai/butterbase-skills
A skill your agent uses when calling the app's AI gateway from agent tools — chat completions, embeddings, listing models, configuring defaults or BYOK, reading token/cost usage
534 GitHub stars~1.1k tokensUpdated 4 days ago
Auto-check passed
Auth Setup
butterbase-ai/butterbase-skills
A skill your agent uses when configuring OAuth providers (Google/GitHub/Apple/X/etc.), setting up post-login auth hooks, tuning JWT lifetimes, or generating service API keys
534 GitHub stars~2.2k tokensUpdated 4 days ago
Auto-check passed
Build App
butterbase-ai/butterbase-skills
A skill your agent uses when building a new Butterbase app from scratch, creating a full-stack application, or when the user asks to set up a complete backend with database, auth, and deployment
534 GitHub stars~4.9k tokensUpdated 4 days ago
Auto-check passed
Contributing
butterbase-ai/butterbase-skills
A skill your agent uses when contributing to the Butterbase codebase, adding new MCP tools, creating API routes, writing migrations, or understanding the monorepo architecture
534 GitHub stars~1.5k tokensUpdated 4 days ago
Auto-check passed
Debug Rls
butterbase-ai/butterbase-skills
A skill your agent uses when users report access denied errors, see wrong data, RLS policies are not working, or when troubleshooting Row-Level Security issues in Butterbase
534 GitHub stars~3.5k tokensUpdated 4 days ago
Auto-check passed
Deploy Frontend
butterbase-ai/butterbase-skills
A skill your agent uses when deploying a frontend (React, Next.js, or static HTML) to a live URL on Butterbase, or when troubleshooting deployment issues like MIME type errors or blank pages
534 GitHub stars~2.8k tokensUpdated 4 days ago
Auto-check passed

Questions about Schema Design

What does Schema Design do?

A skill your agent uses when designing database schemas, creating or modifying tables, choosing column types, adding indexes, or working with the Butterbase declarative schema DSL. Schema Design is an agent skill from butterbase-ai/butterbase-skills.

When should I use Schema Design?

Schema Design fits situations like: designing database schemas; modifying tables; choosing column types; working with the Butterbase declarative schema DSL.

How do I install Schema Design in Claude Code?

Run `npx skills add butterbase-ai/butterbase-skills --skill schema-design -a claude-code`. Or copy the skill folder (skills/schema-design in butterbase-ai/butterbase-skills) into .claude/skills/schema-design in your project. Claude Code loads it when a task matches its description.

How do I install Schema Design in Codex?

Run `npx skills add butterbase-ai/butterbase-skills --skill schema-design -a codex`. Or copy the skill folder (skills/schema-design in butterbase-ai/butterbase-skills) into .agents/skills/schema-design in your project. Codex loads it when a task matches its description.

Can I use Schema Design in Cursor, Gemini CLI or GitHub Copilot?

Cursor, Gemini CLI, GitHub Copilot and OpenCode also load SKILL.md folders. With the skills CLI, run `npx skills add butterbase-ai/butterbase-skills --skill schema-design -a cursor` (or -a gemini-cli, github-copilot or opencode for the others). To copy it by hand, put the folder in .cursor/skills/schema-design, .gemini/skills/schema-design, .github/skills/schema-design and .opencode/skills/schema-design in your project.

What does Schema Design need to run?

SKILL.md names no scripts, command-line tools or credentials: Schema Design is instructions for the agent only.

Does Schema Design access the network?

SKILL.md contains no URLs. Any network use would come from the scripts or tools the agent runs. This is read from the text; nothing was executed.

Is Schema Design safe to install?

Our automated static check of SKILL.md found no risky patterns, such as piping downloads into a shell, reading credential files or hidden Unicode. It is not a guarantee. Review the folder before installing.

What licence does Schema Design use?

Schema Design is published under the MIT licence (the repository's licence). It allows redistribution, so the full SKILL.md is shown on this page.

How many tokens does Schema Design use?

About 5.4k tokens (SKILL.md is roughly 21k characters). Agents keep only the skill's name and description in context until a task matches; then they load SKILL.md in full.

What are the alternatives to Schema Design?

Skills that share tags, products or a category with Schema Design: SQL Optimization Patterns (ynulihao/AgentSkillOS, 617 stars), Datamodellm (nimbalyst/nimbalyst, 1.9k stars), Add Mpk Task (mirage-project/mirage, 2.5k stars) and B200 Flash Attention4 Planner (mirage-project/mirage, 2.5k stars). The comparison table on this page puts their stars, adoption, token cost, safety result and licence side by side.

Who maintains Schema Design?

butterbase-ai (a GitHub organization) maintains it in butterbase-ai/butterbase-skills, which has 534 GitHub stars. The repository holds 39 skills in this directory. The repository was last updated on October 5, 2026.

Source: butterbase-ai/butterbase-skills on GitHub. Facts on this page come from the repository at the commit we read; the author's words are quoted as theirs.

Install Schema Design

Facts

At a glance

What it does

When your agent uses it

Example prompts

Workflow steps

What it can do on your machine

Tool permissions

Runs code

Network

Credentials

Context cost

Safety

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

Files

Compare with similar skills

Similar skills

More from butterbase-ai/butterbase-skills

Related

Questions about Schema Design

6. Using `manage_schema`