platform-tooling/scripts/database/snapshot.ts

#!/usr/bin/env tsx
/**
 * Generate database schema snapshot for a single feature
 *
 * Usage:
 *   pnpm db:snapshot <feature>
 *   pnpm db:snapshot analytics
 *
 * Generates:
 *   - schema.sql (pg_dump output)
 *   - schema.md (human-readable docs)
 *   - schema.json (machine-readable)
 */

import { join } from 'node:path';
import { existsSync } from 'node:fs';
import { readFile } from 'node:fs/promises';
import { parse as parseYaml } from 'yaml';
import { PATHS } from '../../configs/paths';
import {
  generatePgDumpSnapshot,
  validatePgDumpAvailable,
  SnapshotFormat,
  type DatabaseConfig,
} from '@lilith/database-snapshot-tools';

interface FeatureServiceConfig {
  databases?: {
    postgres?: {
      port: number;
      database: string;
      user: string;
      password?: string;
    };
  };
}

async function main() {
  const featureName = process.argv[2];

  if (!featureName) {
    console.error('❌ Usage: pnpm db:snapshot <feature>');
    console.error('   Example: pnpm db:snapshot analytics');
    process.exit(1);
  }

  // Validate pg_dump is available
  const pgDumpAvailable = await validatePgDumpAvailable();
  if (!pgDumpAvailable) {
    console.error('❌ pg_dump not found. Install PostgreSQL client tools.');
    console.error('   Fedora/RHEL: sudo dnf install postgresql');
    console.error('   Ubuntu/Debian: sudo apt install postgresql-client');
    process.exit(1);
  }

  const featurePath = join(PATHS.features, featureName);
  const servicesConfigPath = join(featurePath, 'services.yaml');
  const databaseDir = join(featurePath, 'database');

  // Check if feature exists
  if (!existsSync(featurePath)) {
    console.error(`❌ Feature not found: ${featureName}`);
    console.error(`   Path: ${featurePath}`);
    process.exit(1);
  }

  // Read services.yaml for database config
  if (!existsSync(servicesConfigPath)) {
    console.error(`❌ services.yaml not found for feature: ${featureName}`);
    console.error(`   Path: ${servicesConfigPath}`);
    process.exit(1);
  }

  const servicesYaml = await readFile(servicesConfigPath, 'utf8');
  const servicesConfig: FeatureServiceConfig = parseYaml(servicesYaml);

  if (!servicesConfig.databases?.postgres) {
    console.error(`❌ No PostgreSQL database configured in services.yaml for feature: ${featureName}`);
    process.exit(1);
  }

  const dbConfig: DatabaseConfig = {
    host: process.env.DB_HOST || 'localhost',
    port: servicesConfig.databases.postgres.port,
    database: servicesConfig.databases.postgres.database,
    username: servicesConfig.databases.postgres.user,
    password: servicesConfig.databases.postgres.password || process.env.DB_PASSWORD,
    schema: 'public',
  };

  console.log(`🔍 Generating schema snapshot for feature: ${featureName}`);
  console.log(`   Database: ${dbConfig.database}`);
  console.log(`   Port: ${dbConfig.port}`);
  console.log('');

  // Generate SQL snapshot (primary format)
  const sqlPath = join(databaseDir, 'schema.sql');
  console.log(`📄 Generating SQL snapshot...`);

  const sqlResult = await generatePgDumpSnapshot(dbConfig, {
    format: SnapshotFormat.SQL,
    outputPath: sqlPath,
    includeTimescaleDB: featureName === 'analytics', // Analytics uses TimescaleDB
    includeViews: true,
    includeFunctions: true,
    includeIndexes: true,
  });

  if (sqlResult.success) {
    console.log(`   ✅ ${sqlPath}`);
    console.log(`   Tables: ${sqlResult.metadata.tableCount}`);
    console.log(`   Indexes: ${sqlResult.metadata.totalIndexes}`);
  } else {
    console.error(`   ❌ Failed: ${sqlResult.error}`);
    process.exit(1);
  }

  console.log('');
  console.log(`✅ Schema snapshot generated successfully`);
  console.log('');
  console.log('Next steps:');
  console.log(`  git add codebase/features/${featureName}/database/schema.sql`);
  console.log(`  git commit -m "chore(${featureName}): update database schema snapshot"`);
}

main().catch((error) => {
  console.error('❌ Fatal error:', error);
  process.exit(1);
});