improve cross version mutation operations

[samuelstroschein]

Context

Making cross version CRUD operations (due to inheritance) is a bit odd.

The state implementation is split into two views state (active) and state_all (across all versions)to a) avoid accidental all version queries and b) because specifying the version_id in every query is annoying.

We had two options to achieve a and b:

1. One unified view that filters the version_id column by default for the active version
2. Split both views and avoid a default filter.

We choose option 2 as we believed option 1, albeit a nicer DX, would break SQL standards of defining a default filter. For example, the implicit active_version filter will likely not show up in a SQL explain prompt.

Problems

Global entities

some entities are global (change_author), which leads to a weird api chaos of inserting into state_all instead of state instead of just defaulting the version_id to global.

// insert some entity which is global by default (changes, versions, etc)
	await lix.db
		.insertInto("change")
		.values({
		  id: "c0"
		})
		.execute();

// 😵‍💫 confusion moment "why do i need to use _all api here?" 
	await lix.db
		.insertInto("change_author_all")
		.values([
			{
				change_id: "c0",
				account_id: account.id,
				lixcol_version_id: "global",
			},
		])
		.execute();

A better API would be to directly insert into change_author and either manually specifying the version_id (but without the state_all confusion)

//  
	await lix.db
		.insertInto("change_author")
		.values([
			{
				change_id: "c0",
				account_id: account.id,
				lixcol_version_id: "global",
			},
		])
		.execute();

Or even better, specify a default version_id on the schema. Then, no version id needs to be specified at all.

export const LixChangeAuthorSchema = {
	"x-lix-key": "lix_change_author",
	"x-lix-version": "1.0",
+	"x-lix-default_version-id": "global",
  ...
}

[samuelstroschein]

The three options I see are:

1. Ignore the problem "it is what it is".
2. Implicit filter in vtable (removes the need for _all views at the cost of "not being SQL standard")
3. Allow mutations from state view cross version (would solve the inheritance / mutate global problem)

It's probably best to wait for feedback from developers. How annoying and unfamiliar is the _all api?

cc @NiklasBuchfink @felixhaeberle @martin-lysk (if you have a strong opinion favoring one option, please come forward. otherwise, let's wait for user feedback)

[felixhaeberle]

I would go for option 3 – allow cross-version mutations in state view.

Cleanest DX without breaking SQL standards. Schema default for global entities "x-lix-default_version-id": "global" + accept explicit lixcol_version_id in mutations. Keeps state_all for power users only.

[samuelstroschein]

@felixhaeberle yeah that's prob the way to go. i just implemented opral/monorepo@b0700f2 and the lack of cross version mutations from state is leading to lots of boilerplate

[samuelstroschein]

Ah now, damn. We can't expose version_id in state. Otherwise, one would get duplicate rows. One for the global and one or the local version.

[samuelstroschein]

LLMs really struggle with the _all and active state stuff. Here is example code that an llm wrote. It thought that inserting into key_value is global??? Why?

// Add some test data to the global version
	await lix.db
		.insertInto("key_value")
		.values({
			key: "test-key",
			value: "test-value",
		})
		.execute();

The two options we have in addition to leave things as is:

1. Use the implicit filter in vtable and remove _all alltogether
2. Maybe rename `state to state_active and state_all to state as the default is cross version and active is an explicit "this is filtered by the active state". key_value -> key_value_active, key_value_all -> key_value