Classify the documents already in ADM

By the end of this lesson, you can classify the documents that were in ADM before the classifier existed.

Lesson 5 of 5~20 minScript: 05_skip_folder.sql, 05_backfill.sql, 99_hkd_ai_uninstall.sql

Why a backfill needs an estimate and batches

The upload hook sees new files only. An ADM installation that has run for a while holds many documents that the classifier has never seen. Sending all of them at once has two risks. You do not know the cost before you start. A burst of requests can reach the rate limit of the provider. This lesson continues in the schema of lesson 4. A backfill is the classification of documents that were in ADM before the classifier.

The package hkd_ai_backfill_api solves both. It counts the documents that have no verdict, estimates the tokens, and queues the documents in small batches. It queues the event AI_RECLASSIFY, which the worker handles like any other event:

procedure estimate (
  p_folder_path         in  varchar2 default null
, po_documents          out number
, po_prompt_tokens      out number
, po_completion_tokens  out number
);

function enqueue_unclassified (
  p_batch_size  in number default 20
, p_folder_path in varchar2 default null
, p_max_queued  in number default 40
) return number;

function enqueue_outdated (
  p_before      in timestamp with local time zone
, p_folder_path in varchar2 default null
, p_batch_size  in number default 20
, p_max_queued  in number default 40
) return number;

The package reads its candidates from the view hkd_ai_candidates_v: PDFs and images up to 20 MB, outside folders marked ai.skip, with no event in the queue. Run @05_backfill.sql once. It installs the package and runs the whole backfill for a sample. It sends one request for each of twelve files, plus three for the new run at the end. The script uses the ADM user ADMIN and the folder /users/admin/hkd_archive. It loads each sample file a second time. After that, the scripts of lessons 1 to 4 find two documents with the same name, and they fail or show doubled rows. Run this lesson last, and run it once. To start again, run the uninstall script first.

Keep a folder away from the model

A folder with the annotation ai.skip, and every folder below it, is never sent to the model. This holds for an upload and for the backfill. The classifier checks the folder of the document before it reads the file. Run @05_skip_folder.sql. It creates the folder hkd_private, adds the annotation, and uploads a file into the folder. The script does nothing when the folder exists already. The annotation is one call:

adm_annotations_api.add_folder_annotation(l_folder_id, 'ai.skip', 'confidential');

The queue is empty (waited 0 s).
WORKFLOW_CODE         STATUS     SUMMARY
_____________________ __________ ______________________________________________________________________
DUPLICATE_SCAN        OK         Unique content, 40 B
AI_CLASSIFY_UPLOAD    SKIPPED    The folder is marked ai.skip, so the file was not sent to the model

The workflow ran and ended with SKIPPED. The file stays in ADM and no model call took place. The view that the backfill reads leaves folders with ai.skip out as well.

Simulate documents from before the classifier

An upload runs the hook. An administrator or the system user can skip the hook for one call with p_run_hooks => false. The loader takes a folder and the word NOHOOKS for this. The script loads the sample set into the folder hkd_archive without the hook, so the twelve files look like documents from before the classifier:

  loaded: inv_ferrotek_1042.pdf (document 16532250424757552382655672450742)
  loaded: inv_nordlicht_2207.pdf (document 3505197327933218149248754530171)
  loaded: supply_agreement_ferrotek.pdf (document 12758853842855151721022220695903)
  loaded: quote_alpenwerk_q5531.pdf (document 13500394441754317332853090692084)
  loaded: datasheet_pump_x200.pdf (document 1109485622961992144227879508827)
  loaded: letter_employment_change.pdf (document 17372468280846636673429416323930)
  loaded: scan_fax_amendment.pdf (document 2154689621302980185982674808748)
  loaded: proforma_alpenwerk_pf5531.pdf (document 2872661599613481387303473323518)
  loaded: reminder_ferrotek_1042.pdf (document 19143980410709036290350456230635)
  loaded: delivery_note_nordlicht_ls2207.pdf (document 3266816989298098755649946781191)
  loaded: memo_offsite_menu.pdf (document 1543844119469308620614648224392)
  loaded: note_parking_rules.pdf (document 13078309373080456975170482328446)
The queue is empty (waited 0 s).
   DOCUMENTS
____________
          12
   CLASSIFIED
_____________
            0

Twelve documents exist in the folder, and none has a verdict.

Estimate the tokens before you start

hkd_ai_backfill_api.estimate('/users/admin/hkd_archive', l_documents, l_prompt, l_completion);

The estimate is the number of candidate documents with no verdict, times the mean tokens of all verdicts so far, reruns included. A document with a FAILED verdict counts as classified. Before the first verdict there is no mean, and the estimate is 0 tokens. In this lesson the mean comes from the verdicts of the earlier lessons, which are the same twelve files, so the estimate is close by construction. On a real installation, classify a small batch first and estimate after that.

Documents to classify:  12
Estimated input tokens:  4946
Estimated output tokens: 498

A scanned file with many pages needs more tokens than a one-page text file, so the estimate is a mean and not a limit. Multiply the tokens by the price of your provider to get a cost. This course does not state prices.

Queue the documents in batches

loop
  l_queued := hkd_ai_backfill_api.enqueue_unclassified(p_batch_size => 5, p_folder_path => '/users/admin/hkd_archive');
  commit;
  exit when l_queued = 0;
  -- wait until the worker has handled the batch
end loop;

A call queues at most five documents, and none while 40 events wait already. A document leaves the list when it has a verdict or an event in the queue. A call after an interruption therefore continues where the last one stopped. Each queued event can start a model request, so keep the batch small enough for the rate limit of your provider. The loop in the script stops after 20 batches. A document whose event ended in the exception queue has no verdict and no event in the queue. It comes back on the list in every batch. The limit keeps the loop from running for ever in that case.

Batch 1: queued 5 documents
Batch 2: queued 5 documents
Batch 3: queued 2 documents
Nothing left to classify.

Compare the estimate with the tokens that the run used

   DOCUMENTS    PROMPT_TOKENS    COMPLETION_TOKENS
____________ ________________ ____________________
          12             5219                  429

The run used 5219 input tokens and 429 output tokens for twelve documents. The estimate was 4946 input tokens, which is 5 percent low, and 498 output tokens, which is 16 percent high. The estimate is close here because its mean comes from the same twelve files, so this comparison proves little. On new documents, the gap can be larger. A set with many scans needs a larger margin: the scan in lesson 2 used 778 input tokens, and the text files used 316 to 450. Use the estimate to plan, and read the real figures after the first batches.

Classify again after a change

When you reword a type description (lesson 2), the verdicts that exist do not change. enqueue_outdated queues the documents whose newest verdict is older than a date. A verdict that a person accepted, and a release that a person took back, stay for the current version:

where v.created_at < p_before
  and not (v.status in ('ACCEPTED', 'REVOKED') and v.version_id = c.latest_version_id)

The script takes the date once, queues three documents and lists the documents that have two verdicts now. Take the date once for a whole backlog. A verdict from a run after that date is not older than it, so each call continues with the next documents. Run the call again for the next three. This recording is the first call:

Queued 3 documents for a new run
The queue is empty (waited 15 s).
DOCUMENT_NAME                       VERDICTS
________________________________ ___________
inv_ferrotek_1042.pdf                      2
inv_nordlicht_2207.pdf                     2
supply_agreement_ferrotek.pdf              2

The call queued the three oldest verdicts in the folder hkd_archive, because the script passes p_folder_path. The last result set shows the three documents with two verdicts. The other documents are unchanged.

Use the classifier on your own documents

The sample files are only a start. To classify your own documents, make these changes in a test schema first:

Add the document types that your documents need. A new type releases automatically unless you set REVIEW, so choose the rule on purpose:

insert into hkd_ai_doc_types (doc_type, description, release_mode, sort_order)
values ('report', 'A periodic report with figures and a summary', 'REVIEW', 60);

Mark the folders whose files must stay in the database with the annotation ai.skip, as shown above.
Upload a document into ADM. Run @wait.sql, then read the verdict from hkd_ai_latest_v.
When the verdict is wrong, add the file and its expected type to the table hkd_ai_labels, and compare labels and verdicts with the query of 02_measure.sql. Use file names that no other document has, because the query joins on the name. It lists the sample files too.
When the numbers look right, run the backfill of this lesson on one folder at a time with p_folder_path.

Remove the course objects

Run @99_hkd_ai_uninstall.sql when you finish. It deletes the verdicts, the labels and the types, and the annotations that the classifier wrote on the documents it classified (ai.status, ai.model, ai.run_date and kb.<type>). It deletes the collections of lesson 4. It drops the two packages and the views and deletes the three workflows. It enables INTAKE_CLASSIFY again, also when it was off before the install. It moves the folders hkd_inbox, hkd_archive and hkd_private with their documents to the trash. Your own annotations stay. The tags stay too, and so do the runs in hkd_workflow_runs. The hooks demo keeps working. Its own uninstall script, db/99_hkd_uninstall.sql, removes that.

Hooks demo appRead about the pages of the demo, including the page AI Review, and how to add a workflow of your own.