API endpoint suggestions (from 1103)

[jadudm]

This discussion is being kicked off here on behalf of @jpmorris, who asked about additional endpoints in issue #1103.

#1103

From the issue:

So we don't use any of the current exposed endpoints, we pull all the data to run analysis in aggregate. I don't see any endpoints exposed that allows us to get this information, so we would like to request these endpoints:

• /vw_structured_data(year) - gets all structured data for the year
• /vw_pdf_audit(date range) - gets all audits for a given date range
• /vw_crosswalk(date range) - gets all crosswalk entries for a date range (in the old FAC site this was bundled with the audit PDFs)

Thinking about each of these in turn:

/vw_structured_data suggests a way to get all of the data for "the year." Is this... from ${now} until 12 months ago? For a given fiscal year? Audit year? Those are details, when really... what it sounds like is you would like a database dump. If you do, do you want it in CSV, SQLite, a raw Postgres dump, or something else? And, how often would you need it? (If you say daily or weekly, it feels like you're not taking advantage of the API.) Why, essentially, do you need this (as opposed to querying the API for what you need, when you need it?) It would be good to understand the use case.

vw_pdf_audit. Currently, you can get a link to the PDF package from an entry in the vw_general view. (I think that's true. The key is pdf_urls.) You could do a date range search on the vw_general view, aggregate the PDF links, and then download them. It feels like the API does what you need already.

I'm not yet clear what "the crosswalk" is. There are a number of different crosswalks we might generate, and they're going to be... tricky, given the transition from EIN/DUNS to UEI. Is your third request essentially asking the question "How do we link 2023/UEI data to historical data?" If so, then this is a hard question to answer, and we're working with agencies to figure it out, but we're not there yet.

[jpmorris]

Thanks so much for the hard work on this @jadudm

The eventual cadence of our fetches would be every 2 weeks. Sorry if I was unclear. Because we don't have a fully automated system in place in the PAST we have fetched data for several years all at once, but we would like to move to a 2 week cadence. (That said, there may be a rare need where we need to refetch all data between two arbitrary dates I suppose if we are missing many random files--which we currently need to do due to an bug with the old FAC website that I wont get into-- or some other issue, but this would not be the workflow. The common workflow would be to fetch the last two weeks worth of data looking for the newest audits (pdf, crosswalk, and structured data).

And sorry we may not need those EXACT endpoints. They might be most convenient and compatible with our workflow, but I would completely understand if there's a similar solution with just more API calls, we can definitely develop that on our side by doing, say 1000 calls for a period instead of 1 massive 'give-me-everything-you-got-between-these-dates'. If there are endpoints that meet our requirements but just take more API calls we definitely work with that.

To be clear clear on the requirements are:

• Pdfs
• structured data
• crosswalk
• between arbitrary dates

But maybe there is already a solution I just didn't see. Maybe you can just point me to a solution if I'm missing something? So looking through the API maybe here's the rough process:

1. Get all audits between START_DAY and END_DAY (usually in the last 2
   weeks) -- maybe /vw_general would provide this information? We usually query by 'release date' which I don't see, but maybe we can use different date I need some sort of range to search over. Is there some solution I"m missing? Is there a way to fetch the newest audits to appear in the last 2 weeks, say?

2. After obtaining new audits in the past week (assume some set of unique identifier is returned), fetch all the structured data. We currently fetch 15 files from the old FAC site that I assume are dumps of their tables. We don't use all of these files, but many of them we do. Here's a list:
   
   (I can provide any of these files if you need them.)
   I do see notes, general, and findings_text. I assume the other ones are in progress (we use cfda, cpas, duns an several others). I assume I could use the set of UIDs from step 1 to query for the entries in these tables which would correspond to rows in each of the invividual csv files. I would just need the other endpoints that we use (cfda, cpas etc.)

3. I need to fetch the PDF. As you say the general view may return all the pdf links and I can just fetch the pdfs from the URLs provided

4. I need to find a way to match the FILENAME of the pdf to the the EIN of the auditee which I can reference in all the other structure data files from step 2. Here's an example of the crosswalks we are provided from the old FAC site:
   
   (also available if you want)
   BUT If Step 1 provides me with all relevant unique identifiers AND the pdf link/filename, then I no longer need this crosswalk file because I already have tha connection. Is this correct?

Sorry was too lazy in considering how to get the information we need when I submitted the ISSUE. We don't need those exact endpoints, what you have does solve a lot of our workflow already I just looks like we might need:

1. A way to search for the set of audit unique IDs that are now available in the last two weeks (we would request some arbitrary date range)
2. The additional table endpoints we use (CPAS, CFDA etc)

And then we could probably batch download everything we need? Do I have this right?

[jpmorris]

Hey @jadudm not sure if you had a chance to look at this. I'm sure you're busy, I just want to make sure that I have this right and we are making the proper request.

Do I have the above correct that we will need:

1. A method to return unique audit IDs between two arbitrary dates
2. The additional endpoints that we use (CPAS, CFDA etc) and ability to search these endpoints by the unique audit IDs from 1.

Does this sound right to you?