import { mkdirSync } from "node:fs";import { dirname, resolve } from "node:path"; import { createClient, type Client } from "@libsql/client";import { drizzle, type LibSQLDatabase } from "drizzle-orm/libsql";import { type FumaDB } from "@executor-js/fumadb";import {  createDrizzleRuntimeSchemaFromTables,  ensureDrizzleRuntimeSchemaFromTables,} from "@executor-js/fumadb/adapters/drizzle";import { type schema as fumaSchema, type RelationsMap } from "@executor-js/fumadb/schema";import { Context, Effect, Layer } from "effect"; import {  collectTables,  createExecutorFumaDb,  DbProvider,  type ExecutorDbHandle,} from "@executor-js/api/server";import type { FumaDb, FumaTables } from "@executor-js/sdk"; import { SELF_HOST_NAMESPACE, SELF_HOST_SCHEMA_VERSION } from "../config"; // ---------------------------------------------------------------------------// SQLite executor DB factory, inline (like apps/local's sqlite-fumadb.ts and// apps/cloud's fuma.ts — each app owns its DB wiring; there is no shared// storage package). Differences from apps/local: busy_timeout + synchronous// pragmas for the multi-user HTTP server, and the idempotent// `ensureDrizzleRuntimeSchemaFromTables` schema-ensure (the drizzle adapter// has no versioned migrator). Built ONCE for the process; the per-request// executor reuses this long-lived handle's `db`.//// Driver: libSQL (@libsql/client + drizzle-orm/libsql), not bun:sqlite, so the// self-host server runs on Node AND Bun (and the same code path serves edge by// swapping the `file:` URL for an https Turso URL). Better Auth SHARES this very// `@libsql/client` (its LibsqlDialect is built with `{ client }`, not a fresh// `{ url }` connection) — so the PRAGMAs set here cover auth queries too, and// there is exactly ONE connection and ONE WAL. That sharing is load-bearing: a// second libSQL connection to the same file unlinks this one's `-wal`/`-shm` on// open, orphaning executor-core writes onto a deleted inode that vanishes on// restart (the self-host data-loss bug — see better-auth.ts's header).// --------------------------------------------------------------------------- /** * Build a `file:` libSQL URL from a filesystem path. libSQL requires an * absolute path for `file:` URLs; `:memory:` passes through unchanged. */export const toLibsqlFileUrl = (path: string): string =>  path === ":memory:" ? path : `file:${resolve(path)}`; type SelfHostFumaSchema<TTables extends FumaTables> = ReturnType<  typeof fumaSchema<string, TTables, RelationsMap<TTables>>>; export interface SelfHostDbHandle<TTables extends FumaTables = FumaTables> {  readonly db: FumaDb<SelfHostFumaSchema<TTables>>;  readonly fuma: FumaDB<SelfHostFumaSchema<TTables>[]>;  readonly drizzle: LibSQLDatabase<Record<string, unknown>>;  /**   * The libSQL client for this handle's `file:` URL. Better Auth's LibsqlDialect   * is built on THIS client (one shared connection — see better-auth.ts), and   * the seed reads Better Auth's tables through it too. `url` is retained for   * callers that still need the `file:` string (diagnostics, edge swap).   */  readonly client: Client;  readonly url: string;  readonly close: () => Promise<void>;} export interface CreateSqliteExecutorDbOptions<TTables extends FumaTables = FumaTables> {  readonly tables: TTables;  readonly namespace: string;  readonly version?: string;  readonly path: string;} export const createSqliteExecutorDb = async <const TTables extends FumaTables>(  options: CreateSqliteExecutorDbOptions<TTables>,): Promise<SelfHostDbHandle<TTables>> => {  const version = options.version ?? SELF_HOST_SCHEMA_VERSION;  if (options.path !== ":memory:") {    mkdirSync(dirname(options.path), { recursive: true });  }   const url = toLibsqlFileUrl(options.path);  const client = createClient({ url });  // Connection PRAGMAs. This is the ONE libSQL connection for the process —  // drizzle (executor tables) and Better Auth's LibsqlDialect both run on this  // same client (see better-auth.ts), so these apply to every query, auth  // included. WAL is a file-level mode; foreign_keys/busy_timeout/synchronous  // are connection-level and set once here.  await client.execute("PRAGMA foreign_keys = ON");  await client.execute("PRAGMA journal_mode = WAL");  // Survive concurrent writes from the multi-user HTTP server, and trade  // fsync-per-commit for fsync-per-checkpoint (durable under WAL).  await client.execute("PRAGMA busy_timeout = 5000");  await client.execute("PRAGMA synchronous = NORMAL");   const schema = createDrizzleRuntimeSchemaFromTables({    tables: options.tables,    namespace: options.namespace,    version,    provider: "sqlite",  });  const drizzleDb = drizzle({ client, schema });   await ensureDrizzleRuntimeSchemaFromTables(drizzleDb, {    tables: options.tables,    namespace: options.namespace,    version,    provider: "sqlite",  });   const { db, fuma } = createExecutorFumaDb(drizzleDb, {    tables: options.tables,    namespace: options.namespace,    version,    provider: "sqlite",  });   return {    db,    fuma,    drizzle: drizzleDb,    client,    url,    close: async () => {      client.close();    },  };}; // ---------------------------------------------------------------------------// Long-lived DB layer. Built once at boot; the connection lives for the// process. The per-request executor (execution.ts) reuses this handle's `db`// and only varies the scope stack — so "build once, rebind scope per request"// is cheap.// --------------------------------------------------------------------------- export class SelfHostDb extends Context.Service<SelfHostDb, SelfHostDbHandle>()(  "@executor-js/host-selfhost/SelfHostDb",) {} export interface SelfHostDbLayerOptions {  readonly path: string;  readonly namespace?: string;  readonly version?: string;} /** * Open the self-host DB with the full plugin table set. Used both by the layer * and by the composition root (which needs the raw handle eagerly so Better * Auth can open its own libSQL connection to the same `file:` URL). */export const createSelfHostDb = (options: SelfHostDbLayerOptions): Promise<SelfHostDbHandle> =>  createSqliteExecutorDb({    tables: collectTables(),    namespace: options.namespace ?? SELF_HOST_NAMESPACE,    version: options.version ?? SELF_HOST_SCHEMA_VERSION,    path: options.path,  }); // Shared DbProvider seam (P2a). The self-host handle keeps its libSQL driver,// WAL/busy_timeout PRAGMAs, and the idempotent// `ensureDrizzleRuntimeSchemaFromTables` bring-up; this just re-exposes the// already-built long-lived handle under the shared `DbProvider` tag so the// future shared `makeScopedExecutor` (P3) reads from one injection point. The// release is owned by `SelfHostDb`, so this projection does not re-close.export const SelfHostDbProvider: Layer.Layer<DbProvider, never, SelfHostDb> = Layer.effect(  DbProvider,)(  Effect.map(    SelfHostDb.asEffect(),    (handle): ExecutorDbHandle => ({      db: handle.db,      fuma: handle.fuma,      close: handle.close,    }),  ),);