Historical PostgreSQL runtime heartbeat retention: schema 11

Start here when adding automatic cleanup of old runtime_heartbeat identities to an existing Durable PostgreSQL provider. The table contains one row per configured worker ID. A stopped process cannot remove its row, and deployments that create new IDs can accumulate stale rows. The provider keeps 24 hours of heartbeat history by default, then removes eligible rows in bounded batches. Cleanup manages capacity; it does not determine Work correctness or readiness.

This page preserves the historical schema-11 rollout and preflight proof. The current role recipe requires schema 12 and must not be run against schema 11 or older; use the recipe from the matching historical package for those stores. For current execution-policy compatibility and migration order, use the canonical policy guide and migration checklist.

The local PostgreSQL proof demonstrates one 500-row deletion, current-row survival, and a healthy Work pass on a disposable database. The schema CLI handles explicit migration and complete-manifest preflight. Application startup never applies DDL.

Configure the provider

AddAppSurfaceDurablePostgreSql owns the validated settings. The provider's package pump signals maintenance immediately after its first admitted pass commits the current heartbeat. It does not wait for pruning before executing Work. Later maintenance continues without more passes until the worker host stops or the provider is disposed. A service that never admits a pass does not begin maintenance. Replacing the package pump with a custom IDurableRuntimePump bypasses this trigger; custom-pump owners must schedule cleanup deliberately or use the package pump.

services.AddAppSurfaceDurablePostgreSql(
    dispatcherDataSource,
    runtimeDataSource,
    workOptions,
    scheduleOptions,
    options =>
    {
        options.WorkerId = "worker-1";
        options.EnableHeartbeatMaintenance = true; // default
        options.HeartbeatRetention = TimeSpan.FromHours(24); // default
        options.HeartbeatMaintenanceCadence = TimeSpan.FromHours(24); // default
        options.HeartbeatPruneBatchSize = 500; // default
    });

Set EnableHeartbeatMaintenance = false in a new provider registration to pause cleanup during an incident; passes and heartbeat admission continue. Re-enable it in a later registration to resume. These are startup options, not a live toggle. Retention accepts 24 hours through 3,650 days and must exceed HeartbeatStaleAfter. Cadence accepts one hour through 30 days; batch size accepts 1 through 5,000. Keep the 24-hour default when older identities have little diagnostic value; increase it only when your incident workflow needs a longer lookback and capacity evidence supports it. PostgreSQL time defines the cutoff, so host clock drift does not change eligibility.

The first due call begins at admission. A full batch schedules another call no sooner than one minute after the previous start; a partial or zero result returns to the configured cadence. Failures retry no sooner than one hour. At the default cap, a sustained backlog can clear at most 500 rows per minute, or 720,000 per day; 100,000 rows require about 200 calls, starting from minute 0 through minute 199 plus SQL execution time. Database outages and churn above that rate extend catch-up. Observe actual churn, deletion rate, pool pressure, and renewal latency before changing the batch size.

The function uses last_heartbeat_at < cutoff, an ordered index, FOR UPDATE SKIP LOCKED, a cluster-wide nonblocking advisory lock, and an exact worker ID/instance exclusion. pass_active is ignored because abrupt process loss can leave it true. Renewal that locks first is skipped. Pruning that locks first may make a writer wait; the old row may be deleted, and takeover must retry or re-register. Prune calls have a short server and client time budget; the release proof must keep renewal p95 below five seconds and maximum below the default 15-second stale bound. PostgreSQL DELETE creates dead tuples and does not immediately shrink files. Observe live/dead tuple estimates, relation and index size, autovacuum, and sustained rate during rollout.

Deploy schema 11

This is a downtime migration. 0011_runtime_heartbeat_retention.sql creates a normal transactional index, which can lock the heartbeat table. It cannot use CREATE INDEX CONCURRENTLY inside the package's checksum-bound transaction. The migration's five-second lock_timeout limits acquisition of the table lock, not the full index-build duration. The package migration advisory lock has a separate 30-second acquisition deadline with 75–125 ms jittered attempts. CLI apply allows 45 minutes for the overall operation, including earlier pending migrations, status and metadata commands, transaction boundaries, and cleanup; migrations 0010 and 0011 each have a 330-second command deadline. Measure build duration on a representative table before choosing CLI apply or a reviewed generated script executed under the same package lock.

1. Pack and stage the schema-11-capable package while the old deployment remains active. Pin the immediately previous published PostgreSQL provider artifact, its SHA-256, version, and source commit. Run its actual Work and health path on schema 11 in the release lane. The current proof passes with 0.2.0-preview.8 on schema 11; verify that it is still the immediately previous artifact at publication time.
2. Drain and stop old runtimes and writers. Inspect status and generate the reviewed migration script. A pre-migration preflight failure for pending 0011 is an expected downtime finding, not a passing activation gate.
3. Apply 0011 with the migration-owner credential, then run the schema-11 release's matching package recipe, extracted from that release's contentFiles/any/any/configure-postgresql-roles.sql, with the complete reviewed version-1 role_pairs_json manifest. See the current role-recipe compatibility contract for the present schema-12 floor. The historical recipe transfers function/schema ownership, revokes other execute grants, grants the exact prune signature to every manifest runtime and no dispatcher, and installs the migration-owner heartbeat policy needed for the SECURITY DEFINER function under forced row level security. Do not run the current recipe on schema 11 or older: it requires schema 12. Use only the matching historical package recipe for this preserved schema-11 procedure.
4. Confirm the complete reviewed manifest and independently reviewed migration-owner role, then run preflight once for every distinct runtime credential using the same unchanged manifest and owner. Both flags are required even for one pair. Each pass must identify the current session_user/current_user as that pair's runtime; an owner-diagnostic pass is useful for diagnosis but never counts as runtime evidence. See the complete preflight contract and staged proof checklist.
5. Before activation, keep one dedicated, nonpooled migration-owner guard session holding the package shared migration/recipe/epoch fence from before the first runtime pass through every pass, both lane proofs, and the activation callback. Each preflight separately uses a dedicated nonpooled session-affine connection and its own consistent read-only snapshot. Unsupported transaction/statement poolers cannot provide the required session affinity. The complete combined gate also requires matching StoreId and nonempty active runtime epoch. Any guard loss, cancellation, failure, partial coverage, identity mismatch, or incomplete cleanup invalidates every result; release the guard, repair through reviewed procedure, then rerun the entire gate. Individual receipts never authorize a later activation.
6. Record the actual measurements collected by the release/deployment proof. The 2–5 minute manual documentation walkthrough target below is an acceptance target, not a measured result. Do not copy illustrative scale figures or invent owner names, timings, or release evidence.

# Connection values live in environment variables; do not put them in command arguments.
appsurface durable schema status --connection-env APPSURFACE_DURABLE_MIGRATION_CONNECTION
appsurface durable schema script --from-version 10 --output /tmp/appsurface-schema-0011.sql
# Review the generated file and schedule the downtime window.
appsurface durable schema apply --connection-env APPSURFACE_DURABLE_MIGRATION_CONNECTION --apply
psql -v ON_ERROR_STOP=1 \
  -v migration_owner_role=appsurface_durable_owner \
  -v role_pairs_json="$(< reviewed-role-pairs.json)" \
  -v retention_operator_role=appsurface_durable_retention \
  -f <released-provider-package>/contentFiles/any/any/configure-postgresql-roles.sql \
  "$APPSURFACE_DURABLE_ROLE_RECIPE_CONNECTION"
appsurface durable schema status --connection-env APPSURFACE_DURABLE_RUNTIME_CONNECTION
appsurface durable schema preflight \
  --role-pairs-file ./reviewed-role-pairs.json \
  --migration-owner-role "$APPSURFACE_DURABLE_MIGRATION_OWNER_ROLE" \
  --connection-env APPSURFACE_DURABLE_RUNTIME_CONNECTION

If your deployment runs appsurface durable schema preflight before apply, expect a nonzero pending-0011 downtime diagnostic and stop the old workers before applying. Investigate any other preflight failure; do not mask its exit code. APPSURFACE_DURABLE_ROLE_RECIPE_CONNECTION must name a principal able to transfer schema ownership and reconcile grants, as described in the role recipe.

The CLI apply path is appropriate only if the measured 0011 index build fits its 330-second migration-command deadline and the full operation, including any earlier pending migrations, fits 45 minutes. For a larger table, use the reviewed generated script through a client with a deliberately sized operation deadline and ON_ERROR_STOP=1; keep the script's package advisory lock and transaction wrapper intact. Do not execute the embedded migration fragment directly. Do not run apply and the generated script concurrently. The same SQL migration must be recorded once in schema_migration.

Complete runtime-set preflight and proof checklist

Preflight is a read-only point-in-time check for the AppSurface schema-11 role, policy, ACL, and credential contract. It complements migration-history compatibility; it does not reconcile drift, bootstrap or rotate StoreId/epoch, audit unrelated database objects, prove arbitrary function-body safety, or guarantee against a privileged catalog change after its snapshot. The exact checked predicates are documented in the CLI reference. The canonical role recipe may refuse observed drift; do not promise it repairs every failure.

Use the same immutable reviewed version-1 manifest file for recipe reconciliation and every preflight. The CLI requires both --role-pairs-file <path> and --migration-owner-role <name> for one or many pairs. It reads the manifest once before database access and hashes the exact bytes. Input must be strict UTF-8 without BOM, at most 64 KiB and nesting depth 8, with exactly version: 1 and pairs, 1–32 entries, and exactly dispatcher, runtime, and dispatcher_profile per pair. Profiles are explicit full or work_only. Duplicate decoded JSON properties, comments, trailing commas, wrong types, unknown/missing properties, empty/control-containing names, duplicate role names across either role kind, and names over 63 UTF-8 bytes fail closed. Names are preserved exactly: no trim, normalization, case-folding, SQL interpolation, or truncation. Database resolution must find each exact name once and prove distinct OIDs. The separately supplied owner follows the same name rules and must resolve exactly once, with no overlap with any manifest role. The manifest hash establishes byte identity only, not reviewer identity or custody.

The current CLI invocation is separate from the long-lived deployment guard. Each preflight owns one dedicated nonpooled physical connection, disables ambient enlistment and multiplexing, and requires a direct/session-affine endpoint. It acquires the package shared migration/recipe/epoch fence before beginning one RepeatableRead snapshot, makes the transaction read-only before catalog queries, completes the transaction, verifies explicit unlock, and disposes the connection before success. Transaction- or statement-pooling endpoints are unsupported because they cannot guarantee backend/session affinity. The total online deadline is 30 seconds: at most 28 seconds for connection, fence, and work, with up to 2 seconds reserved for bounded cleanup. Cancellation, timeout, broken transport, uncertain unlock, cleanup failure, or unknown result is nonzero and emits no success. Do not enlarge connection-string timeouts to bypass the bound. A single-slot runtime pool must not trigger nested acquisition. The deployment guard and each runtime command use separate connections; do not try to share a one-slot runtime pool with the guard.

The exact checks compare complete raw sets before joins can discard unexpected entries: every runtime is a restricted LOGIN leaf with no forbidden attributes, membership edges, or database/package-object ownership; runtime policy targets and the two heartbeat policies match exactly; RLS is enabled and forced; the independent owner matches schema/table/function ownership and the owner-policy/ACL principal; every expected runtime has pruning and due-health EXECUTE without grant option, and no grantee outside owner plus expected runtimes exists (including PUBLIC/OID zero, dispatchers, retention-only roles, grant-option entries, or unreviewed principals); every runtime lacks effective heartbeat DELETE/TRUNCATE; and function signatures, result kind, SECURITY DEFINER, search path, and the valid ready nonunique ascending retention index match the contract. Related #823 runtime policy targets are checked for set consistency. These checks do not claim lane-specific PostgreSQL row isolation, authorize from observed catalog state, prove arbitrary function bodies, or audit unrelated objects.

status preserves StoreId and a nullable active runtime epoch. Structural preflight reports those observed values but does not initialize or rotate the epoch. The combined deployment receipt requires the observed StoreId and nonempty epoch to match reviewed deployment identity across all passes and both lane proofs. The current caller is classified only after resolving both session_user and current_user to the same OID: a manifest runtime result is distinct from owner-diagnostic; the latter can help inspect catalog state but can never satisfy runtime coverage. Repeating a credential does not satisfy a second pair.

Candidate and published package proof must cover all four scenarios against actual matching CLI/provider package bytes: (1) one reviewed pair; (2) second-pair enrollment while forwarding Work/Flow/Schedule and Source Work-only boundaries remain proved; (3) identical complete-manifest rerun; and (4) schema 10 with two already-installed pairs captured before the forward upgrade to schema 11, then reconciled and re-proved. Local source or integration tests are useful development evidence, but are not candidate/public package proof. Preserve existing branch, mixed-version, exact-byte, clean-root, lane, cleanup, and nonmutation requirements.

For the manual documentation acceptance run, target 2–5 minutes from the documented prepared-operator start through reading the guide, exact-version install, both runtime commands, and correct interpretation of the still-closed gates. This is a target only; no successful observation or duration is claimed here. Record manual observation separately from cold disposable setup, commands, CI, and candidate/public proof durations. No unsupported capacity or timing measurements belong in the receipt.

Recover and diagnose

Schema migrations are forward-only. If the five-second table-lock wait or the package advisory-lock acquisition times out, keep activation closed, inspect the blocker without printing another session's SQL or credentials, and retry the same reviewed forward operation in a maintenance window. The current exact 0.2.0-preview.8 binary has passed the schema-11 Work/health test, so a failed new-package deployment may redeploy that same pinned binary after the release lane repeats the proof. If the immediately previous binary changes or its proof fails before publication, set migration 0011's minimum reader and writer versions to 11, keep activation closed after commit, and roll forward. Never drop the index/function, lower the schema version, or infer compatibility for arbitrary older binaries.

The runtime role is deliberately trusted for the unscoped heartbeat table. The SECURITY DEFINER function permits repeated bounded deletion of eligible history using caller-supplied exclusion arguments. Forced row level security remains enabled: the runtime policy covers direct heartbeat admission and renewal, while a separate exact migration-owner policy lets the function owner see and delete eligible rows. Those arguments are a self-pruning guard, not authentication. A compromised runtime credential can remove all old diagnostic rows through repeated calls; isolate it from request-controlled code. Runtime must not own the schema, inherit another role, hold grant option, or have direct DELETE/TRUNCATE. The role recipe and post-migration preflight are the grant boundary. A dedicated maintenance credential is a separate design if an adopter cannot accept this trust model.

Maintenance emits appsurface.durable.runtime_heartbeat.prune.attempts, .deleted, and .failures on the ForgeTrust.AppSurface meter. Stable outcomes are deleted, zero_or_contended, failed, and cancelled; zero does not distinguish no eligible rows from an advisory-lock refusal. disabled is configuration state, not an SQL attempt. Logs and metrics must not use worker IDs, instance IDs, connection names, exception text, or credentials as attributes. Repeated failures do not change readiness by themselves. Check connection reachability, the exact function grant, and schema preflight; then retry after repair or pause maintenance through a new provider registration. The Durable diagnostics catalog maps these findings to safe actions.