vespertide

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

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

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