The lab's database is a high performance managed database cluster at digital ocean. It's backed-up daily and configured to automatically switch data handling to a standby node if the original node fails.
New participants must be added in Salesforce before they are added into the database.
1. Choose "New" under the "Participants" tab on Salesforce.
2. Type "Schuler" under "Account Name".
3. Be sure to include the participant's last name.
4. Fill in any known information. For Sona participants, this may just be the participant's name. For child participants recruited from schools, you should have at least date of birth and language information.
5. Click "Save" to save the new participant. This will also assign a Salesforce ID number to the participant. Copy this number!
6. Log in to experiments.childlanglab.com and go to the "Participants" tab.
7. Copy the Salesforce ID into the "Participant ID" box.
8. List the "Source Database" as "salesforce".
9a. For child participants, fill in remaining information and click "submit"
9b. For adults via Sona, leave the remaining information blank and allow them to fill it in when they come into the lab. Do not hit "submit" until they have filled in their information! At this time, we do not have a way for you to edit participant information.
To search for a participant, simply type the participant ID (typically the salesforce ID) into the "Participant ID" search box. This will bring up a list of all of that participant's runs. You can search by partial ID as well.
You can run an experiment via the "Run Experiment" tab.
Choose your experiment number.
Choose your condition.
Type in your participant ID number. This will be the salesforce (or prolific/ mturk) ID number you entered in the previous step to add the participant to the database.
Choose your source. For participants added through Salesforce, choose "salesforce".
Choose your name as the experimenter.
Choose the location. When running experiments at schools, please let Katie know in advance so she can add the school to the location list.
Double check everything! Once you have done that, you can hit the "Checked" toggle switch and start the experiment.
You can search for runs by experiment and participant under the "View Runs" tab. Choose your experiment in "Experiment ID". This will bring up all runs for that experiment. To narrow the runs down to a single participant, fill in the Random ID for that participant's run.
Limited information for a run can be updated at a later date. This information includes:
Whether the participant completed the experiment
Audio file upload
To modify this information, select the run you would like to modify by searching for it, clicking the row to highlight, and selecting "Modify Selected Run". You will then be taken to the end page of the experiment to modify this information. Be sure to submit the modifications when you are through.
To download a .csv file of your experiment data, select your experiment from the drop down menu. As you can see, this brings up a preview of the data that you can navigate using the toolbar at the bottom of the page. You can download this data by clicking "Export as CSV" at the top of the page.
To keep things simple, there is just one database:
Inside the database there are three relevant schemas, or named collections of tables.
Contains general purpose tables:
We create a table for each experiment to hold trial-by-trial data. These have, minimally, a trial_id and random_id (a Foreign Key linking it to Runs.RunID).
There are three main roles in the lab's database: Katie (who can create databases and roles), a Research Coordinator, and all other lab members.
CREATE ROLE pi CREATEDB CREATEROLE;CREATE ROLE labcoord;CREATE ROLE labmember;
Everyone has permission to connect to the database
GRANT CONNECT ON DATABASE defaultdb TO pi, labcoord, labmember;
Everyone can use the research and experiment_data schema, but only Katie and the Research Coordinator can use the Business schema.
GRANT USAGE ON SCHEMA "Research" TO pi, labcoord, labmember;GRANT USAGE ON SCHEMA "Business" TO pi, labcoord;
Everyone can read all of the general purpose tables:
GRANT SELECT ON"Runs", "Experiments", "IRBProtocols", "LabRoles","Locations", "Markers", "Participants","Researchers", "SourceDatabases", "WithdrawnReasons"TO pi, labcoord, labmember;
Katie and the Research Coordinator can read the full Runs table, but Lab members can only read rows containing experiments they've worked on.
-- create a role for experiment1CREATE ROLE exp1-- turn on row level securityALTER TABLE "Runs" ENABLE ROW LEVEL SECURITY;-- create security policy for experiment 1CREATE POLICY exp1_policyON "Runs"FOR SELECT TO exp1USING ("ExperimentID" = '1');-- add a user to exp1GRANT exp1 TO user_name;
Data tables are created to hold the trial-by-trial data generated in experiments. The people who work on these experiments can read these.
GRANT SELECT ON exp1_data TO pi, labcoord, exp1;
Coders get read access to these experiment tables, too, but they're blinded to Condition and other potentially biasing columns.
GRANT SELECT (RunID, TrialID, Transcription, MarkerUsed)ON exp1_dataTO coder;
Writing is very restricted. Lab members can update the Runs table, but only columns Withdrawn, WithdrawnReason, Exclude, ExcludeReason, PermalinkMedia, Notes, and CoderID. And only for experiments they work on.
Coders can write to individual experiment files, but only to columns required by the experiment's coding protocol.
Katie and the Research Coordinator can update the Affiliation and CurrRole in the Researchers table.
Everyone can add new rows to the Participants table.
Katie and the Research Coordinator can also add new rows to the Locations and Researchers table.
Only Katie can add new rows to Markers, LabRoles, IRBProtocols, and Experiments.
No one can delete anything.