Charting the Course: Fortifying Multi-Tenant Apps with E2E Tests and Bulletproof Migrations

Just wrapped up a highly focused development session, and it feels great to see a significant chunk of critical work come together! The past few hours were dedicated to two key areas: implementing robust End-to-End (E2E) tests for our application's superadmin and multi-tenant switching functionalities, and engineering a bulletproof database migration script designed to handle pgvector columns safely in production.

The session concluded with all objectives met: 72 E2E tests passing, a clean TypeScript typecheck, and a production-ready migration script. Let's break down the journey, the tools, and the valuable lessons learned.

The Mission: Guarding the Gates (and Data!)

Our primary goals for this session were twofold, each addressing a critical aspect of application stability and data integrity:

1. E2E Testing for Superadmin & Tenant-Switching Flows: In a multi-tenant application, the ability for a superadmin to manage tenants and for users to switch between their accessible tenants is paramount. These are high-stakes features, and manual testing simply isn't enough. We needed automated E2E tests to ensure these critical paths are always functional and secure.
2. pgvector-Safe Production Migration Script: Database schema changes are a constant in development. However, dropping columns, especially those containing valuable pgvector embeddings or searchVector data, can be disastrous if not handled carefully. Regenerating these vectors can be computationally expensive and time-consuming. We needed a migration script that intelligently filtered out potentially destructive DROP COLUMN statements for these specific columns, ensuring data safety by default.

The Toolkit: Reinforcing Our Foundations

To achieve these goals, we extended our existing test infrastructure and built a new utility.

Robust E2E Test Enhancements with Playwright

Our E2E suite, powered by Playwright, got a significant upgrade. We focused on making tests reliable and easy to write for complex authentication scenarios.

• Auth Helpers: We extended tests/e2e/helpers/auth.ts with two crucial helpers:
  • injectSuperAdminCookie(): This allows our tests to bypass the login flow and directly inject a superadmin session cookie. This is invaluable for quickly spinning up tests that require superadmin privileges without repeatedly logging in.
  • injectTenantCookie(): Similarly, this helper enables tests to assume the context of a specific tenant, facilitating comprehensive testing of tenant-specific features and data isolation.
• Comprehensive Superadmin Test Suite: A new file, tests/e2e/superadmin.spec.ts, was born, adding 16 new test cases (which translates to 32 total tests when run across Chromium and mobile viewports!). These tests cover:
  • Tenant Switcher: Verifying its visibility, the ability to switch tenants, the active tenant checkmark, and the "Other tenants" section for joining new tenants.
  • Superadmin Page: Ensuring the three main tabs (Tenants, Users, Invitations) render correctly, and crucially, that access is denied for non-superadmin users.
  • Tenant Management: Listing tenants, expanding members, and testing the create tenant dialog and mutation.
  • User Management: Listing users, confirming the superadmin badge for appropriate accounts.
  • Invitation Management: Listing invitations and handling empty states.
  • Invite Dialog: Testing the full flow of opening, filling, and submitting an invitation.
  • Data Isolation: Confirming that switching tenants triggers the correct mutations and that non-member tenants correctly display a "join" prompt.
• Enhanced Selectors: To make our tests more resilient to UI changes, we added data-testid="tenant-switcher" to src/components/layout/tenant-switcher.tsx. This best practice ensures our Playwright locators are stable and less prone to breaking when CSS classes or text content changes.

The Bulletproof pgvector-Safe Migration Script

This was a critical piece of infrastructure, ensuring our database remains intact during schema updates. We created scripts/db-migrate-safe.sh:

# Simplified representation of the script logic
#!/bin/bash

# ... initial setup and flag parsing ...

# Generate SQL diff
SQL_DIFF=$(npx prisma migrate diff --from-url "$DATABASE_URL" --to-schema-datamodel prisma/schema.prisma --script)

# Auto-filter DROP COLUMN for pgvector-specific columns
FILTERED_SQL=$(echo "$SQL_DIFF" | grep -v -E 'DROP COLUMN ("?embedding"?|"?"?searchVector"?)')

echo "--- Generated SQL (filtered for pgvector safety) ---"
echo "$FILTERED_SQL"
echo "---------------------------------------------------"

if [ "$DRY_RUN" = true ]; then
  echo "Dry run complete. No changes applied."
elif [ "$APPLY" = true ]; then
  echo "Applying filtered migrations..."
  echo "$FILTERED_SQL" | psql "$DATABASE_URL"
  echo "Migrations applied. Re-running RLS setup..."
  psql "$DATABASE_URL" -f scripts/rls.sql
  echo "RLS re-applied."
else
  echo "Use --dry-run to preview or --apply to execute."
fi

This script is a game-changer for our deployment pipeline:

• It leverages prisma migrate diff to generate a SQL diff between the current database state and our prisma/schema.prisma file.
• Crucially, it then intelligently filters out any DROP COLUMN statements specifically targeting embedding or searchVector columns. This prevents accidental data loss for our pgvector and full-text search fields.
• It provides a clear output of the filtered SQL for review, supporting both --dry-run (to preview changes) and --apply (to execute them).
• Finally, it re-runs our rls.sql script to ensure Row-Level Security policies are correctly applied after schema changes.

Navigating the Treacherous Waters: Lessons Learned

No development session is without its challenges. Here are a few "gotchas" we encountered and how we overcame them, offering valuable insights for anyone working with Playwright or TypeScript.

Playwright Locators & Strict Mode Specificity

• The Problem: We initially tried using simple text selectors like getByText('nyxCore'), getByText('Clarait'), or getByText('superadmin') to locate elements.
• The Failure: Playwright's strict mode kicked in, complaining about multiple matching elements. Our sidebar navigation items, page headings, and various card contents often contained overlapping text, leading to ambiguity.
• The Workaround/Lesson: We learned to be far more specific.
  • Scope locators: Always try to narrow down the search context, e.g., page.locator("main").getByText('nyxCore') to only search within the main content area.
  • Exact matching: Use { exact: true } for case-sensitive and precise text matches.
  • Role-based selectors: For headings, getByRole("heading", { name: 'My Heading' }) is much more robust than getByText('My Heading'), as it targets the semantic role.

Shadcn/UI Component Selection

• The Problem: We attempted to select Shadcn UI components using class names like locator("[class*='CardContent']").
• The Failure: Shadcn components primarily render Tailwind CSS utility classes, not component-specific class names. Our selector found nothing.
• The Workaround/Lesson: Don't rely on internal implementation details (like Tailwind classes). Instead, focus on user-visible attributes and accessible roles. We successfully used page.locator("main").getByRole("button", { name: /Invite/i }).first() to find an invite button, which is far more stable.

TypeScript Type Inference with Inline Arrays

• The Problem: We tried to infer a type from an inline array expression in a function parameter, like typeof [TENANT_A, TENANT_B].
• The Failure: TypeScript threw TS1109, indicating that typeof cannot be used on inline array expressions in this context.
• The Workaround/Lesson: We adjusted the type to typeof TENANT_A[] (assuming TENANT_A is a representative type for the array elements) and handled manual defaults instead of relying on destructuring with the problematic typeof expression. This highlights a subtle but important nuance in TypeScript's type inference capabilities.

The Outcome & What's Next

This session was a huge success. We've significantly boosted our application's test coverage and fortified our deployment process.

• All changes are ready to be committed.
• Our E2E suite now boasts 72 passing tests, up from 40 before this session, providing much greater confidence in our superadmin and multi-tenant features.
• The db-migrate-safe.sh script is a crucial addition to our ops toolkit.

Our immediate next steps are clear:

1. Commit all changes to source control.
2. Thoroughly test scripts/db-migrate-safe.sh against a local development database to ensure its end-to-end functionality.
3. Apply any pending schema changes to production using our new safe migration script.
4. Rotate critical secrets (OpenAI, Anthropic, JWT, encryption, DB password) for enhanced security.
5. Submit nyxcore.cloud for Google Safe Browsing review.

It's incredibly satisfying to build out these foundational pieces that not only improve our development velocity but also ensure the reliability and security of our product. Onwards to the next challenge!