# Sync DB Schema From Code

> Apply schema changes from repo SSOT (prisma/schema.prisma) to a target DB via Prisma migrations, with diff preview + approval gate; then refresh LLM context (docs/context/db/schema.json).

- Skill: `willyu1007/sync-db-schema-from-code` (Agent Skill, multi-file: 17 files)
- Install (CLI): `npx skillmds add willyu1007/sync-db-schema-from-code`
- Raw SKILL.md: https://api.skillmd.com/api/skills/willyu1007/sync-db-schema-from-code/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: AI & ML
- Author: willyu1007 (https://skillmd.com/u/willyu1007)
- Updated: 2026-09-10
- Page: https://skillmd.com/skills/willyu1007/sync-db-schema-from-code

---


# Sync DB Schema from Code (Prisma SSOT)

## Purpose

Treat `prisma/schema.prisma` as the schema Single Source of Truth (SSOT) and safely apply schema changes to a target database with:

- diff preview before any write
- explicit approval gate
- execution logging and post-verification
- **LLM context refresh** via `docs/context/db/schema.json`

The `sync-db-schema-from-code` skill targets a **development workflow** (developers manage DB changes via Prisma), not a DBA-centric workflow.

## Hard precondition (SSOT mode gate)

Use the `sync-db-schema-from-code` skill only when the project DB SSOT is `repo-prisma`:

- Source of truth: `prisma/schema.prisma`
- DB changes propagate via Prisma migrations

To check the mode, read:

- `docs/project/db-ssot.json`
- Or run: `node .ai/scripts/ctl-db-ssot.mjs status`

## When to use

Use when the user asks to:

- add/rename/remove fields or entities in the persistent model
- generate/apply Prisma migrations from repo changes
- deploy schema changes to dev/staging/prod
- resolve drift between `schema.prisma` and the actual DB

Avoid when:

- the task is primarily data backfill / transformation (separate workflow)

## Key invariants (developer-facing)

- **Persistence layer** is defined by Prisma models in `prisma/schema.prisma`.
- **Domain layer** must not depend on Prisma types.
- Repositories adapt: Prisma ↔ persistence entities ↔ domain entities.
- LLMs read DB structure from `docs/context/db/schema.json` (generated; do not hand-edit).

See `./reference/prisma-ssot-mechanism.md` for the end-to-end pattern (domain vs persistence vs DTO).

## Inputs

- Target environment: `dev` / `staging` / `prod` (must be explicit)
- DB connection (prefer `DATABASE_URL` via environment; never paste secrets into chat)
- Prisma strategy:
  - default: versioned migrations (`prisma migrate`)
  - `prisma db push` only if explicitly chosen (and justified)

## Outputs (evidence)

Choose one evidence location (no secrets):

- If the task meets `dev-docs/AGENTS.md` Decision Gate (recommended for staging/prod):
  - `dev-docs/active/<task-slug>/artifacts/db/`
- Otherwise (quick local/dev):
  - `.ai/.tmp/db-sync/<run-id>/`

Evidence files:

- `00-connection-check.md`
- `01-schema-diff-preview.md`
- `02-migration-plan.md`
- `03-execution-log.md`
- `04-post-verify.md`

## Steps

### Phase 0 — Confirm mode and scope

1. Confirm intent is **code → DB**.
2. Confirm DB SSOT mode is `repo-prisma` (`docs/project/db-ssot.json`).
   - If not, STOP: the `sync-db-schema-from-code` skill covers only `repo-prisma`.
3. Confirm the target environment and DB dialect.
4. Choose evidence directory.

### Phase A — Update the SSOT (repo)

5. Apply the schema change in `prisma/schema.prisma` (SSOT).
6. Run local validation (read-only against schema):
   - `npx prisma format`
   - `npx prisma validate`

7. Produce a diff preview (no target DB writes):
   - Prefer generating a migration *without applying it*:
     - `npx prisma migrate dev --create-only --name <slug>` (dev)
   - For existing migration history, review pending SQL under `prisma/migrations/*/migration.sql`.

8. Write `01-schema-diff-preview.md` and `02-migration-plan.md`:
   - summarize changes
   - flag destructive operations
   - specify rollout + rollback expectations

### Approval checkpoint (mandatory)

9. Ask for explicit user approval before any DB writes, confirming:
   - environment and target DB
   - whether destructive changes are allowed
   - backup/snapshot readiness (or explicit risk acceptance)
   - strategy (migrate default vs push explicit)

### Phase B — Apply to the target DB

10. Execute the chosen strategy and log every command in `03-execution-log.md`:

- **Staging/Prod** (typical):
  - `npx prisma migrate deploy`
- **Dev** (typical):
  - `npx prisma migrate dev`
- **Push** (explicit exception):
  - `npx prisma db push`

### Phase C — Post-verify

11. Verify schema and application health:

- `npx prisma migrate status`
- run unit/integration tests as applicable

Record evidence in `04-post-verify.md`.

### Phase D — Keep developer layers coherent

12. Update repository mappings and domain entities:

- Domain classes MAY contain non-persisted fields (computed/transient), but persistence mappings must stay explicit.
- Repositories must return domain entities (not Prisma client types).

13. Refresh LLM DB context contract:

- `node .ai/scripts/ctl-db-ssot.mjs sync-to-context`

> **Note:** The generated artifact (`docs/context/db/schema.json`) is produced by `ctl-db-ssot`. Context registration (if any) is the responsibility of external tooling; `ctl-db-ssot` does not auto-invoke context managers.

## Verification

- [ ] SSOT mode is `repo-prisma`
- [ ] Diff preview produced before writes
- [ ] Explicit approval gate respected
- [ ] Apply step executed with logs
- [ ] Post-verify evidence captured
- [ ] Domain/repository mapping updated (no Prisma types in business layer)
- [ ] `docs/context/db/schema.json` refreshed via `ctl-db-ssot`

## Boundaries

- MUST NOT execute DB writes without explicit user approval
- MUST default to versioned migrations (`prisma migrate`)
- MUST NOT run `prisma migrate dev` against production databases
- MUST NOT log or store credentials
- SHOULD prefer code review of migration SQL for staging/prod changes