Scanner SBOM Hot Lookup Operations

Status: Active Last updated: 2026-06-05 Sprint: SPRINT_20260210_001_DOCS_sbom_attestation_hot_lookup_contract (HOT-005) Audience: Scanner database operators and on-call engineers.

Purpose

Operate the scanner.artifact_boms monthly partition set that backs Scanner SBOM hot lookups:

Required Inputs

Manual Operations

Pre-create current + next month partition:

PG_DSN="Host=...;Database=...;Username=...;Password=..." \
  ./devops/scripts/scanner-artifact-boms-ensure-partitions.sh 1

Retention dry-run (default keep 12 months):

PG_DSN="Host=...;Database=...;Username=...;Password=..." \
  ./devops/scripts/scanner-artifact-boms-retention.sh 12 true

Retention execution:

PG_DSN="Host=...;Database=...;Username=...;Password=..." \
  ./devops/scripts/scanner-artifact-boms-retention.sh 12 false

Seed Export/Import

The Stella Ops mirror seed archive includes scanner.artifact_boms as a projection table. Use it to carry hot lookup/readiness rows into a clean local or air-gapped install. Do not treat it as a raw SBOM/object-store backup.

Export and validate:

dotnet run --project src\Cli\StellaOps.Cli\StellaOps.Cli.csproj --no-build -- `
  mirror seed export `
  --output-directory "$PWD\tmp\build\mirror\seeds\<timestamp>\stellaops-mirror-seed-v1" `
  --tenant-id default `
  --command-timeout-seconds 0 `
  --skip-vexhub-projection `
  --json

dotnet run --project src\Cli\StellaOps.Cli\StellaOps.Cli.csproj --no-build -- `
  mirror seed validate `
  --seed-directory "$PWD\tmp\build\mirror\seeds\<timestamp>\stellaops-mirror-seed-v1" `
  --tenant-id default `
  --command-timeout-seconds 0 `
  --json

During import, the seed importer stages scanner.artifact_boms, creates any missing historical monthly partitions for archived inserted_at values, and then merges rows by (build_id, inserted_at).

Scheduled Jobs

Cron example

# first day each month: ensure next partition exists
10 0 1 * * PG_DSN="..." /opt/stellaops/devops/scripts/scanner-artifact-boms-ensure-partitions.sh 1

# daily retention check
15 0 * * * PG_DSN="..." /opt/stellaops/devops/scripts/scanner-artifact-boms-retention.sh 12 false

Systemd units

Install:

sudo cp devops/scripts/systemd/scanner-artifact-boms-*.service /etc/systemd/system/
sudo cp devops/scripts/systemd/scanner-artifact-boms-*.timer /etc/systemd/system/
sudo systemctl daemon-reload
sudo systemctl enable --now scanner-artifact-boms-ensure.timer
sudo systemctl enable --now scanner-artifact-boms-retention.timer

/etc/stellaops/scanner-hotlookup.env must define PG_DSN.

Failure Modes and Rollback

Missing upcoming partition

Symptom:

Mitigation:

  1. Run scanner-artifact-boms-ensure-partitions.sh 2.
  2. Re-run failed ingest operations.

Retention job dropped incorrect partition

Symptom:

Rollback:

  1. Restore dropped partition table from latest PostgreSQL backup.
  2. Attach restored table back to parent:
    ALTER TABLE scanner.artifact_boms
ATTACH PARTITION scanner.artifact_boms_YYYY_MM
FOR VALUES FROM ('YYYY-MM-01') TO ('YYYY-MM-01'::date + INTERVAL '1 month');
  3. Rebuild per-partition indexes if restore omitted them.

Hot partition bloat

Symptom:

Mitigation:

  1. Run VACUUM (ANALYZE) scanner.artifact_boms_YYYY_MM;
  2. If needed, run REINDEX TABLE scanner.artifact_boms_YYYY_MM;
  3. For online reclaim workflows, use pg_repack partition-by-partition.

References