default content addressable id for snapshots

[samuelstroschein]

Context

Problems: 

• joining if change.snapshot_id can be nullable
• if for every nukllable snapshot a row is created (same snapshot.id) = bloat

Solution: 

1. change.snapshot_id is always defined
2. file handlers create an id from the blob of a snapshot value → nullable snapshot all refer to the same snapshot id → no bloat 

Proposal

Use an id on insert that is content addressable by default.

• users don't need to think about ids
• default = if not content addressable is desired, provide your own id

[samuelstroschein]

EDIT: Haha nvm. We concluded that if the id function can detect the undefined snapshot, it will take the const EMPTY_SNAPSHOT_ID, reducing the need to handle empty snapshots in user land.

@martin.lysk1 inserting the nullable snapshot row does make sense in the schema.

Otherwise, detect changes has to insert an empty snapshot every time a delete change is detected.

The following is easier

• check if a snapshot exists for the detected change
• if yes, let the databsae generate the id, if not choose the empty snapshot id

[samuelstroschein]

Sync with @martin.lysk1

Decisions

1. Snapshot.value is BLOB 
2. snapshot gets no additional properties because content addressable 
3. SDK inserts snapshot with cast as jsonb if plugin returns Record<string, any>
4. utility function for testing jsonSnapshot 

CleanShot 2024-10-17 at [email protected]

[samuelstroschein]