Sermon slide automation

Updated August 14, 2026.

See Focused portal workflow alerts for automated failure, freshness, notification, and recovery monitoring.

The River Oaks Portal accepts versioned Keynote sermon-slide uploads. It stores the original in the configured Google Shared Drive and a protected server cache, reads text and object data directly from the Keynote package, and copies the exact original bytes to the worship slides Mac with a SHA-256 acknowledgement. Keynote rendering and OCR are not the source of sermon text.

Upload group destinations

Portal administrators manage storage and automation under Staff → Sermon Slides → Admin → Upload group settings. A campus/service rule contains the Shared Drive and root-folder IDs, folder layout, and independent controls for automatic lower thirds, weekly-playlist placement, worship-computer copy, rendered previews, and administrator-initiated deployment. Google credentials remain in the protected environment file; the database stores routing IDs and behavior only.

Group	Storage layout	Automatic lower thirds	Manual deploy
Goshen / Sunday Service	Sermon Slides/YYYY/Series	Yes, including weekly playlist and previews	Yes
Espanol / Sunday Service	one current file in Mensajes; prior files in Archive/YYYY/Series	No	Yes
Other groups without a rule	legacy configured Drive hierarchy	No	Yes

For a current-file destination, the new Keynote is uploaded before older current .key files are moved into the archive. Images and unrelated files are not moved. A failure is visible; the workflow never deletes the prior file.

Portal library and sermon metadata

The sermon library keeps search as the primary control. Campus, service, speaker, series, status, and date filters remain available under More filters and stay closed until an operator needs them. Speaker values must be explicit sermon metadata; the signed-in uploader is never assumed to be the speaker. A historical deck without evidence names its speaker Speaker not recorded. Import manifests may supply a verified speaker, but the historical import operator must not be written into that field.

Portal version labels and newly generated filenames use v1, v2, and so on. Existing Drive objects with older zero-padded names are not renamed because their immutable file identity and links remain part of the audit history.

Lower-third workflow

Automatic lower thirds run only for Goshen / Sunday Service:

1. Deterministic code identifies point, Scripture, blank, and image-bearing source slides.
2. Text is split at verse, sentence, and phrase boundaries under the mapped theme's explicit line and character limits. Text is never truncated.
3. The VM creates a native ProPresenter presentation using the immutable theme snapshot mapped to the sermon series.
4. The restricted broadcast-gfx receiver replaces the exact stable presentation already saved in the managed playlist, checks ordered text and slide counts, and renders one preview image for every generated slide.
5. Preview hashes, rendered content identity, and playlist identity must match before the job is ready.

Before Scripture splitting, the generator removes only deterministic Keynote text-run artifacts: punctuation-only lines, artifact periods immediately before a verse number, a missing space between a verse number and its text, duplicate punctuation after a closing quote, and a trailing verse number that has no verse text. It also rejoins an unambiguous two-digit verse reference split between native runs, such as 18:1 9-20 becoming 18:19-20. The immutable extraction still preserves the original source wording for AI/human review. The generator does not spell-check, paraphrase, substitute a Bible translation, or silently apply uncertain corrections.

The receiver compares each rendered image with the generated text identity for that slide. ProPresenter can briefly return a stale cached thumbnail after an import. If identical images are returned for slides with different text, the receiver retrieves the complete set again with a bounded delay. The attempt continues only after every differing text slide has a distinct verified render; eight unsuccessful passes fail closed with propresenter_render_content_mismatch before playlist placement.

After retrieval, a theme-independent deterministic review compares the rendered point slides with one another and the rendered Scripture slides with one another. It detects material layout outliers, missing theme regions, and exact duplicate renders with different generated text. These checks do not infer or rewrite sermon wording. The advisory visual model then reviews every rendered slide with its source-slide text and immediate generated neighbors for clipping, tiny type, inconsistent placement, awkward breaks, and sequence errors.

Every attempt snapshots its required delivery contract in sermon_slide_processing_runs: broadcast-gfx, weekly playlist, worship copy, and preview publishing. Notification reports use that immutable snapshot. A later environment change, playlist rename, or upload-group rule change must not reinterpret an older run as newly successful or failed.

Starting a new attempt clears the mutable job and delivery acknowledgement fields from the prior attempt. Generation, broadcast-gfx delivery, worship delivery, and preview publishing then checkpoint their own verified results as they complete. If a later stage fails, the final report can accurately list what completed during the current attempt without borrowing stale success from an older deployment.

Español and Special Event uploads are stored and copied to the worship slides computer without automatic lower-third generation. An administrator may choose Deploy lower thirds in the portal. That manual decision is recorded in the event payload and runs the full broadcast-gfx verification workflow.

The broadcast-gfx receiver checks whether ProPresenter is running. A weekly stable-presentation update is performed only while ProPresenter is closed. If an operator session is already open, the receiver leaves it untouched and returns propresenter_active_stable_update_deferred; the worker retries that safe deferral up to 12 bounded attempts over approximately four hours. When the application is closed, the receiver atomically replaces the stable library file, launches the configured signed app, waits for its process and API, and enforces a minimum warm-up interval before verification. The API must identify the exact configured application version; an orphaned Network helper from an earlier ProPresenter process is not accepted as proof that the newly launched application is ready. The receiver quits only the process instance that automation launched.

Stable managed presentation

The receiver owns one configured presentation position in the broadcast-gfx playlist. ProPresenter 19.0.1's PUT /v1/playlist/{playlist_id} endpoint is not a durable storage interface in production: it returns success and exposes the new item through the live API, but the saved playlist file does not change and a restart restores the prior item. Focusing the playlist first and extending the wait does not make that update durable.

Production therefore never changes the playlist pointer during an upload. The receiver installer records the exact presentation UUID, display name, and library path already saved in the managed slot. The VM generates each weekly deck with that same presentation identity. While ProPresenter is closed, the receiver stores one last-known-good copy under Library Backups, atomically replaces the exact library file, launches ProPresenter, and verifies all of the following before acknowledging delivery:

• the configured application and API version are active;
• the saved playlist slot still resolves to the stable presentation UUID;
• the stable presentation path, ordered text identity, and slide count match;
• every generated slide has a render-verified thumbnail;
• items outside the managed playlist position are unchanged.

The exact file path is part of ProPresenter's saved library identity. Changing only the internal presentation UUID while renaming or moving the file causes the saved playlist item to become orphaned. Rename the stable presentation only as a deliberate one-time ProPresenter UI maintenance action, then rerun the receiver installer and update the VM's non-secret stable UUID/name variables.

If verification fails after replacement, the receiver closes only its own ProPresenter process and atomically restores the last-known-good file. A rollback failure is explicit as propresenter_stable_rollback_failed. An unexpected saved pointer fails closed as propresenter_playlist_slot_changed; automation does not rewrite it. Correct the saved slot or receiver configuration and use the existing version's Redeploy button. A Mac or playlist failure does not require another pastor upload.

The August 14 upload 47 incident established this behavior. Three bounded deployments reached generation and live playlist acknowledgement, but the playlist store never changed. An isolated _River Oaks Automation Canary reproduced the session-only PUT and restart reversion without touching the live Sunday playlist. A second isolated test proved that exact-path stable file replacement loaded the new 21-slide content after a clean launch while the saved canary pointer remained intact. Renaming that same file intentionally orphaned the canary reference and confirmed why path stability is required; the canary was then restored.

Deep readiness must verify the saved stable pointer, exact library path, application startup, API identity, compatible themes, and render capability. It must not mutate either the live Sunday playlist or a synthetic playlist, because the same session-only API acknowledgement would create a false green result.

The restricted receiver is deployed independently from the portal web root. Install the exact reviewed scripts/sermon-propresenter-receive.py on broadcast-gfx using an atomic replacement, retain the previous executable as a rollback copy, compare SHA-256 before and after activation, and run the Python compile check before redeploying a sermon.

Worship-computer retention

Google Drive and the protected VM cache remain the archive. The dedicated worship-Mac receiver keeps only the two newest distinct sermon dates among portal-managed Keynote copies. Its protected configuration may set a value from 1 through 12 on line two; an absent line defaults to two.

Retention runs only after a new file has been received and SHA-256 verified. The receiver records the relative path, sermon date, and expected digest in a private registry. It deletes an older file only when that exact registered path is still a regular file and its current digest still matches. Untracked files, operator-modified files, invalid registry rows, and paths outside the dedicated destination are preserved. Receivers accept both the current full-date protocol and the legacy year-only protocol while production workers are upgraded; a standardized filename supplies the retention date for a legacy delivery.

Keynote images

The extractor inventories slide-specific Keynote image objects independently from document text. It records the source slide, materialization state, media type, dimensions, and SHA-256 digest for each readable asset. The image bytes are never treated as lower-third text and are not sent through OCR. This avoids quietly inventing words from decorative artwork, screenshots, or photos.

Image-bearing slides follow these deterministic review rules:

• A slide with document text and images generates lower thirds from the document text only. The image inventory remains available for review.
• A slide with images but no document text is explicitly flagged as an image-only source slide. It cannot silently become a blank or inferred lower third.
• A referenced image that cannot be materialized and hash-verified is an explicit review finding. It does not silently pass image verification.
• Images never change readiness on their own unless a required deterministic stage fails. The review finding tells a human what the text-only lower-third output cannot represent.

Repeated template artwork is classified conservatively from native metadata, without image recognition or OCR. The same materialized image hash must appear on at least half of the deck, on at least three slides, and alongside native document text on at least one slide. Only then is it hidden from the slide-specific review list. A repeated asset that appears only on image-only slides remains meaningful review content. Unreadable or unverified assets also remain visible and produce an explicit finding. This rule changes review noise only; it never adds, removes, or edits lower-third text.

The August 12, 2026 legacy-corpus audit supplied the missing repeated-asset evidence. Forty-seven of 48 available 2025 decks parsed, covering 808 source slides. Twenty decks contained images: all 124 native assets materialized and hash-verified, with no unavailable asset. Five repeated asset groups were present. Four identical assets occurred on 17–24 of nearly every slide in their deck alongside text and were classified as template artwork; one asset repeated on three image-only slides and remained in the human-review set. Candidate validation on those five anonymous decks confirmed the rule before release. One legacy deck returned the bounded parser_output_missing compatibility error and remains a separate follow-up; it did not stop or contaminate the cohort.

Historical system tests

Run scripts/sermon-slide-history-test.py against a bounded Drive corpus before changing extraction, splitting, fit limits, or native ProPresenter generation. The generation harness uses the same safe default fit profile as production: four lines/180 characters for points and two lines/136 characters at a 68-column wrap width for Scripture. The Scripture profile is the middle option from a 15-deck comparison: it materially improves type size without the larger slide-count increase of the most aggressive candidate. A profile outside the generator's accepted limits must fail closed.

The August 11, 2026 pre-release audit exercised 31 unique 2026 service decks: 544 Keynote source slides generated 1,205 lower thirds, all 31 decks passed, and all 10 referenced image assets across seven image-bearing slides were materialized and hash-verified. Six source slides were image-only and therefore require human review. No source image was unavailable. This corpus is regression evidence, not proof that every future Keynote structure or visual layout is supported; retain contact-sheet review for new image patterns and theme changes.

Add --deliver --retrieve-previews when the acceptance test must exercise the Mac. Delivery is library-only unless the normal worker explicitly requires the managed weekly playlist. The harness retrieves the receiver's bounded preview bundle, verifies every numbered JPEG against its manifest and combined render hash, and records the preview count and manifest digest. The output directory must remain beneath the configured protected ProPresenter output root.

Historical QA must not publish previews, send email, or call AI by default. Run it through a transient least-privilege service that loads the matching protected environment file through systemd, not by sourcing that file as a shell script. Use a mode-0700 temporary download directory, return only aggregate results, delete VM deck/render copies on exit, and remove or move QA HISTORY - library files to recoverable Trash after ProPresenter closes.

The image-bearing delivery audit on August 11 selected five decks from the 31-week corpus. All five reached ready state: 83 source slides generated 169 lower thirds, all 169 previews were retrieved and hash-validated, all ten source image assets materialized, and no image slide was unavailable. ProPresenter returned to closed state, the managed playlist was unchanged, VM temporary media was removed, and five QA-only library files were moved to Trash. This proves deterministic render retrieval; it does not replace human contact-sheet approval of typography, spacing, and theme-specific fit.

For a portal-level historical acceptance run, use the dry-run-first sermon-slide-history-import.php tool. It deduplicates by Drive ID and SHA-256, attributes test notifications to an explicit portal user, and is hard-limited to development. Imported events carry trigger_mode=historical: Goshen decks may exercise generation, library import, render previews, worship delivery, logging, AI review, and both emails, but cannot enter or replace the managed weekly playlist. Historical production-report CC is suppressed so the explicit test recipient is the only email destination. Re-running a manifest is idempotent.

The August 11 managed backfill reconciled 39 raw Goshen Keynotes into 36 unique sermon versions after excluding two baptism-only decks and one byte-identical duplicate. The Español recovery found six weekly Keynotes from July 5 through August 9; five prior decks were restored to Mensajes/Archive/2026, while the August 9 file remained current. The combined portal cohort contains 42 versions.

Final portal acceptance completed all 42 versions without a terminal failure: 36 Goshen versions reached ready and six Español versions reached storage-only stored. The system extracted 753 source slides, created and remotely verified 1,453 lower thirds, published all 1,453 preview images, and copied all 42 source decks to the worship-slides destination. All 84 recipient-specific emails were sent. Successful runs averaged 89.411 seconds (0.327 seconds minimum and 215.780 seconds maximum), 16 failed attempts recovered through bounded retries, and the production-report AI calls cost $0.049437 in total. Historical runs intentionally did not modify the managed weekly playlist. Temporary QA theme mappings were deactivated after the acceptance run.

Notifications and AI boundaries

The uploader and production staff receive separate, independently tracked emails. The uploader receives safe-storage/delivery status, measured processing time, a new-version link, and a short list of likely source-text concerns. Production staff receive the signed-in uploader's name, deployment details, source/generated/difference counts, processing time, previews, review findings, and AI cost.

Storage, hashing, Keynote extraction, splitting, formatting, delivery, playlist placement, and pass/fail status are deterministic code. AI cannot modify words, slides, or readiness. One bounded AI review is stored with the production report and reused for the pastor message so the two emails do not double cost.

Readiness requires every deterministic stage marked required by that run. A processing_ready notification cannot override a failed processing-run status or a missing required acknowledgement. When both slides computers received their files but another required stage failed, the pastor message names the unfinished step at a high level. The production message lists each failed deterministic check, its safe detail, the bounded technical error code, and the stages that completed before the failure. Avoid generic phrases such as "production review did not finish" when the system knows the exact stage.

If the advisory AI review is unavailable, deterministic deployment readiness is unchanged. Both emails explicitly say that spelling and Scripture review was unavailable for that version and display the AI cost as unavailable; they must not silently omit the review or mark a successful deployment as failed.

Scripture validation compares parsed references and quotations with the YouVersion API; it never relies on model memory. NLT is the default unless the slide names another translation. When the requested translation is unavailable, the report explicitly labels the bounded fallback comparison instead of making an exact-wording claim. An unavailable reference service produces a visible not checked result; the system never guesses, rewrites, or silently corrects Scripture.

Operational logging

SQL is the durable operational log. sermon_slide_processing_runs retains one row per generation/deployment attempt with automatic/manual/storage-only trigger mode, start, finish, duration, outcome, bounded error code, slide counts, and ProPresenter lifecycle acknowledgements. It also snapshots whether that exact attempt required broadcast-gfx delivery, weekly-playlist placement, a worship copy, and rendered-preview publishing.

sermon_slide_processing_events is the append-only stage ledger. It records started, completed, skipped, retry-scheduled, waiting, and failed transitions for portal intake, protected cache storage, Google Drive, notes conversion, Keynote extraction, theme selection, lower-third generation, broadcast-gfx, worship slides, render verification, preview publishing, AI review, and each email notification. Each row may include an attempt number, elapsed milliseconds, logical target, bounded error code, allow-listed counters, and AI cost. It never stores sermon or slide text, filenames, local or remote paths, email addresses or bodies, AI prompts or responses, provider payloads, API keys, SSH details, or credentials.

Two identifiers connect the workflow:

• sermon_slide_uploads.upload_uuid is the correlation ID for the entire version, beginning with the web upload and continuing through every retry.
• sermon_slide_processing_runs.run_uuid identifies one exact generation and deployment attempt. A retry receives a new run UUID but retains the same upload correlation ID.

Every upload also has a required source_environment value of development or production. The trusted portal runtime records that value during intake. Each extraction, ProPresenter, notification, admin-health, and focused-alert query filters on it. This prevents the two portals from racing for a shared queue row or sending a production report through development credentials even though both environments use the same database. Legacy rows default to production; do not change that default without a reviewed data migration.

Queue rows, jobs, delivery artifacts, and notifications remain the current-state records. They should not be treated as the historical log because retries update those rows in place. Full AI review content remains in protected report JSON.

Transient delivery failures use a bounded maximum of three attempts with a safe delay between attempts. Retry events record whether the error was retryable, whether the decision was terminal, and the next retry delay. Configuration and playlist-safety failures such as a changed managed playlist slot stop immediately and alert an operator; repeatedly touching an unexpected playlist slot is not safe. Each retry receives a new run UUID while retaining the upload correlation ID.

The systemd journal is a bounded short-term trace. It may include upload/run identifiers, stage, status, attempt, duration, logical target, counters, and safe error codes, but must not print environment values, source slide text, paths, provider response bodies, email content, prompts, or credentials. Journal lines use correlation_id=<upload UUID> and, after generation begins, run_id=<run UUID> and trace_id=<run UUID without hyphens>.

Portal operations view

Administrators can use Staff → Sermon Slides → Admin for a seven-day health overview based on the latest outcome for each upload: current runs, successful outcomes, unresolved failures, recovered retry attempts, average run time, and AI cost. The collapsed Unresolved failures section links only to uploads whose latest run remains failed; earlier failed attempts that later recovered do not make the current dashboard look unhealthy. On a sermon page, expand Processing log under a version to see the full stage-by-stage retry history, attempt numbers, elapsed times, targets, cost, run UUID, and exact safe error code. The timeline is visible only to users with sermon_slides.manage.

Central logs and end-to-end traces

The portal's SQL audit remains authoritative. Central observability adds two diagnostic views without becoming a dependency of sermon processing:

1. Portal Alloy reads only the eight sermon worker journals and sends them to Loki with low-cardinality environment and component labels.
2. A separate telemetry worker reconstructs one OTLP trace per completed processing attempt from the committed SQL events. Central Alloy receives it on the private network and sends it to Tempo.
3. Grafana dashboard Sermon Slides — Reliability shows recent outcomes, failures, trace-export retries, and combined worker logs. A trace_id in a Loki line links to the matching Tempo waterfall.

The trace ID is deterministic: lowercase run_uuid with hyphens removed. The Tempo root span is sermon_slides.workflow; children include extraction, theme selection, generation, broadcast-gfx delivery, render verification, playlist placement, worship delivery, preview publishing, AI review, and notification delivery when those stages ran. The trace can therefore answer which stage ran, how long it took, which retry succeeded, and whether each required acknowledgement completed.

The exporter uses sermon_slide_trace_exports, a one-row-per-run durable queue. It waits until the run and its pending notifications settle, uses a short OTLP timeout and bounded exponential retry, recovers interrupted claims, and marks only the export failed after twelve attempts. It never changes an upload, job, delivery artifact, notification, or readiness result. The focused workflow monitor alerts when an export fails or remains delayed beyond the stale-run window, and sends recovery after the queue drains.

Trace attributes are restricted to run/upload identifiers, trigger and status, stage/attempt/duration, logical target, safe error code, slide counts, destination requirements, ProPresenter lifecycle flags, safe Bible-review counters, and AI cost. Never add sermon text, filenames, paths, Drive IDs, email addresses or bodies, pastor/series/title metadata, prompts, provider responses, or credentials.

In Loki, start with:

{job="sermon-slides", environment="production"}

Filter one run without turning the trace ID into a label:

{job="sermon-slides"} |= "trace_id=REPLACE-WITH-32-HEX-TRACE-ID"

In Tempo Explore, search for:

{ resource.service.name = "riveroaks-sermon-slides" && resource.deployment.environment = "production" }

The default historical export window is 30 days and is bounded from 1 to 400 days. An LGTM outage queues evidence locally; it must never cause a pastor upload or Mac delivery to fail.

Preemptive readiness monitoring

The five-minute focused monitor now validates the service around each upload, not only failures after an upload. It checks worker state, database/schema, protected storage writability and capacity, notification configuration, active destination rules, the notes-conversion runtime/workspace, Google Drive add-child permission, immutable theme hashes, and the restricted health contracts on both slides Macs. Ordinary probes do not open ProPresenter or mutate sermon files.

A production-only Thursday-through-Sunday deep canary generates a synthetic native presentation through the real generator and active theme, then asks the broadcast-gfx receiver to validate ProPresenter launch, API/version, compatible themes, library resolution, and the managed playlist slot without importing or changing anything. A ProPresenter session already open is left open; one opened by the canary is closed afterward. The last deep result must remain fresher than 30 hours.

The content-free /health/sermon-slides.php contract is intended for Uptime Kuma. It becomes unavailable for systemic dependencies or stale monitoring, while one sermon-specific processing failure remains an actionable focused alert instead of misreporting the whole service as offline. See Focused portal workflow alerts for thresholds, maintenance windows, commands, and triage.

Environment-owned workers

Each environment has four persistent workers. Production services run only code deployed under /usr/share/nginx/portal with /etc/riveroaks-portal/production.env; development services use /usr/share/nginx/html/dev-portal and the protected development file.

Stage	Development	Production
Keynote extraction	sermon-slide-extraction-dev.service	sermon-slide-extraction-production.service
ProPresenter and Mac delivery	sermon-slide-propresenter-dev.service	sermon-slide-propresenter-production.service
Recipient-specific email	sermon-slide-notification-dev.service	sermon-slide-notification-production.service
Loki/Tempo trace export	sermon-slide-telemetry-dev.service	sermon-slide-telemetry-production.service

Install or refresh units only from the matching deployed root:

sudo scripts/install-sermon-slide-workers.sh development
sudo scripts/install-sermon-slide-workers.sh production

The installer pins SERMON_SLIDES_ENVIRONMENT in each unit, reloads systemd, enables the services, and verifies they are active. Never point a production unit at the development checkout, and never run both suffixes with the same environment value.

The four workers also guard against deployed-file/runtime drift. At startup, each worker fingerprints only its own Python source dependencies. If a portal deploy atomically replaces any watched file, the running worker finishes its current claimed job, closes its database connection, and re-executes itself before claiming another job. This loads the newly deployed code without interrupting an upload, ProPresenter delivery, email dispatch, or telemetry export. The journal records sermon_slide_worker_reloading with bounded source filenames followed by the ordinary sermon_slide_worker_started event.

Changing an EnvironmentFile or a systemd unit is different: an in-process re-exec inherits its current environment. Use the installer above for those changes. After a code-only release, verify the relevant worker journal shows the reload/start sequence and that all four services remain active.

Production release acceptance

August 13 render-quality and theme-mapping release

Theme administration is now one operation. Under Staff → Sermon Slides → Admin, an administrator enters a series, selects any compatible theme from the cached broadcast-gfx ProPresenter catalog, chooses the campus/service scope, and saves. The portal imports the selected native theme through the restricted receiver, validates its Short and Scripture slides, stores an immutable VM snapshot, and creates or updates the mapping. The former separate “select and validate” panel is no longer part of the operator workflow. Native portal template upload remains an advanced fallback.

Each active mapping has a Change action. An existing sermon also exposes Change theme beside Redeploy. Entering from a sermon, saving a new theme updates the matching series/scope mapping and queues a full regeneration and redeployment of that sermon's latest uploaded version. A mapping-only edit affects future processing without silently changing historical artifacts.

Release QA used the August 16 production source deck in library-only mode so the managed weekly playlist was untouched. The chosen 68-column/136-character Scripture profile produced 31 lower thirds from 21 source slides. All 31 thumbnails were retrieved and verified; the only two duplicate-render groups were intentional repeated content. A separate controlled defect cohort inserted a missing theme, undersized text, clipped text, and swapped generated output. The deterministic pass detected structural render defects, while the advisory visual pass identified the missing theme, tiny type, clipped body, and source/render order mismatch. The four visual AI requests covered all 26 test images and cost approximately $0.0051.

The same QA exposed ProPresenter's transient stale-thumbnail behavior: four different slides initially returned identical blank images, then returned the correct content on a later fetch. The bounded receiver retry described above was added and validated against both transient recovery and persistent mismatch tests. A final live library-only import returned 31 of 31 correct previews with no differing-text duplicate render.

The August 12 readiness release was promoted through Portal MR !500 as a narrow release from current main; unrelated development changes were not included. Production deploy commit b3456f133d496507acdf4e33fe098922d776e544 contains immutable per-run delivery requirements, current-attempt checkpoints, safe retry classification, exact failure reporting, and the pastor-email wording correction.

Post-deploy acceptance proved the following without reading protected values:

• the four requirement columns exist and the idempotent migration succeeds;
• all three production sermon workers and the five-minute workflow timer are enabled and active;
• the deployed focused notification, ProPresenter-worker, observability, Bible-review, AI-report, transcript, and workflow-monitor tests pass in the server's pinned runtimes;
• production routing points administrator reports to productionstaff@riveroaks.org, while uploader notices continue to snapshot the signed-in uploader's account email;
• MailerSend, LiteLLM, YouVersion, broadcast-gfx delivery, worship delivery, and preview publishing are configured and enabled;
• production has no unresolved current sermon runs or pending/failed sermon notifications after rollout;
• the production monitor is fresh and healthy, and its synthetic sermon-failure dry run renders without sending mail or changing incident state;
• the worship slides Mac is reachable;
• the restricted broadcast-gfx theme-catalog path found 40 themes, 21 matching the portal template contract, and returned ProPresenter to its original closed state after automation opened it;
• destination rules keep automatic lower thirds and the managed weekly playlist limited to Goshen Sunday Service. Espanol remains storage-only with manual lower-third deployment allowed; other unmatched groups use the safe storage-only/manual-deploy fallback.

The human-triggered production acceptance boundary was completed on August 12 with genuine signed-in upload 46, sermon 4, version 2. Intake first exposed a deterministic upload-reservation SQL mismatch. Portal MRs !505 and !506 centralized the reservation statement and binding contract, removed the extra placeholder, and added regression checks for column/value and placeholder/bind counts. The corrected upload reached Drive, protected cache, direct Keynote extraction, and lower-third generation.

The first delivery attempt stopped safely with propresenter_playlist_slot_changed: receiver state still named the August 9 presentation while playlist position 3 already contained an August 16 Goshen v001 presentation. No Mac delivery was acknowledged. Separate failure emails went to the uploader and production staff; the production copy included the exact code and affected checks. Portal MRs !507 and !508 added the bounded same-date/campus version-reconciliation rule described above without a new service, dependency, migration, or setting.

An administrator then used Redeploy on the existing version. Run c20c15ce-4840-4e79-abfd-a39347dded80 reached ready in 86 seconds:

• 21 Keynote slides produced 26 lower thirds and passed content integrity;
• ProPresenter 19.0.1 imported and render-checked all 26 slides;
• playlist position 3 acknowledged presentation UUID 6986C2D8-B242-40A4-9E49-F5B49B11484A;
• ProPresenter began closed, automation launched it, and automation returned it to closed;
• the worship receiver hash-verified the v2 Keynote under the managed August 16 path with SHA-256 6f24d908a206dad71eacc3002905707eda8ae5dd47274360dd136cba9d4e6fa3;
• Drive received exactly 001.jpg through 026.jpg; representative point and Scripture images passed full-size visual review, and blank opening/closing frames remained intentional;
• uploader and production success emails both reported 1 minute 27 seconds and $0.001228 AI cost. The uploader received high-level delivery and proofreading guidance; production received uploader identity, 21-to-26 counts, both Mac confirmations, playlist placement, split detail, and the rendered-set link;
• the version timeline retained 45 safe operational events spanning intake, Drive, extraction, generation, both Macs, rendering, preview publishing, AI, and the two independently tracked notifications.

Portal MR !509 also changes an absent final receiver receipt from misleading zero-import wording to “verified acknowledgement was not returned.” The exact bounded failure code remains in the production report. This distinction matters because a receiver can stage and inspect a presentation before a later playlist check stops the attempt; only a complete receipt is authoritative.

Operator queries

All timestamps are UTC. Recent processing attempts, including retries, can be reviewed without opening report JSON:

SELECT
  r.started_at,
  u.upload_uuid,
  r.run_uuid,
  r.trigger_mode,
  r.status,
  r.attempt_number,
  r.duration_ms,
  r.source_slide_count,
  r.generated_slide_count,
  r.remote_slide_count,
  r.broadcast_gfx_required,
  r.playlist_required,
  r.worship_required,
  r.preview_required,
  r.error_code,
  r.propresenter_was_running,
  r.propresenter_launched,
  r.propresenter_closed_after_processing
FROM sermon_slide_processing_runs AS r
JOIN sermon_slide_uploads AS u ON u.id = r.upload_id
WHERE u.source_environment = 'production'
ORDER BY r.started_at DESC
LIMIT 50;

Failures and retry attempts should be reviewed by bounded error code. The run UUID connects the SQL row to the corresponding systemd journal line:

SELECT r.started_at, r.run_uuid, r.upload_id, r.status, r.attempt_number, r.duration_ms, r.error_code
FROM sermon_slide_processing_runs AS r
JOIN sermon_slide_uploads AS u ON u.id = r.upload_id
WHERE u.source_environment = 'production'
  AND r.status IN ('failed', 'retry', 'waiting_theme', 'deferred')
ORDER BY started_at DESC
LIMIT 50;

Review one upload across intake, extraction, every deployment attempt, AI review, and both emails by its upload correlation ID:

SELECT
  e.occurred_at,
  u.upload_uuid AS correlation_id,
  r.run_uuid,
  e.stage,
  e.event_type,
  e.status,
  e.attempt_number,
  e.duration_ms,
  e.target_name,
  e.error_code,
  e.cost_usd,
  e.metrics_json
FROM sermon_slide_processing_events AS e
JOIN sermon_slide_uploads AS u ON u.id = e.upload_id
LEFT JOIN sermon_slide_processing_runs AS r ON r.id = e.processing_run_id
WHERE u.upload_uuid = 'REPLACE-WITH-UPLOAD-UUID'
ORDER BY e.occurred_at, e.id;

Find recent stage failures without reading sermon content:

SELECT
  e.occurred_at,
  u.upload_uuid AS correlation_id,
  r.run_uuid,
  e.stage,
  e.attempt_number,
  e.duration_ms,
  e.error_code
FROM sermon_slide_processing_events AS e
JOIN sermon_slide_uploads AS u ON u.id = e.upload_id
LEFT JOIN sermon_slide_processing_runs AS r ON r.id = e.processing_run_id
WHERE e.status = 'failed'
  AND u.source_environment = 'production'
ORDER BY e.occurred_at DESC
LIMIT 100;

The corresponding short-term journal trace can be filtered without exposing the environment file:

journalctl \
  -u sermon-slide-extraction-production.service \
  -u sermon-slide-propresenter-production.service \
  -u sermon-slide-notification-production.service \
  -u sermon-slide-telemetry-production.service \
  --since '24 hours ago' --no-pager \
  | grep 'correlation_id=REPLACE-WITH-UPLOAD-UUID'

Only the production-staff notification row records inference cost. Pastor rows reuse that result and deliberately have a null cost, so this total does not double-count calls:

SELECT
  DATE_FORMAT(n.created_at, '%Y-%m-01') AS month_utc,
  COUNT(*) AS inference_calls,
  ROUND(SUM(n.inference_cost_usd), 6) AS ai_cost_usd
FROM sermon_slide_processing_notifications AS n
JOIN sermon_slide_uploads AS u ON u.id = n.upload_id
WHERE n.inference_cost_usd IS NOT NULL
  AND u.source_environment = 'production'
GROUP BY DATE_FORMAT(n.created_at, '%Y-%m-01')
ORDER BY month_utc DESC;

When diagnosing development, substitute development in SQL and the -dev unit suffixes. Never combine environments in a health or cost conclusion.

Use processing_duration_seconds on notification rows for the pastor-facing upload-to-completion time. Use duration_ms on run rows when diagnosing an individual generation, storage, delivery, or retry attempt.

Failure triage

1. Open the affected sermon and expand Processing log for the newest version. Start with the first failed stage, not the final email wording.
2. Copy the correlation ID and run UUID. Confirm whether a later retry already completed before intervening.
3. Check the exact bounded error code and logical target. Do not ask a pastor to upload again for a Mac, playlist, preview, Drive-rate-limit, or email-provider failure; use Redeploy after correcting the dependency.
4. Compare the matching journal lines only when the database timeline lacks enough operational detail. Do not dump environment files or report JSON into tickets or chat.
5. Confirm both recipient-specific notification events reached sent. A successful production email does not prove that the uploader email sent, and vice versa.

Treat any terminal run failure as actionable. Investigate a run left in processing for more than 20 minutes, a notification left pending/retry for more than 15 minutes, any individual AI cost above $2, or monthly AI spend near $10. These are operating thresholds; automated alerting and provider-side hard caps should be configured separately from the audit ledger.

Retention

Keep aggregate processing-run and notification outcome rows for the life of the sermon record. Keep detailed sermon_slide_processing_events for at least 400 days so year-over-year Sunday incidents remain diagnosable. The table is small (normally tens of rows per upload), and no automatic deletion job is enabled as of this update. Before adding retention cleanup, verify a backup and delete only events older than the approved threshold; never delete active queue rows, jobs, processing runs, or notification outcomes as part of event maintenance.