Shipping Project Sync: Production Migrations, Prisma Pitfalls, and the Power of Manual SQL

The air still hums with that particular blend of relief and exhilaration. We've just pushed a significant new feature, Project Sync - Phase 1, to production. All 13 tasks, from schema design to frontend components, are complete and live on nyxcore.cloud. It's a huge win, but like any real-world deployment, it wasn't without its white-knuckle moments and invaluable lessons.

This isn't a marketing fluff piece; it's a raw, authentic peek into the trenches of shipping a feature that touches almost every layer of our stack, with a special focus on the database migration challenges we faced.

The Feature: Project Sync (Phase 1)

Our goal for Phase 1 was to enable users to select a specific GitHub repository branch for syncing. This sounds straightforward, but it required a full-stack overhaul:

• Database Schema: A new project_syncs table and associated columns to store sync configurations.
• Backend Services: Logic to handle branch selection, kick off sync processes, and manage their state.
• Real-time Updates: Leveraging Server-Sent Events (SSE) to push live status updates to the frontend as syncs progress.
• API Layer: Integrating new endpoints via tRPC for type-safe communication between frontend and backend.
• Frontend Components: New UI elements to display available branches, trigger syncs, and show their real-time status.

Seeing "ALL 13 TASKS COMPLETE" was a fantastic feeling. But the real test, as always, came with the production deployment.

The Production Deployment Dance: Migrations and Mayhem

Our application relies heavily on embeddings for various AI-driven features. These are critical pieces of data, and any database migration needs to treat them with extreme care. We had new tables and columns to add, and the process exposed some sharp edges in our deployment strategy.

Here's what went down, and more importantly, what we learned:

Lesson 1: prisma db push --accept-data-loss is NOT for Production

This is a critical, bold-faced takeaway. We've all been there: in a hurry during development, we use prisma db push --accept-data-loss to quickly sync our schema. It's incredibly convenient for rapid iteration.

The Pitfall: On production, this command is a landmine. It will drop and recreate tables, and if you have columns not explicitly defined in your current Prisma schema (like, say, an embedding column managed by a custom migration or extension), it will obliterate them.

We learned this the hard way (or rather, we re-learned it, confirming our existing caution). The result? Our precious embedding column would have been dropped. Thankfully, we caught this before it caused irreversible damage.

Actionable Takeaway: prisma db push is a development tool. NEVER run it on a production database. For production, always use prisma migrate deploy or, as we'll see next, manual SQL.

Lesson 2: Manual SQL Migration is Your Production Lifeline

Given the db push danger and the specific nature of our embedding column, a manual SQL migration was the only safe path forward for our production schema changes.

Why Manual SQL? It gives you granular control. You can ALTER TABLE to add columns, set defaults, or modify types without risking data loss on existing tables or columns not managed by Prisma directly.

Here's a simplified example of the kind of command we'd use:

-- For adding a new table
CREATE TABLE "public"."project_syncs" (
    "id" TEXT NOT NULL,
    "projectId" TEXT NOT NULL,
    "githubRepoUrl" TEXT NOT NULL,
    "githubBranch" TEXT NOT NULL,
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" TIMESTAMP(3) NOT NULL,
    CONSTRAINT "project_syncs_pkey" PRIMARY KEY ("id")
);

-- For adding a new column to an existing table
ALTER TABLE "public"."projects"
ADD COLUMN "syncStatus" TEXT NOT NULL DEFAULT 'IDLE';

Actionable Takeaway: For critical production schema changes, especially those touching sensitive data or custom column types, prepare and apply raw SQL scripts. Test them thoroughly in a staging environment.

Lesson 3: SSH Heredocs – A Tale of Escaping Woe

When applying these SQL commands remotely via SSH, our initial thought was to use heredocs for multi-line commands.

# This looks neat, but can be a nightmare with escaped quotes
ssh user@server <<EOF
    docker compose exec database psql -U postgres -d myapp_db -c "
        ALTER TABLE \"public\".\"projects\"
        ADD COLUMN \"syncStatus\" TEXT NOT NULL DEFAULT 'IDLE';
    "
EOF

The Pitfall: Escaping quotes within the heredoc, especially when nested within a psql -c command, quickly becomes a quoting nightmare. It's brittle, hard to debug, and prone to silent failures.

Actionable Takeaway: For remote execution of complex or multi-line commands via SSH, break them down into individual, simpler commands or pass them as a single line string to bash -c. Avoid complex heredoc escaping where possible. For SQL, save it to a file and execute the file.

# Much safer: pass a single SQL command or execute a file
ssh user@server "docker compose exec database psql -U postgres -d myapp_db -c 'ALTER TABLE \"public\".\"projects\" ADD COLUMN \"syncStatus\" TEXT NOT NULL DEFAULT ''IDLE'';"
# Or even better, if the SQL is in a file:
ssh user@server "cat /path/to/migration.sql | docker compose exec database psql -U postgres -d myapp_db"

Lesson 4: docker compose run --rm for Prisma Commands – Powerful, but Watch Your Quotes (Again)

Running Prisma commands within our Docker Compose setup is standard practice. docker compose run --rm backend npx prisma ... is a great way to execute one-off commands in the context of your application service.

The Pitfall: While effective, passing arguments to Prisma commands (or any command) can still lead to quote escaping challenges, especially if those arguments contain spaces or special characters.

Actionable Takeaway: Be mindful of how your shell interprets quotes when passing arguments to docker compose run. Test your commands locally first. For simple Prisma commands, it's usually fine, but complex ones might require careful wrapping or environment variables.

The Outcome: 382 Embeddings, Intact

Despite the challenges, the deployment was a success.

• Our production environment at nyxcore.cloud is fully updated.
• The project_syncs table and all new columns are live.
• Crucially, after a careful re-backfill process (a safeguard we had in place), all 382 embeddings are intact! This was a major relief and validated our cautious approach to database migrations.

What's Next?

The work isn't over. We're already looking at the immediate next steps:

1. Real-world Testing: Rigorously testing the new sync feature with various GitHub repositories and branches to ensure robustness.
2. Phase 2 Planning: Kicking off planning for Phase 2, which will involve deeper code analysis and automated documentation regeneration based on the selected branch. More AI, more embeddings, more fun!
3. Security Review: Considering adding Row-Level Security (RLS) SQL for the new project_syncs table to enforce data isolation and security best practices.

Shipping features is always a journey of learning. This deployment reinforced the importance of respecting your production database, understanding your tools deeply, and always having a robust fallback plan. Here's to the next challenge!

{
  "thingsDone": [
    "Full Project Sync feature deployed (schema, service, SSE, tRPC, frontend components)",
    "Production schema applied manually (safe migration)",
    "Application rebuilt and deployed to production",
    "Backfill endpoint deployed earlier in session"
  ],
  "pains": [
    "Using `prisma db push --accept-data-loss` on production (drops embedding column)",
    "Complexity of heredoc with escaped quotes via SSH for remote command execution",
    "Quote escaping trickiness with `docker compose run --rm` for Prisma commands"
  ],
  "successes": [
    "ALL 13 TASKS COMPLETE",
    "Production fully deployed at nyxcore.cloud",
    "All 382 embeddings intact after re-backfill",
    "New project_syncs table and columns are live"
  ],
  "techStack": [
    "Prisma",
    "Docker Compose",
    "PostgreSQL",
    "Server-Sent Events (SSE)",
    "tRPC",
    "Next.js (implied by full-stack frontend)",
    "Node.js (implied by Prisma/tRPC backend)",
    "GitHub (integration)"
  ]
}