@kdharris101 and I just had a discussion and realized we need another two new tables: genotype and sequence. These are almost exactly the same as zygosity and allele, but just record slightly different information (G/S record the actual tests you run and their outcomes; Z/A record the inferences you make based on these tests about the genome of the subject. In principle the entries in Z/A should be computed automatically from G/S but this will happen differently on a case-by-case basis so let's worry about it later). To specify them I will copy the docs about zygosity and allele below, and change the few relevant things. Note that, just like zygosities, genotypes should appear on each subject page with the exact same options, just right below zygosities.

class subjects.models.Sequence(*args, **kwargs)[source]
Bases: django.db.models.base.Model

A genetic sequence that you run a genotyping test for.

Parameters:
id (UUIDField) – Id
base_pairs (CharField) – the actual sequence of base pairs in the test.
description (CharField) - any other relevant information about this test
informal_name (CharField) – informal name in lab, e.g. ROSA-WT

class subjects.models.Genotype(*args, **kwargs)[source]
Bases: django.db.models.base.Model

A junction table between Subject and Sequence.

Parameters:
id (AutoField) – Id
subject_id (ForeignKey to Subject) – Subject
allele_id (ForeignKey to Allele) – Allele
test_result (IntegerField with choices: ((0, ‘Absent’), (1, ‘Present’)))

This is just like Experiments and Surgeries, but for procedures that aren't experiments or surgeries. "Put mouse on water restriction" or "administer rimadyl" are examples. It can literally be specified exactly the same way as surgeries I think - the difference is only semantic.

Ok, this one is slightly complicated. However it's great because it's a bit of a "killer app" - people could immediately want to stop using the spreadsheets for this functionality if it works well here. You can see what we're going for in the spreadsheet I'll send you. The reason people would immediately start using the database for this is that the weight is already logged via software, but then they have to type it into the spreadsheet again. Since I wrote this thing to send it straight to alyx, they will be able to skip that step.

The amount of water a mouse needs to get each day is determined by a formula. The formula is complicated, will get to it in a second, but it depends on the mouse's weight. So is it possible to auto-calculate this number, given the formula, either on query, or any time one of the input fields changes?

Inputs to formula:
startWeight and startDate- weight of mouse at some starting date, when it was on free water
todaysWeight and todaysDate
meanRefWeight and stdevRefWeight at both startAge and todaysAge (these come from a lookup table, and also depend on gender)

formula

mouseZscore = (startWeight-meanRefWeightStart)/stdevRefWeightStart
expectedTodaysWeight = meanRefWeightToday + mouseZscore*stdevRefWeightToday
minimumTodaysWeight = 0.7*expectedTodaysWeight; % won't use this here actually
thresholdTodaysWeight = 0.8*expectedTodaysWeight
if todaysWeight<thresholdTodaysWeight
    requiredWater = todaysWeight*0.05
else
    requiredWater = todaysWeight*0.04
end

Ok, and actually, it's slightly more complicated than that, too. So best would be if you make a demo implementation of the formula and I'll write the code for the real thing in place of your demo.

This requires:

1. The ability to auto-compute a field based on some formula. It is possible, right?
2. Subject needs to get fields "referenceWeight" and "referenceWeightDate". Also, the minor further complication that I didn't put in - also need a field "implantWeight".
3. You make a demo implementation of the formula that shows how to access fields of a subject from the function.
4. Eventually, what are the possibilities for showing a graph in the admin interface?

I.e. if I filter for "alive", I don't need a column indicating that the mice I'm now viewing are all alive.

Just the water administration and weighings for now

A solution could be to specify it via a string instead, like "GFP+; ROSA-WT+; AI95+/-"

Already exists in equipment.models.LabLocation

• subject name
• cage name
• ear mark
• gender
• DOB (or better would be Age in weeks)
• litter name
• Mother name
• Father name
• Notes (small)
• any GenotypeTests

Put the documentation in an internal google sheet.

Maybe sanitise nickname with urllib.parse.quote

@kdharris101

Currently there are three options for zygosity: absent, heterozygous, and homozygous. We need a fourth option "present" for tests that you do which don't reveal whether it is het or homo. Or, even if they do, for clarity in what was actually measured. E.g. in AI95, we genotype for GFP (which indicates whether gcamp is present, i.e. whether the AI95 gene is present at least once) and for the ROSA-WT gene, which indicates whether AI95 is missing from at least one of the rosa loci. From this we infer the het/homo/absent status of the AI95 - but I think we should record all three things. So we would record this as:

GFP: absent; ROSA-WT: present; AI95: absent
or
GFP: present; ROSA-WT: present; AI95: heterozygous
or
GFP: present; ROSA-WT: absent; AI95: homozygous

This is both unambiguous, convenient (because you could sort by the AI95 zygosity directly), and preserves the "raw data" that was actually collected.

If spreadsheets (specifically Procedure Log) are manually cleaned up for consistency, can they be imported automatically?

We should have CSV files containing what strains are in the lab, what alleles each line has, etc.

API is rapidly going to get out of control if we keep everything in one place - the URL routing should be kept per-app.

At the moment, a Litter has a mother and a father. Also, subjects have a litter. So it's theoretically possible that we have data inconsistency in the database where a litter has a mother, but the mother's litter is unassigned or is another litter...

I think it would be simpler if a litter had read-only fields mother and father which would be the oldest female/male subject among all subjects belonging to the litter?

... there aren't any for the API, which should be tested with real data

Needs only to have 4 fields: a name, an extended description, a gene name, and an auto-name tag, for use in generating the names of cages, litters, and mice that are part of this line. If it has the JSON field option, then we can also add the Jax number where appropriate.

When cages, litters, or mice are selected to be part of a line, if they do not yet have a manually typed name, then they are given an automatically generated one based on the tag.

This is used to identify subjects when there are multiple ones in a cage. It is things like "TL" for "top left" or "BR" for "bottom right" but in general should be a freeform short string.

The purpose of this is that you should be able to add a litter to a cage, so that the litter can auto-fill mother, father, and line. If you can have a button at the cage page that is like "create litter" but doesn't need the litter to be in that cage, then that would be fine too. But the mice you add to the litter will need to be in the cage also (and this information should also auto-fill) so I think it makes most sense to just make the litter have a cage.

@nsteinme what is the standard?

ie {allele}-{zygosity} or something.

• Set minimum versions
• Somehow matplotlib is now a required dependency (should be added if so...)

Matlab likes this version of ISO8601:
'yyyymmddTHHMMSS' 20000301T154517
(looks like it is valid, on wikipedia)

Could we accept this? And/or, could you update the date/time error message? Currently it is:

Datetime has wrong format. Use one of these formats instead:
YYYY-MM-DDThh:mm[:ss[.uuuuuu]][+HH:MM|-HH:MM|Z].

Would be great if, for helping future users, it said:
In matlab, use datestr(myDate, 30)
(if you can accept the format at the top of this issue, or)
In matlab, use datestr(myDate, 'yyyy-mm-ddTHH:MM:SS')
(if you want to stick with current formats)

Is it possible to export database contents to google sheets? @kdharris101 and I were discussing that we'd like to have a "test switchover" period in which people enter things into the database but it's automatically exported to a google sheet so we have a backup/fallback (that's the same as the old system) in case alyx goes down or something.

More generally, we need to have a good backup scheme, of course, for when we start using it seriously. I suppose this sort of thing is standard.

@kdharris101

There are three fields in our current spreadsheet for each subject that aren't in the database, and they pertain to UK home office data - how should we incorporate these into alyx? As JSON fields so that another implementation of Alyx could ignore them? Or as true fields?

They are: Protocol number (a number, in our case 1-4), adverse effects (an open text field), and actual severity (the options are: sub-threshold, mild, moderate, severe, or non-recovery, as per https://www.gov.uk/government/uploads/system/uploads/attachment_data/file/276014/NotesActualSeverityReporting.pdf)

pretty print json field, and use monospace font

I.e. here: http://alyx.cortexlab.net/admin/subjects/subject/

Also show line instead of strain. I will send you (@rossant) the "transgenic spreadsheet" that shows the view we are trying to roughly recreate here.

small in inline
large in normal