ThothII/frontend/vite.database-management-prototype.config.ts

import path from "path";
import { execFileSync } from "node:child_process";
import react from "@vitejs/plugin-react";
import { defineConfig, type Plugin } from "vite";

const CATALOG_CONTAINER = process.env.THT_PROTOTYPE_CATALOG_CONTAINER
  ?? "thothii-catalog-db-1";

const LIVE_CATALOG_SQL = String.raw`
select json_build_object(
  'capturedAt', now(),
  'databases', coalesce((select json_agg(json_build_object(
    'id', wd.id, 'workspaceId', wd.workspace_id, 'engine', wd.engine,
    'databaseName', wd.database_name, 'schemaName', wd.schema_name,
    'version', wd.version, 'schemaSyncedVersion', wd.schema_synced_version,
    'schemaSyncedAt', wd.schema_synced_at, 'updatedAt', wd.updated_at,
    'transport', b.transport, 'host', b.host, 'port', b.port,
    'baseUrl', b.base_url, 'restPath', b.rest_path,
    'connectionStatus', b.connection_status, 'testedVersion', b.tested_version,
    'lastTestedAt', b.last_tested_at
  ) order by wd.workspace_id) from workspace_databases wd left join database_bindings b on b.database_id = wd.id), '[]'::json),
  'tables', coalesce((select json_agg(json_build_object(
    'id', t.id, 'databaseId', t.database_id, 'name', t.name,
    'sourceComment', t.source_comment, 'description', t.description,
    'generatedDescription', t.generated_description, 'version', t.version,
    'lastSyncedAt', t.last_synced_at
  ) order by t.name) from catalog_tables t), '[]'::json),
  'columns', coalesce((select json_agg(json_build_object(
    'id', c.id, 'tableId', c.table_id, 'name', c.name,
    'ordinalPosition', c.ordinal_position, 'dataType', c.data_type,
    'isNullable', c.is_nullable, 'defaultExpression', c.default_expression,
    'primaryKeyPosition', c.primary_key_position, 'sourceComment', c.source_comment,
    'description', c.description, 'generatedDescription', c.generated_description,
    'sensitive', c.sensitive, 'lastSyncedAt', c.last_synced_at, 'version', c.version
  ) order by c.table_id, c.ordinal_position) from catalog_columns c), '[]'::json),
  'relationships', coalesce((select json_agg(json_build_object(
    'id', r.id, 'databaseId', r.database_id, 'constraintName', r.constraint_name,
    'sourceTableName', st.name, 'targetTableName', tt.name,
    'updateRule', r.update_rule, 'deleteRule', r.delete_rule
  ) order by r.constraint_name) from catalog_relationships r
    join catalog_tables st on st.id = r.source_table_id
    join catalog_tables tt on tt.id = r.target_table_id), '[]'::json),
  'relationshipColumns', coalesce((select json_agg(json_build_object(
    'relationshipId', rc.relationship_id, 'ordinalPosition', rc.position,
    'sourceColumnName', sc.name, 'targetColumnName', tc.name
  ) order by rc.relationship_id, rc.position) from catalog_relationship_columns rc
    join catalog_columns sc on sc.id = rc.source_column_id
    join catalog_columns tc on tc.id = rc.target_column_id), '[]'::json),
  'syncRuns', coalesce((select json_agg(json_build_object(
    'id', r.id, 'databaseId', r.database_id, 'scope', r.scope,
    'state', r.state, 'phase', r.phase, 'counts', r.counts,
    'plannedDiff', r.planned_diff, 'errorCode', r.error_code,
    'errorMessage', r.error_message, 'createdAt', r.created_at,
    'startedAt', r.started_at, 'finishedAt', r.finished_at
  ) order by r.created_at desc) from (select * from catalog_sync_runs order by created_at desc limit 50) r), '[]'::json),
  'syncEvents', coalesce((select json_agg(json_build_object(
    'runId', e.run_id, 'sequence', e.sequence, 'level', e.level,
    'message', e.message, 'createdAt', e.created_at
  ) order by e.created_at desc) from (select * from catalog_sync_events order by created_at desc limit 300) e), '[]'::json),
  'descriptionRuns', coalesce((select json_agg(json_build_object(
    'id', r.id, 'databaseId', r.database_id, 'scope', r.scope,
    'modelId', r.model_id, 'language', r.language, 'status', r.status,
    'total', r.total, 'processed', r.processed, 'generated', r.generated,
    'nonGeneratable', r.non_generatable, 'failed', r.failed,
    'createdAt', r.created_at, 'startedAt', r.started_at,
    'finishedAt', r.finished_at, 'errorSummary', r.error_summary
  ) order by r.created_at desc) from (select * from description_generation_runs order by created_at desc limit 50) r), '[]'::json),
  'descriptionEvents', coalesce((select json_agg(json_build_object(
    'runId', e.run_id, 'sequence', e.sequence, 'level', e.level,
    'message', e.message, 'createdAt', e.created_at
  ) order by e.created_at desc) from (select * from description_generation_events order by created_at desc limit 300) e), '[]'::json),
  'sensitiveRuns', coalesce((select json_agg(json_build_object(
    'id', r.id, 'databaseId', r.database_id, 'scope', r.scope,
    'modelId', r.model_id, 'status', r.status, 'total', r.total,
    'suggestedSensitive', r.suggested_sensitive,
    'suggestedNonSensitive', r.suggested_non_sensitive,
    'createdAt', r.created_at, 'startedAt', r.started_at,
    'finishedAt', r.finished_at, 'errorSummary', r.error_summary
  ) order by r.created_at desc) from (select * from sensitive_data_suggestion_runs order by created_at desc limit 50) r), '[]'::json),
  'sensitiveEvents', coalesce((select json_agg(json_build_object(
    'runId', e.run_id, 'sequence', e.sequence, 'level', e.level,
    'message', e.message, 'createdAt', e.created_at
  ) order by e.created_at desc) from (select * from sensitive_data_suggestion_events order by created_at desc limit 300) e), '[]'::json)
)::text;
`;

function liveCatalogPlugin(): Plugin {
  return {
    name: "database-management-prototype-live-catalog",
    configureServer(server) {
      server.middlewares.use("/__prototype/catalog-snapshot", (_request, response) => {
        try {
          const payload = execFileSync("docker", [
            "exec",
            "--env",
            `THT_PROTOTYPE_SQL=${LIVE_CATALOG_SQL}`,
            CATALOG_CONTAINER,
            "sh",
            "-lc",
            'psql -U "$POSTGRES_USER" -d "$POSTGRES_DB" -Atc "$THT_PROTOTYPE_SQL"',
          ], { encoding: "utf8", maxBuffer: 32 * 1024 * 1024 }).trim();
          response.statusCode = 200;
          response.setHeader("Content-Type", "application/json; charset=utf-8");
          response.setHeader("Cache-Control", "no-store");
          response.end(payload);
        } catch (error) {
          response.statusCode = 503;
          response.setHeader("Content-Type", "application/json; charset=utf-8");
          response.end(JSON.stringify({
            error: "live_catalog_unavailable",
            detail: error instanceof Error ? error.message.split("\n")[0] : "Unknown error",
          }));
        }
      });
    },
  };
}

export default defineConfig(({ command }) => {
  if (command !== "serve") {
    throw new Error("The database-management prototype is dev-only. Run npm run prototype:database-management.");
  }

  return {
    root: path.resolve(__dirname, "prototypes/database-management"),
    plugins: [react(), liveCatalogPlugin()],
    appType: "spa",
    server: {
      host: "127.0.0.1",
      port: 5173,
      strictPort: true,
    },
  };
});