LLM-Driven Extraction of Unstructured Data — Built for API Deployments & ETL Pipeline Workflows
Python ai-agentsdata-engineeringdocument-aigenerative-aiidpjson-extractionllmmcp-serverocrpdf-extractionprompt-engineeringstructured-output
"""Schema-drift guard for the PG-queue tables the workers hit with raw SQL.
The workers speak to Postgres with **raw psycopg2 SQL, no Django ORM** — they run
in Docker decoupled from the backend by design, so they cannot import the models
as a source of truth. Column names therefore live as **string literals** across
``queue_backend/`` — ``queue_backend/pg_queue/*`` plus ``queue_backend/pg_barrier.py``
(the raw SQL for ``pg_barrier_state`` / ``pg_orchestration_claim`` lives there, one
directory up). A change in ``backend/pg_queue`` that renames or drops one of those
columns would NOT fail at import/compile time — only at runtime as a SQL error.
This DB-gated test closes that gap by asserting every column the workers depend on
still exists in the live schema. **Scope of enforcement:** it runs only against a
*migrated* Postgres — a dev machine with the ``pg_queue`` migration applied, or a CI
lane that provisions one and sets ``REQUIRE_PG_TESTS=1`` (conftest already turns the
``pg_conn`` skip into a failure there). The ``integration-workers`` CI lane is
currently ``optional`` and un-migrated, so ``pg_conn`` skips these there — wiring a
migrated lane is a follow-up. Until then, treat this as a dev/CI-lane guard, not a
production merge gate.
Semantics — **expected ⊆ actual**:
* A renamed/removed column listed here → **fails** (the intended signal: update
the raw SQL in ``queue_backend/`` and this contract together).
* A newly *added* column → does **not** fail: the queue SQL uses explicit column
lists (never ``SELECT *``), so a new column can't break a worker.
The manifest is the **full model mirror** of each queue-owned table (the workers
use ~all columns of these purpose-built tables; a couple — e.g.
``pg_periodic_schedule.created_at``/``updated_at`` — may not appear in any current
SQL, so the guard errs toward flagging any rename/removal on these tables for
review). Keep in sync only when a queue column is **renamed or removed**.
"""
import pytest
from queue_backend.pg_queue.schema import QUEUE_TABLES, queue_schema
# table -> columns the workers expect to exist (full model mirror; see module doc).
WORKER_SCHEMA_CONTRACT = {
"pg_queue_message": {
"msg_id",
"queue_name",
"message",
"org_id",
"enqueued_at",
"vt",
"read_ct",
"priority",
"state",
# Delayed visibility (countdown/eta), migration 0002. Absent from this set
# until now, while workers depend on it in raw SQL — so renaming or dropping
# it would have kept this guard green and broken delayed delivery at runtime,
# which is exactly the drift this file exists to catch.
"available_at",
},
"pg_task_result": {
"task_id",
"status",
"result",
"error",
"created_at",
"expires_at",
},
"pg_barrier_state": {
"execution_id",
"organization_id",
"remaining",
"results",
"created_at",
"expires_at",
"last_progress_at",
},
"pg_batch_dedup": {"execution_id", "batch_index", "created_at"},
"pg_orchestration_claim": {"execution_id", "organization_id", "claimed_at"},
"pg_orchestrator_lock": {"id", "leader", "acquired_at"},
"pg_periodic_schedule": {
"pipeline_id",
"organization_id",
"workflow_id",
"pipeline_name",
"cron_string",
"enabled",
"pg_owned",
"last_run_at",
"next_run_at",
"created_at",
"updated_at",
},
# UN-3796 — generic (non-pipeline) Beat periodics. Sibling of the table above;
# each row carries its own task/queue/args rather than the pipeline trigger's
# one fixed shape.
"pg_periodic_task": {
"name",
"task_name",
"queue",
"task_args",
"task_kwargs",
"org_id",
"cron_string",
"enabled",
"pg_owned",
"last_run_at",
"next_run_at",
"created_at",
"updated_at",
},
}
def _actual_columns(pg_conn, schema: str, table: str) -> set[str]:
"""Columns present for *schema.table* in the live DB.
``information_schema.columns`` is **privilege-filtered, not search_path-filtered**,
so the schema is pinned explicitly: an unqualified ``table_name`` match would
union columns from every visible schema (e.g. a stale per-developer sibling
schema alongside ``unstract``) and mask a column dropped in the target schema.
The schema is ``queue_schema()`` — exactly what the workers' ``qualified()`` SQL
resolves to.
"""
with pg_conn.cursor() as cur:
cur.execute(
"SELECT column_name FROM information_schema.columns "
"WHERE table_schema = %s AND table_name = %s",
(schema, table),
)
return {row[0] for row in cur.fetchall()}
def test_contract_covers_every_registered_queue_table():
"""The manifest must list exactly the tables the workers register in
``QUEUE_TABLES`` — so adding a queue table forces a column contract for it and
the drift guard can never silently skip one."""
assert set(WORKER_SCHEMA_CONTRACT) == set(QUEUE_TABLES)
@pytest.mark.parametrize("table", sorted(WORKER_SCHEMA_CONTRACT))
def test_worker_required_columns_exist(pg_conn, table):
schema = queue_schema() # the schema the workers' qualified() SQL targets
actual = _actual_columns(pg_conn, schema, table)
assert actual, f"{schema}.{table} not found in the live schema (migration applied?)"
missing = WORKER_SCHEMA_CONTRACT[table] - actual
assert not missing, (
f"{schema}.{table} is missing worker-required column(s) {sorted(missing)}: a "
f"model rename/removal in backend/pg_queue broke the workers' raw SQL. Update "
f"the SQL in queue_backend/ and WORKER_SCHEMA_CONTRACT together."
)