models.py

About this file

This file is used to declare all the database models with SQLAlchemy for database access. It allows you to define tables and models in one go.
The different tables of the database defined are Roles, Agencies, Users, AgencyUsers, Requests, Events, Responses, Reasons, UserRequests, ResponseTokens, Notes, Files, Links, Instructions, Emails, Envelopes, EnvelopeTemplates, Letters, LetterTemplates, Determinations, CommunicationMethods and CustomRequestForms.

Code Issues

  • On line 601 and 607, use of eval function is comparably insecure. This can be avoided using ast.literal_eval instead.

Code Check Report 


app/models.py:429:47: E712 comparison to True should be 'if cond is True:' or 'if cond:'
app/models.py:723:121: E501 line too long (123 > 120 characters)

Documentation drawn from source code


Models for OpenRecords database

Roles(db.Model):

Define the Roles class with the following columns and relationships:

Roles - Default sets of permissions

id -- Column: Integer, PrimaryKey
name -- Column: String(64), Unique
default -- Column: Boolean, Default = False
permissions -- Column: Integer
users -- Relationship: 'User', 'role
populate(cls):

Insert permissions for each role.
__repr__(self):
Agencies(db.Model):

Define the Agencies class with the following columns and relationships:
ein - the primary key of the agencies table, 3 digit integer that is unique for each agency
parent_ein - the ein that corresponds to the agency to which the ein belongs. This is used for agencies such as the
Mayor's Office, who have a number of smaller agencies that handle their own FOIL offices.
categories - an array of strings containing the category of the agency (ex: business/education)
name - a string containing the name of the agency
next_request_number - a sequence containing the next number for the request starting at 1, each agency has its own
request number sequence
default_email - a string containing the default email of the agency regarding general inquiries about requests
appeal_email - a string containing the appeal email for users regarding the agency closing or denying requests
is_active - a boolean field denoting whether an agency is currently using the OpenRecords system to serve FOIL
requests. Defaults to False.
monitors_sub_agencies - a boolean field that denotes whether administrators for this agency should be able to edit
requests for sub-agencies. Defaults to False.

administrators - an array of user id strings that identify default admins for an agencies requests
standard_users - an array of user id strings that identify agency users (non-admins) for an agencies requests
active_users - an array of user id strings that identify agency users (admin and non-admin) that can login to
OpenRecords.
inactive_users - an array of user id strings that identify agency users (admin and non-admin) that cannot login to
OpenRecords

formatted_parent_ein(self):

Return the correctly formatted EIN for a parent agency.

Parent EINs are ALWAYS preceded by a 0, since City of New York EINs are always 3 characters.
:return: String
parent(self):
next_request_number(self):
next_request_number(self, value):
name(self):
name(self, value):
populate(cls, json_name=None):

Automatically populate the agencies table for the OpenRecords application.
__repr__(self):
Users(UserMixin, db.Model):

Define the Users class with the following columns and relationships:

guid - a string that contains the unique guid of users
is_nyc_employee - a boolean value that determines if the user is a NYC Employee
has_nyc_account - a boolean value that determines if the user has a NYC account
active - a boolean value that determines if the user can login to NYC.ID
agency_ein - a foreign key that links to the primary key of the agency table
email - a string containing the user's email
notification_email - a string containing the user's email for notifications
first_name - a string containing the user's first name
middle_initial - a string containing the user's middle initial
last_name - a string containing the user's last name
email_validated - a boolean that is set to true if the user's email has been validated
terms_of_use_accepted - a boolean that is set to true if the user has agreed to their agency's terms of use
title - a string containing the user's title if they are affiliated with an outside company
company - a string containing the user's outside company affiliation
phone_number - string containing the user's phone number
fax_number - string containing the user's fax number
mailing_address - a JSON object containing the user's address
is_authenticated(self):

Verifies the access token currently stored in the user's session
by invoking the OAuth User Web Service and checking the response.
is_active(self):
is_public(self):

Checks to see if the current user is a public user as defined below:
:return: Boolean
is_agency(self):

Check to see if the current user is an agency user.

:return: Boolean
get_agencies(self):

Returns a list of the agency ein's the user belongs to.
default_agency_ein(self):

Return the Users default agency ein.
:return: String
find_admin_agency_ein(self):

Find the ein of the agency the user is an admin for.
If the user is admin for multiple agencies it will return the first one.
:return: Agency ein
default_agency(self):

Return the Users default Agencies object.
:return: Agencies
has_nyc_id_profile(self):

Checks to see if the current user has authenticated with
NYC.ID, which means they have an NYC.ID Profile.

:return: Boolean
anonymous_request(self):

Returns the request this user is associated with
if this user is an anonymous requester.
has_agency_admin(self):

Determine if a user is an admin for at least one agency.
:return: Boolean
has_agency_active(self):

Determine if a user is active for at least one agency.
:return: Boolean
is_agency_admin(self, ein=None):

Determine if a user is an admin for the specified agency.
:param ein: Agency EIN (4 Character String)
:return: Boolean
is_agency_active(self, ein=None):

Determine if a user is active for the specified agency.
:param ein: Agency EIN (4 Character String)
:return: Boolean
agencies_for_forms(self):
name(self):
mailing_address(self):
mailing_address(self, mailing_address):
formatted_point_of_contact_number(self):
get_id(self):
es_update(self):

Call es_update for any request where this user is the requester
since the request es doc relies on the requester's name.
val_for_events(self):

JSON to store in Events 'new_value' field.
populate(cls, csv_name=None):
__init__(self, **kwargs):
__repr__(self):
Anonymous(AnonymousUserMixin):
is_authenticated(self):

Anonymous users are not authenticated.
:return: Boolean
is_public(self):

Anonymous users are treated differently from Public Users who are authenticated. This method always
returns False.
:return: Boolean
is_anonymous(self):

Anonymous users always return True
:return: Boolean
is_agency(self):

Anonymous users always return False
:return: Boolean
__repr__(self):
AgencyUsers(db.Model):

Define the AgencyUsers class with the following columns and relationships:

user_guid - a string that contains the unique guid of users
agency_ein - a foreign key that links that the primary key of the agency the request was assigned to
user_guid and agency_ein are combined to create a composite primary key
is_agency_active - a boolean value that allows the user to login as a user for the agency identified by agency_ein
is_agency_admin - a boolean value that allows the user to administer settings for the agency identified by
agency_ein
primary_agency - a boolean value that determines whether the agency identified by agency_ein is the users default
agency
Requests(db.Model):

Define the Requests class with the following columns and relationships:

id - a string containing the request id, of the form: FOIL - year 4 digits - EIN 3 digits - 5 digits for request number
agency - a foreign key that links that the primary key of the agency the request was assigned to
title - a string containing a short description of the request
description - a string containing a full description of what is needed from the request
date_created - the actual creation time of the request
date_submitted - a date that rolls forward to the next business day based on date_created
due_date - the date that is set five days after date_submitted,
this has 2 meanings depending on the current status of a request:
OPEN - the agency has to acknowledge the request by this date
not OPEN - the request must be completed by this date
submission - a Enum that selects from a list of submission methods
status - an Enum that selects from a list of different statuses a request can have
privacy - a JSON object that contains the boolean privacy options of a request's title and agency request summary
(True = Private, False = Public)
agency_request_summary - a string that contains an additional description of the request created by the agency
agency_request_summary_release_date - a datetime of when the agency_request_summary will be made public
custom_metadata - a JSON that contains the metadata from an agency's custom request forms
__init__(
val_for_events(self):

JSON to store in Events 'new_value' field.

Values that will not change or that will always
be the same on Request creation are not included.
was_acknowledged(self):
was_reopened(self):
last_date_closed(self):
days_until_due(self):
show_title(self) -> bool:
Determine whether the title should be displayed on the front-end.
The title may be displayed in the following circumstances:
- The currently logged in user is in the requests assigned agency users OR
- The current User is the requester OR
- The title is not private AND
- The request was acknowledged OR
- The request was denied OR
- The request is overdue for an acknowledgment
Returns:
bool: True if the title should be shown, False otherwise.
url(self):

Flask.request-independent url.

Since we cannot use SERVER_NAME in config (and, by extension, 'url_for'),
BASE_URL and VIEW_REQUEST_ENDPOINT will have to do.
agency_request_summary_released(self):

Determine whether the agency_request_summary has been made public and has passed its release date
es_update(self):
es_create(self):
Must be called AFTER UserRequest has been created.
es_delete(self):
Delete a document from the elastic search index
__repr__(self):
Events(db.Model):

Define the Event class with the following columns and relationships:
Events are any type of action that happened to a request after it was submitted

id - an integer that is the primary key of an Events
request_id - a foreign key that links to a request's primary key
user_id - a foreign key that links to the user_id of the person who performed the event
response_id - a foreign key that links to the primary key of a response
type - a string containing the type of event that occurred
timestamp - a datetime that keeps track of what time an event was performed
previous_value - a string containing the old value of the event
new_value - a string containing the new value of the event
__init__(
__repr__(self):
affected_user(self):
RowContent(object):
__init__(

:param verb: action describing event
:param string: format()-ready string where first field is verb and
last field is affected_user, if applicable.
:param affected_user: user affected by this event
:param no_user_string: format()-ready string where there is no event user
__str__(self):
_format_verb(verb):
history_row_content(self):

Returns html safe string for use in the rows of the history section,
or None if this event is not intended for display purposes.
Responses(db.Model):

Define the Response class with the following columns and relationships:

id - an integer that is the primary key of a Responses
request_id - a foreign key that links to the primary key of a request
type - a string containing the type of response that was given for a request
date_modified - a datetime object that keeps track of when a request was changed
content - a JSON object that contains the content for all the possible responses a request can have
privacy - an Enum containing the privacy options for a response
__init__(self, request_id, privacy, date_modified=None, is_editable=False):
preview(self):
Designated preview attribute value.
val_for_events(self):
JSON to store in Events 'new_value' field.
is_public(self):
creator(self):
communication_method_type(self):

Determine the communication method for a response.

:return: response_type.LETTER or response_type.EMAIL
:rtype: str
event_timestamp(self):

This function runs a query on the Events table to get all associated event rows for a response and returns
the newest timestamp of the newest event which will be displayed on the frontend.
:return: timestamp of the newest event row associated with a response
make_public(self):
__repr__(self):
Reasons(db.Model):

Define the Reason class with the following columns and relationships:

id - an integer that is the primary key of a Reasons
type - an enum representing the type of determination this reason corresponds to
agency_ein - a foreign key that links to the a agency's primary key
if null, this reason applies to all agencies
content - a string describing the reason

Reason are based off the Law Department's responses.

populate(cls):
UserRequests(db.Model):

Define the UserRequest class with the following columns and relationships:
A UserRequest is a many to many relationship between users who are related to a certain request
user_guid and request_id are combined to create a composite primary key

user_guid = a foreign key that links to the primary key of the User table
request_id = a foreign key that links to the primary key of the Request table
request_user_type: Defines a user by their relationship to the request.
Requester submitted the request,
Agency is a user from the agency to whom the request is assigned.
Anonymous request_user_type is not needed, since anonymous users can always browse a request
for public information.
point_of_contact = a boolean to determine the point of contact of a request
val_for_events(self):

JSON to store in Events 'new_value' field.
has_permission(self, perm):

Ex:
has_permission(permission.ADD_NOTE)
add_permissions(self, permissions):

:param permissions: list of permissions from app.constants.permissions
remove_permissions(self, permissions):

:param permissions: list of permissions from app.constants.permissions
set_permissions(self, permissions):

:param permissions: list of permissions from app.constants.permissions
or a permissions bitmask
get_permission_choice_indices(self):
ResponseTokens(db.Model):

Define the ResponseTokens class with the following columns and relationships:

id - an integer that is the primary key of ResponseTokens
token - a string consisting of a randomly-generated, unique token
response_id - a foreign key that links to a response's primary key
expiration_date - a datetime object containing the date at which this token becomes invalid
__init__(self, response_id):
generate_token():
Notes(Responses):

Define the Notes class with the following columns and relationships:

id - an integer that is the primary key of Notes
content - a string that contains the content of a note
__init__(
preview(self):
Files(Responses):

Define the Files class with the following columns and relationships:

id - an integer that is the primary key of Files
name - a string containing the name of a file (name is the secured filename)
mime_type - a string containing the mime_type of a file
title - a string containing the title of a file (user defined)
size - a string containing the size of a file
hash - a string containing the sha1 hash of a file
__init__(
preview(self):
Links(Responses):

Define the Links class with the following columns and relationships:

id - an integer that is the primary key of Links
title - a string containing the title of a link
url - a string containing the url link
__init__(
preview(self):
Instructions(Responses):

Define the Instructions class with the following columns and relationships:

id - an integer that is the primary key of Instructions
content - a string containing the content of an instruction
__init__(
preview(self):
Emails(Responses):

Define the Emails class with the following columns and relationships:

id - an integer that is the primary key of Emails
to - a string containing who the the email is being sent to
cc - a string containing who is cc'd in an email
bcc - a string containing who is bcc'd in an email
subject - a string containing the subject of an email
email_content - a string containing the content of an email
__init__(
preview(self):
val_for_events(self):
Envelopes(Responses):

Define an Envelopes class with the following columns and relationships:

id - an integer that is the primary key of Envelopes (FK to Responses)
latex - the latex used to generate the envelope PDF
__init__(
preview(self):
EnvelopeTemplates(db.Model):

Define the EnvelopeTemplates class with the following columns and relationships:

id - an integer that is the primary key of a EnvelopeTemplates
agency_ein - a foreign key that links to the a agency's primary key
if null, this envelope template applies to all agencies
title - a short descriptor for the envelope template
template_name - the name of the template to be loaded from the filesystem
populate(cls, csv_name=None):
Letters(Responses):

Define a Letters class with the following columns and relationships:

id - an integer that is the primary key of Letters (FK to Responses)
content - A string containing the content of a letter (HTML Formatted)
__init__(
preview(self):
LetterTemplates(db.Model):

Define the Reason class with the following columns and relationships:

id - an integer that is the primary key of a Reasons
type - an enum representing the type of determination this reason corresponds to
agency_ein - a foreign key that links to the a agency's primary key
if null, this reason applies to all agencies
content - a string describing the reason

Reason are based off the Law Department's responses.

populate(cls, csv_name=None):
Determinations(Responses):

Define the Determinations class with the following columns and relationships:

id - an integer that is the primary key of Determinations
dtype - a string (enum) containing the type of a determination
reason - a string containing the reason for a determination
date - a datetime object containing an appropriate date for a determination

ext_type | date significance | reason significance
---------------|----------------------------------|------------------------------------------
denial | N/A | why the request was denied
acknowledgment | estimated date of completion | why the date was chosen / additional info
extension | new estimated date of completion | why the request extended
closing | N/A | why the request closed
reopening | new estimated date of completion | N/A

__init__(
preview(self):
val_for_events(self):
CommunicationMethods(db.Model):

A response can have another correlating response (letter or email). CommunicationMethods stores the response and
its correlating response.
Ex: An acknowledgment can have a letter and a email response.

Define a CommunicationMethods class with the following columns and relationships:

response_id - an integer that is a primary key of CommunicationMetholds (FK to Responses)
method_id - an integer that is a primary key of CommunicationMethods (FK to Responses)
method_type - enum ('letters', 'emails') method associated with the response
__init__(self, response_id, method_id, method_type):
CustomRequestForms(db.Model):

Define the CustomRequestForms class with the following columns and relationships:

id - an integer that is the primary key of CustomRequestForms
agency_ein - a string that is a foreign key to the Agencies table
form_name - a string that is the name of the custom form
form_description - a string that prompts the user what the form is about and how to fill it out
field_definitions - a JSON that contains the the name of the field as the key and type of field as the value
repeatable - an integer the determines if that form is repeatable. 0 = not repeatable, 1 = can be added twice, etc.
category - an integer to separate different types of custom forms for an agency
minimum_required - an integer to dictates the minimum amount of fields required for a successful submission
populate(cls, json_name=None):

Automatically populate the custom_request_forms table for the OpenRecords application.

Source code