Track releases and correlate them with deploy/release workflows

projects-monitor will eventually need a repository-level overview: the latest release per repo, and whether the workflows around it (deploy, production, …) succeeded. webhooks is the only thing currently ingesting GitHub webhooks, so this issue asks for the persistence layer to be built here first, independently of projects-monitor’s eventual GitHub App and UI.

Why this bypasses the existing GitHub pipeline entirely

The obvious approach would be adding release/workflow_run handling to GitHubEventHandler and extending github_workflow_runs. That would have silently reintroduced exactly the coupling this feature is required to avoid:

  • validateEventAndAction() (Src/lib/github.functions.php, itself marked // TODO: Move this to gstraccini-bot repository) — called by nearly every existing isGitHubXToBot() matcher that feeds GitHubEventHandler — rejects any webhook whose X-GitHub-Hook-Installation-Target-ID isn’t GStraccini-bot’s own App ID (BOT_APP_ID = 480132). The entire existing OOP pipeline (branches, discussions, issues, pull requests, …) only ever processes events delivered to that one App.
  • isGitHubWorkflowRunToBot() goes further still and hardcodes an allowlist of 14 specific installation IDs. workflow_run events for any other installation are discarded before persistence today — via a legacy function, saveWorkflowRunGitHubToDatabase() (Src/lib/bot.database.php); the OOP WorkflowRunRepository::create()/update() and GitHubEventHandler::handleWorkflowRunEvent() are dead code, since database.php’s dispatcher never routes a native workflow_run delivery into processGitHubWebhook().

Release/workflow tracking needs to see every repository this service receives webhooks for, not just GStraccini-bot’s. So it’s a new, parallel, App-agnostic path instead:

  • Src/Library/ReleaseTrackingHandler.php is the only entry point. It reads X-GitHub-Event directly — no validateEventAndAction(), no installation allowlist, no dependency on GitHubEventHandler at all.
  • saveGithubValidatedToDatabase() (Src/lib/database.php) calls it unconditionally, before the existing isGitHubXToBot() if/elseif chain — purely additive, so existing dispatch behavior for every other event type is untouched.
  • New tables (github_releases, github_release_workflow_runs) rather than reusing github_workflow_runs, since that table’s only live writer is the allowlist-gated legacy path, and repurposing it for this feature would mean either inheriting that gate or fragmenting its semantics.

Data model

github_releases (Sql/0033) — one row per repository (UNIQUE (RepositoryOwner, RepositoryName)), always the latest. ReleaseRepository::createOrUpdate() looks up the existing row’s ReleaseCreatedAt and no-ops if the incoming release isn’t newer, so a delayed or out-of-order redelivery can’t clobber a newer release already stored. Only action IN ('published', 'released') writes — edited/deleted/unpublished are out of scope for this first pass.

github_release_workflow_runs (Sql/0034) — one row per relevant workflow_run, across all repos and all time (not just “latest”). “Relevant” is a case-insensitive substring match of workflow_run.name/.path against ConfigConstants::RELEASE_WORKFLOW_KEYWORDS (deploy, release, publish, production) — a heuristic, not exhaustive; GitHub’s workflow_run payload has no direct release reference to key off instead. Upserted by WorkflowRunId, same create-then-update shape as the existing WorkflowRunRepository.

Correlation is a read-time join, not a stored foreign key. Because github_releases already collapses to one row per repository, github_release_status_view (SQL SECURITY INVOKER, matching the convention Sql/0027 established for every view in this schema) joins it to github_release_workflow_runs rows for the same repository within a symmetric ReleaseCreatedAt ± 6 HOUR window, keeping only the single closest-in-time run per distinct relevant workflow (Sql/0036, refining Sql/0035 — see the corrections below), and aggregates:

CASE
  WHEN SUM(failure-ish conclusions) > 0 THEN 'Failed'
  WHEN COUNT(*) = 0 OR SUM(not completed / conclusion IS NULL) > 0 THEN 'Pending'
  ELSE 'Successful'
END AS WorkflowStatus

WorkflowStatus = 'Failed' is the “this release requires attention” signal — matching the issue’s own worked example (a Deploy workflow can succeed while a Production workflow fails, and the release still requires attention overall). No explicit FK/snapshot was needed: recomputing this at read time is naturally robust to reprocessing and out-of-order webhook arrival, and keeps the write path simple.

Consequences

  • projects-monitor (or anything else) can query github_release_status_view directly for the table the issue describes (repository, last release, tag, date, workflow status) — no aggregation logic needs to live outside this database.
  • Because there’s no App/installation scoping at all yet, this table currently reflects every repository this service receives release/workflow_run webhooks for, which will be broader than projects-monitor’s eventual monitoring scope. Restricting that is explicitly deferred to projects-monitor’s own future GitHub App — this schema doesn’t need to change for that; a future consumer just filters by RepositoryOwner/RepositoryName against its own installation list.
  • The RELEASE_WORKFLOW_KEYWORDS keyword match is a heuristic. A workflow named e.g. ship-it.yml won’t be picked up; a workflow named deploy-docs.yml unrelated to app releases will be. Expected to be refined once real-world workflow naming is observed.

Correction: every release showed as Pending (Sql/0035)

The original join (RunStartedAt >= ReleaseCreatedAt, no look-back) assumed the deploy/release workflow run always starts after the release it’s being correlated with already exists. That’s backwards for this repo’s own deploy.yml: the “Deploy via SSH” workflow (name matches the deploy keyword) starts, deploys, runs migrations, runs API tests, and only as its last job (create_release) cuts the GitHub Release — so RunStartedAt for that run is always earlier than ReleaseCreatedAt. The join predicate therefore never matched, COUNT(w.Sequence) was always 0, and WorkflowStatus was 'Pending' for every repository, not just this one — any repo whose release is the last step of its own deploy workflow hits the same case.

Sql/0035 widens the lower bound to ReleaseCreatedAt - INTERVAL 6 HOUR instead of a strict >=, so a workflow run that started shortly before the release it produced still correlates. The window stays bounded (rather than removing the lower bound entirely) so a stale run left over from a much earlier release cycle for the same repository doesn’t get attributed to the current one.

Correction: unrelated runs in the window could still flip a release to Failed (Sql/0036)

Sql/0035’s window fixed the “always Pending” bug, but it aggregates every row in github_release_workflow_runs that falls inside the 6-hour window — not just the run that actually produced (or followed) the release it’s attached to. That’s a real problem for this repo specifically: deploy.yml’s “Deploy via SSH” workflow (the only one matching RELEASE_WORKFLOW_KEYWORDS here) runs on every push to main, and on an active day multiple runs land inside the same 6-hour window — e.g. four runs between 15:48 and 18:06 on one day, including one failure sandwiched between two successes. A release cut by the successful 18:06 run would still show WorkflowStatus = 'Failed', purely because of the unrelated earlier failure, with nothing wrong with the deploy that actually produced it.

Sql/0036 replaces the flat aggregate with ROW_NUMBER() OVER (PARTITION BY release, Name, Path ORDER BY ABS(TIMESTAMPDIFF(SECOND, RunStartedAt, ReleaseCreatedAt)) ASC), keeping only the single closest-in-time run per distinct relevant workflow per release, and widens the window to be symmetric (ReleaseCreatedAt ± 6 HOUR) instead of only bounded on one side — a workflow triggered by the release itself (on: release: published) necessarily starts after ReleaseCreatedAt, the mirror image of this repo’s deploy-then-release pattern. Partitioning by workflow Name/Path (not just by release) is what keeps ADR-0006’s original multi-workflow scenario working — a Deploy workflow succeeding while a separate Production workflow fails still surfaces both, since each distinct workflow is matched to the release independently — while a stale run of the same workflow from a different release cycle can no longer leak in.

This site uses Just the Docs, a documentation theme for Jekyll.