Brainstorming: Storing plugin metadata in the database

[cdecker]

This came up while debugging #4482, where we have the issue that we
try to reconstruct more context, i.e., group of sendpay commands
that were initiated by the same pay command, but don't really have
enough information to do so. In #4482's case we group by the
payment_hash which is too aggressive in grouping sendpays into the
same group if pay was called multiple times with the same invoice /
payment_hash, resulting in a potential flip-flop of the state
displayed in the listpays output. The solution here is to add an
explicit grouping key that is used to group only the ones together
that are part of the same group, but we faced this issue a number of
times: plugins wanting to store some metadata, in otherwise unrelated
contexts. As an example, sendpay has no concept of an invoice, it
just deals with routes, HTLCs and their status, the invoice we
shoehorn into the first sendpay of a pay is there just because we
don't have a better place for it.

I'd like to propose that we add a new table metadata with just two
columns: id BIGINT AUTOINCREMENT and data BLOB. We then two new
RPC methods createmetadata and getmetadata which can be used to
create a new entry in the table, and get the entry based on its
id. Then from various tables such as the payments table we have a
nullable reference to the metadata table allowing us to attach extra
data to them if desired. pay could then createmetadata for each
invocation, storing invoice and other metadata in the table, it'd
get back the new row's id, which is then passed to all sendpay
invocations. listpay can then collect and group the listsendpays
result, based on the metadata_id not on the payment_hash which
we'll lose as grouping key once we get PTLCs anyway. Furthermore it
can enrich its own result with the metadata stored by pay in a
well-defined location.

To begin with the metadata is immutable, and can only be created.

[vincenzopalazzo]

I'd like to propose that we add a new table metadata with just two
columns: id BIGINT AUTOINCREMENT and data BLOB.

Concept ACK.

[cdecker]

One thing that I forgot to mention would be that lightningd treats the metadata as opaque
blobs, i.e., it doesn't parse or otherwise interpret them. This allows plugins to determine the
format that is most sensible for their use-case, including 0-byte values for markers like the
aforementioned grouping key for sendpays, or full JSON strings that can contain complex
data used by the plugin.

[niftynei]

Sounds ripe for abuse/data-bloat, but I definitely see the benefits to having a centralized message bus for cross-plugin data.