jzebedee / uscis Goto Github PK

View Code? Open in Web Editor NEW

3.0 2.0 0.0 34 KB

Daily datasets of USCIS form processing times

Shell 100.00%

sqlite uscis citizenship immigration

uscis's Introduction

USCIS Dataset

Daily datasets of USCIS form processing times

About

The U.S. Citizenship and Immigration Services (USCIS) provides estimated case processing times that vary by form and service center. These estimates include both an estimate of the case adjudication time as well as the case inquiry date, which determines when an applicant may contact USCIS regarding a case that is outside of normal processing times.

Unfortunately, these estimates are updated silently without any notification or record, sometimes multiple times per day. This project is an attempt to provide transparency for applicants who may be surprised to find that their case inquiry date was changed without their knowledge.

How it works

The dataset releases are produced on a daily cron schedule that scrapes the USCIS case processing time API and compiles the results into a SQLite database.

The raw JSON results are also collected into a SQLite archive and published as an artifact, in the case of debugging or if there's additional information to extract. These artifacts are not a permanent collection and will be removed eventually based on the artifact retention period.

Changelog

v0.3 - 2023-07-29

Release notes are now generated with sqldiff output to compare daily changes

v0.2 - 2022-05-05

Office data is now grouped by form subtype in the form_types table
JSON result artifacts are now published in each action run

Schema

`forms` table

CREATE TABLE forms(
    name TEXT PRIMARY KEY,
    description_en TEXT,
    description_es TEXT
) WITHOUT ROWID

`offices` table

CREATE TABLE offices(
    code TEXT PRIMARY KEY,
    description TEXT
) WITHOUT ROWID

`processing_time` table

CREATE TABLE processing_time(
  "form_name" TEXT,
  "office_code" TEXT,
  "form_note_en" TEXT,
  "form_note_es" TEXT,
  "form_subtype" TEXT,
  "publication_date" TEXT,
  "range_upper" REAL,
  "range_upper_unit" TEXT,
  "range_lower" REAL,
  "range_lower_unit" TEXT,
  "service_request_date" TEXT,
  "subtype_info_en" TEXT,
  "subtype_info_es" TEXT,
  "subtype_note_en" TEXT,
  "subtype_note_es" TEXT,
  PRIMARY KEY("form_name","office_code","form_subtype")
) WITHOUT ROWID

`form_types` table

CREATE TABLE form_types(
    form_name TEXT,
    form_key TEXT,
    form_type TEXT,
    description_en TEXT,
    description_es TEXT,
    offices JSON,
    PRIMARY KEY("form_name","form_key")
) WITHOUT ROWID

`selftest` table

Used for SQLite database integrity self-tests

v0.1 - 2022-03-21

Initial release

Schema

`forms` table

CREATE TABLE forms(
    name TEXT PRIMARY KEY,
    description_en TEXT,
    description_es TEXT,
    offices JSON
) WITHOUT ROWID

`offices` table

CREATE TABLE offices(
    code TEXT PRIMARY KEY,
    description TEXT
) WITHOUT ROWID

`processing_time` table

CREATE TABLE processing_time(
  "form_name" TEXT,
  "office_code" TEXT,
  "form_note_en" TEXT,
  "form_note_es" TEXT,
  "form_subtype" TEXT,
  "publication_date" TEXT,
  "range_upper" REAL,
  "range_upper_unit" TEXT,
  "range_lower" REAL,
  "range_lower_unit" TEXT,
  "service_request_date" TEXT,
  "subtype_info_en" TEXT,
  "subtype_info_es" TEXT,
  "subtype_note_en" TEXT,
  "subtype_note_es" TEXT,
  PRIMARY KEY("form_name","office_code","form_subtype")
) WITHOUT ROWID

`selftest` table

Used for SQLite database integrity self-tests

uscis's People

Contributors

Stargazers

Watchers

uscis's Issues

Form processing times are returning incorrect office code for FOD office

USCIS added the "FOD" office code to the I-601 form offices, but the processing time API is returning the "NSC" office code in the result even when "FOD" office is requested.

Example files

response-processing-time_I-601_NSC_144AB.json

{"data":{"processing_time":{"form_info_en":"Application for Waiver of Grounds of Inadmissibility","form_info_es":"Solicitud de Exención de Causales de Inadmisibilidad","form_name":"I-601","form_note_en":null,"form_note_es":null,"office_code":"NSC","pilot_content_en":"We generally process cases in the order we receive them. This processing time is based on how long it took us to complete 80% of adjudicated cases over the past six months. Each case is unique, and some cases may take longer than others. Processing times should be used as a reference point, not an absolute measure of how long your case will take to be completed.","pilot_content_es":"Generalmente procesamos los casos en el orden en que los recibimos. Este tiempo de procesamiento está basado en cuánto nos llevó completar el 80% de los casos adjudicados en los últimos seis meses. Cada caso es único y algunos casos podrían tomar más tiempo que otros. Los tiempos de procesamiento deben ser utilizados como puntos de referencia y no como una medida absoluta de cuánto tomará su caso en completarse.","pilot_form":true,"range":[{"unit":"Months","unit_en":"Months","unit_es":"Meses","value":40.5},{"unit":"Months","unit_en":"Months","unit_es":"Meses","value":25.5}],"subtypes":[{"form_type":"144AB","publication_date":"March 23, 2023","range":[{"unit":"Months","unit_en":"Months","unit_es":"Meses","value":40.5},{"unit":"Months","unit_en":"Months","unit_es":"Meses","value":25.5}],"service_request_date":"November 30, 2019","service_request_date_en":"November 30, 2019","service_request_date_es":"30 de noviembre de 2019","subtype_info_en":"Waiver of Grounds of Inadmissibility","subtype_info_es":"Exención de Motivos de Inadmisibilidad","subtype_note_en":null,"subtype_note_es":null}]}},"message":"Query was successful for payload {'form_name': 'I-601', 'office_code': 'NSC', 'form_type': '144AB'}"}

response-processing-time_I-601_FOD_144AB.json

{"data":{"processing_time":{"form_info_en":"Application for Waiver of Grounds of Inadmissibility","form_info_es":"Solicitud de Exención de Causales de Inadmisibilidad","form_name":"I-601","form_note_en":null,"form_note_es":null,"office_code":"NSC","pilot_content_en":"We generally process cases in the order we receive them. This processing time is based on how long it took us to complete 80% of adjudicated cases over the past six months. Each case is unique, and some cases may take longer than others. Processing times should be used as a reference point, not an absolute measure of how long your case will take to be completed.","pilot_content_es":"Generalmente procesamos los casos en el orden en que los recibimos. Este tiempo de procesamiento está basado en cuánto nos llevó completar el 80% de los casos adjudicados en los últimos seis meses. Cada caso es único y algunos casos podrían tomar más tiempo que otros. Los tiempos de procesamiento deben ser utilizados como puntos de referencia y no como una medida absoluta de cuánto tomará su caso en completarse.","pilot_form":true,"range":[{"unit":"Months","unit_en":"Months","unit_es":"Meses","value":40.5},{"unit":"Months","unit_en":"Months","unit_es":"Meses","value":25.5}],"subtypes":[{"form_type":"144AB","publication_date":"March 23, 2023","range":[{"unit":"Months","unit_en":"Months","unit_es":"Meses","value":42},{"unit":"Months","unit_en":"Months","unit_es":"Meses","value":22}],"service_request_date":"October 11, 2019","service_request_date_en":"October 11, 2019","service_request_date_es":"11 de October de 2019","subtype_info_en":"Waiver of Grounds of Inadmissibility","subtype_info_es":"Exención de Motivos de Inadmisibilidad","subtype_note_en":null,"subtype_note_es":null}]}},"message":"Query was successful for payload {'form_name': 'I-601', 'office_code': 'FOD', 'form_type': '144AB'}"}

2023-04-19 egov.uscis.gov outage

All connectivity to egov.uscis.gov is down. It's been unavailable at least since Wed, 19 Apr 2023 04:37:30 GMT when the first runner failed.

Local tests

$ curl http://egov.uscis.gov
curl: (7) Failed to connect to egov.uscis.gov port 80 after 89 ms: Connection refused

$ curl https://egov.uscis.gov
curl: (60) SSL certificate problem: unable to get local issuer certificate
More details here: https://curl.se/docs/sslcerts.html

curl failed to verify the legitimacy of the server and therefore could not
establish a secure connection to it. To learn more about this situation and
how to fix it, please visit the web page mentioned above.

Still responds to pings

$ ping egov.uscis.gov
PING egov.uscis.gov (173.255.50.107) 56(84) bytes of data.
64 bytes from 173.255.50.107 (173.255.50.107): icmp_seq=1 ttl=242 time=43.1 ms
64 bytes from 173.255.50.107 (173.255.50.107): icmp_seq=2 ttl=242 time=42.6 ms
64 bytes from 173.255.50.107 (173.255.50.107): icmp_seq=3 ttl=242 time=42.7 ms
64 bytes from 173.255.50.107 (173.255.50.107): icmp_seq=4 ttl=242 time=42.7 ms
64 bytes from 173.255.50.107 (173.255.50.107): icmp_seq=5 ttl=242 time=42.3 ms
64 bytes from 173.255.50.107 (173.255.50.107): icmp_seq=6 ttl=242 time=49.1 ms
64 bytes from 173.255.50.107 (173.255.50.107): icmp_seq=7 ttl=242 time=43.2 ms
64 bytes from 173.255.50.107 (173.255.50.107): icmp_seq=8 ttl=242 time=43.3 ms
64 bytes from 173.255.50.107 (173.255.50.107): icmp_seq=9 ttl=242 time=42.0 ms
64 bytes from 173.255.50.107 (173.255.50.107): icmp_seq=10 ttl=242 time=41.9 ms
64 bytes from 173.255.50.107 (173.255.50.107): icmp_seq=11 ttl=242 time=44.6 ms
64 bytes from 173.255.50.107 (173.255.50.107): icmp_seq=12 ttl=242 time=45.2 ms
64 bytes from 173.255.50.107 (173.255.50.107): icmp_seq=13 ttl=242 time=42.6 ms
64 bytes from 173.255.50.107 (173.255.50.107): icmp_seq=14 ttl=242 time=43.6 ms
64 bytes from 173.255.50.107 (173.255.50.107): icmp_seq=15 ttl=242 time=42.6 ms
64 bytes from 173.255.50.107 (173.255.50.107): icmp_seq=16 ttl=242 time=42.9 ms
64 bytes from 173.255.50.107 (173.255.50.107): icmp_seq=17 ttl=242 time=41.9 ms
^C64 bytes from 173.255.50.107: icmp_seq=18 ttl=242 time=42.2 ms

--- egov.uscis.gov ping statistics ---
18 packets transmitted, 18 received, 0% packet loss, time 18379ms
rtt min/avg/max/mdev = 41.903/43.244/49.050/1.642 ms

Down for external services

https://downforeveryoneorjustme.com/egov.uscis.gov?proto=https

SSL detail

USCIS certificate hasn't changed from the one we store in egov-uscis-gov.pem

$ openssl s_client -connect egov.uscis.gov:443
CONNECTED(00000003)
depth=0 C = US, ST = Maryland, L = Camp Springs, O = U.S. Citizenship and Immigration Services, CN = egov.uscis.gov
verify error:num=20:unable to get local issuer certificate
verify return:1
depth=0 C = US, ST = Maryland, L = Camp Springs, O = U.S. Citizenship and Immigration Services, CN = egov.uscis.gov
verify error:num=21:unable to verify the first certificate
verify return:1
depth=0 C = US, ST = Maryland, L = Camp Springs, O = U.S. Citizenship and Immigration Services, CN = egov.uscis.gov
verify return:1
---
Certificate chain
 0 s:C = US, ST = Maryland, L = Camp Springs, O = U.S. Citizenship and Immigration Services, CN = egov.uscis.gov
   i:C = US, O = DigiCert Inc, CN = DigiCert TLS RSA SHA256 2020 CA1
---
Server certificate
-----BEGIN CERTIFICATE-----
MIIG4TCCBcmgAwIBAgIQAX2nm/Bmqi58TSj4NTfU0DANBgkqhkiG9w0BAQsFADBP
MQswCQYDVQQGEwJVUzEVMBMGA1UEChMMRGlnaUNlcnQgSW5jMSkwJwYDVQQDEyBE
aWdpQ2VydCBUTFMgUlNBIFNIQTI1NiAyMDIwIENBMTAeFw0yMjA5MTMwMDAwMDBa
Fw0yMzEwMTQyMzU5NTlaMIGEMQswCQYDVQQGEwJVUzERMA8GA1UECBMITWFyeWxh
bmQxFTATBgNVBAcTDENhbXAgU3ByaW5nczEyMDAGA1UEChMpVS5TLiBDaXRpemVu
c2hpcCBhbmQgSW1taWdyYXRpb24gU2VydmljZXMxFzAVBgNVBAMTDmVnb3YudXNj
aXMuZ292MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAvnZXlo3MuHTi
KTmW3WG8nTQHJvA79DuRMTa7F9zMFw4qqkSEC41QhGOCu3o/8AJtPf57mk3SJXKm
jFe1xLBfSeDDAdG5SSwfdU10QbqBkEVqVHSbdSz9VGTPj8Rc/sOXRtpwSEMzZzWB
GXeg3TPneP2+F/UyjlXZ0+VbJK48W3DTYY4XDqtceaAIne/V3YUwAYHRxi3yONGs
ErY22NZOnNhSuYhoocaIUAPBMvtkmYpeC5CMbbgn8kRtMPH+eZaczDq9En76IifK
xHVn34osHQQYqM+yxb+XYvZfQWnGqZrGo1U2HvopASLe/lHvMdiT8d/Q/DWKi9kH
ZIQshZN4TwIDAQABo4IDgTCCA30wHwYDVR0jBBgwFoAUt2ui6qiqhIx56rTaD5iy
xZV2ufQwHQYDVR0OBBYEFP3FfjkmJiHaMSOz9i3fwHd7/KEeMC0GA1UdEQQmMCSC
DmVnb3YudXNjaXMuZ292ghJ3d3cuZWdvdi51c2Npcy5nb3YwDgYDVR0PAQH/BAQD
AgWgMB0GA1UdJQQWMBQGCCsGAQUFBwMBBggrBgEFBQcDAjCBjwYDVR0fBIGHMIGE
MECgPqA8hjpodHRwOi8vY3JsMy5kaWdpY2VydC5jb20vRGlnaUNlcnRUTFNSU0FT
SEEyNTYyMDIwQ0ExLTQuY3JsMECgPqA8hjpodHRwOi8vY3JsNC5kaWdpY2VydC5j
b20vRGlnaUNlcnRUTFNSU0FTSEEyNTYyMDIwQ0ExLTQuY3JsMD4GA1UdIAQ3MDUw
MwYGZ4EMAQICMCkwJwYIKwYBBQUHAgEWG2h0dHA6Ly93d3cuZGlnaWNlcnQuY29t
L0NQUzB/BggrBgEFBQcBAQRzMHEwJAYIKwYBBQUHMAGGGGh0dHA6Ly9vY3NwLmRp
Z2ljZXJ0LmNvbTBJBggrBgEFBQcwAoY9aHR0cDovL2NhY2VydHMuZGlnaWNlcnQu
Y29tL0RpZ2lDZXJ0VExTUlNBU0hBMjU2MjAyMENBMS0xLmNydDAJBgNVHRMEAjAA
MIIBfQYKKwYBBAHWeQIEAgSCAW0EggFpAWcAdQDoPtDaPvUGNTLnVyi8iWvJA9PL
0RFr7Otp4Xd9bQa9bgAAAYM35aocAAAEAwBGMEQCIBW1ELONeDldxcC+vc8ynMSY
MCE2plFZnnFVh5dgc8YiAiAM3KZIUhkXHEEiIK+TKakpw3FURjRqkJV1xIZMZ7rY
JAB2ADXPGRu/sWxXvw+tTG1Cy7u2JyAmUeo/4SrvqAPDO9ZMAAABgzflqgMAAAQD
AEcwRQIgKTMCEP018v6eGxStOHm6NRTXuyJTBCUNlGUs+pDi5MwCIQD+orKHizAm
jB3/+XvO65Co7L86EogHgT+AWNVX/K27HAB2ALNzdwfhhFD4Y4bWBancEQlKeS2x
ZwwLh9zwAw55NqWaAAABgzflqjgAAAQDAEcwRQIhALGH62fE1ScJRt5Fs/sJ+adg
PZx7Nm+wIT3cP0R8h8nNAiAWZ0JwKM6uTmnCwBftDHn0627fcA9BpO4KW42k8tjJ
LzANBgkqhkiG9w0BAQsFAAOCAQEAb4yxBw0E+mhE7b7aNQg9ua/p/Lg68hb5nnKk
Z9GGeTUo1YP8Ch7nsTnG1k44mYn2jce+3l3RsYjHw9XxL3Si2LTggam7/prj/HIS
5p2otdwiw26MmKUibCTeBIEHsqn5j3f7oBI2UmiUFZqvUfM6Upud9Tv9uln1fHNY
TW1pihUZ3ImxOlYDBrVfoabmsN3uCjzthmxoQlPvjtAjMUavPUuU9/bqb/AX/PDv
Y/QWrpmkRmKz6/kwaSalguiBFuRIVnkK4PKa9VvBVkoEaSR5K9+2SqkbMj06WHYa
3cds1wDM5OmMkFdjgEGBPieRZLoqMq+2+zTOCkS6f6NTNiczPQ==
-----END CERTIFICATE-----
subject=C = US, ST = Maryland, L = Camp Springs, O = U.S. Citizenship and Immigration Services, CN = egov.uscis.gov

issuer=C = US, O = DigiCert Inc, CN = DigiCert TLS RSA SHA256 2020 CA1

---
No client certificate CA names sent
Peer signing digest: SHA256
Peer signature type: RSA
Server Temp Key: ECDH, P-256, 256 bits
---
SSL handshake has read 2274 bytes and written 442 bytes
Verification error: unable to verify the first certificate
---
New, TLSv1.2, Cipher is ECDHE-RSA-AES128-GCM-SHA256
Server public key is 2048 bit
Secure Renegotiation IS supported
Compression: NONE
Expansion: NONE
No ALPN negotiated
SSL-Session:
    Protocol  : TLSv1.2
    Cipher    : ECDHE-RSA-AES128-GCM-SHA256
    Session-ID: 59E5F3B79C58D74A200CC689E5005C3792B2BAAAF188F831C7594B8A75506F02
    Session-ID-ctx:
    Master-Key: 7F8A0F0CF90F9B3CE7E47BD136783F21DB2A0FB17BC3B93AB378433D70CBED02746E69BC8AE50B44E05F63C512600AFC
    PSK identity: None
    PSK identity hint: None
    SRP username: None
    Start Time: 1681913700
    Timeout   : 7200 (sec)
    Verify return code: 21 (unable to verify the first certificate)
    Extended master secret: yes
---
read:errno=104

Unsafe negotiation disabled in modern SSL libraries

cURL is failing with the following error:
curl: (35) error:0A000152:SSL routines::unsafe legacy renegotiation disabled

OpenSSL sanity check also fails:

$ openssl s_client -connect egov.uscis.gov:443
CONNECTED(00000003)
40474AB1487F0000:error:0A000152:SSL routines:final_renegotiate:unsafe legacy renegotiation disabled:../ssl/statem/extensions.c:879:
---
no peer certificate available

Further details included in this SO answer.

jzebedee / uscis Goto Github PK

uscis's Introduction

USCIS Dataset

About

How it works

Changelog

v0.3 - 2023-07-29

v0.2 - 2022-05-05

Schema

forms table

offices table

processing_time table

form_types table

selftest table

v0.1 - 2022-03-21

Schema

forms table

offices table

processing_time table

selftest table

uscis's People

Contributors

Stargazers

Watchers

uscis's Issues

Example files

Local tests

Still responds to pings

Down for external services

SSL detail

Recommend Projects

Recommend Topics

Recommend Org

`forms` table

`offices` table

`processing_time` table

`form_types` table

`selftest` table

`forms` table

`offices` table

`processing_time` table

`selftest` table