Execution: prove durable API-cost production substrate (PR #906)

[groupthinking]

Parent and downstream work

• Parent program: [PROGRAM][OWNER:Codex] Autonomous EventRelay delivery control plane #898
• Canonical corrective PR: fix(ci): remediate PR #877 rollout and verification gaps #906
• Unblocks Execution: durable API-cost outbox and canonical usage tracking (PR #869) #861 / PR fix: harden API-cost webhook outbox retries (MYX-79) #869.
• This issue owns shared storage, migrations, deployment verification, and durable worker availability.

Current state — CODE GREEN, PROTECTED DEPLOYMENT PROOF PENDING

• Canonical PR: fix(ci): remediate PR #877 rollout and verification gaps #906 (draft; do not merge)
• Canonical branch: codex/pr877-postmerge-remediation
• Exact head: 875de9d60f9c9146727ced4e65eedddf13351ecc
• Review state: 0 unresolved threads
• Competing implementation PRs: none found; fix: harden API-cost webhook outbox retries (MYX-79) #869 is downstream application logic, not a competitor

Agent login

groupthinking

Agent run ID

eventrelay-substrate-remediation-20260721-0413z

Acceptance criteria

• Additive PostgreSQL migration compatibility remains strict about required schema and runtime privileges.
• E2E is fail-closed and binds triggering SHA, deployment, and test target.
• Deployment status URLs are accepted only from Vercel's immutable bot identity.
• Missing preview credentials fail before a secret can be sent to an untrusted URL.
• Invalid workflow permission removed.
• Destructive branch cleanup disabled because its live-PR guard/recovery ledger was unsafe.
• Workflow catalog, audit, permission, inventory, SHA-binding, and provider-binding tests corrected.
• External Fly configuration hardening incorporated with fail-closed regression coverage.

Exact-head evidence

• CI 29806013232: passed
• API cost PostgreSQL 29806013293: passed
• Coverage 29806013270: passed
• CodeQL 29806013221: passed
• Security Scan 29806013299: passed
• Secret Scan 29806013259: passed
• Dependency Review 29806432080: passed
• Review threads: 0 unresolved
• E2E 29806013215: failed closed only because the protected preview-bypass credential was unavailable; no tests ran
• Exact-head Vercel deployment dpl_7Kexd5xwqLP66vMjS9oD5iGPDpry: canceled; not deployment proof

Remaining gates — execute in this order

1. An authorized owner makes the protected Vercel preview-bypass credential available to the existing E2E workflow without exposing its value.
2. Create/observe a Preview for the exact final fix(ci): remediate PR #877 rollout and verification gaps #906 head and rerun E2E against that same deployment and SHA.
3. In protected staging, prove alembic current == head against the same shared PostgreSQL database used by the worker.
4. Run the revision A → pending row → forced revision B → distinct-reader durability probe and attach IDs/timestamps.
5. Confirm delivery remains disabled, document rollback, reverify the exact head, then mark fix(ci): remediate PR #877 rollout and verification gaps #906 ready.

There is no current repository-code test failure to repair. Rerunning E2E before the protected credential and exact-head Preview exist would only reproduce the same fail-closed result.

Execution receipt

• Agent login: groupthinking
• Run ID: eventrelay-substrate-remediation-20260721-0413z
• Canonical PR/branch: fix(ci): remediate PR #877 rollout and verification gaps #906 / codex/pr877-postmerge-remediation
• Claimed: 2026-07-21T04:13:31Z
• Latest verified heartbeat: 2026-07-21T07:43:50Z
• Exact head: 875de9d60f9c9146727ced4e65eedddf13351ecc
• Artifact: green code/security/database workflows and zero unresolved review threads

Safety boundary

Keep #906 draft. Do not expose credentials, enable webhook delivery, mutate production, or delete branches from this issue.

Truth-gate reconciliation — 2026-07-21

• Added the machine-readable Agent login and Agent run ID fields without fabricating a snapshot.
• Normalized the existing verified checklist as Acceptance criteria.
• Added copilot-rabbit authorization and triggered an exact-head reevaluation.
• Replaced the generic invalid_payload failure with explicit evidence on run 29821176987.
• Pre-dispatch intent snapshot exists: no.
• Declared file scope and focused-test contract existed before implementation: no; do not backfill as historical intent.
• Trusted terminal result from groupthinking / eventrelay-substrate-remediation-20260721-0413z: pending.
• Current-head Copilot review: pending.

Controller receipt

• Controller login/run: groupthinking / eventrelay-blocker-watch-20260721-1005z
• Canonical PR: fix(ci): remediate PR #877 rollout and verification gaps #906
• Claimed: 2026-07-21T10:05:35Z
• Latest verified heartbeat: 2026-07-21T10:10:42Z
• Exact head: 875de9d60f9c9146727ced4e65eedddf13351ecc
• Artifact: truth-gate run 29821176987 with valid identity projection and explicit provenance/scope/review reasons

Next executable action

Keep #906 draft. If the accepted Copilot trigger opens a valid finding, patch the same branch. Do not invent retroactive intent, scope, test, or agent-result evidence. Protected deployment verification and an authorized legacy-provenance disposition remain the only later human boundaries.

External conflict resolution — exact head 429c452da9b2f40e0790ebd28214b85c002f8514

• fix(ci): remediate PR #877 rollout and verification gaps #906 advanced externally from conflicted 875de9d to mergeable 429c452da9b2f40e0790ebd28214b85c002f8514.
• Controller returned it from ready to draft because exact-head E2E run 29935857568 and Coverage run 29935857736 fail.
• CI, PostgreSQL, CodeQL, Security, Secret Scan, Dependency Review, and gh-aw Validation pass.
• Five review threads remain unresolved. Protected staging/migration/worker proof remains incomplete.
• Receipt: groupthinking / eventrelay-blocker-watch-20260722-1607z; heartbeat 2026-07-22T16:16:00Z; canonical branch/PR fix(ci): remediate PR #877 rollout and verification gaps #906; exact head 429c452da9b2f40e0790ebd28214b85c002f8514.

[linear-code]

MYX-81

[github-actions]

👋 Thanks for opening this issue! It has been automatically labeled for triage.

[coderabbitai]

🔗 Related PRs

#222 - docs: branch-cleanup assessment + reproducible fail-test harness [merged]
#277 - Vercel remediation complete: waitUntil adoption + active middleware + @vercel/functions package (resolves three user-defined outcomes; 16-agent verification-gate consolidated PASS on full matrix) [closed]
#404 - fix(web): sync package-lock with @vercel/functions (unblocks #277 auto-merge) [merged]
#872 - ci: enforce deterministic agent completion truth gate [merged]

---

📝 Issue Planner

_{Check the box below or use the @coderabbitai plan command to generate an implementation plan and prompts that you can use with your favorite coding assistant.}

• Create Plan

---

🧪 Issue enrichment is currently in open beta.

You can configure auto-planning by selecting labels in the issue_enrichment configuration.

To disable automatic issue enrichment, add the following to your .coderabbit.yaml:

issue_enrichment:
  auto_enrich:
    enabled: false

💬 Have feedback or questions? Drop into our discord!

[groupthinking]

Implementation started from exact current main (832bef55a5c7eeee5a48903c2064db514fcfbc65) on branch agent/api-cost-production-substrate-myx-81.

The change will keep #869 separate and draft. This prerequisite branch owns the production PostgreSQL URL/driver, migration-owned schema and canonical Alembic path, pre-deploy migration gate, reliable Cloud Run worker CPU/minimum instance, production-shaped PostgreSQL tests, and operations/rollback documentation.

External Secret Manager values and an actual PostgreSQL endpoint cannot be fabricated in-repo; deployment will fail closed until the required secrets exist.

[coderabbitai]

Coding Plan

Summary

• Promote the cost-monitor tables to canonical, registered SQLAlchemy models on the shared metadata, fix the env.py target-metadata bug, and create a hand-written migration with the exact durable-outbox columns/indexes — killing import-time create_all() and silent schema errors in production.
• Ship the Postgres driver in the runtime image and add hard production gating that refuses SQLite//tmp/unset storage for cost tracking and webhooks.
• Provision Cloud SQL + a DATABASE_URL Secret Manager secret, wire both an app service and a dedicated always-on (min 1, CPU-always) worker service, and run migrations exactly once via a fail-closed pipeline Job before the new revision serves.
• Add ephemeral-Postgres CI proofs (upgrade-from-head, fresh upgrade, downgrade/re-upgrade), real migration/outbox health checks, a production-shaped durability test, and a rollback/verification runbook.

Design Choices

Design Choice 1: How should the three fragmented `Base`/metadata registries be reconciled so the cost tables become migration-owned?

Options Considered:

1. Move APIUsage/DailyBudget/WebhookOutbox onto the canonical model Base, register them in models/__init__.py, and point env.py's target_metadata at that same metadata.
2. Leave the private base in place and hand-write the migration only.

Chosen Option: 1

Rationale: The outbox models sit on a private base; real models use models/base.Base; Alembic targets config/database.Base — currently an empty registry, so autogenerate sees nothing. Making the models first-class registered models is the only way to keep schema, autogenerate, and RLS/naming conventions coherent long-term, and it directly satisfies "migration-owned metadata." The env.py target mismatch is a latent bug that must be fixed regardless.

Design Choice 2: Which Cloud SQL connectivity mechanism should ship?

Options Considered:

1. Cloud Run native Cloud SQL integration (--add-cloudsql-instances + /cloudsql/<conn> Unix socket) with asyncpg (app) and psycopg2 (Alembic/sync).
2. cloud-sql-python-connector library with IAM auth.

Chosen Option: 1

Rationale: The archived (working) precedent used Cloud Run's native Cloud SQL Auth Proxy socket; the Python Connector library is not present anywhere. Option 1 matches the repo's existing precedent, requires no new library, and is the standard Cloud Run pattern; the DATABASE_URL secret encodes the socket path.

Design Choice 3: How should migrations run "once, before serving," without instances performing DDL?

Options Considered:

1. A dedicated deployment-pipeline step (Cloud Run Job alembic upgrade head) executed before the gcloud run deploy of the app revision; failure aborts rollout.
2. A Docker entrypoint that runs alembic upgrade before uvicorn on every instance.

Chosen Option: 1

Rationale: Instances scale 0→N and must not each run DDL. Option 2 violates "Application instances do not perform DDL," races across instances, and re-runs on every cold start. A pipeline-level migration Job gives single-execution, fail-closed rollout semantics.

Design Choice 4: What is the worker execution model?

Options Considered:

1. A dedicated Cloud Run worker service, CPU-always-allocated, min-instances=1, running the outbox delivery loop.
2. Cloud Tasks / Cloud Run Job on a schedule.

Chosen Option: 1

Rationale: This is the ticket's recommendation: a continuously-running worker gives the simplest correct restart/retry guarantees for a polling outbox; Cloud Tasks would require reworking the delivery trigger owned by EventRelay.

💡 User Tips

Regenerate the plan with different choices with @coderabbitai <feedback>.

Implementation Steps

Phase 1: Consolidate schema ownership and Alembic invocation

Make the cost-monitor tables real, migration-owned models on the canonical metadata, fix the fractured Alembic wiring, and create the migration that defines the durable outbox schema. This phase removes the "silently starts without its schema" failure mode by eliminating import-time create_all() in production.

Task 1: Promote the cost-monitor tables to canonical registered models

Move APIUsage, DailyBudget, and WebhookOutbox onto the shared model Base so they participate in migration-owned metadata.

• Create a new model module under src/youtube_extension/backend/models/ (e.g. api_cost.py) defining the three tables on the canonical Base from models/base.py, and register it in models/__init__.py so from ...models import * pulls them in.
• Extend WebhookOutbox to the durability schema the ticket demands: keep the unique (utc_date, alert_type) constraint (renamed to the convention uq_webhook_outbox_utc_date), and add/confirm persisted next_attempt_at, claim timestamp, stale-recovery/last_attempt timestamp, retry_count, status, payload, error_message, and sent_at.
• Do NOT implement claim/backoff/recovery logic here — only the columns and indexes. That logic is owned by EventRelay #861 / MYX-79.
• Update api_cost_monitor.py to import the models from the new module instead of declaring its private Base; remove the module-local declarative_base().

Task 2: Fix Alembic target metadata and consolidate to one root invocation

Resolve the env.py-targets-empty-metadata bug and the dangling root config.

• Update src/youtube_extension/backend/migrations/env.py so target_metadata points at the same Base.metadata the models actually use (reconcile config/database.Base vs models/base.Base); this is a real bug — autogenerate currently sees nothing.
• Make the repo-root alembic.ini the single canonical entry point: point its script_location at src/youtube_extension/backend/migrations (and align sqlalchemy.url/DATABASE_URL resolution), or remove it in favor of a documented single invocation path. Eliminate the duplicate-config ambiguity.

Task 3: Author the API-cost tables migration

Create the migration that provisions api_usage, daily_budgets, and webhook_outbox.

• Add a new revision in migrations/versions/ following existing conventions (sequential id like 003_api_cost_outbox, down_revision = '002_secure_alembic', timestamped filename, reversible downgrade()).
• Create all three tables; include the unique (utc_date, alert_type) constraint and indexes supporting due-work polling (on status + next_attempt_at) and stale-claim recovery (on status + claim timestamp).
• Apply the repo's RLS/naming conventions consistent with 001_initial_schema where appropriate.
• Verify autogenerate parity, but hand-write to guarantee the exact indexes/constraints the ticket enumerates.

Task 4: Repair the migration CLI and remove import-time DDL

Fix the broken run_migrations reference and stop production create_all().

• Implement a real run_migrations (invoking alembic upgrade head via the canonical config) at the location the CLI expects (config/database.py) or update cli.py to call the actual runner — the current import always raises ImportError.
• Remove import-time Base.metadata.create_all() and the swallowed-exception initialization from api_cost_monitor.py for production; permit create_all() only for explicitly-configured SQLite dev/test paths. Production must never create schema or silently ignore schema errors.
• Update tests/unit/test_cli.py expectations that mock the previously-nonexistent function.

🤖 Prompt for AI agents

Implement Phase 1: consolidate the API-cost/outbox schema onto migration-owned
metadata and fix the fractured Alembic wiring, killing import-time
`create_all()` in production.

- Create `src/youtube_extension/backend/models/api_cost.py` defining `APIUsage`,
`DailyBudget`, and `WebhookOutbox` on the canonical `Base` from
`models/base.py`; register the module in `models/__init__.py`.
- Give `WebhookOutbox` the full durability schema: unique `(utc_date,
alert_type)` constraint renamed to `uq_webhook_outbox_utc_date`, plus persisted
`next_attempt_at`, claim timestamp, stale-recovery/`last_attempt` timestamp,
`retry_count`, `status`, `payload`, `error_message`, `sent_at`. Only add
columns/indexes here — do NOT implement claim/backoff/recovery logic (owned by
EventRelay `#861` / MYX-79).
- Update `api_cost_monitor.py` to import these registered models, removing its
private `declarative_base()`.
- Fix `src/youtube_extension/backend/migrations/env.py` so `target_metadata`
points at the same `Base.metadata` the real models use (reconcile
`config/database.Base` vs `models/base.Base`).
- Consolidate Alembic entry points: repoint the root `alembic.ini`'s
`script_location` at `src/youtube_extension/backend/migrations` (align
`sqlalchemy.url`/`DATABASE_URL` resolution), or remove it — eliminate any
duplicate-config ambiguity so there is exactly one canonical invocation.
- Add a new migration revision under `migrations/versions/` (e.g.
`003_api_cost_outbox`, `down_revision = '002_secure_alembic'`) creating
`api_usage`, `daily_budgets`, and `webhook_outbox` tables, including the unique
constraint and indexes for due-work polling (`status` + `next_attempt_at`) and
stale-claim recovery (`status` + claim timestamp); follow RLS/naming conventions
from `001_initial_schema`; hand-write rather than relying purely on
autogenerate, and include a reversible `downgrade()`.
- Implement a real `run_migrations` function (invoking `alembic upgrade head`
via the canonical config) at the location the CLI expects, or update `cli.py` to
call the real runner — fix the current `ImportError`.
- Remove import-time `Base.metadata.create_all()` and the swallowed-exception
init from `api_cost_monitor.py` for production use; allow `create_all()` only
for explicitly-configured SQLite dev/test paths.
- Update `tests/unit/test_cli.py` to match the real `run_migrations` behavior.

Phase 2: Storage gating and driver packaging

Guarantee production refuses unsafe storage and that the runtime image can actually talk to Postgres. This closes the "always constructs SQLite" and "no Postgres driver shipped" gaps.

Task 1: Ship the PostgreSQL driver in the runtime image

• Add the selected drivers (asyncpg for the app, psycopg2-binary for Alembic/sync) to requirements.txt so the production Dockerfile install path includes them — today the image cannot connect to Postgres at all.
• Confirm the pyproject.toml postgres extra stays aligned; ensure whichever install path the Dockerfile uses pulls the driver.

Task 2: Add storage-resolution detection and production gating

Refuse to enable cost tracking/webhooks on unsafe storage.

• Add helpers to DatabaseSettings in config/database.py (e.g. resolved_url, is_sqlite, is_postgres_configured) using URL-prefix detection, modeled on the substring logic in database_optimizer.py.
• In api_cost_monitor.py, gate cost_tracking_enabled (reusing the existing API_COST_TRACKING flag pattern): in production, if DATABASE_URL is unset, starts with sqlite, or resolves under /tmp, force-disable tracking/webhooks and log a loud warning rather than silently defaulting to /tmp SQLite.
• Point the cost monitor at the shared Postgres engine when configured; retain explicitly-configured SQLite only for dev/tests.
• Update database_cleanup_service.py, which independently re-derives the /tmp SQLite path, so it does not resurrect the ad-hoc store.

🤖 Prompt for AI agents

Implement Phase 2: guarantee the runtime image can talk to Postgres and that
production refuses to silently fall back to unsafe SQLite/`/tmp` storage.

- Add `asyncpg` (app driver) and `psycopg2-binary` (Alembic/sync driver) to
`requirements.txt` so the production Dockerfile install path includes them;
confirm the `pyproject.toml` `postgres` extra stays aligned and whichever
install path the Dockerfile uses pulls the driver in.
- In `config/database.py`, add helpers to `DatabaseSettings` such as
`resolved_url`, `is_sqlite`, and `is_postgres_configured`, using URL-prefix
detection modeled on the substring logic already present in
`database_optimizer.py`.
- In `api_cost_monitor.py`, gate `cost_tracking_enabled` reusing the existing
`API_COST_TRACKING` flag pattern: in production, if `DATABASE_URL` is unset,
starts with `sqlite`, or resolves under `/tmp`, force-disable tracking/webhooks
and log a loud warning instead of silently defaulting to `/tmp` SQLite.
- Point the cost monitor at the shared Postgres engine when properly configured;
retain explicitly-configured SQLite only for dev/test paths.
- Update `database_cleanup_service.py`, which independently re-derives the
`/tmp` SQLite path, so it stops resurrecting the ad-hoc store.

Phase 3: Cloud SQL, Secret Manager, migrate-before-serve, and the worker

Provision the shared database, inject it securely, run migrations exactly once before serving, and stand up a continuously-running worker. This is greenfield — no reusable GCP DB/worker infra exists.

Task 1: Provision Cloud SQL and the DATABASE_URL secret

• Add provisioning (under infrastructure/cloudrun/ scripts and/or the Terraform stack) for a Cloud SQL Postgres instance and a Secret Manager DATABASE_URL secret encoding the Cloud SQL Unix-socket connection string (postgresql+asyncpg://...@/db?host=/cloudsql/<conn>), extending the existing setup.sh/setup-secrets.sh API-key pattern.

Task 2: Wire Cloud SQL + secret into the app and update manifests

• Update .github/workflows/deploy-cloud-run.yml (and the alternative infrastructure/cloudrun/service.yaml / cloudbuild.yaml / deploy.sh) to add --add-cloudsql-instances (or the run.googleapis.com/cloudsql-instances annotation) and --set-secrets DATABASE_URL=DATABASE_URL:latest.

Task 3: Add a migrate-before-serve pipeline step

Run migrations once, fail-closed, before the new revision serves.

• Insert a migration step in the deploy workflow between image push and gcloud run deploy — a Cloud Run Job (built from the same image) executing alembic upgrade head against Cloud SQL, or an equivalent gcloud run jobs execute step.
• Ensure a non-zero migration exit aborts the rollout (no app deploy on migration failure). Instances must never perform DDL.

Task 4: Define the always-on worker execution model

• Add a dedicated Cloud Run worker service (new manifest/deploy invocation) built from the same image, with CPU always allocated and min-instances=1, running the outbox delivery loop entrypoint.
• Bind the same Cloud SQL instance and DATABASE_URL secret to the worker.
• Provide the worker start command/entrypoint hook, but leave the delivery state-machine body to EventRelay #861 / MYX-79; this ticket guarantees the runtime is continuously available, not the algorithm.

🤖 Prompt for AI agents

Implement Phase 3: provision the shared production database substrate, inject it
securely, run migrations exactly once before serving, and stand up an always-on
worker. This is greenfield infrastructure work.

- Add provisioning (under `infrastructure/cloudrun/` scripts and/or the
Terraform stack) for a Cloud SQL Postgres instance plus a Secret Manager
`DATABASE_URL` secret encoding the Cloud SQL Unix-socket connection string
(`postgresql+asyncpg://...@/db?host=/cloudsql/<conn>`), extending the existing
`setup.sh`/`setup-secrets.sh` API-key pattern.
- Update `.github/workflows/deploy-cloud-run.yml` (and the alternative
`infrastructure/cloudrun/service.yaml` / `cloudbuild.yaml` / `deploy.sh`) to add
`--add-cloudsql-instances` (or the `run.googleapis.com/cloudsql-instances`
annotation) and `--set-secrets DATABASE_URL=DATABASE_URL:latest`.
- Insert a migrate-before-serve step in the deploy pipeline, between image push
and `gcloud run deploy`: run a Cloud Run Job (from the same image) executing
`alembic upgrade head` against Cloud SQL, or an equivalent `gcloud run jobs
execute` step. A non-zero exit must abort the rollout — no app deploy on
migration failure. Application instances must never perform DDL themselves.
- Add a dedicated Cloud Run worker service (new manifest/deploy invocation),
built from the same image, with CPU always allocated and `min-instances=1`,
running the outbox delivery loop entrypoint. Bind the same Cloud SQL instance
and `DATABASE_URL` secret to it.
- Provide only the worker start command/entrypoint hook — leave the actual
delivery state-machine body to EventRelay `#861` / MYX-79. This ticket's job is
guaranteeing continuous runtime availability, not the delivery algorithm.

Phase 4: CI migration proofs against ephemeral PostgreSQL

Prove migrations are correct and reversible before they ever reach production. This is a net-new CI capability.

Add a Postgres-backed migration validation job

• Add a new job to .github/workflows/ci.yml with a services: postgres: container and a DATABASE_URL pointing at it, installing the postgres extra.
• Run three proofs: upgrade from the current head, a fresh-database upgrade head, and a downgrade+re-upgrade head cycle — all must succeed against the ephemeral Postgres.

🤖 Prompt for AI agents

Implement Phase 4: add a net-new CI job that proves migrations are correct and
reversible against a real ephemeral Postgres before anything reaches production.

- Add a new job to `.github/workflows/ci.yml` with a `services: postgres:`
container and a `DATABASE_URL` pointing at it; install the `postgres` extra as
part of the job setup.
- Run three proofs against the ephemeral Postgres, all of which must succeed:
(1) upgrade from the current head, (2) a fresh-database `upgrade head`, and (3)
a `downgrade` followed by a re-`upgrade head` cycle.

Phase 5: Operational verification, durability test, and docs

Surface migration/outbox health for readiness, prove durability across replacement, and document rollback/verification.

Task 1: Extend health checks with migration and outbox status

• Extend api/v1/router.py (detailed_health_check_v1) and health_monitoring_service.py to report alembic current == head and outbox pending/failed counts (query WebhookOutbox grouped by status).
• Wire the DB-dependent check as a readiness-style signal (per the k8s /ready vs /health split) so a transient DB outage does not trigger liveness restart loops. Note the existing DatabaseChecker is simulated — replace with a real connection/query.

Task 2: Add a production-shaped durability test

• Add an integration test (under tests/integration/) against real Postgres (reuse the CI Postgres service / a compose fixture) proving a pending WebhookOutbox row survives process/revision replacement and is visible to two concurrent sessions/instances.
• Keep scope to substrate durability/visibility; the outbox concurrency/claim state-machine tests remain owned by EventRelay #861 / MYX-79.

Task 3: Document rollback and operational verification

• Update BACKEND_DEPLOY.md / docs/deployment/** with the canonical alembic upgrade head invocation, rollback procedure (alembic downgrade -1 + Cloud Run traffic revert), the alembic current == head check, and the pending/failed outbox health verification.

🤖 Prompt for AI agents

Implement Phase 5: surface real migration/outbox health signals, prove
durability across process/revision replacement, and document operational
verification.

- Extend `api/v1/router.py`'s `detailed_health_check_v1` and
`health_monitoring_service.py` to report whether `alembic current == head` and
to report outbox pending/failed counts (query `WebhookOutbox` grouped by
status).
- Wire the DB-dependent check as a readiness-style signal (following the k8s
`/ready` vs `/health` split) so a transient DB outage doesn't trigger liveness
restart loops. Replace the existing simulated `DatabaseChecker` with a real
connection/query — it currently fakes its result.
- Add an integration test under `tests/integration/` against real Postgres
(reusing the CI Postgres service or a compose fixture) that proves a `pending`
`WebhookOutbox` row survives process/revision replacement and is visible to two
concurrent sessions/instances. Keep this scoped strictly to substrate
durability/visibility — outbox concurrency/claim state-machine tests remain
owned by EventRelay `#861` / MYX-79.
- Update `BACKEND_DEPLOY.md` / `docs/deployment/**` with the canonical `alembic
upgrade head` invocation, the rollback procedure (`alembic downgrade -1` plus
Cloud Run traffic revert), the `alembic current == head` verification check, and
how to verify pending/failed outbox health.

Research

Heads up — the API-cost monitor (src/youtube_extension/backend/services/api_cost_monitor.py) is a self-contained SQLite store gone rogue: it declares its own declarative_base(), defaults to /tmp/api_cost_monitoring.db, and calls Base.metadata.create_all() at import time inside a swallowed try/except. That's a landmine — a module-level singleton silently self-starts the webhook delivery worker as a fire-and-forget task, with zero connection to the rest of the backend.

Worse, the backend has THREE separate Base classes fighting for attention: the outbox module's private base, config/database.py::Base (which Alembic's env.py targets as target_metadata — an empty registry, so autogenerate sees nothing), and models/base.py::Base (which every real model actually inherits from). This is why autogenerate is blind.

The Alembic setup itself is split-brained: the working environment lives under src/youtube_extension/backend/ (alembic.ini → migrations/), while the repo-root alembic.ini points at a nonexistent alembic/ directory. The CLI migrate command imports a run_migrations function that doesn't even exist — that's a guaranteed ImportError waiting to happen in production.

On the dependency side, requirements.txt ships alembic + aiosqlite but no Postgres driver at all — psycopg2-binary/asyncpg only exist in the optional postgres extra. That means the runtime image literally cannot talk to Postgres.

Deployment-wise, there is no Cloud Run path that sets a DB URL, binds Cloud SQL, runs migrations, or guarantees an always-on worker — everything scales to zero, which is fatal for a polling outbox. CI has no Postgres service and no migration validation, so broken migrations can sail straight to prod undetected.

Finally, the /api/v1/health endpoint is a fraud — it returns stubbed/simulated data with no real DB, Alembic, or outbox awareness, meaning ops has zero real signal into whether the system is actually healthy.

---

🚀 Next Steps

🤖 All AI agent prompts combined

Task: 1

Implement Phase 1: consolidate the API-cost/outbox schema onto migration-owned
metadata and fix the fractured Alembic wiring, killing import-time
`create_all()` in production.

- Create `src/youtube_extension/backend/models/api_cost.py` defining `APIUsage`,
`DailyBudget`, and `WebhookOutbox` on the canonical `Base` from
`models/base.py`; register the module in `models/__init__.py`.
- Give `WebhookOutbox` the full durability schema: unique `(utc_date,
alert_type)` constraint renamed to `uq_webhook_outbox_utc_date`, plus persisted
`next_attempt_at`, claim timestamp, stale-recovery/`last_attempt` timestamp,
`retry_count`, `status`, `payload`, `error_message`, `sent_at`. Only add
columns/indexes here — do NOT implement claim/backoff/recovery logic (owned by
EventRelay `#861` / MYX-79).
- Update `api_cost_monitor.py` to import these registered models, removing its
private `declarative_base()`.
- Fix `src/youtube_extension/backend/migrations/env.py` so `target_metadata`
points at the same `Base.metadata` the real models use (reconcile
`config/database.Base` vs `models/base.Base`).
- Consolidate Alembic entry points: repoint the root `alembic.ini`'s
`script_location` at `src/youtube_extension/backend/migrations` (align
`sqlalchemy.url`/`DATABASE_URL` resolution), or remove it — eliminate any
duplicate-config ambiguity so there is exactly one canonical invocation.
- Add a new migration revision under `migrations/versions/` (e.g.
`003_api_cost_outbox`, `down_revision = '002_secure_alembic'`) creating
`api_usage`, `daily_budgets`, and `webhook_outbox` tables, including the unique
constraint and indexes for due-work polling (`status` + `next_attempt_at`) and
stale-claim recovery (`status` + claim timestamp); follow RLS/naming conventions
from `001_initial_schema`; hand-write rather than relying purely on
autogenerate, and include a reversible `downgrade()`.
- Implement a real `run_migrations` function (invoking `alembic upgrade head`
via the canonical config) at the location the CLI expects, or update `cli.py` to
call the real runner — fix the current `ImportError`.
- Remove import-time `Base.metadata.create_all()` and the swallowed-exception
init from `api_cost_monitor.py` for production use; allow `create_all()` only
for explicitly-configured SQLite dev/test paths.
- Update `tests/unit/test_cli.py` to match the real `run_migrations` behavior.
===============================================================================

Task: 2

Implement Phase 2: guarantee the runtime image can talk to Postgres and that
production refuses to silently fall back to unsafe SQLite/`/tmp` storage.

- Add `asyncpg` (app driver) and `psycopg2-binary` (Alembic/sync driver) to
`requirements.txt` so the production Dockerfile install path includes them;
confirm the `pyproject.toml` `postgres` extra stays aligned and whichever
install path the Dockerfile uses pulls the driver in.
- In `config/database.py`, add helpers to `DatabaseSettings` such as
`resolved_url`, `is_sqlite`, and `is_postgres_configured`, using URL-prefix
detection modeled on the substring logic already present in
`database_optimizer.py`.
- In `api_cost_monitor.py`, gate `cost_tracking_enabled` reusing the existing
`API_COST_TRACKING` flag pattern: in production, if `DATABASE_URL` is unset,
starts with `sqlite`, or resolves under `/tmp`, force-disable tracking/webhooks
and log a loud warning instead of silently defaulting to `/tmp` SQLite.
- Point the cost monitor at the shared Postgres engine when properly configured;
retain explicitly-configured SQLite only for dev/test paths.
- Update `database_cleanup_service.py`, which independently re-derives the
`/tmp` SQLite path, so it stops resurrecting the ad-hoc store.
===============================================================================

Task: 3

Implement Phase 3: provision the shared production database substrate, inject it
securely, run migrations exactly once before serving, and stand up an always-on
worker. This is greenfield infrastructure work.

- Add provisioning (under `infrastructure/cloudrun/` scripts and/or the
Terraform stack) for a Cloud SQL Postgres instance plus a Secret Manager
`DATABASE_URL` secret encoding the Cloud SQL Unix-socket connection string
(`postgresql+asyncpg://...@/db?host=/cloudsql/<conn>`), extending the existing
`setup.sh`/`setup-secrets.sh` API-key pattern.
- Update `.github/workflows/deploy-cloud-run.yml` (and the alternative
`infrastructure/cloudrun/service.yaml` / `cloudbuild.yaml` / `deploy.sh`) to add
`--add-cloudsql-instances` (or the `run.googleapis.com/cloudsql-instances`
annotation) and `--set-secrets DATABASE_URL=DATABASE_URL:latest`.
- Insert a migrate-before-serve step in the deploy pipeline, between image push
and `gcloud run deploy`: run a Cloud Run Job (from the same image) executing
`alembic upgrade head` against Cloud SQL, or an equivalent `gcloud run jobs
execute` step. A non-zero exit must abort the rollout — no app deploy on
migration failure. Application instances must never perform DDL themselves.
- Add a dedicated Cloud Run worker service (new manifest/deploy invocation),
built from the same image, with CPU always allocated and `min-instances=1`,
running the outbox delivery loop entrypoint. Bind the same Cloud SQL instance
and `DATABASE_URL` secret to it.
- Provide only the worker start command/entrypoint hook — leave the actual
delivery state-machine body to EventRelay `#861` / MYX-79. This ticket's job is
guaranteeing continuous runtime availability, not the delivery algorithm.
===============================================================================

Task: 4

Implement Phase 4: add a net-new CI job that proves migrations are correct and
reversible against a real ephemeral Postgres before anything reaches production.

- Add a new job to `.github/workflows/ci.yml` with a `services: postgres:`
container and a `DATABASE_URL` pointing at it; install the `postgres` extra as
part of the job setup.
- Run three proofs against the ephemeral Postgres, all of which must succeed:
(1) upgrade from the current head, (2) a fresh-database `upgrade head`, and (3)
a `downgrade` followed by a re-`upgrade head` cycle.
===============================================================================

Task: 5

Implement Phase 5: surface real migration/outbox health signals, prove
durability across process/revision replacement, and document operational
verification.

- Extend `api/v1/router.py`'s `detailed_health_check_v1` and
`health_monitoring_service.py` to report whether `alembic current == head` and
to report outbox pending/failed counts (query `WebhookOutbox` grouped by
status).
- Wire the DB-dependent check as a readiness-style signal (following the k8s
`/ready` vs `/health` split) so a transient DB outage doesn't trigger liveness
restart loops. Replace the existing simulated `DatabaseChecker` with a real
connection/query — it currently fakes its result.
- Add an integration test under `tests/integration/` against real Postgres
(reusing the CI Postgres service or a compose fixture) that proves a `pending`
`WebhookOutbox` row survives process/revision replacement and is visible to two
concurrent sessions/instances. Keep this scoped strictly to substrate
durability/visibility — outbox concurrency/claim state-machine tests remain
owned by EventRelay `#861` / MYX-79.
- Update `BACKEND_DEPLOY.md` / `docs/deployment/**` with the canonical `alembic
upgrade head` invocation, the rollback procedure (`alembic downgrade -1` plus
Cloud Run traffic revert), the `alembic current == head` verification check, and
how to verify pending/failed outbox health.

💡 Iterate on the plan with: `@coderabbitai` <feedback>

Example Feedback
- `@coderabbitai` You can skip phase 3. Add a simple unit test case for phase 2.
- `@coderabbitai` For design choice 1 go ahead with option 3 and replan.

---

💬 Have feedback or questions? Drop into our discord!