SQLite Cron Receipt Store Grows Unboundedly When Unique Job IDs Are Deleted or Churned

Summary

The cron receipt pruning logic in run-receipt-store.ts caps terminal receipt rows at 64 per (store_key, job_id) pair, but that pruning runs only in the context of the same live job. When a job is deleted, its terminal receipts are never revisited by any subsequent maintenance pass, causing the SQLite store to retain at least one terminal receipt row indefinitely for every distinct historical job ID. The result is Θ(N) store growth proportional to the total number of unique job IDs ever created and deleted, which means the per-job bound provides no meaningful upper bound on aggregate storage.

Root-Cause Analysis

Confirmed Evidence

• The reproduction harness created, ran, and deleted 80 unique jobs. After deletion, cronJobsRemaining was 0, yet terminalReceiptsRemaining was 80 with distinctDeletedJobIdsRetained equal to 80. This confirms a 1-to-1 retention of one terminal receipt row per historically unique job ID.
• Current main (commit a4ae33990374642864b7d5a5f6930f935a514453) contains per-job-only pruning in run-receipt-store.ts. Pruning is triggered only during claim or finalization of the same job, meaning deleted-job IDs never trigger their own pruning path again.
• The schema intentionally omits a foreign key from receipts to jobs, allowing receipt rows to survive job deletion for audit purposes. This design decision is correct but is a necessary precondition for the bug: without a FK cascade, no automatic cleanup occurs on job deletion.

Confirmed Impact

• Every distinct historical cron job ID contributes persistently retained rows containing receipt IDs, owner metadata, configuration revisions, and error text.
• The store grows without bound as jobs are churned (created, run, deleted, re-created under new IDs), defeating the documented per-job cap.

Reasonable Inference

• All deletion paths—explicit job removal, agent-family removal, and deleteAfterRun—leave terminal receipts orphaned outside any future pruning scope, because each path removes the job record but does not remove or schedule removal of its associated receipts.
• No global or age-based maintenance sweep currently exists. Without such a sweep, no code path will ever reclaim receipts for a deleted job ID.
• The missing index on (status, finished_time) or equivalent is inferred (not confirmed from schema evidence) as a likely prerequisite for an efficient global retention sweep.

Assumptions

• "Terminal" receipt rows are rows in a status such as completed, failed, or cancelled — i.e., rows that will never transition to a running state again.
• The 64-row cap is enforced by a DELETE … WHERE rowid NOT IN (SELECT rowid … ORDER BY … LIMIT 64) or equivalent query scoped to (store_key, job_id).

Alternative Causes Considered

A memory leak or connection-pool issue causing rows to appear retained could mimic these symptoms, but the harness explicitly reports cronJobsRemaining: 0, which confirms the jobs themselves are deleted and the receipts are genuinely orphaned rows in the store, not a reporting artifact.

Resolution Steps

1. Do not remove the FK omission. The schema design allowing receipts to survive job deletion is intentional and correct for post-delete audit.

1. Introduce a global terminal-receipt retention owner in run-receipt-store.ts (or a dedicated maintenance module) that:

• Queries terminal receipt rows grouped by (store_key, job_id).
• Applies a configurable global cap on the number of distinct deleted job IDs whose receipts are retained (e.g., retain receipts for the most recent N distinct deleted job IDs per store_key).
• Applies an age-based TTL (e.g., purge terminal receipts for deleted jobs older than a configurable horizon, such as 30 days).
• Never touches receipts whose status is non-terminal (i.e., running, pending, claimed). Add an explicit WHERE status IN ('completed', 'failed', 'cancelled') — or the equivalent terminal-state set — to every global sweep query.

1. Trigger the global sweep at a bounded cadence, not on every claim/finalize event. Acceptable trigger points include:

• A periodic maintenance tick (e.g., once every N claim operations, or on a wall-clock interval).
• A dedicated background maintenance task scheduled independently of job lifecycle events.

1. Add a covering index on the receipt table for the fields used by the global sweep (e.g., status, finished_at or equivalent timestamp, store_key) to prevent the sweep from becoming a full-table scan as the store grows.

1. Coordinate with maintainers on the specific retention horizon (count and age) and on whether a schema migration is needed for the new index. These are product decisions flagged in the issue labels.

1. Write regression tests covering:

• Global cap: assert that after creating and deleting more than the global-cap count of distinct jobs, terminal receipt count remains at or below the cap.
• Recent audit preservation: assert that receipts for recently deleted jobs (within the TTL) are retained.
• Active receipt immunity: assert that receipts in a non-terminal state are never deleted by the global sweep.
• All deletion paths: explicit deletion, agent-family removal, deleteAfterRun.
• Existing per-current-job semantics: the 64-row per-(store_key, job_id) cap continues to apply to live jobs.

CLI Commands

Identify the current terminal receipt count and distinct deleted job ID count directly in the SQLite store for diagnosis:

sqlite3 <PATH_TO_STORE_DB> \
  "SELECT COUNT(*) AS total_terminal_receipts,
          COUNT(DISTINCT job_id) AS distinct_job_ids
   FROM cron_receipts
   WHERE status IN ('completed', 'failed', 'cancelled');"

Identify which job IDs have orphaned receipts (no corresponding row in the jobs table):

sqlite3 <PATH_TO_STORE_DB> \
  "SELECT r.job_id, COUNT(*) AS receipt_count
   FROM cron_receipts r
   LEFT JOIN cron_jobs j ON r.job_id = j.id
   WHERE j.id IS NULL
     AND r.status IN ('completed', 'failed', 'cancelled')
   GROUP BY r.job_id
   ORDER BY receipt_count DESC
   LIMIT 100;"

> Warning: The following command deletes data. Run it only after confirming the affected rows, validating the retention policy with maintainers, and taking a backup.

# Backup first
cp <PATH_TO_STORE_DB> <PATH_TO_STORE_DB>.bak

# Remove orphaned terminal receipts older than <RETENTION_DAYS> days
sqlite3 <PATH_TO_STORE_DB> \
  "DELETE FROM cron_receipts
   WHERE status IN ('completed', 'failed', 'cancelled')
     AND job_id NOT IN (SELECT id FROM cron_jobs)
     AND finished_at < datetime('now', '-<RETENTION_DAYS> days');"

Replace <PATH_TO_STORE_DB>, <RETENTION_DAYS>, finished_at, cron_receipts, and cron_jobs with the actual table and column names from the schema.

Configuration Snippets

Example index migration (SQLite syntax) to support efficient global sweep queries:

-- Add only after confirming column names against the live schema
CREATE INDEX IF NOT EXISTS idx_cron_receipts_status_finished
  ON cron_receipts (store_key, status, finished_at);

Pseudocode sketch of the global sweep logic (adapt to the actual ORM/query layer):

// run-receipt-store.ts — global maintenance sweep (pseudocode)
async function pruneGlobalTerminalReceipts(
  db: Database,
  storeKey: string,
  options: {
    maxDistinctDeletedJobIds: number; // e.g. 1000
    retentionHorizonDays: number;     // e.g. 30
  }
): Promise<void> {
  const { maxDistinctDeletedJobIds, retentionHorizonDays } = options;

  // Age-based: remove terminal receipts for deleted jobs beyond the TTL
  await db.run(
    `DELETE FROM cron_receipts
     WHERE store_key = ?
       AND status IN ('completed', 'failed', 'cancelled')
       AND job_id NOT IN (SELECT id FROM cron_jobs WHERE store_key = ?)
       AND finished_at < datetime('now', ? || ' days')`,
    [storeKey, storeKey, `-${retentionHorizonDays}`]
  );

  // Count-based: keep only the most recent N distinct deleted job IDs
  await db.run(
    `DELETE FROM cron_receipts
     WHERE store_key = ?
       AND status IN ('completed', 'failed', 'cancelled')
       AND job_id NOT IN (
         SELECT id FROM cron_jobs WHERE store_key = ?
       )
       AND job_id NOT IN (
         SELECT DISTINCT job_id
         FROM cron_receipts
         WHERE store_key = ?
           AND status IN ('completed', 'failed', 'cancelled')
           AND job_id NOT IN (SELECT id FROM cron_jobs WHERE store_key = ?)
         ORDER BY MAX(finished_at) DESC
         LIMIT ?
       )`,
    [storeKey, storeKey, storeKey, storeKey, maxDistinctDeletedJobIds]
  );
}

> This is illustrative pseudocode. Exact SQL must be validated against the live schema, tested for correctness, and reviewed for query plan efficiency before deployment.

Verification

After implementing the global sweep, run the reproduction harness with a parameter set that exceeds the configured global cap:

# Example: create, run, delete 200 unique jobs against a store configured
# with maxDistinctDeletedJobIds = 100
<HARNESS_COMMAND> --jobs 200 --delete-after-run

Expected healthy output (values reflect configured caps):

{
  "insertedDeletedJobs": 200,
  "cronJobsRemaining": 0,
  "terminalReceiptsRemaining": "<= configured cap",
  "distinctDeletedJobIdsRetained": "<= configured cap",
  "maxReceiptsPerJob": "<= 64"
}

Verify no running receipts were deleted:

sqlite3 <PATH_TO_STORE_DB> \
  "SELECT COUNT(*) FROM cron_receipts WHERE status NOT IN ('completed', 'failed', 'cancelled');"
# Expected: 0 deletions during sweep (count should match pre-sweep active count)

Verify the index is present and used:

sqlite3 <PATH_TO_STORE_DB> \
  "EXPLAIN QUERY PLAN
   SELECT job_id FROM cron_receipts
   WHERE store_key = 'test' AND status = 'completed'
   ORDER BY finished_at DESC;"
# Expected: query plan should reference idx_cron_receipts_status_finished

Rollback indicator: If terminalReceiptsRemaining continues to equal insertedDeletedJobs after the sweep runs, the global sweep is not being triggered or its WHERE clause is not matching the correct status values. Revert any schema migration and re-examine status enum values against the live schema.

Prevention

1. Regression test in CI: Add an automated integration test that asserts terminalReceiptsRemaining ≤ globalCap after churning more than globalCap distinct job IDs. Run it on every PR that touches run-receipt-store.ts or related migration files.

1. Store size monitoring: Emit a metric (counter or gauge) for the total row count of the receipts table and the count of distinct deleted-job IDs with retained receipts. Alert when either exceeds a configurable threshold (e.g., 10× the configured global cap).

1. Sweep cadence enforcement: Make maxDistinctDeletedJobIds and retentionHorizonDays required configuration fields with no defaults that silently allow unbounded growth. Fail fast at startup if they are not set.

1. Schema migration review gate: Require maintainer sign-off on any schema change to the receipts table that could affect pruning scope, enforced via a CODEOWNERS rule on migration files.

1. Document the retention model: Add a code comment in run-receipt-store.ts explicitly stating that the per-job cap and the global cap are two separate mechanisms, and that both must be functional for aggregate storage to be bounded. This prevents future contributors from inadvertently removing one while believing the other is sufficient.

1. Load-test the sweep query: Before deploying the index and sweep to production, benchmark the sweep query against a database seeded with the expected maximum historical receipt count to confirm the query plan is efficient and sweep latency is acceptable within the chosen trigger cadence.