stacks-migrations

# Stacks Database Migrations Schema change management via migration files. ## Key Paths - Migration files: `database/migrations/` (96+ files) - Database config: `config/database.ts` - Model snapshot: `storage/framework/database/model-snapshot.<dialect>.json` ## CLI Commands ```bash buddy migrate # run pending migrations buddy migrate --diff # show SQL without running buddy migrate --auth # include auth tables buddy migrate:fresh # drop ALL tables and re-migrate buddy migrate:fresh --seed # drop, migrate, then seed buddy dns:pull # live zone as a config/dns.ts block buddy make:migration <name> # create new migration file buddy seed # seed database buddy generate:migrations # generate migrations from model diffs ``` ## Creating a Migration ```bash buddy make:migration create_orders_table ``` Creates a timestamped migration file in `database/migrations/`. ## Migration Generation from Models When you define or modify a model, generate migrations automatically: ```bash buddy generate:migrations ``` This recursively loads both model roots, with `app/Models/` overriding framework models that have the same model name: - `storage/framework/defaults/app/Models/` - `app/Models/` It diffs the merged registry against the committed dialect snapshot and generates the necessary SQL. An application does not need an `app/Models/` directory for framework model migrations to be discovered. The snapshot is part of the schema history and must be committed with the generated migration. It lives under `storage/framework/database/`, not `.qb/`. Run the generator a second time before committing. A stable change reports `Nothing to migrate` and `Model snapshot unchanged`. ## Built-in Migrations (96+) The framework includes migrations for all built-in models: ### Core Tables - `users` — id, name, email (unique), password, timestamps - `personal_access_tokens` — auth tokens - `passkeys` — WebAuthn credentials - `password_resets` — password reset tokens ### Content - `posts` — title, content, excerpt, views, status, published_at, author_id - `pages`, `categories`, `tags`, `comments` - `authors` — linked to users ### Commerce (20+ tables) - `products`, `product_variants`, `product_units` - `orders`, `order_items` - `carts`, `cart_items` - `coupons`, `gift_cards`, `reviews` - `customers`, `manufacturers` ### Payments - `payments`, `payment_methods`, `payment_products`, `payment_transactions` - `subscriptions`, `transactions` ### Shipping - `shipping_methods`, `shipping_rates`, `shipping_zones` - `delivery_routes`, `drivers` ### System - `jobs`, `failed_jobs` — queue tables - `errors`, `logs`, `notifications` - `activities`, `requests`, `websockets` ### Indexes - `users.email` (unique), `users(email, name)` (composite) - `subscribers.email` (unique) - `coupons.code` (unique), `gift_cards.code` (unique) - `payments.transaction_id` (unique) - `subscriptions.provider_id` (unique) ## Workflow 1. Define/modify model in `storage/framework/defaults/app/Models/` or `app/Models/` 2. Run `buddy generate:migrations` to generate SQL diffs 3. Review generated migration files 4. Run `buddy generate:migrations` again and confirm there is no remaining diff 5. Run `buddy migrate` to apply 6. Commit the generated SQL and `storage/framework/database/model-snapshot.<dialect>.json` ## Gotchas - `migrate:fresh` drops ALL tables — only use in development - Migrations run in filename order (timestamps ensure correct sequence) - Never edit a migration that's been run in production — create a new one - Keep the committed model snapshot in sync with every generated migration - Do not commit a second snapshot under `.qb/`; that indicates a missing `snapshotDir` configuration - If a generated SQLite migration rebuilds tables, test it against a copy of the current database and run `PRAGMA integrity_check` plus `PRAGMA foreign_key_check` - `--seed` flag after `migrate:fresh` seeds the database with factory data - 235 migration files exist by default for all framework models - SQLite >= 3.47.2 is required (system requirement) - For the database API (queries, connections), see the `stacks-database` skill