app-backup-supabase.yml

name: lomi · backup · supabase · db

on:
  workflow_dispatch:
  schedule:
    # Off-platform encrypted DB copy every 6 hours. This is the disaster-recovery
    # copy in our own S3; Supabase's own backups/PITR cover lower-RPO needs.
    - cron: "17 */6 * * *"

concurrency:
  group: lomi-backup-supabase-db
  cancel-in-progress: false

permissions:
  contents: read

jobs:
  backup:
    name: Back up database
    runs-on: ubuntu-latest
    timeout-minutes: 60
    env:
      SUPABASE_DB_URL: ${{ secrets.SUPABASE_DB_URL }}
      BACKUP_S3_BUCKET: ${{ secrets.BACKUP_S3_BUCKET }}
      BACKUP_S3_ENDPOINT: ${{ secrets.BACKUP_S3_ENDPOINT }}
      BACKUP_S3_ACCESS_KEY_ID: ${{ secrets.BACKUP_S3_ACCESS_KEY_ID }}
      BACKUP_S3_SECRET_ACCESS_KEY: ${{ secrets.BACKUP_S3_SECRET_ACCESS_KEY }}
      BACKUP_S3_PREFIX: ${{ vars.BACKUP_S3_PREFIX }}
      BACKUP_S3_REGION: ${{ vars.BACKUP_S3_REGION }}
      AGE_PUBLIC_KEY: ${{ secrets.BACKUP_AGE_PUBLIC_KEY }}
    steps:
      - name: Validate configuration
        run: |
          set -euo pipefail

          missing=0

          normalize_env_value() {
            local name="$1"
            local required="${2:-required}"
            local raw="${!name-}"
            local normalized

            normalized="$(printf '%s' "$raw" | tr -d '\r\n' | sed 's/^[[:space:]]*//; s/[[:space:]]*$//')"

            if [ -z "$normalized" ]; then
              if [ "$required" = "required" ]; then
                echo "::error::Missing required secret: ${name}"
                missing=1
              fi
              return
            fi

            if [ "$raw" != "$normalized" ]; then
              echo "::warning::${name} contained leading/trailing whitespace or line breaks; using a trimmed single-line value."
            fi

            export "$name=$normalized"
            printf '%s=%s\n' "$name" "$normalized" >> "$GITHUB_ENV"
          }

          for name in \
            SUPABASE_DB_URL \
            BACKUP_S3_BUCKET \
            BACKUP_S3_ENDPOINT \
            BACKUP_S3_ACCESS_KEY_ID \
            BACKUP_S3_SECRET_ACCESS_KEY \
            AGE_PUBLIC_KEY
          do
            normalize_env_value "$name"
          done

          normalize_env_value BACKUP_S3_PREFIX optional
          normalize_env_value BACKUP_S3_REGION optional

          if [ "$missing" -ne 0 ]; then
            exit 1
          fi

          if printf '%s' "$SUPABASE_DB_URL" | grep -q '[[:space:]]'; then
            echo "::error::SUPABASE_DB_URL must be a single-line Postgres connection string with no literal whitespace."
            exit 1
          fi

          case "$SUPABASE_DB_URL" in
            postgres://*|postgresql://*) ;;
            *)
              echo "::error::SUPABASE_DB_URL must start with postgres:// or postgresql://."
              exit 1
              ;;
          esac

      - name: Checkout
        uses: actions/checkout@v5

      # GitHub-hosted runners are IPv4-only; the direct Supabase host is IPv6-only.
      - name: Set up IPv6 egress
        uses: ./.github/actions/setup-ipv6-warp

      - name: Install backup tools
        run: |
          set -euo pipefail
          sudo apt-get update
          sudo apt-get install -y age jq curl gnupg lsb-release

          # Install the PostgreSQL 17 client. pg_dump/pg_dumpall must be >= the server
          # major version (Supabase runs Postgres 17), which is newer than Ubuntu's default.
          curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc \
            | sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/pgdg.gpg
          echo "deb [signed-by=/etc/apt/trusted.gpg.d/pgdg.gpg] https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" \
            | sudo tee /etc/apt/sources.list.d/pgdg.list >/dev/null
          sudo apt-get update
          sudo apt-get install -y postgresql-client-17

          # The ubuntu-latest runner ships a running PostgreSQL 16 cluster, so the
          # pg_dump/pg_dumpall on PATH are the postgresql-common "pg_wrapper" shim, which
          # resolves to the default local cluster's version (16) even after client 17 is
          # installed. That caused "server version mismatch: server 17.x, pg_dumpall 16.x".
          # Put the v17 binaries at the front of PATH (for all later steps) so the real
          # v17 tools are used and the wrapper is bypassed.
          echo "/usr/lib/postgresql/17/bin" >> "$GITHUB_PATH"

          pg_dumpall_bin="/usr/lib/postgresql/17/bin/pg_dumpall"
          "$pg_dumpall_bin" --version
          case "$("$pg_dumpall_bin" --version)" in
            *" 17."*) echo "pg_dumpall 17 confirmed." ;;
            *) echo "::error::Expected pg_dumpall 17 at ${pg_dumpall_bin}."; exit 1 ;;
          esac

      # Derive the direct (IPv6) connection from SUPABASE_DB_URL. If the secret still
      # points at the shared pooler, rewrite it to the direct host; otherwise use it
      # as-is. The result is exported as BACKUP_DB_URL for the steps below.
      - name: Resolve database connection
        run: |
          set -euo pipefail

          url="$SUPABASE_DB_URL"
          rest="${url#*://}"
          userinfo="${rest%%@*}"
          hostpath="${rest#*@}"
          user="${userinfo%%:*}"
          pass="${userinfo#*:}"
          hostport="${hostpath%%/*}"
          dbname="${hostpath#*/}"
          dbname="${dbname%%\?*}"
          host="${hostport%%:*}"
          [ -n "$dbname" ] || dbname="postgres"

          case "$host" in
            *.pooler.supabase.com)
              ref="${user#*.}"
              backup_db_url="postgresql://postgres:${pass}@db.${ref}.supabase.co:5432/${dbname}"
              echo "::notice::Pooler host detected; using direct IPv6 connection db.${ref}.supabase.co:5432 over Cloudflare WARP."
              ;;
            *)
              backup_db_url="$url"
              echo "::notice::Using SUPABASE_DB_URL host ${host} as-is."
              ;;
          esac

          echo "::add-mask::$pass"
          echo "::add-mask::$backup_db_url"
          printf 'BACKUP_DB_URL=%s\n' "$backup_db_url" >> "$GITHUB_ENV"

      - name: Preflight database connection
        run: |
          set -euo pipefail

          echo "Checking database reachability before backup..."

          db_host="$(printf '%s' "$BACKUP_DB_URL" | sed -E 's#^[a-z]+://[^@]+@([^:/]+).*#\1#')"
          db_port="$(printf '%s' "$BACKUP_DB_URL" | sed -nE 's#^[a-z]+://[^@]+@[^:/]+:([0-9]+)/.*#\1#p')"
          echo "Target: ${db_host}:${db_port:-5432}"

          stderr_file="$(mktemp)"
          trap 'rm -f "$stderr_file"' EXIT

          if PGCONNECT_TIMEOUT=20 psql "$BACKUP_DB_URL" -v ON_ERROR_STOP=1 -tAc "SELECT 1" >/dev/null 2>"$stderr_file"; then
            echo "Database connection OK."
            exit 0
          fi

          err="$(tr '\n' ' ' <"$stderr_file" | sed 's/  */ /g')"

          if printf '%s' "$err" | grep -qiE 'connection to database not available|EAUTHQUERY|ECIRCUITBREAKER'; then
            echo "::error::Pooler reported it cannot reach Postgres (EAUTHQUERY/ECIRCUITBREAKER). This backup targets the direct host, so SUPABASE_DB_URL likely still points at the pooler and was not rewritten. Verify the secret is a valid Supabase connection string."
            exit 1
          fi

          if printf '%s' "$err" | grep -qiE 'Network is unreachable|No route to host|could not connect to server'; then
            echo "::error::Cannot reach ${db_host}. The direct Supabase host is IPv6-only; confirm the Cloudflare WARP step established IPv6 egress (see the 'Verify IPv6 connectivity' step)."
            exit 1
          fi

          if printf '%s' "$err" | grep -qi 'password authentication failed'; then
            echo "::error::Authentication failed for the direct connection (user 'postgres'). Update the password in the SUPABASE_DB_URL secret from Supabase Dashboard → Project Settings → Database."
            exit 1
          fi

          if printf '%s' "$err" | grep -qiE 'could not translate host name|Name or service not known|nodename nor servname'; then
            echo "::error::Cannot resolve ${db_host}. Check the project ref encoded in SUPABASE_DB_URL."
            exit 1
          fi

          if printf '%s' "$err" | grep -qiE 'timeout expired|timed out'; then
            echo "::error::Connection to ${db_host} timed out. Likely an IPv6/WARP issue; re-run in a few minutes."
            exit 1
          fi

          echo "::error::Database preflight failed: ${err}"
          exit 1

      - name: Create database dump
        id: db-dump
        run: |
          set -euo pipefail

          export AWS_ACCESS_KEY_ID="$BACKUP_S3_ACCESS_KEY_ID"
          export AWS_SECRET_ACCESS_KEY="$BACKUP_S3_SECRET_ACCESS_KEY"
          export AWS_DEFAULT_REGION="${BACKUP_S3_REGION:-auto}"
          export AWS_EC2_METADATA_DISABLED="true"

          timestamp="$(date -u +'%Y%m%dT%H%M%SZ')"
          backup_dir="supabase-db-${timestamp}"
          archive="${backup_dir}.tar.gz"
          encrypted_archive="${archive}.age"
          checksum_file="${encrypted_archive}.sha256"
          manifest_file="${backup_dir}.manifest.json"
          prefix="${BACKUP_S3_PREFIX:-supabase}"
          latest_manifest="latest-db-manifest.json"
          endpoint_args=()

          if [ -n "${BACKUP_S3_ENDPOINT:-}" ]; then
            endpoint_args=(--endpoint-url "$BACKUP_S3_ENDPOINT")
          fi

          mkdir -p "$backup_dir"

          # The three dumps below replicate `supabase db dump` (--role-only, schema,
          # --data-only --use-copy) using the native PG17 client, so we can run them
          # over the direct IPv6 connection without Docker. The flags and sed filters
          # are taken verbatim from `supabase db dump --dry-run` to preserve Supabase's
          # filtering (excluded internal schemas, stripped reserved roles, idempotent
          # IF NOT EXISTS clauses). Do not "simplify" these into a plain pg_dump: that
          # would include Supabase internals and break restores.

          # Roles (equivalent of: supabase db dump --role-only)
          pg_dumpall -d "$BACKUP_DB_URL" \
            --roles-only \
            --role "postgres" \
            --quote-all-identifiers \
            --no-role-passwords \
            --no-comments \
          | sed -E 's/^\\(un)?restrict .*$/-- &/' \
          | sed -E "s/^CREATE ROLE \"(anon|authenticated|authenticator|cli_login_.*|dashboard_user|pgbouncer|postgres|service_role|supabase_.*|pgsodium_keyholder|pgsodium_keyiduser|pgsodium_keymaker|pgtle_admin)\"/-- &/" \
          | sed -E "s/^ALTER ROLE \"(anon|authenticated|authenticator|cli_login_.*|dashboard_user|pgbouncer|postgres|service_role|supabase_.*|pgsodium_keyholder|pgsodium_keyiduser|pgsodium_keymaker|pgtle_admin)\"/-- &/" \
          | sed -E "s/ (NOSUPERUSER|NOREPLICATION)//g" \
          | sed -E "s/^-- (.* SET \"(pgaudit.*|pgrst.*|session_replication_role|statement_timeout|track_io_timing)\" .*)/\1/" \
          | sed -E "s/GRANT \".*\" TO \"(anon|authenticated|authenticator|cli_login_.*|dashboard_user|pgbouncer|postgres|service_role|supabase_.*|pgsodium_keyholder|pgsodium_keyiduser|pgsodium_keymaker|pgtle_admin)\"/-- &/" \
          | sed -E "/^--/d" \
          | uniq > "$backup_dir/roles.sql"
          echo "RESET ALL;" >> "$backup_dir/roles.sql"

          # Schema (equivalent of: supabase db dump)
          pg_dump -d "$BACKUP_DB_URL" \
            --schema-only \
            --quote-all-identifiers \
            --role "postgres" \
            --exclude-schema "information_schema|pg_*|_analytics|_realtime|_supavisor|auth|etl|extensions|pgbouncer|realtime|storage|supabase_functions|supabase_migrations|cron|dbdev|graphql|graphql_public|net|pgmq|pgsodium|pgsodium_masks|pgtle|repack|tiger|tiger_data|timescaledb_*|_timescaledb_*|topology|vault" \
          | sed -E 's/^\\(un)?restrict .*$/-- &/' \
          | sed -E 's/^CREATE SCHEMA "/CREATE SCHEMA IF NOT EXISTS "/' \
          | sed -E 's/^CREATE TABLE "/CREATE TABLE IF NOT EXISTS "/' \
          | sed -E 's/^CREATE SEQUENCE "/CREATE SEQUENCE IF NOT EXISTS "/' \
          | sed -E 's/^CREATE VIEW "/CREATE OR REPLACE VIEW "/' \
          | sed -E 's/^CREATE FUNCTION "/CREATE OR REPLACE FUNCTION "/' \
          | sed -E 's/^CREATE TRIGGER "/CREATE OR REPLACE TRIGGER "/' \
          | sed -E 's/^CREATE PUBLICATION "supabase_realtime/-- &/' \
          | sed -E 's/^CREATE EVENT TRIGGER /-- &/' \
          | sed -E 's/^         WHEN TAG IN /-- &/' \
          | sed -E 's/^   EXECUTE FUNCTION /-- &/' \
          | sed -E 's/^ALTER EVENT TRIGGER /-- &/' \
          | sed -E 's/^ALTER PUBLICATION "supabase_realtime_/-- &/' \
          | sed -E 's/^ALTER FOREIGN DATA WRAPPER (.+) OWNER TO /-- &/' \
          | sed -E 's/^ALTER DEFAULT PRIVILEGES FOR ROLE "supabase_admin"/-- &/' \
          | sed -E 's/^GRANT ALL ON FOREIGN DATA WRAPPER (.+) TO "postgres" WITH GRANT OPTION/-- &/' \
          | sed -E "s/^GRANT (.+) ON (.+) \"(information_schema|pg_*|_analytics|_realtime|_supavisor|auth|etl|extensions|pgbouncer|realtime|storage|supabase_functions|supabase_migrations|cron|dbdev|graphql|graphql_public|net|pgmq|pgsodium|pgsodium_masks|pgtle|repack|tiger|tiger_data|timescaledb_*|_timescaledb_*|topology|vault)\"/-- &/" \
          | sed -E "s/^REVOKE (.+) ON (.+) \"(information_schema|pg_*|_analytics|_realtime|_supavisor|auth|etl|extensions|pgbouncer|realtime|storage|supabase_functions|supabase_migrations|cron|dbdev|graphql|graphql_public|net|pgmq|pgsodium|pgsodium_masks|pgtle|repack|tiger|tiger_data|timescaledb_*|_timescaledb_*|topology|vault)\"/-- &/" \
          | sed -E 's/^(CREATE EXTENSION IF NOT EXISTS "pg_tle").+/\1;/' \
          | sed -E 's/^(CREATE EXTENSION IF NOT EXISTS "pgsodium").+/\1;/' \
          | sed -E 's/^(CREATE EXTENSION IF NOT EXISTS "pgmq").+/\1;/' \
          | sed -E 's/^COMMENT ON EXTENSION (.+)/-- &/' \
          | sed -E 's/^CREATE POLICY "cron_job_/-- &/' \
          | sed -E 's/^ALTER TABLE "cron"/-- &/' \
          | sed -E 's/^SET transaction_timeout = 0;/-- &/' \
          | sed -E "/^--/d" > "$backup_dir/schema.sql"

          # Data (equivalent of: supabase db dump --data-only --use-copy -x storage.buckets_vectors -x storage.vector_indexes)
          {
            echo "SET session_replication_role = replica;"
            echo
            pg_dump -d "$BACKUP_DB_URL" \
              --data-only \
              --quote-all-identifiers \
              --role "postgres" \
              --exclude-schema "information_schema|pg_*|graphql|graphql_public|pgsodium|pgsodium_masks|pgtle|repack|tiger|tiger_data|timescaledb_*|_timescaledb_*|topology|vault|etl|extensions|pgbouncer|realtime|supabase_migrations|_analytics|_realtime|_supavisor" \
              --exclude-table "auth.schema_migrations" \
              --exclude-table "storage.migrations" \
              --exclude-table "supabase_functions.migrations" \
              --schema "*" \
              --exclude-table "storage.buckets_vectors" \
              --exclude-table "storage.vector_indexes" \
            | sed -E 's/^\\(un)?restrict .*$/-- &/'
            echo "RESET ALL;"
          } > "$backup_dir/data.sql"

          db_content_sha="$(
            sha256sum "$backup_dir/roles.sql" "$backup_dir/schema.sql" "$backup_dir/data.sql" \
              | sha256sum \
              | awk '{print $1}'
          )"

          previous_db_content_sha=""
          if aws s3 cp "s3://${BACKUP_S3_BUCKET}/${prefix}/db/latest-manifest.json" "$latest_manifest" "${endpoint_args[@]}" --only-show-errors; then
            previous_db_content_sha="$(jq -r '.db_content_sha // empty' "$latest_manifest")"
          fi

          if [ "$db_content_sha" = "$previous_db_content_sha" ]; then
            echo "DB_CHANGED=false" >> "$GITHUB_ENV"
            echo "Database dump content matches the latest uploaded backup; skipping DB archive upload."
            exit 0
          fi

          {
            echo "Backup created at ${timestamp}"
            echo
            echo "Restore order:"
            echo "1. roles.sql"
            echo "2. schema.sql"
            echo "3. data.sql"
          } > "$backup_dir/RESTORE.txt"

          tar -czf "$archive" "$backup_dir"
          age --recipient "$AGE_PUBLIC_KEY" --output "$encrypted_archive" "$archive"
          sha256sum "$encrypted_archive" > "$checksum_file"

          jq -n \
            --arg created_at "$timestamp" \
            --arg backup_file "$encrypted_archive" \
            --arg checksum_file "$checksum_file" \
            --arg db_content_sha "$db_content_sha" \
            '{
              created_at: $created_at,
              backup_file: $backup_file,
              checksum_file: $checksum_file,
              db_content_sha: $db_content_sha
            }' > "$manifest_file"

          echo "DB_CHANGED=true" >> "$GITHUB_ENV"
          echo "DB_BACKUP_FILE=$encrypted_archive" >> "$GITHUB_ENV"
          echo "DB_CHECKSUM_FILE=$checksum_file" >> "$GITHUB_ENV"
          echo "DB_MANIFEST_FILE=$manifest_file" >> "$GITHUB_ENV"

      - name: Upload database backup to object storage
        if: env.DB_CHANGED == 'true'
        run: |
          set -euo pipefail

          export AWS_ACCESS_KEY_ID="$BACKUP_S3_ACCESS_KEY_ID"
          export AWS_SECRET_ACCESS_KEY="$BACKUP_S3_SECRET_ACCESS_KEY"
          export AWS_DEFAULT_REGION="${BACKUP_S3_REGION:-auto}"
          export AWS_EC2_METADATA_DISABLED="true"

          prefix="${BACKUP_S3_PREFIX:-supabase}"
          destination="s3://${BACKUP_S3_BUCKET}/${prefix}/db"
          endpoint_args=()

          if [ -n "${BACKUP_S3_ENDPOINT:-}" ]; then
            endpoint_args=(--endpoint-url "$BACKUP_S3_ENDPOINT")
          fi

          aws s3 cp "$DB_BACKUP_FILE" "$destination/$DB_BACKUP_FILE" "${endpoint_args[@]}" --only-show-errors
          aws s3 cp "$DB_CHECKSUM_FILE" "$destination/$DB_CHECKSUM_FILE" "${endpoint_args[@]}" --only-show-errors
          aws s3 cp "$DB_MANIFEST_FILE" "$destination/latest-manifest.json" "${endpoint_args[@]}" --only-show-errors

          echo "Uploaded encrypted database backup to $destination/$DB_BACKUP_FILE"