update-db.yaml

name: Update DB

on:
  push:
    branches:
      - main
  pull_request:
    branches:
      - main
  workflow_dispatch:

  schedule:
    - cron: '45 6 * * *' # 6:45am UTC every day

env:
  AWS_DEFAULT_REGION: us-west-2
  OFFSETS_DB_API_KEY_STAGING: ${{ secrets.OFFSETS_DB_API_KEY_STAGING }}
  OFFSETS_DB_API_KEY_PRODUCTION: ${{ secrets.OFFSETS_DB_API_KEY_PRODUCTION }}

concurrency:
  group: ${{ github.workflow }}-${{ github.ref }}
  cancel-in-progress: true

permissions:
  id-token: write # This is required for requesting the JWT
  contents: read # This is required for actions/checkout

jobs:
  seed-db:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4

      - name: configure aws credentials
        uses: aws-actions/configure-aws-credentials@v4
        with:
          role-to-assume: arn:aws:iam::631969445205:role/github-action-role
          role-session-name: offsets-db-update-role-session
          aws-region: ${{ env.AWS_DEFAULT_REGION }}

      - name: Get Current time in UTC in format YYYY-MM-DD HH:MM
        if: always()
        id: time
        run: echo "date=$(date -u +'%Y-%m-%d %H:%M')" >> $GITHUB_OUTPUT

      - uses: actions/setup-python@v5
        with:
          python-version: '3.10'
      - name: Install dependencies
        run: |
          python -m pip install --upgrade pip
          python -m pip install fsspec requests s3fs pandas

      - name: Seed Staging Database
        run: |
          python update_database.py staging https://offsets-db-staging.fly.dev/files/

      - name: Seed Production Database
        if: github.event_name == 'workflow_dispatch' || github.event_name == 'schedule'
        run: |
          python update_database.py production https://offsets-db.fly.dev/files/

      - name: Notify Slack on Failure
        if: failure() && (github.event_name == 'workflow_dispatch' || github.event_name == 'schedule')
        uses: slackapi/slack-github-action@v2.0.0
        with:
          method: chat.postMessage
          token: ${{ secrets.SLACK_BOT_TOKEN }}
          payload: |
            {
              "channel": "C06CXEYKMBP",
              "attachments": [
                {
                  "color": "#ff0000",
                  "blocks": [
                    {
                      "type": "header",
                      "text": {
                        "type": "plain_text",
                        "text": "Database Update Failure Alert 🚨"
                      }
                    },
                    {
                      "type": "section",
                      "fields": [
                        {
                          "type": "mrkdwn",
                          "text": "*GH event:*\n${{ github.event_name }}"
                        },
                        {
                          "type": "mrkdwn",
                          "text": "*Time:*\n${{ steps.time.outputs.date }} UTC"
                        }
                      ]
                    },
                    {
                      "type": "section",
                      "fields": [
                        {
                          "type": "mrkdwn",
                          "text": "*Details URL:*\n🔍 <${{ github.server_url }}/${{ github.repository }}/actions/runs/${{ github.run_id }}|Workflow Run>"
                        }
                      ]
                    }
                  ]
                }
              ]
            }