Skip to content

github-app-setup.md

Latest commit

 

History

History
351 lines (249 loc) · 18.4 KB

github-app-setup.md

File metadata and controls

351 lines (249 loc) · 18.4 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

You don't have to write the schema directory by hand. Once the database is registered in the server config (step 4), schemabot onboard -d mydb -e staging -s ./schema pulls the live schema and generates the whole directory — schemabot.yaml, a subdirectory per namespace (the schema name on MySQL, the keyspace on Vitess), and one .sql file per table — then verifies the result plans clean against the source environment. Use --dry-run to preview the files first. Onboard always generates the namespace-subdirectory layout shown under Schema File Layout below; the flat MySQL layout there is a hand-maintained alternative.

Create (or let schemabot onboard 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", "vitess", "strata" (experimental; requires server opt-in — see Strata), or "postgres"
ignore_namespaces No Namespace subdirectories to exclude from plans, applies, and checks (see Ignoring Namespaces)
ignore_tables No Live table names to withhold from the planner, so an undeclared table is neither created nor dropped (see Ignoring Tables)

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

Schema File Layout

MySQL (schema-name subdirectory — what schemabot onboard generates):

schema/
  schemabot.yaml
  mydb/
    users.sql
    orders.sql

MySQL (flat structure, hand-maintained):

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)

First apply for a schema directory that is already merged

SchemaBot plans a database when the PR changes a file under its schema directory. A PR that changes nothing there gets a passing No schema files changed check, even when the database itself is still empty. That is the usual shape of a new database: the declarative schema files merge first, and the database is configured on the SchemaBot server afterwards, so no PR diff is left to trigger the plan.

Reconcile the drift with a nonce edit. Add or toggle a # nonce comment line in the database's schemabot.yaml and open the PR. The change is inert, but it sits in the schema directory, so SchemaBot plans the whole directory against the live schema and the plan shows every table to create, including the ones whose files the PR did not touch. Apply from the plan comment as usual:

schemabot apply -e staging

The same edit reconciles drift on any database, not only a new one. See Reconciling drift with a nonce edit.

Environment Variables Reference

Variable Required Default Description
SCHEMABOT_CONFIG_FILE Yes — Path to server config YAML
STORAGE_DSN No — Fallback storage DSN if storage.dsn is not set in config
GITHUB_APP_ID No — Fallback if github.app-id is not set in config
PORT No 8080 HTTP server port
GRPC_PORT No — gRPC server port; the gRPC listener starts only when this is set
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.

Follow the pre-merge workflow to take your first schema change through review, apply, and merge.

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: A command's -d flag named a database that no schemabot.yaml in the repository declares. Check the database field of the intended schemabot.yaml; the comparison is case-insensitive. On a repository too large for GitHub to return its full tree, SchemaBot searches only the directories listed under the database's allowed_dirs in the server config, and the comment lists the directories it searched.

"Database Not Available to This Repository" comment: A command's -d flag named a database whose allowed_repos in the server config does not include this repository. No schemabot.yaml in the repository can manage that database, so none was searched. Add the repository to the database's allowed_repos if it should manage the database.

"Database Not Configured" comment: A command's -d flag named a database that has no key under databases: in your SchemaBot server config. The repository's schemabot.yaml may be correct; the database still has to be configured on the server before SchemaBot can plan or apply changes for it. On a repository shared by several SchemaBot deployments, only a deployment named with -t, or one that is not the aggregate leader, answers this way; the leader keeps searching the repository so a database no deployment serves is still reported.

"Database Not Registered" comment: On a repository shared by several SchemaBot deployments, the PR's schemabot.yaml declares a database the aggregate leader has no key for under databases:, and its schema directory is under none of the paths the leader expects another deployment to report on. The comment speaks only for the deployment named in its header: a leader serving other environments keeps its own registry and may register the database. When several leaders split a repository's environments, only one answers: the leader serving the command's -e environment, or, for a command without -e, the leader serving the first environment in environment_order. The others log the decision and stay silent. When the PR carries several such configs, the one reply lists them all under "Databases Not Registered". If a database is new to SchemaBot, register it on that deployment with an allowed_dirs entry naming its schema directory; if it is already registered, move the schemabot.yaml and its schema files under the directory registered for it.

"Repository Too Large to Search" comment: GitHub truncated the repository tree, and the server config gave SchemaBot no exhaustive set of directories to search instead. Give the database an allowed_dirs entry naming its schema directory so discovery can probe that directory alone.

"not configured on this SchemaBot instance" comment: The database field in schemabot.yaml doesn't match any key under databases: in your SchemaBot server config. The consumer value is folded to lowercase before matching, so only the letters need to agree with the (lowercase) server key.