[Old] CDS Engineering Knowledge Base

Context

Background

The core of City Data Services (CDS) are the reports. Users login to work on reports, they get notifications about reports, and it’s their responsibility to complete the reports. There are multiple jurisdictions that use CDS, and each of them have their own unique set of reports; many of them similar to each other with minor changes. Each of these reports live in their respective jurisdictions directory alongside other pieces of code that process the data entered into the reports.

• Windows 2022 server running PHP + Perl to serve on citydataservices.net

There are two sets of users in CDS the users, and the admins. Users are agencies that are entering information into the system. Admins are typically city/county staff members that review and approve the information submitted by the users.

We use a MySQL database to store information entered into the system. Each jurisdiction has their own database. There are a handful of tables that are unique per jurisdiction, but mostly the tables in each db depend on the modules in their contract.

There are three separate main modules that a jurisdiction can have; Public Service, Multi-Family, and Single-Family. We will get more specific about the differences between the three later. We’ll list what tables go with each module, but for now we’ll focus on Public Service.

Below are the main tables that relate to each of these modules.

Public Service: progs, reports

Multi-Family: projs, props

Single-Family: sfrprogs, loans, and loanslist

PROGS

Let’s look at the columns that make up the Public Service tables starting with progs. The progs table is where the programs for agencies are stored. . Each program receives a unique number which goes into the ‘program’ column. Programs are designated to a fiscal year this goes into the ‘fyr’ column. Any data that is entered into the system and related to the program is saved into the ‘rpttext’ column. The rpttext data is pipe delimited (|). There can be multiple types a programs depending on the jurisdiction, and the type of program is specified in the ‘type’ column.

An agency can have multiple programs each with their own program number. The same program can run for multiple fiscal years. In the database that looks like multiple rows with the same program number, but different fyr values.

id Unique int for each row

program int designated to an agency’s program

fyr The working fiscal year of the program

rpttext Pipe delimited data

type Type of public service program

REPORTS

The reports table is where all of the reports for a jurisdiction live in CDS. Let’s look at the structure first then we can get into the specifics for each column.

id Unique int for each row

program int program number that the report belongs to

rptty Type of report

subdate Submission date

rptper Report period

rptnum Report Number

rpttext Pipe delimited report data

• program holds the program number that the report belongs to

• rptty is usually a set of two characters that designate the type of report (Quarterly Progress Report->QP, Expense Summary-EX)

• Subdate is the date the report was submitted

• rptper is used for sequencing reports mostly.
  • For example the Quarterly reports from first to fourth quarter typically have the following report period sequence:
    • (fiscal year)1, (fiscal year)2, (fiscal year)3, (fiscal year)4
    • (20231,20232,20233, 20234)

• rptnum is a unique report number prefixed with a letter that designates the report state.
  • An example report number could be: D1234, C4567, or A8901
  • Let’s go over the meaning of the letters
    • D: is used for draft
    • C: is used for submitted and awaiting approval
    • A: is approved/archived, the report is complete.

• rpttext holds the data for the report in a pipe delimited array.

Infrastructure and Development Process

Key Repositories
Server Setup
Development Process
Debugging