schema-design
starsforks
How to install schema-design?
butterbase-ai/butterbase-skills/schema-design
$npx -y skills add butterbase-ai/butterbase-skills --skill schema-designUse when designing database schemas, creating or modifying tables, choosing column types, adding indexes, or working with the Butterbase declarative schema DSL
SKILL.md
| 1 | # Schema Design Skill |
| 2 | |
| 3 | Reference guide for Butterbase's declarative schema DSL. Covers column types, constraints, indexes, and common data modeling patterns. |
| 4 | |
| 5 | --- |
| 6 | |
| 7 | ## 1. Overview |
| 8 | |
| 9 | 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. |
| 10 | |
| 11 | The single `manage_schema` tool exposes four actions: |
| 12 | |
| 13 | | Action | Purpose | |
| 14 | |--------|---------| |
| 15 | | `"get"` | Read the current schema | |
| 16 | | `"dry_run"` | Preview SQL that `apply` would execute, without running it | |
| 17 | | `"apply"` | Apply a declarative schema (diffs against current, runs safe DDL) | |
| 18 | | `"list_migrations"` | List applied migrations, most recent first | |
| 19 | |
| 20 | Key principles: |
| 21 | - **Idempotent**: applying the same schema twice is safe — returns "Schema is up to date" if no changes needed |
| 22 | - **Additive by default**: new columns and tables are created automatically |
| 23 | - **Explicit drops**: destructive operations require opt-in via `_drop` / `_dropColumns` |
| 24 | - **Preview first**: use `action: "dry_run"` to see what will change before committing |
| 25 | - **Transactional**: each migration runs in a single transaction — all changes commit or all roll back |
| 26 | |
| 27 | --- |
| 28 | |
| 29 | ## 2. Column Types Reference |
| 30 | |
| 31 | | Type | PostgreSQL | Use case | |
| 32 | |------|-----------|----------| |
| 33 | | `uuid` | UUID | Primary keys, foreign keys | |
| 34 | | `text` | TEXT | Strings of any length | |
| 35 | | `integer` | INTEGER | Whole numbers (-2B to 2B) | |
| 36 | | `bigint` | BIGINT | Large whole numbers | |
| 37 | | `boolean` | BOOLEAN | True/false flags | |
| 38 | | `timestamptz` | TIMESTAMPTZ | Dates with timezone | |
| 39 | | `jsonb` | JSONB | Structured/semi-structured data | |
| 40 | | `real` | REAL | 32-bit floating point | |
| 41 | | `double precision` | DOUBLE PRECISION | 64-bit floating point | |
| 42 | | `vector(N)` | VECTOR(N) | Embeddings (pgvector); e.g. `vector(1536)` for OpenAI | |
| 43 | |
| 44 | > **Always use `timestamptz` instead of `timestamp`.** `timestamp` silently drops timezone info and causes subtle bugs with users in different time zones. |
| 45 | |
| 46 | --- |
| 47 | |
| 48 | ## 3. Column Properties |
| 49 | |
| 50 | Each column is an object with the following properties: |
| 51 | |
| 52 | | Property | Type | Required | Default | Description | |
| 53 | |----------|------|----------|---------|-------------| |
| 54 | | `type` | string | ✅ yes | — | Column data type (see §2) | |
| 55 | | `primaryKey` | boolean | no | false | Mark as primary key | |
| 56 | | `nullable` | boolean | no | true | Allow NULL values | |
| 57 | | `default` | string | no | — | SQL expression for default value | |
| 58 | | `unique` | boolean | no | false | Add unique constraint | |
| 59 | | `references` | string \| object | no | — | Foreign key target (see below) | |
| 60 | |
| 61 | ### Foreign keys — short or long form |
| 62 | |
| 63 | Short form (just the target): |
| 64 | |
| 65 | ```json |
| 66 | "author_id": { "type": "uuid", "nullable": false, "references": "users.id" } |
| 67 | ``` |
| 68 | |
| 69 | Long form (with cascade behavior): |
| 70 | |
| 71 | ```json |
| 72 | "author_id": { |
| 73 | "type": "uuid", |
| 74 | "nullable": false, |
| 75 | "references": { |
| 76 | "table": "users", |
| 77 | "column": "id", |
| 78 | "onDelete": "CASCADE", |
| 79 | "onUpdate": "NO ACTION" |
| 80 | } |
| 81 | } |
| 82 | ``` |
| 83 | |
| 84 | `onDelete` / `onUpdate` accept `CASCADE | SET NULL | SET DEFAULT | RESTRICT | NO ACTION` (default `NO ACTION`). |
| 85 | |
| 86 | ### Default expressions |
| 87 | |
| 88 | Pass SQL expressions as strings: |
| 89 | |
| 90 | ```json |
| 91 | "default": "gen_random_uuid()" // UUID primary keys |
| 92 | "default": "now()" // Timestamps |
| 93 | "default": "false" // Booleans |
| 94 | "default": "0" // Integers |
| 95 | "default": "'draft'" // String literals (single-quoted) |
| 96 | ``` |
| 97 | |
| 98 | --- |
| 99 | |
| 100 | ## 4. Standard Base Pattern |
| 101 | |
| 102 | Every table should include these base columns: |
| 103 | |
| 104 | ```json |
| 105 | { |
| 106 | "id": { "type": "uuid", "primaryKey": true, "default": "gen_random_uuid()" }, |
| 107 | "created_at": { "type": "timestamptz", "nullable": false, "default": "now()" }, |
| 108 | "updated_at": { "type": "timestamptz", "nullable": false, "default": "now()" } |
| 109 | } |
| 110 | ``` |
| 111 | |
| 112 | If your app uses Row-Level Security (RLS), also add: |
| 113 | |
| 114 | ```json |
| 115 | "user_id": { "type": "uuid", "nullable": false, "references": "users.id" } |
| 116 | ``` |
| 117 | |
| 118 | > Tables without `user_id` cannot have per-user RLS policies applied later without a migration. |
| 119 | |
| 120 | --- |
| 121 | |
| 122 | ## 5. Index Types |
| 123 | |
| 124 | | `method` | Use case | Example opclass | |
| 125 | |----------|----------|----------------| |
| 126 | | `btree` | Default, range queries, sorting | — | |
| 127 | | `hash` | Exact-match lookups | — | |
| 128 | | `gin` | Full-text search on JSONB, arrays | `jsonb_path_ops` | |
| 129 | | `gist` | Geometric/spatial data | — | |
| 130 | | `hnsw` | Vector similarity (pgvector) | `vector_cosine_ops` | |
| 131 | | `ivfflat` | Vector similarity (large datasets) | `vector_cosine_ops` | |
| 132 | |
| 133 | ### Index definition format |
| 134 | |
| 135 | Indexes are defined per-table under the `indexes` key: |
| 136 | |
| 137 | ```json |
| 138 | { |
| 139 | "indexes": { |
| 140 | "idx_posts_author": { |
| 141 | "columns": ["author_id"], |
| 142 | "method": "btree" |
| 143 | }, |
| 144 | "idx_posts_embedding": { |
People who liked this also viewed
6 picksType
- web-design-guidelinesReview UI code for Web Interface Guidelines compliance. Use when asked to "review my UI", "check accessibility", "audit design", "review UX", or "check my site against best practices".SkillsJul 2026
- lark-slides飞书幻灯片：创建和编辑幻灯片。创建演示文稿、读取幻灯片内容、管理幻灯片页面（创建、删除、读取、局部替换）。当用户需要创建或编辑幻灯片、读取或修改单个页面时使用。当用户给出 doubao.com 的 /slides/ URL/token 时，也应直接使用本 skill，不要因为域名不是飞书而回退到 WebFetch；路由依据是 URL 路径模式和 token，而不是域名。不负责：云文档内容编辑（走 SkillsJul 2026
- design-taste-frontendTaste-Skill - gives your AI good taste. stops the AI from generating boring, generic slop SkillsJul 2026
- ui-ux-pro-maxUI/UX design intelligence for web and mobile. Searchable local database with 84 styles, 192 color palettes, 74 font pairings, 192 product types, 98 UX guidelines, 104 icon entries, 16 GSAP motion presSkillsJul 2026
- caveman-helpQuick-reference card for all caveman modes, skills, and commands. One-shot display, not a persistent mode. Trigger: /caveman-help, "caveman help", "what caveman commands", "how do I use caveman".SkillsJul 2026
- codebase-designShared vocabulary for designing deep modules. Use when the user wants to design or improve a module's interface, find deepening opportunities, decide where a seam goes, make code more testable or AI-nSkillsJul 2026
