field name	type	required	description	examples	notes
id	integer		primary key, internal to DB only (not a foreign key)	1	auto-incrementing
source_system	string		name of source system in which biomaterial's primary data is stored	"Labmatrix", "Lab Samples", "Staudt"	we will then need to come up with a mapping for all the non-central systems, and enforce them using that name when uploading
source_id	string		ID of biomaterial within the source system	(UUID), "AZD3-PL-0024-002"
patient_id	string		ID of source patient from whom biomaterial was collected	"1234567", "SS08-145"
patient_id_source	string		source of the patient ID	"CRIS", "SoftPath"
collected_datetime	datetime		date & time the specimen was collected		any vagaries in data (e.g., estimate, or date-only) are handled in source system, rather than here
sample_type	string		the type of sample	"blood", "serum", "prostate", "skin"	provide a catch-all "unknown" option?
status	string		the current status of the sample	"in inventory", "transferred", "exhausted", "destroyed"	if required, we will need a generic "unknown" option
status_datetime	datetime		date & time of the currently-recorded status
created	datetime		date/time the record was created
created_by	string		username of creator
last_modified	datetime		date/time the record was last modified
last_modified_by	string		username of modifier

Notes/questions

1. Do we need to track specific dates/times for specific status changes? (for example, track the date/time a specimen changed from "in inventory" to "exhausted", so that a report can be generated which specifically counts the number of specimens that were exhausted in a given time period)
2. Are we OK that any automated pushes of data into the system will then have the service-level username as the created_by/last_modified_by username?
3. Do we want to maintain a separate table of all the systems that can be represented by "source_system"? The table might contain a bit of metadata about the system (owner, tech lead, etc.), for purposes of reporting...
4. We might consider making it absolutely clear that this system isn't intended to be the primary system for any lab or group — they can't expect that the primary record of whether a sample is in inventory or destroyed would be in this system. (Or would we ever consider that, vs. having these groups use a very simplified interface to Labmatrix?)