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