Vespertide: Скилл для JSON-схем и миграций БД

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