Data Model

All Snowpack state lives in a single Postgres database. This page documents every table, its columns, and the key indexes that support the hot-path queries.

jobs

The primary record for each maintenance request. One row per job.

Column	Type	Constraints	Description
job_id	TEXT	PK	Unique job identifier
database_name	TEXT	NOT NULL	Iceberg database name
table_name	TEXT	NOT NULL	Iceberg table name
actions	TEXT[]	NOT NULL	Ordered list of maintenance actions
dry_run	BOOL	NOT NULL	Whether to simulate without writing
status	TEXT	NOT NULL	One of: pending, running, completed, failed, cancelled
submitted_at	TIMESTAMPTZ	NOT NULL	When the job was submitted
started_at	TIMESTAMPTZ		When the worker began execution
completed_at	TIMESTAMPTZ		When the job reached terminal state
error	TEXT		Error message if failed
attempt_id	TEXT		DL-197 fence — current claim’s unique ID
deleted_at	TIMESTAMPTZ		Soft-delete tombstone
created_at	TIMESTAMPTZ	DEFAULT NOW()	Row creation timestamp

job_queue

Transient work queue. Rows exist only while a job is in-flight (pending or running). Deleted on ack.

Column	Type	Constraints	Description
job_id	TEXT	PK, FK -> jobs	References the parent job
enqueued_at	TIMESTAMPTZ	DEFAULT NOW()	When the job entered the queue
visible_at	TIMESTAMPTZ	NOT NULL	Claim lease deadline — job becomes re-claimable after this time
claimed_at	TIMESTAMPTZ		When a worker claimed the job
claimed_by	TEXT		Worker hostname
attempt_id	TEXT		Fence echo — must match jobs.attempt_id
attempt_count	INT	DEFAULT 0	Number of claim attempts

job_actions

Per-action results within a job. One row per action execution.

Column	Type	Constraints	Description
job_id	TEXT	FK -> jobs	Parent job
action	TEXT		Action name (e.g., rewrite_data_files, expire_snapshots)
success	BOOL		Whether the action succeeded
error	TEXT		Error message if the action failed
message	TEXT		Human-readable result summary
elapsed_seconds	REAL		Wall-clock duration of the action
created_at	TIMESTAMPTZ		Row creation timestamp

Primary key: (job_id, action, created_at)

table_locks

Per-table ownership lock. Prevents concurrent maintenance on the same table.

Column	Type	Constraints	Description
table_key	TEXT	PK	"{database}.{table}" composite key
holder	TEXT	NOT NULL	job_id of the lock owner
acquired_at	TIMESTAMPTZ	DEFAULT NOW()	When the lock was acquired
expires_at	TIMESTAMPTZ	NOT NULL	Lock expiration deadline

table_cache_entries

Cached table inventory from the catalog. Refreshed atomically by TableCacheSyncWorker.

Column	Type	Constraints	Description
database_name	TEXT		Iceberg database name
table_name	TEXT		Iceberg table name
maintenance_enabled	BOOL	nullable	Whether maintenance is enabled for this table
payload	JSONB		Full table metadata payload

Primary key: (database_name, table_name)

table_cache_meta

Single-row metadata about the table cache sync state.

Column	Type	Constraints	Description
id	INT	PK, CHECK (id = 1)	Single-row invariant
last_synced_at	TIMESTAMPTZ		Last successful sync timestamp

health_snapshots

Precomputed health snapshots written by the health sync CronJob. Historical, not on the hot path.

orchestrator_runs

Audit log of orchestrator CronJob executions. Historical, not on the hot path.

Key indexes

Index	Definition	Purpose
idx_jobs_visible	Partial index WHERE deleted_at IS NULL	Supports the live-read joined view — excludes soft-deleted rows so the API’s job listing query only scans active jobs
idx_job_queue_ready	Composite on (claimed_at, visible_at)	Serves both KEDA’s scaler query (SELECT COUNT(*) ... WHERE claimed_at IS NULL AND visible_at <= NOW()) and the worker’s dequeue_with_claim CTE