Find EOL Docker Images, Missing Licenses, and Stale CODEOWNERS Across Thousands of repos with SQL

kind: source
spec:
  name: git
  path: cloudquery/git
  version: 'v1.0.0'
  tables:
    - git_repositories
    - git_files:
        glob_patterns:
          - '**/Dockerfile*'
          - '**/pom.xml'
          - '.buildkite/**/pipeline.yml'
          - '**/CODEOWNERS'
          - '**/package.json'
          - '**/go.mod'
          - '**/.github/workflows/*.yml'
    - git_commits
  destinations:
    - postgresql
  spec:
    # Repository filtering
    repositories:
      - 'https://github.com/your-org/*'
    # Authentication
    token: '${GIT_TOKEN}'

WITH repos_with_license AS (
  SELECT gf.repository_url FROM git_files gf WHERE gf.name = 'LICENSE'
)
SELECT gr.url, gr.owner, gr.full_name FROM git_repositories gr
LEFT OUTER JOIN repos_with_license rl ON rl.repository_url = gr.url
WHERE rl.repository_url IS NULL

WITH dockerfiles_parsed AS (
  SELECT repository_url, path, name,
         regexp_split_to_table(convert_from(content, 'UTF8'), E'\n') AS line
  FROM git_files
  WHERE name = 'Dockerfile'
),
images AS (
  SELECT repository_url, path, regexp_replace(line, '^FROM\s+', '') AS image
  FROM dockerfiles_parsed
  WHERE line ~ '^FROM\s+'
)
SELECT * FROM images WHERE image LIKE 'node:18%'

WITH package_data AS (
  SELECT
    repository_url,
    path,
    convert_from(content, 'UTF8')::jsonb AS package_json
  FROM git_files
  WHERE name = 'package.json' AND content IS NOT NULL
),
all_dependencies AS (
  SELECT
    repository_url,
    package_json->>'name' AS package_name,
    'dependencies' AS dependency_type,
    dep_key AS dependency_name,
    package_json->'dependencies'->>dep_key AS dependency_version
  FROM package_data,
  LATERAL jsonb_object_keys(package_json->'dependencies') AS dep_key
  WHERE package_json->'dependencies' IS NOT NULL

  UNION ALL

  SELECT
    repository_url,
    package_json->>'name' AS package_name,
    'devDependencies' AS dependency_type,
    dep_key AS dependency_name,
    package_json->'devDependencies'->>dep_key AS dependency_version
  FROM package_data,
  LATERAL jsonb_object_keys(package_json->'devDependencies') AS dep_key
  WHERE package_json->'devDependencies' IS NOT NULL
)
SELECT * FROM all_dependencies WHERE dependency_name = 'react'

WITH codeowners_parsed AS (
  SELECT
    gf.repository_url,
    regexp_matches(
      convert_from(gf.content, 'UTF8'),
      '(?:^|\s)@([a-zA-Z0-9_-]+(?:/[a-zA-Z0-9_-]+)?)',
      'gm'
    ) AS owner_handle_match
  FROM git_files gf
  WHERE gf.name = 'CODEOWNERS'
),
owners_flat AS (
  SELECT DISTINCT
    repository_url,
    owner_handle_match[1] AS github_handle
  FROM codeowners_parsed
  WHERE owner_handle_match[1] NOT LIKE '%/%'  -- Exclude team handles like org/team
),
owners_with_email AS (
  SELECT
    o.repository_url,
    o.github_handle,
    gm.email AS github_email
  FROM owners_flat o
  LEFT JOIN github_organization_members gm ON lower(o.github_handle) = lower(gm.login)
)
SELECT
  owe.repository_url,
  owe.github_handle,
  owe.github_email,
  COALESCE(u.status, 'NOT_FOUND') AS okta_status
FROM owners_with_email owe
LEFT JOIN okta_users u ON lower(owe.github_email) = lower(u.email)
WHERE owe.github_email IS NULL  -- GitHub user not found or has no public email
   OR u.status IS NULL          -- No matching Okta account
   OR u.status != 'ACTIVE'      -- Okta account inactive
ORDER BY owe.repository_url

# Example: Scheduled sync with GitHub Actions
name: Sync Git Data
on:
  schedule:
    - cron: '0 6 * * *' # Daily at 6am UTC
jobs:
  sync:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - name: Sync git data
        run: cloudquery sync config.yml
        env:
          GIT_TOKEN: ${{ secrets.GIT_TOKEN }}