Database Schema Sync

Database schema management using idempotent sync script instead of Alembic migrations. Use when (1) Adding new database tables, (2) Adding new columns to exi...

Security Scan

The skill's stated purpose (run a production schema sync script) mostly matches the files and examples, but the instructions reference undeclared production credentials, hard-coded user-specific filesystem paths, and ask you to run a script that is not provided — which is inconsistent and risky unless you review the actual sync script first.

Purpose & Capability

The skill claims to manage production schema via an idempotent sync script and includes examples and a verification helper. That purpose aligns with the example code and docs. However, the manifest declares no required env vars or config paths, while SKILL.md and the verify script expect a production DATABASE_URL and a script located at /Users/tobymorning/Desktop/core/scripts/sync-production-schema.py. The skill does not include the actual sync-production-schema.py file. Hard-coded, single-user absolute paths in 'Required Locations' are disproportionate for a reusable skill and suggest either a packaged personal repo or incomplete packaging.

Instruction Scope

Runtime instructions tell the agent to run python scripts/sync-production-schema.py --dry-run and --apply against production, export DATABASE_URL, and run psql against the production DB. Those actions are expected for schema tooling, but the instructions assume a local file location that may not exist and instruct running scripts that are not present in the package. The included verify script also attempts to change file permissions (chmod +x) on an absolute path and greps for functions in that file—these are file-system modifications and checks outside the skill manifest. SKILL.md references environment variables (DATABASE_URL) and exact filesystem paths that are not declared in the skill metadata. The instructions are prescriptive ('ALWAYS use', 'NEVER run alembic in production') and could lead to dangerous operations if the actual sync script is malicious or buggy.

Install Mechanism

There is no install spec — instruction-only plus a small verify script — so nothing will be automatically downloaded or installed. That reduces supply-chain risk. However, the verify script (provided) will attempt to modify local filesystem permissions when executed, so running it has side effects despite no formal install step.

Credentials

The manifest lists no required environment variables or primary credential, but the SKILL.md explicitly instructs exporting and using DATABASE_URL (production DB credentials). Requesting production DB access is reasonable for a schema tool, but the omission from requires.env is an incoherence. The skill also expects access to a specific user's filesystem path; those path requirements are not declared. The combination (undeclared use of sensitive DB credentials + hard-coded local paths) is disproportionate and should be clarified before use.

Persistence & Privilege

The skill is not always-enabled and does not request permanent presence or elevated platform privileges. It does, however, permit the agent to be invoked normally. There is no evidence it tries to modify other skills or system-wide agent settings.

What to consider before installing

Do not run anything on production until you inspect the actual sync script. Specific actions to take before using/installing: 1) Ask the publisher for the full scripts/sync-production-schema.py used by the skill (it is not included) and review its source for any network calls, secrets exfiltration, or unsafe SQL. 2) Confirm why the skill manifest omits DATABASE_URL and other env requirements — treat DATABASE_URL as a sensitive secret and only provide it in a controlled CI environment or ephemeral shell after code review. 3) Beware the included verify script: it will check for the script at an absolute user-specific path and attempt to chmod it; running the verifier may change file permissions on your machine. 4) Prefer running the sync script first against a copy of production (snapshot or staging) and always run --dry-run. 5) If you rely on this approach, require code review, automated testing, and backups/DB snapshots before applying to production. If you cannot review the sync script, do not run it against production; consider using well-known migration tools (Alembic) or a vetted internal process instead.

Database Schema Management

USE SCHEMA SYNC SCRIPT, NOT ALEMBIC MIGRATIONS

PREFERRED APPROACH: Smart schema sync script that detects and applies only missing changes.

STRICT RULES

• ✅ ALWAYS USE: scripts/sync-production-schema.py for production deployments
• ✅ IDEMPOTENT: Can run multiple times safely without errors
• ✅ TRANSPARENT: Shows exactly what will change before applying
• ❌ AVOID: Running Alembic migrations directly in production
• ❌ AVOID: Manual SQL scripts that aren't version controlled
• ⚠️ KEEP: Alembic migration files for documentation purposes only

Why Schema Sync Script?

✅ Advantages:

• Simpler: One script vs managing many migration files
• Safer: Checks what exists before applying changes
• Idempotent: Run multiple times without errors
• Transparent: Shows diff before applying
• Flexible: Works with any database state (dev, staging, prod)
• No tracking: No need to manage "which migrations have run"

❌ Alembic Migration Problems:

• Fails if run twice (not idempotent)
• Requires tracking which migrations applied
• All-or-nothing (can't skip one migration)
• Complex rollback scenarios
• Team coordination overhead

Workflow

1. DRY RUN FIRST (Always!)

# Show what would change WITHOUT applying
python scripts/sync-production-schema.py --dry-run

2. REVIEW OUTPUT

# Output shows:
# ✓ Tables/columns that already exist (skipped)
# ℹ New tables/columns that would be created
# ⚠ Any potential issues

3. APPLY TO PRODUCTION

# Apply changes to production database
export DATABASE_URL="postgresql://..."
python scripts/sync-production-schema.py --apply

4. VERIFY

# Connect and verify schema
psql "$DATABASE_URL" -c "\dt"  # List tables
psql "$DATABASE_URL" -c "\d table_name"  # Describe table

Required Locations

• Schema Sync Script: /Users/tobymorning/Desktop/core/scripts/sync-production-schema.py
• Documentation: /Users/tobymorning/Desktop/core/docs/deployment/SCHEMA_SYNC_GUIDE.md
• Alembic Migrations (for documentation): /Users/tobymorning/Desktop/core/src/backend/alembic/versions/

When to Update Schema

Adding New Tables

1. Define models in src/backend/app/models/
2. Add table creation logic to scripts/sync-production-schema.py
3. Update docs/deployment/SCHEMA_SYNC_GUIDE.md with new table info
4. Test with --dry-run first
5. Apply to dev, staging, then production

Adding New Columns

1. Update model in src/backend/app/models/
2. Add column check and ADD COLUMN logic to sync script
3. Use IF NOT EXISTS patterns for safety
4. Test with --dry-run first
5. Apply to environments

Safety Checks Built-In

• ✅ Checks table exists before creating
• ✅ Checks column exists before adding
• ✅ Transaction safety (rollback on error)
• ✅ Dry-run mode to preview changes
• ✅ Color-coded output for easy reading
• ✅ Summary of all changes applied

Integration with CI/CD

Railway Deployment:

# In Procfile or deploy script
release: python scripts/sync-production-schema.py --apply

GitHub Actions:

- name: Sync Production Schema
  env:
    DATABASE_URL: ${{ secrets.DATABASE_URL }}
  run: python scripts/sync-production-schema.py --apply

ENFORCEMENT

• ❌ NEVER run alembic upgrade head in production
• ❌ NEVER manually execute SQL in production without sync script
• ❌ NEVER skip dry-run step for production changes
• ✅ ALWAYS use scripts/sync-production-schema.py for schema changes
• ✅ ALWAYS run --dry-run before --apply
• ✅ ALWAYS verify changes in dev/staging before production
• ✅ ALWAYS update documentation when adding new tables/columns

VIOLATION CONSEQUENCES

• Database schema drift between environments
• Failed deployments from migration conflicts
• Data loss from incorrect migrations
• Production downtime from schema errors
• Team confusion about database state

THIS IS A REQUIRED STANDARD. USE SCHEMA SYNC SCRIPT FOR ALL DATABASE CHANGES.

Reference Files

See references/sync-vs-alembic.md for detailed comparison of sync script vs Alembic migrations.

See references/workflow-examples.md for code examples of adding tables, columns, indexes, and handling complex migrations.

Run scripts/verify-sync-script.sh to validate that sync script exists and is properly configured.