Sirloin Runbook

Operational guide for the sirloin Go service. For the deeper billing runbook (saga state, payment reconciliation) see runbooks/billing and runbooks/billing-pitfalls. For service overview see Sirloin.

Deploy

CI workflow: .github/workflows/sirloin.yml. Triggers on push or PR touching apps/sirloin/**.

Pipeline jobs:

1. lint — golangci-lint v2 against apps/sirloin.
2. checks — go mod tidy cleanliness, make verify-migrations, govulncheck.
3. test — go test -race -coverprofile=coverage.out -count=1 -timeout=10m ./..., coverage gate >= 3%.

Image build / push to ECR is currently commented out in the workflow (docker-build job and ECR login steps in .github/workflows/sirloin.yml:110-:146). Sirloin is deployed via Railway only. Production deploys come from Railway tied to the release branch — see deployment-env and railway. Railway picks up apps/sirloin/railway.json.

Index migrations on large tables

go-pg-migrate runs each migration inside a transaction, so migration files use plain CREATE INDEX, which blocks writes while it builds. For indexes on hot, large tables (e.g. media.media, 11M+ rows) build them out-of-band before deploying the migration so the rollout takes no blocking lock:

psql "$DATABASE_URL" -f apps/sirloin/ops/strip_perf_indexes_concurrent.sql

The script uses CREATE INDEX CONCURRENTLY IF NOT EXISTS, so the matching migration (e.g. apps/sirloin/internal/app/migrate/schema/116_strip_perf_indexes.sql) no-ops for indexes already built. Otherwise schedule the deploy off-peak.

For the pending-media fairness index from migration 150, use its focused prebuild script and confirm the readback reports indisready and indisvalid as true:

psql "$DATABASE_URL" -f apps/sirloin/ops/pending_media_fairness_index_concurrent.sql

Rollback of the migration-150 fairness index removes only its supporting index. Run outside a transaction so media reads and writes continue during removal:

DROP INDEX CONCURRENTLY IF EXISTS media.idx_media_pending_user_created_id;

Semantic-search index rollout

Before deploying the new legacy search path, prepare its fallback indexes with the direct Sirloin database URL, including on an empty database. These indexes support search before semantic cutover and during rollback:

psql --no-psqlrc --dbname "$SIRLOIN_DATABASE_URL" \
  --file apps/sirloin/ops/semantic_search_fallback_indexes_concurrent.sql

Migration 170 only adds nullable semantic-artifact columns. It neither populates descriptions or embeddings nor creates or validates semantic indexes, including on an empty database. Deploy it with semantic search disabled. After deployment, prepare the semantic HNSW and filter indexes using the existing operation:

psql --no-psqlrc --dbname "$SIRLOIN_DATABASE_URL" \
  --file apps/sirloin/ops/example_semantic_indexes_concurrent.sql

Migration 170 does not enforce index readiness or set a custom lock timeout. Its ALTER TABLE still takes PostgreSQL’s normal DDL locks. Legacy indexes are managed only by the fallback operation, without a separate schema migration. Migration 171 adds two nullable retry timestamps. Its ALTER TABLE requires a PostgreSQL DDL lock, whose wait time depends on concurrent transactions. It does not backfill rows or build indexes. A failed legacy or semantic description attempt defers that row for 30 minutes, so newer rows can enter the 25-row worker batch. Source changes clear both retry times; successful writes clear their own retry time. Provider or media failures remain visible in worker errors and eligible for later retry.

The semantic operation creates indexes concurrently, repairs invalid or structurally mismatched indexes, and exits nonzero if its final validation fails. It can also run before migration 170 for compatibility with the previous prebuild-first sequence. The fallback operation has the same repair behavior. Healthy indexes retain their build-time tuning; tuning differences alone do not trigger a rebuild.

Run these operations off-peak and monitor database CPU, I/O, WAL, replica lag, and pg_stat_progress_create_index. The fallback operation must exit zero before serving the new legacy search path; this is an operator prerequisite, not a migration gate. The semantic operation must exit zero, with both indexes ready, valid and matching the search contract, before confirming CUTOVER_READY or enabling semantic search. Complete description and embedding backfill and the ranking, recall and latency release checks as well. CUTOVER_READY is an operator confirmation, not an automatic database/index probe. Keep search disabled if the semantic operation fails or is skipped; migration success alone does not establish search readiness.

Already-applied migrations do not replay when their files change. Preserve existing columns and indexes and rerun the corresponding operation for validation or repair; this simplification does not require a database reset or re-backfill.

These numbers replace the showcase branch’s semantic migrations 155–157 and the PR’s earlier semantic migrations 158–159 and 161–162. Main now owns migration 161 for the active-generation count index and 163–165 for recipe families, egress indexes and Explore popularity. Main also owns 166–169 for the payments mirror, dunning metadata/indexes and media lineage. Semantic artifacts now use migration 170; description retry bookkeeping uses migration 171. These replace the PR’s unmerged 166–167 semantic migration numbers. Use a fresh preview database from main when an old semantic migration set was already applied. In particular, a preview that applied the PR’s 166–167 cannot reuse those migration records for main’s different 166–167 migrations. Renumbering the PR files does not repair that migration history. Do not rewrite migration history or assume those databases represent a clean production upgrade.

Deploy Round before Sirloin: the semantic client requires embedding model contract 2.0.0 and fails closed against an older Round. Keep semantic search, cutover readiness and backfill disabled during deployment; wait for old instances to drain before enabling backfill.

The ListMediaExamples pagination fields are additive: old Sirloin instances ignore skip_total and skip_catalog and omit has_more. Brisket keeps a full-page pagination fallback for those responses, so these fields do not require an atomic Sirloin/Brisket/Flank deployment. Keep that fallback until all environments use the new backend. During a mixed-version rollout, old instances still compute totals and return catalogs; wait until they have drained before measuring the full performance improvement or signing off latency. See proto/sirloin/v5/sirloin.proto and apps/brisket/src/features/explore-page/hooks/use-explore-examples.ts.

The two semantic jobs run on startup when backfill is enabled. Per-kind advisory locks prevent overlapping executions, but do not eliminate subsequent startup sweeps during a rolling deploy. During backfill, monitor OpenRouter request volume, 429/5xx errors, description backlog and Round embedding latency. Confirm the configured concurrency fits provider quotas before enabling it. If throttling or latency becomes unacceptable, disable backfill and let active jobs drain; restarting replicas is not a rate-limit remedy. See apps/sirloin/internal/pkg/riverjobs/schedule.go and apps/sirloin/internal/app/worker/river.go for scheduling and overlap guards.

Semantic-search rollback

Disable SIRLOIN_EXAMPLE_SEMANTIC_V2_SEARCH_ENABLED first to return reads to legacy embeddings. Clear SIRLOIN_EXAMPLE_SEMANTIC_V2_CUTOVER_READY, disable SIRLOIN_EXAMPLE_SEMANTIC_V2_BACKFILL_ENABLED, and wait for running semantic jobs to stop. Preserve both vector columns, semantic descriptions/versions and all semantic and fallback indexes prepared by the operations; do not drop the fallback indexes used by legacy search. This rollback changes flags, not migration history. Index or column removal requires a separate reviewed migration after confirming no reader or worker still uses them. See apps/sirloin/internal/app/migrate/schema/170_example_semantic_versions.sql.

Example-search ranking evaluation

The current contract uses character-universal scene descriptions. Image descriptions use openrouter-gemini-3.7-flash-observable-v7; video descriptions use openrouter-gemini-3.7-flash-observable-video-v3. Complete both description and embedding backfill before capturing rankings. Keep semantic search disabled until the release checks pass.

Sirloin requires OPENROUTER_API_KEY at startup to maintain the legacy search fallback for new and changed examples, even when semantic backfill is disabled. Provision it in every deployment before deploying this change. Description backfill also uses this key. It calls OpenRouter’s chat-completions API with google/gemini-3.7-flash, restricted to google-vertex, with provider fallbacks disabled and data_collection: deny. Source images and video spritesheets pass through OpenRouter to Google; verify account access, credits and the team’s data-handling approval before enabling backfill. No direct Google service-account key is required for this worker. SIRLOIN_GOOGLE_CREDENTIALS remains separate for image generation.

Authentication failures, unavailable routes and malformed responses leave the description pending. Only explicit safety filtering/refusal uses metadata-only fallback. The new versions invalidate older descriptions and their embeddings; do not mix pre-OpenRouter evaluation results with the final release gate. See apps/sirloin/internal/pkg/gemini/openrouter.go for the request contract.

Capture both top-10 rankings against one fully backfilled staging snapshot:

The capture command requires an explicit --snapshot identifier. This records provenance, not database isolation: legacy and current rankings use separate reads. Use an isolated, fully backfilled database copy and stop backfill and other corpus writers for the entire capture. Supplying a label does not freeze the database or verify that backfill has completed.

cd apps/sirloin
SIRLOIN_DATABASE_URL=... SIRLOIN_ROUND_HOST=... \
  go run ./cmd/scripts/example-search-ranking-eval \
  --judgments ./cmd/scripts/example-search-ranking-eval/testdata/scene_control_queries.json \
  --capture --snapshot <snapshot-id> --output /tmp/example-search-judgments.json

Grade every captured candidate from 0 to 3. Use preview_path for images and preview_video_path for videos. Judge the source media before inspecting the frozen ranks. Do not use generated descriptions as the primary evidence, because the current search embeds semantic_description directly. Then run the comparison:

go run ./cmd/scripts/example-search-ranking-eval \
  --judgments /tmp/example-search-judgments.json

The evaluator scores the captured ranks without querying live data again. It rejects incomplete judgments, incomplete ranks, and a current macro NDCG@10 below the legacy result. The default scene control set contains 22 search queries. The original 24-query control_queries.json remains unchanged for historical comparison and future coverage. The scene set excludes blonde-woman because hair color is outside the contract, and windy-hair because the prompt excludes hair descriptions, including hair motion. Appearance search is deferred to character and creator discovery work for FaaP.

Freeze queries and relevance criteria before evaluating results. Compare both rankings on that same scene set. Retain the original 24-query results as historical evidence; do not compare their aggregate directly with the 22-query score. Record static and motion cohort deltas separately. A cohort regression requires correction or explicit product acceptance from Sam before release; acceptance of the universal prompt does not accept a ranking regression. Final ranking results are release evidence after implementation, not a Gate 2 prerequisite.

Character recipe families

Migration 163 introduces recipe families directly with one published document and one optional draft. It creates no family version counter, history table, version index, or session foreign key to family documents. Family prices come from recipes; no existing family data or event snapshots need conversion.

The migration retains document integrity, publication state, and session provenance checks. The document uniqueness and route foreign-key constraints are deferred until transaction commit so publication can replace the current document atomically. Sessions record the family key, digest, revision, role, and selections independently of the current family definition.

Historical-eligibility and occupied-family-slot indexes support account routing and quotas. The slot index covers (account_id, family_key) for completed creation sessions; quota queries join the character row to exclude deleted characters.

This is one transactional migration. On established tables its index builds and constraint validation can block writes until commit; schedule deployment based on table size and database load. Migration 163 is introduced by this branch and assumes the family schema has not already been installed.

For a recipe rollback blocked by CharacterRecipeFamilyDependencyError, first publish a disabled family or repoint its members to compatible recipes, retry the recipe rollback, then repair and re-enable the family. Follow the family publication guidance. Malformed canonical data in an active family also blocks recipe publication and rollback; repair the family before retrying, rather than bypassing the dependency check.

Active-generation count index

Before deploying migration 161, prebuild media.idx_media_generating_user_type with apps/sirloin/ops/media_generation_index_concurrent.sql. It indexes (user_id, type) for non-deleted generating media, supporting the per-user counts in apps/sirloin/internal/pkg/storage/media.go.

Read current_database() and COALESCE(host(inet_server_addr()), 'local') from the intended connection. Confirm those values, set EXPECTED_DATABASE and EXPECTED_SERVER_ADDR, then run psql outside a transaction:

psql "$DATABASE_URL" \
  -v expected_database="$EXPECTED_DATABASE" \
  -v expected_server_addr="$EXPECTED_SERVER_ADDR" \
  -f apps/sirloin/ops/media_generation_index_concurrent.sql

The script checks the target and requires a valid, ready index with the expected definition. It stops on an invalid or mismatched index; inspect it before any explicit drop and rebuild. Its statement timeout is 30 minutes.

Migration apps/sirloin/internal/app/migrate/schema/161_media_generation_count_index.sql provides a transactional fallback for fresh databases. Its five-second lock timeout limits lock acquisition, not index-build duration. Do not use that fallback to build the production index during deployment.

After deployment, compare count execution plans and queue wait at comparable load. For rollback, revert the count query before an operator removes the index:

DROP INDEX CONCURRENTLY IF EXISTS media.idx_media_generating_user_type;

Hot path lookup indexes

Before deploying migration 172, prebuild its three indexes with apps/sirloin/ops/hot_path_lookup_indexes_concurrent.sql:

• users.idx_purchases_user_id serves the per-user purchase reads in apps/sirloin/internal/pkg/storage/users.go.
• media.idx_media_user_nsfw_available serves the successful NSFW media count in apps/sirloin/internal/pkg/storage/userkycverifications.go.
• characters.idx_characters_brain_projection_pending serves the Brain projection sweep in apps/sirloin/internal/pkg/storage/characterbrainprojection.go.

As for migration 161, confirm the target, adding COALESCE(inet_server_port()::text, 'local') as EXPECTED_SERVER_PORT, then run psql outside a transaction:

psql "$DATABASE_URL" \
  -v expected_database="$EXPECTED_DATABASE" \
  -v expected_server_addr="$EXPECTED_SERVER_ADDR" \
  -v expected_server_port="$EXPECTED_SERVER_PORT" \
  -f apps/sirloin/ops/hot_path_lookup_indexes_concurrent.sql

The media index scans the whole media.media heap, so the statement timeout is 60 minutes. The script stops unless all three indexes are valid, ready and match their expected definitions. Do not let apps/sirloin/internal/app/migrate/schema/172_hot_path_lookup_indexes.sql build the media index in production; it would block media writes for the whole build. If the prebuild reports an invalid index, for example after an interrupted build, drop it concurrently and rerun the prebuild before deploying. The queries do not depend on the indexes, so rollback is a plain removal:

DROP INDEX CONCURRENTLY IF EXISTS users.idx_purchases_user_id;
DROP INDEX CONCURRENTLY IF EXISTS media.idx_media_user_nsfw_available;
DROP INDEX CONCURRENTLY IF EXISTS characters.idx_characters_brain_projection_pending;

Queued character chain retry index

Before deploying migration 186, prebuild characters.idx_character_executions_queued_chain_retry with apps/sirloin/ops/character_chain_retry_index_concurrent.sql. It serves the queued-child retry sweep in apps/sirloin/internal/pkg/storage/characterexecution.go. Confirm the target as for migration 172, then run psql outside a transaction:

psql "$DATABASE_URL" \
  -v expected_database="$EXPECTED_DATABASE" \
  -v expected_server_addr="$EXPECTED_SERVER_ADDR" \
  -v expected_server_port="$EXPECTED_SERVER_PORT" \
  -f apps/sirloin/ops/character_chain_retry_index_concurrent.sql

The script first applies migration 186’s idempotent nullable-column additions, without changing the migration ledger, so the index can be built before the first photo-first deployment. That metadata change requires a brief exclusive table lock; the script limits waiting for it to five seconds. The index then builds concurrently with no lock timeout and a 60-minute statement timeout, allowing ordinary writes, and the script validates its exact definition and ready/valid state. A failed or interrupted build may leave an invalid index; inspect it, explicitly drop it concurrently, and rerun the prebuild.

Do not let migration 186 build this index on a large production table: its transaction retains the column addition’s exclusive lock, blocking reads and writes for the duration of the build. Even after prebuilding, the migration still takes a brief exclusive table lock to check the column additions before skipping the existing index; deploy without long-running transactions on characters.character_executions. The migrator can safely reapply the prebuilt schema changes.

After deployment, inspect the retry query’s plan with representative queued rows. The index uses (created_at, id) to filter by age; PostgreSQL sorts eligible rows by (updated_at, id) to preserve fair retry ordering. Keeping updated_at out of the index preserves HOT eligibility for timestamp and input-only updates. Running and terminal executions are outside the partial index. The worker uses River’s lowest priority, 4, so a sustained higher-priority backlog can delay retries.

During a mixed deployment, an old worker can log an unknown retry_queued_character_chains kind and discard that sweep tick. The queued child remains durable for a later supported sweep, though recovery may be delayed. Finish deploying compatible workers and Brisket before publishing an opted-in recipe.

To roll back the feature:

1. Restore the previous non-photo-first recipe on the route so new sessions stop pinning the opted-in version. Existing pins are unchanged.
2. Keep compatible Sirloin and Brisket code while opted-in pins remain served. Draining active/running sessions alone does not make old binaries safe: completed sessions and characters retain recipe pins used by later reads and continuations. A binary downgrade requires a separately verified compatibility plan for that persisted data.
3. After sessions that could create or run chained work have drained, optionally remove only the retry index using the statement below. Retain the nullable columns and compatible runtime support for pinned data.

This read-only inventory shows session status and retained character pins by recipe version. canonical_recipe is stored as UTF-8 bytes. An empty active/running count is a drain check, not authorization to downgrade binaries:

WITH photo_first_versions AS (
    SELECT id
    FROM characters.character_recipe_versions
    WHERE convert_from(canonical_recipe, 'UTF8')::jsonb @?
        '$.presentation.flows[*].stages[*].actions[*] ? (@.role == "CHARACTER_ACTION_ROLE_CHAINED")'
)
SELECT session.recipe_version_id, 'session' AS pin_kind,
       session.status AS state, count(*) AS pins
FROM characters.character_sessions AS session
JOIN photo_first_versions AS recipe ON recipe.id = session.recipe_version_id
GROUP BY session.recipe_version_id, session.status
UNION ALL
SELECT character.recipe_version_id, 'character' AS pin_kind,
       character.character_lifecycle AS state, count(*) AS pins
FROM characters.characters AS character
JOIN photo_first_versions AS recipe ON recipe.id = character.recipe_version_id
WHERE character.deleted_at IS NULL
GROUP BY character.recipe_version_id, character.character_lifecycle
ORDER BY recipe_version_id, pin_kind, state;

Optional index removal (outside a transaction):

DROP INDEX CONCURRENTLY IF EXISTS characters.idx_character_executions_queued_chain_retry;

Pending media poll index

Before deploying migration 173, prebuild media.idx_media_user_pending_generating_created with apps/sirloin/ops/pending_media_poll_index_concurrent.sql. It serves Brisket’s pending-media poll, which reaches ListMedia in apps/sirloin/internal/pkg/storage/media.go with PENDING and GENERATING statuses. Confirm the target as for migration 172, then run psql outside a transaction:

psql "$DATABASE_URL" \
  -v expected_database="$EXPECTED_DATABASE" \
  -v expected_server_addr="$EXPECTED_SERVER_ADDR" \
  -v expected_server_port="$EXPECTED_SERVER_PORT" \
  -f apps/sirloin/ops/pending_media_poll_index_concurrent.sql

The build scans the whole media.media heap. The script stops unless the index is valid, ready and matches its expected definition. Even after the prebuild, the migration’s CREATE INDEX IF NOT EXISTS takes a SHARE lock on media.media before it skips, so it waits for any open media.media write transaction and queues later writers behind it. Deploy when no long-running write transaction holds that table. Migration 173 also sets autovacuum_vacuum_scale_factor = 0.01 on media.media, so vacuum clears dead queue entries about daily instead of every few weeks.

After deployment, run a plain EXPLAIN of the poll for a user with queued media. It should scan idx_media_user_pending_generating_created with an Index Cond on user_id, not idx_media_pending_generating_character_id with a user_id filter. Rollback does not touch data:

DROP INDEX CONCURRENTLY IF EXISTS media.idx_media_user_pending_generating_created;
ALTER TABLE media.media RESET (autovacuum_vacuum_scale_factor);

Approved celebrities backfill

Seed users.approved_celebrities from existing Brain approvals with apps/sirloin/ops/approved_celebrities_backfill/README.md, which covers timing, the Trust review gate, CSV handling and rollback.

Strip popularity rollup

StripListPopularExamples / StripListPopularCategories read precomputed counts from the media.popularity_v3_* projection instead of aggregating media.media live. The worker job TaskRefreshMediaPopularity (apps/sirloin/internal/app/worker/refreshmediapopularity.go) republishes recent days and a rotating historical window into it; the endpoints fall back to the live query when a count-affecting filter is set or before the first refresh completes. If the popular pages look stale or empty, confirm the job is running and that media.popularity_v3_state reports the projection ready.

sequenceDiagram
    participant Dev
    participant GH as GitHub Actions
    participant Railway
    participant Prod as sirloin (prod)
    Dev->>GH: push to release
    GH->>GH: lint, checks, test (sirloin.yml)
    GH-->>Dev: green
    Railway->>Prod: build + rollout
    Prod-->>Railway: /health (gRPC + HTTP)

Manual deploy / promote

Promote main → release via PR; Railway watches release. No manual gh step required for rollout. To force a rebuild without code change, trigger a Railway redeploy.

Rollback

For photo-first recipes, follow the pinned-recipe rollback restrictions before reverting binaries. Retained opted-in pins require compatible code even when the database schema is unchanged and active creation has drained.

Railway → service → deployments → previous green → Redeploy. Sirloin is stateless except for distributed locks (Redis) and inflight HTTP servers, so rollback is safe within the same DB schema. Do not roll back across a DB migration boundary — make verify-migrations enforces unique numbers, but down-migrations are not run automatically. Coordinate with the data owner or accept forward-fix only.

Scaling

• Container memory ceiling: 12 GB (per docker-compose.yml); Railway setting independently. CPU/replica count: TODO(unassigned) Railway plan.
• Horizontal scaling is safe: state lives in Postgres + Redis; locks are distributed (internal/pkg/locks).
• Single-replica concerns: the Chargebee event poller is leader-elected via lock — fine to run multi-replica, only one will poll at a time. Same for the monitor probe (SIRLOIN_MONITOR_ENABLED).
• Watch for noisy retries from httpclient when scaling up — Chargebee rate limits will trigger circuit breaker (ErrServiceUnavailable).

Common Incidents

Use observability for the canonical Axiom queries; this section names the failure mode and points to the right dashboard. All claims here trace to source code under apps/sirloin/internal/app/services/billing/.

Chargebee polling lag

• Symptom: GetCurrentUsage returns stale credits; users report “I paid and didn’t get credit”.
• Detect: Axiom — service.name = "sirloin" AND event_type starts_with "chargebee." over last 30 minutes; expect steady cadence ≤ 15s. Look for gaps in events.poller logs.
• Mitigate:
  1. Confirm leader replica is alive (locks namespace events.poller).
  2. Check Chargebee status; circuit breaker may be open (ErrServiceUnavailable).
  3. Run cmd/scripts/wallet-recovery-collect against affected users.
  4. Worst case: bounce sirloin to force re-election.

Primer webhook backlog

• Symptom: Payment confirmations delayed, dunning retries fail.
• Detect: 401s on /webhooks/primer/payments or 5xx from sirloin. Check Primer dashboard for retry queue depth.
• Mitigate:
  1. Verify SIRLOIN_PRIMER_WEBHOOK_SECRET matches Primer dashboard.
  2. Verify host clock — 3-minute skew window is enforced (primer.WebhookSignedAtWithinSkew).
  3. Replay from Primer dashboard once root cause fixed.
  4. Manual recovery via SubmitPaidInvoice per affected invoice.

Payment saga stuck

See billing for the deep version.

• Symptom: Subscription stuck in future; payment recorded; no activation.
• Detect: query Postgres for subscription.status = 'future' AND last_payment_at > checkout_expiration. Cross-reference Chargebee.
• Mitigate:
  1. Acquire the per-user lock (internal/pkg/locks) via admin tool.
  2. Replay through payments/processor.go (idempotent on transactionID).
  3. If Chargebee believes paid + active, force-reconcile DB.

S3 / R2 upload failures

• Symptom: Character reference upload URLs returning 5xx.
• Detect: error rate on GetCharacterReferenceImageUploadURL; look for s3.PutObject / pre-sign errors in internal/pkg/s3.
• Mitigate: rotate SIRLOIN_S3_* keys if 403; check R2 status; fail open by serving cached URLs only when safe.

Auth verification failures (Clerk)

• Symptom: spike in UNAUTHENTICATED from brisket.
• Detect: log query on clerk.verify errors; internal/pkg/clerk.
• Mitigate: verify SIRLOIN_CLERK_API_KEY; confirm Clerk JWKS reachable; rotate key only with brisket coordination (auth-model).

High DB load

• Symptom: latency jumps across all RPCs.
• Detect: BUN auto-instrumented spans (bunotel); long queries surface in Axiom traces.
• Mitigate: identify hot query; consider read-runtime split via SIRLOIN_DATABASE_RUNTIME_URL; engage Neon support if connection ceiling hit.

Background Jobs

All recurring jobs run on River (FOXY-1038): job state lives in the river Postgres schema on rump, the leader-elected periodic scheduler inserts one job per kind per interval (unique-by-period), and a per-kind advisory lock skips a tick whose predecessor is still running. Task code still lives under internal/app/worker/; inspect and manage jobs via the embedded River UI at /riverui on the webhook server (mounts when SIRLOIN_RIVERUI_USER/PASSWORD are set) or the river.river_job table. A restart no longer loses job state.

refresh_media_popularity is the exception to unique-by-period: it dedupes by job state, so at most one popularity job waits or runs at a time (FOXY-1399). Under period dedupe, its 5-second schedule outran its single worker and queued hundreds of thousands of rows. Those rows keep their period-derived unique keys, so state dedupe does not bound them, and they keep the single worker busy until removed. Every popularity job is identical (empty args), so deleting the queued ones is safe:

DELETE FROM river.river_job
WHERE kind = 'refresh_media_popularity' AND state = 'available';

Run this once, after the state-dedupe release is live. Otherwise the old scheduler refills the queue.

A process killed mid-job leaves its row in running. River’s rescuer discards such a row after 40 minutes (RescueStuckJobsAfter; every periodic kind has MaxAttempts 1). Until then, a stuck popularity row blocks new popularity ticks. River’s JobTimeout (35 minutes) sits above every task’s own budget and must stay finite: the rescuer ignores jobs with a negative timeout. Notable workers:

• chargebeesync — Chargebee event poller (see incident above).
• checkmediageneration — polls round/brain for media completion.
• updatecharacterstatus — moves character state machine forward.
• monitorprobe_scoring_generation — synthetic media probe (gated by SIRLOIN_MONITOR_ENABLED).

Resolved

• Hosting topology: Railway only. The docker-build ECR job in .github/workflows/sirloin.yml:110-:146 is fully commented out; only lint, checks, and test run on push/PR. Railway picks up apps/sirloin/railway.json from the release branch.

TODO(unassigned)

• TODO(unassigned): replica count and HPA settings for prod.
• TODO(unassigned): authoritative migration rollback policy — do we ever run down migrations in prod?