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.

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:

1. Ensure $schema URL is present
2. Verify IDE shows no validation errors
3. 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

---

Post-Edit Validation (MANDATORY)

After EVERY edit to a model file, ALWAYS run these checks:

# 1. Check for parsing errors and schema violations
vespertide diff

# 2. Preview generated SQL to verify correctness
vespertide sql

Verify the output:

• vespertide diff shows expected changes (no unexpected additions/removals)
• vespertide sql generates valid SQL for your target database
• IDE shows no red squiggles (schema validation errors)
• All required fields (name, type, nullable) are present

Only proceed to vespertide revision after verification passes.

---

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

---

Exported ORM Files (DO NOT EDIT)

CRITICAL: Files generated by vespertide export are AUTO-GENERATED. Never modify them manually.

Rules

1. Never manually edit exported files (SeaORM entities, SQLAlchemy models, etc.)
2. Always regenerate by running vespertide export --orm <orm_name>
3. Edit source models in models/*.json instead, then re-export

Workflow

# 1. Edit your model files (models/*.json)
# 2. Regenerate ORM code
vespertide export --orm seaorm

# 3. Never touch the generated files after this

---

Migration Files (DO NOT EDIT)

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

Rules

1. Always use vespertide revision -m "message" to create migrations
2. Never manually create migration JSON files
3. Never manually edit migration JSON files
4. 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 (cross-database compatible)
"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 (AVOID - last resort only)

WARNING: Avoid custom types. They break cross-database compatibility. Use built-in types or redesign your schema.

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

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



 
         

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":        

---

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 
                
     
        
        
          
          
             
       
        
        
    
           
             
           
  

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"     
        
    
                  
     
        
        
          
          
         
               
               
               
               
        
       
        
        
    
     
        
        
          
          
              
       
        
        
    
           
             
  
   
           
  

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":  
        
        
              
       
    
             
             
  

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" 
                
                  
           
     
        
                  
        
        
    
             
             
             
  
   
           
  

---

Guidelines Summary

MUST DO

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

SHOULD DO

1. Use enums for status/category fields - Prefer over text + CHECK
2. Use integer enums for expandable sets - No migration needed for new values
3. Use timestamptz over timestamp - Timezone-aware is safer
4. Use json type for JSON data - Works across all backends (PostgreSQL, MySQL, SQLite)

MUST NOT DO

1. Never use PascalCase for reference actions - Use "cascade" not "Cascade"
2. Never skip schema validation - Prevents runtime errors
3. Never add NOT NULL columns without default - Requires fill_with in migration
4. Never use table-level constraints - Except for CHECK expressions only
5. Never manually create/edit migration files - Only fill_with exception
6. Never manually edit exported ORM files - Use vespertide export to regenerate
7. Never use jsonb type - Use json instead (JSONB not supported in SQLite)
8. Never use custom types - Use built-in types only for cross-database compatibility
9. Never use array types - Use a separate join table instead (arrays not supported in SQLite)

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