LLM-Driven Extraction of Unstructured Data — Built for API Deployments & ETL Pipeline Workflows
Python ai-agentsdata-engineeringdocument-aigenerative-aiidpjson-extractionllmmcp-serverocrpdf-extractionprompt-engineeringstructured-output
import uuid
from account_v2.models import User
from django.db import models, transaction
from django.utils import timezone
from utils.models.base_model import BaseModel
from utils.models.organization_mixin import DefaultOrganizationMixin
from agent_kv.constants import TABLE_EXTRACTOR_NAME, V1_EXTRACTOR_NAME
class AgentKVKey(DefaultOrganizationMixin, BaseModel):
id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
name = models.CharField(max_length=128)
description = models.CharField(max_length=512, blank=True, default="")
key = models.UUIDField(default=uuid.uuid4, unique=True)
is_active = models.BooleanField(default=True)
created_by = models.ForeignKey(
User,
on_delete=models.SET_NULL,
null=True,
related_name="agent_kv_keys_created",
)
modified_by = models.ForeignKey(
User,
on_delete=models.SET_NULL,
null=True,
related_name="+",
)
class Meta:
db_table = "agent_kv_key"
constraints = [
models.UniqueConstraint(
fields=["name", "organization"],
name="unique_agent_kv_key_name_per_org",
),
]
def __str__(self):
return f"{self.name} ({self.organization})"
class JobStatus(models.TextChoices):
PENDING = "PENDING"
DISPATCHED = "DISPATCHED"
RUNNING = "RUNNING"
COMPLETED = "COMPLETED"
FAILED = "FAILED"
CANCELLED = "CANCELLED"
class JobExtractor(models.TextChoices):
"""The extractor names a job row may record.
`status` has had `choices` since 0001 and `extractor` was the one
stringly-typed field without them -- so nothing but a reader's memory
connected the column to `EXTRACTOR_ROUTES`.
KV is listed even though this deployment refuses it: the column records
which extractor RAN, and rows written before the carve-out legitimately
say `kv`. Routability is `EXTRACTOR_ROUTES`' job, and these two sets are
deliberately not the same thing -- see `test_table_extractor_routing.py`.
"""
KV = V1_EXTRACTOR_NAME
TABLE = TABLE_EXTRACTOR_NAME
class AgentKVJob(DefaultOrganizationMixin, BaseModel):
TERMINAL = frozenset({JobStatus.COMPLETED, JobStatus.FAILED, JobStatus.CANCELLED})
id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
api_key = models.ForeignKey(
AgentKVKey,
on_delete=models.SET_NULL,
null=True,
related_name="jobs",
)
task_id = models.UUIDField(null=True, blank=True)
# Which extractor this job ran. v1 dispatches exactly one per job, but
# WHICH one is now a choice, and status/result key their payloads by it --
# without this column a table job's output would be filed under `kv`.
#
# NO DEFAULT, deliberately. Migration 0002 added this column with
# `default="kv"`, which was the historical truth at the time (the API
# accepted exactly one extractor and it was always `kv`) and is now a
# trap: a creation path that omits `extractor=` files a TABLE job as
# `kv`, and `kv` is a valid key in `STAGE_NAMES_BY_EXTRACTOR`, so
# `_status_document` hands back the KV stage list and silently drops
# `table_extraction` from every status response. The job runs, the caller
# is billed, and the stages array is empty with no warning logged --
# because nothing is wrong as far as the filter can tell.
#
# With no default, an omission is loud instead: `""` matches no route, so
# `dispatch_job` raises and the job terminalizes as FAILED with an error
# the caller can see, and `_status_document` logs the unknown-extractor
# warning. Which extractor ran is a fact about the job, not something with
# a sensible default.
extractor = models.CharField(max_length=32, choices=JobExtractor.choices)
status = models.CharField(
max_length=16,
choices=JobStatus.choices,
default=JobStatus.PENDING,
)
stage = models.CharField(max_length=32, blank=True, default="")
stages = models.JSONField(default=dict, blank=True)
pages_total = models.IntegerField(null=True, blank=True)
#: Which platform adapters this run was dispatched with, by role --
#: ``{"llm": "<uuid>", "lite_llm": "<uuid>", "x2text": "<uuid>"}``.
#:
#: Recorded because adapter choice is now a per-request, caller-controlled,
#: COST-BEARING decision (the `table` extractor runs on the submitter's own
#: adapters, not operator env credentials). Without it the ids reached
#: `executor_params` and nothing else: neither the status document nor
#: `usage_summary` reported them, so "which model did job X use?" -- the
#: first question in any billing dispute -- could only be answered by
#: joining `usage_v2` on `run_id`, which the API cannot do and a customer
#: cannot see at all.
#:
#: Empty for an env-configured extractor (`kv`), which is why it is a dict
#: with a `{}` default rather than three nullable columns: the two
#: credential models coexist, one per extractor.
#:
#: Ids only. Deliberately never `adapter_metadata` -- that is where the
#: provider credentials live, and this column is returned to the caller.
adapters = models.JSONField(default=dict, blank=True)
input_ref = models.CharField(max_length=512, blank=True, default="")
result_ref = models.CharField(max_length=512, blank=True, default="")
usage_summary = models.JSONField(null=True, blank=True)
error = models.TextField(blank=True, default="")
dispatched_at = models.DateTimeField(null=True, blank=True)
completed_at = models.DateTimeField(null=True, blank=True)
expires_at = models.DateTimeField(null=True, blank=True)
# When TTL cleanup last failed to delete one of this job's files. NULL means
# "never attempted, or last attempt succeeded", and it is what separates
# the two lanes run_ttl_cleanup processes: NOT NULL rows are retries, which
# get a reserved slice of each batch, and NULL rows are new expirations,
# which get the rest. Capping both is what stops either side starving the
# other. See run_ttl_cleanup.
cleanup_failed_at = models.DateTimeField(null=True, blank=True)
# Set once this job's stored files have been destroyed on purpose: by an
# explicit `DELETE /agent-kv/{job_id}` or by the TTL sweep. One-way, and
# written UNCONDITIONALLY by both of those paths -- before the deletes are
# attempted, and regardless of whether any individual delete succeeded.
#
# It exists because purge state CANNOT be inferred from `input_ref` /
# `result_ref`, and three reverted attempts to do so are the evidence. Those
# two columns carry three different meanings at once:
#
# * blank -- nothing was ever staged/produced, or
# * blank -- it was staged/produced and then purged, or
# * SET -- it was purged but the object delete FAILED, so the ref is
# deliberately kept as the handle `run_ttl_cleanup` retries
# from (see `storage.delete_job_files`).
#
# So neither "the ref is blank" nor "the ref is set" tells you whether the
# caller asked for this job's data to be destroyed. This column does, and
# it is the only thing that does.
#
# Two readers depend on that:
#
# * `attach_result_to_cancelled` refuses to attach a late result to a
# purged job -- otherwise data the caller DELETEd becomes readable again.
# * `JobResultView` refuses to SERVE a purged job's result even when a ref
# survived a failed delete, which is the case a ref-based check cannot
# see at all.
#
# Deliberately NOT "the files are gone": a failed delete leaves this True
# with the object still in the bucket and its ref still set, which is
# exactly what lets TTL cleanup retry while the API already treats the job
# as purged. "The caller asked for this to be destroyed" is a fact about the
# request, so it is recorded as one instead of being re-derived later.
files_purged = models.BooleanField(default=False)
tags = models.JSONField(default=list, blank=True)
custom_data = models.JSONField(null=True, blank=True)
webhook_url = models.URLField(max_length=1024, blank=True, default="")
class Meta:
db_table = "agent_kv_job"
indexes = [
models.Index(fields=["organization", "status"]),
models.Index(fields=["expires_at"]),
# Serves BOTH of run_ttl_cleanup's lanes, each of which sorts by a
# single column ascending behind a predicate on this index's
# leading column:
# retries: WHERE cleanup_failed_at IS NOT NULL ORDER BY cleanup_failed_at
# fresh: WHERE cleanup_failed_at IS NULL ORDER BY expires_at
# Deliberately a plain ascending index. The first version of that
# query asked for `cleanup_failed_at ASC NULLS FIRST`, which a
# btree index cannot serve (btree is NULLS LAST ascending), so
# Postgres sorted every matching expired row before applying the
# 500-row limit -- work that grew with the backlog. Splitting the
# query removed the NULLS FIRST rather than adding a second index
# with a non-default null order.
models.Index(fields=["cleanup_failed_at", "expires_at"]),
]
@classmethod
def mark_files_purged(cls, job_id, organization_id):
"""Record that this job's files are being destroyed; return the row as
it is NOW.
Two jobs in one call, and they have to be one call:
1. Set ``files_purged`` -- UNCONDITIONALLY, before any delete is
attempted. It records the caller's intent, not the outcome, so a
delete that fails must not leave it False (see the field's comment).
2. Hand back the row read under ``SELECT ... FOR UPDATE``, so the caller
deletes the files that actually exist rather than the ones its own
earlier read saw.
**Why the lock.** ``attach_result_to_cancelled`` can write a
``result_ref`` onto a CANCELLED row at any moment. Without serializing
the two, this interleaving loses:
purge reads the row -> result_ref ""
attach commits -> result_ref set, file written
purge sets files_purged
purge deletes files from its STALE copy -> only the input
purge blanks the refs it cleared
which leaves a result file in the bucket that the purge never deleted,
on a row whose ref it then blanked -- so TTL cleanup (whose candidate
filter is ``input_ref > "" OR result_ref > ""``) can never reconsider
it, and extracted customer data outlives its retention window with
nothing pointing at it. Taking the row lock here, and taking it in
``attach_result_to_cancelled`` too, makes the two mutually exclusive:
either the attach committed BEFORE this read (so its ref is in the row
returned, and its file is deleted with everything else) or it runs
after this commit (so it sees ``files_purged=True``, matches nothing,
and its caller deletes the file it just wrote). There is no third
interleaving.
The lock is held only for the marker write -- the object-store deletes
happen after it is released, on the row returned here. Blocking a
concurrent attach for one UPDATE is the point; blocking it for the
duration of a bucket round-trip is not.
Returns the row, or ``None`` if it no longer exists (job rows are
retained for the audit trail, so a caller that just read one can treat
``None`` as unreachable and fall back to its own copy).
"""
with transaction.atomic():
row = (
cls.objects.select_for_update()
.filter(id=job_id, organization_id=organization_id)
.first()
)
if row is None:
return None
cls.objects.filter(id=job_id, organization_id=organization_id).update(
files_purged=True
)
row.files_purged = True
return row
#: Every condition a late result must still satisfy to be grafted onto a
#: cancelled job. Declared once and used twice: by the authoritative
#: filtered UPDATE in :meth:`attach_result_to_cancelled`, and by
#: :meth:`can_attach_result`, which ``FinalizeView._salvage`` calls BEFORE
#: it writes anything to object storage.
#:
#: One definition because the two must not drift. A pre-flight looser than
#: the guard writes files the guard then refuses -- the orphan path. One
#: tighter silently drops results the guard would have kept, which is the
#: bug this whole salvage exists to fix.
ATTACH_GUARD = {
"status": JobStatus.CANCELLED,
"result_ref": "",
"files_purged": False,
}
@classmethod
def can_attach_result(cls, job_id, organization_id) -> bool:
"""Whether :meth:`attach_result_to_cancelled` could succeed right now.
Advisory, never authoritative: the row can change between this read
and the guarded UPDATE, which is why the UPDATE keeps its own copy of
every clause. What this buys is not correctness but restraint -- a
callback redelivered after the result is already attached, or arriving
for a job the caller has DELETEd, is refused before a result file is
written for it. Those are the ordinary cases under at-least-once
delivery, and each one used to write a file to object storage and then
rely on a best-effort delete to take it back out again.
"""
return cls.objects.filter(
id=job_id, organization_id=organization_id, **cls.ATTACH_GUARD
).exists()
@classmethod
def attach_result_to_cancelled(
cls, job_id, organization_id, *, result_ref, usage_summary=None
) -> bool:
"""Keep a paid-for result on a job that was CANCELLED mid-flight.
The race this exists for: extraction completes -- OCR and LLM already
spent and flushed by the engine -- and only then does the executor's
final stage report land, by which time a cancel has terminalized the
row. The executor returns success with the full output, and before this
it went nowhere: ``FinalizeView.post`` short-circuited on the
already-terminal row and never wrote it (the ordinary case,
``FinalizeView._salvage``), or wrote it and then DELETED it as an orphan
when ``mark_terminal`` correctly declined to restatus a terminal row
(the narrow case, ``FinalizeView._complete``). Either way the customer
was billed for an answer that was destroyed, with ``GET result``
reporting only ``{"success": false, "status": "cancelled"}`` and one
INFO line.
Deliberately NOT part of ``mark_terminal``, and deliberately unable to
change ``status`` or ``completed_at``: the cancel stays won and stays
visible. This adds a pointer to work that was already paid for, nothing
more -- ``mark_terminal`` remains the only way to reach a terminal
state. ``result_payload`` then serves the data alongside
``status: "cancelled"``.
The guard is one filtered UPDATE, and every clause in it earns its place:
* ``status=CANCELLED`` -- a COMPLETED or FAILED row already owns its
outcome, and grafting a late callback's result onto it would either
overwrite a real result or invent one for a run that failed.
* ``result_ref=""`` -- idempotency under at-least-once delivery. A
second callback matches nothing, returns False, and its caller deletes
the file IT just wrote instead of orphaning it.
* ``files_purged=False`` -- the purge guard. A job the caller DELETEd
(or one the TTL sweep sent through) must never get a result back;
doing so makes purged customer data readable again. **This cannot be
expressed with the ref columns** -- that was attempt 1 (``result_ref``
alone: DELETE blanks the refs and leaves exactly the row the filter
matched) and attempt 2 (plus ``input_ref > ""``: unsound, because
``delete_job_files`` deliberately KEEPS a ref whose delete failed, so
a non-blank ``input_ref`` does not prove the job was never purged).
See the ``files_purged`` field comment.
``select_for_update`` around it is what stops ``mark_files_purged``
interleaving between this guard and its write -- the full argument is in
that method's docstring. Both sides must take the lock for either to be
safe.
Returns True when this call attached the result (and the caller must
therefore keep the file), False when it did not (caller deletes it).
"""
if not result_ref:
return False
fields = {"result_ref": result_ref}
if usage_summary is not None:
fields["usage_summary"] = usage_summary
with transaction.atomic():
# Locked by identity only; the guard itself lives in the UPDATE
# below so there is exactly one copy of it, in SQL.
locked = (
cls.objects.select_for_update()
.filter(id=job_id, organization_id=organization_id)
.first()
)
if locked is None:
return False
return bool(
cls.objects.filter(
id=job_id, organization_id=organization_id, **cls.ATTACH_GUARD
).update(**fields)
)
@classmethod
def mark_terminal(
cls,
job_id,
organization_id,
new_status,
*,
error="",
result_ref="",
usage_summary=None,
require=None,
) -> bool:
"""The ONLY way to reach a terminal state (spec §5.4 write guard).
Guarded UPDATE: at-least-once callbacks, cancel, and the sweep can all
race; whoever lands first wins and everyone else no-ops.
``require`` adds caller-supplied filter kwargs to that same UPDATE, for
a caller whose decision rests on MORE than "the row is not terminal".
The sweep is the case: it picks candidates with one query and
terminalizes them with another, and between the two a delayed dispatch
can turn a long-PENDING row into a live DISPATCHED one. Without the
extra clauses this would fail a running job and release its slot while
the executor carries on spending -- and its finalize could not save the
result, because the row is FAILED by then and only a CANCELLED row gets
a second look. Re-stating the selection predicate in the write is what
closes that window; it costs nothing when nothing raced.
The `.exclude(status__in=TERMINAL)` below guards the ROW, not the
argument -- so without the check that opens this method,
`mark_terminal(..., JobStatus.RUNNING)` would stamp
`status=RUNNING, completed_at=now()`: a row that reads as finished to
every TTL/sweep query that keys off `completed_at`, is invisible to
the terminal guard, and can never be terminalized again by anything
that trusts `completed_at`. No caller does this today; the method is
named for the invariant, so it enforces it rather than documenting it.
"""
if new_status not in cls.TERMINAL:
raise ValueError(
f"mark_terminal called with non-terminal status {new_status!r}; "
f"expected one of {sorted(cls.TERMINAL)}"
)
fields = {"status": new_status, "completed_at": timezone.now()}
if error:
fields["error"] = error
if result_ref:
fields["result_ref"] = result_ref
if usage_summary is not None:
fields["usage_summary"] = usage_summary
rows = cls.objects.filter(id=job_id, organization_id=organization_id)
if require:
rows = rows.filter(**require)
updated = rows.exclude(status__in=list(cls.TERMINAL)).update(**fields)
return updated == 1