Database schema enhancement

[FyzHsn]

Current Database Schema (V9)

Currently, the key attribute in the electrons table is listed as an integer. However, in the workflow defined below, key in the node metadata (shown below) is stored as a string.

@ct.electron
def task_1(x, y):
    return {'key_1': x * y, 'key_2': x + y}

@ct.electron
def sublattice_task(z):
    return z

@ct.electron
@ct.lattice
def task_2(z):
    res = sublattice_task(z)
    return res

@ct.lattice
def workflow(a, b):
    res_1 = task_1(a, b)
    res_2 = task_2(res_1['key_1'], b)
    return res_2

Corresponding node metadata is:

{'function': <covalent._workflow.transport.TransportableObject object at 0x7f8bf3b00430>,
  'id': 3,
  'key': 'key_1',
  'metadata': {'call_after': [],
               'call_before': [],
               'deps': {},
               'executor': 'dask'},
  'name': ':subscripted:task_1()[key_1]'}

On the other hand, the following workflow, would store an integer value in the key field.

@ct.electron
def task_1(x, y):
     return [x * y, x + y]

@ct.electron
def sublattice_task(z):
    return z

@ct.electron
@ct.lattice
def task_2(z):
    res = sublattice_task(z)
    return res

@ct.lattice
def workflow(a, b):
    res_1 = task_1(a, b)
    res_2 = task_2(res_1[1], b)
    return res_2

Question: Since, this field can be an integer or a string should we store key as a pickled file and change key in favor of key_filename in the electrons table?

[santoshkumarradha]

Actually that is a good question, @wjcunningham7 any thoughts ?

[FyzHsn]

@cjao or @kessler-frost did you have any opinions on this?

[FyzHsn]

I'm going ahead and changing this to key_filename.

[cjao]

The key can be any valid dictionary key (just needs to be hashable), and as of #754 will appear to the dispatcher as just a TransportableObject. So we should in fact store the key externally.

[FyzHsn]

Excellent!

[FyzHsn]

Further enhancements for V10 of the Database schema:

• Add output_filename field to the Electron table. Edit: Not required since the results_filename is actually storing the output field in the electron metadata.
• Add electron_num and completed_electron_num fields to the Lattice table.
• Add deps_filename field to the Electron table.
• Remove key field from the Electron table.
• Add key_filename field to the Electron table.
• Add call_after_filename and call_before_filename to the Electron table.

[FyzHsn]

@mshkanth Just added V10 schema in this file - plz let me know if you don't have access. Could you also double-check if the additions make sense to you folks?

@kessler-frost Could you check the doc and let me know if the definitions for call_after_filename and call_before_filename make sense?

[FyzHsn]

@FyzHsn - My comments from #776 reg adding electron_num and completed_electron_num fields to the Lattice table

Possible implementations - a. Maintain these columns from the dispatcher itself. ie Whenever the dispatcher updates the electrons table, it will also update the number of completed electrons b. Delegate it as a DB responsibility For Eg as a Trigger (Performance implications to be studied)

@mshkanth at this moment we're leaning towards point b (although it is less efficient) since we don't want to mess with the dispatcher code. It might be preferable to carry out option a at a future stage when we're making enhancements. Note that, the DB usage will undergo many such improvement stages since the next step would be to make it on-prem compatible.

[mshkanth]

@FyzHsn - Is this the file?

Can you grant access for me? Received this error -

The file was not found. It does not exist or you do not have access. (Shrikanth Mohan, [email protected])

[mshkanth]

All - Got access to above file. Above screenshot is from v1. Was able to access v10.

[FyzHsn]

Nice @mshkanth! Yeah there are tabs in the file corresponding to each version.

[mshkanth]

Proposal to maintain both old & new DB for a period of overlap

Would request to implement the new DB schema in a way that the old DB still remains & is maintained the same way as it would be earlier. This will enable Team Psi to smoothly transition from the old DB to the new DB. Once we are done with moving, we can do away with maintaining the old DB and can even remove it.

Cc @FyzHsn

[FyzHsn]

@wjcunningham7 @cjao @scottwn @mshkanth Let me know if there are any concerns or modifications required to this plan.

[mshkanth]

@FyzHsn @scottwn @wjcunningham7 @cjao - Pls note that dropping the old DB needs to be coordinated with when the web app can be completely re-factored to the new DB (which inturn depends on v10 release etc). If a DB drop is executed and pushed to end users without the corresponding web app refactoring, the web app will essentially break. This was the background why we initially suggested maintaining both DBs for an overlap period.

Cc @santoshkumarradha

[cjao]

Hi @mshkanth, @scottwn recently implemented a server endpoint that reconstructs the Result object from the new DB. Until the UI adapts to the full granularity of the new DB, you can query /result/<dispatch_id> instead of the old /api/results/<dispatch_id> endpoint.

[FyzHsn]

Excellent! Thanks for pointing this out @cjao

[mshkanth]

Hi @mshkanth, @scottwn recently implemented a server endpoint that reconstructs the Result object from the new DB. Until the UI adapts to the full granularity of the new DB, you can query /result/<dispatch_id> instead of the old /api/results/<dispatch_id> endpoint.

Thanks for the info Casey. Cc @ArunPsiog @Aravind-psiog @mpvgithub @Prasy12

[mshkanth]

Hi @FyzHsn @cjao - Quick question. A few weeks back there was a mention that some of the attributes of an electron will be dynamic and not pre-set. Possible implemetations was to store a JSON in the electrons table or also potentially looking at NoSQL.

While this might not directly impact the Web app, wanted to clarify from a knowledge stand point. Can you help with what the final solution was?

[FyzHsn]

Hey @mshkanth, the electron attributes/metadata will be written to the structured SQL database as fields. If the field value has not been set yet, it will be NULL.

[mshkanth]

Hey @mshkanth, the electron attributes/metadata will be written to the structured SQL database as fields. If the field value has not been set yet, it will be NULL.

Thanks @FyzHsn. So we are able to pre-determine all attributes and have them as columns in the electrons table. Correct?

[FyzHsn]

Hey @mshkanth, the electron attributes/metadata will be written to the structured SQL database as fields. If the field value has not been set yet, it will be NULL.

Thanks @FyzHsn. So we are able to pre-determine all attributes and have them as columns in the electrons table. Correct?

Yes @mshkanth exactly! When me and @cjao discussed it, we had decided that having all the metadata that could be used by UI and for the workflow execution to have them as explicit fields in the tables. This would bring a lot of transparency to all the engineering work. I should mention that in the backend currently, a lot of metadata is being created dynamically. But all the relevant fields are intended to be stored in the table statically.

[mshkanth]

Proposal to add a deleted flag to the database

1. Would request to add a deleted or is_active field to all the 3 tables in the database schema. This can be used to soft delete any records either from the web app or from the covalent dispatcher. This is relevant because there is already a feature today on the web app GUI enabling users to delete dispatch(es).
2. Whenever the web app or the covalent dispatcher modifies the deleted, it is expected that the updated_at flag is also updated.

Cc @FyzHsn

[FyzHsn]

@mshkanth Adding it in DB V10 - Can you give it a quick glance here?

[FyzHsn]

@mshkanth I'm going to add the is_active field in the DB models. However, I'm not implementing any methods to change it in the Covalent backend since there is no Result object metadata field to store this information. How do you foresee this field being changed? Do you think, updating these fields from the UI when the user deletes a workflow is possible? In any case, more discussion on how to incorporate modification of this field is required before implementing anything.

[mshkanth]

@mshkanth I'm going to add the is_active field in the DB models. However, I'm not implementing any methods to change it in the Covalent backend since there is no Result object metadata field to store this information. How do you foresee this field being changed? Do you think, updating these fields from the UI when the user deletes a workflow is possible? In any case, more discussion on how to incorporate modification of this field is required before implementing anything.

When will the GUI can update this field
Since this is soft deletion, we can potentially permit a user to delete dispatches across all statuses from the GUI. When a user deletes a dispatch from the GUI, the current inference & requirement is that s/he doesn't want to see those records on the GUI any longer. This is independent of what the dispatch status is.

How will the GUI modify the field
The web app GUI will trigger a 'delete' API written on the web abb backend. This API will modify the is_active field of those lattices, electrons & electron_dependencies

How will this change in the future?
This implementation can be modified when 'cancellation' or 'stoppage' of an inprogress dispatch is brought as a feature. When that feature is introduced, the web app will call a 'cancellation' API supported by the covalent dispatcher which will stop/cancel the dispatch and alter the status to CANCELLED. In this way, 'deletion' is a web app feature while 'cancellation' is a covalent dispatcher feature. Deletion will also trigger cancellation, but may not be the other way (Subject to what the AQ engineering & business team decide)

@FyzHsn - Can you go through the above & also let us know who this needs to be discussed with on the AQ side?

[mshkanth]

Closure to dispatch deletion discussion -

#815 (comment)

[mshkanth]

Consolidation of all fixes & schema enhancement requests from Team Psi - Observed on v9 DB implementation

#776 (comment)

[FyzHsn]

@mshkanth Due to some recent changes in covalent, some of the data stored in the filenames are TransportableObjects. In order to get a string representation of these, once these objects are unpickled, you need to do unpickled_object.object_string to get the string representation. A list of these pickled TransportableObjects are:

Lattice.function_filename, Lattice.inputs_filename (this needs to be unpickled, and then the args and kwargs fields needs to be unpacked), Electron.value_filename, Electron.function_filename, Electron.results_filename.

Let me know where further clarifications are required.

@cjao Let me know if I've missed anything.

[mshkanth]

@FyzHsn Thanks for the heads up Faiyaz. Will reach out if the team has questions.