CREATE TABLE IF NOT EXISTS {SCHEMA_NAME}.postjobs (

-- identifiers ------------------------------------------------------------------------------------------
-- These are set when enqueueing a workflow
id          BIGSERIAL PRIMARY KEY,                                        -- process id
parent_id   BIGINT REFERENCES {SCHEMA_NAME}.postjobs ON DELETE CASCADE,   -- parent process id
full_id     VARCHAR,                                                      -- full process id
root_id     BIGINT,                                                       -- root process id

-- timestamps -------------------------------------------------------------------------------------------
-- managed automatically
created_at  timestamp NOT NULL DEFAULT (now() at time zone 'utc'),        -- creation timestamp
updated_at  timestamp NOT NULL DEFAULT (now() at time zone 'utc'),        -- update timestamp

-- workflow specification -------------------------------------------------------------------------------
-- Set when enqueueing the workflow, readonly

queue VARCHAR,                                                            -- queue name. (readonly)
workflow VARCHAR NOT NULL,                                                -- e.g. "MyJobModule" (readonly)
workflow_method VARCHAR NOT NULL DEFAULT 'run',                           -- e.g. "run" (readonly)
args JSONB,                                                               -- args
timing_out_at timestamp,                                                  -- job times out after this timestamp
max_attempts INTEGER NOT NULL DEFAULT 1,                                  -- maximum attempts before failing

-- process state ----------------------------------------------------------------------------------------
-- Managed automatically.

-- The workflow version is pinned as soon as the workflow is running for the first time.
-- It is readonly afterwards. Note that the default is not NULL, but '', because this allows
-- to query via (workflow, workflow_version) IN (('Foo', ''), ('Foo', '1.0'))
workflow_version VARCHAR NOT NULL DEFAULT '',                             -- e.g. "1.0"

-- The workflow status, one of  'ready', 'sleep', 'failed', 'err', 'timeout', 'ok'
status {SCHEMA_NAME}.statuses DEFAULT 'ready',

-- Timestamp when consider running this the next time.
next_run_at timestamp DEFAULT (now() at time zone 'utc'),

-- Number of failed attempts so far.
failed_attempts INTEGER NOT NULL DEFAULT 0,

-- last_worker_session_id UUID NOT NULL REFERENCES {SCHEMA_NAME}.worker_sessions ON DELETE CASCADE,

-- process result ---------------------------------------------------------------------------------------

results JSONB,            -- The process result, if any. Only valid when status == 'ok'
error VARCHAR,            -- The last error; usually set to the klass of the error.
error_message VARCHAR,    -- The human readable error message, for displaying purposes
error_backtrace JSONB,    -- additional error information, for debugging purposes

-- custom fields ----------------------------------------------------------------------------------------
-- workflow_status VARCHAR,
tags JSONB

-- processing_client information ------------------------------------------------------------------------
-- This information is passed along from workers during processing. They are only valid
-- when status == 'processing'
--
-- Initially these columns didn't exist, and have been created via another migration
-- (003b_processing_columns.sql). They are listed here for documentation purposes.
-- processing_client varchar,               -- host:port of client (taken from pg_stat_activity)
-- processing_client_identifier varchar,    -- free text info, set via set_client_identifier()
-- processing_started_at timestamp          -- when did processing start?
-- processing_max_duration float            -- maximum expected duration of processing. Afterwards the
                                            -- processing is considered failed for unknown reasons, and
                                            -- potentially restarted.

-- process information ------------------------------------------------------------------------
--
-- Initially these columns didn't exist, and have been created via another migration.

-- last_worker_session_id uuid NOT NULL,      -- session_id of last worker that touched this job
-- cron_interval integer,                     -- for cron jobs: cron_interval in seconds
-- is_sticky boolean DEFAULT false NOT NULL,  -- when set this job is supposed to be sticky
-- sticky_host_id uuid                        -- once a sticky job starts running the job's host id
-- is_greedy boolean DEFAULT false NOT NULL   -- is this a greedy job? (i.e. sticky, and do not allow any other job in this worker.)

);

– [TODO] check indices CREATE INDEX IF NOT EXISTS postjobs_tags_idx

ON {SCHEMA_NAME}.postjobs USING GIN (tags jsonb_path_ops);

CREATE INDEX IF NOT EXISTS postjobs_parent_id_idx

ON {SCHEMA_NAME}.postjobs(parent_id);