Engine DB schema

Every table and column of the Engine DB, generated from the PostgreSQL schema etl-craft creates; the SQLite schema has the same tables and columns, in the same order, with SQLite's types. Your team writes pipeline metadata in the CFG_ tables; etl-craft manages API tokens, the AUD_ tables and SCHEMA_MIGRATIONS. See Pipelines and tasks and The Engine DB.

CFG_PIPELINES

One row per pipeline: its code, schedule, SLA, refresh type and generated-DAG settings.

Column	Type	Null	Default	Allowed values
PIPELINE_ID	BIGINT	no
PIPELINE_CODE	VARCHAR	no
PIPELINE_NAME	VARCHAR	no
DESCRIPTION	VARCHAR	yes
RUN_SCHEDULE	VARCHAR	yes
SLA_IN_HOURS	NUMERIC	yes
REFRESH_TYPE	VARCHAR	no		FULL, INCREMENTAL
ACTIVE_FLAG	VARCHAR	no	'Y'	Y, N
PIPELINE_PARAMETERS	JSONB	yes
CREATED_BY	VARCHAR	yes
CREATE_DATE	TIMESTAMPTZ	yes
UPDATED_BY	VARCHAR	yes
UPDATED_DATE	TIMESTAMPTZ	yes
SCHEDULE_TIMEZONE	VARCHAR	yes
CATCHUP	VARCHAR(1)	no	'N'	Y, N
MAX_CATCHUP_RUNS	INT	no	1
OVERLAP_POLICY	VARCHAR	no	'SKIP'	SKIP, QUEUE
SCHEDULE_START_DATE	DATE	yes

Unique: PIPELINE_CODE where ACTIVE_FLAG = 'Y'.

CFG_PIPELINE_DEPENDENCY

A pipeline waiting on another: a new run starts only when the upstream's last finished run satisfies DEPENDENCY_TYPE.

Column	Type	Null	Default	Allowed values
PIPELINE_DEPENDENCY_ID	BIGINT	no
PIPELINE_ID	BIGINT	no
DEPENDS_ON_PIPELINE_ID	BIGINT	no
DEPENDENCY_TYPE	VARCHAR	no		SUCCESS, FAILURE, ALWAYS, HAS_DATA
ACTIVE_FLAG	VARCHAR	no	'Y'	Y, N
CREATED_BY	VARCHAR	yes
CREATE_DATE	TIMESTAMPTZ	yes
UPDATED_BY	VARCHAR	yes
UPDATED_DATE	TIMESTAMPTZ	yes
CONSUME_REPAIRS	VARCHAR(1)	no	'Y'	Y, N

Unique: PIPELINE_ID, DEPENDS_ON_PIPELINE_ID, DEPENDENCY_TYPE where ACTIVE_FLAG = 'Y'.

CFG_TASKS

One row per task: its pipeline, handler and run condition.

Column	Type	Null	Default	Allowed values
TASK_ID	BIGINT	no
TASK_CODE	VARCHAR	no
TASK_TYPE	VARCHAR	no		INGESTION, ETL
PIPELINE_ID	BIGINT	no
HANDLER	VARCHAR	no		PYTHON, SQL, BUSINESS_RULES, EMAIL_ALERT
RUN_CONDITION	VARCHAR	yes
RUN_CONDITION_COUNT	INT	yes
ACTIVE_FLAG	VARCHAR	no	'Y'	Y, N
CREATED_BY	VARCHAR	yes
CREATE_DATE	TIMESTAMPTZ	yes
UPDATED_BY	VARCHAR	yes
UPDATED_DATE	TIMESTAMPTZ	yes

Unique: PIPELINE_ID, TASK_CODE where ACTIVE_FLAG = 'Y'.

CFG_TASK_DEPENDENCY

A task waiting on another, in its own pipeline or, with DEPENDS_ON_PIPELINE_ID, in another.

Column	Type	Null	Default	Allowed values
TASK_DEPENDENCY_ID	BIGINT	no
PIPELINE_ID	BIGINT	no
TASK_ID	BIGINT	no
DEPENDS_ON_PIPELINE_ID	BIGINT	yes
DEPENDS_ON_TASK_ID	BIGINT	no
DEPENDENCY_TYPE	VARCHAR	no		SUCCESS, FAILURE, ALWAYS, HAS_DATA
ACTIVE_FLAG	VARCHAR	no	'Y'	Y, N
CREATED_BY	VARCHAR	yes
CREATE_DATE	TIMESTAMPTZ	yes
UPDATED_BY	VARCHAR	yes
UPDATED_DATE	TIMESTAMPTZ	yes
CONSUME_REPAIRS	VARCHAR(1)	no	'Y'	Y, N

Unique: TASK_ID, DEPENDS_ON_PIPELINE_ID, DEPENDS_ON_TASK_ID, DEPENDENCY_TYPE where ACTIVE_FLAG = 'Y'.

CFG_TASK_PARAMETERS

A task's settings, as name and value rows; each handler reads its own names.

Column	Type	Null	Default	Allowed values
TASK_PARAMETER_ID	BIGINT	no
TASK_ID	BIGINT	no
PARAMETER_NAME	VARCHAR	no
PARAMETER_VALUE	VARCHAR	yes
ACTIVE_FLAG	VARCHAR	no	'Y'	Y, N
CREATED_BY	VARCHAR	yes
CREATE_DATE	TIMESTAMPTZ	yes
UPDATED_BY	VARCHAR	yes
UPDATED_DATE	TIMESTAMPTZ	yes

Unique: TASK_ID, PARAMETER_NAME where ACTIVE_FLAG = 'Y'.

CFG_BUSINESS_RULES

A BUSINESS_RULES task's rules: a correlated SELECT that finds the rows of TARGET_TABLE that break it, and what a failing row gets flagged as.

Column	Type	Null	Default	Allowed values
BUSINESS_RULE_ID	BIGINT	no
BUSINESS_RULE_NAME	VARCHAR	no
PIPELINE_ID	BIGINT	no
TASK_ID	BIGINT	no
BUSINESS_RULE_SQL	VARCHAR	no
BUSINESS_RULE_TYPE	VARCHAR	no		INCOMPLETE, REJECT, REPORT
BUSINESS_RULE_KEY_COLUMN	VARCHAR	no
TARGET_TABLE	VARCHAR	no
SEQUENCE_NUMBER	BIGINT	no
ACTIVE_FLAG	VARCHAR	no	'Y'	Y, N
CREATED_BY	VARCHAR	yes
CREATE_DATE	TIMESTAMPTZ	yes
UPDATED_BY	VARCHAR	yes
UPDATED_DATE	TIMESTAMPTZ	yes

Unique: TASK_ID, BUSINESS_RULE_NAME where ACTIVE_FLAG = 'Y'.

AUD_PIPELINES_RUN_LOG

One row per pipeline run: its status, when it ran, whether it met its SLA, the date it ran as of (RUN_DATE, the SQL tasks' $$run_date), and whether it was part of a backfill.

Column	Type	Null	Default	Allowed values
PIPELINE_RUN_ID	BIGINT	no
PIPELINE_ID	BIGINT	no
START_DATE	TIMESTAMPTZ	no	now()
END_DATE	TIMESTAMPTZ	yes
STATUS	VARCHAR	no		QUEUED, IN-PROGRESS, SUCCESS, FAILED, SKIPPED, CANCELLED
SLA_STATUS	VARCHAR(8)	yes		MET, BREACHED
RUN_DATE	DATE	yes
BACKFILL	VARCHAR(1)	no	'N'	Y, N
RUN_KEY	VARCHAR	no	('manual:' \|\| gen_random_uuid()::text)
TRIGGER_KIND	VARCHAR	no	'MANUAL'	SCHEDULE, MANUAL, BACKFILL, ORCHESTRATOR, STAND_IN
OWNER_ID	VARCHAR	yes
LEASE_EXPIRES_AT	TIMESTAMPTZ	yes
OUTPUT_REVISION	INT	no	1
CONFIG_SHA256	VARCHAR(64)	yes
STARTED_BY	VARCHAR	yes	current_setting('etl_craft.actor', true)
STARTED_BY_KIND	VARCHAR	yes	current_setting('etl_craft.actor_kind', true)	HUMAN, SCHEDULE, ORCHESTRATOR, WORKER, SYSTEM
ENDED_BY	VARCHAR	yes
ENDED_BY_KIND	VARCHAR	yes		HUMAN, SCHEDULE, ORCHESTRATOR, WORKER, SYSTEM
REPAIR_PENDING	VARCHAR	no	'N'	Y, N

Unique: PIPELINE_ID where STATUS = 'IN-PROGRESS'; PIPELINE_ID, RUN_KEY.

AUD_TASK_RUN_LOG

One row per task per run, updated by each attempt: the latest attempt's status, counts, message and log tail.

Column	Type	Null	Default	Allowed values
TASK_RUN_ID	BIGINT	no
TASK_ID	BIGINT	no
PIPELINE_RUN_ID	BIGINT	no
START_DATE	TIMESTAMPTZ	no	now()
END_DATE	TIMESTAMPTZ	yes
STATUS	VARCHAR	no		IN-PROGRESS, SUCCESS, FAILED, SKIPPED, CANCELLED
SOURCE_COUNT	BIGINT	yes
TARGET_COUNT	BIGINT	yes
INSERT_COUNT	BIGINT	yes
UPDATE_COUNT	BIGINT	yes
DELETE_COUNT	BIGINT	yes
ERROR_MESSAGE	VARCHAR	yes
TASK_LOG	VARCHAR	yes
ATTEMPT_COUNT	INT	no	1
ROWS_WRITTEN	BIGINT	yes

Unique: TASK_ID, PIPELINE_RUN_ID.

AUD_RUN_INTERVENTIONS

Every change an operator made to a run: a task or a run marked, a stand-in run recorded, a run cancelled or reopened, a task reset to run again or run again, a task run without its dependencies, and a dependency gate bypassed. TASK_ID is NULL for a change to the run. FROM_STATUS and PREVIOUS_MESSAGE keep what the row held before, so nothing is erased; TO_STATUS is NULL for a task reset to not run yet.

Column	Type	Null	Default	Allowed values
INTERVENTION_ID	BIGINT	no
PIPELINE_ID	BIGINT	no
PIPELINE_RUN_ID	BIGINT	no
TASK_ID	BIGINT	yes
ACTION	VARCHAR(24)	no		MARK, NEW_RUN, CANCEL, REOPEN, RESET, GATE_BYPASS, IGNORE_DEPENDENCIES, RERUN
FROM_STATUS	VARCHAR(16)	yes
TO_STATUS	VARCHAR(16)	yes
TARGET_COUNT	BIGINT	yes
PREVIOUS_MESSAGE	VARCHAR	yes
REASON	VARCHAR	no
REQUESTED_BY	VARCHAR	no
REQUESTED_AT	TIMESTAMPTZ	no	now()
REQUESTED_BY_KIND	VARCHAR	yes	current_setting('etl_craft.actor_kind', true)	HUMAN, SCHEDULE, ORCHESTRATOR, WORKER, SYSTEM

AUD_PIPELINE_PAUSES

Each time a pipeline was paused: while a pause has no RESUMED_AT, run starts nothing of the pipeline, and a run in progress starts no more tasks until the pipeline is resumed.

Column	Type	Null	Default	Allowed values
PIPELINE_PAUSE_ID	BIGINT	no
PIPELINE_ID	BIGINT	no
PAUSED_AT	TIMESTAMPTZ	no	now()
PAUSED_BY	VARCHAR	no
REASON	VARCHAR	no
RESUMED_AT	TIMESTAMPTZ	yes
RESUMED_BY	VARCHAR	yes
RESUME_REASON	VARCHAR	yes
PAUSED_BY_KIND	VARCHAR	yes	current_setting('etl_craft.actor_kind', true)	HUMAN, SCHEDULE, ORCHESTRATOR, WORKER, SYSTEM
RESUMED_BY_KIND	VARCHAR	yes		HUMAN, SCHEDULE, ORCHESTRATOR, WORKER, SYSTEM

Unique: PIPELINE_ID where RESUMED_AT IS NULL.

AUD_BUSINESS_RULES_RUN_LOG

One row per business rule per task run: whether the rule ran, and how long it took.

Column	Type	Null	Default	Allowed values
BUSINESS_RULE_RUN_ID	BIGINT	no
BUSINESS_RULE_ID	BIGINT	no
TASK_RUN_ID	BIGINT	no
START_DATE	TIMESTAMPTZ	no	now()
END_DATE	TIMESTAMPTZ	yes
STATUS	VARCHAR	no		IN-PROGRESS, SUCCESS, FAILED, SKIPPED

Unique: BUSINESS_RULE_ID, TASK_RUN_ID.

AUD_BUSINESS_RULES_RESULTS

The rows each rule flagged, by key; a flag is cleared (ACTIVE_FLAG 'N') once its row passes.

Column	Type	Null	Default	Allowed values
BUSINESS_RULE_RESULT_ID	BIGINT	no
BUSINESS_RULE_RUN_ID	BIGINT	no
BUSINESS_RULE_ID	BIGINT	no
BUSINESS_RULE_KEY	VARCHAR	no
TARGET_TABLE	VARCHAR	no
STATUS	VARCHAR	no		INCOMPLETE, REJECT, REPORT
ACTIVE_FLAG	VARCHAR	no	'Y'	Y, N
START_DATE	TIMESTAMPTZ	no	now()
END_DATE	TIMESTAMPTZ	yes

AUD_TASK_OFFSET_TRACKER

Where each incremental ingestion script got to: the offset it reads from next.

Column	Type	Null	Default	Allowed values
TASK_ID	BIGINT	no
OFFSET_TYPE	VARCHAR	no		NUMBER, TEXT, TIMESTAMP
OFFSET_VALUE	VARCHAR	yes
LAST_UPDATED_TIMESTAMP	TIMESTAMPTZ	no	now()

AUD_COLUMN_LINEAGE

Column lineage traced from each SQL task's SELECT, stored by a hash of what it depends on.

Column	Type	Null	Default	Allowed values
COLUMN_LINEAGE_ID	BIGINT	no
TASK_ID	BIGINT	no
SOURCE_SQL_HASH	VARCHAR	no
TARGET_OBJECT	VARCHAR	no
TARGET_COLUMN	VARCHAR	no
SOURCE_OBJECT	VARCHAR	yes
SOURCE_COLUMN	VARCHAR	yes
TRANSFORMATION	VARCHAR	yes
COMPUTED_AT	TIMESTAMPTZ	no	now()

AUD_DOCS_PUBLICATION

The URL the catalog site is published at, one row per URL: the latest is current. A publish that is given another URL fails unless told to accept it, so shared links keep working.

Column	Type	Null	Default	Allowed values
DOCS_PUBLICATION_ID	BIGINT	no
PUBLISHED_URL	VARCHAR	no
FIRST_PUBLISHED	TIMESTAMPTZ	no	now()
LAST_PUBLISHED	TIMESTAMPTZ	no	now()

AUD_TASK_DOCUMENTATION

The versions of each task's DOCUMENTATION, one row per change.

Column	Type	Null	Default	Allowed values
TASK_DOCUMENTATION_ID	BIGINT	no
TASK_ID	BIGINT	no
VERSION	INT	no
DOCUMENTATION_HASH	VARCHAR	no
DOCUMENTATION	VARCHAR	no
RECORDED_AT	TIMESTAMPTZ	no	now()

AUD_DEPENDENCY_CONSUMPTION

Every upstream run a downstream consumed, one row each, never updated: for a dependency on a pipeline, the downstream run and the upstream run; for a dependency on another pipeline's task, the downstream task and the upstream task run. A dependency's latest row is what it last consumed: an upstream run satisfies it only when it is newer than that one. PIPELINE_RUN_ID is NULL on the rows carried over from the trackers this table replaced.

Column	Type	Null	Default	Allowed values
CONSUMPTION_ID	BIGINT	no
PIPELINE_DEPENDENCY_ID	BIGINT	yes
TASK_DEPENDENCY_ID	BIGINT	yes
PIPELINE_ID	BIGINT	no
PIPELINE_RUN_ID	BIGINT	yes
TASK_ID	BIGINT	yes
DEPENDS_ON_PIPELINE_ID	BIGINT	no
CONSUMED_PIPELINE_RUN_ID	BIGINT	no
CONSUMED_TASK_RUN_ID	BIGINT	yes
CONSUMED_AT	TIMESTAMPTZ	no	now()
CONSUMED_REVISION	INT	no	1

SCHEMA_MIGRATIONS

Every migration applied, from etl-craft (ENGINE) and from the project (PROJECT), with its checksum.

Column	Type	Null	Default	Allowed values
SOURCE	VARCHAR	no		ENGINE, PROJECT
VERSION	VARCHAR	no
CHECKSUM	VARCHAR(64)	no
APPLIED_AT	TIMESTAMPTZ	no	now()

AUD_TASK_ATTEMPTS

Attempt history and the dependencies judged at admission.

Column	Type	Null	Default	Allowed values
ATTEMPT_ID	BIGINT	no
TASK_RUN_ID	BIGINT	no
ATTEMPT_NUMBER	INT	no
STATUS	VARCHAR	no		QUEUED, CLAIMED, RUNNING, SUCCESS, FAILED, TIMED_OUT, CANCELLED, LOST
OWNER_ID	VARCHAR	yes
LEASE_EXPIRES_AT	TIMESTAMPTZ	yes
HEARTBEAT_AT	TIMESTAMPTZ	yes
QUEUED_AT	TIMESTAMPTZ	yes
CLAIMED_AT	TIMESTAMPTZ	yes
STARTED_AT	TIMESTAMPTZ	yes
ENDED_AT	TIMESTAMPTZ	yes
HOST	VARCHAR	yes
PID	INT	yes
PROCESS_START	VARCHAR	yes
EXIT_CODE	INT	yes
SOURCE_COUNT	BIGINT	yes
TARGET_COUNT	BIGINT	yes
INSERT_COUNT	BIGINT	yes
UPDATE_COUNT	BIGINT	yes
DELETE_COUNT	BIGINT	yes
ROWS_WRITTEN	BIGINT	yes
ERROR_MESSAGE	VARCHAR	yes
TASK_LOG	VARCHAR	yes
LOG_PATH	VARCHAR	yes
REQUESTED_BY	VARCHAR	yes	current_setting('etl_craft.actor', true)
REQUESTED_BY_KIND	VARCHAR	yes	current_setting('etl_craft.actor_kind', true)	HUMAN, SCHEDULE, ORCHESTRATOR, WORKER, SYSTEM
NOT_BEFORE	TIMESTAMP	yes
RETRYABLE	BOOLEAN	no	TRUE	TRUE, FALSE

Unique: TASK_RUN_ID, ATTEMPT_NUMBER; TASK_RUN_ID where STATUS IN ('QUEUED','CLAIMED','RUNNING').

AUD_GATE_DECISIONS

Each dependency judged at admission, with the selected upstream revision and reason.

Column	Type	Null	Default	Allowed values
DECISION_ID	BIGINT	no
PIPELINE_RUN_ID	BIGINT	no
ATTEMPT_ID	BIGINT	yes
PIPELINE_DEPENDENCY_ID	BIGINT	yes
TASK_DEPENDENCY_ID	BIGINT	yes
SELECTED_PIPELINE_RUN_ID	BIGINT	yes
SELECTED_TASK_RUN_ID	BIGINT	yes
SELECTED_REVISION	INT	yes
RESULT	VARCHAR	no		SATISFIED, UNSATISFIED, BYPASSED
REASON	VARCHAR	no
DECIDED_AT	TIMESTAMPTZ	no	now()

AUD_ACTIONS

An immutable record of a command request; run and attempt outcomes live in their own logs.

Column	Type	Null	Default	Allowed values
ACTION_ID	BIGINT	no
STARTED_AT	TIMESTAMPTZ	no	now()
ENDED_AT	TIMESTAMPTZ	no	now()
ACTOR	VARCHAR	no
ACTOR_KIND	VARCHAR	no		HUMAN, SCHEDULE, ORCHESTRATOR, WORKER, SYSTEM
HOST	VARCHAR	no
COMMAND	VARCHAR	no
ARGUMENTS	JSONB	no
PIPELINE_ID	BIGINT	yes
PIPELINE_RUN_ID	BIGINT	yes
TASK_ID	BIGINT	yes
OUTCOME	VARCHAR	no
EXIT_CODE	INT	yes

AUD_METADATA_CHANGES

Every metadata row change with its before/after values and the project migration that made it.

Column	Type	Null	Default	Allowed values
CHANGE_ID	BIGINT	no
CHANGED_AT	TIMESTAMPTZ	no	now()
ACTOR	VARCHAR	no
ACTOR_KIND	VARCHAR	no		HUMAN, SCHEDULE, ORCHESTRATOR, WORKER, SYSTEM
TABLE_NAME	VARCHAR	no
ROW_KEY	VARCHAR	no
OPERATION	VARCHAR	no		INSERT, UPDATE, DELETE
BEFORE_JSON	JSONB	yes
AFTER_JSON	JSONB	yes
MIGRATION	VARCHAR	yes

AUD_TARGET_HASH_VERSION

The canonical hash version published after a target warehouse update commits.

Column	Type	Null	Default	Allowed values
TARGET_OBJECT	VARCHAR	no
HASH_VERSION	INTEGER	no		1, 2
RECOMPUTED_AT	TIMESTAMPTZ	no

AUD_OVERSEERS

Overseer process history; the session lock is the source of leadership.

Column	Type	Null	Default	Allowed values
OVERSEER_ID	BIGINT	no
HOST	VARCHAR	no
PID	INTEGER	no
VERSION	VARCHAR	no
STARTED_AT	TIMESTAMPTZ	no
HEARTBEAT_AT	TIMESTAMPTZ	no
STOPPED_AT	TIMESTAMPTZ	yes
STARTED_BY	VARCHAR	no
STARTED_BY_KIND	VARCHAR	no
STOPPED_BY	VARCHAR	yes
STOPPED_BY_KIND	VARCHAR	yes

AUD_GATE_WAITS

Durable gate polling budget; TASK_ID is NULL for pipeline admission and NEXT_CHECK_AT is NULL when settled.

Column	Type	Null	Default	Allowed values
PIPELINE_RUN_ID	BIGINT	no
TASK_ID	BIGINT	yes
FIRST_CHECK_AT	TIMESTAMPTZ	no
NEXT_CHECK_AT	TIMESTAMPTZ	yes
LOOKS	INT	no
WAIT_UNTIL	TIMESTAMPTZ	no

CFG_API_TOKENS

Opaque API credentials: SHA-256 hashes, roles and expiry; token values are never stored or cloned.

Column	Type	Null	Default	Allowed values
TOKEN_ID	BIGSERIAL	no
NAME	VARCHAR(128)	no
TOKEN_SHA256	VARCHAR(64)	no
ROLE	VARCHAR(16)	no		viewer, operator, admin
PROJECT_ID	BIGINT	yes
CREATED_BY	VARCHAR(128)	no
CREATED_AT	TIMESTAMP	no
EXPIRES_AT	TIMESTAMP	yes
REVOKED_AT	TIMESTAMP	yes