¶ API Gateway: Design Document
data_downloader.py
¶ 🏗️ System Architecture
The program is built on a metadata-drive architecture. Instead of hardcoding API endpoints or database schemas, the systesm read from a sources.json file. This allows us to add new Census products (or products from other sources) simply by updating the json file.
¶ The 5-Zone UI Layout
The interface is divided into five functional "Focus Zones" to manage the complexity of geographic data:
-
Data Source (Top Bar 1): Horizontal selection of the Census product (e.g.,
ecnbasic). -
Scope (Top Bar 2): Horizontal selection of granularity: this is geographic for Census-based data (ECN, CBP, NES, ACS the options are
National,State, orCounty) while this is chunks for BLS data (CEX, CPI, PPI). -
Actions (Middle Left): Vertical menu for
Download,Upload,Backup, andRestore. -
List 1 (Middle Center): For Census data , this is a scrollable list of US States with status icons. For BLS data, this is a list of timeseries.
-
List 2 (Middle Right): For Census data, this is a dynamic drill-down showing residency status for counties in the selected state. BLS data provides metadata for the series.
¶ ⚙️ Decoupled Injestion
¶ Stage 1. Download Data (API Ingestion)
The program utilizes the requests library with a retry strategy to fetch data from the Census API.
-
Parameters: It dynamically builds the get (variables) and for (geography) strings based on your UI selection.
-
Storage: Raw data is stored as a JSON file.
¶ Stage 2: Transformation and Uploading to Database
¶ Transformation (Temporary Preparation)
Before data hits the database, it is "cleaned" into a Tab-Separated Values (TSV) format.
-
Null Handling: Census "None" or empty values are explicitly converted to the string
NULL. -
Normalization: Geographic FIPS codes are padded (e.g.,
1becomes01) to match thereferenceschema.
¶ Uploading (Bulk Ingestion)
To handle millions of rows, the program avoids standard INSERT statements, which are slow.
-
COPY Command: It uses the PostgreSQL
COPYcommand to stream the TSV file into a_tmp(temporary) table. -
UPSERT: It then performs an internal database merge:
INSERT INTO production_table SELECT * FROM temporary_table
ON CONFLICT (unique_hash) DO NOTHING;
¶ 🛡️ Data Safety & Versioning
The Backup and Restore system acts as a manual version control for your datasets.
-
Snapshots: The
Backupaction uses a "Shadow Table" approach (table_name_bk). It performs a server-side clone of the production data. -
Restoration: The
Restoreaction drops the production table and reinstates the backup. Crucial: The program manually reconstructs theUNIQUEindex on theunique_hashcolumn after a restore, as cloning data alone does not copy table constraints.
¶ 🕹️ User Controls (Hotkeys)
| Key | Action |
|---|---|
TAB |
Cycle focus between the 5 UI Zones |
Arrows |
Navigate within the active zone (horizontal (left/right) for bars, vertical (up/down) for lists) |
SPACE |
Queue a state or county for batch processing |
C |
Select / deselect ALL counties in the highlighted state |
ENTER |
Execute the highlighted action (sync, backup or restore) |
Q |
Gracefully exit the program |
¶ 📁 File Requirements
-
.env: Must contain DB_NAME, DB_USER, DB_PASS, DB_HOST, and CENSUS_API_KEY. -
sources.json: The metadata configuration file. -
PostgreSQL Tables:
- Raw Schema: The raw schema must contain the tables defined in the JSON, each with a temporary counterpart and a backup version (e.g.,
ecn_2022_standecn_2022_st_tmpandecn_2022_st_bk) - Reference Schema: Tables with FIPS codes for the states and counties.
- Raw Schema: The raw schema must contain the tables defined in the JSON, each with a temporary counterpart and a backup version (e.g.,
¶ Program Summary
- Initialization
- Load environmental variables
- Set up logging file:
data_downloader.log
- Global Data Loading
-
Load json file
sources.json
Loads metadata configuration from json; drives entire dynamic download and upload logic -
Set up raw_data directories for each source
- Census Reference Loader Functions
-
load_states_from_db()
Fetches the master list of 50 states (plus territories) from the reference.states_fips table. This populates the central UI panel. -
load_counties_from_db()
Fetches the complete national directory of counties (~3,200 records) from reference.county_fips. This is stored in memory to allow for instant filtering when browsing states.
- BLS Metadata Context Loader Functions
-
locate_available_chunks(chunks_dir)
Scans the targeted BLS directory for split JSON metadata chunk files. -
load_series_from_chunk(chunk_path)
Loads series items and metadata records from a specified local catalog chunk.
- Database State Synchronization Lookups
-
get_synced_states(geo_meta)
Queries the active production table to see which states already have data. It returns a set of FIPS codes used to turn UI icons Green (the "Green Code" logic). -
get_synced_counties(table, state_fips)
Performs a targeted query for a specific state to identify which of its counties are already in the database. This is used for the "drill-down" status panel.
* `get_synced_bls_series(source_name, series_ids)`
Verifies which series identifiers are already tracked inside production tables. Batches lookups into chunks of 500 rows to prevent query parsing threshold breaks.
- Resuable Master Database Backup/Resotre Layer
-
backup(stdscr, geo_meta)
Clones the current production table into a shadow table (suffixed with_bk). This uses server-side cloning for near-instant snapshots of millions of rows. -
restore_backup(stdscr, geo_meta)
The "Undo" function. It drops the current production table and replaces it with the backup. It also manually restores the UNIQUE constraints that are lost during table cloning. -
extract_column_label(label_string)
Extracts the column (or cohort) name in the ACS data.
- Core Pipeline Pipes: Downloaders and Intermediate Loaders
-
extract_data_acs5(stdscr, s_fips, c_fips, source_meta, geo_meta, s_abbr)
The core engine for ACS (American Community Survey) data. This data is significantly different from the other datasets. -
extract_data_standard(stdscr, s_fips, c_fips, source_meta, geo_meta, s_abbr)
The core engine. It handles: -
upload_data_acs5(stdscr, s_abbr, source_meta, geo_meta)
Transforms multi-index demographic summary files into stacked relational shapes. Streams outputs to a local disk data.tsv buffer and implements binary COPY operations. -
upload_data_standard(stdscr, s_abbr, source_meta, geo_meta)
Flattens and bulk-loads standard layout business and economic datasets (CBP, ECN). -
execute_bls_extraction(stdscr, source_name, api_url, series_data, chunk_filename, start_year="2016", end_year="2026")
Extracts time-series arrays from BLS endpoints for elements mapped inside a target chunk. Saves the output payload to a unique file specific to the active chunk name. -
execute_bls_upload(stdscr, source_name, chunk_filename)
Transforms chunk-specific BLS raw data JSON caches into clean time-series rows and loads them into the database using high-speed binary COPY streams. Safely downcasts missing data hyphen string symbols ('-') into database NULL markers.
- Action Handler
run_action(stdscr, selections, selected_states, selected_counties, geo_meta)
The "Switchboard." It interprets which action is highlighted in the menu and builds the work queue (determining if it should process one county, one state, or a batch of states). Determines which ETL pipeline to run (ACS or main).
- Main Controller Window Management
-
draw_ui(stdscr, status, details)
A "blocking" screen overlay. It hides the dashboard and shows a progress bar or status message (e.g., "SYNCING 5/50") during heavy network or database tasks. -
show_popup(stdscr, title, body)
Displays a modal box summarizing results. -
draw_interface(stdscr, focus_idx, sel, scroll, c_scroll, synced_st, selected_st, selected_co, synced_co)
The primary rendering engine. It draws all 5 zones of the dashboard, calculates scroll positions, and applies color pairs based on the sync status of the data. -
main(stdscr)
The entry point. It initializes the curses environment, manages the focus_idx (which zone is active), and listens for keyboard interrupts to update the program state.
Selection Logic Hierarchy
When you hit ENTER to sync, the program decides what to download based on this priority:
-
County Queue: If any counties were selected via the
Ckey. -
State Queue: If any states were checked via
SPACE. -
Active Highlight: If nothing is checked, it syncs only the state/level currently under the cursor.
¶ Program Diagram
¶ Function Details
¶ load_states_from_db()
Indentifies all states in reference schema: reference.states_fips
Returns: a dataframe with columns ['fips_codes', 'state_name', 'state_abbr']
¶ Details
¶ load_counties_from_db()
Identifies all counties from reference schema: reference.county_fips
Returns: a dataframe with columns ['state_fips', 'county_fips', 'county_name'].
¶ Details
¶ locate_available_chunks(chunks_dir)
Scans the targeted BLS directory for split JSON metadata chunk files.
Args:
- chunks_dir (str): Relative directory path holding partitioned catalogs.
Returns:
- list: A sorted list of filename strings matching target definitions (e.g., '.json').
¶ Details
¶ load_series_from_chunk(chunk_path)
Loads series items and metadata records from a specified local catalog chunk.
Args:
- chunk_path (str): Filepath location pointing to the chunk target.
Returns:
- list: A parsed collection of dictionary objects tracking individual series metrics.
¶ Details
¶ get_synced_states(geo_meta)
Identifies states already in database
Args:
- geo_meta (dict): Visual scope mapping metadata containing target table layouts.
Returns: - set: a collection of 2 digit fips strings of the states present in the database
¶ Details
¶ get_synced_counties(table, state_fips)
Identifies which counties within a specific state are already present in database
Args:
- table (str): Production data table tracking spatial metric assignments.
- state_fips (str): 2-digit parental state boundary restriction constraint code.
Returns:
- set: a collection of 3-digit county fips strings
¶ Details
¶ get_synched_bls_series(source_name, series_ids)
Verifies which series identifiers are already tracked inside production tables.
Batches lookups into chunks of 500 rows to prevent query parsing threshold breaks.
Args:
- source_name (str): Core target data survey layout handle (e.g., 'cex', 'cpi').
- series_ids (list): Array string blocks holding individual series tokens.
Returns:
- set: A collection of plain strings reflecting synced keys found in the database.
¶ Details
¶ backup_table(stdscr, schema_table_name)
Creates a backup of the production table
Args:
- stdscr (window): Master curses interface screen context window.
- schema_table_name (str): Full dot-notated destination table name (e.g., 'raw.bls_cpi').
¶ Details
¶ restore_table_backup(stdscr, schema_table_name, is_bls=False)
Overwrites an active production database table layout from the latest backup clone.
Re-applies unique constraints/hashes dropped during raw server-side table duplication.
Args:
- stdscr (window): Curses active system terminal viewport component context.
- schema_table_name (str): Full dot-notated target table path (e.g., 'raw.bls_cex').
- is_bls (bool): Set to True for BLS datasets to correctly re-provision the composite unique_hash string index.
¶ Details
¶ extract_data_acs5(stdscr, s_fips, c_fips, sourcemeta, geo_meta, s_abbr)
Processes the data extraction, transformation, and loading processing for the ACS data (since it's different from the other data sets).
Returns: number of rows merged
¶ Details
¶ extract_data_standard(stdscr, s_fips, c_fips, source_meta, geo_meta)
Donwloads standard (census) data.
Returns: number of rows merged into database
¶ Details
¶ upload_data_acs5(stdscr, s_abbr, source_meta, geo_meta)
Transforms multi-index demographic summary files into stacked relational shapes.
Streams outputs to a local disk data.tsv buffer and implements binary COPY operations.
Args:
- stdscr (window): Curses window engine instance.
- s_abbr (str): Target identification alphanumeric state locator context handle.
- source_meta (Series): Parameter settings defining the variable constraint matrices.
- geo_meta (dict): Selected geography scale definition options properties map.
Returns:
- int: Cumulative count indicating newly updated or appended production table records.
¶ Details
Column LOOP*
¶ upload_data_standard(stdscr, s_abbr, source_meta, geo_meta)
Flattens and bulk-loads standard layout business and economic datasets (CBP, ECN).
Args:
- stdscr (window): Master graphics engine viewport context window tracking token.
- s_abbr (str): Target textual abbreviation indicating the current tracking geography scope.
- source_meta (Series): Configuration metadata dictionary item mapping profiles.
- geo_meta (dict): Relational database storage structure configurations attributes map.
Returns:
- int: Cumulative count tracking newly synchronized rows saved to database storage.
¶ Details
¶ execute_bls_extraction(stdscr, source_name, api_url, series_data, chunk_filename, start_year="2007", end_year="2026")
Extracts time-series arrays from BLS endpoints for elements mapped inside a target chunk. Saves the output payload to a unique file specific to the active chunk name.
Includes automatic retries for transient read timeouts and network drops.
Args:
- stdscr (window): Live dashboard display interface frame tracking pointer.
- source_name (str): Survey dataset string label index token (e.g., 'ppi', 'cex').
- api_url (str): Target HTTP endpoint URL for post payload submissions.
- series_data (list): Parsed series index mappings tracking active metadata properties.
- chunk_filename (str): The filename of the active highlighted chunk (e.g.,
combined_cpi_chunk_1.json). - start_year (str): Lower chronological bound constraints parameters token.
- end_year (str): Upper chronological bound constraints parameters token.
Returns:
- bool: True if extraction processes finished cleanly without quota block terminations.
¶ Details
¶ execute_bls_upload(stdscr, source_name, chunk_filename)
Transforms chunk-specific BLS raw JSON caches into text-based staging rows,extracts footnote metadata (codes and text), and loads data via COPY streams.
Preserves the generated UUID 'id' from the staging table to the primary table and applies conditional ON CONFLICT logic to handle revisions, preliminary ('P'), initial ('I'), estimated ('E'), and corrected ('C') data updates.
Args:
- stdscr (window): Curses interactive window system active viewport.
- source_name (str): Operational survey registry key parameter (e.g., 'cpi').
- chunk_filename (str): Filename of the active highlighted chunk (e.g., 'combined_cpi_chunk_1.json').
Returns:
- int: Total row count added or updated in production storage.
¶ Details
¶ run_action(stdscr, selections, selected_states, selected_counties, geo_meta)
Intercepts and routes front-end TUI click triggers to appropriate backend data loops.
Distinguishes operational workflows contextually using the asset source 'type'.
Args:
- stdscr (window): Live component frame window handler object instance.
- sel (dict): Active dashboard coordinate focus index selectors map dictionary.
- selected_st (set): Active user-selected multi-index state FIPS codes array context.
- selected_co (set): Active user-selected multi-index county tracking tuple coordinates.
- series_data (list): Loaded structural data series dictionaries (BLS contexts only).
- chunks_list (list): Identified file catalog tokens present inside targeted subdirectories.
Returns:
- tuple: Multi-element set variables refreshing front-end tracking indicators vectors.
¶ Details
¶ Census flow channels
¶ BLS flow channels
¶ draw_ui(stdscr, status, details)
Renders a clean, full-screen status update overlay during blocking I/O tasks.
Args:
- stdscr (window): Curses active system terminal viewport component context.
- status (str): The primary active operation state string (bolded header).
- details (str): Supplementary text tracing current row metrics or paths.
¶ def show_popup(stdscr, title, body)
Renders an informational overlay modal prompt dialog block over the UI workspace grids.
Args:
- stdscr (window): Master graphics engine framework viewport handle wrapper tracking token.
- title (str): Bold header string phrase printing at top of card container.
- body (str): Plain text tracking description message body filling informational areas bounds.
¶ draw_interface(stdscr, focus_idx, sel, scroll, c_scroll, synced_st, selected_st, selected_co, synced_co)
Renders the layout structures and components forming the active 5-zone terminal grid interface.
Handles geometric canvas clipping calculations based on the terminal window limits.
Args:
- stdscr (window): Curses master output viewport panel window component block context tracking pointer.
- focus_idx (int): Global index coordinates tracking the currently focused interactive component zone.
- sel (dict): System coordinate positional index settings dictionary tracking active row assignments.
- chunks (list): Discovered JSON filename mappings entries cached inside localized directory channels.
- series (list): Dictionary item listings loaded from active chunk files containing individual metadata specifications.
- synced_st (set): 2-digit state FIPS identifiers codes confirming localized integration within database schemas.
- selected_st (set): Multi-index operational list storage containing checked state entries.
- selected_co (set): Multi-index tracking parameters layout array containing checked county entry coordinates tuples.
- synced_co (set): 3-digit localized county identity vectors confirming relational integration within target databases.
- synced_bls_ids (set): Explicit strings verifying active index alignment items saved inside database storage schemas.
- scroll (int): Numerical counter tracing top positioning indices offsets for focus zone 3 scroll arrays.
- c_scroll (int): Top index tracking position offsetting row scroll limits inside focus zone 4 structural windows.
- detail_scroll (int): Positional row scroll offset counter tracking metric context lists components attributes mappings.
¶ main(stdscr)
Primary orchestration display thread running loop initialized by curses wrapper processes. Captures continuous keystrokes to evaluate focus panel movements and layout scrolls.
Args:
* stdscr (window): Master application canvas reference window context token block.
¶ Details
¶ Logic by Focus Area
¶ Subfunctions
¶ Data Validation Tests
¶ Raw Data Files
All raw data files are saved in the ./raw_data directory.
| Database | Number of files | Size |
|---|---|---|
acs5 |
7801 | 1.4 GB |
cbp |
104 | 1.1 GB |
ecnbasic |
104 | 336 MB |
ecn_napcs_prdind |
1 | 11 MB |
nes |
104 | 177 MB |
bls_cex |
5 | 492 MB |
bls_cpi |
1 | 317 MB |
bls_ppi |
1 | 355 MB |
| TOTAL | > 4 GB |
¶ Notable Potential Issues
Important issues that have come up and should be noted for future reference or work.
¶ BLS Data Margins of Error (Aug 2026)
Currently the program does not record errors with BLS data. These errors (either margin of error or standard error) are not being downloaded. They exist, the current data does have a footnote if the error is really big (like > 25%). It's unclear if the error can be accessed with the API at this time (August 2026). The errors do exist in csv files (at least for CEX).