dashboard-nanobot/scripts/init-full-db.sh

#!/usr/bin/env bash
set -euo pipefail

ROOT_DIR="$(cd "$(dirname "$0")/.." && pwd)"
ENV_FILE="${1:-$ROOT_DIR/.env.full}"
COMPOSE_FILE="$ROOT_DIR/docker-compose.full.yml"
BOOTSTRAP_SQL="$ROOT_DIR/scripts/sql/init-postgres-bootstrap.sql"
APP_SQL="$ROOT_DIR/scripts/sql/init-postgres-app.sql"
SCHEMA_SQL="$ROOT_DIR/scripts/sql/create-tables.sql"
SEED_SQL="$ROOT_DIR/scripts/sql/init-data.sql"

require_file() {
  local path="$1"
  local hint="$2"
  if [[ -f "$path" ]]; then
    return 0
  fi
  echo "Missing file: $path"
  if [[ -n "$hint" ]]; then
    echo "$hint"
  fi
  exit 1
}

require_env() {
  local name="$1"
  if [[ -n "${!name:-}" ]]; then
    return 0
  fi
  echo "Missing required env: $name"
  exit 1
}

read_env_value() {
  local key="$1"
  local line=""
  local value=""

  while IFS= read -r line || [[ -n "$line" ]]; do
    line="${line%$'\r'}"
    [[ -z "${line//[[:space:]]/}" ]] && continue
    [[ "${line#\#}" != "$line" ]] && continue
    [[ "${line#export }" != "$line" ]] && line="${line#export }"
    [[ "$line" == "$key="* ]] || continue
    value="${line#*=}"
    if [[ "$value" =~ ^\"(.*)\"$ ]]; then
      value="${BASH_REMATCH[1]}"
    elif [[ "$value" =~ ^\'(.*)\'$ ]]; then
      value="${BASH_REMATCH[1]}"
    fi
    printf '%s' "$value"
    return 0
  done < "$ENV_FILE"

  return 1
}

load_env_var() {
  local name="$1"
  local default_value="${2:-}"
  local value=""

  value="$(read_env_value "$name" || true)"
  if [[ -z "$value" ]]; then
    value="$default_value"
  fi
  printf -v "$name" '%s' "$value"
}

wait_for_postgres() {
  local timeout_seconds="${1:-120}"
  local elapsed=0

  while (( elapsed < timeout_seconds )); do
    if docker compose --env-file "$ENV_FILE" -f "$COMPOSE_FILE" exec -T \
      -e PGPASSWORD="$POSTGRES_SUPERPASSWORD" \
      postgres \
      pg_isready -U "$POSTGRES_SUPERUSER" -d "$POSTGRES_BOOTSTRAP_DB" >/dev/null 2>&1; then
      return 0
    fi
    sleep 2
    elapsed=$((elapsed + 2))
  done

  echo "[init-full-db] timed out waiting for postgres"
  docker compose --env-file "$ENV_FILE" -f "$COMPOSE_FILE" logs --tail 100 postgres || true
  exit 1
}

require_file "$ENV_FILE" "Create it from: $ROOT_DIR/.env.full.example"
require_file "$COMPOSE_FILE" ""
require_file "$BOOTSTRAP_SQL" ""
require_file "$APP_SQL" ""
require_file "$SCHEMA_SQL" ""
require_file "$SEED_SQL" ""

load_env_var POSTGRES_SUPERUSER postgres
load_env_var POSTGRES_SUPERPASSWORD
load_env_var POSTGRES_BOOTSTRAP_DB postgres
load_env_var POSTGRES_APP_DB
load_env_var POSTGRES_APP_USER
load_env_var POSTGRES_APP_PASSWORD
load_env_var UPLOAD_MAX_MB 100
load_env_var STT_ENABLED true

require_env POSTGRES_SUPERUSER
require_env POSTGRES_SUPERPASSWORD
require_env POSTGRES_BOOTSTRAP_DB
require_env POSTGRES_APP_DB
require_env POSTGRES_APP_USER
require_env POSTGRES_APP_PASSWORD

docker compose --env-file "$ENV_FILE" -f "$COMPOSE_FILE" up -d postgres >/dev/null

wait_for_postgres 120

echo "[init-full-db] ensuring role/database exist"
docker compose --env-file "$ENV_FILE" -f "$COMPOSE_FILE" exec -T \
  -e PGPASSWORD="$POSTGRES_SUPERPASSWORD" \
  postgres \
  psql \
  -v ON_ERROR_STOP=1 \
  -v app_db="$POSTGRES_APP_DB" \
  -v app_user="$POSTGRES_APP_USER" \
  -v app_password="$POSTGRES_APP_PASSWORD" \
  -U "$POSTGRES_SUPERUSER" \
  -d "$POSTGRES_BOOTSTRAP_DB" \
  -f - < "$BOOTSTRAP_SQL"

echo "[init-full-db] ensuring schema privileges in $POSTGRES_APP_DB"
docker compose --env-file "$ENV_FILE" -f "$COMPOSE_FILE" exec -T \
  -e PGPASSWORD="$POSTGRES_SUPERPASSWORD" \
  postgres \
  psql \
  -v ON_ERROR_STOP=1 \
  -v app_user="$POSTGRES_APP_USER" \
  -U "$POSTGRES_SUPERUSER" \
  -d "$POSTGRES_APP_DB" \
  -f - < "$APP_SQL"

echo "[init-full-db] applying application schema"
docker compose --env-file "$ENV_FILE" -f "$COMPOSE_FILE" exec -T \
  -e PGPASSWORD="$POSTGRES_SUPERPASSWORD" \
  postgres \
  psql \
  -v ON_ERROR_STOP=1 \
  -U "$POSTGRES_SUPERUSER" \
  -d "$POSTGRES_APP_DB" \
  -f - < "$SCHEMA_SQL"

PAGE_SIZE_JSON="10"
CHAT_PULL_PAGE_SIZE_JSON="60"
COMMAND_AUTO_UNLOCK_SECONDS_JSON="10"
AUTH_TOKEN_TTL_HOURS_JSON="24"
AUTH_TOKEN_MAX_ACTIVE_JSON="2"
UPLOAD_MAX_MB_JSON="$UPLOAD_MAX_MB"
ALLOWED_ATTACHMENT_EXTENSIONS_JSON="[]"
WORKSPACE_DOWNLOAD_EXTENSIONS_JSON='[".pdf", ".doc", ".docx", ".xls", ".xlsx", ".xlsm", ".ppt", ".pptx", ".odt", ".ods", ".odp", ".wps"]'
if [[ "${STT_ENABLED,,}" =~ ^(1|true|yes|on)$ ]]; then
  SPEECH_ENABLED_JSON="true"
else
  SPEECH_ENABLED_JSON="false"
fi
ACTIVITY_EVENT_RETENTION_DAYS_JSON="7"

echo "[init-full-db] applying initial data"
docker compose --env-file "$ENV_FILE" -f "$COMPOSE_FILE" exec -T \
  -e PGPASSWORD="$POSTGRES_SUPERPASSWORD" \
  postgres \
  psql \
  -v ON_ERROR_STOP=1 \
  -v page_size_json="$PAGE_SIZE_JSON" \
  -v chat_pull_page_size_json="$CHAT_PULL_PAGE_SIZE_JSON" \
  -v command_auto_unlock_seconds_json="$COMMAND_AUTO_UNLOCK_SECONDS_JSON" \
  -v auth_token_ttl_hours_json="$AUTH_TOKEN_TTL_HOURS_JSON" \
  -v auth_token_max_active_json="$AUTH_TOKEN_MAX_ACTIVE_JSON" \
  -v upload_max_mb_json="$UPLOAD_MAX_MB_JSON" \
  -v allowed_attachment_extensions_json="$ALLOWED_ATTACHMENT_EXTENSIONS_JSON" \
  -v workspace_download_extensions_json="$WORKSPACE_DOWNLOAD_EXTENSIONS_JSON" \
  -v speech_enabled_json="$SPEECH_ENABLED_JSON" \
  -v activity_event_retention_days_json="$ACTIVITY_EVENT_RETENTION_DAYS_JSON" \
  -U "$POSTGRES_SUPERUSER" \
  -d "$POSTGRES_APP_DB" \
  -f - < "$SEED_SQL"

echo "[init-full-db] done"