LLM-Driven Extraction of Unstructured Data — Built for API Deployments & ETL Pipeline Workflows
Python ai-agentsdata-engineeringdocument-aigenerative-aiidpjson-extractionllmmcp-serverocrpdf-extractionprompt-engineeringstructured-output
from django.db import models
from django.db.models import F
from django.utils import timezone
from unstract.core.data_models import QueueMessageState
# Local aliases so the model reads cleanly; the enum is the single source of truth
# shared with the workers' raw SQL (client.py / reaper.py). See QueueMessageState.
_READY = QueueMessageState.READY.value
_CLAIMED = QueueMessageState.CLAIMED.value
_SCHEDULED = QueueMessageState.SCHEDULED.value
class PgQueueMessage(models.Model):
"""Bespoke extension-free queue table (``SKIP LOCKED`` + visibility timeout).
The table and its dequeue index are fully Django-managed (generated by
``makemigrations``, migration 0001) — no hand-written migration, no DB
function, no ``CREATE EXTENSION`` (extension-free decision). The
``SKIP LOCKED`` dequeue itself is a single statement in the workers'
``pg_queue`` client, not a DB-side function. This model doubles as a
typed read handle for the backend producer side.
"""
msg_id = models.BigAutoField(primary_key=True)
queue_name = models.TextField()
# Opaque JSONB payload. For task dispatches the workers write a task
# payload here (``queue_backend.pg_queue.TaskPayload``: task_name / args
# / kwargs / queue / fairness); the queue itself stays payload-agnostic.
message = models.JSONField()
# "" = no org (leaf tasks); the fair-admission query (a later phase)
# uses it for the coupled pipeline. Empty string rather than NULL —
# a string field shouldn't have two "no data" values (Django S6553).
org_id = models.TextField(blank=True, default="")
# Python-level defaults let ORM ``.create()`` work without these; the
# raw-SQL client supplies them explicitly (``now()`` / ``0``), so no
# DB-level default is required.
enqueued_at = models.DateTimeField(default=timezone.now)
vt = models.DateTimeField(default=timezone.now) # visibility timeout
read_ct = models.IntegerField(default=0)
# fairness L3: higher value is claimed sooner. The canonical range lives in
# workers/queue_backend/fairness.py (MIN_PRIORITY=1, MAX_PRIORITY=10,
# DEFAULT_PRIORITY=5); these literals mirror it because backend and workers
# are separate codebases that can't import each other. Keep them in sync — a
# workers integration test (test_db_check_constraint_matches_fairness_bounds)
# asserts this DB constraint matches the fairness bounds, so a divergence
# fails loudly. The dispatch writes priority from fairness.pipeline_priority;
# leaf tasks with no fairness get the neutral default. L1 (org tier) / L2
# (workload) ordering + burst_max admission are deferred to the fair-admission
# orchestrator. The CheckConstraint is the one backstop no writer can bypass.
priority = models.SmallIntegerField(default=5)
# Claim state (scan-past fix) — QueueMessageState {ready, claimed}.
# 'ready' = claimable, 'claimed' = in-flight (a consumer holds it, vt is its
# lease). This makes visibility an INDEXED predicate instead of the old per-row
# `vt <= now()` filter: the claim walks a partial index that contains ONLY
# 'ready' rows, so in-flight rows are no longer physically scanned past on every
# claim (the O(in-flight) cost that collapsed throughput at high concurrency).
#
# REDELIVERY DEPENDENCY: a crashed worker's row (state='claimed', vt expired) is
# re-armed to 'ready' by the reaper (reaper.rearm_expired_claims), NOT by the
# claim itself (the old `vt<=now()` self-heal is gone). The reaper is therefore
# on the crash-redelivery path — it is already the mandatory recovery singleton
# for stranded barriers/claims, so this adds queue re-arm to its remit rather
# than a new deployment dependency. Alert on its liveness for the queue path
# (PG_QUEUE_CLAIM_STATE_RUNBOOK: oldest_claimed_expired_age).
#
# `vt` is STILL not in any index, so the frequent lease-renewal UPDATE (set_vt)
# stays HOT-eligible (actual HOT also needs heap-page free space, i.e.
# fillfactor < 100 — see migration 0014); only the once-per-message state
# transitions (claim / ack / rare re-arm) are non-HOT — bounded. If a soak shows
# partial-index bloat, mitigate with per-environment ops tuning (fillfactor +
# aggressive autovacuum) — kept out of the migration so the schema stays
# portable across Postgres providers.
state = models.TextField(default=_READY)
# Delayed visibility (UN-3843) — Celery ``countdown``/``eta`` parity. Set to a
# FUTURE timestamp only together with ``state='scheduled'``; the pair is what
# defers the row. Default now() keeps every pre-existing dispatch byte-identical
# (immediate rows are enqueued 'ready' and this column is never read for them).
#
# Deliberately NOT a claim predicate. The claim's partial index holds only
# 'ready' rows, so a scheduled row is physically absent from it and a pending
# delay costs the hot path nothing. Adding `available_at <= now()` to the claim
# would instead park every not-yet-due row inside the claim index to be walked
# and discarded on every claim — reintroducing the O(in-flight) scan-past cost
# that the state machine above was introduced to remove. The reaper promotes
# 'scheduled' -> 'ready' when due (reaper.promote_due_scheduled), so delivery is
# "not before available_at", never early, at reaper-tick granularity.
#
# Like `vt`, this column is absent from the claim index, so it never burdens the
# hot path; unlike `vt` it is written exactly once (at enqueue) and then only
# read by the promotion sweep.
available_at = models.DateTimeField(default=timezone.now)
class Meta:
db_table = "pg_queue_message"
constraints = [
models.CheckConstraint(
check=models.Q(priority__gte=1) & models.Q(priority__lte=10),
name="pg_queue_message_priority_range",
),
# Backstop no writer can bypass: state is a closed enum. Values sourced
# from QueueMessageState (single source of truth; drift-tested).
models.CheckConstraint(
check=models.Q(state__in=[_READY, _CLAIMED, _SCHEDULED]),
name="pg_queue_message_state_valid",
),
]
indexes = [
# CLAIM path — partial index over ONLY claimable rows. The dequeue
# (`WHERE queue_name=? AND state='ready' ORDER BY priority DESC, msg_id
# FOR UPDATE SKIP LOCKED`) walks this in (priority DESC, msg_id ASC)
# order; claimed rows are absent from the index entirely, so claim cost
# is INDEPENDENT of in-flight (claimed) depth — it walks O(LIMIT) ready
# rows plus any a concurrent claimer momentarily holds under FOR UPDATE
# (bounded by concurrency, not backlog). `vt` is deliberately NOT here
# (keeps lease renewal HOT-eligible). Replaces the old full
# `pg_queue_message_dequeue_idx`.
models.Index(
F("queue_name"),
F("priority").desc(),
F("msg_id"),
condition=models.Q(state=_READY),
name="pg_queue_message_claim_idx",
),
# REAPER path — partial index over ONLY in-flight rows (bounded by total
# consumer concurrency across the fleet — typically small, ~one in-flight
# row per busy worker). The re-arm sweep (`WHERE state='claimed' AND
# vt<=now()`) enumerates this small set and filters vt in-scan. `vt` is
# intentionally excluded so a lease renewal (which rewrites vt every
# ~lease/3) does not maintain this index and stays HOT-eligible.
models.Index(
F("msg_id"),
condition=models.Q(state=_CLAIMED),
name="pg_queue_message_claimed_idx",
),
# PROMOTION path (UN-3843) — partial index over ONLY deferred rows. The
# reaper's promotion sweep (`WHERE state='scheduled' AND
# available_at<=now()`) walks this in due order, so a large backlog of
# far-future rows costs one index seek, not a scan. Bounded by pending
# delayed messages, which is a small set by construction (a delay is a
# stagger or a retry backoff, not a queue). Keyed on `available_at` alone:
# the sweep is time-ordered and queue-agnostic — it promotes every due row
# in one statement rather than per queue.
models.Index(
F("available_at"),
condition=models.Q(state=_SCHEDULED),
name="pg_queue_message_scheduled_idx",
),
]
class PgOrchestratorLock(models.Model):
"""Single-row leader-election lease for the orchestrator/reaper singleton.
The orchestrator (admit) and reaper (stuck-task recovery, barrier-orphan
sweep) must run as **exactly one** active instance — multiple would contend
on ``SKIP LOCKED`` and double-act on recovery. HA is leader-elected: one or
more candidate processes race to hold this single row; the holder renews its
lease each cycle, and if it dies a standby takes over once the lease goes
stale.
**Lease, not advisory lock.** Leadership is a TTL'd ``UPDATE`` (acquire if
``leader`` is free or ``acquired_at`` is older than the lease window), *not*
``pg_advisory_lock``. Session-scoped advisory locks do not survive
**transaction-pooled PgBouncer** (the queue's connection path),
since the pooler hands a candidate a different backend per transaction. A
plain ``UPDATE`` is one transaction → pooling-safe. All time comparisons use
the **DB clock** (``now()``), so candidate clock skew can't split leadership.
**Free = empty ``leader``** (not NULL), following the ``PgQueueMessage.org_id``
convention — a string field shouldn't carry two "no holder" states (S6553).
``acquired_at`` is only meaningful while ``leader`` is non-empty.
Single row enforced by ``CheckConstraint(id = 1)``; the row is seeded by the
migration. Managed=True / generated migration, extension-free, same posture
as the sibling models.
"""
id = models.IntegerField(primary_key=True, default=1)
# Current leader's worker id; "" means the lease is free (no holder).
leader = models.TextField(blank=True, default="")
# When the current leader last acquired/renewed. Only read while leader != "";
# a free row's value is immaterial. now() default for ORM .create(); the raw
# leader-election SQL always sets it explicitly.
acquired_at = models.DateTimeField(default=timezone.now)
class Meta:
db_table = "pg_orchestrator_lock"
constraints = [
# The whole point: at most one lock row. Any second row is a bug.
models.CheckConstraint(
check=models.Q(id=1),
name="pg_orchestrator_lock_single_row",
),
]
class PgBatchDedup(models.Model):
"""Per-batch idempotency marker for the at-least-once PG execution pipeline.
The PG queue is at-least-once: a ``process_file_batch`` task can be
redelivered after a crash-before-ack, which would re-run the batch and
double-decrement the barrier (``process_file_batch`` is non-idempotent —
``max_retries=0``, and the destination push is not fully idempotent on
redelivery). This table is the durable dedup token: one row per
``(execution_id, batch_index)``.
The worker claims a batch by inserting its row atomically
(``INSERT … ON CONFLICT DO NOTHING RETURNING``): a fresh insert means
"first delivery — the caller proceeds and performs the single barrier
decrement"; a conflict means "already claimed — a redelivery → caller
skips". Pairing the claim with the decrement is what keeps the barrier
decremented exactly once per batch even though the queue may deliver the
task more than once. Rows for an execution are cleared when its barrier
finalises (``remaining`` → 0, via ``clear_execution_batches``).
Orphan reclaim — markers left behind by an execution that never finalises
(the reaper's ``sweep_expired_barriers`` deletes only the ``pg_barrier_state``
row, with no FK / cascade to this table) are swept by the reaper's
``sweep_orphan_dedup``, which deletes markers older than the retention window
by ``created_at`` age (wired into the reaper's periodic ``_maybe_sweep``).
Composite uniqueness on ``(execution_id, batch_index)`` is enforced by a
``UniqueConstraint`` (Django has no composite primary key here); the worker's
``ON CONFLICT (execution_id, batch_index)`` targets exactly those columns,
and the constraint's index — ``execution_id`` leading — also serves the
per-execution cleanup ``DELETE``. Managed=True / generated migration, no
DB-side function, extension-free — same posture as the siblings.
"""
execution_id = models.TextField()
batch_index = models.IntegerField()
# When the marker was claimed. Read by the reaper's dedup-orphan sweep
# (``sweep_orphan_dedup``), which deletes markers older than the retention
# window by this column's age. Deliberately UNINDEXED — the sweep seq-scans a
# normally-near-empty table at a ~5-min cadence; add an index if it grows.
created_at = models.DateTimeField(default=timezone.now)
class Meta:
db_table = "pg_batch_dedup"
constraints = [
# The claim's ON CONFLICT target and the one writer-proof invariant
# (the worker SQL can't import this model): a batch is claimed at
# most once per execution.
models.UniqueConstraint(
fields=["execution_id", "batch_index"],
name="pg_batch_dedup_exec_batch_uniq",
),
# batch_index is a non-negative ordinal; a negative value is a
# programming error. Writer-proof, mirroring PgQueueMessage.priority's
# domain-bound CheckConstraint precedent.
models.CheckConstraint(
check=models.Q(batch_index__gte=0),
name="pg_batch_dedup_batch_index_non_negative",
),
]
class PgBarrierState(models.Model):
"""Per-execution fan-in barrier state for ``PgBarrier``.
One row per in-flight barrier (keyed by ``execution_id``). Each header task
atomically decrements ``remaining`` in-body and appends to ``results`` in a
single ``UPDATE … RETURNING``; the task that
drives ``remaining`` to 0 dispatches the aggregating callback and deletes the
row. A header-task failure aborts the barrier by deleting the row outright
(``DELETE … RETURNING`` — atomic claim+teardown), so the callback can never
fire with partial results. The ``pg-queue-reaper`` marks a stranded execution
ERROR when ``last_progress_at`` goes stale (no batch completed for
``WORKER_PG_BATCH_STUCK_TIMEOUT_SECONDS``) or, as an absolute backstop, when
``expires_at`` passes (see ``workers/queue_backend/pg_queue/reaper.py``).
Managed=True / generated migration — no DB-side function, extension-free,
same posture as ``PgQueueMessage``.
"""
execution_id = models.TextField(primary_key=True)
# Owning org, stamped at enqueue. The reaper needs it to call the org-scoped
# internal status API when recovering a stranded (expired) barrier — it has
# only the execution_id otherwise. "" = unknown (the reaper then skips the API
# mark and just cleans up). Same no-NULL-text convention as PgQueueMessage.org_id.
organization_id = models.TextField(blank=True, default="")
# Header tasks still pending. The last task to decrement it to 0 fires the
# callback. A value < 0 (decrement after expiry/cleanup) means the barrier
# was already torn down — the task cleans up without firing.
remaining = models.IntegerField()
# Aggregated header-task results, appended in completion order (JSONB array).
results = models.JSONField(default=list)
created_at = models.DateTimeField(default=timezone.now)
# Absolute orphan cap (Redis-TTL equivalent, WORKER_BARRIER_KEY_TTL_SECONDS,
# default 6h): a last-resort backstop the reaper also sweeps. Fixed at enqueue
# — the *fast* stuck signal is last_progress_at below.
expires_at = models.DateTimeField()
# Progress liveness: re-stamped to now() on enqueue AND on every
# decrement, so it tracks when a batch last completed. The reaper marks the
# execution ERROR once it is older than WORKER_PG_BATCH_STUCK_TIMEOUT_SECONDS
# (default 2.5h — in the same band as Celery's per-task
# FILE_PROCESSING_TASK_TIME_LIMIT, which ships 2h–3h) — a per-PROGRESS window
# that is invariant to MAX_PARALLEL_FILE_BATCHES (dynamic
# per-org), so it never false-fails a legitimately long multi-batch run.
# Deliberately UNINDEXED: it's written on every decrement, so indexing it would
# break the heap-only-tuple (HOT) update the decrement relies on — and the
# reaper's sweep of this tiny (in-flight-only) table is a cheap seq scan.
# Directly queryable ("when did this barrier last progress?") — the PG-queue
# "stuck-detection = plain SQL" property.
last_progress_at = models.DateTimeField(default=timezone.now)
class Meta:
db_table = "pg_barrier_state"
constraints = [
# The one writer-proof invariant (the worker SQL can't import this
# model). Deliberately NO `remaining >= 0` check — the teardown path
# relies on `remaining` going negative as a "barrier already gone"
# signal, so a non-negative constraint would break it.
models.CheckConstraint(
check=models.Q(expires_at__gt=models.F("created_at")),
name="pg_barrier_expires_after_created",
),
]
indexes = [
# Drives the (future) periodic expiry-sweep job.
models.Index(fields=["expires_at"], name="pg_barrier_expires_idx"),
]
class PgPeriodicSchedule(models.Model):
"""Inert mirror of a scheduled pipeline's cron definition.
One row per scheduled pipeline (keyed by ``pipeline_id``), dual-written
alongside the existing ``django_celery_beat`` ``PeriodicTask`` whenever a
pipeline schedule is created/updated/paused/resumed/deleted. It exists so a
future PG-backed scheduler — folded into the leader-elected reaper loop —
can fire due schedules without Celery Beat.
The PG scheduler tick (folded into the leader-elected reaper loop) fires
due rows where ``pg_owned`` is True; rows default to ``pg_owned=False``, so
the table stays inert until a schedule is explicitly handed over to Postgres.
``last_run_at`` / ``next_run_at`` are owned by that tick (the dual-write
leaves them NULL; a NULL ``next_run_at`` records a baseline on first tick
rather than firing immediately).
Managed=True / generated migration, extension-free, same posture
as ``PgBarrierState``.
"""
# Mirrors the Pipeline PK; also the PeriodicTask ``name`` (str of this UUID).
pipeline_id = models.UUIDField(primary_key=True)
# Owning org — needed to rebuild the ``execute_pipeline_task`` args when the
# scheduler fires. "" = unknown. Same no-NULL-text convention as elsewhere.
organization_id = models.TextField(blank=True, default="")
workflow_id = models.UUIDField(null=True, blank=True)
pipeline_name = models.TextField(blank=True, default="")
cron_string = models.TextField()
# Mirrors PeriodicTask.enabled (pipeline.active / pause / resume).
enabled = models.BooleanField(default=True)
# Per-schedule rollout switch. The PG scheduler fires a row ONLY when this is
# True. The no-double-fire guarantee with Celery Beat is CONDITIONAL on the
# matching Beat PeriodicTask being disabled when a schedule is handed over —
# that's the ramp control, which does not exist yet. Until it lands,
# safety rests on this defaulting to False: nothing is pg_owned, so the PG
# scheduler fires nothing and Beat fires everything. Flipping a row True while
# its PeriodicTask is still enabled WOULD double-fire — the ramp control must
# do both atomically. Migrating is then a reversible per-schedule flip.
pg_owned = models.BooleanField(default=False)
# Owned by the scheduler tick. NULL next_run_at = "record a baseline next
# time, don't fire this cycle" (avoids a burst when a schedule is handed over).
last_run_at = models.DateTimeField(null=True, blank=True)
next_run_at = models.DateTimeField(null=True, blank=True)
created_at = models.DateTimeField(default=timezone.now)
updated_at = models.DateTimeField(auto_now=True)
class Meta:
db_table = "pg_periodic_schedule"
indexes = [
# Drives the "due schedules" scan in the scheduler tick:
# WHERE pg_owned AND enabled AND (next_run_at IS NULL OR <= now()).
models.Index(
fields=["pg_owned", "enabled", "next_run_at"],
name="pg_periodic_schedule_due_idx",
),
]
class PgPeriodicTask(models.Model):
"""Generic (non-pipeline) periodic, mirrored from ``django_celery_beat`` (UN-3796).
The Beat-replacement sibling of :class:`PgPeriodicSchedule`. That table fires
**pipeline** triggers: it is keyed on ``pipeline_id`` and its dispatcher rebuilds
the fixed ``execute_pipeline_task`` argument list onto one fixed queue. Every
other Beat periodic — ``dashboard_metrics.*``, log-history, audit, anything an
operator adds — is an arbitrary ``(task, queue, args, kwargs)`` on a cron, which
that shape cannot express.
A **sibling table rather than a widened one**: ``PgPeriodicSchedule`` is live,
flag-gated and mid-ramp, and widening it would mean a surrogate PK plus rewriting
every existing row to carry columns only the other kind of schedule uses. The two
genuinely differ in payload and target queue; they share only the cron mechanics,
which live in the dispatcher, not the row.
Keyed on ``name`` — ``PeriodicTask.name``, the natural mirror key and already
unique in Beat, so reconciliation is a plain upsert with no id mapping to keep.
Ownership and the no-double-fire guarantee are identical to the sibling's and
depend on the same discipline: ``pg_owned`` defaults False, so the table is inert
until a row is explicitly handed over, and handing one over MUST disable the
matching Beat ``PeriodicTask`` in the same breath. A row flipped ``pg_owned=True``
while Beat still has it enabled fires twice.
Managed=True / generated migration, extension-free.
"""
# = PeriodicTask.name. Beat's own uniqueness key, so the mirror needs no
# surrogate id and reconciliation is an upsert on the same identity Beat uses.
name = models.TextField(primary_key=True)
# Dotted task path (PeriodicTask.task), e.g. "dashboard_metrics.aggregate_from_sources".
task_name = models.TextField()
# Target queue (PeriodicTask.queue). Unlike the pipeline scheduler's single
# hardcoded queue, each periodic carries its own — dashboard_metrics.* go to
# dashboard_metric_events, log-history to celery_periodic_logs, and so on.
queue = models.TextField()
# PeriodicTask.args / .kwargs, already decoded from Beat's JSON strings. Stored
# decoded so the dispatcher builds a TaskPayload without re-parsing per tick, and
# so a malformed value fails loudly at reconcile time rather than at fire time.
task_args = models.JSONField(default=list)
task_kwargs = models.JSONField(default=dict)
# Cron for crontab-backed periodics. Beat also supports IntervalSchedule
# (every N seconds/minutes); those are mirrored as an equivalent cron where one
# exists and skipped with a warning where it does not — sub-minute intervals have
# no cron expression, and silently coarsening one to a minute would change
# behaviour. See reconcile_pg_schedules.
cron_string = models.TextField()
# Owning org, "" = none. Global periodics (dashboard_metrics.*) have no org, so
# this is empty for every row today and the dispatcher writes it straight through
# to the queue message's org_id. It exists now rather than later for one reason:
# PgPeriodicSchedule carries organization_id, so without this column a future
# unification of the two tables would be a schema redesign instead of a data
# migration. Same no-NULL-text convention as elsewhere.
org_id = models.TextField(blank=True, default="")
# Mirrors PeriodicTask.enabled.
enabled = models.BooleanField(default=True)
# Per-row rollout switch; see the class docstring. Defaults False → inert.
pg_owned = models.BooleanField(default=False)
# Owned by the scheduler tick. NULL next_run_at = "record a baseline next time,
# don't fire this cycle" — no burst when a row is handed over.
last_run_at = models.DateTimeField(null=True, blank=True)
next_run_at = models.DateTimeField(null=True, blank=True)
created_at = models.DateTimeField(default=timezone.now)
updated_at = models.DateTimeField(auto_now=True)
class Meta:
db_table = "pg_periodic_task"
indexes = [
# Drives the due scan, mirroring pg_periodic_schedule_due_idx:
# WHERE pg_owned AND enabled AND (next_run_at IS NULL OR <= now()).
models.Index(
fields=["pg_owned", "enabled", "next_run_at"],
name="pg_periodic_task_due_idx",
),
]
class PgTaskResult(models.Model):
"""Request-reply result store for the executor RPC on PG.
Replaces Celery's ``AsyncResult`` / result backend for the *blocking*
executor dispatch when it rides the PG transport. A row appears ONLY when the
executor task finishes: the PG executor consumer (``worker-pg-executor``)
writes the returned ``ExecutionResult`` (``status="completed"``) or the error
text if the task raised (``status="failed"``), keyed by the caller-chosen
``task_id`` (the reply key). The blocking caller polls this table until the
row appears or its timeout elapses; a non-blocking poll just checks presence.
The absence of a row means "not done yet" — there is deliberately no
``pending`` state to maintain. A separate, droppable table that never touches
``WorkflowExecution`` — same posture as ``PgBarrierState`` / ``PgBatchDedup``,
extension-free. ``expires_at`` is written for the retention
sweep (``DELETE … WHERE expires_at <= now()``), which is wired into the
reaper's periodic ``_maybe_sweep`` (``sweep_expired_results``) — so the table
is bounded, not growing unbounded.
"""
# Caller-chosen reply key (opaque text, mirrors a Celery task id's role): the
# caller waits on it, the consumer stores the result under it.
task_id = models.TextField(primary_key=True)
# "completed" = task returned (``result`` holds ExecutionResult.to_dict());
# "failed" = task raised (``error`` holds the message), OR a payload could not
# be stored (``error`` holds one of the writer's two "unstorable" texts).
# Recovery logic must match the text before retrying a reply key, because the
# two unstorable cases point opposite ways: PAYLOAD_UNSTORABLE_ERROR means the
# task already ran to completion (a retry is a second full LLM spend — don't),
# ERROR_TEXT_UNSTORABLE means it raised (retrying is correct). See
# PgResultBackend's module docstring.
status = models.TextField()
result = models.JSONField(null=True, blank=True)
# No-NULL text convention: "" on a completed row (no error), the message on a
# failed row — avoids a string column having two empty states (NULL vs "").
error = models.TextField(blank=True, default="")
created_at = models.DateTimeField(default=timezone.now)
# Retention horizon for the reaper's retention sweep (see class docstring).
# The writer always sets it; nullable only so the column imposes no NOT NULL.
expires_at = models.DateTimeField(null=True, blank=True)
class Meta:
db_table = "pg_task_result"
indexes = [
# Index for the retention sweep (DELETE ... WHERE expires_at <= now()),
# run by the reaper's sweep_expired_results (see class docstring).
models.Index(fields=["expires_at"], name="pg_task_result_expires_idx"),
]
class PgOrchestrationClaim(models.Model):
"""Per-execution idempotency claim for the orchestration task (PG transport).
The PG queue is at-least-once: the orchestration task ``async_execute_bin``
can be redelivered (it runs longer than the consumer's visibility timeout and
a sibling replica re-claims the message) or delivered to two replicas. Re-
running the orchestration is destructive — it re-arms the barrier (resets
``remaining = N`` and wipes the ``pg_batch_dedup`` markers mid-flight),
dispatches a second set of N batch headers, and races the decrements. This
table is the durable single-winner token that closes that window: one row per
``execution_id``.
The worker claims by inserting its row atomically
(``INSERT … ON CONFLICT DO NOTHING RETURNING``): a fresh insert means "first
delivery — orchestrate"; a conflict means "already claimed — a duplicate /
redelivery → no-op". Claimed BEFORE the work; the claim is **released on
failure** (the orchestrator's error path deletes it) so a redelivery/retry can
re-orchestrate. It persists only after a SUCCESSFUL orchestration, as a
tombstone — deliberately kept past barrier finalise (unlike the per-batch
markers, which ``clear_execution_batches`` reclaims), so a redelivery AFTER
completion (when the barrier row is already gone) can't re-orchestrate a
finished execution.
**Recovery + GC (the reaper's ``sweep_orphan_claims``).** Unlike
``pg_batch_dedup`` — whose barrier is always armed before any batch runs, so
the reaper always has a ``pg_barrier_state`` handle — this claim is taken
BEFORE the barrier is armed, so a hard crash in the claim→arm window leaves a
claim with no barrier row that the barrier sweep can't see, and a successful
claim's tombstone has no natural GC. The reaper sweeps this table for claims
with **no matching ``pg_barrier_state`` row** older than the stuck-timeout: a
terminal execution → delete the claim (GC of a completed/failed tombstone); a
non-terminal execution → mark it ERROR (crash-window recovery) via the
org-scoped API, then delete. A claim younger than the stuck-timeout, or one
whose barrier is armed (live run), is left alone. This is why
``organization_id`` is stamped below — the reaper needs it for the org-scoped
status/mark API, exactly like ``pg_barrier_state``.
Managed=True / generated migration, extension-free — same posture
as the siblings.
"""
# One row per execution; the claim's ON CONFLICT target and the writer-proof
# single-winner invariant (the worker SQL can't import this model). Tombstone:
# deliberately never deleted at barrier finalise (there is no orchestration-
# claim analog of clear_execution_batches) — that is what blocks a
# post-completion redelivery from re-orchestrating. Only the reaper's
# sweep_orphan_claims deletes it (GC terminal / recover crash-window); a future
# maintainer must NOT add a finalise-time DELETE here.
execution_id = models.TextField(primary_key=True)
# Owning org, stamped at claim time. The reaper needs it to call the org-scoped
# internal status/mark API when recovering or GC'ing an orphan claim — it has
# only the execution_id off the row otherwise. "" = unknown (the reaper then
# can't mark and skips). Same no-NULL-text convention as PgBarrierState.
organization_id = models.TextField(blank=True, default="")
# When the orchestration was claimed. The reaper's sweep_orphan_claims gates on
# this column's age (only claims older than the stuck-timeout are swept, so a
# just-claimed live orchestration that hasn't armed its barrier is left alone).
# Deliberately UNINDEXED — a near-empty table swept at the 5-min retention
# cadence is a cheap seq scan; add an index if it ever grows.
claimed_at = models.DateTimeField(default=timezone.now)
class Meta:
db_table = "pg_orchestration_claim"