Skip to content

API Logical ER Design

Here is an entity-relationship diagram for the logical design underlying the backend API.

erDiagram
    ASSESSMENT {
      string name
    }
    DOCUMENT {
      string name
    }
    BATCH {
      string name
    }
    PROCESSOR {
      string name
      string description
    }
    BATCH-RUN {
      datetime started_at
      datetime ended_at
      BatchRunStatus status
    }
    SCHEMA {
      SchemaField[] fields
    }
    EXTRACTED-ITEM {
      string type_name
      boolean validated
    }
    EXTRACTED-FIELD {
      string name
      string value
      float confidence
    }
    CORRECTION {
      string field_path
      CorrectionMethod method
      string user_provided_data
      datetime created_at
      datetime updated_at
    }
    BATCH-DOCUMENT {
    }
    DOCUMENT-PROCESSING-STATUS {
      DocumentStatus status
      datetime started_at
      datetime finished_at
      string error_message
    }
    ASSESSMENT ||--o{ DOCUMENT : contains
    ASSESSMENT ||--o{ BATCH : contains
    BATCH ||--o{ BATCH-DOCUMENT : contains
    BATCH-DOCUMENT }o--|| DOCUMENT : references
    PROCESSOR }o..|| SCHEMA : extracts
    PROCESSOR }o..|| EXTRACTOR : uses
    BATCH ||--o{ BATCH-RUN : run-by
    BATCH-RUN }o..|| PROCESSOR : extracts-with
    DOCUMENT ||--o{ EXTRACTED-ITEM : yields
    EXTRACTED-ITEM }o..|| BATCH-RUN : extracted-by
    EXTRACTED-ITEM ||--o{ EXTRACTED-FIELD : contains
    EXTRACTED-ITEM ||--o{ CORRECTION : corrected-by
    DOCUMENT ||--o{ DOCUMENT-PROCESSING-STATUS : tracks
    BATCH-RUN ||--o{ DOCUMENT-PROCESSING-STATUS : tracks

A few notes on the diagram:

  • an ASSESSMENT is the top-level organizational unit; DOCUMENTs belong to exactly one ASSESSMENT, and each BATCH is associated with an ASSESSMENT (via assessment_id in the request body when creating a batch at POST /batches)
  • a DOCUMENT uniquely identifies an uploaded document
  • a BATCH is a named set of documents we can process
  • a PROCESSOR is a procedure for extracting data conforming to a particular SCHEMA using a particular EXTRACTOR
  • a BATCH-RUN has an associated PROCESSOR
  • creating a BATCH-RUN starts one or more long-running computations to extract data from the DOCUMENTs in the associated BATCH
  • different BATCH-RUNs for the same BATCH can use different PROCESSORs, and so extract different types of data, or extract the same type of data, but using a different EXTRACTOR
  • when a BATCH-RUN executes, it creates instances of EXTRACTED-ITEM, each of which carries extracted data associated with a particular document. A single DOCUMENT may have many EXTRACTED-ITEMs that arise from a single BATCH-RUN
  • each EXTRACTED-ITEM has zero or more EXTRACTED-FIELDs (the actual key-value extracted data) and zero or more CORRECTIONs that correct parts of the extracted data in the item
  • DOCUMENT-PROCESSING-STATUS tracks each document's progress within a BATCH-RUN (e.g. queued, processing, completed, failed)

And a few rules for code derived from the diagram:

  • each entity type will have a derived internal Pydantic model that represents a single instance of the entity type. Such a model will have
    • a numeric id field
    • zero or more x_id fields for related entities; each such field represents a 1:1 or many:1 relationship with another entity type
  • each entity type can also have a representation used in the API response models
    • these representations for the API will be the same as the internal models, except that there are no x_id fields representing relationships
  • when there is a many:many relationship between two entity types E1 and E2, there will be an internal domain model that represents the relationship itself, and which you can think of as an ordered pair (e1_id, e2_id) containing the IDs of an instance of E1 and an instance of E2

Cascade deletion behavior

When deleting entities, the following cascade rules apply:

  • Deleting an ASSESSMENT cascades deletion to all its DOCUMENTs
  • Deleting a DOCUMENT cascades deletion to:
    • All EXTRACTED-ITEMs for that document
    • All BATCH-DOCUMENT associations
    • Document content/file storage
  • Deleting an EXTRACTED-ITEM cascades deletion to all its CORRECTIONs

All delete operations are idempotent: deleting an entity that does not exist returns success (HTTP 204) rather than an error.

API endpoints

Assessments

Method-Path Description
GET /assessments List assessments
GET /assessments/{id} Get assessment details
POST /assessments Create a new assessment (multipart: name, reporting_year, customer_identifier, files)
DELETE /assessments/{id} Delete an assessment and cascade to related records
POST /assessments/{id}/documents Upload additional documents to an assessment
DELETE /assessments/{id}/documents/{doc_id} Remove a document from an assessment
POST /assessments/{id}/documents/{doc_id}/rerun Rerun extraction for a specific document
GET /assessments/{id}/completeness Get assessment document completeness status
GET /assessments/{id}/document-processing-details Get processing status for all documents in an assessment

Documents

Method-Path Description
POST /documents/validate Pre-upload file validation (format, type classification)
GET /documents Get a list of documents
GET /documents/{id} Get the details for a particular document
POST /documents Upload one or more files to make new documents
DELETE /documents/{id} Delete a document
GET /documents/{id}/content Stream document file content
GET /documents/{id}/extracted-items Get a list of extracted data for a document
GET /documents/{id}/corrections Get corrections for a document
POST /documents/{id}/extracted-items/{item_id}/validate Validate a specific extracted item
POST /documents/{id}/extracted-items/validate-all Validate all extracted items for a document
DELETE /documents/{id}/extracted-items/{item_id} Remove an extracted item for that document (404 if missing or wrong document; analyst hard-delete; extractor soft-deactivate)
POST /documents/{id}/export/{exporter_name} Export extracted data via a named exporter

Processors

Method-Path Description
GET /processors Get a list of available processors (extractor × schema combinations)
GET /processors/{id} Get the details of a particular processor (includes embedded schema and extractor)

Batches

Method-Path Description
GET /batches Get a list of batches
GET /batches/{id} Get the details of a particular batch
POST /batches Create a new batch
DELETE /batches/{id} Delete a batch
POST /batches/process Create batch from assessment's eligible documents and auto-resolve runs
GET /batches/{id}/runs Get a list of the batch runs for a batch
GET /batches/{id}/runs/{run_id} Get the details of a particular run for a batch
POST /batches/{id}/runs?processor_id=... Start a new run for a batch (processor selected via query parameter)
DELETE /batches/{id}/runs/{run_id} Delete a batch run
POST /batches/{id}/runs/auto Auto-resolve and create runs by document classification
GET /batches/{id}/runs/{run_id}/documents/{doc_id}/status Get document processing status within a run

Batch Documents

Method-Path Description
GET /batch-documents List batch-document associations
PUT /batch-documents/{id} Associate a document with a batch
DELETE /batch-documents/{id} Remove a document from a batch

Corrections

Method-Path Description
POST /extracted-items/{item_id}/corrections Create a new correction for an extracted item
GET /extracted-items/{item_id}/corrections Get the corrections for an extracted item
GET /extracted-items/{item_id}/corrections/{corr_id} Get the details of a particular correction
PUT /extracted-items/{item_id}/corrections/{corr_id} Update a correction

Interpretating the diagram

There are a few rules for interpreting the diagram.

Nouns in the REST API correspond to entities, sometimes relationships

  • only entities have identity
  • if something has identity it means
    • it's got a unique ID; assume there is a string or integer-valued field on the entity called id
    • it is a candidate for direct manipulation through the REST API

For example, in the diagram above, ASSESSMENT, DOCUMENT, BATCH and BATCH-RUN are entities. That means you might expect REST endpoints like

GET /assessments
GET /assessments/{id}
POST /assessments
DELETE /assessments/{id}

GET /documents
GET /documents/{id}
POST /documents
PUT /documents/{id}
DELETE /documents/{id}

GET /batches
GET /batches/{id}
POST /batches

Here every BATCH-RUN entity has exactly one associated BATCH via the run-by relationship. Because of this, you might expect an API endpoint at a path below /batches/{id} that lets you see or manipulate all the BATCH-RUNs associated with the BATCH identified by ID id:

GET /batches/{id}/runs
GET /batches/{id}/runs/{run_id}
POST /batches/{id}/runs

The POST starts a new BATCH-RUN for the batch, maybe with a new EXECUTOR different from the ones used in the previous BATCH-RUNs for the same BATCH.

Here every DOCUMENT entity has exactly one associated ASSESSMENT via the contains relationship. Because of this, you might expect an API endpoint at a path below /assessments/{id} that lets you see or manipulate all the DOCUMENTs associated with the ASSESSMENT identified by ID id:

GET /assessments/{id}          # returns assessment with its documents
POST /assessments/{id}/documents
DELETE /assessments/{id}/documents/{doc_id}

Similarly, every BATCH is associated with exactly one ASSESSMENT, but unlike DOCUMENTs, batches are not nested under an assessment path. Instead they live at the top level (POST /batches, POST /batches/process) and carry assessment_id in the request body.

The many-to-many relationship between BATCH and DOCUMENT is modelled as the BATCH-DOCUMENT junction entity, a first-class resource with its own identity and endpoints (GET /batch-documents, PUT /batch-documents/{id}, DELETE /batch-documents/{id}). Here we should think of batch-documents as representing the relationship between a batch and its documents, rather than a subordinate type of either entity. Contrast this with /batches/{id}/runs, where we are dealing with a truly subordinate entity: a BATCH-RUN cannot exist without an associated BATCH.

There can be rich data without identity

You'll notice that SCHEMA is an entity, but within it there is a list of SchemaField objects. However, there is no FIELD entity. That means that fields do not have identity: the value of fields in a SCHEMA is just structured data. This further suggests that there is no API of the form

GET /schemas/{id}/fields

but instead the set of fields arrives as data in the result of

GET /schemas/{id}

Similary, the EXTRACTED-ITEM entity includes a list of ExtactedField objects. Once again, ExtractedField is not an entity, so you would not expect to see an API endpoint like

GET /documents/{id}/extracted-item/{item_id}/extracted-fields

but intead all of the extracted fields arrive in the result of

GET /documents/{id}/extracted-item/{item_id}

Options for querying

You'll noticed that there is a chain of x-to-1 relationships that goes

  • from EXTRACTED-ITEM to BATCH-RUN
  • from EXTRACTED-ITEM to DOCUMENT
  • from BATCH-RUN to PROCESSOR
  • from PROCESSOR to SCHEMA
  • from PROCESSOR to EXTRACTOR
  • from BATCH-RUN to BATCH

This suggests, for example, that the API endpoint

GET /extracted-items

could take query parameters limiting the results to

  • a particular BATCH-RUN: get all the items for the run
  • a particular DOCUMENT: get all items for that document, over all BATCH-RUNs in which it was processed
  • a particular BATCH: get all the items for all of the BATCH-RUNs for the BATCH
  • a particular PROCESSOR, or SCHEMA, or EXTRACTOR: get all the items that were produced by a particular processor, or for processors that used a particular schema or extractor

It also suggests that

GET /documents/{id}/extracted-data

could take all the same query parameters besides the one for a document.

Which of these we support depends on whether we think users would find them useful.

General API considerations: Pagination

Sheldon asked about pagination and I think that although we may not need pagination for the MVP, we should set down a couple of thoughts:

  1. in general, any API of the form GET /[.../]<plural-noun> should potentially be paginated
  2. we should adopt a general scheme for pagination-related fields in response bodies, and pagination-releated query-parameters
  3. we should adopt some well-worn approach to pagination (e.g. there is a typical way to do this for a query from an RDBMS) and aim to support it

However, we won't address those particular design details here in this document.