taher-ghaleb/DEPosit

1-- DEPosit: Pipeline artifact tables2-- Applied automatically by collect_pipeline_artifacts.py on first run.3-- Manual init: sqlite3 Data/DE_pipeline_artifacts.db < Scripts/pipeline_schema.sql4 5-- ── Collection progress (including repos with 0 matched artifacts) ─────────6CREATE TABLE IF NOT EXISTS repo_scan_status (7    repo_full_name       TEXT PRIMARY KEY,8    artifact_file_count  INTEGER NOT NULL,9    tree_status          TEXT,               -- ok | 404 | empty | truncated | http_*10    scanned_at           TEXT NOT NULL11);12 13-- ── Discovery ──────────────────────────────────────────────────────────────14CREATE TABLE IF NOT EXISTS pipeline_files (15    id                  INTEGER PRIMARY KEY AUTOINCREMENT,16    repo_full_name      TEXT    NOT NULL,17    file_path           TEXT    NOT NULL,18    artifact_type       TEXT    NOT NULL,   -- airflow_dag | dbt_model | dbt_schema |19                                            -- prefect_flow | dagster | luigi |20                                            -- kedro | beam | dlt | mage_block21    file_sha            TEXT,               -- git blob SHA22    file_size_bytes     INTEGER,23    collected_at        TEXT    NOT NULL,24    parse_error         TEXT,               -- NULL when parsing succeeded25    UNIQUE(repo_full_name, file_path)26);27 28-- ── Airflow ─────────────────────────────────────────────────────────────────29CREATE TABLE IF NOT EXISTS airflow_dag_features (30    id                          INTEGER PRIMARY KEY AUTOINCREMENT,31    repo_full_name              TEXT    NOT NULL,32    file_path                   TEXT    NOT NULL,33    dag_id                      TEXT,34    schedule_interval           TEXT,       -- cron expr, timedelta repr, preset, or NULL35    schedule_type               TEXT,       -- cron | timedelta | preset | none | dynamic36    catchup                     INTEGER,    -- 0/1/NULL (NULL = not set, inherits default)37    max_active_runs             INTEGER,38    default_retries             INTEGER,39    default_retry_delay_seconds INTEGER,40    task_count                  INTEGER,41    edge_count                  INTEGER,    -- explicit dependency edges42    unique_operator_count       INTEGER,43    operator_types              TEXT,       -- JSON array of operator class names44    has_python_operator         INTEGER,    -- 0/145    has_bash_operator           INTEGER,46    has_external_task_sensor    INTEGER,    -- cross-DAG coupling47    has_branch_operator         INTEGER,48    has_trigger_rule            INTEGER,    -- non-default trigger rules49    uses_xcom                   INTEGER,    -- xcom_push/xcom_pull detected50    uses_pools                  INTEGER,51    uses_connections            INTEGER,    -- conn_id parameter present52    has_sla                     INTEGER,    -- sla= or sla_miss_callback53    has_on_failure_callback     INTEGER,54    has_on_success_callback     INTEGER,55    dag_tags                    TEXT,       -- JSON array56    -- Anti-pattern flags (directly usable as SE research features)57    antipattern_catchup_no_maxruns  INTEGER, -- catchup=True with no max_active_runs58    antipattern_no_retries          INTEGER, -- no retry config anywhere in DAG59    antipattern_no_failure_cb       INTEGER, -- no failure callback60    antipattern_no_schedule         INTEGER, -- schedule_interval=None (orphaned DAG)61    antipattern_bare_xcom           INTEGER, -- xcom used for large data transfer62    file_lines                  INTEGER,63    FOREIGN KEY(repo_full_name, file_path) REFERENCES pipeline_files(repo_full_name, file_path)64);65 66-- ── dbt ─────────────────────────────────────────────────────────────────────67CREATE TABLE IF NOT EXISTS dbt_model_features (68    id                      INTEGER PRIMARY KEY AUTOINCREMENT,69    repo_full_name          TEXT    NOT NULL,70    file_path               TEXT    NOT NULL,71    model_name              TEXT    NOT NULL,72    model_layer             TEXT,           -- staging | intermediate | marts | other73    materialization         TEXT,           -- view | table | incremental | ephemeral | NULL74    ref_count               INTEGER,        -- upstream dbt model dependencies75    source_count            INTEGER,        -- raw source references76    cte_count               INTEGER,        -- WITH clause count77    has_where_clause        INTEGER,78    has_join                INTEGER,79    has_window_function     INTEGER,80    uses_incremental        INTEGER,81    has_unique_key          INTEGER,        -- required for safe incremental models82    hardcoded_limit         INTEGER,        -- LIMIT with literal integer (smell)83    select_star             INTEGER,        -- SELECT * (smell)84    has_tests               INTEGER,        -- set from schema.yml via extract_schema85    file_lines              INTEGER,86    -- Anti-pattern flags87    antipattern_incremental_no_unique_key   INTEGER,88    antipattern_select_star                 INTEGER,89    antipattern_no_source_no_ref            INTEGER, -- raw table name in FROM (hardcoded)90    FOREIGN KEY(repo_full_name, file_path) REFERENCES pipeline_files(repo_full_name, file_path)91);92 93CREATE TABLE IF NOT EXISTS dbt_project_summary (94    repo_full_name          TEXT    PRIMARY KEY,95    dbt_version_required    TEXT,96    model_paths             TEXT,           -- JSON array from dbt_project.yml97    has_tests_dir           INTEGER,98    has_snapshots           INTEGER,99    has_seeds               INTEGER,100    has_analyses            INTEGER,101    profile_outputs         TEXT,           -- JSON: target names from profiles.yml if present102    total_model_files       INTEGER,103    total_schema_files      INTEGER,104    FOREIGN KEY(repo_full_name) REFERENCES pipeline_files(repo_full_name)105);106 107-- ── Prefect ──────────────────────────────────────────────────────────────────108CREATE TABLE IF NOT EXISTS prefect_flow_features (109    id                      INTEGER PRIMARY KEY AUTOINCREMENT,110    repo_full_name          TEXT    NOT NULL,111    file_path               TEXT    NOT NULL,112    prefect_version         TEXT,           -- 1.x | 2.x inferred from imports113    flow_count              INTEGER,114    task_count              INTEGER,115    has_retries             INTEGER,116    max_retries             INTEGER,117    has_schedule            INTEGER,118    has_timeout             INTEGER,119    uses_result_caching     INTEGER,120    uses_state_handler      INTEGER,121    uses_mapped_tasks       INTEGER,        -- .map() or unmapped() pattern122    has_deployment_block    INTEGER,        -- Prefect 2.x deployment definition123    -- Anti-patterns124    antipattern_no_retries          INTEGER,125    antipattern_no_timeout          INTEGER,126    file_lines              INTEGER,127    FOREIGN KEY(repo_full_name, file_path) REFERENCES pipeline_files(repo_full_name, file_path)128);129 130-- ── Dagster ──────────────────────────────────────────────────────────────────131CREATE TABLE IF NOT EXISTS dagster_features (132    id                      INTEGER PRIMARY KEY AUTOINCREMENT,133    repo_full_name          TEXT    NOT NULL,134    file_path               TEXT    NOT NULL,135    asset_count             INTEGER,136    op_count                INTEGER,137    job_count               INTEGER,138    graph_count             INTEGER,139    resource_count          INTEGER,140    has_partitions          INTEGER,141    has_sensors             INTEGER,142    has_schedules           INTEGER,143    uses_io_manager         INTEGER,144    uses_config_schema      INTEGER,145    uses_metadata           INTEGER,146    has_freshness_policy    INTEGER,147    -- Anti-patterns148    antipattern_op_without_io_manager   INTEGER, -- op returning data without IOManager149    antipattern_no_metadata             INTEGER,150    file_lines              INTEGER,151    FOREIGN KEY(repo_full_name, file_path) REFERENCES pipeline_files(repo_full_name, file_path)152);153 154-- ── Luigi ────────────────────────────────────────────────────────────────────155CREATE TABLE IF NOT EXISTS luigi_task_features (156    id                      INTEGER PRIMARY KEY AUTOINCREMENT,157    repo_full_name          TEXT    NOT NULL,158    file_path               TEXT    NOT NULL,159    task_count              INTEGER,160    max_requires_depth      INTEGER,        -- longest dependency chain in file161    uses_local_target       INTEGER,162    uses_s3_target          INTEGER,163    uses_gcs_target         INTEGER,164    uses_hdfs_target        INTEGER,165    uses_external_task      INTEGER,        -- ExternalTask dependency166    has_dynamic_requires    INTEGER,        -- requires() returns variable list167    has_run_method          INTEGER,168    has_output_method       INTEGER,169    -- Anti-patterns170    antipattern_missing_output          INTEGER, -- run() without output()171    antipattern_hardcoded_path          INTEGER, -- string literal in LocalTarget172    file_lines              INTEGER,173    FOREIGN KEY(repo_full_name, file_path) REFERENCES pipeline_files(repo_full_name, file_path)174);175 176-- ── Kedro ────────────────────────────────────────────────────────────────────177CREATE TABLE IF NOT EXISTS kedro_pipeline_features (178    id                      INTEGER PRIMARY KEY AUTOINCREMENT,179    repo_full_name          TEXT    NOT NULL,180    file_path               TEXT    NOT NULL,181    node_count              INTEGER,182    pipeline_count          INTEGER,183    dataset_types           TEXT,           -- JSON array of dataset type strings184    has_parameters_file     INTEGER,185    has_catalog_file        INTEGER,186    uses_versioning         INTEGER,        -- versioned datasets187    uses_partitioned_ds     INTEGER,188    file_lines              INTEGER,189    FOREIGN KEY(repo_full_name, file_path) REFERENCES pipeline_files(repo_full_name, file_path)190);191 192-- ── Apache Beam ──────────────────────────────────────────────────────────────193CREATE TABLE IF NOT EXISTS beam_pipeline_features (194    id                      INTEGER PRIMARY KEY AUTOINCREMENT,195    repo_full_name          TEXT    NOT NULL,196    file_path               TEXT    NOT NULL,197    ptransform_count        INTEGER,        -- custom PTransform subclasses198    pardo_count             INTEGER,199    combinefn_count         INTEGER,200    runner_type             TEXT,           -- DirectRunner | DataflowRunner | SparkRunner201    uses_windowing          INTEGER,202    uses_side_inputs        INTEGER,203    uses_state_and_timers   INTEGER,204    file_lines              INTEGER,205    FOREIGN KEY(repo_full_name, file_path) REFERENCES pipeline_files(repo_full_name, file_path)206);207 208-- ── dlt (data load tool) ────────────────────────────────────────────────────209CREATE TABLE IF NOT EXISTS dlt_features (210    id                      INTEGER PRIMARY KEY AUTOINCREMENT,211    repo_full_name          TEXT    NOT NULL,212    file_path               TEXT    NOT NULL,213    source_count            INTEGER,        -- @dlt.source decorated functions214    resource_count          INTEGER,        -- @dlt.resource decorated functions215    destination_types       TEXT,           -- JSON array: bigquery, snowflake, etc.216    uses_incremental        INTEGER,217    uses_schema_contracts   INTEGER,218    uses_transformers       INTEGER,        -- @dlt.transformer219    file_lines              INTEGER,220    FOREIGN KEY(repo_full_name, file_path) REFERENCES pipeline_files(repo_full_name, file_path)221);222 223-- ── Indexes ──────────────────────────────────────────────────────────────────224CREATE INDEX IF NOT EXISTS idx_pf_repo   ON pipeline_files(repo_full_name);225CREATE INDEX IF NOT EXISTS idx_pf_type   ON pipeline_files(artifact_type);226CREATE INDEX IF NOT EXISTS idx_adf_repo  ON airflow_dag_features(repo_full_name);227CREATE INDEX IF NOT EXISTS idx_dbt_repo  ON dbt_model_features(repo_full_name);228CREATE INDEX IF NOT EXISTS idx_prf_repo  ON prefect_flow_features(repo_full_name);229CREATE INDEX IF NOT EXISTS idx_dag_repo  ON dagster_features(repo_full_name);230CREATE INDEX IF NOT EXISTS idx_lui_repo  ON luigi_task_features(repo_full_name);231CREATE INDEX IF NOT EXISTS idx_ked_repo  ON kedro_pipeline_features(repo_full_name);232