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

```json
{
  "$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:

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

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

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

```bash
# 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:

```bash
# Edit models/*.json -> create revision
vespertide revision -m "describe your change"
```

**Only exception**: Adding `fill_with` values when prompted (NOT NULL columns without defaults).

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

---

## 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[] */ ],
  "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

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

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

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

### Unique

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

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

### 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"`, `"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:
```json
{
  "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).

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

```toml
[dependencies]
vespertide = "0.1"
sea-orm = { version = "2.0.0-rc", features = ["sqlx-postgres", "runtime-tokio-native-tls", "macros"] }
```

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

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

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

---

## Workflow Summary

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