User Preferences (Upsert) ​

Store per-user settings as key-value pairs. The edit/:id endpoint with or: 'INSERT' gives you upsert behavior -- create if missing, update if exists.

Config ​

typescript
{
    name: 'preferences',
    autoSetUid: false,  // we'll use a composite key pattern
    fields: [
        { name: 'id', type: 'text', sqlType: 'text', primary: true, notNull: true,
          usage: 'record_uid', noUpdate: true },
        { name: 'created', type: 'date', sqlType: 'timestamp',
          default: sql`CURRENT_TIMESTAMP`, notNull: true, usage: 'record_created',
          noInsert: true, noUpdate: true },
        { name: 'updated', type: 'date', sqlType: 'timestamp',
          default: sql`CURRENT_TIMESTAMP`, notNull: true, usage: 'record_updated',
          noInsert: true, noUpdate: true },
        { name: 'user_id', type: 'relation', sqlType: 'text', notNull: true,
          foreignKey: { table: 'users', column: 'id', onDelete: 'CASCADE' } },
        { name: 'key', type: 'text', sqlType: 'text', notNull: true },
        { name: 'val', type: 'text', sqlType: 'text' },
    ],
    triggers: [createdTrigger, updatedTrigger],
    indexes: [
        { fields: 'user_id' },
        { fields: 'key' },
    ],
    extensions: [{
        name: 'rules',
        listRule: 'auth.uid == user_id',
        viewRule: 'auth.uid == user_id',
        createRule: 'auth.uid != null & user_id == auth.uid',
        updateRule: 'auth.uid == user_id',
        deleteRule: 'auth.uid == user_id',
    } as TableRulesExtensionData],
}

API calls ​

bash
# Upsert a preference — creates if the ID doesn't exist, replaces if it does
curl -X POST http://localhost:8787/api/v1/table/preferences/edit/USER_ID_theme \
  -H 'Authorization: Bearer <token>' \
  -H 'Content-Type: application/json' \
  -d '{"setValues": {"user_id": "USER_ID", "key": "theme", "val": "dark"}, "or": "INSERT"}'

# Get all preferences for a user
curl 'http://localhost:8787/api/v1/table/preferences/select?where=user_id%3D%22USER_ID%22' \
  -H 'Authorization: Bearer <token>'