Migrate Postgresql

Orchestrates the complete PostgreSQL to ScalarDB migration workflow through an interactive chat interface. Collects database connection parameters from the user via questions, updates the configuration file, then runs the analysis and migration skills.

---

Execution Instructions

You MUST follow these steps exactly in order. Do NOT skip any step.

---

STEP 0: Discover Plugin Installation Directory (PLUGIN_ROOT)

Run the following Bash command to get the plugin installation directory:

echo "$CLAUDE_PLUGIN_ROOT"

• If the output is a non-empty path, set PLUGIN_ROOT to that value.
• If the output is empty, run this fallback to locate it:

find ~/.claude/plugins -name "plugin.json" -path "*/architect/*" 2>/dev/null | head -1 | xargs -I{} dirname {} | xargs -I{} dirname {}

Store the result as PLUGIN_ROOT. All subagent template paths in Steps 7–11 use this variable (e.g., PLUGIN_ROOT/skills/common/subagents/postgresql/0-test-connection.md).

---

STEP 1: Read Current Configuration (or Detect First Run)

First, attempt to read the current configuration file:

Read file: .claude/configuration/databases.env

Two possible outcomes:

A) File EXISTS → Set CONFIG_EXISTS = true

• Note down the current PostgreSQL values (POSTGRESHOST, POSTGRESPORT, POSTGRESDATABASE, POSTGRESUSER, POSTGRESPASSWORD, POSTGRESSCHEMA, POSTGRESINCLUDEPLPGSQLSOURCE, OUTPUTDIR)
• These will be shown in "Keep current" option descriptions

B) File DOES NOT EXIST → Set CONFIG_EXISTS = false

• Inform the user: "No existing configuration found. This appears to be a first-time setup — I'll collect all connection parameters from you."
• Ensure the configuration directory exists using Bash: mkdir -p .claude/configuration
• All parameters will need to be collected fresh (no "Keep current" option available)

---

STEP 2: Collect Connection Parameters (Batch 1 of 2)

Build the questions based on CONFIG_EXISTS:

If CONFIG_EXISTS = true: Include "Keep current" options with actual values in descriptions (e.g., "Keep current" with description "Keep: localhost").

If CONFIG_EXISTS = false: Replace "Keep current" options with additional useful defaults instead. Do NOT offer "Keep current" since there is nothing to keep.

Use the AskUserQuestion tool:

{
  "questions": [
    {
      "question": "What is the PostgreSQL database host?",
      "header": "Host",
      "options": [
        {"label": "localhost", "description": "Database running on local machine"},
        CONFIG_EXISTS ? {"label": "Keep current", "description": "Keep: "}
                      : {"label": "127.0.0.1", "description": "Loopback IP address"}
      ],
      "multiSelect": false
    },
    {
      "question": "What is the PostgreSQL port?",
      "header": "Port",
      "options": [
        {"label": "5432 (Default)", "description": "Standard PostgreSQL port"},
        CONFIG_EXISTS ? {"label": "Keep current", "description": "Keep: "}
                      : {"label": "5433", "description": "Alternative PostgreSQL port"}
      ],
      "multiSelect": false
    },
    {
      "question": "What is the PostgreSQL database name to analyze?",
      "header": "Database",
      "options": [
        {"label": "postgres", "description": "Default PostgreSQL database"},
        CONFIG_EXISTS ? {"label": "Keep current", "description": "Keep: "}
                      : {"label": "template1", "description": "PostgreSQL template database"}
      ],
      "multiSelect": false
    },
    {
      "question": "What is the PostgreSQL username?",
      "header": "Username",
      "options": [
        {"label": "postgres", "description": "Default PostgreSQL superuser"},
        CONFIG_EXISTS ? {"label": "Keep current", "description": "Keep: "}
                      : {"label": "admin", "description": "Common admin username"}
      ],
      "multiSelect": false
    }
  ]
}

Note: The pseudo-code CONFIG_EXISTS ? ... : ... means you must construct the actual JSON dynamically based on whether the config file existed. Replace `` placeholders with actual values read from the file.

Save all responses. For "Keep current" responses, use the existing values from the config file. For "Other" responses, use the custom text the user typed.

---

STEP 3: Collect Authentication & Options (Batch 2 of 2)

Use the AskUserQuestion tool, again adapting based on CONFIG_EXISTS:

{
  "questions": [
    {
      "question": "What is the database password? (Select 'Other' to type your password)",
      "header": "Password",
      "options": [
        CONFIG_EXISTS ? {"label": "Keep current", "description": "Use the password already in config"}
                      : {"label": "No password", "description": "Connect without a password (empty)"},
        CONFIG_EXISTS ? {"label": "No password", "description": "Connect without a password (empty)"}
                      : {"label": "Type below", "description": "Select 'Other' to enter your password"}
      ],
      "multiSelect": false
    },
    {
      "question": "Which PostgreSQL schema to analyze?",
      "header": "Schema",
      "options": [
        {"label": "public (Default)", "description": "Default PostgreSQL schema"},
        CONFIG_EXISTS ? {"label": "Keep current", "description": "Keep: "}
                      : {"label": "pg_catalog", "description": "PostgreSQL system catalog schema"}
      ],
      "multiSelect": false
    },
    {
      "question": "Include PL/pgSQL function and procedure source code in the report?",
      "header": "PL/pgSQL",
      "options": [
        {"label": "Yes (Recommended)", "description": "Include PL/pgSQL source code for migration analysis"},
        {"label": "No", "description": "Skip source code - faster analysis, smaller report"}
      ],
      "multiSelect": false
    },
    {
      "question": "Where should output files be saved? (Must be an absolute path)",
      "header": "Output Dir",
      "options": [
        {"label": "Default (.claude/output)", "description": "Use the project's .claude/output directory"},
        CONFIG_EXISTS ? {"label": "Keep current", "description": "Keep: "}
                      : {"label": "Custom path", "description": "Select 'Other' to type a custom absolute path"}
      ],
      "multiSelect": false
    }
  ]
}

Save all responses.

---

STEP 4: Map Responses to Configuration Values

Process the collected answers into configuration values using these rules:

| Parameter | Response Mapping | |-----------|-----------------| | POSTGRESHOST | "localhost" -> localhost, "127.0.0.1" -> 127.0.0.1, "Keep current" -> keep existing, "Other" -> user's typed value | | POSTGRESPORT | "5432 (Default)" -> 5432, "5433" -> 5433, "Keep current" -> keep existing, "Other" -> user's typed value | | POSTGRESDATABASE | "postgres" -> postgres, "template1" -> template1, "Keep current" -> keep existing, "Other" -> user's typed value | | POSTGRESUSER | "postgres" -> postgres, "admin" -> admin, "Keep current" -> keep existing, "Other" -> user's typed value | | POSTGRESPASSWORD | "Keep current" -> keep existing, "No password" -> empty string, "Type below" -> user must use "Other", "Other" -> user's typed value | | POSTGRESSCHEMA | "public (Default)" -> public, "pgcatalog" -> pg_catalog, "Keep current" -> keep existing, "Other" -> user's typed value | | POSTGRESINCLUDEPLPGSQLSOURCE | "Yes (Recommended)" -> true, "No" -> false | | OUTPUT_DIR | "Default (.claude/output)" -> absolute path to project's .claude/output directory, "Keep current" -> keep existing, "Custom path" -> user must use "Other", "Other" -> user's typed value |

Also set: ACTIVE_DATABASE=postgresql

---

STEP 5: Write or Update Configuration File

If CONFIG_EXISTS = false (first run):

Use the Write tool to create .claude/configuration/databases.env with the complete template populated with collected values:

# =============================================================================
# CONSOLIDATED DATABASE CONFIGURATION
# =============================================================================
# Single configuration file for all database migration skills
# =============================================================================

# ACTIVE DATABASE SELECTION
ACTIVE_DATABASE=postgresql

# SHARED OUTPUT CONFIGURATION (ABSOLUTE PATH REQUIRED)
OUTPUT_DIR=

# ScalarDB target version
SCALARDB_TARGET_VERSION=3.17

# =============================================================================
# POSTGRESQL CONFIGURATION
# =============================================================================
POSTGRES_HOST=
POSTGRES_PORT=
POSTGRES_DATABASE=
POSTGRES_USER=
POSTGRES_PASSWORD=
POSTGRES_SCHEMA=
POSTGRES_REPORT_FILENAME=postgresql_schema_report.md
POSTGRES_SCALARDB_NAMESPACE=
POSTGRES_INCLUDE_PLPGSQL_SOURCE=
POSTGRES_PSQL_PATH=
POSTGRES_CONNECTION_TIMEOUT=30
POSTGRES_QUERY_TIMEOUT=300

# =============================================================================
# MYSQL CONFIGURATION (defaults - not yet configured)
# =============================================================================
MYSQL_HOST=localhost
MYSQL_PORT=3306
MYSQL_DATABASE=your_database
MYSQL_USER=your_username
MYSQL_PASSWORD=your_password
MYSQL_REPORT_FILENAME=mysql_schema_report.md
MYSQL_SCALARDB_NAMESPACE=
MYSQL_INCLUDE_SOURCE=false
MYSQL_CHARSET=utf8mb4
MYSQL_CONNECTION_TIMEOUT=30

# =============================================================================
# ORACLE CONFIGURATION (defaults - not yet configured)
# =============================================================================
ORACLE_HOST=localhost
ORACLE_PORT=1521
ORACLE_SERVICE=ORCL
ORACLE_USER=your_username
ORACLE_PASSWORD=your_password
ORACLE_SCHEMA=
ORACLE_REPORT_FILENAME=oracle_schema_report.md
ORACLE_SCALARDB_NAMESPACE=
ORACLE_INCLUDE_PLSQL_SOURCE=false
ORACLE_SQLPLUS_PATH=
ORACLE_HOME=
ORACLE_TNS_ADMIN=

# =============================================================================
# END OF CONFIGURATION
# =============================================================================

If CONFIG_EXISTS = true (updating existing):

Use the Edit tool to update .claude/configuration/databases.env with the collected values:

1. Set ACTIVE_DATABASE=postgresql
2. Update OUTPUT_DIR if changed
3. Update all POSTGRES_* parameters with the mapped values from Step 4
4. Do NOT modify the MySQL or Oracle sections

After writing/updating, display a confirmation summary to the user:

Configuration :
  Host:      
  Port:      
  Database:  
  User:      
  Password:  ******** (hidden)
  Schema:    
  PL/pgSQL:  
  Output:    

---

STEP 6: Ensure Output Directory Exists

Use Bash to create the output directory if it doesn't exist:

mkdir -p 

---

STEP 7: Subagent 0 — Connection Test via API (Bash)

Spawn a Bash subagent using the Task tool to test the PostgreSQL database connection via the external API.

1. Read the prompt template at: ${PLUGIN_ROOT}/skills/common/subagents/postgresql/0-test-connection.md
2. Substitute the runtime variables: replace `, , , , , and ` with the actual values from Steps 4-5
3. Call the Task tool with subagent_type: "Bash", description: "Test PostgreSQL connection", and the substituted prompt

After the subagent completes:

• Extract DURATION_SECONDS from the subagent's response → store as S0_DURATION
• Extract total_tokens from the ` block in the Task result (if present) → store as S0_TOKENS`

Check the subagent result:

• If STATUS is FAILURE → Display the error to the user with resolution hints (check host/port/database name, verify credentials, check pg_hba.conf allows connections, ensure PostgreSQL server is running). STOP HERE — do NOT proceed to Step 8, 9, or 10.
• If STATUS is SUCCESS → Note the database product and version, then proceed to Step 8.

---

STEP 8: Subagent 1 — Schema Extraction (Bash)

Spawn a Bash subagent using the Task tool to run the Python extractor script.

1. Read the prompt template at: ${PLUGIN_ROOT}/skills/common/subagents/postgresql/1-extract-schema.md
2. Substitute the runtime variables as documented in the template (replace `` based on POSTGRESINCLUDEPLPGSQL_SOURCE from Step 4)
3. Call the Task tool with subagent_type: "Bash", description: "Extract PostgreSQL schema", and the substituted prompt

After the subagent completes:

• Extract DURATION_SECONDS from the subagent's response → store as S1_DURATION
• Extract total_tokens from the ` block in the Task result (if present) → store as S1_TOKENS`

Check the subagent result:

• If STATUS is FAILURE → Display the error to the user with resolution hints (check host/port, verify credentials, ensure PostgreSQL server is running). STOP HERE — do NOT proceed to Step 9 or Step 10.
• If STATUS is SUCCESS → Note the OUTPUT_FILE path and proceed to Step 9.

---

STEP 9: Subagent 2 — Schema Report Generation (general-purpose)

Spawn a general-purpose subagent using the Task tool to generate the schema report from the extracted JSON.

1. Read the prompt template at: ${PLUGIN_ROOT}/skills/common/subagents/postgresql/2-generate-report.md
2. Substitute the runtime variables as documented in the template (replace all `` with the actual absolute output directory path from Step 4)
3. Call the Task tool with subagent_type: "general-purpose", description: "Generate PostgreSQL schema report", and the substituted prompt

After the subagent completes:

• Extract DURATION_SECONDS from the subagent's response → store as S2_DURATION
• Extract total_tokens from the ` block in the Task result (if present) → store as S2_TOKENS`

Check the subagent result:

• If STATUS is FAILURE → Display the error to the user. Note that raw_schema_data.json is still available for manual inspection. STOP HERE — do NOT proceed to Step 10.
• If STATUS is SUCCESS → Note the summary and proceed to Step 10.

---

STEP 10: Subagents 3 & 4 — Migration Analysis + SP & Trigger Migration (Parallel)

Both subagents run simultaneously in a single message — send both Task tool calls together in one response. They share the same inputs (postgresql_schema_report.md and raw_schema_data.json) and have no dependency on each other's output.

Preparation:

1. Read the prompt template at: ${PLUGIN_ROOT}/skills/common/subagents/postgresql/3-migration-analysis.md

• Substitute all `` with the actual absolute output directory path from Step 4

1. Read the prompt template at: ${PLUGIN_ROOT}/skills/common/subagents/postgresql/4-sp-trigger-migration.md

• Substitute all `` with the actual absolute output directory path from Step 4

Spawn both in one message:

• Task call A: subagent_type: "general-purpose", description: "Generate PostgreSQL migration docs", substituted prompt from 3-migration-analysis.md
• Task call B: subagent_type: "general-purpose", description: "Generate SP & trigger migration code", substituted prompt from 4-sp-trigger-migration.md

After both subagents complete:

• From Subagent 3 result: extract DURATION_SECONDS → store as S3_DURATION; extract total_tokens from ` block → store as S3_TOKENS`
• From Subagent 4 result: extract DURATION_SECONDS → store as S4_DURATION; extract total_tokens from ` block → store as S4_TOKENS`
• Compute parallel wall-clock: S34_WALL = max(S3_DURATION, S4_DURATION)

Error cascading rules:

• Subagent 2 (Step 9) failed → do NOT spawn either Subagent 3 or 4 (both need postgresqlschemareport.md)
• Subagent 3 fails while Subagent 4 succeeds → capture both results, report Subagent 3 error, proceed with Subagent 4 output
• Subagent 4 fails while Subagent 3 succeeds → capture both results, report Subagent 4 error, pro

…

Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: wfukatsu
• Source: wfukatsu/nexus-architect
• License: MIT

Install and usage instructions live in the source repository linked above.