// One-shot generator: introspect the live SQLite and emit a Drizzle pg schema // (control-server/src/schema.ts). Fields are camelCase (matching the app's TS // domain); with casing:"snake_case" on the client, Drizzle maps them to // snake_case columns. INTEGER→bigint(mode:number), REAL→doublePrecision, else // text. Run once; the emitted schema.ts is then the hand-maintained source. // // STREAMLINE_DB_PATH=~/.streamline/data.sqlite bun scripts/gen_drizzle_schema.ts import { Database } from "bun:sqlite"; import { homedir } from "node:os"; import path from "node:path"; import { writeFileSync } from "node:fs"; const SRC = process.env.STREAMLINE_DB_PATH || path.join(homedir(), ".streamline", "data.sqlite"); const sq = new Database(SRC, { readonly: true }); type Col = { name: string; type: string; notnull: number; dflt_value: string | null; pk: number }; const tables = sq.query( "SELECT name, sql FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%' ORDER BY name", ).all() as { name: string; sql: string }[]; // field name (camelCase) → snake_case column name, matching Drizzle's casing. const snake = (s: string) => s.replace(/([a-z0-9])([A-Z])/g, "$1_$2").toLowerCase(); // map a sqlite decl type → a drizzle column constructor call with an EXPLICIT // snake_case column name (self-documenting; independent of the casing config). function drizzleType(t: string, col: string): string { const u = (t || "").toUpperCase(); const c = JSON.stringify(col); if (u.includes("INT")) return `bigint(${c}, { mode: "number" })`; if (u.includes("REAL") || u.includes("FLOA") || u.includes("DOUB")) return `doublePrecision(${c})`; return `text(${c})`; } function defaultLiteral(dflt: string, isText: boolean): string { // sqlite stores text defaults quoted ('x'); numbers bare. if (/^'.*'$/.test(dflt)) return JSON.stringify(dflt.slice(1, -1)); if (/^-?\d+(\.\d+)?$/.test(dflt)) return dflt; return JSON.stringify(dflt.replace(/^'|'$/g, "")); // fallback } const importedTypes = new Set(["pgTable"]); const tableBlocks: string[] = []; for (const { name, sql } of tables) { const cols = sq.query(`PRAGMA table_info("${name}")`).all() as Col[]; const fks = sq.query(`PRAGMA foreign_key_list("${name}")`).all() as { table: string; from: string; to: string; on_delete: string }[]; const idxList = sq.query(`PRAGMA index_list("${name}")`).all() as { name: string; unique: number; origin: string }[]; const uniqueCols = new Set(); for (const ix of idxList) if (ix.origin === "u" && ix.unique) { const uc = sq.query(`PRAGMA index_info("${ix.name}")`).all() as { name: string }[]; if (uc.length === 1) uniqueCols.add(uc[0].name); // single-col unique → inline .unique() } const autoinc = /AUTOINCREMENT/i.test(sql || ""); const pkCols = cols.filter((c) => c.pk > 0).sort((a, b) => a.pk - b.pk).map((c) => c.name); const singleAiPk = autoinc && pkCols.length === 1; const fkByCol = new Map(fks.map((f) => [f.from, f])); const lines: string[] = []; for (const c of cols) { const dt = drizzleType(c.type, snake(c.name)); importedTypes.add(dt.split("(")[0]); let chain = ` ${c.name}: ${dt}`; if (singleAiPk && pkCols[0] === c.name) { chain = ` ${c.name}: bigint(${JSON.stringify(snake(c.name))}, { mode: "number" }).generatedByDefaultAsIdentity().primaryKey()`; lines.push(chain + ","); continue; } if (pkCols.length === 1 && pkCols[0] === c.name) chain += ".primaryKey()"; if (c.notnull) chain += ".notNull()"; if (uniqueCols.has(c.name)) chain += ".unique()"; if (c.dflt_value !== null) chain += `.default(${defaultLiteral(c.dflt_value, dt.startsWith("text"))})`; const fk = fkByCol.get(c.name); if (fk) { const od = fk.on_delete && fk.on_delete.toUpperCase() !== "NO ACTION" ? `, { onDelete: "${fk.on_delete.toLowerCase()}" }` : ""; chain += `.references(() => ${fk.table}.${fk.to}${od})`; } lines.push(chain + ","); } // table-level: composite PK const extras: string[] = []; if (pkCols.length > 1) { importedTypes.add("primaryKey"); extras.push(` primaryKey({ columns: [${pkCols.map((c) => `t.${c}`).join(", ")}] })`); } const cb = extras.length ? `, (t) => [\n${extras.join(",\n")},\n]` : ""; tableBlocks.push(`export const ${name} = pgTable("${name}", {\n${lines.join("\n")}\n}${cb});`); } const pgImports = [...importedTypes].filter((t) => t !== "pgTable" && t !== "primaryKey").sort(); const coreImports = ["pgTable", ...(importedTypes.has("primaryKey") ? ["primaryKey"] : []), ...pgImports]; const out = `// AUTO-GENERATED once by scripts/gen_drizzle_schema.ts from the live SQLite, then\n` + `// hand-maintained. Drizzle entities for the Postgres (snake_case) database.\n` + `// Fields are camelCase (app domain); casing:"snake_case" on the client maps\n` + `// them to snake_case columns. int8 columns use mode:"number" so timestamps/\n` + `// counts come back as JS numbers (values are all < 2^53).\n` + `import { ${coreImports.join(", ")} } from "drizzle-orm/pg-core";\n\n` + tableBlocks.join("\n\n") + "\n"; const dest = path.join(import.meta.dir, "..", "control-server", "src", "schema.ts"); writeFileSync(dest, out); console.log(`[gen] wrote ${dest} (${tables.length} tables, ${[...importedTypes].join("/")})`);