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

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

Installation

cargo install vespertide-cli

CLI Commands

vespertide init

vespertide.json

vespertide new <name>

$schema

vespertide diff

vespertide sql

vespertide sql --backend mysql

vespertide revision -m "msg"

vespertide status

vespertide log

vespertide export --orm seaorm

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

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

The

fill_with

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

name

columns

Optional Fields

description

constraints

Column Definition

Required Fields

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

Optional Fields

default

comment

primary_key

unique

index

foreign_key

Column Types

Simple Types (string values)

"small_int"

"integer"

"big_int"

"real"

"double_precision"

"text"

"boolean"

"date"

"time"

"timestamp"

"timestamptz"

"interval"

"bytea"

"uuid"

"json"

"inet"

"cidr"

"macaddr"

"xml"

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

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

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

Inline Works for Everything Else

"primary_key": true

"unique": ["constraint_name"]

"index": ["index_name"]

"foreign_key": {...}

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"] }                    // Use: "index": true on column
]

// GOOD - Only CHECK constraints
"constraints": [
  { "type": "check", "name": "check_amount", "expr": "amount >= 0" }
]

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

(user_id, role_id)

Default Values

"'pending'"

true

false

0

0.0

"NOW()"

"gen_random_uuid()"

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

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

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

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

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

user_role

created_at

idx_{table}_{columns}

idx_user_email

uq_{table}_{columns}

uq_user_email

fk_{table}_{ref}

fk_post_author

check_{description}

check_positive_amount

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

on_delete

"cascade"

"Cascade"

nullable

"nullable": true/false

default

fill_with