---
name: database-schema
description: >-
  Use only when requested work creates, changes, or reviews a persistent database contract or its direct storage mapping: tables, collections, columns, fields, constraints, indexes, migrations, ORM models, or storage-mapped models. Keep schema documents and code synchronized. Excludes read-only operations and designs that only consume persistence without changing its contract.
---

# Database Schema

Maintain a canonical, JSON-Schema-valid description of persistent data contracts and keep it synchronized with their code and database representations.

## Closed-loop workflow

For any persistent structure or contract change, even when the request does not say "schema":

1. Locate `docs/schemas/SCHEMA-REGISTRY.json` in the consuming project.
2. Resolve the affected source, canonical, or target document from the registry. Without an existing record, use `docs/schemas/<table_name>.jsonc` unless the user explicitly chose a shared location.
3. Read the canonical document, relevant imports, affected migrations or models, and adjacent project conventions.
4. Apply the user's requested change to both schema and code in the same task.
5. Update the registry without adding duplicate document entries.
6. Validate structure and report semantic risks and any unsynchronized artifacts.

If a registered external document is missing, report the broken path and preserve the record. Do not create a second local schema to hide the problem.

## Hard invariants

- Preserve user-declared names, types, nullability, defaults, readonly flags, enum values, indexes, and descriptions exactly. Risks are warnings, not permission to rewrite intent.
- `required: false` means nullable and must map to a nullable target-language type.
- Explicit per-language or per-database type mappings override general recommendations.
- Keep risk warnings outside generated artifacts unless the user asks to include them.
- Generated code must include `@schema-ref` provenance for the source file, table, and field.
- A schema and its code mapping are one contract: changes and review findings must be checked in both directions.

## Multiple sources and databases

Use registry version 2 when a project has multiple sources or database targets. Identify documents by stable `schema_id`, `entity`, `role`, and, for source or target documents, `system_id`. Keep separate target documents for databases whose types, indexes, naming, or nullability differ.

## Load only what the task needs

- For any schema or code synchronization, read [references/CONSTRAINTS.md](references/CONSTRAINTS.md) for fidelity, registry, nullable, and provenance rules.
- When creating, validating, or editing a JSONC schema instance, read [schema.json](./schema.json). Read [registry.schema.json](./registry.schema.json) only when creating, migrating, or changing the registry shape.
- For multiple sources, multiple targets, shared storage, or registry version 2, read [references/multi-source-layout.md](references/multi-source-layout.md).
- For type mapping, nullable code generation, driver types, precision, date/time, or enum representation, read [references/type-mapping.md](references/type-mapping.md).
- For index changes or database-specific index behavior, read [references/indexes-and-risks.md](references/indexes-and-risks.md).
- Load one matching file under [examples](./examples/) only when an existing project artifact and the schemas above do not make the required format clear.