vespertide

dev-five-git/vespertide

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.

19 stars

1 forks

Rust

166 views

View on GitHub Add to Favorites

SKILL.md

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.

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

Command	Description
`vespertide init`	Initialize `vespertide.json` with defaults
`vespertide new <name>`	Create model template with `$schema`
`vespertide new <name> -f yaml`	Create model in specific format (`json`/`yaml`/`yml`)
`vespertide diff`	Show pending changes between migrations and models
`vespertide sql`	Preview SQL for next migration (default: postgres)
`vespertide sql -b mysql`	SQL for specific backend (`postgres`/`mysql`/`sqlite`)
`vespertide log`	Show SQL per applied migration
`vespertide log -b mysql`	Migration log for specific backend
`vespertide status`	Show config and sync overview
`vespertide revision -m "msg"`	Create migration file
`vespertide revision -m "msg" --fill-with table.col=value`	Create migration with fill values (repeatable)
`vespertide export --orm seaorm`	Export to ORM code (`seaorm`/`sqlalchemy`/`sqlmodel`)
`vespertide export --orm seaorm -d out/`	Export to custom directory

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

Field	Type	Default	Description
`modelsDir`	string	`"models"`	Model JSON files directory
`migrationsDir`	string	`"migrations"`	Migration files directory
`tableNamingCase`	string	`"snake"`	`snake` / `camel` / `pascal`
`columnNamingCase`	string	`"snake"`	`snake` / `camel` / `pascal`
`modelFormat`	string	`"json"`	`json` / `yaml` / `yml`
`migrationFormat`	string	`"json"`	`json` / `yaml` / `yml`
`migrationFilenamePattern`	string	`"%04v_%m"`	`%v`=version, `%m`=message
`modelExportDir`	string	`"src/models"`	ORM export output directory
`prefix`	string	`""`	Prefix for all table names

SeaORM Config (seaorm nested object):

Field	Type	Default	Description
`extraEnumDerives`	string[]	`["vespera::Schema"]`	Extra derives for generated enums
`extraModelDerives`	string[]	`[]`	Extra derives for entity models
`enumNamingCase`	string	`"camel"`	Serde `rename_all` for enums
`vesperaSchemaType`	bool	`true`	Generate `vespera::schema_type!` calls

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 */ ]
}

Field	Required	Type	Description
`name`	yes	string	Table name (snake_case)
`columns`	yes	array	Column definitions
`description`	no	string	Table documentation
`constraints`	no	array	Table-level constraints (CHECK only)

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	Type	SQL
`"small_int"`	SMALLINT	`"text"`	TEXT
`"integer"`	INTEGER	`"boolean"`	BOOLEAN
`"big_int"`	BIGINT	`"uuid"`	UUID
`"real"`	REAL	`"json"`	JSON
`"double_precision"`	DOUBLE PRECISION	`"bytea"`	BYTEA
`"date"`	DATE	`"inet"`	INET
`"time"`	TIME	`"cidr"`	CIDR
`"timestamp"`	TIMESTAMP	`"macaddr"`	MACADDR
`"timestamptz"`	TIMESTAMPTZ	`"xml"`	XML
`"interval"`	INTERVAL

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.

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

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

Type	Example	Notes
String literal	`"'pending'"`	Single quotes inside string
Boolean	`true` / `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

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

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

SHOULD DO

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

MUST NOT DO

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

Naming Conventions

Item	Convention	Example
Tables	snake_case	`user_role`
Columns	snake_case	`created_at`
Indexes	`ix_{table}__{columns}`	`ix_user__email`
Unique	`uq_{table}__{columns}`	`uq_user__email`
Foreign Key	`fk_{table}__{columns}`	`fk_post__author_id`
Check	`check_{description}`	`check_positive_amount`
Enums	snake_case	`order_status`

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

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 column types table above
FK 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

Installation

Option 1: Use slash command in Claude Code

/install-skill https://github.com/dev-five-git/vespertide

Option 2: Clone to skills directory

# Global (all projects)

git clone https://github.com/dev-five-git/vespertide ~/.claude/skills/vespertide

# Project-specific

git clone https://github.com/dev-five-git/vespertide .claude/skills/vespertide

Add MCP server to .cursor/mcp.json:

{
  "mcpServers": {
    "skillz": {
      "command": "npx",
      "args": ["-y", "skillz-mcp", "https://github.com/dev-five-git/vespertide"]
    }
  }
}

Restart Cursor after adding the configuration.

Option 1: Use Gemini CLI command

gemini extensions install https://github.com/dev-five-git/vespertide

Option 2: Clone to extensions directory

git clone https://github.com/dev-five-git/vespertide ~/.gemini/extensions/vespertide

Topics

Related Skills

skill-creator

Lightweight coding agent that runs in your terminal

tantivy

Tantivy is a full-text search engine library inspired by Apache Lucene and written in Rust

quickwit

Cloud-native search engine for observability. An open-source alternative to Datadog, Elasticsearch, Loki, and Tempo.

Generate Component Story

Rust GUI components for building fantastic cross-platform desktop application by using GPUI.

vespertide

SKILL.md

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)

Post-Edit Validation (MANDATORY)

Installation

CLI Commands

Configuration (vespertide.json)

Exported ORM Files (DO NOT EDIT)

Migration / Revision Files (DO NOT EDIT)

Model Structure

Column Definition

Required Fields

Optional Fields

Column Types

Simple Types (string values)

Complex Types (object values)

Enum Types (RECOMMENDED for status/category fields)

Inline Constraints (PREFERRED)

Primary Key

Unique

Index

Foreign Key

Composite Primary Key (Inline)

Table-Level Constraints (CHECK only)

Default Values

Runtime Migration Macro

Complete Examples

User Table with Enum Status

Order Table with Integer Enum and CHECK

Many-to-Many Join Table

Workflow Summary

Guidelines Summary

MUST DO

SHOULD DO

MUST NOT DO

Naming Conventions

Quick Reference

Troubleshooting

Configuration (`vespertide.json`)