Skip to content

Compass Database Tables

Jump to bottom
Stephen De Vight edited this page Nov 6, 2023 · 1 revision

Compass v2 Database

Documentation last updated 11/2/23 targting database migration rev 0012.

Connecting to the Database

The Compass v2 database is a PostgreSQL 13 database, connections can be made from the command line psql client or any number of other database clients, some examples below. Connections must come from an UW IP, either through a workstation located on campus or via the Husky OnNet VPN client.

Connecting with PSQL

psql is a terminal-based front-end to PostgreSQL.

psql -h [database IP address] -U [username] -d compass-prod

Connecting with DBeaver

DBeaver is free and open source universal database tool for developers and database administrators. Windows, Mac OS X, and Linux are supported.

  1. Add a new database connection (Database > New Database Connection)
  2. Select PostgreSQL
  3. Enter the database configuration
    Connect by: Host
    Host: [database IP address]
    Database: compass-prod
    Port: 5432
    Authentication: Database Native
    Username: [username]
    Password: [password]
  1. Click Finish

Connecting with Microsoft Access

  1. Install the latest psqlodbc drivers
  2. Open the Windows ODBC Data Source Administrator tool
  3. Under User DSN click Add
  4. Select PostgreSQL Unicode
  5. Enter the database configuration
    Data Source: [name this data source]
    Server: [database IP address]
    Database: compass-prod
    Port: 5432
    User Name: [username]
    Password: [password]
  1. Click OK
  2. In Microsoft Access navigate to the External Data ribbon and select New Data Source > From Other Sources > ODBC Source
  3. Select "Link to the data source by creating a linked table"
  4. Under the Machine Data Source tab select the data source you created above and select OK
  5. Select all of the public.compass_* tables and click OK
  6. You should see the linked tables appear under All Access Objects and be able to access their data

Tables

Table Details

compass_accessgroup

AccessGroups manage their Affiliation, ContactTopic, ContactType, and ContactMethod lists. AccessGroup membership is defined externally in UW groups (Astra) and determined for a AppUser at login. Access groups for the app are created in the Django Admin. The access_group_id is the prefix of the uw-groups (user and manger) that it is affiliated with.

Column Type Notes
id integer
name character varying(50)
access_group_id character varying(50)

compass_affiliation

Departmental/Group Affiliation (e.g. CAMP, TRIO, SSS, Champions, IC Eligible)

Column Type Notes
id integer
name character varying(50)
slug character varying(50)
active boolean
editable boolean
access_group_id integer Foreign key to compass_accessgroup

compass_appuser

User of the app that content, such as contacts, is associated with.

Column Type Notes
id integer
uwnetid character varying(50)

compass_cohort

Cohort to which a student can be assigned.

Column Type Notes
id integer
start_year smallint
end_year smallint

compass_contact

A contact/appointment with a student. For OMAD contacts are generated via the automated check-in system and updated by advisers, or created manually by advisers.

Column Type Notes
id integer
noshow boolean
notes text
actions text
source character varying(50)
created_date timestamp with time zone
app_user_id integer Foreign key to compass_appuser
contact_type_id integer Foreign key to compass_contacttype
student_id integer Foreign key to compass_student
checkin_date timestamp with time zone
contact_method_id integer Foreign key to compass_contactmethod

compass_contact_access_group

Bridge table between compass_contact and compass_accessgroup

Column Type Notes
id integer
contact_id integer Foreign key to compass_contact
accessgroup_id integer Foreign key to compass_accessgroup

compass_contact_contact_topics

Bridge table between compass_contact and compass_contacttopic

Column Type Notes
id integer
contact_id integer Foreign key to compass_contact
contacttopic_id integer Foreign key to compass_contacttopic

compass_contactmethod

The method used in the contact. These are created for a given access group by the access group managers. Examples include Telephone, In-Person, and Video Conference.

Column Type Notes
id integer
name character varying(50)
slug character varying(50)
active boolean
editable boolean
access_group_id integer Foreign key to compass_accessgroup

compass_contacttopic

Topics discussed with a student. These are created for a given access group by the access group managers. Examples include Add/Drop Class, Join/Affiliate, Academic Difficulties, Hardship Withdrawl, Internships, Research Opportunities, Graduate Professional School, and Testing/Assessment.

Column Type Notes
id integer
name character varying(50)
slug character varying(50)
active boolean
editable boolean
access_group_id integer Foreign key to compass_accessgroup

compass_contacttype

Type of contact with a student. These are created for a given access group by the access group managers. Examples include Quick Question, Appointment, and Workshop.

Column Type Notes
id integer
name character varying(50)
slug character varying(50)
active boolean
editable boolean
access_group_id integer Foreign key to compass_accessgroup

compass_eligibilitytype

A service or resource that is available to students

Column Type Notes
id integer
name character varying(50)
slug character varying(50)
editable boolean
access_group_id integer Foreign key to compass_accessgroup

compass_student

The student model is used to associate app data to a student. In this app only system_key is stored as it provides a unique key that is used to request student information from the PDS. Besides system-key, no student information is ever stored in the app, all student data is fetched directly from the PDS since it is kept up to date via automated jobs.

Column Type Notes
id integer
system_key character varying(50)

compass_studentaffiliation

Affiliation assigned to a student

Column Type Notes
id integer
date date
actively_advised boolean
affiliation_id integer Foreign key to compass_affiliation
student_id integer Foreign key to compass_student

compass_studentaffiliation_cohorts

Bridge table between compass_cohort and compass_studentaffiliation

Column Type Notes
id integer
studentaffiliation_id integer Foreign key to compass_studentaffiliation
cohort_id integer Foreign key to compass_cohort

compass_studenteligibility

Services and resources for which a Student is provided access

Column Type Notes
id integer
student_id integer Foreign key to compass_student

compass_studenteligibility_eligibility

Bridge table linking compass_studenteligibility to compass_eligibilitytype

Column Type Notes
id integer
studenteligibility_id integer Foreign key to compass_studenteligibility
eligibilitytype_id integer Foreign key to compass_eligibilitytype

compass_visit

Student interaction with service

Column Type Notes
id integer
course_code character varying(64)
checkin_date timestamp with time zone
checkout_date timestamp with time zone
access_group_id integer Foreign key to compass_accessgroup
student_id integer Foreign key to compass_student
visit_type_id integer Foreign key to compass_visittype

compass_visittype

Type of student visit. These are created for a given access group by the access group managers. Examples include IC Drop-In Tutoring and and IC Workshop.

Column Type Notes
id integer
name character varying(50)
slug character varying(50)
editable boolean
access_group_id integer Foreign key to compass_accessgroup
Clone this wiki locally