# fix(db): bootstrap migrations on local-tier gateway startup

## Problem

Fresh `mosaic gateway install` (npm-installed) leaves the gateway DB schema empty:

```
relation "users" does not exist
```

Sign-in 500s, `auth users create` says "Not signed in", `admin/bootstrap setup`
also fails — every entry point queries `users` before doing anything else.

## Scope

This PR fixes the **local (PGlite) tier** end-to-end. The postgres-tier path
has additional pre-existing bugs (see "Known issues, out of scope" below) and
needs a separate change with real Postgres validation.

## Root causes addressed (5 stacked bugs on the local-tier path)

1. **`packages/db/package.json` `files: ["dist"]`** — the `drizzle/` SQL
   migrations folder is excluded from the published tarball. Even if a
   migrate runner existed, it would have nothing to apply.

2. **`packages/db/src/migrate.ts`** only supports `postgres-js`. Local-tier
   gateways use embedded PGlite, which can't be reached over a postgres wire
   protocol — so `runMigrations()` is unusable for the local tier.

3. **`apps/gateway/src/database/database.module.ts`** never invokes
   migrations at startup. The module creates the DB handle and storage
   adapter, but no consumer calls `.migrate()` on either. `mosaic storage
migrate` CLI even claims "pglite runs schema setup automatically on first
   connection via `adapter.migrate()`" — but `adapter.migrate()` is only
   called by tests, never at runtime.

4. **`createPgliteDb` does not load the pgvector extension.** Migration 0001
   declares `CREATE EXTENSION IF NOT EXISTS vector;` for the
   `insights.embedding` column. Bare PGlite has no pgvector — the migration
   fails on extension control file lookup.

5. **Drizzle's PG migrator wraps every migration in one outer transaction.**
   Migration 0009 does `ALTER TYPE grant_status ADD VALUE 'pending'` and then
   `ALTER TABLE federation_grants ALTER COLUMN status SET DEFAULT 'pending'`.
   Postgres' `check_safe_enum_use` rejects the second statement because the
   new enum value isn't committed yet. Splitting the migration into two
   files doesn't help — drizzle batches all migrations into one outer tx.

## Fix

- `packages/db/package.json` — ship `drizzle/` in `files`.
- `packages/db/src/client-pglite.ts` — load `@electric-sql/pglite/vector`.
- `packages/db/src/migrate.ts` — add `runPgliteMigrations(handle)`. Walks the
  Drizzle journal and runs each statement-breakpoint chunk through PGlite's
  `client.exec()` (Simple Query protocol → autocommit per statement). Writes
  to the standard `drizzle.__drizzle_migrations` ledger so the result is
  interoperable with `runMigrations()` on a postgres-backed deployment.
  Per-statement try/catch surfaces which statement of which migration failed
  and the ledger row is only written on full success.
- `packages/db/src/index.ts` — re-export.
- `apps/gateway/src/database/database.module.ts` — implement `OnModuleInit`:
  - Local tier → `runPgliteMigrations(handle)`, then `storageAdapter.migrate()`
    (the local storage adapter has its own kv tables in a separate PGlite dir).
  - Postgres tier → `storageAdapter.migrate()` only, since
    `PostgresAdapter.migrate()` already calls `runMigrations(url)` against
    the same DATABASE_URL — we deliberately don't double-call.

  NestJS awaits `onModuleInit` before `app.listen()`, so DB-dependent modules
  see a populated schema before any HTTP traffic is accepted.

- `packages/storage/src/test-utils/pglite-with-vector.ts` — **deleted**.
  The "intentionally not exported" rationale is moot now that migration 0001
  forces pgvector load anyway. `migrate-tier.integration.test.ts` switched
  to `createPgliteDb` + `runPgliteMigrations` from `@mosaicstack/db`.

## Tests

`packages/db/src/migrate.test.ts`:

- Verifies `runPgliteMigrations` creates the BetterAuth tables (the original
  failure mode).
- Idempotence (transitively re-runs migration 0009).
- Partial-failure: pre-creates a conflicting `users` table, asserts the
  thrown error includes statement context (`hash=… statement #N failed`)
  and that no ledger row was written.

## QA evidence

End-to-end on a fresh PGlite install:

- `[DatabaseModule] Applying PGlite schema migrations...` then
  `Initializing storage adapter (pglite)...` in startup log.
- `GET /api/bootstrap/status` → `{"needsSetup":true}` HTTP 200 (was 500
  with `relation "users" does not exist`).
- `POST /api/bootstrap/setup` with empty body → HTTP 400 with Zod
  validation error (was 500), confirming the request reached the
  validator past the table-existence check.

## Known issues, out of scope (file separately)

- **Postgres-tier first install is still broken.** `runMigrations()` uses
  Drizzle's `migratePostgres`, which has the same outer-transaction problem
  as PGlite's migrator. A fresh standalone-tier install would also fail at
  migration 0009. Inline TODO in `migrate.ts:31-35` flags this. Fixing it
  needs either (a) a shared per-statement loop reused for both drivers, or
  (b) splitting migration 0009.
- **`drizzle/meta/_journal.json` has 0009 ordered before 0008** (`when`
  values `1745280000000` < `1776822435828`). `migratePostgres` skips by
  `created_at < folderMillis`, so on a postgres deployment that already
  applied 0008, 0009 would be skipped forever. Our hash-based skip in the
  PGlite path sidesteps this.
- **No advisory lock around the migration loop.** Two gateway processes
  pointed at the same DATABASE_URL would race. PGlite is single-process by
  file lock so the local tier is fine; postgres-tier deployments should add
  `pg_advisory_lock(<deterministic-id>)` around the loop in a follow-up.
- **`mosaic storage migrate` CLI message is misleading** — it claims
  "automatic on first connection via adapter.migrate()" but the adapter
  doesn't self-migrate. With this PR the gateway invokes it explicitly, but
  the CLI message could still be tightened.
- **Crash mid-migration leaves a partial-state PGlite DB without a ledger
  row.** Detected loudly on next boot (the replay errors on "already
  exists"), but recovery is manual (drop the partially-applied objects or
  insert the migration hash into `drizzle.__drizzle_migrations`). A robust
  fix would add a "started_at" column to a sidecar table to detect
  half-applied state and refuse to start with actionable guidance.
