Discovery for CheckTool(Submit Repo)

[sagar-code-git]

Summary Description

Discovery: Check Tool (Submit Repo) Data Storage for Lookup , Endpoint and Source

Overview

Perform a deep-dive into the Check Tool within the Submit repository to understand how lookup source endpoints are persisted, cached, filtered, and surfaced in the system.

Repository: https://github.com/digital-land/submit

Objectives

• Map the database tables and schemas related to lookup source endpoints.
• Identify temporary storage mechanisms (in-memory, cache layer, etc.) used during record comparison if any.
• Analyze the code to pinpoint the functions and classes responsible for writing to and reading from the storage tables.

Acceptance Criteria

• Database Tables Analysis
  • Locate the table(s) storing lookup source endpoints.
  • Document each table’s columns, data types, and relationships.
• Entity Filtering Flow
  • Trace where incoming records are compared with lookup,endpoint and source file.
  • Document the findings.
• Codebase Storage Flow Analysis
  • Identify the modules, functions, and classes that handle persistence of lookup endpoint data.
  • Map the call flow from data fetch through to database insert/update.

[sagar-code-git]

Hi @eveleighoj @gmatchett ,

We have provided the analysis in the following document.

Analysis of Table Usage in the Submit Repository ( Check Tool ).docx

Conclusion

All referenced tables are used exclusively for read-only operations via SELECT queries. Datasette serves as the intermediary for database access, with configuration managed externally through YAML files with the url.
Docker Compose files confirm that Datasette is not hosted within the local development environment but connects to the request-db service externally.

Submit Repo calls Request-API service to submit data for validation and processing. The endpoint gets hit with the following type of payaloads for the types check_file and check_url
1)
{
"dataset": "conservation-area",
"collection": "conservation-area",
"geom_type": "value",
"uploaded_filename": "uploaded-file.csv",
"original_filename": "original-file.csv",
"type": "check_file"
}

{
"dataset": "conservation-area",
"collection": "conservation-area",
"geom_type": "value",
"url": "https://example.com/data.csv",
"type": "check_url"
}

We need to know if datasette tables are getting updated from payload response from the request api endpoint
We have added one more type which is add_data and currently new entities, exisiting entities, last row from endpoint.csv and last row from source.csv is getting updated in the response payload of the endpoint
We need to validate if the same data can be updated in the response_detail table and we need to know if that should be integrated with submit repo so that it can be updated in the datasette.

So basically we need to perform the impact analysis on the payload update functionality with the data present in the endpoint.csv, source.csv and lookup.csv details with datasette

[sagar-code-git]

@neilfwar , @Swati-Dash , @Ben-Hodgkiss , @eveleighoj , @gmatchett

Summary of the analysis on submit repo / check tool

Our submit-repo / Check Tool currently integrates with two backend services:

1. Datasette (Read-Only)

   • Executes only SELECT queries to fetch existing records
   • Does not have INSERT or UPDATE operations in submit repo

2. Request API

   • Exposes two methods—file_upload and endpoint_url—for submitting new data checks

Following our recent Async-Request-Backend enhancements, the tool now:

• Downloads lookup.csv, endpoint.csv, and source.csv
• Performs validation of new and existing entities
• Appends validated rows to the respective CSV files
• Validates new or existing endpoint url and appends those validated rows to CSV
• Renders the appended (or pending) rows in the UI for review

However, the Collecting and Managing Data Team cannot access the full CSVs, limiting their ability to conduct thorough validation.

Discussion Topics

1. How should new data be ingested into Datasette to enable write operations?
2. What mechanism will allow the Data Team to access the complete CSV files for validation?
3. How should we persist validated records in the response_details PostgreSQL table (lookup, endpoint, and source data fucntionalities), and what impact will this have on the Check Tool and Datasette integration?

[eveleighoj]

the submit service is read only except for posting requests to the async backend. You should not be editing. the submit repo at all. You work consists of coding across two repos:

• config-manager
• async-equestbackend

For any features you are adding you must consider:

• what changes to the config-manager repo need to be made (primarily changes focussed on the frontend and calling the async-request-processor for now)
• what changes need to be made to the async-request-processor if it's editing a task it should be adding functionality not changing the current behavior. or it can be adding a new task.

For data storage for you current tasks the request processor and the request api both have access to a sqlite db to store details including the entity response table.

You will not be making any changes to datasette in data for now.

[sagar-code-git]

Thanks Owen,
We could see there are these tables currently getting accessed by asynnc reqest backend

[sagar-code-git]

@eveleighoj
currently the data is getting saved in the following format in response table ( existing entities scenario)-

{
"new-entities": [],
"error-summary": [
"Reference column missing",
"Geometry column missing"
],
"entity-summary": {
"new-in-resource": 0,
"existing-in-resource": 0,
"new-entity-breakdown": [],
"existing-entity-breakdown": []
},
"column-field-log": [
{
"field": "reference",
"missing": true
},
{
"field": "geometry",
"missing": true
}
],
"new-entity-count": 0,
"existing-entities": [
{
"entity": "19160621",
"reference": "432_W1"
},
{
"entity": "19160622",
"reference": "444_W1"
},
{
"entity": "19160623",
"reference": "445_W1"
},
{
"entity": "19160624",
"reference": "446_W1"
},
{
"entity": "19160625",
"reference": "447_W1"
},
{
"entity": "19160626",
"reference": "448_W1"
},
{
"entity": "19160627",
"reference": "449_W1"
},
{
"entity": "19160628",
"reference": "442_W1"
},
{
"entity": "19160629",
"reference": "456_W1"
},
{
"entity": "19160630",
"reference": "465_W1"
},
{
"entity": "19160631",
"reference": "168_W1"
},
{
"entity": "19160632",
"reference": "515_W1"
},
{
"entity": "19160633",
"reference": "16012_W1"
},
{
"entity": "19160634",
"reference": "17027_W2"
},
{
"entity": "19160635",
"reference": "17027_W3"
},
{
"entity": "19160636",
"reference": "176_W1"
},
{
"entity": "19160637",
"reference": "315_W1"
},
{
"entity": "19160638",
"reference": "364_W1"
},
{
"entity": "19160639",
"reference": "378_W1"
},
{
"entity": "19160640",
"reference": "379_W1"
},
{
"entity": "19160641",
"reference": "398_W1"
},
{
"entity": "19160642",
"reference": "405_W1"
},
{
"entity": "19160643",
"reference": "409_W1"
},
{
"entity": "19160644",
"reference": "411_W1"
},
{
"entity": "19160645",
"reference": "421_W1"
},
{
"entity": "19160646",
"reference": "30_W1"
},
{
"entity": "19160647",
"reference": "193_W1"
},
{
"entity": "19160648",
"reference": "260_W1"
},
{
"entity": "19160649",
"reference": "17026_W1"
},
{
"entity": "19160650",
"reference": "175_W1"
},
{
"entity": "19160651",
"reference": "172_W1"
},
{
"entity": "19160652",
"reference": "17027_W1"
},
{
"entity": "19160653",
"reference": "3_G1"
},
{
"entity": "19160654",
"reference": "15_G1"
},
{
"entity": "19160655",
"reference": "79_G2"
},
{
"entity": "19160656",
"reference": "79_G3"
},
{
"entity": "19160657",
"reference": "152_G1"
},
{
"entity": "19160658",
"reference": "172_G3"
},
{
"entity": "19160659",
"reference": "172_G2"
},
{
"entity": "19160660",
"reference": "172_G4"
},
{
"entity": "19160661",
"reference": "172_G5"
},
{
"entity": "19160662",
"reference": "17027_G1"
},
{
"entity": "19160663",
"reference": "176_G1"
},
{
"entity": "19160664",
"reference": "176_G2"
},
{
"entity": "19160665",
"reference": "200_G1"
},
{
"entity": "19160666",
"reference": "86_G1"
},
{
"entity": "19160667",
"reference": "86_G2"
},
{
"entity": "19160668",
"reference": "79_G1"
},
{
"entity": "19160669",
"reference": "214_G3"
},
{
"entity": "19160670",
"reference": "214_G2"
},
{
"entity": "19160671",
"reference": "214_G1"
},
{
"entity": "19160672",
"reference": "213_G1"
},
{
"entity": "19160673",
"reference": "213_G2"
},
{
"entity": "19160674",
"reference": "213_G3"
},
{
"entity": "19160675",
"reference": "213_G4"
},
{
"entity": "19160676",
"reference": "368_G1"
},
{
"entity": "19160677",
"reference": "420_G2"
},
{
"entity": "19160678",
"reference": "17025_W1"
},
{
"entity": "19160679",
"reference": "272_G2"
},
{
"entity": "19160680",
"reference": "272_G1"
},
{
"entity": "19160681",
"reference": "279_G1"
},
{
"entity": "19160682",
"reference": "180_G1"
},
{
"entity": "19160683",
"reference": "17016_G2"
},
{
"entity": "19160684",
"reference": "17016_G1"
},
{
"entity": "19160685",
"reference": "327_G1"
},
{
"entity": "19160686",
"reference": "73_G1"
},
{
"entity": "19160687",
"reference": "339_G1"
},
{
"entity": "19160688",
"reference": "348_G1"
},
{
"entity": "19160689",
"reference": "349_G1"
},
{
"entity": "19160690",
"reference": "353_G1"
},
{
"entity": "19160691",
"reference": "172_G1"
},
{
"entity": "19160692",
"reference": "363_G1"
},
{
"entity": "19160693",
"reference": "42_G1"
},
{
"entity": "19160694",
"reference": "75_G1"
},
{
"entity": "19160695",
"reference": "77_G1"
},
{
"entity": "19160696",
"reference": "121_G1"
},
{
"entity": "19160697",
"reference": "121_G2"
},
{
"entity": "19160698",
"reference": "98_G1"
},
{
"entity": "19160699",
"reference": "98_G2"
},
{
"entity": "19160700",
"reference": "98_G3"
},
{
"entity": "19160701",
"reference": "104_G1"
},
{
"entity": "19160702",
"reference": "104_G2"
},
{
"entity": "19160703",
"reference": "118_G1"
},
{
"entity": "19160704",
"reference": "30_G1"
},
{
"entity": "19160705",
"reference": "270_G1"
},
{
"entity": "19160706",
"reference": "270_G2"
},
{
"entity": "19160707",
"reference": "270_G3"
},
{
"entity": "19160708",
"reference": "263_G1"
},
{
"entity": "19160709",
"reference": "227_G1"
},
{
"entity": "19160710",
"reference": "201_G1"
},
{
"entity": "19160711",
"reference": "128_G1"
},
{
"entity": "19160712",
"reference": "256_G3"
},
{
"entity": "19160713",
"reference": "256_G2"
},
{
"entity": "19160714",
"reference": "256_G1"
},
{
"entity": "19160715",
"reference": "154_G1"
},
{
"entity": "19160716",
"reference": "206_G1"
},
{
"entity": "19160717",
"reference": "206_G2"
},
{
"entity": "19160718",
"reference": "206_G3"
},
{
"entity": "19160719",
"reference": "262_G1"
},
{
"entity": "19160720",
"reference": "199_G1"
},
{
"entity": "19160721",
"reference": "177_G1"
},
{
"entity": "19160722",
"reference": "125_G1"
},
{
"entity": "19160723",
"reference": "125_G2"
},
{
"entity": "19160724",
"reference": "125_G3"
},
{
"entity": "19160725",
"reference": "125_G4"
},
{
"entity": "19160726",
"reference": "125_G5"
},
{
"entity": "19160727",
"reference": "89_G1"
},
{
"entity": "19160728",
"reference": "1_G3"
},
{
"entity": "19160729",
"reference": "1_G1"
},
{
"entity": "19160730",
"reference": "61_G2"
},
{
"entity": "19160731",
"reference": "61_G1"
},
{
"entity": "19160732",
"reference": "3_G2"
},
{
"entity": "19160733",
"reference": "270_A3"
},
{
"entity": "19160734",
"reference": "270_A5"
},
{
"entity": "19160735",
"reference": "70_A1"
},
{
"entity": "19160736",
"reference": "70_A2"
},
{
"entity": "19160737",
"reference": "355_A1"
},
{
"entity": "19160738",
"reference": "375_A1"
},
{
"entity": "19160739",
"reference": "460_A1"
},
{
"entity": "19160740",
"reference": "420_G1"
},
{
"entity": "19160741",
"reference": "460_G1"
},
{
"entity": "19160742",
"reference": "488_G1"
},
{
"entity": "19160743",
"reference": "499_G1"
},
{
"entity": "19160744",
"reference": "520_G1"
},
{
"entity": "19160745",
"reference": "520_G2"
},
{
"entity": "19160746",
"reference": "168_G1"
},
{
"entity": "19160747",
"reference": "511_G1"
},
{
"entity": "19160748",
"reference": "512_G1"
},
{
"entity": "19160749",
"reference": "517_G2"
},
{
"entity": "19160750",
"reference": "517_G1"
},
{
"entity": "19160751",
"reference": "524_G2"
},
{
"entity": "19160752",
"reference": "524_G1"
},
{
"entity": "19160753",
"reference": "530_G1"
},
{
"entity": "19160754",
"reference": "16006_G1"
},
{
"entity": "19160755",
"reference": "16015_G1"
},
{
"entity": "19160756",
"reference": "17022_G1"
},
{
"entity": "19160757",
"reference": "17022_G2"
},
{
"entity": "19160758",
"reference": "17025_G1"
},
{
"entity": "19160759",
"reference": "350_G1"
},
{
"entity": "19160760",
"reference": "25001_G1"
},
{
"entity": "19160761",
"reference": "25001_G2"
},
{
"entity": "19160762",
"reference": "25001_G3"
},
{
"entity": "19160763",
"reference": "25001_G4"
},
{
"entity": "19160764",
"reference": "270_A1"
},
{
"entity": "19160765",
"reference": "270_A2"
},
{
"entity": "19160766",
"reference": "270_A4"
}
],
"endpoint_url_validation": {
"url": "https://geoserver.leicester.gov.uk/geoserver/ODP/ows?service=WFS&version=1.0.0&request=GetF…",
"entry_date": null,
"new_source_entry": {
"source": "b5213b537466aa1f8404a9345e80433b",
"licence": "ogl",
"end-date": "",
"endpoint": "5fe3e5849302eef3282e3314f6fe88b6727998b81e24dd0131ce9e0ff42443b9",
"pipelines": "tree-preservation-zone",
"collection": "tree-preservation-order",
"entry-date": "2025-10-13T08:38:55.448528",
"start-date": "",
"attribution": "",
"organisation": "local-authority:LCE",
"documentation-url": "https://data.leicester.gov.uk/explore/?sort=modified"
},
"endpoint_csv_path": "/opt/pipeline/tree-preservation-zone/QRFZnvciVBuBiu2o5M4QHp/endpoint.csv",
"new_endpoint_entry": {
"--plugin": "",
"end-date": "",
"endpoint": "5fe3e5849302eef3282e3314f6fe88b6727998b81e24dd0131ce9e0ff42443b9",
"entry-date": "2025-10-13T08:38:55.448528",
"parameters": "",
"start-date": "",
"endpoint-url": "https://geoserver.leicester.gov.uk/geoserver/ODP/ows?service=WFS&version=1.0.0&request=GetF…"
},
"found_in_endpoint_csv": false
}
}

[sagar-code-git]

As per the discussion of yesterdays call please find the action items and the update status

Action Items

Action	Notes
Provide examples of current response and response_details table data
Conduct impact analysis on response_details table and creating a new celery worker task for add_data functionality

---

Response Details Table Analysis

The response_details table stores row-level processing information in the detail JSON column. Examples:

 id | response_id | detail
----+-------------+--------------------------------------------------------------------------------------------------------------------------
  1 |           1 | {"issue_logs": [], "entry_number": 1, "converted_row": {"<?xml version=\"1.0\" ?>": "<ServiceExceptionReport"}, "is_new_entity": false, "transformed_row": []}
  2 |           1 | {"issue_logs": [], "entry_number": 2, "converted_row": {"<?xml version=\"1.0\" ?>": "   version=\"1.2.0\""}, "is_new_entity": false, "transformed_row": []}

**Input Handling**  
- Depending on the request type:
-- For check_file, the uploaded file is processed.
-- For check_url, the file is downloaded from the URL and processed.
-- The file is saved to a temporary directory.
   if we post the payload like following 
{
  "params": {
    "type": "check_url",
    "dataset": "article-4-direction-area",
    "collection": "article-4-direction",
    "column_mapping": null,
    "geom_type": null,
    "documentation_url": "https://www.centralbedfordshire.gov.uk/info/44/planning/1909/planning_open_data",
    "licence": "ogl",
    "start_date": "2025-10-13",
    "organisation": "local-authority:CBF",
    "url": "https://www.centralbedfordshire.gov.uk/download/downloads/id/205/article_4_direction_area_dataset_csv.csv"
  }
}
After hitting the payload, it downloads the file from the URL
(https://www.centralbedfordshire.gov.uk/download/downloads/id/205/article_4_direction_area_dataset_csv.csv)
Store it in a temporary directory
The temporary directory is under 
request-processor/docker_volume/pipeline/article-4-direction-area/<request_id>/

Currently this file is not getting generated for the types types check_url and also for add_data due to which we are seeing the empty data in the response payload it is just creating xml like format for converted rows and issue logs are empty and transformed rows are empty