AMY Database structure

The primary tables used in AMY (that will appear in most queries) are those that store information on events, persons, memberships, organizations, and instructor training. Additional tables provide more information about events, persons, memberships, and organizations. This information can be useful in writing SQL queries in redash.

Primary Tables in AMY

Events

workshops_event - Primary table for all event data.

Commonly used fields

• id Sequential, automatically assigned integer
• start and end Event start and end dates
• slug Event's unique identifier in the form YYYY-MM-DD-sitename
• url Event's website. Typically in the format username.github.io/YYYY-MM-DD-sitename (but not required)
• host_id An integer representing the Event Host. This is linked to the workshops_organization table
• Location based fields:
  • venue The venue name of the event
  • address The street address of the event
  • latitude and longitude Stored as floating point (decimal) numbers
  • country Stored as the two character country code
• contact A list of email addresses listed as contacts for that workshop
• completed A Boolean field to note that all work (including workshop coordination and data entry) is complete.
• assigned_to_id The id of the Regional Coordinator or other Carpentries Core Team member assigned to this event. This is linked to the workshops_person table.
• language_id The integer id of the language used at the workshop. This is not typically recorded. This is linked to the workshops_language table
• open_TTT_applications Used only for instructor training events
• adminstrator_id An integer representing the event organizer. This is linked to the workshops_organization table. Historically any organization could be listed as an administrator. Recent updates to AMY limit this to Data Carpentry, Library Carpentry, Software Carpentry, Instructor Training, Collaborative Lesson Development Training, or self-organized. This enforcement is at the AMY app level, not at the database level.
• reg_key Eventbrite registration key
• instructors_pre Link to both pre- and post-workshop survey results.

Unused fields

• manual_attendance We are no longer collecting or recording attendance
• admin_fee, invoice_status We are not recording financial data in AMY
• repository_last_commit_hash, repository_metadata, metadata_all_changes, metadata_changed Previously used to store metadata changes
• instructors_post learners_longterm learners_post learners_pre Previously used to store links to surveys.

Persons

workshops_person - Primary table for all person data. This includes all individuals, regardless of their role with The Carpentries.

Commonly used fields

• id Sequential, automatically assigned integer
• personal middle family Three fields to hold the individual's name. Only personal is required.
• email Individual's primary email address. Used for user log in
• secondary_email Alternate email address. Optional.
• gender Options are Prefer not to say (undisclosed), Female, Gender variant / non-conforming, Male, Other.
  • gender_other Text if individual selected Other
• may_contact A boolean field. We may not contact people if this field is false. This field has been replaced by new-style consents (see also 2021 Consents Project).
• github Individual's GitHub user id
• twitter Individual's Twitter user id
• orcid Individual's ORCID iD
• url Link to the individual's personal website
• airport_id An integer representing the person's self identified nearest. This is linked to the workshops_airport table
• affiliation A free text field representing the person's self identified institutional affliation. This is not linked to the workshops_organization table.
• occupation A free text field representing the person's self identified occupation
• user_notes Free text field with notes from the individual
• publish_profile A boolean field that acknowledges permission to publish the individual's profile on our website pages such as the Instructors, Trainers, or Maintainers pages. This field has been replaced by new-style consents.
• country Self identified country of residence. Stored as the two character country code
• lesson_publication_consent Allows individual to consent to publishing their name associated with lesson contributions. Individual can select publication by name, ORCID iD, or GitHub id, or not consent to publishing their name or identity. This field has been replaced by new-style consents.

Less commonly used fields

• last_login When the user last logged in (regardless of their activity or permissions)
• is_superuser Boolean field to note whether the person is a superuser, giving them all administrative privileges
• created_at Autogenerated timestamp when the record is created
• last_updated_at Autogenerated timestamp when the record was last updated
• duplication_reviewed_on Timestamp tracking when a potential duplicate individual's record was reviewed
• is_active A boolean field. Only active users are able to log in to AMY
• data_privacy_agreement Acknowledges user has read our data policy. Required the first time any user logs in

Other fields

• password Not stored as plain text. Do not use or modify this field for anything.

Memberships

workshops_membership - Stores information about each membership agreement.

• id Sequential, automatically assigned integer.
• variant Membership type (Gold, Silver, etc.)
• agreement_start and agreement_end Membership term start and end dates
• extended Integer; number of days the membership term end date has been extended by; NULL value indicates no extension
• contribution_type Financial, Person-days, or Other
• workshops_without_admin_fee_per_agreement Integer; number of centrally organized workshops allowed
• workshops_without_admin_fee_rolled_from_previous Integer; number of centrally-organised workshops allowed that was rolled over from previous membership. This should be the same as workshops_without_admin_fee_rolled_over in preceding membership
• workshops_without_admin_fee_rolled_over Integer; number of centrally-organised workshops allowed that was rolled over to succeeding membership. The same number should be recorded in workshops_without_admin_fee_rolled_from_previous in succeeding membership
• public_instructor_training_seats Integer; number of public seats allowed in instructor training events in the original contract.
• additional_public_instructor_training_seats Integer; number of additional public seats allowed in instructor training events beyond the original contract.
• public_instructor_training_seats_rolled_from_previous Integer; number of public instructor training seats allowed that was rolled over from previous membership. This should be the same as public_instructor_training_seats_rolled_over in preceding membership
• public_instructor_training_seats_rolled_over Integer; number of public instructor training seats allowed that was rolled over to succeeding membership. The same number should be recorded in public_instructor_training_seats_rolled_from_previous in succeeding membership
• inhouse_instructor_training_seats Integer; number of in-house seats allowed in instructor training events in the original contract.
• additional_inhouse_instructor_training_seats Integer; number of additional in-house seats allowed in instructor training events beyond the original contract.
• inhouse_instructor_training_seats_rolled_from_previous Integer; number of in-house instructor training seats allowed that was rolled over from previous membership. This should be the same as inhouse_instructor_training_seats_rolled_over in preceding membership
• inhouse_instructor_training_seats_rolled_over Integer; number of in-house instructor training seats allowed that was rolled over to succeeding membership. The same number should be recorded in inhouse_instructor_training_seats_rolled_from_previous in succeeding membership
• agreement_link A link to the Member agreement in Google Drive
• registration_code A string representing the code used by the Member site for Eventbrite registration and the instructor training application
• public_status a string indicating agreement to publicising membership on The Carpentries websites
• emergency_contact text with emergency contact data for the membership
• consortium a boolean value indicating consortium (umbrella for more than one organisation member)

Member

workshops_member - Stores information about organisations and their roles in memberships.

• id Sequential, automatically assigned integer.
• membership_id - Integer linking membership instance
• organization_id - Integer linking organisation instance
• role_id - Integer linking member role instance

MemberRole

workshops_memberrole - Stores roles for organisations in memberships.

• id Sequential, automatically assigned integer.
• name string with role's name, e.g. contact_signatory - preferably a computer-friendly format
• verbose_name string with role's name suitable for humans, e.g. Contact Signatory

MembershipTask

fiscal_membershiptask - Stores information about persons and their roles in memberships.

• id Sequential, automatically assigned integer.
• membership_id - Integer linking membership instance
• person_id - Integer linking person instance
• role_id - Integer linking membership person role instance

MembershipPersonRole

fiscal_membershippersonrole - Stores roles for persons in memberships.

• id Sequential, automatically assigned integer.
• name string with role's name - preferably a computer-friendly format
• verbose_name string with role's name suitable for humans

Organizations

workshops_organization - Stores all organizations in AMY.

• id Sequential, automatically assigned integer. This is used by the host_id and administrator_id fields in the workshops_event table, and the organization_id field in the workshops_membership table.
• domain Website of the organization
• fullname Human friendly name of the organization
• country Stored as the two character country code
• latitude and longitude Stored as floating point (decimal) numbers
• affiliated_organizations Many-to-many relationship between organizations; the purpose of this field is to "link together" organisations that in some way are related. For example, "University of California" organisation can be linked to "University of California, Berkeley", "University of California, Davis", and "University of California, Los Angeles". This field is not being used.

Training applications

workshops_trainingrequest - Primary table for all Instructor Training applications

• personal, middle, family Three fields to hold the individual's name.
• person_id This is linked to the workshops_person table after the trainee is matched to a Person
• state State of the individual's application. Options are a (accepted), p (pending), w (withdrawn), d (discarded)
• email Individual's primary email address. Used for user log in
• secondary_email Alternate email address. Optional.
• affiliation A free text field representing the person's self identified institutional affliation. This is not linked to the workshops_organization table.
• country Stored as the two character country code
• location Free text field to share location within country (city, province, state, etc.)
• underresourced Boolean field whether individual is from an underresourced area
• underrepresented Boolean field whether individual is from an underrepresented demographic
  • underrepresented_details Details on the individual's underrepresented status
• github Individual's GitHub user id
• review_process Whether this is an open or pre-approved (usually member) Training application
• group_name Registration code used for pre-approved requests
• score_auto Automatically generated score from application review
• score_manual Manually adjusted score from application review
• score_notes Notes about the individual's score
• workshop_teaching_agreement Agreement that applicant agrees to teach a Carpentries workshop
• max_travelling_frequency Frequency individual may travel to teach. Select one of given options.
• teaching_frequency_expectation Frequency individual may teach. Select one of given options.
• occupation Applicant's current occupation/career stage. Select one of given options.
  • occupation_other Text if individual selected Other
• previous_training Level of previous training as a teacher/instructor. Select one of given options.
  • previous_training_other Text if individual selected Other
• programming_language_usage_frequency Frequency in using programming languages. Select one of given options.
• previous_experience Applicant's previous experience teaching. Select one of given options.
• previous_experience_other Text if individual selected Other
• previous_experience_explanation Free text field to describe teaching experience
• nonprofit_teaching_experience Free text field to describe nonprofit teaching experience
• reason Free text field to describe why individual wants to attend Instructor Training
• data_privacy_agreement Consent agreeing to The Carpentries' data privacy policy
• code_of_conduct_agreement Consent agreeing to The Carpentries' Code of Conduct
• user_notes Free text field with notes from the individual
• created_at and last_updated_at Dates the record was created and last updated. Automatically generated by database.

Training progress

workshops_trainingrequirement - Lists all available steps towards Instructor certification (Training Event, Welcome Session, etc.)

• id Sequential, automatically assigned integer.
• name Name of requirement (Demo, Welcome Session, etc.)
• url_required Notes whether a URL is required for this type of training requirement. Currently, no requirements require a URL, but some Get Involved activities do (see below).
• event_required Notes whether an event is required for this type of training requirement. This only applies to the Training (the actual event they attended).
• involvement_required Notes whether an activity must be provided for this type of training requirement. This only applies to the Get Involved requirement where multiple different activities are acceptable.

trainings_involvement - Lists all activities that are accepted as part of the Get Involved training requirement (GitHub contribution, Workshop Instructor/helper, etc.) Note that this table uses the trainings_ prefix, rather than workshops_.

• id Sequential, automatically assigned integer.
• name A short name for the activity (GitHub Contribution, Community Meeting, etc.)
• display_name A fully descriptive name for the activity (e.g. Attended an Instructor meeting, regional meetup, or other community meeting)
• url_required Notes whether a URL must be provided for this type of activity.
• date_required Notes whether a date must be provided for this type of activity. Currently, all activities require a date.
• notes_required Notes whether text notes must be provided for this type of activity (such as when tracking an activity that is not covered by the existing options). These notes can come from either the trainee or an administrator.

workshops_trainingprogress - Connects workshops_trainingrequirement, trainings_involvement, and workshops_person to show what Persons have completed what steps of the checkout process.

• id Sequential, automatically assigned integer.
• created_at and last_updated_at Dates the record was created and last updated. Automatically generated by database.
• state State of the trainee's progress.
  • p: pass
  • a: ask to repeat
  • f: fail
  • n: not evaluated yet
• requirement_id id of the requirement that is being recorded. This is linked to the workshops_trainingrequirement table
• involvement_type_id only for Get Involved requirement, id of the activity that is being recorded. This is linked to the trainings_involvement table
• trainee_id id of the trainee being evaluated. This is linked to the workshops_person table
• url Link to a GitHub contribution, workshop website, etc.
• event_id id of the event this trainee was at. This is linked to the workshops_event table
• date only for Get Involved requirement, the date of the activity (first day if the activity covered multiple days)
• trainee_notes only for Get Involved requirement, notes submitted by the trainee
• notes Notes written by an administrator

Consents

Consent Terms

consents_term - Stores all types of consent terms in AMY (e.g. privacy policy, permission to contact).

• slug slug of the term. Used to uniquely identify the term.
• content content of the term. This text is shown to users when they consent.
• training_request_content if set, the regular content is replaced with this text when displaying this term on the instructor training request form.
• required_type determines whether or not a term is considered required for the user or not. If required it is presented to the user when they first log in.
• help_text additional text shown to the user in order to give more context on the term.
• short_description a short description of the consent, shown in the admin view of a profile
• archived_at if this term is archived, a timestamp of when it was archived

Consent Term Options

consents_termoption - Stores all options for all terms in AMY. Options are displayed when the user is asked to consent to a particular term. Options are considered answer choices for the term.

• term_id id of the term this option belongs to. This is linked to the consents_term table. Unarchived term options attached to a term will be displayed to the user when the term is rendered.
• option_type determines whether or not a term option is considered as an agreement or a decline for that term.
• content the text displayed to the user when the term is rendered.
• archived_at a timestamp of when the option was archived or NULL if it wasn't

Individual Consent

consents_consent - Stores all consents for all users in AMY.

• person_id id of the person providing the consent. This is linked to the workshops_person table.
• term_id id of the term this consent applies to. This is linked to the consents_term table. There is a check on the Consent model to ensure the given TermOption belongs to the Term.
• term_option_id id of the term option chosen in this consent. This is linked to the consents_termoption table. When this field is null, the consent has not been set by the user.
• archived_at if this consent is archived, a timestamp of when it was archived.

Training Request Consent

consents_trainingrequestconsent Stores all consents for all instructor training requests in AMY.

• training_request_id id of the training request this consent option belongs to. This is linked to the workshops_trainingrequest table.
• term_id id of the term this consent applies to. This is linked to the consents_term table. There is a check on the Consent model to ensure the given TermOption belongs to the Term.
• term_option_id id of the term option chosen in this consent. This is linked to the consents_termoption table. When this field is null, the consent has not been set by the user.
• archived_at a timestamp of when the consent was archived or NULL if it wasn't.

Additional Tables in AMY

Badges

• workshops_badge Lists all available badges (Instructor, Trainer, etc.)

  • id Sequential, automatically assigned integer. This is used by badge_id in the workshops_award table.
  • criteria Description of what this badge is
  • title Verbose, human friendly name of badge (e.g., Lesson Developer or Trainer)
  • name "back-end" badge name (e.g., lesson-developer, trainer)

• workshops_award Connects workshops_badge and workshops_person tables to show what Badges have been awarded to what Persons

  • id Sequential, automatically assigned integer.
  • awarded Date the badge was awarded. This is usually the date it was recorded in AMY, not the date the person completed all requirements.
  • badge_id An integer representing the badge. This is linked to the workshops_badge table
  • event_id An integer representing the event the badge came from. This is linked to the workshops_event table
  • person_id An integer representing the person who got the badge. This is linked to the workshops_person table
  • awarded_by_id An integer representing the person who awarded the badge (entered it in AMY). This is linked to the workshops_person table

Roles

• workshops_role Lists all available roles (Instructor, Helper, Learner, etc.)

  • id Sequential, automatically assigned integer.
  • verbose_name Verbose, human friendly name of role (e.g., Workshop host, Supporting Instructor)
  • name "back end" task name (e.g., workshop-host, supporting-instructor)

• workshops_task Connects workshops_role, workshops_event, and workshops_person tables to show what what Persons have served in what Roles at what Events

  • id Sequential, automatically assigned integer.
  • event_id An integer representing the event the person was at. This is linked to the workshops_event table
  • person_id An integer representing the person who was at the event. This is linked to the workshops_person table
  • role_id An integer representing the person's role. This is linked to the workshops_person table
  • seat_membership_id Used for Instructor Training Learner role only. An integer representing the membership this seat was assigned to.
  • seat_public Used for Instructor Training Learner role only. Determines if the seat counts as public or in-house for the specific membership.
  • seat_open_training Used for Instructor Training Learner role only. Boolean field noting whether this was an open (non-member) training seat.
  • title andurl are not used.

Tags

• workshops_tag Lists all availabe tags for an Event (SWC, DC, LC, Online, Pilot, Circuits, etc.)

  • id Sequential, automatically assigned integer.
  • name "back end" tag name
  • details Description of what tag is used for
  • priority Used to control the sort order in the AMY web interface. Not relevant for any other queries.

• workshops_event_tags Connects workshops_tag and workshops_event to show what Tags have been applied to what Events

  • id Sequential, automatically assigned integer.
  • event_id An integer representing the event that got that tag. This is linked to the workshops_event table
  • tag_id An integer representing the tag that was assigned to that event. This is linked to the workshops_tag table.