FAC API differences with historical fields

This is a living document

As of this writing (February '24), the historical data migration is underway. We're still discussing how best to map historical data that does not fit the 2023 collection data model. Federal API users are encouraged to engage the FAC in dialogue as to how historical data is exposed for future use.

We are in the process of migrating historical data (2018-2023) so that it maps into the 2023 collection's PRA. Fields that do not fit (e.g. DBKEY) will be mapped in via a seperate (e.g. historical) endpoint. As part of this process, some historical fields have been omitted. For example:

Short-lived fields. The historical data includes some fields that only existed for a handful of years and do not have an apparent correlate when compared to other years.
Duplicative data. The historical data includes some fields that "roll up" one or more fields into a secondary field. Where practical, we have omitted these fields in favor of their constituent parts.

When trying to understand how GSA maps historical data into the API, three parts of the codebase are useful references:

Historical migration code. GSA implemented the historical migration by (1) loading historical data, (2) creating "virtual submissions" from it (e.g. constructing submission workbooks and form data), (3) running them through the entire submission process, including validation, and (4) disseminating those "virtual audits." This code is in census_historical_migration.
IntakeToDissemination. This code maps the internal data structures of the GSA FAC through to the dissemination tables (which feed the API).
The API itself. The API (v1.0.3) provides the mapping from the internal dissemination tables through to PostgREST, which is then consumed by clients.

This document is organized by FAC endpoint.

FAC API differences with historical fields
- Endpoints
- Other tables

Endpoints

general

Differences in the `general` endpoint.
Census field	FAC field	Description
COMPLETED ON	`submitted_date` or `fac_accepted_date`	We do not know what Census meant by this field. We use `submitted_date` internally to mean "the day on which the final button was pressed by the auditee in order to submit their audit." We use `fac_accepted_date` to mean "the day upon which we processed and disseminated their audit."
EINSubCode	Unknown	No information available at this time.
AUDITEE STREET2	N/A	Not in the 2023 collection.
AuditeeFax	N/A	Not in the 2023 collection.
AuditeeNameTitle	`auditee_certify_name` and `auditee_certify_title`	These are likely the closest mappings in the GSA FAC API.
`TYPEREPORT_MP`	Rollup	See rollups.
`SIGNIFICANTDEFICIENCY_MP`	Rollup	See rollups.
`QCOSTS`	Rollup	See rollups.
`CYFINDINGS`	Rollup	See rollups.
`PYSCHEDULE`	Rollup	See rollups.
COMPONENT DATE RECEIVED	Unknown	No information available at this time.
FORM DATE RECEIVED	Unknown	No information available at this time.
INITIAL DATE RECEIVED	`submitted_date`	GSA FAC tracks multiple timestamps as the auditor and auditee progress through the system. `date_created` is the date the audit is first initialized in the FAC. `ready_for_certification_date` is the point that the audit is locked and marked ready to be certified. The certified dates are when the auditor/auditee sign. `submitted_date` is when the auditee "pushes the button" to certify, and `fac_accepted_date` is when the FAC acknowledges that submission. These last two days are currently (Jan '24) always the same, but there may be reasons, in the future, that they are not.
FAC RELEASE DATE	`fac_accepted_date`	GSA sets `fac_accepted_date` to the date that the audit is compeleted and moved to dissemination.
`AUDITEEDATESIGNED`	`auditee_certified_date`
`CPADATESIGNED`	`auditor_certified_date`
FAC ACCEPTED DATE	`fac_accepted_date`
`PREVIOUSCOMPLETEDON`	Unknown	No information available at this time.
`REPORTREQUIRED`	Unknown	No information available at this time.
`MATERIALWEAKNESS_MP`	Rollup	See rollups.
DBKEY		Not yet incorporated into the GSA FAC API. This value does not exist for 2023 and future years. The DBKEY and AUDITYEAR formed a compound primary key in the Census FAC, and GSA uses a single PK (`report_id`) which is guaranteed to be globally unique within the system. When historical data is incorporated into the collection, all historical audits will be given a new-style `report_id`, and a mapping from `report_id` to `DBKEY` and `AUDITYEAR` will be provided as a standalone table. It will not be part of `general`.
`COGOVER`	Rollup	See rollups.
`CPAFAX`	N/A	Not in the 2023 collection.
CPASTREET2	N/A	Not in the 2023 collection.

secondary_auditors

This was previously the CPAS table.

Differences in the `secondary_auditors` endpoint.
Census field	FAC field	Description
`CPAFAX`	N/A	Not in the 2023 collection.

findings

Differences in the `findings` endpoint.
Census field	FAC field	Description
ELECAUDITFINDINGSID	`award_reference`	The `award_reference` is an effective key into the findings, findings text, and CAP text tables.
FINDING SEQ_NUMBER	implicit	Findings are ordered. GSA can consider adding an explicit index back in.
CAP SEQ_NUMBER	Unknown?	If this is a reverse-mapping from CAP text, then it is not in the GSA endpoints. Forward-referencing from the `award_reference` is recommended.

federal_awards

This was previously the CFDAs table.

Differences in the `federal_awards` endpoint.
Census field	FAC field	Description
`TYPEREQUIREMENT`	`type_requirement`
RD	Unknown	No information available at this time.
ARRA	Unknown	No information available at this time.
CFDAPROGRAMNAME	Uknown

notes_to_sefa

Differences in the `notes_to_sefa` endpoint.
Census field	FAC field	Description
ID	Unknown	No information available at this time.
REPORTID	Unknown	No information available at this time.
VERSION	Unknown	No information available at this time.
SEQ_NUMBER		Forward reference from `award_reference`
TYPE_ID	Not needed	GSA encodes the notes differently; this field is not required to determine row type.
NOTE_INDEX	implicit	Notes are ordered.

Other tables

Census had three more tables that are not represented (at this time) in the GSA FAC API:

REVISIONS (or similar) will be implemented in Q2/Q3 after further research with agencies.
AGENCY It is unclear whether the FAC should provide this table. In Q2/Q3, we will determine whether it is redundant with offerings from SAM.gov or elsewhere.
DUNS will be investigated as part of historical data migration and mapping to new-style reports. There are no DUNS numbers in the 2023 collection, and therefore it is not present as part of the 2023 data.