Sync DB Schema From Code

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

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

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

4. 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.

5. 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

6. 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:

     datasource db {
       provider = "postgresql"
       url      = env("DATABASE_URL")
       schemas  = ["public"]  // exclude extension schemas
     }
     

   • See ./reference/handling-extensions.md for detailed guidance

7. 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"

8. 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 "..."

9. 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.

10. 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)

11. 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)

12. Execute the chosen strategy and log every command in 03-execution-log.md.

13. 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)

14. 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

Converted and distributed by TomeVault — claim your Tome and manage your conversions.