Skip to content

github-app-setup.md

Latest commit

 

History

History
312 lines (224 loc) · 13.5 KB

github-app-setup.md

File metadata and controls

312 lines (224 loc) · 13.5 KB
Raw

GitHub App Setup

Table of Contents

This guide walks through creating a GitHub App, configuring SchemaBot to receive webhooks, and adding the schemabot.yaml config to your repositories.

1. Create a GitHub App

Go to Settings > Developer settings > GitHub Apps > New GitHub App (direct link).

Basic Information

Field Value
GitHub App name SchemaBot (or your preferred name)
Homepage URL Your SchemaBot deployment URL
Description Declarative schema change orchestration via PR comments

Webhook

Field Value
Active Checked
Webhook URL https://your-domain.com/webhook
Webhook secret Generate a random secret (save it for later)

Generate a webhook secret:

openssl rand -hex 32

Permissions

Under Repository permissions, grant:

Permission Access Used For
Checks Read & Write Create SchemaBot check runs and read GitHub check runs for the require_passing_checks gate
Commit statuses Read Read legacy commit statuses for the require_passing_checks gate
Contents Read Read schemabot.yaml, schema files, CODEOWNERS, and commit refs
Issues Read & Write Post PR comments and add reactions
Merge queues Read Receive merge_group events so SchemaBot can publish its checks on merge-queue commits (only needed if a repo uses a merge queue)
Metadata Read Required (granted automatically)
Pull requests Read & Write Fetch PR info, changed files, and reviews

Under Organization permissions, grant:

Permission Access Used For
Members Read Verify GitHub team membership for review policies, CODEOWNERS review gates, and PR command authorization

Subscribe to Events

Event Purpose
Check run Receive check_run.rerequested so a human clicking Re-run on a SchemaBot check re-plans the PR head
Check suite Receive check_suite.requested as a redundant auto-plan convergence signal: when the organic pull_request delivery for a push is lost upstream, SchemaBot recovers the missing auto-plan from the check suite instead of waiting for the next reconciler scan
Issue comment Receive schemabot plan, schemabot help, etc. from PR comments
Merge group Publish a passing SchemaBot check on a merge-queue commit so a required SchemaBot check does not block the merge queue (only needed if a repo uses a merge queue)
Pull request Auto-plan each affected database on PR open/synchronize/reopen and clean up locks and stored check state on close
Push Publish a passing SchemaBot check on default-branch commits so branch rulesets can select the App as a pinned required-check source (rulesets only index Apps whose check suites ran against the target branch)

Subscribe to Merge group, not Merge queue entry — the two are distinct events, and SchemaBot handles only merge_group. The Merge group event requires the Merge queues: Read repository permission above. Because that is a new permission, adding it to an existing App marks the App as requesting new permissions, which an org or repository admin must approve on each installation before it takes effect.

The Push event needs no new permission — Contents: Read above already unlocks it — so subscribing an existing App takes effect immediately on every installation, with no admin re-approval. Expect webhook delivery volume to rise once subscribed: GitHub sends a push event for every branch and tag push on installed repositories, and SchemaBot discards everything but default-branch pushes.

The Check suite event likewise needs no new permission — Checks: Read & Write above already unlocks it — so subscribing takes effect immediately. It also raises delivery volume (GitHub sends check_suite.requested for every push to every branch), and SchemaBot acknowledges and drops every suite that has no open PR at its head. Without this subscription the recovery feature receives no deliveries and is silently inert: lost auto-plans then wait for the reconciler's next scan instead of converging within the recovery grace.

Where Can This GitHub App Be Installed?

Choose Only on this account for private use, or Any account if you plan to share the app.

Create the App

Click Create GitHub App. Note the App ID shown on the next page.

2. Generate a Private Key

On the app settings page, scroll to Private keys and click Generate a private key.

A .pem file will be downloaded. Store it securely — this is your app's authentication credential.

3. Install the App

Go to your app's settings page, click Install App in the sidebar, and install it on your organization or account.

You can restrict it to specific repositories or grant access to all repositories.

4. Configure SchemaBot

Add the github: section to your SchemaBot server config (config.yaml):

storage:
  dsn: "env:SCHEMABOT_DSN"

github:
  app-id: "123456"                                 # From step 1
  private-key: "file:/path/to/private-key.pem"     # PEM file
  webhook-secret: "env:GITHUB_WEBHOOK_SECRET"      # From step 1
  check-name: "SchemaBot X"                      # Optional Check Run base name

databases:
  mydb:
    type: mysql
    environments:
      staging:
        dsn: "env:STAGING_DSN"
      production:
        dsn: "env:PRODUCTION_DSN"

The private-key and webhook-secret fields support secret references — the same format used for DSNs:

Format Example
Direct value "my-secret"
Environment variable "env:GITHUB_WEBHOOK_SECRET"
File "file:/run/secrets/github-key.pem"
AWS Secrets Manager "secretsmanager:my-app/github#private-key"

For AWS deployments, see deploy/aws/ which stores credentials in Secrets Manager.

5. Start SchemaBot

schemabot serve

You should see:

{"level":"INFO","msg":"GitHub webhook endpoint registered"}
{"level":"INFO","msg":"starting server","port":"8080"}

6. Add schemabot.yaml Config to Your Repository

Create a schemabot.yaml file in the directory containing your schema SQL files:

my-repo/
  schema/
    schemabot.yaml      <-- config file
    users.sql
    orders.sql
    products.sql

database: mydb
type: mysql
Field Required Description
database Yes Must match a database name in your SchemaBot server config
type Yes "mysql" or "vitess"
ignore_namespaces No Namespace subdirectories to exclude from plans, applies, and checks (see Ignoring Namespaces)

Environment availability and promotion order are configured on the SchemaBot server.

Schema File Layout

MySQL (flat structure):

schema/
  schemabot.yaml
  users.sql
  orders.sql

Vitess (keyspace subdirectories):

schema/
  schemabot.yaml
  commerce/
    users.sql
    orders.sql
    vschema.json
  lookup/
    lookup_table.sql
    vschema.json

Each .sql file should contain a single CREATE TABLE statement using the canonical format that matches SHOW CREATE TABLE output:

CREATE TABLE `users` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

7. Test It

Open a PR that modifies a schema file, then comment:

schemabot plan -e staging

SchemaBot will:

  1. React with 👀 to acknowledge the command
  2. Fetch the schemabot.yaml config and schema files from the PR branch
  3. Diff the desired schema against the live database
  4. Post a comment with the DDL plan
  5. Create a GitHub Check Run showing the result

Other commands:

schemabot help                  # Show command reference
schemabot plan                  # Plan for all configured environments
schemabot plan -d mydb          # Plan for a specific database (multi-db repos)

Environment Variables Reference

Variable Required Default Description
SCHEMABOT_CONFIG_FILE Yes — Path to server config YAML
GITHUB_APP_ID No — Fallback if github.app-id is not set in config
PORT No 8080 HTTP server port
LOG_LEVEL No info debug, info, warn, error

GitHub credentials (private-key, webhook-secret) are configured in the YAML config file using secret references, not environment variables. This keeps all configuration in one place and supports any secret backend.

Webhook Ingress

GitHub needs to reach SchemaBot's POST /webhook endpoint over the public internet. If your deployment already has a public URL (e.g., AWS App Runner, a VM with a public IP), set the GitHub App's Webhook URL directly:

https://your-schemabot-host/webhook

See deploy/aws/ for a complete example using App Runner.

If SchemaBot runs on Kubernetes, pods aren't publicly accessible by default. You'll need an ingress path — for example, an API Gateway or reverse proxy in front of an internal load balancer, or an ingress controller like nginx or Traefik with a route to /webhook.

For local development and PR testing, smee.io can proxy GitHub webhooks to your machine — recommended by GitHub for webhook development. This lets you test the full PR workflow (comment schemabot plan, receive webhook, process command) without deploying anything:

  1. Visit https://smee.io and create a new channel
  2. Temporarily set the GitHub App's Webhook URL to the smee channel URL
  3. Run the smee client locally: 
    npx smee-client --url https://smee.io/your-channel --target http://localhost:8080/webhook
  4. Switch the webhook URL back to your production endpoint when done

IP Allowlisting

GitHub publishes its webhook source IPs at https://api.github.com/meta (the hooks field). Restricting your webhook endpoint to these CIDRs is recommended as defense-in-depth. SchemaBot always validates webhook signatures via HMAC-SHA256 when webhook-secret is configured (see below), so IP allowlisting provides an additional layer of protection.

Webhook Signature Validation

If webhook-secret is set in the config, SchemaBot validates the X-Hub-Signature-256 header on every webhook request using HMAC-SHA256. Requests with invalid or missing signatures are rejected with HTTP 401.

If the secret is not set, signature validation is skipped (useful for local development).

Repository-level Webhooks (optional)

A single deployment can also accept deliveries from a repository-level webhook (a webhook configured directly on a repo, rather than on the GitHub App). This lets several SchemaBot deployments that share one GitHub App all receive the same repo's events. Set a separate secret for these deliveries:

github:
  webhook-secret: "env:GITHUB_WEBHOOK_SECRET"           # App-installed deliveries
  repo-webhook-secret: "env:GITHUB_REPO_WEBHOOK_SECRET" # repository-level deliveries

When repo-webhook-secret is set, deliveries whose X-GitHub-Hook-Installation-Target-Type header is repository are HMAC-verified against this secret. Such deliveries carry no installation id in their payload, so SchemaBot resolves the App's installation for the repo via the App JWT and caches it. Leave repo-webhook-secret unset to disable this path; App-installed deliveries are unaffected either way.

Troubleshooting

Webhook not receiving events: Check that the webhook URL is reachable from GitHub. Use the Recent Deliveries tab on your GitHub App's settings page to see delivery attempts and response codes.

401 Unauthorized on webhook: The webhook secret in your GitHub App settings doesn't match GITHUB_WEBHOOK_SECRET. Regenerate and update both.

"No schemabot.yaml config found" comment: SchemaBot couldn't find a schemabot.yaml file in the PR's changed file directories. Make sure the file exists and is committed to the PR branch.

"database not found" comment: The database field in schemabot.yaml doesn't match any database in your SchemaBot server config. The names must match exactly.