Database

Information about the lab's database

Basics

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.

It's highly secure and can only be accessed directly by trusted sources or via the password protected front-end web page at experiments.childlanglab.com. The password can be found in the Lab HQ.

Participants

Adding a new participant:

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.

If you accidentally submit the new participant before date of birth, etc, can be added, be sure to get that information from the participant and edit it in salesforce so we have it on file!

Searching for participants:

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.

Run Experiment

You can run an experiment via the "Run Experiment" tab.

  1. Choose your experiment number.

  2. Choose your condition.

  3. 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.

  4. Choose your source. For participants added through Salesforce, choose "salesforce".

  5. Choose your name as the experimenter.

  6. Choose the location. When running experiments at schools, please let Katie know in advance so she can add the school to the location list.

  7. Double check everything! Once you have done that, you can hit the "Checked" toggle switch and start the experiment.

The easiest way to make sure Katie sees a location needs to be added is to add it as a "To Do" and assign it to her. You can also let Ariel know that the location needs to be added, and she can follow up!

Runs

View runs

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.

You can find the Random ID for a given participant's run by searching for the desired participant as shown previously. This will give you a list of all runs the participant has completed with Random IDs. You can copy and paste this Random ID to view/ modify the run.

Modify runs

Limited information for a run can be updated at a later date. This information includes:

  • Whether the participant completed the experiment

  • Experimenter notes

  • 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.

Code Data

Coming Soon!

Download Experiment Data

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.

Access via in lab iMacs

Databases

To keep things simple, there is just one database: defaultdb

Schemas and tables

Inside the database there are three relevant schemas, or named collections of tables.

research:

Contains general purpose tables:

  • experiments

  • irb_protocols

  • lab_roles

  • locations

  • markers

  • participants

  • researchers

  • runs

  • source_databases

  • withdrawn_reasons

experiment-data:

  • 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).

business

  • TBD.

Roles and access

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;

USAGE

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;

SELECT (Read)

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 experiment1
CREATE ROLE exp1
-- turn on row level security
ALTER TABLE "Runs" ENABLE ROW LEVEL SECURITY;
-- create security policy for experiment 1
CREATE POLICY exp1_policy
ON "Runs"
FOR SELECT TO exp1
USING ("ExperimentID" = '1');
-- add a user to exp1
GRANT 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_data
TO coder;

UPDATE (Write)

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.

INSERT (Add rows)

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.

Alfred, our server, can add new rows to Runs and any individual experiment data tables. Alfred has no other permissions.

DELETE

No one can delete anything.