Database schemas
| Tracking # |
R000830-060523 |
| Submitted | June 3, 2023 |
MuckRock users can file, duplicate, track, and share public records requests like this one. Learn more.
Communications
From: Rose Terse
To Whom It May Concern:
Pursuant to the Washington Public Records Act, I hereby request the following records:
Relating to request R000747-040723:
- The database query that was run to produce the records
- The database schemas of all tables in the database(s) involved in producing the records
The requested documents will be made available to the general public, and this request is not being made for commercial purposes.
In the event that there are fees, I would be grateful if you would inform me of the total charges in advance of fulfilling my request. I would prefer the request filled electronically, by e-mail attachment if available or CD-ROM if not.
Thank you in advance for your anticipated cooperation in this matter. I look forward to receiving your response to this request within 5 business days, as the statute requires.
Sincerely,
Rose Terse
From: Washington State Criminal Justice Training Commission
Dear Rose Terse:
Thank you for your interest in public records of WSCJTC. Your request has been received and is being processed in accordance with the State of Washington Public Records Act, Chapter 42.56 RCW. Your request was received in this office on 6/5/2023 and given the reference number R000830-060523 for tracking purposes. Records Requested: To Whom It May Concern:
Pursuant to the Washington Public Records Act, I hereby request the following records:
Relating to request R000747-040723:
- The database query that was run to produce the records
- The database schemas of all tables in the database(s) involved in producing the records
The requested documents will be made available to the general public, and this request is not being made for commercial purposes.
In the event that there are fees, I would be grateful if you would inform me of the total charges in advance of fulfilling my request. I would prefer the request filled electronically, by e-mail attachment if available or CD-ROM if not.
Thank you in advance for your anticipated cooperation in this matter. I look forward to receiving your response to this request within 5 business days, as the statute requires.
Sincerely,
Rose Terse
Upload documents directly: https://www.muckrock.com/
Your request will be forwarded to the relevant department(s) to locate the information you seek and to determine the volume and any costs associated with satisfying your request. You will be contacted within 5 business days with the availability and/or provided with copies of the records in question.
You can monitor the progress of your request at the link below and you'll receive an email when your request has been completed.
Be aware that if you do not download your provided records or installment of records within 30 days after the WSCJTC provided them, the WSCJTC will inform you about the abandonment of your request and close your record request per WAC 139-02-090(10).
WSCJTC
From: Washington State Criminal Justice Training Commission
--- Please respond above this line ---
Hi Rose,
I can just look at the report and tell you what was selected. First here is the PRR for R000747:
1) A copy of the database of all WA certified law enforcement officers, as of April 1, 2023.
2) A copy of the database of all WA DE-certified law enforcement officers, as of April 1, 2023.
These database extracts should include the following fields:
ID
FIRSTNAME
LASTNAME
FULLNAME
AGENCY
STUDENT ID
STATUS
RANK
HIRE DATE
COMMENTS
CREATED DATE
CREATED BY
MODIFIED DATE
MODIFIED BY
CERTIFICATION DATE
CERTIFICATION TYPE
Here is what I selected to fulfill that PRR:
the Acadis Student ID;
the first, middle last, and suffix for names;
Person status (whether they are active or not in the database);
Employment start date (i.e. hire date)
Employment rank;
Employment organization;
Certification status;
Certification type;
certification effective date;
certification expiration date;
certification date;
certification entered by;
certification comments;
certification action (i.e. database action with any certificate).
I selected everything that was specifically asked for that I know about. The issue with asking for "all certification information" on peace officers is to pull a list of peace officers, I have to use their "peace officer certificate" as my way of parsing the data. You'd only receive 13000 or so lines of peace officers, but with this request you have the entire database, including all of the peace officers and revoked peace officers, since you wanted all certification data.
To clarify that a bit more, since I have to parse the data by "peace officer certificate" that means all other certification information excluded. I did not do that in this search so you have the entire database of profiles.
Also if you want to see a list of de-certified officers, please use the public database found here: https://data.wa.gov/stories/s/WSCJTC-Certification-Database/3xkp-u89m/. (https://u8387778.ct.sendgrid.net/ls/click?upn=6HtRfOYLt5fXvpttM-2FU1HWWQsD56cV589H3c2VJYm9oZ4mJ-2Bobi-2FI5EDRd29AjAuX0qb5iIErR7BPVjGozkySPrE5FfFQBJZwd3RGTqFDXQ-3DkEWo_6hsh8p-2BSjSV3tAl1hoYpYtnaMS4i7hGcHWtkXKIT7eDEuDvAklX3uVgjqQwSc5pqvbPvLEFW6T6tzl-2FZcT9sGjhY2iShpTPTaxJTa8vjYg-2FQAllNxDwKiQ37u8L0FNFNWBcHgynbauOkpPzO3k1n0KLfqfez72QPxjHYOrWKz4roogQXU-2BKLa77BLdDzMztVmaPoBz1vdccsZt-2BR2mjj59Ytx0vrqtvLs8ZeaGXsbwA-3D)
I'm happy to answer any other questions or help you narrow and direct your requests to the information you need. Usually a phone call is easiest since I openly discuss what options are on the database, direct you to the information you're looking for, and the limitation my searches have. My number is 206-793-6332.
I also included snips of the "person" database fields so you can see what they are. That is the type of search we use to pull profiles of people. Please be aware, we do not use all of these fields. I'm just sharing all of them for the sake of transparency. Additionally, I cannot pull every-single field in one go. Each field is a new column and one field can add upwards of 1 to 30 columns, so it can exceed Excels ability to generate a document. You could comfortably ask for 6 to 10 fields (depending on how we're narrowing the search) and it'll work.
Thank you for your time and attention. I will leave this request open for further discussion, but this completes your request.
-Derek Zable
WSCJTC Records Manager
Phone: 206-793-6332
From: Rose Terse
Hi Derek,
Appreciate the response! Did you mean to attach the "snips of the 'person database fields'"? I don't believe I received any files.
Thank you,
Rose
From: Washington State Criminal Justice Training Commission
Here is a PDF of the "person" fields. I also released them on the PRR as well. If you want the images as separate jpgs, they're open to the request. I apologize, but I forgot to release them.
Thank you for your patience,
-Derek Zable
From: Washington State Criminal Justice Training Commission
--- Please respond above this line ---
RE: PUBLIC RECORDS REQUEST of June 05, 2023, Reference # R000830-060523
Dear Rose Terse,
Thank you for your message. I did forget to release the images. They are released now and I uploaded a single PDF with all of them to your link.
Again, I'll leave the request open until 6/16 for discussion, but the request is completed.
Thank you again,
-Derek Zable
Records Manager
WSCJTC
-
Person_-_Address_fields
-
Person_-_All_person_fields
-
Person_-_Application_1
-
Person_-_Application_3
-
Person_-_Application_2
-
Person_-_Certification
-
Person_-_Application_4
-
Person_-_email_address
-
Person_-_Employment_fields
There are too many files to display on this communication. See all files
From: Rose Terse
Hi Derek,
This is really helpful! I saw in the previous request that the data included fields regarding "Employing Organization Name" and "Employment Title". Would you mind clarifying whether WSCJTC keeps records regarding all of an officer's past and current employing departments and ranks within each department?
I really appreciate you taking the time to answer my questions!
Thanks,
Rose
From: Washington State Criminal Justice Training Commission
--- Please respond above this line ---
Hi Rose,
I'm not exactly certain what you're asking, but I'll answer what I think you're looking to know.
1. Does WSCJTC keep records on each officer?
--Yes, back until roughly 2004 but that older data isn't stellar and we've been cleaning errors and making data better the later on it is.
2. Does the WSCJTC keep Employing departments and ranks for each officer?
-- We do keep the employing for each officer. Department denotes a sub-section of a particular agency, and we do not maintain that data. If we do have it, it would be based solely if that agency put it in. agency
-- Ranks of each officer is kept, but the accuracy is very low. The WSCJTC receives notices of hire/separation, which notes their rank at the time of initial employment. If an officer is promoted within the same agency, no information is required to be sent to the WSCJTC to update their rank, unless they go through first level or mid-level supervisor training. At that time, they're required to update their rank again. All that say, Rank data isn't a reliable source of data to make large assumptions upon.
3. If you want employing organization name and employment title in your data request, please feel free to submit a new PRR and ask for it. I provided specifically what was requested. I do realize you don't know what the fields exactly are, which is why I provided those when asked.
Hope this answers your questions.
Take care,
-Derek Zable
Records Manager
From: Rose Terse
Hi Derek,
That's exactly what I was looking for. Appreciate your help!
Thanks,
Rose Terse
Files
pages
