Notes/manual additions to datastream

Feedback and suggestions
1

The pioreactor system provides a great way to feed data that is collected automatically into a database for later retrieval. But it’s definitely useful to be able to add at least two more forms of data:

  • Manual notes, that are just strings etc. that are logged to specific timepoints of an experiment. e.g. - “This is where there was a power-cut”
  • Manual/offline readings, e.g. for something like dry cell weight, or offline OD measurements from larger reactors

From the back-end perspective it seems fairly trivial to create plugins that take this data and add it to the database in the same way that other jobs do. However the UI implementation is something that needs thinking about. I thought we could discuss here!

1 Like
2

I agree! I’ve had the idea of manual logs for a while (and you’re right that it’s really easy to do in the backend), just had trouble with how to design it into the UI.

  • Manual/offline readings, e.g. for something like dry cell weight, or offline OD measurements from larger reactors

I think I’ve always thought that the Description box on the Overview could be used for this. But you are thinking about something more structured? Can you describe the “shape” more?

3

Basically a form in the UI that would insert a reading into the db in the same way that a reading coming from a sensor would be inserted. Will be a much sparser dataset but still very useful to have.

  • Unit and experiment autofilled
  • DateTime defaults to current datetime but can be altered
  • Database and column to insert into
  • Value

I’d want to be able to say “At 2024-12-17 16:15 I took a manual OD reading and the value was 15.4” and have this inserted into the db. (Obviously not plain-text input, until a chatbot is integrated…)

I don’t know, however, whether the database table should be defined somewhere else, and columns for units etc. should be autofilled. Seems like it would add a whole load of problems with typos etc. creating new tables.

4

Here’s a simple mock-up of a “record a log” feature that should satisfy the idea of manual notes.

Next extension would be to add these notes the the charts…

The offline readings is interesting, but still relies on some sort of architecture that allows plugins to edit the UI.

2 Likes
5

That’s awesome! I can definitely see how the same form could be used for adding offline readings. A drop-down to select either ‘Note’ or ‘data’ and then if data is selected brings up ‘table’ and ‘column’ and restricts entry to the correct data type which then sends it to mqtt/db would be all that’s needed. Display of the readings is then handled in the same way as other data.

If this is writing to mqtt then maybe all that’s needed is for plugins that watch that stream and add to the database. Dropdowns for table/column would be populated by the additional_sql file as part of the plugin.

Essentially plugin architecture would stay the same apart from the fact that the code wouldn’t actually do any writing to mqtt

1 Like
6

Is this in a branch somewhere I can play with it or is it mocked up in canva or something?

7

it’s on main branch now in _frontend repo, and the required backend endpoint is in develop on pioreactorui repo

1 Like
8

This is the solution that I’ve come up with for manually adding OD values. As long as the “source” in the manual addition is “od_reading” the value in the text gets added to the od_readings table.

@CamDavidsonPilon does this seem sensible/any reason it might not work? (apologies if the code is weird I don’t know SQL so it’s ChatGPT)

Would this be overwritten during an update? Of course there’s also weirdness around the schema of the od_readings table requiring an angle and channel which don’t exist for manual readings.

DROP TRIGGER IF EXISTS insert_od_reading_from_ui_log;

CREATE TRIGGER insert_od_reading_from_ui_log
AFTER INSERT ON logs
WHEN NEW.task = 'od_reading' AND NEW.source = 'UI'
BEGIN
  UPDATE od_readings
  SET od_reading = CAST(NEW.message AS REAL),
      angle = 180,
      channel = 1
  WHERE experiment = NEW.experiment
    AND pioreactor_unit = NEW.pioreactor_unit
    AND timestamp = NEW.timestamp;

  INSERT INTO od_readings (
    experiment,
    pioreactor_unit,
    timestamp,
    od_reading,
    angle,
    channel
  )
  SELECT
    NEW.experiment,
    NEW.pioreactor_unit,
    NEW.timestamp,
    CAST(NEW.message AS REAL),
    180,
    1
  WHERE (SELECT changes() = 0);
END;
9

wow that’s clever!

Would this be overwritten during an update?

nope, this is persisted until you delete it with a DROP TRIGGER statement.

1 Like