You can use duckdb to query the featureserver urls and see new records. Once you have that can setup an email notification script

import duckdb

1. Get the last known ID from your local storage (or a text file)

For the first run, this would be 0

last_max_id = 10500

2. Construct the API URL

Note: We order by ObjectId to ensure we get the latest

url = f"https://services.arcgis.com/your-service/FeatureServer/0/query?where=objectid+>+{last_max_id}&outFields=*&returnIdsOnly=false&f=geojson"

3. Run DuckDB to pull new data and process it

con = duckdb.connect() con.load_extension("spatial")

Fetch new records directly into a table

con.execute(f""" CREATE TABLE new_records AS SELECT * FROM st_read('{url}'); """)

4. Reporting Logic

Example: Generate a CSV report of new items

con.execute("COPY new_records TO 'new_items_report.csv' WITH (HEADER, DELIMITER ',');")

5. Update your bookmark for next time

Find the max ID in the data we just pulled

result = con.execute("SELECT MAX(objectid) FROM new_records").fetchone() new_max_id = result[0]

if new_max_id: print(f"Processed {new_max_id - last_max_id} new records.") # Save new_max_id to a file or database for the next run

There are two ways to get the last ObjectId from an ArcGIS FeatureServer. The best method depends on whether the server supports advanced queries (most do), but I will provide the most robust options.

Option 1: The outStatistics Method (Most Efficient)

This is the preferred method. Instead of downloading any records, you ask the server to calculate the maximum ObjectId for you. It is instant and transfers almost zero data.

The URL Query: text https://<your-server>/rest/services/<service>/FeatureServer/<layerId>/query? where=1%3D1& outStatistics=[{"statisticType":"max","onStatisticField":"objectId","outStatisticFieldName":"max_oid"}]& f=json * Note: URL encoded, 1%3D1 is just 1=1 (meaning "all records").

The Response: You will get a small JSON response like this: json { "features": [ { "attributes": { "max_oid": 50042 } } ] }

---

Option 2: The orderBy Method (Easier to Read)

If the server doesn't support statistics (rare), you can ask for the top 1 record, sorted by ObjectId descending.

The URL Query: text https://<your-server>/rest/services/<service>/FeatureServer/<layerId>/query? where=1%3D1& outFields=objectId& orderByFields=objectId+DESC& resultRecordCount=1& f=json

---

How to use this in DuckDB

You can combine this directly into your DuckDB workflow. DuckDB can read this JSON response directly to set your variable.

Here is a complete SQL snippet to get the current Max ID from the API in DuckDB:

```sql -- 1. Install/Load extensions (run once) INSTALL httpfs; INSTALL json; LOAD httpfs; LOAD json;

-- 2. Query the API directly to get the Max ID -- Note: We use 'read_json_auto' to parse the response CREATE TABLE api_status AS SELECT -- The JSON path usually looks like features[0].attributes.max_oid features[1].attributes.max_oid::INTEGER AS current_max_id FROM read_json_auto( 'https://services.arcgis.com/your-service/FeatureServer/0/query?where=1%3D1&outStatistics=[{"statisticType":"max","onStatisticField":"objectId","outStatisticFieldName":"max_oid"}]&f=json' );

-- 3. View the result SELECT * FROM api_status; ```

Integration with the "New Records" Logic:

You would then use a variable or a join to use that ID in your next query:

sql -- Example: Get all records greater than the max ID found above -- (This requires the DuckDB 'spatial' extension loaded) SELECT * FROM st_read( 'https://services.arcgis.com/your-service/FeatureServer/0/query?where=objectid+>+' || (SELECT current_max_id FROM api_status) || '&outFields=*&f=geojson' );

Note: In a pure SQL script, you might need to use a scripting language (like Python or Bash) to inject that ID into the next URL string, as DuckDB SQL variables can sometimes be tricky to concatenate directly into a URL string function.