LastSyncDate on github_installations and github_repositories, written only by the bot’s scheduled syncs

github_installations and github_repositories are fed by Events: an installation created Event writes the installation and the repositories it came with, a repository created Event writes one repository. Everything else that changes what the GitHub App can see is either not handled (installation_repositories added/removed, privatized/publicized) or only partially handled (renamed rewrites Name but not FullName), and a missed delivery leaves a gap for good. The tables drift from GitHub, and the Idle Pull Request view (Sql/0040) already has to tolerate repositories with no row at all. GStraccini-bot’s manual checkup job backfills missing rows, but only when someone runs it.

GStraccini-bot (gstraccini-bot-service) now reconciles both tables on a schedule, from the loops of its existing installations and repositories workers (its ADR-0001), and Sql/0041 adds the one column each pass needs to pace itself: LastSyncDate.

• The installations worker’s monthly pass, from the 1st: lists the app’s installations, registers the missing ones (with their repositories), updates the account metadata of the known ones, marks the ones GitHub no longer lists with Status = '0' (the same write as the deleted Event), and re-lists the repositories of every installation whose LastSyncDate is NULL or older than 3 months. A repository listing updates the metadata (name, full name, owner, visibility) of known repositories, adds missing ones, and appends a deleted/removed row for the ones the installation can no longer access.
• The repositories worker’s hourly pass: takes the one active repository with the oldest LastSyncDate (NULL first), refreshes its metadata and reconciles its open pull requests, open issues and branches against GitHub, backfilling missing rows and flagging the ones GitHub no longer reports.

Considered options

• Handle the missing Events (installation_repositories, privatized/publicized, a full renamed) in this service. Still worth doing, but it doesn’t close the gap: any delivery can be missed, and the historical drift stays. The scheduled sync is the safety net under the Events, not a replacement for them.
• Make the checkup job periodic as it is. Rejected: it walks every installation, repository, pull request, issue, branch and commit in one pass, deliberately slowly, which is fine to run by hand and far too much to run every hour. Splitting the work into a monthly installation pass and an hourly one-repository pass needs per-row bookkeeping, which is what LastSyncDate is.
• A unique key on InstallationId / Id so the sync can INSERT ... ON DUPLICATE KEY UPDATE. Rejected for now: both tables hold a row per Event that announced the entity, so production has duplicates per id, and deduplicating historical rows is a migration with its own risks. The sync workers treat the most recent row (highest Sequence) per InstallationId / per Id as the current one instead; Sql/0041 adds the (id, Sequence) indexes that pick needs. Sql/0039/Sql/0040 already made the same most-recent-row choice for the Idle Pull Request view.
• Keep the monthly pass’s last run in memory in the bot. Rejected: the bot’s workers start a fresh process every minute in cron/HTTP mode and restart on every deploy as daemons, so the pass would run on every start. The hourly pass needs no extra state (the newest LastSyncDate is its own trace); the monthly one keeps its last run as a Setting (gstraccini-bot.sync-installations.last-run, ISO 8601) in the settings table, under a gstraccini-bot. prefix so it can’t collide with this app’s Settings.

Consequences

• LastSyncDate is timestamp NULL DEFAULT NULL on both tables, after UpdatedAt. NULL means “never synced”, and sorts first, so every existing row is due on the workers’ first run after this migration. This service never writes the column; the Event writers leave it alone.
• On github_installations the date means “the installation’s repositories were last listed”, not “the installation row was last checked”: the monthly pass updates account metadata on every run but only moves the date after a successful repository listing. A failed listing (suspended installation, token error) leaves the date where it was, so the installation is due again next month.
• On github_repositories the date is set when the hourly pass claims the row, before any GitHub call. A repository whose sync fails therefore goes to the back of the queue instead of blocking the pass every hour, at the cost of the column meaning “last sync attempt” for failed rows (the failure is logged). With N active repositories, every repository gets a turn roughly every N hours. The newest value in the table is also what paces the pass (TIMESTAMPDIFF(SECOND, MAX(LastSyncDate), NOW()), computed by the database so the two clocks’ time zones never enter it).
• The monthly pass’s “missing from GitHub” decisions (installation Status = '0', repository deleted/removed rows) are only taken after a complete listing: the bot’s fetcher aborts the whole pass on any failed page rather than treating a partial list as the truth.
• The hourly pass never closes a pull request or issue in the database itself: when GitHub no longer reports one as open, it sets the row back to RE_REQUESTED, and the bot’s own pull request / issue handler fetches it and runs the normal closing flow (labels, notifications, next pull request). Branches GitHub no longer reports get the same UPDATE the delete Event writes (Event = 'delete', ProcessingState = 'UPDATED'), so the branches handler runs its deletion flow.
• Rollout order: webhooks first, then gstraccini-bot-service. The bot’s scheduled passes read and write LastSyncDate; deployed before 0041 they fail on every check on the unknown column (logged; nothing else in the workers touches it, so their regular processing is unaffected). The reverse window is harmless: the column sits NULL until the workers ship.
• The *_processing_views and their GET /api/v1/*-processing endpoints are unchanged; LastSyncDate isn’t exposed by this service’s API yet.
• settings holds one row written by another service for the first time. SettingsManager reads it like any other row; nothing here depends on it.