mosaicstack/stack
16
0
Fork 1
Code Issues 335 Pull Requests 15 Actions Packages Projects Releases 15 Wiki Activity

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

Merged
jason.woltje merged 1 commits from fix/db-bootstrap-migrations into main 2026-05-04 22:13:15 +00:00
Conversation 0 Commits 1 Files Changed 9
+351 -72
jason.woltje commented 2026-05-04 22:12:58 +00:00
Owner
Copy Link
Copy Source

Summary

Fresh mosaic gateway install (npm) leaves the gateway DB schema empty — sign-in 500s with relation "users" does not exist. Five stacked bugs on the local (PGlite) tier:

  1. packages/db/package.json files: ["dist"] excluded the drizzle/ SQL migrations from the published tarball.
  2. runMigrations() only supports postgres-js — unusable for embedded PGlite.
  3. apps/gateway/src/database/database.module.ts never invoked migrations at startup.
  4. createPgliteDb didn't load pgvector, so migration 0001's CREATE EXTENSION vector failed.
  5. Drizzle's PG migrator wraps every migration in one outer transaction, which trips Postgres' check_safe_enum_use on migration 0009 (ALTER TYPE ADD VALUE 'pending' → SET DEFAULT 'pending' in the same tx).

Changes

  • Ship drizzle/ in the published tarball.
  • createPgliteDb loads @electric-sql/pglite/vector.
  • New runPgliteMigrations(handle) walks the Drizzle journal and runs each statement-breakpoint chunk through PGlite's client.exec() (autocommit per statement). Records into drizzle.__drizzle_migrations for interop with the postgres-js path. Per-statement try/catch surfaces which statement of which migration failed.
  • DatabaseModule runs migrations in OnModuleInit before app.listen(). Local tier: explicit runPgliteMigrations then storageAdapter.migrate(). Postgres tier: just storageAdapter.migrate(), which already calls runMigrations(url) internally — no double-call.
  • Removed packages/storage/src/test-utils/pglite-with-vector.ts. The "intentionally not exported" rationale is moot now that migration 0001 forces pgvector load anyway. The integration test uses createPgliteDb + runPgliteMigrations from @mosaicstack/db.

Tests

packages/db/src/migrate.test.ts — BetterAuth tables exist after migrate; idempotent (re-runs 0009); partial-failure surfaces statement-level context and leaves no ledger row.

QA

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 reaches Zod validator (was 500), confirming the request reached past the table-existence check.

Scope

This PR fixes the local (PGlite) tier end-to-end. Postgres-tier first-install still has:

  • The same outer-transaction problem in runMigrations().
  • A pre-existing journal ordering bug — 0009's when (1745280000000) < 0008's (1776822435828), so migratePostgres skips by created_at < folderMillis and would skip 0009 forever after 0008 lands.

Both flagged inline in migrate.ts:31-35 and in the design doc at scratchpads/fix-db-bootstrap-migrations.md. Needs a separate change with real-Postgres validation.

Test plan

  • Pull the branch and rebuild — pnpm --filter @mosaicstack/gateway... build
  • Wipe ~/.config/mosaic/gateway/pglite/ to simulate fresh install
  • Restart gateway — log should show "Applying PGlite schema migrations..."
  • curl http://localhost:14242/api/bootstrap/status → expect {"needsSetup":true} HTTP 200
  • Run mosaic auth users create interactive flow and confirm user is created

🤖 Generated with Claude Code

## Summary Fresh `mosaic gateway install` (npm) leaves the gateway DB schema empty — sign-in 500s with `relation "users" does not exist`. Five stacked bugs on the local (PGlite) tier: 1. `packages/db/package.json` `files: ["dist"]` excluded the `drizzle/` SQL migrations from the published tarball. 2. `runMigrations()` only supports postgres-js — unusable for embedded PGlite. 3. `apps/gateway/src/database/database.module.ts` never invoked migrations at startup. 4. `createPgliteDb` didn't load pgvector, so migration 0001's `CREATE EXTENSION vector` failed. 5. Drizzle's PG migrator wraps every migration in one outer transaction, which trips Postgres' `check_safe_enum_use` on migration 0009 (`ALTER TYPE ADD VALUE 'pending'` → `SET DEFAULT 'pending'` in the same tx). ## Changes - Ship `drizzle/` in the published tarball. - `createPgliteDb` loads `@electric-sql/pglite/vector`. - New `runPgliteMigrations(handle)` walks the Drizzle journal and runs each statement-breakpoint chunk through PGlite's `client.exec()` (autocommit per statement). Records into `drizzle.__drizzle_migrations` for interop with the postgres-js path. Per-statement try/catch surfaces which statement of which migration failed. - `DatabaseModule` runs migrations in `OnModuleInit` before `app.listen()`. Local tier: explicit `runPgliteMigrations` then `storageAdapter.migrate()`. Postgres tier: just `storageAdapter.migrate()`, which already calls `runMigrations(url)` internally — no double-call. - Removed `packages/storage/src/test-utils/pglite-with-vector.ts`. The "intentionally not exported" rationale is moot now that migration 0001 forces pgvector load anyway. The integration test uses `createPgliteDb` + `runPgliteMigrations` from `@mosaicstack/db`. ## Tests `packages/db/src/migrate.test.ts` — BetterAuth tables exist after migrate; idempotent (re-runs 0009); partial-failure surfaces statement-level context and leaves no ledger row. ## QA 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` reaches Zod validator (was 500), confirming the request reached past the table-existence check. ## Scope This PR fixes the **local (PGlite) tier** end-to-end. Postgres-tier first-install still has: - The same outer-transaction problem in `runMigrations()`. - A pre-existing journal ordering bug — 0009's `when` (1745280000000) < 0008's (1776822435828), so `migratePostgres` skips by `created_at < folderMillis` and would skip 0009 forever after 0008 lands. Both flagged inline in `migrate.ts:31-35` and in the design doc at `scratchpads/fix-db-bootstrap-migrations.md`. Needs a separate change with real-Postgres validation. ## Test plan - [ ] Pull the branch and rebuild — `pnpm --filter @mosaicstack/gateway... build` - [ ] Wipe `~/.config/mosaic/gateway/pglite/` to simulate fresh install - [ ] Restart gateway — log should show "Applying PGlite schema migrations..." - [ ] `curl http://localhost:14242/api/bootstrap/status` → expect `{"needsSetup":true}` HTTP 200 - [ ] Run `mosaic auth users create` interactive flow and confirm user is created 🤖 Generated with [Claude Code](https://claude.com/claude-code)
jason.woltje added 1 commit 2026-05-04 22:12:58 +00:00
fix(db): bootstrap migrations on local-tier gateway startup ac5650d9f9 
Fresh `mosaic gateway install` (npm) left the gateway DB schema empty —
sign-in 500'd with `relation "users" does not exist`, and every entry
point (auth, bootstrap setup) failed because they all query the users
table first. Five stacked bugs on the local (PGlite) tier:

1. `packages/db/package.json` `files: ["dist"]` excluded the `drizzle/`
   SQL migrations from the published tarball.
2. `runMigrations()` only supports postgres-js — unusable for embedded
   PGlite.
3. `apps/gateway/src/database/database.module.ts` never invoked
   migrations at startup.
4. `createPgliteDb` didn't load pgvector, so migration 0001's
   `CREATE EXTENSION vector` failed.
5. Drizzle's PG migrator wraps every migration in one outer
   transaction, which trips Postgres' `check_safe_enum_use` on
   migration 0009 (`ALTER TYPE ADD VALUE 'pending'` → `SET DEFAULT
   'pending'` in the same tx).

Changes:
- Ship `drizzle/` in the published tarball.
- `createPgliteDb` loads `@electric-sql/pglite/vector`.
- New `runPgliteMigrations(handle)` walks the Drizzle journal and
  runs each statement-breakpoint chunk through PGlite's `client.exec()`
  (autocommit per statement). Records into `drizzle.__drizzle_migrations`
  for interop with the postgres-js path. Per-statement try/catch
  surfaces which statement of which migration failed.
- `DatabaseModule` runs migrations in `OnModuleInit` before
  `app.listen()`. Local tier: explicit `runPgliteMigrations` then
  `storageAdapter.migrate()`. Postgres tier: just `storageAdapter.migrate()`,
  which already calls `runMigrations(url)` internally — no double-call.
- Removed `packages/storage/src/test-utils/pglite-with-vector.ts`. The
  "intentionally not exported" rationale is moot now that migration
  0001 forces pgvector load anyway. The integration test uses
  `createPgliteDb` + `runPgliteMigrations` from `@mosaicstack/db`.

Tests: BetterAuth tables exist after migrate; idempotent (re-runs 0009);
partial-failure surfaces statement-level context and leaves no ledger row.

QA on a fresh PGlite install:
- `Applying PGlite schema migrations...` then `Initializing storage
  adapter (pglite)...` in startup log.
- `GET /api/bootstrap/status` → `{"needsSetup":true}` HTTP 200 (was 500).
- `POST /api/bootstrap/setup` reaches Zod validator (was 500).

Scope: this PR fixes the local (PGlite) tier. Postgres-tier first
install still has the outer-transaction problem and a journal ordering
bug (0009's `when` < 0008's). Documented inline as TODO and in the
scratchpad — needs a separate change with real-Postgres validation.

Co-Authored-By: Claude Opus 4.7 (1M context) <[email protected]>
jason.woltje merged commit 755df9079e into main 2026-05-04 22:13:15 +00:00
jason.woltje deleted branch fix/db-bootstrap-migrations 2026-05-04 22:13:58 +00:00
Sign in to join this conversation.
Reviewers
No Reviewers
Labels
Clear labels
bug
Defect in framework tooling or delivered code
 fleet-enhancement
Fleet-wide reliability/speed/cost improvements surfaced by the nightly enhance pass
No labels
Milestone
No items
No Milestone
Projects
Clear projects
No projects
Assignees
Clear assignees
code-be-01 (Mosaic fleet seat code-be-01) code-be-02 (Mosaic fleet seat code-be-02) code-infra-01 (Mosaic fleet seat code-infra-01) fargo fred gate-merge-01 (Mosaic fleet seat gate-merge-01) happy jason.woltje (Jason Woltje) merge-gate ops-01 (Mosaic fleet seat ops-01) ops-02 (Mosaic fleet seat ops-02) ops-03 (Mosaic fleet seat ops-03) ops-ci-01 (Mosaic fleet seat ops-ci-01) ops-deploy-01 (Mosaic fleet seat ops-deploy-01) pepper rev-code-01 rev-code-02 rev-security-01 rev-security-02 rev-security-03 (Mosaic fleet seat rev-security-03) sanity scooby (Scooby) scrappy shaggy tiny velma veronica (Mosaic fleet seat veronica) vision woodpecker
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: mosaicstack/stack#510