name: vespertide description: Define database schemas in JSON and generate migration plans. Use this skill when creating or modifying database models, defining tables with columns, constraints, and ENUM types for Vespertide-based projects.

Vespertide Database Schema Definition

Declarative database schema management. Define tables in JSON, generate typed migrations and SQL.

CRITICAL: Always validate your model against the JSON Schema before committing. Use $schema in every model file for IDE validation.

Schema Validation (MANDATORY)

Every model file MUST include the $schema field:

{
  "$schema": "https://raw.githubusercontent.com/dev-five-git/vespertide/refs/heads/main/schemas/model.schema.json",
  "name": "table_name",
  "columns": []
}

Before saving any model:

Ensure $schema URL is present
Verify IDE shows no validation errors
Run vespertide diff to check for parsing errors

The schema URL provides:

Real-time validation in VS Code, WebStorm, etc.
Autocompletion for all fields
Type checking for column types and constraints

Installation

cargo install vespertide-cli

CLI Commands

Command	Description
`vespertide init`	Initialize project with `vespertide.json`
`vespertide new <name>`	Create model template with `$schema`
`vespertide diff`	Show pending changes
`vespertide sql`	Preview SQL for next migration
`vespertide sql --backend mysql`	SQL for specific backend (postgres/mysql/sqlite)
`vespertide revision -m "msg"`	Create migration file
`vespertide status`	Show project status
`vespertide log`	List applied migrations
`vespertide export --orm seaorm`	Export to ORM code

Migration Files (DO NOT EDIT)

CRITICAL: Migration files are AUTO-GENERATED. Never create or modify them manually.

Rules

Always use vespertide revision -m "message" to create migrations
Never manually create migration JSON files
Never manually edit migration JSON files
Only exception: Adding fill_with values when prompted

When `fill_with` is Required

When adding a NOT NULL column to an existing table without a default value, the CLI will prompt for a fill_with value. This is the ONLY case where you may need to edit the migration:

{
  "type": "add_column",
  "table": "user",
  "column": {
    "name": "status",
    "type": "text",
    "nullable": false
  },
  "fill_with": "'active'"
}

The fill_with value is used to backfill existing rows during migration.

Workflow

# 1. Edit your model files (models/*.json)
# 2. Check what changed
vespertide diff

# 3. Preview SQL
vespertide sql

# 4. Create migration (auto-generated)
vespertide revision -m "add status column"

# 5. If prompted for fill_with, provide a value
# 6. Never touch migration files after this

Model Structure

{
  "$schema": "https://raw.githubusercontent.com/dev-five-git/vespertide/refs/heads/main/schemas/model.schema.json",
  "name": "table_name",
  "description": "Optional table description",
  "columns": [ /* ColumnDef[] */ ]
}

Note: constraints field is optional. Only add it when you need CHECK constraints.

Required Fields

Field	Type	Description
`name`	string	Table name (snake_case)
`columns`	array	Column definitions

Optional Fields

Field	Type	Description
`description`	string	Table documentation
`constraints`	array	Table-level constraints (only for CHECK)

Column Definition

Required Fields

{
  "name": "column_name",
  "type": "ColumnType",
  "nullable": false
}

Optional Fields

Field	Type	Description
`default`	string \| boolean \| number	Default value
`comment`	string	Column documentation
`primary_key`	boolean \| object	Inline primary key
`unique`	boolean \| string \| string[]	Inline unique constraint
`index`	boolean \| string \| string[]	Inline index
`foreign_key`	string \| object	Inline foreign key

Column Types

Simple Types (string values)

Type	SQL	Use Case
`"small_int"`	SMALLINT	Small integers (-32768 to 32767)
`"integer"`	INTEGER	IDs, counts, standard integers
`"big_int"`	BIGINT	Large numbers, timestamps as int
`"real"`	REAL	Single precision float
`"double_precision"`	DOUBLE PRECISION	Double precision float
`"text"`	TEXT	Variable-length strings
`"boolean"`	BOOLEAN	True/false flags
`"date"`	DATE	Date only (no time)
`"time"`	TIME	Time only (no date)
`"timestamp"`	TIMESTAMP	Date/time without timezone
`"timestamptz"`	TIMESTAMPTZ	Date/time with timezone
`"interval"`	INTERVAL	Time duration
`"bytea"`	BYTEA	Binary data
`"uuid"`	UUID	UUIDs
`"json"`	JSON	JSON data
`"jsonb"`	JSONB	Binary JSON (indexable, recommended)
`"inet"`	INET	IPv4/IPv6 address
`"cidr"`	CIDR	Network address
`"macaddr"`	MACADDR	MAC address
`"xml"`	XML	XML data

Complex Types (object values)

VARCHAR (variable-length string with limit)

{ "kind": "varchar", "length": 255 }

CHAR (fixed-length string)

{ "kind": "char", "length": 2 }

NUMERIC/DECIMAL (exact precision)

{ "kind": "numeric", "precision": 10, "scale": 2 }

ENUM (STRONGLY RECOMMENDED)

Use enums instead of text columns with CHECK constraints for status fields, categories, and any fixed set of values.

String Enum (PostgreSQL native enum):

{
  "kind": "enum",
  "name": "order_status",
  "values": ["pending", "processing", "shipped", "delivered", "cancelled"]
}

Integer Enum (stored as INTEGER, no DB migration needed for new values):

{
  "kind": "enum",
  "name": "priority_level",
  "values": [
    { "name": "low", "value": 0 },
    { "name": "medium", "value": 1 },
    { "name": "high", "value": 2 },
    { "name": "critical", "value": 3 }
  ]
}

Why Integer Enums?

Adding new values requires NO database migration
Application-level enum mapping only
Better for frequently-changing value sets
Works identically across PostgreSQL, MySQL, SQLite

Custom Type (fallback)

{ "kind": "custom", "custom_type": "POINT" }
{ "kind": "custom", "custom_type": "TSVECTOR" }

Enum Best Practices (RECOMMENDED)

When to Use Enums

Scenario	Recommended Type
Status fields (order_status, user_status)	String enum or Integer enum
Categories with fixed values	String enum
Priority/severity levels	Integer enum
Roles with potential expansion	Integer enum
Country/currency codes (ISO)	String enum

String Enum Example

{
  "name": "status",
  "type": {
    "kind": "enum",
    "name": "article_status",
    "values": ["draft", "review", "published", "archived"]
  },
  "nullable": false,
  "default": "'draft'"
}

Integer Enum Example

{
  "name": "role",
  "type": {
    "kind": "enum",
    "name": "user_role",
    "values": [
      { "name": "guest", "value": 0 },
      { "name": "user", "value": 10 },
      { "name": "moderator", "value": 50 },
      { "name": "admin", "value": 100 }
    ]
  },
  "nullable": false,
  "default": 0
}

Tip: Leave gaps in integer values (0, 10, 50, 100) to allow inserting new values in between without renumbering.

Inline Constraints (PREFERRED)

Always define constraints directly on columns. This is cleaner, more readable, and the recommended pattern. Use table-level constraints array ONLY for composite keys or CHECK expressions.

Primary Key

Simple:

{ "name": "id", "type": "integer", "nullable": false, "primary_key": true }

With auto-increment:

{ "name": "id", "type": "integer", "nullable": false, "primary_key": { "auto_increment": true } }

Unique

Simple unique:

{ "name": "email", "type": "text", "nullable": false, "unique": true }

Named unique (for composite):

{ "name": "tenant_id", "type": "integer", "nullable": false, "unique": ["uq_tenant_user"] },
{ "name": "username", "type": "text", "nullable": false, "unique": ["uq_tenant_user"] }

Index

Simple index:

{ "name": "email", "type": "text", "nullable": false, "index": true }

Composite index:

{ "name": "user_id", "type": "integer", "nullable": false, "index": ["idx_user_created"] },
{ "name": "created_at", "type": "timestamptz", "nullable": false, "index": ["idx_user_created"] }

Foreign Key

Object syntax (recommended):

{
  "name": "user_id",
  "type": "integer",
  "nullable": false,
  "foreign_key": {
    "ref_table": "user",
    "ref_columns": ["id"],
    "on_delete": "cascade",
    "on_update": null
  },
  "index": true
}

Shorthand syntax:

{
  "name": "user_id",
  "type": "integer",
  "nullable": false,
  "foreign_key": "user.id",
  "index": true
}

Reference Actions (snake_case):

"cascade" - Delete/update child rows
"restrict" - Prevent if children exist
"set_null" - Set to NULL
"set_default" - Set to default value
"no_action" - Defer check (PostgreSQL)

Always add "index": true on foreign key columns for query performance.

Table-Level Constraints

IMPORTANT: Always prefer inline constraints (primary_key, unique, index, foreign_key on columns). Table-level constraints is ONLY needed for CHECK expressions.

When Table-Level is Required

Scenario	Why Inline Won't Work
CHECK constraint with expression	No inline equivalent exists

Inline Works for Everything Else

Scenario	Inline Solution
Composite primary key	`"primary_key": true` on EACH column
Composite unique	`"unique": ["constraint_name"]` on each column
Composite index	`"index": ["index_name"]` on each column
Foreign key	`"foreign_key": {...}` on the column

Syntax (CHECK only)

"constraints": [
  { "type": "check", "name": "check_positive_amount", "expr": "amount > 0" },
  { "type": "check", "name": "check_dates", "expr": "end_date > start_date" }
]

What NOT to Put in Table-Level

// BAD - Use inline instead
"constraints": [
  { "type": "primary_key", "columns": ["tenant_id", "user_id"] },  // Use: "primary_key": true on each column
  { "type": "unique", "columns": ["email"] },                       // Use: "unique": true on column
  { "type": "foreign_key", "columns": ["user_id"], ... },           // Use: "foreign_key": {...} on column
  { "type": "index", "columns": ["created_at"] }                    // Use: "index": true on column
]

// GOOD - Only CHECK constraints
"constraints": [
  { "type": "check", "name": "check_amount", "expr": "amount >= 0" }
]

Composite Primary Key Example (Inline)

{
  "name": "user_role",
  "columns": [
    { "name": "user_id", "type": "integer", "nullable": false, "primary_key": true },
    { "name": "role_id", "type": "integer", "nullable": false, "primary_key": true }
  ]
}

Both columns with "primary_key": true creates a single composite primary key (user_id, role_id).

Default Values

Type	Example	Notes
String literal	`"'pending'"`	Single quotes inside string
Boolean	`true` or `false`	Native JSON boolean
Integer	`0`	Native JSON number
Float	`0.0`	Native JSON number
SQL function	`"NOW()"`	No quotes around function
UUID generation	`"gen_random_uuid()"`	PostgreSQL

{ "name": "status", "type": "text", "nullable": false, "default": "'active'" },
{ "name": "count", "type": "integer", "nullable": false, "default": 0 },
{ "name": "enabled", "type": "boolean", "nullable": false, "default": true },
{ "name": "created_at", "type": "timestamptz", "nullable": false, "default": "NOW()" }

Complete Examples

User Table with Enum Status

{
  "$schema": "https://raw.githubusercontent.com/dev-five-git/vespertide/refs/heads/main/schemas/model.schema.json",
  "name": "user",
  "columns": [
    { "name": "id", "type": "integer", "nullable": false, "primary_key": { "auto_increment": true } },
    { "name": "email", "type": "text", "nullable": false, "unique": true, "index": true },
    { "name": "name", "type": { "kind": "varchar", "length": 100 }, "nullable": false },
    { 
      "name": "status", 
      "type": { 
        "kind": "enum", 
        "name": "user_status", 
        "values": ["pending", "active", "suspended", "deleted"] 
      }, 
      "nullable": false, 
      "default": "'pending'" 
    },
    { "name": "metadata", "type": "jsonb", "nullable": true },
    { "name": "created_at", "type": "timestamptz", "nullable": false, "default": "NOW()" },
    { "name": "updated_at", "type": "timestamptz", "nullable": true }
  ]
}

Order Table with Integer Enum Priority

{
  "$schema": "https://raw.githubusercontent.com/dev-five-git/vespertide/refs/heads/main/schemas/model.schema.json",
  "name": "order",
  "columns": [
    { "name": "id", "type": "uuid", "nullable": false, "primary_key": true, "default": "gen_random_uuid()" },
    { 
      "name": "customer_id", 
      "type": "integer", 
      "nullable": false, 
      "foreign_key": { "ref_table": "customer", "ref_columns": ["id"], "on_delete": "restrict" }, 
      "index": true 
    },
    { "name": "total", "type": { "kind": "numeric", "precision": 10, "scale": 2 }, "nullable": false },
    { 
      "name": "priority", 
      "type": { 
        "kind": "enum", 
        "name": "order_priority", 
        "values": [
          { "name": "low", "value": 0 },
          { "name": "normal", "value": 10 },
          { "name": "high", "value": 20 },
          { "name": "urgent", "value": 30 }
        ]
      }, 
      "nullable": false, 
      "default": 10 
    },
    { 
      "name": "status", 
      "type": { 
        "kind": "enum", 
        "name": "order_status", 
        "values": ["pending", "confirmed", "shipped", "delivered", "cancelled"] 
      }, 
      "nullable": false, 
      "default": "'pending'" 
    },
    { "name": "notes", "type": "text", "nullable": true },
    { "name": "created_at", "type": "timestamptz", "nullable": false, "default": "NOW()" }
  ],
  "constraints": [
    { "type": "check", "name": "check_total_positive", "expr": "total >= 0" }
  ]
}

Many-to-Many Join Table

{
  "$schema": "https://raw.githubusercontent.com/dev-five-git/vespertide/refs/heads/main/schemas/model.schema.json",
  "name": "user_role",
  "columns": [
    { 
      "name": "user_id", 
      "type": "integer", 
      "nullable": false, 
      "primary_key": true, 
      "foreign_key": { "ref_table": "user", "ref_columns": ["id"], "on_delete": "cascade" } 
    },
    { 
      "name": "role_id", 
      "type": "integer", 
      "nullable": false, 
      "primary_key": true, 
      "foreign_key": { "ref_table": "role", "ref_columns": ["id"], "on_delete": "cascade" },
      "index": true
    },
    { "name": "granted_at", "type": "timestamptz", "nullable": false, "default": "NOW()" },
    { "name": "granted_by", "type": "integer", "nullable": true, "foreign_key": "user.id" }
  ]
}

Article with Composite Index

{
  "$schema": "https://raw.githubusercontent.com/dev-five-git/vespertide/refs/heads/main/schemas/model.schema.json",
  "name": "article",
  "columns": [
    { "name": "id", "type": "big_int", "nullable": false, "primary_key": { "auto_increment": true } },
    { "name": "author_id", "type": "integer", "nullable": false, "foreign_key": "user.id", "index": ["idx_author_published"] },
    { "name": "title", "type": { "kind": "varchar", "length": 200 }, "nullable": false },
    { "name": "slug", "type": { "kind": "varchar", "length": 200 }, "nullable": false, "unique": true },
    { "name": "content", "type": "text", "nullable": false },
    { 
      "name": "status", 
      "type": { "kind": "enum", "name": "article_status", "values": ["draft", "review", "published", "archived"] }, 
      "nullable": false, 
      "default": "'draft'" 
    },
    { "name": "published_at", "type": "timestamptz", "nullable": true, "index": ["idx_author_published"] },
    { "name": "view_count", "type": "integer", "nullable": false, "default": 0 },
    { "name": "created_at", "type": "timestamptz", "nullable": false, "default": "NOW()" }
  ],
  "constraints": [
    { "type": "check", "name": "check_view_count", "expr": "view_count >= 0" }
  ]
}

Guidelines Summary

MUST DO

Always include $schema - No exceptions
Always specify nullable - Required for every column
Validate against schema - Before saving, check IDE errors
Index foreign key columns - Add "index": true
Use inline constraints - primary_key, unique, index, foreign_key ON the column

SHOULD DO

Use enums for status/category fields - Prefer over text + CHECK
Use integer enums for expandable sets - No migration needed for new values
Use timestamptz over timestamp - Timezone-aware is safer
Use jsonb over json - Indexable and faster

MUST NOT DO

Never use PascalCase for reference actions - Use "cascade" not "Cascade"
Never skip schema validation - Prevents runtime errors
Never add NOT NULL columns without default - Requires fill_with in migration
Never use table-level constraints - Except for CHECK expressions only
Never manually create/edit migration files - Only fill_with exception

Naming Conventions

Item	Convention	Example
Tables	snake_case	`user_role`
Columns	snake_case	`created_at`
Indexes	`idx_{table}_{columns}`	`idx_user_email`
Unique	`uq_{table}_{columns}`	`uq_user_email`
Foreign Key	`fk_{table}_{ref}`	`fk_post_author`
Check	`check_{description}`	`check_positive_amount`
Enums	snake_case	`order_status`

Quick Reference Card

COLUMN TYPES (simple)
────────────────────────────────────────
integer, big_int, small_int          Numbers
real, double_precision               Floats
text                                 Strings
boolean                              Flags
date, time, timestamp, timestamptz   Time
interval                             Duration
uuid                                 UUIDs
json, jsonb                          JSON
bytea                                Binary
inet, cidr, macaddr                  Network
xml                                  XML

COLUMN TYPES (complex)
────────────────────────────────────────
{ "kind": "varchar", "length": N }
{ "kind": "char", "length": N }
{ "kind": "numeric", "precision": P, "scale": S }
{ "kind": "enum", "name": "...", "values": [...] }
{ "kind": "custom", "custom_type": "..." }

REFERENCE ACTIONS (snake_case!)
────────────────────────────────────────
cascade, restrict, set_null, set_default, no_action

CONSTRAINT TYPES
────────────────────────────────────────
primary_key, unique, foreign_key, check, index

Troubleshooting

Error	Cause	Fix
Invalid enum in `on_delete`	PascalCase used	Use `"cascade"` not `"Cascade"`
Missing required property	`nullable` omitted	Add `"nullable": true/false`
Unknown column type	Typo in type name	Check SimpleColumnType enum
Foreign key validation failed	Referenced table missing	Create referenced table first
NOT NULL without default	Adding column to existing table	Add `default` or use `fill_with` in revision

vespertide

Installation

Details

Usage

Skill Instructions

name: vespertide description: Define database schemas in JSON and generate migration plans. Use this skill when creating or modifying database models, defining tables with columns, constraints, and ENUM types for Vespertide-based projects.

Vespertide Database Schema Definition

Schema Validation (MANDATORY)

Installation

CLI Commands

Migration Files (DO NOT EDIT)

Rules

When fill_with is Required

Workflow

Model Structure

Required Fields

Optional Fields

Column Definition

Required Fields

Optional Fields

Column Types

Simple Types (string values)

Complex Types (object values)

VARCHAR (variable-length string with limit)

CHAR (fixed-length string)

NUMERIC/DECIMAL (exact precision)

ENUM (STRONGLY RECOMMENDED)

Custom Type (fallback)

Enum Best Practices (RECOMMENDED)

When to Use Enums

String Enum Example

Integer Enum Example

Inline Constraints (PREFERRED)

Primary Key

Unique

Index

Foreign Key

Table-Level Constraints

When Table-Level is Required

Inline Works for Everything Else

Syntax (CHECK only)

What NOT to Put in Table-Level

Composite Primary Key Example (Inline)

Default Values

Complete Examples

User Table with Enum Status

Order Table with Integer Enum Priority

Many-to-Many Join Table

Article with Composite Index

Guidelines Summary

MUST DO

SHOULD DO

MUST NOT DO

Naming Conventions

Quick Reference Card

Troubleshooting

More by dev-five-git

When `fill_with` is Required