← nyxcore-systems
5 min read

Beyond the Schema: Crafting Robust E2E Tests and Safe PGVector Migrations

Join us as we navigate the complexities of implementing end-to-end tests for sensitive superadmin flows and devise a bulletproof strategy for `pgvector` database migrations with Prisma.

e2e-testingplaywrightprismapgvectordatabase-migrationsmulti-tenancynextjstrpcsuperadminlessons-learned

Just wrapped up a crucial design phase for an upcoming development sprint, and the insights are too valuable not to share. Our mission: fortify our superadmin flows with robust End-to-End (E2E) tests, ensure seamless multi-tenancy user experience, and, perhaps most critically, engineer a bulletproof database migration strategy for our pgvector columns.

The design is now with the user for final review, but the journey to get here was packed with explorations, a few "aha!" moments, and some critical lessons learned. Let's dive in.

Laying the Foundation: E2E Testing for Superadmin Flows

Our primary goal was to ensure that the superadmin experience, including the critical tenant switcher UX, is rock-solid. This meant a deep dive into our existing E2E setup and how to extend it for complex, multi-tenant scenarios.

E2E Exploration & Strategy

We use Playwright for our E2E tests, and the existing structure gave us a great starting point. We explored:

  • Playwright Configuration: Understanding how our playwright.config.ts is set up, especially for handling different environments.
  • Authentication Helper: The tests/e2e/helpers/auth.ts file is a gem. It allows us to programmatically inject a JWT cookie into the browser context, ensuring our tests start with a consistent, authenticated user state. For superadmin tests, this will be crucial for simulating different roles and tenant access.
  • Existing Test Patterns: Reviewing our current 4 spec files provided blueprints for interacting with the UI and asserting outcomes.

Deconstructing Multi-Tenancy & Superadmin Access

To effectively test superadmin flows, we needed to understand the underlying architecture:

  • Tenant Switcher Component: src/components/layout/tenant-switcher.tsx is the heart of our multi-tenancy UX. Testing this means verifying not just the UI interaction, but also the data isolation and correct context switching.
  • tRPC Routers: src/server/trpc/routers/superadmin.ts and src/server/trpc/routers/teams.ts expose the server-side logic for managing users, tenants, and teams. Our E2E tests will need to interact with or mock these endpoints.
  • Authentication & Authorization: The auth.ts JWT callback logic is vital. It's where user roles and tenantId are embedded into the token, dictating access. Our E2E auth helper will mimic this to simulate superadmin privileges.

Understanding how these pieces interlocked was essential to design tests that truly reflect real-world superadmin interactions and ensure data integrity across tenants.

The PGVector Migration Conundrum: A Critical Challenge

While powerful, our current database setup for pgvector introduced a unique challenge when it came to database migrations. We rely on pgvector for storing embedding vectors in tables like workflow_insights and document_chunks, and we use HNSW indexes for efficient similarity search.

The Problem: Prisma's db push and Unsupported Types

Our current setup doesn't use Prisma's migration engine (i.e., no migrations directory). Instead, we use prisma db push during development, and apply custom rls.sql scripts to add vector columns and HNSW indexes directly.

The critical issue arises when prisma db push is run on production. It attempts to DROP our embedding vector(1536) columns on workflow_insights and document_chunks because Prisma's introspection flags them as Unsupported types in the schema.

sql
-- Example of what Prisma *wants* to do
ALTER TABLE "workflow_insights" DROP COLUMN "embedding";

This is a non-negotiable red flag. Running prisma db push --accept-data-loss on production would literally obliterate crucial vector data, leading to catastrophic data loss.

The Solution: Engineering a Safe Migration Script

To circumvent this, we've designed a custom, pgvector-safe migration script: scripts/db-migrate-safe.sh. The plan is:

  1. Generate Diff SQL: Use prisma migrate diff to generate the raw SQL needed to bring the database schema up to date, based on the Prisma schema.
  2. Filter Dangerous Operations: The script will then parse this generated SQL to identify and filter out any DROP COLUMN statements that target our embedding vector columns.
  3. Apply Safe Changes: The filtered SQL will be applied to the production database using psql.
  4. Re-run RLS & Indexes: Finally, we'll re-run our rls.sql script to ensure all pgvector columns and HNSW indexes (which are not managed by Prisma's schema) are correctly in place.

This approach ensures we can evolve our Prisma schema safely without risking our valuable vector data.

A Quick Reminder: Edge Runtime Constraints

During our review, a critical lesson from a previous session (captured in commit 54933e0) resurfaced regarding our src/server/auth.ts file. This file runs in the Edge Runtime – a lightweight, performant environment often used in serverless functions.

The key takeaway: NEVER import Node.js-specific modules (like crypto, fs, path) into files intended for the Edge Runtime. This is because the Edge Runtime doesn't have access to the full Node.js API. While a quick win for performance, it's a sharp reminder to be mindful of module imports in these specific contexts.

The Path Forward: Bringing Design to Life

With our design approved by the user and potential pitfalls carefully charted, our path forward is clear. We're ready to transition from planning to implementation.

Immediate Next Steps:

  1. Get User Approval: Final sign-off on the 3-task design proposal.
  2. Develop Superadmin E2E Tests: Create tests/e2e/superadmin.spec.ts, implementing E2E tests for superadmin flows, leveraging tRPC mocking where necessary.
  3. Extend Auth Helper: Enhance tests/e2e/helpers/auth.ts with an injectSuperAdminCookie() helper to streamline superadmin test setup.
  4. Implement Safe Migration Script: Create scripts/db-migrate-safe.sh to handle pgvector-safe migrations using prisma migrate diff.
  5. Verify E2E Tests: Run npm run test:e2e to ensure all new and existing tests pass.
  6. Validate Tenant Switcher UX: Confirm that the tenant switcher's functionality and data isolation are thoroughly covered by the new superadmin E2E tests.

This session reinforced the idea that modern web development isn't just about shipping features; it's about shipping them securely, reliably, and with an eye towards future maintainability and data integrity. Onwards to implementation!