Conquering Multi-Tenant E2E Tests and Crafting Bulletproof Database Migrations

Building robust multi-tenant applications requires meticulous attention to detail, especially when it comes to user isolation and data integrity. In a recent development session, our focus was squarely on fortifying these critical areas: implementing end-to-end (E2E) tests for our superadmin and tenant-switching flows, and creating a production-grade, pgvector-safe database migration script.

I'm thrilled to report that all objectives were met. We now have a comprehensive suite of 72 passing E2E tests, a squeaky-clean typecheck, and a migration script ready to tackle schema changes safely. Let's dive into the how and the what, including some valuable lessons learned along the way.

Fortifying Multi-Tenant Flows with Comprehensive E2E Tests

Multi-tenancy introduces a unique set of testing challenges: ensuring data isolation, verifying correct role-based access, and confirming seamless switching between tenant contexts. Our goal was to build E2E tests that simulate real-world user journeys for both superadmins and regular tenant users.

The Testing Toolkit

We extended our tests/e2e/helpers/auth.ts with two crucial helpers:

• injectSuperAdminCookie(): Allows Playwright to instantly log in as a superadmin, bypassing the login UI.
• injectTenantCookie(): Similarly, logs in a regular user into a specific tenant.

These helpers are game-changers, enabling us to set up complex testing scenarios quickly and reliably without repetitive UI interactions.

The superadmin.spec.ts Deep Dive

The core of our E2E work landed in a new file, tests/e2e/superadmin.spec.ts, which now houses 16 distinct test cases. With Playwright's ability to run tests across different browsers and devices (e.g., Chromium desktop and mobile), this translates to a whopping 32 total tests covering:

• Tenant Switcher Functionality: Verifying its visibility, the ability to switch tenants, active tenant checkmarks, and the proper display of "Other tenants" for non-members.
• Superadmin Page Access and Rendering: Ensuring all three superadmin tabs (Tenants, Users, Invitations) render correctly and that non-superadmins are properly denied access.
• Tenant Management: Testing the listing of tenants, expanding members within a tenant, and the full create tenant dialog and mutation flow.
• User Management: Verifying the user list, including the crucial superadmin badge for designated users.
• Invitation Flows: Testing the listing of invitations (and the empty state), as well as the complete invite dialog, fill, and submit mutation.
• Critical Data Isolation: A key set of tests confirming that switching tenants correctly triggers a data mutation, and that non-member tenants correctly display a "join" option, reinforcing that users only see data relevant to their active tenant.

This extensive suite significantly boosts our confidence in the multi-tenant core of our application, ensuring critical features behave as expected under various user contexts. A small but important addition was data-testid="tenant-switcher" to our src/components/layout/tenant-switcher.tsx, making the switcher a robust target for tests.

The Art of the Safe Database Migration (Especially with Vector Data)

Database migrations are always a delicate operation, but they become even more critical when dealing with specialized data types like pgvector embeddings. An accidental DROP COLUMN on an embedding or searchVector column could lead to catastrophic data loss. Our solution: scripts/db-migrate-safe.sh.

A Bulletproof Migration Script

This new executable script leverages prisma migrate diff to generate SQL differences between our current database schema and our Prisma schema. Here's how it ensures safety and reliability:

1. Generates SQL Diff: It first creates a raw SQL diff, highlighting all proposed changes.
2. Auto-Filters Critical DROP COLUMN Operations: This is the secret sauce. The script intelligently scans the generated SQL and automatically removes any DROP COLUMN statements targeting embedding and searchVector columns. This prevents accidental loss of valuable vector data during schema evolution.
3. Review and Apply: Before any changes are made, the filtered SQL is displayed for manual review. Only after explicit confirmation is the SQL applied to the database via psql.
4. Re-runs RLS: After applying schema changes, it automatically re-runs rls.sql to ensure Row-Level Security policies are up-to-date and correctly applied to the new schema.
5. Flexible Execution: The script supports --dry-run to inspect changes without applying them, and --apply for confident execution.

This script provides immense peace of mind, allowing us to evolve our schema with confidence, knowing our vector data is protected from common migration pitfalls.

Lessons Learned from the Trenches

No development session is complete without encountering a few snags. These "pain points" often transform into the most valuable lessons.

Playwright Locators: Precision is Key

The Challenge: When trying to locate elements like getByText('nyxCore'), getByText('Clarait'), or getByText('superadmin'), Playwright's strict mode kept failing due to multiple matching elements. For instance, "superadmin" might appear in a sidebar navigation item, a page heading, and within card content on the same page.

The Solution: This forced us to adopt more precise and robust locator strategies:

• Scope Locators: We often scope searches to a specific area of the page, like page.locator("main"), to narrow down the context.
• Exact Matching: Using { exact: true } ensures case-sensitive and exact text matches, differentiating "Superadmin" from "superadmin".
• Semantic Roles: Leveraging getByRole("heading", { name: 'Superadmin' }) allows us to target elements based on their semantic meaning, which is more stable than relying purely on visible text that might appear elsewhere.

Insight: Playwright's strictness is a feature, not a bug. It pushes developers to write more resilient tests that are less prone to breaking when minor UI text changes occur.

Targeting UI Components: Avoid Implementation Details

The Challenge: We initially tried to select shadcn Card components using a CSS selector like locator("[class*='CardContent']"). This failed because shadcn components render Tailwind CSS classes, not directly matching component names. The selector found nothing.

The Solution: Instead of relying on internal implementation details (like generated CSS classes), we pivoted to semantic selectors that reflect user interaction. For instance, page.locator("main").getByRole("button", { name: /Invite/i }).first() successfully targeted an "Invite" button.

Insight: When writing E2E tests, it's best to test against what the user sees and interacts with. Prioritize semantic roles (getByRole), accessible names, or explicit data-testid attributes over brittle CSS class selectors that can change with UI library updates.

TypeScript's typeof: Knowing Its Boundaries

The Challenge: We attempted to use typeof [TENANT_A, TENANT_B] directly within a TypeScript function parameter type definition to infer an array type. This resulted in a TS1109 error, indicating that typeof doesn't work on inline array expressions in this context.

The Solution: The workaround involved changing the type to typeof TENANT_A[] (assuming TENANT_A is a defined type or constant) and handling manual defaults for function parameters instead of relying on destructuring with the problematic typeof expression.

Insight: While TypeScript's typeof operator is powerful for deriving types from existing variables, it has limitations, particularly with inline expressions in type positions. Understanding these nuances helps in writing more robust and type-safe code.

Current State and Next Steps

All changes from this session are currently uncommitted, poised for a git add and commit. The E2E suite now boasts 72 passing tests, a significant leap from the 40 tests before this session. The dev server is humming along on port 3000, ready for further action.

Our immediate next steps include:

1. Commit all changes: E2E tests, migration script, and the small data-testid addition.
2. Local Migration Test: Thoroughly test the scripts/db-migrate-safe.sh script against a