Export GitHub Stargazers to BigQuery

id: github-stargazer-bigquery-export
namespace: company.team
description: |
  Export a repository's stargazers (user and starred date) to BigQuery on a
  schedule, so growth trends can be analyzed in SQL.

triggers:
  - id: weekly_export
    type: io.kestra.plugin.core.trigger.Schedule
    description: Refresh the stargazer table weekly; shipped disabled so it never
      runs before you configure secrets.
    cron: "0 6 * * 1"
    disabled: true

inputs:
  - id: repository
    type: STRING
    defaults: kestra-io/kestra
    description: Owner/repo whose stargazers to export.
  - id: max_pages
    type: INT
    defaults: 5
    description: Pages of 100 stargazers to fetch per run; keep small on first runs.
  - id: bigquery_table
    type: STRING
    defaults: my_dataset.stargazers
    description: Destination BigQuery table in project.dataset.table form.
  - id: gcp_project_id
    type: STRING
    defaults: my-project
    description: GCP project that owns the destination table.

tasks:
  - id: fetch_stargazers
    type: io.kestra.plugin.scripts.python.Script
    description: Page through the GitHub stargazers API (up to max_pages x 100
      rows), and write a CSV via the output directory protocol.
    containerImage: python:3.11-slim
    warningOnStdErr: false
    env:
      REPOSITORY: "{{ inputs.repository }}"
      MAX_PAGES: "{{ inputs.max_pages }}"
      GITHUB_TOKEN: "{{ secret('GITHUB_TOKEN') }}"
    script: |
      import csv
      import json
      import os
      import urllib.request

      repository = os.environ['REPOSITORY']
      max_pages = int(os.environ['MAX_PAGES'])
      token = os.environ['GITHUB_TOKEN']

      rows = []
      for page in range(1, max_pages + 1):
          request = urllib.request.Request(
              f'https://api.github.com/repos/{repository}/stargazers?per_page=100&page={page}',
              headers={'Accept': 'application/vnd.github.v3.star+json', 'Authorization': f'Bearer {token}', 'User-Agent': 'kestra-stargazer-export'},
          )
          with urllib.request.urlopen(request) as response:
              batch = json.loads(response.read())
          if not batch:
              break
          rows.extend(batch)

      with open('{{ outputDir }}/stargazers.csv', 'w', newline='') as f:
          writer = csv.writer(f)
          writer.writerow(['user', 'starred_at'])
          for item in rows:
              writer.writerow([item['user']['login'], item['starred_at']])
      print(f'exported {len(rows)} stargazers')
      print('::' + json.dumps({'outputs': {'count': len(rows)}}) + '::')
    outputFiles:
      - stargazers.csv

  - id: check_results
    type: io.kestra.plugin.core.flow.If
    description: Only touch BigQuery when the export actually produced rows — an
      empty week must not wipe the table.
    condition: "{{ outputs.fetch_stargazers.vars.count > 0 }}"
    then:
      - id: load_to_bigquery
        type: io.kestra.plugin.gcp.bigquery.Load
        description: Load the CSV into BigQuery, replacing the table contents with the
          fresh export.
        serviceAccount: "{{ secret('GCP_SERVICE_ACCOUNT') }}"
        projectId: "{{ inputs.gcp_project_id }}"
        destinationTable: "{{ inputs.bigquery_table }}"
        format: CSV
        from: "{{ outputs.fetch_stargazers.outputFiles['stargazers.csv'] }}"
        writeDisposition: WRITE_TRUNCATE
    else:
      - id: log_no_results
        type: io.kestra.plugin.core.log.Log
        description: Record the empty pull so a surprise zero rows is visible in the
          execution log instead of silently truncating.
        message: "No stargazers fetched for {{ inputs.repository }} — skipping BigQuery
          load. Check the token and repository name."

errors:
  - id: alert_export_failure
    type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
    description: Alert when the export or load fails — stale stars must never look
      like fresh ones.
    url: "{{ secret('SLACK_WEBHOOK_URL') }}"
    payload: |
      {
        "text": "Stargazer export FAILED in flow {{ flow.id }} (execution {{ execution.id }}) for {{ inputs.repository }}. Check GITHUB_TOKEN rate limits and GCP_SERVICE_ACCOUNT permissions."
      }

outputs:
  - id: export_count
    type: INT
    description: Number of stargazer rows exported in this run.
    value: "{{ outputs.fetch_stargazers.vars.count }}"