reports

0 rows


Description

Partner-submitted reports about their organizations and projects.

Columns

Column Type Size Nulls Auto Default Children Parents Comments
id int8 19 null
report_files.report_id report_files_report_id_fkeyR
report_photos.report_id report_photos_report_id_fkeyR
organization_id int8 19 null
organizations.id reports_organization_id_fkeyR
year int4 10 null
quarter int4 10 null
locked_by int8 19 √ null
users.id reports_locked_by_fkeyR
locked_time timestamptz 35,6 √ null
modified_by int8 19 √ null
users.id reports_modified_by_fkeyR
modified_time timestamptz 35,6 √ null
submitted_by int8 19 √ null
users.id reports_submitted_by_fkeyR
submitted_time timestamptz 35,6 √ null
status_id int4 10 null
report_statuses.id reports_status_id_fkeyR
body jsonb 2147483647 null
project_id int8 19 √ null
projects.id reports_project_id_fkeyN

If this report is for a specific project and the project still exists, the project ID. If the project has been deleted, this will be null but project_name will still be populated.
project_name text 2147483647 √ null

If this report is for a specific project, the name of the project as of the time the report was submitted.

Indexes

Constraint Name Type Sort Column(s)
reports_pkey Primary key Asc id
one_org_report_per_quarter Performance Asc/Asc/Asc organization_id + year + quarter
one_project_report_per_quarter Must be unique Asc/Asc/Asc/Asc organization_id + project_id + year + quarter
reports_project_id_idx Performance Asc project_id

Check Constraints

Constraint Name Constraint
cannot_submit_while_locked (((locked_time IS NULL) OR (submitted_time IS NULL)))
quarter_in_range (((quarter >= 1) AND (quarter <= 4)))
submitted_by_and_time_both_set ((((submitted_by IS NULL) AND (submitted_time IS NULL)) OR ((submitted_by IS NOT NULL) AND (submitted_time IS NOT NULL))))
locked_by_and_time_both_set ((((locked_by IS NULL) AND (locked_time IS NULL)) OR ((locked_by IS NOT NULL) AND (locked_time IS NOT NULL))))
status_reflects_locked ((((locked_by IS NULL) AND (status_id <> 3)) OR ((locked_by IS NOT NULL) AND (status_id = 3))))
status_reflects_submitted ((((submitted_by IS NULL) AND (status_id <> 4)) OR ((submitted_by IS NOT NULL) AND (status_id = 4))))

Relationships

Close relationships within degrees of separation