AMY Database structure¶
The primary tables used in AMY (that will appear in most queries) are those that store information on events, persons, memberships, and organizations. 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 integerstart
andend
Event start and end datesslug
Event's unique identifier in the form YYYY-MM-DD-sitenameurl
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 theworkshops_organization
table- Location based fields:
venue
The venue name of the eventaddress
The street address of the eventlatitude
andlongitude
Stored as floating point (decimal) numberscountry
Stored as the two character country code
contact
A list of email addresses listed as contacts for that workshopcompleted
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 theworkshops_person
table.language_id
The integer id of the language used at the workshop. This is not typically recorded. This is linked to theworkshops_language
tableopen_TTT_applications
Used only for instructor training eventsadminstrator_id
An integer representing the event organizer. This is linked to theworkshops_organization
table. Historically any organization could be listed as an administrator. Recent updates to AMY limit this to Data Carpentry, Library Carpentry, Software Carpentry, The Carpentries, or self-organized. This enforcement is at the AMY app level, not at the database level.reg_key
Eventbrite registration keyinstructors_pre
Link to both pre- and post-workshop survey results.
Unused fields¶
manual_attendance
We are no longer collecting or recording attendanceadmin_fee
,invoice_status
We are not recording financial data in AMYrepository_last_commit_hash
,repository_metadata
,metadata_all_changes
,metadata_changed
Previously used to store metadata changesinstructors_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 integerpersonal
middle
family
Three fields to hold the individual's name. Onlypersonal
is required.email
Individual's primary email address. Used for user log insecondary_email
Alternate email address. Optional.gender
Options arePrefer not to say (undisclosed)
Female
Gender variant / non-conforming
Male
Other
.gender_other
Text if individual selectedOther
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 idtwitter
Individual's Twitter user idorcid
Individual's ORCID iDurl
Link to the individual's personal websiteairport_id
An integer representing the person's self identified nearest. This is linked to theworkshops_airport
tableaffiliation
A free text field representing the person's self identified institutional affliation. This is not linked to theworkshops_organization
table.occupation
A free text field representing the person's self identified occupationuser_notes
Free text field with notes from the individualpublish_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 privilegescreated_at
Autogenerated timestamp when the record is createdlast_updated_at
Autogenerated timestamp when the record was last updatedduplication_reviewed_on
Timestamp tracking when a potential duplicate individual's record was reviewedis_active
A boolean field. Only active users are able to log in to AMYdata_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
andagreement_end
Membership term start and end datesextended
Integer; number of days the membership term end date has been extended by;NULL
value indicates no extensioncontribution_type
Financial, Person-days, or Otherworkshops_without_admin_fee_per_agreement
Integer; number of centrally organized workshops allowedworkshops_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 asworkshops_without_admin_fee_rolled_over
in preceding membershipworkshops_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 inworkshops_without_admin_fee_rolled_from_previous
in succeeding membershippublic_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 aspublic_instructor_training_seats_rolled_over
in preceding membershippublic_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 inpublic_instructor_training_seats_rolled_from_previous
in succeeding membershipinhouse_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 asinhouse_instructor_training_seats_rolled_over
in preceding membershipinhouse_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 ininhouse_instructor_training_seats_rolled_from_previous
in succeeding membershipagreement_link
A link to the Member agreement in Google Driveregistration_code
A string representing the code used by the Member site for Eventbrite registration and the instructor training applicationpublic_status
a string indicating agreement to publicising membership on The Carpentries websitesemergency_contact
text with emergency contact data for the membershipconsortium
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 instanceorganization_id
- Integer linking organisation instancerole_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 formatverbose_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 instanceperson_id
- Integer linking person instancerole_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 formatverbose_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 thehost_id
andadministrator_id
fields in theworkshops_event
table, and theorganization_id
field in theworkshops_membership
table.domain
Website of the organizationfullname
Human friendly name of the organizationcountry
Stored as the two character country codelatitude
andlongitude
Stored as floating point (decimal) numbersaffiliated_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".
Additional Tables in AMY¶
Badges¶
-
workshops_badge
Lists all available badges (Instructor, Trainer, etc.)id
Sequential, automatically assigned integer. This is used bybadge_id
in theworkshops_award
table.criteria
Description of what this badge istitle
Verbose, human friendly name of badge (e.g., Lesson Developer or Trainer)name
"back-end" badge name (e.g., lesson-developer, trainer)
-
workshops_award
Connectsworkshops_badge
andworkshops_person
tables to show what Badges have been awarded to what Personsid
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 theworkshops_badge
tableevent_id
An integer representing the event the badge came from. This is linked to theworkshops_event
tableperson_id
An integer representing the person who got the badge. This is linked to theworkshops_person
tableawarded_by_id
An integer representing the person who awarded the badge (entered it in AMY). This is linked to theworkshops_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
Connectsworkshops_role
,workshops_event
, andworkshops_person
tables to show what what Persons have served in what Roles at what Eventsid
Sequential, automatically assigned integer.event_id
An integer representing the event the person was at. This is linked to theworkshops_event
tableperson_id
An integer representing the person who was at the event. This is linked to theworkshops_person
tablerole_id
An integer representing the person's role. This is linked to theworkshops_person
tableseat_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 namedetails
Description of what tag is used forpriority
Used to control the sort order in the AMY web interface. Not relevant for any other queries.
-
workshops_event_tags
Connectsworkshops_tag
andworkshops_event
to show what Tags have been applied to what Eventsid
Sequential, automatically assigned integer.event_id
An integer representing the event that got that tag. This is linked to theworkshops_event
tabletag_id
An integer representing the tag that was assigned to that event. This is linked to theworkshops_tag
table.
Training progress¶
-
workshops_trainingrequirement
Lists all available steps towards Instructor certification (Training Event, Discussion, etc.)id
Sequential, automatically assigned integer.name
Name of requirement (DC Homework, LC Demo, etc.)url_required
Notes whether a URL is required for this type of training requirement. This only applies to the Lesson Contribution requirements.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).
-
workshops_trainingprogress
Connectsworkshops_trainingrequirement
andworkshops_person
to show what Persons have completed what steps of the checkout process.id
Sequential, automatically assigned integer.created_at
andlast_updated_at
Dates the record was created and last updated. Automatically generated by database.state
State of the trainee's progress.p
: passa
: ask to repeatf
: failn
: not evaluated yet
url
Only for Lesson Contribution requirement; links to the trainee's GitHub contributionnotes
Any human generated notesevaluated_by_id
id of the user entering this record. This is linked to theworkshops_person
tableevent_id
id of the event this trainee was at. This is linked to theworkshops_event
tablerequirement_id
id of the requirement that is being recorded. This is linked to theworkshops_trainingrequirement
tabletrainee_id
id of the trainee being evaluated. This is linked to theworkshops_person
table
Term¶
consents_term
- Stores all Terms in AMY (i.e. the privary policy).
Archive Behavior¶
When Terms
are archived (archived_at
timestamp is set), that Term
's TermOptions
and Consents
are archived as well. If the Term
was required, once archived it is no longer required in AMY.
Commonly used fields¶
slug
slug of the term. Used to uniquely identify the term.content
content of the term. This text shown to users when they consent.required_type
determines whether or not a term is considered required for the user or not. If required it will be shown to the user when they log in to consent to.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
TermOption¶
consents_termoption
- Stores all options for a stored Term
in AMY. TermOptions
are displayed when the user is asked to consent to a Term
,
and are considered answer choices for the Term
.
Archive Behavior¶
When TermOptions are archived (archived_at
timestamp is set), any Consents
that rely on that option are archived and a new unset Consent
is created by AMY for the user. If the Term
the option was attached to is required, archiving a TermOption
may result in an email sent to any users who answered with this opition.
Commonly used fields¶
term
a required foreign key to term. 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 an affirmative aggrement to the term or the user has declined the term.content
the text displayed to the user when the term is rendered.archived_at
- a nullable timestamp
Consent¶
consents_consent
- Stores all consents for all users in AMY.
Archive Behavior¶
When Consents
are archived (archived_at
timestamp is set), a new unset consent is created by AMY.
Commonly used fields¶
person
- required foreign key toPerson
.term
- required foreign key toTerm
. Provided for ease of use and reduction of queries. There is a check on the Consent model to ensure the given TermOption belongs to the Term.term_option
- a nullable foreign key to TermOption. When this field is null, the Consent is unset.archived_at
- a nullable timestamp