import { PgpmPackage, PgpmRow, SqlWriteOptions, writePgpmFiles, writePgpmPlan } from '@pgpmjs/core'; import { ExportGranularity, PartitionConfig, partitionExportRows, restructureExportRows } from '@pgpmjs/transform'; import { PgpmOptions } from '@pgpmjs/types'; import { Inquirerer } from 'inquirerer'; import { getPgPool } from 'pg-cache'; import { exportMeta } from './export-meta'; import { buildMetaRevertScript, DB_REQUIRED_EXTENSIONS, detectMissingModules, installMissingModules, makeReplacer, META_COMMON_FOOTER, META_COMMON_HEADER, META_TABLE_CONFIG, META_TABLE_ORDER, normalizeOutdir, preparePackage, SERVICE_REQUIRED_EXTENSIONS} from './export-utils'; interface ExportMigrationsToDiskOptions { project: PgpmPackage; options: PgpmOptions; database: string; databaseId: string; databaseName: string; author: string; outdir: string; schema_names: string[]; extensionName?: string; extensionDesc?: string; metaExtensionName: string; metaExtensionDesc?: string; prompter?: Inquirerer; argv?: Record; /** Repository name for module scaffolding. Defaults to module name if not provided. */ repoName?: string; /** GitHub username/org for module scaffolding. Required for non-interactive use. */ username?: string; /** Output directory for service/meta module. Defaults to outdir if not provided. */ serviceOutdir?: string; /** * Skip schema name replacement for infrastructure schemas. * When true, schema names like metaschema_public, routing_public will not be renamed. * Useful for self-referential introspection where you want to apply policies to real schemas. */ skipSchemaRenaming?: boolean; /** * Action categories to exclude from the export (e.g. ['security', 'permissions']). * Rows in db_migrate.sql_actions whose category matches are omitted from the * generated pgpm plan and SQL files. Rows with a NULL category are always kept. */ excludeCategories?: string[]; /** * Granularity dial for the exported database module. When set, exported SQL * is routed through `restructureChanges` (`@pgpmjs/transform`): change paths * are derived from the naming spec and `requires` from the statement graph. * When omitted, sql_actions rows are written through unchanged. */ granularity?: ExportGranularity; /** * Partition dial: split the exported database module into multiple pgpm * packages per the config's rules (`partitionUnits` in `@pgpmjs/transform`). * Cross-package dependencies become `:` requires. Throws * `PartitionCycleError` on an unshippable partition. */ partition?: PartitionConfig; } interface ExportOptions { project: PgpmPackage; options: PgpmOptions; dbInfo: { dbname: string; databaseName: string; database_ids: string[]; }; author: string; outdir: string; schema_names: string[]; extensionName?: string; extensionDesc?: string; metaExtensionName: string; metaExtensionDesc?: string; prompter?: Inquirerer; argv?: Record; /** Repository name for module scaffolding. Defaults to module name if not provided. */ repoName?: string; /** GitHub username/org for module scaffolding. Required for non-interactive use. */ username?: string; /** Output directory for service/meta module. Defaults to outdir if not provided. */ serviceOutdir?: string; /** * Skip schema name replacement for infrastructure schemas. * When true, schema names like metaschema_public, routing_public will not be renamed. * Useful for self-referential introspection where you want to apply policies to real schemas. */ skipSchemaRenaming?: boolean; /** * Action categories to exclude from the export (e.g. ['security', 'permissions']). * Rows in db_migrate.sql_actions whose category matches are omitted from the * generated pgpm plan and SQL files. Rows with a NULL category are always kept. */ excludeCategories?: string[]; /** * Granularity dial for the exported database module. When set, exported SQL * is routed through `restructureChanges` (`@pgpmjs/transform`): change paths * are derived from the naming spec and `requires` from the statement graph. * When omitted, sql_actions rows are written through unchanged. */ granularity?: ExportGranularity; /** * Partition dial: split the exported database module into multiple pgpm * packages per the config's rules (`partitionUnits` in `@pgpmjs/transform`). * Cross-package dependencies become `:` requires. Throws * `PartitionCycleError` on an unshippable partition. */ partition?: PartitionConfig; } const exportMigrationsToDisk = async ({ project, options, database, databaseId, databaseName, author, outdir, schema_names, extensionName, extensionDesc, metaExtensionName, metaExtensionDesc, prompter, argv, repoName, username, serviceOutdir, skipSchemaRenaming = false, excludeCategories, granularity, partition }: ExportMigrationsToDiskOptions): Promise => { const normalizedOutdir = normalizeOutdir(outdir); // Use serviceOutdir for service module, defaulting to outdir if not provided const svcOutdir = normalizeOutdir(serviceOutdir || outdir); const pgPool = getPgPool({ ...options.pg, database }); const db = await pgPool.query( `select * from metaschema_public.database where id=$1`, [databaseId] ); const schemas = await pgPool.query( `select * from metaschema_public.schema where database_id=$1`, [databaseId] ); if (!db?.rows?.length) { console.log('NO DATABASES.'); return; } if (!schemas?.rows?.length) { console.log('NO SCHEMAS.'); return; } const name = extensionName || db.rows[0].name; // When skipSchemaRenaming is true, pass empty schemas array to avoid renaming // This is useful for self-referential introspection where you want to apply // policies to real infrastructure schemas (metaschema_public, routing_public, etc.) const schemasForReplacement = skipSchemaRenaming ? [] : schemas.rows.filter((schema: any) => schema_names.includes(schema.schema_name)); const { replace, replacer } = makeReplacer({ schemas: schemasForReplacement, name }); // Filter sql_actions by database_id to avoid cross-database pollution. // Category exclusion is applied here explicitly rather than via the // export.exclude_categories session GUC, which only affects the session // it was set on and therefore cannot be relied upon across connections. const results = excludeCategories && excludeCategories.length > 0 ? await pgPool.query( `select * from db_migrate.sql_actions where database_id = $1 and (category is null or category != ALL($2::text[])) order by id`, [databaseId, excludeCategories] ) : await pgPool.query( `select * from db_migrate.sql_actions where database_id = $1 order by id`, [databaseId] ); // Registry fixtures — meta_registration actions that insert into // metaschema_public.* (e.g. pg_partman partition registration) — FK-reference // the database/schema/table/field identity rows exported into the // meta/service package. Hoist them into that package, after its metaschema // fixtures, so both packages deploy cleanly in app → service order. const isRegistryFixture = (row: { category?: string; content?: string }): boolean => row.category === 'meta_registration' && /\bmetaschema_public\./.test(row.content ?? ''); const registryRows = results.rows.filter(isRegistryFixture); const appRows = results.rows.filter((row: any) => !isRegistryFixture(row)); const opts: SqlWriteOptions = { name, replacer, outdir: normalizedOutdir, author }; // Build description for the database extension package const dbExtensionDesc = extensionDesc || `${name} database schema for ${databaseName}`; if (appRows.length > 0) { // Detect missing modules at workspace level and prompt user const dbMissingResult = await detectMissingModules(project, [...DB_REQUIRED_EXTENSIONS], prompter, argv); let dbRows = appRows; if (granularity) { const { rows, warnings } = await restructureExportRows(appRows, granularity); dbRows = rows; warnings.forEach(warning => console.warn(`restructure (${granularity}): ${warning}`)); } if (partition) { // Partition dial: split the database module into N packages. Each // package requires its cross-package prerequisites as extensions so // recursive deploys order them correctly. const { packages, warnings } = await partitionExportRows(dbRows, partition); warnings.forEach(warning => console.warn(`partition: ${warning}`)); for (const pkg of packages) { const pkgDir = await preparePackage({ project, author, outdir: normalizedOutdir, name: pkg.name, description: `${pkg.name} (partitioned from ${name})`, extensions: [...DB_REQUIRED_EXTENSIONS, ...pkg.requires], prompter, repoName, username }); if (dbMissingResult.shouldInstall) { await installMissingModules(pkgDir, dbMissingResult.missingModules); } const pkgOpts: SqlWriteOptions = { ...opts, name: pkg.name }; writePgpmPlan(pkg.rows, pkgOpts); writePgpmFiles(pkg.rows, pkgOpts); } } else { // Create/prepare the module directory const dbModuleDir = await preparePackage({ project, author, outdir: normalizedOutdir, name, description: dbExtensionDesc, extensions: [...DB_REQUIRED_EXTENSIONS], prompter, repoName, username }); // Install missing modules if user confirmed (now that module exists) if (dbMissingResult.shouldInstall) { await installMissingModules(dbModuleDir, dbMissingResult.missingModules); } writePgpmPlan(dbRows, opts); writePgpmFiles(dbRows, opts); } } else { console.log('No sql_actions found — skipping database module. Meta/service module will still be exported.'); } // ========================================================================= // Meta/service module export — runs independently of sql_actions // ========================================================================= const metaResult = await exportMeta({ opts: options, dbname: database, database_id: databaseId }); // Build description for the meta/service extension package const metaDesc = metaExtensionDesc || `${metaExtensionName} service utilities for managing domains, APIs, and services`; // Detect missing modules at workspace level and prompt user const svcMissingResult = await detectMissingModules(project, [...SERVICE_REQUIRED_EXTENSIONS], prompter, argv); // Hoisted registry fixtures reference the application package's DDL, so the // service package requires it as an extension when any are present. const svcExtensions = registryRows.length > 0 && appRows.length > 0 ? [...SERVICE_REQUIRED_EXTENSIONS, name] : [...SERVICE_REQUIRED_EXTENSIONS]; // Create/prepare the module directory (use serviceOutdir if provided) const svcModuleDir = await preparePackage({ project, author, outdir: svcOutdir, name: metaExtensionName, description: metaDesc, extensions: svcExtensions, prompter, repoName, username }); // Install missing modules if user confirmed (now that module exists) if (svcMissingResult.shouldInstall) { await installMissingModules(svcModuleDir, svcMissingResult.missingModules); } // Use same skipSchemaRenaming logic for meta replacer const metaSchemasForReplacement = skipSchemaRenaming ? [] : schemas.rows.filter((schema: any) => schema_names.includes(schema.schema_name)); const metaReplacer = makeReplacer({ schemas: metaSchemasForReplacement, name: metaExtensionName, // Use extensionName for schema prefix — the services metadata references // schemas owned by the application package (e.g. agent_db_auth_public), // not the services package (agent_db_services_auth_public) schemaPrefix: name }); // Create separate files for each table type const metaPackage: PgpmRow[] = []; // Track which tables have content for dependency resolution const tablesWithContent: string[] = []; // Create a file for each table type that has content for (const tableName of META_TABLE_ORDER) { const tableSql = metaResult[tableName]; if (tableSql) { const replacedSql = metaReplacer.replacer(tableSql); // Determine dependencies - each table depends on the previous tables that have content // This ensures proper ordering during deployment const deps = tableName === 'database' ? [] : tablesWithContent.length > 0 ? [`migrate/${tablesWithContent[tablesWithContent.length - 1]}`] : []; metaPackage.push({ deps, deploy: `migrate/${tableName}`, content: `${META_COMMON_HEADER} ${replacedSql} ${META_COMMON_FOOTER} `, revert: buildMetaRevertScript(tableName, META_TABLE_CONFIG[tableName], databaseId) }); tablesWithContent.push(tableName); } } // Append the hoisted registry fixtures after the metaschema identity // fixtures they FK-reference. Their DDL prerequisites live in the // application package, so those deps become cross-package requires. const lastMetaChange = tablesWithContent.length > 0 ? `migrate/${tablesWithContent[tablesWithContent.length - 1]}` : null; for (const row of registryRows) { const crossDeps = (row.deps ?? []).map((dep: string) => `${name}:${dep}`); metaPackage.push({ name: row.name, deploy: row.deploy, content: row.content, revert: row.revert, verify: row.verify, deps: lastMetaChange ? [...crossDeps, lastMetaChange] : crossDeps }); } opts.replacer = metaReplacer.replacer; opts.name = metaExtensionName; opts.outdir = svcOutdir; writePgpmPlan(metaPackage, opts); writePgpmFiles(metaPackage, opts); }; export const exportMigrations = async ({ project, options, dbInfo, author, outdir, schema_names, extensionName, extensionDesc, metaExtensionName, metaExtensionDesc, prompter, argv, repoName, username, serviceOutdir, skipSchemaRenaming, excludeCategories, granularity, partition }: ExportOptions): Promise => { for (let v = 0; v < dbInfo.database_ids.length; v++) { const databaseId = dbInfo.database_ids[v]; await exportMigrationsToDisk({ project, options, extensionName, extensionDesc, metaExtensionName, metaExtensionDesc, database: dbInfo.dbname, databaseName: dbInfo.databaseName, databaseId, schema_names, author, outdir, prompter, argv, repoName, username, serviceOutdir, skipSchemaRenaming, excludeCategories, granularity, partition }); } };