[skdb] Referencing the current SKDB userID.

[ltique]

I've realised that I'm often having to insert the SKDB user ID I'm currently logged in as. In the CLI I have to spell it out each time (f8b7ObYn10n1kuppab1Ds4bti3 etc.). In TypeScript I do the parameters dance of "... userID = @userID", {userID: 'f8b7ObYn10n1kuppab1Ds4bti3'}.

Could we introduce an SQL function (or constant?) that references the current SKDB userID? It would make a lot of this stuff a lot easier. It would also make it more obvious (at least to me!) when I'm referencing the current user ID (which I do 99% of the time) or another user ID (which I've so far done very rarely). By making the latter pop out, it might help me more carefully audit when I'm doing something "cross user", which is probably the most likely place I'm going to introduce privacy mistakes.

[gregsexton]

skdb.exec(
  `INSERT INTO foo
    (x, skdb_access, skdb_author)
    VALUES (@dbName, @whoami, @whoami);`,
  { dbName, whoami: skdb.currentUser ?? null },
);

[ltique]

@gregsexton Indeed, that's the "parameters dance". It's fairly verbose, and also not something one can do in the CLI. I've also realised that it's quite easy to put the wrong user ID in the wrong row, particularly when refactoring code.

So what I was thinking was: wouldn't it be nice (and for the CLI essential!) if I could access the SKDB user in SQL?? For example something like INSERT INTO foo (x, skdb_access, skdb_author) VALUES ("...", skdb_user(), skdb_user()) or similar? This would make it possible to use the CLI for insertions, and also make the "common case" of "the current user ID" pop out syntactically vs. the less common case of "another user / group ID".

[gregsexton]

I think this can be done quite easily and I'm not against it. Can't hurt to make this situation convenient.

But I'm less sold on the value prop. And I think it's worth me explaining to get us on the same page.

First up, I'm ignoring the CLI somewhat because it's not the main way you'll interact with SKDB when developing something. It's useful for a quick test or some debugging. But when I'm actually building services or web apps (which I've been doing for the last few weeks), I rarely touch it.

and also make the "common case" of "the current user ID" pop out syntactically vs. the less common case of "another user / group ID".

I don't agree that this will be more common than using a group. For many situations you'll want a group because it's rare that you're communicating information with just yourself. But let's say the info is private, you'll potentially want a service to make use of it in someway - so you share that row with yourself and the service worker. This is how the 'create db' request table works, for example. And even in the case where it's purely private, you probably want a group with just that user in it, to allow for future use cases and give yourself some flex.

When creating, managing, and working with groups, we have a JS api for that and you pass in the value as a param. So the currentUser example I give above is consistent with that.

[ltique]

You're right that groups are going to be very common. It might just be because my experiments have had a unusual mix of group and "solo" use.

Here's a slightly different way of looking at what I'm suggesting. Setting skdb_access to user_id() is the ultra-safe option from a privacy perspective, so making it easy or a default stops people over-sharing. Of course, having written that I now wonder if what I really want is DEFAULT USER_ID in the sense of CREATE TABLE (x TEXT, skdb_access TEXT DEFAULT USER_ID)! Actually that would be most useful, I think, for the skdb_author column where so far I have mostly just stuck the current user ID in?

[beauby]

Closing skdb-specific issues as skdb in its current form is being deprecated.