Maintainability of schema changes during runtime of an app We have an application which accepts JSON objects from X number of third party services and unfortunately we're locked into using SQL to store it. The objects must be flat (no nesting), but each third party can have a different set of properties.
I feel like it would be a better approach to have a static schema - for example, there would be a &quot;Definitions&quot; table that contained an entry for each unique property, then for each object there would be a row for each property that references the definition, for example - given:
{
  &quot;prop_name&quot;: &quot;John Doe&quot;,
  &quot;prop_tel&quot;: &quot;+15555555555&quot;
}

There would be the following in SQL:
Definitions table - 
  | Id | Ref       | ...
  | 2  | prop_name |
  | 3  | prop_tel  |

Entities table -
  | Id | ...
  | 6  |

Values table - 
  | Id | FK_DEF | FK_ENTITY | Value        | ...
  | 9  | 2      | 6         | John Doe     |
  | 10 | 3      | 6         | +15555555555 |

This is slightly simplified from what might be a final design, but in practice this would let you store any format of data in SQL. The expense here is that lookups on this data is hard, you need to use a pivot or nested selects. (and we do need to do lookups on this data, sorting the entities by the values table, etc)
My colleague is convinced that this is ridiculous and we should just be dynamically modifying the schema as we go - either:

Adding a new table for each unique json object we need to store
Adding a new column to a single table for each unique property. Note that properties named the same coming from different third parties or data sources still need to be treated as separate columns.

We can never delete data and hope for this to be a very scalable application - containing hundreds of thousands or potentially millions of records.

Keeping in mind we know both of these options are crap but don't have a choice: Can anyone give me an idea of what the best practices are, and pros/cons of each approach? None of us have had to do this before and we are worried we will get locked into a path and hit road blocks later when trying to scale this. Off the top of my head having absolutely no experience, here's the list I'd make:
Static Schema
Pros:

Predictable
Maintainable
Not difficult to add new data format or perform inserts

Cons:

Slower / complicated lookups (pivot or nested select)
More complicated inserts / updates (for entities that exist)

Mutating Schema
Pros:

Fast lookups
Easy updates (for entities that exist)

Cons:

Schema out of our control, can get massive and hard to maintain over time
Database migrations to new schema gets harder and harder as time passes
Its not recommended for production app to have access to change schema

What am I missing and what would be the best path to take considering long-term scalability and maintenance?
If the data is going to have many different layouts, you might be overthinking it.

Many DBMSs have great support for JSON now. You can store it natively as JSON while defining a view on top of JSON data that exposes it to other users as a regular view with columns.

https://www.postgresql.org/docs/current/static/datatype-json.html

https://docs.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server