Skip to main content

Pre-Merge Validation for Metabase TCP Setup

Before merging the Metabase TCP-based setup changes, run these validation commands to ensure everything is configured correctly.

Quick Validation Script​

Run the automated pre-merge validation script:

./scripts/validate-metabase-pre-merge.sh

This script checks:

  • ✅ Dockerfile contains Cloud SQL Proxy installation
  • ✅ Dockerfile has startup script with CLOUD_SQL_INSTANCES handling
  • ✅ Workflow uses MB_DB_HOST=localhost and MB_DB_PORT=5432
  • ✅ Workflow includes CLOUD_SQL_INSTANCES environment variable
  • ✅ Workflow does NOT use --add-cloudsql-instances (we run proxy ourselves)
  • ✅ Connection string format is valid
  • ✅ Cloud SQL instances exist and are RUNNABLE
  • ✅ Service account has roles/cloudsql.client permission

Manual Validation with gcloud​

1. Validate Cloud SQL Instances​

# Staging
gcloud sql instances describe metabase-db-staging \
  --project=barto-dev \
  --format="value(state)"

gcloud sql instances describe flowpos-db \
  --project=barto-dev \
  --format="value(state)"

# Production
gcloud sql instances describe metabase-db-production \
  --project=barto-prod \
  --format="value(state)"

gcloud sql instances describe flowpos-db-production \
  --project=barto-prod \
  --format="value(state)"

Expected output: RUNNABLE

2. Validate Service Account Permissions​

# Staging
PROJECT_NUMBER="723334209984"
COMPUTE_SA="${PROJECT_NUMBER}-compute@developer.gserviceaccount.com"

gcloud projects get-iam-policy barto-dev \
  --flatten="bindings[].members" \
  --filter="bindings.members:serviceAccount:$COMPUTE_SA AND bindings.role:roles/cloudsql.client" \
  --format="value(bindings.role)"

# Production
PROJECT_NUMBER="136147256555"
COMPUTE_SA="${PROJECT_NUMBER}-compute@developer.gserviceaccount.com"

gcloud projects get-iam-policy barto-prod \
  --flatten="bindings[].members" \
  --filter="bindings.members:serviceAccount:$COMPUTE_SA AND bindings.role:roles/cloudsql.client" \
  --format="value(bindings.role)"

Expected output: roles/cloudsql.client

3. Test Cloud SQL Proxy Connection Locally​

Test that Cloud SQL Proxy can connect to the instances:

# Staging
./scripts/test-cloudsql-proxy-connection.sh staging

# Production
./scripts/test-cloudsql-proxy-connection.sh production

This will:

  • Download Cloud SQL Proxy if needed
  • Start the proxy on localhost:5432
  • Test the connection
  • Keep the proxy running for manual testing

Press Ctrl+C to stop the script (proxy will continue running).

4. Validate Existing Service Configuration (if service exists)​

# Staging
SERVICE_NAME="flowpos-metabase-staging"
REGION="us-central1"
PROJECT_ID="barto-dev"

# Get latest revision
LATEST_REVISION=$(gcloud run revisions list \
  --service="$SERVICE_NAME" \
  --region="$REGION" \
  --project="$PROJECT_ID" \
  --format="value(name)" \
  --limit=1)

# Check environment variables
gcloud run revisions describe "$LATEST_REVISION" \
  --region="$REGION" \
  --project="$PROJECT_ID" \
  --format="value(spec.template.spec.containers[0].env[?(@.name=='MB_DB_HOST')].value)"

gcloud run revisions describe "$LATEST_REVISION" \
  --region="$REGION" \
  --project="$PROJECT_ID" \
  --format="value(spec.template.spec.containers[0].env[?(@.name=='MB_DB_PORT')].value)"

gcloud run revisions describe "$LATEST_REVISION" \
  --region="$REGION" \
  --project="$PROJECT_ID" \
  --format="value(spec.template.spec.containers[0].env[?(@.name=='CLOUD_SQL_INSTANCES')].value)"

# Check that Cloud SQL annotation is NOT set
gcloud run revisions describe "$LATEST_REVISION" \
  --region="$REGION" \
  --project="$PROJECT_ID" \
  --format="value(spec.template.metadata.annotations['run.googleapis.com/cloudsql-instances'])"

# Check that VPC connector is NOT set
gcloud run revisions describe "$LATEST_REVISION" \
  --region="$REGION" \
  --project="$PROJECT_ID" \
  --format="value(spec.template.metadata.annotations['run.googleapis.com/vpc-access-connector'])"

Expected values:

  • MB_DB_HOST: localhost
  • MB_DB_PORT: 5432
  • CLOUD_SQL_INSTANCES: barto-dev:us-central1:metabase-db-staging,barto-dev:us-central1:flowpos-db (or production equivalents)
  • Cloud SQL annotation: (empty)
  • VPC connector: (empty)

5. Full Validation Script​

Run the comprehensive validation script:

./scripts/validate-metabase-setup.sh

This checks both staging and production environments.

What Changed​

Before (Unix Sockets - Not Supported by Metabase)​

  • Used --add-cloudsql-instances annotation
  • Set MB_DB_HOST to Unix socket path (/cloudsql/...)
  • Did NOT set MB_DB_PORT
  • Result: Metabase constructed invalid JDBC URL

After (TCP via Cloud SQL Proxy)​

  • Run Cloud SQL Proxy in container (TCP mode on localhost:5432)
  • Set MB_DB_HOST=localhost
  • Set MB_DB_PORT=5432
  • Set CLOUD_SQL_INSTANCES environment variable
  • Do NOT use --add-cloudsql-instances annotation
  • Result: Metabase connects via standard TCP connection

Key Files Changed​

  1. deploy/gcp/metabase.Dockerfile

    • Installs Cloud SQL Proxy
    • Creates startup script that runs proxy in TCP mode
    • Starts Metabase after proxy is ready

  2. .github/workflows/deploy-metabase.yml

    • Sets MB_DB_HOST=localhost
    • Sets MB_DB_PORT=5432
    • Sets CLOUD_SQL_INSTANCES environment variable
    • Removes --add-cloudsql-instances annotation

Troubleshooting​

If validation fails:

  1. Cloud SQL instances not RUNNABLE

    gcloud sql instances list --project=barto-dev

  2. Service account missing permissions

    PROJECT_NUMBER="723334209984"
    COMPUTE_SA="${PROJECT_NUMBER}-compute@developer.gserviceaccount.com"

    gcloud projects add-iam-policy-binding barto-dev \
      --member="serviceAccount:$COMPUTE_SA" \
      --role="roles/cloudsql.client"

  3. Dockerfile syntax errors

    docker build -f deploy/gcp/metabase.Dockerfile -t test-metabase .

  4. Workflow YAML syntax errors

    # Check YAML syntax
    yamllint .github/workflows/deploy-metabase.yml

Next Steps​

After validation passes:

  1. ✅ Merge the PR
  2. ✅ GitHub Actions will automatically deploy
  3. ✅ Monitor deployment logs
  4. ✅ Verify Metabase connects successfully