fix(cli): generated service aggregate health check fails on sqlite/libSQL — and intermittently on postgres

[rickylabs]

Summary

The generated example service's aggregate /health check uses Prisma's tagged $queryRaw\SELECT 1`form. The libSQL adapter rejects that form, so a scaffolded **sqlite** project ships a service whose/healthreportsdatabase: unhealthy. The same Invalid `prisma.$queryRaw()` invocationerror was also observed on **postgres** during ascaffold.runtime` run, so the defect may not be engine-specific.

Discovered while implementing #1158 (PR #1220).

Evidence

sqlite — behavior.service-health fails; this is why #1158 excludes that one gate from the new scaffold.runtime.sqlite tier (recorded as drift D-15, with the gate retained unchanged in scaffold.runtime).

postgres — a full scaffold.runtime run returned passed=51 failed=1:

service health probe failed for users:
  https://localhost:44677/health -> 0: fetch failed;
  http://localhost:3001/health  -> 503: {"status":"unhealthy","checks":[{"name":"database","healthy":false,
      "message":"Invalid `prisma.$queryRaw()` invocation: Raw query failed…"}]};
  http://localhost:46435/health -> 200: Healthy

That run had a known confounder: several concurrent e2e suites from other worktrees were running on the same host, and the probe resolved three endpoints with contradictory verdicts — the signature of cross-run interference. An isolated re-run is in progress; this issue will be updated with the result. It is filed now regardless, because the sqlite failure is reproducible and independent of that confounder.

Why it matters

A user scaffolding a sqlite project gets a service that reports itself unhealthy out of the box. Nothing in the scaffold warns them.

Relevant code

• Generated health check: the tagged queryRaw\SELECT 1` form in the generated engine module (packages/cli/src/kernel/assets/embedded.generated.ts; note the template also carries a queryRawUnsafe(...)` variant).
• packages/cli/src/kernel/templates/database/generate-engine-mod.ts:35 currently reads
  const healthQuery = provider.engine === 'sqlite' ? 'SELECT 1' : 'SELECT 1'; — a vestigial ternary whose branches are identical, suggesting an engine-specific health path was once intended and never landed.

Suggested direction (needs design)

• Use a call form the libSQL adapter supports (e.g. the parameterless/unsafe variant, or the driver adapter's own connectivity probe) rather than a tagged template.
• Decide whether the health query should genuinely differ per engine, and either implement that or delete the vestigial ternary.
• Once fixed, re-include behavior.service-health in the scaffold.runtime.sqlite gate list and drop the exclusion in packages/cli/e2e/suites/scaffold/capability-suites.ts.

Acceptance

• A scaffolded sqlite project's /health reports database: healthy.
• Root-caused on postgres: either confirmed as cross-run interference only, or fixed.
• behavior.service-health restored to the sqlite tier, exclusion removed.
• The vestigial healthQuery ternary is resolved either way.

Refs #1158, PR #1220.

[rickylabs]

Decisive evidence: this is pre-existing on main, and it is currently red for postgres

Follow-up from #1158 / PR #1220. The postgres half of this issue is no longer "observed once, with a confounder" — it is confirmed, reproducible, and independent of that branch.

Method

Run	Result	Failing gate
PR #1220 branch, isolated (own lease, no foreign runs)	passed=51 failed=1	behavior.service-health
main @ c6f243da, clean worktree	passed=51 failed=1	behavior.service-health

Identical error on both sides:

service health probe failed for users:
  https://localhost:<p>/health -> 0: fetch failed;
  http://localhost:3001/health -> 503: {"status":"unhealthy","checks":[{"name":"database","healthy":false,
      "message":"Invalid `prisma.$queryRaw()` invocation: Raw query failed. Code: `N/A`..."}]};
  http://localhost:<q>/health -> 200: Healthy

The baseline worktree was verified to contain none of #1220's changes before running (withDatabasePermissions absent from every register generator).

Two hypotheses were tested and discarded

1. Cross-run interference — plausible at first: several worktrees were running e2e suites concurrently, and the probe resolved three endpoints with contradictory verdicts. Wrong — the failure reproduced with the branch's own worktree holding the exclusive lease and nothing else running.
2. Regression from test(e2e): sqlite-backed runtime tier so docker+postgres is reserved for postgres verification #1220 — wrong: main fails identically. test(e2e): sqlite-backed runtime tier so docker+postgres is reserved for postgres verification #1220's generator change branches only on databaseEngine === 'Sqlite' and carries a test asserting non-sqlite output is byte-identical across [undefined, Postgres, Mysql, Mssql].

Four lease-contention verdicts were hit while trying to run the baseline; those were discarded as non-results rather than counted as failures.

What this changes about this issue

The original framing — "fails on sqlite/libSQL, possibly postgres" — understates it. scaffold.runtime is red on main today, which means the repo's merge-readiness gate is broken for everyone, not only for sqlite consumers. Draft PRs run no CI (#1212), so nothing has been surfacing it.

Suggested: treat the postgres path as a primary symptom rather than a secondary observation. The sqlite/libSQL failure and this one look like the same defect in the tagged $queryRaw call form.

The vestigial ternary at packages/cli/src/kernel/templates/database/generate-engine-mod.ts:35 — provider.engine === 'sqlite' ? 'SELECT 1' : 'SELECT 1' — remains the strongest hint that an engine-aware health path was intended and never landed.

[rickylabs]

Correction: my previous comment overstated what the baseline proves

Owner correction, and it is right — I need to narrow my own claim.

My baseline comparison controlled for exactly one variable: the branch changes. It ran the identical suite at main @ c6f243da and at PR #1220's HEAD, on the same host. So:

• Still supported: the failure is not a regression from test(e2e): sqlite-backed runtime tier so docker+postgres is reserved for postgres verification #1220. Same failure with and without the changes — that comparison is sound.
• Not supported: that scaffold.runtime is "red on main today" for everyone. Both runs shared one WSL host, so a dangling postgres container, a stale bind-mount, or accumulated end-of-day clutter would poison them identically. My comparison cannot separate "defect in main" from "fault on this machine". I asserted the former; only the weaker statement is established.

That distinction matters here: an earlier leak report on this host showed a stale postgres-* container roughly four hours old owned by a different worktree, alongside several concurrent e2e runs. That is exactly the kind of state that survives a --isolated start and would hit both runs the same way.

The authoritative signal is cloud CI, not this workstation. PR #1220 has been marked ready for review, so scaffold-runtime will now run on a clean GitHub runner. Reading the result:

• Passes on cloud → this was local environment clutter. The postgres half of this issue is withdrawn, and the issue narrows back to the sqlite/libSQL failure, which reproduces independently and is unaffected by any of this.
• Fails on cloud → then it is either a stale-cache artifact or a genuine gate defect, and it warrants real investigation with the cloud logs as evidence.

The priority:p1 label and the "repo merge gate is broken for everyone" framing are provisional pending that run. I would rather flag the retraction now than leave an overstated claim sitting on a p1 issue.

Unchanged by any of this: the sqlite/libSQL failure. It reproduces on its own, is the reason #1220 excludes behavior.service-health from the sqlite tier only, and the vestigial ternary at generate-engine-mod.ts:35 (provider.engine === 'sqlite' ? 'SELECT 1' : 'SELECT 1') still points at an engine-aware health path that was intended and never landed.

[rickylabs]

Withdrawing the postgres half — cloud CI is green

scaffold-runtime passed on a clean GitHub runner (run 30941839021, job scaffold-runtime (aspire + docker + postgres) → success, E2E step 4m 24s).

So the postgres $queryRaw failure I reported was local environment state — cluttered WSL with dangling postgres/docker resources — not a defect in main. My earlier "red on main for everyone" claim was wrong, and the same-host baseline comparison that seemed to confirm it could only ever rule out the code delta, never the host. Retracted.

Restoring priority:p2 and narrowing this issue back to its original, still-valid scope: the sqlite/libSQL failure.

That half stands on its own and is unaffected by any of the above:

• A scaffolded sqlite project's generated service reports database: unhealthy because the aggregate health check uses Prisma's tagged $queryRaw\SELECT 1`` form, which the libSQL adapter rejects.
• It is why PR test(e2e): sqlite-backed runtime tier so docker+postgres is reserved for postgres verification #1220 excludes behavior.service-health from the scaffold.runtime.sqlite tier only (retained unchanged in scaffold.runtime — 69 gates vs 67).
• packages/cli/src/kernel/templates/database/generate-engine-mod.ts:35 still reads provider.engine === 'sqlite' ? 'SELECT 1' : 'SELECT 1' — a vestigial ternary whose branches are identical, suggesting an engine-aware health path was intended and never landed.

Acceptance is unchanged except for dropping the postgres item:

• A scaffolded sqlite project's /health reports database: healthy.
• behavior.service-health restored to the sqlite tier and the exclusion removed from capability-suites.ts.
• The vestigial healthQuery ternary is resolved.