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

```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:**
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:**

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

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

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

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:

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

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 |