Share test database infrastructure with Playwright E2E tests

[Darkkal]

Problem

Several Playwright E2E tests are forced to skip when running in CI because the database is empty. These tests guard against missing data with patterns like:

// Wait for items — skip if no data (CI has empty DB)
try {
  await expect(page.locator('img[class*="media"]').first()).toBeVisible({ timeout: 5000 });
} catch {
  test.skip(true, "No gallery items to test");
  return;
}

Affected specs

Spec file	Skipped tests
gallery.spec.ts	Grid/lightbox, scroll mode, infinite scroll
timeline.spec.ts	Lightbox, scroll mode, infinite scroll, post condensing
statistics.spec.ts	Ranking card click navigation
autocomplete.spec.ts	Context-aware suggestions (2 tests)

This means a significant portion of E2E coverage only runs locally against a developer's populated database, and CI is effectively testing empty-state rendering only.

Context

Issue #110 introduced unit/integration testing with an in-memory SQLite database that:

1. Dynamically replays Drizzle migrations from drizzle/meta/_journal.json to construct the schema
2. Provides seed factories (tests/unit/helpers/seed.ts) for creating typed mock data (posts, tags, media items, playlists, users, sources)
3. Runs in under a second with zero external dependencies

This infrastructure currently lives in tests/unit/helpers/ and is only used by Vitest. The same migration runner and seed factories could be reused to pre-seed a file-backed SQLite database for Playwright tests.

Proposed Approach

1. Parameterize setupTestDb()

The current helper in tests/unit/helpers/db.ts hardcodes file::memory:. Refactor it to accept an optional URL:

export function setupTestDb(options?: { url?: string }) {
  const client = createClient({ url: options?.url ?? 'file::memory:' });
  // ... rest unchanged
}

2. Add Playwright globalSetup

Create a tests/e2e-setup.ts (or similar) that:

1. Creates a temp directory for the test database
2. Calls setupTestDb({ url: 'file:/tmp/pw-test-data/sqlite.db' })
3. Runs migrations
4. Seeds with fixture data that satisfies all currently-skipping tests (posts with media items, tags, users, etc.)
5. Exports DATA_DIR so the Next.js server boots against the seeded database

3. Wire into playwright.config.ts

export default defineConfig({
  globalSetup: './tests/e2e-setup.ts',
  webServer: {
    command: 'npm run dev',
    env: { DATA_DIR: '/tmp/pw-test-data' },
    // ...
  },
});

4. Remove skip guards

Replace all test.skip(true, "No gallery items...") patterns with assertions against the known seed data. Tests become deterministic rather than conditional.

5. Consider test media files

Posts need associated media files to render in the UI. Options:

• Include a small set of test images in the repo (e.g., tests/fixtures/media/)
• Use placeholder/broken-image-tolerant assertions where visual fidelity isn't the point of the test
• Generate tiny synthetic images in globalSetup

Benefits

• Full E2E coverage in CI — no more skipped tests on empty databases
• Deterministic assertions — tests assert against known data instead of "whatever happens to be in the dev DB"
• No Docker dependency — eliminates compose.test.yaml requirement for test runs
• Single source of truth — migration runner and seed factories are shared between unit and E2E tests

Out of Scope

• Modifying the existing unit test infrastructure (only parameterizing the URL)
• React component testing (Testing Library) — separate concern
• Performance/load testing

Related

• Add unit/integration testing for server actions and repository layer #110 — Unit/integration testing setup (established the shared infrastructure)