Coreex Db Migration

---
name: coreex-db-migration
description: "Add or change a database table for a CoreEx domain. USE FOR: new transactional table, new reference-data table, altering an existing table (columns, indexes, constraints), or any other schema change (indexes, functions, stored procs). Scaffolds the correct migration script, updates dbex.yaml, applies the migration, and regenerates Infrastructure persistence models. DO NOT USE FOR: outbox provisioning (use dotnet run -- script outbox directly), seed data only changes (dotnet run -- Data), or CoreEx contract/service generation (that is *.CodeGen, not *.Database)."
argument-hint: "Optional: entity/table name, schema, SQL Server vs PostgreSQL, nature of change"
tags: ["database", "migration", "dbex", "schema", "efcore", "coreex"]
---

<!--
  AI workflow asset — dual-audience notice:
  - In the Avanade/CoreEx repository: this file is the authored source. Edit it here.
  - In a consumer repository: this file was generated by `dotnet new coreex-ai` (or refreshed via
    `dotnet new coreex-ai --force` / the `/coreex-docs-sync` skill). Do not hand-edit it directly —
    propose the change upstream in Avanade/CoreEx instead, then refresh once it is released.
-->

# CoreEx: DB Migration

Guides you through any database schema change for a CoreEx domain — from choosing the right migration script through to regenerated EF persistence models.

## When to Use

- Adding a new table for a new entity (transactional or reference-data)
- Altering an existing table — adding/modifying/removing columns, indexes, or constraints
- Any other schema change that needs to flow through to regenerated `*.g.cs` Infrastructure files
- Non-entity schema changes (adding an index, a unique constraint, a function)

## When Not to Use

- Provisioning the transactional outbox — run `dotnet run -- script outbox <schema> <name>` directly; see `coreex-tooling.instructions.md`
- Changing reference-data seed rows only — edit `Data/ref-data.seed.yaml` and run `dotnet run -- Data`
- Generating CoreEx contracts/services/repositories — that is `*.CodeGen`, not `*.Database`
- Runtime or deployment issues

> **Resolve project-wide choices from state before asking.** Read the solution-root `AGENTS.md`
> **Feature Configuration** for `data-provider` (SQL Server / PostgreSQL — gates this whole skill; `None`
> means no database) and `outbox-enabled`. Only prompt for what is unrecorded; re-state resolved values
> for confirmation.

## Quick Reference

All commands run from the `*.Database` project directory.

| Task | Command |
|---|---|
| Bring DB up to date | `dotnet run -- database` |
| Inspect current table state | `dotnet run -- inspect <schema> <table>` |
| New transactional table | `dotnet run -- script create <schema> <table>` |
| New reference-data table | `dotnet run -- script refdata <schema> <table>` |
| Alter existing table | `dotnet run -- script alter <schema> <table>` |
| Non-entity schema change | `dotnet run -- script` |
| Apply everything + regenerate | `dotnet run -- All` |
| Drop + full rebuild (destructive, confirm first) | `dotnet run -- dropandall --accept-prompts` |

## Naming

- Script commands produce a file named `yyyyMMdd-HHmmss-<descriptor>.{sql|pgsql}` using the current UTC date and time.
- For `create`, `refdata`, and `alter` the descriptor is auto-derived from the table name.
- For bare `script` (non-entity changes), DbEx names the file with a placeholder suffix — **rename it immediately** to a 3–5 word kebab summary of intent (e.g. `add-unique-code-index`, `drop-legacy-status-column`).
- The complete filename (timestamp + descriptor + extension) **must not exceed 255 characters**. The project name and Migrations folder (e.g. `Contoso.Products.Database.Migrations`) are used as an embedded-resource name prefix internally, so keep descriptors concise.

## Polyglot Note

| Provider | Script extension | Casing |
|---|---|---|
| PostgreSQL | `.pgsql` | `snake_case` |
| SQL Server | `.sql` | `PascalCase` |

Check the project's `*.Database/Program.cs` or `appsettings.json` to confirm the provider in use.

For the full step-by-step decision tree, SQL column templates, and guardrails see [`references/workflow.md`](references/workflow.md).

## JSON Columns

A column whose name ends with `Json` (SQL Server) or `_json` (PostgreSQL) stores a serialised .NET type as JSON text. DbEx surfaces this in `Inspect` output as `Json: Yes`.

Three things are required:

1. **A `columns:` entry in `dbex.yaml`** — without it, DbEx generates `string?` with no converter.
   `name:` (DB column name including the suffix), `property:` (C# name without suffix), `type:` (CLR type, e.g. `Persistence.Address?` or `List<string>?`).
2. **A hand-authored persistence POCO** in `Infrastructure/Persistence/` when the stored type is a complex object. For natively-serialisable types (`List<string>?`, `Dictionary<K,V>?`, etc.) use the .NET type directly — no extra class needed.
3. **No manual `.HasConversion(...)` call** — `TypeToJsonStringEfConverter<T>` is auto-wired in the generated `*DbContext.g.cs` when `type:` is non-string.

Default column types (unless the user explicitly opts into unbounded/native JSON storage): bounded text matching the DB's normal string-column convention — `NVARCHAR(n)` (SQL Server) / `VARCHAR(n)` (PostgreSQL), e.g. `NVARCHAR(2000)`/`VARCHAR(2000)` as a reasonable starting size. `NVARCHAR(MAX)` / native `JSONB`/`JSON` are an **override** for when unbounded storage or in-database JSON querying/indexing is deliberately wanted — see `samples/src/Contoso.Products.Database` (`tags_json` → native `JSONB`, an intentional override) vs. `samples/src/Contoso.Shopping.Database` (`ShippingAddressJson` → bounded `NVARCHAR(2000)`, the default) for both side-by-side.

For the full workflow, example YAML, DDD aggregate vs CRUD service guidance, and POCO class conventions see [`references/workflow.md` — JSON columns](references/workflow.md#json-columns).

## Key References

- [`/.github/instructions/coreex-tooling.instructions.md`](/.github/instructions/coreex-tooling.instructions.md) — DbEx command reference, `dbex.yaml` structure, SQL conventions, outbox provisioning
- [`/.github/instructions/coreex-repositories.instructions.md`](/.github/instructions/coreex-repositories.instructions.md) — what the generated `*.g.cs` feeds into
- Related skills: [`coreex-refdata`](../coreex-refdata/SKILL.md) (reference-data tables + CodeGen), [`coreex-repository`](../coreex-repository/SKILL.md) (maps the generated persistence models)
- Illustrative examples (CoreEx sample — not present in your project):
  - [PostgreSQL domain database project](https://github.com/Avanade/CoreEx/tree/main/samples/src/Contoso.Products.Database) — canonical `.pgsql` / `snake_case` migrations, `dbex.yaml`, seed files
  - [SQL Server domain database project](https://github.com/Avanade/CoreEx/tree/main/samples/src/Contoso.Shopping.Database) — canonical `.sql` / `PascalCase` migrations, `dbex.yaml`, seed files