Imported from dev-five-git/vespertide (SKILL.md). Install upstream with npx skills add dev-five-git/vespertide. Copyright stays with the author.

Vespertide Database Schema Definition

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

Schema Validation (MANDATORY)

Every model file MUST include $schema. Before saving: ensure no IDE validation errors, then run vespertide diff.

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

Post-Edit Validation (MANDATORY)

After EVERY model edit, run:

vespertide diff   # Check for parsing errors and expected changes
vespertide sql    # Preview generated SQL for correctness

Only proceed to vespertide revision after both pass cleanly.

Installation

cargo install vespertide-cli

CLI Commands

Configuration (vespertide.json)

{
  "modelsDir": "models",
  "migrationsDir": "migrations",
  "tableNamingCase": "snake",
  "columnNamingCase": "snake",
  "modelFormat": "json",
  "migrationFormat": "json",
  "migrationFilenamePattern": "%04v_%m",
  "modelExportDir": "src/models",
  "prefix": "",
  "seaorm": {
    "extraEnumDerives": ["vespera::Schema"],
    "extraModelDerives": [],
    "enumNamingCase": "camel",
    "vesperaSchemaType": true
  }
}

SeaORM Config (seaorm nested object):

Exported ORM Files (DO NOT EDIT)

CRITICAL: Files in src/models/ (e.g. src/models/*.rs, src/models/*.py) are AUTO-GENERATED by vespertide export. NEVER manually create or edit these files. Always edit source models in models/*.json, then regenerate:

# Edit models/*.json -> regenerate
vespertide export --orm seaorm

Migration / Revision Files (DO NOT EDIT)

CRITICAL: Migration files in the migrations/ directory are AUTO-GENERATED by vespertide revision. NEVER manually create, edit, or modify revision files. Always edit source models in models/*.json, then create a new revision:

# Edit models/*.json -> create revision
vespertide revision -m "describe your change"

Only exception: Adding fill_with values when prompted (NOT NULL columns without defaults).

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

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[] */ ],
  "constraints": [ /* Optional: CHECK constraints only */ ]
}

Column Definition

Required Fields

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

Optional Fields

Column Types

Simple Types (string values)

Complex Types (object values)

{ "kind": "varchar", "length": 255 }
{ "kind": "char", "length": 2 }
{ "kind": "numeric", "precision": 10, "scale": 2 }
{ "kind": "enum", "name": "...", "values": [...] }
{ "kind": "custom", "custom_type": "..." }

Enum Types (RECOMMENDED for status/category fields)

String Enum (PostgreSQL native enum):

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

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

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

Inline Constraints (PREFERRED)

Always define constraints on columns. Use table-level constraints ONLY for CHECK expressions.

Primary Key

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

Unique

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

Named composite unique:

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

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", "restrict", "set_null", "set_default", "no_action"

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

Composite Primary Key (Inline)

Both columns with "primary_key": true create a single composite primary key:

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

Table-Level Constraints (CHECK only)

Use inline constraints for everything else (PK, unique, index, FK).

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

Default Values

Runtime Migration Macro

Use vespertide_migration! to run migrations at application startup:

[dependencies]
vespertide = "0.1"
sea-orm = { version = "2.0.0-rc", features = ["sqlx-postgres", "runtime-tokio-native-tls", "macros"] }

use sea_orm::Database;

#[tokio::main]
async fn main() -> anyhow::Result<()> {
    let db = Database::connect("postgres://user:pass@localhost/mydb").await?;
    vespertide::vespertide_migration!(db).await?;
    Ok(())
}

The macro generates database-specific SQL at compile time for zero-runtime overhead.

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": "json", "nullable": true },
    { "name": "created_at", "type": "timestamptz", "nullable": false, "default": "NOW()" },
    { "name": "updated_at", "type": "timestamptz", "nullable": true }
  ]
}

Order Table with Integer Enum and CHECK

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

Workflow Summary

# 1. Create model
vespertide new user

# 2. Edit models/user.json

# 3. Validate
vespertide diff          # Check changes
vespertide sql           # Preview SQL

# 4. Create migration
vespertide revision -m "create user table"

# 5. Export ORM code (if needed)
vespertide export --orm seaorm

Guidelines Summary

MUST DO

1. Always include $schema in every model file
2. Always specify nullable on every column
3. Run vespertide diff + vespertide sql after every model edit
4. Index foreign key columns ("index": true)
5. Use inline constraints (primary_key, unique, index, foreign_key on columns)

SHOULD DO

1. Use enums for status/category fields (prefer over text + CHECK)
2. Use integer enums for expandable value sets (no migration needed)
3. Use timestamptz over timestamp (timezone-aware)
4. Use json type for JSON data (cross-backend compatible)

MUST NOT DO

1. Manually create/edit/modify revision (migration) files -- use vespertide revision only
2. Manually create/edit src/models/*.rs (or *.py) files -- use vespertide export to regenerate
3. Use PascalCase for reference actions -- use "cascade" not "Cascade"
4. Skip schema validation
5. Add NOT NULL columns without default or fill_with
6. Use table-level constraints for anything except CHECK
7. Use jsonb type -- use json instead (not supported cross-backend)
8. Use custom types -- breaks cross-database compatibility
9. Use array types -- use a join table instead

Naming Conventions

Note: Auto-generated constraint names use double underscore __ as separator.

Quick Reference

SIMPLE TYPES                              COMPLEX TYPES
----------------------------------------  ----------------------------------------
integer, big_int, small_int   Numbers     { "kind": "varchar", "length": N }
real, double_precision        Floats      { "kind": "char", "length": N }
text                          Strings     { "kind": "numeric", "precision": P, "scale": S }
boolean                       Flags       { "kind": "enum", "name": "...", "values": [...] }
date, time, timestamp         Time        { "kind": "custom", "custom_type": "..." }
timestamptz, interval         Time+
uuid                          UUIDs       REFERENCE ACTIONS (snake_case!)
json                          JSON        ----------------------------------------
bytea                         Binary      cascade, restrict, set_null,
inet, cidr, macaddr           Network     set_default, no_action
xml                           XML

CONSTRAINT TYPES (inline preferred)       DATABASE BACKENDS
----------------------------------------  ----------------------------------------
primary_key, unique, index,               postgres (default), mysql, sqlite
foreign_key, check

Troubleshooting