Save the primitive config to the database in every case instead of just for dynamic primitives

Primitives in Paima actually write information to the database in pre-defined tables. This is great, because it means you can query these tables directly in SQL if needed, as well as use the provided utility functions for the most common use-cases

The problem comes, whenever, when you want to write an SQL query that combined with a built-in Paima table. Consider the following example:

CREATE TABLE my_custom_table(
   chain_id TEXT NOT NULL,
   contract_address TEXT NOT NULL,
   token_id UINT256 NOT NULL,
   PRIMARY KEY (chain_id, contract_address, token_id)
);

-- select queries

SELECT *
FROM my_custom_table
JOIN cde_erc721_data
    ON ??? -- no good solution here!

(Partial) Existing solutions

1. Hardcoded primitive name

This works as long as you have exactly one table to query

SELECT *
FROM my_custom_table
JOIN cde_erc721_data
WHERE cde_erc721_data.cde_name = 'my-primitive-name';

2. Dynamic tables

Dynamic tables, as mentioned previously, already comes with the config built into it, so you can query it right away

Suppose you have an existing table with the following schema

Then you can query it using the following

SELECT *
FROM cde_dynamic_primitive_config 
JOIN cde_erc721_data
    ON cde_dynamic_primitive_config.cde_name = cde_erc721_data.cde_name
JOIN my_custom_table
    -- careful for casing here
    ON LOWER(cde_dynamic_primitive_config.config->>'contractAddress') = my_custom_table.contract_address;
    AND my_custom_table.token_id::TEXT = cde_erc721_data.token_id;

So what's wrong?

As you can see, the dynamic primitive case is more powerful, and it would be nice to get that flexibility elsewhere

But beyond this, there is another issue: how do we filter by the network?

Here, even the dynamic primitive doesn't help us because primitive configurations do not contain a machine-friendly name for the network they are registered in. Rather, the primitives only contain the human-friendly name of the network that is then matched against the config/env to get the chain ID

In an ideal world, we would expand the network information inlined in every primitive which would solve this problem, but that would require a redesign of the config system (which I definitely think we should do eventually) since it would require us to dynamically compute the caip-2 ID

In the meantime, I think we should add the caip2 network ID to the chain_data_extensions table, since every primitive should have a network (the only case is in the future if we have some kind of timer or purely L2 extension, but those can just be null or something we can decide later)

This would allow for the following SQL

SELECT *
FROM cde_dynamic_primitive_config 
JOIN cde_erc721_data
    ON cde_dynamic_primitive_config.cde_name = cde_erc721_data.cde_name
JOIN chain_data_extensions
    ON cde_dynamic_primitive_config.cde_name = chain_data_extensions.cde_name
JOIN my_custom_table
    -- careful for casing here
    ON LOWER(cde_dynamic_primitive_config.config ->>'contractAddress') = my_custom_table.contract_address;
    AND my_custom_table.chain_id = chain_data_extensions.cde_caip2
    AND my_custom_table.token_id::TEXT = cde_erc721_data.token_id;

PaimaStudios / paima-engine