Sync DB Schema from Code

Purpose

Treat the project codebase as the schema Single Source of Truth (SSOT) and safely apply schema changes to a target database (remote or local) with:

•connection preflight
•schema drift/diff preview
•an explicit approval gate before any write
•execution logging and post-verification

When to use

Use the sync-db-schema-from-code skill when the user asks to:

•apply schema changes from the project to a remote database
•deploy database migrations to a managed database (cloud or self-hosted)
•sync Prisma or ORM model changes to an actual database
•verify and resolve schema drift before releasing

Avoid the skill when:

•the user wants to pull/introspect schema from the database back into code (reverse direction)
•the task is primarily data migration/backfill (separate workflow)

Inputs

•Target database type: PostgreSQL, MySQL/MariaDB, or SQLite
•Target database connection info (prefer DATABASE_URL)
•Target environment: dev/staging/prod (must be explicit)
•
SSOT type in the repo:
- •Prisma (prisma/schema.prisma), or
- •SQLAlchemy models with Alembic (if present)
•
Execution strategy:
- •Default: Prisma migrate (versioned migrations)
- •Optional (explicitly chosen): Prisma db push
•
Schema scope configuration (optional, PostgreSQL only):
- •includeSchemas: schemas to sync (default: ["public"])
- •excludeSchemas: schemas to exclude (e.g., ["extensions", "tiger", "topology"] for PostGIS)
- •shadowDatabaseSchema: schema for Prisma shadow database (if non-default)
- •See ./templates/schema-scope-config.md for configuration guide

Outputs

Create an auditable task log under dev-docs/active/<task>/db/:

•00-connection-check.md (no secrets)
•01-schema-drift-report.md
•02-migration-plan.md
•03-execution-log.md
•04-post-verify.md

Optionally, store machine-readable snapshots under dev-docs/active/<task>/db/artifacts/.

Steps

Phase 0 — Confirm intent and scope

•Confirm the user wants code → target DB synchronization (not reverse).
•Confirm the target environment (dev/staging/prod) and the target DB type.
•Propose a <task> slug for dev-docs/active/<task>/ and confirm it.

Phase A — Read-only preflight (no DB writes)

•
Detect the SSOT approach:
- •Prisma: prisma/schema.prisma exists
- •Alembic: alembic.ini / alembic/ exists
- •If both exist, ask which is the SSOT for this project.
•
Guide connection setup (lightweight):
- •Prefer DATABASE_URL in the environment (or .env loaded by the runtime)
- •Never ask the user to paste secrets into chat logs
- •Record a redacted connection summary in 00-connection-check.md
•
Extension and schema detection (PostgreSQL only):
- •Query installed extensions: SELECT extname, extnamespace::regnamespace FROM pg_extension
- •Detect non-public schemas created by extensions (e.g., tiger, topology for PostGIS)
- •
  If extensions are detected:
  - •Inform user about extension schemas found
  - •Ask if schema scope configuration is needed
  - •If yes, guide user to configure excludeSchemas (see ./templates/schema-scope-config.md)
  - •Record extension info in 00-connection-check.md
- •
  Configure Prisma schemas array in schema.prisma if needed:
  prisma
  datasource db { provider = "postgresql" url = env("DATABASE_URL") schemas = ["public"] // exclude extension schemas }
- •See ./reference/handling-extensions.md for detailed guidance
•
Validate connectivity using the included script:
- •python3 ./scripts/db_connect_check.py --url "$DATABASE_URL" --out "dev-docs/active/<task>/db/00-connection-check.md"
•
Capture a schema snapshot (for SQLite; for other DBs if drivers are available):
- •python3 ./scripts/db_schema_snapshot.py --url "$DATABASE_URL" --out "dev-docs/active/<task>/db/artifacts/schema_snapshot.json"
- •
  For PostgreSQL with extensions, use --exclude-schemas to filter extension schemas:
  - •python3 ./scripts/db_schema_snapshot.py --url "$DATABASE_URL" --exclude-schemas extensions,tiger,topology --out "..."
•
Produce a diff preview (no writes):
- •
  Prisma (default migrate):
  - •Prefer generating a reviewable migration (--create-only) for local/dev.
  - •For remote/prod deploy: review pending prisma/migrations/*/migration.sql.
  - •Optionally generate a SQL preview with prisma migrate diff.
  - •If schema scope is configured, ensure prisma migrate diff respects the schemas array.
- •
  Prisma (explicit push):
  - •There is no native db push --dry-run; use prisma migrate diff as the preview.
  - •For high-risk changes, recommend testing on a cloned/staging DB first.
- •
  Alembic:
  - •Generate a revision with --autogenerate and review the script before applying.
  - •Use include_schemas / exclude_schemas in env.py if schema filtering is needed.
•
Write 01-schema-drift-report.md and 02-migration-plan.md:
- •summarize intended schema changes
- •flag destructive operations (drop column/table, type changes)
- •explicitly note extension-related objects (functions, types, operators created by extensions)
- •define verification and rollback strategy
- •choose strategy: migrate (default) vs push (explicit)

Approval checkpoint (mandatory)

•
Ask for explicit user approval before any DB writes, confirming:
- •target environment and target DB
- •schema scope configuration (if PostgreSQL with extensions)
- •backup/snapshot readiness (or acceptance of risk)
- •chosen strategy (migrate default vs push explicit)
- •whether destructive changes are allowed

Phase B — Apply (DB writes allowed only after approval)

•
Execute the chosen strategy and log every command in 03-execution-log.md.
•
Post-verify and record evidence in 04-post-verify.md:
- •rerun schema snapshot / Prisma status checks
- •confirm application compatibility (build/tests as applicable)
- •confirm no unintended destructive impact
- •verify extension objects remain intact (if applicable)
•
SSOT maintenance:
- •If using Prisma migrate: ensure the migration files and schema.prisma are committed together.
- •If using push: record why, and define how/when the project will move back to versioned migrations.
- •If schema scope is configured: document the configuration in project README or prisma/README.md.

Verification

• Intent is confirmed as code → target DB
• Target environment and DB type are explicit
• Connectivity check completed and saved without secrets
• (PostgreSQL) Extension detection completed; schema scope configured if needed
• Diff preview produced and reviewed before applying changes
• Strategy is explicit (default migrate; push only if explicitly chosen)
• Approval gate was respected before any DB writes
• Execution log and post-verification evidence are saved under dev-docs/active/<task>/db/
• (PostgreSQL with extensions) Extension objects verified intact post-migration

Boundaries

•MUST NOT run reverse sync (DB → code) as the primary workflow
•MUST NOT execute DB writes (migrations, push, DDL) without explicit user approval
•MUST default to versioned migrations (Prisma migrate) unless the user explicitly chooses push
•MUST NOT run prisma migrate dev against production databases
•MUST NOT apply destructive changes without an explicit backup/snapshot plan (or explicit risk acceptance)
•MUST NOT log or store credentials; always redact connection strings
•SHOULD prefer reviewing migration SQL in code review for remote/prod changes

Included assets

•
Templates: ./templates/ for connection, drift, plan, execution log, and verification docs
- •schema-scope-config.md: PostgreSQL schema scope configuration guide
•
Reference: ./reference/ for lightweight connection and strategy guidance
- •handling-extensions.md: PostgreSQL extension handling and troubleshooting
•
Scripts: ./scripts/ for connection checks and schema snapshots
- •db_schema_snapshot.py supports --exclude-schemas for PostgreSQL
•Tests: ./tests/ contains a SQLite smoke test harness for the scripts