Why wouldn't we want to send the sync summary with every sync request? It seems like a minor overhead for a function that only gets called ~once per day by most users, and means the plugin doesn't have to store that state. My vote would be to send it as part of the Sync response.

that can work

let's do this in a follow-up PR though as it's already pretty big

Instead of _cq_source_name and _cq_sync_time, which will be the same for all inserted rows in a sync, I would like to suggest that we introduce a CloudQuery syncs table, for which an ID gets referenced here.

So for example, here we'd add one new column, _cq_sync_id. In another table, maybe called cq_syncs, we can list all past syncs with the following columns to start with:

id text (pk)
source_name text
start_time timestamp -- indexed

--- other ideas, not strictly required now:
end_time timestamp -- update at end of sync
status text -- updated when we transition from one state to another
errors uint
warnings uint
metadata jsonb -- plugins can use this to store cursors, for example

For delete-stale functionality, we wouldn't need to join or reference this table: we would only need to delete all rows with sync IDs that are not the current sync's ID.

Users who wish to delete stale records on their own can query this table to get the latest sync ID and then delete all rows with a different sync id. If we expand it with some more data (some ideas listed above), this table could also give users a great place to start monitoring their syncs without needing to parse CLI output or logs.

In any case, even if we keep it minimal for now, such a table will allow us to expand our metadata in the future without polluting all tables with new columns.

This table could also be useful for future functionality, such as cursors, where source plugins need to store some information for retrieval on the next sync. But that can also be designed differently.

I would avoid for now the need for additional table and foreign keys. I think it is quite common in datalakes to have duplicates in favour of simplicity.

So basically simple implementation first -> operational experience -> more complex one later.

we can still add later on a table with all sync times but I would still avoid the foreign keys as they are also not supported in all databases. but I don't think this a blocker and we can always add this table later.

Agree, makes sense to keep things simple at first, and data lakes often do denormalize for the sake of simplicity. I don't think a database needs to strictly support foreign keys for this secondary table to be useful though; you can always look up the latest ID and then run further queries using it (accepting the risk in this case that operations won't necessarily be atomic, again depending on the database).

For me the important thing is having a handle to a unique sync id with which you can reference further information, should you need it. If we think that's something we'll add later, to maintain backwards-compatibility we will then have 5 _cq columns instead of 3:

|_cq_id|UUID|
|_cq_parent_id|UUID|
|_cq_source_name|String|
|_cq_sync_time|Timestamp|
|_cq_sync_id|UUID|

I think that's okay, and if you think it's okay we can do it, but worth noting that we have the chance now to keep the number of internal columns to their theoretical minimum.

Or the other option, down the line, would be to use sync_time and source_name as a combined PK, if we want to keep it to 4 internal columns but make joins a bit harder.

I think _cq_sync_time (_source_name) should be good enough for now for uniqueness and I wouldn't optimize yet for small amount of columns it doesn't really cost us anything atm. so would suggest to look into that down the line when/if it will become an option.