Why this plan exists
An audit of the Cloud Run job fleet led into a deep look at the media-ingest path specifically: how photos get from a coordinator's phone into a published post. That look surfaced several real fragility points — a SQLite registry sitting on a bucket mount that has already been corrupted by concurrent writers once, an hourly polling job that's mostly a no-op now that a push-based trigger exists, an approval surface split across a Google Sheet and a newer hub page, and an Apps Script doorbell with a hardcoded URL and no observability. This plan collects the decisions made while working through those, so they can be built as one coherent pass instead of piecemeal.
Scope note. This plan is about the ingest path
(Drive → bundle → registry → Dashboard/hub) only. It does not cover the
two other cost/reliability findings from the original job-fleet audit
(two nightly care jobs failing,
finance-mail failing) —
those are separate, unrelated issues and should be tracked on their own.
Decisions
1. Trigger vs. schedule
DecidedRemove the hourly
ingest-watch-trigger Scheduler job. Ingestion becomes trigger-only, driven by the existing Drive push-notification webhook.
Why: the push trigger already does the real work within seconds of an upload; the hourly poll was mostly a no-op and was the single largest Cloud Run compute cost line item in the original audit.
DecidedKeep the daily channel-renewal Scheduler job. This is a different job from the one being removed — it keeps the Drive push subscription alive (subscriptions expire and must be renewed) and has to stay or the trigger path stops working within days.
DecidedAdd a Cloud Monitoring alert on the channel-renewal job's failure count. Free — Cloud Scheduler already records success/failure per run natively; this is just a policy on an existing signal, not new infrastructure. Answers "how do we know if the push mechanism silently dies" without reinstating the hourly poll.
Cost: effectively $0 — no new job, no new metric collection, free alerting policy + email notification channel.
ProposedOptional second layer: a small, infrequent (1–2×/day) check that reads the push channel's stored expiry and pages if it's closer than expected, instead of waiting for a renewal failure to be visible. Costs a fraction of a cent/month if built as a tiny Cloud Run Job or Function — not decided whether this is worth the extra moving part on top of the Scheduler-failure alert above.
2. Registry storage: SQLite-on-bucket → Postgres
DecidedMove the asset/rendition/caption/post/performance/audit registry off the gcsfuse-mounted SQLite file, into Postgres (same instance already used elsewhere in the workspace, new schema alongside the existing one rather than a new instance).
Why: this exact class of problem (a SQLite file on a bucket mount with no real file locking) has already corrupted this system once under concurrent writers. Postgres gives real ACID transactions — atomic commits, enforced constraints, automatic deadlock detection with a clean recoverable error instead of silent corruption, and durable writes that survive a crash mid-write. This mirrors a migration already completed for a different part of this workspace for the identical reason.
| Table | Change from today's schema |
|---|---|
asset | + new event_slug column (see below — today there is no durable link from an asset to the event it belongs to; it's inferred from a file-path string) |
rendition, caption, post, performance, audit | Same shape as today, moved as-is |
3. Promote "event" to a real table
DecidedCreate a first-class
event table (keyed on the event slug), consolidating what is today split across three separate places: the coordinator brief file, the Dashboard Sheet row, and each bundle's status file. asset and post reference it by a real foreign key, not a matching string.
Why: today, two independent front doors — the brief-form submission and however photos actually land in the folder — only agree by naming convention, with nothing to catch a mismatch. A foreign key surfaces that class of bug immediately instead of silently producing an orphaned asset nothing ever finds.
4. Retire the Dashboard Sheet
DecidedMigrate off the Google Sheet Dashboard entirely — it becomes the
event table above, not a parallel copy.
- The event-intake webhook writes new events to the DB instead of a Sheet row.
- The trips/activity ledger in the hub reads the DB directly instead of the Sheet.
- The Sheet-cell-editing approval path (an Apps Script trigger that calls the same approval logic the hub's Approve button already calls) is retired as redundant — the hub's review page is the one approval surface going forward.
- The periodic backup re-sync job that exists specifically to repair a dropped Sheet write becomes far less necessary once writes are transactional; its current ~20-minute cadence should be revisited once this lands, not carried forward unexamined.
5. Photo count
DecidedRetire the
.media-count bucket sidecar file once event_slug exists on asset — the count becomes a live query (or a column kept in sync by the DB) instead of a file living outside the folder it's counting, specifically so it survives the working-file cleanup that happens after a story is published.
6. Doorbell reliability (event-intake webhook)
DecidedRename the liveness route from the reserved path to the working one. Cloud Run's platform layer silently intercepts requests to the literal path used by convention for liveness checks before they ever reach the container — this was already discovered and fixed on the main hub app; the event-intake service still has the old, unreachable version and should get the same fix.
ProposedFold the standalone event-intake webhook service into the management hub as a mounted route, reached through the hub's existing stable custom domain instead of its own auto-generated, hash-based URL. This is the same consolidation pattern already used twice elsewhere in this workspace (two other standalone services were folded into the hub for the identical reason). Removes the specific fragility of a hardcoded URL silently going stale if the standalone service is ever recreated. Needs the route added to the hub's short allow-list of paths that bypass the login gate, while keeping its own separate caller-identity check exactly as strict as it is today.
Not yet explicitly confirmed as a go — flagged as strongly recommended, discussed and agreed on the merits, but should be signed off explicitly before scoping work against it.
7. Concurrency & safety, once multiple ingest runs can overlap
DecidedKeep the "don't start a new ingest run if one is already in flight" guard — its original reason (protecting the fragile SQLite file) goes away once Postgres lands, but it stays as a cost/efficiency control: a burst of upload notifications shouldn't spin up many redundant overlapping runs even if none of them would be individually unsafe.
DecidedMake the dedup insert atomic (an upsert that either inserts a new row or safely no-ops on an existing one, in a single database operation) instead of today's separate "check, then insert" logic, which is not safe if two ingest runs ever process the same photo at the same moment.
DecidedAdd retry-with-backoff around Postgres writes for the specific, expected "conflict, please retry" errors (deadlock / serialization failure). Verified this doesn't exist anywhere in the codebase yet — it would be new, not something to inherit from the earlier Postgres migration elsewhere in the workspace, which has the same gap today.
DecidedAdd a one-off delayed re-check after a skipped trigger. When the in-flight guard causes a trigger to be skipped, schedule a single follow-up run a few minutes later (a one-time delayed task, not a recurring schedule) so whatever arrived during the busy window isn't left waiting on some unrelated future upload to get picked up. The follow-up is a cheap no-op if nothing was actually missed.
ProposedMove the Drive change-feed cursor (the "how far have we already read" bookmark) into the same database, claimed safely under a row lock, rather than leaving it as a lone file on the bucket. Needed for the concurrency guard removal/relaxation above to be fully safe, not just the registry content.
8. Download safety
DecidedEnforce the existing size cap live, during download, not only as a pre-check against Drive's self-reported file size. Drive doesn't always report a size up front; when it doesn't, today's pre-check is silently skipped and an oversized file could still be pulled fully into memory. The cap itself and its value are unchanged — this closes the one path around it, it does not relax it.
Open / not yet decided
- Whether the Drive change-feed cursor migration (item 7, last row) is in scope for this pass or a fast-follow.
- The exact mechanism and cadence for the optional secondary channel-expiry watchdog (item 1).
- The revised cadence for the Sheet-repair backup job once the Sheet dependency is gone (item 4) — needs a number, not just "less often."
- Explicit go-ahead on folding the event-intake webhook into the hub (item 6) — recommended, discussed, not yet signed off as a decision.
Explicitly out of scope for this plan
- The two nightly care-data jobs currently failing, and the failing bank-notification mail job — separate, unrelated findings from the original job-fleet audit.
- The general-purpose "send us a file" intake form's audience/routing ambiguity — a real open question, but not part of the ingest pipeline itself.
- Any other Cloud Run job cadence questions from the original audit not touched by this plan (e.g. the safeguarding media-sanitise sweep's frequency).