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:

json

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

Post-Edit Validation (MANDATORY)

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

bash

# 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

bash

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

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

Workflow

bash

# 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

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

json

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

bash

# 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

json

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

json

{
  "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)

json

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

CHAR (fixed-length string)

json

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

NUMERIC/DECIMAL (exact precision)

json

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

json

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

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

json

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

json

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

json

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

Integer Enum Example

json

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

json

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

With auto-increment:

json

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

Unique

Simple unique:

json

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

Named unique (for composite):

json

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

Index

Simple index:

json

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

Composite index:

json

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

json

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

json

{
  "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)

json

"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

json

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

json

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

json

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

json

{
  "$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 Priority

json

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

json

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

json

{
  "$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 json type for JSON data - Works across all backends (PostgreSQL, MySQL, SQLite)

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
•Never manually edit exported ORM files - Use vespertide export to regenerate
•Never use jsonb type - Use json instead (JSONB not supported in SQLite)
•Never use custom types - Use built-in types only for cross-database compatibility
•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

code

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

Vespertide Database Schema Definition

Schema Validation (MANDATORY)

Post-Edit Validation (MANDATORY)

Installation

CLI Commands

Exported ORM Files (DO NOT EDIT)

Rules

Workflow

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 (AVOID - last resort only)

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

When `fill_with` is Required