[CI/CD Improvements] Update and simplify CI workflows

[mikealfare]

Is this your first time submitting a feature request?

• I have read the expectations for open source contributors
• I have searched the existing issues, and I could not find an existing issue for this feature
• I am requesting a straightforward extension of existing dbt-postgres functionality, rather than a Big Idea better suited to a discussion

Describe the feature

As a maintainer, I want to simplify the CI jobs, so that I know I am running the same CI regardless of scenario (e.g. PR vs scheduled tests vs end to end tests for dependent packages).

Acceptance Criteria

Preview Give feedback

1. All workflows adhere to the style standards below
2. One CI workflow runs all checks: integration tests, unit tests, code quality tests, build verification, psycopg2 test
3. Branch protection rules are limited to a single job, regardless of OS, python version, etc.
4. I can manually run a CI workflow; optionally, I can provide specific branches for dbt dependencies (dbt-adapters, dbt-common, etc.) similar to what is done with integration-tests.yml today; the current workflow is cancelled in favor of the new workflow if the same run is triggered with the same branches
5. I can trigger a CI workflow from another repo for end to end testing, similar to what is done with integration-tests.yml today
6. A CI workflow runs on every PR; the current workflow is cancelled in favor of the new workflow when the PR is updated
7. A CI workflow runs against each protected branch (main, 1.8.latest, etc.) every week; the current workflow is cancelled in favor of the new workflow if the CI workflow is triggered for the same branch (shouldn't happen since the workflow should finish in far less than a week)
8. I'm able to easily see what triggered each CI workflow in the Actions tab (e.g. don't use a generic run name or job name of "Adapter Integration Tests")
9. Integration tests are only configured in one place (e.g. environment variables, the Postgres service, etc.)
10. Legacy workflows are removed (e.g. code-quality.yml, integration-tests.yml, unit-tests.yml, etc.)

Suggestions

• Create three workflows:
  • ci-checks.yml
    • Run on workflow_call and workflow_dispatch
    • Allow for updating branches, default to main
  • ci-pull-request-checks.yml
    • Run on pull_request_target
    • Don't allow for updating branches
    • Only run for external PRs if approved
  • ci-scheduled-checks.yml
    • Run on schedule: 18:00 UTC
    • Don't allow for updating branches
    • Alert on failure

While these workflows all do roughly the same thing, they are different enough (e.g. concurrency, updating upstream branches, alerting, etc.) that it might be easier to manage as separate workflows.

Style standards:

• Job ids, step ids, and variables are in kebab case
• Job names, step names, and description fields follow dbt docs standards (capitalize first word only)
• Extra descriptors should be avoided unless required for disambiguation, e.g.
  • version-number -> version
  • archive-name -> archive
• Workflow files use a four space tab
• Scripts (inline or separate files) use environment variables in env instead of inline substitution like ${{ inputs.value }}

[mikealfare]

This will happen as part of the monorepo work, hence this is duplicative.